Hive

一、Hive的简介
1.Hive是一个数据仓库软件
hive可以使用SQL来促进对已经存在在分布式设备中的数据进行读，写和管理等操作！
Hive在使用时，需要对已经存储的数据进行结构的投影（映射）。
Hive提供了一个命令行和JDBC的方式，让用户可以连接到Hive.
注意：Hive只能分析结构化数据！hive在Hadoop之上，使用hive的前提是要安装hadoop！
2.Hive的特点
hive并不是一个关系型数据库
是基于OLTP（在线事务管理）设计
OLTP:侧重点在数据的分析上，不追求分析的效率！
Hive使用类SQL，称为HQL对数据进行分析
Hive容易使用，可扩展，有弹性！
3.WordCount
如何用hive实现wordCount？
源文件：
hadoop hive hadoop
hadoop hive
…

将源文件转为结构化的数据
hadoop 1
hive 1
hadoop 1
…
①建表
表表需要根据存储的数据的结构创建！
表需要映射到数据的结构上
create table a(word string, totalCount int)
②写HQL
select word ,sum(totalCount int)
from a
group by word
4.hive的安装
①保证有JAVA_HONE,HADOOP_HOME
②将bin配置到PATH中，在环境变量中提供HIVE_HOME

二、HiVE 数据的存储

1.Hive要分析的数据是存储在HDFS上
hive中的库的位置，在hdfs上就是一个目录！
hive中表的位置，在hdfs也是一个目录，在所在的库目录下创建了一个子目录！
hive中的数据，是存在在表目录中的文件！
2.在hive中，存储的数据必须是结构化的数据，而且这个数据的格式要和表的属性紧密相关！
表在创建时，有分隔符属性，这个分隔符属性，这个分隔符属性，代表在执行MR程序时，使用哪个分隔符去分割每行中的字段！
hive中默认字段的分隔符：ctrl+A，进入编辑模式，ctrl+V再ctrl+A
3.hive中数据的元数据（schema）存储在关系型数据库
默认存储在的derby中！
derby是使用Java语言编写的一个微型，常用于内嵌在Java中的数据库！
derby同一个数据库的实例文件不支持多个客户端同时访问！
4.将hive的元数据的存储设置存储在mysql中！
Mysql支持多用户同时访问一个库的信息！
注意事项： ①metastore库的字符集必须是latin1
②5.5mysql，改 binlog_format=mixed | row
默认为statement
mysql的配置文件： /etc/my.cnf
6.hive常用的交互参数
usage:hive
-d,–define <key=value> Variable subsitution to apply to hive
commands. e.g. -d A=B or --define A=B
定义一个变量，在hive启动后，可以使用${变量名}引用变量

--database <databasename>     Specify the database to use
			指定使用哪个库

-e SQL from command line
指定命令行获取的一条引号引起来的sql，执行完返回结果后退出cli!

-f SQL from files
执行一个文件中的sql语句！执行完返回结果后退出cli!

-H,–help Print help information
–hiveconf <property=value> Use value for given property
在cli运行之前，定义一对属性！

hive在运行时，先读取 hadoop的全部8个配置文件，读取之后，再读取hive-default.xml
再读取hive-site.xml， 如果使用--hiveconf，可以定义一组属性，这个属性会覆盖之前读到的参数的值！


--hivevar <key=value>         Variable subsitution to apply to hive
                              commands. e.g. --hivevar A=B
			作用和-d是一致的！

-i Initialization SQL file
先初始化一个sql文件，之后不退出cli
-S,–silent Silent mode in interactive shell
不打印和结果无关的信息
-v,–verbose Verbose mode (echo executed SQL to the
console)

三、 HIve的表操作

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,colname),…]
[sorted by (col_name [asc] [desc],…)] into num_buckets buckets] // 分桶后排序
[row format row_format] //表中数据每行的格式，定义数据字段的分隔符，集合元素的分隔符等
[stored as file_format] //表中的数据要以哪种格式来存储，默认为textfile(文本文件)
[localtion hdfs_path] //表在hdfs上的位置
（1）建表时，不带external，创建的表是一个managed_table内部表，建表时，带有external，创建的表是一个外部表！
外部表和内部表的区别是：
内部表在执行删除操作时，会将表的元数据（schema）和表位置的数据一起删除！
外部表在执行删除操作时，只删除表的元数据（schema）
在企业中，创建的都是外部表！
在hive中，表是廉价的，数据是珍贵的！
建表语句执行时：
hive会在hdfs生成表的执行路径；hive还会向mysql的metastore库中参入两条表的信息（元数据）
管理表和外部表之间的转换：
将表改为外部表：alter table p1 set tblproperties(‘external’=‘true’);

将表改为管理表：alter table p1 set tblproperties(‘EXTERNAL’=‘FALSE’);
注意：在hive中，语句不区分大小写，但是在参数红严格区分大小写！

其他建表：
只复制表的结构：create table 表名 like 表名1
执行查询语句，将查询语句查询的结果，按照顺序作为新表的普通列：create table 表名 as select 语句不能创建分区表！
2.删
drop table 表名：删除表
truncate table 表名：清空管理表，只清空数据
对列进行调整：
改列名或列类型： alter table 表名 change [column] 旧列名 新列名 新列类型 [comment 新列的注释]
[FIRST|AFTER column_name] //调整列的顺序

			添加列和重置列：ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) 

3.改
改表的属性： alter table 表名 set tblproperties(属性名=属性值)
4.查
desc 表名：查看表的描述
desc formatted 表名：查看表的详细描述

四、hive的库操作

1.增
create （database|schema）[if not exists] database_name
[comment database_comment] //库的注释说明
[localtion hdfs_path] //库在hdfs上的路径
[with dbproperties (property_name=proper_value,…)] // 库的属性

create database if not exists mydb2
comment ‘this is my db’
location ‘hdfs://slave01:9000/mydb2’

with dbproperties(‘ownner’=‘jack’,‘tel’=‘12345’,‘department’=‘IT’);

2.删
drop database 库名：只能删除空库
drop database 库名 cascade ：删除非空库
3.改
use 库名：切换库
dbproperties:alter database mydb2 set dbproperties(‘ownner’=‘tom’,‘empid’=‘10001’);
同名的属性值会覆盖，之前没有的属性会新增
4.查
show databases:查看当前所有的库
show tables in database:查看库中所有的表
desc database 库名：查看库的描述信息
desc database extended 库名：查看库的详细描述信息

五、hive 的数据类型

数据类型
在hive中最常用的数据类型就是String。
基本数据类型中，低精度的类型和高精度的类型进行计算时，hive会自动进行隐式转换，将低精度转为高精度！
boolean类型无法进行隐式转换
使用cast(值 as 类型)进行强制转换

	集合类型：
			Array:  数组，使用下标定位数组中的元素
			Map:   key-value，使用key获取value的值
			Struct:  通过struct.属性名获取属性对应的值

数据的格式：hive只能分析结构化的数据！在使用hive之前，需要对数据进行ETL，转为结构化的数据！
在建表时，需要指定正确的分隔符，不然hive在读取每行数据时，无法将字段和数据进行映射！

hive的本质：在hive中创建的表，库都在hdfs上有相应的路径！
表中的数据，是文件的形式在表对应的目录中存放！
在建表和建库后，会在Mysql中生成对应的shema信息！
tbls: 存放表的元数据
dbs: 库的元数据
column_v2: 列的元数据

六、分区表

【partitioned by (col_name data_type[comment col_comment], …)】
1.分区表
在建表时，指定了patitioned by ,这个表称为分区表
2.分区概念
MR:在MapTask输出key-value时，为每个key-value计算一个区号，同一个分区的数据，会被同一个ReduceTask处理这个分区的数据，最终生成一个结果文件！
通过分区，将MapTask输出的key-value经过reduce后，分散到多个不同的文件中！
Hive：将表中的数据，分散到表目录下的多个子目录（分区目录）中
3.分区意义
分区的目的是将数据分散到各个子目录中，在执行查询时，可以只选择查询某些子目录中的数据，加快查询效率！
只有分区表才有子目录（分区目录）
分区目录的名称由两部分组成：分区列列名=分区列值
将输入导入到指定的分区之后，数据会附加上分区列的信息！
分区的最终目的是在查询时，使用分区进行过滤！

七、分区表的操作

create external table if not exists defalut.deptpart1(
depno 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.deptpart2(
deptno int,
dname string,
loc int
)
PARTITIONED BY(area string,province string)
row format delimited fields terminated by '\t';

2.分区的查询
show patitions 表名

3.创建分区
（1）alter table 表名 add patition(分区字段名=分区字段值)；
a)在hdfs上生成分区路径
b)在mysql中metastore.partitions表中生成分区的元数据
（2）直接使用load命令向分区加载数据，如果分区不存在，load时自动帮我们生成分区
（3）如果数据已经按照规范的格式，上传到了hdfs，可以使用修复分区命令自动生成分区的元数据：msck repair table 表名；
注意事项;
（1）如果表是个分区表，在导入数据时，必须指定向哪个分区目录导入数据
（2）如果表是多级分区表，在导入数据时，数据必须位于最后一级分区的目录

八、分桶表

1.建表

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’;

2.导入数据
向分通表导入数据时，必须运行MR程序，才能实现分桶操作！
load的方式，只是执行put操作，无法满足分通表导入数据！
必须执行insert into
insert into 表名 values
insert into 表名 select 语句
导入数据之前：
需要打开强制分桶开关：set hive.enforce.bucketing=true
需要打开强制排序开关： set hive.enforce.sorting=true;
insert into table stu_buck select * from stu_buck_tmp
3.抽样查询
格式：select from 分桶表 tablesample(bucket x out of y on 分桶表分桶字段)；
要求：
（1）抽样查询的表必须是分桶表！
（2）buck x out of y on 分桶表分桶字段
假设当前表一共分了z个桶
x:从当前表的第几桶开始抽样。0<x<=y
y:z/y必须是z的因子或倍数！
怎么抽：从第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号桶的一半		

八、DML导入：

1.load:将数据直接加载到表目录中
语法：
load data [local] inpath ‘xx’ into table 表名 partition()
local:如果导入的文件在本地文件系统，需要加上local，使用put将本地上传到hdfs
不加local默认导入的文件是在hdfs，使用mv将源文件移动到目标目录
2.insert:insert方式运行MR 程序，通过程序将数据输入到表目录！
在某些场景，必须使用insert方式来导入数据：
（1）向分桶表中插入数据
（2）如果指定表中的数据，不是以纯文本形式存储，需要使用insert方式导入
语法：
insert into|overwrite table 表名 select xxx | values()
insert into:向新表中追加新的数据
insert overwrite：先清空表中所有的数据，再向表中添加新的数据
特殊情况：
多插入模式（从一张源表查询，向多个目标表插入）
from 源表
insert xxxx 目标表 select xxx
insert xxxx 目标表 select xxx
insert xxxx 目标表 select xxx
举例： from deptpart2
insert into table deptpart1 partition(area=‘huaxi’) select deptno,dname,loc
insert into table deptpart1 partition(area=‘huaxinan’) select deptno,dname,loc
3.location：在建表时，指定表的location为数据存放的目录
4.import:不仅可以导入数据，还可以顺便导入元数据（表结构）。Import只能导入export输出的内容！
IMPORT [[EXTERNAL] TABLE 表名(新表或已经存在的表) [PARTITION (part_column=“value”[, …])]]
FROM ‘source_path’
[LOCATION ‘import_target_path’]
①如果向一个新表中导入数据，hive会根据要导入表的元数据自动创建表
②如果向一个已经存在的表导入数据，在导入之前会先检查表的结构和属性是否一致
只有在表的结构和属性一致时，才会执行导入
③不管表是否为空，要导入的分区必须是不存在的
import external table importtable1 from ‘/export1’

九、DML导出

1.insert：将一条sql运算的结果，插入到指定的路径
语法：insert overwrite [local] directory ‘xxx’
row format xxxx
select *from student;
2.export:既能导出数据，还可以导出元数据（表结构）！
export会在hdfs的导出目录中，生成数据和元数据！
导出的元数据是和RDMS无关！
如果是分区表，可以选择将分区表的部分分区进行导出！
语法：export table 表名 【partition（分区信息）】to ‘hdfspath’

十、排序

Hive 的本质是MR，MR中如何排序的！
全排序：结果只有一个（只有一个分区），所有数据整体有序！
部分排序：结果有多个（有多个分区），每个分区内部有序！
二次排序：在排序时，比较的条件有多个！
排序：在reduce之前就已经排好序了，排序是shuffle阶段的主要工作！
分区：使用Partitioner来进行分区！
当reduceTaskNum>1,设置用户自己定义的分区器，如果没有使用HashPatitioner！
HashPartitioner只根据key的hashcode来分区！
order by col_list:全排序！
sort by col_list:部分排序！设置reduceTaskNum>1,只写sort by 是随机分区！
如果希望自定义使用哪个分区字段，需要使用distribute by
distribute by col_list：指定按照哪个字段分区！结合sort by 使用！
cluster col_list：如果分区的字段和排序的字段一致，可以简写为cluster by
DISTRIBUTE BY sal sort by sal asc 等价于 CLUSTER BY sal
要求： CLUSTER BY 后不能写排序方式，只能按照asc排序！

insert overwrite local directory '/home/atguigu/sortby'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from emp DISTRIBUTE BY deptno sort by sal desc ;

insert overwrite local directory '/home/atguigu/sortby'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from emp where mgr is not null CLUSTER BY  mgr ;

十一、函数

1.查看函数
函数有库的概念，系统提供的除外，系统提供的函数可以在任意库中使用！
查看当前库所有的函数：show functions;
查看函数的使用：desc function 函数名
查看函数的详细使用：desc function extended 函数名
2.函数的分类
函数的来源：
（1）系统函数，自带的，直接使用即可
（2）用户自定义的函数
a)遵守hive函数类的要求，自定义一个函数类
b)打包函数，放入到hive的lib目录下，或在HIVE_HOME/auxlib
auxlib用来存放hive可以加载的第三方jar包的目录
c)创建一个函数，让这个函数和之前编写的类关联
函数有库的概念
d)使用函数

函数按照特征进行分类：
UDF:用户定义的函数。一进一出。输入单个参数，返回单个结果！
UDTF:用户定义的生成函数。一进多出。传入一个参数（集合类型），返回一个结果集！
UDAF：用户定义的聚集函数。多进一出。传入一列多行的数据，返回一个结果（一列一行）count，avg,sum.

十二、常用函数

1.NVL：
NVL（string1，replace_with):判断string1是否为null,如果为null，使用replace_with替换null，否则不做操作！
在以下场景使用：①将null替换为默认值②运行avg()
2.concat:字符串拼接，可以在参数中传入多个string类型的字符串，一旦有一个参数为null，返回null！
3.concat_ws:使用指定的分隔符完成字符串拼接，concat_ws(分隔符.[string | array]+)
4.collect_set:collect_set(列名）：将此列的多行记录合并为一个set集合，去重！
5.collect_list:collect_list(列名)：将此列的多行记录合并为一个set集合，不去重！
6.explode：explode（列名）：参数只能是array或者map，将array类型的参数转换为一行多列，将map类型的参数转换为 2列n行

十三、窗口函数

官方文档地址：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics
oracle,sqlserver都提供了窗口函数，但是在mysql5.5和5.6都没有提供窗口函数！
窗口函数：窗口+函数
窗口：函数运行时计算的数据集的范围
函数：运行的函数！
仅支持以下的函数：:
Windowing function:
LEAD:LEAD (scalar_expression [,offset] [,default])： 返回当前行以下N行的指定列的列值！

LAG:LAG (scalar_expression [,offset] [,default])： 返回当前行以上N行的指定列的列值！

FIRST_VALUE:FIRST_VALUE(列名,[false(默认)])： 返回当前窗口指定列的第一个值
LAST_VALUE:LAST_VALUE(列名,[false(默认)])： 返回当前窗口指定列的最后一个值
统计类的函数(一般都需要结合over使用)： min,max,avg,sum,count
排名分析：
RANK
ROW_NUMBER
DENSE_RANK
CUME_DIST
PERCENT_RANK
NTILE
注意：不是所有的函数在运行时都可以改变窗口的大小，来控制计算数据集的范围！
所有的排名函数和：LAG，LEAD，支持使用over()，但是在over中不能定义window_clause
格式：函数 over（partition by 字段，over by 字段 window_claue）
窗口的大小可以通过window_claue来指定：

(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()中既没有出现window_claue，也没有出现order by，默认窗口为rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
②在over()中(没有出现windows_clause)，指定了order by，窗口默认为rows between UNBOUNDED PRECEDING and CURRENT ROW
窗口函数和分组的区别：①如果是分组操作，select后只能写分组后 的字段
②如果是窗口函数，窗口函数是在指定的窗口内，对每一条记录都执行一次函数
③如果是分组操作，有去重效果，partition不去重！
business.name | business.orderdate | business.cost

(9) 查询前20%时间的订单信息
精确算法：
select *
from
(select name,orderdate,cost,cume_dist() over(order by orderdate ) cdnum
from business) tmp
where cdnum<=0.2

不精确计算：
select *
from
(select name,orderdate,cost,ntile(5) over(order by orderdate ) cdnum
from business) tmp
where cdnum=1

(8)查询顾客的购买明细及顾客最近三次cost花费

最近三次： 当前和之前两次 或 当前+前一次+后一次

当前和之前两次:
select name,orderdate,cost,sum(cost) over(partition by name order by orderdate rows between 2 PRECEDING and CURRENT row)
from business

当前+前一次+后一次:
select name,orderdate,cost,sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and 1 FOLLOWING)
from business

或
select name,orderdate,cost,cost+
lag(cost,1,0) over(partition by name order by orderdate )+
lead(cost,1,0) over(partition by name order by orderdate )
from business

(7) 查询顾客的购买明细及顾客本月最后一次购买的时间
select name,orderdate,cost,LAST_VALUE(orderdate,true) over(partition by name,substring(orderdate,1,7) order by orderdate rows between CURRENT row and UNBOUNDED FOLLOWING)
from business

(6) 查询顾客的购买明细及顾客本月第一次购买的时间
select name,orderdate,cost,FIRST_VALUE(orderdate,true) over(partition by name,substring(orderdate,1,7) order by orderdate )
from business

(5) 查询顾客的购买明细及顾客下次的购买时间
select name,orderdate,cost,lead(orderdate,1,‘无数据’) over(partition by name order by orderdate )
from business

（4）查询顾客的购买明细及顾客上次的购买时间
select name,orderdate,cost,lag(orderdate,1,‘无数据’) over(partition by name order by orderdate )
from business

（3）查询顾客的购买明细要将cost按照日期进行累加
select name,orderdate,cost,sum(cost) over(partition by name order by orderdate )
from business

（2）查询顾客的购买明细及月购买总额

select name,orderdate,cost,sum(cost) over(partition by name,substring(orderdate,1,7) )
from business

（1）查询在2017年4月份购买过的顾客及总人数

count()在分组后，统计一个组内所有的数据！

传统写法： 效率低
with tmp as (select name
from business
where year(orderdate)=2017 and month(orderdate)=4
group by name)

select tmp.name,tmp1.totalcount
from
(select count(*) totalcount
from tmp ) tmp1 join tmp;

---

select name,count(*) over(rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING)
from business
where substring(orderdate,1,7)=‘2017-04’
group by name

等价于

select name,count(*) over()
from business
where substring(orderdate,1,7)=‘2017-04’
group by name