BigQueryのTABLE_SUFFIXの面白い挙動

BigQueryの_TABLE_SUFFIXの絞り込みがどこまで賢く動くのかがわからなかったので、完全に理解しようと思います。また、分割テーブルの話は今回しないです。詳しくはドキュメントを参照してください。

• ワイルドカードテーブル https://cloud.google.com/bigquery/docs/querying-wildcard-tables
• 分割テーブル https://cloud.google.com/bigquery/docs/partitioned-tables

サマリ

結論としては状況によります。JOINの有無と関数を挟むかで変わります（データベースの気持ちになったら確かにという印象）。

• JOINしない場合 => 賢い
• JOINする場合 => 期待したほど賢くない

特に、JOIN時に_TABLE_SUFFIXと比較するカラムは事前に文字列に変換して保存しておきましょう。

Bad

SELECT *
FROM `tmp.log_*` AS log
LEFT JOIN `tmp.not_log_formatted` AS non_log ON log._TABLE_SUFFIX = FORMAT_TIMESTAMP("%Y%m%d", non_log.time) AND log.id = non_log.id
WHERE non_log.formated_time = "20220401"

Good

SELECT *
FROM `tmp.log_*` AS log
LEFT JOIN `tmp.not_log_formatted` AS non_log ON log._TABLE_SUFFIX = non_log.formated_time AND log.id = non_log.id
WHERE non_log.formated_time = "20220401"

diffは

- ON log._TABLE_SUFFIX = FORMAT_TIMESTAMP("%Y%m%d", non_log.time)
+ ON log._TABLE_SUFFIX = non_log.formated_time

こんな形になります。事前に変換しておいたformated_time を使っています。

事前準備

ダミーのテーブルを用意します。今回は以下の形にしました。

それぞれ、100レコードを持っています。idはすべてのテーブルで共通で1~100までの値が入っています。

• tmp.log_*
  • time: その日の0時が入っている。tmp.log_20220401 だったら2022-04-01の0時
• tmp.not_log_formatted
  • time: 2022-04-01 の0時
  • formatted_time: “20220401” が入っている

準備用のスクリプトは以下になります。tmp データセットさえあればコピペで実行できます。

create_tables.sql

-- tmp.prepare_table("tmp.non_log", 100)
-- とすると、100行ある`tmp.non_log` が作成される
-- とすると、`tmp.log_20220401`, `tmp.log_20220402`, ... `20220416` というテーブルが複数作成される。それぞれ100個のレコードを持つ
CREATE OR REPLACE PROCEDURE tmp.create_dummy_table(table_name STRING , table_size INT64, date DATE) BEGIN
  EXECUTE IMMEDIATE FORMAT('CREATE OR REPLACE TABLE %s AS (SELECT id, TIMESTAMP("%t") AS time FROM UNNEST(GENERATE_ARRAY(1, %d)) AS id);', table_name, date, table_size);
END;


-- tmp.prepare_table("tmp.log_", GENERATE_DATE_ARRAY("2022-04-01", "2022-04-16"), 100)
-- とすると、それぞれ100行ある`tmp.log_20220401`, `tmp.log_20220402`, ... `20220416` というテーブルが複数作成される。
CREATE OR REPLACE PROCEDURE tmp.create_dummy_log_table(table_prefix STRING, cal ARRAY<DATE>, table_size INT64) BEGIN
  DECLARE size INT64 DEFAULT ARRAY_LENGTH(cal);
  DECLARE iter INT64 DEFAULT 0;
  LOOP
    IF iter >= size THEN
      LEAVE;
    END IF;
    CALL tmp.create_dummy_table(CONCAT(table_prefix, FORMAT_DATE("%Y%m%d", cal[OFFSET(iter)])), table_size, cal[OFFSET(iter)]);
    SET iter = iter + 1;
  END LOOP;
END;

CREATE OR REPLACE PROCEDURE tmp.convert(source_table STRING, destination_table STRING, time_column_name STRING) BEGIN
  EXECUTE IMMEDIATE FORMAT('CREATE OR REPLACE TABLE %s AS (SELECT *, FORMAT_TIMESTAMP("%%Y%%m%%d", %s) AS formated_%s FROM %s)', destination_table, time_column_name, time_column_name, source_table);
END;

BEGIN
  CALL tmp.create_dummy_table("tmp.not_log", 100, "2022-04-01");
  CALL tmp.convert("tmp.not_log", "tmp.not_log_formatted", "time");
  CALL tmp.create_dummy_log_table("tmp.log_", GENERATE_DATE_ARRAY("2022-04-01", "2022-04-16"), 100);
END;

実験

• レコード数
• ワイルドカードの対象になるテーブル数
• リージョン（今回はUS）

これらは影響しないと仮定する。

また、コンソール上に表示される処理されるバイト列で比較。

実験するものとしては、

• WHERE での絞り込み
• ワイルドカードテーブルを使用していないテーブルとの JOIN とJOIN先の値を利用した絞り込み

この2つについて実験する。

WHERE での絞り込み

• where_1: _TABLE_SUFFIX = "20220401"
• where_2: _TABLE_SUFFIX = CONCAT("20220401")
• where_3: CONCAT(_TABLE_SUFFIX) = "20220401"

ex_where_tmpl.sql

SELECT *
FROM `tmp.log_*` AS log
WHERE <WHERE>

結果としては変化せずでした。すべて1.56KBとなります。賢い！

ワイルドカードテーブルを使用していないテーブルとの JOIN とJOIN先の値を利用した絞り込み

以下の組み合わせで実験します。

• join
  • join_1: log._TABLE_SUFFIX = non_log.formated_time
  • join_2: log._TABLE_SUFFIX = CONCAT(non_log.formated_time)
  • join_3: CONCAT(log._TABLE_SUFFIX) = non_log.formated_time
  • join_4: log._TABLE_SUFFIX = FORMAT_TIMESTAMP("%Y%m%d", non_log.time)
• where
  • where_1: non_log.formated_time = "20220401"
  • where_2: non_log.formated_time = CONCAT("20220401")
  • where_3: CONCAT(non_log.formated_time) = "20220401"

これらの値を以下の実験用のSQLに入れます

ex_where_tmpl.sql

SELECT *
FROM `tmp.log_*` AS log
LEFT JOIN `tmp.not_log_formatted` AS non_log ON <JOIN> AND log.id = non_log.id
WHERE <WHERE>

結果としては以下のようになります。

-	where_1	where_2	where_3
join_1	4.1KB	4.1KB	27.54KB
join_2	27.54KB	27.54KB	27.54KB
join_3	27.54KB	27.54KB	27.54KB
join_4	27.54KB	27.54KB	27.54KB

ちなみにベースラインとして、ワイルドカードを使わない以下の場合

ex_join_simple.sql

SELECT *
FROM `tmp.log_20220401` AS log
LEFT JOIN `tmp.not_log_formatted` AS non_log ON log.id = non_log.id

で、4.1KBだったのでjoin_1とwhere_1, 2の組み合わせは最良のケースとなります。期待していたよりは賢くないです。

まとめ

• やっていいこと
  • JOINする際は絞り込み時に関数を挟んだ値で絞り込んでも良い
  • JOINしない場合は気にする必要がない
• やってはいけないこと
  • 基本的に_TABLE_SUFFIXまわりには関数を挟まない（例: FORMAT_TIMESTAMP ,CONCAT）
  • JOIN時にも_TABLE_SUFFIX と比較対象の値、どちらも関数を挟むことはしてはいけない。クエリから自明に決まる場合でもNG（例: CONNCAT(value))

また、今回のformated_time のように時系列データとJOINする場合は、必ずその_TABLE_SUFFIXと一致する文字列を保存しておくのをおすすめします。保存されていない場合、絞り込まれずに全スキャンしてしまいます。

Lookerなどの可視化ツールで_TABLE_SUFFIXを利用する際などに参考にしてほしいです。