結局 LATERAL とは何者なのか？

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

2021-04-14: 「LEFT JOIN したいときはどうするの？」のセクションを大幅に書き換えました。

---

こんにちは、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 JOIN や INNER 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 FLATTEN で foo を展開しても、その値はまだ 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 回目の LATERAL で bar 2 行と結合され、さらに 2 回目の LATERAL で bar の中身の配列要素 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 は、ある程度スキーマが決まっていることが多いものの、本質的にはスキーマレスです。

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

例えば、下記の 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 FLATTEN で bar についてシンプルに展開すると {"qux": [7, 8]} は闇に葬られ、無かったことになります。

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

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]} を結果に残したい場合は、どうすればいいでしょうか。まずは、なぜこの行が消えたのかを考えてみましょう。

LATERAL は前述の通り CROSS JOIN なので、f2 の FLATTEN() で存在しない行が NULL に展開されていれば、NULL が維持されるはずです。したがって、このケースでは FLATTEN() が存在しない行を出力から消している可能性が高くなります。

実はこの動作はドキュメントにも記載されており、OUTER というオプションでコントロールできます。

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

OUTER => TRUE | FALSE

・ FALSE の場合、パスでアクセスできないか、フィールドまたはエントリがゼロであるために展開できない入力行は、出力から完全に省略されます。
・ TRUE の場合、ゼロ行展開用に正確に1行が生成されます（ KEY、 INDEX、 VALUE 列に NULL が含まれる）。

デフォルト： FALSE

したがって、このケースは OUTER オプションをつけるだけで解決します。

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

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

では、以下のようなケースはどうでしょうか。

create or replace table t1 (c1 variant) as
select parse_json('{"id": 1, "foo":[{"bar":[{"value": "ichi", "index": 1}, {"value": "ni", "index": 2}]}]}')
union
select parse_json('{"id": 2, "foo":[{"bar":[{"value": "odin", "index": 1}]}]}');

bar は複数の子プロパティを持っており、それぞれ 1 または 2 の index を持っていて、かつ 2 行目には index = 2 のプロパティが存在しません。

このデータに対して、index ごとのカラムに展開しつつ、対応する index のデータがなければ NULL にしたい、つまり下記のような結果が欲しい場合はどのように記述することができるでしょうか。

value_one	value_two
"ichi"	"ni"
"odin"	NULL

まず、このケースは完全な階層構造ではない (value_one と value_two は同階層に存在する) ので LATERAL を使用するのは難しそうです。

「存在しない行を維持する」必要があるので、直感的にはサブクエリで行けそうですが、サブクエリ中から foo に対する FLATTEN に触ることができないので、これは不可能です。

select f2.value, f3.value
from t1,
table(flatten(t1.c1:foo)) f1,
(select value from table(flatten(f1.value:bar)) where value:index = 1) f2,
(select value from table(flatten(f1.value:bar)) where value:index = 2) f3
;

-- SQL compilation error: error line 4 at position 33 invalid identifier 'F1.VALUE'

また、上のクエリで LATERAL を使用していないように、LATERAL はあらゆる結合の左側になれないため、ネストした展開の場合、2 段目以降に LEFT JOIN を置くことができません。

select t1.c1, f1.value, f2.value
from t1,
lateral flatten(t1.c1:foo) f1
left join table(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
),
f_one as (
    select f1.id, f1.index, f.value:value value
    from f1, lateral flatten(f1.value:bar) f
    where f.value:index = 1
),
f_two as (
    select f1.id, f1.index, f.value:value value
    from f1, lateral flatten(f1.value:bar) f
    where f.value:index = 2
)
select f_one.value value_one, f_two.value value_two
from t1
join f1 on t1.c1:id = f1.id
left join f_one on f1.id = f_one.id and f1.index = f_one.index
left join f_two on f1.id = f_two.id and f1.index = f_two.index
;

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

value_one	value_two
"ichi"	"ni"
"odin"	NULL

まとめ

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

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

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