🎉

Cloud Shellで変数を定義してBigQueryにクエリ発行する

2023/12/30に公開

SQL

BigQuery

Cloud Shell

tech

こんにちは、りょうです。Cloud Shellで変数を定義してBigQueryにクエリを発行する方法について躓いた点など含めてまとめます。

シチュエーション

BigQueryに新規で必要になったデータの過去分のテーブルを作成するときに、hogehoge_rawテーブルからhogehoge_20231010テーブル（日付はデータ取得日）にrawテーブルのカラムを変換したレコードをinsertしたい。
上記のような状況でやり方はいくつかあると思いましたが、Cloud Shellのbqコマンドでデータ取得日をループで回して、該当するrawテーブルにクエリ発行するのが一番楽かなと思いました。
1度きりの操作のためスクリプト化するまでもなく、、

前準備

hogehoge_rawテーブルにexec_dateとuidのSTRING型のレコードが登録されています。
rawテーブルのexec_dateをDATE型に変換する処理をしてhogehoge_yyyymmddテーブルに挿入したいと思います。
事前にhogehoge_yyyymmddテーブルは作成済みの状態です。

結論

Cloud Shellから下記コマンドを実行することで、hogehoge_rawテーブルからhogehoge_yyyymmddテーブルへレコードを挿入することができました。

$ for i in 20221015;
do
  TABLE_NAME="[project_id].[dataset_name].hogehoge_${i}"
  bq query --nouse_legacy_sql \
    "INSERT INTO \`${TABLE_NAME}\` (
    SELECT PARSE_DATE('%Y%m%d', exec_date) AS exec_date, uid
    FROM \`[project_id].[dataset_name].hogehoge_raw\`
    )"
done;

TABLE_NAMEを定義し、for文を使用することでテーブル名の異なる複数のテーブルに対して同様の操作を行うことができます。

つまずいた点

当初パラメータを使用してyyyymmdd部分だけ異なる複数テーブルに対してクエリを発行しようとしていました。

$ for i in 20221015;
do
bq query --nouse_legacy_sql \
	 --parameter=param::[project_id].[dataset_name].hogehoge_${i} \
	 'INSERT INTO `@param` (
	 SELECT PARSE_DATE("%Y%m%d", exec_date) AS exec_date, uid
	 FROM `[project_id].[dataset_name].hogehoge_raw`
	 )';
done;

上記を実行すると、下記のように怒られます。

Table "@param" must be qualified with a dataset (e.g. dataset.table)

BigQueryのパラメータ機能でテーブル名を動的に指定することがサポートされていないので、テーブル名の日付部分をfor文で動的に変更しようという考えが間違っていたようです、、

パラメータは主にリテラル値（数値、文字列、日付など）の置き換えに使用され、テーブル名やカラム名のようなSQLの構造的な要素には使用できません。

ChatGPTに教えてもらいました。

まとめ

クエリで動的に変更できるリテラル値はパラメータにしてクエリ発行できる
予約語やテーブル名、カラム名はシェルスクリプトで変数を定義する

上記２点は今回ぼくなりに学びになりました。
どなたかの参考になれば幸いです。最後まで読んでいただきありがとうございました

シチュエーション

前準備

結論

つまずいた点

まとめ

Discussion