Hive学习指南（四）

查询和插入数据

Hive查询操作参见文档Select，插入操作参见文档Inserting 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不一样，这里总是将数据插入到user_active表中。稍后会说明用户如何检查这些结果，并且将结果转存到本地文件中。可以在Beeline和[Hive CLI]中运行下面的查询：

SELECT user.*
FROM user
WHERE user.active = 1;

结果会重写为一些临时文件并在Hive客户端进行展示。

基于分区的查询

查询中使用哪些分区是由系统根据where语句中分区列的条件决定的。例如，为了获取所有03/2008从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 DATETIME, country STRING)。

连接

为了获取2008-03-03的page_view人员统计分析（按照性别），可以使用userid列连接page_view表和user表。使用下面查询实现：

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（左保留，右保留或双向保留）关键字。例如，对上面的查询进行全外连接，如下：

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。建议将最大的表放在连接的最右边，这样性能最好。

聚合

按照性别计算不同用户数量，如下：

INSERT OVERWRITE TABLE pv_gender_sum
SELECT pv_users.gender, count (DISTINCT pv_users.userid)
FROM pv_users
GROUP BY pv_users.gender;

可以同时进行多个聚合，但是不能对不同列进行聚合，下面代码是允许的：

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;

下面查询是不允许的：

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;

多表/文件插入

聚合或者简单查询的输出可以进一步发送到多个表或者hadoop dfs文件。如下：

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;

第一个insert语句将结果插入到Hive表中，第二个将结果存储到hadoop dfs文件中。

插入到本地文件

将结果输出到本地文件，这样可以使用excel等工具查看文件。如下：

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/pv_gender_sum'
SELECT pv_gender_sum.*
FROM pv_gender_sum;

抽样

抽样语句让用户可以写查询抽样部分数据。目前抽样是在列上进行的，在CREATE TABLE中的CLUSTERED BY语句中指定。下面的示例选择了pv_gender_sum表的第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是表创建时指定的桶数量。针对上面的示例，下面的语句会选择第3个和第19个桶。

TABLESAMPLE(BUCKET 3 OUT OF 16)

下面的语句会选择第3个桶的一半。

TABLESAMPLE(BUCKET 3 OUT OF 64 ON userid)

Union All

Hive SQL支持union all，例如，我们有两个表，一个跟踪用户发布视频，一个跟踪用户发布评论，下面的查询将union all的结果和user表进行连接：

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;

select语句获取了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;

---