Column to Row (Lateral View)
Used in conjunction with generator functions such as EXPLODE, will generate a virtual table containing one or more rows. LATERAL VIEW applies rows to each raw input row.
Grammar
LATERAL VIEW generator_function ( expression [,...]) table_identifier AS column_identifier [,...]
Parameters
-
generator_function
Generator functions (EXPLODE, EXPLODE_SPLIT, etc.).
-
table_identifier
Alias for
generator_function. -
column_identifier
List column alias
generator_function, which can be used to reference the output columns. The number of column identifiers must match the number of columns returned by the generator function.
Example
CREATETABLE`person`(
`id`int(11)NULL,
`name`textNULL,
`age`int(11)NULL,
`class`int(11)NULL,
`address`textNULL
)ENGINE=OLAP
UNIQUEKEY(`id`)
COMMENT'OLAP'
DISTRIBUTEDBYHASH(`id`) BUCKETS 1
PROPERTIES (
"replication_allocation"="tag.location.default: 1",
"in_memory"="false",
"storage_format"="V2",
"disable_auto_compaction"="false"
);
INSERTINTO person VALUES
(100,'John',30,1,'Street 1'),
(200,'Mary',NULL,1,'Street 2'),
(300,'Mike',80,3,'Street 3'),
(400,'Dan',50,4,'Street 4');
mysql>SELECT*FROM person
-> LATERAL VIEW EXPLODE(ARRAY(30,60)) tableName AS c_age;
+------+------+------+-------+----------+-------+
| id | name | age | class | address | c_age |
+------+------+------+-------+----------+-------+
|100| John |30|1| Street 1|30|
|100| John |30|1| Street 1|60|
|200| Mary |NULL|1| Street 2|30|
|200| Mary |NULL|1| Street 2|60|
|300| Mike |80|3| Street 3|30|
|300| Mike |80|3| Street 3|60|
|400| Dan |50|4| Street 4|30|
|400| Dan |50|4| Street 4|60|
+------+------+------+-------+----------+-------+
8rowsinset(0.12 sec)