《hive编程指南》阅读笔记摘要（五）

第6章 HiveQL：查询

查询字段是数组类型的元素数据

select name, subordinates[0] from employees;

查询字段是map类型的元素数据

select name, deductions["state taxes"] from employees;

查询字段是struct类型的元素数据

select name, address.city from employees;

使用正则表达式指示列

select symbol, `price.*` from stocks;

hive中支持所有典型的算术运算符。

函数：数学函数、聚合函数、 表生成函数（要有别名，否则无法表明含义，因为是从集合类型字段导出的数据，没有单独的字段名）、其他内置函数（用于处理字符串、map、数组、json和时间戳）

进行数据类型转换时，floor、round、ceil是首选的处理方式，而不是cast类型转换操作符。

set hive.map.aggr=true; 可以提高聚合性能

case......when......then......

select name,salary,

  case

    when salary < 5000 then 'low'

    when salary >= 5000 and salary < 7000 then 'middle'

    when salary >= 7000 and salary < 10000 then 'high'

    else 'very high'

  end as bracket from employees;

hive本地模式：不触发mapreduce job。

什么情况下可以避免进行mapreduce？

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

2、where子句中过滤条件只是分区字段，无论是否使用limit，都不会触发mapreduce

select * from employees

where country='US' and state='CA'

limit 100;

3、set hive.exec.mode.local.auto=true; hive会尝试使用本地模式执行

否则，其他所有查询都会触发mapreduce job

where子句中不能引用平层的select中定义的别名，但可以引用嵌套查询里定义的别名

浮点数比较的解决办法：

1、和钱相关的数值都避免使用浮点数；

2、使用cast

select * from employees where deductions['federal taxes] > cast(0.2 as float);

deductions['federal taxes]是float型的，0.2在hive中是double型的，使用cast(0.2 as float)让0.2是float型的

RLIKE是hiveQL的扩展，可以通过java的正则表达式指定匹配条件

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

having子句让用户通过一个简单的语法完成原本需要通过子查询才能对group by子句产生的分组进行条件筛选的任务。

having 和where 都是用来筛选用的 ： having 是筛选组  而where是筛选记录

用having就一定要和group by连用，用group by不一有having （它只是一个筛选条件用的）

select ... from ... where ... group by ... having...

WHERE语句在GROUPBY语句之前；SQL会在分组之前计算WHERE语句。

HAVING语句在GROUPBY语句之后；SQL会在分组之后计算HAVING语句。

join语句

hive支持通常的sql join操作，但是只支持等值连接。

hive不支持在on子句中的谓词间使用or

大多数情况下，hive会对每个join连接启动一个mapreduce任务。如

a join b

   join c

会县启动一个mapreduce job对a和b进行连接操作，然后再启动一个mapreduce job对第一个mapreduce job的输出和表c进行连接操作。

当对3个或者更多个表进行join连接时，如果每个on子句都使用相同的连接键，那么只会产生一个mapreduce job。

hive会假设查询中最后一个表是最大的表，会尝试将其他表缓存起来。因此，用户需要保证连续查询中的表的大小从左到右是依次增加的。

幸运的是，用户并非总是要将最大的表放在查询语句最后，因为hive提供了 标记 机制，显式地告知哪张表是大表

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

select ... from ...join ...on... where ...

会先执行join语句，再将结果通过where语句进行过滤

on语句中的分区过滤条件在外链接中是无效的，在内连接中是有效的

hive不支持where s.ymd,s.symbol in (select d.ymd, d.symbol from dividends d)种种操作，而是以left semi join实现此功能

select s.ymd,s.symbol,s.price_close

from stocks left semi join dividends d on s.ymd=d.ymd and s.symbol=d.symbol;

注意：select和where子句中不能引用右边表中的字段。

semi join比通常的inner join高效

如果join on语句写错了，可能会导致一个笛卡尔积join查询，如果设置hive.mapred.mode=strict，hive会阻止用户执行笛卡尔积查询

map-side join：hive可以在map端执行连接过程（称为map-side join），即hive可以和内存中的小表进行逐一匹配，从而省略掉常规连接操作所需要的reduce过程。有时还可以同时减少map过程的执行步骤。

set hive.auto.convert.join=true;   hive会在必要的时候启动map-side join优化

用户还可以配置能够使用这个优化的小表的大小

hive.mapjoin.smalltable.filesize=25000000   （单位是字节，即25M）

hive对right outer join 和 full outer join 不支持这个优化

order by 和sort by

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

cluster by s.symbol = distribute by s.symbol sort by s.symbol asc

cluster by剥夺了sort by 的并行性，但可以实现输出文件的数据是全局排序的。

类型转换函数cast(field as type)，type是具体的字段类型，如cast(salary as FLOAT),如果转换失败，会返回NULL

将浮点数转换为整形的推荐方式是round()、floor()

抽样查询、数据块抽样

抽样会扫描表中所有的数据，然后在每N行中抽取一行数据。

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

*****************************

微信公众号：IT人成长关注

大数据技术QQ群：485681776