Hive SQL, lateral view profile function and expand application

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

select
    name,
    catelogy_name
from
    movie
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