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 output row.

Grammar

1. 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 output rows. The number of column identifiers must match the number of columns returned by the generator function.

Example

1. CREATE TABLE `person` (
2. `id` int(11) NULL,
3. `name` text NULL,
4. `age` int(11) NULL,
5. `class` int(11) NULL,
6. `address` text NULL
7. ) ENGINE=OLAP
8. UNIQUE KEY(`id`)
9. COMMENT 'OLAP'
10. DISTRIBUTED BY HASH(`id`) BUCKETS 1
11. PROPERTIES (
12. "replication_allocation" = "tag.location.default: 1",
13. "in_memory" = "false",
14. "storage_format" = "V2",
15. "disable_auto_compaction" = "false"
16. );

18. INSERT INTO person VALUES
19. (100, 'John', 30, 1, 'Street 1'),
20. (200, 'Mary', NULL, 1, 'Street 2'),
21. (300, 'Mike', 80, 3, 'Street 3'),
22. (400, 'Dan', 50, 4, 'Street 4');

24. mysql> SELECT * FROM person
25. ->     LATERAL VIEW EXPLODE(ARRAY(30, 60)) tableName AS c_age;
26. +------+------+------+-------+----------+-------+
27. | id   | name | age  | class | address  | c_age |
28. +------+------+------+-------+----------+-------+
29. |  100 | John |   30 |     1 | Street 1 |    30 |
30. |  100 | John |   30 |     1 | Street 1 |    60 |
31. |  200 | Mary | NULL |     1 | Street 2 |    30 |
32. |  200 | Mary | NULL |     1 | Street 2 |    60 |
33. |  300 | Mike |   80 |     3 | Street 3 |    30 |
34. |  300 | Mike |   80 |     3 | Street 3 |    60 |
35. |  400 | Dan  |   50 |     4 | Street 4 |    30 |
36. |  400 | Dan  |   50 |     4 | Street 4 |    60 |
37. +------+------+------+-------+----------+-------+
38. 8 rows in set (0.12 sec)