Hive个人笔记总结

	1. TBLS: 存放表的元数据

 		2. DBS:  库的元数据
             		3. COLUMNS_V2: 列的元数据
            		4. PARTITIONS

 CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  [COMMENT database_comment]  -- 库的注释说明
  [LOCATION hdfs_path]        -- 库在hdfs上的路径
  [WITH DBPROPERTIES (property_name=property_value, ...)]; -- 库的属性

 create database if not exists mydb2 
  comment 'this is my db' 
  location 'hdfs://hadoop101:9000/mydb2' 
  with dbproperties('ownner'='jack','tel'='12345','department'='IT');

drop database 库名  -- 只能删除空库
drop database 库名 cascade -- 删除非空库

use 库名 -- 切换库

-- 同名的属性值会覆盖，之前没有的属性会新增
dbproperties: alter database mydb2 set dbproperties('ownner'='tom','empid'='10001');

show databases  					 -- 查看当前所有的库
show tables in database 	 -- 查看库中所有的表
desc database 库名			 	  -- 查看库的描述信息
desc database extended 库名 -- 查看库的详细描述信息

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] -- 表中的数据要以哪种文件格式来存储，默认为TEXTFILE（文本文件）可以设置为SequnceFile或 Paquret,ORC等
[LOCATION hdfs_path]  -- 表在hdfs上的位置

- 建表时，不带EXTERNAL，创建的表是一个MANAGED_TABLE(管理表，内部表)
- 建表时，带EXTERNAL，创建的表是一个外部表

songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing

{
    "name": "songsong",
    "friends": ["bingbing" , "lili"] ,       //列表Array, 
    "children": {                      //键值Map,
        "xiao song": 18 ,
        "xiaoxiao song": 19
    }
    "address": {                      //结构Struct,
        "street": "hui long guan" ,
        "city": "beijing" 
    }
}

-- Map和Struct的区别：Struct中属性名是不变的，Map中key可以变化的！
-- 注意：在一个表中，array每个元素之间的分隔符，Map每个Entry之间的分隔符，struct每个属性之间的分隔符，都需要一致
create table people(name string,friends array<string>,
children map<string,int>,
address struct<street:string,city:string>)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';

	1. 内部表(管理表)在执行删除操作时，会将表的元数据(schema)和表位置的数据一起删除！

	1. hive会在hdfs生成表的路径；

-- 	将表改为外部表
alter table p1 set tblproperties('EXTERNAL'='TRUE');	

-- 	将表改为管理表
alter table p1 set tblproperties('EXTERNAL'='FALSE');

-- 只复制表结构，新表分区列会复制，但是分区信息不复制(show partition看不到)
create table person1 like person;  
-- 执行查询语句查询的结果，按照顺序作为新表的普通列，不能创建分区表，复制后分区列为普通列
create table 表名 as select 语句

drop table 表名 -- 删除表
truncate table 表名 -- 清空管理表，只清空数据，报错 Cannot truncate non-managed table 表名.

-- 改表的属性  
alter table 表名 set tblproperties(属性名=属性值)		

-- 对列进行调整：
-- (1)改列名或列类型
alter table 表名 change [column] 旧列名 新列名 新列类型 [comment 新列的注释] 
[FIRST|AFTER column_name] -- 调整列的顺序
-- (2)添加列和重置列
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) 

desc 表名： 查看表的描述
desc formatted 表名： 查看表的详细描述

[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 

create external table if not exists default.deptpart1(
	deptno int,
	dname string,
	loc int
)
PARTITIONED BY(area string)
row format delimited fields terminated by '\t';

create external table if not exists default.deptpart2(
	deptno int,
	dname string,
	loc int
)
PARTITIONED BY(area string, province string)
row format delimited fields terminated by '\t';

create external table if not exists default.deptpart3(
deptno int,
dname string,
loc int
)
PARTITIONED BY(area string)
row format delimited fields terminated by '\t'
location 'hdfs://hadoop101:9000/deptpart3';

show partitions 表名

-- 方式1：
alter table 表名 add partition(分区字段名=分区字段值);

-- 方式2：
-- load data:表示加载数据
-- local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表
-- inpath:表示加载数据的路径
-- overwrite:表示覆盖表中已有数据，否则表示追加
-- into table:表示加载到哪张表
-- student:表示具体的表
-- partition:表示上传到指定分区
load data [local] inpath '/opt/module/datas/student.txt' [overwrite] into table student [partition (partcol1=val1,…)];

-- 样例
load data local inpath '/opt/module/datas/dept.txt' into table dept_partition2 partition(month='201709',day='10');

-- 方式3：
msck repair table 表名;

create table stu_buck(id int, name string)
clustered by(id) 
SORTED BY (id desc)
into 4 buckets
row format delimited fields terminated by '\t';

create table stu_buck_tmp(id int, name string)
row format delimited fields terminated by '\t';
-- load data 到 stu_buck_tmp

insert into table stu_buck select * from stu_buck_tmp

怎么抽： 从第x桶开始抽样，每间隔y桶抽一桶，知道抽满 z/y桶

bucket 1 out of 2 on id：  从第1桶(0号桶)开始抽，抽第x+y*(n-1)，一共抽2桶   ： 0号桶,2号桶

select * from stu_buck tablesample(bucket 1 out of 2 on id)

bucket 1 out of 1 on id：  从第1桶(0号桶)开始抽，抽第x+y*(n-1)，一共抽4桶  ： 0号桶,2号桶,1号桶,3号桶
bucket 2 out of 4 on id：  从第2桶(1号桶)开始抽，一共抽1桶   ： 1号桶
bucket 2 out of 8 on id：  从第2桶(1号桶)开始抽，一共抽0.5桶   ： 1号桶的一半

 load  data [local] inpath 'xx' into table 表名 partition()

insert into|overwrite table 表名 select xxx | values(),(),() 
insert into: 向表中追加新的数据
insert overwrite： 先清空表中所有的数据，再向表中添加新的数据

from deptpart2
insert into table deptpart1 partition(area='huaxi') select deptno,dname,loc
insert into table deptpart1 partition(area='huaxinan') select deptno,dname,loc 

hive (default)> create table if not exists student5(
id int, name string
)
row format delimited fields terminated by '\t'
location '/user/hive/warehouse/student5';

hive (default)> dfs -put /opt/module/datas/student.txt /user/hive/warehouse/student5;

hive (default)> select * from student5;

create table if not exists student3
as select id, name from student;

IMPORT [[EXTERNAL] TABLE 表名(新表或已经存在的表) [PARTITION (part_column="value"[, ...])]]
FROM 'source_path'
[LOCATION 'import_target_path']

-- 示例
import external table importtable1  from '/export1'

insert overwrite [local] directory '/opt/module/datas/export/student' -- 没有local就是HDFS
row format delimited fields terminated by '\t' 	-- 将查询的结果，按照\t的格式导出
select * from student;

hive (default)> dfs -get /user/hive/warehouse/student/month=201709/000000_0 /opt/module/hive/datas/export/student3.txt;

hive -e 'select * from default.student;' > /opt/module/datas/export/student4.txt;

EXPORT TABLE tablename [PARTITION (part_column="value"[, ...])]
  TO 'export_target_path' [ FOR replication('eventid') ]

[WITH CommonTableExpression (, CommonTableExpression)*]  (Note: Only available starting with Hive 0.13.0)

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
 FROM table_reference
 [WHERE where_condition]
 [GROUP BY col_list]
 [ORDER BY col_list]
 [
   CLUSTER BY col_list | [DISTRIBUTE BY col_list] 
 	 [SORT BY col_list]
 ]
 [LIMIT number]

		- 全排序：结果只有一个(只有一个分区)，所有的数据整体有序
		- 部分排序：结果有多个(有多个分区)，每个分区内部有序
		- 二次排序：在排序时，比较的条件有多个

SELECT avg(nvl(comm, 0)) from emp;

SELECT CONCAT('a', 'b', 'c');
+------+--+
| _c0  |
+------+--+
| abc  |
+------+--+ 
SELECT CONCAT('a', 'b', NULL);
+-------+--+
|  _c0  |
+-------+--+
| NULL  |
+-------+--+

select concat_ws('.', 'wwww', array('facebook', 'com'));
+--------------------+--+
|        _c0         |
+--------------------+--+
| wwww.facebook.com  |
+--------------------+--+

SELECT dept_id,
sum( if(sex='男', 1, 0) ) male_count,
sum( if(sex='女', 1, 0) ) male_count
from emp_sex
GROUP BY dept_id;

+----------+-------------+-------------+--+
| dept_id  | male_count  | male_count  |
+----------+-------------+-------------+--+
| A        | 2           | 1           |
| B        | 1           | 2           |
+----------+-------------+-------------+--+

射手座,A      大海|凤姐
白羊座,A      孙悟空|猪八戒
白羊座,B      宋宋

select collect_list(constellation), blood_type from person_info group by blood_type;
+----------------------------+-------------+--+
|            _c0             | blood_type  |
+----------------------------+-------------+--+
| ["白羊座","射手座","白羊座","射手座"]  | A           |
| ["白羊座"]                    | B           |
+----------------------------+-------------+--+

select collect_set(constellation), blood_type from person_info group by blood_type;
+----------------+-------------+--+
|      _c0       | blood_type  |
+----------------+-------------+--+
| ["白羊座","射手座"]  | A           |
| ["白羊座"]        | B           |
+----------------+-------------+--+

SELECT concat_ws(',',constellation, blood_type), concat_ws('|', collect_set(name)) FROM person_info GROUP BY constellation, blood_type;
+--------+----------+--+
|  _c0   |   _c1    |
+--------+----------+--+
| 射手座,A  | 大海|凤姐    |
| 白羊座,A  | 孙悟空|猪八戒  |
| 白羊座,B  | 宋宋       |
+--------+----------+--+

select movie,explode(category) 
from  movie_info;

select movie,col1
from movie_info
join 
(select explode(category)  col1
from  movie_info) tmp;

Lateral view explode() 临时表名 as 临时列名

select movie, category_name from movie_info lateral view explode(category) table_tmp as category_name;

《疑犯追踪》   悬疑
《疑犯追踪》   动作
《疑犯追踪》   科幻
《疑犯追踪》   剧情 
《Lie to me》  悬疑
《Lie to me》  警匪
《Lie to me》  动作
《Lie to me》  心理
《Lie to me》  剧情
《战狼2》     战争
《战狼2》     动作
《战狼2》     灾难

select * from person_info2;

+-------------------------+-----------------------+-------------------------+--+
|   person_info2.names    |   person_info2.tags   |   person_info2.hobbys   |
+-------------------------+-----------------------+-------------------------+--+
| ["jack","tom","jerry"]  | ["阳光男孩","肌肉男孩","直男"]  | ["晒太阳","健身","说多喝热水"]    |
| ["marry","nancy"]       | ["阳光女孩","肌肉女孩","直女"]  | ["晒太阳","健身","看有内涵的段子"]  |
+-------------------------+-----------------------+-------------------------+--+

select name_ex, tags_ex, hobbys_ex from person_info2
lateral view explode(names) tmp1 as name_ex
lateral view explode(tags) tmp1 as tags_ex
lateral view explode(hobbys) tmp1 as hobbys_ex;

窗口函数：窗口+函数
			窗口： 函数运行时计算的数据集的范围
			函数： 运行的函数
仅仅支持以下函数：
			Windowing functions：
				LEAD:
					LEAD (scalar_expression [,offset] [,default])： 返回当前行以下N行的指定列的列值！
												如果找不到，就采用默认值
				LAG:
					LAG (scalar_expression [,offset] [,default])： 返回当前行以上N行的指定列的列值！
												如果找不到，就采用默认值
				FIRST_VALUE:
					FIRST_VALUE(列名,[false(默认)])：  返回当前窗口指定列的第一个值，
												第二个参数如果为true,代表加入第一个值为null，跳过空值，继续寻找！
				LAST_VALUE:
					LAST_VALUE(列名,[false(默认)])：  返回当前窗口指定列的最后一个值，
												第二个参数如果为true,代表加入第一个值为null，跳过空值，继续寻找！
						统计类的函数(一般都需要结合over使用)： min,max,avg,sum,count
						排名分析：  
								RANK
								ROW_NUMBER
								DENSE_RANK
								CUME_DIST
								PERCENT_RANK
								NTILE

注意：不是所有的函数在运行都是可以通过改变窗口的大小，来控制计算的数据集的范围
		 所有的排名函数和LAG,LEAD，支持使用over()，但是在over()中不能定义 window_clause

窗口函数和分组有什么区别？
		 ①如果是分组操作，select后只能写分组后的字段
		 ②如果是窗口函数，窗口函数是在指定的窗口内，对每条记录都执行一次函数
		 ③如果是分组操作，有去重效果，而partition不去重！

(rows | range) between (unbounded | [num]) preceding and ([num] preceding | current row | (unbounded | [num]) following)
(rows | range) between current row and (current row | (unbounded | [num]) following)
(rows | range) between [num] following and (unbounded | [num]) following

特殊情况： 
①在over()中既没有出现windows_clause，也没有出现order by，窗口默认为rows between UNBOUNDED  PRECEDING and UNBOUNDED FOLLOWING
②在over()中(没有出现windows_clause)，指定了order by，窗口默认为rows between UNBOUNDED  PRECEDING and CURRENT ROW

+----------------+---------------------+----------------+--+
| business.name  | business.orderdate  | business.cost  |
+----------------+---------------------+----------------+--+
| jack           | 2017-01-01          | 10             |
| tony           | 2017-01-02          | 15             |
| jack           | 2017-02-03          | 23             |
| tony           | 2017-01-04          | 29             |
| jack           | 2017-01-05          | 46             |
| jack           | 2017-04-06          | 42             |
| tony           | 2017-01-07          | 50             |
| jack           | 2017-01-08          | 55             |
| mart           | 2017-04-08          | 62             |
| mart           | 2017-04-09          | 68             |
| neil           | 2017-05-10          | 12             |
| mart           | 2017-04-11          | 75             |
| neil           | 2017-06-12          | 80             |
| mart           | 2017-04-13          | 94             |
+----------------+---------------------+----------------+--+

RANK: 允许并列，一旦有并列跳号 
ROW_NUMBER: 行号, 连续的，每个号之间差1
DENSE_RANK： 允许并列，一旦有并列不跳号
CUME_DIST：  从排序后的第一行到当前值之间数据 占整个数据集的百分比！
PERCENT_RANK：  rank-1/ 总数据量-1   
NTILE(x):  将数据集均分到X个组中，返回每条记录所在的组号

select *, rank() over(partition by subject order by score desc) from score;

+-------------+----------------+--------------+----------------+--+
| score.name  | score.subject  | score.score  | rank_window_0  |
+-------------+----------------+--------------+----------------+--+
| 孙悟空         | 数学             | 95           | 1              |
| 宋宋          | 数学             | 86           | 2              |
| 婷婷          | 数学             | 85           | 3              |
| 大海          | 数学             | 56           | 4              |
| 宋宋          | 英语             | 84           | 1              |
| 大海          | 英语             | 84           | 1              |
| 婷婷          | 英语             | 78           | 3              |
| 孙悟空         | 英语             | 68           | 4              |
| 大海          | 语文             | 94           | 1              |
| 孙悟空         | 语文             | 87           | 2              |
| 婷婷          | 语文             | 65           | 3              |
| 宋宋          | 语文             | 64           | 4              |
+-------------+----------------+--------------+----------------+--+

select name, sumscore, rank() over(order by sumscore desc)
from 
(select name, sum(score) sumscore 
from score 
group by name) tmp;

+-------+-----------+----------------+--+
| name  | sumscore  | rank_window_0  |
+-------+-----------+----------------+--+
| 孙悟空   | 250       | 1              |
| 宋宋    | 234       | 2              |
| 大海    | 234       | 2              |
| 婷婷    | 228       | 4              |
+-------+-----------+----------------+--+

select *, DENSE_RANK() over(order by tmp.sumscore DESC) FROM
(select name, subject, score, sum(score) over(partition by name) sumscore 
from score) tmp;

+-----------+--------------+------------+---------------+----------------------+--+
| tmp.name  | tmp.subject  | tmp.score  | tmp.sumscore  | dense_rank_window_0  |
+-----------+--------------+------------+---------------+----------------------+--+
| 孙悟空       | 语文           | 87         | 250           | 1                    |
| 孙悟空       | 数学           | 95         | 250           | 1                    |
| 孙悟空       | 英语           | 68         | 250           | 1                    |
| 宋宋        | 语文           | 64         | 234           | 2                    |
| 宋宋        | 数学           | 86         | 234           | 2                    |
| 宋宋        | 英语           | 84         | 234           | 2                    |
| 大海        | 语文           | 94         | 234           | 2                    |
| 大海        | 数学           | 56         | 234           | 2                    |
| 大海        | 英语           | 84         | 234           | 2                    |
| 婷婷        | 语文           | 65         | 228           | 3                    |
| 婷婷        | 数学           | 85         | 228           | 3                    |
| 婷婷        | 英语           | 78         | 228           | 3                    |
+-----------+--------------+------------+---------------+----------------------+--+

SELECT *, MAX(score) over(partition by subject order by score desc) 
FROM score;

+-------------+----------------+--------------+---------------+--+
| score.name  | score.subject  | score.score  | max_window_0  |
+-------------+----------------+--------------+---------------+--+
| 孙悟空         | 数学             | 95           | 95            |
| 宋宋          | 数学             | 86           | 95            |
| 婷婷          | 数学             | 85           | 95            |
| 大海          | 数学             | 56           | 95            |
| 宋宋          | 英语             | 84           | 84            |
| 大海          | 英语             | 84           | 84            |
| 婷婷          | 英语             | 78           | 84            |
| 孙悟空         | 英语             | 68           | 84            |
| 大海          | 语文             | 94           | 94            |
| 孙悟空         | 语文             | 87           | 94            |
| 婷婷          | 语文             | 65           | 94            |
| 宋宋          | 语文             | 64           | 94            |
+-------------+----------------+--------------+---------------+--+

-- 或者使用FIRST_VALUE
SELECT *, FIRST_VALUE(score) over(partition by subject order by score desc) 
FROM score;

plant_carbon.plant_id  | plant_carbon.plant_name  | plant_carbon.low_carbon 
user_low_carbon.user_id  | user_low_carbon.data_dt  | user_low_carbon.low_carbon 

SELECT user_id, sum(low_carbon) AS sumCarbon FROM user_low_carbon WHERE regexp_replace(data_dt, '/', '-') between '2017-1-1' and '2017-10-1'  GROUP BY user_id 
-- t1

-- 胡杨单价：
SELECT low_carbon huyangCarbon from plant_carbon where plant_id = 'p004'; -- t2

-- 沙柳单价：
SELECT low_carbon shaliuCarbon from plant_carbon where plant_id = 'p002'; -- t3

SELECT user_id, floor((sumCarbon - huyangCarbon) / shaliuCarbon) AS shaliuCount 
FROM t1 join t2 join t3 
order by shaliuCount desc
LIMIT 11;			-- t4

SELECT user_id, shaliuCount, rank() over(order by shaliuCount desc),
(shaliuCount - LEAD(shaliuCount, 1, 0)) over(order by shaliuCount desc)
FROM t4

SELECT user_id, shaliuCount, rank() over(order by shaliuCount desc),
shaliuCount - LEAD(shaliuCount, 1, 0) over(order by shaliuCount desc)
FROM
(SELECT user_id, floor((sumCarbon - huyangCarbon) / shaliuCarbon) AS shaliuCount 
FROM 
(SELECT user_id, sum(low_carbon) AS sumCarbon FROM user_low_carbon WHERE regexp_replace(data_dt, '/', '-') between '2017-1-1' and '2017-10-1'  GROUP BY user_id) t1 join (SELECT low_carbon huyangCarbon from plant_carbon where plant_id = 'p004') t2 
join (SELECT low_carbon shaliuCarbon from plant_carbon where plant_id = 'p002') t3 
order by shaliuCount desc
LIMIT 11
) t4;

+----------+--------------+----------------+------+--+
| user_id  | shaliucount  | rank_window_0  | _c3  |
+----------+--------------+----------------+------+--+
| u_007    | 66           | 1              | 3    |
| u_013    | 63           | 2              | 10   |
| u_008    | 53           | 3              | 7    |
| u_005    | 46           | 4              | 1    |
| u_010    | 45           | 5              | 1    |
| u_014    | 44           | 6              | 5    |
| u_011    | 39           | 7              | 2    |
| u_009    | 37           | 8              | 5    |
| u_006    | 32           | 9              | 9    |
| u_002    | 23           | 10             | 1    |
| u_004    | 22           | 11             | 22   |
+----------+--------------+----------------+------+--+

plant_carbon.plant_id  | plant_carbon.plant_name  | plant_carbon.low_carbon 
user_low_carbon.user_id  | user_low_carbon.data_dt  | user_low_carbon.low_carbon 

select user_id, regexp_replace(data_dt, '/', '-') dt, sum(low_carbon) carbonPerDay
from user_low_carbon
where year(regexp_replace(data_dt, '/', '-')) = 2017
GROUP BY user_id, data_dt  
having carbonPerDay >= 100  -- t1

	b)如果当前日期位于连续三天中的第二天，使用当前日期减去 当前日期前一天的日期，差值一定为1
																	使用当前日期减去 当前日期后一天的日期，差值一定为-1
											
	c)如果当前日期位于连续三天中的第三天，使用当前日期减去 当前日期前一天的日期，差值一定为1
																	使用当前日期减去 当前日期前二天的日期，差值一定为2
											
	满足a,b,c其中之一，当前日期就符合要求

select  user_id,dt,carbonPerDay,
datediff(dt,lag(dt,1,'1970-1-1') over(partition by user_id order by dt)) pre1diff,
datediff(dt,lag(dt,2,'1970-1-1') over(partition by user_id order by dt)) pre2diff,
datediff(dt,lead(dt,1,'1970-1-1') over(partition by user_id order by dt)) after1diff,
datediff(dt,lead(dt,2,'1970-1-1') over(partition by user_id order by dt)) after2diff
from  t1  -- t2

select user_id,regexp_replace(dt,'-','/') newdt,carbonPerDay
from  t2
where  (after1diff=-1 and  after2diff=-2)  or (pre1diff=1 and after1diff=-1) or (pre1diff=1 and pre2diff=2) -- t3

select u.*
from t3 join  user_low_carbon u
on t3.user_id=u.user_id and t3.newdt=u.data_dt

select u.*
from 
(select user_id,regexp_replace(dt,'-','/') newdt,carbonPerDay
from  
(select  user_id,dt,carbonPerDay,
datediff(dt,lag(dt,1,'1970-1-1') over(partition by user_id order by dt)) pre1diff,
datediff(dt,lag(dt,2,'1970-1-1') over(partition by user_id order by dt)) pre2diff,
datediff(dt,lead(dt,1,'1970-1-1') over(partition by user_id order by dt)) after1diff,
datediff(dt,lead(dt,2,'1970-1-1') over(partition by user_id order by dt)) after2diff
from  (select  user_id,regexp_replace(data_dt,'/','-') dt,sum(low_carbon) carbonPerDay
from  user_low_carbon
where year(regexp_replace(data_dt,'/','-'))=2017
group by  user_id,data_dt 
having  carbonPerDay >= 100)t1 )t2
where  (after1diff=-1 and  after2diff=-2)  or (pre1diff=1 and after1diff=-1) or (pre1diff=1 and pre2diff=2))t3 join  user_low_carbon u
on t3.user_id=u.user_id and t3.newdt=u.data_dt

		A						B
1.	a						b						  a-b
2.	a+X					b+Y				(a-b)+(x-y)
3.	a+2X				b+2Y			(a-b)+2(x-y)
4.	a+3X				b+3Y
n.	a+(n-1)X		b+(n-1)Y

		dt				 列B						diff
	2017-1-1			1					2016-12-31
	2017-1-3			2					2017-1-1
	2017-1-5			3					2017-1-2
	2017-1-6			4					2017-1-2
	2017-1-7			5					2017-1-2
	2017-1-8			6					2017-1-2
	2017-1-12			7					2017-1-5
	2017-1-13			8					2017-1-5
	2017-1-15			9					2017-1-6
	2017-1-16			10				2017-1-6
	2017-1-17			11				2017-1-6

-- 判断连续
select  user_id, dt, carbonPerDay, 
date_sub(dt, row_number() over(partition by user_id order by dt) ) diff
from t1 -- t2

-- 判断连续的天数超过3天
select user_id, dt, carbonPerDay, diff, count(*) over(partition by user_id,diff) diffcount
from t2 -- t3

-- 过滤超过3天的数据
select user_id, dt
from t3
where diffcount >= 3   -- t4

-- 关联原表求出结果
select user_id, dt
from
(
select user_id, dt, carbonPerDay, diff, count(*) over(partition by user_id,diff) diffcount
from
(
select  user_id, dt, carbonPerDay, 
date_sub(dt, row_number() over(partition by user_id order by dt) ) diff
from (
select user_id, regexp_replace(data_dt, '/', '-') dt, sum(low_carbon) carbonPerDay
from user_low_carbon
where year(regexp_replace(data_dt, '/', '-')) = 2017
GROUP BY user_id, data_dt  
having carbonPerDay >= 100 ) t1
) t2
) t3
where diffcount >= 3 order by user_id, dt asc;

<dependency>
  <groupId>org.apache.hive</groupId>
  <artifactId>hive-exec</artifactId>
  <version>1.2.1</version>
</dependency>

create [temporary] function 函数名  as  自定义的函数的全类名

-- 样例
create temporary function show_name as 'cn.itcats.hive.udf.MyFunction';

-- 例如
SELECT * FROM employees;

-- 开启本地mr
set hive.exec.mode.local.auto=true;
-- 设置local mr的最大输入数据量，当输入数据量小于这个值时采用local mr的方式，默认为134217728，即128M
set hive.exec.mode.local.auto.inputbytes.max=50000000;
-- 设置local mr的最大输入文件个数，当输入文件个数小于这个值时采用local mr的方式，默认为4
set hive.exec.mode.local.auto.input.files.max=10;

EXPLAIN [EXTENDED | DEPENDENCY | AUTHORIZATION] query

hive (default)> explain select * from emp;
hive (default)> explain select deptno, avg(sal) avg_sal from emp group by deptno;

hive (default)> explain extended select * from emp;
hive (default)> explain extended select deptno, avg(sal) avg_sal from emp group by deptno;

<property>
 <name>mapreduce.map.speculative</name>
 <value>true</value>
</property>

<property>
 <name>mapreduce.reduce.speculative</name>
 <value>true</value>
</property>

 <property>
  <name>hive.mapred.reduce.tasks.speculative.execution</name>
  <value>true</value>
 </property>

<property>
 <name>mapreduce.job.jvm.numtasks</name>
 <value>10</value>
</property>

<property>
  <name>hive.mapred.mode</name>
  <value>strict</value>
</property>

-- 打开任务并行执行
set hive.exec.parallel=true;
-- 同一个sql允许最大并行度，默认为8。
set hive.exec.parallel.thread.number=16;

set hive.input.format = org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

-- 1．执行查询
hive (default)> select count(*) from emp;
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1

-- 2．设置最大切片值为100个字节
hive (default)> set mapreduce.input.fileinputformat.split.maxsize=100;
hive (default)> select count(*) from emp;
Hadoop job information for Stage-1: number of mappers: 6; number of reducers: 1

set mapreduce.job.reduces = 15;

-- 情形一：A left join B  on A.c = b.c
-- 假如不需要id为null的数据！此时可以将A表中id为null的字段提前过滤，减少MR在执行时，输入的数据量！
-- 解决：将null值过滤，过滤后再执行Join!

-- 测试不过滤空id
insert overwrite table jointable2 
select n.* from nullidtable n left join ori o on n.id = o.id; -- Time taken: 18.743 seconds

-- 测试过滤空id
insert overwrite table jointable2
select n.* from (select * from nullidtable where id is not null ) n  left join ori o on n.id = o.id;  -- Time taken: 7.537 seconds

情形二： A表中c字段为null的数据也需要，不能过滤，如何解决数据倾斜？
注意：①可以将null替换为一个不影响执行结果的随机值！
		 ②注意转换后类型匹配的问题
insert overwrite local directory '/home/atguigu/joinresult'
select n.* from nullidtable n full join ori o on 
case when n.id is null then -floor(rand()*100) else n.id end = o.id;

set hive.auto.convert.join = true; -- 默认为true

set hive.mapjoin.smalltable.filesize = 25000000;

hive.map.aggr = true  -- Combiner 

hive.groupby.mapaggr.checkinterval = 100000  -- (1)和(2)配合使用

hive.groupby.skewindata = true

select count(distinct id) from bigtable;

select count(id) from (select id from bigtable group by id) a;  -- 优化

select o.id from bigtable b
join ori o on o.id = b.id
where o.id <= 10; -- Time taken: 34.406 seconds, Fetched: 100 row(s)

select b.id from bigtable b
join (select id from ori where id <= 10 ) o on b.id = o.id; -- Time taken: 30.058 seconds, Fetched: 100 row(s)

hive.exec.dynamic.partition=true

hive.exec.dynamic.partition.mode=nonstrict

hive.exec.max.dynamic.partitions=1000

hive.exec.max.dynamic.partitions.pernode=100

hive.exec.max.created.files=100000

hive.error.on.empty.partition=false

create table ori_partitioned(id bigint, time bigint, uid string, keyword string, url_rank  int, click_num int, click_url string)
partitioned by (p_time bigint)
row format delimited fields terminated by '\t';  

load data local inpath '/home/atguigu/ds1'  into table ori_partitioned partition(p_time='20111230000010') ;  
load data local inpath '/home/atguigu/ds2'  into table ori_partitioned partition(p_time='20111230000011') ;  

create table ori_partitioned_target(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string)
PARTITIONED BY (p_time STRING)
row format delimited fields terminated by '\t';  

set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = nonstrict;
set hive.exec.max.dynamic.partitions = 1000;
set hive.exec.max.dynamic.partitions.pernode = 100;
set hive.exec.max.created.files = 100000;
set hive.error.on.empty.partition = false;

insert overwrite table ori_partitioned_target partition (p_time) 
select id, time, uid, keyword, url_rank, click_num, click_url, p_time from ori_partitioned; 

show partitions ori_partitioned_target;