はじめに
BigQueryにて、指定期間内の日付別にシャード化された空テーブルをLOOP文で一度に作成するクエリを作成したので、メモ書きとして残しておくことにします。
背景
AWSのS3バケットにエクスポートされるCSVファイルの内容を「Bigquery Data Transfer Service」を利用して、BigQuery内の対応する日付の空テーブルに転送していました。
なお、テーブル名はtableName_yyyymmddの形となります。
その際に、事前にCSVファイルの転送先として未来の日付のBigQueryテーブル（空）を大量に作成する必要があったため、今回のクエリを作成しました。
クエリ
以下のクエリを実行すれば、2024/01/01-02/29までの空テーブルをtableName_yyyymmddの形で一括で作成できます。
DECLARE date DATE DEFAULT '2024-01-01';
DECLARE yyyymmdd INT64;
LOOP
SET yyyymmdd = CAST(FORMAT_DATE("%Y%m%d", date) as INT64);
EXECUTE IMMEDIATE format("""
CREATE TABLE projecctId.datasetName.tableName_%d
(
column_name_1 DATETIME
, column_name_2 STRING
, column_name_3 INTEGER
)
""", yyyymmdd);
IF date >= '2024-02-29' THEN
LEAVE;
END IF;
SET date = date + 1;
END LOOP;
本クエリのポイントは、以下の部分です。
不正な日付形式の文字列はINT64型への変換に失敗するため、テーブル作成のSQL文が実行されず、間接的に日付の妥当性チェックが行われます。
例えば、2024/01/32のような不正な日付のテーブルは作成されません。
SET yyyymmdd = CAST(FORMAT_DATE("%Y%m%d", d) as INT64);
日付の妥当性チェックがなくても、SET date = date + 1;の記述で不正な日付が入力されることはありません。
クエリを実行すると、指定したカラム名とデータ型で構成されたテーブルが作成されました。
Tips
空テーブルを作成するにあたり、AWSのS3バケットにあるCSVファイルの中身を確認してカラム数やデータ型を確認しておく必要がありました。
ただ、対象のS3バケットが他者所有で、CSVファイルの共有に手間がかかりそうだったので「Cyberduck」というツールを利用して効率化を図りました。
Cyberduckを利用すれば、以下の手順で自由にS3バケット内の読み取りが可能になります。
- 他者にS3バケット読み取り用のIAMユーザーを作成する（他者）
- 1に、
AmazonS3FullAccessのポリシーをアタッチする（他者） - シークレットアクセスキー、アクセスキー、バケット名を共有する（他者）
- 3の情報を入力し、Cyberduckでコネクトする（作業者）
クライアントワークを行う方は、参考になるかもしれません。
参考：https://dev.classmethod.jp/articles/cyberduck-to-s3/
おわりに
LOOP文を使えば、効率よく指定期間のテーブルを作成することができました。
また、後から気づいたのですが、S3バケットのCSVファイルをBigQueryテーブル化するよりも「BigQuery Omni」を利用した方がよかったかもしれません。