Hive_HQL

一：驱动器：Driver
（1）解析器（SQL Parser）：将SQL字符串转换成抽象语法树AST，这一步一般都用第三方工具库完成，比如antlr；对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。
（2）编译器（Physical Plan）：将AST编译生成逻辑执行计划MR。
（3）优化器（Query Optimizer）：对逻辑执行计划进行优化。
（4）执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说，就是MR/Spark。

1.数据加载：将本地/opt/module/datas/student.txt这个目录下的数据导入到hive的student(id int, name string)表中。
hive> create table student(id int, name string) ROW FORMAT DELIMITED FIELDS TERMINATED
BY ‘\t’;
hive> load data local inpath ‘/opt/module/datas/student.txt’ into table student;
1．“-e”不进入hive的交互窗口执行sql语句
bin/hive -e “select id from student;”
2．“-f”执行脚本中sql语句
（1）在/opt/module/datas目录下创建hivef.sql文件
touch hivef.sql
文件中写入正确的sql语句
select from student; > ./a.txt
hive (default)> set mapred.reduce.tasks=100;
二：DDL
1.hive (default)> create database db_hive2 location ‘/db_hive2.db’;
2.hive> show databases like 'db_hive’;
3.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]
（2）EXTERNAL创建一个外部表外部表只删除元数据，不删除数据。
*分区表
谓词下推 ： 先走where
hive (default)> create table dept_partition2(
deptno int, dname string, loc string
)
partitioned by (month string, day string)
row format delimited fields terminated by ‘\t’;
hive (default)> load data local inpath ‘/opt/module/datas/dept.txt’ into table
default.dept_partition2 partition(month=‘201709’, day=‘13’);
hive (default)> select * from dept_partition2 where month=‘201709’ and day=‘13’;
把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式
（1）方式一：上传数据后修复
上传数据
hive (default)> dfs -mkdir -p
/user/hive/warehouse/dept_partition2/month=201709/day=12;
hive (default)> dfs -put /opt/module/datas/dept.txt /user/hive/warehouse/dept_partition2/month=201709/day=12;
查询数据（查询不到刚上传的数据）
hive (default)> select * from dept_partition2 where month=‘201709’ and day=‘12’;
执行修复命令
hive> msck repair table dept_partition2;
再次查询数据
hive (default)> select * from dept_partition2 where month=‘201709’ and day=‘12’;
（2）方式二：上传数据后添加分区
上传数据
hive (default)> dfs -mkdir -p
/user/hive/warehouse/dept_partition2/month=201709/day=11;
hive (default)> dfs -put /opt/module/datas/dept.txt /user/hive/warehouse/dept_partition2/month=201709/day=11;
执行添加分区
hive (default)> alter table dept_partition2 add partition(month=‘201709’,
day=‘11’);
查询数据
hive (default)> select * from dept_partition2 where month=‘201709’ and day=‘11’;
（3）方式三：创建文件夹后load数据到分区
创建目录
hive (default)> dfs -mkdir -p
/user/hive/warehouse/dept_partition2/month=201709/day=10;
上传数据
hive (default)> load data local inpath ‘/opt/module/datas/dept.txt’ into table
dept_partition2 partition(month=‘201709’,day=‘10’);
查询数据
hive (default)> select * from dept_partition2 where month=‘201709’ and day=‘10’;

DML数据操作
1．语法
hive> load data [local] inpath ‘/opt/module/datas/student.txt’ overwrite | into table student [partition (partcol1=val1,…)];
（1）load data:表示加载数据
（2）local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表
（3）inpath:表示加载数据的路径
（4）overwrite:表示覆盖表中已有数据，否则表示追加
（5）into table:表示加载到哪张表
（6）student:表示具体的表
（7）partition:表示上传到指定分区
2．实操案例
（0）创建一张表
hive (default)> create table student(id string, name string) row format delimited fields terminated by ‘\t’;
（1）加载本地文件到hive
hive (default)> load data local inpath ‘/opt/module/datas/student.txt’ into table default.student;
5.1.2 通过查询语句向表中插入数据（Insert）
hive (default)> insert overwrite table student partition(month=‘201708’)
select id, name from student where month=‘201709’;
多插入模式（根据多张表查询结果）
hive (default)> from student
insert overwrite table student partition(month=‘201707’)
select id, name where month=‘201709’
insert overwrite table student partition(month=‘201706’)
select id, name where month=‘201709’;

create external table if not exists default.emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int)
row format delimited fields terminated by ‘\t’;

create external table if not exists default.dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by ‘\t’;

empno ,ename ,job ,mgr ,hiredate , sal , comm ,deptno)
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
load data local inpath ‘/opt/module/datas/emp.txt’ into table default.emp;
deptno int,dname string,loc int
10 ACCOUNTING 1700
20 RESEARCH 1800
30 SALES 1900
40 OPERATIONS 1700

select count(*) from emp group by deptno; group by 通常前面有聚合函数 。等同于MR中的GroupingCompare

select * from emp order by sal; 默认升序 | desc //默认一个Reduce
select ename, deptno, sal from emp order by deptno, sal ; //参考MR中的二次排序
set mapreduce.job.reduces=3;
select * from emp sort by empno desc; //区内有序 分区按照MR中的HASH分区原则 没有分区字段 所以随机分区
insert overwrite local directory ‘/opt/module/datas/dis-by’
> select * from emp distribute by deptno sort by sal; //按照deptno分区 区内有序 等同于MR中的Partition
当分区字段和排序字段相同时
insert overwrite local directory ‘/opt/module/datas/cluster-by’
> select * from emp cluster by deptno;
4个by
oreder by 全局排序
sort by 与distribute by 连用//分区内有序 rededuce个数要多个
cluster by 当分区内字段排序与区内排序字段相同时
注意 建表时的分区表是partitioned by
常用函数：
NVL(comn,-1) 空字段赋值
select nvl(comm,empno) from emp;
时间类 (时间函数只对-起作用)
date_format()
select date_format(‘1987-5-23’,‘yyyy-MM’);
select date_add(‘2019-07-03’,-3) //时间相加
select datediff(‘2019-07-03’,‘2019-06-05’) //两个时间向减
select regexp_replace(‘2019/07/02’,’/’,’-’); //对时间先做转换 再做其他处理
name dept_id sex

	悟空	A		男
	大海	A		男
	宋宋	B		男
	凤姐	A		女
	婷姐	B		女
	婷婷	B		女
	//求每个部门男的多少人 女的多少人
	//思考 每个部门 将部门分组

select =======select
dept_id, =======dept_id,
sum(if(sex=‘男’,1,0)) as male_count, =======sum(case sex when ‘男’ then 1 else 0 end) male_count,
sum(if(sex=‘女’,1,0)) as famale_count =======sum(case sex when ‘女’ then 1 else 0 end) female_count
from =======from
emp_sex =======emp_sex
group by =======group by
dept_id; ======= dept_id;

行转列(其实是将一行中的多列数据连接成一列)
select concat(deptno,’-’,dname) from dept;
select concat_ws(’-’,deptno,dname) from dept; //连接中的类型必须相同
多行转一行 (形成数组) 收集多列数据
select collect_set(deptno) from dept; //形成一个数组形式的一列值
collect_set() 聚合函数 类似与sum() avag() 多进一出 aggregate() reduce()
select concat_ws(’-’,collect_set(dname)) from dept;
需求复杂时要考虑嵌套子查询
select
concat(constellation,’,’,blood_)
create table person_info(
name string,
constellation string,
blood_type string)
row format delimited fields terminated by “\t”;
load data local inpath ‘/opt/module/datas/person_info.txt’ into table person_info
孙悟空 白羊座 A
大海 射手座 A
宋宋 白羊座 B
猪八戒 白羊座 A
凤姐 射手座 A
//冷静
select
concat_ws(’-’,constellation,blood_type) as constellation_blood
from person_info;t1

select
constellation_blood,
concat_ws(’|’,collect_set(name))
from
(select
concat(constellation,’,’,blood_type) as constellation_blood,
name
from person_info
)t1
group by constellation_blood

《疑犯追踪》 悬疑,动作,科幻,剧情
《Lie to me》 悬疑,警匪,动作,心理,剧情
《战狼2》 战争,动作,灾难
create table movie_info(
movie string,
category array)
row format delimited fields terminated by “\t”
collection items terminated by “,”;
EXPLODE ()udtf 一进多出
LATERAL VIEW udtf(expression)
select
movie,
category_name
from
movie_info lateral view explode(category) new_tamp_view as category_name //形成新表 侧写表
《疑犯追踪》 悬疑
《疑犯追踪》 动作
《疑犯追踪》 科幻
《疑犯追踪》 剧情
《Lie to me》 悬疑
《Lie to me》 警匪
《Lie to me》 动作
《Lie to me》 心理
《Lie to me》 剧情
《战狼2》 战争
《战狼2》 动作
《战狼2》 灾难
接下来 按电影分组 后面用cancat
窗口函数：
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
1.（1）查询在2017年4月份购买过的顾客及总人数
select
name,count(*)
from business
where substring(orderdate,1,7)=‘2017-04’
group by name;

select
name,count(*) over() //over() 必须跟在聚合函数后面 后面的开窗作用范围仅仅给前面的聚合函数使用
from business
where substring(orderdate,1,7)=‘2017-04’
group by name;
//over() 区别 over()也叫分组 是对前面的每一条数据（分组）开窗 窗口大小就是over里面的范围
与group by 区别 一组一个值 分组函数 多个字段分组后只有一条数据 可做去重操作。
（2）查询顾客的购买明细及月购买总额
select
*,sum(cost) over()
from business;
（3）上述的场景,要将cost按照日期进行累加
select
orderdate,cost
from business
order by orderdate;

select
orderdate,sum(cost) over()
from business
order by orderdate;

select
orderdate,sum(cost) over(order by orderdate)
from business

select
name,orderdate,cost,sum(cost) over(group by name) //不能用group by 用distribute by
from business
select
name,orderdate,cost,sum(cost) over(distribute by name)
from business
//按人分组 按日期排序 累加 分组条件可以多个。
select
name,orderdate,cost,sum(cost) over(distribute by name,orderdate sort by orderdate)
from business

窗口函数
1．相关函数说明
OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化
CURRENT ROW：当前行
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点
LAG(col,n)：往前第n行数据
LEAD(col,n)：往后第n行数据
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。
（4）查询顾客上次的购买时间 lag（）函数 不能有group by 后必须跟over开窗操作
跨行操作 lag() lead()
select
name,
orderdate,
cost,
lag(orderdate,1,‘1980-1-1’) over(distribute by name sort by orderdate)
from
business;

select
name,
orderdate,
cost,
lead(orderdate,1,‘9999’) over(distribute by name sort by orderdate)
from
business;
求同一用户两次购买的时间差。
select
name,
orderdate,
cost,
lag(orderdate,1,‘1980-1-1’) over(distribute by name sort by orderdate) as nextday
from
business;
OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化
之后接 CURRENT ROW：当前行
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点
之前 放 LAG(col,n)：往前第n行数据
LEAD(col,n)：往后第n行数据
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

select name,orderdate,cost,
sum(cost) over() as sample1,–所有行相加
sum(cost) over(partition by name) as sample2,–按name分组，组内数据相加
sum(cost) over(partition by name order by orderdate) as sample3,–按name分组，组内数据累加
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,–和sample3一样,由起点到当前行的聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --当前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,–当前行和前边一行及后面一行
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行
from business;
（4）查看顾客上次的购买时间