BigQuery
凄すぎる
テク
クエリの書き方テク
こっち 👉 BigQuery クエリ
常識
SELECT * しない列志向なので、全部なめるのと同じ料金、LIMIT で絞ってもいみはない
SELECT * EXCEPT(hoge) Cmd+Shift+F を押せクエリがフォーマットされる
ゼロ除算
SAFE_DIVIDE(X, Y) 関数がある、ゼロ除算が発生すると NULL になる IFNULL(SAFE_DIVIDE(X, Y), 0) などすると、 NULL の時は 0 にできる(ほかにも
SAFE_ がいろいろある)標準SQLとレガシーSQL
CLI はまだデフォルトがレガシー、
--use_legacy_sql=false することで標準になる DISTINCT の精度 GROUP EACH BY SQL の常識
HAVING は ORDER BY の前というより
ORDER BY , LIMIT はとにかく後ろであるGROUP BY
重複除去
(やってない)
replace-distinct.sqlCREATE OR REPLACE TABLE project.dataset.table
AS SELECT DISTINCT * FROM project.dataset.tableDISTINCT と EXACT_COUNT_DISTINCT
レガシーでは
DISTINCT は概算値, 正確な COUNT(DISTINCT(...)) がほしいなら EXACT_COUNT_DISTINCT を使う標準 SQL では
DISTINCT は正確な値、概算でいい場合は APPROX_COUNT_DISTINCT を使う> レガシー SQL では COUNT(DISTINCT x) は概算の数が返されます。標準 SQL では正確な数が返されます。重複しない値のおおよその数を返す APPROX_COUNT_DISTINCT を使用すると、実行時間が短縮され、必要なリソースも少なくて済みます。
DATETIME と TIMESTAMP
> DATETIME オブジェクトは、タイムゾーンに依存せずにカレンダーや時計に表示される日時を表します。これには年、月、日、時、分、秒、サブ秒が含まれます。絶対的な時刻を表すには、タイムスタンプを使用します。
> TIMESTAMP オブジェクトは、タイムゾーンや夏時間などの慣習に関係なく、マイクロ秒精度の絶対的な時刻を表します。
JOIN した際にカラム名がかぶる
例えば
created などのカラムはよく重複する> Duplicate column names in the result are not supported. Found duplicate(s): ...
同名のカラムを出力すると上のようなエラーが出るので、以下のように
EXCEPT して解決する。join-except.sqlSELECT
table_a.*,
table_b.* EXCEPT(created, updated),
FROM table_a JOIN table_b ON table_a.hoge_id = table_b.idストリーミングバッファにあるデータに影響ある更新系クエリは流せない
> UPDATE or DELETE statement over table ... would affect rows in the streaming buffer, which is not supported
Stackdriver Logging → BigQuery の sink でデータを転送しているとき、BigQuery に到着しているが書き込まれる前のデータはストリーミングバッファ上にある
参照系クエリではストリーミングバッファの内容も読まれる
更新系クエリでストリーミングバッファ上のデータが影響を受ける場合はエラーが出る
ストリーミングバッファは "最も早いエントリ時間" を持ってる
ストリーミングバッファはどのぐらいのタイミングで flush されるのか
もろもろのサイズによるらしい
26MB 50000行/h ぐらいのデータは1時間程度では flush されない、sink 作成直後だからかもしれないが...
テーブルの更新時刻
説明の編集等でも更新される、データが見たいならちゃんと SELECT すること
Firestore export の metadata ファイルは protocol buffer
source: asia-northeast1, destination: asia
BigQuery の export で Multi-Regional な bucket に export したらエラーになる
クラスタ化テーブル
WIP
View の作成の権限
クエリ文字列を入れれたらいいじゃん、というわけではなくて参照先のテーブルにアクセスできる必要がある(内部的にView作成時に参照先テーブルのスキーマを読む, 表示されうるため?)
Terraform でクロスプロジェクトの View を作って管理する、みたいなときに面倒
2022/03/16 のような / 区切りで書かれた日付を load したい
できない
> CSV データまたは JSON データを読み込む場合、DATE 列の値に区切りとしてダッシュ（-）を使用し、YYYY-MM-DD（年-月-日）の形式にする必要があります。
一度
STRING としてロードして PARSE_DATE('%Y/%m/%d', string_date) する矛盾した Timezone 指定はエラーになる
SELECT TIMESTAMP("2022-01-01T00:00:00+09:00", "America/Los_Angels") はエラーが出てくれるので安心ロール
roles/bigquery.jobUser プロジェクトに課金させてジョブを実行できる
事前定義ロールと権限 | BigQuery | Google Cloud だと少ないけどもっと色々出来たイメージだが...
? `roles/bigquery.user` はジョブ実行できるんだっけフォーマッタ
組み込みのやつはツラすぎるのであまり使わない
セッション
対話的に調べたり何個もクエリを実行するときに費用を抑えられる
CREATE TEMP TABLE name AS SELECT ... でデカめのクエリは名前つけておくとよいコンソールから既存のセッションにアタッチできる?
できなさそう
セッション作りつつ変数にセッションIDを入れたり、クエリでセッションID指定できるとよいのだが
temporary table 作るのが主な気持ちだからだけど、temporary table 一覧がほしい
確実にキャッシュを使う
createDisposition: "CREATE_NEVER" で、キャッシュにないときは失敗する[*
ARRAY_AGG(...)[OFFSET(0)] のほうが効率がいい]テーブル定義
カラムを捨てる
Struct の中身は EXCEPT できないので、一部を落とすには
SELECT * REPLACE するselect-replace.sqlSELECT * REPLACE ((SELECT AS STRUCT payload.* EXCEPT (comment)) AS payload)
FROM ...テーブル定義以外でも、STRUCT 中の特定フィールドを除いて SELECT する際に
(SELECT AS STRUCT payload.* EXCEPT (comment)) AS payload は使えるjsonPayload 以下しか興味ないなら
SELECT jsonPayload.* EXCEPT(field) でもいいカラムの型を変える
SELECT * EXCEPT (foo), CAST(foo AS INT64) as foo などで特定のカラムだけ型を変更する、元テーブルを宛先テーブルにしたらよい jsonPayload などネストしたテーブルの場合は SELECT * REPLACE でやるselect-replace-example.sql SELECT * REPLACE (
(
SELECT AS STRUCT jsonPayload.* EXCEPT (event_time, logged_time),
# jsonPayload.event_time を FLOAD などから INT64 へ
CAST(jsonPayload.event_time AS INT64) as event_time,
# jsonPayload.logged_time を INT64(epoch) から TIMESTAMP へ
TIMESTAMP_SECONDS(jsonPayload.event_time) as event_time,
) AS jsonPayload
) FROM ...パーティションを確認
その他 > クエリの設定 > SQL 言語 をレガシーにして以下のクエリ
partition.sqlSELECT partition_id,creation_time,last_modified_time FROM [Dataset.MyTable$__PARTITIONS_SUMMARY__]CLI はデフォルトで
legacy_sql なので CLI からのほうが楽かもpartition.sh$ bq query --project_id={project} 'SELECT partition_id, MSEC_TO_TIMESTAMP(creation_time), MSEC_TO_TIMESTAMP(last_modified_time) FROM [{dataset}.{table}$__PARTITIONS_SUMMARY__]'2022/10/18 もう
INFORMATION_SCHEMA.PARTITIONS があるスキーマの自動検出
TIMESTAMP パーティション
Day だけでなく Month, Year も選べるようになった 2020/9 頃?
CREATE TABLE LIKE
SELECT * FROM dataset.table LIMIT 0 で宛先テーブルを設定すればよいlike-create-table-like.sqlCREATE TABLE mydataset.myclusteredtable
PARTITION BY DATE(timestamp)
CLUSTER BY customer_id
AS SELECT * FROM mydataset.myothertable LIMIT 02022/10/18 今は
CREATE TABLE LIKE ある後からパーティションつける
直接はつけれないけど DDL でスパッといけないかな
Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN.
ARRAY の中身を JOIN する時とかに見る
SELECT ARRAY(SELECT AS STRUCT * FROM UNNEST(array) JOIN other USING(foo)) とかLEFT JOIN の LEFT を消せば通ったりする
CREATE SCHEMA DDL
は、データセットを作るクエリ
外部テーブルを DDL で作る
external_table.sqlCREATE OR REPLACE EXTERNAL TABLE `project.dataset.user` (
id STRING OPTIONS(description="..."),
username STRING OPTIONS(description="..."),
is_pro BOOL OPTIONS(description="...")
)
OPTIONS (
format="GOOGLE_SHEETS",
uris=["https://docs.google.com/spreadsheets/d/..."],
sheet_range="users!A:C",
skip_leading_rows=1
)エラーハンドリング
@@error.message などエラー出力を参照できるDataform で Dataset 作るところなどで見る
create_schema.sqlBEGIN
CREATE SCHEMA IF NOT EXISTS `...` OPTIONS(location="asia-northeast1");
EXCEPTION WHEN ERROR THEN
IF NOT CONTAINS_SUBSTR(@@error.message, "already exists: dataset") AND
NOT CONTAINS_SUBSTR(@@error.message, "too many dataset metadata update operations")
THEN
RAISE USING MESSAGE = @@error.message;
END IF;
END;Cannot use INFORMATION_SCHEMA with a hidden dataset
_ から始まる名前のデータセットはコンソール上に表示されないなど特別扱いされるクエリ自体は普通にできる
ただしデータセットの
INFORMATION_SCHEMA を参照しようとするとエラーになる>Invalid value: Cannot use INFORMATION_SCHEMA with a hidden dataset: ...
Dataform によるこういうクエリは実行できない
hidden_dataset.sqlSELECT ANY_VALUE(table_type)
FROM `myproject._hidden_dataset.INFORMATION_SCHEMA.TABLES`
WHERE table_name = 'hoge'変更履歴
FROM APPENDS(TABLE <table_name>, <since>, <until>) 同一テーブルの更新ログなので replace table した場合の履歴は得られない
列・行のアクセス制御
列にポリシータグを付与、ポリシータグへのアクセス権を管理
行アクセスポリシーはクエリで条件を書く、"行" というより WHERE 句で制限する感じ
CREATE ROW ACCESS POLICY ... GRANT TO ("group:hoo@pokutuna.com") FILTER USING (Col="Value") FILTER USING が真のものに限って結果を得られるGemini 叩く
外部接続 & モデル作る
create_model.sqlCREATE OR REPLACE MODEL `hogehoge.ml.flash001`
REMOTE WITH CONNECTION `projects/hogehoge/locations/asia-northeast1/connections/vertexai`
OPTIONS (ENDPOINT = 'gemini-1.5-flash-001');generate.sqlSELECT
*,
-- 最初の出力取り出す
ml_generate_text_result['candidates'][0]['content']['parts'][0]['text'] AS generated_text,
FROM
ML.GENERATE_TEXT(
MODEL `ml.flash001`,
(SELECT "こんにちは" AS prompt)
)bq コマンド
$ bq ls$ bq mk --location=asia-northeast1 {dataset_name}$ bq show {dataset_name 他いろいろ}$ bq show --format=prettyjson {project}:{dataset}.appengine_googleapis_com_stdout スキーマ見る$ bq load --source_format=NEWLINE_DELIMITED_JSON --autodetect my_dataset.my_table ./path/to/data.json$ bq --project_id=<PROJECT_ID> load --source_format=NEWLINE_DELIMITED_JSON --autodetect --time_partitioning_field=<FIELD> <DATASET>.<TABLE> gs://...スキーマの確認
SELECT * FROM {dataset}.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS WHERE table_name = ... で絞れる$ bq show --schema --format=prettyjson {dataset}.{table}データを別のテーブルにコピーする
あるテーブルのデータを別のテーブルに突っ込みたい
カラムが増えたら追加してほしい
コンソールから書き込み先テーブルを指定する場合、同時にスキーマは変更できない
→ CLI でやるのがよい
REQUIRED を NULLABLE にしてもいいならさらに --schema_update_option=ALLOW_FIELD_RELAXATION を追加確認は
--dry_run でcopy-to-another-table.sqlbq query \
--project_id=<project> \
--destination_table=<dataset>.<to_table> \
--schema_update_option=ALLOW_FIELD_ADDITION \
--use_legacy_sql=false \
--append_table \
--allow_large_results \
'SELECT * FROM <project>.<dataset>.<from_table>'普通に CLI 使ったらいけた #blogged
> FORMAT は DATASTORE_BACKUP です。Cloud Firestore の場合、適切なオプションは Datastore Backup です。Cloud Firestore と Datastore のエクスポート形式は同じです。
$ bq --location=asia-northeast1 mk --dataset pokutuna-playground:import-firebase-backup$ bq --location=asia-northeast1 load --source_format=DATASTORE_BACKUP pokutuna-playground:import_firebase_backup.table1 gs://campenguin-firestore-export/2020-01-07T10:11:58_4616/all_namespaces/kind_queries/all_namespaces_kind_queries.export_metadata調べる
Stackdriver Logging の sink は
--schema_update_option= なに?テーブルのクラスタ
リージョンを横断した JOIN とかするとどうなっちゃうの？
できない
GCP のカレントプロジェクトのリージョンの影響がある? なんか実行できないことがある気がする
クエリの設定でリージョン変えれば良い?
書き込み設定
"テーブルに追加する" は
bq load --append_table , "テーブルを上書きする" は --replace かな?
取り込み時間分割テーブル
承認済みビュー
一部のデータを社外に共有する際などに、承認済みビューを作ってそれを割り当てる
CREATE TABLE LIKE ... みたいなのやりたい
select_schemaSELECT STRING_AGG(CONCAT(column_name, ", ", data_type), "\n")
FROM `{dataset}.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS`
WHERE table_name = {table}
AND field_path NOT LIKE "%.%"みたいな感じでスキーマ文字列作れそうなので、これを
CREATE TABLE に渡せないか(bq show & bq mk のほうが簡単とはいえ)
node client で結果は使わずジョブの終了を待つ
wait.tsawait job.getQueryResults({maxResults: 0});Spreadsheet への federated query で Drive credentials エラー
> Permission denied while getting Drive credentials.
読み取るサービスアカウントは対象のシートへの閲覧権限はあるがうまくいかない
アクセススコープに
https://www.googleapis.com/auth/drive がなかったこれ impersonate しているところで出ていた
普通の ADC で、ユーザーの認証なら
https://www.googleapis.com/auth/drive のスコープなくても Spreadsheet にクエリできる気がする、どうだっけ? --enable-gdrive-access でなくても