leileihhxx 2022-02-13 07:44:14 阅读数:272
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
copyright:author[leileihhxx],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130744118075.html