CN114519079A - 基于元数据配置的sql自动生成系统、方法、设备及介质 - Google Patents

Abstract

本申请提供基于元数据配置的SQL自动生成系统、方法、设备及介质，包括：SQL生成自动化引擎，用于通过元数据配置信息对不同表类型进行数据类型的自动转化，并依据配置信息自动生成数据仓库表、表分区以及多表组合的业务视图。本发明提供了一种设计及自动化高质SQL生成方案，通过一套有效的元数据(Metadata)设置后，可以高质且高效地生成SQL，其主要思路是利用元数据表述一套完整基于数据库数据类型分类存放，设计不同数据库之间的数据类型映射关系，不同表之间的关系也通过元数据建立组织，开发人员通过配置而无需再手工录入SQL来完成ETL的开发，很大程度的减少人力成本并大幅提升了产品开发效率。

Description

基于元数据配置的SQL自动生成系统、方法、设备及介质

技术领域

本申请涉及数据仓库技术领域，特别是涉及基于元数据配置的SQL自动生成系统、方法、设备及介质。

背景技术

ETL是英文全称是抽取(Extract)、转换(Transform)、装载(Load)首字母缩写，是建立数据仓库的关键环节；其主要任务是按照设计者规定的业务规则和在合适的时间把数据从一个系统或系统副本通过ETL技术正确的加载到数据仓库，以备当前业务使用存在数据仓库里的有效数据。

传统的数据仓库非常依赖ETL过程，ETL开发和实施占到整合数据仓库建设周期60％以上，其中含有大量的SQL(Structure Query Language)编写及业务逻辑集成等问题，深刻困扰着数据仓库的建设周期及数据质量，行业中典型问题如下：

①不同数据库的数据类型不同，例如Oracle数据库的数据类型有Varchar2，而SQLServer数据库的数据类型有Varchar(MAX)等。

②多个表在做连接时，各个连接的条件关系较难确定。

③对表创建后的物理存储及设计需要专业人士来设计。

④不同技术水平差异导致写的用法及结构不统一，个性化较强。

⑤当有些表字段较多时，手工编写效率较低。

在行业典型数据仓库的建设过程中，如若遇到项目或产品无法如期交付的问题，归根结底是以上五点所导致的，这也是数据仓库项目或者产品建设耗时长(至少半年甚至几年)的一个重要因素。

发明内容

鉴于以上所述现有技术的缺点，本申请的目的在于提供基于元数据配置的SQL自动生成系统、方法、设备及介质，用于解决现有技术中数据仓库项目或者产品建设耗时长问题。

为实现上述目的及其他相关目的，本申请的第一方面提供一种基于元数据配置的SQL自动生成系统，包括：SQL生成自动化引擎，用于通过元数据配置信息对不同表类型进行数据类型的自动转化，并依据配置信息自动生成数据仓库表、表分区以及多表组合的业务视图。

于本申请的第一方面的一些实施例中，所述数据仓库表至少包括维度表、事实表及配置表；所述维度表包括随时间变化不明显但仍会发生变化的缓慢变化维度；所述缓慢变化维度的类型包括缓慢变化维度类型一、缓慢变化维度类型二、缓慢变化维度类型三。

于本申请的第一方面的一些实施例中，所述SQL生成自动化引擎在创建表之前将缓慢变化维度配置为类型二；当产生新的状态信息时，根据新的状态信息生成最新记录，旧的状态信息保持为旧记录；为旧纪录和最新记录配置开始日期字段和结束日期字段，且旧记录的结束日期与最新记录的开始日期衔接。

于本申请的第一方面的一些实施例中，在主键及分区处理过程中，所述SQL生成自动化引擎通过配置信息确定主键，并确定主键和不同数据库是否需要分区，并基于当前主键配置信息分区规则决定分区处理。

于本申请的第一方面的一些实施例中，所述SQL生成自动化引擎针对不同行业通用数据库及数据类型，构建不同数据类型之间的转换规则及数据类型映射关系。

于本申请的第一方面的一些实施例中，所述SQL生成自动化引擎设定命名规则；所述命名规则至少包括由特定字符作为间隔符的三段式命名规则。

于本申请的第一方面的一些实施例中，所述SQL生成自动化引擎利用一或多种表连接方式将不同业务组合生成所需的业务视图；所述表连接方式包括内连接、左连接、右连接或者全连接。

为实现上述目的及其他相关目的，本申请的第二方面提供一种基于元数据配置的SQL自动生成方法，应用于所述SQL生成自动化引擎；所述方法包括：通过元数据配置信息对不同表类型进行数据类型的自动转化，并依据配置信息自动生成数据仓库表、表分区以及多表组合的业务视图。

为实现上述目的及其他相关目的，本申请的第三方面提供一种计算机可读存储介质，其上存储有计算机程序，所述计算机程序被处理器执行时实现所述基于元数据配置的SQL自动生成方法。

为实现上述目的及其他相关目的，本申请的第四方面提供一种计算机设备，包括：处理器及存储器；所述存储器用于存储计算机程序，所述处理器用于执行所述存储器存储的计算机程序，以使所述计算机设备执行所述基于元数据配置的SQL自动生成方法。

如上所述，本申请的基于元数据配置的SQL自动生成系统、方法、设备及介质，具有以下有益效果：本发明提供了一种设计及自动化高质SQL生成方案，通过一套有效的元数据(Metadata)设置后，可以高质且高效地生成SQL，其主要思路是利用元数据表述一套完整基于数据库数据类型分类存放，设计不同数据库之间的数据类型映射关系，不同表之间的关系也通过元数据建立组织，开发人员通过配置而无需再手工录入SQL来完成ETL的开发，很大程度的减少人力成本并大幅提升了产品开发效率。

附图说明

图1显示为本申请一实施例中基于元数据配置的SQL自动生成系统的结构示意图。

图2显示为本申请一实施例中主键分区处理的示意图。

图3显示为本申请一实施例中电子邮件系统的邮件表的示意图。

图4显示为本申请一实施例中基于左连接(left join)方式的JOIN表关系配置示意图。

图5显示为本申请一实施例中配置源列和目标列自动匹配工作的示意图。

图6显示为本申请一实施例中计算机设备的结构示意图。

具体实施方式

以下通过特定的具体实例说明本申请的实施方式，本领域技术人员可由本说明书所揭露的内容轻易地了解本申请的其他优点与功效。本申请还可以通过另外不同的具体实施方式加以实施或应用，本说明书中的各项细节也可以基于不同观点与应用，在没有背离本申请的精神下进行各种修饰或改变。需说明的是，在不冲突的情况下，以下实施例及实施例中的特征可以相互组合。

需要说明的是，在下述描述中，参考附图，附图描述了本申请的若干实施例。应当理解，还可使用其他实施例，并且可以在不背离本申请的精神和范围的情况下进行机械组成、结构、电气以及操作上的改变。下面的详细描述不应该被认为是限制性的，并且本申请的实施例的范围仅由公布的专利的权利要求书所限定。这里使用的术语仅是为了描述特定实施例，而并非旨在限制本申请。空间相关的术语，例如“上”、“下”、“左”、“右”、“下面”、“下方”、“下部”、“上方”、“上部”等，可在文中使用以便于说明图中所示的一个元件或特征与另一元件或特征的关系。

在本申请中，除非另有明确的规定和限定，术语“安装”、“相连”、“连接”、“固定”、“固持”等术语应做广义理解，例如，可以是固定连接，也可以是可拆卸连接，或一体地连接；可以是机械连接，也可以是电连接；可以是直接相连，也可以通过中间媒介间接相连，可以是两个元件内部的连通。对于本领域的普通技术人员而言，可以根据具体情况理解上述术语在本申请中的具体含义。

再者，如同在本文中所使用的，单数形式“一”、“一个”和“该”旨在也包括复数形式，除非上下文中有相反的指示。应当进一步理解，术语“包含”、“包括”表明存在所述的特征、操作、元件、组件、项目、种类、和/或组，但不排除一个或多个其他特征、操作、元件、组件、项目、种类、和/或组的存在、出现或添加。此处使用的术语“或”和“和/或”被解释为包括性的，或意味着任一个或任何组合。因此，“A、B或C”或者“A、B和/或C”意味着“以下任一个：A；B；C；A和B；A和C；B和C；A、B和C”。仅当元件、功能或操作的组合在某些方式下内在地互相排斥时，才会出现该定义的例外。

为了解决现有技术中大量的SQL(Structure Query Language)编写及业务逻辑集成等问题很大程度上影响了数据仓库的建设周期及数据质量，本发明提供了一种设计及自动化高质SQL生成方案，通过一套有效的元数据(Metadata)设置后，可以高质且高效地生成SQL，其主要思路是利用元数据表述一套完整基于数据库数据类型分类存放，设计不同数据库之间的数据类型映射关系，不同表之间的关系也通过元数据建立组织，开发人员通过配置而无需再手工录入SQL来完成ETL的开发，很大程度的减少人力成本并大幅提升了产品开发效率。

为了使本发明的目的、技术方案及优点更加清楚明白，通过下述实施例并结合附图，对本发明实施例中的技术方案的进一步详细说明。应当理解，此处所描述的具体实施例仅用以解释本发明，并不用于限定发明。

如图1所示，展示了本发明一实施例中基于元数据配置的SQL自动生成系统的结构示意图。本实施例的SQL自动生成系统至少包括SQL生成自动化引擎，SQL生成自动化引擎通过元数据配置信息对不同表类型进行数据类型的自动转化，并依据配置信息自动生成数据仓库表、表分区以及多表组合的业务视图。

通常来说，数据仓库(英文名称为Data Warehouse，可简写为DW或DWH)，是为企业所有级别的决策制定过程提供所有类型数据支持的战略集合，是单个数据存储，处于分析性报告和决策支持目的而创建，也为需要业务智能的企业提供指导业务流程改进、监视时间、成本、质量极易控制等。在通常情况下，数据仓库有三类表，维度表(通常以DIM开头)、事实表(通常以DWD或者DWS开头)、配置表(通常一CTRL开头)。

所述维度表是数据仓库中非常重要的一类表，维度表中包含实时数据表中事实记录的特性，有些特性提供描述性信息，有些特性指定如何汇总事实数据表数据，以便分析者提供有用的信息，维度表包含帮助汇总数据的特性的层次结构。数据仓库维度表中随时间变化比较不明显但仍会发生变化的维度称为缓慢变化维度(slowly changing dimension，SCD)，可分为如下三种类型：

①缓慢变化维度类型一(SCD#1)：不记录历史数据，新数据覆盖旧数据。具体来说，保持业务数据和数据仓库中的数据始终保持一致，可以在Customer维度中使用来自业务数据库中的Business Key-CustomerID来追踪业务数据的变化，一旦发生变化那么就将旧的业务数据覆盖重写，即只保持当前最新的状态。

举例来说，下表一是有教师的维度表，其中有个字段是“职称”，当设置为缓慢变化维度类型一(SCD#1)时，若将教师的职称从中级升为高级，则“中级”职称会被覆盖掉，只有“高级”职称。

表一：SCD#1教师维度表

②缓慢变化维度类型二(SCD#2)：保存多条记录，直接新添一条记录，同时保留原有记录，并用单独的专用的字段保存区别。具体来说，缓慢变化维度类型二是对相对静态的历史数据进行数据的汇总和分析，因此会尽可能的维护来自业务系统中的历史数据，能够真正捕获到这种历史数据的变化。

在本实施例中，SQL生成自动化引擎在创建表之前制定缓慢变化维度SCD类型为SCD#2，当有新的状态信息时，新的信息维持为最新记录，旧的信息仍会保持为旧记录，并为旧记录和最新记录配置“开始日期”字段和“结束日期”字段，且旧记录的结束时期与最新记录的开始日期相衔接。

举例来说，下表二是有教师的维度表，其中有个字段是“职称”，当设置为缓慢变化维度类型二(SCD#2)时，若将教师的职称从中级升为高级，那么“中级”职称的时间段(包括开始日期和结束日期)及“中级”职称的记录都会保留，但最新的行以及更新为“高级”职称，通过代理建来控制，而且“中级”职称的结束日期与“高级”职称的开始日期衔接。

表二：SCD#2教师维度表

③缓慢变化维度类型三(SCD#3)：添加历史列，用不同的字段保存变化痕迹，适用于变化不超过两次的维度。例如，把要维护的历史字段新增一列，然后每次只更新当前栏(Current Column)和先前栏(Previous Column)，这样就只保存了最近两次的历史记录。

应理解的是，现有技术中的通常做法支持缓慢变化维度类型一(SCD#1)和缓慢变化维度类型二(SCD#2)，而缓慢变化维度类型三(SCD#3)由于需要改变表结构，所以通常并不采用。因此，通过配置信息，在创建表之前，需要制定缓慢变化维度SCD类型，并针对代理键和业务主键统一配置SCD属性。

所述事实表一般分为两大类：一类是贴源事实表，其英文全称是Data WarehouseDetail(DWD)，通常通过配置表可以直接生成；另一类是接近业务事实表，其英文全称是Data Warehouse Summary(DWS)，通常在贴源事实表的基础上通过连接不同的贴源事实表以快速响应业务需求。

在一些示例中，在主键及分区处理过程中，通过配置信息确定主键，并确定主键和不同数据库是否需要分区，并基于当前主键配置信息分区规则决定分区处理。在本实施例中，主键及分区处理的示意图如图2所示：表类型为Fact事实表，哈希分区键为TenantCodeX，范围分区为Range Parition，哈希分区值为2。

具体来说，表中的每一行由主键(PK)唯一确定，在创建表时必须制定组成主键的列，这些列称为主键列，主键列必须有值，并确保主键列的值的组合能够唯一地确定一行，在后续使用过程中，主键列的类型不能改变。在数据存储过程中，会自动把表分成不同的数据分区，以达到对其存储数据的负载均衡，数据分区的划分粒度为主键的第一列，该列即为数据分区键。

举例来说，图3展示的是一个电子邮件系统的邮件表的示意图，该表的主键和分区键信息如下：列UserID、Receive Time、FromAddr分别表示邮件用户的ID、接收时间、发送人，这些列为主键列，用于唯一确定一封邮件，其中UserID列为数据分区键。列ToAddr、MailSize、Subject、Read分别表示收件人、邮件大小、邮件主题和邮件是否已读，这些是普通列，存储邮件的相关信息。图中表格存储把UserID为U0001和U0002的用户信息划在同一个数据分区(数据分区一)，而将UserID为U0003和U0004的用户信息划分在另一个数据分区(数据分区二)。

在一些示例中，SQL生成自动化引擎针对不同行业通用数据库及数据类型，构建不同数据类型之间的转换规则及数据类型映射关系。通过这种方式，有效减少异构数据源之间数据转换的方式，使得不同数据之间的转换成为可能，例如把Oracle Varchar2转换为SQL Server Varchar类型就是一种常用的转换方式。

在一些示例中，本发明设定命名规则，至少包括由特定字符间隔的三段式命名规则。由于机器生成后，名字更易辨别，一般需要一个完整的命名规则来支持，最少三段式，三段式命名可由特定字符做间隔处理，例如以地划线隔开，如名称：XXX_XXX_XXX。

在一些示例中，本发明基于不同业务组合生成所需的业务视图，并在业务创建连接不同的业务表。其中，业务表连接方式包括但不限于内连接(inner join)、左连接(leftjoin)、右连接(right join)、全连接(full join)等连接。

具体来说，内连接(inner join)是在两张表进行连接查询时，只保留两张表中完全匹配的结果集。左连接(left join)是在两张表进行连接查询时，返回左表所有的行，如果右表中没有匹配，则依然会有左表的记录，右表字段用null来填充。右连接(right join)是在两张表进行连接查询时，返回右表所有的行，如果左表中没有匹配，则依然会有右表的记录，左表字段用null来填充。全连接(full join)是在两张表进行连接查询时，返回左表和右表中所有没有匹配的行。

图4展示了基于左连接(left join)方式的JOIN表关系配置示意图，在创建表之前，可点击“添加JOIN”按钮并选择对应的连接方式，例如选择左连接“LEFT JOIN”，就可以基于左连接来创建对应的业务视图，通过连接可将不同业务组合生成最终所需的业务视图。

本发明是对数据仓库建设过程ETL重要环节不同类型数据库表自动化脚本生成处理过程，能针对不同表类型，通过配置方式，解决表的数据类型自动转化，依据配置的关系，生成表和视图包含主键，维度表的代理键以及缓慢变化，表分区以及多表组合的业务视图，最终通过配置信息，使生成引擎自动生成相应的表及视图。采用本发明的技术方案后，不再需要像现有技术中那样花费一天甚至几天来设计一张有着几十个或上百个字段的表，本发明的一键生成方式生成一张表只耗费小时级别，例如如图5所示的配置源列和目标列自动匹配工作。

本发明还提供一种基于元数据配置的SQL自动生成方法，应用于SQL生成自动化引擎。所述SQL自动生成方法包括：通过元数据配置信息对不同表类型进行数据类型的自动转化，并依据配置信息自动生成数据仓库表、表分区以及多表组合的业务视图。

在一些示例中，所述方法还包括：在创建表之前将缓慢变化维度配置为类型二；当产生新的状态信息时，根据新的状态信息生成最新记录，旧的状态信息保持为旧记录；为旧纪录和最新记录配置开始日期字段和结束日期字段，且旧记录的结束日期与最新记录的开始日期衔接。其中，所述数据仓库表至少包括维度表、事实表及配置表；所述维度表包括随时间变化不明显但仍会发生变化的缓慢变化维度；所述缓慢变化维度的类型包括缓慢变化维度类型一、缓慢变化维度类型二、缓慢变化维度类型三。各类型的缓慢变化维度已于前文中做过解释，因此不再赘述。

在一些示例中，所述方法还包括：针对不同行业通用数据库及数据类型，构建不同数据类型之间的转换规则及数据类型映射关系。

在一些示例中，所述方法还包括：设定命名规则；所述命名规则至少包括由特定字符作为间隔符的三段式命名规则。

在一些示例中，所述方法还包括：利用一或多种表连接方式将不同业务组合生成所需的业务视图；所述表连接方式包括内连接、左连接、右连接或者全连接。

需说明的是，本实施例中基于元数据配置的SQL自动生成方法的实施方式，于上文中基于元数据配置的SQL自动生成系统类似，因此不再赘述。

如图6所示，展示了本发明一实施例中计算机设备的结构示意图。本实施例的计算机设备包括：处理器61、存储器62、通信器63；存储器62通过系统总线与处理器61和通信器63连接并完成相互间的通信，存储器62用于存储计算机程序，通信器63用于和其他设备进行通信，处理器61用于运行计算机程序，使计算机设备执行如上基于元数据配置的SQL自动生成方法的各个步骤。

上述提到的系统总线可以是外设部件互连标准(Peripheral ComponentInterconnect，简称PCI)总线或扩展工业标准结构(Extended Industry StandardArchitecture，简称EISA)总线等。该系统总线可以分为地址总线、数据总线、控制总线等。为便于表示，图中仅用一条粗线表示，但并不表示仅有一根总线或一种类型的总线。通信接口用于实现数据库访问装置与其他设备(例如客户端、读写库和只读库)之间的通信。存储器可能包含随机存取存储器(Random Access Memory，简称RAM)，也可能还包括非易失性存储器(non-volatile memory)，例如至少一个磁盘存储器。

上述的处理器可以是通用处理器，包括中央处理器(Central Processing Unit，简称CPU)、网络处理器(Network Processor，简称NP)等；还可以是数字信号处理器(Digital Signal Processing，简称DSP)、专用集成电路(Application SpecificIntegrated Circuit，简称ASIC)、现场可编程门阵列(Field－Programmable Gate Array，简称FPGA)或者其他可编程逻辑器件、分立门或者晶体管逻辑器件、分立硬件组件。

本发明还提供一种计算机可读存储介质，其上存储有计算机程序，所示计算机程序被处理器执行时实现所述基于元数据配置的SQL自动生成方法。

于本申请提供的实施例中，所述计算机可读写存储介质可以包括只读存储器、随机存取存储器、EEPROM、CD-ROM或其它光盘存储装置、磁盘存储装置或其它磁存储设备、闪存、U盘、移动硬盘、或者能够用于存储具有指令或数据结构形式的期望的程序代码并能够由计算机进行存取的任何其它介质。另外，任何连接都可以适当地称为计算机可读介质。例如，如果指令是使用同轴电缆、光纤光缆、双绞线、数字订户线(DSL)或者诸如红外线、无线电和微波之类的无线技术，从网站、服务器或其它远程源发送的，则所述同轴电缆、光纤光缆、双绞线、DSL或者诸如红外线、无线电和微波之类的无线技术包括在所述介质的定义中。然而，应当理解的是，计算机可读写存储介质和数据存储介质不包括连接、载波、信号或者其它暂时性介质，而是旨在针对于非暂时性、有形的存储介质。如申请中所使用的磁盘和光盘包括压缩光盘(CD)、激光光盘、光盘、数字多功能光盘(DVD)、软盘和蓝光光盘，其中，磁盘通常磁性地复制数据，而光盘则用激光来光学地复制数据。

综上所述，本申请提供基于元数据配置的SQL自动生成系统、方法、设备及介质，本发明提供了一种设计及自动化高质SQL生成方案，通过一套有效的元数据(Metadata)设置后，可以高质且高效地生成SQL，其主要思路是利用元数据表述一套完整基于数据库数据类型分类存放，设计不同数据库之间的数据类型映射关系，不同表之间的关系也通过元数据建立组织，开发人员通过配置而无需再手工录入SQL来完成ETL的开发，很大程度的减少人力成本并大幅提升了产品开发效率。所以，本申请有效克服了现有技术中的种种缺点而具高度产业利用价值。

上述实施例仅例示性说明本申请的原理及其功效，而非用于限制本申请。任何熟悉此技术的人士皆可在不违背本申请的精神及范畴下，对上述实施例进行修饰或改变。因此，举凡所属技术领域中具有通常知识者在未脱离本申请所揭示的精神与技术思想下所完成的一切等效修饰或改变，仍应由本申请的权利要求所涵盖。

Claims (10)

1.一种基于元数据配置的SQL自动生成系统，其特征在于，包括：

SQL生成自动化引擎，用于通过元数据配置信息对不同表类型进行数据类型的自动转化，并依据配置信息自动生成数据仓库表、表分区以及多表组合的业务视图。

2.根据权利要求1所述的SQL自动生成系统，其特征在于，所述数据仓库表至少包括维度表、事实表及配置表；所述维度表包括随时间变化不明显但仍会发生变化的缓慢变化维度；所述缓慢变化维度的类型包括缓慢变化维度类型一、缓慢变化维度类型二、缓慢变化维度类型三。

3.根据权利要求2所述的SQL自动生成系统，其特征在于，所述SQL生成自动化引擎在创建表之前将缓慢变化维度配置为类型二；当产生新的状态信息时，根据新的状态信息生成最新记录，旧的状态信息保持为旧记录；为旧纪录和最新记录配置开始日期字段和结束日期字段，且旧记录的结束日期与最新记录的开始日期衔接。

4.根据权利要求1所述的SQL自动生成系统，其特征在于，在主键及分区处理过程中，所述SQL生成自动化引擎通过配置信息确定主键，并确定主键和不同数据库是否需要分区，并基于当前主键配置信息分区规则决定分区处理。

5.根据权利要求1所述的SQL自动生成系统，其特征在于，所述SQL生成自动化引擎针对不同行业通用数据库及数据类型，构建不同数据类型之间的转换规则及数据类型映射关系。

6.根据权利要求1所述的SQL自动生成系统，其特征在于，所述SQL生成自动化引擎设定命名规则；所述命名规则至少包括由特定字符作为间隔符的三段式命名规则。

7.根据权利要求1所述的SQL自动生成系统，其特征在于，所述SQL生成自动化引擎利用一或多种表连接方式将不同业务组合生成所需的业务视图；所述表连接方式包括内连接、左连接、右连接或者全连接。

8.一种基于元数据配置的SQL自动生成方法，其特征在于，应用于权利要求1中所述的SQL生成自动化引擎；所述方法包括：

通过元数据配置信息对不同表类型进行数据类型的自动转化，并依据配置信息自动生成数据仓库表、表分区以及多表组合的业务视图。

9.一种计算机可读存储介质，其上存储有计算机程序，其特征在于，所述计算机程序被处理器执行时实现权利要求8所述基于元数据配置的SQL自动生成方法。

10.一种计算机设备，其特征在于，包括：处理器及存储器；

所述存储器用于存储计算机程序；

所述处理器用于执行所述存储器存储的计算机程序，以使所述计算机设备执行如权利要求8所述基于元数据配置的SQL自动生成方法。

Images