Hive DDL
1. 创建表
CREATE TABLE：用于创建一个指定名字的表 。如果相同名字的表已经存在，则抛出异常 用户可以用 IF NOT EXIST 选项来忽略这个异常。
EXTERNAL ：该关键字可以让用户创建一个外部表，在创建表的同时指定一个指向实际数据的路径（LOCATION）。
COMMENT ：可以为表与字段增加描述。
ROW FORMAT ：用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。
STORED AS ：如果文件数据是纯文本，则使用 STORED AS TEXTFILE ；如果数据需要压缩， 则使用 STORED AS SEQUENCE 。
LIKE： 允许用户复制现有的表结构，但是不复制数据。
hive> CREATE TABLE empty key value store LIKE key value store;
还可以通过 CREATE TABLE AS SELECT 的方式来创建表，示例如下：
Hive> CREATE TABLE new key value store ROW FORMAT SERDE "org.apache.Hadoop.hive.serde2.columnar.ColumnarSerDe" STORED AS RCFile AS SELECT (key % 1024) new_key, concat(key, value) key_value_pair FROM key_value_store SORT BY new_key, key_value_pair;
2. 修改表
修改表名的语法如下：
hive> ALTER TABLE old_table_name RENAME TO new_table_name;
修改列名的语法如下：
ALTER TABLE table_name CHANGE (COLUMN) old_col_name new_col_name column_type [COMMENT col_comment) (FIRST|AFTER column_name)
上述语法允许改变列名 数据类型 注释 列位 它们的任意组合 建表后如果要新增一列，则使用如下语法：
hive> ALTER TABLE pokes ADD COLUMNS (new_col INT COMMENT 'new col comment');
3. 删除表
DROP TABLE 语句用于删除表的数据和元数据 。对于外部表，只删除 Metastore 中的元数据，而外部数据保存不动，示例如下：
drop table my_table;
如果只想删除表数据，保留表结构，跟 MySQL 类似，使用 TRUNCATE 语句：
TRUNCATE TABLE my_table;
4. 插入表
( 1 ）向表中加载数据
相对路径的示例如下：
hive> LOAD DATA LOCAL INPATH ’./exarnples/files/kvl.txt ’ OVERWRITE INTO TABLE pokes;
( 2 ）将查询结果插入 Hive
将查询结果写入 HDFS 文件系统。
INSERT OVERWRITE TABLE tablenamel [PARTITION (partcoll=val1, partcol2=val2 ... )] select_statement1 FROM from_statement
这是基础模式，还有多插入模式和自动分区模式，这里就不再叙述。
Hive DML
1. 基本的 select 操作
SELECT [ALL | DISTINCT] select_expr, select_expr, ... FROM table_reference [WHERE where_condition] [GROUP BY col_list [HAVING condition]] [ CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY | ORDER BY col_list] ] [LIMIT number]
使用 ALL、 DISTINCT 选项区分对重复记录的处理 。默认是 ALL ，表示查询所有记录， DISTINCT 表示去掉重复的记录
WHERE 条件：类似于传统 SQL的 where 条件，支持 AND 、OR 、BETWEEN、 IN 、NOT IN 等
ORDER BY 与 SORT BY 的不同： ORDER BY 指全局排序，只有一个 Reduce 任务，而 SORT BY 只在本机做排序
LIMIT ：可以限制查询的记录数，如 SELECT * FROM tl LJMIT5 ，也可以实现 Topk 查询，比如下面的查询语句可以查询销售记录最多的 5个销售代表
SET mapred.reduce.tasks = 1 SELECT * FROM test SORT BY amount DESC LIMIT 5
REGEX Column Specification : select 语句可以使用正则表达式做列选择，下面的语句查询除了 ds 和 hr 之外的所有列
SELECT `(ds|hr)?＋.＋` FROM test
2. join 表
join_table: table_reference (INNER] JOIN table_factor (join_condition] | table_reference {LEFTIRIGHTjFULL} (OUTER] JOIN table_reference join_ condition | table_reference LEFT SEM JOIN table_reference join_condition | table_reference CROSS JOIN table_reference (join_condition] (as of Hive 0.10) table reference: table_factor | join_table table_factor: tbl_name [alias] | table_subquery alias | (table_references) join_condition: on expression
Hive中只支持等值连接，外连接和左半连接（left semi join），（从2.2.0版本后支持非等值连接）;
可以连接2个以上的表，如：
select a.val, b.val,c.val from a join b on (a.key=b.key1) join c on(c.key = b.key2);
如果连接中多个表的join key是同一个,则连接会被转化为单个Map/Reduce任务
select a.val,b.val,c.val from a join b on (a.key=b.key1) join c on(c.key=b.key1);
join时大表放在最后: Reduce会缓存join序列中除最后一个表之外的所有表的记录，再通过最后一个表将结果序列化到文件系统
如果想限制join的输出, 应该在where子句中写过滤条件，或是在join子句中写。
但是有表分区的情况，比如下面的第一个 SQL 语句所示，如果d表中找不到对应c表的记录， d表的所有列都会列出 NULL ，包括 ds列。 也就是说， join 会过滤d表中不能找到匹配c表 join key 的所有记录。 这样， LEFT OUTER 就使得查询结果与 WHERE 子句无关，解决办法是在join 时指定分区（如下面的第二个 SQL 语句所示）
--第一个 SQL 语句 SELECT c.val, d.val FROM c LEFT OUTER JOIN d ON (c.key=d.key) WHERE a.ds='2010-07-07' AND b.ds='2010-07-07' -- 第二个 SQL 语句 SELECT c.val, d.val FROM c LEFT OUTER JOIN d ON (c.key=d.key AND d.ds=’ 2009-07-07 ’ AND c.ds='2009-07-07')
left semi join是in/exists子查询的一种更高效的实现，join子句中右边的表只能在on子句中设置过滤条件，在where子句、select子句中或其他方式过滤都不行
SELECT a.key, a.value FROM a WHERE a.key in (SELECT b.key FROM B); --可以被重写为： SELECT a.key, a.val FROM a LEFT SEMI JOIN b on (a.key = b.key)
三、Hive SQL 执行原理图解
我们都知道，一个好的的 Hive SQL 和写得不好的 Hive SQL ，对底层计算和资源的使用可能相差百倍甚至千倍、万倍。
除了资源的浪费，不恰当地使用 Hive SQL 可能会运行几个小时甚至十几个小时都得不到运算结果。因此，我们深入的理解 Hive SQL 的执行过程和原理是非常有必要的。
以 group by 语句执行图解为例：
我们假定一个业务背景：分析购买iPhone7客户在各城市中的分布情况，即哪个城市购买得最多、哪个最少。
select city,count(order_id) as iphone7_count from orders_table where day='201901010' and cat_name='iphone7' group by city;
底层MapReduce执行过程：
Hive SQL 的 group by 语句涉及数据的重新分发和分布，因此其执行过程完整地包含了 MapReduce 任务的执行过程。
( 1 ）输入分片
group by 语句的输入文件依然为 day=20170101 的分区文件，其输入分片过程和个数同 select 语句，也是被分为大小分别为： 128MB 、128MB、44MB 三个分片文件。
( 2 ) Map 阶段
Hadoop 集群同样启动三个 Map 任务，处理对应的三个分片文件；每个 map 任务处理其对应分片文件中的每行，检查其商品类目是否为 iPhone7 ，如果是，则输出形如 的键值对，因为需要按照 city 对订单数目进行统计（注意和 select 语句的不同)。
( 3 ) Combiner 阶段
Combiner 阶段是可选的，如果指定了 Combiner 操作，那么 Hadoop 会在 Map 任务的地输出中执行 Combiner 操作，其好处是可以去除冗余输出，避免不必要的后续处理和网络传输开销等
此列中，Map Task1 的输出中< hz,1＞出现了两次，那么 Combiner 操作就可以将其合并为
Combiner 操作是有风险的，使用它的原则是 Combiner 的输出不会影响到 Reduce 计算的最终输入。例如，如果计算只是求总数、最大值和最小值，可以使用 combiner ，但是如果做平均值计算使用了 Combiner ，最终的 Reduce 计算结果就会出错
( 4 ) Shuffle 阶段
完整的shuffle包括分区(partition)，排序(sort)和分隔(spill)、复制(copy)、合并(merge)等过程。
对于理解group by语句，关键的过程实际就两个，即分区和合并；所谓分区，即 Hadoop 如何决定将每个 Map 任务的每个输出键值对分配到那个 Reduce Task 所谓合井，即在 一个Reduce Task 中，如何将来自于多个 Map Task 的同样一个键的值进行合并
Hadoop 中最为常用的分区方法是 Hash Partitioner ，即 Hadoop 会对每个键取 hash 值，然后再对此 hash 值按照 reduce 任务数目取模，从而得到对应的 reduce ，这样保证相同的键，肯定被分配到同一个 reduce 上，同时 hash 函数也能确保 Map 任务的输出被均匀地分配到所有的 Reduce务上
( 5 ）Reduce 阶段
调用reduce函数，每个reduce任务的输出存到本地文件中
( 6 ）输出文件
hadoop 合并 Reduce Task任务的输出文件到输出目录
四、小结
我们介绍了 Hive SQL 的执行原理。当然了，要知其然，并要知其所以然，理解 Hive 的执行原理是写高效 SQL 的前提和基础，也是掌握 Hive SQL 优化技巧的根本，接下来我们就要进入 Hive 优化实践的环节啦。
