Use Lateral Join for column-to-row conversion
列から行への変換は、ETL 処理において一般的な操作です。Lateral は特別なジョインキーワードで、行を内部サブクエリやテーブル関数と関連付けることができます。Lateral を unnest() と組み合わせることで、1 行を複数の行に展開できます。詳細については、 unnest を参照してください。
制限事項
- 現在、Lateral Join は列から行への変換を実現するために unnest() と一緒にのみ使用されます。他のテーブル関数や UDTF は後でサポートされる予定です。
- 現在、Lateral Join はサブクエリをサポートしていません。
Lateral Join の使用
構文:
from table_reference join [lateral] table_reference;
例:
SELECT student, score
FROM tests
CROSS JOIN LATERAL UNNEST(scores) AS t (score);
SELECT student, score
FROM tests, UNNEST(scores) AS t (score);
ここでの2番目の構文は、最初のものを短縮したバージョンで、Lateral キーワードを省略して UNNEST キーワードを使用できます。UNNEST キーワードは、配列を複数の行に変換するテーブル関数です。Lateral Join と一緒に使用することで、一般的な行展開ロジックを実装できます。
NOTE
複数の列に対して unnest を実行したい場合は、各列にエイリアスを指定する必要があります。例えば、
select v1, t1.unnest as v2, t2.unnest as v3 from lateral_test, unnest(v2) t1, unnest(v3) t2;のようにします。
CelerData は、BITMAP、STRING、ARRAY、および Column 間の型変換をサポートしています。
使用例
unnest() と一緒に使用することで、以下の列から行への変換機能を実現できます。
文字列を複数の行に展開
-
テーブルを作成し、このテーブルにデータを挿入します。
CREATE TABLE lateral_test2 (`v1` bigint(20) NULL COMMENT "",`v2` string NULL COMMENT "")DUPLICATE KEY(v1)DISTRIBUTED BY HASH(`v1`)PROPERTIES ("storage_format" = "DEFAULT");INSERT INTO lateral_test2 VALUES (1, "1,2,3"), (2, "1,3"); -
展開前のデータをクエリします。
select * from lateral_test2;+------+-------+| v1 | v2 |+------+-------+| 1 | 1,2,3 || 2 | 1,3 |+------+-------+ -
v2を複数の行に展開します。-- 単一の列に対して unnest を実行します。select v1,unnest from lateral_test2, unnest(split(v2, ","));+------+--------+| v1 | unnest |+------+--------+| 1 | 1 || 1 | 2 || 1 | 3 || 2 | 1 || 2 | 3 |+------+--------+-- 複数の列に対して unnest を実行します。各操作にエイリアスを指定する必要があります。select v1, t1.unnest as v2, t2.unnest as v3 from lateral_test2, unnest(split(v2, ",")) t1, unnest(split(v3, ",")) t2;+------+------+------+| v1 | v2 | v3 |+------+------+------+| 1 | 1 | 1 || 1 | 1 | 2 || 1 | 2 | 1 || 1 | 2 | 2 || 1 | 3 | 1 || 1 | 3 | 2 || 2 | 1 | 1 || 2 | 1 | 3 || 2 | 3 | 1 || 2 | 3 | 3 |+------+------+------+
配列を複数の行に展開
unnest() 関数は、異なる型と長さの複数の配列を受け取ることができます。 詳細については、 unnest() を参照してください。
-
テーブルを作成し、このテーブルにデータを挿入します。
CREATE TABLE lateral_test (`v1` bigint(20) NULL COMMENT "",`v2` ARRAY NULL COMMENT "")DUPLICATE KEY(v1)DISTRIBUTED BY HASH(`v1`)PROPERTIES ("storage_format" = "DEFAULT");INSERT INTO lateral_test VALUES (1, [1,2]), (2, [1, null, 3]), (3, null); -
展開前のデータをクエリします。
select * from lateral_test;+------+------------+| v1 | v2 |+------+------------+| 1 | [1,2] || 2 | [1,null,3] || 3 | NULL |+------+------------+ -
v2を複数の行に展開します。select v1,v2,unnest from lateral_test , unnest(v2) ;+------+------------+--------+| v1 | v2 | unnest |+------+------------+--------+| 1 | [1,2] | 1 || 1 | [1,2] | 2 || 2 | [1,null,3] | 1 || 2 | [1,null,3] | NULL || 2 | [1,null,3] | 3 |+------+------------+--------+
Bitmap データを展開
-
テーブルを作成し、このテーブルにデータを挿入します。
CREATE TABLE lateral_test3 (`v1` bigint(20) NULL COMMENT "",`v2` Bitmap BITMAP_UNION COMMENT "")AGGREGATE KEY(v1)DISTRIBUTED BY HASH(`v1`);INSERT INTO lateral_test3 VALUES (1, bitmap_from_string('1, 2')), (2, to_bitmap(3)); -
展開前のデータをクエリします。
select v1, bitmap_to_string(v2) from lateral_test3;+------+------------------------+| v1 | bitmap_to_string(`v2`) |+------+------------------------+| 1 | 1,2 || 2 | 3 |+------+------------------------+ -
新しい行を挿入します。
insert into lateral_test3 values (1, to_bitmap(3));select v1, bitmap_to_string(v2) from lateral_test3;+------+------------------------+| v1 | bitmap_to_string(`v2`) |+------+------------------------+| 1 | 1,2,3 || 2 | 3 |+------+------------------------+ -
v2のデータを複数の行に展開します。select v1,unnest from lateral_test3 , unnest(bitmap_to_array(v2));+------+--------+| v1 | unnest |+------+--------+| 1 | 1 || 1 | 2 || 1 | 3 || 2 | 3 |+------+--------+