文档章节

Hive 中的复合数据结构简介以及一些函数的用法说明

大数据之路
 大数据之路
发布于 2013/04/07 01:31
字数 2562
阅读 34833
收藏 11

目前 hive 支持的复合数据类型有以下几种:

map
(key1, value1, key2, value2, ...) Creates a map with the given key/value pairs
struct  
(val1, val2, val3, ...) Creates a struct with the given field values. Struct field names will be col1, col2, ...
named_struct  
(name1, val1, name2, val2, ...) Creates a struct with the given field names and values. (as of Hive 0.8.0)
array  
(val1, val2, ...) Creates an array with the given elements
create_union  
(tag, val1, val2, ...) Creates a union type with the value that is being pointed to by the tag parameter

一、map、struct、array 这3种的用法:

1、Array的使用

创建数据库表,以array作为数据类型
create table  person(name string,work_locations array<string>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',';
数据
biansutao beijing,shanghai,tianjin,hangzhou
linan changchu,chengdu,wuhan
入库数据
LOAD DATA LOCAL INPATH '/home/hadoop/person.txt' OVERWRITE INTO TABLE person;
查询
hive> select * from person;
biansutao       ["beijing","shanghai","tianjin","hangzhou"]
linan   ["changchu","chengdu","wuhan"]
Time taken: 0.355 seconds
hive> select name from person;
linan
biansutao
Time taken: 12.397 seconds
hive> select work_locations[0] from person;
changchu
beijing
Time taken: 13.214 seconds
hive> select work_locations from person;   
["changchu","chengdu","wuhan"]
["beijing","shanghai","tianjin","hangzhou"]
Time taken: 13.755 seconds
hive> select work_locations[3] from person;
NULL
hangzhou
Time taken: 12.722 seconds
hive> select work_locations[4] from person;
NULL
NULL
Time taken: 15.958 seconds

2、Map 的使用

创建数据库表
create table score(name string, score map<string,int>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':';
要入库的数据
biansutao '数学':80,'语文':89,'英语':95
jobs '语文':60,'数学':80,'英语':99
入库数据
LOAD DATA LOCAL INPATH '/home/hadoop/score.txt' OVERWRITE INTO TABLE score;
查询
hive> select * from score;
biansutao       {"数学":80,"语文":89,"英语":95}
jobs    {"语文":60,"数学":80,"英语":99}
Time taken: 0.665 seconds
hive> select name from score;
jobs
biansutao
Time taken: 19.778 seconds
hive> select t.score from score t;
{"语文":60,"数学":80,"英语":99}
{"数学":80,"语文":89,"英语":95}
Time taken: 19.353 seconds
hive> select t.score['语文'] from score t;
60
89
Time taken: 13.054 seconds
hive> select t.score['英语'] from score t;
99
95
Time taken: 13.769 seconds

3、Struct 的使用

创建数据表
CREATE TABLE test(id int,course struct<course:string,score:int>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',';
数据
1 english,80
2 math,89
3 chinese,95
入库
LOAD DATA LOCAL INPATH '/home/hadoop/test.txt' OVERWRITE INTO TABLE test;
查询
hive> select * from test;
OK
1       {"course":"english","score":80}
2       {"course":"math","score":89}
3       {"course":"chinese","score":95}
Time taken: 0.275 seconds
hive> select course from test;
{"course":"english","score":80}
{"course":"math","score":89}
{"course":"chinese","score":95}
Time taken: 44.968 seconds
select t.course.course from test t; 
english
math
chinese
Time taken: 15.827 seconds
hive> select t.course.score from test t;
80
89
95
Time taken: 13.235 seconds

4、数据组合 (不支持组合的复杂数据类型)

LOAD DATA LOCAL INPATH '/home/hadoop/test.txt' OVERWRITE INTO TABLE test;
create table test1(id int,a MAP<STRING,ARRAY<STRING>>)
row format delimited fields terminated by '\t' 
collection items terminated by ','
MAP KEYS TERMINATED BY ':';
1 english:80,90,70
2 math:89,78,86
3 chinese:99,100,82
LOAD DATA LOCAL INPATH '/home/hadoop/test1.txt' OVERWRITE INTO TABLE test1;

二、hive中的一些不常见函数的用法:

常见的函数就不废话了,和标准sql类似,下面我们要聊到的基本是HQL里面专有的函数,

hive里面的函数大致分为如下几种:Built-in、Misc.、UDF、UDTF、UDAF

我们就挑几个标准SQL里没有,但是在HIVE SQL在做统计分析常用到的来说吧。

1、array_contains (Collection Functions)

这是内置的对集合进行操作的函数,用法举例:

 

create EXTERNAL table IF NOT EXISTS userInfo (id int,sex string, age int, name string, email string,sd string, ed string)  ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' location '/hive/dw';

select * from userinfo where sex='male' and (id!=1 and id !=2 and id!=3 and id!=4 and id!=5) and age < 30;
select * from (select * from userinfo where sex='male' and !array_contains(split('1,2,3,4,5',','),cast(id as string))) tb1 where tb1.age < 30;

其中建表所用的测试数据你可以用如下链接的脚本自动生成:

http://my.oschina.net/leejun2005/blog/76631

2、get_json_object (Misc. Functions)

测试数据:

first {"store":{"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],"bicycle":{"price":19.951,"color":"red1"}},"email":"amy@only_for_json_udf_test.net","owner":"amy1"} third
first {"store":{"fruit":[{"weight":9,"type":"apple"},{"weight":91,"type":"pear"}],"bicycle":{"price":19.952,"color":"red2"}},"email":"amy@only_for_json_udf_test.net","owner":"amy2"} third
first {"store":{"fruit":[{"weight":10,"type":"apple"},{"weight":911,"type":"pear"}],"bicycle":{"price":19.953,"color":"red3"}},"email":"amy@only_for_json_udf_test.net","owner":"amy3"} third

create external table if not exists t_json(f1 string, f2 string, f3 string) row format delimited fields TERMINATED BY ' ' location '/test/json'
select get_json_object(t_json.f2, '$.owner') from t_json;
SELECT * from t_json where get_json_object(t_json.f2, '$.store.fruit[0].weight') = 9;
SELECT get_json_object(t_json.f2, '$.non_exist_key') FROM t_json;

这里尤其要注意UDTF的问题,官方文档有说明:

json_tuple
A new json_tuple() UDTF is introduced in hive 0.7. It takes a set of names (keys) and a JSON string, and returns a tuple of values using one function. This is much more efficient than calling GET_JSON_OBJECT to retrieve more than one key from a single JSON string. In any case where a single JSON string would be parsed more than once, your query will be more efficient if you parse it once, which is what JSON_TUPLE is for. As JSON_TUPLE is a UDTF, you will need to use the LATERAL VIEW syntax in order to achieve the same goal.

For example,

select a.timestamp, get_json_object(a.appevents, '$.eventid'), get_json_object(a.appenvets, '$.eventname') from log a;

should be changed to

select a.timestamp, b.*
from log a lateral view json_tuple(a.appevent, 'eventid', 'eventname') b as f1, f2;

UDTF(User-Defined Table-Generating Functions)  用来解决 输入一行输出多行(On-to-many maping) 的需求。  

通过Lateral view可以方便的将UDTF得到的行转列的结果集合在一起提供服务,因为直接在SELECT使用UDTF会存在限制,即仅仅能包含单个字段,不光是多个UDTF,仅仅单个UDTF加上其他字段也是不可以,hive提示在UDTF中仅仅能有单一的表达式。如下:
hive> select my_test(“abcef:aa”) as qq,’abcd’ from sunwg01;
FAILED: Error in semantic analysis: Only a single expression in the SELECT clause is supported with UDTF’s

使用Lateral view可以实现上面的需求,Lateral view语法如下:
lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (‘,’ columnAlias)*
fromClause: FROM baseTable (lateralView)*
hive> create table sunwg ( a array, b array )
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY ‘\t’
> COLLECTION ITEMS TERMINATED BY ‘,’;
OK
Time taken: 1.145 seconds
hive> load data local inpath ‘/home/hjl/sunwg/sunwg.txt’ overwrite into table sunwg;
Copying data from file:/home/hjl/sunwg/sunwg.txt
Loading data to table sunwg
OK
Time taken: 0.162 seconds
hive> select * from sunwg;
OK
[10,11] ["tom","mary"]
[20,21] ["kate","tim"]
Time taken: 0.069 seconds
hive>
> SELECT a, name
> FROM sunwg LATERAL VIEW explode(b) r1 AS name;
OK
[10,11] tom
[10,11] mary
[20,21] kate
[20,21] tim
Time taken: 8.497 seconds

hive> SELECT id, name
> FROM sunwg LATERAL VIEW explode(a) r1 AS id
> LATERAL VIEW explode(b) r2 AS name;
OK
10 tom
10 mary
11 tom
11 mary
20 kate
20 tim
21 kate
21 tim
Time taken: 9.687 seconds

3、parse_url_tuple

测试数据:

url1 http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1
url2 https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-getjsonobject
url3 https://www.google.com.hk/#hl=zh-CN&newwindow=1&safe=strict&q=hive+translate+example&oq=hive+translate+example&gs_l=serp.3...10174.11861.6.12051.8.8.0.0.0.0.132.883.0j7.7.0...0.0...1c.1j4.8.serp.0B9C1T_n0Hs&bav=on.2,or.&bvm=bv.44770516,d.aGc&fp=e13e41a6b9dab3f6&biw=1241&bih=589

create external table if not exists t_url(f1 string, f2 string) row format delimited fields TERMINATED BY ' ' location '/test/url';
SELECT f1, b.* FROM t_url LATERAL VIEW parse_url_tuple(f2, 'HOST', 'PATH', 'QUERY', 'QUERY:k1') b as host, path, query, query_id;

结果:

url1 facebook.com /path1/p.php k1=v1&k2=v2 v1
url2 cwiki.apache.org /confluence/display/Hive/LanguageManual+UDF NULL NULL
url3 www.google.com.hk / NULL NULL

4、explode

explode 是一个 hive 内置的表生成函数:Built-in Table-Generating Functions (UDTF),主要是解决 1 to N 的问题,即它可以把一行输入拆成多行,比如一个 array 的每个元素拆成一行,作为一个虚表输出。它有如下需要注意的地方:

Using the syntax "SELECT udtf(col) AS colAlias..." has a few limitations:
No other expressions are allowed in SELECT
SELECT pageid, explode(adid_list) AS myCol... is not supported
UDTF's can't be nested
SELECT explode(explode(adid_list)) AS myCol... is not supported
GROUP BY / CLUSTER BY / DISTRIBUTE BY / SORT BY is not supported
SELECT explode(adid_list) AS myCol ... GROUP BY myCol is not supported

从上面的原理与语法上可知,

  • select 列中不能 udtf 和其它非 udtf 列混用,

  • udtf 不能嵌套,

  • 不支持 GROUP BY / CLUSTER BY / DISTRIBUTE BY / SORT BY

  • 还有 select 中出现的 udtf 一定需要列别名,否则会报错:

SELECT explode(myCol) AS myNewCol FROM myTable;
SELECT explode(myMap) AS (myMapKey, myMapValue) FROM myMapTable;
SELECT posexplode(myCol) AS pos, myNewCol FROM myTable;

5、lateral view

lateral view 是Hive中提供给UDTF的conjunction,它可以解决UDTF不能添加额外的select列的问题。当我们想对hive表中某一列进行split之后,想对其转换成1 to N的模式,即一行转多列。hive不允许我们在UDTF函数之外,再添加其它select语句。

如下,我们想将登录某个游戏的用户id放在一个字段user_ids里,对每一行数据用UDTF后输出多行。

select game_id, explode(split(user_ids,'\\[\\[\\[')) as user_id   from login_game_log  where dt='2014-05-15' ;
FAILED: Error in semantic analysis: UDTF's are not supported outside the SELECT clause, nor nested in expressions。

提示语法分析错误,UDTF不支持函数之外的select 语句,如果我们想支持怎么办呢?接下来就是Lateral View 登场的时候了。

Lateral view 其实就是用来和像类似explode这种UDTF函数联用的。lateral view 会将UDTF生成的结果放到一个虚拟表中,然后这个虚拟表(1 to N)会和输入行即每个game_id进行join 来达到连接UDTF外的select字段的目的(源表和拆分的虚表按行做行内 1 join N 的直接连接),这也是为什么 LATERAL VIEW udtf(expression) 后面需要表别名和列别名的原因。

Lateral View Syntax

lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)*

fromClause: FROM baseTable (lateralView)*

可以看出,可以在2个地方用Lateral view:

  • 在udtf前面用

  • 在from baseTable后面用

例如:

pageid adid_list

front_page   [1, 2, 3]

contact_page [3, 4, 5]

SELECT pageid, adid
FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;

 

pageid               adid

front_page         1

front_page         2

front_page         3

contact_page     3

contact_page     4

contact_page     5

From语句后可以跟多个Lateral View。

A FROM clause can have multiple LATERAL VIEW clauses. Subsequent LATERAL VIEWS can reference columns from any of the tables appearing to the left of the LATERAL VIEW.

给定数据:

Array<int> col1     Array<string> col2

[1, 2]                       [a", "b", "c"]

[3, 4]                       [d", "e", "f"]

转换目标:

想同时把第一列和第二列拆开,类似做笛卡尔乘积。

我们可以这样写:

SELECT myCol1, myCol2 FROM baseTable
LATERAL VIEW explode(col1) myTable1 AS myCol1
LATERAL VIEW explode(col2) myTable2 AS myCol2;

 

还有一种情况,如果UDTF转换的Array是空的怎么办呢?

在Hive0.12里面会支持outer关键字,如果UDTF的结果是空,默认会被忽略输出。

如果加上outer关键字,则会像left outer join 一样,还是会输出select出的列,而UDTF的输出结果是NULL。

总结:

  • Lateral View通常和UDTF一起出现,为了解决UDTF不允许在select字段的问题。

  • Multiple Lateral View可以实现类似笛卡尔乘积。

  • Outer关键字可以把不输出的UDTF的空结果,输出成NULL,防止丢失数据。

三、ref:

http://blog.csdn.net/wf1982/article/details/7474601
http://www.cnblogs.com/ggjucheng/archive/2013/01/08/2850797.html
http://www.oratea.net/?p=650
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-parseurltuple
https://cwiki.apache.org/confluence/display/Hive/Tutorial

http://blog.csdn.net/inte_sleeper/article/details/7196114  hive lateral view语句:列拆分成行

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-explode

http://blog.csdn.net/oopsoom/article/details/26001307    Lateral View用法 与 Hive UDTF explode

http://bit.ly/2bDuVxS    助力大数据的复杂统计分析-Hive窗口函数

© 著作权归作者所有

大数据之路
粉丝 1605
博文 514
码字总数 333288
作品 0
武汉
架构师
私信 提问
加载中

评论(4)

大数据之路
大数据之路 博主

引用来自“山狼”的评论

引用来自“xrzs”的评论

引用来自“山狼”的评论

你好,想请教你一个问题
设置row format delimited fields terminated by '#' 但是
insert overwrite directory local 'path' select * test;这样输出的文件分割符还是'\001'
使用自定义的inputformat,outputformat时 inputformat 正常使用,但是 outputformat 始终没见调用,这是什么问题?

不知道你是这意思不:
hadoop fs -cat /tmp/6/*
1 2
3 4

create external table if not exists t06_1(f1 string, f2 string) row format delimited fields TERMINATED BY '\t' location '/tmp/6/';
create table t06_2(f1 string, f2 string) row format delimited fields TERMINATED BY '#';
insert overwrite table t06_2 select * from t06_1;

hadoop fs -cat /hive/warehouse/t06_2/*
1#2
3#4
请参考:
https://issues.apache.org/jira/browse/HIVE-634
http://grokbase.com/t/hive/user/1147dsszke/text-output-separator-for-hive-output

我有原始文件:
1#2
3#4
insert overwrite directory local 'path' select * test; 导出后的文件:
1[]2 (分割符是乱码)
3[]4
我想要的输出结果是:
1#2
3#4

看我给的链接。
山狼

引用来自“xrzs”的评论

引用来自“山狼”的评论

你好,想请教你一个问题
设置row format delimited fields terminated by '#' 但是
insert overwrite directory local 'path' select * test;这样输出的文件分割符还是'\001'
使用自定义的inputformat,outputformat时 inputformat 正常使用,但是 outputformat 始终没见调用,这是什么问题?

不知道你是这意思不:
hadoop fs -cat /tmp/6/*
1 2
3 4

create external table if not exists t06_1(f1 string, f2 string) row format delimited fields TERMINATED BY '\t' location '/tmp/6/';
create table t06_2(f1 string, f2 string) row format delimited fields TERMINATED BY '#';
insert overwrite table t06_2 select * from t06_1;

hadoop fs -cat /hive/warehouse/t06_2/*
1#2
3#4
请参考:
https://issues.apache.org/jira/browse/HIVE-634
http://grokbase.com/t/hive/user/1147dsszke/text-output-separator-for-hive-output

我有原始文件:
1#2
3#4
insert overwrite directory local 'path' select * test; 导出后的文件:
1[]2 (分割符是乱码)
3[]4
我想要的输出结果是:
1#2
3#4
大数据之路
大数据之路 博主

引用来自“山狼”的评论

你好,想请教你一个问题
设置row format delimited fields terminated by '#' 但是
insert overwrite directory local 'path' select * test;这样输出的文件分割符还是'\001'
使用自定义的inputformat,outputformat时 inputformat 正常使用,但是 outputformat 始终没见调用,这是什么问题?

不知道你是这意思不:
hadoop fs -cat /tmp/6/*
1 2
3 4

create external table if not exists t06_1(f1 string, f2 string) row format delimited fields TERMINATED BY '\t' location '/tmp/6/';
create table t06_2(f1 string, f2 string) row format delimited fields TERMINATED BY '#';
insert overwrite table t06_2 select * from t06_1;

hadoop fs -cat /hive/warehouse/t06_2/*
1#2
3#4
请参考:
https://issues.apache.org/jira/browse/HIVE-634
http://grokbase.com/t/hive/user/1147dsszke/text-output-separator-for-hive-output
山狼
你好,想请教你一个问题
设置row format delimited fields terminated by '#' 但是
insert overwrite directory local 'path' select * test;这样输出的文件分割符还是'\001'
使用自定义的inputformat,outputformat时 inputformat 正常使用,但是 outputformat 始终没见调用,这是什么问题?
Hive从入门到实战视频教程【40讲全】

1、Hive是什么,Hive的体系结构,Hive和Hadoop的关系 2、Hive的元数据存储、Hive的数据存储、Hive和RDBMS的区别 3、JDK安装 4、Hadoop集群搭建-1 5、Hadoop集群搭建-2 6、Hadoop集群搭建-3 ...

tom先生
2014/11/26
48
0
MySQL学习笔记-自定义函数

MySQL学习笔记-自定义函数 1.自定义函数简介 自定义函数:用户自定义函数(user-defined function,UDF)是一种对MySQL扩展的途径,其用法与内置函数相同 自定义函数的两个必要条件:(1)参数...

谢育政
2017/05/24
0
0
sqoop简介以及架构介绍

本篇文章在具体介绍Sqoop之前,先给大家用一个流程图介绍Hadoop业务的开发流程以及Sqoop在业务当中的实际地位。 如上图所示:在实际的业务当中,我们首先对原始数据集通过MapReduce进行数据清...

qi49125
2017/11/15
0
0
开源大数据处理工具汇总(上)

本文一共分为上下两部分。我们将针对大数据开源工具不同的用处来进行分类,并且附上了官网和部分下载链接,希望能给做大数据的朋友做个参考。下面是第一部分。 查询引擎 一、Phoenix 贡献者:...

openthings
2016/01/05
88
0
Hive分析函数row_number()

业务背景 最近有一个需求是给用户浏览的商品进行评分, 然后将商品评分按降序排序, 最后取 Top 50 推荐给终端用户。评分的计算公式为: 该商品一个月内的购买次数 / 该商品两个月内的浏览次数。...

JackieYeah
2016/05/25
4K
0

没有更多内容

加载失败,请刷新页面

加载更多

还为PDF转Word抓狂?以下神器让你在职场倍受欢迎!

身在职场的你,是否一直在琢磨:如何能让自己在公司更受欢迎?如何才能在办公室里混个好人缘?如何在同事圈里留个好印象?其实,想要让自己成为受欢迎的人,只要让自己成为大家需要的人不就行...

foxit2world
28分钟前
7
0
AndServer+Service打造Android服务器实现so文件调用

so 文件调用 随着 Android 移动安全的高速发展,不管是为了执行效率还是程序的安全性等,关键代码下沉 native 层已成为基本操作。 native 层的开发就是通指的 JNI/NDK 开发,通过 JNI 可以实...

夜幕NightTeam
29分钟前
4
0
Docker下kafka学习三部曲之二:本地环境搭建

在上一章《 Docker下kafka学习,三部曲之一:极速体验kafka》中我们快速体验了kafka的消息分发和订阅功能,但是对环境搭建的印象仅仅是执行了几个命令和脚本,本章我们通过实战来学习如何编写...

程序员欣宸
30分钟前
4
0
萌新推荐!不再为Excel转换PDF发愁,Aspose.Cells for .NET一步到位!

Aspose.Cells for .NET(点击下载)是Excel电子表格编程API,可加快电子表格管理和处理任务,支持构建具有生成,修改,转换,呈现和打印电子表格功能的跨平台应用程序。 将Excel工作簿转换为...

mnrssj
30分钟前
6
0
对于绘画小白怎么画制服?该注意什么?

怎样制作学生服装?想必绘画初学者们常常会想的问题吧,不知道怎样才能画好人物的衣服,别着急,今日就在这儿讲一些关于如何绘画学生衣服校服的教程给我们!期望能够帮到你们! 轻便西装是不...

热爱画画的我
36分钟前
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部