使用 Hive 炸裂函数 explode (map<string,string>) 宽表转高表

2021-12-24 14:34:57 浏览数 (1)

Hive 炸裂函数 explode(map<string,string>) 宽表转高表SQL:

代码语言：javascript复制
select  
slice_id,
user_id,
shop_id,
'user_stats_public' as table_code,
explode(kv) as (field_code,field_value)
from 
(   select 
    user_id,
    -1 as shop_id,
    abs(hash(user_id) % 20000) as  slice_id, 
    map(
    'residence_city_name',residence_city_name,
    'residence_city_level',residence_city_level,
    'gender', gender,
    'age', age,
    'activeness_level' , activeness_level,
    'consuming_level' , consuming_level,
    'pay_amt_td_level' , pay_amt_td_level,
    'old_ecom_user' , old_ecom_user
    ) as kv
from  ecom_kunlun.types_mapping_table_13_1639904624993 
where date = max_pt('ecom_kunlun.types_mapping_table_13_1639904624993') ) a;

explode() : Usage Examples

explode (array)
代码语言：javascript复制
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;
explode (map)
代码语言：javascript复制
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;
posexplode (array)
代码语言：javascript复制
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;
inline (array of structs)
代码语言：javascript复制
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;
stack (values)
代码语言：javascript复制
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;

Using the syntax "SELECT udtf(col) AS colAlias..." has a few limitations:

  • No other expressions are allowed in SELECT
    • SELECT pageid, explode(adid_list) AS myCol... is not supported
  • UDTF's can't be nested
    • SELECT explode(explode(adid_list)) AS myCol... is not supported
  • GROUP BY / CLUSTER BY / DISTRIBUTE BY / SORT BY is not supported
    • SELECT explode(adid_list) AS myCol ... GROUP BY myCol is not supported

Please see LanguageManual LateralView for an alternative syntax that does not have these limitations.

Also see Writing UDTFs if you want to create a custom UDTF.

参考资料

https://cwiki.apache.org/confluence/display/Hive/LanguageManual UDF#LanguageManualUDF

hive 编程算法

0 人点赞