BigQueryのテーブルに可変の日付prefixを入れる

末尾に日付を入れてシャーディングすることがよくあると思います
クエリ実行時の日付を自動で入れる時のメモ

結論から

• DECLAREで変数を宣言
• 今日の日付を取得する関数の作成
• 宣言した変数にテーブル名と関数をくっつける
• EXECUTE IMMEDIATEで変数を入れたcreate文を実行する

例

DECLARE table_name STRING;
create temporary function getDate() AS (FORMAT_DATE("%Y%m%d",  current_date('Asia/Tokyo')));
set table_name = "{dataset-name}.{target-table-name}_" || getDate();
EXECUTE IMMEDIATE FORMAT('CREATE TABLE %s AS SELECT * FROM {dataset-name}.hoge-table', table_name);

実践

アカウントとproject

まずはloginします

gcloud auth login

存在するproject一覧

gcloud projects list

project設定

gcloud config set project {project id}

GCSアップロード

バケット作成

gsutil mb gs://bucket-for-blog

データは以下とします

# sample.csv
---
a,b,c
1,2,3
4,5,6
7,8,9
---

ファイルをバケット内にコピー

gsutil cp ./sample.csv gs://bucket-for-blog/

BigQuery初期設定

データセット作成

bq mk for-blog

sample.csvをテーブルに入れる

bq load \
--source_format=CSV \
--skip_leading_rows=1 \
--autodetect \
for_blog.sample_20210707 \
gs://bucket-for-blog/sample.csv

前日の日付を入れたテーブルを作成しました

–autodetectによりカラムと型を推定して作成してくれます
※プロダクション環境の場合、指定するスキーマをきちんと定義してください(推奨)

シャーディングテーブル作成

では本題のクエリを実行します

sqlファイルを作成して以下のように書きました

-- sample-query.sql
DECLARE table_name STRING;
create temporary function getDate() AS (FORMAT_DATE("%Y%m%d",  current_date('Asia/Tokyo')));
set table_name = "for_blog.sample_" || getDate();
EXECUTE IMMEDIATE FORMAT('CREATE TABLE %s AS SELECT * FROM for_blog.sample_20210707', table_name);

冒頭で書いたのとほぼ同様ですね
前日テーブルを今日の日付に変えただけで、中身はただのコピーとなります

このクエリを実行します

bq query --use_legacy_sql=false < ./sample-query.sql

prefixをシャーディングとして認識してくれてますね

higashi kunimitsu

• データサイエンス