WO2019127744A1 - 一种olap数据模型自动建模的方法、分类器 - Google Patents

一种olap数据模型自动建模的方法、分类器 Download PDF

Info

Publication number
WO2019127744A1
WO2019127744A1 PCT/CN2018/073320 CN2018073320W WO2019127744A1 WO 2019127744 A1 WO2019127744 A1 WO 2019127744A1 CN 2018073320 W CN2018073320 W CN 2018073320W WO 2019127744 A1 WO2019127744 A1 WO 2019127744A1
Authority
WO
WIPO (PCT)
Prior art keywords
query
column
modes
rule
difference degree
Prior art date
Application number
PCT/CN2018/073320
Other languages
English (en)
French (fr)
Inventor
李栋
张逸凡
李扬
韩卿
Original Assignee
上海跬智信息技术有限公司
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by 上海跬智信息技术有限公司 filed Critical 上海跬智信息技术有限公司
Priority to EP18894747.7A priority Critical patent/EP3709192A4/en
Priority to US15/769,397 priority patent/US11055307B2/en
Publication of WO2019127744A1 publication Critical patent/WO2019127744A1/zh

Links

Images

Classifications

    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/21—Design, administration or maintenance of databases
    • G06F16/211—Schema design and management
    • G06F16/212—Schema design and management with details for data modelling support
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/25—Integrating or interfacing systems involving database management systems
    • G06F16/254—Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/22—Indexing; Data structures therefor; Storage structures
    • G06F16/2282—Tablespace storage structures; Management thereof
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/245—Query processing
    • G06F16/2455—Query execution
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/245—Query processing
    • G06F16/2458—Special types of queries, e.g. statistical queries, fuzzy queries or distributed queries
    • G06F16/2465—Query processing support for facilitating data mining operations in structured databases
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/25—Integrating or interfacing systems involving database management systems
    • G06F16/258—Data format conversion from or to a database
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/28—Databases characterised by their database models, e.g. relational or object models
    • G06F16/283—Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/28—Databases characterised by their database models, e.g. relational or object models
    • G06F16/284—Relational databases
    • G06F16/285—Clustering or classification
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06N—COMPUTING ARRANGEMENTS BASED ON SPECIFIC COMPUTATIONAL MODELS
    • G06N20/00—Machine learning
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06N—COMPUTING ARRANGEMENTS BASED ON SPECIFIC COMPUTATIONAL MODELS
    • G06N5/00—Computing arrangements using knowledge-based models
    • G06N5/04—Inference or reasoning models

Definitions

  • the invention belongs to the field of OLAP big data information, and particularly relates to a method and a classifier for automatic modeling of an OLAP data model.
  • the underlying architecture of OLAP analysis is a data warehouse, which contains a series of data tables.
  • Modelers design data models based on these tables based on business analysis requirements for analysts to use; ultimately, analysts' analysis operations are transformed into a series of SQL query for the data table.
  • the data model gives the data table business meaning, decoupling the relationship between the underlying data and business needs, is an integral part of the entire architecture.
  • the data scale can reach 100 billion or trillion, and the number of dimensions is too large, the business scenario is complex and changeable, which increases the difficulty of data modeling; the use of OLAP Cube does not recommend frequent data models. The changes have increased the difficulty of trial and error in data modeling. These all pose great challenges for the modelers. How to implement automatic modeling through computer algorithms to reduce the cost of manual modeling is particularly important.
  • the technical problem to be solved by the present invention is that the existing OLAP model is extremely dependent on the artificial understanding of the data table and the business requirements, and the modeling efficiency is low, the cost is large, and automatic modeling cannot be realized.
  • the present invention provides a method for automatically modeling an OLAP data model, the method comprising:
  • the beneficial effects of the present invention through the above method, parsing and extracting features of the input SQL sample, and finding patterns required by the data model; then clustering and merging these patterns to generate all required data models,
  • the generated data model fully supports all input SQL and has certain generalization capabilities.
  • the generated data model can fully support the Cube operation and fast query, and ensure that the Cube expansion rate is within 10 times, and the automatic modeling effectively reduces the user's learning difficulty and trial and error costs, and optimizes the user experience.
  • the column information refers to all columns used in the SQL query statement, wherein each column includes: a column name of the column, a table in which the column is located, a type of the column, and a number of times the column appears.
  • the table information refers to all the tables used in the SQL query statement, wherein each table includes: a table name of the table, a type of the table, and associated information of the table and the fact table.
  • classifying the at least N groups of query modes in the S4 includes:
  • S41 Perform a difference judgment on the at least N groups of query modes by using a static rule, where the static rule refers to calculating a difference degree of the same table in any two sets of query modes, and determining whether the difference degree of the table is greater than the first The preset threshold, if greater, the two sets of query modes cannot be classified into one category;
  • the difference degree of the table After the difference degree of the table is calculated, the difference degree of the same column in any two sets of query modes is calculated, and it is determined whether the difference degree of the column is greater than the first preset threshold. one type;
  • the at least N groups of query modes are clustered by using a statistical rule and a preset learning rule.
  • the clustering of the at least N groups of query modes by using the statistical rule and the preset learning rule in the S42 includes:
  • S421 Calculate a feature vector of each group of query modes according to column information and table information used in each group of query modes;
  • the preset learning rule refers to determining whether the clustering result meets a preset criterion after clustering. If not, the supervised machine learning algorithm is used to adjust the clustering result, and the adjusted aggregate is recorded. Class result.
  • converting the mode relationship tree into a corresponding data model in the S6 includes:
  • the column information in the pattern relationship tree is converted into column information of the corresponding data model.
  • the converting the column information in the mode relationship tree into the column information of the corresponding data model further includes:
  • PartScore(i) PartFunc(Score(i), Stats(i)), where PartFunc() is the scoring function, Score(i) is the score in column (i) of each set of query patterns, and Stats(i) is the first ( i) The characteristic statistics of the column, when the (i)th column partition score PartScore(i) exceeds the predetermined scoring threshold, the (i)th column is set as the partition column.
  • the invention also relates to a classifier, comprising: a static rule classification module, a statistical rule and a preset learning rule classification module;
  • the static rule classification module is configured to perform a difference judgment on at least N groups of query modes by using a static rule, where the static rule refers to calculating a difference degree of the same table in any two sets of query modes, and determining a difference of the table. Whether the degree is greater than the first preset threshold, if greater than, the two sets of query modes cannot be classified into one category, where N is a natural number greater than or equal to 1;
  • the difference degree of the table After the difference degree of the table is calculated, the difference degree of the same column in any two sets of query modes is calculated, and it is determined whether the difference degree of the column is greater than the first preset threshold. one type;
  • the statistical rule and the preset learning rule classification module are configured to cluster the at least N groups of query modes by using a statistical rule and a preset learning rule after performing the difference judgment.
  • the preset learning rule refers to determining whether the clustering result meets a preset criterion after clustering. If not, the supervised machine learning algorithm is used to adjust the clustering result, and the adjusted aggregate is recorded. Class result.
  • FIG. 1 is a flow chart of a method for automatically modeling an OLAP data model according to the present invention
  • FIG. 2 is a schematic structural view of a classifier of the present invention.
  • a method for automatically modeling an OLAP data model comprising:
  • step S1 is to perform pre-checking on the obtained SQL query statement, so that it can test whether each input SQL query statement can effectively function in a subsequent stage
  • step S2 adopts SQL syntax.
  • the parser parses the input SQL query statement to determine whether there is a lexical or grammatical error in each SQL query statement; if it exists, it gives an explicit error prompt to guide the user to correct the SQL query statement, if all the verification passes, It is allowed to proceed to the next step S3.
  • step S3 the input SQL query statement is simulated.
  • the engine does not return any meaningful query results. Instead, the analysis results of the query plan are collected and converted into a set of query modes during execution.
  • a lot of SQL will contain: multiple sub-queries (such as SQL containing sub-queries, each sub-query and main query corresponds to a context (ie query structure)), each context will generate a query mode; therefore a SQL may generate multiple query patterns. For example: If the input SQL query statement is:
  • Col(0) sellers.seller_name-name(0): seller_name, tbl(0): sellers, Cat(0): dimension, Score(0): calculated based on the number of occurrences 1, using by group.
  • Col(1) order.location-name(1): location
  • tbl(1) order
  • Cat(1) dimension
  • Score(1) calculated based on the number of occurrences 1, using by group.
  • Col(2) order.price-name(2): price
  • tbl(2) order
  • Cat(2) metric
  • Score(2) calculated based on the number of occurrences 1, usage (metric).
  • Col(3) order.time-name(3): time
  • tbl(3) order
  • Cat(3) dimension
  • Score(3) calculated based on the number of occurrences 1, the use of the filter.
  • the query structure is extracted from the SQL query statement to obtain at least N sets of query modes.
  • step S4 at least N sets of query modes are classified
  • Col(0) sellers.seller_name-name(0): seller_name, tbl(0): sellers, Cat(0): dimension, Score(0): calculated based on the number of occurrences 1, using by group.
  • Col(1) order.location-name(1): location
  • tbl(1) order
  • Cat(1) dimension
  • Score(1) calculated based on the number of occurrences 1, using by group.
  • Col(2) order.price-name(2): price
  • tbl(2) order
  • Cat(2) metric
  • Score(2) calculated based on the number of occurrences 1, usage (metric).
  • Col(0) order.time-name(0): time
  • tbl(0) order
  • Cat(0) dimension
  • Score(0) based on the number of occurrences 1, the use of the filter (filter);
  • Tbl(0) order-Name(0): order, Cat(0): fact table;
  • step S5 the query patterns in each class are merged to obtain a corresponding pattern relationship tree, and the following merge process is to take a union:
  • Col(0) sellers.seller_name-name(0): seller_name, tbl(0): sellers, Cat(0): dimension, Score(0): based on the number of occurrences 1, the use of (group by);
  • Col(1) order.location-name(1):location,tbl(1):order,Cat(1): dimension, Score(1): based on the number of occurrences 1, the use of (group by);
  • Col(2) order.price-name(2): price
  • tbl(2) order
  • Cat(2) metric
  • Score(2) calculated based on the number of occurrences 1, usage (metric);
  • Col(3) order.time-name(3):time,tbl(3):order,Cat(3): dimension, Score(3): based on the number of occurrences 1, the use of the filter (filter);
  • Col(4) buyers.buyer_name-name(4): buyer_name, tbl(4): buyers, Cat(4): dimension, Score(4): based on the number of occurrences 1, use by (group by);
  • step S6 the query mode is classified in the previous step in step S6, and a pattern relationship tree is generated. Each pattern relationship tree will generate a data model at this step.
  • the process from the schema relation tree to the data model is mainly data conversion, that is, the table information in the pattern relationship tree (such as fact table, dimension table, JOIN, etc.) is directly converted into table information in the data model; similarly, the pattern relationship tree Column information (dimensions, measures, computed columns, and so on) is directly translated into column information in the data model. So after a successful set of data models is created, this set of data models can collectively support the input set of SQL queries and the business analysis needs behind those SQLs.
  • the table information in the pattern relationship tree such as fact table, dimension table, JOIN, etc.
  • Column information dimensions, measures, computed columns, and so on
  • the column information refers to all columns used in the SQL query statement, wherein each column includes: a column name of the column, a table in which the column is located, and a type of the column. And the number of times the column appears.
  • the table information refers to all the tables used in the SQL query statement, wherein each table includes: a table name of the table, a type of the table, and associated information of the table and the fact table.
  • Col(i) column information of all the columns used in the SQL query statement in the second embodiment is denoted as Col(i), and includes all the columns used in the context.
  • Name(i) represents the column name of this column
  • Tab(i) represents the table in which the column is located
  • Cat(i) represents the type of the column, such as dimensions, metrics, computed columns, etc.
  • Score( i) represents a score calculated based on the number of occurrences, usage, etc. in this column.
  • Table information Recorded as Tab(j), containing all the tables used in the context.
  • Name(j) represents the table name of the table
  • Cat(j) represents the type of the table, such as fact table, dimension table, etc.
  • Join(j) represents the JOIN association of this table and the fact table.
  • Information including JOIN type, JOIN condition.
  • classifying the at least N groups of query modes in S4 in another embodiment 3 includes:
  • S41 Perform a difference judgment on the at least N groups of query modes by using a static rule, where the static rule refers to calculating a difference degree of the same table in any two sets of query modes, and determining whether the difference degree of the table is greater than the first The preset threshold, if greater, the two sets of query modes cannot be classified into one category;
  • the difference degree of the table After the difference degree of the table is calculated, the difference degree of the same column in any two sets of query modes is calculated, and it is determined whether the difference degree of the column is greater than the first preset threshold. one type;
  • the at least N groups of query modes are clustered by using a statistical rule and a preset learning rule.
  • the general query mode classification includes a plurality of rules, wherein the static rules include a series of mutually exclusive rules, that is, two modes that meet certain conditions cannot be classified into one class. mainly includes:
  • the difference degree Diff(i,j) is calculated for the same column in the two query modes. If the degree of difference of the column is greater than the set threshold, the two query modes cannot be classified into one class.
  • the at least N groups of query modes are clustered by using a statistical rule and a preset learning rule.
  • clustering the at least N groups of query modes by using a statistical rule and a preset learning rule in the S42 in another embodiment 4 includes:
  • S421 Calculate a feature vector of each group of query modes according to column information and table information used in each group of query modes;
  • the feature vector is calculated based on all the column information and the table information of the query mode, for example:
  • X(i) (col1, col2, col3, ...colN, tab1, tab2,...,tabM), where each colX represents the score Score(X) of the Col(X) column, and each tabY represents the Tab(Y) table. Score Score(Y).
  • the preset learning rule refers to determining whether the clustering result meets a preset criterion after clustering, and if not, using a supervised machine learning algorithm to perform clustering results. Adjust and record the adjusted cluster results.
  • the learning rule “static rule” and “statistical rule” generate a recommendation result, and the user can manually adjust or correct the classification result; for example, some query modes are “ After the static rule and the "statistical rule” are judged, they can be classified into one category, but the user manually modifies from the perspective of business and the like to be inseparable.
  • the “learning rules” will save all the final classification results after the user has confirmed, and use the supervised machine learning to train the appropriate classification model to predict whether a group of patterns can be classified into one category, and based on this prediction, the clustering results. Make adjustments.
  • the query mode in each class is merged in the S5, and the corresponding mode relationship tree is obtained:
  • the structure formed by the merged query pattern of each class contains a set of tables and their JOIN relationship trees, and also defines the selected columns on each table.
  • Such a structure is called a schema relation tree.
  • converting the mode relationship tree into a corresponding data model in the S6 in another embodiment 7 includes:
  • the column information in the pattern relationship tree is converted into column information of the corresponding data model.
  • converting the column information in the mode relationship tree into the column information of the corresponding data model in another embodiment 8 further includes:
  • PartScore(i) PartFunc(Score(i), Stats(i)), where PartFunc() is the scoring function, Score(i) is the score in column (i) of each set of query patterns, and Stats(i) is the first ( i) The characteristic statistics of the column (ie, the statistical value of the data feature), when the (i)th column partition score PartScore(i) exceeds the predetermined score threshold, the (i)th column is set as the partition column.
  • Embodiment 9 of the present invention further relates to a classifier, including: a static rule classification module, a statistical rule, and a preset learning rule classification module;
  • the static rule classification module is configured to perform a difference judgment on at least N groups of query modes by using a static rule, where the static rule refers to calculating a difference degree of the same table in any two sets of query modes, and determining a difference of the table. Whether the degree is greater than the first preset threshold, if greater than, the two groups of query modes cannot be classified into one category;
  • the difference degree of the table After the difference degree of the table is calculated, the difference degree of the same column in any two sets of query modes is calculated, and it is determined whether the difference degree of the column is greater than the first preset threshold. one type;
  • the statistical rule and the preset learning rule classification module are configured to cluster the at least N groups of query modes by using a statistical rule and a preset learning rule after performing the difference judgment.
  • the classifier is used to classify multiple query modes, and the query mode classification includes three rules: a static rule: a series of mutually exclusive rules, that is, two modes that meet certain conditions. Can not be divided into one category.
  • the method mainly includes: calculating a difference degree Diff(i, j) for the same table in the two modes, and if the degree of difference is greater than a set threshold, the two modes are not classified into one class;
  • the difference degree Diff(i,j) is calculated for the same column in the two query modes, and if the degree of difference is greater than the set threshold, the two query modes cannot be classified into one class;
  • the merge rules are:
  • each type of query pattern is merged to form a structure containing a set of tables and their JOIN relationship tree, and also defines the selected column on each table.
  • a structure is called a pattern relationship tree.

Landscapes

  • Engineering & Computer Science (AREA)
  • Theoretical Computer Science (AREA)
  • Databases & Information Systems (AREA)
  • Physics & Mathematics (AREA)
  • Data Mining & Analysis (AREA)
  • General Engineering & Computer Science (AREA)
  • General Physics & Mathematics (AREA)
  • Software Systems (AREA)
  • Computational Linguistics (AREA)
  • Mathematical Physics (AREA)
  • Evolutionary Computation (AREA)
  • Computing Systems (AREA)
  • Artificial Intelligence (AREA)
  • Computer Vision & Pattern Recognition (AREA)
  • Medical Informatics (AREA)
  • Fuzzy Systems (AREA)
  • Probability & Statistics with Applications (AREA)
  • Information Retrieval, Db Structures And Fs Structures Therefor (AREA)

Abstract

本发明涉及一种OLAP数据模型自动建模的方法、分类器，该方法包括：获取SQL查询语句；对SQL查询语句进行解析，判断该SQL查询语句中是否存在语法或者词法错误；若不存在，则对该SQL查询语句进行查询结构抽取，得到至少N组查询模式；对该至少N组查询模式进行分类；将每类中的查询模式进行合并，得到对应的模式关系树；将该模式关系树转换成对应的数据模型。还涉及一种分类器，该分类器包括：静态规则分类模块、统计规则和预设学习规则分类模块。通过本发明所生成的数据模型既能完全支持所有的输入SQL，同时又具有一定的泛化能力还可以完全支持Cube操作和快速查询有效降低了用户的学习难度和试错成本。

Description

一种OLAP数据模型自动建模的方法、分类器 技术领域
本发明属于OLAP大数据信息领域，尤其涉及一种OLAP数据模型自动建模的方法、分类器。
背景技术
在当今数据化的时代，如何通过OLAP分析海量、复杂的数据以辅助商业决策，是商务智能和数据分析领域的重要课题；而数据模型就是OLAP分析的基础。
一般的，OLAP分析的架构底层是数据仓库，包含一系列数据表；建模人员根据业务分析需求，基于这些表设计数据模型供分析人员使用；最终，分析人员的分析操作都会转化为一系列针对数据表的SQL查询。数据模型给数据表赋予了业务含义，解耦了数据底层和业务需求的关系，是整个架构中不可或缺的一部分。
在大数据多维分析场景中，数据规模可达千亿或万亿级别，且维度数量过多、业务场景复杂多变，增大了数据建模的难度；OLAP Cube的使用不建议数据模型有频繁的改动，又增大了数据建模的试错难度。这些都给建模人员带来了较大挑战，如何通过计算机算法实现自动建模以减少人工建模的代价，显得尤为重要。
发明内容
本发明所要解决的技术问题是：现有的OLAP模型极度依赖人工对数据表和业务需求的理解而实现，建模效率较低、成本较大，无法实现自动建模。
为解决上面的技术问题，本发明提供了一种OLAP数据模型自动建模的 方法，该方法包括：
S1，获取SQL查询语句；
S2，对所述SQL查询语句进行解析，根据解析结果判断所述SQL查询语句是否存在语法或者词法错误；
S3，若不存在，则根据所述SQL查询语句使用到的与源数据相关的列信息和表信息，对所述SQL查询语句进行查询结构抽取，得到至少N组查询模式，其中N为大于等于1的自然数；
S4，对所述至少N组查询模式进行分类；
S5，将每类中的查询模式进行合并，得到对应的模式关系树；
S6，将所述模式关系树转换成对应的数据模型。
本发明的有益效果：通过上述的方法，对输入的SQL样例进行解析并抽取特征，寻找出数据模型所需的模式；再对这些模式进行聚类、合并，生成所需的所有数据模型，所生成的数据模型既能完全支持所有的输入SQL，同时又具有一定的泛化能力。另外所生成的数据模型可以完全支持Cube操作和快速查询，并保证Cube膨胀率在10倍以内，且该自动建模有效降低了用户的学习难度和试错成本，优化了用户体验。
进一步地，所述列信息是指所述SQL查询语句中使用的所有列，其中每列中包括：该列的列名、该列所在的表、该列的类型和该列出现的次数。
所述表信息是指所述SQL查询语句中使用的所有表，其中每表中包括：该表的表名、该表的类型、该表和事实表的关联信息。
进一步地，所述S4中对所述至少N组查询模式进行分类包括：
S41，采用静态规则对所述至少N组查询模式进行差异判断，其中，所述静态规则是指计算任意两组查询模式中相同的表的差异度，判断所述表的差异度是否大于第一预设阈值，若大于，则两组查询模式不可分为一类；
且在计算完表的差异度后，计算任意两组查询模式中相同的列的差异度，判断所述列的差异度是否大于所述第一预设阈值，若大于则两组查询模式不可分为一类；
S42，在进行差异判断后，采用统计规则和预设学习规则对所述至少N组查询模式进行聚类。
进一步地，所述S42中采用统计规则和预设学习规则对所述至少N组查询模式进行聚类包括：
S421，根据每组查询模式中使用的列信息和表信息，计算每组查询模式的特征向量；
S422，根据无监督机器学习中的聚类算法和所述预设学习规则对所有的所述特征向量进行聚类。
进一步地，所述预设学习规则是指在聚类后，判断聚类结果是否符合预设标准，若不符合，则采用有监督机器学习算法对聚类结果进行调整，并记录调整后的聚类结果。
进一步地，所述S5中将每类中的查询模式进行合并，得到对应的模式关系树包括：
计算每类中任意两组查询模式中相同的表的差异度，判断所述表的差异度是否小于第二预设阈值，若小于，则将两组查询模式合并，得到对应的模式关系树；
且在计算完每类中表的差异度后，计算每类中任意两组查询模式中相同的列的差异度，判断所述列的差异度是否小于所述第二预设阈值，若小于，则将两组查询模式合并，得到对应的模式关系树。
进一步地，所述S6中将所述模式关系树中转换成对应的数据模型包括：
将所述模式关系树中的表信息转换成对应的所述数据模型的表信息；
将所述模式关系树中的列信息转换成对应的所述数据模型的列信息。
进一步地，所述将所述模式关系树中的列信息转换成对应的所述数据模 型的列信息还包括：
对所述模式关系树中的列进行分区评分，确定所述数据模型的分区列，其中对所述模式关系树中的列进行分区评分的计算公式为：
PartScore(i)＝PartFunc(Score(i),Stats(i))，其中PartFunc()是评分函数，Score(i)是每组查询模式上第(i)列评分，Stats(i)是第(i)列的特征统计值，当第(i)列分区评分PartScore(i)超过预定评分阈值，则将第(i)列设置为分区列。
本发明还涉及一种分类器，该分类器包括：静态规则分类模块、统计规则和预设学习规则分类模块；
所述静态规则分类模块，用于采用静态规则对至少N组查询模式进行差异判断，其中，所述静态规则是指计算任意两组查询模式中相同的表的差异度，判断所述表的差异度是否大于第一预设阈值，若大于，则两组查询模式不可分为一类，其中N为大于等于1的自然数；
且在计算完表的差异度后，计算任意两组查询模式中相同的列的差异度，判断所述列的差异度是否大于所述第一预设阈值，若大于则两组查询模式不可分为一类；
所述统计规则和预设学习规则分类模块，用于在进行差异判断后，采用统计规则和预设学习规则对所述至少N组查询模式进行聚类。
本发明的有益效果：通过上述的分类器，可以将多个查询模式进行分类，且也提高了分类的精度。
进一步地，所述预设学习规则是指在聚类后，判断聚类结果是否符合预设标准，若不符合，则采用有监督机器学习算法对聚类结果进行调整，并记录调整后的聚类结果。
附图说明
图1为本发明的一种OLAP数据模型自动建模的方法的流程图；
图2为本发明的一种分类器的结构示意图。
具体实施方式
以下结合附图对本发明的原理和特征进行描述，所举实例只用于解释本发明，并非用于限定本发明的范围。
如图1所示，一种OLAP数据模型自动建模的方法，该方法包括：
S1，获取SQL查询语句；
S2，对所述SQL查询语句进行解析，根据解析结果判断所述SQL查询语句是否存在语法或者词法错误；
S3，若不存在，则根据所述SQL查询语句使用到的与源数据相关的列信息和表信息，对所述SQL查询语句进行查询结构抽取，得到至少N组查询模式，其中N为大于等于1的自然数；
S4，对所述至少N组查询模式进行分类；
S5，将每类中的查询模式进行合并，得到对应的模式关系树；
S6，将所述模式关系树转换成对应的数据模型。
可以理解的是，在本实施例中步骤S1是对获取的SQL查询语句进行预检，这样可以测试每条输入的SQL查询语句是否能够有效地在后续阶段发挥作用，步骤S2中是采用SQL语法解析器对输入SQL查询语句进行解析，判断每条SQL查询语句是否存在词法、语法错误；如果存在，则给出明确的错误提示引导用户对该SQL查询语句进行修正，如果全部校验通过，则允许进入下一步骤S3。
在步骤S3中是对输入SQL查询语句进行模拟执行，这个引擎并不返回任何有意义的查询结果，而是在执行过程中对查询计划的分析结果进行收集，并转换为一组查询模式。需要说明的是，很多SQL会包含：多个子查询(如包含子查询的SQL，每个子查询以及主查询都对应一个上下文(即查询 结构))，每个上下文会生成一份查询模式；因此一条SQL可能生成多个查询模式。比如：如果输入SQL查询语句是：
select sellers.seller_name,order.location,sum(order.price)from order join sellers on sellers.id＝order.seller_id where order.time＝20120101group by sellers.seller_name,order.location，针对该条SQL查询语句其对应的抽取出的查询模式结构是：
列信息：
Col(0):sellers.seller_name-name(0):seller_name,tbl(0):sellers,Cat(0):维度,Score(0):基于出现次数1、使用用途(group by)计算。
Col(1):order.location-name(1):location,tbl(1):order,Cat(1):维度,Score(1):基于出现次数1、使用用途(group by)计算。
Col(2):order.price-name(2):price,tbl(2):order,Cat(2):度量,Score(2):基于出现次数1、使用用途(度量)计算。
Col(3):order.time-name(3):time,tbl(3):order,Cat(3):维度,Score(3):基于出现次数1、使用用途(filter)计算。
表信息：
Tbl(0):order-Name(0):order,Cat(0):事实表
Tbl(1):sellers-Name(1):sellers,Cat(1):维度表,join(1):order join sellers on sellers.id＝order.seller_id。
根据上述的列信息和表信息，对所述SQL查询语句进行查询结构抽取，得到至少N组查询模式。
其次，在步骤S4中就是对至少N组查询模式进行分类；
如下比如：假设有两个查询模式被分为一类：
模式1：
列信息：
Col(0)：sellers.seller_name-name(0):seller_name,tbl(0):sellers,Cat(0)：维度,Score(0):基于出现次数1、使用用途(group by)计算。
Col(1)：order.location-name(1):location,tbl(1):order,Cat(1):维度,Score(1):基于出现次数1、使用用途(group by)计算。
Col(2):order.price-name(2):price,tbl(2):order,Cat(2):度量,Score(2):基于出现次数1、使用用途(度量)计算。
表信息：
Tbl(0):order-Name(0):order,Cat(0):事实表
Tbl(1):sellers-Name(1):sellers,Cat(1):维度表,join(1):order join sellers on sellers.id＝order.seller_id
模式2：
列信息：
Col(0):order.time-name(0):time,tbl(0):order,Cat(0):维度,Score(0):基于出现次数1、使用用途(filter)计算；
Col(1):buyers.buyer_name-name(1):buyer_name,tbl(1):buyers,
Cat(1):维度,Score(1):基于出现次数1、使用用途(group by)计算；
表信息：
Tbl(0):order-Name(0):order,Cat(0):事实表；
Tbl(1):buyers-Name(1):buyers,Cat(1):维度表,join(1):order join buyers on buyers.id＝order.buyer_id；
在步骤S5中将将每类中的查询模式进行合并，得到对应的模式关系树，如下合并过程就是取并集：
列信息：
Col(0):sellers.seller_name-name(0):seller_name,tbl(0):sellers,Cat(0):维度,Score(0):基于出现次数1、使用用途(group by)计算；
Col(1):order.location-name(1):location,tbl(1):order,Cat(1):维度,Score(1):基于出现次数1、使用用途(group by)计算；
Col(2):order.price-name(2):price,tbl(2):order,Cat(2):度量,Score(2):基于出现次数1、使用用途(度量)计算；
Col(3):order.time-name(3):time,tbl(3):order,Cat(3):维度,Score(3):基于出现次数1、使用用途(filter)计算；
Col(4):buyers.buyer_name-name(4):buyer_name,tbl(4):buyers,Cat(4):维度,Score(4):基于出现次数1、使用用途(group by)计算；
表信息：
Tbl(0):order-Name(0):order,Cat(0):事实表
Tbl(1):sellers-Name(1):sellers,Cat(1):维度表,join(1):order join sellers on sellers.id＝order.seller_id
Tbl(2):buyers-Name(2):buyers,Cat(2):维度表,join(2):order join buyers on buyers.id＝order.buyer_id。
最后，在步骤S6中上一步对查询模式进行了分类，并生成了模式关系树。每一个模式关系树会在该步骤生成一个数据模型。
从模式关系树到数据模型的过程主要是数据转换，即模式关系树中的表信息(如事实表、维度表、JOIN等)直接转换为数据模型中的表信息；同理，即模式关系树中的列信息(维度、度量、计算列等)直接转换为数据模型中的列信息。因此一组数据模型创建成功后，这一组数据模型就能够共同支持输入的一组SQL查询，以及这些SQL背后的业务分析需求。
可选地，在另一实施例2中所述列信息是指所述SQL查询语句中使用的所有列，其中每列中包括：该列的列名、该列所在的表、该列的类型和该列出现的次数。
所述表信息是指所述SQL查询语句中使用的所有表，其中每表中包括：该表的表名、该表的类型、该表和事实表的关联信息。
可以理解的是，在本实施例2中所述SQL查询语句中使用的所有列的列信息：记为Col(i)，包含上下文中用到的所有列。对于每一列保存如下记录：Name(i)代表这一列的列名；Tab(i)代表这一列所在的表；Cat(i)代表这一列的类型，如维度、度量、计算列等；Score(i)代表根据这一列出现次数、用途等计算的评分。表信息：记为Tab(j)，包含上下文中用到的所有表。对于每个表保存如下记录：Name(j)代表这个表的表名；Cat(j)代表这个表的类型，如事实表、维度表等；Join(j)代表这个表和事实表的JOIN关联信息，包括JOIN类型、JOIN条件。
可选地，在另一实施例3中所述S4中对所述至少N组查询模式进行分类包括：
S41，采用静态规则对所述至少N组查询模式进行差异判断，其中，所述静态规则是指计算任意两组查询模式中相同的表的差异度，判断所述表的差异度是否大于第一预设阈值，若大于，则两组查询模式不可分为一类；
且在计算完表的差异度后，计算任意两组查询模式中相同的列的差异度，判断所述列的差异度是否大于所述第一预设阈值，若大于则两组查询模式不可分为一类；
S42，在进行差异判断后，采用统计规则和预设学习规则对所述至少N组查询模式进行聚类。
可以理解的是，在本实施例3中由于一个数据模型需要服务于多个查询，因此需要对查询模式进行分类，以期望每一类的查询模式能共同生成一个支持这些查询的数据模型。一般查询模式分类包括多种规则，其中静态规则：包含一系列互斥规则，即符合某些条件的两个模式不可分为一类。主要包括:
对两个查询模式中相同的表计算差异度Diff(i,j)，如果表的差异度大于设定阈值，则两个查询模式不可分为一类；
对两个查询模式中相同的列计算差异度Diff(i,j)，如果列的差异度大于设定阈值，则两个查询模式不可分为一类。
在进行差异判断后，采用统计规则和预设学习规则对所述至少N组查询模式进行聚类。
可选地，在另一实施例4中所述S42中采用统计规则和预设学习规则对所述至少N组查询模式进行聚类包括：
S421，根据每组查询模式中使用的列信息和表信息，计算每组查询模式的特征向量；
S422，根据无监督机器学习中的聚类算法和所述预设学习规则对所有的所述特征向量进行聚类。
可以理解的是，在上述实施例3中经过静态规则的判断后，基于查询模式的所有列信息和表信息计算特征向量，比如：
X(i)＝(col1,col2,col3,…colN,tab1,tab2,…,tabM)，其中每一个colX代表Col(X)列的评分Score(X)，每一个tabY代表Tab(Y)表的评分Score(Y)。然后使用无监督机器学习中的聚类算法对这些特征向量进行聚类，最终把所有的查询模式分为了若干类。
可选地，在另一实施例5中所述预设学习规则是指在聚类后，判断聚类结果是否符合预设标准，若不符合，则采用有监督机器学习算法对聚类结果进行调整，并记录调整后的聚类结果。
可以理解的是，在本实施例5中学习规则：“静态规则”和“统计规则”生成的是建议结果，用户可以在得到分类结果后进行手动调整或修正；例如，某些查询模式经过“静态规则”和“统计规则”之后被判断可以分为一类，但用户从业务等角度出发手动修改为不可分为一类。“学习规则”会保存所有经过用户确认过之后的最终分类结果，并使用有监督机器学习训练合适的分类模型，对一组模式是否可以分为一类进行预测，并基于此预测对聚类结果进行调整。
可选地，在另一实施例6中所述S5中将每类中的查询模式进行合并，得到对应的模式关系树包括：
计算每类中任意两组查询模式中相同的表的差异度，判断所述表的差异度是否小于第二预设阈值，若小于，则将两组查询模式合并，得到对应的模式关系树；
且在计算完每类中表的差异度后，计算每类中任意两组查询模式中相同的列的差异度，判断所述列的差异度是否小于所述第二预设阈值，若小于，则将两组查询模式合并，得到对应的模式关系树。
可以理解的是，在本实施例6中在把所有的查询模式分为若干类之后，需要对每一类的多个查询模式进行合并，合并的规则是：
对两个模式中相同的表计算差异度Diff(i,j)，如果差异度小于设定阈值，则表示这两个表对应同一张数据表，可以进行合并，并且合并后的Cat(m)由一个选择函数FuncSelect()确定，
即Cat(j)＝FuncSelect(Cat(i),Cat(j))；合并后的Score(m)＝Score(i)+Score(j)。
对两个模式中相同的列计算差异度Diff(i,j)，如果差异度小于设定阈值，则表示这两个列对应同一张数据表上的同一列，可以进行合并，并且合并后的Cat(m)由一个选择函数FuncSelect()确定，
即Cat(j)＝FuncSelect(Cat(i),Cat(j))；合并后的Score(m)＝Score(i)+Score(j)..
最终，每一类的查询模式经过合并后所形成的结构包含一组表及其JOIN关系树，还定义了每个表上被选择的列，这样的结构被称为一个模式关系树。
可选地，在另一实施例7中所述S6中将所述模式关系树中转换成对应的数据模型包括：
将所述模式关系树中的表信息转换成对应的所述数据模型的表信息；
将所述模式关系树中的列信息转换成对应的所述数据模型的列信息。
可选地，在另一实施例8中所述将所述模式关系树中的列信息转换成对应的所述数据模型的列信息还包括：
对所述模式关系树中的列进行分区评分，确定所述数据模型的分区列，其中对所述模式关系树中的列进行分区评分的计算公式为：
PartScore(i)＝PartFunc(Score(i),Stats(i))，其中PartFunc()是评分函数，Score(i)是每组查询模式上第(i)列评分，Stats(i)是第(i)列的特征统计值(就是数据特征的统计值)，当第(i)列分区评分PartScore(i)超过预定评分阈值，则将第(i)列设置为分区列。
可以理解的是，在本实施例8中是在上述的实施例7进行的另一实施例 方案。
如图2所示，本发明实施例9还涉及一种分类器，该分类器包括：静态规则分类模块、统计规则和预设学习规则分类模块；
所述静态规则分类模块，用于采用静态规则对至少N组查询模式进行差异判断，其中，所述静态规则是指计算任意两组查询模式中相同的表的差异度，判断所述表的差异度是否大于第一预设阈值，若大于，则两组查询模式不可分为一类；
且在计算完表的差异度后，计算任意两组查询模式中相同的列的差异度，判断所述列的差异度是否大于所述第一预设阈值，若大于则两组查询模式不可分为一类；
所述统计规则和预设学习规则分类模块，用于在进行差异判断后，采用统计规则和预设学习规则对所述至少N组查询模式进行聚类。
可以理解的是，在本实施例9中使用分类器将多种查询模式进行分类，查询模式分类包含三种规则：先静态规则：包含一系列互斥规则，即符合某些条件的两个模式不可分为一类。主要包括:对两个模式中相同的表计算差异度Diff(i,j)，如果差异度大于设定阈值，则两个模式不可分为一类；
对两个查询模式中相同的列计算差异度Diff(i,j)，如果差异度大于设定阈值，则两个查询模式不可分为一类；
统计规则：基于查询模式的所有列信息和表信息计算特征向量X(i)＝(col1,col2,col3,…colN,tab1,tab2,…,tabM)，其中每一个colX代表Col(X)列的评分Score(X)，每一个tabY代表Tab(Y)表的评分Score(Y)。然后使用无监督机器学习中的聚类算法对这些特征向量进行聚类，最终把所有的查询模式分为了若干类。
学习规则：“静态规则”和“统计规则”生成的是建议结果，用户可以在得到分类结果后进行手动调整或修正；例如，某些查询模式经过“静态规则”和“统计规则”之后被判断可以分为一类，但用户从业务等角度出发手 动修改为不可分为一类。“学习规则”会保存所有经过用户确认过之后的最终分类结果，并使用有监督机器学习训练合适的分类模型，对一组模式是否可以分为一类进行预测，并基于此预测对聚类结果进行调整。
在把所有的查询模式分为若干类之后，分类器需要对每一类的多个查询模式进行合并，合并的规则是：
对两个模式中相同的表计算差异度Diff(i,j)，如果差异度小于设定阈值，则表示这两个表对应同一张数据表，可以进行合并，并且在合并后的Cat(m)由一个选择函数FuncSelect()确定，即Cat(j)＝FuncSelect(Cat(i),Cat(j))；合并后的Score(m)＝Score(i)+Score(j)。
对两个模式中相同的列计算差异度Diff(i,j)，如果差异度小于设定阈值，则表示这两个列对应同一张数据表上的同一列，可以进行合并，并且在合并后的Cat(m)由一个选择函数FuncSelect()确定，即Cat(j)＝FuncSelect(Cat(i),Cat(j))；合并后的Score(m)＝Score(i)+Score(j)..
最终，每一类的查询模式经过合并后所形成的结构包含一组表及其JOIN关系树，还定义了每个表上被选择的列，这样的结构被称为一个模式关系树
在本说明书中，对上述术语的示意性表述不必须针对的是相同的实施例或示例。而且，描述的具体特征、结构、材料或者特点可以在任一个或多个实施例或示例中以合适的方式结合。此外，在不相互矛盾的情况下，本领域的技术人员可以将本说明书中描述的不同实施例或示例以及不同实施例或示例的特征进行结合和组合。
以上所述仅为本发明的较佳实施例，并不用以限制本发明，凡在本发明的精神和原则之内，所作的任何修改、等同替换、改进等，均应包含在本发明的保护范围之内。

Claims (10)

  1. 一种OLAP数据模型自动建模的方法，其特征在于,该方法包括：
    S1，获取SQL查询语句；
    S2，对所述SQL查询语句进行解析，根据解析结果判断所述SQL查询语句是否存在语法或者词法错误；
    S3，若不存在，则根据所述SQL查询语句使用到的与源数据相关的列信息和表信息，对所述SQL查询语句进行查询结构抽取，得到至少N组查询模式，其中N为大于等于1的自然数；
    S4，对所述至少N组查询模式进行分类；
    S5，将每类中的查询模式进行合并，得到对应的模式关系树；
    S6，将所述模式关系树转换成对应的数据模型。
  2. 根据权利要求1所述的方法，其特征在于，所述列信息是指所述SQL查询语句中使用的所有列，其中每列中包括：该列的列名、该列所在的表、该列的类型和该列出现的次数。
    所述表信息是指所述SQL查询语句中使用的所有表，其中每表中包括：该表的表名、该表的类型、该表和事实表的关联信息。
  3. 根据权利要求2所述的方法，其特征在于，所述S4中对所述至少N组查询模式进行分类包括：
    S41，采用静态规则对所述至少N组查询模式中任意两组查询模式进行差异判断，其中，所述静态规则是指计算任意两组查询模式中相同的表的差异度，判断所述表的差异度是否大于第一预设阈值，若大于，则两组查询模式不可分为一类；且在计算完表的差异度后，计算任意两组查询模式中相同的列的差异度，判断所述列的差异度是否大于所述第一预设阈值，若大于则两组查询模式不可分为一类；
    S42，在进行差异判断后，采用统计规则和预设学习规则对所述至少N 组查询模式进行聚类。
  4. 根据权利要求3所述的方法，其特征在于，所述S42中采用统计规则和预设学习规则对所述至少N组查询模式进行聚类包括：
    S421，根据每组查询模式中使用的列信息和表信息，计算每组查询模式的特征向量；
    S422，根据无监督机器学习中的聚类算法和所述预设学习规则对所有的所述特征向量进行聚类。
  5. 根据权利要求4所述的方法，其特征在于，所述预设学习规则是指在聚类后，判断聚类结果是否符合预设标准，若不符合，则采用有监督机器学习算法对聚类结果进行调整，并记录调整后的聚类结果。
  6. 根据权利要求3或4所述的方法，其特征在于，所述S5中将每类中的查询模式进行合并，得到对应的模式关系树包括：
    计算每类中任意两组查询模式中相同的表的差异度，判断所述表的差异度是否小于第二预设阈值，若小于，则将两组查询模式合并，得到对应的模式关系树；
    且在计算完每类中表的差异度后，计算每类中任意两组查询模式中相同的列的差异度，判断所述列的差异度是否小于所述第二预设阈值，若小于，则将两组查询模式合并，得到对应的模式关系树。
  7. 根据权利要求3或4所述的方法，其特征在于，所述S6中将所述模式关系树中转换成对应的数据模型包括：
    将所述模式关系树中的表信息转换成对应的所述数据模型的表信息；
    将所述模式关系树中的列信息转换成对应的所述数据模型的列信息。
  8. 根据权利要求7所述的方法，其特征在于，所述将所述模式关系树中的列信息转换成对应的所述数据模型的列信息还包括：
    对所述模式关系树中的列进行分区评分，确定所述数据模型的分区列，其中对所述模式关系树中的列进行分区评分的计算公式为：
    PartScore(i)＝PartFunc(Score(i),Stats(i))，其中PartFunc()是评分函数，Score(i)是每组查询模式上第(i)列评分，Stats(i)是第(i)列的特征统计值，当第(i)列分区评分PartScore(i)超过预定评分阈值，则将第(i)列设置为分区列。
  9. 一种分类器，其特征在于，该分类器包括：静态规则分类模块、统计规则和预设学习规则分类模块；
    所述静态规则分类模块，用于采用静态规则对至少N组查询模式进行差异判断，其中，所述静态规则是指计算任意两组查询模式中相同的表的差异度，判断所述表的差异度是否大于第一预设阈值，若大于，则两组查询模式不可分为一类，其中N为大于等于1的自然数；
    且在计算完表的差异度后，计算任意两组查询模式中相同的列的差异度，判断所述列的差异度是否大于所述第一预设阈值，若大于则两组查询模式不可分为一类；
    所述统计规则和预设学习规则分类模块，用于在进行差异判断后，采用统计规则和预设学习规则对所述至少N组查询模式进行聚类。
  10. 根据权利要求9所述的分类器，其特征在于，所述预设学习规则是指在聚类后，判断聚类结果是否符合预设标准，若不符合，则采用有监督机器学习算法对聚类结果进行调整，并记录调整后的聚类结果。
PCT/CN2018/073320 2017-12-29 2018-01-19 一种olap数据模型自动建模的方法、分类器 WO2019127744A1 (zh)

Priority Applications (2)

Application Number Priority Date Filing Date Title
EP18894747.7A EP3709192A4 (en) 2017-12-29 2018-01-19 PROCESS AND CLASSIFIER FOR AUTOMATIC MODELING OF AN OLAP DATA MODEL
US15/769,397 US11055307B2 (en) 2017-12-29 2018-01-19 Automatic modeling method and classifier for OLAP data model

Applications Claiming Priority (2)

Application Number Priority Date Filing Date Title
CN201711487470.5 2017-12-29
CN201711487470.5A CN108153894B (zh) 2017-12-29 2017-12-29 一种olap数据模型自动建模的方法、分类器装置

Publications (1)

Publication Number Publication Date
WO2019127744A1 true WO2019127744A1 (zh) 2019-07-04

Family

ID=62460107

Family Applications (1)

Application Number Title Priority Date Filing Date
PCT/CN2018/073320 WO2019127744A1 (zh) 2017-12-29 2018-01-19 一种olap数据模型自动建模的方法、分类器

Country Status (4)

Country Link
US (1) US11055307B2 (zh)
EP (1) EP3709192A4 (zh)
CN (1) CN108153894B (zh)
WO (1) WO2019127744A1 (zh)

Cited By (1)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN112445812A (zh) * 2020-11-27 2021-03-05 中原银行股份有限公司 一种结构化查询语句处理方法及装置

Families Citing this family (10)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN111209328A (zh) * 2018-11-22 2020-05-29 厦门白山耘科技有限公司 自动确定待展示的输入数据需使用的图表属性的方法、装置、客户端及服务器
CN110008239A (zh) * 2019-03-22 2019-07-12 跬云(上海)信息科技有限公司 基于预计算优化的逻辑执行优化方法及系统
US11269880B2 (en) * 2019-05-20 2022-03-08 Google Llc Retroreflective clustered join graph generation for relational database queries
US11281671B2 (en) * 2019-05-20 2022-03-22 Google Llc Retroreflective join graph generation for relational database queries
CN110597876B (zh) * 2019-08-30 2023-03-24 南开大学 一种基于离线学习历史查询预测未来查询的近似查询方法
CN111125147B (zh) * 2019-12-12 2021-06-01 跬云(上海)信息科技有限公司 基于扩展预计算模型和sql函数的超大集合分析方法及装置
CN111832661B (zh) * 2020-07-28 2024-04-02 平安国际融资租赁有限公司 分类模型构建方法、装置、计算机设备及可读存储介质
CN112132420B (zh) * 2020-09-04 2023-11-28 广西大学 一种面向sql查询的细化评分方法
US11386053B2 (en) * 2020-10-15 2022-07-12 Google Llc Automatic generation of a data model from a structured query language (SQL) statement
CN113672615B (zh) * 2021-07-22 2023-06-20 杭州未名信科科技有限公司 一种基于树型表间关系自动生成sql的数据分析方法与系统

Citations (3)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US7571182B1 (en) * 2005-01-26 2009-08-04 Star Analytics, Inc. Emulation of a balanced hierarchy from a nonbalanced hierarchy
CN105468702A (zh) * 2015-11-18 2016-04-06 中国科学院计算机网络信息中心 一种大规模rdf数据关联路径发现方法
CN105930388A (zh) * 2016-04-14 2016-09-07 中国人民大学 一种基于函数依赖关系的olap分组聚集方法

Family Cites Families (10)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US7225343B1 (en) * 2002-01-25 2007-05-29 The Trustees Of Columbia University In The City Of New York System and methods for adaptive model generation for detecting intrusions in computer systems
US6947929B2 (en) * 2002-05-10 2005-09-20 International Business Machines Corporation Systems, methods and computer program products to determine useful relationships and dimensions of a database
US7716167B2 (en) * 2002-12-18 2010-05-11 International Business Machines Corporation System and method for automatically building an OLAP model in a relational database
US7707143B2 (en) * 2004-06-14 2010-04-27 International Business Machines Corporation Systems, methods, and computer program products that automatically discover metadata objects and generate multidimensional models
JP4682030B2 (ja) * 2005-11-30 2011-05-11 富士通株式会社 図形検索プログラム、該プログラムを記録した記録媒体、図形検索装置、および図形検索方法
US10289637B2 (en) * 2014-06-13 2019-05-14 Excalibur Ip, Llc Entity generation using queries
CN104391895A (zh) * 2014-11-12 2015-03-04 珠海世纪鼎利通信科技股份有限公司 一种基于云计算的sql语句处理系统
US20160371355A1 (en) * 2015-06-19 2016-12-22 Nuodb, Inc. Techniques for resource description framework modeling within distributed database systems
US20170031980A1 (en) * 2015-07-28 2017-02-02 InfoKarta, Inc. Visual Aggregation Modeler System and Method for Performance Analysis and Optimization of Databases
CN106997386B (zh) * 2017-03-28 2019-12-27 上海跬智信息技术有限公司 一种olap预计算模型、自动建模方法及自动建模系统

Patent Citations (3)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US7571182B1 (en) * 2005-01-26 2009-08-04 Star Analytics, Inc. Emulation of a balanced hierarchy from a nonbalanced hierarchy
CN105468702A (zh) * 2015-11-18 2016-04-06 中国科学院计算机网络信息中心 一种大规模rdf数据关联路径发现方法
CN105930388A (zh) * 2016-04-14 2016-09-07 中国人民大学 一种基于函数依赖关系的olap分组聚集方法

Non-Patent Citations (1)

* Cited by examiner, † Cited by third party
Title
See also references of EP3709192A4 *

Cited By (1)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN112445812A (zh) * 2020-11-27 2021-03-05 中原银行股份有限公司 一种结构化查询语句处理方法及装置

Also Published As

Publication number Publication date
CN108153894A (zh) 2018-06-12
US11055307B2 (en) 2021-07-06
EP3709192A1 (en) 2020-09-16
US20200394201A1 (en) 2020-12-17
EP3709192A4 (en) 2020-11-04
CN108153894B (zh) 2020-08-14

Similar Documents

Publication Publication Date Title
WO2019127744A1 (zh) 一种olap数据模型自动建模的方法、分类器
US8898145B2 (en) Query optimization techniques for business intelligence systems
US8996555B2 (en) Question answering framework for structured query languages
US9280535B2 (en) Natural language querying with cascaded conditional random fields
US11461319B2 (en) Dynamic database query efficiency improvement
WO2018176623A1 (zh) 一种olap预计算模型、自动建模方法及自动建模系统
US9286370B2 (en) Viewing a dimensional cube as a virtual data source
US6718338B2 (en) Storing data mining clustering results in a relational database for querying and reporting
US20120117054A1 (en) Query Analysis in a Database
US9135296B2 (en) System, method, and data structure for automatically generating database queries which are data model independent and cardinality independent
EP3267330A1 (en) Query rewriting in a relational data harmonization framework
US20200175390A1 (en) Word embedding model parameter advisor
US11288266B2 (en) Candidate projection enumeration based query response generation
WO2020168702A1 (zh) 一种基于模板的软件缺陷自动问答方法
Peng et al. Optimizing probabilistic query processing on continuous uncertain data
Feldman et al. A knowledge-based approach for index selection in relational databases
Zhu et al. A data cleaning method for heterogeneous attribute fusion and record linkage
Xu et al. Semantic connection set-based massive RDF data query processing in Spark environment
Ordonez et al. A data mining system based on SQL queries and UDFs for relational databases
Castellanos et al. SIE-OBI: a streaming information extraction platform for operational business intelligence
Wang et al. Probabilistic object deputy model for uncertain data and lineage management
Li et al. Cost-based query optimization for XPath
Sakr Towards a comprehensive assessment for selectivity estimation approaches of XML queries
CN112100370B (zh) 一种基于文本卷积和相似度算法的图审专家组合推荐方法
Rong et al. DBinsight: A Tool for Interactively Understanding the Query Processing Pipeline in RDBMSs

Legal Events

Date Code Title Description
WWE Wipo information: entry into national phase

Ref document number: 18894747.7

Country of ref document: EP

ENP Entry into the national phase

Ref document number: 2018894747

Country of ref document: EP

Effective date: 20200611

NENP Non-entry into the national phase

Ref country code: DE

121 Ep: the epo has been informed by wipo that ep was designated in this application

Ref document number: 18894747

Country of ref document: EP

Kind code of ref document: A1

121 Ep: the epo has been informed by wipo that ep was designated in this application

Ref document number: 18894747

Country of ref document: EP

Kind code of ref document: A1