Big data SQL join intelligent optimization method and device

Abstract

The application provides a big data SQL join intelligent optimization method and device, and belongs to the technical field of SQL optimization application. The method comprises the following steps: S1: collecting and recording user SQL information; S2: analyzing the recorded SQL information to obtain key information of the SQL, wherein the key information comprises a table name, a table partition and a join field; S3: reading fsimage information according to the table name and the table partition through a presto engine to obtain the actual size of the current used table partition in the fsimage information; S4: judging whether the SQL join order is arranged according to a preset order, if not, adjusting the SQL join order to the preset order according to the size of the table partition, and if yes, continuing to execute downwards; and S5: judging whether data skew is caused according to the join field, and if yes, confirming the SQL after rewriting. The application improves the running speed of big data SQL, saves the resources of a big data cluster, and solves the problem of insufficient experience of engineers and the problem of not knowing how to optimize SQL.

Description

Technical Field

[0001] This invention relates to the field of SQL optimization application technology, and more specifically, to a method and apparatus for intelligent optimization of big data SQL joins. Background Technology

[0002] In today's big data era, SQL accounts for over 70% of big data queries. However, simply developing big data SQL is insufficient; numerous performance issues exist. Optimizing big data SQL requires developers with extensive experience and strong technical skills. Engineers spend considerable time optimizing SQL, and the decision to optimize a particular query often relies on their own experience; there is no truly intelligent method for optimizing big data SQL. Summary of the Invention

[0003] To address the aforementioned issues, this application provides a method and apparatus for intelligent optimization of big data SQL joins.

[0004] Firstly, this application provides a real-time example of a smart optimization method for big data SQL joins, including the following steps:

[0005] S1: Collect and record user SQL information;

[0006] S2: Parse the recorded SQL information to obtain key information about the SQL, including table name, table partition, and join field;

[0007] S3: Based on the table name and table partitions, read the fsimage information through the presto engine to obtain the actual size of the partitions of the currently used table in the fsimage information;

[0008] S4: Determine if the SQL join order is arranged according to the preset order. If not, adjust the SQL join order to the preset order according to the size of the table partition. If yes, continue to execute.

[0009] S5: Determine whether the join field will cause data skew. If so, rewrite the SQL and confirm.

[0010] Preferably, in step S5, a judgment is made based on the join field to determine whether it will lead to data skew. If so, the SQL is rewritten accordingly, specifically including:

[0011] S51: Based on the obtained join field, obtain the field type of the current join field through Hive, check whether the join field types on both sides of the current join field are consistent. If they are inconsistent, determine whether the inconsistent join field types will cause data skew. If so, modify the SQL type. If the join field types on both sides are consistent or it is determined that the inconsistent join field types will not cause data skew, continue to execute.

[0012] Preferably, the type modification of SQL in step S51 specifically includes:

[0013] Convert the types of the join fields on both sides to the same type, and then perform the join operation on the join fields on both sides.

[0014] Preferably, step S5 involves determining whether the join field will cause data skew, and if so, rewriting the SQL accordingly. Specifically, this also includes:

[0015] S52: Based on the obtained join field, query the Hive table using the Presto engine to see if there are any null values. If there are null values, determine whether the null values ​​in the join field will cause data skew. If so, modify the SQL to remove null values. If there are no null values ​​in the join table or it is determined that the null values ​​in the join field will not cause data skew, continue execution.

[0016] Preferably, step S5 involves determining whether the join field will cause data skew, and if so, rewriting the SQL accordingly. Specifically, this also includes:

[0017] S53: Obtain the percentage of the join key based on the obtained join field. If the actual percentage of the join key exceeds the preset percentage, determine whether the percentage of the join field will cause data skew. If it will, modify the percentage of the SQL. If the actual percentage of the join key does not exceed the preset percentage or it is determined that the percentage of the join field will not cause data skew, continue to execute.

[0018] Preferably, step S52, which modifies the SQL for null values, specifically includes:

[0019] Determine if null values ​​are needed. If not, filter out null values ​​from the join table to directly modify the SQL. If null values ​​are needed, modify the SQL to filter out null values ​​from the join table to form a null value modification SQL, and add a null value receiving SQL to receive the filtered null values. Then, perform a UNION ALL operation on the null value receiving SQL and the null value modification SQL.

[0020] Preferably, the percentage modification of SQL in step S53 specifically includes:

[0021] Extract the content of the join key, rewrite the SQL to form a "multiple-key SQL" with more than the first preset number of keys and a "short-key SQL" with fewer than the second preset number of keys, and perform a UENO ALL operation on the "multiple-key SQL" and the "short-key SQL".

[0022] Secondly, embodiments of this application provide a big data SQL join intelligent optimization device, including...

[0023] The logging module is used to collect and record user SQL information;

[0024] The parsing module is used to parse the recorded SQL information and obtain the key information of the SQL, including table name, table partition, and join field;

[0025] The partition retrieval module is used to read fsimage information through the presto engine based on the table name and table partitions, and obtain the actual size of the partitions of the currently used table from the fsimage information;

[0026] The sorting module is used to determine whether the SQL join order is arranged according to the preset order. If not, the SQL join order is adjusted to the preset order according to the size of the table partition.

[0027] The modification module is used to determine whether the SQL join order will cause data skew when the SQL join order is arranged in a preset order. If it will, the SQL will be rewritten and sent for confirmation.

[0028] Thirdly, embodiments of this application provide an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the steps of the method provided as in the first aspect or any possible implementation of the first aspect.

[0029] Fourthly, embodiments of this application provide a computer-readable storage medium having a computer program stored thereon, wherein the computer program, when executed by a processor, implements the steps of the method provided as in the first aspect or any possible implementation thereof.

[0030] The beneficial effects of this invention are as follows: It adjusts the SQL join order when the SQL join order is not arranged according to the preset order; it rewrites the SQL accordingly when it is determined that the join fields will cause data skew; it uses a big data SQL intelligent optimization method to determine whether each SQL statement needs optimization; if optimization is needed, it intelligently rewrites the current SQL statement using big data technology; it can intelligently optimize big data SQL, solve various performance bottlenecks caused by joins in big data SQL, improve the running speed of big data SQL, save resources in big data clusters, and solve the problem of big data development engineers lacking experience and not knowing how to optimize SQL. Attached Figure Description

[0031] To more clearly illustrate the technical solutions in the embodiments of this application, the accompanying drawings used in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0032] Figure 1 A flowchart illustrating a big data SQL join intelligent optimization method provided in this application embodiment;

[0033] Figure 2 This is a schematic diagram of the structure of a big data SQL join intelligent optimization device provided in an embodiment of this application;

[0034] Figure 3 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application;

[0035] Figure 4 This is a schematic diagram of the intelligent SQL optimization process in a big data SQL join intelligent optimization method provided in an embodiment of this application. Detailed Implementation

[0036] The technical solutions in the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings.

[0037] In the following description, the terms "first" and "second" are used for descriptive purposes only and should not be construed as indicating or implying relative importance. The following description provides multiple embodiments of this application, which can be substituted or combined with each other. Therefore, this application can also be considered to include all possible combinations of the same and / or different embodiments described. Thus, if one embodiment includes features A, B, and C, and another embodiment includes features B and D, then this application should also be considered to include embodiments containing one or more other possible combinations of A, B, C, and D, even if such embodiments are not explicitly described in the following text.

[0038] The following description provides examples and does not limit the scope, applicability, or examples set forth in the claims. Changes may be made to the function and arrangement of the described elements without departing from the scope of this application. Various processes or components may be appropriately omitted, substituted, or added to the examples. For example, the described methods may be performed in a different order than described, and various steps may be added, omitted, or combined. Furthermore, features described with respect to some examples may be combined into other examples.

[0039] Please see Figure 1 , 4 . Figure 1 This is a flowchart illustrating a smart optimization method for big data SQL joins provided in an embodiment of this application. Figure 4 This document illustrates the intelligent SQL optimization process in a big data SQL join intelligent optimization method provided in this application embodiment. In this application embodiment, the method includes the following steps:

[0040] S1: Collect and record user SQL information;

[0041] S2: Parse the recorded SQL information to obtain key information about the SQL, including table name, table partition, and join field;

[0042] S3: Based on the table name and table partitions, read the fsimage information through the presto engine to obtain the actual size of the partitions of the currently used table in the fsimage information;

[0043] S4: Determine if the SQL join order is arranged according to the preset order. If not, adjust the SQL join order to the preset order according to the size of the table partition. If yes, continue to execute.

[0044] S5: Determine whether the join field will cause data skew. If so, rewrite the SQL and confirm.

[0045] In this embodiment, step S5 determines whether the join field will cause data skew. If so, the SQL is rewritten accordingly and sent to the user for confirmation. This application addresses the pain points of big data SQL optimization by using a smart big data SQL optimization method to determine whether each SQL statement needs optimization. If optimization is required, big data technology is used to intelligently optimize the current SQL statement, eliminating the need for experienced big data engineers to perform optimizations or to optimize big data SQL statements that were overlooked by big data development engineers. This application is used for big data SQL optimization applications under massive datasets and can be used to intelligently detect whether big data SQL statements need optimization. This invention performs intelligent optimization based on big data SQL, intelligently rewriting SQL statements to address various data skews caused by joins and rewriting SQL statements to address resource waste caused by tables not being ordered from smallest to largest in the join sequence. This invention can significantly optimize the execution efficiency of big data SQL statements and improve the utilization of cluster resources.

[0046] In this embodiment, the join order is adjusted as follows: The join tables are obtained by parsing the SQL, and then the Presto engine is used to read the fsimage information to obtain the table's space usage. The sizes of each join table are compared, and then the SQL join order is adjusted. Specifically, based on the parsed table names and partitions, the Presto engine reads the actual partition size (physical space usage) of the currently used table in the Hadoop fsimage. Then, the SQL join order is adjusted according to the table size, with smaller tables joined first and the largest table joined last. If only the SQL order is adjusted, the optimized SQL is sent to the user via email using previously recorded user information for final verification and confirmation.

[0047] Join data skew optimization: Obtain the join field information by parsing the SQL, read the table information through Hive, determine whether the types of the two join fields are consistent, read the table through the Presto engine to see if the proportion of certain contents exceeds a certain percentage, and then intelligently rewrite the SQL.

[0048] In this embodiment, user SQL information is collected, including username, email address, job name, and SQL information, which is then recorded. The recorded SQL is parsed using ANTLR to obtain key information such as table name, table partition, and the tables to which fields joined belong.

[0049] In this application, current patents cannot solve the data skew caused by joins in big data SQL, nor the SQL resource waste caused by the join order. The method proposed in this patent can solve the data skew caused by joins in big data SQL, thereby improving the execution efficiency of big data SQL. Current patents cannot solve the various problems existing in joins of massive data. This published patent is for MySQL databases. The method proposed in this patent solves the join problems existing in SQL in the field of big data by obtaining the attribute information of big data tables through ANTLR syntax parsing. Current patents are for the big data field. Although current patents propose adjusting the table join order, they do not specify the order adjustment of partitioned tables, nor do they provide specific solutions. The concept is relatively general, and its practicality in the field of big data is questionable. This patent aims to solve the SQL problems of massive data. This patent addresses the join order issue by using the Presto engine to query the Hadoop fsimage to obtain the table partition size, thereby determining the join order in the SQL. The adjusted SQL is then sent to the user for confirmation, ensuring the correctness of the SQL. This patent also resolves data skew caused by inconsistent join key types and excessive join key values. While current patents offer null value filtering, they don't consider the fact that null values ​​are sometimes necessary. This patent handles null values ​​in two ways: necessary and unnecessary. When null values ​​are unnecessary, filtering is performed directly in the original SQL. If null values ​​are necessary, a new SQL statement is added to the original SQL to retrieve null values, preventing data skew, and then a UNION ALL statement is performed with the original SQL to obtain the final result set. Furthermore, this patent resolves data skew caused by inconsistent join key types and excessive join key values ​​by querying various required data using data stored on the Hadoop cluster through the Presto engine, thus solving various data skew problems caused by large-scale SQL queries, including skew caused by inconsistent join key field types and excessive join key fields.

[0050] Compared with existing technologies, the main advantages of this application are: resolving data skew caused by inconsistent join types in big data SQL; resolving data skew caused by an excessive number of keys in big data SQL joins; improving the execution efficiency of big data SQL; intelligently rewriting data skew caused by joins; and intelligently adjusting the join order of big data SQL. This application solves various skew problems caused by SQL joins under massive data; it can determine whether the join keys account for too much of the data; it can determine whether the join field types are consistent; it analyzes whether SQL needs optimization based on big data technology; and it can intelligently adjust the join order of big data SQL.

[0051] In one possible implementation, step S5 involves determining whether the join field will cause data skew, and if so, rewriting the SQL accordingly. Specifically, this includes:

[0052] S51: Based on the obtained join field, obtain the field type of the current join field through Hive, check whether the join field types on both sides of the current join field are consistent. If they are inconsistent, determine whether the inconsistent join field types will cause data skew. If so, modify the SQL type. If the join field types on both sides are consistent or it is determined that the inconsistent join field types will not cause data skew, continue to execute.

[0053] In this embodiment, Hive is a data warehouse tool based on Hadoop, used for data extraction, transformation, and loading. It's a mechanism for storing, querying, and analyzing large-scale data stored in Hadoop. Presto is a browser rendering engine developed by Opera Software, used by Opera versions 7.0-12.18. FSImage files are mainly used to store HDFS file system metadata information (information on file and directory structures, including the number of copies of file blocks), and the NameNode periodically saves the file system's namespace metadata information to the fsimage file. Hadoop is a distributed system infrastructure developed by the Apache Software Foundation. Users can develop distributed programs without understanding the underlying details of distributed systems.

[0054] In one possible implementation, the type modification of SQL in step S51 specifically includes:

[0055] Convert the types of the join fields on both sides to the same type, and then perform the join operation on the join fields on both sides.

[0056] In this embodiment, the join field type is determined as follows: based on the join field in the record table, the field type of the current join field is obtained through Hive. It is then checked whether the join field types on both sides are consistent. If they are inconsistent, the SQL is modified to convert the join field type to a consistent type before joining. This resolves data skew caused by inconsistent join field types.

[0057] In one possible implementation, step S5 includes determining whether the join field will cause data skew, and if so, rewriting the SQL accordingly. Specifically, this also includes:

[0058] S52: Based on the obtained join field, query the Hive table using the Presto engine to see if there are any null values. If there are null values, determine whether the null values ​​in the join field will cause data skew. If so, modify the SQL to remove null values. If there are no null values ​​in the join table or it is determined that the null values ​​in the join field will not cause data skew, continue execution.

[0059] In one possible implementation, step S5 includes determining whether the join field will cause data skew, and if so, rewriting the SQL accordingly. Specifically, this also includes:

[0060] S53: Obtain the percentage of the join key based on the obtained join field. If the actual percentage of the join key exceeds the preset percentage, determine whether the percentage of the join field will cause data skew. If it will, modify the percentage of the SQL. If the actual percentage of the join key does not exceed the preset percentage or it is determined that the percentage of the join field will not cause data skew, continue to execute.

[0061] In one possible implementation, modifying the SQL for null values ​​in step S52 specifically includes:

[0062] Determine if null values ​​are needed. If not, filter out null values ​​from the join table to directly modify the SQL. If null values ​​are needed, modify the SQL to filter out null values ​​from the join table to form a null value modification SQL, and add a null value receiving SQL to receive the filtered null values. Then, perform a UNION ALL operation on the null value receiving SQL and the null value modification SQL.

[0063] In this embodiment, the join field null value judgment is as follows: Based on the obtained join field, the Presto engine is used to quickly query the Hive tables to see if there are a large number of null values. If there are a large number of null values, optimization is performed in two cases: whether the null values ​​are needed or not: a) If null values ​​are not needed, the SQL is directly modified to filter out null values ​​from both join tables, and the modified SQL is sent to the user via email for confirmation; b) If null values ​​are needed, the SQL is modified to filter out null values ​​from both tables, and then a new SQL statement is added to perform a UNION ALL operation with the previously filtered null values. This solves the data skew problem in large-scale SQL queries caused by null values.

[0064] In one possible implementation, the percentage modification of SQL in step S53 specifically includes:

[0065] Extract the content of the join key, rewrite the SQL to form a "multiple-key SQL" with more than the first preset number of keys and a "short-key SQL" with fewer than the second preset number of keys, and perform a UENO ALL operation on the "multiple-key SQL" and the "short-key SQL".

[0066] In this embodiment, the issue of excessive JOIN field proportion is addressed by: identifying any keys with an excessively large proportion in the JOIN key; if so, extracting the contents of these excessively large keys, rewriting the SQL to filter out those with many keys and those with few keys, performing a JOIN ALL operation on the two SQL statements, and finally sending the modified SQL to the user via email for confirmation. This resolves the data skew problem caused by an excessive number of keys in large-scale SQL queries.

[0067] The following will be combined with the appendix Figure 2 This application provides a detailed description of a big data SQL join intelligent optimization device according to embodiments. It should be noted that the appendix... Figure 2 The above describes a big data SQL join intelligent optimization device for executing the functions described in this application. Figure 1 The methods shown in the embodiments are for illustrative purposes only, illustrating the parts relevant to the embodiments of this application. For specific technical details not disclosed, please refer to this application. Figure 1 The example shown.

[0068] Please see Figure 2 , Figure 2 This is a schematic diagram of the structure of a big data SQL join intelligent optimization device provided in an embodiment of this application. Figure 2 As shown, the device includes

[0069] The recording module 201 is used to collect and record user SQL information;

[0070] The parsing module 202 is used to parse the recorded SQL information and obtain the key information of the SQL, including table name, table partition, and join field;

[0071] The partition acquisition module 203 is used to read fsimage information through the presto engine based on the table name and table partitions, and obtain the actual size of the partitions of the currently used table in the fsimage information;

[0072] The sorting module 204 is used to determine whether the SQL join order is arranged in the preset order. If not, the SQL join order is adjusted to the preset order according to the size of the table partition.

[0073] Modify module 205 to determine whether data skew will occur when the SQL join order is arranged in the preset order based on the join field. If it will, rewrite the SQL and send it for confirmation.

[0074] In this embodiment, the modification module 205 is used to determine whether data skew will occur when the SQL join order is arranged in a preset order based on the join field. If so, the SQL is rewritten accordingly and sent to the user for confirmation.

[0075] In one possible implementation, the modification module 205 includes

[0076] The type modification unit is used to obtain the field type of the current join field through Hive based on the obtained join field, check whether the join field types on both sides of the current join field are consistent, if they are inconsistent, determine whether the inconsistent join field types will cause data skew, and if so, modify the type of the SQL.

[0077] The type modification unit is used to convert the types of the two join fields to a consistent type and then perform the join operation on the two join fields.

[0078] In one possible implementation, the modification module 205 further includes

[0079] The null value modification unit is used to query the Hive table for null values ​​based on the retrieved join field after the SQL modification unit has been modified. If null values ​​are found, it is determined whether the null values ​​in the join field cause data skew. If so, the SQL is modified to null values.

[0080] The null value modification unit is used to determine whether null values ​​are needed. If not, it filters out null values ​​from the join table and performs direct modification SQL. If null values ​​are needed, it modifies the SQL by filtering out null values ​​from the join table to form null value modification SQL, and adds a null value receiving SQL to receive the filtered null values. Then, it performs a UNION ALL operation on the null value receiving SQL and the null value modification SQL.

[0081] In one possible implementation, the modification module 205 further includes

[0082] The percentage modification unit is used to obtain the percentage of the join key based on the obtained join field after the SQL is modified in the null value modification unit. If the actual percentage of the join key exceeds the preset percentage, it is determined whether the percentage of the join field will cause data skew. If so, the percentage of the SQL is modified.

[0083] The percentage modification unit extracts the content of the join key, rewrites the SQL to form a "multiple-key SQL" with more than the first preset number of filtered keys and a "short-key SQL" with fewer than the second preset number of filtered keys, and performs a UENO ALL operation on the "multiple-key SQL" and "short-key SQL".

[0084] Those skilled in the art will clearly understand that the technical solutions of the embodiments of this application can be implemented by means of software and / or hardware. In this specification, "unit", "module" and "part" refer to software and / or hardware that can independently complete or cooperate with other components to complete a specific function, wherein the hardware may be, for example, a field-programmable gate array (FPGA), an integrated circuit (IC), etc.

[0085] Each processing unit and / or module in the embodiments of this application can be implemented by an analog circuit that implements the functions described in the embodiments of this application, or by software that executes the functions described in the embodiments of this application.

[0086] See Figure 3 It shows a schematic diagram of the structure of an electronic device according to an embodiment of this application, which can be used to implement... Figure 1 The method in the illustrated embodiment. (As shown) Figure 3 As shown, the electronic device 300 may include: at least one central processing unit 301, at least one network interface 304, user interface 303, memory 305, and at least one communication bus 302.

[0087] The communication bus 302 is used to enable communication between these components.

[0088] The user interface 303 may include a display screen and a camera. Optionally, the user interface 303 may also include a standard wired interface and a wireless interface.

[0089] The network interface 304 may optionally include a standard wired interface or a wireless interface (such as a Wi-Fi interface).

[0090] The central processing unit 301 may include one or more processing cores. The central processing unit 301 connects to various parts within the electronic device 300 using various interfaces and lines. It executes various functions of the terminal 300 and processes data by running or executing instructions, programs, code sets, or instruction sets stored in the memory 305, and by calling data stored in the memory 305. Optionally, the central processing unit 301 may be implemented using at least one hardware form of Digital Signal Processing (DSP), Field-Programmable Gate Array (FPGA), or Programmable Logic Array (PLA). The central processing unit 301 may integrate one or more of the following: a central processing unit (CPU), a graphics processing unit (GPU), and a modem. The CPU primarily handles the operating system, user interface, and applications; the GPU is responsible for rendering and drawing the content required for display; and the modem handles wireless communication. It is understood that the modem may also be implemented as a separate chip without being integrated into the central processing unit 301.

[0091] The memory 305 may include random access memory (RAM) or read-only memory. Optionally, the memory 305 may include a non-transitory computer-readable storage medium. The memory 305 may be used to store instructions, programs, code, code sets, or instruction sets. The memory 305 may include a program storage area and a data storage area, wherein the program storage area may store instructions for implementing an operating system, instructions for at least one function (such as touch function, sound playback function, image playback function, etc.), instructions for implementing the above-described method embodiments, etc.; the data storage area may store data involved in the above-described method embodiments, etc. Optionally, the memory 305 may also be at least one storage device located remotely from the aforementioned central processing unit 301. Figure 3 As shown, the memory 305, which serves as a computer storage medium, may include an operating system, a network communication module, a user interface module, and program instructions.

[0092] exist Figure 3In the illustrated electronic device 300, the user interface 303 is mainly used to provide an input interface for the user and to obtain the user's input data; while the central processing unit 301 can be used to call a big data SQL join intelligent optimization application stored in the memory 305, and specifically perform the following operations:

[0093] S1: Collect and record user SQL information;

[0094] S2: Parse the recorded SQL information to obtain key information about the SQL, including table name, table partition, and join field;

[0095] S3: Based on the table name and table partitions, read the fsimage information through the presto engine to obtain the actual size of the partitions of the currently used table in the fsimage information;

[0096] S4: Determine if the SQL join order is arranged according to the preset order. If not, adjust the SQL join order to the preset order according to the size of the table partition. If yes, continue to execute.

[0097] S5: Determine whether the join field will cause data skew. If so, rewrite the SQL and confirm.

[0098] This application also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the above-described method. The computer-readable storage medium may include, but is not limited to, any type of disk, including floppy disks, optical disks, DVDs, CD-ROMs, microdrives, as well as magneto-optical disks, ROMs, RAMs, EPROMs, EEPROMs, DRAMs, VRAMs, flash memory devices, magnetic cards or optical cards, nanosystems (including molecular memory ICs), or any type of medium or device suitable for storing instructions and / or data.

[0099] It should be noted that, for the sake of simplicity, the foregoing method embodiments are all described as a series of actions. However, those skilled in the art should understand that this application is not limited to the described order of actions, as some steps may be performed in other orders or simultaneously according to this application. Furthermore, those skilled in the art should also understand that the embodiments described in the specification are preferred embodiments, and the actions and modules involved are not necessarily essential to this application.

[0100] In the above embodiments, the descriptions of each embodiment have different focuses. For parts not described in detail in a certain embodiment, please refer to the relevant descriptions in other embodiments.

[0101] In the several embodiments provided in this application, it should be understood that the disclosed apparatus can be implemented in other ways. For example, the apparatus embodiments described above are merely illustrative; for instance, the division of units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be through some service interface; the indirect coupling or communication connection between devices or units may be electrical or other forms.

[0102] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0103] Furthermore, the functional units in the various embodiments of this application can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit.

[0104] If the integrated unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage device (CMD). Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or all or part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a memory and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of this application. The aforementioned memory includes various media capable of storing program code, such as USB flash drives, read-only memory (ROM), random access memory (RAM), portable hard drives, magnetic disks, or optical disks.

[0105] Those skilled in the art will understand that all or part of the steps in the various methods of the above embodiments can be implemented by a program instructing related hardware. The program can be stored in a computer-readable storage medium, which may include: a flash drive, a read-only memory (ROM), a random access memory (RAM), a magnetic disk, or an optical disk, etc.

[0106] The foregoing description is merely an exemplary embodiment of this disclosure and should not be construed as limiting the scope of this disclosure. Any equivalent changes and modifications made in accordance with the teachings of this disclosure shall still fall within the scope of this disclosure. Those skilled in the art will readily conceive of embodiments of this disclosure upon considering the specification and practicing the disclosure herein. This application is intended to cover any variations, uses, or adaptations of this disclosure that follow the general principles of this disclosure and include common knowledge or customary techniques in the art not described herein. The specification and embodiments are to be considered exemplary only, and the scope and spirit of this disclosure are defined by the claims.

Claims

1. A smart optimization method for big data SQL joins, characterized in that, Includes the following steps: S1: Collect and record user SQL information; S2: Parse the recorded SQL information to obtain key information about the SQL, including table name, table partition, and join field; S3: Based on the table name and table partitions, read the fsimage information through the presto engine and obtain the actual size of the partitions of the currently used table from the fsimage information; S4: Determine if the SQL join order is arranged according to the preset order. If not, adjust the SQL join order to the preset order according to the size of the table partition. If yes, continue to execute. S5: Determine whether the join field will cause data skew. If so, rewrite the SQL and confirm. In step S5, it is determined whether the join field will cause data skew. If so, the SQL is rewritten accordingly, specifically including: S51: Based on the obtained join field, obtain the field type of the current join field through Hive, check whether the join field types on both sides of the current join field are consistent. If they are inconsistent, determine whether the inconsistent join field types will cause data skew. If so, modify the SQL type. If the join field types on both sides are consistent or it is determined that the inconsistent join field types will not cause data skew, continue to execute.

2. The intelligent optimization method for big data SQL join as described in claim 1, characterized in that: Step S51, which modifies the type of SQL, specifically includes: Convert the types of the join fields on both sides to the same type, and then perform the join operation on the join fields on both sides.

3. A big data SQL join intelligent optimization method as described in claim 1 or 2, characterized in that, Step S5 involves determining whether the join field will cause data skew. If so, the SQL is rewritten accordingly. Specifically, this includes: S52: Based on the obtained join field, query the Hive table using the Presto engine to see if there are any null values. If there are null values, determine whether the null values ​​in the join field will cause data skew. If so, modify the SQL to remove null values. If there are no null values ​​in the join table or it is determined that the null values ​​in the join field will not cause data skew, continue execution.

4. The intelligent optimization method for big data SQL join as described in claim 3, characterized in that, Step S5 involves determining whether the join field will cause data skew. If so, the SQL is rewritten accordingly. Specifically, this includes: S53: Obtain the percentage of the join key based on the obtained join field. If the actual percentage of the join key exceeds the preset percentage, determine whether the percentage of the join field will cause data skew. If it will, modify the percentage of the SQL. If the actual percentage of the join key does not exceed the preset percentage or it is determined that the percentage of the join field will not cause data skew, continue to execute.

5. The intelligent optimization method for big data SQL join as described in claim 3, characterized in that, Step S52, modifying the SQL for null values, specifically includes: Determine if null values ​​are needed. If not, filter out null values ​​from the join table to directly modify the SQL. If null values ​​are needed, modify the SQL to filter out null values ​​from the join table to form a null value modification SQL, and add a null value receiving SQL to receive the filtered null values. Then, perform a UNION ALL operation on the null value receiving SQL and the null value modification SQL.

6. The intelligent optimization method for big data SQL join as described in claim 4, characterized in that, Step S53, which modifies the percentage of SQL, specifically includes: Extract the content of the join key, rewrite the SQL to form a "multiple-key SQL" with more than the first preset number of keys and a "short-key SQL" with fewer than the second preset number of keys, and perform a UENO ALL operation on the "multiple-key SQL" and the "short-key SQL".

7. A big data SQL join intelligent optimization device, characterized in that: include The logging module is used to collect and record user SQL information; The parsing module is used to parse the recorded SQL information and obtain the key information of the SQL, including table name, table partition, and join field; The partition retrieval module is used to read fsimage information through the presto engine based on the table name and table partitions, and obtain the actual size of the partitions of the currently used table from the fsimage information; The sorting module is used to determine whether the SQL join order is arranged according to the preset order. If not, the SQL join order is adjusted to the preset order according to the size of the table partition. The modification module is used to determine whether the SQL join order will cause data skew when the join fields are arranged in a preset order. If so, the SQL will be rewritten accordingly and sent to the user for confirmation. The modification module includes The type modification unit is used to obtain the field type of the current join field through Hive based on the obtained join field, check whether the join field types on both sides of the current join field are consistent, if they are inconsistent, determine whether the inconsistent join field types will cause data skew, and if so, modify the type of the SQL.

8. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the steps of the method as described in any one of claims 1-6.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the steps of the method as described in any one of claims 1-6.

Images