6.HiveQL： 查询

平时学习记录，大佬不喜勿喷。。。

6.1 SELECT…FROM

用户选择的列是集合数据类型时，Hive会使用JSON语法应用于输出。

集合的字符串元素集上引号，基本数据类型的string的列值不加引号。

引用一个不存在的元素会返回NULL，同时，提取出的string数据类型的值将不再加引号。

引用集合中数据类型中的元素。
1、基于索引 ：从0开始 subordinates[0]
2、使用键值:deductions[“State Taxes”]
3、使用“点”符号 类似于 “表的别名.列名”

6.1.1 使用正则表达式来指定列

使用正则表达式来选择想要的列

select symbol,‘price.*’ from stocks;

6.1.2 使用列值进行计算

selectupper(name),salary,
deductions[“Federal Taxes”],
round(salary*(1-deductions[“Federal Taxes”])) from employees;

转换为大写的雇员姓名，雇员对应的薪水，需要缴纳的联邦税收比例，扣除税收后再进行取整所得的税后薪资

6.1.3 算数运算符

A/B ______商
A%B _____ 余
A&B 与 ____同1为1，否则为0
A | B 或 _____有一个为1 则为1
A～B 亦或 __相同为1 不同为0
～ A ________按位取反

算数运算符接受任意数值类型，若类型不同，则向上兼容。

Hive遵循底层java中数据类型的规则，发上溢出或者下溢时计算结果不会向更广泛的数据类型转换，乘法和除法会遇到此类问题。

6.1.4 使用函数

mysql中bigint、int、mediumint、smallint与tinyint的取值范围

mysql常用函数

1.数学函数

2.聚合函数

设置属性 set hive.map.aggr=true; 提高聚合性能

在Hive wiki中，不允许在一个查询语句中使用多余一个的函数（DISTINCT…）表达式，但实际是可以执行的

3.表生成函数

可以讲单列扩展为多列或者多行

当使用表生成函数时，Hive要求使用列别名

selsect explode(subordinates) as sub from employees;
这里使用 as sub 定义了列别名

4.其他内置函数

6.1.5 LIMIT语句

限制返回行数

6.1.6 列别名

有些心产生的结果列对于原来的表不存在，所以有必要对新的列起一个名字，也就是别名

selectupper(name),salary,
deductions[“Federal Taxes”] as fed_taxes,
round(salary*(1-deductions[“Federal Taxes”])) from employees as salary_minux_fed_taxes;

6.1.7 嵌套SELECT语句

from

selectupper(name),salary,
deductions[“Federal Taxes”] as fed_taxes,
round(salary*(1-deductions[“Federal Taxes”])) from employees as salary_minux_fed_taxes;

e

select e.name,e.salary_minus_fed_taxes
where e.salary_minus_fed_taxes> 70000;

6.1.8 CASE…WHEN…THEN句式

CASE…WHEN…THEN 和 if条件语句类似，用于处理单个列的查询结果。

select name,salary,
case
when salary < 50000.0 then ‘low’
when salary >= 50000.0 and salary < 70000.0 then ‘middle’
when salary >= 70000.0 and salary < 100000.0 then ‘high’
else ‘very high’
end as bracket from employees;

6.1.9 什么情况下Hive可以避免进行MapReduce

大多数情况下查询都会触发一个MapReduce任务（job）。Hive中对某些情况的查询可以不必使用MapReduce，也就本地模式

select * from employees; #简单的读取对应存储目录下的文件

select * from employees #过滤条件只是分区字段的，无需MapReduce过程
where country = ‘US’ and state = ‘CA’
limit 100;

属性 hive.exec.mode.local.auto=true；Hive会尝试使用本地模式进行其他操作

6.2 WHERE语句

不能在where语句中使用列别名

select upper(name),salary,
deductions["Federal Taxes"] as fed_taxes,
round(salary*(1-deductions["Federal Taxes"])) from        employees as salary_minux_fed_taxes
where round(salary_minux_fed_taxes) > 70000;

会报错

应该用

select e.* from
(
select upper(name),salary,
deductions["Federal Taxes"] as fed_taxes,
 salary*(1-deductions["Federal Taxes"])) as salary_minux_fed_taxes 
 from employees
 ) e
where round(e.salary_minux_fed_taxes) > 70000;

6.2.1 谓词操作符

## 6.2.2 关于浮点数比较
select upper(name),salary,
deductions[“Federal Taxes”]
from employees where deductions[“Federal Taxes”] > 0.2;

此时的输出结果可能包括0.2
可以理解为FLOAT的0.2是 0.2000001
DOUBLE的0.2是 0.200000000001
表中 Float 值通过Hive转换为 DOUBLE值时，DOUBLE是0.200000100000 所以会出现上述现象

select upper(name),salary,
deductions["Federal Taxes"] 
from employees where deductions["Federal Taxes"] > cast(0.2 as float);

规避上述现象采用cast操作符语法：数值 as float

和钱相关都避免使用浮点数

6.2.3 LIKE和RLIKE

1.LIKE是一个标准的SQL操作符，可以通过字符串的开头或结尾，以及指定特定的子字符串，子字符串出现在字符串内的任何位置时进行匹配。

‘%Ave’ 开头
‘O%’ 结尾
‘%Chi%’ 指定的特定字符串

2.RLIKE时Hive中一个功能扩展，可通过java的正则表达式来指定匹配条件。

6.3 GROUP BY语句

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

HAVING 语句

having 允许用户通过一个简单的语法完成原本需要子查询才能对GROUP BY语句产生分组进行条件过滤的任务。

6.4 JOIN语句

只支持等值连接

6.4.1 INNER JOIN

进行连接的两个表中都存在与连接标准想匹配的数据

select a.ymd, a.price,b.prie_close
from stocks a JOIN stocks b on a.ymd = b.ymd
where a.symbol = 'AAPL' AND b.symbol = 'IBM'

where 字句限制了左边表时AAPL的记录，右边表时IBM的记录。

Hive 目前不支持 在on字句中的位次件使用or

    select a.ymd,a.price_close,b.price_close,c.price_close
    from stocks a JOIN stocks b ON a.ymd = b.ymd
                 JOIN stocks c on a.ymd = c.ymd
    where  a.symbol = 'AAPL' and b.symbol = 'IBM' and c.symbol = 'GE';

Hive连接表时总是按照从左到右的顺序执行。

大多数情况下 Hive 会对每对JOIN 连接对象启动一个MapReduce任务。

6.4.2 JOIN 优化

保证连续查询中的表的大小时从左到右依次递增的，这样可以保证越大的表缓存次数越少。

Hive 另一个机制可以并非将最大的表放置在查询语句的最后，提供了”标记“机制来显示地告知查询优化器哪张时最大表

  select /*+STREAMTABLES（s）*/ s.ymd,s.symblo,s.price_close,d.dividend
  from stocks s JOIN dividends d on s.ymd = d.ymd and s.symblo = d.symbol
  where s.symbol ='AAPL'

6.4.3 LEFT OUTER JOIN

左外连接 JOIN操作符右边如果没有符合ON后面练剑条件的记录时，那么右边表指定选择的列的 值将会是NULL

6.4.4 OUTER JOIN

where 语句在执行链接操作之后才会执行

6.4.5 RIGHT OUTER JOIN

右外连接 左边中匹配不上的字段用NULL代替

6.4.6 FULL OUTER JOIN

任一表的制定字段没有符合条件的值的话，就是用NULL值代替。

6.4.7 LEFT SEMI-JOIN

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

SQL 不支持IN…EXISTS结构处理这种情况。

6.4.8 笛卡尔积JOIN

左边的行数*右边的行数

set hive.mapred.mode = strict #不允许笛卡尔积

和其他连接不同的是，笛卡尔积不是并行执行的，且使用mapreduce计算的话，任何方式都无法进行优化。

6.4.9 map_side JOIN

如果所有表中只有一张表是小表，可以在最大的表通过mapper的时候将小表完全放到内存中

Hive 可以在map端执行连接过程，因为Hive可以和内存中的小表进行逐一匹配，从而省略掉常规连接操作所需要的reduce过程，不仅减少了reduce过程，有时还可以同时减少map过程的执行步骤。

使用/MAPJOIN（d）/触发该优化

    select /*MAPJOIN（d）*/ 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'

Hive v0.7版本开始，废弃了这种标记方法，但是增加标记仍然有效，如果不加标记，set hive.auto.convert.JOIN = true
使得hive在必要时启动这个优化。

也可以配置 能够使用这个优化的小表大小
hive.mapjoin.smalltable.filesize=25000000;
对于右外连接和全外连接不支持这个优化

分桶表 Page 110

6.5 ORDER BY 和 SORT BY

order by 对查询结果进行全局排序，较为耗时

sort by 会在每个reducer中对数据尽心排序，也就是执行一个局部排序的过程，以便提高后续全局排序效率。

select s.ymd,s.symbol, s.price_close
from stocks s
sort by s.ymd ASC,s.symbol DESC;

如果使用的reducer个数大于1,输出结果排序可能不同，因为每个reduer智能保证局部有序

order by 可能导致运行时间过长，若属性hivelmapred.mode=strict ，hive要求这样的语句必须有加limit进行限制，默认下改实行是nostrict

6.6 含有SORT BY 的DISTRIBUTE BY

distribute by 控制map的输出在reducer中如何划分的。

mapreduce job中传输的所有数据都是按照键-值对的方式进行组织的，所以Hive讲用户的查询语句转换成mapreduce job时，必须在内部使用该功能。

默认情况下，mapreduce计算框架会根据map输入的键计算相应的哈希值，然后按照得到的哈希值将键-值对均匀分发到多个reducer去。

所以当我们使用sort by 时不同的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 和 group by 在其控制reducer时如何接受一行行数据进行处理比较类似，而sort by控制reducer 内的数据如何排序。

Hive要求 distribute by 语句要写在 sort by 语句之前。

set mapred.reduce.tasks= 10
distribute by rand(seed) #数据比较均匀分布在 10 个reducer中

6.7 CLUSTER BY

distribute by … sort by 简化版为 cluster by剥夺sort by 的并行性，可以实现输出文件的数据是全局排序。

    select s.ymd, s.symbol, s.price_close
    from stocks s
    cluster by s.symbol;

6.8 类型转换

对不同类型的2个数值进行比较操作就会有隐式类型转换。
类型转换方法 cast (value AS TYPE ) 若栗子中 value字段值不合法，则返回NULL

浮点数转整数使用 round()或floor（）

select name, salary from employees
where cast(salary as float) < 100000.0

类型转换BINARY值

hive v0.8.0中新引入的BINARY类型只支持将BINARY类型转换为STRING类型

6.9 抽样查询

若数据集过于庞大，hive通过对表进行分桶抽样来满足这个需求。
rand进行抽样，这个函数会返回一个随机值。
分桶语句，分母表示的是数据将会被散列的桶的个数，分子表示将会 选择的桶的编号。

6.9.1 数据块抽样

hive 提供一种按照抽样百分比抽样的方式，基于行数的，按照输入路径下的数据块百分比进行的抽样。

    select * from numbersflat TABLESAMPLE(0.1 PERCENT) s;

6.9.2 分桶表的输入和裁剪

对于大多数数据类型的表，抽样就扫描表中所有的数据，然后在每N行中抽取一行数据。

如果TABLESAMPLE 语句中指定的列和CLUSTERED BY 语句中指定的列相同，那么TABLESAMPLE 只会扫描涉及到的表和哈希分区下的数据。

6.10 UNION ALL

讲两个活着多个表进行河滨，每一个union子查询都必须具有相同的列。且对应的每个字段的字段类型必须是一致的。

union 也可以用于同一个元表的数据合并。