[贴贴板]快速Hive SQL语句

持续更新中，是用来应对ETL基本操作的贴贴板和一点笔记。
旨在把变量名改掉快速使用语句结构。

1.建表：

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
  [(col_name data_type [COMMENT 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], ...)] INTO num_buckets BUCKETS] 
  [ROW FORMAT row_format] 
  [STORED AS file_format] 
  [LOCATION hdfs_path]

1.1 如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常。COMMENT后接字段描述。
1.2 ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char]
[COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, …)]
用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';

1.3 如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE 。ORC hive给出的新格式，属于RCFILE的升级版。使用Stored as orc；

2.PARTITION：

基本操作：

1.概念

Hive分区更方便于数据管理，常见的有时间分区和业务分区。
分区建表分为2种，一种是单分区，也就是说在表文件夹目录下只有一级文件夹目录。另外一种是多分区，表文件夹下出现多文件夹嵌套模式，即多分区的顺序性。
a. 单分区建表语句：create table day_table (id int, content string) partitioned by (dt string);单分区表，按天分区，在表结构中存在id，content，dt三列。这里分区字段不能和表中的字段重复，重复了会报错。
b. 双分区建表语句：create table day_hour_table (id int, content string) partitioned by (dt string, hour string);双分区表，按天和小时分区，在表结构中新增加了dt和hour两列。载入数据时必须两个分区标识都给，不然会报错。

2.操作

2.1 载入文件带着分区标识：

load data local inpath '/home/hadoop/Desktop/data' overwrite into table t1 partition ( pt_d = '201701');

2.2 基于分区的查询：

SELECT day_table.* FROM day_table WHERE day_table.dt>= '2008-08-08';

2.3 添加一个分区(分区文件)：

alter table t1 add partition (pt_d = ‘333333’); 

2.4 往指定分区中嵌入数据：
* 关键字
1）由于使用了OVERWRITE关键字，目标表中原来相同partition中的所有数据被覆盖，如果目标表中没有partition，则整个表会被覆盖。
如果把OVERWRITE关键字删掉，或者替换成INTO，则hive会追加而不是替代原分区或原表中的数据，这个特性在Hive v0.8.0之后才支持。
2）INSERT INTO SELECT
我们可以从一个表中复制所有的列插入到另一个已存在的表中：

INSERT INTO table2
SELECT * FROM table1;
或
INSERT INTO table2(column_name(s))
SELECT column_name(s) FROM table1;

*select into from 和 insert into select 都是用来复制表
两者的主要区别为： select into from 要求目标表不存在，因为在插入时会自动创建；insert into select from 要求目标表存在。

SELECT column_name(s)
INTO new_table_name [IN externaldatabase] 
FROM old_tablename

*insert…select句型
hive对这个SQL语句进行了改造，只需要扫描一次原表就可以生成不同的输出（多路输出）。
先通过from表获取一条记录，然后执行每一个select子句，如果select子句验证通过则执行相应的insert语句。注意这里的多条select子句是完全独立执行的，并不是if … then … else的关系，这就意味着这几条select子句在某种情况下可能同时通过where检测。每条Insert语句能将数据写到不同的数据表中，不管这个表是否分区都一样。

FROM staged_employees se
INSERT OVERWRITE TABLE employees
	PARTITION (country = '中国', state = '河北省')
	SELECT * WHERE se.cnty = '中国' AND se.st = '河北省'

2.5 一次添加多个分区：

alter table testljb add partition(age=3) partition(age=4);

2.6 索引字段ds：

PARTITIONED BY (ds STRING)

2.7 动态分区
静态分区拿不同分区的数据insert要写重复多个insert into … select …where子句，这非常不现实。于是hive的一种叫做动态分区的特性就出现了，它能够根据select出来的参数自动推断将数据插入到那个分区中去。

INSERT OVERWRITE TABLE employees
PARTITION (country, state)
SELECT ..., se.cnty, se.st
FROM staged_employees se;

hive先获取select的最后两个位置的se.cnty和se.st参数值，然后将这两个值填写到Insert语句partition中的两个country和state变量中，即动态分区是通过位置来对应分区值的。原始表select出来的值和输出partition的值的关系仅仅是通过位置来确定的，和名字并没有关系，比如这里se.cnty和county的名称完全没有关系。最末几个位置的值自动设定目标分区的值。

2.8 静态动态混用
保持select参数位置值与partition的对应。
静态分区值必须在动态分区值的前面！

INSERT OVERWRITE TABLE employees
PARTITION (country = 'US', state)
SELECT ..., se.cnty, se.st
FROM staged_employees se
WHERE se.cnty = 'US';

2.9 动态分区的参数设定

参数	情况
hive.exec.dynamic.partition	默认false，设置为true用于打开动态分区功能
hive.exec.dynamic.partition.mode	默认strict，设置为nonstrict能够让所有的分区都动态被设定，否则的话至少需要指定一个分区值
hive.exec.max.dynamic.partitions.pernode	默认100，能被每个mapper或者reducer创建的最大动态分区的数目，如果一个mappre或者reducer试图创建多余这个值的动态分区数目，会引发错误
hive.exec.max.dynamic.partitions	默认+1000，被一条带有动态分区的SQL语句所能创建的动态分区总量，如果超出限制会报出错误
hive.exec.max.created.files	默认100000，全局能被创建文件数目的最大值，专门有一个hadoop计数器来跟踪该值，如果超出会报错
hive.exec.parallel	默认为false，控制在同一个sql中的不同的job是否可以同时运行

常用贴贴头：

SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.exec.dynamic.partition=true;
SET hive.exec.max.dynamic.partitions=1000;

3.视图

CREATE VIEW [IF NOT EXISTS] view_name [ (column_name [COMMENT column_comment], …) ][COMMENT view_comment][TBLPROPERTIES (property_name = property_value, …)] AS SELECT

hive不支持用insert语句一条一条的进行插入操作，也不支持update操作。数据是以load的方式加载到建立好的表中。数据一旦导入就不可以修改。

4.统计函数

1.count
统计有效行(非null)，返回int。

select count(*) from tmp.stu    --统计所有行数
select count(id) from tmp.stu    --统计id列不为null的行数
select count(distinct t) from lxw_dual
--count(DISTINCTexpr[, expr_.])返回指定字段的不同的非空值的个数

2.sum
将某列进行累加(如果某行的列的为null则忽略，返回double。

select sum(id) from tmp.stu
select sum(distinct t) from lxw_dual

3.avg，min，max
返回double。

select avg(t) from lxw_dual
select min(t) from lxw_dual
select max(t) from lxw_dual

select as的场合：

select 
	count(*) as s,
	count(case when name == 'la' then 0 else 1 end) as s1,
	count(case when name == 'la' then null else 1 end) as s2,
	sum(case when name == 'la' then 0 else 1 end) as s3,
	sum(case when name == 'la' then null else 1 end) as s4
from tmp.stu

5.Select搭配用

5.1 ORDER BY和SORT BY–排序
ORDER BY用于全局排序，就是对指定的所有排序键进行全局排序，使用ORDER BY的查询语句，最后会用一个Reduce Task来完成全局排序。
SORT BY用于分区内排序，即每个Reduce任务内排序。如果reduce数为1，那么ORDER BY和SORT BY的结果是一样的：

set mapred.reduce.tasks=2;
select * from lxw1234_com sort by id;

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

set mapred.reduce.tasks=2;
INSERT overwrite LOCAL directory '/tmp/lxw1234/' 
SELECT id FROM lxw1234_com 
distribute BY id;

set mapred.reduce.tasks=2;
hive> INSERT overwrite LOCAL directory '/tmp/lxw1234/' 
SELECT id FROM lxw1234_com 
CLUSTER BY id;

5.3 子查询–as等
和标准SQL中的子查询语法和用法基本一致，需要注意的是，Hive中如果是从一个子查询进行SELECT查询，那么子查询必须设置一个别名。

SELECT col
FROM (
  SELECT a+b AS col
  FROM t1
) t2

SELECT *
FROM A
WHERE A.a IN (SELECT foo FROM B);
 
SELECT A
FROM T1
WHERE EXISTS (SELECT B FROM T2 WHERE T1.X = T2.Y)

*with as(用到再说，是CTE)
Hive对子查询的支持很有限。它只允许子查询出现在SELECT语句的FROM子句中。Hive 不支持 where 子句中的子查询， SQL 常用的 exist in 子句需要改写。如果发现Hive不支持你写的子查询，可以看看能不能把它写成连接操作。

SELECT a.key, a.val 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);

try：

select stName from Student where stId in(
select distinct stId from score where teId=(select teId from teacher where teName='Rona'));

–>变为：

SELECT stId,stName where teId=(select teId from teacher where teName=‘Rona’)
FROM Student LEFT SEMI JOIN score on (Student.stId = score.stId);

6. 常用运算和条件函数

关系运算：

1. 等值比较: =
2. 等值比较:<=>
3. 不等值比较: <>和!=
4. 小于比较: <
5. 小于等于比较: <=
6. 大于比较: >
7. 大于等于比较: >=
8. 区间比较
9. 空值判断: IS NULL
10. 非空判断: IS NOT NULL
11. LIKE比较: LIKE

逻辑运算：

1. 逻辑与操作: AND 、&&
2. 逻辑或操作: OR 、||
3. 逻辑非操作: NOT、!

条件函数：
If函数: if(boolean testCondition, T valueTrue, T valueFalseOrNull)

select if(1=2,100,200) from dual；

select COALESCE(null,'100','50′) from dual; 
#返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL

CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
--如果 a 等于 b ，那么返回 c ；如果 a 等于 d ，那么返回 e ；否则返回 f

DATEDIFF(datepart,startdate,enddate)
DATEDIFF() 函数返回两个日期之间的时间。datepart好像默认是day。

7.ETL变量

`${target.table}`

用户自定义变量以$开头，例如 where cityid = $abc，其中cityid是某一个字段，abc是自定义变量的名称，在执行SQL前需要先对变量进行赋值。

In addition, the curly braces may also prevent PHP from interpreting too many characters as the variable name. The curly braces are for complex variable expressions. They are interpreted by PHP, not by the SQL interface.

8. 字符串处理

select substring('abcde',3) from lxw_dual;
select substr('abcde',-1) from lxw_dual;  
select substr('abcde',3,2) fromlxw_dual;
select substr('abcde',1,2) 和selectsubstr('abcde',0,2)
--结果一样ab，默认都是从第一位开始取.

9. Bucket

对于每一个表（table）或者分区（partition）， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
(1)对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。
(2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

Clustered by(id)     按id分桶，自动哈希分桶
Sorted by(id)   按id排序
Into 4 buckets   分成4个桶

桶对应于 MapReduce 的输出文件分区：一个作业产生的桶(输出文件)和reduce任务个数相同。

10.JSON

JSON是一种轻量级的数据格式，结构灵活，支持嵌套，非常易于人的阅读和编写，而且主流的编程语言都提供相应的框架或类库支持与JSON数据的交互，因此大量的系统使用JSON作为日志存储格式。
第一个参数填写json对象变量，第二个参数使用$表示json变量标识，然后用 . 或 [] 读取对象或数组；如果输入的json字符串无效，那么返回NULL。 每次只能返回一个数据项。

 select  get_json_object(data, '$.owner') from test; --get单层值
 select  get_json_object(data, '$.store.bicycle.price') from test;--多层值
 select  get_json_object(data, '$.store.fruit[0]') from test;--数组值

11.表的连接

11.1 union
UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。(上下连接并感

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

11.2 left join
LEFT JOIN 关键字会从左表那里返回所有的行，即使在右表中没有匹配的行。

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

12.Lateral view

explode与lateral view在关系型数据库中本身是不该出现的，因为他的出现本身就是在操作不满足第一范式的数据（每个属性都不可再分），
本身已经违背了数据库的设计原理（不论是业务系统还是数据仓库系统），不过大数据技术普及后，很多类似pv，uv的数据，在业务系统中是存贮在非关系型数据库中，
用json存储的概率比较大，直接导入hive为基础的数仓系统中，就需要经过ETL过程解析这类数据，explode与lateral view在这种场景下大显身手。用于UDTF(user-defined table generating functions)中将行转成列，例如explode().

-- 内嵌查询及统计
select lx,count(*) from movie_message lateral view explode(leixing) leixing as lx group by lx;  

--把字典型元素拆成二维表元素
select name,mo,time from people_movie lateral view explode(movie) movie as mo,time;   

-- 多个lateral view
SELECT myCol1, myCol2 FROM baseTable LATERAL VIEW explode(col1) myTable1 AS myCol1
LATERAL VIEW explode(col2) myTable2 AS myCol2;

13.分组内排序

select 
*
from (select rank() over(partition by 科目 order by 分数 desc) rk,
a.* from a) t
where t.rk<=3;

hive和presto的注释用 – ；mysql注释用 ## 。
接下来的工作
ETL作业位于目标数据库上，加载作业只需要使用INSERT或者LOAD的方式导入目标表即可。