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

results matching ""

    No results matching ""