HIVE应用文档

hive基于hadoop的数据仓库工具,用来进行数据提取,转化,加载,这是一种可以存储，查询,分析存储在Hadoop中的大规模数据的机制，hive数仓工具能够讲结构化的数据文件映射为一张数据库表，并提供sql查询功能，将sql转化为mapreduce任务执行

一、数据库连接(beeline)

1.1 beeline连接并操作hive

登录

kinit -kt tkmodellab.keytab  tkmodellab@BIGDATA.TAIKANG.COM
beeline -u "jdbc:hive2://10.129.181.72:10003/tkmodellabdb;principal=hive/tkhdp-mgmt5.bigdata.taikang.com@BIGDATA.TAIKANG.COM"
beeline -u "jdbc:hive2://10.129.181.72:10003/tkmodellabdb;principal=hive/tkhdp-mgmt5.bigdata.taikang.com@BIGDATA.TAIKANG.COM" --outputformat=csv -e "SELECT * from jt_tyjrdb.ods_tkdw_agt_prs WHERE dt=20220215;" > /home/tkmodellab/ods_tkdw_agt_prs.csv;    ---操作导出数据到你的docker


---案例
beeline -u jdbc:hive2://host:10000 --incremental=true --showHeader=false --outputformat=dsv --delimiterForDSV=$'\t' -e 'select * from test' > file.csv

参数	含义
–incremental=[true/false]	从Hive 2.3版本往后默认是true，在它之前是默认为false。当设置为false时，为了最佳的展示列宽，完整的结果集会在展示之前被收集然后缓存起来。当设置为true时，结果集一旦被抓取到就会立即展示， 为了在展示列的填充额外消耗更少的延迟和内存。当你在客户端遭遇一个内存溢出时，推荐设置–incremental=true (因为抓取到的结果集非常大)。
–showHeader=[true/false]	展示列名是否在查询结果中。默认是true。用例: beeline --showHeader=false
–outputformat=[table/vertical/csv/tsv/dsv/csv2/tsv2]	结果展示的模式。默认是table。dsv可配合delimiterForDSV一起使用，设置分隔符
–delimiterForDSV= DELIMITER	用于输出格式中划分值的界定符。默认是‘|’，如果需要输入特殊符号，如‘\n001’ ，‘\x01’等需在前面加$，例 --delimiterForDSV=$’\t’
-e <query>	导出sql0

把导出的csv文件scp至相连的服务器

• 如果下载了sshpass则可以通过-p来带入密码
• 导出后再通过xshell连上导出数据的服务器来download到本地

- 如果下载了sshpass则可以通过-p来带入密码
- 导出后再通过xshell连上导出数据的服务器来download到本地
scp -p T@ikanguseradmin ods_tkdw_agt_extend.csv useradmin@10.129.181.123:/home/useradmin/ods_tkdw_agt_extend.csv


- 如果下载了sshpass则可以通过-p来带入密码
- 导出后再通过xshell连上导出数据的服务器来download到本地

• 如果下载了sshpass则可以通过-p来带入密码
• 导出后再通过xshell连上导出数据的服务器来download到本地

scp -p T@ikanguseradmin ods_tkdw_agt_extend.csv useradmin@10.129.181.123:/home/useradmin/ods_tkdw_agt_extend.csv

• 如果下载了sshpass则可以通过-p来带入密码
• 导出后再通过xshell连上导出数据的服务器来download到本地

2.2 HIVE-EXPORT-DB2

需要监控提交的任务,可以根据jobID在yarn上监控，本质是将导入导出命令翻译成mapreduce实现，在翻译出的mapreduce中主要对imputformat和outputformat进行定制

将数据从HDFS文件导出到RDBMS数据库导出前，目标表必须存在于目标数据库中。

默认操作是将文件中的数据使用INSERT语句插入到表中。

更新模式下，是生成UPDATE语句更新表数据，一共有两个模式，updateonly模式(只更新现有字段)和allowinsert模式（类似于update else insert）

• 代码逻辑：从hive-export-DB2
• 参数列表：罗列代码中涉及的参数和含义

  参数	含义
  --connect	jdbc连接串
  --useradmin/--password	目标数据库账号密码
  --table	表名(db2需要指定schema)
  --fields-terminated-by	对HDFS上面文件所采用的分隔符，默认是' , '，但有的时候文件可能是' \t ' 这样的分隔符
  --export-dir	HDFS上默认路径 /user/hive/warehouse/${库名.db}/${tablename}
  --hcatalog-database	源数据库(hive.db)
  --m	==1 意味执行启动一个map task执行数据导入，若不设置则需要指定一列(分区字段)来作为划分map task任务的依据
  --quary(import)	执行逻辑 格式为双引号"" 若报错缺少参数$conditions 正确格式案例 –query "select id,check_code,lastupdatetime,check_num,report_name,json_object_keys(reportinfo) from check_report where reportinfo::Text<>‘null’ and \$CONDITIONS "
  --where(import)
  --update-key	更新主键
  --update-mode	更新模式

sqoop export --connect jdbc:db2://10.129.82.171:50000/TKFADW --username TKDEV --password T@ikangtkdev --table TKMGT.ods_tkdw_agt_prs --fields-terminated-by '\t' --export-dir --HDFS上默认路径 /user/hive/warehouse/库名.db/...  

sqoop export --connect jdbc:db2://10.129.82.171:50000/TKFADW --username TKDEV --password T@ikangtkdev --table TKMGT.tkdw_d_plcy --fields-terminated-by '\t' --hcatalog-database tkmodellabdb--hcatalog-table tkdw_d_plcy -m 1 --columns plcy_idprod_catprod_subcatplcy_lgcy_nbrplcy_own_idplcy_own_incomegrp_idsurr_prd_idbrc_idagt_idrnew_agt_idplcy_type_idplcy_ftrs_idplcy_stat_idpymt_freq_idchnl_idterm_idbk_idapp_prd_iddet_prd_idstrt_prd_idsurr_daternew_prd_idexpr_prd_idend_pymt_prd_idcfm_prd_idlps_prd_idrst_prd_idplcy_own_keystat_prd_idload_day_idupd_day_idapp_datedet_datestrt_daternew_dateycb_det_dateexpr_dateend_pymt_datecfm_datelps_daterst_datechnl_ybt

案例：
sqoop export --connect jdbc:db2://$db2_ip:$db2_port/$db2_name --username $db2_user --password $db2_pwd --table $db2_tablename --hcatalog-database jtzn_tdlogdb --hcatalog-table $hive_tablename -m 1 --columns dt,mobile,num 

---updateonly
sqoop export --connect jdbc:mysql://localhost:3306/wht --username root --password cloudera --tablewht_test2 --fields-terminated-by ',' --update-key c_id --export-dir /user/hive/warehouse/wht_test1

--allowinsert
sqoop export --connect jdbc:mysql://localhost:3306/wht --username root --password cloudera --tablewht_test2 --fields-terminated-by ',' --update-key c_id --update-mode allowinsert --export-dir /user/hive/warehouse/wht_test1

一.hive上传文件

1.带表头 直接在hue或者dbeaver上import 导入表，可以自己调整

2.不带表头

2.1首先新建一个表然后根据csv文件的分隔符建表

ROW FORMAT 
DELIMITED FIELDS TERMINATED BY ','
stored as textfile;  ----(只有这个类型的表才可以用离线文件导入)，
根据需求可看需不需要再把这个表import 至其他类型的表(转1)

2.2把csv文件上传到租户所在docker

scp /路径/filename.csv tkmodellab@10.129.181.33 /home/tkmodellab 
hadoop fs -put /home/tkmodellab/stream_order_data_new.csv /user/hive/warehouse/tkmodellabdb.db

2.3登录hive再加载数据到 textfile 这个类型的表 load data后会自动把这个csv文件删掉

一般来说hive都是默认地址如下/user/hive/warehouse/xxx.db

load data  inpath '/user/hive/warehouse/tkmodellabdb.db/stream_customer_data_new.csv'   into table $tablename  

加载完成后验证

2.4 RDBMS-IMPORT-HIVE

参数	含义
--connect	jdbc连接串
--useradmin/--password	目标数据库账号密码
--table	表名(db2需要指定schema)
--fields-terminated-by	对HDFS上面文件所采用的分隔符，默认是' , '，但有的时候文件可能是' \t ' 这样的分隔符
--export-dir	HDFS上默认路径 /user/hive/warehouse/${库名.db}/${tablename}
--split-by	指定切分字段
--m	==1 意味执行启动一个map task执行数据导入，若不设置则需要指定一列(分区字段)来作为划分map task任务的依据
--quary(import)	执行逻辑 格式为双引号"" 若报错缺少参数$conditions 正确格式案例 –query "select id,check_code,lastupdatetime,check_num,report_name,json_object_keys(reportinfo) from check_report where reportinfo::Text<>‘null’ and \$CONDITIONS "
--where	"..." 匹配条件
–hive-database	库名
–hive-import	必须参数，指定导入hive
–hive-table	hive表名
–fields-terminated-by	hive的分隔符
–hive-overwrite	重写重复字段
–create-hive-table	帮创建好 hive 表，但是表存在会出错。不建议使用这个参数，因为到导入的时候，会与我们的字段类型有出入。
–hive-partition-key	指定分区表的字段
–hive-partition-value	指定分区表的值

sqoop import
--connect jdbc:mysql://192.168.66.4:3306/networkmanagement \
--username sendi \
--password 1234 \
--table people
--hive-import 
--create-hive-table 
--fields-terminated-by "\t"
-m 5

二.hive文件存储格式

首先科普一下行列存储的概念

1. 行存储可以理解为一条记录存储一行，通过条件能够查询一整行数据。TEXTFILE和SEQUENCEFILE的存储格式都是基于行存储的；

2. 列存储，以字段聚集存储，可以理解为相同的字段存储在一起。在查询只需要少数几个字段的时候，能大大减少读取的数据量；每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算法。 select 某些字段效率更高 ；ORC和PARQUET是基于列式存储的

列存储有更好的查询以及压缩比，但是数据加载或插入的话需要整个重构，没有行存储来的方便，直接一行插入

Hive文件存储格式包括以下几类：

• TEXTFILE
• SEQUENCEFILE
• RCFILE
• 自定义格式

其中TEXTFILE为默认格式，建表时不指定,默认为这个格式，导入数据时会直接把数据文件拷贝到hdfs上不进行处理。

SequenceFile,RCFile格式的表不能直接从本地文件导入数据，数据要先导入到textfile格式的表中，然后再从textfile表中用insert导入到SequenceFile,RCFile表中。

2.1 TEXTFILE

默认格式，数据不做压缩，磁盘开销大，数据解析开销大。可结合Gzip、Bzip2使用（系统自动检查，执行查询时自动解压）， 但使用这种方式，hive不会对数据进行切分，从而无法对数据进行并行操作。存储方式为行存储，在反序列化过程中，必须逐个字符判断是不是分隔符和行结束符，因此反序列化开销会比SequenceFile高几十倍。

> create table test1(str STRING)  
> STORED AS TEXTFILE;
OK  
Time taken: 0.786 seconds  
#写脚本生成一个随机字符串文件，导入文件：  
> LOAD DATA LOCAL INPATH '/home/work/data/test.txt' INTO TABLE test1;  
Copying data from file:/home/work/data/test.txt  
Copying file: file:/home/work/data/test.txt  
Loading data to table default.test1  
OK  
Time taken: 0.243 seconds

2.3 RCFILE

RCFILE是一种行列存储相结合的存储方式。首先，其将数据按行分块，保证同一个record在一个块上，避免读一个记录需要读取多个block。其次，块数据列式存储，有利于数据压缩和快速的列存取。数据加载时性能消耗较大，但是具有较好的压缩比和查询响应。数据仓库的特点是一次写入、多次读取，因此，整体来看，RCFILE相比其余两种格式具有较明显的优势。RCFile不支持任意方式的数据写操作，仅提供一种追加接口，这是因为底层的 HDFS当前仅仅支持数据追加写文件尾部。数据仓库的特点是一次写入、多次读取，因此，整体来看，RCFILE相比其余两种格式具有较明显的优势。大多数的Hadoop和Hive存储都是行式存储的，如果一个表有成百上千个字段，一般查询只对个别字段进行查询，基于这些场景，Hive设计了RCfile（RCFILE是基于SEQUENCEFILE实现的列存储格式）

RCFILE文件示例：

 > create table test3(str STRING)  
> STORED AS RCFILE;  
OK  
Time taken: 0.184 seconds  
>  INSERT OVERWRITE TABLE test3 SELECT * FROM test1; 

2.3 ORCFILE

是RCFILE的进阶版，相比TEXTFILE和SEQUENCEFILE，RCFILE由于纯列式存储方式，数据加载时性能消耗较大，但是具有较好的压缩比和查询响应。ORC支持三种压缩：ZLIB,SNAPPY,NONE。最后一种就是不压缩，orc默认采用的是ZLIB压缩；

每个Orc文件由1个或多个stripe组成，每个stripe250MB大小（Stripe实际相当于RowGroup），不过大小由4MB->250MB，这样能提升顺序读的吞吐率。每个Stripe里有三部分组成，分别是Index Data,Row Data,Stripe Footer：

1. 一个orc文件可以分为若干个Stripe；
2. 一个stripe可以分为三个部分；
3. indexData：某些列的索引数据
4. rowData :真正的数据存储
5. StripFooter：stripe的元数据信息

1）Index Data：轻量级的index，默认是每隔1W行做一个索引。这里做的索引只是记录某行的各字段在Row Data中的offset。

2）Row Data：存的是具体的数据，先取部分行，然后对这些行按列进行存储。对每个列进行了编码，分成多个Stream来存储。

3）Stripe Footer：存的是各个stripe的元数据信息。

每个文件有一个File Footer，这里面存的是每个Stripe的行数，每个Column的数据类型信息等；

每个文件的尾部是一个PostScript，这里面记录了整个文件的压缩类型以及FileFooter的长度信息等。在读取文件时，会seek到文件尾部读PostScript，从里面解析到File Footer长度，再读FileFooter，从里面解析到各个Stripe信息，再读各个Stripe，即从后往前读。

总结：

• orc 默认的压缩方式ZLIB比Snappy压缩的还小。
• 在实际的项目开发当中，hive表的数据存储格式一般选择：orc或parquet。
• 由于snappy的压缩和解压缩 效率都比较高，压缩方式一般选择snappy。

存储格式      ORC       Sequencefile   Parquet      RCfile    Avro
数据压缩后大小 1.8G         67.0G        11G         63.8G    66.7G
存储耗费时间   535.7s       625.8s       537.3s      543.48s   44.3s
SQL查询响应速度 19.63s      184.07s      24.22s      88.5s     281.65s

2.3.1 hive导出及查看orc文件

---hive导出至text文件
hive --orcfiledump -d /user/hive/warehouse/jtzn_tdlogdb.db/ycpm_order_info_tmp/000000_0 > /home/jtzn_tdlog/ycpm_order_info_tmp.txt

----hive通过json查看格式
hive --orcfiledump -j -p /user/hive/warehouse/jtzn_tdlogdb.db/ycpm_order_info_tmp/000000_0

2.4 自定义格式

当用户的数据文件格式不能被当前 Hive 所识别的时候，可以自定义文件格式。用户可以通过实现inputformat和outputformat来自定义输入输出格式，参考代码：.\hive-0.8.1\src\contrib\src\java\org\apache\hadoop\hive\contrib\fileformat\base64

> create table test4(str STRING)  
> stored as  
> inputformat 'org.apache.hadoop.hive.contrib.fileformat.base64.Base64TextInputFormat'  
> outputformat 'org.apache.hadoop.hive.contrib.fileformat.base64.Base64TextOutputFormat';  
$ cat test1.txt 
aGVsbG8saGl2ZQ==
aGVsbG8sd29ybGQ=
aGVsbG8saGFkb29w

test1文件为base64编码后的内容，decode后数据为：

hello,hive

hello,world

hello,hadoop

load数据并查询：

hive> LOAD DATA LOCAL INPATH '/home/work/test1.txt' INTO TABLE test4;   
Copying data from file:/home/work/test1.txt  
Copying file: file:/home/work/test1.txt  
Loading data to table default.test4  
OK  
Time taken: 4.742 seconds  
hive> select * from test4;  
OK  
hello,hive  
hello,world  
hello,hadoop  
Time taken: 1.953 seconds

hive文件存储格式参考文档：http://t.csdn.cn/gCyjR

三.hive架构

3.1 数据存储位置

Hive的数据存储在hdfs上，元数据可以存储在指定的地方，比如mysql或者PostgreSQL

3.1.1 元数据

在安装Hive时，需要在hive-site.xml文件中配置元数据相关信息。与传统关系型数据库不同的是，hive表中的数据都是保存的HDFS上，也就是说hive中的数据库、表、分区等都可以在HDFS找到对应的文件。这里说到的元数据可以理解成hive中用于保存数据库、表、分区或者表字段等基本属性，以及这些属性与HDFS文件对应关系的一个映射。

这些映射关系比较常见的一个场景是保存在mysql数据库中。接下来会分析hive安装时的一些配置信息，以及元数据库中主要表的用途。hive元数据对应的表约有20个，其中和表结构信息有关的有9张，其余的10多张或为空，或只有简单的几条记录，以下是部分主要表的简要说明。

表名	说明	关联键
TBLS	所有hive表的基本信息	TBL_ID,SD_ID
TABLE_PARAM	表级属性，如是否外部表，表注释等	TBL_ID
COLUMNS	Hive表字段信息(字段注释，字段名，字段类型，字段序号)	SD_ID
SDS	所有hive表、表分区所对应的hdfs数据目录和数据格式	SD_ID,SERDE_ID
SERDE_PARAM	序列化反序列化信息，如行分隔符、列分隔符、NULL的表示字符等	SERDE_ID
PARTITIONS	Hive表分区信息	PART_ID,SD_ID,TBL_ID
PARTITION_KEYS	Hive分区表分区键	TBL_ID
PARTITION_KEY_VALS	Hive表分区名(键值)	PART_ID

从上面表的内容来看，hive整个创建表的过程已经比较清楚了。

1. 解析用户提交hive语句，对其进行解析，分解为表、字段、分区等hive对象
2. 根据解析到的信息构建对应的表、字段、分区等对象，从 SEQUENCE_TABLE中获取构建对象的最新ID，与构建对象信息（名称，类型等）一同通过DAO方法写入到元数据表中去，成功后将SEQUENCE_TABLE中对应的最新ID+5。

实际上我们常见的RDBMS都是通过这种方法进行组织的，典型的如postgresql，其系统表中和hive元数据一样裸露了这些id信息（oid,cid等），而Oracle等商业化的系统则隐藏了这些具体的ID。通过这些元数据我们可以很容易的读到数据诸如创建一个表的数据字典信息，比如导出建表语名等。

3.2 数据更新

Hive处理数据时一般不对数据进行改写，因为它不支持行级别的增删操作，如果要进行更新数据，一般可以通过分区或者表直接覆盖。插入数据。Hive不支持一条一条的用 insert 语句进行插入操作，这个应该是与hive的storage layer是有关系的，因为它的存储层是HDFS，插入一个数据要全表扫描，还不如用整个表的替换来的快些。Hive也不支持update的操作。数据是以load的方式，加载到建立好的表中。数据一旦导入，则不可修改。要么drop掉整个表，要么建立新的表，导入新的数据。

3.3 内部表&外部表

未被external修饰的是内部表（managed table），被external修饰的为外部表（external table）区别：

内部表数据由Hive自身管理，外部表数据由HDFS管理

内部表数据存储的位置是hive.metastore.warehouse.dir（默认：/user/hive/warehouse），外部表数据的存储位置由自己制定（这是存储表的位置，如果没有LOCATION，Hive将在HDFS上的/user/hive/warehouse文件夹下以外部表的表名创建一个文件夹，并将属于这个表的数据存放在这里）

删除内部表会直接删除元数据（metadata）及存储数据；删除外部表仅仅会删除元数据，HDFS上的文件并不会被删除；即虽然数据都存在HDFS中，但是内置的Hive程序可以管理一部分空间，这部分空间保存着内部表的相关数据。可以这样理解，Hive程序管理着部分空间，剩下的交给HDFS管理

对内部表的修改会将修改直接同步给元数据，而对外部表的表结构和分区进行修改，则需要修复（MSCK REPAIR TABLE table_name;）

四.hive的应用

4.1 四个by的区别

1. Sort By：在同一个分区内排序
2. Order By：全局排序，只有一个Reducer
3. Distrbute By：类似 MapReduce 中Partition，进行分区，一般结合sort by使用
4. Cluster By：当 Distribute by 和 Sort by 字段相同时，可以使用Cluster by方式。Cluster by 除了具有 Distribute by 的功能外还兼具 Sort by 的功能。但是只能升序排序，不能指定排序规则为ASC或者DESC

4.2 行转列函数

1. CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串。例如： concat( aa, ‘:’, bb) 就相当于把aa列和bb列用冒号连接起来了，aa:bb。
2. CONCAT_WS(separator, str1, str2,…)：CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。但是CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。
3. COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

4.3 列转行函数

1. EXPLODE(col)：将hive某列中复杂的array或者map结构拆分成多行。
2. LATERAL VIEW：常和UDTF函数一起使用。

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

4.4 Rank排名函数

1. RANK() 排序相同时会重复，总数不会变；
2. DENSE_RANK() 排序相同时会重复，总数会减少；
3. ROW_NUMBER() 根据顺序计算排名。

4.5 窗口函数

1. OVER()：用于指定分析函数工作时的数据窗口大小，这个数据窗口大小可能会随着行的变而变化；
2. CURRENT ROW：当前行；
3. n PRECEDING：往前n行数据；
4. n FOLLOWING：往后n行数据；
5. UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点；
6. LAG(col,n,default_val)：往前第n行数据；
7. LEAD(col,n, default_val)：往后第n行数据；
8. NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。这个函数需要注意：n必须为int类型。

4.5 如何解决数据倾斜及优化

1. Group by

注：group by 优于 distinct group

情形：group by 维度过小，某值的数量过多

后果：处理某值的 reduce 非常耗时

解决方式：采用 sum() group by 的方式来替换 count(distinct)完成计算。

1. count(distinct)

情形：某特殊值过多

后果：处理此特殊值的 reduce 耗时；只有一个 reduce 任务

解决方式：count distinct 时，将值为空的情况单独处理，比如可以直接过滤空值的行，

在最后结果中加 1。如果还有其他计算，需要进行 group by，可以先将值为空的记录单独处

理，再和其他计算结果进行 union。

1. 不同数据类型关联产生数据倾斜

情形：比如用户表中 user_id 字段为 int，log 表中 user_id 字段既有 string 类型也有 int 类

型。当按照 user_id 进行两个表的 Join 操作时。

后果：处理此特殊值的 reduce 耗时；只有一个 reduce 任务

默认的 Hash 操作会按 int 型的 id 来进行分配，这样会导致所有 string 类型 id 的记录都分配

到一个 Reducer 中。

解决方式：把数字类型转换成字符串类型

select *
from
users a 
left outer join
logs b
on a.usr_id =cast(b.user_id as string)

1. 开启数据倾斜时负载均衡

set hive.groupby.skewindata=true;

思想：就是先随机分发并处理，再按照 key group by 来分发处理。

操作：当选项设定为 true，生成的查询计划会有两个 MRJob。

第一个 MRJob 中，Map 的输出结果集合会随机分布到 Reduce 中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的 GroupBy Key 有可能被分发到不同的Reduce 中，从而达到负载均衡的目的；

第二个 MRJob 再根据预处理的数据结果按照 GroupBy Key 分布到 Reduce 中（这个过程可以保证相同的原始 GroupBy Key 被分布到同一个 Reduce 中），最后完成最终的聚合操作。

总结：它使计算变成了两个 mapreduce，先在第一个中在 shuffle 过程 partition 时随机给 key 打标记，使每个 key 随机均匀分布到各个reduce 上计算，但是这样只能完成部分计算，因为相同 key 没有分配到相同 reduce 上。所以需要第二次的 mapreduce,这次就回归正常 shuffle,但是数据分布不均匀的问题在第一次 mapreduce 已经有了很大的改善，因此基本解决数据倾斜。因为大量计算已经在第一次mr 中随机分布到各个节点完成。

1. 控制空值分布

将为空的 key 转变为字符串加随机数或纯随机数，将因空值而造成倾斜的数据分不到多个 Reducer

注：对于异常值如果不需要的话，最好是提前在 where 条件里过滤掉，这样可以使计算量大大减少

实践中，可以使用 case when 对空值赋上随机值。此方法比直接写 is not null 更好，因为前者 job 数为 1，后者为 2

select userid, name from user_info a
join (
select case when userid is null
then
cast (rand(47)* 100000 as int )
else userid end from user_read_log
)b
on a.userid = b.userid

1. 如当有多个 JOIN 的时候，建议建立临时表，然后拆分HIVE SQL 语句

2. MapJoin ：如果不指定 MapJoin 或者不符合 MapJoin 的条件，那么 Hive 解析器会将 Join 操作转换 成 Common Join，即：在 Reduce 阶段完成 join。容易发生数据倾斜。可以用MapJoin把小 表全部加载到内存在 map 端进行 join，避免 reducer 处理

3. 行列过滤：

列处理：在 SELECT 中，只拿需要的列，如果有，尽量使用分区过滤，少用 SELECT *

行处理：在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在 Where 后面，那 么就会先全表关联，之后再过滤

1. 多采用分桶技术及合并大量小文件

4.5.1 分桶表

4.5.1.1 分桶表和分区表的区别

桶的概念就是MapReduce的分区的概念，两者是完全一样的。也就是说物理上每个桶就是目录里的一个文件，一个作业产生的桶（输出文件）数量和reduce任务个数相同

Hive的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中

分桶是将数据集分解成更容易管理的若干部分的一个技术，是比表或分区更为细粒度的数据范围划分。针对某一列进行桶的组织，对列值哈希，然后除以桶的个数求余，决定将该条记录存放到哪个桶中。常用于获得更高的查询处理效率和抽样调查

分区表提供了一个隔离数据和优化查询的便利方式。但是在实际场景下，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive还可以进一步组织成桶，也就是更为细粒度的数据范围划分。即：

1. 分桶对数据的处理比分区更加细粒度化；

2. 分桶和分区两者不干扰，可以把分区表进一步分桶；
3. 分区针对的是数据的存储路径；分桶针对的是数据文件。

hive的桶其实就是MapReduce的分区的概念，两者完全相同。在物理上每个桶就是目录里的一个文件，一个作业产生的桶（输出文件）数量和reduce任务个数相同。

而分区代表了数据的仓库，也就是文件夹目录。每个文件夹下面可以放不同的数据文件。通过文件夹可以查询里面存放的文件。但文件夹本身和数据的内容毫无关系。

桶则是按照数据内容的某个值进行分桶，把一个大文件散列称为一个个小文件。这些小文件可以单独排序。如果另外一个表也按照同样的规则分成了一个个小文件。两个表join的时候，就不必要扫描整个表，只需要匹配相同分桶的数据即可，效率当然大大提升。

同样的道理，在对数据抽样的时候，也不需要扫描整个文件。只需要对每个分区按照相同规则抽取一部分数据即可。

查询效率更快，因为分桶为表加上了额外结构，链接相同列划分了桶的表，可以使用map-side join更加高效；

由于分桶规则保证了数据在不同桶的随机性，因此平时进行抽样调查时取样更加方便。

4.5.1.2 分桶表实际应用案例

1.创建临时表并导入数据

create table temp_buck(id int, name string)
row format delimited fields terminated by '\t';
load data local inpath '/tools/test_buck.txt' into table temp_buck;

---案例
create table buck_table_name (id int,name string)
clustered by (id) sorted by (id asc) into 4 buckets
row format delimited fields terminated by '\t';
--注意：CLUSTERED BY来指定划分桶所用列和划分桶的个数。
--分桶规则：HIVE对key的hash值除bucket个数取余数，保证数据均匀随机分布在所有bucket里。
--SORTED BY对桶中的一个或多个列另外排序

insert into table test_buck select id, name from temp_buck;

3.分桶抽样

函数：tablesample(bucket x out of y)

解析：

y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。

例如，table总共分了6份，当y=2时，抽取(6/2=)3个bucket的数据，当y=8时，抽取(6/8=)3/4个bucket的数据。

x表示从哪个bucket开始抽取，如果需要取多个分区，以后的分区号为当前分区号加上y。

例如，table总bucket数为6，tablesample(bucket 1 out of 2)，表示总共抽取（6/2=）3个bucket的数据，抽取第1(x)个和第3(x+y)个和第5(x+y)个bucket的数据。

注意：x的值必须小于等于y的值

select * from test_buck tablesample(bucket 1 out of 3 on id);

1. 结合实际环境合理设置 Map和Reduce数

2. 中间结果压缩：设置 map 端输出、中间结果压缩。（不完全是解决数据倾斜的问题，但是减少了 IO 读写和网络传输，能提高很多效率）

五.hive数据压缩

HQL语句最终都会转换成为hadoop中的MapReduce job,而MapReduce job可以有对处理数据进行压缩。

压缩格式	对应编码/解码	压缩比	压缩速度	解压缩速度	是否可分割
DEFAULT	org.apache.hadoop.io.compress.DefaultCodec
Gzip	org.apache.hadoop.io.compress.GzipCodec	13.4%	21 MB/s	118 MB/s	否
Bzip2	org.apache.hadoop.io.compress.BzipCodec	13.2%	2.4MB/s	9.5MB/s	是
Snappy	org.apache.hadoop.io.compress.SnappyCodec	22.2%	172 MB/s	409 MB/s	是
Lzo	org.apache.hadoop.io.compress.LzopCodec	20.5%	135 MB/s	410 MB/s	否

通常选用lzo或者snappy压缩方式

5.1 hive中间数据压缩

hive.exec.compress.intermediate：默认为false，设置true为激活中间数据压缩功能，就是MapReduce的shuffle阶段对mapper产生中间压缩，在这个阶段，优先选择一个低CPU开销：

set hive.exec.compress.intermediate=true
set mapred.map.output.compression.codec= org.apache.hadoop.io.compress.SnappyCodec
set mapred.map.output.compression.codec=com.hadoop.compression.lzo.LzoCodec

5.2 hive最终输出结果压缩

hive.exec.compress.output：用户可以对最终生成的Hive表的数据通常也需要压缩。该参数控制这一功能的激活与禁用，设置为true来声明将结果文件进行压缩。

mapred.output.compression.codec：将hive.exec.compress.output参数设置成true后，然后选择一个合适的编解码器，如选择SnappyCodec。设置如下（两种压缩的编写方式是一样的）：

set hive.exec.compress.output=true 
set mapred.output.compression.codec=org.apache.hadoop.io.compress.SnappyCodec
/*
---or
*/
set mapred.output.compress=true
set mapred.output.compression.codec=org.apache.hadoop.io.compress.LzopCodec

在hive中使用压缩需要灵活的方式，如果是数据源的话，采用RCFile+bz或RCFile+gz的方式，这样可以很大程度上节省磁盘空间；而在计算的过程中，为了不影响执行的速度，可以浪费一点磁盘空间，建议采用RCFile+snappy的方式，这样可以整体提升hive的执行速度。至于lzo的方式，也可以在计算过程中使用，只不过综合考虑（速度和压缩比）还是考虑snappy适宜。

六.hive的SerDe序列化

5.2 SerDe概念

Serde是 Serializer/Deserializer的简写。hive使用Serde进行行对象的序列与反序列化。最后实现把文件内容映射到 hive 表中的字段数据类型。

Hive 是如何读数据的流程：

HDFS files –> InputFileFormat –> <key, value> –> Deserializer –> Row object（序列化）

Row object –> Serializer –> <key, value> –> OutputFileFormat –> HDFS files（反序列化）

6.2 hive的SerDe 类型

Hive 中内置org.apache.hadoop.hive.serde2库，内部封装了很多不同的SerDe类型。

hive创建表时，通过自定义的SerDe或使用Hive内置的SerDe类型指定数据的序列化和反序列化方式。

创建表时使用row format 参数说明SerDe的类型。

使用用户自定义的Serde或者native Serde， 如果 ROW FORMAT没有指定或者指定了 ROW FORMAT DELIMITED就会使用native Serde。