BigQueryの少し凝った使い方メモ（テーブル作成・データ更新）

普段Google BigQueryでは簡単な集計SQLしか使っていないのですが、集計SQL以外にも色々便利な機能があります。

ただ、使用頻度が高くないので、いざ使おうとするとマニュアルを探すところから始まるので、自分用に簡単な使い方をまとめておこうと思います。

ここではクエリ結果からテーブルを作成・更新する方法をまとめました。

クエリ結果を、テーブルに保存する

例

クエリ結果でテーブルを作る
実行の度に新しいテーブルに置き換わるので、前回実行時のレコードやカラムは残らない
descriptionを入れる

/* table */
CREATE OR REPLACE TABLE `project_name.dataset_name`.test_table
OPTIONS(description="about")
AS
(
SELECT
  "ABC" AS val01,
  123 AS val02
)
;

/* check */
SELECT
  *
FROM `project_name.dataset_name`.test_table
LIMIT 100

クエリ結果を、分割テーブルの、指定パーティションに上書きする

例

先にパーティションのデータを削除してからクエリ結果を追加する
追加するデータのカラムの並びを明示する

/* delete partition */
DELETE FROM `project_name.dataset_name`.test_table
WHERE DATE(created_at)="2022-01-01"
;

/* add data */
INSERT INTO `project_name.dataset_name`.test_table(
  created_at,
  val_01
)
(
SELECT
  TIMESTAMP("2022-01-01"),
  1
)
;

/* check */
SELECT
  *
FROM `project_name.dataset_name`.test_table
WHERE DATE(created_at)="2022-01-01"
LIMIT 100

クエリ結果を、取り込み時間テーブルの、指定パーティションに上書きする

例

先にパーティションのデータを削除してからクエリ結果を追加する
追加するデータのカラムの並びを明示する
追加するパーティションは_PARTITIONTIMEで指定する

/* delete partition */
DELETE FROM `project_name.dataset_name`.test_table
WHERE DATE(_PARTITIONTIME)="2022-01-01"
;

/* add data */
INSERT INTO `project_name.dataset_name`.test_table(
  _PARTITIONTIME,
  created_at,
  val_01
)
(
SELECT
  TIMESTAMP("2022-01-01"), /* _PARTITIONTIME */
  TIMESTAMP("2022-01-01"),
  1
)
;

/* check */
SELECT
  *
FROM `project_name.dataset_name`.test_table
WHERE DATE(_PARTITIONTIME)="2022-01-01"
LIMIT 100