HIVE 编程指南笔记

#不一样的插入数据
Hive提供一种特别的INSERT语法。其SQL栗子如下：

FROM CMB_A A
INSERT INTO TABLE CMB_B
PARTITION (DT = '20180331')
SELECT * WHERE A.DATE = '20180331'
INSERT OVERWRITE TABLE CMB_C
PARTITION (DT = '20180401')
SELECT * WHERE A.DATE = '20180401';

可以只扫描一次输入数据，然后按照多种方式进行划分。通过这种结构，源表中的某些数据可以被写入目标表的多个分区中或者不被写入任一个分区中。目标表可以是分区表也可以是非分区表。官方说的是INSERT INTO和INSERT OVERWRITE可以混合使用。但是有老司机测试过Hive 0.13.1版本，混合使用存在BUG，只会追加而不能实现覆盖的效果。

#动态分区插入数据
##动态分区的基本操作
Hive分区分为静态分区与动态分区，动态分区SQL栗子如下：

INSERT OVERWRITE TABLE CMB_B
PARTITION (DT)
SELECT ...,A.DATE
FROM CMB_A A;

Hive先获取SELECT的最后一个位置A.DATE的参数值，然后将这个值填写到INSERT语句PARTITION中DT变量中，即动态分区是通过位置来对应分区值的。源表SELECT出来的值和输出到PARTITION的值的关系仅仅是通过位置来确定的，与名字没有关系，比如这里A.DATE的名称完全没有关系。

还可以混用动态分区和静态分区值设定，比如下面这个栗子，静态指定一个SYS值，DT的值采用动态的方法设定：

INSERT OVERWRITE TABLE CMB_B
PARTITION (SYS = 'TG', DT)
SELECT ..., A.SYSTEM, A.DATE
FROM CMB_A A
WHERE A.SYSTEM = 'TG';

注意：静态分区值必须在动态分区值的前面！

##动态分区的参数设定
动态分区功能默认是关闭的，启用时，默认为“strict”模式，这种模式下要求至少指定一个静态分区的值。这样做是为了防止设计了大量PARTITION的糟糕情况，举个栗子你使用时间戳来进行分区，竟然每一秒钟都产生一个分区！还有其他的一些属性设定用来限制类似的情况出现，如下表所示：

名称	默认值	描述
hive.exec.dynamic.partiton	fasle	设置为true用于打开动态分区功能
hive.exec.dynamic.partiton.mode	strict	设置为nonstrict能够让所有的分区都动态被设定，否则的话至少需要指定一个分区值
hive.exec.max.dynamic.partitons.pernode	100	能被每个mapper或者reducer创建的最大动态分区的数目，如果一个mappre或者reducer试图创建多余这个值的动态分区数目，会引发错误
hive.exec.max.dynamic.partitons	+1000	被一条带有动态分区的SQL语句所能创建的动态分区总量，如果超出限制会报出错误
hive.exec.max.created.files	100000	全局能被创建文件数目的最大值，专门有一个hadoop计数器来跟踪该值，如果超出会报错

#避免进行MapReduce
在hive的查询中，大部分都会出发一个MapReduce操作，但是在hive中，某些情况可以不必使用MapReduce，也就是所谓的本地操作模式。

SELECT * FROM CMB

在这种情况下，Hive可以简单的读取CMB对应的存储目录下的文件，然后输出格式化后的文件到控制台。对于在where条件中使用分区字段这种情况，也是无需MapReduce过程的，无论使用limit语句限制记录条数。如下：

SELECT * FROM CMB WHERE DT = '20180401'；

如果属性hive.exec.mode.local.auto的值设置为true的话，Hive还会尝试使用本地模式执行其他的操作：

set hive.exec.mode.local.auto=true；

注意：最好是将set hive.exec.mode.local.auto=true这个设置增加到$HOME/.hiverc配置文件中。
#HiveQL查询
Hive支持通常的SQL JOIN语句，但是只支持等值连接

Hive目前还不支持在ON子句中的谓词间使用OR

Hive总是按照从左到右的顺序执行

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

Hive提供了一个“标记”机制来仙逝地告之查询优化器哪张表是大表，/+STREAMTABLE(s)/

WHERE语句在连接操作执行后才执行，因此，WHERE语句应该只用于过滤那些非NULL值得列值。同时，ON语句中的分区过滤条件，外连接是无效的，内连接有效。通用的解决方案是使用嵌套SELECT语句。

LEFT SEMI-JOIN左半开连接会返回左边表的记录，前提是其记录对于右表满足ON语句中的判定条件。注意SELECT和WHERE语句中不能引用到右表中的字段。不支持右半开连接。SEMI-JOIN相当于INNER JOIN中的特例，比通常的INNER JOIN要高效，原因：对于左表中的一条指定记录，在右表中一旦找到匹配记录，HIve就会立即停止扫描。

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

Hive可以在map端执行连接过程（map-side JOIN），因为Hive可以和内存中的小表进行逐一匹配，从而省略掉常规连接操作所需要的reduce过程，有时还可以同时减少map过程的执行步骤。从Hive v0.7版本后，废弃了显式标记方式（/+MAPJOIN(d)/），不过如果增加这个标记同样是有效的。如果不加标记，用户需要设置属性hive.auto.convert.JOIN的值为true。用户还可以配置使用这个优化的小表的大小（hive.mapjoin.smalltable.filesize）。如果所有表的数据时分桶的，对于大表，在特定情况下同样可以使用这个优化。

Hive提供一种可选排序方式，SORT BY，其只会在每个reducer中对数据进行排序，即局部排序。可以使用DISTRIBUTE BY使得具有相同条件的记录分发到同一个reducer中进行处理。DISTRIBUTE BY和GROUP BY在控制着reducer是如何接受一行行数据进行处理这方面是类似的，而SORT BY则控制着reducer内的数据如何进行排序，DISTRIBUTE BY语句要写在SORT BY语句之前。如果涉及到的列完全相同，且采用升序排序，可以使用CLUSTER BY代替DISTRIBUTE BY…SORT BY语句，但是这两种语句都会剥夺SORT BY的并行性，但可以实现全局排序。

因为ORDER BY操作可能导致运行时间过长，如果属性hive.mapred.mode的值为strict，那么Hive要求这样的语句必须加LIMIT语句进行限制，默认为nonstrict。