文章目录
前言
数据仓库是为了解决大数据分析而产生的数据存储平台,简称数仓:DW(DataWarehouse),面向分析的集成化数据平台,分析结果给公司提供决策依据。
一、数据仓库
1.基本性质
1、不生产数据,即面向分析、非面向事务
2、不消费数据,即数据冗余存储,分析结果对外服务
3、不支持事务,需要先存在数据源,才能存在数仓,如:RDBMS。
4、不是大型数据库,是数据分析平台。
2.特性
1、面向主题性,以分析为目的,以业务主题为导向。
2、集成性,数据源种类多样,格式多样,字段表达方式多样,经ETL集成在数仓中。
3、不可更新性,面向分析,数据越多越好,冗余储存,数据之间规律不可更改。
4、时变性,批处理、流处理,需要持续维护,T+1、T+N模式
3.数仓分层
分层的意义:
基础:问题复杂,数据多
将数据清洗,留下高质量的数据
将数据拆解、汇总,提取有意义的维度、指标
面向分析,简化表关系,关联主题
明确过程,保留中间结果,更好的排错
一般分层结构:
1、ODS(操作型数据层、数据引入层、数据接入层):将数据源的数据汇总储存至分布式存储系统中,保持原数据,分区优化,压缩存储等
2、DW(数据仓库):ETL将数据反复转化,储存大量中间结果,可根据具体业务继续分层
3、DA(面向应用层):最终消费DW数据的各种应用,即分析结果的实际应用体现,如可视化、暴漏接口
阿里数仓三层分层:
https://help.aliyun.com/document_detail/114447.html
分层的好处:
1、清晰数据结构:分层之后的数据结构更清晰,便于提取分析
2、数据血缘追踪:因为有分层,可根据分层找到源数据位置(冗余存储)
3、减少重复开发:分层保留了中间结果,出现问题不必从头再来
4、把复杂问题简单化:每一层只干一件事,细化问题
5、屏蔽原始数据的异常:异常数据在分层中会被剔除
二、HIVE
1.来源、特性
来源:
Hive是Facebook开源出来,后来贡献给力Apache
宗旨是:提高分析数据的能力降低分析数据的开发成本
特性:
基于hadoop的分布式文件储存
基于hadoop的资源调度
基于hadoop的分布式计算(MapReduce)
最大的功能:hive元数据(文件和表之间的关系)管理,将结构化数据映射成表结构,提供类sql查询功能,方便分析
2.hive两代客户端
第一代:hive
第二代:beeline
区别:
第一代不支持jdbc协议只启一个服务metastore
第二代支持jdbc协议启metastore、hiveserver2(! connect jdbc:hive2://localhost:10000)
3.hive表映射语法
row format delimited fields terminated by "\t";
支持的数据类型:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types
4.hive序列化与反序列化
序列化机制:SerDe(Serializer and Deserializer)
读文件:
HDFS files --> InputFileFormat --> <key, value> --> Deserializer --> Row object
1、使用InputFileFormat(默认实现TextInputFormat)读取hdfs上文件,按行读
2、返回<key, value>,其中数据存储在value中
3、使用Deserializer反序列化动作读取value 解析成为对象(Row object),默认为LazysimpleSerDe
写文件:
Row object --> Serializer --> <key, value> --> OutputFileFormat --> HDFS files
1、将对象序列化成<key,value>
2、利用outputfileformat组件上传至hdfs
5.hive基础语法
基础语法格式:
row format delimited
[fields terminated by char] #指定字段分隔符
[collection items terminated by char] #指定集合元素之间的分隔符
[map keys terminated by char] #指定map类型kv之间的分隔符
[lines terminated by char] #指定换行符
默认分割符:\001 ACSII SOH
内部表、外部表(external):
最大区别:外部表删除时不会删除HDFS上的数据。
分区表、分桶表:
分区表:
1、开启非严格模式、动态分区
set hive.exec.dynamic.partition.mode=nonstrict;
2、分区字段不能与表中字段相同
3、分区结果根据表字段distinct个数建对应数量的小文件
4、优化表,分区条件过滤更高效
5、PARTITION BY(date string)
分桶表:
1、开启分桶
set hive.enforce.bucketing=true;
2、分桶字段为表中字段
3、分桶数自己指定,数字类型%N=分桶结果,非数字类型.hashcode%N=分桶结果
4、优化表,减少join笛卡尔集数
5、CLUSTERED BY xxx INTO N BUCKETS
常用语法:
insert into xxx select * from xxx;
insert overwrite xxx select * from xxx;
desc formatted table;
show create table xxx;
desc database extended databasename;
show partitions;
show functions;
加载服务器文件至hive数据库
LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO
TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 …)];
从hive数据库导数据至hdfs:
insert overwrite directory ‘/tmp’ row format delimited fields terminated by ‘,’ stored as orc select * from table limit 2;
从hive数据库导出至服务器:
insert overwrite local directory ‘/root/’ select * from table limit 2;
hive客户端执行程序:
批处理:
./hive -e ‘select * from table’
./hive -f test.sql
位与操作: & A和B按位进行与操作的结果。 与表示两个都为1则结果为1
select 4 & 8 from dual; --4转换二进制:0100 8转换二进制:1000
select 6 & 4 from dual; --4转换二进制:0100 6转换二进制:0110
位或操作: | A和B按位进行或操作的结果 或表示有一个为1则结果为1
select 4 | 8 from dual;
select 6 | 4 from dual;
位异或操作: ^ A和B按位进行异或操作的结果 异或表示两者的值不同,则结果为1
select 4 ^ 8 from dual;
select 6 ^ 4 from dual;
6.hive函数
UDF 、UDAF、UDTF:
UDF:普通函数,一进一出,spilt
UDAF:聚合函数,多进一出,sum
UDTF:表生成函数,一进多出,explode
正则表达式替换函数:regexp_replace(str, regexp, rep)
select regexp_replace(‘100-200’, ‘(\d+)’, ‘num’); --正则分组
正则表达式解析函数:regexp_extract(str, regexp[, idx]) 提取正则匹配到的指定组内容
select regexp_extract(‘100-200’, ‘(\d+)-(\d+)’, 2);
URL解析函数:parse_url 注意要想一次解析出多个 可以使用parse_url_tuple这个UDTF函数
json解析函数:get_json_object(json_txt, path)
,$表示json对象
--获取当前日期: current_date
select current_date();
--获取当前时间戳: current_timestamp
--同一查询中对current_timestamp的所有调用均返回相同的值。
select current_timestamp();
--获取当前UNIX时间戳函数: unix_timestamp
select unix_timestamp();
--日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp("2011-12-07 13:01:03");
--指定格式日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss');
--UNIX时间戳转日期函数: from_unixtime
select from_unixtime(1620723323);
select from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
--日期比较函数: datediff 日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'
select datediff('2012-12-08','2012-05-09');
--日期增加函数: date_add
select date_add('2012-02-28',10);
--日期减少函数: date_sub
select date_sub('2012-01-1',10);
条件函数
--if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
select if(1=2,100,200);
select if(sex ='男','M','W') from student limit 3;
--空判断函数: isnull( a )
select isnull("allen");
select isnull(null);
--非空判断函数: isnotnull ( a )
select isnotnull("allen");
select isnotnull(null);
--空值转换函数: nvl(T value, T default_value)
select nvl("allen","itcast");
select nvl(null,"itcast");
--非空查找函数: COALESCE(T v1, T v2, ...)
--返回参数中的第一个非空值;如果所有值都为NULL,那么返回NULL
select COALESCE(null,11,22,33);
select COALESCE(null,null,null,33);
select COALESCE(null,null,null);
--条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end;
select case sex when '男' then 'male' else 'female' end from student limit 3;
强制转换:
--任意数据类型之间转换:cast
select cast(12.14 as bigint);
select cast(12.14 as string);
select cast("hello" as int);
脱敏函数、加密函数:
--mask
--将查询回的数据,大写字母转换为X,小写字母转换为x,数字转换为n。
select mask("abc123DEF");
select mask("abc123DEF",'-','.','^'); --自定义替换的字母
--mask_first_n(string str[, int n]
--对前n个进行脱敏替换
select mask_first_n("abc123DEF",4);
--mask_last_n(string str[, int n])
select mask_last_n("abc123DEF",4);
--mask_show_first_n(string str[, int n])
--除了前n个字符,其余进行掩码处理
select mask_show_first_n("abc123DEF",4);
--mask_show_last_n(string str[, int n])
select mask_show_last_n("abc123DEF",4);
--mask_hash(string|char|varchar str)
--返回字符串的hash编码。
select mask_hash("abc123DEF");
--取哈希值函数:hash
select hash("allen");
--current_user()、logged_in_user()、current_database()、version()
--SHA-1加密: sha1(string/binary)
select sha1("allen");
--SHA-2家族算法加密:sha2(string/binary, int) (SHA-224, SHA-256, SHA-384, SHA-512)
select sha2("allen",224);
select sha2("allen",512);
--crc32加密:
select crc32("allen");
--MD5加密: md5(string/binary)
select md5("allen");
7.hive高阶函数
1、表生成函数explode
功能:将array或者map集合中的数据“炸开”,生成多行数据
基本用法:
select explode(`array`(11,22,33,44,55));
select explode(`map`("id",10086,"name","allen","age",18));
explode属于UDTF表生成函数,其结果为多行,不能直接在select字句中出现,需要借助侧视图join
侧视图的原理:
将UDTF的结果构建成一个类似于视图的表,然后将原表中的每一行和UDTF函数输出的每一行进行连接,生成一张新的虚拟表
--lateral view侧视图基本语法如下
select …… from tabelA lateral view UDTF(xxx) 别名 as col1,col2,col3……;
因为explode只能接收array和map类型,可用collect_list()和collect_tuple()收集字段中的数据,利用concat_ws()将结果拼接。
2、行列转换
--多行转多列
select
col1 as col1,
max(case col2 when 'c' then col3 else 0 end) as c,
max(case col2 when 'd' then col3 else 0 end) as d,
max(case col2 when 'e' then col3 else 0 end) as e
from row2col1
group by col1;
--多列转单行
select
col1,
col2,
concat_ws("-",collect_list(cast(col3 as string)))
from row2col2
group by col1,col2;
--多列转多行union去重
select 'b','a','c'
union
select 'a','b','c'
union
select 'a','b','c';
--单列转多行
select
col1,
col2,
lv.col3 as col3
from col2row2
lateral view explode(split(col3, ',')) lv as col3;
3、join解析函数
--get_json_object用于解析单个对象
--获取设备名称
get_json_object(json,"$.device") as device,
--获取设备类型
get_json_object(json,"$.deviceType") as deviceType,
--获取设备信号强度
get_json_object(json,"$.signal") as signal,
--获取时间
get_json_object(json,"$.time") as stime
--json_tuple用于解析全部对象
json_tuple(json,"device","deviceType","signal","time") as (device,deviceType,signal,stime)
--可配合侧视图lateral view使用
--使用JsonSerDe类解析,在加载json数据到表中的时候完成解析动作
4、窗口函数
用法与mysql大致相同,需要注意的是:
有PARTITION BY 没有PARTITION BY的区别;有ORDER BY没有ORDER BY的区别:
1、有没有partition by 影响的是全局聚合 还是分组之后 每个组内聚合
2、没有order by,默认是rows between,首行到最后行,这里的"行"是物理上的行;
3、有order by,默认是range between,首行到当前行,这里的"行"是逻辑上的行,由字段的值的区间range来划分范围。
可用自定义窗口。
--语法
(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
三、HIVE通用调优
1、orc存储和snappy压缩
hive默认存储为textfile按行读取,不利于分析,且文件大小未改变。可利用orc存储,按列读取,利于分析,减少文件空间。
stored as ‘orc’ tblproperties(“orc.compress”=“SNAPPY”)
snappy为综合使用较好的压缩算法。hive默认使用ZLIB
本质上,还是mapreduce的压缩,所以该慢的还慢,一些参数:
--设置Hive的中间压缩 也就是map的输出压缩
1)开启 hive 中间传输数据压缩功能
set hive.exec.compress.intermediate=true;
2)开启 mapreduce 中 map 输出压缩功能
set mapreduce.map.output.compress=true;
3)设置 mapreduce 中 map 输出数据的压缩方式
set mapreduce.map.output.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;
--设置Hive的最终输出压缩,也就是Reduce输出压缩
1)开启 hive 最终输出数据压缩功能
set hive.exec.compress.output=true;
2)开启 mapreduce 最终输出数据压缩
set mapreduce.output.fileoutputformat.compress=true;
3)设置 mapreduce 最终数据输出压缩方式
set mapreduce.output.fileoutputformat.compress.codec =org.apache.hadoop.io.compress.SnappyCodec;
4)设置 mapreduce 最终数据输出压缩为块压缩 还可以指定RECORD
set mapreduce.output.fileoutputformat.compress.type=BLOCK;
2、fetch抓取与MR本地模式
1、fetch不走mr,直接读取hdfs文件
2、mr本地,不走分布式,不让yarn分配调度任务
set hive.exec.mode.local.auto = true;
开启后尝试本地,不行就走yarn
关键点:计算引擎的问题,追求速度,不用mr,用tez,spark
3、join优化
1、mapjoin,小表join大表,小表join小表,实现方式就是小表全部数据进内存缓存中,和大表切片join。
2、reducejoin,适用于大表join大表
3、join之前先过滤,大表转小表;利用分区裁剪
4、bucketjoin,适用大表join大表
分桶字段和join相同Bucktet Map Join,分桶的个数相等或者成倍数。
SMB分桶之后排序,分桶字段 = Join字段 = 排序字段,分桶的个数相等或者成倍数
核心是在maptask阶段完成join
3、数据倾斜优化
出现原因:
group by导致数据倾斜
join导致数据倾斜
优化:
group by:
1、开启Map端聚合,先做一部分
2、实现随机分区select * from table distribute by rand();
3、自动负载均衡
两阶段,一阶段,每个reducer随机拉取做部分聚合
第二个MapReduce将上一步聚合的结果再按照业务(group by key)进行处理,保证相同的分布到一起,最终聚合得到结果
join数据倾斜:
1、提前过滤,将大数据变成小数据,实现Map Join
2、使用Bucket Join
3、使用Skew Join
将Map Join和Reduce Join进行合并,如果某个值出现了数据倾斜,就会将产生数据倾斜的数据单独使用Map Join来实现
其他没有产生数据倾斜的数据由Reduce Join来实现,这样就避免了Reduce Join中产生数据倾斜的问题
最终将Map Join的结果和Reduce Join的结果进行Union合并
4、其他优化
1、MR程序task个数调整
maptask个数:
调整maptask和reducetask任务数。maptask通过调整逻辑切片大小、文件数量。小文件合并、block size。
reducetask个数:
(1)每个 Reduce 处理的数据量默认是 256MB
hive.exec.reducers.bytes.per.reducer=256000000
(2)每个任务最大的 reduce 数,默认为 1009
hive.exec.reducsers.max=1009
(3)mapreduce.job.reduces
该值默认为-1,由 hive 自己根据任务情况进行判断。不适用order by全局排序
2、执行计划
explain + sql语句,查看hive如何执行,适当调优
3、并行机制、推测执行机制
并行机制:
如果hivesql的底层某些stage阶段可以并行执行,就可以提高执行效率。
前提是stage之间没有依赖 并行的弊端是瞬时服务器压力变大。
推测执行机制:
一个job底层可能有多个task执行,如果某些拖后腿的task执行慢,可能会导致最终job失败。
所谓的推测执行机制就是通过算法找出拖后腿的task,为其启动备份的task。
两个task同时处理一份数据,谁先处理完,谁的结果作为最终结果。
总结
MR优点稳定,但也导致了繁琐的shuffle过程。hive提高了分析效率,可以直接用类sql完成数据的分析,但没有自己的计算引擎、资源调度,过分的依赖导致运行速率不可直视。
时光如水,人生逆旅矣。