explode

使⽤Hive炸裂函数explode（mapstring,string）宽表转⾼表

Hive炸裂函数explode(map)宽表转⾼表SQL:

lect

slice_id,

ur_id,

shop_id,

'ur_stats_public'astable_code,

explode(kv)as(field_code,field_value)

from

(lect

ur_id,

-1asshop_id,

abs(hash(ur_id)%20000)asslice_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_ur',old_ecom_ur

)askv

fromecom__mapping_table_13_93

wheredate=max_pt('ecom__mapping_table_13_93'))a;

explode()explode()::UsageExamplesUsageExamples

raexplode(array)

lectexplode(array('A','B','C'));

lectexplode(array('A','B','C'))ascol;

lecttf.*from(lect0)tlateralviewexplode(array('A','B','C'))tf;

lecttf.*from(lect0)tlateralviewexplode(array('A','B','C'))tfascol;

de(maexplode(map)

lectexplode(map('A',10,'B',20,'C',30));

lectexplode(map('A',10,'B',20,'C',30))as(key,value);

lecttf.*from(lect0)tlateralviewexplode(map('A',10,'B',20,'C',30))tf;

lecttf.*from(lect0)tlateralviewexplode(map('A',10,'B',20,'C',30))tfaskey,value;

poxplode(array)

lectpoxplode(array('A','B','C'));

lectpoxplode(array('A','B','C'))as(pos,val);

lecttf.*from(lect0)tlateralviewpoxplode(array('A','B','C'))tf;

lecttf.*from(lect0)tlateralviewpoxplode(array('A','B','C'))tfaspos,val;

inline(arrayofstructs)

lectinline(array(struct('A',10,date'2015-01-01'),struct('B',20,date'2016-02-02')));

lectinline(array(struct('A',10,date'2015-01-01'),struct('B',20,date'2016-02-02')))as(col1,col2,col3);

lecttf.*from(lect0)tlateralviewinline(array(struct('A',10,date'2015-01-01'),struct('B',20,date'2016-02-02')))tf;

lecttf.*from(lect0)tlateralviewinline(array(struct('A',10,date'2015-01-01'),struct('B',20,date'2016-02-02')))tfascol1,col2,col3;

stack(values)

lectstack(2,'A',10,date'2015-01-01','B',20,date'2016-01-01');

lectstack(2,'A',10,date'2015-01-01','B',20,date'2016-01-01')as(col0,col1,col2);

lecttf.*from(lect0)tlateralviewstack(2,'A',10,date'2015-01-01','B',20,date'2016-01-01')tf;

lecttf.*from(lect0)tlateralviewstack(2,'A',10,date'2015-01-01','B',20,date'2016-01-01')tfascol0,col1,col2;

Usingthesyntax"SELECTudtf(col)AScolAlias..."hasafewlimitations:

NootherexpressionsareallowedinSELECT

SELECTpageid,explode(adid_list)ASmyCol...isnotsupported

UDTF'scan'tbenested

SELECTexplode(explode(adid_list))ASmyCol...isnotsupported

GROUPBY/CLUSTERBY/DISTRIBUTEBY/SORTBYisnotsupported

SELECTexplode(adid_list)ASmyCol...GROUPBYmyColisnotsupported

PleaeLanguageManualLateralViewforanalternativesyntaxthatdoesnothavethelimitations.

AlsoeWritingUDTFsifyouwanttocreateacustomUDTF.

参考资料