Hive 基础（-）

1.Hive创建表 - 默认创建外部表

create table student (
id int,
name string,
 sex string, 
 age int,
 department string
 ) 
row format delimited fields terminated by ",";

关键字ROW FORMAT DELIMITED 指定表的分隔符，通常后面要与以下关键字连用：
FIELDS TERMINATED BY ‘,’ //指定每行中字段分隔符为逗号
LINES TERMINATED BY ‘\n’ //指定行分隔符
COLLECTION ITEMS TERMINATED BY ‘,’ //指定集合中元素之间的分隔符
MAP KEYS TERMINATED BY ‘:’ //指定数据中Map类型的Key与Value之间的分隔符

2.Hive中的SELECT基础语法和标准SQL语法基本一致，支持WHERE、DISTINCT、GROUP BY、ORDER BY、HAVING、LIMIT、子查询等；

[WITH CommonTableExpression (, CommonTableExpression)*]  
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[CLUSTER BY col_list
 | [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]

ORDER BY和SORT BYORDER BY用于全局排序，就是对指定的所有排序键进行全局排序，使用ORDER BY的查询语句，最后会用一个Reduce Task来完成全局排序。

SORT BY用于分区内排序，即每个Reduce任务内排序。真实业务环境中，我们的需求大多需要使用ORDER BY全局排序来完成。

DISTRIBUTE BY和CLUSTER BYdistribute by：指定reduce次数，然后按照指定的字段或表达式对数据进行划分，输出到对应的Reduce或者文件中。cluster by：除了兼具distribute by的功能，还兼具sort by的排序功能。

3.子查询

子查询和标准SQL中的子查询语法和用法基本一致，需要注意的是，Hive中如果是从一个子查询进行SELECT查询，那么子查询必须设置一个别名。还有一种将子查询作为一个表的语法，叫做Common Table Expression（CTE）：

with q1 as (select * from src where key= '5'),
q2 as (select * from src s2 where key = '4')
select * from q1 union all select * from q2;

4. join

joinHive中除了支持和传统数据库中一样的内关联、左关联、右关联、全关联，还支持LEFT SEMI JOIN和CROSS JOIN，但这两种JOIN类型也可以用前面的代替。
注意：Hive中Join的关联键必须在ON ()中指定，不能在Where中指定，否则就会先做笛卡尔积，再过滤。LEFT SEMI JOIN以LEFT SEMI JOIN关键字前面的表为主表，返回主表的KEY也在副表中的记录。CROSS JOIN返回两个表的笛卡尔积结果，不需要指定关联键

5.Hive SQL的优化

1）、使用分区剪裁、列剪裁在SELECT中，只拿需要的列，如果有，尽量使用分区过滤，少用SELECT *。
在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在Where后面，那么就会先全表关联，之后再过滤，所以正确的写法是写在ON后面：

SELECT a.id
FROM lxw1234_a a
left outer join t_lxw1234_partitioned b
ON (a.id = b.url AND b.day = ‘2015-05-10′);

或者 直接将副表写成子查询

SELECT a.id
FROM lxw1234_a a
left outer join (SELECT url FROM t_lxw1234_partitioned WHERE day = ‘2015-05-10′) b
ON (a.id = b.url)

2）、少用COUNT DISTINCT
数据量小的时候无所谓，数据量大的情况下，由于COUNT DISTINCT操作需要用一个Reduce Task来完成，这一个Reduce需要处理的数据量太大，就会导致整个Job很难完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替换：

SELECT day,
COUNT(DISTINCT id) AS uv
FROM lxw1234
GROUP BY day

可以换成

SELECT 
day, count(1) as uv
FROM(
SELECT 
day,id
FROM lxw1234
GROUP day, id
）a 
GROUP BY day

参考：添加链接描述