百倍压缩、秒级查询！观远智能拉链表这样高效处理海量数据

      <!-- /\* Font Definitions \*/ @font-face {font-family:宋体; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-alt:SimSun; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 680460288 22 0 262145 0;} @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:0; mso-generic-font-family:roman; mso-font-pitch:variable; mso-font-signature:3 0 0 0 1 0;} @font-face {font-family:等线; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-alt:DengXian; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:-1610612033 953122042 22 0 262159 0;} @font-face {font-family:"Microsoft YaHei UI"; panose-1:2 11 5 3 2 2 4 2 2 4; mso-font-charset:134; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-2147483001 718224464 22 0 262175 0;} @font-face {font-family:"\\@Microsoft YaHei UI"; mso-font-charset:134; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-2147483001 718224464 22 0 262175 0;} @font-face {font-family:"\\@宋体"; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 680460288 22 0 262145 0;} @font-face {font-family:"\\@等线"; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:-1610612033 953122042 22 0 262159 0;} /\* Style Definitions \*/ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; mso-pagination:none; font-size:10.5pt; mso-bidi-font-size:11.0pt; font-family:等线; mso-ascii-font-family:等线; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:等线; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:等线; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-font-kerning:1.0pt;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-family:等线; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} /\* Page Definitions \*/ @page {mso-page-border-surround-header:no; mso-page-border-surround-footer:no;} @page WordSection1 {size:595.3pt 841.9pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:42.55pt; mso-footer-margin:49.6pt; mso-paper-source:0; layout-grid:15.6pt;} div.WordSection1 {page:WordSection1;} /\* List Definitions \*/ @list l0 {mso-list-id:1756394649; mso-list-template-ids:-1063857600;} @list l0:level1 {mso-level-number-format:bullet; mso-level-text:; mso-level-tab-stop:36.0pt; mso-level-number-position:left; text-indent:-18.0pt; mso-ansi-font-size:10.0pt; font-family:Symbol;} @list l0:level2 {mso-level-number-format:bullet; mso-level-text:; mso-level-tab-stop:72.0pt; mso-level-number-position:left; text-indent:-18.0pt; mso-ansi-font-size:10.0pt; font-family:Symbol;} @list l0:level3 {mso-level-number-format:bullet; mso-level-text:; mso-level-tab-stop:108.0pt; mso-level-number-position:left; text-indent:-18.0pt; mso-ansi-font-size:10.0pt; font-family:Symbol;} @list l0:level4 {mso-level-number-format:bullet; mso-level-text:; mso-level-tab-stop:144.0pt; mso-level-number-position:left; text-indent:-18.0pt; mso-ansi-font-size:10.0pt; font-family:Symbol;} @list l0:level5 {mso-level-number-format:bullet; mso-level-text:; mso-level-tab-stop:180.0pt; mso-level-number-position:left; text-indent:-18.0pt; mso-ansi-font-size:10.0pt; font-family:Symbol;} @list l0:level6 {mso-level-number-format:bullet; mso-level-text:; mso-level-tab-stop:216.0pt; mso-level-number-position:left; text-indent:-18.0pt; mso-ansi-font-size:10.0pt; font-family:Symbol;} @list l0:level7 {mso-level-number-format:bullet; mso-level-text:; mso-level-tab-stop:252.0pt; mso-level-number-position:left; text-indent:-18.0pt; mso-ansi-font-size:10.0pt; font-family:Symbol;} @list l0:level8 {mso-level-number-format:bullet; mso-level-text:; mso-level-tab-stop:288.0pt; mso-level-number-position:left; text-indent:-18.0pt; mso-ansi-font-size:10.0pt; font-family:Symbol;} @list l0:level9 {mso-level-number-format:bullet; mso-level-text:; mso-level-tab-stop:324.0pt; mso-level-number-position:left; text-indent:-18.0pt; mso-ansi-font-size:10.0pt; font-family:Symbol;} ol {margin-bottom:0cm;} ul {margin-bottom:0cm;} -->   

做更有温度的智能数据分析产品

观远智能数据分析平台不仅要在分析结果上为企业打造智能决策大脑，更要在分析过程中，通过一系列黑科技产品帮助企业摆脱数据存储、采集、清洗、分析到可视化过程中高成本、高代码、低效率的困境，解锁更多贴心的数据分析新姿势。

随着互联网+大数据时代的发展,海量数据在为企业带来价值的同时也为数据存储、清洗、查询的工作带来了很多困扰。

「历史拉链表」功能强大，但同样要求复杂的操作流程和高人天投入

面对动辄几十亿行的库存数据，很多企业都会面对“留之累赘弃之担忧”的尴尬。最常见的解决办法就是把每一天的全量快照数据都存下来，提供日期主键，然后开放给用户去查询。但这样实际上会保存很多不变的信息，对存储是极大的浪费；再者，设计不当还非常影响查询效率，拖垮数据库。

举个例子

一家连锁药店企业，门店数3000，SKU数1000，如果存库存快照数据，每天就是300万，一年就是10个亿。如果要求能够查询5年的历史数据，那么就需要保存近50亿的历史快照数据。

面对这类问题，“历史拉链表”被公认为是处理海量历史数据压缩存储与查询的最佳手段。既能满足反应数据的历史状态，又可以最大程度的节省存储空间，提高查询效率。

但是，一般在关系型数据库上构建和维护拉链表，需要包含拉链初始化、开链、封链、增量更新等多个复杂的操作步骤，每个步骤都需要用户对拉链表的操作思路非常清楚，且对SQL使用非常熟悉。因此一般情况下如果不是经验丰富的数仓工程师是很难掌握这套方法的，更不用说在一个BI分析平台内做拉链表的设计。

有没有既能节省存储空间、提高查询效率，又能降低学习门槛，提升用户体验的方法？本期技术干货将为大家揭晓如何利用观远Smart ETL平台制作智能历史拉链表，半小时实现百倍压缩，秒级响应。

基于观远Smart ETL平台30分钟制作智能历史拉链表

区别于复杂的拉链表制作过程，观远数据分析平台依托于Spark计算引擎，彻底简化了历史拉链表的构建和维护的流程，总结和封装出一套用于构建与查询历史拉链表自定义函数（UDF），将原本需要高阶数据分析师通过复杂设计完成的工作，简化成普通数据分析员通过简单几个函数也能构建拉链表，并能支持高效查询。

通过观远Smart ETL平台制作历史拉链表，无论在性能表现还是拓展学习层面都具有独特的优势：

· 性能表现：根据客户库存快照数据查询为例，12亿行数据的库存快照表精处理后生成的数据集为800万行。基于该数据集进行指定仓库、指定日期的库存快照数据查询，响应时间基本在2~3秒；

· 拓展学习：实际上，观远数据分析平台针对时序相关的大数据量问题提供了一系列内置的UDF来解决一些常见的指标计算问题，可以做到快速、增量构建，同时又可以进行快速查询。这些函数可以用来做库存快照查询，销售额的WTD、MTD、YTD计算等各种与时间轴相关的大数据量查询与跨行计算问题。具体提供的函数列表如下：

以下为利用观远Smart ETL平台制作历史拉链表的实操介绍：

1. 库存拉链表构建

首先，将一份历史库存快照表（包含日期、商品基本信息、以及库存金额的基本数据）导入到观远数据平台中，去构建一个Smart ETL处理这份数据。

接着，我们使用一个分组聚合节点，并添加一个聚合字段 - “库存拉链”，改字段的表达式为：

 date_range_build(collect_list(`date`), collect_list(`库存金额`))，然后把商品信息字段拖入维度区域，库存拉链字段拖入数值区域，进行分组聚合计算。

当然如果你更习惯用SQL来处理数据，也可以使用“SQL输入”节点来处理该过程。SQL表达式为：

这样，我们就可以得到一个带库存拉链字段的输出数据集。预览一下，就可以看到该字段其实是一个k-v键值对，其中k是一个排序的日期数组，v是相应的库存金额数组。

到这一步，我们其实已经对库存快照数据进行了大幅的压缩。以观远数据的一个客户库存快照数据压缩为例，1年6亿行的数据经这一步压缩后，只剩下500万行；2年12亿行的数据经压缩后，只剩800万行。

进一步我们知道，对于库存数据来说，有些商品可能很长时间内库存都不会发生变化，也就是说上面的库存金额数组里可能会存在大量连续重复的数。这个时候，我们就可以使用date_range_remove_adjacent_same_values函数对库存快照键值再度进行压缩，构建库存拉链字段。

拉链表构建完成后，我们把结果表输出为数据集，这样就可以开始构建卡片进行快照数据查询了。

对于以上结构的库存拉链字段，我们提供了相应的函数进行快照查询。您可以通过以下方式进行快照数据的参数化查询：

date_range_lookup([库存拉链], to_date([DYNAMIC_PARAMS.时间参数])

2. 库存拉链表增量更新

以上是基于历史库存快照数据构建库存拉链表。那么当有新的快照数据进来，该如何Merge到现有的库存拉链表里面去呢？不慌，我们替你都想好了！我们提供了date_range_merge函数，可以帮你将多个时间区段的拉链表进行合并。比如：date_range_merge([库存拉链_2018],[库存拉链_2019])。

观远数据离线计算引擎基于Spark构建，我们针对零售行业业务场景，研究和实践了大量算法黑科技产品，帮助客户有效解决各类大数据量或复杂计算问题，如果你想解锁更多数据分析新姿势，欢迎与我们联系。

下 期 预 告

下一篇，将为大家讲解《如何借助Spark高阶函数，依托Smart ETL来实现复杂逻辑的指标计算》，敬请期待！