基于SQL查询模板提取数据的查询加速方法、系统及装置与流程

基于sql查询模板提取数据的查询加速方法、系统及装置
技术领域：
：1.本发明涉及大数据查询
技术领域：
：，尤其涉及一种基于sql查询模板提取数据的查询加速方法、系统及装置。
背景技术：
：：2.现有技术中，数据查询加速技术经常出现在报表场景中，因为报表查询通常需要极致的响应性能比如需要在3s内打开，除了页面渲染及其他流程的耗时外，真实的sql查询只剩下1s。目前想要实现数据查询加速的解决方案有三种：通过预计算实现；在存储计算层(比如选择列存、mpp计算引擎)上进行优化进而进行加速；或基于缓存热点查询结果，下次查询时可直接返回。3.而对于大数据量的报表来说，第二种方案存在计算并不能满足极致的性能要求；第三种方案能解决重复查询的性能问题，但是无法解决查询日期变化的场景。在较为固定的查询模式下，预计算属于更优的方案，而目前的预计算一般通过如下两种方式：打cube，比如kylin，按某些维度列对某个聚合列进行预先计算；或构建物化视图来实现。4.而目前预计算的不足之处在于：无法保证所有报表的极致查询性能，预计算的配置比如kylincube和物化视图都需要专业的数据人员支持，且需要经常进行维护，即使存在这样的数据专家，也无法保证所有报表查询的极致性能。比如kylin的cube，配置的维度列和聚合列一旦和实际的查询有差异，会存在二次计算(实际场景报告配置人员和数据模型构建配置cube的人员是独立的两个部门)，导致性能满足不了要求。构建物化视图同样存在一样的问题，且不是所有的数据源都支持物化视图。技术实现要素：5.本发明针对现有技术中的缺点，提供了一种基于sql查询模板提取数据的查询加速方法、系统及装置。6.为了解决上述技术问题，本发明通过下述技术方案得以解决：7.一种基于sql查询模板提取数据的查询加速方法，包括以下步骤：8.基于历史行为数据提取查询实例的初始sql查询模式，其中，初始sql查询模式包括动态参数及参数历史值；9.基于所述动态参数及参数历史值生成参数维度表；10.将所述初始sql查询模式和所述参数维度表按照预设模型进行改写，生成关系投影sql；11.基于动态参数调度所述关系投影sql，调度执行关系投影sql得到执行结果，将执行结果转为查询模式相应的所有查询实例，将查询实例和对应的结果一对一映射存储于hbase数据库中；12.查询待查询实例对应的结果是否存在hbase数据库内，若是，则请求hbase数据库。13.作为一种可实施方式，所述基于历史行为数据提取查询实例的初始sql查询模式，包括以下步骤：14.获取历史行为数据，所述历史行为数据中包括查询实例的所有数据；15.获取查询实例的所有数据，其中，所述所有数据包括查询实例，基于所述查询实例推断出动态参数及动态参数的历史组合值；16.对所有数据按照预设聚类模型进行聚类抽取，得到相应的初始sql查询模式，其中，预设聚类模型基于动态参数设置。17.作为一种可实施方式，所述基于所述动态参数及参数历史值生成参数维度表，包括以下步骤：18.将所述动态参数及参数历史值对应的所有数据进行提取，得到动态参数，所述动态参数包括日期动态参数和非日期动态参数，基于非日期动态参数的每个历史值组合生成唯一标识符；19.将所述非日期动态参数的每个历史值组合及唯一标识符，作为参数维度表的字段。20.作为一种可实施方式，所述调度执行所述关系投影sql得到执行结果，将所述执行结果转化为所述查询实例对应的结果，包括以下步骤：21.获取关系投影sql，将相同标识符的数据进行组合，转化为对应的查询实例sql对应的结果。22.作为一种可实施方式，所述查询待查询实例对应的结果是否存在hbase数据库内，若是，则请求hbase数据库，包括以下步骤；23.响应于待查询实例的查询请求，判断待查询实例对应的结果是否存在hbase数据库内；若存在，直接查询hbase数据库，若不存在，则直接下发到原始数据库进行数据查询。24.作为一种可实施方式，所述并存储到hbase数据库内，包括以下步骤：25.将唯一标识符将查询实例sql作为key，compose_uk为01的数据集合作为value存到kv存储hbase中。26.作为一种可实施方式，所述将初始sql查询模式和参数维度表按照预设模型进行改写，还包括以下步骤：若初始sql查询模式存在嵌套的sql子查询，则查找所有非日期动态参数动态参数所在的最近公共父节点sql子查询；27.基于最近公共父节点sql子查询的位置和参数维度表按照预设模型进行改写。28.一种基于sql查询模板提取数据的查询加速系统，包括模板提取模块、参数唯维度表生成模块、改写生成模块、调度执行模块及查询请求模块；29.所述模板提取模块，基于历史行为数据提取查询实例的初始sql查询模式，其中，初始sql查询模式包括动态参数及参数历史值；30.所述参数唯维度表生成模块，基于所述动态参数及参数历史值生成参数维度表；31.所述改写生成模块，用于将所述初始sql查询模式和所述参数维度表按照预设模型进行改写，生成关系投影sql；32.所述调度执行模块，用于根据日期参数调度所述关系投影sql，调度执行关系投影sql得到执行结果，将执行结果转为查询模式相应的所有查询实例，将查询实例和对应的结果一对一映射存储于hbase数据库中；33.所述查询请求模块，用于查询待查询实例对应的结果是否存在hbase数据库内，若是，则请求hbase数据库。34.一种计算机可读存储介质，所述计算机可读存储介质存储有计算机程序，所述计算机程序被处理器执行时实现如上所述的方法。35.一种基于sql查询模板提取数据的查询加速装置，包括存储器、处理器以及存储在所述存储器中并在所述处理器上运行的计算机程序，所述处理器执行所述计算机程序时实现如上所述的方法。36.本发明由于采用了以上技术方案，具有显著的技术效果：37.本发明通过利用sql查询模板及历史参数值，进行sql物化改写，按日期进行调度，可提前计算出所有可能会被查询的sql实例，以此实现极致的查询性能。附图说明38.为了更清楚地说明本发明实施例或现有技术中的技术方案，下面将对实施例或现有技术描述中所需要使用的附图作简单地介绍，显而易见地，下面描述中的附图仅仅是本发明的一些实施例，对于本领域普通技术人员来讲，在不付出创造性劳动性的前提下，还可以根据这些附图获得其他的附图。39.图1是本发明方法的整体流程示意图；40.图2是本发明系统的整体结构示意图。具体实施方式41.下面结合实施例对本发明做进一步的详细说明，以下实施例是对本发明的解释而本发明并不局限于以下实施例。42.实施例1：43.一种基于sql查询模板提取数据的查询加速方法，如图1所示，包括以下步骤：44.s100、基于历史行为数据提取查询实例的初始sql查询模式，其中，初始sql查询模式包括动态参数及参数历史值；45.s200、基于动态参数及参数历史值生成参数维度表；46.s300、将初始sql查询模式和参数维度表按照预设模型进行改写，生成关系投影sql；47.s400、基于动态参数调度关系投影sql，调度执行关系投影sql得到执行结果，将执行结果转为查询模式相应的所有查询实例，将查询实例和对应的结果一对一映射存储于hbase数据库中；48.s500、查询待查询实例对应的结果是否存在hbase数据库内，若是，则请求hbase数据库。49.查询模版是由sql查询场景来定的，个人信息其实不是一个固定的，只是个人信息也是个动态参数，这里可以去掉。为了解释清楚，描述多点哈，下面举个例子来理解，比如一个报表图表(其实每个图表背后对应着一个sql)，有一些下拉框(可以多选也可以单选)也可以选择日期(日期也属于下拉框)，也可以默认展示最新一天的，或者同一个页面，每个人看到的不一样，那么人也属于动态字段，但是呢，这个报告整体要展示的内容图是固定的，其实就是查询模式(也就是查询模版)固定，但是有一些动态参数会变化，比如上面描述的人员，日期，或者查看范围(比如杭州市可以看全区的数据，那西湖区的智能看西湖区的)，每天都需要打开报表看数据，那么日期也是动态的。50.在一个实施例中，基于历史行为数据提取查询实例的初始sql查询模式，包括以下步骤：51.获取历史行为数据，历史行为数据中包括查询实例的所有数据；52.获取查询实例的所有数据，其中，所有数据包括查询实例，基于查询实例推断出动态参数及动态参数的历史组合值；53.对所有数据按照预设聚类模型进行聚类抽取，得到相应的初始sql查询模式，其中，预设聚类模型基于动态参数设置。在此，动态变化参数只有两类，日期类型和其他类型(比如用户id，业务类型之类的)，可以以动态参数举例子，比如图表的下拉框就是个动态参数，可以是任意的，日期类型的是用来调度关系投影sql的，来达到提前执行出结果进行缓存的目的，非日期类型的是指同一个查询模式，比如一个图表，但是每个人看到的数据范围不一样，这里的每个人条件就是一个动态参数，查询模式对应的关系投影任务，每天调度的时候，可以一次把所有人需要的数据都查出来，然后根据参数组合，来映射到每个人查询实例sql上，来达到提前缓存的目的。54.在步骤s200中，基于动态参数及参数历史值生成参数维度表，包括以下步骤：55.将动态参数及参数历史值对应的所有数据进行提取，得到动态参数，动态参数包括日期动态参数和非日期动态参数，基于非日期动态参数的每个历史值组合生成唯一标识符；56.将非日期动态参数的每个历史值组合及唯一标识符，作为参数维度表的字段。这里是得到动态参数类型包括日期动态参数和非日期动态参数，会根据每个非日期动态参数组合，还生成唯一标识符来代表一个查询实例sql。57.在一个实施例中，调度执行关系投影sql得到执行结果，将执行结果转化为查询实例对应的结果，包括以下步骤：58.获取关系投影sql，将相同标识符的数据进行组合，转化为对应的查询实例sql对应的结果。59.在一个实施例中，查询待查询实例对应的结果是否存在hbase数据库内，若是，则请求hbase数据库，包括以下步骤；60.响应于待查询实例的查询请求，判断待查询实例对应的结果是否存在hbase数据库内；若存在，直接查询hbase数据库，若不存在，则直接下发到原始数据库进行数据查询。61.在一个实施例中，并存储到hbase数据库内，包括以下步骤：62.将唯一标识符将查询实例sql作为key，compose_uk为01的数据集合作为value存到kv存储hbase中。63.可以结合以下实例来说明：64.某个银行机构，每个客户经理每天需查询各自负责范围(包括团队人员及业务类型)的销售数据，比如某些客户经理的查询实例为：[0065][0066]通过对历史查询sql行为的聚类抽取，能够得到如下的查询模版：[0067][0068]其中，${param1}、${biz_param2}是可变化的动态参数(可理解为对应报表的下拉框或者多选框，同一个报表，不同的人看的数据范围不一样)[0069]其中，datetime为日期参数，假设报告默认每天看昨天的数据(当然时间周期可能是周或者月粒度)。[0070]上面的例子中，存在多个查询实例，即动态参数user_id和biz_type是可变的，假设组合有如下三种：[0071](1)user_id:01，02.biz_type:基金1[0072](2)user_id:01,03,04.biz_type：基金2[0073](3)user_id:05biz_type：基金3[0074]生成的参数唯维度表结构：[0075]tabledimension_template1{[0076]compose_uk,[0077]user_id,[0078]biz_type[0079]}[0080]参数唯维度表结构说明：[0081]compose_uk为每个查询实例动态参数的组合值的唯一键，比如上述的三个查询实例，会各自生成自己的组合uk值，这样做的好处是，能够按uk还原一个查询，且应用到物化sql中，能让物化sql的结果不存在二次计算，只利用uk进行结果数据聚合即可还原查询实例。[0082]参数唯维度表的数据内容如下表所示：[0083][0084][0085]当然，基金也可以是多值的，动态参数的个数不限制。[0086]当前版本的动态参数组合是从历史查询行为中得到的，因为一个报表是会被反复看的，只计算历史查询过的，会提高预计算的有效率，如果需要提前对报告进行加速，也可以查询原始表的动态参数字段的所有值，作为参数唯维度表的数据内容。根据模版sql和参数维度表进行改写，将所有的动态参数的操作符改为in(查询下推)。[0087]仍然以步骤一中的例子为例。模版为：[0088][0089]参数唯维度表为：dimension_temnplate01(列为：compose_uk，param1,param2)[0090]则改写后的物化sql为：[0091][0092][0093]当前需调度的日期为20220102，则生成的物化sql调度实例为：[0094]根据上述步骤中物化实例sql执行的结果，可以参见下表：[0095][0096][0097]将相同的dimension_template01.uk的其他列数据放到一个集合中，每个uk对应的参数组合值，还原成一个查询实例sql，比如compose_uk为01代表参数组合：param1:01，02；param2:基金1，则查询实例sql为：[0098][0099]并将查询实例sql作为key，compose_uk为01的数据集合作为value存到kv存储hbase中。[0100]查询uk为01的上述查询实例，对应的结果数据为[0101]user_id,count(col_1),sum(col_2)[0102]01ꢀꢀꢀꢀ100ꢀꢀꢀꢀ1000[0103]02ꢀꢀꢀ101ꢀꢀꢀꢀ2000[0104]接收到查询sql时，先判断该sql(去掉空格等无意义的操作符号)是否存在hbase中，如果存在，直接查hbase，如果不存在，则发送到原始的sql查询引擎中。[0105]另外，整个过程中，还包括以下步骤：[0106]若初始sql查询模式存在嵌套的sql子查询，则查找所有非日期动态参数动态参数所在的最近公共父节点sql子查询；[0107]基于最近公共父节点sql子查询的位置和参数维度表按照预设模型进行改写。[0108]在此实施例中，不管嵌套层次有多深，从最里层的动态参数层开始处理，如果没到可和参数维度表做join的位置，则动态参数加到select字段后(如果有groupby，则也需要加到groupby后)，记录参数列传递的关系，在可和参数维度表做join的位置，统一做和维度表里每个动态参数的关联关系。[0109]可和参数维度表做join的位置如下：[0110]从最里层往外逐层处理，如果当前层已经处理了所有的动态参数了，即当前层可以引用到所有的动态参数(外层没有动态参数了)，则当前层为join参数维度表的位置[0111]总结：找所有维度动态参数所在的最近公共父节点。[0112]举个例子：[0113]模版sql为：[0114][0115]通过遍历该模版sql，得到模版sql存在两层查询，最里层的查查询存在动态参数param3，最外层的查询存在动态参数param1和param2，从最里层开始处理，将动态参数列加到select后(子查询存在groupby，则groupby也需要增加)，到最外层后，发现所有的动态参数均遍历到了，则可在当前层做关联参数维度表的操作，改写后的物化sql如下：[0116]但是该物化改写方案也有一些约束限制，在实际的场景实践中，并未发现不符合物化改写要求的模版，约束限制如下：[0117]动态参数所在的where表达式中，不能包含函数，比如substr(col1)＝xx为不符合。[0118]动态参数所在的where表达式中，与其他的表达式之间的关系只能是and，不能是or，即动态参数对sql查询结果的行有决定性的关系。[0119]多值动态参数位置约束：如果出现在子查询中，且外层还有动态参数时，需要进行模版结构check：如果子查询为聚合，则外层也必须是聚合，且里层聚合只存在维度列，不存在sum等聚合列(因为多值改成了单值出现在select后如果用到了sum等聚合函数会导致聚合粒度变小)。[0120]实施例2：[0121]一种基于sql查询模板提取数据的查询加速系统，如图2所示，包括模板提取模块100、参数唯维度表生成模块200、改写生成模块300、调度执行模块400及查询请求模块500；[0122]模板提取模块100，基于历史行为数据提取查询实例的初始sql查询模式，其中，初始sql查询模式包括动态参数及参数历史值；[0123]参数唯维度表生成模块200，基于动态参数及参数历史值生成参数维度表；[0124]改写生成模块300，用于将初始sql查询模式和参数维度表按照预设模型进行改写，生成关系投影sql；[0125]调度执行模块400，用于根据日期参数调度关系投影sql，调度执行关系投影sql得到执行结果，将执行结果转为查询模式相应的所有查询实例，将查询实例和对应的结果一对一映射存储于hbase数据库中；[0126]查询请求模块500，用于查询待查询实例对应的结果是否存在hbase数据库内，若是，则请求hbase数据库。[0127]本说明书中的各个实施例均采用递进的方式描述，每个实施例重点说明的都是与其他实施例的不同之处，各个实施例之间相同、相似的部分互相参见即可。[0128]本领域内的技术人员应明白，本发明的实施例可提供为方法、装置、或计算机程序产品。因此，本发明可采用完全硬件实施例、完全软件实施例、或结合软件和硬件方面的实施例的形式。而且，本发明可采用在一个或多个其中包含有计算机可用程序代码的计算机可用存储介质(包括但不限于磁盘存储器、cd-rom、光学存储器等)上实施的计算机程序产品的形式。[0129]本发明是参照根据本发明的方法、终端设备(系统)、和计算机程序产品的流程图和/或方框图来描述的。应理解可由计算机程序指令实现流程图和/或方框图中的每一流程和/或方框、以及流程图和/或方框图中的流程和/或方框的结合。可提供这些计算机程序指令到通用计算机、专用计算机、嵌入式处理机或其他可编程数据处理终端设备的处理器以产生一个机器，使得通过计算机或其他可编程数据处理终端设备的处理器执行的指令产生用于实现在流程图一个流程或多个流程和/或方框图一个方框或多个方框中指定的功能的装置。[0130]这些计算机程序指令也可存储在能引导计算机或其他可编程数据处理终端设备以特定方式工作的计算机可读存储器中，使得存储在该计算机可读存储器中的指令产生包括指令装置的制造品，该指令装置实现在流程图一个流程或多个流程和/或方框图一个方框或多个方框中指定的功能。[0131]这些计算机程序指令也可装载到计算机或其他可编程数据处理终端设备上，使得在计算机或其他可编程终端设备上执行一系列操作步骤以产生计算机实现的处理，从而在计算机或其他可编程终端设备上执行的指令提供用于实现在流程图一个流程或多个流程和/或方框图一个方框或多个方框中指定的功能的步骤。[0132]需要说明的是：[0133]说明书中提到的“一个实施例”或“实施例”意指结合实施例描述的特定特征、结构或特性包括在本发明的至少一个实施例中。因此，说明书通篇各个地方出现的短语“一个实施例”或“实施例”并不一定均指同一个实施例。[0134]此外，需要说明的是，本说明书中所描述的具体实施例，其零、部件的形状、所取名称等可以不同。凡依本发明专利构思所述的构造、特征及原理所做的等效或简单变化，均包括于本发明专利的保护范围内。本发明所属
技术领域：
：的技术人员可以对所描述的具体实施例做各种各样的修改或补充或采用类似的方式替代，只要不偏离本发明的结构或者超越本权利要求书所定义的范围，均应属于本发明的保护范围。当前第1页12当前第1页12
技术特征：
1.一种基于sql查询模板提取数据的查询加速方法，其特征在于，包括以下步骤：基于历史行为数据提取查询实例的初始sql查询模式，其中，初始sql查询模式包括动态参数及参数历史值；基于所述动态参数及参数历史值生成参数维度表；将所述初始sql查询模式和所述参数维度表按照预设模型进行改写，生成关系投影sql；基于动态参数调度所述关系投影sql，调度执行关系投影sql得到执行结果，将执行结果转为查询模式相应的所有查询实例，将查询实例和对应的结果一对一映射存储于hbase数据库中；查询待查询实例对应的结果是否存在hbase数据库内，若是，则请求hbase数据库。2.根据权利要求1所述的基于sql查询模板提取数据的查询加速方法，其特征在于，所述基于历史行为数据提取查询实例的初始sql查询模式，包括以下步骤：获取历史行为数据，所述历史行为数据中包括查询实例的所有数据；获取查询实例的所有数据，其中，所述所有数据包括查询实例，基于所述查询实例推断出动态参数及动态参数的历史组合值；对所有数据按照预设聚类模型进行聚类抽取，得到相应的初始sql查询模式，其中，预设聚类模型基于动态参数设置。3.根据权利要求1所述的基于sql查询模板提取数据的查询加速方法，其特征在于，所述基于所述动态参数及参数历史值生成参数维度表，包括以下步骤：将所述动态参数及参数历史值对应的所有数据进行提取，得到动态参数，所述动态参数包括日期动态参数和非日期动态参数，基于非日期动态参数的每个历史值组合生成唯一标识符；将所述非日期动态参数的每个历史值组合及唯一标识符，作为参数维度表的字段。4.根据权利要求1所述的基于sql查询模板提取数据的查询加速方法，其特征在于，所述调度执行所述关系投影sql得到执行结果，将所述执行结果转化为所述查询实例对应的结果，包括以下步骤：获取关系投影sql，将相同标识符的数据进行组合，转化为对应的查询实例sql对应的结果。5.根据权利要求1所述的基于sql查询模板提取数据的查询加速方法，其特征在于，所述查询待查询实例对应的结果是否存在hbase数据库内，若是，则请求hbase数据库，包括以下步骤；响应于待查询实例的查询请求，判断待查询实例对应的结果是否存在hbase数据库内；若存在，直接查询hbase数据库，若不存在，则直接下发到原始数据库进行数据查询。6.根据权利要求5所述的基于sql查询模板提取数据的查询加速方法，其特征在于，所述并存储到hbase数据库内，包括以下步骤：将唯一标识符将查询实例sql作为key，compose_uk为01的数据集合作为value存到kv存储hbase中。7.根据权利要求1所述的基于sql查询模板提取数据的查询加速方法，其特征在于，所述将初始sql查询模式和参数维度表按照预设模型进行改写，还包括以下步骤：若初始sql
查询模式存在嵌套的sql子查询，则查找所有非日期动态参数动态参数所在的最近公共父节点sql子查询；基于最近公共父节点sql子查询的位置和参数维度表按照预设模型进行改写。8.一种基于sql查询模板提取数据的查询加速系统，其特征在于，包括模板提取模块、参数唯维度表生成模块、改写生成模块、调度执行模块及查询请求模块；所述模板提取模块，基于历史行为数据提取查询实例的初始sql查询模式，其中，初始sql查询模式包括动态参数及参数历史值；所述参数唯维度表生成模块，基于所述动态参数及参数历史值生成参数维度表；所述改写生成模块，用于将所述初始sql查询模式和所述参数维度表按照预设模型进行改写，生成关系投影sql；所述调度执行模块，用于根据日期参数调度所述关系投影sql，调度执行关系投影sql得到执行结果，将执行结果转为查询模式相应的所有查询实例，将查询实例和对应的结果一对一映射存储于hbase数据库中；所述查询请求模块，用于查询待查询实例对应的结果是否存在hbase数据库内，若是，则请求hbase数据库。9.一种计算机可读存储介质，所述计算机可读存储介质存储有计算机程序，其特征在于，所述计算机程序被处理器执行时实现如权利要求1至7任意一项所述的方法。10.一种基于sql查询模板提取数据的查询加速装置，包括存储器、处理器以及存储在所述存储器中并在所述处理器上运行的计算机程序，其特征在于，所述处理器执行所述计算机程序时实现如权利要求1至7任意一项所述的方法。

技术总结
本发明公开一种基于SQL查询模板提取数据的查询加速方法，包括以下步骤：基于历史行为数据提取查询实例的初始SQL查询模式；基于所述动态参数及参数历史值生成参数维度表；将初始SQL查询模式和参数维度表按照预设模型进行改写，生成关系投影SQL；根据日期参数调度所述关系投影SQL，调度执行关系投影SQL得到执行结果，将执行结果转为查询模式相应的所有查询实例，将查询实例和对应的结果一对一映射存储于HBASE数据库中；查询待查询实例对应的结果是否存在HBase数据库内，若是，则请求HBase数据库。本发明通过利用SQL查询模板及历史参数值，进行SQL物化改写，按日期进行调度，可提前计算出所有可能会被查询的SQL实例，以此实现极致的查询性能。的查询性能。的查询性能。


技术研发人员：徐保荣 张乐 闪冰洋
受保护的技术使用者：浙江大应科技有限公司
技术研发日：2023.04.18
技术公布日：2023/7/25