IoT Platform:Complex type functions

EXPLODE

• Limits

  • A SELECT statement can include only one EXPLODE function, and only column data can be processed.

  • This function cannot be used with the GROUP BY, CLUSTER BY, DISTRIBUTE BY, or SORT BY clause.

• Syntax

  explode (<var>)

• Description

  Transposes one row of data into multiple rows (UDTF).

  • If the value is of the array<T> type, each element in the array is transposed into a separate row.

  • If the value is of the map<K, V> type, each key-value pair is transposed into a row with two columns: one for the key and one for the value.

  • Rows that contain only null values are excluded from the output.

• Parameters

  var: required. The value must be of the array<T> or map<K, V> type.

• Return value

  Returns the transposed rows.

• Examples

  • Example 1:

    The t_table_map table contains the c1 (BIGINT) and t_map (MAP<STRING,BIGINT>) columns. Data in the table:

      +------------+-------+
    | c1         | t_map |
    +------------+-------+
    | 1000       | {k11:86, k21:15} |
    | 1001       | {k12:97, k22:2} |
    | 1002       | {k13:99, k23:1} |
    +------------+-------+

    Sample statement:

    select explode(t_map) from t_table_map;
    -- Sample result: 
    +-----+------------+
    | key | value      |
    +-----+------------+
    | k11 | 86         |
    | k21 | 15         |
    | k12 | 97         |
    | k22 | 2          |
    | k13 | 99         |
    | k23 | 1          |
    +-----+------------+

  • Example 2:

    Rows that contain only null values are excluded from the output.

    -- Create a table.
    create table explod_array (arr array<string>);
    -- Insert data into the table.
    insert into table explod_array values (array('1','2','3',null,'agb')),
                                          (array('1','2','3',null,'ag')),
                                          (null);
    -- Query data.
    select * from explod_array;
    -- Sample result:
    +------------+
    | arr        |
    +------------+
    | ["1","2","3",null,"agb"] |
    | ["1","2","3",null,"ag"] |
    | NULL       |
    +------------+
    -- Display data.
    select explode(arr) from explod_array;
    -- The returned result does not include a row that contains only the null data.
    +------------+
    | col        |
    +------------+
    | 1          |
    | 2          |
    | 3          |
    | NULL       |
    | agb        |
    | 1          |
    | 2          |
    | 3          |
    | NULL       |
    | ag         |
    +------------+