数仓复习笔记<三>

517 阅读14分钟

其他更多java基础文章:
java基础学习(目录)


数仓项目地址:尚硅谷大数据电商数仓V3.0
数仓项目脚本:大数据项目之电商数仓(脚本篇)

即席查询Presto

Presto概念

image.png

Presto架构

image.png

Presto优缺点

image.png

blog.csdn.net/u012551524/…

Impala性能稍领先于Presto,但是Presto在数据源支持上非常丰富,包括Hive、图数据库、传统关系型数据库、Redis等。

Presto优化之数据存储

合理设置分区

与Hive类似,Presto会根据元数据信息读取分区数据,合理的分区能减少Presto数据读取量,提升查询性能。

使用列式存储

Presto对ORC文件读取做了特定优化,因此在Hive中创建Presto使用的表时,建议采用ORC格式存储。相对于Parquet,Presto对ORC支持更好。

使用压缩

数据压缩可以减少节点间数据传输对IO带宽压力,对于即席查询需要快速解压,建议采用Snappy压缩。

Presto优化之查询SQL

只选择使用的字段

由于采用列式存储,选择需要的字段可加快字段的读取、减少数据量。避免采用*读取所有字段。

[GOOD]: SELECT time, user, host FROM tbl

[BAD]:  SELECT * FROM tbl

过滤条件必须加上分区字段

对于有分区的表,where语句中优先使用分区字段进行过滤。acct_day是分区字段,visit_time是具体访问时间。

[GOOD]: SELECT time, user, host FROM tbl where acct_day=20171101

[BAD]:  SELECT * FROM tbl where visit_time=20171101

Group By语句优化

合理安排Group by语句中字段顺序对性能有一定提升。将Group By语句中字段按照每个字段distinct数据多少进行降序排列。distinct数据多的排在前面

[GOOD]: SELECT GROUP BY uid, gender

[BAD]:  SELECT GROUP BY gender, uid

Order by时使用Limit

Order by需要扫描数据到单个worker节点进行排序,导致单个worker需要大量内存。如果是查询Top N或者Bottom N,使用limit可减少排序计算和内存压力。

[GOOD]: SELECT * FROM tbl ORDER BY time LIMIT 100

[BAD]:  SELECT * FROM tbl ORDER BY time

使用Join语句时将大表放在左边

Presto中join的默认算法是broadcast join,即将join左边的表分割到多个worker,然后将join右边的表数据整个复制一份发送到每个worker进行计算。如果右边的表数据量太大,则可能会报内存溢出错误。

[GOOD] SELECT ... FROM large_table l join small_table s on l.id = s.id
[BAD] SELECT ... FROM small_table s join large_table l on l.id = s.id

注意事项

字段名引用

避免和关键字冲突:MySQL对字段加反引号`、Presto对字段加双引号分割 当然,如果字段名称不是关键字,可以不加这个双引号。

时间函数

对于Timestamp,需要进行比较的时候,需要添加Timestamp关键字,而MySQL中对Timestamp可以直接进行比较。

/*MySQL的写法*/
SELECT t FROM a WHERE t > '2017-01-01 00:00:00'; 

/*Presto中的写法*/
SELECT t FROM a WHERE t > timestamp '2017-01-01 00:00:00';

不支持INSERT OVERWRITE语法

Presto中不支持insert overwrite语法,只能先delete,然后insert into。

PARQUET格式

Presto目前支持Parquet格式,支持查询,但不支持insert。

即席查询Kylin

Kylin 定义

Apache Kylin是一个开源的分布式分析引擎,提供Hadoop/Spark之上的SQL查询接口及多维分析(OLAP)能力以支持超大规模数据,最初由eBay Inc开发并贡献至开源社区。它能在亚秒内查询巨大的Hive表。

Kylin架构

image.png

  • REST Server REST Server是一套面向应用程序开发的入口点,旨在实现针对Kylin平台的应用开发工作。 此类应用程序可以提供查询、获取结果、触发cube构建任务、获取元数据以及获取用户权限等等。另外可以通过Restful接口实现SQL查询。

  • 查询引擎(Query Engine) 当cube准备就绪后,查询引擎就能够获取并解析用户查询。它随后会与系统中的其它组件进行交互,从而向用户返回对应的结果。 

  • 路由器(Routing) 在最初设计时曾考虑过将Kylin不能执行的查询引导去Hive中继续执行,但在实践后发现Hive与Kylin的速度差异过大,导致用户无法对查询的速度有一致的期望,很可能大多数查询几秒内就返回结果了,而有些查询则要等几分钟到几十分钟,因此体验非常糟糕。最后这个路由功能在发行版中默认关闭。

  • 元数据管理工具(Metadata) Kylin是一款元数据驱动型应用程序。元数据管理工具是一大关键性组件,用于对保存在Kylin当中的所有元数据进行管理,其中包括最为重要的cube元数据。其它全部组件的正常运作都需以元数据管理工具为基础。 Kylin的元数据存储在hbase中。 

  • 任务引擎(Cube Build Engine) 这套引擎的设计目的在于处理所有离线任务,其中包括shell脚本、Java API以及Map Reduce任务等等。任务引擎对Kylin当中的全部任务加以管理与协调,从而确保每一项任务都能得到切实执行并解决其间出现的故障。

Kylin特点

Kylin的主要特点包括支持SQL接口、支持超大规模数据集、亚秒级响应、可伸缩性、高吞吐率、BI工具集成等。

  1. 标准SQL接口:Kylin是以标准的SQL作为对外服务的接口。
  2. 支持超大数据集:Kylin对于大数据的支撑能力可能是目前所有技术中最为领先的。早在2015年eBay的生产环境中就能支持百亿记录的秒级查询,之后在移动的应用场景中又有了千亿记录秒级查询的案例。
  3. 亚秒级响应:Kylin拥有优异的查询相应速度,这点得益于预计算,很多复杂的计算,比如连接、聚合,在离线的预计算过程中就已经完成,这大大降低了查询时刻所需的计算量,提高了响应速度。
  4. 可伸缩性和高吞吐率:单节点Kylin可实现每秒70个查询,还可以搭建Kylin的集群。
  5. BI工具集成 Kylin可以与现有的BI工具集成,具体包括如下内容。
    ODBC:与Tableau、Excel、PowerBI等工具集成
    JDBC:与Saiku、BIRT等Java工具集成
    RestAPI:与JavaScript、Web网页集成
    Kylin开发团队还贡献了Zepplin的插件,也可以使用Zepplin来访问Kylin服务。

使用进阶

全量维度表及拉链维度表重复Key问题

错误原因是model中的维度表dwd_dim_user_info_his为拉链表、dwd_dim_sku_info为每日全量表,故使用整张表作为维度表,必然会出现订单表中同一个user_id或者sku_id对应多条数据的问题,针对上述问题,有以下两种解决方案。

  • 方案一:在hive中创建维度表的临时表,该临时表中只存放维度表最新的一份完整的数据,在kylin中创建模型时选择该临时表作为维度表。
  • 方案二:与方案一思路相同,但不使用物理临时表,而选用视图(view)实现相同的功能。

此处采用方案二:

--拉链维度表视图
create view dwd_dim_user_info_his_view as select * from dwd_dim_user_info_his where end_date='9999-99-99';

--全量维度表视图
create view dwd_dim_sku_info_view as select * from dwd_dim_sku_info where dt=date_add(current_date,-1);

--首日情形我们先创建一个2020-03-10的视图
create view dwd_dim_sku_info_view as select * from dwd_dim_sku_info where dt='2020-03-10';

如何实现每日自动构建cube

Kylin提供了Restful API,因次我们可以将构建cube的命令写到脚本中,将脚本交给azkaban或者oozie这样的调度工具,以实现定时调度的功能。 脚本如下:

#!/bin/bash
cube_name=order_cube
do_date=`date -d '-1 day' +%F`

#获取00:00时间戳
start_date_unix=`date -d "$do_date 08:00:00" +%s`
start_date=$(($start_date_unix*1000))

#获取24:00的时间戳
stop_date=$(($start_date+86400000))

curl -X PUT -H "Authorization: Basic QURNSU46S1lMSU4=" -H 'Content-Type: application/json' -d '{"startTime":'$start_date', "endTime":'$stop_date', "buildType":"BUILD"}' http://hadoop102:7070/kylin/api/cubes/$cube_name/build

Kylin Cube构建原理

image.png 每个角度都是Cuboid,所有角度合起来叫Cube

度和度量

维度:即观察数据的角度。 比如员工数据,可以从性别角度来分析,也可以更加细化,从入职时间或者地区的维度来观察。维度是一组离散的值,比如说性别中的男和女,或者时间维度上的每一个独立的日期。因此在统计时可以将维度值相同的记录聚合在一起,然后应用聚合函数做累加、平均、最大和最小值等聚合计算。

度量:即被聚合(观察)的统计值,也就是聚合运算的结果。 比如说员工数据中不同性别员工的人数,又或者说在同一年入职的员工有多少。

Cube和Cuboid

有了维度跟度量,一个数据表或者数据模型上的所有字段就可以分类了,它们要么是维度,要么是度量(可以被聚合)。于是就有了根据维度和度量做预计算的Cube理论。 给定一个数据模型,我们可以对其上的所有维度进行聚合,对于N个维度来说,组合的所有可能性共有2^n种。对于每一种维度的组合,将度量值做聚合计算,然后将结果保存为一个物化视图,称为Cuboid。所有维度组合的Cuboid作为一个整体,称为Cube。

下面举一个简单的例子说明,假设有一个电商的销售数据集,其中维度包括时间[time]、商品[item]、地区[location]和供应商[supplier],度量为销售额。 image.png

  • 维度(1D)的组合有:[time]、[item]、[location]和[supplier]4种;
  • 二维度(2D)的组合有:[time, item]、[time, location]、[time, supplier]、[item, location]、[item, supplier]、[location, supplier]3种;
  • 三维度(3D)的组合也有4种;
  • 最后还有零维度(0D)和四维度(4D)各有一种,总共16种。0维度有时也不会算上,那就是15种。 注意:每一种维度组合就是一个Cuboid,16个Cuboid整体就是一个Cube。

Cube存储原理

image.png

Cuboid id由前面维度是否存在表示,存在为1,不存在为0。维度值则表示存在Cuboid中的维度的对应值。比如第三行101+10,表示customer_address和order_data维度存在,其中customer_address维度的值是1,也就是对应是上海,order_data的值为0,对应的2019-01-09。

Cube构建算法

逐层构建算法(layer)

我们知道,一个N维的Cube,是由1个N维子立方体、N个(N-1)维子立方体、N*(N-1)/2个(N-2)维子立方体、......、N个1维子立方体和1个0维子立方体构成,总共有2^N个子立方体组成,在逐层算法中,按维度数逐层减少来计算,每个层级的计算(除了第一层,它是从原始数据聚合而来),是基于它上一层级的结果来计算的。 比如,[Group by A, B]的结果,可以基于[Group by A, B, C]的结果,通过去掉C后聚合得来的;这样可以减少重复计算;当 0维度Cuboid计算出来的时候,整个Cube的计算也就完成了。
每一轮的计算都是一个MapReduce任务,且串行执行;一个N维的Cube,至少需要N次MapReduce Job。

image.png

算法优点:

  • 此算法充分利用了MapReduce的优点,处理了中间复杂的排序和shuffle工作,故而算法代码清晰简单,易于维护;
  • 受益于Hadoop的日趋成熟,此算法非常稳定,即便是集群资源紧张时,也能保证最终能够完成。

算法缺点:

  • 当Cube有比较多维度的时候,所需要的MapReduce任务也相应增加;由于Hadoop的任务调度需要耗费额外资源,特别是集群较庞大的时候,反复递交任务造成的额外开销会相当可观;
  • 由于Mapper逻辑中并未进行聚合操作,所以每轮MR的shuffle工作量都很大,导致效率低下。
  • 对HDFS的读写操作较多:由于每一层计算的输出会用做下一层计算的输入,这些Key-Value需要写到HDFS上;当所有计算都完成后,Kylin还需要额外的一轮任务将这些文件转成HBase的HFile格式,以导入到HBase中去;

总体而言,该算法的效率较低,尤其是当Cube维度数较大的时候。

快速构建算法(inmem)

也被称作“逐段”(By Segment) 或“逐块”(By Split) 算法,从1.5.x开始引入该算法,该算法的主要思想是,每个Mapper将其所分配到的数据块,计算成一个完整的小Cube 段(包含所有Cuboid)。每个Mapper将计算完的Cube段输出给Reducer做合并,生成大Cube,也就是最终结果。如图所示解释了此流程。

image.png

与旧算法相比,快速算法主要有两点不同:

  • Mapper会利用内存做预聚合,算出所有组合;Mapper输出的每个Key都是不同的,这样会减少输出到Hadoop MapReduce的数据量,Combiner也不再需要;
  • 一轮MapReduce便会完成所有层次的计算,减少Hadoop任务的调配。

Kylin Cube构建优化

使用聚合组(Aggregation group)

聚合组(Aggregation Group)是一种强大的剪枝工具。 对于每个分组内部的维度,用户可以使用如下三种可选的方式定义,它们之间的关系,具体如下。

强制维度(Mandatory)

如果一个维度被定义为强制维度,那么这个分组产生的所有Cuboid中每一个Cuboid都会包含该维度。每个分组中都可以有0个、1个或多个强制维度。如果根据这个分组的业务逻辑,则相关的查询一定会在过滤条件或分组条件中,因此可以在该分组中把该维度设置为强制维度。

image.png

层级维度(Hierarchy)

每个层级包含两个或更多个维度。假设一个层级中包含D1,D2…Dn这n个维度,那么在该分组产生的任何Cuboid中, 这n个维度只会以(),(D1),(D1,D2)…(D1,D2…Dn)这n+1种形式中的一种出现。每个分组中可以有0个、1个或多个层级,不同的层级之间不应当有共享的维度。如果根据这个分组的业务逻辑,则多个维度直接存在层级关系,因此可以在该分组中把这些维度设置为层级维度。

image.png

联合维度(Joint)

每个联合中包含两个或更多个维度,如果某些列形成一个联合,那么在该分组产生的任何Cuboid中,这些联合维度要么一起出现,要么都不出现。每个分组中可以有0个或多个联合,但是不同的联合之间不应当有共享的维度(否则它们可以合并成一个联合)。如果根据这个分组的业务逻辑,多个维度在查询中总是同时出现,则可以在该分组中把这些维度设置为联合维度。

image.png

Row Key优化

Kylin会把所有的维度按照顺序组合成一个完整的Rowkey,并且按照这个Rowkey升序排列Cuboid中所有的行。 设计良好的Rowkey将更有效地完成数据的查询过滤和定位,减少IO次数,提高查询速度,维度在rowkey中的次序,对查询性能有显著的影响。 Row key的设计原则如下:

被用作过滤的维度放在前边

image.png

基数大的维度放在基数小的维度前边。

这里的基数是指distinct数量的大小,比如下图的维度C有6种分类,维度D有3种分类,那C就在D的前面 image.png

并发粒度优化

当Segment中某一个Cuboid的大小超出一定的阈值时,系统会将该Cuboid的数据分片到多个分区中,以实现Cuboid数据读取的并行化,从而优化Cube的查询速度。具体的实现方式如下:构建引擎根据Segment估计的大小,以及参数kylin.hbase.region.cut的设置决定Segment在存储引擎中总共需要几个分区来存储,如果存储引擎是HBase,那么分区的数量就对应于HBase中的Region数量。

kylin.hbase.region.cut的默认值是5.0,单位是GB,也就是说对于一个大小估计是50GB的Segment,构建引擎会给它分配10个分区。用户还可以通过设置kylin.hbase.region.count.min(默认为1)和kylin.hbase.region.count.max(默认为500)两个配置来决定每个Segment最少或最多被划分成多少个分区。