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

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

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)


欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/zaji/5116699.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022-11-17
下一篇 2022-11-17

发表评论

登录后才能评论

评论列表(0条)

保存