BigQuery関係のTipsをまとめていきます。
bqコマンドについては こちら に記載してます。

コストについて

こちらを参照するとわかりますが、ストレージコストとクエリコストがあります。
ストレージコストはだいたい想像つくと思うので、ここではクエリコストについて記載します。

認識しておきたいのは以下の３つだと私は思ってます。

1. コストは、アクセスするデータ量（バイト数）で決まる
2. カラム型DBなので、必要なカラムのデータだけを取得する
3. オプティマイザが優秀なので、最終的に利用するテーブル・カラム以外は処理対象にならない

このルールを元に考えると、だいたいコストがどうなるか分かります。
実際にbq query --dry_runでアクセスするデータ量を厳密に確認しつつ以下の表を作りました。

項目	コストへの影響	説明
*	対象テーブルの全カラム分のコストがかかる	*を使うとそのテーブルの全カラムを対象にしてしまう。カラム型DBなので使うカラムだけを指定するとそのカラムだけデータアクセスするのでコストは減る
WHERE句やLIMITでデータを絞る	コストは変わらない（全件取得と一緒）	FROM句で対象のテーブルを指定し、SELECT句やWHERE句などで利用するカラムが決まった時点でアクセスするデータは決まるので、そのあとレコードを絞ってもコストには影響はない
JOIN	JOINで使用するカラム分コストが増える	結合条件のON句やそのあとSELECT句やWHERE句で使用したカラムのデータ量分だけ単純に加算される
UNIONやサブクエリ等で同じテーブルに二回アクセス	コストは変わらない	クエリ内でアクセスしたデータはキャッシュに保管されるようで、同一クエリ内で何回同じテーブルにアクセスしてもコストには影響はない
サブクエリで*で取得した結果から最終的に一部のカラムだけselect	最終的に利用するカラム分だけコストがかかる	例えばWITH fuga AS (SELECT * FROM hoge) SELECT id FROM fugaのようなクエリだとhoge.*分だけコストがかかりそうだが実際にはhoge.id分だけしかコストはかからない。オプティマイザが優秀
集計処理や計算処理	コストは変わらない	どれだけやってもコストには影響しない

大量データのテーブルの集約（GROUP BY）クエリの高速化

1億件以上のレコードを保持する3つのテーブルをJOINしてGROUP BYする以下のようなSQLを頻繁に実行する必要が出てきました。（実際にはもっと複雑ですが...）

SELECT
  m.match_id
  , SUM(ug1.group_rank * u1.user_score) AS first_user_group_score
  , SUM(ug2.group_rank * u2.user_score) AS second_user_group_score
FROM
  mydata.match m
  JOIN mydata.user_group ug1 ON (ug1.user_group_id = m.first_user_group_id)
  JOIN mydata.user_group ug2 ON (ug2.user_group_id = m.second_user_group_id)
  JOIN mydata.user u1 ON (u1.user_group_id = ug1.user_group_id)
  JOIN mydata.user u2 ON (u2.user_group_id = ug2.user_group_id)
GROUP BY
  m.match_id
;

このSQL1時間経っても全く応答が返って来ず、MATCHのところを(SELECT * FROM mydata.match LIMIT 100000)のようなサブクエリに置き換えて最初のテーブルを100万件に絞って実行したところ10分くらいで返ってきましたが、実運用にはとても耐えられないので高速化を検討しました。

クラスタリング

こちら に以下のような記述があります。

クラスタリングは並び順を示すものであるため、クラスタリング列と同じ値を持つ行は、その近くまたは同じブロックに格納されます。これにより、クラスタリング列でグループ化した集約クエリの最適化が可能になります。集約を計算するために、BigQuery ではまず各ブロックから部分的な集約を計算します。次に、これらの部分的な集約をシャッフルおよびマージして、最終的な集約を計算します。通常はクラスタリング列と同じ値を持つ行が一緒にされているため、作成された部分的な集約のサイズはかなり小さくなり、シャッフルする必要のある中間データの量が減少します。これにより、集約クエリのパフォーマンスが向上します。

これをみると、group byに指定している項目でクラスタリングを行えば、ブロック内の部分的な集約のサイズが小さくなり、最終的にシャッフル&マージする対象の中間データ数が減るので高速化できそうです。

クラスタリングの設定はcreate table文の中で行います。以下のような感じです。

CREATE TABLE mydata.match (match_id, xxx, yyy, created_at)
PARTITION BY DATE(created_at)
CLUSTER BY match_id
;

クラスタリングするためにはパーティショニングも合わせて行う必要があるため、partition byも指定しています。

matchテーブルのmatch_id、user_groupテーブルのuser_group_id、userテーブルのuser_idでクラスタリングして、クエリを再実行したところ「100万件で10分」だったのが「100万件で13分」に、、、むむ、、なぜだ、短縮されてないぞ。。。
JOINするとダメとかなのかな。。時間があるときにもう少しシンプルな例で試してみますwww

中間テーブル

上のクラスタリングでは思うような結果が得られませんでしたが、中間テーブルを作って処理することで劇的の処理が早くなることがあります。
上記の例だと、user_group.rankとuser.scoreをあらかじめ掛け合わた中間テーブルを作ると効果的でした。

CREATE OR REPLACE TABLE mydata.tmp_user_group_score
OPTIONS (
    EXPIRATION_TIMESTAMP=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) -- 有効期間1日
)
AS
  ug.user_group_id
  , SUM(ug.group_rank * u.user_score) AS user_group_score
FROM
  mydata.user_group ug
  JOIN mydata.user u ON (u.user_group_id = ug.user_group_id)
GROUP BY
  ug.user_group_id
;

その上で、中間テーブルを使うようにしました。これにより「100万件で10分」だったのが、「全件(数億件)で5分」に大幅改善しました。

SELECT
  m.match_id
  , ugs1.user_group_score as first_user_group_score
  , ugs2.user_group_score as second_user_group_score
FROM
  mydata.match m
  JOIN mydata.tmp_user_group_score ugs1 ON (ugs1.user_group_id = m.first_user_group_id)
  JOIN mydata.tmp_user_group_score ugs2 ON (ugs2.user_group_id = m.second_user_group_id)
;

テーブル定義の変更

列の追加

ALTER TABLE hoge ADD COLUMN fugaのようなSQLでは実行できません。

bq query コマンドを使う場合、以下のように--destination_tableと--replaceで実行します。

bq query --destination_table mydata.hoge --replace \
'SELECT h.*, CAST(null AS INT64) AS added_column FROM mydata.hoge h'

単純にクエリだけで完結させたい場合は、以下のようにします。

CREATE OR REPLACE TABLE mydata.hoge
AS
SELECT
    h.*
    , CAST(null AS INT64) AS added_column
FROM
    mydata.hoge h
;

その他、JSONでスキーマ情報を定義して、列を追加/変更することもできます。こちらを参考にさせていただきました。

# jsonにスキーマ情報を出力する
bq --format=prettyjson show mydata.hoge > hoge.json

# jsonの内容を、fieldsの中身だけにして、追加したいカラムのFIELD情報を追加する
vi hoge.json 
cat hoge.json
> [
>     {
>         "name": "id",
>         "type": "INT64"
>     },
>     {
>         "name": "fuga",
>         "type": "STRING"
>     },
>     (省略)
>     {
>         "name": "added_column",
>         "type": "INT64"
>     },
> ]

# jsonを元にテーブル定義を更新する
bq update mydata.hoge hoge.json

列を削除しようとすると、BigQuery error in update operation: Provided Schema does not match Table myproject:mydata.hoge. Field fuga is missing in new schema のようなエラーが発生して削除できません。少し残念ですが、間違って削除してしまうリスクを考えるとメリットの方が少し大きい気がします。

列の削除

aLTER TABLE hoge DROP COLUMN fugaのようなSQLでは実行できません。

bq query コマンドを使う場合、以下のように--destination_tableと--replaceで実行します。
EXCEPT(fuga)で*からfugaを除外することができます

bq query --destination_table mydata.hoge --replace \
'SELECT * EXCEPT(fuga) FROM mydata.hoge'

単純にクエリだけで完結させたい場合は、以下のようにします。

CREATE OR REPLACE TABLE mydata.hoge
AS
SELECT
  * EXCEPT(fuga)
FROM
  mydata.hoge
;

その他、exportしてから新しいテーブルとしてimportする方法もあって、一番低コストですが、ちょっと手間がかかるのでよほどコストがきになる場合しか使わない気がします。

BigQuery Web UI（コンソール）が便利

Web UIはRedashとかに比べると少しスタイリッシュ感がないですが、慣れると使いやすい気がしてきました。少なくともbq queryで実行するよりは圧倒的に楽です。なお、使用できるショートカットキーはこちらから確認できます。

クエリのバイト数をリアルタイムで確認できる

クエリエディタで入力中のクエリを実行した場合にアクセスするバイト数が、

このクエリを実行すると、337.7 KB が処理されます。

のように、リアルタイムでクエリエディタの右下に表示されます

クエリの選択実行ができる

複数のクエリをクエリエディタに書いておき、そのうち一部を選択して実行ボタンの右側の▼メニューから選択項目を実行をクリックすると、クエリの選択実行ができます。ショートカットキーcmd + eが便利です。1TBで5ドルなので100GBぐらいから少し気にするようにしています。

Tabで単語をオートコンプリートできる

予約語、データセット名、テーブル名、カラム名などを、Tabでオートコンプリートできます。
例えば、hogeテーブルにhoge hでエイリアスを付けた後に、h.まで入力してTabをクリックすると、hogeテーブルに存在する項目がプルダウン表示されます。かなり便利です。

クエリ結果をスプレッドシートやCSV、テーブルに保存できる

エラーメッセージも出ずに保存できない時は、結果にARRAYやSTRUCTが含まれている可能性があります。
これが原因の場合、ARRAY_TO_STRING()やFORMAT()などで文字列に変換すると解決できます、

クエリ結果がブラウザにキャッシュされる

ブラウザにキャッシュされているので、クエリ結果のエリアで、二本指スワイプ（Mac）で前のクエリや次のクエリの結果にSQLを再実行することなく移動することができます。

その他

• 矩形選択ができる（cmd + option + ドラッグ）
• クエリを「名前をつけて保存」できる
• クエリの履歴が参照できて、過去のクエリを参照できる

配列（ARRAY)の利用

BigQueryではarray型を利用することができます。
array型にはstring, int32, structなどarray以外のデータ型を配列として格納することができます。（arrayを使いたい場合は、一旦structで包む必要あり）

配列を利用するケース

基本的には、正規化して別テーブルに持つ方がいいと思っているのですが、

• 特定の用途だけで利用し、普段SQLでアクセスすることが極端に少ない
  • 特に配列で管理するデータを結合キーにでアクセスするようなケースはSQL書くのが大変になってくるので避けたい
• テンポラリテーブルなど生存期間が短いものも
• 頻繁に1対多で管理したい項目が増減する
  • テーブルを管理するコストの方が増えてしまう
• 別テーブルにすることで、テーブルが増えすぎて繁雑
  • 1対多で管理する項目が多すぎる場合、全て別テーブルにしだすとER図に入らなくなる
  • 1対多で管理する項目が頻繁に増減する場合も、テーブルの方がコストがかかる
• SQLアンチパターンのEAVを導入したくない

のようなケースであれば、配列を使ってもいいのかなと思います。

配列の利用方法

配列を使ったテーブルを作成する

SQL文でテーブルを作成する場合は以下のようになります。

CREATE TABLE mydata.hoge (
    id STRING
    , labels ARRAY<STRING>
)
;

スキーマjsonを指定して作成する場合、以下のようにmodeをREPEADTEDにします。

{
    "mode": "REPEATED",
    "name": "labels",
    "type": "STRING"
},

配列にデータを突っ込む

-- 単純にinsert
INSERT INTO mydata.hoge(id, labels) VALUES
    (GENERATE_UUID(), ['aaa1', 'bbb1'])
    , (GENERATE_UUID(), ['aaa2', 'bbb2', 'ccc2'])
;
-- クエリ結果を使ってinsert
iNSERT INTO mydata.hoge(id, labels)
SELECT
    GENERATE_UUID()
    , ARRAY_AGG(DISTINCT label) -- ORDER BYで並べ替えることもできるが、DISTINCTとORDER BYは同時に使えないww
FROM
    mydata.fuga
GROUP BY
    fuga_id
;

配列データを取得する

UNNEST()によって配列を縦持ちの別レコードに分割し、CROSS JOINによって元テーブルと結合することで、元テーブルの１項目であるかのように利用することができます。その際にwith offsetを指定すると順序も取得できます。

SELECT
    id
    , label
    , idx
FROM
    mydata.hoge h
    CROSS JOIN UNNEST(h.labels) AS label WITH OFFSET AS idx
;
-- このケースでは5レコード返ってくる
> id                    label  idx
> asdfasldfbiya8b3fea    aaa1  0
> asdfasldfbiya8b3fea    bbb1  1
> b7bgl3wi4t8fsgrtlew    aaa2  0
> b7bgl3wi4t8fsgrtlew    bbb2  1
> b7bgl3wi4t8fsgrtlew    ccc2  2

また、わざわざUNNESTしなくても、配列の添字で取得することもできます。
ただし、指定した添字のデータが存在しない場合はArray index x is out of bounds (overflow)エラーが発生するため、利用する際は注意が必要です。

SELECT
    id
    , labels[OFFSET(0)] AS offset_0 -- offsetは0オリジン
    , labels[ORDINAL(1)] AS ordinal_1 -- ordinalは1オリジン
FROM
    mydata.hoge
;
-- このケースでは2レコード返ってくる。offset_0とordinal_1は同じ内容
> id                    offset_0  ordinal_1
> asdfasldfbiya8b3fea    aaa1  aaa1
> asdfasldfbiya8b3fea    bbb1  bbb1

構造体（STRUCT）の利用

BigQueryではstruct型を利用することができます。
structは、順序付きフィールドのコンテナで、フィールド値は必須ですがフィールド名は任意です。
フィールドには、arrayやstructも含めいろんなデータ型を定義することができます。

構造体を利用するケース

基本的には、正規化して別テーブルに持つ方がいいと思っているのですが、

• 特定の用途にだけ使い、普段はSQLでアクセスして利用することは非常に少ない
• 別テーブルで管理しだすと、テーブルが増えて繁雑

みたいな事情がある際には、構造体を使ってもいいのかなと思います。

構造体の利用方法

構造体を使ったテーブルを作成する

SQL文でテーブルを作成する場合は以下のようになります。

CREATE TABLE mydata.hoge (
    id STRING
    , log STRUCT<
      key STRING
      , value STRING
    >
)
;

スキーマjsonを指定して作成する場合、以下のようにtypeをRECORDにします。

{
  "type": "RECORD",
  "name": "log",
  "fields": [
      {
        "name": "id",
        "type": "STRING",
        "mode": "REQUIRED"
      },
      {
        "name": "value",
        "type": "STRING",
        "mode": "REQUIRED"
      }
  ]
},

構造体にデータを突っ込む

-- 単純にINSERT
INSERT INTO mydata.hoge(id, log) VALUES
    (GENERATE_UUID(), STRUCT('xxx1' AS id, 'yyy1' AS value))
    , (GENERATE_UUID(), STRUCT('xxx2' AS id, 'yyy2' AS value))
;

-- クエリ結果を使ってinsert
INSERT INTO mydata.hoge(id, log)
SELECT
    GENERATE_UUID()
    , STRUCT(fuga.id AS id, fuga.value AS value)
FROM
    mydata.fuga
;

構造体データを取得する

通常のテーブル項目のように.で構造体の内部のデータにアクセスできる

SELECT
    log.key
    , log.value
FROM
    mydata.hoge
;
-- このケースでは2レコード返ってくる
> key     value
> xxx1    yyy1
> xxx2    yyy2

再帰SQLはサポート対象外

BigQueryでは再帰SQLはサポートされてません（涙）ここに書いてあります。

BigQuery は WITH RECURSIVE をサポートしません。

サロゲートキー

BigQueryにはsequenceのようなものはないので、以下のいずれかの方法をとることになると思われます。

row_number関数を使う

この方法だと連番が振れてとても良いです。

CREATE OR REPLACE mydata.hoge AS
SELECT
    ROW_NUMBER() OVER() AS id
    , f.item1
FROM
    mydata.fuga f
;

ですが、レコード件数が1000万件（？）ぐらいを越えると

Resources exceeded during query execution: The query could not be executed in the allotted memory

というエラーが発生してしまいます。回避するためにoffsetとlimitを使って100万件ずつとか何回かにわけて実行すればいいのですが、正直面倒です。
なので、自分はサロゲートキーにはgenerate_uuid()を使うようにしてます。

generate_uuid関数を使う

一応重複する可能性がありますが、こちらを参照させていただくと10の19乗に1回とかそれぐらいのオーダーでしか重複は発生しないようですので、100億件（10の10乗）程度のデータを扱う場合には重複は気にしなくていいと思います。
ただ、出来上がる文字列は64バイトになるので、ストレージコスト・クエリコストを気にするのであれば、やめたほうがいいかもしれません。

CREATE OR REPLACE mydata.hoge AS
SELECT
    GENERATE_UUID() AS id
    , f.item1
FROM
    mydata.fuga f
;

別プロジェクト間でのテーブルコピー

同一のサービスアカウントで両方のプロジェクトにアクセスできる場合

単純にcreate table as selectをすれば良いです

CREATE OR REPLACE `to-project`.mydataset.hoge AS
SELECT * FROM `from-project`.mydataset.hoge

同一のサービスアカウントで両方のプロジェクトにアクセスできない場合

とても面倒ですが、一旦bq extractしてからbq loadすることになります。しかも、ローカルにextractできないので、一旦GCSを経由する必要があります。
CSV形式を使う場合は、CSVファイルにヘッダー行を含めないようにextractするか、loadする際に--skip_leading_rows=1でヘッダーを含め内容にする必要があります。

# from serviceアカウントを有効に
gcloud auth activate-service-account --key-file {from_service_account_key_json} --project {from_project}
# from projectのGCSにエクスポート
bq extract --destination_format=CSV --compression=GZIP --print_header=false {dataset}.{table} gs://{from_backet}/{some_path}/{table}.csv
# ローカルにダウンロード
gsutil cp gs://{from_backet}/{some_path}/{table}.csv {directory_path}/
# to serviceアカウントを有効に
gcloud auth activate-service-account --key-file {to_service_account_key_json} --project {to_project}
# to projectのGCSにアップロード
gsutil cp {directory_path}/{table}.csv gs://{to_backet}/{some_path}/
# to projectのBigQueryにロード
bq load --source_format=CSV {dataset}.{table} gs://{to_backet}/{some_path}/{table}.csv

※2019/12/12現在、ARVO形式でextract -> loadしようとすると、Field hoge_column has changed mode from REQUIRED to NULLABLEというエラーが出るものの回避方法がわからず、断念しました。

データロード

テーブル作成とデータロードを一括で行う

最後にスキーマ情報を書くと、テーブル作成までやってくれます。
data loadコマンドのオプションはこちらを参照ください。

bq load\
  --replace\
  --skip_leading_rows=1\
  --source_format=CSV\
  --null_marker="<NULL>"\
  mydata.hoge\
  ~/Downloads/hoge.csv\
  "column1: STRING, column2: STRING"

テーブル作成とデータロードを別々に行う

テーブルを作成して、bq loadで一括ロードします。
テンポラリテーブルを作る時は、expirationの指定がこちらの方が楽なので、こちらを使っています。

# テーブル作成
bq query "
CREATE TABLE mydata.hoge (item1 STRING, item2 STRING)
OPTIONS (EXPIRATION_TIMESTAMP=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 3 DAY))
"

# データロード
bq load\
  --replace\
  --skip_leading_rows=1\
  --source_format=CSV\
  --null_marker="<NULL>"\
  mydata.hoge\
  ~/Downloads/hoge.csv

直近7日以内の過去データを復元する

for system time as of timestamp expression を使うと、直近7日以内の過去データにアクセスできます。

SELECT
    *
FROM
    mydata.hoge h
        FOR SYSTEM TIME AS OF TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -3 DAY)  -- 3日前
;

あくまで、tableを修飾するオプションなので、複数テーブルに対して個別のタイミングを指定することもできます。

SELECT
    *
FROM
    mydata.hoge h
        FOR SYSTEM TIME AS OF TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -3 DAY)  -- 3日前
    JOIN mydata.fuga f
        FOR SYSTEM TIME AS OF TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -1 DAY)  -- 1日前
        ON (f.hoge_id = h.hoge_id)
;

過去の状態を復元したい場合は、CREATE OR REPLACE TABLE してあげれば良いです。

CREATE OR REPLACE TABLE mydata.hoge
AS 
SELECT
    *
FROM
    mydata.hoge h
        FOR SYSTEM TIME AS OF TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -3 DAY)
;

累計を計算する

window関数の(SUM)を利用して実装可能です。rows betweenで合計する範囲を指定できるのですが、fromの方にunbounded precedingを指定して、toの方にcurrent rowを指定すると良いです。
precedingは先行するという意味なので、例えば5 precendingだと現在のレコードより5つ前のレコードを意味します。unboundedは限界がないという意味なので、限界なく先行するということになり、先頭レコードということになります。
current rowは言葉通り現在のレコードです。ここでは使って無いですが5 followingのような指定もできて、現在のレコードより5つ後のレコードを意味します。

SELECT
    id
    , SUM(price) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_price
FROM
    mydata.hoge

スクリプトの利用

2020年3月にBigQuery スクリプトがリリースされたようですね。
大抵のことはSQLでなんとかなるし、ある特定のレコードや値に対する複雑な処理であれば、Functionで実現できますので、使いどころが難しいという印象です。
LOOPしながらSQLを発行したい場合ぐらいしか使い道は思いつきませんでした。1SQLで実行するとmemoryがoverflowしたので、頭1文字（イニシャル）毎にグルーピングしてLOOPしながらSQLを分割実行するような時に使ってみたので、ちょっと簡略化して載せておきます。

-- 変数宣言
DECLARE initials ARRAY<STRING>;
DECLARE initial STRING;
DECLARE i INT64 DEFAULT 0;

-- SQLの結果を変数に代入
SET initials = (
  SELECT ARRAY_AGG(DISTINCT SUBSTR(name, 0, 1)) FROM mydataset.members
);

-- 配列件数分LOOPしながらSQLを発行
WHILE i < ARRAY_LENGTH(initials) DO
    SET initial = initials[OFFSET(i)]; -- 配列から0オリジンでindexを指定して値を取り出す
    SELECT * FROM mydataset.members WHERE SUBSTR(name, 0, 1) = initial; -- initialを使った何かの処理
    SET i = i + 1;
END WHILE;

いくつか発生したエラーを載せておきます。

• Variable declarations are allowed only at the start of a block or script
  • 変数宣言はスクリプトもしくはブロックの最初で行う必要あり
• Query error: Cannot coerce expression ( SELECT ....) to type ARRAY
  • 配列に配列以外の値を設定しようとしているため、配列に設定する場合はARRAY_AGGする必要あり

また、変数の最大サイズは 1 MB で、スクリプトで使用されるすべての変数の最大サイズは 10 MB です。とのことなので、巨大なテーブルを変数に突っ込んで処理をするようなことはできません。

テーブルのメタ情報

メタ情報は__TABLES__というテーブルに入ってます。
登録内容は項目名からすぐに判断つくと思いますが、typeだけ説明すると1がtableで2がviewです。

select * from mydataset.__TABLES__;

+------------+------------+---------------+---------------+--------------------+-----------+------------+------+
| project_id | dataset_id |   table_id    | creation_time | last_modified_time | row_count | size_bytes | type |
+------------+------------+---------------+---------------+--------------------+-----------+------------+------+
| myproject  | mydataset  | hoge          | 1602669671355 |      1603262397431 |      1451 |     453205 |    1 |
| myproject  | mydataset  | fuga          | 1602669672399 |      1603262400529 |      1195 |   29768175 |    1 |
| myproject  | mydataset  | piyo_veiw     | 1602669667583 |      1603261487231 |        16 |       1616 |    2 |
+------------+------------+---------------+---------------+--------------------+-----------+------------+------+