この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
こんにちは、みかみです。
やりたいこと
- BigQuery では他のデータベースサービスと同等の SQL 構文がサポートされているのか確認したい
- BigQuery でサポートされていない SQL と同等の処理を行うにはどうすればよいか知りたい
前提
BigQuery の標準 SQL を対象とします。
また、SQL の検証には GCP 管理コンソール、bq
コマンドまたは Python クライアントライブラリを使用しています。
bq
コマンド、Python クライアントライブラリは、CLOUD SHELL から実行しました。
- コマンドライン ツール リファレンス | BigQuery ドキュメント
- Python Client for Google BigQuery
- Cloud Shell の使用 | Cloud Shell ドキュメント
CREATE TABLE
BigQuery でも CREATE TABLE
構文でテーブル作成が可能です。
カラムのデータ型には BigQuery のデータ型を指定する必要があり、他のデータベースサービスでサポートしている VARCHAR や BIGINT などの型は指定できませんが、NOT NULL 指定や OPTIONS 項目でカラムの論理名も付与することができます。
以下の SQL で、テーブルを作成してみます。
CREATE TABLE IF NOT EXISTS dataset_1.test_create (
id INT64 OPTIONS(description="ID"),
value STRING NOT NULL OPTIONS(description="カラムの説明")
)
定義した通り、テーブルが無事作成できました。
テーブルに対して OPTIONS 指定することで、パーティショニングや有効期限も指定できます。
以下の SQL で、有効期限とテーブルの説明を付与して既存のテーブルを置き換えてみます。
CREATE OR REPLACE TABLE dataset_1.test_create (
id INT64 OPTIONS(description="ID"),
value STRING NOT NULL OPTIONS(description="カラムの説明")
)
OPTIONS(
expiration_timestamp=TIMESTAMP "2020-12-31 23:59:59 Asia/Tokyo",
friendly_name="テーブルの分かりやすい名前",
description="テーブルの説明"
)
期待通り、OPTIONS が反映されたことが確認できました。
CTAS
CREATE TABLE ... AS
もサポートしているので、他テーブルからの SELECT
結果で新しいテーブルを作成することもできます。
CREATE OR REPLACE TABLE dataset_1.test_create
AS SELECT * FROM dataset_1.table_sample
カラムの NOT NULL 制約や description、テーブルの有効期限などのオプションは引き継がれませんが、元テーブルと同じテーブル定義、同じデータを持つテーブルが作成できました。
CREATE TABLE LIKE
BigQuery は CREATE TABLE ... LIKE
構文はサポートしていません。
ですが、前述の CREATE TABLE ... AS
を使って SELECT
結果が 0 件の SQL を実行すれば、カラム定義が同じ空テーブルが作成されることが確認できました。
TEMPテーブル
BigQuery Scripting を使えば、Temporary テーブルも使用できます。
一時テーブルなので、スクリプトを使わないで作成しようとするとエラーになります。
以下の BigQuery スクリプトで、一時テーブルを作成して結果を SELECT
してみます。
CREATE OR REPLACE TEMP TABLE test_create_temp AS SELECT * FROM dataset_1.table_sample;
SELECT name, gender FROM test_create_temp ORDER BY count DESC LIMIT 3;
複数の SQL をセミコロンで区切りさえすれば、bq コマンド経由でももちろん実行できます。
gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'CREATE OR REPLACE TEMP TABLE test_create_temp AS SELECT * FROM dataset_1.table_sample;SELECT name, gender FROM test_create_temp ORDER BY count DESC LIMIT 3;'
Waiting on bqjob_r54c5ca2c915d80b1_0000017395334b5b_1 ... (3s) Current status: DONE
CREATE OR REPLACE TEMP TABLE test_create_temp AS SELECT * FROM dataset_1.table_sample; -- at [1:1]
Created cm-da-mikami-yuki-258308._script92e81cfeb4fd560670ff07cbbd57ba35884da6b3.test_create_temp
SELECT name, gender FROM test_create_temp ORDER BY count DESC LIMIT 3; -- at [1:87]
+----------+--------+
| name | gender |
+----------+--------+
| Isabella | F |
| Jacob | M |
| Sophia | F |
+----------+--------+
Python クライアントライブラリ経由の以下のコードでも、Temporary テーブルが利用できることが確認できました。
from google.cloud import bigquery
query = 'CREATE OR REPLACE TEMP TABLE test_create_temp AS SELECT * FROM dataset_1.table_sample;SELECT name, gender FROM test_create_temp ORDER BY count DESC LIMIT 3;'
client = bigquery.Client()
job = client.query(query)
ret = job.result()
for row in ret:
print('{}: {}'.format(row.name, row.gender))
gcp_da_user@cloudshell:~/temp (cm-da-mikami-yuki-258308)$ python3 create_temptable.py
Isabella: F
Jacob: M
Sophia: F
ALTER TABLE
BigQuey でも ALTER TABLE
構文は使えますが、ラベルや有効期限などのテーブルオプションの変更しかできません。
テーブル定義やテーブルなどを変更したい場合は、別途コンソールや bq update
コマンド、テーブルのコピーなどで対応する必要があります。
SELECT
SELECT
文は他のデータベースサービスとそれほど相違なく利用することができます。
サブクエリや JOIN
も使えますし、GROUP BY
や ORDER BY
はもちろん、可読性を上げるためにも便利な WITH
句や、グルーピングデータを扱う時によく使う HAVING
句も使用できます。
- SELECT リスト | BigQuery ドキュメント
- 標準 SQL の式サブクエリ | BigQuery ドキュメント
- JOIN のタイプ | BigQuery ドキュメント
- WITH 句 | BigQuery ドキュメント
- HAVING 句 | BigQuery ドキュメント
以下、WITH
句を使った SQL を実行して、期待通りの結果が取得できました。
WITH top AS (
SELECT
max(count) AS count
FROM
dataset_1.table_sample
WHERE
gender = 'F'
)
SELECT
org.name,
org.count
FROM
dataset_1.table_sample as org, top
WHERE
org.count = top.count
EXCEPT
他のデータベースサービスで 2 つのテーブルの差集合を取得するときに EXCEPT
を使うことがあるかと思いますが、BigQuery では SELECT
句で EXCEPT
を指定することで 1 つのテーブルから特定のカラムを除いた結果を取得できます。
例えば、ウインドウ関数 ROW_NUMBER
で 重複レコードをパーティショニングしてユニークなレコードを取得したい場合、他の多くのデータべースサービスでは SELECT
句に row_number を除くテーブルカラムを全て列挙する必要がありますが、BigQuery では EXCEPT
を使って SELECT * EXCEPT(row_num)
で記述することができます。
以下、サンプル SQL です。
col_1 から col_10 まで、10 個のカラムを持つ table_a から、col_1 がユニークかつ col_10 の昇順でユニークレコードを抽出する場合、他のデータベースサービスでは row_number 以外の col_1 から col_10 まで全てのカラムを SELECT
する以下の SQL を書く必要があります。
SELECT
col_1,
col_2,
col_3,
col_4,
col_5,
col_6,
col_7,
col_8,
col_9,
col_10
FROM (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY
col_1
ORDER BY
col_10
) as row_number
FROM
table_a
) a
WHERE
row_num = 1
BigQuery では EXCEPT
を使って以下のように書くことができます。
SELECT
*,
EXCEPT(row_num)
FROM (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY
col_1
ORDER BY
col_10
) as row_number
FROM
table_a
) a
WHERE
row_num = 1
カラムが少ないテーブルであればそれほど問題はないかと思いますが、カラム数が多いテーブルの場合は EXCEPT
で row_number を除外できると非常に便利です!
SELECT COUNT(*)
BigQuery はクエリ課金のため、実行したクエリのデータ処理量に従って課金が発生しますが、SELCECT COUNT(*)
には料金がかかりません。
34073 行のレコードがあるテーブルに対して SELCECT COUNT(*)
クエリを実行した場合の処理データ量を bq query
コマンドの --dry_run
オプションで確認してみます。
gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> --dry_run \
> 'SELECT COUNT(*) FROM dataset_1.table_sample'
Query successfully validated. Assuming the tables are not modified, running this query will process upper bound of 0 bytes of data.
running this query will process upper bound of 0 bytes of data
とのことで、スキャンされるデータ量は 0 byte であることが確認できました。
UNNEST
BigQuery では、構造化データや配列型のカラムを持つテーブルを定義できます。
JOSN データをロードする場合などに便利ですが、一方、ロード済みデータを SELECT
する時には 1 レコードに複数のネストデータが存在する、若干扱いにくいデータ構造になってしまいます。
SELECT
favoriteFruit,
friends,
tags
FROM
`cm-da-mikami-yuki-258308.dataset_2.load_json`
WHERE
favoriteFruit = 'banana'
UNNEST
を使えば、構造化データを平坦化して組み合わせパターン分のレコード数で取得することができます。
SELECT
favoriteFruit,
friends,
tags
FROM
`cm-da-mikami-yuki-258308.dataset_2.load_json`,
UNNEST(friends) AS friends,
UNNEST(tags) AS tags
WHERE
favoriteFruit = 'banana'
UPDATE/DELETE
BigQuery でも UPDATE
や DELETE
構文をサポートしていますが、必ず WHERE
句を指定する必要があります。
テーブルの全てのレコードを更新/削除したい場合、他のデータベースサービスでは WHERE
句なしで SQL を実行できますが、BigQuery ではエラーとなってしまいます。
gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'DELETE FROM dataset_1.test_create'
Error in query string: Error processing job 'cm-da-mikami-yuki-258308:bqjob_r5ea6d98016cdcd7f_00000173a04993b6_1': DELETE must have a WHERE clause at [1:1]
BigQuery でテーブルの全件を UPDATE
または DELETE
したい場合には、WHERE True
を付与する必要があります。
gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'DELETE FROM dataset_1.test_create WHERE True'
Waiting on bqjob_rc2572d4a97f0417_00000173a04c1dc0_1 ... (0s) Current status: DONE
Number of affected rows: 34073
gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT COUNT(*) FROM dataset_1.test_create'
Waiting on bqjob_r621ef1d6564b54ee_00000173a04ceed6_1 ... (0s) Current status: DONE
+-----+
| f0_ |
+-----+
| 0 |
+-----+
なお、True を意図して WHERE 1
と記述してみたのですが、True とは認識してくれませんでした。。
gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'DELETE FROM dataset_1.test_create WHERE 1'
Error in query string: Error processing job 'cm-da-mikami-yuki-258308:bqjob_r786bd401ea4e1efb_00000173a04b8b02_1': WHERE clause should return type BOOL, but returns INT64 at [1:41]
TRUNCATE
BigQuery では、TRUNCATE
構文をサポートしていません。
テーブルの全データを削除する場合には、前述の通り WHERE True
付きの DELETE
構文を実行することで TRUNCATE
同等の結果が実現できます。
または、バッチ処理などで連携ファイルや別テーブルから全件洗い替えでデータをロードしたい場合、実行オプションを指定することで、TRUNCATE
不要で処理が実現できます。
例えば GCS に配置された連携ファイルから全件洗い替えでデータをロードする場合には、以下のコマンドで実現できます。
bq load \
--autodetect \
--replace \
--source_format=CSV \
dataset_1.table_load_sample \
gs://test-mikami/data_sample.csv
テーブルデータを以下のファイルデータで洗い替えてみます。
col_1, col_2
1,value1
2,value2
3,value3
gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT COUNT(*) FROM dataset_1.table_load_sample'
Waiting on bqjob_r521d7be2697ba7e8_00000173a30189eb_1 ... (0s) Current status: DONE
+-----+
| f0_ |
+-----+
| 5 |
+-----+
gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq load \
> --autodetect \
> --replace \
> --source_format=CSV \
> dataset_1.table_load_sample \
> gs://test-mikami/data_sample.csv
Waiting on bqjob_r376b17627e7a7292_00000173a301a06d_1 ... (1s) Current status: DONE
gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT COUNT(*) FROM dataset_1.table_load_sample'
Waiting on bqjob_r2af3719e9fbbecca_00000173a301bf24_1 ... (0s) Current status: DONE
+-----+
| f0_ |
+-----+
| 3 |
+-----+
また、別テーブルから抽出したデータでテーブルデータを洗い替えする場合には、以下のコマンドで実現できます。
bq query \
--destination_table dataset_1.replae_sample \
--replace \
--use_legacy_sql=false \
'SELECT
name,
gender,
count
FROM
dataset_1.table_sample
WHERE
gender = "F"'
replae_sample テーブルデータを、table_sample から SELECT
したデータで書き換えてみます。
gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT COUNT(*) FROM dataset_1.replae_sample'
Waiting on bqjob_r6d7bfdab493c81f7_00000173a30c9c19_1 ... (0s) Current status: DONE
+-------+
| f0_ |
+-------+
| 34073 |
+-------+
gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT COUNT(*) FROM dataset_1.table_sample WHERE gender = "F"'
Waiting on bqjob_r7d28d14f4413436a_00000173a30cbcfb_1 ... (0s) Current status: DONE
+-------+
| f0_ |
+-------+
| 19815 |
+-------+
gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query \
> --destination_table dataset_1.replae_sample \
> --replace \
> --use_legacy_sql=false \
> 'SELECT
> name,
> gender,
> count
> FROM
> dataset_1.table_sample
> WHERE
> gender = "F"'
Waiting on bqjob_r3acb17d66e5f10a2_00000173a30d2082_1 ... (0s) Current status: DONE
+-----------+--------+-------+
| name | gender | count |
+-----------+--------+-------+
| Leona | F | 256 |
| Donna | F | 256 |
| Ariah | F | 256 |
(省略)
| Olea | F | 5 |
+-----------+--------+-------+
gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT COUNT(*) FROM dataset_1.replae_sample'
Waiting on bqjob_r12cbfab7bf2e131_00000173a30d7587_1 ... (0s) Current status: DONE
+-------+
| f0_ |
+-------+
| 19815 |
+-------+
まとめ(所感)
データ型は BigQuery 特有なものも多いので、確認しておくと良いと思います。
また、BigQuery はクエリ課金なので SQL 実行には注意が必要ですし、トランザクション処理をサポートしていないのでエラー発生時にロールバックが必要な処理の場合は実装を検討する必要があります。
BigQuery サービス開始当時は DDL や DML をサポートしておらず SQL もレガシー SQL のみだったのでとっつきにくかったと思いますが、標準 SQL のサポートや DML の回数制限の解除などにより、現在では他のデータベースサービスとほとんど同じ使用感で利用できるのではないかと思います。
各 SQL 構文のサポート有無はどんどん更新されていますし、今はサポートされていない SQL 構文も今後サポートされるようになる可能性もあるのではないかと思います。