...
Row-set columns types | Name(Signature) | Description |
|---|---|---|
T | explode(ARRAY<T> a) | Explodes an array to multiple rows. Returns a row-set with a single column (col), one row for each element from the array. |
| int,T | posexplode(ARRAY<T> a) | Explodes an array to multiple rows with additional positional column of int type (position of items in the original array, starting with 0). Returns a row-set with two columns (pos,val), one row for each element from the array. |
Tkey,Tvalue | explode(MAP<Tkey,Tvalue> m) | Explodes a map to multiple rows. Returns a row-set with a two columns (key,value) , one row for each key-value pair from the input map. (As of Hive 0.8.0.). |
| int,T | posexplode(ARRAY<T> a) | Explodes an array to multiple rows with additional positional column of int type (position of items in the original array, starting with 0). Returns a row-set with two columns (pos,val), one row for each element from the array. |
T1,...,Tn | inline(ARRAY<STRUCT<f1:T1,...,fn:Tn>> a) | Explodes an array of structs to multiple rows. Returns a row-set with N columns (N = number of top level elements in the struct), one row per struct from the array. (As of Hive 0.10.) |
| T1,...,Tn/r | stack(int r,T1 V1,...,Tn/r Vn) | Breaks up n values V1,...,Vn into r rows. Each row will have n/r columns. r must be constant. |
string1,...,stringn | json_tuple(string jsonStr,string k1,...,string kn) | Takes JSON string and a set of n keys, and returns a tuple of n values. This is a more efficient version of the |
string 1,...,stringn | parse_url_tuple(string urlStr,string p1,...,string pn) | Takes URL string and a set of n URL parts, and returns a tuple of n values. This is similar to the |
Usage Examples
explode (array)
| Code Block | ||
|---|---|---|
| ||
select explode(array('A','B','C'));
select explode(array('A','B','C')) as col;
select tf.* from (select 0) t lateral view explode(array('A','B','C')) tf;
select tf.* from (select 0) t lateral view explode(array('A','B','C')) tf as col; |
| HTML | ||
|---|---|---|
| ||
<table><tbody><tr><th>col</th></tr><tr><td>A</td></tr><tr><td>B</td></tr><tr><td>C</td></tr></tbody></table> |
explode (map)
| Code Block | ||
|---|---|---|
| ||
select explode(map('A',10,'B',20,'C',30));
select explode(map('A',10,'B',20,'C',30)) as (key,value);
select tf.* from (select 0) t lateral view explode(map('A',10,'B',20,'C',30)) tf;
select tf.* from (select 0) t lateral view explode(map('A',10,'B',20,'C',30)) tf as key,value; |
| HTML | ||
|---|---|---|
| ||
<table><tbody><tr><th>key</th><th>value</th></tr><tr><td>A</td><td>10</td></tr><tr><td>B</td><td>20</td></tr><tr><td>C</td><td>30</td></tr></tbody></table> |
posexplode (array)
| Code Block | ||
|---|---|---|
| ||
select posexplode(array('A','B','C'));
select posexplode(array('A','B','C')) as (pos,val);
select tf.* from (select 0) t lateral view posexplode(array('A','B','C')) tf;
select tf.* from (select 0) t lateral view posexplode(array('A','B','C')) tf as pos,val; |
| HTML | ||
|---|---|---|
| ||
<table><tbody><tr><th>pos</th><th>val</th></tr><tr><td>0</td><td>A</td></tr><tr><td>1</td><td>B</td></tr><tr><td>2</td><td>C</td></tr></tbody></table> |
inline (array of structs)
| Code Block | ||
|---|---|---|
| ||
select inline(array(struct('A',10,date '2015-01-01'),struct('B',20,date '2016-02-02')));
select inline(array(struct('A',10,date '2015-01-01'),struct('B',20,date '2016-02-02'))) as (col1,col2,col3);
select tf.* from (select 0) t lateral view inline(array(struct('A',10,date '2015-01-01'),struct('B',20,date '2016-02-02'))) tf;
select tf.* from (select 0) t lateral view inline(array(struct('A',10,date '2015-01-01'),struct('B',20,date '2016-02-02'))) tf as col1,col2,col3; |
| HTML | ||
|---|---|---|
| ||
<table><tbody><tr><th>col1</th><th>col2</th><th>col3</th></tr><tr><td>A</td><td>10</td><td>2015-01-01</td></tr><tr><td>B</td><td>20</td><td>2016-02-02</td></tr></tbody></table> |
stack (values)
| Code Block | ||
|---|---|---|
| ||
select stack(2,'A',10,date '2015-01-01','B',20,date '2016-01-01');
select stack(2,'A',10,date '2015-01-01','B',20,date '2016-01-01') as (col0,col1,col2);
select tf.* from (select 0) t lateral view stack(2,'A',10,date '2015-01-01','B',20,date '2016-01-01') tf;
select tf.* from (select 0) t lateral view stack(2,'A',10,date '2015-01-01','B',20,date '2016-01-01') tf as col0,col1,col2; |
| HTML | ||
|---|---|---|
| ||
<table><tbody><tr><th>col0</th><th>col1</th><th>col2</th></tr><tr><td>A</td><td>10</td><td>2015-01-01</td></tr><tr><td>B</td><td>20</td><td>2016-01-01</td></tr></tbody></table> |
Using the syntax "SELECT udtf(col) AS colAlias..." has a few limitations:
...
For information about how to create a custom UDF, see Hive Plugins and Create Function.
select explode(array('A','B','C'));select explode(array('A','B','C')) as col;select tf.* from (select 0) t lateral view explode(array('A','B','C')) tf;select tf.* from (select 0) t lateral view explode(array('A','B','C')) tf as col;