はじめに

私が所属する会社ではビックデータのプラットフォームに Treasure Data を利用しているのですが、最近そこそこ楽しい JSON Parse の Presto Query を書きました。

単に Parse するだけで無く、要件を満たすために構造を作り変える必要があったので Presto の普及活動も兼ねて、利用した関数などを紹介したいと思います。

WITH

WITH は入れ子になったクエリを平坦化したり、サブクエリを単純化できます。また、 WITH 内ではクエリの結果をチェインすることができます。

Sample Query

WITH
  t1 AS(SELECT 1, '太郎'),
  t2 AS(SELECT * ,'29才' FROM t1),
  t3 AS(SELECT * ,'エンジニア' FROM t2)
SELECT * FROM t3

Query Result

id	name	old	job
1	太郎	29才	エンジニア

JSON_EXTRACT

JSON_EXTRACT はJSONのカラムから特定のフィールド抽出する関数です。
第二引数に指定した JSONPath を評価して結果を JSON で返します。

Sample Query

SELECT 
  CAST(
    JSON_EXTRACT(
      raw_json,
      '$.field_1'
    ) AS VARCHAR
  )  AS field_1,
  CAST(
    JSON_EXTRACT(
      raw_json,
      '$.field_2'
    ) AS INTEGER
  ) AS field_2
FROM
  (
  VALUES JSON_PARSE ('
      { "field_1": "value_1",
        "field_2": 1
      }') ,
  JSON_PARSE ('
      { "field_1": "value_2",
        "field_2": 2
      }') ,
  JSON_PARSE ('
      { "field_1": "value_3",
        "field_2": 3
      }')
  ) AS t(raw_json)

Query Result

field_1	field_2
"value_1"	1
"value_2"	2
"value_3"	3

CAST

ご存知かとは思いますが特定のデータ型を別のデータ型へ変換する関数です。
JSONのオブジェクト型を MAP に変換できるのが便利でした。

Sample Query

SELECT 
   CAST(
      raw_json AS MAP < VARCHAR,
      INTEGER >
    )
FROM
  (
  VALUES JSON_PARSE('
      { "key_1": 1,
        "key_2": 2,
        "key_3": 3
      }')
  ) AS t(raw_json)

Query Result

i_am_map
{"key_3":3,"key_2":2,"key_1":1}

MAP_FILTER

MAP に対し条件を指定し、該当する要素のみにフィルターすることができます。

Sample Query

SELECT 
  MAP_FILTER(
    m, 
    (key, value) -> key <> 'should_not_select'
  ) AS filtered_map
FROM
  (
  VALUES CAST(JSON_PARSE('
      { "key_1": 1,
        "key_2": 2,
        "should_not_select": null
      }') AS MAP < VARCHAR,
      INTEGER >
    )
  ) AS t(m)

Query Result

filtered_map
{"key_2":2,"key_1":1}

CROSS JOIN UNNET

CROSS JOIN UNNET は ARRAY や MAP のフィールドをレコードに展開することができる関数です。

Case of ARRAY

Sample Query

SELECT 
   record
FROM
  (
  VALUES CAST(JSON '[
      { "record_1": "a" },
      { "record_2": "b" },
      { "record_3": "c" }
    ]' AS ARRAY(JSON))
  ) AS t(json_array)
CROSS
JOIN
  UNNEST(json_array) AS t(record)

Query Result

record
{"record_1":"a"}
{"record_2":"b"}
{"record_3":"c"}

Case of MAP

Sample Query

SELECT 
  KEY,
  VALUE
FROM (
  VALUES JSON_PARSE('
      { "key_1": 1,
        "key_2": 2,
        "key_3": 3
      }')
  ) AS t(m) CROSS
JOIN
  UNNEST(
    CAST(
      m AS MAP < VARCHAR,
      INTEGER >
    )
  ) AS m(
    KEY,
    VALUE
  )

Query Result

KEY	VALUE
key_1	1
key_2	2
key_3	3

Window関数

抜粋です。

Window関数はテーブルを区間ごとに集計する機能です。集約関数 (GROUP BY) に似ていますが、Window関数では複数の行がまとめられることはなく行それぞれが返却されます。また、処理中の行以外の行の値を読み取ることも可能です。Window関数は以下の構文を使います。

PARTITION を指定し区間を定め、集計関数(MIN,MAX)と組み合わせて結果を取得します。

Sample Query

下のクエリでは PARTITION に YEAR を指定し年毎の区間を定めています。現在行に対し1レコード前の日付を yesterday として現在行へのカラムとして反映しています。過去日付の行が存在しない場合は現在行の値がとして反映されます。

SELECT 
   * ,
  MIN(today) OVER(
    PARTITION BY YEAR
  ORDER BY
    today ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS yesterday
FROM (
  VALUES
    ('2017', CAST('2017-01-01' AS DATE)),
    ('2017', CAST('2017-01-02' AS DATE)),
    ('2017', CAST('2017-01-03' AS DATE)),
    ('2018', CAST('2018-01-01' AS DATE)),
    ('2018', CAST('2018-01-02' AS DATE)),
    ('2018', CAST('2018-01-03' AS DATE)),
    ('2019', CAST('2019-01-01' AS DATE)),
    ('2019', CAST('2019-01-02' AS DATE)),
    ('2019', CAST('2019-01-03' AS DATE))
  ) AS t(YEAR, today)
ORDER BY
  today

Query Result

YEAR	TODAY	YESTERDAY
2017	2017-01-01	2017-01-01
2017	2017-01-02	2017-01-01
2017	2017-01-03	2017-01-02
2018	2018-01-01	2018-01-01
2018	2018-01-02	2018-01-01
2018	2018-01-03	2018-01-02
2019	2019-01-01	2019-01-01
2019	2019-01-02	2019-01-01
2019	2019-01-03	2019-01-02

上記は単純な例ですが、 PARTITION の組み合わせによって様々な単位での区間を組み立てることができます。

終わりに

想像以上に柔軟にやりたいことができるなーという印象でした。特にWindow関数は初めて触ったのですが楽しかったです。データ分析系の Query 書くなら必須の技術だと思います。

おわり