为什么宽桌占上风?
#编程 #sql #database #bigdata

BI业务中的宽桌子很多。每当构建BI系统时,您需要做的第一件事就是准备一张宽桌子。有时,系统中的宽表可能具有数百个字段,由于太宽了数据库表的限制。

,通常需要分开该表

为什么人们如此热衷于创建宽桌?有两个主要原因。

一个是提高查询性能。通常,Modern BI将关系数据库作为后端,但是Hash JOIN算法的计算性能通常采用时采用的算法会急剧减少,当表格和关联的级别增加时,只有七或八个现象可以观察到这种现象桌子和三个或四个级别。但是,BI业务的关联复杂性远远超过了该规模,如果直接使用SQL的JOIN算法执行查询任务,它将无法满足查询要求立即在前端获得结果。为了避免关联引起的性能问题,它需要从查询阶段消除关联,也就是说,提前将多个表关联并将其存储在单个表中(即宽表)。这样,在查询时无需再次关联,从而提高了查询性能。

另一个是减少业务困难。因为在采用自动关联(基于诸如现场类型之类的信息)时,很难表达和使用多桌子关联,尤其是在BI前端的复杂关联,当面对相同的字段时,系统会感到困惑维度(例如,一个表具有2个以上的区域字段),并且不知道应该关联哪个字段。同样,也很难处理表末端的表或自我关联之间的循环关联。如果向用户开放了许多表格,允许他们自己交往,那么几乎没有用,因为他们无法理解表之间的关系。尽管逐步协会可以描述复杂的关联要求,但是一旦上一步出错,就必须从头开始完成。因此,无论采用哪种方法,无论是在实践还是用户操作中都非常麻烦。相反,如果它是一张表,并且企业用户没有运营的障碍,这将非常简单。因此,将多个表组织到宽桌子中是很自然的。

但是,一切都有两个方面。当我们在意识到其优势之后广泛应用宽表时,我们不应忽略其缺点。一些缺点将对该应用程序产生重大影响。以下是几个缺点。

宽桌子的缺点

数据冗余,容量较高

宽表不符合数据库规范,并且将多个表连接到单个表中时存在许多冗余数据。冗余度与原始表的数据量和表之间的关系有关。通常,如果有多层外部钥匙表,则冗余度将成倍增加。大量的冗余数据不仅给存储带来压力(从多个表连接的宽表的字段数可能很大),从而导致数据库容量问题,而且会影响计算性能,因为如此庞大的冗余数据参与查询计算。结果,即使使用宽桌,查询仍然很慢。

数据错误

由于宽表不符合3NF,因此在数据存储期间可能会出现一致性错误(肮脏的写作)。例如,同一销售人员在不同的记录中可能具有不同的性别。在不同记录中,同一供应商所在的区域可能有所不同。基于此类数据进行的分析的结果肯定是不正确的,并且隐藏了此类错误,很难找到。

此外,如果未正确构建宽表,将发生聚合误差。例如,当基于一对多表A和B构建宽表时,如果表A中有计算索引(例如数量),则它们将在宽表中复制,并根据重复索引进行汇总会导致错误。

灵活性不佳

本质上,宽表是按需建模的一种手段。但是,当宽度表是根据业务需求构建的(尽管从理论上讲是可以组合所有表以形成宽桌子的情况,但这仅在理论上存在。如果您这样做,您会发现所需的存储空间是如此很大的是,它完全无法接受),这是一个矛盾:构建BI系统的最初意图是满足灵活的业务查询的需求,即,您不会事先知道业务需求。但是,在业务开发过程中引起了某些查询要求,用户暂时需要一些查询要求,这种灵活和不断变化的要求与解决方案(宽表)非常矛盾,需要提前处理。结果,如果您想要宽桌子的优势,则必须牺牲灵活性,不能同时获得两者。

低可用性

除了上述问题外,宽表还可能导致过多的领域导致较低的可用性。事实表将对应于多个维表,一个尺寸表可能具有其自身的维表,并且表之间可能存在自我关联 /周期关联关系。这种结构在数据库系统中非常普遍。当创建基于具有这种结构的表的宽表时,尤其是在表达多个级别时,宽表的字段数量将大大增加,通常达到数百个字段(某些数据库表限制了字段数,在这种情况下,宽表需要水平拆分)。想象一下,如果在用户访问接口上出现数百个字段,如何使用它们?这是宽桌子带来的低可用性问题。

总的来说,在许多情况下,宽桌子的缺点大于其优势。然后,为什么宽桌仍然占上风?

因为没有其他方法。没有比宽桌更好的解决方案来解决之前提到的查询性能和业务困难问题。实际上,只要解决这两个问题,就可以消除宽桌子,并相应地消除了各种问题。

SPL+DQL消除了可释放的

可以借助开源ESPROC SPL实现此目标。

SPL(结构化过程语言)是一种开源的结构化数据计算引擎,具有固有强大的计算能力,不取决于数据库,并且已构建了许多高性能算法,特别是优化的关联操作。您可以为不同的关联方案选择不同的方法,从而大大提高了关联性能。因此,也可以在没有宽表的情况下实现实时关联,以满足多维分析中的及时性需求。此外,SPL提供了高性能存储方案。通过这些方案及其有效算法,可以进一步利用SPL的性能优势。

仅高性能是不够的,SPL的本机计算语法不适合连接到多维分析应用程序(生成SPL语句需要经常修改BI系统)。当前,大多数多维分析前端都是基于SQL开发的,但是SQL系统很难描述没有宽表的复杂关联。因此,SPL设计了用于构建语义层的专业SQL式查询语法DQL(尺寸查询语言)。首先,在前端生成DQL语句,然后将其在DQL Server上转换为SPL语句,接下来,基于SPL计算引擎和存储引擎查询,最后将结果返回到前端。此时,实现了全链接BI查询。应该注意的是,SPL仅用作计算引擎,并且前端接口仍应由用户(或选择相关产品)实现。

Image description

SPL:协会实施技术

doesspl如何实现实时关联的目标而没有宽桌来满足性能要求?

在BI业务中,绝大多数加入是同等的联接(即,其关联条件是方程式的联接)。 SPL将等效的加入到外国密钥协会和初级密钥协会中。 外国密钥协会是指使用一个表的非主要密钥字段与另一个表的主要密钥相关联,前者称为“事实表”,后者称为“事实表”维度表。这两个表处于众多关系中,例如订单表和客户表。 主密钥关联是指使用一个表的主键将主要密钥或另一个主键的一部分关联。例如,客户表和VIP客户表(一对一),订单表和订单详细表(一对多)。

两种类型的连接都涉及主键。如果此功能已充分利用,并且采用了不同的算法,则可以实现高性能实时关联。

不幸的是,SQL的JOIN定义不涉及主键,它只是两个桌子上的笛卡尔产品操作,然后在特定条件下进行过滤。这个定义简单而广泛,几乎描述了所有内容。但是,如果严格遵循此定义以实施加入,则无法通过在计算过程中利用上述功能来提高理论上的性能,并且在实践中只能进行有限的优化,但是当这种优化往往是无效的情况很复杂(许多表,许多级别)。

SPL更改联接的定义,并分别处理两种类型的连接。通过这样做,可以利用主键的特征来减少操作负载以提高计算性能。

外国密钥协会

与SQL不同,SPL清楚地区分了尺寸表和事实表。 BI系统中的尺寸表通常不大,您可以预先将其读取到内存中以创建索引。这样,可以在关联期间将哈希值的计算减少一半。

对于多级维度表(尺寸表具有其自身的尺寸表),您可以使用外键通讯技术技术 pre-ssococotiate ,也就是说将尺寸表的外键字段值(顶层的表)转换为相应的维度表(外键表)记录的地址。这允许在无需计算和比较哈希值的情况下直接将关联的维度表的数据直接获取。在多级维度表中花费的额外时间仅仅是通过地址获取值的时间,并且关联性能基本上与一级维度表相当。

类似地,如果事实表也不大,并且可以将其完全加载到内存中,则还可以通过预先关联来解决事实表和维度表之间的关联问题以提高关联效率。

事实表可以读取为内存并在系统启动时进行一次预先相关,并且将来可以直接使用。

当事实表太大而无法完全存储在内存中时,SPL提供了外键序列编号方法:将事实表的外键字段值转换为相应维度的序列编号表记录。在关联计算过程中,使用序列编号获取相应的维表记录可以获得相当于外国密钥处理的效果,同时避免了哈希值的计算和比较,并大大提高了关联性能。

主要钥匙关联

一些事实表也有详细表。例如,订单表具有订单详细表,并且两个表通过主键或主键的一部分关联。前者是主要表,后者是子表(对于那些通过所有主要键关联的表被称为同性差异表,可以被视为主sub表的特殊情况)。主要表和子表都是事实表,每个表都涉及大量数据。

鉴于这种情况,SPL提供了有序合并算法:按主键按主键预先存储表格中的表格,并按顺序取出数据以执行合并计算。 ,并且无需生成临时缓冲文件,并且只需少量的存储空间即可完成计算。相反,SQL采用的哈希值分区算法更为复杂,它不仅需要计算哈希值以进行比较,而且还会生成临时缓冲数据的读写动作,从而导致计算性能差。

对于哈希值分区技术,由于多个线程需要将数据缓冲到某个分区,因此很难实现并行计算,从而导致共享资源的冲突,并且在关联时会消耗大量的内存空间某个分区,使得无法进行大量的并行计算。相比之下,有序合并算法很容易在段中执行并行计算。当数据按顺序进行时,可以根据主表的键值同步对齐和分割子表,以确保正确性,并且不需要数据缓冲。此外,它可以处理大量的并行计算,因为内存空间职业较低,从而实现了更高的性能。

尽管预分量的成本很高,但在此之后,它只需要一次使用一次,但可以通过合并算法实现JOIN,并且性能可以大大提高。此外,SPL还提供了一个解决方案,即使有附加的数据,也可以将整个数据保持在顺序中。

对于初级 - 单表关联,SPL采用了一种更有效的存储方法,即以综合方式存储它们,子表作为主表的设定字段,其值由多个子 - 表记录与主表的数据有关。这等同于预加入。这样,直接获取数据就可以了,并且在计算时不需要比较,存储量和较高的性能。

存储机制

高性能与有效存储密不可分。因此,SPL提供了列存储,该技术可以显着减少BI计算中的数据阅读量,以提高读取效率。 SPL的圆柱存储采用唯一的双增量分段技术。与传统柱状存储的封锁并行计算方案不同,仅在数据量非常大(否则将受到限制)时起作用(否则将受到限制),分段技术使SPL柱状存储能够达到良好的并行分段效果,即使数据量不是很大大,并行计算的优势是完全利用的。

此外,SPL为数据类型提供了优化机制,该机制可以显着改善多维分析中切片操作的性能。例如,我们首先将枚举的类型维度转换为整数,然后将切片条件转换为查询期间由布尔值组成的对齐序列。这样,可以在比较过程中直接从指定的序列位置取出切片判断结果。此外,SPL提供了一个标签位维度技术,该技术存储多个标签维度(仅具有两个值的维度:是或否,在多维分析中,此类维度非常普遍)到整数字段(一个整数字段可以存储16个标签),该技术可以大大减少存储量,并且可以同时对多个标签进行位计算,从而大大提高计算性能。

使用这些有效的机制,我们可以在BI分析中丢弃宽桌。取而代之的是,我们可以根据SPL存储方案和算法进行实时关联,性能高于宽表(由于没有冗余数据,因此数据读取和更快的速度)。

但是,仅这些机制,算法和方案还不够,SPL的本地语法不适合在BI前端直接访问,它需要适当的语义转换技术来将用户操作转换为SPL语法以查询SPL以合适的方式。

DQL是这样的技术。

DQL:协会描述技术
DQL是SPL SPL数据关联关系(建模)上层应用程序的语义层的构建工具。具体来说,将SPL存储映射到DQL表,然后根据表描述数据关联关系。

Image description

描述了数据表的数据关系后,形成了以维度为中心的类似总线结构(与E-R图中的网格结构不同),中间的尺寸在中间,并且表不直接相关的表通过维度。

Image description

基于此结构的关联查询(DQL语句)很容易表达。例如,当我们想查询中国东部的销售人员在订单表(订单),客户表(客户),销售员表(员工)和城市桌(城市)周围的每个销售区域的销售年份时:

SQL中的编码是这样的:

SELECT
 ct1.area,o.emp_id,sum(o.amount) somt
FROM
 orders o
 JOIN customer c ON o.cus_id = c.cus_id
 JOIN city ct1 ON c.city_id = ct1.city_id
 JOIN employee e ON o.emp_id = e.emp_id
 JOIN city ct2 ON e.city_id = ct2.city_id
WHERE
 ct2.area = 'east' AND year(o.order_date)= 2022
GROUP BY
 ct1.area,  o.emp_id

我们可以看到,在将多个表关联时,它需要多次加入。同一区域表必须重复关联两次,以找出销售人员和客户所在的区域。在这种情况下,很难在BI前端表达。如果将关联提交给用户,则很难理解。

那么,DQL如何处理?

DQL代码:

SELECT
 cus_id.city_id.area,emp_id,sum(amount) somt
FROM
 orders
WHERE
 emp_id.city_id.area == "east" AND year(order_date)== 2022
BY
 cus_id.city_id.area,emp_id

dql不需要加入多个表,而基于单个表(订单)的查询工作。外键点直接用作属性的表字段,它可以引用任何数量的级别,并且易于表达。例如,如果您想查询客户所在的区域,则可以编写:cus_id.city_id.area。这样,消除了关联,将多桌关联查询转换为单个表查询。

此外,很容易开发基于DQL的BI前端接口,例如:

Image description

此树结构在多个级别上表达多层维度表的关联。这样的多维分析页面不仅很容易开发,而且很容易理解普通的企业用户。这是DQL效应所在的地方。

总而言之,宽表的目的是解决BI查询性能和前端用户操作问题。但是,在解决这两个问题的同时,宽桌子还带来了诸如数据冗余和灵活性差等问题。如果采用SPL和DQL,则可以更有效地解决这两个问题。具体而言,SPL的实时关联技术和有效的存储方案以更好的结果解决了性能问题,而没有数据冗余,并且较少的存储空间要求(压缩);通过DQL构建的语义层解决了第二个问题,使实时关联成为可能,并且具有更高灵活性的优势(它不再限于宽表的按需建模),易于实现接口和更广泛的应用程序范围。

spl+dql继承(超过)宽桌子的优势,同时改善了其缺点。这就是bi应该的样子。

起源:https://blog.scudata.com/why-does-wide-table-prevail/
SPL源代码:https://github.com/SPLWare/esProc