RedshiftからBigQueryへのクエリ変換でハマったポイント

はじめに

分析環境がRedshiftからBigQueryへ移行することに伴い、従来のクエリの書き換えをすることになりました。簡単に終わるだろう思っていたら、いくつかハマったポイントがあったので共有します。

ハマったこと

データ型が異なる比較はBigQueryが厳格

Redshiftでは、異なるデータ型を比較することがかなり許容されていたが、BQでは厳格。例えば、TIMESTAMP、DATETIME、DATEの比較はできない。
そもそも異なるデータ型で比較するのが気持ち悪いので、BQのほうが処理的には良いと思う。（Redshiftは良くも悪くも、異なるデータ型をよしなに比較してくれている）

-- Redshift 文字列とdateを比較してもエラーは出ない
select substring(cast(date('2021-12-01') as VARCHAR), 1, 10) >= dateadd(day, -365, date('2021-12-01'))
-- アウトプット true

-- BigQuery 異なるデータ型は比較することができない
select substring(cast(date('2021-12-01') as string), 1, 10) >= date_add(date('2021-12-01 00:00:00'), interval - 365 day)
/* エラーメッセージ
No matching signature for operator >= for argument types: STRING, DATE. Supported signature: ANY >= ANY at [4:1]
*/

DATE_TRUNC関数で週の最初の曜日が異なる

デフォルトだと、WEEKの開始日が以下のように異なる。
Redshift: 月曜日
BigQuery: 日曜日
もしBigQueryで月曜開始にしたい場合は、DATE_TRUNC(date, WEEK(MONDAY)) とすれば挙動が揃う。

また戻り値も異なるので注意。
Redshift: TIMESTAMP（公式ドキュメント）
BigQuery: DATE （公式ドキュメント）

-- Redshift 
select DATE_TRUNC('week', date('2021-12-01'))
-- アウトプット 2021-11-29 00:00:00.000000

-- BigQuery
select DATE_TRUNC('2021-12-01', week)
-- アウトプット 2021-11-28

-- 参考：第2引数を変えると任意の曜日を開始日にできる
select DATE_TRUNC('2021-12-01', week(monday))
-- アウトプット 2021-11-29

特定の日時でのユーザーの年齢を算出するクエリ

RedshiftでMONTHS_BETWEEN関数を使用していたが、BigQueryにはMONTHS_BETWEENに相当する関数が無いので、以下のように修正。

-- Redash
select TRUNC(MONTHS_BETWEEN(date('2022-12-01'), date('2021-12-01')) / 12) as age

-- BigQuery 年齢を出すクエリは色々あるが、汎用的なものを採用
select TRUNC((CAST(FORMAT_DATE('%Y%m%d', date('2022-12-01')) AS INT64) - CAST(FORMAT_DATE('%Y%m%d',date('2021-12-01')) AS INT64)) / 10000) as age

緯度経度を使用した2地点間の距離計算の結果が微妙にずれる (BigQueryが世界測地系 WGS84での距離計算には未対応)

Redshiftでは2地点間の距離をより地球上の距離に近く（世界測地系　WGS84を使用して）計算できるが、Bigqueryでは現在、完全な球体の距離でしか計測できないらしい。

公式ドキュメントからの抜粋

任意指定の use_spheroid パラメータは、この関数で距離が測定される方法を決定します。use_spheroid が FALSE の場合、完全な球体の表面上の距離が測定されます。
use_spheroid パラメータで現在サポートされている値は FALSE だけです。use_spheroid のデフォルト値は FALSE です。

-- 東京駅と神田駅の距離を計算してみる。

-- Redshift デフォルトでWGS84を使った距離を計算できる
select ST_DistanceSphere(st_point(139.7671, 35.6812), st_point(139.7709, 35.6918)) / 1000 as distance_in_km
-- アウトプット 1.2276163950893566

-- BQ　WGS84には対応しておらず、完全な球体の距離でしか計算できない
select ST_Distance(st_geogpoint(139.7671, 35.6812), st_geogpoint(139.7709, 35.6918)) / 1000 distance_in_km
-- アウトプット 1.227616631823407