Hive SQL, lateral view profile function and expand application

leileihhxx 2022-02-13 07:44:14 阅读数:272

hive sql lateral view profile

demand :

Change to the format shown in the figure below

Build a watch first

create table movie(name string,category string)
row format delimited fields terminated by '\t';

Import data

load data local inpath '/home/hadoop/data/movie.txt' into table movie;


For direct demand explode Function directly to category Try it

Report errors :

FAILED: UDFArgumentException explode() takes an array or a map as a parameter

Tips explode The function should use an array or map Type in . I think of split function

split function :split(string str, string pat), according to pat Division str

select split(category,",") from movie;

Get array , Reuse explode try

 select explode(split(category,",")) from movie;

Successfully convert one row to multiple columns , But the name wasn't found , stay select add to name try

select name,explode(split(catelogy,",")) from movie;

Report errors :

 SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions

  as a result of name Only 3 Column , and catelogy Use explode After the function , Divided into 12 Column , Much larger than the original corresponding name Columns of

It's used here lateral view function

lateral view explode(split(category,",")) movie_temp as catelogy_name

there movie_temp Is the alias of the profile table ,catelogy_name It's using explode Alias after function .

lateral view The actual operation can be considered as doing a Cartesian product . Pictured :

That is to get the desired result

copyright:author[leileihhxx],Please bring the original link to reprint, thank you.