どーも、データアナリティクス事業本部コンサルティングチームのsutoです。
今回はそれに関連してBigQuery Migration ServiceのSQL変換機能を使って、関数の仕様の違いなどでRedshiftのクエリをそのまま使えない場合にどのように変換されるのか・どのように書き換えれば良いかをいくつか例として紹介したいと思います。
BigQuery Migration ServiceのSQL変換機能については、以下の記事が参考になります。
NVL関数
- 関数内には2つ以上の引数を指定でき、一連の式の中で、Null 以外の最初の式の値を返します
- Redshiftで使うことができるNVL関数は、BigQueryでは使えません
- どちらでも使用可能なCOALESCE関数で記述すればOKです
- 関数内の引数が2つだけの場合、BigQueryではIFNULL関数でも代用可能です
- ※逆にRedshiftではIFNULL関数は使用できません
# Redshift
SELECT
NVL(f.user_id, t.account_id) AS client_id
FROM
test_table AS t
LEFT JOIN test_table2 AS f ON (t.master_id = f.master_id)
;
# BigQuery
SELECT
coalesce(f.user_id, t.account_id) AS client_id
FROM
test_table AS t
LEFT OUTER JOIN test_table2 AS f ON t.master_id = f.master_id
;
NVL2関数
- 指定された式の結果が NULL か NOT NULL かに基づいて、2 つの値のいずれかを返します
- BigQueryではNVL2関数は使用できないのでCASEを使って条件式に書き直すこととなります
# Redshift
SELECT
(firstname + ' ' + lastname) as name,
NVL2(house_phone, house_phone, mobile_phone) AS contact_info
FROM
users
;
# BigQuery
SELECT
concat(users.firstname, ' ', users.lastname) AS name,
CASE WHEN users.house_phone IS NOT NULL THEN users.house_phone ELSE users.mobile_phone END AS contact_info
FROM
users
;
DEOCDE関数
- 等価条件の結果に応じて、特定の値を別の特定の値またはデフォルト値で置換します
- BigQueryではDECODE関数は使用できないのでCASEを使って条件式に書き直すこととなります
# Redshift
SELECT
decode(user_type, 1, client_id, 0) AS client_id
FROM test_table
;
# BigQuery
SELECT
CASE
test_table.user_type
WHEN 1 THEN test_table.client_id
ELSE 0
END AS client_id
FROM
test_table
;
注意点
- RedshiftのDECODE関数はデフォルト値を指定しない場合NULLを返します
- BigQueryのCASE文の仕様でもELSEがない場合NULLを返す仕様なので問題ありませんが、NULLが格納されることが好ましくないカラムならばデフォルト値を指定しましょう
日付・時刻関数
- 日付や時刻に関わる関数は表記に違いがあります
# Redshift
# 現在時刻(UTC)
SELECT SYSDATE
# 現在時刻(JST)
SELECT CONVERT_TIMEZONE('Asia/Tokyo', SYSDATE)
# N日前の日付
SELECT TRUNC(DATEADD(DAY, -1, TRUNC(CONVERT_TIMEZONE('Asia/Tokyo', SYSDATE))))
# 2つの日付の差
SELECT DATEDIFF(DAY, '2018-01-01', '2018-01-28')
# BigQuery
# 現在時刻(UTC)
SELECT CURRENT_DATETIME
# 現在時刻(JST)
SELECT CURRENT_DATETIME('Asia/Tokyo')
# N日前の日付
SELECT DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 DAY)
# 2つの日付の差
SELECT DATE_DIFF('2018-01-28', '2018-01-01', DAY)
MEDIAN関数
- 値の範囲の中央値を計算します。範囲の Null 値は無視されます
- BigQueryではMEDIAN関数はないため、代わりにPERCENTILE_CONT関数を使って表現できます
# Redshift
SELECT
MEDIAN(score) AS score_median
FROM test_table
;
# BigQuery
SELECT
PERCENTILE_CONT(score, 0.5) OVER() AS score_median
FROM test_table
limit 1
;
- また、BigQuery Migration Serviceで変換してみた際は以下のようなSQLとなりました
# BigQuery
SELECT
bqutil.fn.cw_array_median(array_agg(test_table.score)) AS score_median
FROM
test_table
;
- 上記はBigQueryのコミュニティ提供のユーザー関数を利用したかたちに変換したようです
- README:
- 参考ブログ:
- 一方でAPPROX_QUANTILES関数でも同じ結果が得られそうですが、以下テーブルのようなレコード数が偶数だと違った結果となります
user_id | score |
---|---|
1 | 3 |
2 | 10 |
3 | 12 |
4 | 30 |
# BigQuery
SELECT
PERCENTILE_CONT(score, 0.5) OVER() AS score_median
FROM test_table
limit 1
;
→ 11.0
# BigQuery
SELECT
APPROX_QUANTILES(score, 2)[OFFSET(1)] AS score_median
FROM test_table
limit 1
;
→ 10.0
- このように近似集合関数であるAPPROX_QUANTILES関数だと中央の2つのデータの平均を計算してくれないので注意が必要です
- (BigQuery Migration Serviceを使った際、Redshiftの
PERCENTILE_CONT
関数をAPPROX_QUANTILES
関数に変換することがあったため補足として記載)
- (BigQuery Migration Serviceを使った際、Redshiftの
GREATEST・LEAST関数
- 任意の数の式のリストから最大値・最小値を返します
- この関数は以下のようにRedshiftとBigQueryで返り値が異なります
- Redshift:引数のリスト内にNULLがあった場合、NULLは無視され、NULL以外の値のリストから返り値が決まる
- BigQuery:引数のリスト内にNULLがあった場合、返り値はNULLとなる
- 以前書いたこちらのブログが参考になります
LAG・LEAD関数
- パーティションの現在の行より上(前)・下(後)の指定されたオフセットの行の値を返します
- どちらでも利用可能な関数でオプションは”offset”が共通しています
- 違いがあるオプションは以下のとおりとなります
- Redshift:IGNORE NULLS(オフセット先の値がNULLの場合スキップ) | RESPECT NULLS(オフセット先の値がNULLの場合そのままNULLを返す)
- BigQuery:default_expression(オフセットのウィンドウ フレームに行がない場合に使用される値で、指定しない場合NULLを返す)
- 問題となるのはRedshift側のオプション”IGNORE NULLS”の動きをBigQueryで表現したい場合です。BigQueryでは「オフセット先の値がNULLの場合、その行をスキップ」する動作を実現するためにはSQL文を工夫する必要があります
- LAG関数を例にBigQuery Migration Serviceで変換してみると以下のような結果を得られました
# Redshift
SELECT
LAG(t.times) IGNORE NULLS OVER(PARTITION BY t.user_id, t.user_type ORDER BY t.client_id) AS lag_auto_times
FROM test_table AS t
# BigQuery
SELECT
LAST_VALUE(t.times IGNORE NULLS) OVER (PARTITION BY t.user_id, t.user_type ORDER BY t.client_id NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS lag_auto_times
FROM
test_table AS t
;
- LAST_VALUE関数はウィンドウフレームの最後の行に関する式の値を返します
- LAST_VALUE関数はBigQueryでも
IGNORE NULLS
が使えます - また、
ORDER BY t.client_id NULLS LAST
でNULL値はソート時に最後に配置されます - OVER句における
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
は、ウィンドウフレームの範囲を指定します。現在の行の直前の行までの範囲を含みます - つまり、れぞれのパーティション内で、現在の行の直前の行までの範囲で、times列の最後の非NULL値を返します
- LAST_VALUE関数はBigQueryでも
- ちなみにLEAD関数の場合でも同じく
IGNORE NULLS
を表現するために以下のように書き換えます
# Redshift
SELECT
LEAD(t.times) IGNORE NULLS OVER(PARTITION BY t.user_id, t.user_type ORDER BY t.client_id) AS lag_auto_times
FROM test_table AS t
# BigQuery
SELECT
FIRST_VALUE(t.times IGNORE NULLS) OVER (PARTITION BY t.user_id, t.user_type ORDER BY t.client_id NULLS LAST ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS lag_auto_times
FROM
test_table AS t
;
ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
でウィンドウフレームの範囲として現在の行の次の行から末尾の行までの範囲を指定しています
RATIO_TO_REPORT関数
- ウィンドウまたはパーティションの値の合計に対する、ある値の比率を計算する関数で、Redshiftでは標準で使えます
- ただし、BigQueryでは利用できないので純粋に演算で表現する必要があります
- ※このRATIO_TO_REPORT関数についてはBigQuery Migration Serviceではエラーとなって変換できませんでした
# Redshift
SELECT
sellerid,
qty,
RATIO_TO_REPORT(qty) OVER (PARTITION BY sellerid) AS ratio_to_report_qty
FROM
sales
;
# BigQuery
SELECT
sellerid,
qty,
DIV(qty, SUM(qty)) OVER (PARTITION BY sellerid) AS ratio_to_report_qty
FROM
sales
;
このようにBigQuery Migration ServiceではだいたいのSQLはうまく変換してくれましたが、たまに変換できなかった関数などもありましたので、とくにウィンドウ関数については各々のドキュメントを比較しながら最終チェックをするのが良さそうです。