2.Hive

数据仓库

Data Warehouse可简称DW或者DWH
目的：构建面向分析的集成化数据环境，出于分析性报告和决策支持的目的而创建。
仓库：数据来源于外部，并且开放给外部应用，不生产小号数据，不是工厂。

• List item

hive的底层执行引擎有3种：
mapreduce（默认）
tez（支持DAG作业的计算框架）
spark（基于内存的分布式计算框架）

特征

面向主题subject-oriented
集成的integrated
非易失的non-volatile
时变的time-variant
数据集合

数据仓库和数据库的区别

操作型处理，也叫联机事务处理OLTP（on-line transaction processing）也可称面向交易的处理系统。针对具体业务在数据库联机的日常操作，对少数记录进行查询、修改。用户较为关心操作的响应时间、数据的安全性、完整性和并发性。面向事务。
分析性处理，也叫联机分析处理OLAP（on-line analytical processing），针对某些主题的历史数据进行分析。面向主题。

数据仓库分层架构

源数据层ODS：沿用外围系统的数据结构和数据，不对外开放，临时存储层。

数据仓库层DW：对源数据系统进行了清洗（去了杂质）后的数据。

数据应用层DA或者APP：前端应用直接读取的数据源，根据报表、专题分析需求而计算生成的数据。

数据仓库从各数据源获取数据以及数据在仓库内的数据转换和流动都可认为是ETL（抽取Extra，转化Transfer，装载Load），是数据仓库的流水线，是数据仓库的血液。工作任务保证ETL的正常和稳定。

分层原因：空间换时间，黑盒变白盒，数据错误是只需要局部调整。

Hive

基于Hadoop的一个数据仓库工具。
将结构化的数据文件映射成数据库表，提供类SQL查询功能。
本质将SQL转换为MapReduce的任务进行运算，底层HDFS提供数据的存储支持。mr客户端。只适合做海量离线数据统计分析。

客户端（发出sql语句）
Hive处理转换成MapReduce
MapReduce运行

优点：

类SQL语法，提供快速开发的能力
支持用户自定义函数

缺点

延迟很严重，不支持事务

用户接口：client

CLI（hive shell）
JDBC/ODBC（java访问hive）
WEBUI（浏览器访问hive）

元数据：metastore

表名、表所属的数据库、表的拥有者、列/分区字段、表的类型 、数据所在目录
默认存储在自带的derby数据库中，推荐使用MySQL存储Merastore

Hadoop集群

使用HDFS进行存储，使用MapReduce进行计算。

启动器：Driver

SQL Parser解析器：将SQL字符串转换成抽象语法树AST，比如表是否存在，字段是否存在
Physical Plan编译器：将AST编译生成逻辑执行计划
Query Optimizer优化器：对逻辑执行计划进行优化
Execution执行器：把逻辑执行计划转换成可以运行的物理计划，mr任务。

使用Hive之前：
先启动hadoop集群，hive表数据一般存储在HDFS上。
MySQL服务，因为对hive操作过程中，需要访问mysql中存储元数据的库和表。

交互方式

• shell

$ hive#运行

• JDBC

$ hive --service hiveserver2#前台运行
$ nohup hive --service hiveserver2 &#后台启动
#前台启动的话 还需要beeline连接
！quit#退出

• Hive的命令

#使用-e参数直接执行hql语句
hive -e "show databases"
#使用-f参数执行包含hql语句的文件
hive -f ////hive.sql

Hive数据类型

复合数据类型

创建表的时候可以指定每行数据的格式，如果使用的是妇科数据类型，还需要指定复合数据类型中的元素分隔符。

Array:数据为相同类型
t_array.txt字段空格分割
1 zhangsan beijing,shanghai
2 lisi shanghai,tianjin
#创建语法
create table t_array(
id string,
name string,
locations array<string>
)row format delimited fields terminated by ’ ’ collection items terminated by ‘,’;
#加载数据
load data local inpath ‘/home/hadoop/t_array.txt’ into table t_array;
#查询数据
select id,name,locations[0],locations[1] from t_array;

map类型
key/value
t_map.txt
name:zhangsan#age:30
name:lisi#age:40
建表语法
create table t_map(
id string,
info map<string,string>
)row format delimited fields terminated by ’ ’ collection items terminated by ‘#’ map keys terminated by ‘:’;
加载数据
load data local inpath ‘/home/hadoop/t_map.txt’ into table t_map;
查询数据
select id, info[‘name’], info[‘age’] from t_map;

struct类型
可以存储不同类型的数据
t_struct.txt
1 zhangsan:30:bejing
2 list:40:shanghai
建表语法
create table t_struct(
id string,
info struct<name:string, age:int, address:string>
)row format delimited fields terminated by ’ ’ collection items terminated by ‘:’;
加载数据
load data local inpath ‘/t_struct.txt’ into table t_struct;
查询数据
select id, info.name,info.age,info.address from t_struct;

创建表

create table complex(
         col1 array<int>,
         col2 map<string,int>,
         col3 struct<a:string,b:int,c:double>
)

DDL操作

#创建数据库
create database db_hive;
create database if not exists db_hive;
#显示所有数据库
show databases;
#查询数据库
show databases like 'hive*';
#查看数据库详情
desc database db_hive;
#显示数据库详细信息
desc database extended db_hive;
#切换当前数据库
use db_hive;
#删除数据库
# 删除为空的数据库
hive> drop database db_hive;
# 如果删除的数据库不存在，最好采用if exists 判断数据库是否存在
hive> drop database if exists db_hive;
# 如果数据库中有表存在，这里需要使用cascade强制删除数据库
hive> drop database if exists db_hive cascade;

建表语法

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]  row format delimited fields terminated by “分隔符”
[STORED AS file_format] 
[LOCATION hdfs_path]

• CREATE TABLE 闯将一个指定名字的表
• EXTERNAL创建一个外部表，在建表的同时指定一个指向实际数据的路径LOCATION，指定表的数据保存在哪里
• COMMENT为表和列增加注释
• PARTITIONED BY创建分区表
• CLUSTERED BY创建分桶表
• SORTED BY按照字段排序（一般不用）
• ROW FORMAT指每一行中字段的分隔符row format delimited fields terminated by ‘\t’
• STORED AS指定存储文件类型：常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、ORCFILE（列式存储格式文件）。如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。
• LOCATION 指定表在HDFS上的存储位置。

#创建内部表 标准语句直接建表
use myhive;
create table stu(id int , name string);
insert into stu(id,name) values(1,"zhangsan");
select * from stu;
#查询建表法 通过as查询语句完成建表，将子查询的结果存入新表中，表中有数据
create rable if not exists myhive.stu1 as selsct id,name from stu;
#like建表法 跟胡已经存在的表结构创建表，无数据
create table if not exist myhibe.stu2 like stu;
desc formatted myhive.stu
#查询表的类型
desc formatted myhive.stu;

create table if not exists myhive.stu3(id int, name string)
row format delimited fields terminated by '\t' 
stored as textfile 
location '/user/stu3';

外部表是指定其他的hdfs路径的数据加载到表中来的
所以hive表认为自己不完全独占这份数据，删除hive表时，数据仍会存放在hdfs中，不会删掉。加上external关键字。
一般外部表用在数据仓库的ODS层，内部表用在数据仓库的DW层。
insert方法不推荐，实际工作中使用load方式加载数据到内部表或者外部表
load 可以从哪个本地文件加载也可以从hdfs上面的数据进行加载

#将数据上传到这个路径下
cd /install/hivedatas
hdfs dfs -mkdir -p /hdfsload/hivedatas
#将文件上传到这个目录下
hdfs dfs -put teacher.csv /hdfsload/hivedatas
# 在hive的客户端当中执行
load data inpath '/hdfsload/hivedatas' overwrite into table myhive.teacher;

内部表和外部表的互相交换

#内部表转换为外部表
alter table stu set tblproperties('EXTERNAL'='TRUE');
#外部表转换为内部表
alter table teacher set tblproperties('EXTERNAL'='FALSE');

如果hive中所有数据都存在一个文件夹下，在使用MR计算程序的时候，读取一整个目录下面的所有文件来进行计算，会变得特别慢，因为数据量太大。
实际工作当中一般时计算前一天的数据，只需要将前一天的数据挑出来放到一个文件夹下面，专门去计算前一天的数据。
可以在hive分区表中通过份文件的形式，将每一天的数据都分成一个文件夹，计算数据时只计算前一天 的数据。分治。

#在文件系统上建立文件夹，把表的数据放在不同文件夹下，加快查询速度
#创建分区表语法
create table score(s_id string, c_id string, s_score int)partitioned by (month string) row format delimited fields terminated by '\t';
#创建一个表带多个分区
create table score2(s_id string ,c_if string, s_score int)partitioned by (year string,month string,day string)row format delimited fields terminated by '\t';
#加载数据到分区表
load local inpath '/.../score.csv' into table score partition(month='201806'); 
#加载数据到多分区表中
load data local inpath '///score.csv' into table score2 partition(year='2018',month='06',day='01
');
#查看分区
show partitions score;
#增加一个分区
alter table score add partition(month='201805');
#同时添加多个分区
alter table score add partition(month='201804') partition(month = '201803');
#添加完分区后就可以在hdfs文件系统当中看到表下多了一个文件夹
#删除分区
alter table score drop partition(month = '201806');

DDL操作和DML操作

分桶是相对分区和hive表进行更细粒度的划分
分桶是将整个数据内容按照某列取hash值，对桶中的个数取模的方式决定该记录存放在哪个桶当中，具有相同hash值的数据进入同一个文件中。

在创建分桶表之前要执行命令
set hive.enforce.bucketing=true;#开启对分桶表的支持
set mapreduce.job.reduces=4;#设置与桶相同的reduce个数

use myhive;
set hive.enforce.bucketing=true; 
set mapreduce.job.reduces=4;  
#创建分桶表
create table myhive.user_buckets_demo(id int , name string)
clustered by(id)
into 4 buckets
row format delimited fields terminated by '\t';
#创建普通表
create table user_demo(id int,name string)
row format delimited fields terminated by '\t';

准备数据文件buckets.txt

1 anzhulababy1
2 anzhulababy2
3 anzhulababy3
4 anzhulababy4
5 anzhulababy5
6 anzhulababy6
7 anzhulababy7
8 anzhulababy8
9 anzhulababy9
10 anzhulababy10

加载数据到普通表user_demo中

load data local inpath '/./././user_bucket.txt'
overwrite into table user_demo;

加载数据到桶表user_buckets_demo中

insert into table user_buckets_demo select * from user_demo;

hive的数据导入

直接向表中插入数据（不推荐）

create table score3 like score;
insert into table score3 partition(month = '201807')
values('001','002','100');

通过load加载数据（必须会）

load data [local] inpath 'dataPath' [overwrite] into table student [partition(partcol1=val1,...)];
#example
load data local inpath 'score.csv' overwrite into table score partition(month='201806');

通过查询加载数据（必须会）

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;
INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;

create table score5 like score;
insert overwrite table score5
patition(month = '201806')
select s_id,c_id,s_score from score;

查询语句中创建表并加载数据

create table score6 as select *from score;

创建表时指定location

create external table score7(s_id string,c_id string,s_score int)row format delimited fields terminated by '\t' location '/myscore7';
#也可以直接在hive客户端下面通过dfs进行操作hdfs
dfs -mkdir -p /myscore7;
dfs -put score.csv /myscore7;

export导出与import导入hive表数据（内部表操作）

create table teacher2 like teacher;
export table teacher to '/tt';
import table teacher from '/tt';

Hive数据导出

INSERT OVERWRITE [LOCAL] DIRECTORY directory1
  [ROW FORMAT row_format] [STORED AS file_format] (Note: Only available starting with Hive 0.11.0)
  SELECT ... FROM ...

#将查询的结果导出到本地
insert overwrite local directory '/stu' select * from stu;
#将查询的结果格式化导出到本地
insert overwrite local directory '///tu2' row format delimited fields terminated by ',' select * from stu;
#没有local就是导出到HDFS中。

hive shell命令导出：

#导出myhive.stu表的数据到本地磁盘文件/kkb///tudent1.txt

export导出到HDFS上

export table  myhive.stu to 'stuexport';

Hive的静态分区和动态分区

静态分区

表的分区字段需要开发人员手动给定
创建分区表

use muhive;
create table order_partition(
order_number string,
order_price double,
order_time string
)
partitioned BY(month string)
row format delimited fields terminated by '\t';

数据

cd /hivedatas
vim order.txt 

10001	100	2019-03-02
10002	200	2019-03-02
10003	300	2019-03-02
10004	400	2019-03-03
10005	500	2019-03-03
10006	600	2019-03-03
10007	700	2019-03-04
10008	800	2019-03-04
10009	900	2019-03-04

加载到分区表

load data local inpath '/txt' overwrite into table order_partition partition(month ='2019-03');

查询结果数据

select * from order_partition where month ='2019-03';
10001   100.0   2019-03-02      2019-03
10002   200.0   2019-03-02      2019-03
10003   300.0   2019-03-02      2019-03
10004   400.0   2019-03-03      2019-03
10005   500.0   2019-03-03      2019-03
10006   600.0   2019-03-03      2019-03
10007   700.0   2019-03-04      2019-03
10008   800.0   2019-03-04      2019-03
10009   900.0   2019-03-04      2019-03

动态分区

按照需求实现把数据自动导入相应的分区，不需要手动指定分区字段的值。根据字段不同的值，自动导入到分区不同的分组中。
创建表

--创建普通表
create table t_order(
    order_number string,
    order_price  double, 
    order_time   string
)row format delimited fields terminated by '\t';

--创建目标分区表
create table order_dynamic_partition(
    order_number string,
    order_price  double    
)partitioned BY(order_time string)
row format delimited fields terminated by '\t';
-- 要想进行动态分区，需要设置参数
-- 开启动态分区功能
hive> set hive.exec.dynamic.partition=true; 
-- 设置hive为非严格模式
hive> set hive.exec.dynamic.partition.mode=nonstrict; 
hive> insert into table order_dynamic_partition partition(order_time) select order_number, order_price, order_time from t_order;
##查看分区
 hive> show partitions order_dynamic_partition;

Hive的查询语法

SQL语言大小写不敏感
SQL可以写在一行或者多行
各字句一般分行写

查询全表和特定列

select * from stu;
select id,name from stu;

列起别名

select id,name as stdName from stu;

常用函数

#求总行数
select count(*) cnt from score;
#求分数最大值max
select max(s_score) from score;
#求分数最小值min
select min(s_score) from score;
#求分数总和sum
select sum(s_score) from score;
#求分数的平均值（avg）
select avg（s_score) from score;

limit 语句

#imit子句用于限制返回的行数
select * from score limit 5;

where语句

使用where子句，将不满足条件的行过滤掉
where子句紧跟随from子句

where * from score where s_score >60;

运算符

算数运算符

运算符	描述
A+B	A和B 相加
A-B	A减去B
A*B	A和B 相乘
A/B	A除以B
A%B	A对B取余
A&B	A和B按位取与
A|B	A和B按位取或
A^B	A和B按位取异或
~A	A按位取反

比较运算符

操作符	支持的数据类型	描述
A=B	基本数据类型	如果A等于B则返回true，反之返回false
A<=>B	基本数据类型	如果A和B都为NULL，则返回true，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULL
A<>B, A!=B	基本数据类型	A或者B为NULL则返回NULL；如果A不等于B，则返回true，反之返回false
A<B	基本数据类型	A或者B为NULL，则返回NULL；如果A小于B，则返回true，反之返回false
A<=B	基本数据类型	A或者B为NULL，则返回NULL；如果A小于等于B，则返回true，反之返回false
A>B	基本数据类型	A或者B为NULL，则返回NULL；如果A大于B，则返回true，反之返回false
A>=B	基本数据类型	A或者B为NULL，则返回NULL；如果A大于等于B，则返回true，反之返回false
A [NOT] BETWEEN B AND C	基本数据类型	如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为true，反之为false。如果使用NOT关键字则可达到相反的效果。
A IS NULL	所有数据类型	如果A等于NULL，则返回true，反之返回false
A IS NOT NULL	所有数据类型	如果A不等于NULL，则返回true，反之返回false
IN(数值1, 数值2)	所有数据类型	使用 IN运算显示列表中的值
A [NOT] LIKE B	STRING 类型	B是一个SQL下的简单正则表达式，如果A与其匹配的话，则返回true；反之返回false。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。like不是正则，而是通配符
A RLIKE B, A REGEXP B	STRING 类型	B是一个正则表达式，如果A与其匹配，则返回true；反之返回false。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。

逻辑运算符

操作符	操作	描述
A AND B	逻辑并	如果A和B都是true则为true，否则false
A OR B	逻辑或	如果A或B或两者都是true则为true，否则false
NOT A	逻辑否	如果A为false则为true,否则false

分组

Group By

select s_id, avg(s_score) from score group by s_id;

Having
where 针对表中列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。后面可以用聚合函数。
求每个学生的平均分数。

select s_id,avg(s_score) from score group by s_id;
select s_id, avg(s_score) as avgScore from score group by s_id having avgScore > 60;

join语句

Hive支持通常的SQL语句，只支持等值连接，不支持非非等值连接。

select * from stu left join score on stu.id=score.s_id;

内连接 inner join

内连接：只能进行连接的两个表中都存在与连接条件型匹配的才能被保留下来。
join默认是inner join
select * from teacher t inner join course  c on t.t_id=c.t_id;

左外连接

左外连接：
join操作符左边表中符合where子句的所有记录都会被返回。
右边表的指定字段没有符合条件的值的话，那么就使用null值替代。
 select * from teacher t left outer join course c on t.t_id = c.t_id;

右外连接

join 操作符右边表中符合where子句的所有记录将会被返回。
左边表的指定字段没有符合条件的值得话，就使用null替代。
select * from teacher t right outer join course c on t.t_id = c.t_id;

满外连接 full outer join

将会返回所有表中符合where语句的所有记录
如果任一表的指定字段没有符合条件的值的话，就使用null替代。
select * from teacher t full outer join course c on t.t_id = c.t_id;

多表连接

多个表使用join进行连接
连接n个表，至少需要n-1个连接条件
select * from teacher t 
left join course c on t.t_id = c.t_id 
left join score s on c.c_id = s.c_id 
left join stu on s.s_id = stu.id;

排序

全局排序，只有一个reduce
使用order by排序： asc升序（默认） desc降序

select * from score s order by s_score desc;

每个mapreduce内部排序sort by
并非全局有序

set mapreduce.job.reduce=3;#设置reduce个数
set mapreduce.job.reduces;#查看reduce的个数
select *from score as s sort by s.s_score;
 insert overwrite local directory '/sort' select * from score s sort by s.s_score;#将查询结果导入到文件中（按照成绩降序排列）

distribute by分区排序
类似MR中的partition，采集hash算法，在map端将查询的结果中hash值相同的结果分发到对应的reduce文件中。
结合sort by使用，d在前

set mapreduce.job.reduces=3;#设置reduce的个数
insert overwrite local directory '/kstribute' select * from score distribute by s_id sort by s_score;#通过distribute by  进行数据的分区,，将不同的sid 划分到对应的reduce当中去

cluster by
除了dist的功能还会对字段进行排序，所以cluster by=distribute by + sort by

--以下两种写法等价
insert overwrite local directory '/k/distribute_sort' select * from score distribute by s_score sort by s_score;

insert overwrite local directory '/k/cluster' select * from score  cluster by s_score;

SerDe

Serde是Serializer/Deserializer的简写，进行对象的序列化和反序列化。
Hive读数据方式

HDFS files –> InputFileFormat –> <key, value> –> Deserializer –> Row object

Row object –> Serializer –> <key, value> –> OutputFileFormat –> HDFS files

Hive 主流存储格式

create table lo_text(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_if string,
city_id string
)
row format delimited fields terminated by '\t'
stored as textfile;
stored as parquet;
stored as orc;
insert into table log_text select * from log_text;

压缩比：ORC > Parquet > textFile
存储文件的查询速度：ORC > Parquet > TextFile

使用压缩可以最小化所需要的磁盘存储空间，减少磁盘和网络io操作。
支持三种压缩：ZLIB,SNAPPY,NONE
不指定格式，默认zlib，压缩比高但是速度慢
实际开发中一般选择snappy

#创建一个非压缩的ORC
stored as orc tblproperties("orc.compress"="NONE");
#创建一个snappy压缩的ORC存储方式
stored as orc tblproperties("orc.compress"="SNAPPY") ;

数据压缩

压缩方式	压缩比	压缩速度	解压缩速度	是否可分割
gzip	13.4%	21 MB/s	118 MB/s	否
bzip2	13.2%	2.4MB/s	9.5MB/s	是
lzo	20.5%	135 MB/s	410 MB/s	否
snappy	22.2%	172 MB/s	409 MB/s	否

压缩格式	对应的编码/解码器
DEFLATE	org.apache.hadoop.io.compress.DefaultCodec
Gzip	org.apache.hadoop.io.compress.GzipCodec
BZip2	org.apache.hadoop.io.compress.BZip2Codec
LZO	com.hadoop.compress.lzo.LzopCodec
Snappy	org.apache.hadoop.io.compress.SnappyCodec

压缩算法	原始文件大小	压缩文件大小	压缩速度	解压速度
gzip	8.3GB	1.8GB	17.5MB/s	58MB/s
bzip2	8.3GB	1.1GB	2.4MB/s	9.5MB/s
LZO	8.3GB	2.9GB	49.3MB/s	74.6MB/s

可以选择开启map和reduce

TEXTFILE行式存储
SEQUENCEFILE行式存储
ORC列式存储
PARQUET列式存储