BigQuery

https://gyazo.com/c32baba970bf9c8175cb295eb9043b5f

凄すぎる

テク

クエリの書き方テク

常識

列志向なので、全部なめるのと同じ料金、LIMIT で絞ってもいみはない

SELECT * EXCEPT(hoge)

クエリがフォーマットされる

ゼロ除算

標準SQLとレガシーSQL

GROUP EACH BY

SQL の常識

GROUP BY

重複除去

(やってない)

code:replace-distinct.sql

CREATE OR REPLACE TABLE project.dataset.table

AS SELECT DISTINCT * FROM project.dataset.table

DISTINCT と EXACT_COUNT_DISTINCT

レガシー SQL では COUNT(DISTINCT x) は概算の数が返されます。標準 SQL では正確な数が返されます。重複しない値のおおよその数を返す APPROX_COUNT_DISTINCT を使用すると、実行時間が短縮され、必要なリソースも少なくて済みます。

DATETIME と TIMESTAMP

DATETIME オブジェクトは、タイムゾーンに依存せずにカレンダーや時計に表示される日時を表します。これには年、月、日、時、分、秒、サブ秒が含まれます。絶対的な時刻を表すには、タイムスタンプを使用します。

TIMESTAMP オブジェクトは、タイムゾーンや夏時間などの慣習に関係なく、マイクロ秒精度の絶対的な時刻を表します。

JOIN した際にカラム名がかぶる

Duplicate column names in the result are not supported. Found duplicate(s): ...

code:join-except.sql

SELECT

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

参照系クエリではストリーミングバッファの内容も読まれる

更新系クエリでストリーミングバッファ上のデータが影響を受ける場合はエラーが出る

ストリーミングバッファは "最も早いエントリ時間" を持ってる

ストリーミングバッファはどのぐらいのタイミングで 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（年-月-日）の形式にする必要があります。

矛盾した Timezone 指定はエラーになる

ロール

roles/bigquery.jobUser

プロジェクトに課金させてジョブを実行できる

フォーマッタ

組み込みのやつはツラすぎるのであまり使わない

セッション

対話的に調べたり何個もクエリを実行するときに費用を抑えられる

コンソールから既存のセッションにアタッチできる?

できなさそう

セッション作りつつ変数にセッションIDを入れたり、クエリでセッションID指定できるとよいのだが

temporary table 作るのが主な気持ちだからだけど、temporary table 一覧がほしい

確実にキャッシュを使う

テーブル定義

カラムを捨てる

code:select-replace.sql

SELECT * REPLACE ((SELECT AS STRUCT payload.* EXCEPT (comment)) AS payload)

FROM ...

テーブル定義以外でも、STRUCT 中の特定フィールドを除いて SELECT する際に

カラムの型を変える

code: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 言語 をレガシーにして以下のクエリ

code:partition.sql

code:partition.sh

スキーマの自動検出

TIMESTAMP パーティション

CREATE TABLE LIKE

code:like-create-table-like.sql

CREATE TABLE mydataset.myclusteredtable

PARTITION BY DATE(timestamp)

CLUSTER BY customer_id

AS SELECT * FROM mydataset.myothertable LIMIT 0

後からパーティションつける

直接はつけれないけど 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 する時とかに見る

LEFT JOIN の LEFT を消せば通ったりする

CREATE SCHEMA DDL

は、データセットを作るクエリ

外部テーブルを DDL で作る

code:external_table.sql

id STRING OPTIONS(description="..."),

username STRING OPTIONS(description="..."),

is_pro BOOL OPTIONS(description="...")

)

OPTIONS (

format="GOOGLE_SHEETS",

sheet_range="users!A:C",

skip_leading_rows=1

)

エラーハンドリング

code:create_schema.sql

BEGIN

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

クエリ自体は普通にできる

Invalid value: Cannot use INFORMATION_SCHEMA with a hidden dataset: ...

code:hidden_dataset.sql

SELECT ANY_VALUE(table_type)

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")

Gemini 叩く

外部接続 & モデル作る

code:create_model.sql

OPTIONS (ENDPOINT = 'gemini-1.5-flash-001');

code:generate.sql

SELECT

*,

-- 最初の出力取り出す

FROM

ML.GENERATE_TEXT(

(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

$ bq show --schema --format=prettyjson {dataset}.{table}

データを別のテーブルにコピーする

あるテーブルのデータを別のテーブルに突っ込みたい

カラムが増えたら追加してほしい

コンソールから書き込み先テーブルを指定する場合、同時にスキーマは変更できない

→ CLI でやるのがよい

code:copy-to-another-table.sql

bq 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>'

$ 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

調べる

テーブルのクラスタ

リージョンを横断した JOIN とかするとどうなっちゃうの？

できない

GCP のカレントプロジェクトのリージョンの影響がある? なんか実行できないことがある気がする

クエリの設定でリージョン変えれば良い?

書き込み設定

https://gyazo.com/2782532f60b08c8093e6ebbfda487a23

取り込み時間分割テーブル

承認済みビュー

一部のデータを社外に共有する際などに、承認済みビューを作ってそれを割り当てる

code:select_schema

SELECT STRING_AGG(CONCAT(column_name, ", ", data_type), "\n")

WHERE table_name = {table}

AND field_path NOT LIKE "%.%"

(bq show & bq mk のほうが簡単とはいえ)

node client で結果は使わずジョブの終了を待つ

code:wait.ts

await job.getQueryResults({maxResults: 0});

Spreadsheet への federated query で Drive credentials エラー

Permission denied while getting Drive credentials.

読み取るサービスアカウントは対象のシートへの閲覧権限はあるがうまくいかない

これ impersonate しているところで出ていた