这里分类和汇总了欣宸的全部原创(含配套源码):https://github.com/zq2599/blog_demos
set hive.cli.print.header=true;
create table if not exists t2(
person string,
friends array<string>
)
row format delimited
fields terminated by '|'
collection items terminated by ',';
tom|tom_friend_0,tom_friend_1,tom_friend_2
jerry|jerry_friend_0,jerry_friend_1,jerry_friend_2,jerry_friend_3,jerry_friend_4,jerry_friend_5
load data local inpath '/home/hadoop/temp/202010/25/002.txt' into table t2;
hive> select * from t2;
OK
t2.person t2.friends
tom ["tom_friend_0","tom_friend_1","tom_friend_2"]
jerry ["jerry_friend_0","jerry_friend_1","jerry_friend_2","jerry_friend_3","jerry_friend_4","jerry_friend_5"]
Time taken: 0.052 seconds, Fetched: 2 row(s)
select person, friends[0], friends[3] from t2;
执行结果如下,第一条记录没有friends3,显示为NULL:
hive> select person, friends[0], friends[3] from t2;
OK
person _c1 _c2
tom tom_friend_0 NULL
jerry jerry_friend_0 jerry_friend_3
Time taken: 0.052 seconds, Fetched: 2 row(s)
select person, array_contains(friends, 'tom_friend_0') from t2;
执行结果如下,第一条记录friends数组中有tom_friend_0,显示为true,第二条记录不包含,就显示false:
hive> select person, array_contains(friends, 'tom_friend_0') from t2;
OK
person _c1
tom true
jerry false
Time taken: 0.061 seconds, Fetched: 2 row(s)
select t.person, single_friend
from (
select person, friends
from t2 where person='tom'
) t LATERAL VIEW explode(t.friends) v as single_friend;
执行结果如下,可见数组中的每个元素都能拆成单独一行:
OK
t.person single_friend
tom tom_friend_0
tom tom_friend_1
tom tom_friend_2
Time taken: 0.058 seconds, Fetched: 3 row(s)
create table if not exists t3(
person string,
address map<string, string>
)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':';
tom|province:guangdong,city:shenzhen
jerry|province:jiangsu,city:nanjing
load data local inpath '/home/hadoop/temp/202010/25/003.txt' into table t3;
hive> select * from t3;
OK
t3.person t3.address
tom {"province":"guangdong","city":"shenzhen"}
jerry {"province":"jiangsu","city":"nanjing"}
Time taken: 0.075 seconds, Fetched: 2 row(s)
hive> select person, address["province"] from t3;
OK
person _c1
tom guangdong
jerry jiangsu
Time taken: 0.075 seconds, Fetched: 2 row(s)
select person,
if(address['street'] is null, "filed street not exists", address['street'])
from t3;
OK
tom filed street not exists
jerry filed street not exists
Time taken: 0.087 seconds, Fetched: 2 row(s)
hive> select explode(address) from t3;
OK
province guangdong
city shenzhen
province jiangsu
city nanjing
Time taken: 0.081 seconds, Fetched: 4 row(s)
select t.person, address_key, address_value
from (
select person, address
from t3 where person='tom'
) t LATERAL VIEW explode(t.address) v as address_key, address_value;
结果如下:
OK
tom province guangdong
tom city shenzhen
Time taken: 0.118 seconds, Fetched: 2 row(s)
hive> select person, size(address) from t3;
OK
tom 2
jerry 2
Time taken: 0.082 seconds, Fetched: 2 row(s)
create table if not exists t4(
person string,
info struct<age:int, city:string>
)
row format delimited
fields terminated by '|'
collection items terminated by ',';
tom|11,shenzhen
jerry|12,nanjing
load data local inpath '/home/hadoop/temp/202010/25/004.txt' into table t4;
hive> select * from t4;
OK
tom {"age":11,"city":"shenzhen"}
jerry {"age":12,"city":"nanjing"}
Time taken: 0.063 seconds, Fetched: 2 row(s)
hive> select person, info.city from t4;
OK
tom shenzhen
jerry nanjing
Time taken: 0.141 seconds, Fetched: 2 row(s)
CREATE TABLE union_test(foo UNIONTYPE<int, double, array<string>, struct<a:int,b:string>>);
SELECT foo FROM union_test;
{0:1}
{1:2.0}
{2:["three","four"]}
{3:{"a":5,"b":"five"}}
{2:["six","seven"]}
{3:{"a":8,"b":"eight"}}
{0:9}
{1:10.0}
如果您不想自己搭建kubernetes环境,推荐使用腾讯云容器服务TKE:无需自建,即可在腾讯云上使用稳定, 安全,高效,灵活扩展的 Kubernetes 容器平台;
如果您希望自己的镜像可以通过外网上传和下载,推荐腾讯云容器镜像服务TCR:像数据加密存储,大镜像多节点快速分发,跨地域镜像同步
微信搜索「程序员欣宸」,我是欣宸,期待与您一同畅游Java世界...
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。