Query statement syntax checking method and device

Abstract

The present disclosure provides a method and device for checking the syntax of a query statement, which can be used in the financial field or other fields. The method includes: obtaining a query statement to be checked; performing a syntax check on each associated query operation in the query statement. The syntax check process includes: obtaining information on M operation objects of the associated query operation, where M is an integer greater than or equal to 2; based on the information of each of the operation objects, determining the type of each of the operation objects as a data table or a subquery; based on the judgment rules corresponding to the type of each of the operation objects, determining whether the type of the query result of each of the operation objects is a large result set or a small result set; and based on the types of the query results of the M operation objects, checking the existence of the Cartesian product in the syntax of the associated query operation. The present disclosure also provides an electronic device and a computer-readable storage medium.

Description

Technical Field

[0001] The present disclosure belongs to the field of big data technology, and more specifically, relates to a grammar checking method and device for query statements, an electronic device, and a computer-readable storage medium. Background Art

[0002] When writing SQL (structured query language) to query data, it is sometimes necessary to associate multiple tables to query data. If no association condition is specified or the association condition is invalid during the association process, the final result set will be the Cartesian product of multiple table records. The Cartesian product is a full result, including a large number of unfiltered invalid records. A large number of Cartesian product queries will consume database resources and affect the database response speed. Therefore, it is necessary to check the Cartesian product that may exist in the query statement.

[0003] In the process of implementing the concept of the present disclosure, the inventors found that there are at least the following problems in the prior art: when checking whether there is a Cartesian product in a query statement in the prior art, usually only the data table size of the associated query and whether there are associated conditions are checked. The checking method is single and not flexible enough. Summary of the invention

[0004] In view of this, the embodiments of the present disclosure provide a more flexible query statement grammar checking method and device, an electronic device, and a computer-readable storage medium.

[0005] One aspect of an embodiment of the present disclosure provides a method for checking the syntax of a query statement. The checking method includes obtaining a query statement to be checked, and performing a syntax check on each associated query operation in the query statement. The syntax checking process includes: obtaining information on M operation objects of the associated query operation, where M is an integer greater than or equal to 2; based on the information on each of the operation objects, determining that the type of each of the operation objects is a data table or a subquery; based on a judgment rule corresponding to the type of each of the operation objects, determining that the type of the query result of each of the operation objects is a large result set or a small result set; and based on the types of the query results of the M operation objects, checking the existence of a Cartesian product in the syntax of the associated query operation.

[0006] According to an embodiment of the present disclosure, determining whether the type of the query result of each operation object is a large result set or a small result set based on a judgment rule corresponding to the type of each operation object includes: when the operation object is a data table, searching for information of the operation object from whitelist information; when information of the operation object is found from the whitelist information, determining that the query result of the operation object is a small result set; and when information of the operation object cannot be found from the whitelist information, determining that the operation object is a large result set.

[0007] According to an embodiment of the present disclosure, the method also includes obtaining the whitelist information in at least one of the following ways: obtaining the whitelist information from a cache queue used to cache data tables that meet predetermined heat and predetermined data volume conditions; reading the whitelist information from a configuration file; obtaining the whitelist information by calling a table name added by a user through a remote service interface; obtaining the whitelist information by querying the table name of the small table or the naming format information of the small table from a data table in a database used to record small tables in the database; and determining the whitelist information from the table metadata information of a distributed database based on a comparison of the data volume of each data table updated in real time with a preset threshold.

[0008] According to an embodiment of the present disclosure, when the operation object is a data table, searching for information of the operation object from the whitelist information includes: querying the table name of the operation object from the cache queue; when the table name of the operation object is queried from the cache queue, determining that the query result of the operation object is a small result set, and moving the data table corresponding to the operation object to the head of the cache queue; and when the operation object is not queried in the cache queue, searching for information of the operation object from the whitelist information obtained by other means other than the cache queue.

[0009] According to an embodiment of the present disclosure, determining whether the type of the query result of each operation object is a large result set or a small result set based on a judgment rule corresponding to the type of each operation object includes: when the operation object is a subquery, searching for a filter condition statement from the statement of the operation object; when the filter condition statement is found and the filter condition statement is legal, determining that the query result of the operation object is the small result set; and when the filter condition statement is not found or the found filter condition statement is illegal, determining that the query result of the operation object is the large result set.

[0010] According to an embodiment of the present disclosure, the checking the existence of the Cartesian product in the syntax of the associated query operation based on the types of the query results of the M operation objects includes: when the types of the query results of the M operation objects meet the Cartesian product exclusion condition corresponding to the associated query operation, determining that the Cartesian product does not exist in the syntax of the associated query operation; otherwise, determining that the Cartesian product exists in the syntax of the associated query operation. The associated query operation includes a join operation or a union operation; wherein the Cartesian product exclusion conditions corresponding to the join operation and the union operation are different.

[0011] According to an embodiment of the present disclosure, when the associated query operation is a join operation, the Cartesian product exclusion condition corresponding to the associated query operation includes: at least one of the types of the query results of the M operation objects is the small result set; or when the types of the query results of the M operation objects are all large result sets, there are legal associated conditions for the M operation objects in the join operation.

[0012] According to an embodiment of the present disclosure, the checking of the existence of the Cartesian product in the syntax of the associated query operation based on the type of the query results of the M operation objects includes: when the types of the query results of the M operation objects are all large result sets, searching for the association conditions of the join operation on the M operation objects; when a legal association condition is found, determining that there is no Cartesian product in the syntax of the join operation; and when the association condition is not found, or the found association condition is illegal, determining that there is a Cartesian product in the syntax of the join operation.

[0013] According to an embodiment of the present disclosure, when the associated query operation is a union operation, the Cartesian product exclusion condition corresponding to the associated query operation includes: the types of the query results of the M operation objects are all the small result set. According to an embodiment of the present disclosure, the checking the existence of the Cartesian product in the syntax of the associated query operation based on the types of the query results of the M operation objects also includes: when the types of the query results of the M operation objects include a large result set, determining that a Cartesian product exists in the syntax of the associated query operation.

[0014] According to an embodiment of the present disclosure, the method further includes parsing the query statement to obtain an abstract syntax tree. The obtaining of information of the M operation objects of the associated query operation also includes traversing each node of the associated query operation in the abstract syntax tree; and obtaining M nodes of the operation object based on the child nodes of each node of the associated query operation.

[0015] In another aspect of the embodiments of the present disclosure, a grammar checking device for query statements is provided. The device includes a statement acquisition module and a scanning and analysis module. The statement acquisition module is used to acquire the query statement to be checked. The scanning and analysis module is used to perform grammar checking on each associated query operation in the query statement. The scanning and analysis module includes a first acquisition submodule, a first determination submodule, a result set judgment submodule, and a checking submodule. The first acquisition submodule is used to acquire information of M operation objects of the associated query operation, where M is an integer greater than or equal to 2. The first determination submodule is used to determine, based on the information of each operation object, that the type of each operation object is a data table or a subquery. The result set judgment submodule is used to determine, based on the judgment rule corresponding to the type of each operation object, whether the type of the query result of each operation object is a large result set or a small result set. The checking submodule is used to check the existence of the Cartesian product in the grammar of the associated query operation based on the types of the query results of the M operation objects.

[0016] According to an embodiment of the present disclosure, the device also includes a whitelist module. The whitelist module is used to obtain the whitelist information in at least one of the following ways: obtaining the whitelist information from a cache queue for caching data tables that meet the conditions of a predetermined heat and a predetermined data volume; reading the whitelist information from a configuration file; obtaining the whitelist information by calling the table name added by the user through a remote service interface; obtaining the whitelist information by querying the table name of the small table or the naming format information of the small table from a data table in a database used to record the small tables in the database; and determining the whitelist information from the table metadata information of the distributed database based on the comparison of the data volume of each data table updated in real time with a preset threshold.

[0017] According to an embodiment of the present disclosure, the device further includes a parsing module. The parsing module is used to parse the query statement to obtain an abstract syntax tree before performing a syntax check on each associated query operation in the query statement. The first acquisition submodule is also used to traverse each node of the associated query operation in the abstract syntax tree, and based on the child nodes of each node of the associated query operation, obtain M nodes of the operation object.

[0018] Another aspect of the embodiment of the present disclosure provides an electronic device. The electronic device includes one or more memories and one or more processors. The memories store executable instructions. The processor executes the executable instructions to implement the above method.

[0019] Another aspect of an embodiment of the present disclosure provides a computer-readable storage medium storing computer-executable instructions, which are used to implement the method described above when executed.

[0020] Another aspect of an embodiment of the present disclosure provides a computer program, wherein the computer program includes computer executable instructions, and the instructions are used to implement the method described above when executed.

[0021] One or more of the above-mentioned embodiments have the following advantages or beneficial effects: it is possible to distinguish different situations in which the operation object of the associated query operation is a data table or a subquery, and to judge whether the type of the query result of each of the operation objects is a large result set or a small result set according to the corresponding judgment rules, and then to judge whether there is a Cartesian product in an associated query operation based on the grammatical features of the associated query operation, thereby providing a more flexible Cartesian product checking solution. BRIEF DESCRIPTION OF THE DRAWINGS

[0022] The above and other objects, features and advantages of the present disclosure will become more apparent through the following description of the embodiments of the present disclosure with reference to the accompanying drawings, in which:

[0023] Figure 1 A flowchart of a method for checking the syntax of a query statement according to an embodiment of the present disclosure is schematically shown;

[0024] Figure 2 Schematically illustrating various situations of checking the Cartesian product in the syntax of the join operation according to an embodiment of the present disclosure;

[0025] Figure 3 Schematically illustrating various situations of checking the Cartesian product in the syntax of the union operation according to an embodiment of the present disclosure;

[0026] Figure 4 A flowchart schematically illustrates a method for determining the type of a query result of an operation object of an associated query operation according to an embodiment of the present disclosure;

[0027] Figure 5 The source of the whitelist information according to an embodiment of the present disclosure is schematically shown;

[0028] Figure 6 A flowchart schematically illustrates a method for determining the type of a query result of an operation object of an associated query operation according to another embodiment of the present disclosure;

[0029] Figure 7 A flowchart schematically illustrates a method for determining the type of a query result of an operation object of an associated query operation according to another embodiment of the present disclosure;

[0030] Figure 8 A flowchart of traversing an abstract syntax tree to check a Cartesian product according to an embodiment of the present disclosure is schematically shown;

[0031] Fig. 9A block diagram schematically shows a grammar checking device for query statements according to an embodiment of the present disclosure; and

[0032] Fig.10 The block diagram schematically shows an electronic device suitable for implementing the grammar checking method according to an embodiment of the present disclosure. DETAILED DESCRIPTION

[0033] Hereinafter, embodiments of the present disclosure will be described with reference to the accompanying drawings. However, it should be understood that these descriptions are exemplary only and are not intended to limit the scope of the present disclosure. In the following detailed description, for ease of explanation, many specific details are set forth to provide a comprehensive understanding of the embodiments of the present disclosure. However, it is apparent that one or more embodiments may also be implemented without these specific details. In addition, in the following description, descriptions of known structures and technologies are omitted to avoid unnecessary confusion of the concepts of the present disclosure.

[0034] The terms used herein are only for describing specific embodiments and are not intended to limit the present disclosure. The terms "comprise", "include", etc. used herein indicate the existence of the features, steps, operations and / or components, but do not exclude the existence or addition of one or more other features, steps, operations or components.

[0035] All terms (including technical and scientific terms) used herein have the meanings commonly understood by those skilled in the art, unless otherwise defined. It should be noted that the terms used herein should be interpreted as having a meaning consistent with the context of this specification, and should not be interpreted in an idealized or overly rigid manner.

[0036] When using expressions such as "at least one of A, B, and C, etc.", they should generally be interpreted according to the meaning of the expression commonly understood by those skilled in the art (for example, "a system having at least one of A, B, and C" should include but is not limited to a system having A alone, B alone, C alone, A and B, A and C, B and C, and / or A, B, C, etc.).

[0037] With the development of the digital economy, there are a large number of database-based data analysis needs every day. If there are a large number of queries that generate Cartesian products, it will inevitably affect the efficiency of the entire analysis. In the prior art, the Cartesian product is checked by checking the size of the data table of the associated query and whether it has an associated condition. There are relatively large limitations. On the one hand, in some business scenarios, some parameter tables and dictionary tables are tables with relatively small data volumes. Even if there is a so-called Cartesian product when the associated query between these data tables occurs, the amount of data query is not large, and the impact on the database system is relatively small. In this case, it can actually be considered that there is no Cartesian product, which can make the query proceed normally and reduce the workload of developers to repair the code. On the other hand, in the actual development process, the operation objects of the associated query are not necessarily all data tables, and may also be subqueries, etc., and the prior art can only be applied to the case where the operation object of the associated query is two data tables, and cannot be applied to the case where the operation object of the associated query includes a subquery.

[0038] In view of this, the present disclosure provides a more flexible syntax checking method, device, electronic device, and medium for query statements, which can distinguish different situations in which the operation object of an associated query operation is a data table or a subquery, and judge whether the type of the query result of each operation object is a large result set or a small result set according to the corresponding judgment rules, and then judge whether there is a Cartesian product in an associated query operation based on the grammatical features of the associated query operation.

[0039] Specifically, the method according to the embodiment of the present disclosure may include first obtaining a query statement to be checked, and then performing a syntax check on each associated query operation in the query statement. The process of performing a syntax check on each associated query operation includes first obtaining information on M operation objects of the associated query operation, where M is an integer greater than or equal to 2; then based on the information on each operation object, determining the type of each operation object as a data table or a subquery; then based on the judgment rule corresponding to the type of each operation object, determining whether the type of the query result of each operation object is a large result set or a small result set; and based on the types of the query results of the M operation objects, checking the existence of the Cartesian product in the syntax of the associated query operation.

[0040] According to an embodiment of the present disclosure, the query result of the operation object refers to the result obtained from each operation object in the association query operation, wherein the association query operation is to associate the query results of the M operation objects to be associated to obtain the query result of the association query operation.

[0041] According to an embodiment of the present disclosure, small result sets and large result sets are classified according to whether the data volume of a query result of an operation object (data table or subquery) is expected to be controllable.

[0042] For example, for a data table with a small amount of data (which can be referred to as a small table), even if a full query is performed, the query results obtained belong to a small result set. Alternatively, if the subquery contains a legal filter condition statement (for example, a where filter condition), the query results obtained also belong to a small result set.

[0043] For another example, when a query operation is performed on a data table (referred to as a large table) with a large data volume (the specific standard is defined according to actual needs), the query result obtained can be considered to belong to a large result set. Alternatively, when there is no filter condition in a subquery in an associated query operation, or even if there is a filter condition but the filter condition is illegal, the query result obtained can also be considered to belong to a large result set.

[0044] The criteria or methods for distinguishing between small tables and large tables can be determined according to actual needs. According to some disclosed embodiments, whether a data table belongs to a small table can be determined by whether the data table belongs to the whitelist information.

[0045] In an embodiment of the present disclosure, after determining whether the operation object (i.e., a data table or a subquery) of an associated query operation is a large result set or a small result set, further analysis is performed in combination with the grammatical features of the associated query operation to determine whether the data volume of the query result of the associated query operation is expected to be controllable, thereby determining whether a Cartesian product exists in the syntax of the associated query operation.

[0046] It should be noted that the query statement syntax checking method and device determined in the embodiments of the present disclosure can be used in the financial field, and can also be used in any field other than the financial field. The present disclosure does not limit the application field.

[0047] Figure 1 The flowchart of the grammar checking method of the query statement according to the embodiment of the present disclosure is schematically shown.

[0048] like Figure 1 As shown, the grammar checking method for a query statement according to this embodiment may include operations S110 to S150.

[0049] In operation S110, a SQL query statement to be checked is obtained.

[0050] In operation S120, for each associated query operation in the query statement, information of M operation objects of the associated query operation is obtained, where M is an integer greater than or equal to 2.

[0051] In SQL language, an associative query operation may be, for example, a join operation or a union operation, wherein the union operation may be divided into two types, union and union all, which are collectively referred to as union operations in the embodiments of the present disclosure. Among them, a join operation may generally associate two operation objects (e.g., a data table or a subquery). A union operation also generally associates two operation objects. Of course, the embodiments of the present disclosure are not limited thereto, and the present disclosure may also be applicable to other associative query operation statements and situations that may appear in the query language in the future.

[0052] According to some embodiments of the present disclosure, an abstract syntax tree can be obtained by parsing a query statement, and then each node associated with a query operation in the abstract syntax tree is traversed, and M operation object nodes are obtained based on the child nodes of each node associated with a query operation. In this way, information on the M operation objects of each associated query operation is obtained.

[0053] In operation S130 , based on the information of each operation object, it is determined whether the type of each operation object is a data table or a subquery.

[0054] In operation S140 , based on a judgment rule corresponding to the type of each operation object, it is determined whether the type of the query result of each operation object is a large result set or a small result set.

[0055] In operation S150 , based on the types of the query results of the M operation objects, the existence of a Cartesian product in the syntax of the associated query operation is checked.

[0056] Specifically, the checking result in operation S150 may be that when the types of the query results of the M operation objects as a whole meet the Cartesian product exclusion condition corresponding to the associated query operation, it is determined that there is no Cartesian product in the syntax of the associated query operation; otherwise, it is determined that there is a Cartesian product in the syntax of the associated query operation.

[0057] Among them, for the association query operation is a join operation or a union operation, the corresponding Cartesian product exclusion conditions are different. For this, you can refer to the following Figure 2 and Figure 3 Introduction.

[0058] Figure 2 Schematic diagrams of various situations of checking the Cartesian product in the syntax of a join operation according to an embodiment of the present disclosure are schematically shown. Figure 2 Parts (a), (b), and (c) illustrate the local structures of three abstract syntax trees with join as the parent node.

[0059] First reference Figure 2In (a) and (b), according to an embodiment of the present disclosure, when the associated query operation is a join operation, the Cartesian product exclusion condition corresponding to the associated query operation can be that at least one of the types of the query results of the M operation objects is a small result set. This is because the query data volume of the join operation is determined by the part of the results with the smallest query volume in the operation object. That is, Figure 2 In the join operations shown in (a) and (b), we can ignore the existence of the association condition and directly conclude that there is no Cartesian product.

[0060] For example, suppose Figure 2 In (b), the small result set of the left node of the join operation is extracted from a small table (for example, a data table with only 50 records), and the right node is extracted from a large table (for example, a data table with 1 million records). In this case, regardless of whether the join operation has a join condition for the small table and the large table (i.e., o n condition), the maximum amount of query data for this join operation will not exceed 50. Therefore, in this case, it can be directly determined that there is no Cartesian product in this join.

[0061] Next reference Figure 2 (c) According to another embodiment of the present disclosure, when the associated query operation is a join operation, if the query results of the M operation objects are all large result sets, it does not necessarily mean that a Cartesian product exists. In this case, the Cartesian product exclusion condition corresponding to the join operation further includes the presence of a legal associated condition in the syntax of the join operation.

[0062] like Figure 2 As shown in (c), it is possible to find out from the abstract syntax tree whether there is an on node in the branch structure with the join operation as the parent node. If it exists, further analyze whether the association conditions under the on node are legal and valid. A legal association condition is a condition that can filter the associated data of two tables, such as: table 1. column name 1 = table 2. column name 1 AND table 1. column name 2 = table 2. column name 2. The association query operation performed under legal conditions is not determined to have a Cartesian product. An illegal condition is a condition that is always true and does not have the function of filtering the number of records, such as: 1 = 1 OR (other conditions), table name. column name = table name. column name, etc. According to an embodiment of the present disclosure, when the query results of multiple operation objects of the Join operation are all large result sets, the association conditions for the multiple operation objects in the Join operation can be further searched. When a legal association condition is found, it is determined that there is no Cartesian product in the syntax of the Join operation. If the association condition cannot be found or the association condition found is illegal, it is determined that there is a Cartesian product in the syntax of the Join operation.

[0063] Figure 3 Schematic diagrams of various situations of checking the Cartesian product in the syntax of the union operation according to an embodiment of the present disclosure are schematically shown. Figure 3 Parts (a), (b), and (c) illustrate three local structures of abstract syntax trees with union as the parent node.

[0064] First reference Figure 3 (a), according to an embodiment of the present disclosure, when the associated query operation is a union operation, the Cartesian product exclusion condition corresponding to the associated query operation includes: the query results of the M operation objects are all small result sets. Figure 3 The union operation shown in (a) does not have a Cartesian product. Accordingly, Figure 3 (b) and (c) The query result of at least one of the operation objects of the union operation belongs to the large result set, and the union operation has a Cartesian product.

[0065] This is because the union operation merges the results of the operation object (data table or subquery) together. Therefore, if the query result type of the operation object of the union operation includes a large result set, this means that the data volume of the query result of the union operation must exceed the data volume of the large result set, and thus the data volume of the query result of the union operation is expected to be uncontrollable.

[0066] It can be seen that according to the method of the embodiment of the present disclosure, the syntax of the associated query operation is more flexible and diverse when performing Cartesian product query, thereby reducing the occupation of database resources by query statements that generate Cartesian products.

[0067] Figure 4 The flowchart schematically shows the type of the query result of the operation object of the associated query operation in operation S140 according to an embodiment of the present disclosure. Figure 4 This is aimed at determining the type of query result of the operation object when the operation object is a data table.

[0068] like Figure 4 As shown, according to this embodiment, operation S140 may include operations S441 to S444.

[0069] First, in operation S441, when the operation object is a data table, information of the operation object is searched from the whitelist information.

[0070] Then, in operation S442, it is determined whether the information of the operation object is found in the whitelist information. If it is found, operation S443 is executed, and if not, operation S444 is executed.

[0071] In operation S443, when information of the operation object is found from the whitelist information, it is determined that the query result of the operation object is a small result set.

[0072] In operation S444, when the information of the operation object cannot be found in the whitelist information, it is determined that the query result of the operation object is a large result set.

[0073] The disclosed embodiment can provide a judgment measure through a whitelist mechanism to determine whether the query volume of the data table involved in the associated query operation is expected to be controllable.

[0074] Figure 5 The source of the whitelist information according to an embodiment of the present disclosure is schematically shown.

[0075] like Figure 5 As shown, according to this embodiment, the whitelist information 501 may come from any at least one of a cache queue 511 , a configuration file 512 , a remote service interface 513 , a database 514 , or table metadata 515 .

[0076] Specifically, for example, the whitelist information 501 can be obtained from a cache queue 511 for caching data tables that meet the conditions of a predetermined heat and a predetermined data volume. Generally, in database queries, some data tables with small data volumes but frequent queries (usually referred to as hot tables) are often placed in the cache to facilitate quick queries. Therefore, the disclosed embodiment can use this mechanism to use the data table name in the cache queue 511 as the source of the whitelist information 501.

[0077] For another example, the whitelist information 501 may be read from the configuration file 512. The configuration file 512 may be, for example, a file in a variety of formats, including but not limited to JSON, XML, CSV, etc. The user may write the table names of the qualified or specified small tables into the configuration file, and then in operation S443, these table names may be read from the configuration file 512 to obtain the whitelist information 501.

[0078] For another example, the table name added by the user can be called through the remote service interface 513 to obtain the whitelist information 501. Some users can maintain table names that can be used as whitelist information locally on their own devices, and then provide a remote service interface for calling. In this way, in operation S443, the table name can be read by calling the remote service interface.

[0079] For another example, the table name of the small table or the naming format information of the small table can be queried from the data table in the database 514 for recording the small table in the database to obtain the whitelist information 501. For example, the database developer or maintenance personnel maintains a data table specifically used to record the small table in the database in the database, so that in operation S443, the information in the data table recording the small table in the database 514 can be queried by connecting to the database 514 to obtain the whitelist information 501. Among them, in actual applications, different databases may have different ways of recording small tables. For example, some databases may distinguish small tables in a specific naming format. In this way, the naming format of the small table can be recorded in the whitelist information 501 in operation S443, so that if the naming format of a data table conforms to the naming format of the small table, it can be considered that the data table belongs to the small table, and the query result corresponding to the table is the small result set. According to other embodiments of the present disclosure, it is also possible to match the whitelist information in multiple heterogeneous data sources and multi-cluster scenarios by specific settings of multiple dimensions such as cluster name, database type, schema name, library name, table name, user name, etc.

[0080] For example, the whitelist information 501 can be determined from the table metadata information 515 of the distributed database based on the comparison of the data volume of each data table updated in real time with the preset threshold. For example, when executing operation S430, you can connect to the table metadata information in the corresponding HDFS, query the actual size of the table to be checked, and then compare it with the threshold to determine whether the table to be checked is a small table. For products based on HDFS at the bottom layer, such as Hive and HBase, the table size information can be obtained in real time by directly querying the size of the data file on HDFS. By setting a size threshold, the large and small tables are distinguished by comparing the actual size of the table with the threshold. Different methods are switched through switches to flexibly use the whitelist information 501. By combining the database metadata information, dynamic data of the table data volume is obtained, and the judgment of the large and small tables is dynamically controlled.

[0081] According to an embodiment of the present disclosure, in operation S441, when the operation object is a data table, when searching for information of the operation object from the whitelist information 501, the table name of the operation object can be first queried from the cache queue 511. When the table name of the operation object is queried from the cache queue, it is determined that the query result of the operation object is a small result set, and the data table corresponding to the operation object can be moved to the head of the cache queue (this indicates that the popularity of the table has increased, so it is moved to the head of the queue in advance). When the table name of the operation object is not queried in the cache queue 511, it can be queried from the cache queue 511. Figure 5 The whitelist information is obtained by other means except the cache queue 511 to determine whether the query result of the data table as the operation object is a small result set.

[0082] According to the disclosed embodiment, a syntax check can be performed before the query statement is about to be executed. At this time, the whitelist information is first obtained from the cache queue. If it is found, it means that the data table found is hot, and the data of the table can be directly read from the cache queue when the query statement is executed.

[0083] The disclosed embodiment provides a more fine-grained configuration method for whitelist information, which can meet the different needs of different users and different situations for whitelist rules. The whitelist information can adopt a limited cache and old data removal strategy, configure the limited number of parameters, and cache the table names of small data volumes, parameter table names, and other table names that can perform Cartesian product queries.

[0084] Figure 6 The flowchart schematically shows operation S140 of determining the type of the query result of the operation object of the associated query operation according to another embodiment of the present disclosure. Figure 6 The illustration is for the case where the operation object is a subquery.

[0085] like Figure 6 As shown, according to an embodiment of the present disclosure, operation S140 may include operations S641 to S645.

[0086] First, in operation S641, when the operation object is a subquery, a filtering condition statement (eg, a where condition statement) is searched from the statement of the operation object.

[0087] Then, in operation S642, it is determined whether a filtering condition statement is found. If so, operation S643 is performed, and if not, operation S645 is performed.

[0088] In operation S643, when a filter condition statement is found, it is determined whether the filter condition statement is legal. If it is legal, operation S644 is executed, and if it is not legal, operation S645 is executed.

[0089] In operation S644, if a filter condition statement is found and the filter condition statement is legal, it is determined that the query result of the operation object is a small result set.

[0090] In operation S645, when no filter condition statement is found or the found filter condition statement is illegal, it is determined that the query result of the operation object is a large result set.

[0091] Figure 7 A flowchart for determining the type of a query result of an operation object of an associated query operation according to yet another embodiment of the present disclosure is schematically shown.

[0092] like Figure 7 As shown, the process may include steps S701 to S713.

[0093] Step S701: Determine whether the type of the current abstract syntax tree node is an identifier type. In this embodiment, the operation object represented by the identifier type node is a data table. If yes, jump to step S702; if no, jump to step S710.

[0094] Step S702: Determine whether the table name already exists in the cache queue 510 for storing small tables. If yes, execute step S703; if no, jump to step S704.

[0095] Step S703: Move the data table to the head of the cache queue.

[0096] Step S704: Check whether the table name exists in the whitelist configuration by remote calling service (for example, RPC, RESTful, service interface method), etc. If yes, jump to step S705; if not, it is determined to be a large table.

[0097] Step S705: Prepare to add the new table to the cache queue. The cache queue has a capacity limit. When adding a new table, first determine whether the cache queue is full. If so, execute step S706; if not, execute step S707.

[0098] Step S706: The table at the end of the cache queue is considered to be a table with a small number of accesses. When the queue is full, the table at the end of the queue is removed. Jump to step S707.

[0099] Step S707: insert the newly added table at the head of the queue.

[0100] Step S708: The table in the queue is determined to be a small table.

[0101] Step S709: The table that is not in the queue is determined to be a large table.

[0102] Step S710: For a node whose node type is not an identifier, determine whether it is a select subquery node. If so, jump to step S711. If not, jump to step S712.

[0103] Step S711: Determine whether there is a legal where condition in the select subquery node, that is, a condition that can filter the table in the select. If yes, jump to step S712. If not, jump to step S713.

[0104] Step S712: For non-identifier nodes, determine them as small result sets.

[0105] Step S713: For non-identifier nodes, determine them as large result sets.

[0106] Figure 8The flowchart of traversing an abstract syntax tree to check a Cartesian product according to an embodiment of the present disclosure is schematically shown.

[0107] like Figure 8 As shown, the process may include steps S801 to S807.

[0108] Step S801: traverse the nodes of the abstract syntax tree. The traversal adopts a depth-first or breadth-first search method to ensure that each node can be traversed.

[0109] Step S802: If the current node is a join node, jump to step S805. If not, execute step S803.

[0110] Step S803: If the current node is a union node, jump to step S805. If not, execute step S804

[0111] Step S804: For other node types other than join and union nodes, obtain a list of child nodes. If there are child nodes, return to step S801. Otherwise, the traversal is completed and the entire process ends.

[0112] Step S805: Utilize Figure 7 The judgment process of the example determines the size of the result set of the left and right child nodes of the join node and the union node.

[0113] Step S806: Check whether the result set sizes of the left and right child nodes meet the following rules: if the parent node is a join node, both the left and right child nodes are large tables or large result sets; if the parent node is a union node, at least one of the left and right child nodes is a large result set. If the above rules are met, execute step S807. If not, return to step S801.

[0114] Step S807: Add the node information that meets the requirements of the large table or large result set to the final result set. After the entire process is completed, the result set is returned to the maintainer for Cartesian product judgment. The node information usually includes: the row number and column number of the SQL statement where the node is located, and the SQL statement fragment corresponding to the node.

[0115] Fig. 9 A block diagram of a grammar checking device 900 for a query statement according to an embodiment of the present disclosure is schematically shown.

[0116] like Fig. 9 As shown, the query statement syntax checking device 900 according to the embodiment of the present disclosure may include a statement acquisition module 910 and a scanning analysis module 920. According to other embodiments of the present disclosure, the device 900 may further include a whitelist module 930 and / or a parsing module 940. The device 900 may be used to implement reference Figures 1 to 8 Describes the syntax checking method for query statements.

[0117] The statement acquisition module 910 is used to acquire the query statement to be checked.

[0118] The scanning and analysis module 920 is used to perform a syntax check on each associated query operation in the query statement. In one embodiment, the scanning and analysis module 920 can traverse the generated abstract syntax tree, starting from the root node, and traversing all nodes.

[0119] When traversing to the join node, determine whether the query results of the left and right child nodes belong to the large result set or the small result set, and then analyze whether there is a Cartesian product based on the analysis rules. The basic rule is: if the child nodes on both sides of the join node are table names, check whether there is a small table (the corresponding query results belong to the small result set). If there are small tables in the child nodes on both sides of the join node, it is not determined to be a Cartesian product; if there is no small table, it is necessary to check whether the join operation has an on association condition, and the association condition must be a legal condition, that is, a condition that can filter the associated data of the two tables. If the child nodes on both sides of the join node are not table name nodes, the following situations are considered to have a Cartesian product: a large table joins a large result set, and a large result set joins a large result set.

[0120] When traversing to the union node, the nodes on both sides of the union are regarded as independent syntax tree root nodes. If one of the left and right child nodes belongs to a large result set or a large table, it is considered that a Cartesian product exists.

[0121] Specifically, the scanning and analysis module 920 may include a first acquisition submodule 921 , a first determination submodule 922 , a result set judgment submodule 923 , and a checking submodule 924 .

[0122] The first acquisition submodule 921 is used to acquire information of M operation objects of the associated query operation, where M is an integer greater than or equal to 2.

[0123] The first determination submodule 922 is used to determine, based on the information of each operation object, whether the type of each operation object is a data table or a subquery.

[0124] The result set judgment submodule 923 is used to determine whether the type of the query result of each operation object is a large result set or a small result set based on the judgment rules corresponding to the type of each operation object. Specifically, for example, the node type can be analyzed for the child nodes of the associated operation in the abstract syntax tree, and whether it is a large result set is returned. For the node type is an identifier (i.e., the table name), by querying the whitelist information, if the query is marked as a small result set, otherwise it is marked as a large result set. For the node type is not an identifier, it is determined whether the subquery has a legal where condition for screening, and if so, it is marked as a small result set. Otherwise, it is marked as a large result set.

[0125] The checking submodule 924 is used to check the existence of the Cartesian product in the syntax of the associated query operation based on the types of the query results of the M operation objects.

[0126] According to an embodiment of the present disclosure, the whitelist module 930 is used to obtain whitelist information in at least one of the following ways: obtaining whitelist information from a cache queue used to cache data tables that meet predetermined heat and predetermined data volume conditions; reading whitelist information from a configuration file; obtaining whitelist information by calling a table name added by a user through a remote service interface; obtaining whitelist information by querying the table name of a small table or the naming format information of a small table from a data table in a database used to record small tables in the database; and determining whitelist information from the table metadata information of a distributed database based on a comparison of the data volume of each data table updated in real time with a preset threshold.

[0127] According to an embodiment of the present disclosure, the parsing module 940 is used to parse the query statement before performing a syntax check on each associated query operation in the query statement to obtain an abstract syntax tree. The first acquisition submodule is also used to traverse each associated query operation node in the abstract syntax tree, and based on the child nodes of each associated query operation node, obtain M operation object nodes.

[0128] According to the embodiments of the present invention, any one or more of the modules, submodules, units, and subunits, or at least part of the functions of any one of them can be implemented in one module. According to the embodiments of the present invention, any one or more of the modules, submodules, units, and subunits can be split into multiple modules for implementation. According to the embodiments of the present invention, any one or more of the modules, submodules, units, and subunits can be at least partially implemented as hardware circuits, such as field programmable gate arrays (FPGAs), programmable logic arrays (PLAs), systems on chips, systems on substrates, systems on packages, application specific integrated circuits (ASICs), or can be implemented by hardware or firmware in any other reasonable way of integrating or packaging the circuit, or implemented in any one of the three implementation methods of software, hardware, and firmware, or in any appropriate combination of any of them. Alternatively, according to the embodiments of the present invention, one or more of the modules, submodules, units, and subunits can be at least partially implemented as computer program modules, and when the computer program modules are run, the corresponding functions can be performed.

[0129] For example, any multiple of the statement acquisition module 910, the scanning and analysis module 920, the whitelist module 930, the parsing module 940, the first acquisition submodule 921, the first determination submodule 922, the result set judgment submodule 923, and the inspection submodule 924 can be combined into one module for implementation, or any one of the modules can be split into multiple modules. Alternatively, at least part of the functions of one or more of these modules can be combined with at least part of the functions of other modules and implemented in one module. According to an embodiment of the present disclosure, at least one of the statement acquisition module 910, the scanning and analysis module 920, the whitelist module 930, the parsing module 940, the first acquisition submodule 921, the first determination submodule 922, the result set judgment submodule 923, and the inspection submodule 924 can be at least partially implemented as a hardware circuit, such as a field programmable gate array (FPGA), a programmable logic array (PLA), a system on a chip, a system on a substrate, a system on a package, an application-specific integrated circuit (ASIC), or can be implemented by hardware or firmware such as any other reasonable way of integrating or packaging the circuit, or by any one of the three implementation methods of software, hardware and firmware or by an appropriate combination of any of them. Alternatively, at least one of the statement acquisition module 910, the scanning and analysis module 920, the whitelist module 930, the parsing module 940, the first acquisition submodule 921, the first determination submodule 922, the result set judgment submodule 923, and the inspection submodule 924 can be at least partially implemented as a computer program module, and when the computer program module is run, the corresponding function can be executed.

[0130] Fig.10The block diagram of an electronic device 1000 suitable for implementing the grammar checking method according to an embodiment of the present disclosure is schematically shown. Fig.10 The electronic device shown is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present disclosure.

[0131] like Fig.10 As shown, the electronic device 1000 according to an embodiment of the present disclosure includes a processor 1001, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 1002 or a program loaded from a storage part 1008 into a random access memory (RAM) 1003. The processor 1001 may include, for example, a general-purpose microprocessor (e.g., a CPU), an instruction set processor and / or a related chipset and / or a special-purpose microprocessor (e.g., an application-specific integrated circuit (ASIC)), etc. The processor 1001 may also include an onboard memory for caching purposes. The processor 1001 may include a single processing unit or multiple processing units for performing different actions of the method flow according to an embodiment of the present disclosure.

[0132] In RAM 1003, various programs and data required for the operation of electronic device 1000 are stored. Processor 1001, ROM 1002 and RAM 1003 are connected to each other via bus 1004. Processor 1001 performs various operations of the method flow according to the embodiment of the present disclosure by executing the program in ROM 1002 and / or RAM 1003. It should be noted that the program can also be stored in one or more memories other than ROM 1002 and RAM 1003. Processor 1001 can also perform various operations of the method flow according to the embodiment of the present disclosure by executing the program stored in the one or more memories.

[0133] According to an embodiment of the present disclosure, the electronic device 1000 may further include an input / output (I / O) interface 1005, which is also connected to the bus 1004. The electronic device 1000 may further include one or more of the following components connected to the I / O interface 1005: an input portion 1006 including a keyboard, a mouse, etc.; an output portion 1007 including a cathode ray tube (CRT), a liquid crystal display (LCD), etc., and a speaker, etc.; a storage portion 1008 including a hard disk, etc.; and a communication portion 1009 including a network interface card such as a LAN card, a modem, etc. The communication portion 1009 performs communication processing via a network such as the Internet. A drive 1010 is also connected to the I / O interface 1005 as needed. A removable medium 1011, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 1010 as needed, so that a computer program read therefrom is installed into the storage portion 1008 as needed.

[0134] According to an embodiment of the present disclosure, the method flow according to an embodiment of the present disclosure can be implemented as a computer software program. For example, an embodiment of the present disclosure includes a computer program product, which includes a computer program carried on a computer-readable storage medium, and the computer program contains a program code for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from the network through the communication part 1009, and / or installed from the removable medium 1011. When the computer program is executed by the processor 1001, the above-mentioned functions defined in the system of the embodiment of the present disclosure are executed. According to an embodiment of the present disclosure, the system, equipment, device, module, unit, etc. described above can be implemented by a computer program module.

[0135] The present disclosure also provides a computer-readable storage medium, which may be included in the device / apparatus / system described in the above embodiments; or may exist independently without being assembled into the device / apparatus / system. The above computer-readable storage medium carries one or more programs, and when the above one or more programs are executed, the method according to the embodiment of the present disclosure is implemented.

[0136] According to an embodiment of the present disclosure, a computer-readable storage medium may be a non-volatile computer-readable storage medium, for example, may include but is not limited to: a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination thereof. In the present disclosure, a computer-readable storage medium may be any tangible medium containing or storing a program, which may be used by or in combination with an instruction execution system, an apparatus or a device. For example, according to an embodiment of the present disclosure, a computer-readable storage medium may include the ROM 1002 and / or RAM 1003 described above and / or one or more memories other than ROM 1002 and RAM 1003.

[0137] An embodiment of the present disclosure also includes a computer program product, which includes a computer program, and the computer program contains a program code for executing the method provided by the embodiment of the present disclosure. When the computer program product runs on an electronic device, the program code is used to enable the electronic device to implement the image recognition method provided by the embodiment of the present disclosure.

[0138] When the computer program is executed by the processor 1001, the above functions defined in the system / device of the embodiment of the present disclosure are executed. According to the embodiment of the present disclosure, the system, device, module, unit, etc. described above can be implemented by a computer program module.

[0139] In one embodiment, the computer program may rely on tangible storage media such as optical storage devices, magnetic storage devices, etc. In another embodiment, the computer program may also be transmitted and distributed in the form of signals on a network medium, and downloaded and installed through the communication part 1009, and / or installed from the removable medium 1011. The program code contained in the computer program may be transmitted using any appropriate network medium, including but not limited to: wireless, wired, etc., or any suitable combination of the above.

[0140] According to an embodiment of the present disclosure, the program code for executing the computer program provided by the embodiment of the present disclosure can be written in any combination of one or more programming languages. Specifically, these computing programs can be implemented using high-level process and / or object-oriented programming languages, and / or assembly / machine languages. Programming languages ​​include, but are not limited to, Java, C++, python, "C" language or similar programming languages. The program code can be executed entirely on the user computing device, partially on the user device, partially on the remote computing device, or entirely on the remote computing device or server. In the case of a remote computing device, the remote computing device can be connected to the user computing device through any type of network, including a local area network (LAN) or a wide area network (WAN), or can be connected to an external computing device (for example, using an Internet service provider to connect through the Internet).

[0141] The flow charts and block diagrams in the accompanying drawings illustrate the possible architecture, functions and operations of the systems, methods and computer program products according to various embodiments of the present disclosure. In this regard, each box in the flow chart or block diagram can represent a module, a program segment, or a part of a code, and the above-mentioned module, program segment, or a part of a code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the box can also occur in a different order from the order marked in the accompanying drawings. For example, two boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram or flow chart, and the combination of the boxes in the block diagram or flow chart can be implemented with a dedicated hardware-based system that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

[0142] It will be appreciated by those skilled in the art that the features described in the various embodiments and / or claims of the present disclosure may be combined and / or combined in a variety of ways, even if such combinations and / or combinations are not explicitly described in the present disclosure. In particular, the features described in the various embodiments and / or claims of the present disclosure may be combined and / or combined in a variety of ways without departing from the spirit and teachings of the present disclosure. All of these combinations and / or combinations fall within the scope of the present disclosure.

[0143] The embodiments of the present disclosure are described above. However, these embodiments are only for illustrative purposes and are not intended to limit the scope of the present disclosure. Although the embodiments are described above separately, this does not mean that the measures in the various embodiments cannot be used in combination to advantage. The scope of the present disclosure is defined by the attached claims and their equivalents. Without departing from the scope of the present disclosure, those skilled in the art may make a variety of substitutions and modifications, which should all fall within the scope of the present disclosure.

Claims

1. A method for checking the syntax of a query statement, comprising: Get the query statement to be checked; Performing a syntax check on each associated query operation in the query statement includes: Obtain information of M operation objects of the associated query operation, where M is an integer greater than or equal to 2; Based on the information of each of the operation objects, determining the type of each of the operation objects is a data table or a subquery; Based on a judgment rule corresponding to the type of each of the operation objects, determining whether the type of the query result of each of the operation objects is a large result set or a small result set; and Based on the types of the query results of the M operation objects, checking the existence of a Cartesian product in the syntax of the associated query operation; Wherein, determining whether the type of the query result of each operation object is a large result set or a small result set based on a judgment rule corresponding to the type of each operation object includes: When the operation object is a data table, searching for information of the operation object from the whitelist information; when the information of the operation object is found from the whitelist information, determining that the query result of the operation object is a small result set; and when the information of the operation object is not found from the whitelist information, determining that the query result of the operation object is a large result set; When the operation object is a subquery, a filter condition statement is searched from the statement of the operation object; when the filter condition statement is found and the filter condition statement is legal, the query result of the operation object is determined to be the small result set; and when the filter condition statement is not found or the found filter condition statement is illegal, the query result of the operation object is determined to be the large result set.

2. The method according to claim 1, wherein: The method further includes obtaining the whitelist information in at least one of the following ways, including: Acquire the whitelist information from a cache queue for caching data tables that meet predetermined heat and predetermined data volume conditions; Read the whitelist information from the configuration file; Call the table name added by the user through the remote service interface to obtain the whitelist information; Querying the table name of the small table or the naming format information of the small table from a data table in the database for recording the small table in the database to obtain the whitelist information; and The whitelist information is determined from the table metadata information of the distributed database based on a comparison between the data volume of each data table updated in real time and a preset threshold.

3. The method according to claim 2, wherein: When the operation object is a data table, searching the whitelist information for information of the operation object includes: Query the table name of the operation object from the cache queue; When the table name of the operation object is queried from the cache queue, determining that the query result of the operation object is a small result set, and moving the data table corresponding to the operation object to the head of the cache queue; and When the operation object is not found in the cache queue, the information of the operation object is searched from the whitelist information obtained in a manner other than the cache queue.

4. The method according to any one of claims 1 to 3, wherein: The checking the existence of the Cartesian product in the syntax of the associated query operation based on the types of the query results of the M operation objects includes: When the types of the query results of the M operation objects meet the Cartesian product exclusion condition corresponding to the associated query operation, determining that there is no Cartesian product in the syntax of the associated query operation; otherwise, determining that there is a Cartesian product in the syntax of the associated query operation; in, The associated query operation includes a join operation or a union operation; wherein the Cartesian product exclusion conditions corresponding to the join operation and the union operation are different.

5. The method according to claim 4, wherein: When the associated query operation is a join operation, the Cartesian product exclusion condition corresponding to the associated query operation includes: At least one of the types of the query results of the M operation objects is the small result set; or When the types of the query results of the M operation objects are all large result sets, there are legal association conditions for the M operation objects in the join operation.

6. The method according to claim 5, wherein: The checking the existence of the Cartesian product in the syntax of the associated query operation based on the types of the query results of the M operation objects includes: When the types of the query results of the M operation objects are all large result sets, searching for association conditions of the join operation on the M operation objects; When a legal association condition is found, determining that a Cartesian product does not exist in the syntax of the join operation; and When the association condition cannot be found or the found association condition is illegal, it is determined that a Cartesian product exists in the syntax of the join operation.

7. The method according to claim 4, wherein: When the associated query operation is a union operation, the Cartesian product exclusion condition corresponding to the associated query operation includes: the types of the query results of the M operation objects are all the small result sets.

8. The method according to claim 7, wherein: The checking the existence of a Cartesian product in the syntax of the associated query operation based on the types of the query results of the M operation objects further includes: When the types of the query results of the M operation objects include a large result set, it is determined that a Cartesian product exists in the syntax of the associated query operation.

9. The method according to claim 1, wherein: The method further comprises: parsing the query statement to obtain an abstract syntax tree; The acquiring information of the M operation objects of the associated query operation includes: traversing each node of the associated query operation in the abstract syntax tree; and obtaining M nodes of the operation object based on child nodes of each node of the associated query operation.

10. A grammar checking device for a query statement, comprising: A statement acquisition module is used to acquire the query statement to be checked; The scanning and analysis module is used to perform syntax checking on each associated query operation in the query statement, including: A first acquisition submodule is used to acquire information of M operation objects of the associated query operation, where M is an integer greater than or equal to 2; A first determination submodule, configured to determine, based on information of each operation object, whether the type of each operation object is a data table or a subquery; A result set judgment submodule, configured to determine whether the type of the query result of each operation object is a large result set or a small result set based on a judgment rule corresponding to the type of each operation object; and A checking submodule, configured to check the existence of a Cartesian product in the syntax of the associated query operation based on the types of the query results of the M operation objects; The result set determination submodule is specifically used for: When the operation object is a data table, searching for information of the operation object from the whitelist information; when the information of the operation object is found from the whitelist information, determining that the query result of the operation object is a small result set; and when the information of the operation object is not found from the whitelist information, determining that the query result of the operation object is a large result set; When the operation object is a subquery, a filter condition statement is searched from the statement of the operation object; when the filter condition statement is found and the filter condition statement is legal, the query result of the operation object is determined to be the small result set; and when the filter condition statement is not found or the found filter condition statement is illegal, the query result of the operation object is determined to be the large result set.

11. An electronic device, comprising: One or more memories storing executable instructions; as well as One or more processors execute the executable instructions to implement the method according to any one of claims 1 to 9.

12. A computer-readable storage medium having executable instructions stored thereon, which, when executed by a processor, causes the processor to execute the method according to any one of claims 1 to 9.

Images