以下是一个嵌套表格的例子。
CREATE EXTERNAL TABLE ParentTable (
name STRING,
age INT,
kids ARRAY>
) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://my-bucket/parent-table/';
CREATE EXTERNAL TABLE ChildTable (
parentName STRING,
kidName STRING,
kidAge INT
) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://my-bucket/child-table/';
INSERT INTO ChildTable
SELECT name, kids.name, kids.age
FROM ParentTable
CROSS JOIN UNNEST(kids) AS kids;
SELECT *
FROM ParentTable pt
LEFT JOIN ChildTable ct ON pt.name = ct.parentName;