【dwd022】数据仓库dwd层，DWB层构建实践，Presto计算引擎，preview_220616

方法1:拉链导入

对场景有效：增量和更新同步表设计要求：start_date开始时间、end_date结束时间start_date表示数据有效开始时间可以用作表的分区字段。end_date表示数据到期时间。默认数据为9999-99。有更新时，通过拉链表操作修改end_date。代表：fact_shop_order订单表、fact_order_settle订单结算表等

方法2:获取全部复盖

适合场景：无论是否存在历史数据，每次导入时都直接复盖表设计要求。没有要求，没有隔板，没有拉链。代表性代表：dim_district领域字典表、dim_date时间维表

方法3:增量导入

适合方案：仅考虑每个增量数据同步表设计要求。分区表按(dt string)，分区字段通常是时间日期。一天一个分区，一次增量导入一个分区。代表：fact_goods_evaluation订单评估表、fact_user_login登录记录表。

知识点17:构建DWD层-订单事实表-构建表和第一个导入

适合场景：表中的数据已更新，并且是新的。拉链桌是否使用取决于两个条件

1、保持过去的状态吗？

2、是否应该考虑数据重复？

Step1:表格建立作业

1.提取影响dwd表格创建语句的字段时，不会有任何不足

2.转换动作做什么？一定要在这里做吗？一定有问题的数据可以在这里处理，也可以在后面再出来。

3.请注意，除了业务字段外，还必须在拉链上添加两个附加字段starttime endtime

Step2:第一次汇入

如果是动态分区插入，并且相关参数ODS层次结构的表字段具有枚举类型，则可以在从ETL到dwd的转换过程中使用case when语句进行转换。

-由于项目的首次收集，将所有以前的数据视为一天的数据2021-11-29在将数据插入DWD层时出现选择题。分区以哪个为准？

-截至数据时间发生之日的create_time

-以收集时间发生的日期为基准

Step3:查看验证表结果

知识点18:构建DWD层-订单事实表-导入环路和拉链

Step1:在MySQL中修改t_shop_order表数据模拟业务中存在新订单，并发生订单数据更新。由于上次导入ODS时指定的分区时间为2021-11-29，因此在模拟数据时，请将时间设置为2021-11-30天的新任务和更新任务。

Step 2:提取ODS层新，更新数据

使用Sqoop添加和更新同步实施。

Step3:创建中间中间中间临时表以存储拉链结果

临时桌的结构与最终拉链桌的结构相同。

Step4:拉链操作结果to临时表

步骤5:临时表验证查询

知识点19:构建DWD层-时间维表-获取整个范围

适合场景：

表是低表数据，很少更新，不需要历史状态维护

知识点20:构建dwd层-订单评估表-增量导入(仅限新)

适合场景：表数据有新的(insert)，但没有更新。不需要记录状态维护。每次都可以使用新添加的数据作为新分区。

知识点21:构建dwd层-最终正式版

对于DWD图层上的其他表格工作，您可以使用课程中提供的脚本批次执行，以提高效率。

前提是要掌握拉链手表的使用。

Day05 _ DWB分层建设实战，Presto计算引擎

p>DWB

• 名称：基础数据层、中间数据层
• 功能：退化维度（降维）形成大宽表

退化维度

• 百科定义
• 退化维度（Degenerate Dimension,DD），就是那些看起来像是事实表的一个维度关键字，但实际上并没有对应的维度表。
• 退化维度技术可以减少维度的数量（降维操作），简化维度数据仓库的模式。简单的模式比复杂的更容易理解，也有更好的查询性能。

常见操作

1、将各个维度表的核心字段数据汇总到事实表中；

2、如果一个业务模块有多个事实表，也可以将多个事实表的核心字段汇总到一个事实表。

功能

• 通过退化维度操作之后，带来的显著效果是
• 整个数仓中表的个数减少了；
• 业务相关联的数据（跟你分析相关的）数据字段聚在一起了，形成一张宽表。
• 分析查询时的效率显著提高了：多表查询和单表查询的差异。
• 带来的坏处是
• 数据大量冗余、宽表的概念已经不符合3范式设计要求了。
• 但是数仓建模的核心追求是，只要有利于分析，能够加快数据分析，都可以做。

• 新零售项目--DWB层明细宽表
• 根据业务形式和后续分析需求，划分了3大模块，对应着3张明细宽表。
• 用户业务模块没有进行退化维度操作，原因是后面的指标单表可以操作技术。
• 订单明细宽表 dwb_order_detail
• 店铺明细宽表 dwb_shop_detail
• 商品明细宽表 dwb_goods_detail
• 使用DataGrip在Hive中创建dwb层

建表：订单明细宽表 dwb_order_detail

在进行维度退化的时候，需要将各个表的核心字段退化到事实表中形成宽表，究竟哪些是核心字段呢？

答案是：明显不需要的可以不退化 另外拿捏不住 “宁滥勿缺”。

知识点04：DWB层搭建--订单明细宽表--join操作

• join方式的选择
• left左连接
• 以y为左表，其他表进行left join
• 注意事项
• 除fact_order_pay订单组表之外，其他表都是通过order_id与订单主表进行连接
• fact_order_pay与fact_shop_order_group通过group_id连接，间接与订单主表连接
• 如果表是一张拉链表，注意加上过滤条件 end_date='9999-99-99',把当前有效的数据查询出来
• 对于fact_shop_order的end_date='9999-99-99'过滤，应该放在where条件中完成，先过滤，后join

核心表: y 店铺表
退化维度表:
dim_trade_area 商圈表
记录商圈相关信息，店铺需要归属商圈中(ID主键是店铺表中的外键，trade_area_id)
dim_location 地址信息表
记录了店铺地址
dim_district 区域字典表
记录了省市县区域的名称、别名、编码、父级区域ID

知识点07：DWB层搭建--店铺明细宽表--省市区join实现剖析

• 业务梳理
• 业务系统在设计地址信息类数据存储时，采用的方法如下。

#a、所有地址类信息统一存储在dim_location地址表中，通过type来表名属于什么地址。比如我们需要店铺地址时，需要在查询是添加条件where type=2来找出店铺地址信息。
1：商圈地址；2：店铺地址；3.用户地址管理;4.订单买家地址信息;5.订单卖家地址信息

#b、而地址详细信息比如我们业务需要的省、市、县名称及其ID共6个字段，却又是存储在dim_district区域字典表中的。

#c、然而比较可惜的是，区域字典表中的数据，不是所谓的帮省市区信息存储在一行种，而是通过父ID这样的方式形成关联。具体数据样式见下图。

核心表: dim_goods 商品SKU表
记录了商品相关信息
退化维度表:
dim_goods_class 商品分类表
记录了商品所属的分类信息：商品大类、商品中类、商品小类
dim_brand 品牌信息表
记录了品牌信息

# 1、业务系统在设计商品分类表dim_goods_class时，准备采用的是3级存储。通过level值来表示。
1 大类
2 中类
3 小类

# 2、但是dim_goods_class数据集中实际等级效果只有两类，level=3的只有1个分类。

问题

上述现象也就意味着很多商品在存储的时候采用的是两类存储，这点通过简单的sql得到了验证；

构建商品明细表时候，我们需要的是3类结果：商品小类、商品中类、商品大类。

因此在编写join的时候，我们需要关联3次，实际中的join情况因为分为下面3种：

如果level=3，才会关联到level=2 ，再去关联level=1

如果level=2，关联到level=1,结束

如果level=1，结束

结束指的是，已经到大类级别了，没有parent_id了。就是执行join，结果也是为空。

1、先根据dim_goods.store_class_id = dim_good查出商品小类

2、然后根据小类.parent_id=dim_good查出商品中类

3、最后根据中类.parent_id=dim_good查出商品大类

这样导致的结果是：查询出来的3级分类会形成错位。如:

一个商品level=2，只能查询出来中类、大类，但是根据上述join的方式，却把

中类当成了小类，大类当成了中类，把null当成了大类。

那么在查询结果取值返回的时候，一定要进行条件判断了，使用case when语句。避免错误。

知识点12：Presto--分布式SQL查询引擎介绍

• 背景
• 大数据分析类软件发展历程。
• Apache Hadoop MapReduce
• 优点：统一、通用、简单的编程模型，分而治之思想处理海量数据。
• 缺点：java学习成本、MR执行慢、内部过程繁琐
• Apache Hive
• 优点：SQL on Hadoop。sql语言上手方便。学习成本低。
• 缺点：底层默认还是MapReduce引擎、慢、延迟高
• hive的后续发展：改变自己的引擎 Tez Spark
• 各种SQL类计算引擎开始出现，主要追求的就是一个问题：怎么能计算的更快，延迟低。
• Spark On Hive、Spark SQL
• Impala
• Presto
• ClickHouse
• ........

介绍

Presto是一个开源的分布式SQL查询引擎，适用于交互式查询，数据量支持GB到PB字节。

Presto的设计和编写完全是为了解决Facebook这样规模的商业数据仓库交互式分析和处理速度的问题。

Presto支持在线数据查询，包括Hive、kafka、Cassandra、关系数据库以及专门数据存储;

=一条Presto查询可以将多个数据源进行合并，可以跨越整个组织进行分析;

Presto以分析师的需求作为目标，他们期望相应速度小于1秒到几分钟;

Presto终结了数据分析的两难选择，要么使用速度快的昂贵的商业方案，要么使用消耗大量硬件的慢速的“免费”方案。

#优点
1）Presto与Hive对比，都能够处理PB级别的海量数据分析，但Presto是基于内存运算，减少没必要的硬盘IO，所以更快。

2）能够连接多个数据源，跨数据源连表查，如从Hive查询大量网站访问记录，然后从Mysql中匹配出设备信息。

3）部署也比Hive简单，因为Hive是基于HDFS的，需要先部署HDFS。

#缺点
1）虽然能够处理PB级别的海量数据分析，但不是代表Presto把PB级别都放在内存中计算的。而是根据场景，如count，avg等聚合运算，是边读数据边计算，再清内存，再读数据再计算，这种耗的内存并不高。但是连表查，就可能产生大量的临时数据，因此速度会变慢，反而Hive此时会更擅长。

2）为了达到实时查询，可能会想到用它直连MySql来操作查询，这效率并不会提升，瓶颈依然在MySql，此时还引入网络瓶颈，所以会比原本直接操作数据库要慢。

知识点12：Presto--架构、相关术语

• 架构图
• Presto是一个运行在多台服务器上的分布式系统。 完整安装包括一个coordinator和多个worker。 由客户端提交查询，从Presto命令行CLI提交到coordinator; coordinator进行解析，分析并执行查询计划，然后分发处理队列到worker。

Presto查询引擎是一个M-S的架构，由一个coordinator节点，一个Discovery Server节点，多个Worker节点组成,注意Discovery Server通常内嵌在Coordinator节点中。

主角色:Coordinator负责SQL的解析，生成执行计划，分发给Worker节点进行执行;
从角色:Worker节点负责实时查询执行任务。Worker节点启动后向discovery Server服务注册，Coordinator 从discovery server获取可以工作的Worker节点。

如果配置了hive connector，需要配置hive MetaSote服务为Presto提供元信息，worker节点和HDFS进行交互数据。

Connector 连接器

1、Presto通过Connector连接器来连接访问不同数据源，例如Hive或mysql。连接器功能类似于数据库的驱动程序。允许Presto使用标准API与资源进行交互。

2、Presto包含几个内置连接器：JMX连接器，可访问内置系统表的System连接器，Hive连接器和旨在提供TPC-H基准数据的TPCH连接器。许多第三方开发人员都贡献了连接器，因此Presto可以访问各种数据源中的数据，比如：ES、Kafka、MongoDB、Redis、Postgre、Druid、Cassandra等。

Catalog 连接目录

1、Presto Catalog是数据源schema的上一级，并通过连接器访问数据源。

2、例如，可以配置Hive Catalog以通过Hive Connector连接器提供对Hive信息的访问。

3、在Presto中使用表时，标准表名始终是被支持的。
例如，的标准表名将引用hive catalog中test_data schema中的test table。
Catalog需要在Presto的配置文件中进行配置。

schema

Schema是组织表的一种方式。Catalog和Schema共同定义了一组可以查询的表。

当使用Presto访问Hive或关系数据库（例如MySQL）时，Schema会转换为目标数据库中的对应Schema。

=schema通俗理解就是我们所讲的database.
=想一下在hive中，下面这两个sql是否相等。
show databases;
shwo schemas;

知识点13：Presto--集群模式安装

step1：集群规划

step2：项目集群环境安装JDK

已经安装好

step3：上传Presto安装包（hadoop01）

step4：添加配置文件（hadoop01）

• etc
• etc
• etc
• etc/catalog
• step4：scp安装包到其他机器
• step5：hadoop02配置修改
• etc
• etc
• 和hadoop01一样，不变，唯一注意的就是如果机器内存小，需要调整-Xmx参数
• etc
• 修改编号node.id

etc/catalog

保持不变

知识点14：Presto--集群启停

注意，每台机器都需要启动

• 前台启动
• 后台启动

web UI页面

知识点15：Presto--命令行客户端

下载CLI客户端

上传客户端到Presto安装包

CLI客户端启动

ctrl+D 退出客户端

知识点16：Presto--Datagrip连接使用

JDBC 驱动：

JDBC 地址：jdbc:presto://192.168.88.80:8090/hive

• step1：配置驱动

step2：创建连接

step3：测试体验

知识点17：Presto--时间日期类型注意事项

• date_format(timestamp, format) ==> varchar
• 作用: 将指定的日期对象转换为字符串操作
• date_parse(string, format) → timestamp
• 作用: 用于将字符串的日期数据转换为日期对象

• date_add(unit, value, timestamp) → [same as input]
• 作用: 用于对日期数据进行 加 减 操作
• date_diff(unit, timestamp1, timestamp2) → bigint
• 作用: 用于比对两个日期之间差值

知识点18：Presto--常规优化

• 数据存储优化

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

--2）使用列式存储
Presto对ORC文件读取做了特定优化，因此在Hive中创建Presto使用的表时，建议采用ORC格式存储。相对于Parquet，Presto对ORC支持更好。
Parquet和ORC一样都支持列式存储，但是Presto对ORC支持更好，而Impala对Parquet支持更好。在数仓设计时，要根据后续可能的查询引擎合理设置数据存储格式。

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

--4）预先排序
对于已经排序的数据，在查询的数据过滤阶段，ORC格式支持跳过读取不必要的数据。比如对于经常需要过滤的字段可以预先排序。

• SQL优化
• 列裁剪
• 分区裁剪
• group by优化
• 按照数据量大小降序排列
• order by使用limit
• 用regexp_like代替多个like语句
• join时候大表放置在左边
• 替换非ORC格式的Hive表

知识点19：Presto--内存调优

• 内存管理机制--内存分类
• Presto管理的内存分为两大类：user memory和system memory
• user memory用户内存
• 跟用户数据相关的，比如读取用户输入数据会占据相应的内存，这种内存的占用量跟用户底层数据量大小是强相关的
• system memory系统内存

执行过程中衍生出的副产品，比如tablescan表扫描，write buffers写入缓冲区，跟查询输入的数据本身不强相关的内存。

内存管理机制--内存池

内存池中来实现分配user memory和system memory。

内存池为常规内存池GENERAL_POOL、预留内存池RESERVED_POOL。

1、GENERAL_POOL:在一般情况下，一个查询执行所需要的user/system内存都是从general pool中分配的，reserved pool在一般情况下是空闲不用的。

2、RESERVED_POOL:大部分时间里是不参与计算的，但是当集群中某个Worker节点的general pool消耗殆尽之后，coordinator会选择集群中内存占用最多的查询，把这个查询分配到reserved pool，这样这个大查询自己可以继续执行，而腾出来的内存也使得其它的查询可以继续执行，从而避免整个系统阻塞。

注意:
reserved pool到底多大呢？这个是没有直接的配置可以设置的，他的大小上限就是集群允许的最大的查询的大小)。
reserved pool也有缺点，一个是在普通模式下这块内存会被浪费掉了，二是大查询可以用Hive来替代。因此也可以禁用掉reserved pool（ex设置为false），那系统内存耗尽的时候没有reserved pool怎么办呢？它有一个OOM Killer的机制，对于超出内存限制的大查询SQL将会被系统Kill掉，从而避免影响整个presto。

1、user memory用户内存参数
query.max-memory-per-node:单个query操作在单个worker上user memory能用的最大值
query.max-memory:单个query在整个集群中允许占用的最大user memory

2、user+system总内存参数
query.max-total-memory-per-node:单个query操作可在单个worker上使用的最大(user + system)内存
query.max-total-memory:单个query在整个集群中允许占用的最大(user + system) memory

当这些阈值被突破的时候，query会以insufficient memory（内存不足）的错误被终结。

3、协助阻止机制
在高内存压力下保持系统稳定。当general pool常规内存池已满时，操作会被置为blocked阻塞状态，直到通用池中的内存可用为止。此机制可防止激进的查询填满JVM堆并引起可靠性问题。

4、其他参数
memory.heap-headroom-per-node:这个内存是JVM堆中预留给第三方库的内存分配，presto无法跟踪统计，默认值是-Xmx * 0.3

5、结论
GeneralPool = 服务器总内存 - ReservedPool - memory.heap-headroom-per-node - Linux系统内存

常规内存池内存大小=服务器物理总内存-服务器linux操作系统内存-预留内存池大小-预留给第三方库内存

内存优化建议

• 常见的报错解决
• total memory= user memory +system

建议参数设置

1、query.max-memory-per-node和query.max-total-memory-per-node是query操作使用的主要内存配置，因此这两个配置可以适当加大。
memory.heap-headroom-per-node是三方库的内存，默认值是JVM-Xmx * 0.3，可以手动改小一些。

1) 各节点JVM内存推荐大小: 当前节点剩余内存*80%

2) 对于heap-headroom-pre-node第三方库的内存配置: 建议jvm内存的%15左右

3) 在配置的时候, 不要正正好好, 建议预留一点点, 以免出现问题

数据量在35TB , presto节点数量大约在30台左右 (128GB内存 + 8核CPU)

注意：
1、query.max-memory-per-node小于query.max-total-memory-per-node。
2、query.max-memory小于query.max-total-memory。
3、query.max-total-memory-per-node 与memory.heap-headroom-per-node 之和必须小于 jvm max memory，也就是jvm.config 中配置的-Xmx。

项目优化：Hive Map join优化

Sort Merge Bucket Join（SMB Join）

SMB是针对Bucket Map Join的一种优化。条件类似却有些不一样。

1）
set = true;
set ;
set .sortedmerge = true;
set ;

2）
Bucket 列 == Join 列 == sort 列

#hive并不检查两个join的表是否已经做好bucket且sorted，需要用户自己去保证join的表数据sorted， 否则可能数据不正确。

3）
bucket数相等

#注意：
a、可以设置参数 为true，开启强制排序。插数据到表中会进行强制排序。
b、表创建时必须是CLUSTERED BY+SORTED BY

已看完：：：：：：：：：：：：：：

​