BigQuery で array<struct<...>> にデータを登録する方法

BigQuery で array<struct<...>> のフィールドにデータを登録する方法のメモ。
■テーブル定義

create table `items` (
  item_id      string,
  store_id     string,
  stock        integer
);

create table `item_store_infos` (
  item_id          string,
  store_infos      array<
    struct<
      store_id     string,
      stock        integer
    >
  >
);

■登録
items テーブルから items_store_info テーブルにデータを登録。

insert into
  `item_store_infos`
(item_id, store_infos)
select
  item_id
  , store_infos
from (
  select
    item_id
    , array_agg(store_info) as store_infos
  from (
      select item_id, struct(store_id, stock) as store_info from `itmes`
  ) s
  group by
    item_id
);

Cloud SQL で Cloud Storage 上の tsv ファイルをインポート

Cloud SQL で Cloud Storage 上の tsv ファイルをインポートする方法のメモ。

以下のオプションで csv として読み込む。
・--fields-terminated-by で区切り文字にタブ（0x09）を指定
・-llines-terminated-by で改行（0x0A）を指定
・--quote でので囲み文字を指定しない

gcloud sql import csv \
       {cloud_sql_instance_id} \
       gs://{bucket}/{file.tsv} \
       --database={database_name} \
       --table={table_name} \
       --fields-terminated-by="09" \
       --lines-terminated-by="0A"

BigQuery で文字列から数値の配列を生成

BigQuery で文字列から数値の配列を生成する方法のメモ。

select
  array(select cast(e as float64) from unnest(split("1, 2, 3", ",")) e) as vector
;

BigQuery でデータを JSON 化して配列に集約

BigQuery でデータ項目を struct() で JSON 化して、array_agg() で配列に集約する方法のメモ。

BigTable には以下のデータを登録します。

insert into tbl values ('item_01', 'store_01', 100);
insert into tbl values ('item_01', 'store_02', 110);
insert into tbl values ('item_01', 'store_03', 120);

insert into tbl values ('item_02', 'store_01', 200);
insert into tbl values ('item_02', 'store_02', 210);
insert into tbl values ('item_02', 'store_03', 220);

insert into tbl values ('item_03', 'store_04', 300);

以下のクエリで JSON 化したデータを配列に集約します。
struct(store_cd, price) で {"store_cd": ..., "price": ... } の JSON 形式に変換し、array_agg() で item_id が同じレコードを集約します。

select
   item_id
  , array_agg(store_info)
from (
select
   item_id
  , struct(store_cd, price) as store_info
from
  tbl
)
group by
  item_id
;

実行結果は以下の通りで、同一 item_id で {"store_cd": ..., "price": ...} を集約した配列が得られます。

{"item_id": "item_01", "store_infos": [{ "store_cd": "store_03", "price": "120"}, {"store_cd": "store_01", "price": "100"}, {"store_cd": "store_02", "price": "110"}]}
{"item_id": "item_02", "store_infos": [{"store_cd": "store_02", "price": "210"}, {"store_cd": "store_03", "price": "220"}, { "store_cd": "store_01", "price": "200"}]}
{"item_id": "item_03", "store_infos": [{"store_cd": "store_04", "price": "300"}]}

BigQuery で json オブジェクト内の array の処理

BigQuery で json オブジェクト内の array を処理する方法のメモ。
まず、BigQuery のテーブルに以下のような json データを登録します。

insert into `test1.tbl1` (json) values ('''
{"type": "search", "uid": "004", "kw": "フライパン", "items": [
{"rank": 1, "id": "001", "title": "商品1"},
{"rank": 2, "id": "002", "title": "商品2"},
{"rank": 3, "id": "003", "title": "商品3"}
]}
''');

insert into `test1.tbl1` (json) values ('''
{"type": "search", "uid": "004", "kw": "鍋", "items": [
{"rank": 1, "id": "002", "title": "商品2"},
{"rank": 2, "id": "003", "title": "商品3"},
{"rank": 3, "id": "004", "title": "商品4"}
]}
''');

上記のデータから、kw と id を抽出するには以下のクエリを実行します。

select
  json_extract_scalar(t1.json, "$.kw") as kw
  , json_value(item, "$.id") as id
from
  `test1.tbl1` as t1
left join
  unnest(json_query_array(json_extract(t1.json, "$.items"))) as item
where
  json_extract_scalar(t1.json, "$.type") = "search"
;

実行結果は以下の通り、kw と id を抽出できています。

行  kw          id
1   フライパン  001
2   フライパン  002
3   フライパン  003
4   鍋          002
5   鍋          003
6   鍋          004