如何实现一个跨库连表SQL生成器

共计 3914 个字符，预计需要花费 10 分钟才能阅读完成。

这篇文章将为大家详细讲解有关如何实现一个跨库连表 SQL 生成器，文章内容质量较高，因此丸趣 TV 小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

一 概述

ADC（Alibaba DChain Data Converger）项目的主要目的是做一套工具，用户在前端简单配置下指标后，就能在系统自动生成的大宽表里面查询到他所需要的实时数据，数据源支持跨库并支持多种目标介质。说的更高层次一点，数据的全局实时可视化这个事情本身就是解决供应链数据“神龙效应”的有效措施。做 ADC 也是为了这个目标，整个 ADC 系统

架构解析：

初始数据来自于元数据中心。

经过元数据适配层后转换为内部格式数据。

调度中心把内部格式的数据传到计划中心，计划中心分析数据需求并建模，通过 SQL 生成器生成资源和 SQL，分别通过告警中心、对账中心设定监控标准和对账标准。

对账中心定时对账，查看数据的对齐情况。

告警中心可以针对任务错误、延迟高等情况发送报警。

资源的生命周期管控在资源管理中心下，view 删除时资源管理中心负责回收资源。

基础资源适配层主要借助集团基础资源管理能力串联阿里各类数据服务，比如阿里云 MaxComputer、Flink、阿里云 AnalyticDB 等。

其中，SQL 生成器的上游和下游主要涉及：

上游计划中心

配置指标：用户在前端配置他想看的数据有哪些。

生产原始数据：根据用户输入得到哪些表作为数据源，以及它们之间的连接关系。

下游基础资源适配层适配器

把 SQL 发布到 Flink，根据建表数据建物理表。

主要从技术角度介绍下 SQL 生成器相关的内容。

二 技术实现

在项目实施阶段，需要从需求分析、技术方案设计、测试联调几个步骤展开工作。本文重点不放在软件开发流程上，而是就设计模式选择和数据结构算法设计做下重点讲解。

需求分析

在需求分析阶段，我们明确了自动生成 SQL 模块所需要考虑的需求点，主要包含如下几点：

需要支持多个事实表（流表）、多个维度表连表，其中一个事实表是主表，其他的均为辅助表。

维表变动也应当引起最终数据库更新。

主表对辅助表为 1:1 或 N1，也就是说主表的粒度是最细的，辅表通过唯一键来和主表连接。

流表中可能存在唯一键一致的多张流表，需要通过全连接关联。唯一键不同的表之间通过左连接关联。

只有连表和 UDF，没有 groupby 操作。

要求同步延时较小，支持多种源和目标介质。由于查询压力在目标介质，所以查询 qps 没有要求。

系统流程图

明确需求后，我们把 SQL 生成器总体功能分为两块：

同步生成 SQL 和建表数据

异步发布 SQL 和建表

之所以把生成 SQL 阶段做成同步是因为同步阶段内存操作为主，如果发现数据有问题无法生成 SQL 能做到快速失败。发布阶段调用基础资源适配层需要同步等待较长时间，每个发布步骤要做到有状态记录，可回滚或者重试。所以异步实现。SQL 生成器同步阶段的整体功能细化到小模块，如下图所示：

检查阶段

检查原始数据是否有问题，无法生成 SQL 则快速失败。

参数检查：检查上游是否提供了基本的参数，比如事实表信息（可以没有维表，但是必须有事实表）。

表类型检查：检查数据来源类型是否支持。

分区字段检查：是否提供了大宽表分区字段。

连接约束：检查流表，维表连接信息是否正确。

主表唯一性约束：检查主表是否含连接信息，唯一键是否有 ETL 信息。

元数据检查：检查是否包含 HBase 配置信息。

主键修正：修正维表连接键，必须是维表的唯一键。

数据同步

同步所有原始表和原始表的连接数据（比如源表同步进来，生成 1:1 的 HBase 表）。

生成优先级队列：生成连接和发布等任务的执行优先级。

同步填充：填充源表对应的同步阶段 HBase 表数据，和对应的配置项，类型转换（比如源表是 MySQL 表，字段类型要转换为 HBase 的类型），ETL 填充，添加消息队列（通过发送消息的方式通知下游节点运行）。

重复列修剪：删除重复的列。

空白列打标：对于满足一定条件（比如不需要在大宽表展示，不是唯一键列，连接键列，保序列）的列打上空白列标识。

保序字段填充：如果上游提供了表示数据创建时间的字段，则用该字段作为数据保序字段，没有则填充系统接收到数据的时间作为保序字段。

计算阶段

生成大宽表，填充 SQL。

中间表填充：填充全连接产生的中间表。

连接关系升级：会在本文后面说明。

反向索引填充：填充“反向索引”信息。

消息填充：中间表添加消息队列（中间表更新可以触发下游节点）。

大宽表填充：填充大宽表数据。

连接链对齐：中间表和大宽表连接键对齐。

ETL 填充：填充大宽表列的 ETL 信息。

分区字段填充：填充大宽表分区字段。

SQL 填充：填充 Flink 同步表映射 SQL 语句，Flink 计算 SQL 语句，Flink 结果表映射 SQL 语句。

保存：把 SQL 和建表数据存入数据库，之后的请求可以复用已有的数据，避免重复建表。

异步发布阶段会把 SQL 语句发布到 Flink。

添加反向索引的原因

假如有 A、B 两表连接，那么连接方式为 A 表的非主键连接 B 表主键。从时序上来说可能有以下三种情况：

B 表数据先于 A 表数据多天产生

B 表数据后于 A 表数据多天产生

B 表数据和 A 表数据同时产生

下面我们就这三种情况逐一分析。

场景 1：B 表数据先于 A 表数据多天产生

我们假如 B 表数据存储于某个支持高 qps 的数据库内，我们可以直接让 A 表数据到来时直接连接此表（维表）来实现连表。

场景 2：B 表数据后于 A 表数据多天产生

这种场景比较麻烦。A 表数据先行产生，因此过早的落库，导致 B 表数据到来时即使连接 B 维表也拿不到数据。这种场景还有一个类似的场景：如果 AB 连接完成后 B 发生了更新，如何让 B 的更新体现在宽表中？

为了解决这种问题，我们增加了一个“反向索引表”。假如 A 的主键是 id，连接键是 ext_id, 那么我们可以将 ext_id 和 id 的值存储在一张表内，当 B 的数据更新时，用 B 的主键连接这种表的 ext_id 字段，拉取到所有的 A 表 id 字段，并将 A 表 id 字段重新流入 Flink。

三 设计模式

对系统整体流程有了解以后，我们再来看看系统的设计模式选择，选择设计模式时，我们考虑到数据处理相关的开发工作存在一些共性：

拆解后小功能多

小功能存在复用情况

小功能执行有严格的先后顺序

需要记录小功能运行状态，流程执行可回滚或者中断可恢复执行

由于数据处理任务的步奏比较冗长，而且由于每个阶段的结果与下阶段的执行有关系，又不能分开。

参考 PipeLine（流水线）设计模式 [2]，综合考虑后我们系统的整体设计如下图所示：

首先有一个全局的 PipeLineContainer 管理多个 pipeLine 和 pipeline context，每个 pipeline 可独立执行一个任务，比如 pipeline1 执行同步生成 sql 任务。pipeline2 执行异步发布任务。发布必须在生成 SQL 结束后执行，pipeline 有状态并且按一定顺序串联。每个 pipeline 包含多个可重用的 valve（功能）。valve 可以重用，任意组合，方便完成更多的数据处理任务（比如以后如果要支持 Tisplus dump 平台接入，则简单拼接现有的 valve 就可以）。

四 数据结构和算法问题说明

SQL 生成器关键点，就是把各个表（Meta 节点）之间的关系表示出来。Meta 之间的关系分为两类，分别是全连接关联和左连接关联（因为左连接关联涉及到数据的时序问题，需要添加反向索引较为复杂，所以和全连接区分了一下，为了简化问题我们先执行全连接，再执行左连接）。

我们要解决的问题是，多个数据源同步数据进来之后，按一定的优先级关联，最终得到一个大宽表并需要自动发布。抽象到数据结构层面就是：

每个同步进来的数据源对应一个叶子节点

节点之间有关联关系，关联关系有多类并有执行优先级

所有节点和关联关系组成一棵树

最终得到一个根节点（大宽表）并发布

算法思路

下面说明下解决该问题的算法思路。

优先级队列

因为叶子节点之间连接执行优先级不同，先放入优先级队列。之后每次取出高优先级任务执行。相同优先级任务可以复用，连续执行多次。优先级队列示意图如下：

构建树

有了优先级队列的概念，我们来构建树。构建主要分以下步骤：

1. 首先得到四种优先级的任务，优先级从高到低分别为：

优先级 1，六个节点的同步任务

优先级 2，节点 1、2、3 和节点 4、5 的 Full Join 任务

优先级 3，节点 1、4 和节点 6 的 Left Join 任务

优先级 4，发布任务

2. 取优先级 1 的任务执行，同步进来六个数据源对应六个叶子。

3. 取优先级 2 的任务并执行得到中间表 1，2。

4. 取优先级 3 的任务并执行，发现节点 1、4 有父节点，则执行中间节点 1、2 分别和节点 6 Left Join 得到根节点。

5. 取优先级 4 的任务并执行，发布根节点。

可以看到最终的数据结构是一棵树，通过这种方式我们能支持复杂 sql 的自动构建。进一步抽象，这种“一个队列驱动一棵树生成”的模式可以解决一类问题：

问题的解决由一系列不同优先级的任务组成，任务需要复用。

通过从队列取优先级高的任务的方式构建任务关系树。

最后遍历树完成各个节点任务。

限于篇幅，重点在于介绍自动生成 sql 功能开发中运用到的主要数据结构和设计模式思想。

目前我们实现了任意张表关联 sql 自动生成并发布，整体延迟控制在 2s 以内。之后 SQL 生成器主要会针对方便接入更多第三方实时计算平台（比如 Tisplus），降低整体系统延迟工作展开。方便接入主要考验的是架构的设计，也是本文着重写的点（包括数据结构和算法设计、设计模式的选择）。降低系统延迟则包括消息中间件优化，代码执行效率提升等。

关于如何实现一个跨库连表 SQL 生成器就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。