普段Google BigQueryでは簡単な集計SQLしか使っていないのですが、集計SQL以外にも色々便利な機能があります。
ただ、使用頻度が高くないので、いざ使おうとするとマニュアルを探すところから始まるので、自分用に簡単な使い方をまとめておこうと思います。
これまでいくつかのカテゴリーに分けてまとめたのですが、今回はそれらに当てはまらないものをその他としてまとめました。
「BigQueryの少し凝った使い方メモ」記事一覧
- BigQueryの少し凝った使い方メモ（ビュー・マテリアライズドビュー・テーブル関数）
- BigQueryの少し凝った使い方メモ（テーブル作成・データ更新）
- BigQueryの少し凝った使い方メモ（スケジュールされたクエリ）
- BigQueryの少し凝った使い方メモ（プログラミング）
- BigQueryの少し凝った使い方メモ（etc）
配列
カラムから配列を作る
SELECT ARRAY<STRING>[col01, col02] AS array_data FROM tbl
インデックスを指定して配列から要素を取り出す
- インデックス指定には
OFFSET()を使う SAFE_OFFSET()を使うと要素が無い時はNULLとなる
SELECT array_data[SAFE_OFFSET(2)] AS array_value FROM tbl
GROUP BYして配列を作る
SELECT col01, ARRAY_AGG(col02) AS array_data FROM tbl GROUP BY col01
- 配列の中の要素の並びを指定する
SELECT col01, ARRAY_AGG(col02 ORDER BY col03, col04) AS array_data FROM tbl GROUP BY col01
配列を行に展開する
- 配列の要素数が０個の行は展開されない
SELECT tt.col01, array_value FROM tbl AS tt CROSS JOIN UNNEST(tt.array_data) AS array_value
- 配列の要素数が０個の行を
NULLとして展開する
SELECT tt.col01, array_value FROM tbl AS tt LEFT JOIN UNNEST(tt.array_data) AS array_value
JSON型
- BigQueryには基本型に加え
JSON型がある JSONの要素はアクセス演算子により通常のJSONと同じように参照できる
WITH tbl AS ( SELECT JSON'{"id": 123, "name": "ABC", "items": ["itemA", "itemB"]}' AS json_val ) SELECT json_val.id AS id, json_val.name AS name, json_val.items[0] AS first_item FROM tbl
要素の型はJSON
- 要素にアクセスする際、その要素がスカラー値だったとしても型は
JSON型になるので、そのままでは演算できない
下記はJSON型とINT64型の演算になるのでエラーになる。
SELECT json_val.id * 10 AS id_by_10 FROM tbl
JSON型からスカラー値を取り出すには、各型の変換を行う必要があるBOOL()INT64()FLOAT64()STRING()
下記はJSON型からINT64型に変換しているのでエラーにならない
SELECT INT64(json_val.id) * 10 AS id_by_10 FROM tbl
配列の取得
JSON型の配列にアクセスしても、それはBigQueryの配列ではなくJSON型になるJSON型の配列から要素を取り出し、BigQueryの配列型に格納するにはJSON_QUERY_ARRAY()を使う- 配列に格納される値は
JSON型ARRAY<JSON>
SELECT JSON_QUERY_ARRAY(json_val.items) AS items_array FROM tbl
文字列化されたJSONをJSON型に変換する
PARSE_JSON()を使う
WITH tbl AS ( SELECT '{"id": 123, "name": "ABC", "items": ["itemA", "itemB"]}' AS json_str_val ) SELECT PARSE_JSON(json_str_val).id AS id, PARSE_JSON(json_str_val).name AS name, PARSE_JSON(json_str_val).items[0] AS first_item FROM tbl
JSONデータ取り込み
- 改行区切りの
JSONデータをテーブルに取り込むパターンは下記がある- 全体を1つの
JSONとして取り込む - 要素を
JSON型とする JSON要素に文字列化したJSONデータを格納する
- 全体を1つの
全体を1つのJSONとして取り込む
data.json
{"event_date": "2023-01-01", "json_data": {"id": 123, "name": "ABC"}} {"event_date": "2023-01-02", "json_data": {"id": 456, "name": "DEF"}}
schema
json_data: JSON
要素をJSON型とする
data.json
{"event_date": "2023-01-01", "json_data": {"id": 123, "name": "ABC"}} {"event_date": "2023-01-02", "json_data": {"id": 456, "name": "DEF"}}
schema
event_date: DATE, json_data: JSON
JSON要素に文字列化したJSONデータを格納する
JSONデータは文字列で持ち、必要に応じてSQLでPARSE_JSON()を使ってJSON型に変換する
data.json
{"event_date": "2023-01-01", "json_str_data": "{\"id\": 123, \"name\": \"ABC\"}"} {"event_date": "2023-01-02", "json_str_data": "{\"id\": 456, \"name\": \"DEF\"}"}
schema
event_date: DATE, json_str_data: STRING
SQL
SELECT PARSE_JSON(json_str_data) AS json_data FROM tbl
bq load
- 通常テーブル・日付パーティショニングテーブル
#!/bin/bash dataset=dataset_name table=table_name bq load \ --replace=true \ --source_format=NEWLINE_DELIMITED_JSON \ ${dataset}.${table} \ ./data.json
- 取り込み時間によるパーティショニングテーブル
#!/bin/bash dataset=dataset_name table=table_name partitiontime=20220101 bq load \ --replace=true \ --source_format=NEWLINE_DELIMITED_JSON \ ${dataset}.${table}"\$"${partitiontime} \ ./2022-01-01.json
日付パーティショニングテーブルでbq loadを使う時の注意
--replace=trueにすると、loadされるデータ以外のデータは全て削除される。
日付パーティショニングテーブルでbq loadを繰り返し行っても値が変わらないようにするには下記の手順になる
- ロードするデータの日付は１つに揃える
- テーブルからロードする日付のデータがあれば事前に削除する
bq loadを--replace=falseで実行する
指定した日のデータを削除
DELETE FROM `project_name.dataset_name`.table_name WHERE DATE(column_name)="2023-01-01"
bq query
- クエリ内の値をパラメータ化できる
@param_nameでパラメータ化にする--parameter=<param_naem>::<value>で値を渡す
sample.sql
SELECT * FROM tbl WHERE user_id=@param_id
query.sh
#!/bin/bash cat sample.sql | bq query \ --parameter=param_id::123 \
サンプルデータ作成
- 各レコードの値だけを並べて書いて作成する方法
SELECT * FROM UNNEST( ARRAY<STRUCT<val01 INT64, val02 STRING>> [ (1, "A"), (2, "B"), (3, "C") ] )
| val01 | val02 |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
その他便利構文
EXCEPT()
*で全カラムを出力しつつ、EXCEPT()で特定のカラムを出力しない
SELECT * EXCEPT(user_id, created_at) FROM tbl
DISTINCT
- テーブルの重複を取り除く
SELECT DISTINCT * FROM tbl
ANY_VALUE()
GROUP BYしてもカラムの値が全て同じと分かっている時、カラムから任意の１レコードを取り出す
SELECT user_id, ANY_VALUE(age) AS age FROM tbl GROUP BY user_id
最頻値
APPROX_TOP_COUNT(col, x)を使うcol取得するカラムx出現回数上位何位まで取得するかを指定する。ここでは最頻値だけ知りたいので1とする
- 結果は出現回数を示す
countと値を示すvalueを持つ構造体の配列になる- 配列の一番最初の要素を取り出し、その要素から
valueを取り出す
- 配列の一番最初の要素を取り出し、その要素から
- カウントする値は
NULLも含むので注意
| user_id | val |
|---|---|
| 1 | A |
| 1 | A |
| 1 | B |
| 2 | A |
| 2 | B |
| 2 | B |
WITH tbl AS ( SELECT * FROM UNNEST(ARRAY<STRUCT<user_id INT64, val STRING>> [ (1, "A"), (1, "A"), (1, "B"), (2, "A"), (2, "B"), (2, "B") ])) SELECT user_id, APPROX_TOP_COUNT(val, 1)[OFFSET(0)].count AS top_count, APPROX_TOP_COUNT(val, 1)[OFFSET(0)].value AS top_count_value, FROM tbl GROUP BY user_id
中央値
PERCENTILE_CONT(col, x)を使うcolは取得するカラムxは全体を1とした時の抽出する場所。中央値なので0.5になる
- ウィンドウ関数なので全行に中央値が入る
GROUP BYとANY_VALUE()で値を1つにする
| user_id | val |
|---|---|
| 1 | 0 |
| 1 | 0 |
| 1 | 7 |
| 1 | 10 |
| 1 | 10 |
WITH tbl AS ( SELECT * FROM UNNEST(ARRAY<STRUCT<user_id INT64, val INT64>> [ (1, 0), (1, 0), (1, 7), (1, 10), (1, 10) ])) ,tbl_res AS ( SELECT user_id, PERCENTILE_CONT(val, 0.5) OVER(PARTITION BY user_id) AS median FROM tbl ) SELECT user_id, ANY_VALUE(median) AS median FROM tbl_res GROUP BY user_id
