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

wuchangjian2021-11-04 20:35:16编程学习

文章目录

    • 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)


相关文章

2两数之和

给你两个 非空 的链表,表示两个非负的整数。它们每位数字都是按照 逆序 的...

如何在python上安装安装包(python 3.9 环境 PyCharm pillow安装包为例)

如何在python上安装安装包(python 3.9 环境 PyCharm pillow安装包为例)

一、确定安装了python 1.cmd的打开方式:windows+...

城市统计年鉴数据查询

城市统计年鉴查询可以去 https://www.yearbookchina.com...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。