はじめに
私が所属する会社ではビックデータのプラットフォームに 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 書くなら必須の技術だと思います。