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 output row.
LATERAL VIEW generator_function ( expression [, ...] ) table_identifier AS column_identifier [, ...]
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 output rows. The number of column identifiers must match the number of columns returned by the generator function.
CREATE TABLE `person` ( `id` int(11) NULL, `name` text NULL, `age` int(11) NULL, `class` int(11) NULL, `address` text NULL ) ENGINE=OLAP UNIQUE KEY(`id`) COMMENT 'OLAP' DISTRIBUTED BY HASH(`id`) BUCKETS 1 PROPERTIES ( "replication_allocation" = "tag.location.default: 1", "in_memory" = "false", "storage_format" = "V2", "disable_auto_compaction" = "false" ); INSERT INTO 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 | +------+------+------+-------+----------+-------+ 8 rows in set (0.12 sec)