本文共 1767 字，大约阅读时间需要 5 分钟。

1.explode

--1.直接使用explodehive (hive)> select * from person;OKperson.id	person.name	person.likes1	wang	["lol","data2"]2	tom	["跳槽"]3	Jack	["得分","阿斯蒂芬","大师傅"]4	jim	["登陆","饿啊分"]Time taken: 0.13 seconds, Fetched: 4 row(s)hive (hive)> select explode(likes) from person;OKcolloldata2跳槽得分阿斯蒂芬大师傅登陆饿啊分Time taken: 0.109 seconds, Fetched: 8 row(s)

--2.explode和lateral view 结合使用,数组hive (hive)> select name,hobby from person lateral view explode(likes) likes as hobby;OKname	hobbywang	lolwang	data2tom	跳槽Jack	得分Jack	阿斯蒂芬Jack	大师傅jim	登陆jim	饿啊分Time taken: 0.152 seconds, Fetched: 8 row(s)hive (hive)> select name,hobby from person  lateral view explode(likes) likes as hobby where name='wang';OKname	hobbywang	lolwang	data2Time taken: 0.274 seconds, Fetched: 2 row(s)

--3.explode中拆分map类型的写法hive (hive)> select * from person1;OKperson1.id	person1.name	person1.desc1	wang	{"爱好":"lol","形象":"好"}2	tom	{"爱好":"跳槽","形象":"不会"}3	Jack	{"得分":null,"阿斯蒂芬":null,"大师傅":null}4	jim	NULLTime taken: 0.139 seconds, Fetched: 4 row(s)hive (hive)> select id,key ,value from person1 lateral view explode(desc) descmap as key,value;OKid	key	value1	爱好	lol1	形象	好2	爱好	跳槽2	形象	不会3	得分	NULL3	阿斯蒂芬	NULL3	大师傅	NULLTime taken: 0.213 seconds, Fetched: 7 row(s)

2.parse_url_tuple

建表create table urltab(url string)ROW FORMAT  DELIMITED FIELDS TERMINATED BY ','STORED AS TEXTFILE;测试数据https://www.sogou.com/sogou?prs=5&rfg=1导入数据load data local inpath '/home/wangfutai/a/url.txt'  INTO TABLE hive.urltab;--1.查询结果hive (hive)> select parse_url_tuple(url,'HOST','PATH','QUERY') as(host,path,query) from urltab;OKhost	path	querywww.sogou.com	/sogou	prs=5&rfg=1Time taken: 1.667 seconds, Fetched: 1 row(s)--2.parse_urlhive (hive)> select parse_url(url,'QUERY','prs') from urltab;OK_c05Time taken: 0.194 seconds, Fetched: 1 row(s)

转载地址：http://ugjxi.baihongyu.com/