hive的Query和Insert，Group by，Aggregations（聚合）操作

1、Query

（1）分区查询

在查询的过程中，采用那个分区来查询是通过系统自动的决定，但是必须是在分区列上基于where子查询。

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';

（2）、join 从集合的角度上来理解

在mysql中join的查询分为两类：（1）内联结查询（2）外联结查询；hive当然也是有两种join的查询，并且其join的查询方式跟mysql相当的类似。下面通过案例详细介绍

首先创建两个表，并插入几天记录来作为测试：

create table jn1 （name String）；

insert into table jn1 values("java1","java2","blue");

hive的Query和Insert，Group by，Aggregations（聚合）操作-LMLPHP

create table jn2 （name String）；

insert into table jn2 values("mysql1"),("mysql2"),("blue");

hive的Query和Insert，Group by，Aggregations（聚合）操作-LMLPHP

　　1）内联结查询 (可以理解为求两个表的交集)

select jn1.*,jn2.* from jn1 JOIN jn2 ON(jn1.name=jn2.name)

所得结果是：

hive的Query和Insert，Group by，Aggregations（聚合）操作-LMLPHP

　　2）外联结查询

外联结查询与可以分为三种查询方式：1）左外连接， 2）右外连接，3）全外联结查询

同样采用上面的两个表来进行测试。

　　i）左外连接查询 (可以理解成表a以及表a和表b的交集的集合)

　　 select jn1.* ,jn2.* from jn1 LEFT JOIN jn2 ON(jn1.name=jn2.name);

　　或者

　　 select a.* ,b.* from jn1 a LEFT JOIN jn2 b ON(a.name=b.name);

　　 hive的Query和Insert，Group by，Aggregations（聚合）操作-LMLPHP

　　ii）右外连接查询(可以理解成表b以及表a和表b的交集的集合)

　　 select jn1.* ,jn2.* from jn1 RIGHT JOIN jn2 ON(jn1.name=jn2.name);

　　或者

　　 select a.* ,b.* from jn1 a RIGHT JOIN jn2 b ON(a.name=b.name);

　　 hive的Query和Insert，Group by，Aggregations（聚合）操作-LMLPHP

　　iii）全外联结查询(表a和表b的并集)

　　select jn1.* ,jn2.* from jn1 FULL JOIN jn2 ON(jn1.name=jn2.name);

　　或者

　　select a.* ,b.* from jn1 a FULL JOIN jn2 b ON(a.name=b.name);

　　 hive的Query和Insert，Group by，Aggregations（聚合）操作-LMLPHP

2、Group by 的查询

为了统计每一个性别不同用户的总数的查询语句可以是：

SELECT pv_users.gender, count (DISTINCT pv_users.userid) FROM pv_users GROUP BY pv_users.gender;

3、聚合计算

（1）单个聚合情况

SELECT pv_users.gender, count (DISTINCT pv_users.userid) FROM pv_users GROUP BY pv_users.gender;

（2）多个聚合可以同时计算，但是必须要有不同的列名

Multiple aggregations can be done at the same time, however, no two aggregations can have different DISTINCT columns .e.g while the following is possible

　　SELECT pv_users.gender, count(DISTINCT pv_users.userid), count(*), sum(DISTINCT pv_users.userid)

　　FROM pv_users

　 GROUP BY pv_users.gender;

如下面的多个聚合就不行：

SELECT pv_users.gender, count(DISTINCT pv_users.userid), count(DISTINCT pv_users.ip) //这两个查询之后列名重复，所以不允许

FROM pv_users

GROUP BY pv_users.gender;

4、Insert

（1）将数据插入到hive中的表中

FROM pv_users
INSERT OVERWRITE TABLE pv_gender_sum
SELECT pv_users.gender, count_distinct(pv_users.userid)
GROUP BY pv_users.gender

（2）将数据插入到hdfs中的文件中

　　INSERT OVERWRITE DIRECTORY '/user/data/tmp/pv_age_sum'
SELECT pv_users.age, count_distinct(pv_users.userid)
GROUP BY pv_users.age;

（3）动态分区插入

我们可以实现分区插入如下实现。

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';

以上实现的分区插入，存在一个问题：当增加某个国家时，那么需要在增加一个insert的语句，这样极其的不方便，为此hive提供了一个动态确定分区的方式来实现动态分区插入，只需要一条语句即可：

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

（4）插入数据到本地文件中

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

5、Union （连接）

它的意思是将两次或者多次的查询结果连接起来（但是它没有去重的功能）

select * from jn1 union all select * from jn2;

结果如下：

hive的Query和Insert，Group by，Aggregations（聚合）操作-LMLPHP