概要

GoogleAnalyticsからBigQueryに連携しているデータを定期的に集計してGCSに出力しようとした。

スケジュールされたクエリでEXPORT DATAするだけで簡単かと思いきや、変なところで詰まったので備忘録的に残す。

結論だけ先に

①EXPORT DATAではmeta tablesにアクセスできない（＝_TABLE_SUFFIXが使えない）のでジョブを分割する

②（2021/05/12現在）EXPORT DATAクエリをスケジュールされたクエリに設定する際は、クエリのタブを新規作成してSQLを貼り付けしてから設定する。

バグっぽい挙動なのでそのうち修正されると思われる。

①EXPORT DATAではmeta tablesにアクセスできない

EXPORT DATAにて_TABLE_SUFFIXを使ったクエリを流すとエラーになる。
GoogleAnalyticsとBigQueryを連携している場合、よく書くクエリだと思われる。

SELECT *
FROM `project-name.dataset_name.event_*`
WHERE
_TABLE_SUFFIX = （日付条件）

エラー内容を読むと、meta tablesにアクセスできないとのこと。
_TABLE_SUFFIXが使えないことになる。

"EXPORT DATA statement cannot reference meta tables in the queries." 

解決方法としては、ジョブを分割する方法があるようだ。
1つ目のジョブで、_TABLE_SUFFIXを利用して集計してBigQueryのテーブルに書き出す。
2つ目のジョブで、EXPORT DATAで当該テーブルを書き出す。

自分のケースではジョブの実行時間が将来に渡ってある程度推測できるものだったので、2つのジョブを時間差で実行すれば良かった。
厳密に1つ目のジョブが完了してから2つ目を実行するのは、現状のBigQueryのスケジュールされたクエリ機能では難しそう。

（参考文献）
https://stackoverflow.com/questions/67397911/export-data-in-standard-sql-bigquery-export-data-statement-cannot-reference-me

②現状のWEB UIではEXPORT DATAクエリを正しくスケジュールされたクエリに設定できないケースがある

EXPORT DATAクエリを書く際にSELECT句をまずテストしてから書く、というのはよくあるフローだと思う。
しかし、その状態でスケジュールされたクエリに設定しようとすると、クエリ結果の書き込み先を聞かれる。
これは必須項目で、入力しないと設定が完了しない。

これを設定してスケジュールされたクエリが実行されるとエラーになる。
「EXPORT DATAクエリではクエリ結果の書き込み先を設定できないよ」と読める。
先程必須入力させたのに。。

Error code 3 : Query error: Cannot set destination table in jobs with EXPORT statement;

どうやらBigQueryのWEB UIの不具合のようで、EXPORT DATAクエリか判定するタイミングが不完全ののようだ。
新規にクエリタブを作成してまっさらなエディタにEXPORT DATA句を貼り付けるとクエリ結果の書き込み先を聞かれなくなり、うまく設定できる。

終わりに

とてもニッチだし細かいことだが同じことで困っている人がいればと思いぱぱっと書きました。
多分、すぐに修正されると思われます。