Secret Ninja Blog

プロダクトマネージャーしてます

PrestoSQL350からTrino423に移行にあたっての新しい構文勉強

EMR6.14.0からTrino 422が採用されています。

トレジャーデータでもPresto(SQL)3xxからTrino423への移行を行う予定です。

マイグレーション担当のPMなので、自分の勉強も兼ねてPresto350からTrino423までの間に新しくサポートされたSQL構文を試してみる。

Trinoを分析用途に使うアナリスト向けのSQL変更点はTDクエリエンジンチームが下記にまとめてくれています。

また、非互換性があるSQL関数は下記にまとまっています。EMRでTrinoを使い始める人にとっても参考になるのではないでしょうか。

EXECUTE IMMEDIATE

OracleやSnowflakeなどでもサポートされているEXECUTE IMMEDIATE構文がサポートされました。

EXECUTE IMMEDIATE — Trino 446 Documentation

(厳密には違いますが、)ざっくりわかりやすくいうと、定義した式の中で?で指定した箇所に変数代入が行える機能です。

例としては下記。

EXECUTE IMMEDIATE
'SELECT * FROM www_access WHERE code = ? and method = ?'
USING 200, 'POST';

これは、SELECT文としては下記に変換されます。

SELECT * FROM www_access WHERE code = 200 and method = 'POST'

アドホックに変数を置き換えて色々したい場合には便利な気がしますね。

Window句

Window関数自体はサポートされていましたが、Window句window_name AS (window_specification)が追加でサポートされました。複数のWindow関数で同じ定義を使う場合に書き方が冗長になるのを防ぐことができるようになります。

例えば、顧客ID毎のステージの最初と最後のステージを取得する下記SQLがあるとします。

SELECT
  id,
  FIRST_VALUE(accountstage__c) OVER (PARTITION BY id ORDER BY time),
  Last_VALUE(accountstage__c) OVER (PARTITION BY id ORDER BY time)
FROM account

この(PARTITION BY id ORDER BY time)の共通項を別途書き出すことができます。

SELECT
  id,
  FIRST_VALUE(accountstage__c) OVER n,
  Last_VALUE(accountstage__c) OVER n
FROM account
WINDOW n as (PARTITION BY backendid__c ORDER BY time)

Window関数を多用するアナリストにとっては便利かもしれませんね。

MATCH_RECOGNIZE

これもOracleやSnowflakeなどでもサポートされているMATCH_RECOGNIZE構文がサポートされました。

これは読み込みデータに対して数値のパターンや文字列の出現順序に柔軟に対応をしてレコードを抽出できるものになります。正規表現大好きな人には嬉しい機能かもしれないですね・・・

Snowflakeのドキュメントにはわかりやすく説明されているので、参考に書くと下記のようになります。

これは会社の株価が下落しているとき、上昇しているとき、それぞれのパターンがいつからいつまででどのくらいのデータポイントが含まれているかというのを抽出することができます。

SELECT * FROM nasdaq
  MATCH_RECOGNIZE(
    PARTITION BY symbol
    ORDER BY time
    MEASURES
      MATCH_NUMBER() AS match_number,
      FIRST(td_time_format(time, 'yyyy-MM-dd')) AS start_date,
      LAST(td_time_format(time, 'yyyy-MM-dd')) AS end_date,
      COUNT(*) AS rows_in_sequence,
      COUNT(row_with_price_decrease.*) AS num_decreases,
      COUNT(row_with_price_increase.*) AS num_increases
    ONE ROW PER MATCH
    AFTER MATCH SKIP TO LAST row_with_price_increase
    PATTERN(row_before_decrease row_with_price_decrease+ row_with_price_increase+)
    DEFINE
      row_with_price_decrease AS close < PREV(close),
      row_with_price_increase AS close > PREV(close)
  )
ORDER BY symbol, match_number;

ただ、こんな構文を大量に使って作られたクエリが発生したらメンテするのに心が折れそうですね・・・

JSON操作

JSON pathを使った文字列に対する新しいJSON関数がでています。

1フィールドにデータがJSONとして保存され、その構造がカオスなケースというのはよくあると思いますが、そういったデータへのアクセスが簡単になりました。

例えば下記のような構造が会った時に、commentがkeyの値を全て取りたいといった場合、

{
    "id" : 1,
    "notes" : [{"type" : 1, "comment" : "foo"}, {"type" : 2, "comment" : null}],
    "comment" : ["bar", "baz"]
}

一つ一つの値を個別に指定しないとうまく取得する方法がありません。

SELECT
json_extract(json, '$.comment')
FROM (
  SELECT
json '{
    "id" : 1,
    "notes" : [{"type" : 1, "comment" : "foo"}, {"type" : 2, "comment" : null}],
    "comment" : ["bar", "baz"]
}' json
)

=> ["bar","baz"]

新しいJSON関数群では下記のように書き換え、

SELECT
json_query(json, 'lax $.comment' WITH ARRAY WRAPPER)
FROM (
  SELECT
'{
    "id" : 1,
    "notes" : [{"type" : 1, "comment" : "foo"}, {"type" : 2, "comment" : null}],
    "comment" : ["bar", "baz"]
}' json
)

==> {["bar","baz"]}

$..commentと指定することで再帰的に全てのcommentにアクセスすることができます。

SELECT
json_query(json, 'lax $..comment' WITH ARRAY WRAPPER)
FROM (
  SELECT
'{
    "id" : 1,
    "notes" : [{"type" : 1, "comment" : "foo"}, {"type" : 2, "comment" : null}],
    "comment" : ["bar", "baz"]
}' json
)

==> [["bar","baz"],"foo",null]

おわり

ユーザ向けの関数以外にも様々な機能改善などが含まれていますが、ユーザ向けの構文として大きく追加されたところとしてはこんなところになっています。