HIve 中explode,inline与lateral view

HIve 中explode,inline与lateral view

1.功能展示

2.使用注意事项

• 不支持与其他字段同时使用
• 不支持一次对同字段使用多次explode
• 不支持在group by/sort by/cluster by 后使用explode

3.与lateral view 一起使用

SELECT myCol1, myCol2 FROM baseTable

LATERAL VIEW explode(col1) myTable1 AS myCol1

LATERAL VIEW explode(col2) myTable2 AS myCol2;

结果：

4.inline(array(Struct))将结构数据展开为多列

create table tmp_user_scan_info_struct_mid stored as orc as
select 
    uid,
    named_struct('page_id', page_id, 
                 'page_name', page_name, 
                 'log_date', log_date
                 ) as named_struct_1
from tmp_user_scan_info;

select * from tmp_user_scan_info_struct_mid;
+------+---------------------------------------------------------+--+
| uid  |                     named_struct_1                      |
+------+---------------------------------------------------------+--+
| 0    | {"page_id":10,"page_name":"A","log_date":"2015-01-01"}  |
| 0    | {"page_id":20,"page_name":"B","log_date":"2015-01-02"}  |
| 1    | {"page_id":10,"page_name":"A","log_date":"2016-02-01"}  |
| 1    | {"page_id":20,"page_name":"B","log_date":"2016-02-02"}  |
+------+---------------------------------------------------------+--+

select
    uid,
    page_id,
    page_name,
    log_date
from tmp_user_scan_info_struct_mid t
lateral view outer inline(array(named_struct_1)) tmp as page_id, page_name, log_date;

select inline(array(named_struct_1)) from tmp_user_scan_info_struct_mid;
+------------+----------+-------------+--+
| page_name  | page_id  |  log_date   |
+------------+----------+-------------+--+
| A          | 10       | 2015-01-01  |
| B          | 20       | 2016-02-02  |
+------------+----------+-------------+--+

-------------------- 支持多列别名（多字段名）---------------
select inline( array(struct('A',10,date '2015-01-01'),
                     struct('B',20,date '2016-02-02')
                     ) 
             ) as (page_name, page_id, log_date);
+------------+----------+-------------+--+
| page_name  | page_id  |  log_date   |
+------------+----------+-------------+--+
| A          | 10       | 2015-01-01  |
| B          | 20       | 2016-02-02  |
+------------+----------+-------------+--+