HIVE

一、数据倾斜优化或避免

1、数据倾斜产生的原因：

①数据业务本身的特性，如某作为key值的字段重复较多

②join时，大表与小表关联小表字段作为key值

③join时，大表与大表关联，但是很多数据没关联上，导致产生了空值

④count(distinct) 后导致数据倾斜

⑤group by 时因为分区不合理导致数据倾斜，如group by 的字段某类值过多导致数据倾斜

2、数据倾斜的表现

而当其中每一组的数据量过大时，会出现其他组的计算已经完成而这里还没计算完成，其他节点的一直等待这个节点的任务执行完成，所以会看到一直map 100% reduce 99%的情况，大概率是发生了数据倾斜。

3、各场景hive优化方案

3.1map端处理的小文件过多

要合并小文件，可以通过set hive.merge.mapfiles=true来解决。

3.2map端数据量大，需要扩充map的数量或reduce的数量

set mapred.map.tasks个数，set mapred.reduce.tasks个数

3.3大小表join

可以使用map join让小表先进内存

设置 hive.mapjoin.smalltable.filesize = 25000000（25M，默认）

3.4大表与大表join

为了避免产生大量空值而导致数据倾斜，可以把空值设置为字符串＋随机数：concate(字符串＋rand())

3.5避免用作join的字段数据类型一定要相同

二、HIVE分区与分桶

1、什么是分区

分区的目的就是提高查询效率，查询分区数据的方式就是指定分区名，指定分区名之后就不再全表扫描，直接从指定分区中查询，从hdfs的角度看就是从相应的文件系统中你指定的分区名会生成一个单独的文件，去这个指定文件中查找特定的数据

这个分区字段形式上存在于数据表中，在查询时会显示到客户端上，但并不真正在存储在数据表文件中，是所谓伪列。所以，千万不要以为是对属性表中真正存在的列按照属性值的异同进行分区。比如上面的分区依据的列name并不真正的存在于数据表中，是我们为了方便管理添加的一个伪列，这个列的值也是我们人为规定的

2、什么是分桶

分桶是相对分区进行更细粒度的划分。分桶将整个数据内容安装某列属性值得hash值进行区分，如要安装name属性分为3个桶，就是对name属性值的hash值对3取摸，按照取模结果对数据分桶。

与分区不同的是，分区依据的不是真实数据表文件中的列，而是我们指定的伪列，但是分桶是依据数据表中真实的列而不是伪列。所以在指定分区依据的列的时候要指定列的类型，因为在数据表文件中不存在这个列，相当于新建一个列。而分桶依据的是表中已经存在的列，这个列的数据类型显然是已知的，所以不需要指定列的类型。

3、为什么要进行分桶

可提高查询效率，如：我们要对两张在同一列上进行了分桶操作的表进行JOIN操作的时候，只需要对保存相同列值的桶进行JOIN操作即可。同时分桶也能让取样（Sampling）更高效。

三、内部表与外部表

未被external修饰的是内部表（managed table），被external修饰的为外部表（external table）；
区别：
内部表数据由Hive自身管理，外部表数据由HDFS管理；
内部表数据存储的位置是hive.metastore.warehouse.dir（默认：/user/hive/warehouse），外部表数据的存储位置由自己制定（如果没有LOCATION，Hive将在HDFS上的/user/hive/warehouse文件夹下以外部表的表名创建一个文件夹，并将属于这个表的数据存放在这里）；
删除内部表会直接删除元数据（metadata）及存储数据；删除外部表仅仅会删除元数据，HDFS上的文件并不会被删除，且删除后如果重新create相同的外部表数据不会变；

四、Hive 的函数：UDF、UDAF、UDTF 的区别

1、区别

UDF: 单行进入，单行输出
UDAF: 多行进入，单行输出
UDTF: 单行输入，多行输出

2、UDTF函数

①对array类型数据

hive> create table student_array(
    > name string,
    > course_score array<string>
    > )
    > row format delimited fields terminated by '\t'
    > collection items terminated by ','
    > stored as textfile;
hive> select * from student_array;
-chgrp: '*' does not match expected pattern for group
Usage: hadoop fs [generic options] -chgrp [-R] GROUP PATH...
OK
zs	["语文:86","数学:87.5","英语:90"]
ls	["语文:76","数学:93","英语:88"]
ww	["语文:88","数学:90","英语:95"]

对array类型的数据可以用explode函数将数组中的元素拆分，按行输出每个元素

hive> select explode(course_score) from student_array;
-chgrp: '*' does not match expected pattern for group
Usage: hadoop fs [generic options] -chgrp [-R] GROUP PATH...
OK
语文:86
数学:87.5
英语:90
语文:76
数学:93
英语:88
语文:88
数学:90
英语:95
select split(course_score,':')[1] from student_array

②对map类型的数据

hive> create table student_map(
    > name string,
    > course_score map<string ,float>
    > )
    > row format delimited fields terminated by '\t'
    > collection items terminated by ','
    > map keys terminated by ':'
    > stored as textfile;
hive> select * from student_map;
-chgrp: '*' does not match expected pattern for group
Usage: hadoop fs [generic options] -chgrp [-R] GROUP PATH...
OK
zs	{"语文":86.0,"数学":87.5,"英语":90.0}
ls	{"语文":76.0,"数学":93.0,"英语":88.0}
ww	{"语文":88.0,"数学":90.0,"英语":95.0}

对于map类型的数据，可以使用explode分开key和value类型的数据

hive> select explode(course_score) from student_map;
-chgrp: '*' does not match expected pattern for group
Usage: hadoop fs [generic options] -chgrp [-R] GROUP PATH...
OK
语文	86.0
数学	87.5
英语	90.0
语文	76.0
数学	93.0
英语	88.0
语文	88.0
数学	90.0
英语	95.0

③对于结构体类型的数据（map和array的集合）

hive> create table student_map(
    > name string,
    > course_score array<map<string ,float>>
    > )
    > row format delimited fields terminated by '\t'
    > collection items terminated by ','
    > map keys terminated by ':'
    > stored as textfile;
hive> select * from student_struct_array;
-chgrp: '*' does not match expected pattern for group
Usage: hadoop fs [generic options] -chgrp [-R] GROUP PATH...
OK
zs	[{"course":"语文","score":86.0},{"course":"数学","score":87.5},{"course":"英语","score":90.0}]
ls	[{"course":"语文","score":76.0},{"course":"数学","score":93.0}]
ww	[{"course":"语文","score":88.0},{"course":"数学","score":90.0},{"course":"英语","score":95.0}]
Time taken: 0.19 seconds, Fetched: 3 row(s)

可以使用inline函数进行拆分，注：不能使用UDTF函数进行嵌套

hive> select inline(course_score) from student_struct_array;
-chgrp: '*' does not match expected pattern for group
Usage: hadoop fs [generic options] -chgrp [-R] GROUP PATH...
OK
语文	86.0
数学	87.5
英语	90.0
语文	76.0
数学	93.0
语文	88.0
数学	90.0
英语	95.0

3、UDAF函数

①count，返回值为bigint

count（*）：返回指定列检索到的行数，包括空值

count（expr）：expr表达式不是NULL的行的数量

count(distinct) ：返回去除重复后的行数

②sum，min，max，avg

常用函数，不予详细介绍

③返回值类型为array的函数

select filter_name ,
	collect_list(path_id),
	collect_set(path_id),
	concat_ws('@',collect_list(path_id)) a,
	concat_ws('@',collect_set(path_id)) b,
	concat_ws('@',collect_set(market_type)) c
from FDM_SOR.T_FIBA_MULTI_UBA_CFG_PATH_DETAIL_D 
where path_id >89
group by filter_name

4、UDF函数

①数学函数

函数名称	说明
round(double d, int n)	返回保留n位小数的近似d值
bin(int d)	计算二进制值d的string值
rand(int seed)	返回随机数,seed是随机因子
ceil(double d)	返回大于d的最小整值
floor(double d)	返回小于d的最大整值

②日期函数

函数名称	说明
to_date(string timestamp)	返回时间字符串中的日期部分,如to_date(‘1970-01-01 00:00:00’)=‘1970-01-01’
current_date	返回当前日期
year(date)	返回日期date的年,类型为int如year(‘2019-01-01’)=2019
month(date)	返回日期date的月,类型为int,如month(‘2019-01-01’)=1
day(date)	返回日期date的天,类型为int,如day(‘2019-01-01’)=1
weekofyear(date1)	返回日期date1位于该年第几周。如weekofyear(‘2019-03-06’)=10
datediff(date1,date2)	返回日期date1与date2相差的天数，如datediff(‘2019-03-06’,‘2019-03-05’)=1
date_add(date1,int1)	返回日期date1加上int1的日期,如date_add(‘2019-03-06’,1)=‘2019-03-07’
date_sub(date1,int1)	返回日期date1减去int1的日期,如date_sub(‘2019-03-06’,1)=‘2019-03-05’
months_between(date1,date2)	返回date1与date2相差月份,如months_between(‘2019-03-06’,‘2019-01-01’)=2
add_months(date1,int1)	返回date1加上int1个月的日期，int1可为负数。如add_months(‘2019-02-11’,-1)=‘2019-01-11’
last_day(date1)	返回date1所在月份最后一天。如last_day(‘2019-02-01’)=‘2019-02-28’
next_day(date1,day1)	返回日期date1的下个星期day1的日期。day1为星期X的英文前两字母如next_day(‘2019-03-06’,‘MO’) 返回’2019-03-11’
trunc(date1,string1)	返回日期最开始年份或月份。string1可为年(YYYY/YY/YEAR)或月(MONTH/MON/MM)。如trunc(‘2019-03-06’,‘MM’)=‘2019-03-01’，trunc(‘2019-03-06’,‘YYYY’)=‘2019-01-01’
unix_timestamp()	返回当前时间的unix时间戳，可指定日期格式。如unix_timestamp(‘2019-03-06’,‘yyyy-mm-dd’)=1546704180
from_unixtime()	返回unix时间戳的日期，可指定格式。如select from_unixtime(unix_timestamp(‘2019-03-06’,‘yyyy-mm-dd’),‘yyyymmdd’)=‘20190306’

③条件函数

函数名称	说明
if(boolean,t1,t2)	若布尔值成立，则返回t1,反正返回t2。如if(1>2,100,200)返回200
case when boolean then t1 else t2 end	若布尔值成立，则t1,否则t2,可加多重判断
coalesce(v0,v1,v2)	返回参数中的第一个非空值,若所有值均为null,则返回null。如coalesce(null,1,2)返回1
isnull(a)	若a为null则返回true，否则返回false

④字符串函数

函数名称	说明
length(string1)	返回字符串长度
concat(string1,string2)	返回拼接string1及string2后的字符串
concat_ws(sep,string1,string2)	返回按指定分隔符拼接的字符串
lower(string1)	返回小写字符串
upper(string1)	返回大写字符串
trim(string1)	去字符串左右空格
split(string1,pat1)	以pat1字符分隔字符串string1,返回数组。如split(‘a,b,c’,’,’)返回[“a”,“b”,“c”]
substr(string1,index1,int1)	以index位置起截取int1个字符。如substr(‘abcde’,1,2)返回’ab’

五、HIVE数据仓库

1、数据仓库搭建步骤

①用户行为数据采集平台搭建
②业务数据采集平台搭建
③数据仓库维度建模
④采用即席查询工具，随时进行指标分析
⑤对集群性能进行监控，发生异常需要报警

2、技术选型

数据采集传输:Flume，Kafka，Sqoop
数据存储: MySql，HDFS
数据计算:Hive，Spark
数据查询: Presto，Kylin
数据可视化:Superset
任务调度: Azkaban
集群监控: Zabbix

3、数据流图

4、数据分层

ODS（原始数据层）：原始数据层，存放原始数据，直接加载原始日志、数据，数据保持原貌不做处理。

DWD层（明细数据层）:对ODS层数据进行清洗(去除空值，脏数据，超过极限范围的数据)、脱敏等。保存明细数据，一行信息代表一次业务行为， 例如一次下单。

DWS层（服务数据层）：以DWD为基础，按天进行轻度汇总。一行信息代表一个主题对象一天的汇总行为， 例如一个用户一天下单次数

DWT层（数据主题层）：以DWS为基础，对数据进行累积汇总。一行信息代表一个主题对象的累积行为，例如一个用户从注册那天开始至今-共下了多少次单

ADS层（数据应用层）：为各种统计报表提供数据

ODS层是原始数据层也可被称为接口层

中间三层也被称为数据仓库层，每一层都是对上一层的累积和汇总。一般都是对数据关联的日期进行拆分，使得其更具体的分类，如拆分成年、月、日等，

5、数据分层的优点

➢1)把复杂问题简单化 将复杂的任务分解成多层来完成，每一层只处理简单的任务，方便定位问题。

➢2) 减少重复开发 规范数据分层，通过的中间层数据，能够减少极大的重复计算，增加一次计算结果的复用性。

➢3) 隔离原始数据 不论是数据的异常还是数据的敏感性，使真实数据与统计数据解耦开。

6、维度建模

①事实表和维度表

**事实表（Fact Table）**是指存储有事实记录的表，如系统的日志、销售记录、用户访问日志等信息，事实表的记录是动态的增长的，所以体积是大于维度表。

如：用户访问日志（事实表）：用户名、url、时间…

维度表（Dimension Table）也称为查找表（Lookup Table）是与事实表相对应的表，这个表保存了维度的属性值，可以跟事实表做关联，相当于是将事实表中经常重复的数据抽取、规范出来用一张表管理，常见的有日期（日、周、月、季度等属性）、地区表等，所以维度表的变化通常不会太大。

常见的维度表有：地市维表、区县维表、资费维表、活动维表、渠道维表等