結局 LATERAL とは何者なのか?

公開:2020/12/14
更新:2020/12/18
10 min読了の目安(約9000字TECH技術記事

この記事は Snowflake Advent Calendar 2020 の 14 日目です。


こんにちは、Snowflake でサポートエンジニアをやっている @indigo13love です。

Snowflake では Semi-structured Data (半構造化データ / JSON とか Parquet とか) を柔軟に取り扱うための機能として、JSON 内の配列や ARRAY 型の値を行に展開してテーブルとして返すテーブル関数である FLATTEN を用意しています。

https://docs.snowflake.com/ja/sql-reference/functions/flatten.html

この FLATTEN の典型的な使用方法として、LATERAL FLATTEN という書き方が半ばイディオムのように使われています。

https://docs.snowflake.com/ja/user-guide/json-basics-tutorial-flatten.html

上記ドキュメントより抜粋:

LATERAL JOIN および FLATTEN 関数を使用して、グローバルデータを保持しながら、イベントを個々の JSON オブジェクトに分離できます。FLATTEN 各オブジェクトの行を返し、 LATERAL 修飾子はデータをオブジェクトの外部の情報(この例ではデバイスタイプとバージョン)と結合します。この関数は、フラット化されたオブジェクトの値を含む VALUE 列を出力します。

select src:device_type::string
, src:version::string
, value
from
  raw_source
, lateral flatten( input => src:events );

上記のクエリを見るだけでもなんとなく使い方は理解できますが、実際この LATERAL FLATTEN は一体何をしているのか、LATERAL はどういう処理なのかというところを、この記事では解説していきます。

(本記事は所属する組織の公式見解ではなく、個人的なプラクティスの共有になります。)

TL;DR

  • LATERAL は左側の行を使う複数行返すサブクエリやテーブル関数を JOIN したいときに使う
    • 通常の JOIN では左側の行にはアクセスできない
  • LATERAL FLATTEN は左側の行を引数に取った FLATTEN の各行を左側の行に結合している

LATERAL とは何か?

LATERAL は Lateral Join (ラテラル結合) のことで、つまり JOIN の一種になります。

https://docs.snowflake.com/ja/sql-reference/constructs/join-lateral.html

LATERAL は、主に左側の行にアクセスするサブクエリ(相関サブクエリ)を右側に取って、その相関サブクエリの結果を左側の行とそれぞれ結合します。

なんのこっちゃという感じですが、コードっぽく書くとわかりやすいかもしれません。

for row_left in table_left:
    for row_right in subquery(row_left):
        join(row_left, row_right)

つまり、左側のテーブルから 1 行ずつ取り出して、その行を使ってサブクエリを実行し、そのサブクエリの結果行を右側として、左側の行と結合していく、という流れになります。

あれ、これって CROSS JOIN (クロス結合) と同じなんじゃないの、という感じですが、まさに動きとしては CROSS JOIN に近く、左側の行を右側の行数分複製して、右側の行それぞれを結合しています。

大きな違いは、右側の行が左側の行から生成された値であることで、つまり左側の行ごとに結合される右側の行の値が変化することです。

実際にクエリの動きを見てみましょう。

create or replace table t1 (c1 int) as
select * from values (0), (1), (2);

select * from t1,
lateral (select t1.c1+1);

LATERAL の右側になっているサブクエリ (select t1.c1+1) は左側テーブルのカラム t1.c1 にアクセスしているので、相関サブクエリです。この相関サブクエリは t1.c1 より 1 大きい数を 1 行返します。

このクエリの結果は下記のようになります。

C1	T1.C1 + 1
0	1
1	2
2	3

t1.c1 の 1 行目は 0 なので +1 された 1 が、2 行目 は 1 なので +1 された 2 が…という形で結合されています。

1 行と 1 行だとわかりにくいので、右側も複数行にしてみましょう。

create or replace table t1 (c1 int) as
select * from values (0), (1), (2);

create or replace table t2 (c1 int) as
select * from values (3), (4), (5);

select * from t1,
lateral (select t1.c1+t2.c1 from t2);

(select t1.c1+t2.c1 from t2)t1.c1 にアクセスしているので、相関サブクエリです。この相関サブクエリは、t2 に格納されている 3, 4, 5 の 3 行にそれぞれ t1.c1 を足した数を返すので、t1.c1 の各行に対して 3 行ずつ別の値を返します。

このクエリの結果は下記のようになります。

C1	T1.C1+T2.C1
0	3
0	4
0	5
1	4
1	5
1	6
2	5
2	6
2	7

これはまさに CROSS JOIN といった見た目ですが、普通の JOIN (CROSS JOIN, INNER JOIN, OUTER JOIN) では左側の値を右側で使うことができないため、これは CROSS JOININNER JOIN では実現できません。

select * from t1
join (select t1.c1+t2.c1 from t2);
-- SQL compilation error: error line 2 at position 13 invalid identifier 'T1.C1'

つまり「左側の値から演算/展開してあれこれした複数行の結果を右側にくっつけたい!」と思ったときが LATERAL を使うべきタイミングです。ちなみに複数行返すサブクエリを non-scalar subquery と呼びます。

ちなみに複数行ではなく 0〜1 行だけを返すサブクエリ (scalar subquery) の場合、LATERAL は必要なく、そのまま SELECT のカラムリストにサブクエリを書けばよいです。

LATERAL FLATTEN とは何か?

LATERAL がなんとなく何をするのかわかったところで、LATERAL FLATTEN を考えていきましょう。

まず、今までは相関サブクエリの話だけをしてきましたが、LATERAL はテーブル関数も右側に取ることができます。つまり、左側の行の値を引数に取るテーブル関数を右側に持ってくることができるわけです。

下記の LATERAL FLATTEN の例で見ていきましょう。

create or replace table t1 (c1 array) as
select array_construct(1, 2, 3)
union
select array_construct(4, 5, 6)
union
select array_construct(7, 8, 9);

select t1.c1, f.value from t1, lateral flatten(t1.c1) f;

flatten(t1.c1) は、左側の行の値 t1.c1 を引数に取るテーブル関数です。このテーブル関数は、配列である t1.c1 を各要素を格納した行に展開して返します。例えば、[1, 2, 3] であれば 1, 2, 3 の 3 行を(メタデータ付きで)返します。

結果は下記の通りです。

C1	VALUE
[7, 8, 9]	7
[7, 8, 9]	8
[7, 8, 9]	9
[4, 5, 6]	4
[4, 5, 6]	5
[4, 5, 6]	6
[1, 2, 3]	1
[1, 2, 3]	2
[1, 2, 3]	3

相関サブクエリを右側に取っていたときと同じですね。

さらに LATERAL は、それ以前の LATERAL の右側の行にもアクセスすることができます。つまり、配列がネストしているようなオブジェクトを段階的に展開していくことができます。

言葉ではわかりにくいと思うので、下記のクエリで確認していきましょう。

create or replace table t1 (c1 variant) as
select parse_json('{"id": 1, "foo":[{"bar":[1, 2]}, {"bar": [3, 4]}]}')
union
select parse_json('{"id": 2, "foo":[{"bar":[5, 6]}, {"bar": [7, 8]}]}');

上記の JSON オブジェクトは foo というキーの値として JSON オブジェクトを要素として持つ配列を持っており、さらにその JSON オブジェクトは bar というキーの値として数値の配列を持っています。

つまり、LATERAL FLATTENfoo を展開しても、その値はまだ JSON オブジェクトで、最終的な数値を行として取り出すには、さらに bar も展開する必要があります。

これは下記のように LATERAL を重ねるだけで実現できます。

select t1.c1, f1.value, f2.value
from t1,
lateral flatten(t1.c1:foo) f1,
lateral flatten(f1.value:bar) f2;

この場合、1 回目の LATERALbar 2 行と結合され、さらに 2 回目の LATERALbar の中身の配列要素 2 行と結合されるので、2 * 2 * 2 = 8 行が返ります。

t1.c1 f1.value f2.value
{"id": 1, "foo": [{"bar": [1, 2]}, {"bar": [3, 4]}]} {"bar": [1, 2]} 1
{"id": 1, "foo": [{"bar": [1, 2]}, {"bar": [3, 4]}]} {"bar": [1, 2]} 2
{"id": 1, "foo": [{"bar": [1, 2]}, {"bar": [3, 4]}]} {"bar": [3, 4]} 3
{"id": 1, "foo": [{"bar": [1, 2]}, {"bar": [3, 4]}]} {"bar": [3, 4]} 4
{"id": 2, "foo": [{"bar": [5, 6]}, {"bar": [7, 8]}]} {"bar": [5, 6]} 5
{"id": 2, "foo": [{"bar": [5, 6]}, {"bar": [7, 8]}]} {"bar": [5, 6]} 6
{"id": 2, "foo": [{"bar": [5, 6]}, {"bar": [7, 8]}]} {"bar": [7, 8]} 7
{"id": 2, "foo": [{"bar": [5, 6]}, {"bar": [7, 8]}]} {"bar": [7, 8]} 8

右側が相関してなかったらどうなるの?

実は LATERAL は相関していないサブクエリやテーブル関数を右側に取ることができます。

https://docs.snowflake.com/ja/sql-reference/constructs/join-lateral.html

通常、インラインビューは <左側のテーブルの式> の列を参照しますが、必ずしもそうする必要はありません。

この場合、何が起きるかというと普通に ON/USING 句なしの INNER JOIN、つまり CROSS JOIN と同じ動作になります。

create or replace table t1 (c1 int) as
select * from values (0), (1), (2);

create or replace table t2 (c1 int) as
select * from values (3), (4), (5);

select * from t1,
lateral (select c1 from t2);
C1	C1
0	3
0	4
0	5
1	3
1	4
1	5
2	3
2	4
2	5

ただ、ドキュメントにも書いてある通り、あまり意味がないので、基本的には LATERAL は相関する必要があるときにのみ使われます。

LEFT JOIN したいときはどうするの?

Semi-structured Data は、ある程度スキーマが決まっていることが多いものの、本質的にはスキーマレスです。

したがって、あるプロパティが、ある行には存在するが、ある行には存在しないということがあり得ます。

このとき LATERAL FLATTENINNER JOIN なので、あるプロパティを展開しようとして存在しないと「展開後の行が存在しない」という扱いになり、左側の行ごと結果から消えます。

しかし、LATERAL FLATTEN が多段のとき、その前段までは結果に入っていてほしいというケースがあるかもしれません。

例えば、下記の JSON オブジェクトにおいて、最後のオブジェクトだけ bar プロパティを持っていません。

create or replace table t1 (c1 variant) as
select parse_json('{"id": 1, "foo":[{"bar":[1, 2]}, {"bar": [3, 4]}]}')
union
select parse_json('{"id": 2, "foo":[{"bar":[5, 6]}, {"qux": [7, 8]}]}');

ここで、何も考えずに LATERAL FLATTENbar についてシンプルに展開すると {"qux": [7, 8]} は闇に葬られ、無かったことになります。

t1.c1 f1.value f2.value
{"id": 1, "foo": [{"bar": [1, 2]}, {"bar": [3, 4]}]} {"bar": [1, 2]} 1
{"id": 1, "foo": [{"bar": [1, 2]}, {"bar": [3, 4]}]} {"bar": [1, 2]} 2
{"id": 1, "foo": [{"bar": [1, 2]}, {"bar": [3, 4]}]} {"bar": [3, 4]} 3
{"id": 1, "foo": [{"bar": [1, 2]}, {"bar": [3, 4]}]} {"bar": [3, 4]} 4
{"id": 2, "foo": [{"bar": [5, 6]}, {"qux": [7, 8]}]} {"bar": [5, 6]} 5
{"id": 2, "foo": [{"bar": [5, 6]}, {"qux": [7, 8]}]} {"bar": [5, 6]} 6

ここで、{"qux": [7, 8]} を結果に残したい場合は、どうすればいいでしょうか。直感的には LEFT JOIN で行けそうですが、残念ながら LATERAL は通常の JOIN の左側になることができません。

select t1.c1, f1.value, f2.value
from t1,
lateral flatten(t1.c1:foo) f1
left join lateral flatten(f1.value:bar) f2;
-- SQL compilation error: Lateral View cannot be on the left side of join

実は、これを解決するスマートな手段はありません。基本的には CTE で LATERAL FLATTEN しておいて LEFT JOIN で結合する、といった泥臭い方法になります。

with
f1 as (
    select t1.c1:id id, f.index, f.value
    from t1, lateral flatten(t1.c1:foo) f
),
f2 as (
    select f1.id, f1.index, f.value
    from f1, lateral flatten(f1.value:bar) f
)
select t1.c1, f1.value, f2.value
from t1
join f1 on t1.c1:id = f1.id
left join f2 on f1.id = f2.id and f1.index = f2.index
;

JSON 内のユニークキー (今回の例で言うと id) や、FLATTEN が返す INDEX カラムなどを使って結合することで、LATERAL FLATTEN に近い動きを再現することができます。

t1.c1 f1.value f2.value
{"id": 1, "foo": [{"bar": [1, 2]}, {"bar": [3, 4]}]} {"bar": [1, 2]} 1
{"id": 1, "foo": [{"bar": [1, 2]}, {"bar": [3, 4]}]} {"bar": [1, 2]} 2
{"id": 1, "foo": [{"bar": [1, 2]}, {"bar": [3, 4]}]} {"bar": [3, 4]} 3
{"id": 1, "foo": [{"bar": [1, 2]}, {"bar": [3, 4]}]} {"bar": [3, 4]} 4
{"id": 2, "foo": [{"bar": [5, 6]}, {"qux": [7, 8]}]} {"bar": [5, 6]} 5
{"id": 2, "foo": [{"bar": [5, 6]}, {"qux": [7, 8]}]} {"bar": [5, 6]} 6
{"id": 2, "foo": [{"bar": [5, 6]}, {"qux": [7, 8]}]} {"qux": [7, 8]} NULL

まとめ

相関サブクエリやカラム値を引数に取るテーブル関数は、クエリが複雑になる原因になりがちです。そんなとき LATERAL は強力な武器になります。

特に LATERAL FLATTEN は Snowflake の柔軟な Semi-structured Data の扱いを支える非常に強力なイディオムです。

この記事で LATERALLATERAL FLATTEN がどう動いているのかを理解することで、データに合わせて自在に使いこなせるようになるための助けになればと思います。