- Simple Query
- Partition Based Query
- Joins
- Aggregations
- Multi Table/File Inserts
- Dynamic-Partition Insert
- Inserting into Local Files
- Sampling
- Union All
- Array Operations
- Map (Associative Arrays) Operations
- Custom Map/Reduce Scripts
- Co-Groups
Hive查询操作在文档Select,插入操作在文档insert data into Hive Tables from queries和writing data into the filesystem from queries。
简单的查询
对于所有的活跃用户,可以使用以下查询格式:
INSERT OVERWRITE TABLE user_active
SELECT user.*
FROM user
WHERE user.active = 1;
注意:不像SQL,我们老是插入结果到表中。随后我们会描述,用户如何检查这些结果,甚至把结果导出到一个本地文件。你也可以在Beeline或HiveCLI执行以下查询:
SELECT user.*
FROM user
WHERE user.active = 1;
这在内部将会重写到一些临时文件,并在Hive客户端显示。
基于查询的分区
在一个查询中,要使用什么分区,是由系统根据where
在分区列上条件自动的决定。例如,为了获取所有2008年3月份,从域名xyz.com
过来的page_views,可以这么写查询:
INSERT OVERWRITE TABLE xyz_com_page_views
SELECT page_views.*
FROM page_views
WHERE page_views.date >= '2008-03-01' AND page_views.date <= '2008-03-31' AND
page_views.referrer_url like '%xyz.com';
注意:在这里使用的page_views.date
是用PARTITIONED BY(date DATATIME, country STRING)
定义的.如果你的分区命名不一样,那么不要指望.date
能够做你想做的事情,即无法获得分区的优势。
连接
表的连接可以使用以下命令:
INSERT OVERWRITE TABLE pv_users
SELECT pv.*, u.gender, u.age
FROM user u JOIN page_view pv ON (pv.userid = u.id)
WHERE pv.date = '2008-03-03';
想实现外连接,用户可以使用LEFT OUTER
,RIGHT OUTER
或FULL OUTER
关键词来指示不同的外连接(左保留,右保留或两端都保留)。例如,想对上面的查询做一个FULL OUTER
,相应的语法可以像下面这样:
INSERT OVERWRITE TABLE pv_users
SELECT pv.*, u.gender, u.age
FROM user u FULL OUTER JOIN page_view pv ON (pv.userid = u.id)
WHERE pv.date = '2008-03-03';
为了检查key在另外一个表中是否存在,用户可以使用LEFT SEMI JOIN
,正如以下例子一样:
INSERT OVERWRITE TABLE pv_users
SELECT u.*
FROM user u LEFT SEMI JOIN page_view pv ON (pv.userid = u.id)
WHERE pv.date = '2008-03-03';
为了连接多个表,可以使用以下语法:
INSERT OVERWRITE TABLE pv_friends
SELECT pv.*, u.gender, u.age, f.friends
FROM page_view pv JOIN user u ON (pv.userid = u.id) JOIN friend_list f ON (u.id = f.uid)
WHERE pv.date = '2008-03-03';
注意:Hive只支持equi-joins#Equi-join)。所以,把最大的表放在join
的最右边,可以得到最好的性能。
聚合
统计用户每个性别的人数,可以使用以下查询:
INSERT OVERWRITE TABLE pv_gender_sum
SELECT pv_users.gender, count (DISTINCT pv_users.userid)
FROM pv_users
GROUP BY pv_users.gender;
可以同时做多个聚合,然而,两个聚合函数不能同时用DISTINCT
作用于不同的列,以下情况是可以的(DISTINCT
作用于相同列):
INSERT OVERWRITE TABLE pv_gender_agg
SELECT pv_users.gender, count(DISTINCT pv_users.userid), count(*), sum(DISTINCT pv_users.userid)
FROM pv_users
GROUP BY pv_users.gender;
然而,以下情况(DISTINCT
作用于不同的列)是不允许的:
INSERT OVERWRITE TABLE pv_gender_agg
SELECT pv_users.gender, count(DISTINCT pv_users.userid), count(DISTINCT pv_users.ip)
FROM pv_users
GROUP BY pv_users.gender;
多表/文件插入
聚合或简单查询的输出可以插入到多个表中,或者甚至是HDFS文件(能够使用HDFS工具进行操纵)。例如,如果沿用前面的“性别分类”,例子如下:
FROM pv_users
INSERT OVERWRITE TABLE pv_gender_sum
SELECT pv_users.gender, count_distinct(pv_users.userid)
GROUP BY pv_users.gender
INSERT OVERWRITE DIRECTORY '/user/data/tmp/pv_age_sum'
SELECT pv_users.age, count_distinct(pv_users.userid)
GROUP BY pv_users.age;
第一个插入语句将结果插入到Hive表中,而第二个插入语句是将结果写到HDFS文件。
动太分区插入
在前面的例子中,我们知道,在插入语句中,只能有一个分区。如果我们想加载到多个分区,我们必须像以下描述来使用多条插入语句:
FROM page_view_stg pvs
INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country='US')
SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip WHERE pvs.country = 'US'
INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country='CA')
SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip WHERE pvs.country = 'CA'
INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country='UK')
SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip WHERE pvs.country = 'UK';
为了加载数据到全部的country
分区到指定的日期。我们必须在输入数据中为每个country
增加一条插入语句。这是非常不方便的,因为我们需要提前创建且知道已存在哪些country
分区列表。如果哪天这些country
列表变了,我们必须修改我们的插入语句,也应该创建相应的分区。这也是非常低效的,因为每个插入语句可能都是转换成一个MapReduce作业。
动态分区插入(Dynamic-partition insert)(或multi-partition插入)就是为了解决以上问题而设计的,它通过动态地决定在扫描数据的时候,哪些分区应该创建和填充。这个新的特征是在版本0.6.0加入的。在动态分区插入中,输入列被评估,这行应该插入到哪个分区。如果分区没有创建,它将自动创建这个分区。使用这个特征,我们仅仅需要插入语桀犬吠尧来创建和填充所有需要的分区。另外,因为只有一个插入语句,相应的也只有一个MapReduce作业。相比多个插入语句的情况,这将显著地提高性能且降低Hadoop集群负载。
以下是使用一个插入语句,加载数据到所有country
分区的例子:
FROM page_view_stg pvs
INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country)
SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip, pvs.country
与多条插入语句相比,动态分区插入有一些语法上的不同:
country
出现在PARTITION
后面,但是没有具体的值。这种情况,country
就是一个动态分区列。另一方面,dt
有一个值,这意味着它是一个静态的分区列。如果一个列是动态分区列,它的值将会使用输入列的值。目前,我们仅仅允许在分区条件的最后一列放置动态分区列,因为分区列的顺序,指示了它的层级次序(意味着dt
是根分区,country
是子分区)。我们不能这样指定分区(dt,country='US'),因为这表示,我们需要更新所有的日期的分区且它的country
子分区是‘US’。一个额外的
pvs.country
列被加入在查询语句中。这对动态分区列来说,相当于输入列。注意:对于静态分区列,我们不需要添加一个输入列,因为在PARTITION语句中,它的值已经知道。注意:动态分区列的值(不是名字)查出来是有序的,且是放在select语句的最后。
动态分区插入的语义:
- 对于动态分区列,当已经此分区时,(例如,
country='CA'
已存在dt
根分区下面)如果动态分区插入与输入数据中相同的值('CA'),它将会被重写(overwritten)。
插入到本地文件
在某些场合,我们需要把输出写到一个本地文件,以便于能用excel表格打开。这可以使用以下命令:
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/pv_gender_sum'
SELECT pv_gender_sum.*
FROM pv_gender_sum;
抽样
抽样语句允许用户对数据抽样查询,而不是全表查询。当前,抽样是对那些在CREATE TABLE
语句的CLUSTERED BY
修饰的列上。以下例子,我们从表pv_gender_sum
表中的32个桶中,选择第3个桶。
INSERT OVERWRITE TABLE pv_gender_sum_sample
SELECT pv_gender_sum.*
FROM pv_gender_sum TABLESAMPLE(BUCKET 3 OUT OF 32);
通常,TABLESAMPLE
的语法像这样:
TABLESAMPLE(BUCKET x OUT OF y)
这个y
必须是桶的数量的因子或倍数,桶的数量是在创建表的时候指定的。抽样所选的桶由桶大小,y和x共同决定。如果y和桶大小相等,则抽样所选的桶是x对y的求模结果。
TABLESAMPLE(BUCKET 3 OUT OF 16)
这将抽样第3个和第19个桶。桶的编号从0开始。
tablesample
语句的另一方面:
TABLESAMPLE(BUCKET 3 OUT OF 64 ON userid)
这将抽取第3个桶的一半。
union all
这个语言也支持union all
,如果假设我们有两个不同的表,分别用来记录用户发布的视频和用户发布的评论,以下例子是一个union all 的结果与用户表再连接的查询:
INSERT OVERWRITE TABLE actions_users
SELECT u.id, actions.date
FROM (
SELECT av.uid AS uid
FROM action_video av
WHERE av.date = '2008-06-03'
UNION ALL
SELECT ac.uid AS uid
FROM action_comment ac
WHERE ac.date = '2008-06-03'
) actions JOIN users u ON(u.id = actions.uid);
数组操作
表的数组列可以这样:
CREATE TABLE array_table (int_array_column ARRAY<INT>);
假设pv.friends 是类型ARRAY<INT>
(也就是一个整型数组),用户可以通过索引号获取数组中特定的元素,如下:
SELECT pv.friends[2]
FROM page_views pv;
这个查询得到的是pv.friends里的第三个元素。
用户也可以使用函数size
来获取数组的长度,如下:
SELECT pv.userid, size(pv.friends)
FROM page_view pv;
Map(关联数组)操作
Map提供了类似于关联数组的集合。这样的结构不仅可以由程序创建。我们也将很快可以继承这个。假设pv.properties是类型map<String,String>
,如下:
INSERT OVERWRITE page_views_map
SELECT pv.userid, pv.properties['page type']
FROM page_views pv;
这将查询表page_views
的‘page_type‘属性。
与数组相似,也可以使用函数size
来获取map的大小:
SELECT size(pv.properties)
FROM page_view pv;
定制Map/Reduce脚本
通过使用Hive语言原生支持的特征,用户可以插入他们自己定制的mapper和reducer在数据流中。例如,要运行一个定制的mapper脚本script-map_script
和reducer脚本script-reduce_script
),用户可以执行以下命令,使用TRANSFORM
来嵌入mapper和reducer脚本。
注意:在执行用户脚本之前,表的列会转换成字符串,且由TAB
分隔,用户脚本的标准输出将会被作为以TAB
分隔的字符串列。用户脚本可以输出调试信息到标准错误输出,这个信息也将显示hadoop的详细任务页面上。
FROM (
FROM pv_users
MAP pv_users.userid, pv_users.date
USING 'map_script'
AS dt, uid
CLUSTER BY dt) map_output
INSERT OVERWRITE TABLE pv_users_reduced
REDUCE map_output.dt, map_output.uid
USING 'reduce_script'
AS date, count;
map脚本样本(weekday_mapper.py)
import sys
import datetime
for line in sys.stdin:
line = line.strip()
userid, unixtime = line.split('\t')
weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()
print ','.join([userid, str(weekday)])
当然,对于那些常见的select转换,MAP和REDUCE都是“语法糖”。内部查询也可以写成这样:
SELECT TRANSFORM(pv_users.userid, pv_users.date) USING 'map_script' AS dt, uid CLUSTER BY dt FROM pv_users;
Co-Groups
在使用map/reduce的群体中,cogroup
是相当常见的操作,它是将来自多个表的数据发送到一个定制的reducer,使得行由表的指定列的值进行分组。在Hive的查询语言中,可以使用以下方式,通过使用union all
和cluster by
来实现此功能。假设我们想对来自表actions_video
和action_comment
的行对uid
列进行分组,且需要发送他们到reducer_script
定制的reducer,可以使用以下语法:
FROM (
FROM (
FROM action_video av
SELECT av.uid AS uid, av.id AS id, av.date AS date
UNION ALL
FROM action_comment ac
SELECT ac.uid AS uid, ac.id AS id, ac.date AS date
) union_actions
SELECT union_actions.uid, union_actions.id, union_actions.date
CLUSTER BY union_actions.uid) map
INSERT OVERWRITE TABLE actions_reduced
SELECT TRANSFORM(map.uid, map.id, map.date) USING 'reduce_script' AS (uid, id, reduced_val);