hiveodps等with as 的插入语法、lateral view 侧视图与explode、json

hive/odps等with as 的插入语法、lateral view 侧视图与explode、json

文章目录

• • Oracle中则可以将插入语句放在开头
  • lateral view 几种用法
  • lateral view 除了explode还有json_tupe等函数联用

with a as (
select  * from test
),
b as (
select * from test2
)
insert into/overwrite table 
select * from a join b on a. id =b.id

Oracle中则可以将插入语句放在开头

insert into/overwrite table 
with a as ( select  * from test
),
b as (
select * from test2
)
select * from a join b on a. id =b.id

lateral view 几种用法

hive中

hive> select explode(Array("a","b","c","d"));
OK
a
b
c
d
Time taken: 0.505 seconds, Fetched: 4 row(s)

在表中如果只是单独查询explode的字段可以，但是查询其他字段就会报错

--错误
hive> select explode(co),c from ( select collect_list(id) as co,'wo'as c  from sys_community )t ;
FAILED: SemanticException 1:19 only a single expression in the SELECt clause is supported with UDTF's. Error encountered near token 'c'
--正确写法
hive> select c,b from ( select collect_list(id) as co,'wo'as c  from sys_community )t lateral view explode(co) rr as b;

lateral view 除了explode还有json_tupe等函数联用

hive> select json_tuple("{"nong":5.0,"fast":5.0,"strong":5.0,"beautiful":5.0,"power":5.0}","nong","fast","strong",'beautiful','power');
OK
5.0     5.0     5.0     5.0     5.0
Time taken: 0.177 seconds, Fetched: 1 row(s)

hive> select a,b,c from sys_community lateral view json_tuple("{"nong":5.0,"fast":5.0,"strong":5.0,"beautiful":5.0,"power":5.0}","nong","fast","strong") rr as a,b,c limit 1;
OK
5.0     5.0     5.0
Time taken: 0.437 seconds, Fetched: 1 row(s)