hive 是什么？

hive 是一个SQL解析工具吧，其实就是将SQL转换成为计算引擎的任务。

注意：hive 不支持表格之中数据的更新和删除。用insert overwrite 来实现数据的删除

hive的工作原理

是将sql 转化成为mapreduce作业

hive 客户端

hive cli 客户端
beeline 客户端
Java编程操作客户端。

语法介绍

with as

 1: with as 必须和其他sql一起使用(可以定义一个`with`但在后续语句中不使用他)
 2: with as 是一次性的

location 关键字

location关键字
（后面必须需要指定文件夹的名字，指定文件会出错）
对于创建数据库的时候，
location 指定的是数据库文件夹的存放位置 要具体到某个xx.db文件夹。

内置表：
location关键字指定的是表文件夹的存放的位置 要具体到以表为命名的文件夹位置。

外部表：
location 也是数据存放的位置。

order by vs distribute by vs sort by vs cluster by

order by
  只会产生一个reduce任务，进行全局排序。
  1:使用order的时候 设置reduce task 数目的方式是不起作用的。
  2:如果hive的执行模式是hive.mapred.mode 是严格的话，使用order by 的时候
  后面是需要加上limit 进行限制的。
  
distribute by
    distribute by 需要写在 sort by 的前面。
    distribute by 控制map 输出到 reduce 的方式。
   
sort by 
  后面跟随字段，每个reduce内部根据写的字段来进行排序。
  
cluster by
   是 distributed by + sort by 相加的总和。
   但是 cluster by 指定的字段 只能是按照正序排列。
   cluster by 在分桶之中会被使用。

distinct

distinct 关键字的作用是去重 

注意：
distinct + field
当field的取值为null 有很多 可能会造成数据倾斜。
一般以group by 语法 来代替 distinct关键字的作用

join

hive join 的种类

map join
common join

common join

reduce阶段完成join

map join

map 阶段完成 join

map join 与 common join

   map join 一般用于大表与小表 join , 小表的大小限制为 由参数`hive.mapjoin.smalltable.filesize` 来决定，默认值为 25M。满足条件的话 Hive 在执行时候会自动转化为 MapJoin，或使用 hint 提示 `/*+ mapjoin(table) */` 执行 MapJoin。

map join的相关的参数

hive.auto.convert.join= ture; 设置自动化开启(默认为false)

hive.mapjoin.smalltable.filesize=25000000; 设置小表大小的上限（默认为25M）

join 注意点

1：join时 表格的顺序 很重要
   左边的表 一般为小表 右边的表为大表
   一般会将左边表读入缓存之中 右边表是流式读取。
   
2: inner join
  a join b on a.user_id=b.user_id
  where 条件之中 加入过滤 不管是什么字段 都是先过滤 再join
  没有遵循 先join 再 where 的顺序。谓词下推

3: 内连接的连接字段 在连接的时候 会对on 字段进行过滤 过滤掉不是null的字段,然后再join。但是外连接 对于连接字段 不会进行null的过滤
  
4: join 的时候 不能加入 < > !=的比较式子 只是支持 =

5： join 的时候 on 两边比较的字段的类型是需要相同的，否则的话，某个字段的值 可能会全部shuffle到一个reduce 之中，这样的话，也是会造成数据倾斜。

6: 大小表之间进行join 思考mapjoin来进行优化实现

lateral view

   hive之中的侧视图 一般和 udtf函数结合使用

侧视图的原理

  侧视图的原理是将UDTF的结果构建成一个类似于视图的表，然后将原表中的每一行和UDTF函数输出的每一行进行连接(一列输出的多行)，生成一张新的虚拟表

数据库相关

创建库：
create database [if not exists] xxx
[with dbproperties(xxx=yyy)]
[location xxxx] 指定数据库数据文件存储的位置 指定位置的时候 是需要指定到具体要创建的db.sl地址的。;

创建的库 会在 配置文件设置的目录下面建立了一个文件夹,后缀是以.db结尾的。

显示数据库：
show databases;

显示数据库的信息：
describe databases [extended] database_name; 
不加extended 不显示扩展信息
加上extended 显示扩展信息。

修改数据库：
alter database database_name set dbproperties(""="")

使用数据库：
 use xxx
 
删除数据库：
  drop database [if exists] xxx （那么在hdfs上面db的目录也是会被删除的）后面有关键字 cascade 加上这个关键字来删除非空的数据库。

表之中写入数据

1: 数据来自文件
load data [local] inpath 'data_path' into table table_name [partition (pfield=pval)]
  
2: 数据来自表
insert into table_name_A
select * from table_name_B

表拷贝

数据表结构拷贝

create table new_table_name like copyed_table_name;

数据表结构和其中的数据一同拷贝

create table new_table_name as
select * from copyed_table_name;

分区开窗（over( partition by )）

开窗函数的语法


分析函数 over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)

开始位置就是表示 向前取多少行 结束位置，就是表示往后取多少行。
默认的取值范围 为 到 当前行

表

外部表与内部表

1：内部表的数据由hive自己管理  
2：外部表的数据由hdfs管理

删除内部表 元数据和真实数据会一起删除。  
删除外部表 只会删除元数据。

表的相关操作

建表语句：
create [tempoary| external] table table_name // 创建的是否使外部表 使需要注意的
（field1 field_type,.......）
[location]
[comment]
[partitioned by (pfield ptype,.....)] // 是否分区
[clustered by (buck_field) sort by(bucked_field) into num buckets]
[serde]
[row format delimited
fields terminated by ''
lines terminated by '']
[stored as fileType]

注意点：
创建表的时候 注意表的读取格式 stored as 指定上传文件的类型
默认的文件类型使textFile
serde 和row format delimited 的作用几乎使相同的，
指定如何将列转化成为字段。
如果想要将文本格式存储数据的表 转换成为以 特殊文本形式存储的表的话，
要建立一个中间表 然后使用insert 语句 将数据插入到表格之中。


拷贝表
create table tableA like tableB;

查看表结构
desc tableName
desc formatted tableName

删除表
drop table tableName

修改表名
alter table xxx rename to yyy;

修改表字段
alter table xxx change column oldColumn newColumn 字段类型 comment 'xxx' after 字段名。

增加表字段
alter table xxx add column 字段类型

分区

对于分区的理解

分区的目的：
  将表数据，分散到多个子目录中，在执行查询时，可以只选择查询某些子目录中的数据，加快查询效率；

分区的实质：
  分区其实就是一个目录
  分区对应的目录名为 区列列名=分区列列值

动态分区与静态分区

静态分区：
  若分区的值是确定的，那么称为静态分区。新增分区或者是加载分区数据时，已经指定分区名。

动态分区：
  分区的值是非确定的，由输入数据来决定。

分区表

分区表的查询模式

分区表的查询模式由严格模式和非严格模式两种。默认的模式是非严格。
设置查询模式为严格模式的方法：
set hive.mapred.mode=strict

查看分区表下有哪些分区

show partitions table_name [partition("pname"="pvalue")]

partition关键字 表示查看具体的分区下面有哪些分区。

增加分区

alter table table_name add partition ("pname"=”xxx“) [location 放置到指定的路径下]

分桶

什么是分桶

  一个分桶就是一个目录，使用分桶的时候，需要指定分桶的数目，
  对分桶列进行hash去值，然后对分桶数进行取模，决定数据被存储在哪个分桶之中

分区和分桶之间的区别

  分区的数目不固定，分桶的数目都是固定的。
  分区和分桶的实质都是目录。

hive 元数据

1: hive 元数据可以存储在内嵌的 Derby数据库。
2: hive 元数据可以存储在 mysql数据库之中

hive 读写流程


hdfs -> inputformat ->serde->row object->serde->outputformat->hdfsfile

serde的作用是将文本行 与 表的字段解释相对应。

map / reduce 任务数的计算方式

压缩方式比较

  主要从压缩方式是否支持切分这个角度来观察。

压缩算法   是否支持切割    native   压缩率     解压速度   是否hadoop自带   换成压缩格式后原本程序是否需要修改
gzip      否            是        很高      比较快                    和文本处理一样，不需要修改   
lzo       是            是        比较高     很快                     需要建立索引 
snappy    否            是        比较高     很快                     和文本处理一样，不需要修改
bzip2     是            否        最高       慢                      和文本处理一样，不需要修改

文件格式

  在hive 之中，对于 文件格式的主要关注点，也在于，文件格式是否支持切割，以及是 行式存储 还会 列式存储容量
 Text File: 行式存储，支持切割
 SequenceFile：行式存储，支持分割
 ORC Files： 先按行切分成块，块内按列存储，支持切分

hive.input.format

 查看 hive 当前所使用的 inputformat类型
 set hive.input.format

map任务数

map任务数 与三个因素相关：
1: 文件的压缩格式
2: 文件的存储格式 
3: hive.input.format

情况1:
  前提：文件压缩存储，且压缩算法不支持切分。
   hive.input.format 的值为 CombineFileInputFormat
   
   map数由 四个参数影响
    mapred.split.max.size // 每个map处理的文件的最大数据量
    mapred.split.min.size // 每个map处理的最小的数据量
    mapred.split.min.size.per.node
    mapred.split.min.size.per.rack
 
  四个参数生效的优先级 最下面的最高，就以参数设置而言，
  mapred.split.max.size >= mapred.split.min.size >= 
   mapred.split.min.size.per.node >= mapred.split.min.size.per.rack
   
   注意：在输入文件的压缩格式是不能被切分的情况下，设置以上的参数，只能调低map的任务数，而不能调大map的任务数。通过调大
mapred.split.max.size 参数的值，来降低map的任务数。

情况2:
   前提：
     文件被压缩，支持切分，
      hive.input.format 的值为CombineFileInputFormat
      
   map数由 四个参数影响
    mapred.split.max.size // 每个map处理的文件的最大数据量
    mapred.split.min.size // 每个map处理的最小的数据量
    mapred.split.min.size.per.node
    mapred.split.min.size.per.rack
 
  四个参数生效的优先级 最下面的最高，就以参数设置而言，
  mapred.split.max.size >= mapred.split.min.size >= 
   mapred.split.min.size.per.node >= mapred.split.min.size.per.rack
   
   注意：
     可以通过调下  mapred.split.min.size.per.rack ，mapred.split.min.size.per.node ，mapred.split.min.size 这三个参数来增加map的任务数。
     可以通过调大 mapred.split.max.size 参数值，来增加map的任务数。

reduce 任务数


hive 自己决定reduce数目的规则，
 reduce

// 设置reduce的数目
mapred.reduce.tasks = N；

// 每个reduce所接收的数据量的大小
hive.exec.reducers.bytes.per.reducer

//每个任务最大的reduce数，默认为999
hive.exec.reducers.max  

reduce的任务数 = min(hive.exec.reducers.max ，总输入数据量/参数1))

注意：
  使用 order by，全局只会生成一个reduce任务

压缩格式

  查看hive 当前支持的压缩算法
  set io.compression.codecs;
  
  查看 hive的输出是否开启压缩
  set hive.exec.compress.output;
  
  查看当前hive 使用的压缩算法
  set mapreduce.output.fileoutputformat.compress.codec

数据倾斜

什么是数据倾斜

大数据是分布式计算，数据倾斜就是大量的数据都分布到了一个任务上计算，导致出现长尾问题。

导致数据倾斜的部分情况以及处理方式

  1: join的字段之中null值非常多
      处理方式：
         1: 过滤null值
         2: 对null值赋予随机值
  2: join的字段 类型不一样
      处理方式：
        在join的时候，使用cast 进行强制数据类型转换
  3: 文件过大，使用不可切割的压缩文件导致的
      处理方式：
         选择可切割的压缩文件算法
  4: 使用group by 引发的数据倾斜
     处理方式：
        开启参数 开启参数 hive.groupby.skewindata

数据倾斜的处理方式

 1: 可以尝试这样设置参数 set hive.groupby.skewindata=true

 备注：其实对于数据倾斜问题 主要是找到哪里的数据倾斜了，然后具体问题具体处理。
   如果是两个表进行join,join的字段之中有太多的null,那么可以将null值多的字段的null值进行补全，然后打散。

参数 hive.groupby.skewindata 背后的实现原理

  开启该配置会将作业拆解成两个作业，第一个作业会尽可能将Map的数据平均分配到Reduce阶段，并在这个阶段实现数据的预聚合，以减少第二个作业处理的数据量；第二个作业在第一个作业处理的数据基础上进行结果的聚合。
  
  注意：对于需要全局计算的统计，这个参数无法解决数据倾斜的问题。

小文件

小文件来源

1: 本身表格之中存储的数据文件很小
2: reduce 任务输出的是小文件

小文件处理

  对于reduce任务输出小文件的问题，
  开启reduce输出合并
   参数 hive.merge.mapredfiles 设置为true 
   hive.merge.smallfiles.avgsize // 默认为16MB,如果不是partitioned table的话，输出table文件的平均大小小于这个值，启动merge job，如果是partitioned table，则分别计算每个partition下文件平均大小，只merge平均大小小于这个值的partition。这个值只有当hive.merge.mapfiles或hive.merge.mapredfiles设定为true时，才有效。
   
  对于表格之中本身存储的数据文件很小的问题 
    可以使用 insert overwrite + distribute by的语法，将数据覆盖一遍。
    
   
  hive的 的 concatenate 命令 可以自动合并小文件
  注意：
    1、concatenate 命令只支持 RCFILE 和 ORC 文件类型。  
    2、使用 concatenate 命令合并小文件时不能指定合并后的文件数量，但可以多次执行该命令。

hive 函数

hive函数类型 UDF UDTF UDAF

UDF: 输入一行，返回一行
UDTF: 输入多行，返回一行
UDTAF: 输入一行，返回多行

部分hive内置函数介绍

UDF

开窗函数

备注：row_number,rank,dese_rank, 三个函数的关注点在于，值相同的记录，返回的顺序是否会重复，以及排序的顺序是否会跳跃

row_numer()

值相同，返回的记录顺序不会重复，顺序不会跳跃

rank()

值相同，返回的记录顺序会重复，顺序会跳跃

dense_rank

值相同，返回的记录顺序会重复，顺序不会跳跃

first_value() // 求出开窗分区的第一个值

last_value() // 求出开窗分区的最后的值

聚合函数应用于开窗

sum() 求和

avg() 求平均值

max() 求最大值

min() 求最小值

count() 求数目

lag() 取出分区的前n行数据

lead() 取出分区的后n行数据

split

split // 字符串切割函数

coalesce

coalesce(T v1, T v2, …) 返回参数中的第一个非空值；如果所有值都为 NULL，那么返回NULL。

json相关

get_json_object

使用例子：
  get_json_object 函数第一个参数填写`json`对象变量，第二个参数使用`$`表示json变量标识，然后用 . 或 [] 读取对象或数组

时间相关

字符串日期时间转日期 to_date

例子：to_date('2015-04-02 13:34:12')

日期类型转时间字符串 date_format

获取当前的时间戳 unix_timestamp

  unix_timestamp

转化unix时间戳到当前时区的时间 from_unixtime

 from_unixtime

UDTF:

collect_list // 不会去重

UDAF

explode

explode的入参是一个数组，和测试图结合使用

posexplode

针对 多列 进行explode的场景而产生的函数。

业务分析思考

求连续

 连续的话 是一个等差数列，所以一个等差数列 减去 另一个等差数列，最后得到的结果是一致的。
 所以使用开窗函数 row_numer()增加一列等差数列，然后用另一列去剪去这一列，得到计算的结果，如果每一行的此列的取值结果都是相同的，那么就是连续的。

会话分组（以用户的操作时间小于60秒作为一个会话窗口）

假设数据记录的格式为 
  id  时间戳 
  
实现的思路就是
   首先使用 lag 进行开窗，对每行增加一列，列是之前的记录的时间，对查询的结果 使用 sum 进行开窗，类似 sum(if(diff > 60,1, 0)),新增一列 分组的标志。

间隔连续（求出每个用户的最大的连续登录的天数）

间隔连续，就是 连续的过程之中 允许间隔，只要在间隔范围内，也算是连续的。

解决的思路：
  使用 lag + 开窗函数，增加一列，是前一天的时间，
  对查询的结果 使用 sum + 开窗函数 设置会话分组的标志，
  然后对会话分组进行聚合，求出连续的天数。

hive 优化思路

 1: 分表 
   合理利用中间结果集，重视查过就丢的资源浪费。
 2: join表的时候 首先进行 where过滤 更好（谓词下推）。
 3: 合理控制map的数目 和 reduce 的任务数。

hive 部分参数介绍

控制map任务数的方式：
    控制map数目的方式 可以是通过合并小文件，来减少map的数目。

相关的配置参数：

并行计算：
  hive.exec.parallel = true 

fetch抓取：
hive.fetch.task.conversion //该属性修改为more以后，在全局查找、字段查找、limit查找等都不走mapreduce


map相关：
  hive.merge.mapfiles=true //设置在map 阶段进行文件合并
  hive.map.aggr // map端进行聚合，默认的取值为true


reduce相关：
  mapreduce.reduce.memory.mb // reduce的内存大小
hive.merge.mapredfiles=true // 设置在mapred结束的时候 合并小文件 默认是false;

hive.merge.smallfiles.avgsize // 默认为16MB,如果不是partitioned table的话，输出table文件的平均大小小于这个值，启动merge job，如果是partitioned table，则分别计算每个partition下文件平均大小，只merge平均大小小于这个值的partition。这个值只有当hive.merge.mapfiles或hive.merge.mapredfiles设定为true时，才有效。

hive.merge.size.per.task // hive小文件合并之后 文件的最大的大小。

hive