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)