A method, device and equipment for determining a structured query statement for full table scanning
A method and device for determining full table scans in SQL queries by converting statements to syntax trees and filtering table names and fields, enhancing database efficiency.
Patent Information
- Application Number
- CN202110751806.4
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2021-07-02
- Publication Date
- 2024-12-17
- Estimated Expiration
- 2041-07-02
AI Technical Summary
Existing methods are inefficient in determining whether a structured query statement (SQL) is a full table scan, which can lead to resource wastage and inefficiency in database operations.
A method and device to determine whether a structured query statement is converted into a syntax tree, which traverses the nodes to determine the field information set, which includes a syntax tree, and determines the field information set, and determines the field syntax tree, which includes a syntax tree, and determines the field information.
Efficiently and accurately identifies full table scans in SQL queries, optimizing database resource utilization.
Smart Images
Figure CN113448965B_ABST
Abstract
Description
Technical Field
[0001] The embodiments of this specification relate to the field of big data technology, and in particular to a method, device and equipment for determining a structured query statement for a full table scan. Background Art
[0002] When writing SQL (Structured Query Language) to query data, sometimes a full table scan is involved. If any field is not filtered during the query process, the final scan result is the data of the entire table. Full table scan is a full process, which involves a large amount of data that is completely irrelevant to the result. Therefore, if the query triggers a full table scan, it will consume database resources, affect the database response speed, and seriously cause the application program using the database to freeze. With the vigorous development of the big data field, more and more companies have a large number of database-based data analysis needs every day. If a large number of SQL triggers a full table scan query, it will inevitably affect the efficiency of the entire analysis.
[0003] In the prior art, it is usually done manually to check whether any fields have been filtered in the SQL statement to determine whether it is a full table scan SQL. This method requires a lot of manpower and time. As SQL becomes more and more complex, it will affect the execution time of normal SQL. It can be seen that the technical solution in the prior art cannot efficiently determine the full table scan SQL.
[0004] To address the above problems, no effective solution has been proposed yet. Summary of the invention
[0005] The embodiments of this specification provide a method, device and apparatus for determining a structured query statement for a full table scan, so as to solve the problem that the full table scan SQL cannot be efficiently determined in the prior art.
[0006] An embodiment of the present specification provides a method for determining a structured query statement for a full table scan, comprising: converting a target structured query statement into a syntax tree; traversing the nodes in the syntax tree to filter out the table name involved in the target structured query statement; determining, based on the syntax tree, the field filtered by the target structured query statement; obtaining a field information set corresponding to the table name involved in the target structured query statement; determining whether the field filtered by the target structured query statement matches the field information set; and if there is no match, determining that the target structured query statement is a structured query statement for a full table scan.
[0007] The embodiments of the present specification also provide a device for determining a structured query statement for a full table scan, comprising: a conversion module, used to convert a target structured query statement into a syntax tree; a screening module, used to traverse the nodes in the syntax tree to screen out the table name involved in the target structured query statement; a first determination module, used to determine the field screened by the target structured query statement according to the syntax tree; an acquisition module, used to acquire the field information set corresponding to the table name involved in the target structured query statement; a second determination module, used to determine whether the field screened by the target structured query statement matches the field information set; and a third determination module, used to determine that the target structured query statement is a structured query statement for a full table scan if there is no match.
[0008] The embodiments of the present specification also provide a device for determining a structured query statement for a full table scan, including a processor and a memory for storing processor executable instructions, wherein the processor implements the steps of any method embodiment of the embodiments of the present specification when executing the instructions.
[0009] The embodiment of this specification provides a method for determining a structured query statement for a full table scan. The target structured query statement can be converted into a syntax tree, and the table name involved in the target structured query statement can be screened out by traversing the nodes in the syntax tree. The field screened by the target structured query statement can be determined based on the above syntax tree. The field information set corresponding to the table name involved in the target structured query statement can be obtained, and it is determined whether the field screened by the target structured query statement matches the field information set. In the case of mismatch, it means that the field screened by the target structured query statement does not contain information in the field information set, and the target structured query statement does not perform a screening query on the field in the data table corresponding to the table name, and it can be determined that the target structured query statement is a structured query statement for a full table scan. Therefore, it can be efficiently and accurately determined whether the target structured query statement is a structured query statement for a full table scan, and then it can be ensured that database resources can be reasonably utilized. BRIEF DESCRIPTION OF THE DRAWINGS
[0010] The drawings described herein are used to provide a further understanding of the embodiments of this specification, constitute a part of the embodiments of this specification, and do not constitute a limitation of the embodiments of this specification. In the drawings:
[0011] Figure 1 It is a schematic diagram of the steps of a method for determining a structured query statement for full table scanning provided in an embodiment of this specification;
[0012] Figure 2 It is a schematic diagram of the steps of determining whether a structured query statement is a full table scan according to an embodiment of this specification;
[0013] Figure 3 It is a structural schematic diagram of a device for determining a structured query statement for full table scanning provided in an embodiment of this specification;
[0014] Figure 4 It is a structural schematic diagram of a device for determining a structured query statement for a full table scan provided in an embodiment of this specification. DETAILED DESCRIPTION
[0015] The principles and spirit of the embodiments of this specification will be described below with reference to several exemplary embodiments. It should be understood that these embodiments are provided only to enable those skilled in the art to better understand and implement the embodiments of this specification, and are not intended to limit the scope of the embodiments of this specification in any way. On the contrary, these embodiments are provided to make the disclosure of the embodiments of this specification more thorough and complete, and to fully convey the scope of the disclosure to those skilled in the art.
[0016] Those skilled in the art know that the implementation of the embodiments of this specification can be implemented as a system, device, method or computer program product. Therefore, the embodiments disclosed in this specification can be specifically implemented in the following forms, namely: complete hardware, complete software (including firmware, resident software, microcode, etc.), or a combination of hardware and software.
[0017] Although the processes described below include multiple operations that occur in a particular order, it should be clear that these processes may include more or fewer operations, which may be performed sequentially or in parallel (eg, using parallel processors or a multi-threaded environment).
[0018] See also Figure 1 This embodiment can provide a method for determining a structured query statement for a full table scan. The method for determining a structured query statement for a full table scan can be used to efficiently and accurately determine whether a target structured query statement is a structured query statement for a full table scan, thereby ensuring that database resources can be reasonably utilized. The above-mentioned method for determining a structured query statement for a full table scan can include the following steps.
[0019] S101: Convert the target structured query statement into a syntax tree.
[0020] In this embodiment, a target structured query statement submitted by a user may be received, and in order to determine the database and table name involved in the target structured query statement, the structured query statement may be converted into an abstract syntax tree. The target structured query statement may be a structured query statement to be detected as a full table scan.
[0021] In this embodiment, a syntax tree can be generated using a syntax parser, which supports syntax extension to support the parsing needs of different syntaxes of multiple data sources such as Mysql, Oracle, and Hive. For example, open source syntax parsers such as antlr and javacc can support syntax of multiple data sources by extending the syntax rule file; parsers such as Calcite and Druid can have built-in programmatic syntax parsers. The above syntax tree can be composed of a series of nodes connected in series.
[0022] S102: traverse the nodes in the syntax tree to filter out table names involved in the target structured query statement.
[0023] In this implementation, the nodes in the syntax tree may be traversed to filter out all table names involved in the target structured query statement, and the table names may be table names of data tables in a database.
[0024] In this implementation, since tables with the same name may exist in different databases, in order to distinguish them, the table names are usually recorded in a preset format, for example: database.table name. The database and table name can be separated to obtain all table names.
[0025] In this implementation, the table name involved in the target structured query statement may be one or more.
[0026] S103: Determine the fields to be screened by the target structured query statement according to the syntax tree.
[0027] In this embodiment, the fields to be screened by the target structured query statement can be determined based on the above syntax tree. Of course, in some embodiments, the fields to be screened can also be determined directly based on the target structured query statement, which can be determined specifically based on actual conditions, and this specification embodiment does not limit this.
[0028] In this embodiment, the filtered field can be determined according to the semantics of the target structured query statement, and can be a field filtered in the table, for example, the field filtered in the structured query statement where'key'=***and'pt_dt'=** is pt_dt.
[0029] In this implementation, the field screened by the target structured query statement may also be empty, which may be specifically determined according to actual conditions, and is not limited in this embodiment of the present specification.
[0030] S104: Obtain a field information set corresponding to the table name involved in the target structured query statement.
[0031] In this embodiment, the field information set corresponding to the table name involved in the target structured query statement can be obtained. Among them, the above field information set can include at least one field name. In some embodiments, the above field information set can also include the table name corresponding to each field name, or a corresponding field information set can be generated for different table names respectively. The specific details can be determined according to actual conditions, and this specification embodiment does not limit this.
[0032] In this embodiment, after the table names involved in the target structured query statement are screened out, the operation interface (API) for querying the HIVE table can be called to query the field information in the data table corresponding to each table name involved from the metadata database. Of course, the method of obtaining the field information set is not limited to the above examples. Under the inspiration of the technical essence of the embodiments of this specification, technical personnel in the relevant field may also make other changes, but as long as the functions and effects achieved are the same or similar to the embodiments of this specification, they should be covered within the protection scope of the embodiments of this specification. Among them, HIVE is a data warehouse tool used for data extraction, transformation, and loading.
[0033] In this embodiment, after the table names involved in the target structured query statement are screened out, the query HIVE table operation interface can be called first. If the connection fails, the query is exited and the check failure is reported. If the connection is successful, the next operation can be performed.
[0034] In this embodiment, HIVE is a data warehouse tool based on Hadoop, which is used for data extraction, transformation, and loading. This is a mechanism that can store, query, and analyze large-scale data stored in Hadoop. The HIVE data warehouse tool can map structured data files into a database table and provide SQL query functions. Among them, Hadoop is a distributed system infrastructure.
[0035] S105: Determine whether the fields screened by the target structured query statement match the field information set.
[0036] In this embodiment, it can be further determined whether the field screened by the target structured query statement matches the field information set, that is, it can be determined whether the field screened by the target structured query statement contains information in the field information set.
[0037] S106: In the case of no match, determining that the target structured query statement is a structured query statement for full table scan.
[0038] In this embodiment, when it is determined that the fields filtered by the target structured query statement do not match the field information set, it means that the information in the field information set does not exist in the fields filtered by the target structured query statement, and it can be determined that the target structured query statement is a structured query statement for a full table scan.
[0039] In this embodiment, it can be determined whether the fields filtered by the target structured query statement match the field information set corresponding to each table name. If the fields filtered by the target structured query statement match the field information set corresponding to the target table name, if they do not match, it means that the target structured query statement does not filter the fields in the data table corresponding to the target table name, which means that the target structured query statement is a full table scan for the data table corresponding to the target table name, so it can be determined that the target structured query statement is a structured query statement for a full table scan, and the target structured query statement needs to be optimized.
[0040] In this embodiment, after the result is determined, the determined result can be fed back to the target user who submitted the target structured query statement. In some embodiments, if it is determined that the target structured query statement is not a full table scan, the target structured query statement can be executed, and the determination result is not fed back to the user, but the execution result is fed back to the user after the execution is completed to improve the user experience. The specific method can be determined according to the actual situation, and the embodiments of this specification are not limited to this.
[0041] From the above description, it can be seen that the embodiment of this specification achieves the following technical effects: the target structured query statement can be converted into a syntax tree, and the table name involved in the target structured query statement can be screened out by traversing the nodes in the syntax tree. The field screened by the target structured query statement can be determined based on the above syntax tree. The field information set corresponding to the table name involved in the target structured query statement can be obtained, and it is determined whether the field screened by the target structured query statement matches the field information set. In the case of mismatch, it means that the field screened by the target structured query statement does not contain information in the field information set, and the target structured query statement does not perform a screening query on the field in the data table corresponding to the table name, and it can be determined that the target structured query statement is a structured query statement for a full table scan. In this way, it is possible to efficiently and accurately determine whether the target structured query statement is a structured query statement for a full table scan, thereby ensuring that database resources can be reasonably utilized.
[0042] In one embodiment, after converting the target structured query statement into a syntax tree, the method may further include: determining whether the target structured query statement is a structured query statement for building a table according to the syntax tree; if it is determined to be a structured query statement for building a table, the target structured query statement may be released.
[0043] In this embodiment, since the structured query statement for creating a table does not involve a full table scan, it is necessary to filter out this type of structured query statement before checking. Therefore, it is possible to first determine whether the type of the target structured query statement is CREATE VIEW (create a new view). If it is determined to be so, it means that the target structured query statement is a structured query statement for creating a table.
[0044] In this implementation, when it is determined that the target structured query statement is a structured query statement for building a table, the target structured query statement may be released and directly executed.
[0045] In one embodiment, after determining whether the target structured query statement is a structured query statement for building a table, the method further includes: if it is determined that the target structured query statement is not a structured query statement for building a table, traversing the nodes in the syntax tree to filter out the table name involved in the target structured query statement.
[0046] In this embodiment, if the target structured query statement is not a structured query statement for building a table, it is necessary to further determine whether the target structured query statement is a structured query statement for full table scanning. Therefore, the nodes in the syntax tree can be traversed to filter out the table names involved in the target structured query statement.
[0047] In one embodiment, after traversing the nodes in the syntax tree to filter out the table name involved in the target structured query statement, it also includes: determining whether the target table name involved in the target structured query statement conforms to the target format. If it is determined that it does not conform to the target format, the target table name can be discarded.
[0048] In this embodiment, since there may be tables with the same name in different databases, in order to distinguish them, the table names are usually recorded in the target format, for example: database.table name. If the target table name involved in the target structured query statement does not conform to the target format, there is a high probability of incorrect judgment. Therefore, the target table name can be discarded, and the target table name can be directly skipped and the next table name can be judged.
[0049] In this embodiment, the target table name can be any one of the table names involved in the target structured query statement. If the target structured query statement involves only one target table name, if the target table name does not conform to the target format, an exception can be fed back to the user to prompt the user whether the target table name needs to be modified, or the target structured query statement can be directly released. The specific details can be determined based on actual conditions, and the embodiments of this specification are not limited to this.
[0050] In one embodiment, obtaining a field information set corresponding to a table name involved in the target structured query statement may include: determining whether a data table corresponding to a target table name involved in the target structured query statement is a partitioned table; if it is determined to be a partitioned table, the partition field of the data table corresponding to the target table name may be determined; further, a field information set may be generated based on the partition field of the data table corresponding to the target table name.
[0051] In this embodiment, if the structured query statement is checked to see whether any fields have been filtered in the structured query statement to determine whether it is a full table scan, there will be some limitations. Because, in some business scenarios, some tables that need to be queried may be tables with relatively small data volumes. Even if this type of full table scan query has a small amount of data, the impact on the system is small. In this case, a full table scan operation can be allowed. The general creation rules specify that if the table in the data lake is a large table, a partition field must be created so that all large tables are partitioned tables, but not all partitioned tables are large tables. Based on this, you can first determine whether the data table corresponding to the target table name involved in the target structured query statement is a partitioned table.
[0052] In this embodiment, when it is determined that the data table corresponding to the target table name involved in the target structured query statement is a partition table, it means that the data table corresponding to the target table name involved in the target structured query statement is a large table, and the target structured query statement is not allowed to be a structured query statement for full table scan. Since the definition of full table scan is that the screening field in the structured query statement does not contain the partition field of this table, the partition field of the data table corresponding to the target table name can be determined.
[0053] In this embodiment, for example: a table has fields pt_dt, key, and value. After parsing, it can be determined that pt_dt is a partition field and is recorded in the field information set. Since the target structured query statement where'key'=***and'value'=*** does not filter the partition field pt_dt, the target structured query statement will scan the entire table to find the value it wants when it is executed, thus causing a full table scan. If the target structured query statement is where'key'=***and'pt_dt'=***, a full table scan will not be triggered because the partition field pt_dt is filtered. pt_dt can have monthly selections from 1 to 12, similar to the catalog of a book. Finding the value of key=4 in January will be much faster than finding the value of ket=4 in January to December. Therefore, a full table scan can be avoided by filtering the partition field.
[0054] In one embodiment, after determining whether the data table corresponding to the target table name involved in the target structured query statement is a partition table, it may also include: if it is determined that it is not a partition table, determining whether the data table corresponding to the next table name of the target table name is a partition table.
[0055] In this embodiment, when it is determined that the data table corresponding to the target table name involved in the target structured query statement is not a partitioned table, it means that the data table corresponding to the target table name is not a large table. There is no need to continue to determine whether to perform a full table scan on the data table corresponding to the target table name, and the target table name can be skipped.
[0056] In this embodiment, if the target structured query statement involves only one table name, the target structured query statement can be directly released if it is determined that the data table corresponding to the target table name is not a partition table. If the target structured query statement involves more than one table name, it can continue to determine whether the data table corresponding to the next table name of the target table name is a partition table.
[0057] In one embodiment, determining whether the data table corresponding to the target table name involved in the target structured query statement is a partitioned table may include: obtaining the field name in the data table corresponding to the target table name involved in the target structured query statement, and when it is determined that the field name in the data table corresponding to the target table name is repeated, it can be determined that the data table corresponding to the target table name is a partitioned table.
[0058] In this implementation, an operation interface (API) for querying HIVE tables may be called to query all field names in a data table corresponding to a target table name from a metadata database.
[0059] In this implementation, it is possible to determine whether there are duplicate fields in the field names in the data table corresponding to the target table name, because the field results returned after the HIVE table query are unfiltered fields. If it is a partition table, the same fields will appear (such as the common pt_dt, or data_dt, etc.). If there are duplicate fields, it can be determined that the data table corresponding to the target table name is a partition table; if there are no duplicate fields, it means that the data table corresponding to the target table name is not a partition table, and the next table name can be determined.
[0060] In one embodiment, after obtaining the field name in the data table corresponding to the target table name involved in the target structured query statement, it can also include: when it is determined that there is a first characteristic field in the field name in the data table corresponding to the target table name, determining that the data table corresponding to the target table name is a partition table.
[0061] In this implementation, by judging whether there are repeated fields in the field names in the data table corresponding to the target table name, it is impossible to judge whether the table of view type is a partition table, because the partition fields of the view are all pt_dt, and they will not be repeated after parsing. Therefore, it can be determined that there is a first characteristic field in the field names in the data table corresponding to the target table name. If it exists, it can be determined that the data table corresponding to the target table name is a partition table; if it does not exist, it means that the data table corresponding to the target table name is not a partition table, and the next table name can be judged.
[0062] In this implementation manner, the first characteristic field pt_dt is used, and thus it can be determined whether the view type table is a partition table through pt_dt.
[0063] In one embodiment, after obtaining the field names in the data table corresponding to the target table name involved in the target structured query statement, the method further includes: determining whether the data table corresponding to the target table name is a dedicated view library when it is determined that the field names in the data table corresponding to the target table name exist a second characteristic field. When it is determined to be a dedicated view library, it can be determined that the data table corresponding to the target table name is a partition table.
[0064] In this embodiment, the second characteristic field may be end_dt (timestamp). When it is determined that the field name in the data table corresponding to the target table name contains the end_dt field, it can be determined whether the data table corresponding to the target table name is a BDPVIEW dedicated view library. If it is determined to be a dedicated view library, it can be determined that the data table corresponding to the target table name is a partition table; if it is not a dedicated view library, it means that the data table corresponding to the target table name is not a partition table, and the next table name can be determined.
[0065] In one embodiment, after determining whether the data table corresponding to the target table name involved in the target structured query statement is a partition table, the method may further include: if it is determined to be a partition table, storing the target table name in a target cache. Correspondingly, determining whether the data table corresponding to the target table name involved in the target structured query statement is a partition table may include: determining whether the target table name exists in the target cache, and if it is determined to exist, determining that the data table corresponding to the target table name involved in the target structured query statement is a partition table.
[0066] In this implementation, when it is determined that a data table corresponding to a certain table name is a partition table, the table name determined to be the partition table can be written into the target cache, and the target cache can store relevant judgment information determined to be the partition table within a preset time period. When subsequently determining whether the data table corresponding to the table name is a partition table, it can be determined first whether the target table name exists in the target cache. If so, it can be directly determined that the data table corresponding to the target table name is a partition table, thereby improving the efficiency of the judgment.
[0067] In this implementation, if the target table name does not exist in the target cache, it can be determined in the manner described in the above embodiment whether the data table corresponding to the target table name involved in the target structured query statement is a partition table.
[0068] In this embodiment, the above-mentioned preset time period can be nearly three months, or nearly half a year, etc., which can be determined according to actual conditions, and the embodiments of this specification do not limit this.
[0069] The following describes the steps of determining whether a structured query statement is a full table scan in conjunction with a specific embodiment. However, it is worth noting that this specific embodiment is only for better illustrating the embodiments of this specification and does not constitute an improper limitation on the embodiments of this specification. Figure 2 As shown, it may include:
[0070] Step S202: After parsing the table name involved in the target structured query statement, call the query HIVE table operation interface API, where HIVE is a data warehouse tool used to extract, transform, and load data.
[0071] If the connection fails, jump to step S203, exit the query, and check failure; if the connection succeeds, jump to S204.
[0072] Step S204: input the target table name to be queried, and jump to S205.
[0073] Step S205: query all field names corresponding to the target table name in the metadata database through the input target table name, perform grouping judgment according to the read field names, and jump to steps S206, S207, and S208 for judgment respectively.
[0074] Step S206: Determine whether there are duplicate fields in the field names corresponding to the target table name.
[0075] Because the field results returned after the HIVE table query are unfiltered fields, if it is a partitioned table, the same fields will appear. If there are duplicate fields, it is determined to be a partitioned table and jumps to step S209; if there are no duplicate fields, it is determined to be not a partitioned table and jumps to step S210.
[0076] Step S207: Determine whether there is a pt_dt field in the field name corresponding to the target table name.
[0077] If it exists, it is determined to be a partition table and the process jumps to step S209; if the pt_dt field does not exist, it is determined to be not a partition table and the process jumps to step S210.
[0078] Step S208: Determine whether there is an end_dt field in the field name corresponding to the target table name, and determine whether it is a BDPVIEW dedicated view library.
[0079] If it exists and is, it is determined to be a partition table and jumps to step S209; if it does not exist or is not, it is determined to be not a partition table and jumps to step S210: continue to determine the next table name.
[0080] Among them, as long as one of steps S206, S207, and S208 is determined to be a partition table, it can jump to step S209.
[0081] Step S209: After determining that it is a partition table, all partition fields of this table are put into a set, a partition field set is generated, and the process jumps to S211.
[0082] Step S211: determine whether the fields screened in the target structured query statement match the fields in the collection, if they match, jump to step S212: continue to determine the next table name. If they do not match, jump to S213.
[0083] Step S213: If the fields in the collection do not match the fields screened in the target structured query statement, the full table scan rule is hit, and it is determined that the target structured query statement meets the full table scan requirement. Further, the target structured query statement can be written into the cache.
[0084] Step S214: The loop ends and the result is returned.
[0085] Based on the same inventive concept, an apparatus for determining a structured query statement for a full table scan is also provided in the embodiments of this specification, as described in the following embodiments. Since the principle of solving the problem by the apparatus for determining a structured query statement for a full table scan is similar to the method for determining a structured query statement for a full table scan, the implementation of the apparatus for determining a structured query statement for a full table scan can refer to the implementation of the method for determining a structured query statement for a full table scan, and the repeated parts will not be repeated. As used below, the term "unit" or "module" can be a combination of software and / or hardware that implements a predetermined function. Although the apparatus described in the following embodiments is preferably implemented in software, the implementation of hardware, or a combination of software and hardware, is also possible and conceivable. Figure 3is a structural block diagram of a device for determining a structured query statement for full table scanning in an embodiment of this specification, such as Figure 3 As shown, it may include: a conversion module 301, a screening module 302, a first determination module 303, an acquisition module 304, a second determination module 305, and a third determination module 306. The structure is described below.
[0086] The conversion module 301 may be used to convert the target structured query statement into a syntax tree;
[0087] A screening module 302 may be used to traverse the nodes in the syntax tree to screen out the table names involved in the target structured query statement;
[0088] A first determination module 303 may be used to determine the fields to be screened by the target structured query statement according to the syntax tree;
[0089] The acquisition module 304 may be used to acquire a field information set corresponding to the table name involved in the target structured query statement;
[0090] The second determination module 305 may be used to determine whether the field screened by the target structured query statement matches the field information set;
[0091] The third determination module 306 may be used to determine, in the case of a mismatch, that the target structured query statement is a structured query statement for a full table scan.
[0092] The embodiment of this specification also provides an electronic device, which can be specifically referred to in Figure 4 The schematic diagram of the structure of the electronic device composition of the method for determining the structured query statement of the full table scan provided by the embodiment of the present specification is shown, and the electronic device may specifically include an input device 41, a processor 42, and a memory 43. Among them, the input device 41 can be specifically used to input the target structured query statement. The processor 42 can be specifically used to convert the target structured query statement into a syntax tree; traverse the nodes in the syntax tree to filter out the table name involved in the target structured query statement; determine the field filtered by the target structured query statement according to the syntax tree; obtain the field information set corresponding to the table name involved in the target structured query statement; determine whether the field filtered by the target structured query statement matches the field information set; in the case of mismatch, determine that the target structured query statement is a structured query statement of the full table scan. The memory 43 can be specifically used to store data such as syntax trees.
[0093] In this embodiment, the input device may specifically be one of the main devices for information exchange between the user and the computer system. The input device may include a keyboard, a mouse, a camera, a scanner, a light pen, a handwriting input board, a voice input device, etc.; the input device is used to input the original data and the program for processing these numbers into the computer. The input device can also obtain and receive data transmitted from other modules, units, and devices. The processor can be implemented in any appropriate manner. For example, the processor can take the form of a computer-readable medium, a logic gate, a switch, an application-specific integrated circuit (Application Specific Integrated Circuit, ASIC), a programmable logic controller, and an embedded microcontroller, etc., such as a microprocessor or a processor and a computer-readable program code (such as software or firmware) that can be executed by the (micro) processor. The memory may specifically be a memory device used to store information in modern information technology. The memory may include multiple levels. In a digital system, anything that can store binary data can be a memory; in an integrated circuit, a circuit with a storage function without a physical form is also called a memory, such as a RAM, a FIFO, etc.; in a system, a storage device with a physical form is also called a memory, such as a memory stick, a TF card, etc.
[0094] In this embodiment, the functions and effects specifically realized by the electronic device can be explained in comparison with other embodiments and will not be described in detail here.
[0095] The embodiment of the present specification also provides a computer storage medium for a method for determining a structured query statement based on a full table scan, wherein the computer storage medium stores computer program instructions, which can achieve the following when executed: converting a target structured query statement into a syntax tree; traversing the nodes in the syntax tree to filter out the table name involved in the target structured query statement; determining the field filtered by the target structured query statement according to the syntax tree; obtaining a field information set corresponding to the table name involved in the target structured query statement; determining whether the field filtered by the target structured query statement matches the field information set; and if there is no match, determining that the target structured query statement is a structured query statement for a full table scan.
[0096] In this embodiment, the storage medium includes, but is not limited to, a random access memory (RAM), a read-only memory (ROM), a cache, a hard disk (HDD), or a memory card. The memory may be used to store computer program instructions. The network communication unit may be an interface for network connection communication set in accordance with the standard specified by the communication protocol.
[0097] In this embodiment, the functions and effects specifically implemented by the program instructions stored in the computer storage medium can be explained in comparison with other embodiments and will not be repeated here.
[0098] Obviously, those skilled in the art should understand that the modules or steps of the above-mentioned embodiments of this specification can be implemented by a general computing device, they can be concentrated on a single computing device, or distributed on a network composed of multiple computing devices, and optionally, they can be implemented by a program code executable by a computing device, so that they can be stored in a storage device and executed by the computing device, and in some cases, the steps shown or described can be executed in a different order from that here, or they can be made into individual integrated circuit modules, or multiple modules or steps therein can be made into a single integrated circuit module for implementation. In this way, the embodiments of this specification are not limited to any specific combination of hardware and software.
[0099] Although the embodiments of this specification provide method operation steps as described in the above embodiments or flowcharts, more or fewer operation steps may be included in the method based on routine or no creative labor. In the steps where there is no necessary causal relationship logically, the execution order of these steps is not limited to the execution order provided by the embodiments of this specification. When the device or terminal product of the method described in practice is executed, it can be executed in sequence or in parallel according to the method shown in the embodiments or drawings (for example, a parallel processor or a multi-threaded processing environment).
[0100] It should be understood that the above description is for illustration and not for limitation. By reading the above description, many embodiments and many applications beyond the examples provided will be apparent to those skilled in the art. Therefore, the scope of the embodiments of this specification should not be determined with reference to the above description, but should be determined with reference to the full scope of the aforementioned claims and the equivalents possessed by these claims.
[0101] The above is only a preferred embodiment of the embodiment of this specification, and is not intended to limit the embodiment of this specification. For those skilled in the art, the embodiment of this specification may have various changes and variations. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the embodiment of this specification shall be included in the protection scope of the embodiment of this specification.
Claims
1. A method for determining a structured query statement for full table scanning, characterized in that: include: Convert the target structured query statement into a syntax tree; Traversing the nodes in the syntax tree to filter out the table names involved in the target structured query statement; Determining the fields to be screened by the target structured query statement according to the syntax tree; Calling an operation interface for querying a HIVE table to obtain a field information set corresponding to the table name involved in the target structured query statement; Determine whether the field screened by the target structured query statement matches the field information set; In the case of no match, it is determined that the target structured query statement is a structured query statement for full table scan.
2. The method according to claim 1, characterized in that: After converting the target structured query statement into a syntax tree, it also includes: Determining whether the target structured query statement is a structured query statement for building a table according to the syntax tree; When it is determined that the target structured query statement is a table building statement, the target structured query statement is released.
3. The method according to claim 2, characterized in that After determining whether the target structured query statement is a structured query statement for building a table, the method further includes: When it is determined that the target structured query statement is not a table creation statement, the nodes in the syntax tree are traversed to filter out the table name involved in the target structured query statement.
4. The method according to claim 1, characterized in that: After traversing the nodes in the syntax tree to filter out the table name involved in the target structured query statement, the method further includes: Determine whether the target table name involved in the target structured query statement conforms to the target format; If it is determined that the target table name does not conform to the target format, the target table name is discarded.
5. The method according to claim 1, characterized in that Obtaining a field information set corresponding to the table name involved in the target structured query statement, including: Determine whether the data table corresponding to the target table name involved in the target structured query statement is a partition table; In the case of determining that the target table is a partition table, determining the partition field of the data table corresponding to the target table name; A field information set is generated according to the partition fields of the data table corresponding to the target table name.
6. The method according to claim 5, characterized in that After determining whether the data table corresponding to the target table name involved in the target structured query statement is a partition table, the method further includes: If it is determined that the target table is not a partition table, it is determined whether the data table corresponding to the next table name of the target table name is a partition table.
7. The method according to claim 5, characterized in that Determining whether the data table corresponding to the target table name involved in the target structured query statement is a partition table includes: Obtain the field name in the data table corresponding to the target table name involved in the target structured query statement; When it is determined that there are duplicate field names in the data table corresponding to the target table name, it is determined that the data table corresponding to the target table name is a partition table.
8. The method according to claim 7, characterized in that After obtaining the field name in the data table corresponding to the target table name involved in the target structured query statement, the method further includes: When it is determined that the first characteristic field exists in the field names in the data table corresponding to the target table name, it is determined that the data table corresponding to the target table name is a partition table.
9. The method according to claim 8, characterized in that After obtaining the field name in the data table corresponding to the target table name involved in the target structured query statement, the method further includes: In the case where it is determined that the field name in the data table corresponding to the target table name has a second characteristic field, determining whether the data table corresponding to the target table name is a dedicated view library; In the case of determining that it is a dedicated view library, it is determined that the data table corresponding to the target table name is a partition table.
10. The method according to claim 5, characterized in that After determining whether the data table corresponding to the target table name involved in the target structured query statement is a partition table, the method further includes: If it is determined to be a partition table, storing the target table name in a target cache; Correspondingly, determining whether the data table corresponding to the target table name involved in the target structured query statement is a partition table includes: Determine whether the target table name exists in the target cache; If it is determined that the target table name involved in the target structured query statement exists, it is determined that the data table corresponding to the target table name involved in the target structured query statement is a partition table.
11. A device for determining a structured query statement for full table scanning, characterized in that: include: A conversion module, used for converting a target structured query statement into a syntax tree; A screening module, used for traversing the nodes in the syntax tree to screen out the table names involved in the target structured query statement; A first determination module, configured to determine the fields to be screened by the target structured query statement according to the syntax tree; An acquisition module, used for calling an operation interface for querying a HIVE table to acquire a field information set corresponding to a table name involved in the target structured query statement; A second determination module is used to determine whether the field screened by the target structured query statement matches the field information set; The third determination module is used to determine that the target structured query statement is a structured query statement for full table scan when there is no match.
12. A device for determining a structured query statement for a full table scan, characterized in that: The invention comprises a processor and a memory for storing instructions executable by the processor, wherein the steps of the method according to any one of claims 1 to 10 are implemented when the processor executes the instructions.