Hive编程指南-学习笔记（四） 查询

一、SELECT ... FROM ...语句

CREATE TABLE employees(  
name         STRING,
salary       FLOAT,
subordinates ARRAY<STRING>,
deductions   MAP<STRING,FLOAT>,
address      STRUCT<street:STRING,city:STRING,state:STRING,zip:INT>)
PARTITIONED BY (country STRING,state STRING); 

1、当选择的列是集合数据类型时（ARRAY、MAP、STRUCT），Hive会使用JSON语法用于输出。

ARRAY:

hive> SELECT name,subordinates FROM employees;
John Doe    ["Mary Smith","Todd Jones"]
...

hive> SELECT name,subordinates[0] FROM employees; #使用索引查询集合数据类型中的元素
John Doe    Mary Smith #查询不存在的元素返回NULL，提取出的STRING值不加引号

MAP：

hive> SELECT name,deductions FROM employees;
John Doe    {"Federal Taxes":0.2,"State Taxes":0.05}
...

hive> SELECT name,deductions["State Taxes"] FROM employees; #使用“键值”查询集合数据类型中的元素
John Doe    0.05

STRUCT：

hive> SELECT name,address FROM employees;
John Doe    {"Street":"1 Michigan Ave.","city":"Chicago",...}
...

hive> SELECT name,address.city FROM employees; #使用“点”符号查询集合数据类型中的元素
John Doe    Chicago

2、使用正则表达式来指定列

SELECT symbol,'price.*' FROM stocks; #选择symbol列和所有列名开头为price的列

3、使用列值进行计算

SELECT upper(name)
        ,salary
        ,deductions["Federal Taxes"]
        ,round(salary*(1-deductions["Federal Taxes"]))
FROM employees;

4、算术运算符

5、LIMIT语句

LIMIT子句用于限制返回的行数。

6、嵌套SELECT语句

FROM(
  SELECT upper(name)
        ,salary
        ,deductions["Federal Taxes"] as fed_taxes
        ,round(salary*(1-deductions["Federal Taxes"])) as salary_minus_fed_taxes
  FROM employees) e
SELECT e.name,e.salary_minus_fed_taxes
WHERE e.salary_minus_fed_taxes>7000;

7、CASE...WHEN...THEN...END

和if条件语句类似，用来处理单个列的查询结果

8、Hive避免使用MapReduce任务

（1）SELECT * FROM employees; 读取employees对应的存储目录下的文件，然后输出道控制台

（2）WHERE语句中过滤条件只是分区字段，无需MapReduce过程，

SELECT * FROM employees
WHERE country='US' AND state='CA';

（3）设置属性值，Hive尝试使用本地模式执行其他的操作

SET hive.exec.mode.local.auto=true; #最好增加到$HOME/.hiverc配置文件中

二、WHERE语句

1、不能在WHERE语句中使用列别名，可以采用嵌套的SELECT语句解决。

2、浮点数的比较问题，可将数字转换成FLOAT类型，如cast(0.2 AS FLOAT)

3、RLIKE，通过JAVA的正则表达式这个更强大的语言来指定匹配条件

#查找街道名称中含有Chicago或Ontario的记录
SELECT name,address.street
FROM employees
WHERE address.street RLIKE '.*(Chicago|Ontario).*'; 
#点号表示和任意字符匹配，星号表示重复“左边的字符串”零次到无数次，表达式(x|y)表示x或者y

可以通过LIKE子句改写为：

SELECT name,address.street
FROM employees
WHERE address.street LIKE '%Chicago%' OR address.street LIKE '%Ontario%'; 

三、GROUP BY 语句

GROUP BY语句通常和聚合函数一起使用，按照一个或多个列对结果进行分组，然后对每个组执行聚合操作。

HAVING子句对GROUP BY语句产生的分组进行条件过滤。

四、JOIN语句

Hive支持通常的SQL JOIN语句，但是只支持等值连接。

1、INNER JOIN 内连接

Hive目前还不支持在ON子句中使用OR。

2、JOIN优化

（1）需要保证连续查询中的表的大小，从左到右是依次增加的。

（2）Hive提供了一个“标记”机制，显式地告之查询优化器哪张表是大表

SELECT /*+STREAMTABLE(s)*/ s.ymd,s.symbol,s.price_close,d.dividend
FROM stocks s JOIN dividends d ON s.ymd=d.ymd AND s.symbol=d.symbol
WHERE s.symbol='AAPL';

3、LEFT OUTER JOIN 左外连接

4、RIGHT OUTER JOIN 右外连接

5、FULL OUTER JOIN 完全外连接

6、LEFT SEMI JOIN 左半开连接

返回左边表的记录，前提是其记录对于右边表满足ON语句中的判定条件。

SELECT s.ymd,s.symbol,s.price_close
FROM stocks s LEFT SEMI JOIN dividends d ON s.ymd=d.ymd AND s.symbol=d.symbol;

等价于：

SELECT s.ymd,s.symbol,s.price_close
FROM stocks s
WHERE s.ymd,s.symbol IN
(SELECT d.ymd,d.symbol FROM dividends d);

7、JOIN 笛卡尔积

左边表的行数乘以右边表的行数，等于笛卡尔结果集的大小

SELECT * FROM stocks JOIN dividends;

设置属性hive.mapred.mode值为strict，Hive会阻止执行笛卡尔积查询。

8、map-side JOIN

如果所有表中只有一张表是小表，那么可以在最大的表通过mapper的时候，将小表完全放到内存中。Hive可以在map端执行连接过程，称为map-side JOIN。

启动map-side JOIN优化：

set hive.auto.convert.join=true; #启动优化

也可以配置能够使用这个优化的小表的大小，这个属性的默认值（单位是字节）：

hive.mapjoin.smalltable.filesize=25000000

Hive对于右外连接和全外连接不支持这个优化。

如果所有表中的数据是分桶的，那么对于大表，同样可以使用这个优化。

五、ORDER BY 和SORT BY

ORDER BY：对查询结果全局排序；

SORT BY：在每个reducer中对数据进行排序，也就是局部排序。

如果reducer的个数大于1，那么输出结果就不一样。

六、DISTRIBUTE BY

DISTRIBUTE BY控制map的输出在reducer中是如何划分的。

举例：使用DISTRIBUTE BY保证相同交易码的记录会分发到同一个reducer中进行处理，然后用SORT BY排序。

SELECT s.ymd,s.symbol,s.price_close
FROM stocks s
DISTRIBUTE BY s.symbol
SORT BY s.symbol ASC,s.ymd ASC; #要求DISTRIBUTE BY语句要写在SORT BY语句之前

七、CLUSTER BY

如果DISTRIBUTE BY语句和SORT BY语句中涉及到的列完全相同，而且采用的是升序排序方式（默认），CLUSTER BY相当于前面两个句子的简写方式。

八、抽样查询

1、分桶抽样

SELECT * FROM numbers TABLESAMPLE(BUCKET 3 OUT OF 10 ON rand()) s;

TABLESAMPLE子句允许用户编写用于数据抽样而不是整个表的查询，该子句出现FROM子句中，可用于任何表中。

在rand()上分桶的行随机进入1到10个桶中，返回属于桶3的行。

2、数据块抽样

SELECT * FROM numbersflat TABLESAMPLE(0.1 PERCENT) S;#按照百分比进行抽样

不一定适用于所有的文件格式，最小抽样单元是一个HDFS数据块，如果表的数据大小小于普通的块大小128M，将会返回所有行。

九、UNION ALL

将2个或多个表进行合并，每一个union子查询都必须具有相同的列，而且字段类型一致。