1、Hive 表 DDL
1.1、创建表
Hive中创建表的完整语法如下：
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[ (col_name data_type [COMMET col_comment], . . .)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], . . . )]
[CLUSTERED BY (col_name, col_name, . . . )]
[SORTED BY (col_name [ASC|DESC], . . . ) INFO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
CREATE TABLE：用于创建一个指定名称的表，如果相同名字的表已经存在，则抛出异常，可以用 IF NOT EXISTS选项来忽略这个异常。
EXTERNAL：该关键字可以让用户创建一个外部表，在创建表的同时指定一个指向实际数据的的路径（LOCATION）.
COMMENT：可以为表和字段增加注释
ROW FORMAT：用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定的ROW FORMAT或者ROW FORMAT DELIMITED，将会使用自带的SerDe；在创建表时，用户还需要为表指定列，同时也会指定
自定义的SerDe。Hive通过SerDe确定表的具体的列的数据。Serde是 Serializer/Deserializer的简写。hive使用Serde进行行对象的序列与反序列化。
一条简单的建表语句如下：
hive> CREATE TABLE gripe(foo INT, bar STRING);
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;
1.2、修改表
修改表名的语法如下：
hive> ALTER TABLE old_table_name RENAME TO new_table_name;
修改列名的语法如下：
hive>ALTER TABLE table_name CHANGE [COLUMN] old_col_name new_col_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
上述语法允许改变列名、数据类型、注释、列位置或者它们的任意组合。建表后如果需要增加一列，则使用如下语法：
hive>ALTER TABLE gripe ADD COLUMNS (new_col INT COMMENT 'new col comment');
1.3、删除表
DROP TABLE 语句用于删除表的数据和元数据。对应外部表，只删除Metastore中的元数据，而外部数据保存不动，例如：
drop table my_table;
如果只想删除表数据，保留表结构，跟MYSQL类似，使用TRUNCATE语句。
TRUNCATE TABLE my_table;
1.4、插入表
1、向表中加载数据
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INFO TABLE tablename [PARTITION(partcol1 = val1, partcol2 = val2, . . .)]
Load操作只是单纯的复制/移动操作，将数据 文件移动到Hive表对应的位置，filepath可以是相对路径，例如project/data1, 也可以是绝对路径，例如：/user/hive/project/data1, 或是包含模式的完整URI，例如：
hdfs://namenode:9000/user/hive/project/data1。
相对路径实例如下：
hive> LOAD DATA LOCAL INPATH './examples/files/kv1.txt' OVERWRITE INTO TABLE gripe;
2、将查询结果插入Hive表
将查询结果写入HDFS文件系统。
a、基本模式：
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1 = val1, partcol2 = val2, . . .)]
select_statements FROM from_statement
b、多插入模式：
INSERT OVERWRITE TABLE tablename1
[PARTITION (partcol1 = val1, partcol2 = val2, . . . )]
select_statement1
[ INSERT OVERWRITE TABLE tablename2 [PARTITION . . . ] select_statement2] . . .
c、自动分区模式：
INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] . . . )
select_statement From from_statement
2、Hive 表 DML
1、基本的select操作
Hive中的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 t1 LIMIT 5, 也可以实现Top k查询，比如下面的查询语句可以查询销售记录最多的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表
Hive中join表的语言如下：
join_table：
table_reference [INNER] JOIN table_factor [join_condition]
| table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
| table_reference LEFT SEMI 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_confition：
On expression
对Hive中表Join操作的说明以及注意事项如下：
1、Hive只支持等值连接、外连接和左半连接（left semi join）,Hive 不支持所有的非等值连接，因为非等值连接很难转化到map/reduce任务（从2.2.0版本后开始支持非等值连接）。
2、可以连接2个以上的表，例如：
SELECT a.val, b.val, c.val FROM a JOIN b on (a.key = b.key1) JOIN c on (c.key = b .key2)
3、如果连接中多个表的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)
4、join时大表放在最后。这是因为每次Map/Reduce任务的逻辑是这样的：Reduce会缓存join序列中除最后一个表之外的所有表的记录，再通过最后一个表将将结果序列化文件系统，因此在实践中，应该把最大的那个表写在最后。
5、如果想限制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 c.ds = '-08-08' AND d.ds ='-08-08'
//第一个SQL语句
SELECT c.val, d.val FROM c LEFT OUTER JOIN d ON (c.key = d.key AND c.ds = '-08-08' AND d.ds ='-08-08')
6、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.value FROM a LEFT SEMI JOIN b on (a.key = b.key)
参考资料：《离线和实时大数据开发实战》
