HIVE总结

最新推荐文章于 2024-07-10 01:10:32 发布

点点滴滴z

最新推荐文章于 2024-07-10 01:10:32 发布

阅读量458

点赞数

分类专栏： hive 文章标签： HIVE总结 hive

本文链接：https://blog.youkuaiyun.com/qq_42244078/article/details/101231816

版权

hive 专栏收录该内容

1 篇文章

订阅专栏

在这里插入图片描述

HIVE总结

hive简介

简介
- **Hive是建立在 Hadoop上的数据仓库基础构架。它提供了一系列的工具，可以用来进行数据提取转化加载（ETL ），这是一种可以存储、查询和分析存储在 Hadoop中大规模数据的机制。**Hive 定义了简单的类 SQL查询语言，称为 HQL ，它允许熟悉SQL的用户查询数据。同时，这个语言也允许熟悉 MapReduce的开发者开发自定义的 mapper和 reducer来处理内建的 SQL函数(mapper和reducer)无法完成的复杂的分析工作。
- Hive是SQL解析引擎，它将SQL语句转译成M/R Job，然后在Hadoop执行。
- Hive的表其实就是HDFS的目录，按表名把文件夹分开。
- Hive相当于Hadoop的客户端工具，部署时不一定放在集群管理节点中，可以放在任意一个节点上。但是要保证这个节点可以和集群通信(或者理解为这个节点是集群的一个客户端节点)。
hive的数据存储
- Hive的数据存储基于Hadoop的HDFS
- Hive没有专门的数据存储格式
- Hive主要包括：数据库、表、视图、索引
- Hive默认可以直接加载文本文件（TextFile），还支持SequenceFile、RCFile等
- 创建表时，指定数据的列分隔符与行分隔符，Hive即可解析数据
hive的系统架构
- 用户连接
  - CLI，即Shell命令行
  - JDBC/ODBC 是 Hive 的Java操作，与传统数据库JDBC的方式类似
  - WebGUI是通过浏览器访问 Hive
- Hive 将元数据(Metastore)存储在数据库中，目前只支持 mysql、derby。Hive 中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等
- 解释器、编译器、优化器完成 HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划（plan）的生成。生成的查询计划存储在 HDFS 中，并在随后由 MapReduce 调用执行
- Hive 的数据存储在 HDFS 中，大部分的查询由 MapReduce 完成（包含 * 的查询，特例： select * from table 不会生成 MapRedcue 任务）
hive的metastore 元数据
- Metastore是Hive元数据的集中存放地。
- Metastore默认使用内嵌的derby数据库作为存储引擎
  - Derby引擎的缺点：在同一个目录下一次只能打开一个会话
    - 使用derby存储方式时，Hive会在当前目录生成一个derby.log文件和一个metastore_db目录存储元数据信息
    - 如果下次切换到一个新目录访问Hive，则会重新生成derby.log文件和metastore_db目录，这样就无法使用之前的元数据信息了。
  - 推荐使用MySQL作为外置存储引擎，可以支持多用户同时访问以及元数据共享。
hive的特点
- 基于Hadoop的大数据的计算/扩展能力
- 支持类SQL查询语言
- 统一的元数据管理
- 简单编程

hive与传统数据库的比较

查询语言	HiveQL	SQL
数据存储位置	HDFS	本地磁盘
数据格式	用户定义	系统决定
数据更新	不支持	支持
索引	新版本有，但弱	有
执行	MapReduce	Executor
执行延迟	高	低
可扩展性	高	低
数据规模	大	小

hive数据库以及表操作

hive的执行模式
- 命令行 cli
  - 使用hive 命令进入命令行
  - 写hql语句执行结果显示在控制台上
- 封装到脚本中执行
  - 第一种：shell脚本
  - 使用hive -e 后面加具体hql语句
  - 第二种把hql语句保存到本地文件中在执行
  - hive -f .sql文件执行
- JDBC模式使用Java操作
  - hive远程服务调用（端口10000）
    - hive --server hiveserver2
  - 在Java中调用hive的JDBC连接
    - org.apache.hive.jdbc.HiveDriver
hive 与依赖环境的交互
- 与Linux的交互
- ！ls
- !pwd
- 与hdfs的交互
- dfs -ls / 省略之前使用的 bin/hdfs
- dfs -mkdir /test
set命令的使用
- hive命令行下执行set命令仅对当前会话有效
  - set hive.cli.print.current.db=true; 显示当前数据库的名称
  - set hive.cli.print.header=true; 显示字段名称
- hive脚本中的配置set命令对运行该脚本的机器有效
  - ~/.hiverc
  - 在执行hive命令进入shell命令行的时候默认会加载这个脚本
- 查看hive的历史命令集
  - more ~/.hivehistory
hive的数据库操作
- 查看所有数据库 show databases
- 选择数据库 use default； default是默认的数据库默认情况下数据都放在这个数据库中
- hive数据在hdfs的默认目录是 user/hive/warehouse
- 来自于hive-site.xml中参数hive.metastore.warehouse.dir的配置
- 目录信息在MySQL的tdbs表中 COLUMNS_V2存储的是表的子弹信息
- 创建数据库 create database db1 （location ‘ ’ 指定创建数据库的位置）
- 删除数据库 drop database db1 删除数据库但是默认数据库default是无法删除的

hive中对表的操作

操作	操作语句
创建表	create table t2(id int);
查看所有表	show tables;
查看表结构	desc t2;
查看表的创建信息	show create table t2;
重命名表名	alter table t2 rename to t2_bak;
加载数据	load data local inpath ‘/data/soft/t2.data’ into table t2_bak; hdfs dfs -put /data/soft/t2.data /user/hive/warehouse/t2_bak/t2_bak.data
修改表中字段	增加字段 alter table t2_bak add columns (name string);
注释	comment “注释” 【注释中文乱码问题解决方案见备注】
列和行分隔符	row format delimited fields terminated by ‘\t’ lines terminated by ‘\n’;
删除表	drop table t2_bak;

行分割符正常情况下可以不写但是如果写了的话必须写到最后面

数据加载的两种模式
- 我们加载数据的到数据库中一般有两种模式读模式跟写模式
  - 写模式指的是在数据加载的时候对数据的合法性进行校验，表中的所有数据都是合法的，有利于快速查询和快速处理例如 MySQL等其他关系型数据库（RDBMS）
  - 读模式指的是在数据读取的时候对数据进行合法性校验，在写入的时候不进行校验。优点是加载数据的速度快，适合大量数据的快速加载例如 hive
  - hive在上传文件的时候hive不会对表中的数据进行校验，对于错误格式的数据在查询的时候也不会报错但是会显示为null

hive的数据类型

hive的基本数据类型

hive的复杂数据类型

hive中默认的分隔符

分隔符	描述	语句
\n	分隔行	LINES TERMINATED BY ‘\n’【行分隔符设置的时候只能放在最后面】
^A	分隔字段(列)，显示编码使用\001	FIELDS TERMINATED BY ‘\001’
^B	分隔复合类型中的元素，显示编码使用\002	COLLECTION ITEMS TERMINATED BY ‘\002’
^C	分隔map元素的key和value，显示编码使用\003	MAP KEYS TERMINATED BY ‘\003’

array

array中的数据为相同类型，例如，假如array A中元素[‘a’,‘b’,‘c’]，则A[1]的值为’b’
关键语句
- collection items terminated by ‘,’ 定义数组内多个元素的分割符

create table stu(
id int,
name string,
favors array<string>
)row format delimited
fields terminated by '\t'      ID  name  这些字段之间的分割符
collection items terminated by ','     array<string>   数组内部多个元素的分隔符
lines terminated by '\n';      行分隔符

map

类似Java中的集合存储的是kv键值对类型的数据
collection items terminated by ‘,’ 一个数组内元素之间的分隔符
map keys terminated by ‘,’ kv之间的分割符

create table stu2(
id int,
name string,
scores  map<string,int>
)row format delimited
fields terminated by '\t'
collection items terminated by ','    map<string,int>   多个kv对的分隔符
map keys terminated by ':'     k v   的分割符
lines terminated by '\n';

查询的时候可以指定k来查询v值 scores[‘math’]

struct

类似Java中的对象
collection items terminated by ‘,’ 定义struct中多个元素的分隔符

create table stu3(
id int,
name string,
address  struct<home_addr:string,office_addr:string>
)row format delimited
fields terminated by '\t'
collection items terminated by ','     struct 中 多个元素之间的分割符
lines terminated by '\n';

查询的时候可以直接使用 address.home_addr

map与struct的区别
- map
  - Map在建表语句中需要指定k-v的类型
  - Map中后期可以随意增加k-v对
  - Map中通过[]取值
  - Map的源数据中需要带有k-v
- Struct
  - Struct在建表语句中需要指定所有的属性名称和类型
  - Struct中的k-v(属性)个数是确定的

综合练习

create table stuu (
id int comment 'id',
name string comment 'name',
favors array<string>,
scores map<string,int>,
address struct<city:string,province:string>
)row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n';


load data local inpath '/data/soft/hive/a.data' into table stuu;


1	zhangsan	english,basketball,swing	chinese:80,math:90,english:100	hd,bj
2	lisi	study,games	chinese:89,english:70,math:88	mh,sh

hive的表格式

hive 的表类型大致可以分为是四种内部表、外部表、分区表、桶表
- 严格来说只能分为两种
  - 内部表
  - 外部表
  - 分区表、桶表都可以说是内部表的不同表现形式
内部表也成受控表
- 就是说数据的生命周期受表的控制，当表删除的时候，其数据文件一并被删除
- 所有表的数据（除了外部表以外）都存放在 hdfs的 user/hive/warehouse/… 中（默认情况下）可以通过location来指定存放的位置
- 在加载数据的时候，实际的数据会被移动到数据仓库的目录中，之后不对数据的访问就直接在数据仓库的目录中完成在删除表的时候数据也会被删除
- 创建内部表 create table tablename（）；
外部表
- 表的定义与数据的生命周期互相不约束，数据只是表对hdfs中的某一个目录的引用，当删除表的定义的时候，表中的数据是不变的
- 建表语句中包含external关键字的表叫做外部表
  - 删除外部表值删除元素的metastore的元数据，不删除hdfs表中的数据
  - 外部表只有一个过程，加载数据和创建表同时完成，并不会移动数据到数据仓库之中，只是在外部建立了一个链接即映射关系，当删除一个外部表的时候，仅仅是删除这个外部表的元数据信息，以及这个链接，不会删除数据
  - 外部表和内部表在元数据的组织上是相同的，而实际数据的存储则有较大的差异
- 内部表和外部表的的相互转换
  - 内部表转外部表
  - alter table tablename set tblproperties (‘external’ = ‘true’)
  - 外部表转换成为内部表
  - alter table tablename set tblproperties (‘external’ = ‘false’)
- 外部表的创建
  - create external table tablename（）location ‘/data/externallll’
  - 指定外部表的位置这个位置会自动在hdfs上创建
分区表
- 通过分类将不同的数据放到不同的目录下，当使用hive查询的时候，根据分区的类型去判断子目录。然后扫表所有符合条件的子目录中的数据文件，提高查询的效率
- 分类的标准就是字段，这个字段可以是一个也可以是多个
- 分区表的意义在于优化查询，查询时尽量利用分区字段，如果不使用分区字段，就会全表扫描
- 实际工作中我们用的最多的就是外部分区表，通常是将时间作为分区字段
- 创建分区表
- create table partitiontable（）partitioned by (date string,type string)
- 加载数据
- load data local inpath ‘’ into table partitiontable partition(date=‘2000.01.01’,type=‘log’)
- 查看所有分区
- show partitions partitiontable
- 添加分区
- alter table partitiontable add partition （）
- 删除分区
- alter table partitiontable drop partition （）
- 外部分区表
- ```
create external table stu(
id int,
name string
)partitioned by (dt string)
row format delimited
fields terminated by '\t'
location ''
```
桶表
- 就是对数据进行哈希取值，打散以后放到不同的文件中存储
- 在hive分区表中，分区的数据量过于庞大时，建议使用桶表，分桶以后的效率比分区效率高
- 在分桶时，对指定字段的值进行hash运算得到hash值，并使用hash值除以桶的个数做取余运算得到的值进行分桶，保证每个桶中有数据但每个桶中的数据不一定相等
- 主要作用是做抽样调查等提高查询效率
- 创建一个桶表
  - create table tablename (id int) clustered by (id) into 3buckets; 根据ID字段进行哈希取值分到三个桶里面
  - buckets的数量等于实际计算式reduce的数量
  - 桶表不能使用load加载数据只能从另外一张表中插入数据
- 桶表的抽样查询语句
  - select * from tablename tablesample(bucket 1 out of 4 on id)
  - TABLESAMPLE(BUCKET x OUT OF y)
  - y必须是table总bucket的总数的倍数或者因数
  - hive根据y的大小决定抽样的比例
  - 例如：table总共分了64份，当y=32时，抽取(64/32=)2个bucket的数据，当y=128时，抽取(64/128=)1/2个bucket的数据。
  - x表示从哪个bucket开始抽取。
  - 例如，table总bucket数为32，tablesample(bucket 3 out of 16 on id)，表示总共抽取（32/16=）2个bucket的数据，分别为第3个bucket和第（3+16=）19个bucket的数据。
视图 view
- 使用视图可以降低查询的复杂度
- 视图的创建
  - create view aa as select tablename.id,tablename.name from tablename ;
- 视图的删除
  - drop view if exists aa
- 例子：针对json格式的源数据，可以抽取json中的指定字段建视图方便快速查询
  使用函数get_json_object
- 视图的缺点：如果源数据很大的话，查询起来会很慢，因为在查询的时候才会对源数据进行一条条的解析
- 视图就是对数据库的一层简化当一个查询引用一个视图时，这个视图所定义的查询语句将和用户的查询语句组合在一起，然后供Hive制定查询计划。从逻辑上讲，可以想象为Hive先执行这个视图，然后使用这个结果进行余下后续的查询。
  - ```
  原来的查询语句
  FROM (
  SELECT * FROM people JOIN cart
  ON(cart.people_id=people.id)WHERE firstname='john'
  ) a SELECT a.lastname WHERE a.id=3；
  增加一个视图
  CREATE VIEW shorter_join AS
  SELECT FROM people JOIN cart
  ON(cart.people_id=people.id) WHERE firstname='john'
  简化后的查询语句
  SELECT lastname FROM shorter_join WHERE id=3
```
索引
- Hive的索引目的是提高Hive表指定列的查询速度。
- 没有索引时，类似’WHERE tab.id = 10’ 的查询，Hive会加载整张表或分区，然后处理所有的rows，但是如果在字段id上面存在索引时，那么只会加载和处理文件的一部分。
- 与其他传统数据库一样，增加索引在提升查询速度时，会消耗额外资源去创建索引和需要更多的磁盘空间存储索引。
- Hive 0.7.0版本中，加入了索引。
- 不过维护索引的代价也是比较大的，在Hive中一般使用分区进行按天统计的需求比较多，所以索引的使用并不是很多。
- 创建索引
  - ```
  创建一张表
  CREATE TABLE employees(
      name STRING,
      salary FLOAT,
      subordinates ARRAY<STRING>,
      deductions MAP<STRING,FLOAT>,
      address STRUCT<street:STRING,city:STRING,state:STRING,zip:STRING
  ) PARTITIONED BY (country STRING , state STRING);
  根据country字段创建索引
  hive> create index employees_index
  on table employees(country)
  as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
  with deferred rebuild
  idxproperties ('creator'='me','created_at'='2017-2-13')
  in table employees_index_table
  partition by (country,name)
  comment 'Employees indexed by country and name.';
```
- 显示索引
  - show formatted index on t3_new;
- 重建索引
  - alter index t3_index on t3_new rebuild;
- 删除索引
  - drop index if exists t3_index on t3_new;
- 在Hive中有逻辑分区，而建立索引也是成为分区的另一种选择。建立索引可以帮助裁掉一张表的一些数据块，这样能够减少MapReduce的输入数据量。但并不是所有的查询都可以通过建立索引来获得查询速度。
- Hive中使用索引和关系型数据库中使用数据库一样，都需要进行仔细评估。因为索引其实是用空间换时间，所以用户需要权衡一下空间和时间之间的损失代价再做选择
装载数据
- 从文件中装载数据
  - load data [local] inpath ‘…’ [overwrite] into table t2 [partition(province=‘beijing’)];
- 通过从表中的查询将结果存入已存在的表中
  - insert [into/overwrite] table t2 [partition (province=‘beijing’)] select * from xxx where xxx
- 单语句建表并且装载数据
  - create table t4 as select …

hive的存储格式

hive的四种存储格式
- textfile 默认的存储格式
  - 存储方式：按行存储
  - 磁盘开销大，数据解析开销大
  - 压缩后的text文件 hive无法进行合并和拆封
- Squencefile
  - 二进制文件，以kv的形式序列化到文件中
  - 存储格式：行存储
  - 可分割支持压缩
  - 一般选择 block压缩
- RCfile 0.6.0以后
  - 存储格式数据按行分块每块按列存储
  - 压缩快快速列存取
  - 读记录尽量涉及到少的block
  - 读取需要的列只需要读取每个 row group的头部定义
  - 读取全量数据的操作性能对比Sequence file 没有优势
- ORCfile 0.11.0以后
  - 存储格式按行分块每块按列存储
  - 压缩快快速列存取
  - 是RCfile的改良版本
Textfile
- Hive 的默认数据存储格式，数据不做压缩，磁盘开销大，数据解析开销大。可结合 Gzip、Bzip2、Snappy 等使用（系统自动检查，执行查询时自动解压），但使用这种方式，hive不会对数据进行切分，从而无法对数据进行并行操
- create table t1_text(…) STORED AS TEXTFILE; 指定存储格式
- set hive.exec.compress.output=true; --启用压缩格式
- set mapred.output.compress=true;
- set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec; --指定输出的压缩格式为Gzip
- set io.compression.codecs=org.apache.hadoop.io.compress.GzipCodec;
- 开启压缩以后，再向表中添加数据的时候只能使用insert的方式，这样最终导入的数据才是压缩格式的，如果通过load的方式添加数据最终导出的数据就是原始的样子，没有导出成压缩格式的样式。
- 如果没有开启压缩的话 textfile文件是可以直接使用load的加载的但是其他格式的文件是不可以的
Sequencefile
- SequenceFile 是 Hadoop API 提供的一种二进制文件支持，其具有使用方便、可分割、
  可压缩的特点。
- SequenceFile 支持三种压缩选择：NONE，RECORD，BLOCK。Record 是默认选项，
  但是压缩率低，一般建议使用 BLOCK 压缩。
- ```
set hive.exec.compress.output=true; --启用压缩格式
set mapred.output.compression.codec=org.apache.hadoop.io.compress.Gzi
pCodec; --指定输出的压缩格式为 Gzip
set mapred.output.compression.type=BLOCK; --压缩选项设置为 BLOCK
set mapred.output.compress=true;
set io.compression.codecs=org.apache.hadoop.io.compress.GzipCodec;
```
- sequencefile的文件格式是无法直接看出来的需要专门的解析方法解析以后才可以查看
RCfile
- RCFILE 是一种行列存储相结合的存储方式。首先，其将数据按行分块，保证同一个 record
  在一个块上，避免读一个记录需要读取多个 block。其次，块数据列式存储，有利于数据
  压缩和快速的列存取
- ```
set hive.exec.compress.output=true;
set mapred.output.compression.codec=org.apache.hadoop.io.compress.Gzi
pCodec;
set mapred.output.compress=true;
set io.compression.codecs=org.apache.hadoop.io.compress.GzipCodec;
```
ORCfile
- ORC 是在一定程度上扩展了 RCFile，是对 RCFile 的优化
- set hive.default.fileformat=Orc;
存储格式的总结
- Textfile的存储空间消耗大，压缩的textfile无法进行分割合并，查询的效率低，可以直接存储，加载数据的速度的是最高的
- Sequencefile 存储空间消耗大，压缩后的文件可以进行分割以及合并，查询效率高，需要通过text文件进行转换加载
- RCfile 存储空间占用小，查询的效率高，需要通过text文件进行转换加载，加载数据的速度最低
- ORCfile是对RCfile的一种优化
- 相比 TEXTFILE 和 SEQUENCEFILE，RCFILE 由于列式存储方式，数据加载时性能消耗较
  大，但是具有较好的压缩比和查询响应。数据仓库的特点是一次写入、多次读取，因此，整
  体来看，RCFILE 相比其余两种格式具有较明显的优势。又因为 ORC 是对 RCFile 做了优
  化，所以建议使用 ORC 格式
Parquet
- 是0.13.0后新增加的存储格式，是列存储
- 能够很好的压缩，具有很好的查询性能，但是写速度通常就比较慢了
- Parquet 是与语言无关的一种存储格式，不与任何一种数据处理框架绑定在一起，适配多种语言以及组件，能够与Parquet的组件有：
  - 查询引擎：Hive, Impala
  - 计算框架：MapReduce, Spark
- 在工作中 Spark 计算的结果可以直接输出为 Parquet 格式，然后后续再使用 Hive 进行
  分析处理。所以这种格式也是推荐使用的。
Hive SerDe
- SerDe 是 "Serializer and Deserializer."的缩写，目的是用于序列化和反序列化
- Hive 使用 SerDe 和 FileFormat 进行行内容的读写.
- hive 读数据： HDFS 文件 --> InputFileFormat --> <key, value> -->
  Deserializer --> 行对象
- hive 写数据：行对象 --> Serializer --> <key, value> --> OutputFileFormat
  –> HDFS 文件
- 默认 hive 使用 org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe 序列
  化和反序列化数据
- 如果 ROW FORMAT 没有指定或者指定了 ROW FORMAT DELIMITED 就会使用本地默认的
  Serde
- Hive 使用如下 SerDe 类(反)序列化数据:
  - LazySimpleSerDe 不指定 Serde 时，默认使用的 Serde
  - MetadataTypedColumnsetSerDe: 读写 csv 文件
  - ThriftSerDe: 读写 Thrift 序列化后的对象.
  - DynamicSerDe: 读写 Thrift 序列化后的对象, 不过不需要解读 schema 中的 ddl.

hive的高级函数

hive函数说明

同 mysql 一样的，因为 hive 也是一个主要做统计的软件，所以为了满足各种各样的统计
需要，也内置了相当多的函数
show functions 查看所有的函数
desc function year 查看year函数的具体内容
desc function extended year 查看year函数的扩展内容

hive的高级函数

count（）计算数量
- select count（*） from table
sum（）计算和
- select sum（score） from table

hive的扩展函数

分组排序取TopN
- row_number() 行号
- over(partition by column order by column) 根据某列分区排序
- rank() 如果排序的值相等则其后面的值是行号加一就是如果第二第三名并列则没有第三直接第四
- dense_rank() 如果并列行号不变则如果第二第三并列则两个第二后面的是第三

列转行

concat_ws collect_list(所有列）collect_set(不重复的列）

行转列
name favor 

zs	sing
zs	basketball
zs	swing
ls	mkl
ls	aass
建表
create external table favor(
name string,
favor string
)row format delimited
fields terminated by '\t'
lines terminated by '\n'
location '/data/favor'; 

装载数据
hdfs dfs -put favor.dat /data/favor

行转列

select name,concat_ws(',',collect_list(favor)) as favor_list from favor group by name;

行转列
- explode() split() lateral view
- ```
列转行
建表以及数据
create table favor2 as select name,concat_ws(',',collect_list(favor)) as favor_list from favor group by name;
ls      mkl,aass
zs      sing,basketball,swing

explode()  split() lateral view
select name,favor_list,favor from favor2 lateral view explode(split(favor_list,',')) tablel as favor;
```
- Lateral View语法
  - 在将 explode 和 split 等这些内嵌函数组合起来使用的时候，是不能直接放在查询字段
    的后面的，这里需要使用到关键字 lateral view，从另外一个角度来获取数据。
  - lateral view 用于和 split, explode 等 UDTF 一起使用，它能够将一行数据拆成多
    行数据，在此基础上可以对拆分后的数据进行聚合。lateral view 首先为原始表的每行
    调用 UDTF，UDTF 会把每行拆分成一行或者多行，lateral view 再把结果组合，产生一
    个支持别名表的虚拟表。这里的 table1 为虚拟表名

hive函数的对比

order by 与sort by 的对比
- order by 跟在SQL中是一样的，直接进行全局排序
- sort by 是在reduce中进行的，只能保证局部有序，即每一个reduce中是有序的，但不能保证所有reduce是有序的
distribute by
- distribute by 是控制map的输出在reducer是如何划分的
- 只会根据指定的 key 对数据进行分区，但是不会排序
- 一般情况下可以和sort by 结合使用来实现分组排序
- distribute by 必须写在sort by 之前
cluster by
- 是distribute 与 sort的结合
- 如果分组以及排序的字段是一样的就可以使用cluster by 进行分组排序
- 默认只能是升序排列而且不能指定为倒序即不能使用desc
group by 与 over(partition by )的区别
- group by 可以实现同样的分组聚合操作，但是SQL语句中不能写与分组聚合无关的字段，否则就会报错
- group by 的 hive sql 语句只能显示与分组聚合相关的字段
- over(partition by …)的 hive sql 语句能显示所有字段
group by 与 distinct 的区别
- 需求：统计order表中的name去重之后的数量
- 实现：
  - select count（distinct name） from order
  - select count（tmp.name) from （select name from order group by name）tmp;
- 分析：使用distinct 会将所有的name 都shuffle到一个reducer之中，性能比较低
- 使用group by 对name进行分组因为分组的同时就是去重，然后在计算count即可

hive的自定义函数

那有时候，这些 hive 内嵌的函数，虽然说功能非常的强大，但是我们的业务可能是千变万
化的，他有时候还是不能很好的满足我们的一些个性化的要求，而我们又想完成我们的工作，
那么该怎么办呢？这个时候呢，就可以通过我们自定义的函数来完成我们的功能需求了，这
个时候就需要我们写一点 java 代码了
- 自定义函数分为两种
  - UDF F(user defined function)用户自定义函数
  - UDAF(user defined aggregation function)用户自定义聚合函数
UDF操作
- 自定义UDF extends org.apache.hadoop.hive.ql.exec.UDF（继承）
- 需要实现evaluate函数，evaluate函数支持重载
- 将写好的项目打jar包放到hive机器上
- 进入hive的客户端添加jar包 hive>add jar jar包路径
- 创建临时函数：hive>create temporary function 自定义函数名 as ‘自定义函数的主类名’
- 执行hql语句
- 销毁临时函数：hive > drop temporary function 自定义函数名
- UDF 只能实现一进一出的操作，如果需要实现多进一出，则需要实现 UDAF（聚合函数），
  做法类似
- ```
import org.apache.hadoop.hive.ql.exec.Description;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;
@Description(value = "_FUNC_(str) - Returns str with all characters
changed to uppercase",
extended = "Example:\n>SELECT _FUNC_(str) from src")
public class MyUpper extends UDF {
public Text evaluate(Text text) {
if(text != null) {
return new Text(text.toString().toUpperCase());
} else {
return null;
}
}
}
```

UDAF

hive中的UDAF是多进一出，类似聚合函数 count（） sum（）
自定义UDAF需要继承UDAF
还要实现org.apache.hadoop.hive.ql.exec.UDAFEvaluator接口
其他步骤和UDF是一样的

import org.apache.hadoop.hive.ql.exec.Description;
import org.apache.hadoop.hive.ql.exec.UDAF;
import org.apache.hadoop.hive.ql.exec.UDAFEvaluator;
import org.apache.hadoop.io.LongWritable;
@Description(value = "_FUNC_(str) - get max value",
extended = "Example:\n>SELECT _FUNC_(str) from src")
public class MyMax extends UDAF{
public static class MaxNumber implements UDAFEvaluator{
//最终结果
private LongWritable result;
//负责初始化计算函数并设置它的内部状态，result 是存放最终结果的
public void init() {
result=null;
}
//每次对一个新值进行比较计算的时候都会调用 iterate 方法
public boolean iterate(LongWritable value)
{
if(value==null)
return false;
if(result==null)
result=new LongWritable(value.get());
else
result.set(Math.max(result.get(), value.get()));
return true;
}
//Hive 需要部分比较结果的时候会调用该方法,会返回一个封装了当前状态的对
象
public LongWritable terminatePartial()
{
return result;
}
//合并两个部分比较结果时会调用这个方法
public boolean merge(LongWritable other)
{
return iterate(other);
}
//Hive 需要最终比较结果的时候会调用该方法
public LongWritable terminate()
{
return result;
}
}
}

hive性能优化

hive与hadoop的调用关系

hive的执行大部分都会被转换为mr去执行但是我们知道 mr的执行过程较慢，name怎么才能避免一些指令转换成mr任务呢
- 进行全表扫描的时候即hadoop中 hadoop fs -text/hadoop fs -tail的命令就不会走mr，在hive中的表现形式就是 select * from table；
- 普通的表 select * from tablename [limit num]
- 分区表 select * from tablename where partition_sec [limit num]
  -
hive到mr的转换过程
- executeQuery：用户通过 Hive 界面（CLI/Web UI）将查询语句发送到 Driver（驱
  动有 JDBC、ODBC 等）来执行；
- getPlan ：Driver 根据查询编译器解析 query 语句，验证 query 语句的语法、查询计
  划、查询条件；
- getMetaData：编译器将元数据请求发送给 Metastore；
- send MetaData：Metastore 将元数据作为响应发送给编译器
- send Plan：编译器检查要求和重新发送 Driver 的计划。至此，查询的解析和编译完成；
- execute Plan：Driver 将执行计划发送给执行引擎；
  - MetaDataOps for DDLs：执行引擎发送任务的同时，对 hive 元数据进行相应操作
    （直接对数据库表进行操作的（创建表、删除表等），直接与 MetaStore 进行交互）
  - execute Job：mapreduce 执行 job 的过程。执行引擎发送任务到 resourcemanager，
    resourcemanager 将任务分配给 nodenameger，由 nodemanager 分布式执行 mapreduce任务。
  - 任务执行结束，返回执行结果给执行引擎，同步执行下一步
  - 找 Namenode 获取数据
- fetch Results：执行引擎接收来自数据节点(data node)的结果
- sendResults：执行引擎发送结果数据到 Driver
- sendResults：Driver 将结果发送到 hive 接口

性能调优

尽量减少产生mr任务的hql语句使用 explain来观察查询语句的执行过程
设置严格模式将消耗性能时间表多的语句进行限制
- 强制分区表的where条件过滤即必须制定分区查询
- order by 必须使用limit 因为它是对全局进行排序不指定获取前几个的话会直接获取所有数据
- 限制产生笛卡尔积的查询
开启中间压缩
- 对中间数据进行压缩可以减少 job 中 map 和 reduce task 间的数据传输量。此时，选择一个
  低 CPU 开销的编/解码器要比选择一个压缩率高的编/解码器更重要
- 属性 hive.exec.compress.intermediate 的默认值为 false。开启中间压缩的话需要设
  置为 true
- SnappyCodec 是一个比较好的中间文件压缩编/解码器，因为其很好地结合了低 CPU 开销和好的压缩执行效率
- set mapred.map.output.compression.codec=org.apache.hadoop.io.compress.Sna
  ppyCodec
表连接
- inner join 两张表都有的字段，并且符合连接条件
  - select t1.name,t1.age,t9.age from t9 join t1 on t1.name=t9.name;
- left outer join 左边的表符合where条件右边的表可以为空
  - select t1.name,t2.age from t1 left join t2 on t1.id = t2.uid;
  - 左外连接左表为主表如果右边表中没有想匹配的就显示为null
- right outer join 右边表符合where条件左边的表可以为空
- full outer join 返回所有表符合where条件的记录没有使用null替代
- left semi-join 左边的表中符合右边on条件出现，右边的表不出现
  - select t1.name,t1.age from t9 LEFT SEMI JOIN t1 on t1.name=t9.name;
  - 即不查询右边表的数据但是需要他的字段进行判断
- map-side JOIN 只有一张小表在map阶段将表完全放到内存中
  - select /*+ mapjoin(t9) */t1.name,t1.age from t9 JOIN t1 on
    t1.name=t9.name;

数据倾斜以及解决方案

表现：任务进度长时间维持在 99%（或 100%），查看任务监控页面，发现只有少量（1 个或几个）reduce子任务未完成。因为其处理的数据量和其他 reduce 差异过大。单一 reduce 的记录数与平均记录数差异过大，通常可能达到 3 倍甚至更多。最长时长远大于平均时长。
产生原因：
- key分布不均匀
- 业务数据本身的特性
- 建表的时候考虑不周
- SQL语句本身就有数据倾斜

容易产生数据倾斜的语句

关键字	现象	后果
join	其中一个表较小，但是key集中	分发到某一个或几个Reduce上的数据远高于平均值
join	大表与小表，但是分区的判断字段0值或空值过多	这些空值都由一个reduce处理，非常慢
group by	group by维度过小，处理的数量过多	处理某值的reduce非常耗时
count distinct	某特殊值过多	处理此特殊值的reduce非常耗时

解决方案：

SELECT a.Key
, SUM(a.Cnt) AS Cnt
FROM (
SELECT Key
, COUNT(*) AS Cnt
FROM TableName
GROUP BY Key,
CASE
WHEN Key = 'KEY001' THEN Hash(Random()) % 50    将比较大的key打散 随机分50分进行处理 
ELSE 0
END
) a
GROUP BY a.Key;

参数调节：
- hive.map.aggr=true 让数据在map阶段进行局部聚合
- hive.groupby.skewindata=true 将负载均衡打开会产生两个mrjob同时执行

SQL语句调节：
- 大小表 Join：
  - 使用 map join 让小的维度表（1000 条以下的记录条数）先进内存。在 map 端完成 reduce.
- 大表 Join 大表：
  - 把空值的 key 变成一个字符串加上随机数，把倾斜的数据分到不同的 reduce 上，由于 null值关联不上，处理后并不影响最终结果。
- count distinct 大量相同特殊值
  - count distinct 时，将值为空的情况单独处理，如果是计算 count distinct，可以不用
    处理，直接过滤，在最后结果中加 1。如果还有其他计算，需要进行 group by，可以先将值为空的记录单独处理，再和其他计算结果进行 union。
- 去重
  - 采用 sum() group by 的方式来替换 count(distinct)完成计算