《Hive编程指南学习》：HQL基础、HQL优化

Hive优化为文中标黄的内容。

1 HQL数据定义

//数据库中，表名，正则表达式
use jmw;
show tables like “jm.*”;

//简单查询
select distinct gender
from jmw.jm_ti_account
where ymd>“20191230”
limit 10

//创建表
create table if not exists jmw.stephenTest(
name string comment “person’s name”,
age int comment “person’s age”,
sex int comment “person’s sex”
)
comment “stephen’s test”
tblproperties(‘creator’=‘me’,‘created_at’ = ‘2020-03-13 10:22:00’)
location “hdfs://nameservice1/user/hive/warehouse/jmw.db”

//展示表的表结构，即大致信息
describe jmw.jm_ti_account;

//展示表的详细表结构信息
describe extended jmw.jm_ti_account;

//展示更加冗长的、可读的表结构信息
describe formatted jmw.jm_ti_account;

//展示表的所有分区
show partitions jmw.jm_ti_account;

//查询是否存在某个分区键的分区
show partitions jmw.jm_ti_account partition(ymd=‘20191016’);

//加载数据，并创建分区
load data local inpath’${env:home}/…’
into table xxx
partition(country=’’,state=’’);

overwrite:使用该关键字时，目标文件夹中之前存在的数据会被删除掉。
如果没有该关键字，仅仅会把新增的文件增加到文件夹中，如果已存在同名文件，会将新文件重命名为文件名_序列号。

2 HQL数据操作

//装载数据
load data local inpath’${env:home}/…’
into table xxx
partition(country=’’,state=’’);

//装载数据，将之前的数据删除掉，再装载
load data local inpath’${env:home}/…’
overwrite into table xxx
partition(country=’’,state=’’);

//通过查询语句向表中插入数据
Insert overwrite table xxx1 partition(country=‘c1’,state=‘s1’)
select* from xxx2 where country=‘c1’ and state=’s1’

//需要创建的分区有点多时
From xxx2
Insert overwrite table xxx1
partition(country=‘c1’,state=‘s1’)
select * from xxx2 where country=‘c1’ and state=‘s1’
Insert overwrite table xxx1
partition(country=‘c2’,state=‘s2’)
select * from xxx2 where country=‘c2’ and state=‘s2’
Insert overwrite table xxx1
partition(country=‘c2’,state=‘s2’)
select * from xxx2 where country=‘c2’ and state=‘s2’

//动态分区：需要创建的分区非常多时
Insert overwrite table xxx1
partition(country,state) //此处没有指定需要创建的分区的，具体的值，所以这里是动态分区。
select … ,alias.country,alias.state //注意给xxx2起了别名，为了强调原表字段值和输出分区值之间的关系是根据位置而不是命名来匹配的
From xxx2 alias

//混合使用静态和动态分区
Insert overwrite table xxx1
partition(country=’Ch’,state)//注意，静态分区必须在动态分区之前
select ….,alias.country,alias.state
from xxx2 alias
where xxx2.country = ‘Ch’

注意：有很多相关属性限制资源利用。
hive.exec.dynamic.partition：开启动态分区功能。
hive.exec.dynamic.partition.mode：允许所有分区都是动态的。
hive.exec.max.dynamic.partitions.pernode：每个mapper或reducer可以创建的最大分区个数。
hive.exec.max.dynamic.partitions：一个动态分区创建语句可以创建的最大分区个数。
hive.exec.max.created.files：全局可以创建的最大文件个数。

//单个查询语句中创建表并加载数据
Create table xxx
as select语句

//导出数据

3 HQL查询

3.1 select

HQL函数
upper(字符串)：将字符串转换为大写。
round(浮点数)：将浮点数取整。

1 数学函数
Round
Floor
Ceil
Exp
Log
Pow
sqrt
Bin
Hex
Unhex
Conv
Abs
Pmod
Sin
Asin
Cos
Acos
tan
Atan
Degress
Radians
Positive
Negative
Sign
e()
pi()

2 聚合函数
count
sum
Avg
Min
Max
variance
var_pop
var_samp
stddev_pop
stddev_samp
covar_pop
covar_samp

聚合优化
set hive.map.aggr = true
这个设置可以将顶层的聚合操作放在Map阶段执行，从而减轻清洗阶段数据传输和Reduce阶段的执行时间，提升总体性能。
缺点：该设置会消耗更多的内存。
注：顶层的聚合操作（top-levelaggregation operation）,是指在group by语句之前执行的聚合操作。例如：

hive> SET hive.map.aggr=true;
hive> SELECT count(*), avg(salary)FROM employees group by xxx having max()>1

3 表生成函数
Explod
json_tuple = get_json_object

4 其他内置函数 P88***，这就是需要学习的呀！！！
Cast：转换类型。
Concat：连接字符串
concat_ws：连接字符串，并制定分隔符。

3.2 join

• inner join：内连接，两个表中的记录都符合on语句，才选出。两个表中的记录都有可能删除。

• left outer join ：左外连接，左表中记录全部返回。右表中没有对应的就用NULL填充。

• right outer join：右外连接，右表中记录全部返回。左表中没有对应的就用NULL填充。

• full outer join：完全外连接，两个表中的记录都全部返回。另一个表中没有对应的就用NULL填充。

• left semi join：左半开连接，返回左表中的记录，但不一定全部返回，只有左表中记录满足on语句时才会返回。相当于SQL中的Exist in。
  缺点：select 和 where 语句不能引用右表中的字段。
  优化：left semi join，能使用left semi join时，不要使用inner join。什么时候可以用left semi join代替inner join呢？笔者认为，只需要一个表中的数据，即左表中的数据时，就可以替代了。

3.3 where

3.3.1 模式匹配

Like和RLike
Like：字符串的开头、结尾，指定的子字符串，子字符串出现在字符串内的任何位置时。
RLike：可以使用Java的正则表达式。

3.3.2 三值逻辑

问题：我们使用!=null查询数据时，查不出数据，结果总为空。
SQL中逻辑表达式的可能值有三种：TRUE、FALSE、Unknown。Unknown即什么都不知道。我们将任何值与NULL比较，结果都是Unknown，而在查询表达式（Where、having）中，Unknown为视为FALSE。(不是所有场景中Unknown都视为FALSE，在check约束中Unknown会视为True。)
所以判断null值请使用is not，例如：

where uid is not null

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

MapReduce优化：本地模式
Hive对某些情况下的查询可以不触发MapReduce任务，即本地模式。

• select* from employee
  这种情况下，Hive可以直接读取employee目录下的文件，格式化后输出到控制台。
• where 后的过滤条件全是分区字段，无论是否使用limit。
  无需建立MapReduce，直接读取。
• set hive.exec.mode.local.auto = true ，设置该参数后，Hive会尝试使用本地模式执行其他操作，否则Hive使用MapReduce执行其他所有查询。

四、优化
4.1 Join优化
大多数情况下，Hive会对每对join连接对象启动一个MapReduce任务。
例如a join b on a.x = b.y and b join c on b.p = c.q，这时会启动两个MapReduce任务，先对a join b启动一个，输出的结果再与join c启动一个MR任务。

Join优化：on连接键：但是，当对多个表进行join连接时，如果每个on字句都使用同一个连接键（例如都使用UserID），那么只会产生一个MapReduce Job。

Join优化：驱动表：Hive在进行join时，会假定最后一个表是大表，在对每行记录进行连接操作时，会尝试将其他表缓存起来，然后扫描最后那个表进行计算。
（1）如上所说，应当把小表放在左边，用小表驱动大表。
（2）使用/*+streamTable(大表名称)*/显示标记大表，这样大表就可以放在任何位置。
==Join优化：mapjoin：==使用大表join小表时，

4 优化

4.1 限制调整

缺点：很多情况下，limit语句还是需要执行整个查询语句，然后再返回部分结果。

解决方法：这种情况通常是浪费的，可以通过设置属性hive.limit.optimize.enable，对源数据进行采样，从而避免浪费。

该属性的详细描述如下：

<property>
<name>hive.limit.optimize.enable</name>
<value>true</value>
<description>Wheather to enable to optimization to try a smaller subset of data for simple limit first.（是否进行优化，即对源数据的一个子集应用limit，从子集中选出需要的行数返回。）</description>
</property>

一旦hive.limit.optimize.enable属性的值设置为true，还可以通过参数hive.limit.row.max.size和hive.limit.optimize.limit.file空值这个操作。

<property>
<name>hive.limit.row.max.size</name>
<value>100000</value>
<description>When trying a smaller subset of data for simple LIMIT,how much size we need to guarantee each row to have at least.（即子集的最大行数，建议使用默认设置10w。）</description>

<property>
<name>hive.limit.optimize.limit.file</name>
<value>10</value>
<description>When trying a smaller subset of data for simple LIMIT,maxmum number of files we can sample.（子集中的数据可能抽取自分布式存储中的多个文件，这里设置可抽样的最大文件数，默认是10。）</description>

缺点：这个功能的一个缺点就是，有可能输入中有用的数据永远不会被处理到，例如，像任意的一个需要reduce步骤的查询，JOIN和GROUP BY操作，以及聚合函数的大多数调用，等等，将会产生很不同的结果。也许这个差异在很多情况下是可以接受的，但是重要的是要理解。（？？？好吧，其实这个例子我没看懂）

参考文献：
[1]hive调优之 优化hive-site.xml配置
[2]数据分析利器之hive优化十大原则