CN115757475A - Data query method and system based on SQL (structured query language) statement - Google Patents
Data query method and system based on SQL (structured query language) statement Download PDFInfo
- Publication number
- CN115757475A CN115757475A CN202211562377.7A CN202211562377A CN115757475A CN 115757475 A CN115757475 A CN 115757475A CN 202211562377 A CN202211562377 A CN 202211562377A CN 115757475 A CN115757475 A CN 115757475A
- Authority
- CN
- China
- Prior art keywords
- statement
- data
- query
- processing
- sql
- Prior art date
- Legal status (The legal status 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 status listed.)
- Pending
Links
Images
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a data query method and a system based on SQL sentences, wherein the method comprises the following steps: acquiring and analyzing an SQL query statement to obtain tree structure information; traversing a plurality of pre-collected databases according to the tree structure information and constructing a leaf node data set; and filtering the leaf node data set to obtain target query data. According to the invention, the SQL sentences can be obtained, the plurality of different databases or data tables are constructed into the leaf node data sets according to the SQL sentences, the target data can be obtained by calculating and filtering the leaf node data sets, the data can be extracted from the plurality of incompatible data tables in the whole process without editing a plurality of languages, the obstruction of each database is eliminated, the workload of extracting the data is reduced, the time consumption of processing is shortened, and the processing efficiency of the service is improved.
Description
Technical Field
The invention relates to the technical field of data query, in particular to a data query method and a data query system based on SQL sentences.
Background
With the rapid development of computer technology, business systems in various industries tend to be perfect, and a large amount of business data is accumulated to form a database with a certain scale. Such as a tax system database. The standard data Query Language in the database is usually a Structured Query Language (SQL) selected, and the database is controlled and used by various SQL instructions.
For a tax database, a tax bureau worker queries a taxpayer and corresponding risks and other contents in the database, and in order to quickly query required target data, a commonly used method at present is as follows: and editing the SQL conditional statement, and directly acquiring data from the corresponding database based on the SQL statement. Or combining the configuration information and the data source, and performing configuration access from the corresponding database according to the combination.
However, the conventional method has the following technical problems: whether the SQL sentence is edited for direct query or the combined query of the configuration information and the data source is carried out, the specific target database is queried; when the business requirement of a user needs to acquire data from a plurality of different or incompatible databases or data tables, a plurality of SQL statements or data combinations need to be edited, the editing workload is large, the time consumption is long, the efficiency is low, and the business requirement of the user is difficult to meet.
Disclosure of Invention
The invention provides a data query method and a data query system based on SQL sentences.
A first aspect of an embodiment of the present invention provides a data query method based on an SQL statement, where the method includes:
obtaining and analyzing an SQL query statement to obtain tree structure information;
traversing a plurality of pre-collected databases according to the tree structure information and constructing a leaf node data set;
and filtering the leaf node data set to obtain target query data.
In a possible implementation manner of the first aspect, the obtaining and analyzing the SQL query statement to obtain tree structure information includes:
acquiring an SQL query statement, calling an analysis tool to analyze the SQL query statement into an analysis statement with an abstract syntax tree structure, and extracting an outer layer query statement from the analysis statement;
traversing the outer layer query statement and preprocessing the outer layer query statement to obtain a processed statement, wherein the statement processing comprises: converting the table name and the field of the outer layer query statement and replacing the sub-query of the outer layer query statement;
analyzing the content of the processing statement and determining whether the processing statement is of a sub-query type;
if the processing statement is not the sub-query type, extracting and storing SqlDataSet type information from the processing statement;
and forming a mapping set by a preset alias and the SqlDataSet type information to obtain tree structure information.
In a possible implementation manner of the first aspect, the acquiring and analyzing an SQL query statement to obtain tree structure information further includes:
and if the processing statement is of the sub-query type, re-executing the analysis operation by taking the processing statement as the SQL query statement until the processing statement is not of the sub-query type and the tree structure information is obtained.
In a possible implementation manner of the first aspect, the analyzing the content of the processing statement and determining whether the processing statement is of a sub-query type includes:
analyzing form content data contained in the processing statement and determining whether the form content data contains a join statement;
if the form content data contains a join statement, taking out the join statement from the form content data;
if the form content data does not contain a join statement, determining whether the processing statement is of a sub-query type.
In a possible implementation manner of the first aspect, traversing a plurality of databases collected in advance according to the tree structure information and constructing a leaf node data set includes:
traversing a plurality of collected databases according to the tree structure information to obtain a tree data set;
performing data set processing on the tree data set to obtain a processed data set, wherein the data set processing comprises database-based and table-based processing, database compatibility processing, function expansion processing, screening processing and safety processing which are sequentially performed;
and eliminating consumption data contained in the processing data set, taking the data reserved in the processing data set as leaf nodes, and performing join calculation to form a leaf node data set.
In a possible implementation manner of the first aspect, the database-based and table-based processing specifically includes:
generating the tree data into an initial data set according to preset service entity structure information, wherein the service entity structure information is description information of a service entity structure and contains a mapping relation between a page field and a table;
and allocating each metadata in the initial data set to a corresponding cloud read according to business requirements, and performing horizontal table division according to each metadata field combination in the initial data set.
In a possible implementation manner of the first aspect, the filtering the leaf node data set to obtain target query data includes:
extracting filtering data from the leaf node data set by using a preset where statement;
and screening target query data from the filtering data based on a preset select statement.
A second aspect of the embodiments of the present invention provides a data query system based on an SQL statement, where the system includes:
the acquisition module is used for acquiring and analyzing the SQL query statement to obtain tree structure information;
the building module is used for traversing a plurality of pre-collected databases according to the tree structure information and building a leaf node data set;
and the query module is used for filtering the leaf node data set to obtain target query data.
A third aspect of an embodiment of the present invention provides an electronic device, including: the system comprises a memory, a processor and a computer program stored on the memory and capable of running on the processor, wherein the processor executes the program to realize the SQL statement-based data query method.
A fourth aspect of the embodiments of the present invention provides a computer-readable storage medium storing a computer-executable program for causing a computer to execute the SQL statement-based data query method described above.
A fifth aspect of the embodiments of the present invention provides a computer program product, which when running on a computer device, enables the computer device to implement the data query method based on SQL statements as described above.
Compared with the prior art, the data query method and the data query system based on the SQL statement provided by the embodiment of the invention have the beneficial effects that: according to the invention, when the SQL sentences are obtained, a plurality of different databases or data tables are constructed into the leaf node data sets according to the SQL sentences, the target data can be obtained by calculating and filtering the leaf node data sets, the data can be extracted from a plurality of incompatible data tables only by editing one sentence in the whole process, a plurality of languages do not need to be edited, the obstruction of each database can be eliminated, the workload of extracting the data is reduced, the processing time is shortened, and the processing efficiency of the service is improved.
Drawings
Fig. 1 is a schematic flowchart of a data query method based on SQL statements according to an embodiment of the present invention;
fig. 2 is a schematic flowchart of a data query method based on SQL statements according to an embodiment of the present invention;
fig. 3 is a schematic flowchart of a data query method based on SQL statements according to an embodiment of the present invention;
fig. 4 is an operation flowchart of a data query method based on SQL statements according to an embodiment of the present invention;
FIG. 5 is a flowchart illustrating a data query method based on SQL statements according to an embodiment of the present invention;
fig. 6 is a schematic flowchart of a data query method based on SQL statements according to an embodiment of the present invention;
FIG. 7 is a flowchart illustrating an operation of a method for querying data based on SQL statements according to an embodiment of the present invention;
fig. 8 is a schematic structural diagram of a data query system based on SQL statements according to an embodiment of the present invention.
Detailed Description
The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are only a part of the embodiments of the present invention, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.
With the rapid development of computer technology, business systems in various industries tend to be perfect, and a large amount of business data is accumulated to form a database with a certain scale. Such as a tax system database. The standard data Query Language in the database is usually a Structured Query Language (SQL) selected, and the database is controlled and used by various SQL instructions.
For a tax database, tax bureau staff inquire contents such as taxpayers and corresponding risks in the database, and in order to quickly inquire required target data, a commonly used method at present is as follows: and editing the SQL conditional statement, and directly acquiring data from the corresponding database based on the SQL statement. Or combining the configuration information and the data source, and performing configuration access from the corresponding database according to the combination.
However, the conventional method has the following technical problems: whether the direct query of the SQL sentence is edited or the combined query of the configuration information and the data source is carried out, the query operation is carried out on a specific target database; when the business requirement of a user needs to acquire data from a plurality of different or incompatible databases or data tables, a plurality of SQL statements or data combinations need to be edited, the editing workload is large, the time consumption is long, the efficiency is low, and the business requirement of the user is difficult to meet.
In order to solve the above problem, the following specific embodiments will specifically describe and explain a data query method based on SQL statements provided in the embodiments of the present application.
Referring to fig. 1, a schematic flow chart of a data query method based on an SQL statement according to an embodiment of the present invention is shown.
As an example, the data query method based on the SQL statement may include:
and S11, obtaining and analyzing the SQL query statement to obtain tree structure information.
In an embodiment, the SQL query statement may be a statement that the user enters select.
In one embodiment, the statements are exemplified as follows:
SELECT SUM(T.treducedtotal)FROM((SELECT treducedtotal,rule FROM tccit_treduced_summary WHERE org＝'#orgid'AND skssqq＝'#startDate'AND skssqz＝'#endDate')a INNER JOIN(SELECT id,itemchoicename FROM tccit_preferential_item WHERE org＝'#orgid')c ON c.id＝a.rule INNER JOIN(SELECT id FROM tpo_discount_tree WHERE number＝'204')b ON b.id＝c.itemchoicename)T。
the tree structure information may be information obtained by parsing a syntax tree structure of the SQL query statement.
Optionally, the SQL query statement may be parsed by a parsing tool to obtain tree structure information.
And S12, traversing a plurality of pre-collected databases according to the tree structure information and constructing a leaf node data set.
In an embodiment, the collected sub data sets or databases or data tables may be traversed according to a syntax tree structure of the tree structure information, and a data set may be constructed by constructing information extracted by traversal through traversing each data and information, so as to obtain a leaf node data set.
The subdata set or database may contain: list of fields to output such as treducedtotal in example a, column name of rule output is a
By constructing a data set from a plurality of databases or data tables, incompatible databases can be integrated into a data set tree, and then data query and extraction are performed on the data set tree, so that barriers of the databases can be eliminated. Meanwhile, data query and extraction of multiple databases can be realized through one SQL query statement, multiple SQL statements do not need to be edited, workload can be greatly reduced, and processing efficiency is improved.
And S13, filtering the leaf node data set to obtain target query data.
After the leaf node data set is generated, data can be extracted from the leaf node data set through data filtering conditions or a screening tool, and target query data required by a user can be obtained.
In this embodiment, an embodiment of the present invention provides a data query method based on an SQL statement, which has the following beneficial effects: according to the invention, when the SQL sentences are obtained, a plurality of different databases or data tables are constructed into the leaf node data sets according to the SQL sentences, the target data can be obtained by calculating and filtering the leaf node data sets, the data can be extracted from a plurality of incompatible data tables only by editing one sentence in the whole process, a plurality of languages do not need to be edited, the obstruction of each database can be eliminated, the workload of extracting the data is reduced, the processing time is shortened, and the processing efficiency of the service is improved.
Fig. 2 is a schematic flow chart illustrating a data query method based on an SQL statement according to an embodiment of the present invention.
As an example, the data query method based on the SQL statement may include:
s21, obtaining an SQL query statement, calling an analysis tool to analyze the SQL query statement into an analysis statement with an abstract syntax tree structure, and extracting an outer layer query statement from the analysis statement.
In an alternative embodiment, the Sql query statement may be parsed by a druid Sql parsing tool, and after parsing, a parsed statement of an abstract syntax tree structure may be obtained. Specifically, a sqlDataSet class may be formed, which is a node class of the tree and includes attributes such as a data set, an alias, and a sub data set that are generated after parsing. The dataSet attribute may be a class of an Algo framework, and may perform a series of operations on the dataSet, for example, performing data operations through a join select where condition.
An outer query statement may be extracted from the parse statement, the outer query statement may include: select, from and where.
S22, traversing the outer layer query statement and preprocessing the outer layer query statement to obtain a processed statement, wherein the statement processing comprises: converting the table name and the field of the outer layer query statement and replacing the sub-query of the outer layer query statement.
In an alternative embodiment, the outer query statement may be traversed to convert the table names and fields of the abstract syntax tree structure of the outer query statement into lower case; the in sub-queries within the outer query statement may be replaced with an in + data list.
And S23, analyzing the content of the processing statement and determining whether the processing statement is of a sub-query type.
After the preprocessing is completed, the processed sentence can be analyzed by adopting an analysis tool.
Since the statement contains three parts of select, from and where, when parsing, the parsing can be started from the outermost form content. In the parsing process, it can be determined whether the processing statement is of a sub-query type.
And S24, if the processing statement is of the sub-query type, re-executing the analysis operation by taking the processing statement as the SQL query statement until the processing statement is not of the sub-query type and the tree structure information is obtained.
If the processing statement is of the sub-query type, it indicates that the preprocessing operation is not completed, and the statement also stores the sub-query.
The step S21 may be restarted by using the processed statement as an SQL query statement, and the step S25 may be executed after the statement is parsed and preprocessed again until the processed statement is not the sub-query type.
And S25, if the processing statement is not the sub-query type, extracting and storing SqlDataSet type information from the processing statement.
In an alternative embodiment, if the processing statement is not of the sub-query type, the processing statement may be constructed into the sqlDataSet type information, and the relevant information of this part may be saved.
And S26, forming a mapping set by the preset alias and the SqlDataSet information to obtain tree structure information.
In an embodiment, the alias preset by the user and the sql dataset type information may be combined to establish a mutual mapping, so as to obtain the tree structure information.
And S27, traversing a plurality of pre-collected databases according to the tree structure information and constructing a leaf node data set.
In an embodiment, step S27 is the same as step S12 of the above embodiment, and the detailed operations may refer to the above description, which is not repeated herein for avoiding redundancy.
And S28, filtering the leaf node data set to obtain target query data.
In an embodiment, step S28 is the same as step S13 of the above embodiment, and the detailed operations may refer to the above description, and are not repeated herein to avoid repetition.
In this embodiment, an embodiment of the present invention provides a data query method based on an SQL statement, which has the following beneficial effects: the invention can extract the attributes of the data set, the alias, the contained subdata set and the like in the statement by analyzing the statement of the tree structure, thereby respectively and subsequently carrying out join select where data operation on various data so as to accurately extract the query data.
Fig. 3 is a schematic flow chart illustrating a data query method based on an SQL statement according to an embodiment of the present invention.
As an example, the data query method based on the SQL statement may include:
s31, obtaining an SQL query statement, calling an analysis tool to analyze the SQL query statement into an analysis statement with an abstract syntax tree structure, and extracting an outer layer query statement from the analysis statement.
In an embodiment, step S31 is the same as step S21 of the above embodiment, and the detailed operations may refer to the above description, which is not repeated herein for avoiding redundancy.
S32, traversing the outer layer query statement and preprocessing the outer layer query statement to obtain a processed statement, wherein the statement processing comprises the following steps: converting the table name and the field of the outer layer query statement and replacing the sub-query of the outer layer query statement.
In an embodiment, step S32 is the same as step S22 of the above embodiment, and the detailed operations may refer to the above description, which is not repeated herein for avoiding redundancy.
S33, analyzing the form content data contained in the processing statement, and determining whether the form content data contains a join statement.
And S34, if the form content data contains a join sentence, taking out the join sentence from the form content data.
S35, if the form content data does not contain a join statement, determining whether the processing statement is of a sub-query type.
In an alternative embodiment, in parsing the form content data contained in the processing statement, it may be determined whether the form content data contains a join statement. If the form content data contains a join statement, the join statement may be taken out from the form content data, and then it is determined whether the form content data after the join statement is taken out contains the join statement. If the form content data does not contain a join statement, it can be determined whether the processed statement is of the sub-query type.
And S36, if the processing statement is of the sub-query type, re-executing the analysis operation by taking the processing statement as the SQL query statement until the processing statement is not of the sub-query type and the tree structure information is obtained.
In an embodiment, step S36 is the same as step S24 of the above embodiment, and the detailed operations may refer to the above description, and are not repeated herein to avoid repetition.
And S37, if the processing statement is not the sub-query type, extracting and storing SqlDataSet type information from the processing statement.
In an embodiment, step S37 is the same as step S25 of the above embodiment, and for specific operations, reference may be made to the above description, and details are not repeated herein to avoid repetition.
And S38, forming a mapping set by the preset alias and the SqlDataSet type information to obtain tree structure information.
In an embodiment, step S38 is the same as step S26 of the above embodiment, and the detailed operations may refer to the above description, which is not repeated herein for avoiding redundancy.
And S39, traversing a plurality of pre-collected databases according to the tree structure information and constructing a leaf node data set.
In an embodiment, step S39 is the same as step S27 of the above embodiment, and the detailed operations may refer to the above description, and are not repeated herein to avoid repetition.
And S310, filtering the leaf node data set to obtain target query data.
In an embodiment, step S310 is the same as step S28 of the above embodiment, and the detailed operations may refer to the above description, which is not repeated herein for avoiding redundancy.
Referring to fig. 4, an operation flow diagram of a data query method based on an SQL statement according to an embodiment of the present invention is shown.
Specifically, the Sql query statement may be parsed by using a dry Sql parsing tool, a parsing statement with an abstract syntax tree structure may be obtained after the parsing, then an outermost part statement of the parsing statement is obtained, a table name and a field of the outermost part statement are replaced, and in sub-queries of the outermost part statement are replaced. And analyzing the form content of the statement after the replacement is finished, judging whether the integral content of the form has a join statement, and if so, removing the join statement part. Judging whether the statement is of a sub-query type, if the processing statement is of the sub-query type, re-executing the analysis operation by taking the processing statement as an SQL query statement until the processing statement is not of the sub-query type; if the processing statement is not the sub-query type, the SqlDataSet type information can be extracted from the processing statement and stored, and the alias preset by the user and the SqlDataSet type information form a mapping set to obtain tree structure information.
In this embodiment, an embodiment of the present invention provides a data query method based on an SQL statement, which has the following beneficial effects: in the process of analyzing the statement, the method can judge whether the form content in the statement contains the join statement, and if the form content contains the join statement, the join statement can be removed, so that tree structure information is formed.
Referring to fig. 5, a flowchart of a data query method based on an SQL statement according to an embodiment of the present invention is shown.
As an example, the data query method based on an SQL statement may include:
and S51, obtaining and analyzing the SQL query statement to obtain tree structure information.
In an embodiment, step S51 is the same as step S11 of the above embodiment, and the detailed operations may refer to the above description, and are not repeated herein to avoid redundancy.
And S52, traversing the collected databases according to the tree structure information to obtain a tree data set.
In an alternative embodiment, multiple collected databases may be traversed in a tree structure of tree structure information, such that multiple data sets may be combined into a tree data set.
And S53, carrying out data set processing on the tree data set to obtain a processed data set, wherein the data set processing comprises database-based and table-based processing, database compatibility processing, function expansion processing, screening processing and safety processing which are sequentially carried out.
In an embodiment, the database-dividing and table-dividing processing specifically includes:
and generating the tree data into an initial data set according to preset service entity structure information, wherein the service entity structure information is the mapping relation between page fields and tables contained in the description information of the service entity structure.
Allocating each metadata in the initial data set to a corresponding cloud read according to business requirements, and performing horizontal tabulation according to each metadata field combination in the initial data set.
In an alternative embodiment, each database may be generated from the database according to the metadata and run to generate the original data set. The metadata may be data about the mapping relationship of the page fields and the tables contained in the description information of the business entity structure.
In the process of generating the SQL statement, the dynamic QL statement may be organized according to the table splitting configuration information configured by the metadata.
The sub-database is specifically based on the service cloud, and the sub-tables are based on the field content level sub-tables and are all realized based on metadata. Each metadata belongs to a fixed cloud, and one metadata corresponds to a plurality of tables.
The sub-table is specifically performed according to a certain field combination strategy in the metadata, such as monthly + organization for horizontal sub-table according to the date.
The database compatibility process may be a compatibility process based on the metadata generation dataset toollevel. Through the layer of compatible processing, the computing expansibility can be increased, and users can expand according to different service requirements.
The function expansion process may be a function calculation according to a function set by a user.
The screening process may be a metadata-based expansion process, which examines data, such as sensitive information, and may mask the sensitive information after the sensitive information is queried.
The security process may be a security check, for example, it may be determined whether to check for system important information or to check for no data.
After the above-mentioned various processes are completed, a processed data set can be obtained.
It should be noted that if the data source is elsewhere or different platform, the above processing operations may be performed on the data set when the data set is initially started.
And S54, eliminating the consumption data contained in the processing data set, taking the data reserved in the processing data set as leaf nodes, and performing join calculation to form a leaf node data set.
In one embodiment, consumable data in the processed data set may be eliminated first, and data not consumed in the processed data set may be used as leaf nodes, and the whole query sql is finally to output one data set.
Join calculation can be performed on the output data set to obtain a leaf node data set.
Therein, a join computation may be a computational operation performed by a function encapsulated within a platform or system.
And S55, filtering the leaf node data set to obtain target query data.
In an embodiment, step S55 is the same as step S13 of the above embodiment, and the detailed operations may refer to the above description, which is not repeated herein for avoiding redundancy.
In this embodiment, an embodiment of the present invention provides a data query method based on an SQL statement, which has the following beneficial effects: when the processing data set is constructed, the invention can carry out various data processing on the data set, can eliminate redundant data in the data set through various data processing, and eliminates the obstruction of each data set, so that each data set can form a uniform data set.
Fig. 6 is a schematic flow chart illustrating a data query method based on an SQL statement according to an embodiment of the present invention.
As an example, the data query method based on an SQL statement may include:
and S61, obtaining and analyzing the SQL query statement to obtain tree structure information.
In an embodiment, step S51 is the same as step S11 of the above embodiment, and the detailed operations may refer to the above description, and are not repeated herein to avoid redundancy.
S62, traversing a plurality of pre-collected databases according to the tree structure information and constructing a leaf node data set.
In an embodiment, step S62 is the same as step S12 of the above embodiment, and the detailed operations may refer to the above description, which is not repeated herein for avoiding redundancy.
And S63, extracting filtering data from the leaf node data set by using a preset where statement.
And S64, screening target query data from the filtered data based on a preset select statement.
Specifically, the condition of filtering is a word-written statement, such as "id >0" in "select sum (t. Around) from table1 as t word id >0", obtained by parsing and combing by an sql parsing tool.
The filtered data is the amount set in table1 with id > 0. The final Sum function is processed when the select operation is output.
The condition for the select process is that the data set is provided to the upper level for summary generation at the time of generation. The selected data includes column names, or functions, or expressions, written in-between select and from, such as select (1+2) as a, sum (t.around) from t.
In actual operation, the script statement may be first parsed to obtain information of each part, a leaf data set is first generated (a leaf node data set of the whole tree structure is first derived from a database or other approaches to generate a data set object operable for the service), then a connection operation may be performed in the memory according to each part of the sql statement according to the attribute and information of the data set, a new data set may be formed by filtering and outputting columns in a layer-by-layer connection process, and a final data set may be subjected to where filtering and select output to obtain a final data result to be queried
In this embodiment, an embodiment of the present invention provides a data query method based on an SQL statement, which has the following beneficial effects: according to the invention, the target query data can be rapidly and accurately screened from the data set through the where statement and the select statement, and the processing efficiency of the service can be effectively improved.
Referring to fig. 7, an operation flow diagram of a data query method based on SQL statements according to an embodiment of the present invention is shown.
As an example, the data query method based on the SQL statement may include:
firstly, inputting a select SQL statement, and analyzing the SQL statement to obtain tree structure information about a data set; and performing database-based and table-based processing, database compatibility processing, function expansion processing, security and examination and other processing on a plurality of data sets or databases according to the tree structure information to obtain processed data sets. And acquiring a leaf node of the processing data set as a leaf data set, and performing join calculation on the leaf data set to obtain a final data set (also called a leaf node data set). And performing work filtering and select screening on the final data set to obtain target query data required by the user.
In this embodiment, an embodiment of the present invention provides a data query method based on an SQL statement, which has the following beneficial effects:
1. the problems that the sql access database of the direct operation database is incompatible, data access is unsafe, and sub-database and sub-table are not supported are solved.
2. The problem that a plurality of entities cannot be associated to fetch in the system is solved.
3. The syntax is compiled by the sql sentences, so that the learning is easy, the implementation cost is reduced, and the system can take data more conveniently.
The embodiment of the present invention further provides a data query system based on an SQL statement, and referring to fig. 8, a schematic structural diagram of the data query system based on the SQL statement provided in the embodiment of the present invention is shown.
By way of example, the SQL statement-based data query system may include:
an obtaining module 801, configured to obtain and analyze an SQL query statement to obtain tree structure information;
a building module 802, configured to traverse a plurality of databases collected in advance according to the tree structure information and build a leaf node data set;
and the query module 803 is configured to filter the leaf node data set to obtain target query data.
Optionally, the obtaining module is further configured to:
acquiring an SQL query statement, calling an analysis tool to analyze the SQL query statement into an analysis statement with an abstract syntax tree structure, and extracting an outer layer query statement from the analysis statement;
traversing the outer layer query statement and preprocessing the outer layer query statement to obtain a processed statement, wherein the statement processing comprises: converting the table name and the field of the outer layer query statement and replacing the sub-query of the outer layer query statement;
analyzing the content of the processing statement and determining whether the processing statement is of a sub-query type;
if the processing statement is not of the sub-query type, extracting and storing SqlDataSet type information from the processing statement;
and forming a mapping set by the preset alias and the SqlDataSet information to obtain tree structure information.
Optionally, the obtaining module is further configured to:
and if the processing statement is of the sub-query type, re-executing the analysis operation by taking the processing statement as the SQL query statement until the processing statement is not of the sub-query type and the tree structure information is obtained.
Optionally, the obtaining module is further configured to:
analyzing form content data contained in the processing statement and determining whether the form content data contains a join statement;
if the form content data contains a join statement, taking out the join statement from the form content data;
if the form content data does not contain a join statement, determining whether the processing statement is of a sub-query type.
Optionally, the building module is further configured to:
traversing a plurality of collected databases according to the tree structure information to obtain a tree data set;
performing data set processing on the tree data set to obtain a processed data set, wherein the data set processing comprises database-based and table-based processing, database compatibility processing, function expansion processing, screening processing and safety processing which are sequentially performed;
and eliminating consumption data contained in the processing data set, taking the data reserved in the processing data set as leaf nodes, and performing join calculation to form a leaf node data set.
Optionally, the database-dividing and table-dividing processing specifically includes:
generating the tree data into an initial data set according to preset service entity structure information, wherein the service entity structure information is description information of a service entity structure and contains a mapping relation between a page field and a table;
and allocating each metadata in the initial data set to a corresponding cloud read according to business requirements, and performing horizontal table division according to each metadata field combination in the initial data set.
Optionally, the query module is further configured to:
extracting filtering data from the leaf node data set by using a preset where statement;
and screening target query data from the filtering data based on a preset select statement.
It can be clearly understood by those skilled in the art that, for convenience and brevity, the specific working process of the system described above may refer to the corresponding process in the foregoing method embodiment, and is not described herein again.
Further, an embodiment of the present application further provides an electronic device, including: the system comprises a memory, a processor and a computer program stored on the memory and capable of running on the processor, wherein the processor executes the program to realize the SQL statement-based data query method according to the embodiment.
Further, an embodiment of the present application also provides a computer-readable storage medium, where the computer-readable storage medium stores computer-executable instructions, where the computer-executable instructions are configured to enable a computer to execute the SQL statement-based data query method according to the foregoing embodiment.
While the foregoing is directed to the preferred embodiment of the present invention, it will be understood by those skilled in the art that various changes and modifications may be made without departing from the spirit and scope of the invention.
Claims (10)
1. A data query method based on SQL statements is characterized by comprising the following steps:
acquiring and analyzing an SQL query statement to obtain tree structure information;
traversing a plurality of pre-collected databases according to the tree structure information and constructing a leaf node data set;
and filtering the leaf node data set to obtain target query data.
2. The SQL statement-based data query method of claim 1, wherein the obtaining and parsing the SQL statement to obtain the tree structure information comprises:
acquiring an SQL query statement, calling an analysis tool to analyze the SQL query statement into an analysis statement with an abstract syntax tree structure, and extracting an outer layer query statement from the analysis statement;
traversing the outer layer query statement and preprocessing the outer layer query statement to obtain a processed statement, wherein the statement processing comprises: converting the table name and the field of the outer layer query statement and replacing the sub-query of the outer layer query statement;
analyzing the content of the processing statement and determining whether the processing statement is of a sub-query type;
if the processing statement is not the sub-query type, extracting and storing SqlDataSet type information from the processing statement;
and forming a mapping set by a preset alias and the SqlDataSet type information to obtain tree structure information.
3. The SQL statement-based data query method according to claim 2, wherein the acquiring and parsing SQL query statement to obtain tree structure information further comprises:
and if the processing statement is of the sub-query type, re-executing the analysis operation by taking the processing statement as the SQL query statement until the processing statement is not of the sub-query type and the tree structure information is obtained.
4. The SQL statement-based data query method according to claim 2, wherein the parsing the content of the processing statement and determining whether the processing statement is a sub-query type includes:
analyzing form content data contained in the processing statement and determining whether the form content data contains a join statement;
if the form content data contains a join statement, taking out the join statement from the form content data;
if the form content data does not contain a join statement, determining whether the processing statement is of a sub-query type.
5. The SQL statement-based data query method according to claim 1, wherein traversing a plurality of pre-collected databases according to the tree structure information and constructing a leaf node data set comprises:
traversing a plurality of collected databases according to the tree structure information to obtain a tree data set;
performing data set processing on the tree data set to obtain a processed data set, wherein the data set processing comprises database-based and table-based processing, database compatibility processing, function expansion processing, screening processing and safety processing which are sequentially performed;
and eliminating consumption data contained in the processing data set, taking the data reserved in the processing data set as leaf nodes, and performing join calculation to form a leaf node data set.
6. The SQL statement-based data query method according to claim 5, wherein the sub-library and sub-table processing specifically comprises:
generating the tree data into an initial data set according to preset service entity structure information, wherein the service entity structure information is description information of a service entity structure and contains a mapping relation between a page field and a table;
and allocating each metadata in the initial data set to a corresponding cloud read according to business requirements, and performing horizontal table division according to each metadata field combination in the initial data set.
7. The SQL statement-based data query method according to any one of claims 1 to 6, wherein the filtering the leaf node dataset to obtain target query data comprises:
extracting filtering data from the leaf node data set by using a preset where statement;
and screening target query data from the filtering data based on a preset select statement.
8. A data query system based on SQL statements, the system comprising:
the acquisition module is used for acquiring and analyzing the SQL query statement to obtain tree structure information;
the building module is used for traversing a plurality of pre-collected databases according to the tree structure information and building a leaf node data set;
and the query module is used for filtering the leaf node data set to obtain target query data.
9. An electronic device, comprising: memory, processor and computer program stored on the memory and executable on the processor, characterized in that the processor implements the SQL statement-based data query method according to any of claims 1 to 7 when executing the program.
10. A computer-readable storage medium storing computer-executable instructions for causing a computer to perform the SQL statement-based data query method according to any one of claims 1 to 7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211562377.7A CN115757475A (en) | 2022-12-07 | 2022-12-07 | Data query method and system based on SQL (structured query language) statement |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211562377.7A CN115757475A (en) | 2022-12-07 | 2022-12-07 | Data query method and system based on SQL (structured query language) statement |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN115757475A true CN115757475A (en) | 2023-03-07 |
Family
ID=85343985
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202211562377.7A Pending CN115757475A (en) | 2022-12-07 | 2022-12-07 | Data query method and system based on SQL (structured query language) statement |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN115757475A (en) |
-
2022
- 2022-12-07 CN CN202211562377.7A patent/CN115757475A/en active Pending
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US9031895B2 (en) | Matching metadata sources using rules for characterizing matches | |
| KR100269258B1 (en) | Integrated CASE Information Repository Metamodel System for Process Methodology and its Integration Support Method | |
| Bjeladinovic et al. | A proposal of architecture for integration and uniform use of hybrid SQL/NoSQL database components | |
| CN106991276B (en) | Data interface dynamic generation method based on openEHR template | |
| CN110555032A (en) | Data blood relationship analysis method and system based on metadata | |
| JP2002297605A (en) | Method and device for structured document retrieval, and program | |
| US20060161568A1 (en) | Utilization of logical fields with conditional constraints in abstract queries | |
| KR101505858B1 (en) | A templet-based online composing system for analyzing reports or views of big data by providing past templets of database tables and reference fields | |
| CN109376153B (en) | System and method for writing data into graph database based on NiFi | |
| WO2008107665A2 (en) | A graphical user interface for use in integrating programming functions with spreadsheet applications | |
| CN110531971B (en) | Automatic generation method and device of access code, computer equipment and storage medium | |
| CN110555035A (en) | Method and device for optimizing query statement | |
| US10671631B2 (en) | Method, apparatus, and computer-readable medium for non-structured data profiling | |
| CN110209699B (en) | Data interface dynamic generation and execution method based on openEHR Composition template | |
| CN109344306B (en) | Method and system for customized online transaction and service of field multi-standard metadata | |
| KR101783791B1 (en) | Compression apparatus and method for managing provenance | |
| JPH06282576A (en) | Relational access system for network type data base | |
| CN116166718B (en) | Data blood margin acquisition method and device | |
| CN115757475A (en) | Data query method and system based on SQL (structured query language) statement | |
| CN112445867A (en) | Intelligent analysis method and system for data relationship | |
| CN113221528B (en) | Automatic generation and execution method of clinical data quality evaluation rule based on openEHR model | |
| Elamin et al. | Toward an Ontology Based Approach for Data Warehousing | |
| Marotta et al. | Managing source schema evolution in web warehouses | |
| JP2002297601A (en) | Method and device for structured document management, and program | |
| Schönig et al. | Configuring SQL-based process mining for performance and storage optimisation |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |