基于MaxCompute实现拉链表

注意事项

• 本案例通过命令建表，在实际开发中，您可通过DataWorks可视化功能创建，详情请参见：创建并使用MaxCompute表。

• 本案例中的，数据开发部分任务可以通过ETL工作流模板一键导入。在导入模板后，您可以前往目标工作空间，并自行完成任务运维等后续操作。

  • 仅空间管理员角色可导入ETL模板至目标工作空间，为账号授权空间管理员角色详情请参见空间级模块权限管控。

  • 导入ETL工作流模板，详情请参见导入ETL工作流模板。

  • ETL工作流模板快捷入口，请点击拉链表实现。

适用场景

• 在设计数据仓库的数据模型时，拉链存储技术可作为一种解决方案，满足以下需求：

  • 数据量较大。
  • 表中的部分字段被更新。

    例如，用户的地址、产品的描述信息、订单的状态和手机号码等。

  • 需要查看某一个时间点或时间段的历史快照信息。

    例如，查看某一个订单在某一个历史时间点的状态，或查看某一个用户在过去某段时间内更新过几次等。

  • 变化的比例不大或频率不高。

    假设总共有1000万个会员，且每天新增和发生变化的会员只有10万左右，如果每天都在表中保留一份全量，那么每次全量中会保存很多不变的信息，极大地浪费了存储资源。

关键字段介绍

拉链表通常包含以下几个关键字段：

案例介绍

• 数据表设计，本案例共设计2张数据表。

  • 交易下单源表：ods_order_d，用于存放业务库同步用来的每日增量数据。包含如下两个字段：

    • id：业务主键订单ID。

    • status：追踪变化的订单状态。

  • 交易下单事实表（拉链表）：dwd_order，用于拉链存储全量有效和失效的数据。包含如下四个字段：

    • id：业务主键订单ID。

    • status：追踪变化的订单状态。

    • start_date：有效开始日期。

    • end_date：有效结束时间。

• 拉链表实现逻辑。

  • 本案例将使用拉链表记录电商订单从开始到当前状态（创建/支付/完成）的所有变化信息，拉链表的加载逻辑详情请参见：拉链表数据加载逻辑。

任务开发：准备工作

任务开发：拉链表的实现

• 创建ODPS SQL节点：dwd_order，用于产出交易下单事实明细表数据。

• 节点间的依赖关系，如下图所示：

  

dwd_order

• 表数据：交易下单事实明细表（拉链表），用于存放全量有效数据和全量失效数据。包含有效开始日期start_date、有效结束时间end_date、业务主键ID和追踪订单状态变化的status字段。

• 在dwd_order节点编辑页面，输入如下示例代码：

  -- 1. 创建交易下单事实明细表，存放全量数据（拉链存储）。
  -- 补充说明：拉链表增加记录生命周期的字段start_date(生效日期)、end_date(失效日期)，当end_date为极大值'9999-12-31'
  --         时表示数据有效。
  CREATE TABLE IF NOT EXISTS dwd_order
  (
      ID            BIGINT COMMENT '订单ID'
      ,gmt_create   DATE COMMENT '创建时间，格式为yyyy-mm-dd'
      ,gmt_modified DATE COMMENT '更新时间，格式为yyyy-mm-dd'
      ,`status`     STRING COMMENT '状态'
      ,start_date   DATE COMMENT '生效日期，格式为yyyy-mm-dd'
      ,end_date     DATE COMMENT '失效日期，格式为yyyy-mm-dd，9999-12-31表示有效'
  )
  COMMENT '交易下单事实明细表(拉链表)'
  ;
  
  -- 2. 拉链存储的实现逻辑：业务日期当日ods_order_di新增或更新的所有数据插入dwd_order状态为有效数据、dwd_order中
  -- 有更新过的有效数据修改成失效数据。
  INSERT OVERWRITE TABLE dwd_order
  SELECT  id
          ,gmt_create
          ,gmt_modified
          ,`status`
          ,start_date
          ,end_date
  FROM    (
              SELECT  id
                      ,gmt_create
                      ,gmt_modified
                      ,`status`
                      ,start_date
                      ,end_date
                      -- 2.3 支持重跑：使用开窗函数，按id,gmt_create,gmt_modified,`status`分发，按end_date reducer内降序排序，仅取第一条数据。
                      ,ROW_NUMBER() OVER (DISTRIBUTE BY id,gmt_create,gmt_modified,`status` SORT BY end_date DESC ) AS row_num
              FROM    (
                          -- 2.1 当日失效数据：dwd_order中有被更新过的有效数据修改成失效数据。
                          SELECT  a.id AS id
                                  ,a.gmt_create AS gmt_create
                                  ,a.gmt_modified AS gmt_modified
                                  ,a.`status` AS `status`
                                  ,a.start_date AS start_date
                                  ,CASE   WHEN b.id IS NOT NULL
                                              -- AND CAST(a.end_date AS STRING) > '${biz_date}' THEN DATE'${biz_date}' 这里再考虑下用哪个合适
                                              AND CAST(a.end_date AS STRING) == '9999-12-31' THEN DATE'${biz_date}' 
                                          ELSE a.end_date
                                  END AS end_date
                          -- 2.4 调度依赖补充说明：由于当日dwd_order表数据计算依赖于自身昨日数据产出，所以我们在节点「调度配置」-「跨周期依赖」中配置了依赖「本节点」。 
                          FROM    dwd_order AS a
                          LEFT JOIN   (
                                          SELECT  id
                                                  ,gmt_create
                                                  ,gmt_modified
                                                  ,`status`
                                          FROM    ods_order_di
                                          WHERE   pt == '${biz_date}'
                                      ) b
                          ON      a.id == b.id
                          UNION ALL 
                          -- 2.2 当日新增或更新的有效数据：业务日期当日ods_order_di新增或更新的所有数据插入dwd_order状态为有效数据。
                          SELECT  id
                                  ,gmt_create
                                  ,gmt_modified
                                  ,`status`
                                  ,DATE'${biz_date}' AS start_date
                                  ,DATE'9999-12-31' AS end_date
                          FROM    ods_order_di
                          WHERE   pt == '${biz_date}'
                      ) 
          ) with_row_num_tb
  -- 支持重跑
  WHERE   row_num == 1
  ;

• 调度配置，核心配置要点如下，其他参数保持默认即可。

  • 调度参数：在调度配置 > 调度参数中，点击加载代码中的参数自动生成的参数配置结果，参数值中输入$[yyyy-mm-dd-1]，更多关于调度参数的配置说明，请参见调度参数支持的格式。

    说明

    您也可以单击用表达式定义，使用手动输入的方式添加调度参数biz_date=$[yyyy-mm-dd-1]

  • 配置跨周期依赖。由于dwd_order表的当天数据处理依赖于自身前一日的数据产出，为了确保数据处理的正确性和连续性。

    在调度配置 > 调度依赖 > 跨周期依赖（原上一周期）中勾选本节点的设置自依赖操作。通过配置自依赖，可以保障只有在上一周期的数据成功处理后，才会进行当前周期的数据加载，从而保障了数据处理流程的顺利进行。

任务开发：拉链表的使用

• 本阶段需要创建一个ODPS SQL节点，依赖上述所有任务：

  • 拉链表查询节点。

• 节点间的依赖关系，如图所示：

  

• 表定义：通过上述步骤已经完成了拉链表数据加载，通过创建查询节点，编写SQL查询语句，实现拉链存储结果的查询。

• 在拉链表查询节点编辑页面，输入如下示例代码：

  -- 1. 拉链表使用
  -- 1.1 我们仅需查询dwd_order表中end_date=9999-12-31的记录就可以得到全量最新状态的有效数据。
  SELECT  id
          ,gmt_create
          ,gmt_modified
          ,`status`
          ,start_date
          ,end_date
  FROM    dwd_order
  WHERE   end_date == DATE'9999-12-31'
  ORDER BY id DESC
  LIMIT   100
  ;
  
  -- 1.2 我们仅需查询dwd_order表中指定的订单id并按end_date排序的记录就可以得到该订单所有的历史快照。
  SELECT  id
          ,gmt_create
          ,gmt_modified
          ,`status`
          ,start_date
          ,end_date
  FROM    dwd_order
  WHERE   id == 210001
  ORDER BY end_date DESC
  LIMIT   100
  ;
  
  -- 1.3 查询dwd_order表中全量数据，并按订单id,end_date降序排序。
  SELECT  id
          ,gmt_create
          ,gmt_modified
          ,`status`
          ,start_date
          ,end_date
  FROM    dwd_order
  ORDER BY id,end_date DESC
  LIMIT   100
  ;

运行业务流程

由于上述的案例测试中，我们将3天的每日新增数据一次性分别插入指定分区"2023-10-04、2023-10-05、2023-10-06"，所以需要在生产环境执行补数据操作，实现业务数据回刷操作。运维中心补数据操作如下。

1. 提交任务。

   1. 单击工具栏中的图表。

   2. 在提交对话框中，选中需要提交的节点，输入变更描述。

   3. 单击确认，在对话框中出现操作完成，说明任务提交成功。

      如果您使用的是标准模式的工作空间，提交成功后，请单击右上方的发布。具体操作请参见发布任务。

2. 进入运维中心。

   任务发布成功后，单击右上角的运维中心。

   您也可以进入业务流程（体验案例_拉链表的实现）的编辑页面，单击工具栏中的前往运维，进入运维中心页面。

3. 周期任务执行补数据操作。

   1. 在左侧导航栏，单击周期任务运维 > 周期任务，进入周期任务页面，单击业务流程（体验案例_拉链表的实现）的起始根节点拉链表实现说明。

   2. 右键单击拉链表实现说明节点，选择补数据 > 当前节点及下游节点。

      

   3. 在补数据对话框中，选择业务日期为2023-10-04~2023-10-06，在选择需要补数据的节点中勾选所有节点，单击确定，自动跳转至补数据实例页面。

   4. 单击刷新，直至SQL任务全部运行成功即可。

结果查询

在运维中心运行完所有SQL任务后，您可以在日志中查看运行结果：

1. 在补数据示例页面的实例列表中，单击拉链表查询节点操作列的DAG图，点击查看日志。

2. 进入拉链表查询的运行日志详情页面，在运行详情 > 任务执行 > 日志中，可以查看到运行的结果。

   

附录：使用ETL工作流模板

DataWorks ETL工作流模板已内置该案例，您直接导入本案例相关代码，具体操作如下：

1. 登录DataWorks控制台，点击左侧导航栏的大数据体验 > ETL工作流模板，进入ETL工作流模板页面。

2. 在ETL工作流模板页面选择视拉链表的实现业务流程，单击查看详情进入模板页面，点击载入模板。

3. 在载入模板对话框中，选择对应的工作空间，并在MaxCompute配置中选择数据源，点击确认载入模板。

资源释放

若您在案例测试完成之后，想要清理与释放当前案例生成的资源，您可参考以下文档处理：

• 删除表：批量删除MaxCompute表。

• 下线任务：下线任务。

相关文档

• 了解虚拟节点相关操作，详情请参见：虚拟节点。

• 了解MaxCompute节点的ODPS SQL节点，详情请参见：创建并管理MaxCompute节点。

• 了解更多开发ODPS SQL任务，详情请参见：开发ODPS SQL任务。

• 了解更多运维中心的周期任务调度，详情请参见：周期任务基本运维操作。