Database query method based on tree structure conversion and HIVE multi-table union

Abstract

The present invention provides a database query method based on tree structure conversion and HIVE multi-table joint, which relates to the field of data query technology, including generating an initial syntax tree by parsing query statements, building a tree structure for multi-table joint query, analyzing the data distribution of table nodes, calculating data volume weight coefficients and data tilt indicators, reordering table nodes and optimizing data sharding, generating an optimized query tree, and integrating data pre-filtering conditions and temporary index structures to generate a final execution plan. The present invention can effectively solve the data tilt problem in HIVE multi-table joint query and improve query performance and resource utilization.

Description

Technical Field

[0001] The present invention relates to data query technology, and in particular to a database query method based on tree structure conversion and HIVE multi-table union. Background Art

[0002] With the rapid development of big data technology, enterprises have an increasing demand for the analysis and processing of massive data. HIVE, as a data warehouse tool based on Hadoop, can map structured data files into database tables and provide SQL-like query functions. In practical applications, complex business analysis often requires joint query operations on multiple data tables, which places higher requirements on HIVE's query performance. At present, HIVE mainly uses the MapReduce computing model when processing multi-table joint queries, completing data processing by converting query statements into multiple Map and Reduce tasks.

[0003] The existing technology has the following main problems:

[0004] When processing large-scale multi-table joint queries, the lack of in-depth analysis and optimization of table data distribution characteristics can easily lead to data skew problems, causing some computing nodes to be overloaded, seriously affecting query efficiency.

[0005] Existing query optimization methods often use a fixed table join order and fail to dynamically adjust the query plan according to the actual data size, which makes the join operation of small and large tables inefficient and increases system resource consumption.

[0006] The traditional HIVE query execution process lacks targeted data pre-filtering mechanisms and temporary index support, which results in the need to process a large amount of redundant data when performing table association operations, reducing query performance and increasing network transmission overhead. Summary of the invention

[0007] The embodiment of the present invention provides a database query method based on tree structure conversion and HIVE multi-table union, which can solve the problems in the prior art.

[0008] According to a first aspect of the embodiments of the present invention,

[0009] Provides a database query method based on tree structure conversion and HIVE multi-table union, including:

[0010] Obtain a query statement of a HIVE database, and parse the query statement to generate an initial syntax tree; extract a multi-table joint query operation node in the initial syntax tree as a root node, and use table nodes associated with the multi-table joint query operation node as child nodes to construct a query tree structure; analyze the data distribution of each table node in the query tree structure, and obtain the number of data records, data block size, and data block distribution information of the table node; calculate the data volume weight coefficient and data tilt index of each table node based on the data volume weight coefficient and the data tilt index; generate a table node optimization strategy list according to the data volume weight coefficient and the data tilt index;

[0011] According to the data volume weight coefficient in the table node optimization strategy list, the table nodes in the query tree structure are reordered in the order of data volume from small to large to generate a reordered query tree; table nodes whose data tilt index exceeds a preset tilt threshold are extracted from the table node optimization strategy list as nodes to be optimized; data records in the nodes to be optimized are fragmented according to the data block distribution information, and each of the nodes to be optimized is split into multiple data-balanced sub-table nodes; in the reordered query tree, the nodes to be optimized are replaced with the data-balanced sub-table nodes, and independent parallel processing branches are constructed for each of the sub-table nodes to generate an optimized query tree;

[0012] Generate a HIVE query execution plan based on the optimized query tree; extract data features of each table node from the table node optimization strategy list, and configure data pre-filtering conditions for each table node according to the data features; analyze the association relationship between the table nodes in the optimized query tree, and create a temporary index structure for the table nodes based on the association relationship; integrate the data pre-filtering conditions and the temporary index structure into the HIVE query execution plan to generate a final optimized execution plan; submit the final optimized execution plan to the HIVE distributed computing cluster, monitor the query execution status of each table node, and collect and merge query results.

[0013] Obtaining a query statement of a HIVE database, parsing the query statement to generate an initial syntax tree; extracting a multi-table joint query operation node in the initial syntax tree as a root node, and using table nodes associated with the multi-table joint query operation node as child nodes, and constructing a query tree structure includes:

[0014] Performing morpheme segmentation on the query statement of the HIVE database, extracting a keyword set, an identifier set, and an operator set in the query statement; constructing a syntax analysis state machine based on the keyword set, the identifier set, and the operator set; performing state conversion on the query statement according to the syntax analysis state machine, and generating a syntax analysis result;

[0015] Each grammatical component in the grammatical analysis result is constructed as a node, and a node type, a node value, a parent node reference, a child node set and a node attribute are configured for each of the nodes; the nodes are connected to form an initial grammatical tree based on a preset grammatical tree construction rule; the type of joint operation between table nodes is identified from the initial grammatical tree; a joint relationship graph is constructed based on the joint operation type, wherein the vertices of the joint relationship graph are table nodes and the edges are joint relationships;

[0016] Calculate the joint complexity score of each edge in the joint relationship graph, where the joint complexity score is determined by the weight of the edge and the number of tables involved in the joint; determine the root node position based on the joint complexity score; calculate the data dependency, the number of joint relationships and the complexity of the filtering condition of the table node; perform weighted calculation on the data dependency, the number of joint relationships and the complexity of the filtering condition to obtain the table node association degree;

[0017] The initial syntax tree is hierarchically optimized according to the table node association, and the node distribution score of each level is calculated; the hierarchical position of the table node is adjusted based on the node distribution score to generate a query tree structure.

[0018] Based on the number of data records, the size of the data block, and the data block distribution information, calculating the data volume weight coefficient and the data skew index of each table node; generating a table node optimization strategy list according to the data volume weight coefficient and the data skew index includes:

[0019] Constructing a three-dimensional data feature vector of the table node, the three-dimensional data feature vector includes the number of data records, the size of the data block and the distribution information of the data block of the table node; calculating the distribution entropy value of the data block on the cluster node in the data block distribution information; constructing a feature vector matrix based on the number of data records, the size of the data block and the distribution entropy value;

[0020] The eigenvector matrix is ​​normalized, the number of data records is divided by the maximum number of data records in all table nodes to obtain a first normalized value, the data block size is divided by the maximum data block size in all table nodes to obtain a second normalized value, and the distribution entropy value is divided by the maximum distribution entropy value in all table nodes to obtain a third normalized value; the third normalized value is inversely mapped to obtain a distribution uniformity value; the first normalized value, the second normalized value and the distribution uniformity value are weighted according to a preset weight factor to obtain a data volume weight coefficient;

[0021] Calculate the standard deviation and average value of each data block size in the table node; calculate the coefficient of variation based on the standard deviation and the average value; calculate the data tilt index according to the coefficient of variation, the distribution entropy value and the number of cluster nodes; compare the data volume weight coefficient with the preset weight threshold, and compare the data tilt index with the preset tilt threshold to obtain a comprehensive comparison result;

[0022] Determine the priority and sharding strategy of the table node according to the comprehensive comparison result; generate the parallel processing configuration of the table node based on the priority and the sharding strategy; check the parallel processing configuration with the system resource upper limit; when the overall resource demand exceeds the system resource upper limit, adjust the resource quota of each table node in proportion to the data volume weight coefficient; integrate the adjusted priority, sharding strategy and parallel processing configuration to generate a table node optimization strategy list.

[0023] Slicing the data records in the node to be optimized according to the data block distribution information, and splitting each node to be optimized into a plurality of sub-table nodes with balanced data includes:

[0024] Obtain data block distribution information of the node to be optimized, calculate the distribution density function of the data block distribution information based on the kernel function, and generate a cumulative distribution function of the data block according to the data block size based on the distribution density function; calculate the mean and the overall standard deviation of the data block size in the data block distribution information, construct a Gini coefficient calculation formula based on the mean and the overall standard deviation, and calculate the Gini coefficient corresponding to the data inclination degree of the data block distribution information;

[0025] The basic number of shards is obtained by dividing the total data volume of the node to be optimized by the optimal data volume of a single shard, and the weighted product of the basic number of shards and the Gini coefficient is calculated to obtain the optimal number of shards that takes into account data skew; based on the optimal number of shards, the cumulative distribution function is divided into equally spaced intervals, and the shard boundary value corresponding to each of the equally spaced intervals is calculated by the inverse function of the cumulative distribution function to generate an initial shard boundary sequence;

[0026] Calculate the amount of data in each shard divided by the initial shard boundary sequence, normalize the deviation between the actual amount of data in each shard and the ideal amount of data to obtain a shard balance evaluation value; construct a boundary optimization objective function based on the shard balance evaluation value, calculate the gradient direction and step size of the boundary optimization objective function, iteratively adjust the initial shard boundary sequence according to the gradient direction and the step size until the shard balance evaluation value is less than a preset evaluation threshold, and obtain a final shard boundary sequence; shard the data records of the node to be optimized according to the final shard boundary sequence to generate multiple sub-table nodes.

[0027] In the reordered query tree, the node to be optimized is replaced with the data balanced sub-table node, and an independent parallel processing branch is constructed for each sub-table node. Generating the optimized query tree includes:

[0028] The data volume weight is obtained by counting the proportion of the number of data records of each sub-table node to the total data volume, and the calculation volume weight is obtained by analyzing the query operation complexity of each sub-table node; the resource requirement coefficient of each sub-table node is obtained by weighted summing the data volume weight and the calculation volume weight;

[0029] Calculate the optimal parallelism configuration of each of the sub-table nodes based on the resource requirement coefficient and the total parallelism available in the system; analyze the degree of data overlap between the sub-table nodes and construct a data dependency matrix between nodes; group the sub-table nodes according to the data dependency matrix, divide the sub-table nodes with a dependency degree lower than a preset dependency threshold into the same execution layer, and generate a hierarchical execution plan;

[0030] Based on the optimal parallelism configuration, a corresponding parallel processing branch is constructed for each of the sub-table nodes, and the parallel processing branches with the same execution layer are organized into parallel execution units; the execution order of each parallel execution unit is determined according to the hierarchical execution scheme; in the reordered query tree, the nodes to be optimized are replaced by the parallel execution units organized according to the execution order to generate an optimized query tree structure.

[0031] Analyzing the association relationship between table nodes in the optimized query tree, creating a temporary index structure for the table nodes based on the association relationship; integrating the data pre-filtering condition and the temporary index structure into the HIVE query execution plan, and generating a final optimized execution plan includes:

[0032] Obtain a set of table nodes in the optimized query tree, analyze the query operation records between each pair of table nodes in the table node set, and count the number of associated queries and the number of connection conditions between each pair of table nodes; divide the number of associated queries of each pair of table nodes by the total number of queries in which the pair of table nodes participate to obtain a query association ratio, and divide the number of connection conditions by the maximum number of connection conditions in all table node pairs to obtain a connection condition ratio; multiply the query association ratio by the connection condition ratio to obtain the association strength of the table node pair; perform type analysis on the association operation between each pair of table nodes, extract connection operation features, filtering operation features, and grouping operation features, respectively, and assign weight coefficients, and generate an association feature vector of the table node pair through weighted calculation; construct a table node association relationship model based on the association strength and the association feature vector; calculate the selectivity metric, usage frequency, and data distribution uniformity of each data column in the table node in the query, and perform weighted summation of the selectivity metric, the usage frequency, and the data distribution uniformity to obtain the index value of the data column;

[0033] Sort and filter according to the index value of each data column, select the data columns with the top 10% index value as index keys, and generate multiple temporary index structure candidate solutions based on the selected index keys; calculate the index space overhead, index maintenance time overhead and query performance improvement benefits of each candidate solution, and take the difference between the weighted sum of the space overhead and time overhead and the performance improvement benefits as the comprehensive overhead of the index solution; calculate the dependency coefficient of each candidate solution in the current query environment, and multiply the ratio of the performance improvement benefits to the comprehensive overhead by the dependency coefficient to obtain the construction priority of the candidate solution;

[0034] The candidate solutions are screened according to the construction priority, the data scale, construction time constraints and memory resource requirements of the selected solutions are analyzed, a parallel construction plan for the temporary index structure is generated and the creation of the temporary index structure is completed; the interactive impact of the temporary index structure and the existing data pre-filtering conditions is analyzed, and the comprehensive improvement effect on the query performance after the combination is evaluated; an execution dependency graph including the temporary index structure and the data pre-filtering conditions is constructed, and a final optimized execution plan that meets the constraints is determined based on the dependencies between nodes.

[0035] A second aspect of an embodiment of the present invention provides a database query system based on tree structure conversion and HIVE multi-table union, including:

[0036] The first unit is used to obtain a query statement of a HIVE database, and parse the query statement to generate an initial syntax tree; extract a multi-table joint query operation node in the initial syntax tree as a root node, and use the table nodes associated with the multi-table joint query operation node as child nodes to construct a query tree structure; analyze the data distribution of each table node in the query tree structure, and obtain the number of data records, data block size, and data block distribution information of the table node; based on the number of data records, the data block size, and the data block distribution information, calculate the data volume weight coefficient and data tilt index of each table node; generate a table node optimization strategy list according to the data volume weight coefficient and the data tilt index;

[0037] The second unit is used to reorder the table nodes in the query tree structure in the order of small to large data volume according to the data volume weight coefficient in the table node optimization strategy list to generate a reordered query tree; extract the table nodes whose data tilt index exceeds the preset tilt threshold from the table node optimization strategy list as nodes to be optimized; perform data sharding on the data records in the nodes to be optimized according to the data block distribution information, and split each of the nodes to be optimized into a plurality of sub-table nodes with balanced data; in the reordered query tree, replace the nodes to be optimized with the sub-table nodes with balanced data, and construct an independent parallel processing branch for each of the sub-table nodes to generate an optimized query tree;

[0038] The third unit is used to generate a HIVE query execution plan based on the optimized query tree; extract data features of each table node from the table node optimization strategy list, and configure data pre-filtering conditions for each table node according to the data features; analyze the association relationship between the table nodes in the optimized query tree, and create a temporary index structure for the table nodes based on the association relationship; integrate the data pre-filtering conditions and the temporary index structure into the HIVE query execution plan to generate a final optimized execution plan; submit the final optimized execution plan to the HIVE distributed computing cluster, monitor the query execution status of each table node, and collect and merge query results.

[0039] A third aspect of the embodiments of the present invention

[0040] An electronic device is provided, comprising:

[0041] processor;

[0042] a memory for storing processor-executable instructions;

[0043] The processor is configured to call the instructions stored in the memory to execute the aforementioned method.

[0044] A fourth aspect of the embodiments of the present invention is:

[0045] A computer-readable storage medium is provided, on which computer program instructions are stored. When the computer program instructions are executed by a processor, the aforementioned method is implemented.

[0046] The beneficial effects of this application are as follows:

[0047] By constructing a query tree structure and analyzing data distribution, the system can accurately evaluate the data volume weight and data skewness of each table node, thereby formulating targeted optimization strategies and effectively improving query performance and resource utilization efficiency.

[0048] Data sharding and parallel processing are used to deal with data skew problems. Table nodes with large amounts of data are split into multiple balanced sub-table nodes, and independent processing branches are built for each sub-table node, which significantly improves the balance of data processing and avoids single-node performance bottlenecks.

[0049] By configuring data pre-filtering conditions and creating temporary index structures, the system achieves accurate filtering and rapid positioning of data, reduces the amount of invalid data to be processed, and optimizes the execution efficiency of inter-table association operations, ultimately achieving a comprehensive improvement in query performance. BRIEF DESCRIPTION OF THE DRAWINGS

[0050] Figure 1 It is a flowchart of a database query method based on tree structure conversion and HIVE multi-table union according to an embodiment of the present invention;

[0051] Figure 2 The present invention is a schematic diagram of the structure of a database query system based on tree structure conversion and HIVE multi-table union. DETAILED DESCRIPTION

[0052] In order to make the purpose, technical solution and advantages of the embodiments of the present invention clearer, the technical solution in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0053] The technical solution of the present invention is described in detail with specific embodiments below. The following specific embodiments can be combined with each other, and the same or similar concepts or processes may not be described in detail in some embodiments.

[0054] Figure 1 FIG. 1 is a flow chart of a database query method based on tree structure conversion and HIVE multi-table union according to an embodiment of the present invention. Figure 1 As shown, the method includes:

[0055] S101. Obtain a query statement of a HIVE database, and parse the query statement to generate an initial syntax tree; extract a multi-table joint query operation node in the initial syntax tree as a root node, and use the table nodes associated with the multi-table joint query operation node as child nodes to construct a query tree structure; analyze the data distribution of each table node in the query tree structure, and obtain the number of data records, data block size, and data block distribution information of the table node; based on the number of data records, the data block size, and the data block distribution information, calculate the data volume weight coefficient and data tilt index of each table node; generate a table node optimization strategy list according to the data volume weight coefficient and the data tilt index;

[0056] S102 reorders the table nodes in the query tree structure in the order of data volume from small to large according to the data volume weight coefficient in the table node optimization strategy list to generate a reordered query tree; extracts table nodes whose data tilt index exceeds a preset tilt threshold from the table node optimization strategy list as nodes to be optimized; performs data sharding on the data records in the nodes to be optimized according to the data block distribution information, and splits each of the nodes to be optimized into a plurality of data-balanced sub-table nodes; replaces the nodes to be optimized with the data-balanced sub-table nodes in the reordered query tree, and constructs an independent parallel processing branch for each of the sub-table nodes to generate an optimized query tree;

[0057] S103. Generate a HIVE query execution plan based on the optimized query tree; extract data features of each table node from the table node optimization strategy list, and configure data pre-filtering conditions for each table node according to the data features; analyze the association relationship between the table nodes in the optimized query tree, and create a temporary index structure for the table nodes based on the association relationship; integrate the data pre-filtering conditions and the temporary index structure into the HIVE query execution plan to generate a final optimized execution plan; submit the final optimized execution plan to the HIVE distributed computing cluster, monitor the query execution status of each table node, and collect and merge query results.

[0058] In an optional implementation, a query statement of a HIVE database is obtained, and the query statement is parsed to generate an initial syntax tree; a multi-table joint query operation node in the initial syntax tree is extracted as a root node, and table nodes associated with the multi-table joint query operation node are used as child nodes, and constructing a query tree structure includes:

[0059] Performing morpheme segmentation on the query statement of the HIVE database, extracting a keyword set, an identifier set, and an operator set in the query statement; constructing a syntax analysis state machine based on the keyword set, the identifier set, and the operator set; performing state conversion on the query statement according to the syntax analysis state machine, and generating a syntax analysis result;

[0060] Each grammatical component in the grammatical analysis result is constructed as a node, and a node type, a node value, a parent node reference, a child node set and a node attribute are configured for each of the nodes; the nodes are connected to form an initial grammatical tree based on a preset grammatical tree construction rule; the type of joint operation between table nodes is identified from the initial grammatical tree; a joint relationship graph is constructed based on the joint operation type, wherein the vertices of the joint relationship graph are table nodes and the edges are joint relationships;

[0061] Calculate the joint complexity score of each edge in the joint relationship graph, where the joint complexity score is determined by the weight of the edge and the number of tables involved in the joint; determine the root node position based on the joint complexity score; calculate the data dependency, the number of joint relationships and the complexity of the filtering condition of the table node; perform weighted calculation on the data dependency, the number of joint relationships and the complexity of the filtering condition to obtain the table node association degree;

[0062] The initial syntax tree is hierarchically optimized according to the table node association, and the node distribution score of each level is calculated; the hierarchical position of the table node is adjusted based on the node distribution score to generate a query tree structure.

[0063] This embodiment provides a method for obtaining a HIVE database query statement and parsing and generating a query tree structure. The method includes the following steps:

[0064] First, get the query statement of the HIVE database. This can be obtained by user input or reading from a file. For example, suppose we have the following query statement:

[0065] "SELECT a.id, b.name, c.age FROM table_a a JOIN table_b b ON a.id =b.id LEFT JOIN table_c c ON b.id = c.id WHERE a.id > 100 AND b.name LIKE '%test%'";

[0066] Next, the query statement is segmented. This step uses a lexical analyzer to split the query statement into a series of tokens. In this example, we can get the following token sequence:

[0067] SELECT, a.id, ,, b.name, ,, c.age, FROM, table_a, a, JOIN, table_b,b, ON, a.id, =, b.id, LEFT, JOIN, table_c, c, ON, b.id, =, c.id, WHERE, a.id,>, 100, AND, b.name, LIKE, '%test%';

[0068] Then, a set of keywords (such as SELECT, FROM, JOIN, WHERE, etc.), a set of identifiers (such as a.id, b.name, table_a, etc.), and a set of operators (such as =, >, LIKE, etc.) are extracted from these tokens.

[0069] Based on the extracted keywords, identifiers, and operator sets, a syntax analysis state machine is constructed. This state machine defines the syntax rules and state transitions of the query statement. For example, it may contain the following states: initial state, SELECT clause state, FROM clause state, JOIN clause state, WHERE clause state, etc.

[0070] The constructed syntax analysis state machine is used to perform state transition on the query statement and generate syntax analysis results. This process will identify the various components of the query statement, such as the select list, table reference, connection conditions, filter conditions, etc.

[0071] Each grammatical component in the grammatical analysis result is constructed into a node. Each node contains the following information: node type (such as SELECT, FROM, JOIN, WHERE, etc.), node value (such as specific column name, table name, conditional expression, etc.), parent node reference, child node set and node attributes (such as connection type, alias, etc.).

[0072] Based on the preset syntax tree construction rules, these nodes are connected to form an initial syntax tree. In this example, the root node of the initial syntax tree may be a SELECT node, whose child nodes include a FROM node and a WHERE node, and the child nodes of the FROM node include multiple JOIN nodes, etc.

[0073] Identify the type of join operation between table nodes from the initial syntax tree. In this case, we have an inner join (JOIN) and a left outer join (LEFT JOIN).

[0074] A join relationship graph is constructed based on the identified join operation types. In this graph, vertices represent table nodes (table_a, table_b, table_c) and edges represent join relationships (JOIN, LEFT JOIN).

[0075] Calculate the join complexity score for each edge in the join graph. This score can be calculated based on the weight of the edge (for example, an inner join may have a higher weight than an outer join) and the number of tables involved in the join. In this example, the complexity of the first JOIN may be lower than that of the LEFT JOIN because it only involves two tables.

[0076] The root node position is determined based on the calculated join complexity score. Usually, the join operation with the highest complexity is selected as the root node. In this case, LEFT JOIN may be selected as the root node.

[0077] Calculate the data dependency, number of joins, and filter complexity of each table node. Data dependency can be calculated based on the number of times a table is referenced in a query. The number of joins is the number of join operations a table participates in. Filter complexity can be calculated based on the number and complexity of conditions involving the table in the WHERE clause.

[0078] The data dependency, the number of joint relations and the complexity of the filter conditions are weighted and calculated to obtain the table node association degree, which reflects the importance of the table in the query.

[0079] The initial syntax tree is hierarchically optimized based on the calculated table node relevance. This step rearranges the position of the table nodes in the tree so that nodes with high relevance are closer to the root node.

[0080] Calculate the node distribution score of each level of the optimized tree. This score can be calculated based on the number and type of nodes in each layer, with the goal of making the tree structure more balanced.

[0081] Based on the node distribution scores, the hierarchical positions of the table nodes are further adjusted to finally generate a query tree structure, which should better reflect the logical structure and execution order of the query.

[0082] Through the above steps, we can convert the original HIVE query statement into an optimized query tree structure. This structure not only reflects the grammatical structure of the query, but also takes into account the relationship between tables and the complexity of the query, providing a good foundation for subsequent query optimization and execution.

[0083] This application can achieve:

[0084] This method generates a tree structure that reflects the query logic and complexity by deeply analyzing and optimizing HIVE query statements, which helps to improve the efficiency and accuracy of query processing.

[0085] By considering the joint relationship between tables, data dependency and filtering condition complexity, this method can more accurately identify key operations and important tables in the query, thereby providing more valuable information for query optimization.

[0086] The generated query tree structure provides a good foundation for subsequent query optimization and parallel execution, which helps to improve the performance and scalability of large-scale data processing systems.

[0087] In an optional implementation, based on the number of data records, the size of the data block, and the data block distribution information, a data volume weight coefficient and a data skew index of each table node are calculated; and generating a table node optimization strategy list according to the data volume weight coefficient and the data skew index includes:

[0088] Constructing a three-dimensional data feature vector of the table node, the three-dimensional data feature vector includes the number of data records, the size of the data block and the distribution information of the data block of the table node; calculating the distribution entropy value of the data block on the cluster node in the data block distribution information; constructing a feature vector matrix based on the number of data records, the size of the data block and the distribution entropy value;

[0089] The eigenvector matrix is ​​normalized, the number of data records is divided by the maximum number of data records in all table nodes to obtain a first normalized value, the data block size is divided by the maximum data block size in all table nodes to obtain a second normalized value, and the distribution entropy value is divided by the maximum distribution entropy value in all table nodes to obtain a third normalized value; the third normalized value is inversely mapped to obtain a distribution uniformity value; the first normalized value, the second normalized value and the distribution uniformity value are weighted according to a preset weight factor to obtain a data volume weight coefficient;

[0090] Calculate the standard deviation and average value of each data block size in the table node; calculate the coefficient of variation based on the standard deviation and the average value; calculate the data tilt index according to the coefficient of variation, the distribution entropy value and the number of cluster nodes; compare the data volume weight coefficient with the preset weight threshold, and compare the data tilt index with the preset tilt threshold to obtain a comprehensive comparison result;

[0091] Determine the priority and sharding strategy of the table node according to the comprehensive comparison result; generate the parallel processing configuration of the table node based on the priority and the sharding strategy; check the parallel processing configuration with the system resource upper limit; when the overall resource demand exceeds the system resource upper limit, adjust the resource quota of each table node in proportion to the data volume weight coefficient; integrate the adjusted priority, sharding strategy and parallel processing configuration to generate a table node optimization strategy list.

[0092] First, obtain the basic data information of the table node, including the number of data records, data block size, and the distribution information of data blocks on the cluster nodes. The number of data records is obtained by scanning the metadata information of the table node; the data block size is obtained by counting the actual physical space occupied in the storage system; the data block distribution information is obtained by collecting the storage location information of the data blocks on each cluster node.

[0093] Based on the acquired basic data, a three-dimensional data feature vector of the table node is constructed. Taking a table node as an example, assume that the node contains one million data records, the total data block size is one hundred gigabytes, and it is distributed on ten cluster nodes. The distribution entropy value is obtained by calculating the distribution of data blocks on each node. For example, the data blocks of a table node account for different proportions of 8%, 12%, 9% and so on on ten nodes, and the specific distribution entropy value is calculated based on this.

[0094] When normalizing the feature vector, assuming that the maximum number of data records in all table nodes is 10 million, the first normalized value of the table node is 0.1; the maximum data block size is 500 gigabytes, the second normalized value is 0.2; the maximum distribution entropy value is 3.5, the current entropy value is 2.8, and the third normalized value is 0.8. The third normalized value is reverse mapped to obtain a distribution uniformity value of 0.2. The weight factors are set to 0.4, 0.3, and 0.3 respectively, and the data volume weight coefficient is 0.16 after weighted calculation.

[0095] When calculating the data skew index, the discrete degree of each data block size is counted. Assuming that the standard deviation of the data block size of a table node is 10 gigabytes and the average is 20 gigabytes, the coefficient of variation is 0.5. Combining the aforementioned distribution entropy value and the number of cluster nodes of ten, the data skew index is calculated to be 0.6. Compare the data volume weight coefficient of 0.16 with the preset weight threshold of 0.2, and the data skew index of 0.6 with the preset skew threshold of 0.5, and comprehensively judge that the table node needs to be optimized and adjusted.

[0096] According to the comparison results, the optimization priority of the table node is determined to be high priority, and the sharding strategy of data block redistribution is adopted. When generating the parallel processing configuration, the parallelism of the node is set to eight, and the memory quota of each parallel task is eight gigabytes. When the sum of the resource requirements of all table nodes exceeds the upper limit of the system resources, it is adjusted according to the weight coefficient ratio. For example, the total memory upper limit of the system is one hundred gigabytes. According to the weight coefficient of 0.16 of the node, the adjusted memory quota becomes six gigabytes. Finally, the adjusted configuration information is integrated into the optimization strategy list.

[0097] This application can achieve:

[0098] By constructing three-dimensional data feature vectors and normalizing them, accurate quantification of table node data features is achieved, providing a reliable data basis for subsequent optimization strategy formulation.

[0099] The dual evaluation mechanism based on data volume weight coefficient and data skew index can comprehensively reflect the data distribution status of table nodes and effectively identify target nodes that need to be optimized.

[0100] The weight-based resource allocation scheme not only ensures that important data nodes obtain sufficient computing resources, but also achieves reasonable resource scheduling when system resources are limited, thereby improving the overall operating efficiency of the system.

[0101] In an optional implementation, data records in the node to be optimized are segmented according to the data block distribution information, and each of the node to be optimized is split into a plurality of sub-table nodes with balanced data, including:

[0102] Obtain data block distribution information of the node to be optimized, calculate the distribution density function of the data block distribution information based on the kernel function, and generate a cumulative distribution function of the data block according to the data block size based on the distribution density function; calculate the mean and the overall standard deviation of the data block size in the data block distribution information, construct a Gini coefficient calculation formula based on the mean and the overall standard deviation, and calculate the Gini coefficient corresponding to the data inclination degree of the data block distribution information;

[0103] The basic number of shards is obtained by dividing the total data volume of the node to be optimized by the optimal data volume of a single shard, and the weighted product of the basic number of shards and the Gini coefficient is calculated to obtain the optimal number of shards that takes into account data skew; based on the optimal number of shards, the cumulative distribution function is divided into equally spaced intervals, and the shard boundary value corresponding to each of the equally spaced intervals is calculated by the inverse function of the cumulative distribution function to generate an initial shard boundary sequence;

[0104] Calculate the amount of data in each shard divided by the initial shard boundary sequence, normalize the deviation between the actual amount of data in each shard and the ideal amount of data to obtain a shard balance evaluation value; construct a boundary optimization objective function based on the shard balance evaluation value, calculate the gradient direction and step size of the boundary optimization objective function, iteratively adjust the initial shard boundary sequence according to the gradient direction and the step size until the shard balance evaluation value is less than a preset evaluation threshold, and obtain a final shard boundary sequence; shard the data records of the node to be optimized according to the final shard boundary sequence to generate multiple sub-table nodes.

[0105] When sharding the data records in the node to be optimized, you first need to obtain the data block distribution information of the node to be optimized. By scanning the metadata information of the data block, you can get the size of each data block. Take the user behavior table in a distributed database as an example. The table contains 1,000 data blocks, and the data block sizes range from 64MB to 256MB.

[0106] The kernel density estimation method is used to calculate the density function of the data block distribution. The Gaussian kernel function is selected as the kernel function. A smooth probability density curve is obtained by assigning a weight to each data point and superimposing them. By integrating the density function, the cumulative distribution function of the data block size can be obtained.

[0107] Next, we calculate the mean and standard deviation of the data block size. In this example, the average size of the data block is 128MB and the standard deviation is 32MB. Based on these statistical features, we construct a Gini coefficient calculation method. By calculating the area ratio between the Lorenz curve and the uniform distribution line, we get the Gini coefficient of the data skewness. The Gini coefficient in this example is 0.3, indicating a moderate degree of data skewness.

[0108] Assuming that the optimal data volume of a single shard is 10GB and the total data volume of the nodes to be optimized is 100GB, the number of basic shards is 10. Multiplying the number of basic shards by the weighted value of the Gini coefficient of 1.5, the optimal number of shards after considering data skew is 15. According to the optimal number of shards, the cumulative distribution function is divided into 15 equal intervals, and the length of each interval is 1 / 15. The data block size value corresponding to each interval boundary is calculated through the inverse function of the cumulative distribution function to obtain the initial shard boundary sequence.

[0109] For the initial sharding scheme, calculate the actual amount of data contained in each shard. Ideally, the amount of data in each shard should be close to 6.67GB. Calculate the deviation between the actual amount of data in each shard and the ideal value, and perform normalization to obtain the shard balance evaluation value. By iteratively adjusting the shard boundary position, minimize the difference in data volume between adjacent shards. When the shard balance evaluation value is less than the preset 0.05 threshold, obtain the final shard boundary sequence.

[0110] Finally, according to the optimized shard boundary sequence, the data records in the node to be optimized are divided into different sub-table nodes. The amount of data contained in each sub-table node is relatively balanced, and the data distribution is more reasonable.

[0111] This application can achieve:

[0112] By analyzing the distribution characteristics of data blocks through kernel density estimation and cumulative distribution function, we can accurately grasp the data distribution law, provide a reliable basis for subsequent sharding optimization, and improve the accuracy and reliability of data analysis.

[0113] The Gini coefficient is introduced to quantitatively evaluate the degree of data skew and is used as an adjustment factor for the number of shards, so that the sharding scheme can adaptively cope with different degrees of data skew, thereby enhancing the adaptability and robustness of the sharding strategy.

[0114] The shard boundaries are fine-tuned through iterative optimization, and data balance is achieved by minimizing the difference in data volume between shards, which significantly improves the uniformity of data distribution and the overall computing and storage performance of the system.

[0115] In an optional implementation, in the reordered query tree, the node to be optimized is replaced with the data balanced sub-table node, and an independent parallel processing branch is constructed for each sub-table node, and generating the optimized query tree includes:

[0116] The data volume weight is obtained by counting the proportion of the number of data records of each sub-table node to the total data volume, and the calculation volume weight is obtained by analyzing the query operation complexity of each sub-table node; the resource requirement coefficient of each sub-table node is obtained by weighted summing the data volume weight and the calculation volume weight;

[0117] Calculate the optimal parallelism configuration of each of the sub-table nodes based on the resource requirement coefficient and the total parallelism available in the system; analyze the degree of data overlap between the sub-table nodes and construct a data dependency matrix between nodes; group the sub-table nodes according to the data dependency matrix, divide the sub-table nodes with a dependency degree lower than a preset dependency threshold into the same execution layer, and generate a hierarchical execution plan;

[0118] Based on the optimal parallelism configuration, a corresponding parallel processing branch is constructed for each of the sub-table nodes, and the parallel processing branches with the same execution layer are organized into parallel execution units; the execution order of each parallel execution unit is determined according to the hierarchical execution scheme; in the reordered query tree, the nodes to be optimized are replaced by the parallel execution units organized according to the execution order to generate an optimized query tree structure.

[0119] The present invention provides a method for optimizing a query tree, which generates an optimized query tree by replacing the node to be optimized with a sub-table node with balanced data and constructing an independent parallel processing branch for each sub-table node. The method comprises the following steps:

[0120] First, analyze the data volume and computational volume of each sub-table node. Count the proportion of the number of data records in each sub-table node to the total data volume to obtain the data volume weight. For example, suppose there are three sub-table nodes A, B, and C, and their data record numbers are 1000, 2000, and 3000 respectively, and the total data volume is 6000. Then, their data volume weights are 1 / 6, 1 / 3, and 1 / 2 respectively.

[0121] Next, analyze the query operation complexity of each subtable node to obtain the computational weight. Here, we can consider factors such as the type of query operation, the number of fields involved, and index usage. Assume that after analysis, the computational weights of nodes A, B, and C are 0.2, 0.3, and 0.5, respectively.

[0122] Then, the data volume weight and the computational volume weight are weighted and summed to obtain the resource requirement coefficient of each subtable node. The weight ratio of data volume and computational volume can be set according to the actual situation, for example, 6:4. Then, the resource requirement coefficient of node A is (1 / 6 * 0.6 + 0.2 * 0.4) = 0.18, node B is 0.32, and node C is 0.5.

[0123] Based on the resource requirement coefficient and the total parallelism available in the system, the optimal parallelism configuration of each sub-table node is calculated. Assuming that the total parallelism available in the system is 100, the optimal parallelism configurations of nodes A, B, and C are 18, 32, and 50 respectively.

[0124] Next, analyze the degree of data overlap between sub-table nodes and construct a data dependency matrix between nodes. The degree of data overlap can be evaluated by comparing common fields and association conditions between nodes. Assume that the resulting data dependency matrix is ​​as follows:

[0125] ABC;

[0126] A 1 0.3 0.1;

[0127] B 0.3 1 0.2;

[0128] C 0.1 0.2 1;

[0129] The sub-table nodes are grouped according to the data dependency matrix, and the sub-table nodes with a dependency lower than the preset dependency threshold are divided into the same execution layer to generate a hierarchical execution plan. Assuming the preset dependency threshold is 0.25, nodes A and C can be divided into the same execution layer, and node B can be divided into a separate layer, forming a two-layer execution plan.

[0130] Based on the optimal parallelism configuration, a corresponding parallel processing branch is constructed for each sub-table node. For node A, 18 parallel processing threads can be created, and each thread is responsible for processing about 56 data records. Node B creates 32 parallel processing threads, and each thread processes about 63 data records. Node C creates 50 parallel processing threads, and each thread processes 60 data records.

[0131] Parallel processing branches with the same execution layer are organized into parallel execution units. In this example, the first execution layer contains the parallel processing branches of nodes A and C, and the second execution layer contains the parallel processing branch of node B.

[0132] The execution order of each parallel execution unit is determined according to the layered execution scheme. In this example, the execution order is: first execute the first layer (the parallel processing branches of nodes A and C), and then execute the second layer (the parallel processing branch of node B).

[0133] Finally, in the reordered query tree, the nodes to be optimized are replaced with parallel execution units organized in the execution order to generate the optimized query tree structure. Specifically, the original nodes to be optimized are replaced with two sequentially executed parallel execution units, the first unit contains the parallel processing branches of nodes A and C, and the second unit contains the parallel processing branch of node B.

[0134] Through the above steps, the query tree optimization process is completed, and the optimization goals of data balance and parallel processing are achieved.

[0135] The present application can achieve more accurate resource allocation and improve query processing efficiency and system resource utilization by performing detailed data volume and computational volume analysis on sub-table nodes.

[0136] By constructing a data dependency matrix and hierarchical execution plan between nodes, unnecessary data interaction and waiting time are reduced, the query execution process is optimized, and the overall query performance is improved.

[0137] The design of parallel processing branches and parallel execution units fully utilizes the parallel processing capabilities of the system and significantly improves the processing speed and throughput of large-scale data queries.

[0138] In an optional implementation, analyzing the association relationship between table nodes in the optimized query tree, creating a temporary index structure for the table nodes based on the association relationship; integrating the data pre-filtering condition and the temporary index structure into the HIVE query execution plan, and generating a final optimized execution plan includes:

[0139] Obtain a set of table nodes in the optimized query tree, analyze the query operation records between each pair of table nodes in the table node set, and count the number of associated queries and the number of connection conditions between each pair of table nodes; divide the number of associated queries of each pair of table nodes by the total number of queries in which the pair of table nodes participate to obtain a query association ratio, and divide the number of connection conditions by the maximum number of connection conditions in all table node pairs to obtain a connection condition ratio; multiply the query association ratio by the connection condition ratio to obtain the association strength of the table node pair; perform type analysis on the association operation between each pair of table nodes, extract connection operation features, filtering operation features, and grouping operation features, respectively, and assign weight coefficients, and generate an association feature vector of the table node pair through weighted calculation; construct a table node association relationship model based on the association strength and the association feature vector; calculate the selectivity metric, usage frequency, and data distribution uniformity of each data column in the table node in the query, and perform weighted summation of the selectivity metric, the usage frequency, and the data distribution uniformity to obtain the index value of the data column;

[0140] Sort and filter according to the index value of each data column, select the data columns with the top 10% index value as index keys, and generate multiple temporary index structure candidate solutions based on the selected index keys; calculate the index space overhead, index maintenance time overhead and query performance improvement benefits of each candidate solution, and take the difference between the weighted sum of the space overhead and time overhead and the performance improvement benefits as the comprehensive overhead of the index solution; calculate the dependency coefficient of each candidate solution in the current query environment, and multiply the ratio of the performance improvement benefits to the comprehensive overhead by the dependency coefficient to obtain the construction priority of the candidate solution;

[0141] The candidate solutions are screened according to the construction priority, the data scale, construction time constraints and memory resource requirements of the selected solutions are analyzed, a parallel construction plan for the temporary index structure is generated and the creation of the temporary index structure is completed; the interactive impact of the temporary index structure and the existing data pre-filtering conditions is analyzed, and the comprehensive improvement effect on the query performance after the combination is evaluated; an execution dependency graph including the temporary index structure and the data pre-filtering conditions is constructed, and a final optimized execution plan that meets the constraints is determined based on the dependencies between nodes.

[0142] First, analyze the association relationship of table nodes in the optimized query tree. Obtain all table nodes by traversing the query tree and establish a table node pair list. For each pair of table nodes, count the number of times they appear together in historical queries as the number of associated queries. For example, Table A and Table B appear together 200 times in 1,000 historical queries, Table A participates in 500 queries in total, and Table B participates in 400 queries in total, then their query association ratio is 0.4. At the same time, count the connection conditions between table node pairs, such as equi-connection, range connection, etc. Assuming that the maximum number of connection conditions is 10 and the number of connection conditions for the current table node pair is 4, the connection condition ratio is 0.4. Multiply the query association ratio of 0.4 by the connection condition ratio of 0.4 to obtain an association strength of 0.16.

[0143] Then, the operation type characteristics between table nodes are analyzed. For the connection operation, the connection type, connection column data type and other features are extracted, and a weight of 0.4 is assigned; for the filtering operation, the filtering condition type, filtering value distribution and other features are extracted, and a weight of 0.3 is assigned; for the grouping operation, the grouping column, aggregation function and other features are extracted, and a weight of 0.3 is assigned. The association feature vector is generated through weighted calculation. Combining the association strength and feature vector, a machine learning method is used to construct a table node association relationship model.

[0144] Then evaluate the index value of the data column. Calculate the selectivity metric of each data column, such as the ratio of the number of different values ​​to the total number of rows; count the frequency of use of the column in the query; and analyze the uniformity of data distribution. For numeric columns, the variance coefficient can be used to measure the uniformity of distribution; for character columns, the entropy value can be used to calculate the uniformity. The three indicators are weighted 0.4, 0.3, and 0.3 respectively, and the weighted sum is calculated to obtain the index value score.

[0145] Select index keys based on the index value ranking. Assume that there are 100 data columns, and select the top 10 columns as index keys. Generate different combination schemes for the selected index keys, such as single-column index, joint index, etc. Evaluate the cost and benefit of each scheme, including index space cost, maintenance time cost, and query performance improvement. For example, a scheme requires an additional storage space of 100GB, an average daily maintenance time of 1 hour, and can improve query performance by 30%. Calculate the dependency coefficient based on factors such as the current system load and resource utilization, and finally determine the construction priority of the index scheme.

[0146] Finally, build a temporary index structure in parallel. Divide the index building task into multiple subtasks and execute them in parallel according to the data scale and available resources. Evaluate the synergy between the index and the existing pre-filtering conditions, such as the index may affect the push-down of some filter conditions. Build an execution dependency graph to ensure that the sequence of each execution node satisfies the dependency relationship and generate the final optimized execution plan.

[0147] This application can achieve:

[0148] By deeply analyzing the association relationship and operation characteristics of table nodes and establishing an accurate association model, the accuracy of query optimization is improved, making the generated execution plan more in line with the actual query scenario requirements.

[0149] The use of multi-dimensional evaluation indicators to select the optimal index key and index scheme balances performance improvement and resource overhead, avoids the system burden caused by blindly creating indexes, and maximizes the query optimization effect.

[0150] Combining parallel construction and dependency analysis technology ensures efficient creation and rational use of temporary index structures, significantly improves query execution efficiency, and ensures the stability and reliability of system operation.

[0151] Figure 2 FIG. 1 is a schematic diagram of a database query system based on tree structure conversion and HIVE multi-table union according to an embodiment of the present invention. Figure 2 As shown, the system comprises:

[0152] The first unit is used to obtain a query statement of a HIVE database, and parse the query statement to generate an initial syntax tree; extract a multi-table joint query operation node in the initial syntax tree as a root node, and use the table nodes associated with the multi-table joint query operation node as child nodes to construct a query tree structure; analyze the data distribution of each table node in the query tree structure, and obtain the number of data records, data block size, and data block distribution information of the table node; based on the number of data records, the data block size, and the data block distribution information, calculate the data volume weight coefficient and data tilt index of each table node; generate a table node optimization strategy list according to the data volume weight coefficient and the data tilt index;

[0153] The second unit is used to reorder the table nodes in the query tree structure in the order of small to large data volume according to the data volume weight coefficient in the table node optimization strategy list to generate a reordered query tree; extract the table nodes whose data tilt index exceeds the preset tilt threshold from the table node optimization strategy list as nodes to be optimized; perform data sharding on the data records in the nodes to be optimized according to the data block distribution information, and split each of the nodes to be optimized into a plurality of sub-table nodes with balanced data; in the reordered query tree, replace the nodes to be optimized with the sub-table nodes with balanced data, and construct an independent parallel processing branch for each of the sub-table nodes to generate an optimized query tree;

[0154] The third unit is used to generate a HIVE query execution plan based on the optimized query tree; extract data features of each table node from the table node optimization strategy list, and configure data pre-filtering conditions for each table node according to the data features; analyze the association relationship between the table nodes in the optimized query tree, and create a temporary index structure for the table nodes based on the association relationship; integrate the data pre-filtering conditions and the temporary index structure into the HIVE query execution plan to generate a final optimized execution plan; submit the final optimized execution plan to the HIVE distributed computing cluster, monitor the query execution status of each table node, and collect and merge query results.

[0155] According to a third aspect of the embodiments of the present invention,

[0156] An electronic device is provided, comprising:

[0157] processor;

[0158] a memory for storing processor-executable instructions;

[0159] The processor is configured to call the instructions stored in the memory to execute the aforementioned method.

[0160] A fourth aspect of the embodiments of the present invention is:

[0161] A computer-readable storage medium is provided, on which computer program instructions are stored. When the computer program instructions are executed by a processor, the aforementioned method is implemented.

[0162] The present invention may be a method, an apparatus, a system and / or a computer program product. The computer program product may include a computer-readable storage medium carrying computer-readable program instructions for executing various aspects of the present invention.

[0163] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or replace some or all of the technical features therein with equivalents. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A database query method based on tree structure conversion and HIVE multi-table union, characterized in that: include: Obtain a query statement from a HIVE database, and parse the query statement to generate an initial syntax tree; Extracting a multi-table joint query operation node in the initial syntax tree as a root node, and taking table nodes associated with the multi-table joint query operation node as child nodes to construct a query tree structure; Analyze the data distribution of each table node in the query tree structure, obtain the number of data records, data block size and data block distribution information of the table node; calculate the data volume weight coefficient and data skew index of each table node based on the number of data records, the data block size and the data block distribution information; generate a table node optimization strategy list according to the data volume weight coefficient and the data skew index; According to the data volume weight coefficient in the table node optimization strategy list, the table nodes in the query tree structure are reordered in order of small to large data volume to generate a reordered query tree; Extracting table nodes whose data tilt index exceeds a preset tilt threshold from the table node optimization strategy list as nodes to be optimized; performing data sharding on the data records in the nodes to be optimized according to the data block distribution information, and splitting each of the nodes to be optimized into a plurality of sub-table nodes with balanced data; In the reordered query tree, the nodes to be optimized are replaced with the data-balanced sub-table nodes, and an independent parallel processing branch is constructed for each of the sub-table nodes to generate an optimized query tree; Generate a HIVE query execution plan based on the optimized query tree; Extracting data features of each table node from the table node optimization strategy list, and configuring data pre-filtering conditions for each table node according to the data features; Analyzing association relationships between table nodes in the optimized query tree, and creating a temporary index structure for the table nodes based on the association relationships; Integrate the data pre-filtering condition and the temporary index structure into the HIVE query execution plan to generate a final optimized execution plan; Submit the final optimized execution plan to the HIVE distributed computing cluster, monitor the query execution status of each table node, and collect and merge the query results.

2. The method according to claim 1, characterized in that Obtaining a query statement of a HIVE database, parsing the query statement to generate an initial syntax tree; extracting a multi-table joint query operation node in the initial syntax tree as a root node, and using table nodes associated with the multi-table joint query operation node as child nodes, and constructing a query tree structure includes: Performing morpheme segmentation on the query statement of the HIVE database, extracting a keyword set, an identifier set, and an operator set in the query statement; constructing a syntax analysis state machine based on the keyword set, the identifier set, and the operator set; performing state conversion on the query statement according to the syntax analysis state machine, and generating a syntax analysis result; Each grammatical component in the grammatical analysis result is constructed as a node, and a node type, a node value, a parent node reference, a child node set and a node attribute are configured for each of the nodes; the nodes are connected to form an initial grammatical tree based on a preset grammatical tree construction rule; the type of joint operation between table nodes is identified from the initial grammatical tree; a joint relationship graph is constructed based on the joint operation type, wherein the vertices of the joint relationship graph are table nodes and the edges are joint relationships; Calculate the joint complexity score of each edge in the joint relationship graph, where the joint complexity score is determined by the weight of the edge and the number of tables involved in the joint; determine the root node position based on the joint complexity score; calculate the data dependency, the number of joint relationships and the complexity of the filtering condition of the table node; perform weighted calculation on the data dependency, the number of joint relationships and the complexity of the filtering condition to obtain the table node association degree; The initial syntax tree is hierarchically optimized according to the table node association, and the node distribution score of each level is calculated; the hierarchical position of the table node is adjusted based on the node distribution score to generate a query tree structure.

3. The method according to claim 1, characterized in that Based on the number of data records, the size of the data block, and the data block distribution information, calculating the data volume weight coefficient and the data skew index of each table node; generating a table node optimization strategy list according to the data volume weight coefficient and the data skew index includes: Constructing a three-dimensional data feature vector of the table node, the three-dimensional data feature vector includes the number of data records, the size of the data block and the distribution information of the data block of the table node; calculating the distribution entropy value of the data block on the cluster node in the data block distribution information; constructing a feature vector matrix based on the number of data records, the size of the data block and the distribution entropy value; The eigenvector matrix is ​​normalized, the number of data records is divided by the maximum number of data records in all table nodes to obtain a first normalized value, the data block size is divided by the maximum data block size in all table nodes to obtain a second normalized value, and the distribution entropy value is divided by the maximum distribution entropy value in all table nodes to obtain a third normalized value; the third normalized value is inversely mapped to obtain a distribution uniformity value; the first normalized value, the second normalized value and the distribution uniformity value are weighted according to a preset weight factor to obtain a data volume weight coefficient; Calculate the standard deviation and average value of each data block size in the table node; calculate the coefficient of variation based on the standard deviation and the average value; calculate the data tilt index according to the coefficient of variation, the distribution entropy value and the number of cluster nodes; compare the data volume weight coefficient with the preset weight threshold, and compare the data tilt index with the preset tilt threshold to obtain a comprehensive comparison result; Determine the priority and sharding strategy of the table node according to the comprehensive comparison result; generate the parallel processing configuration of the table node based on the priority and the sharding strategy; check the parallel processing configuration with the system resource upper limit; when the overall resource demand exceeds the system resource upper limit, adjust the resource quota of each table node in proportion to the data volume weight coefficient; integrate the adjusted priority, sharding strategy and parallel processing configuration to generate a table node optimization strategy list.

4. The method according to claim 1, characterized in that: Slicing the data records in the node to be optimized according to the data block distribution information, and splitting each node to be optimized into a plurality of sub-table nodes with balanced data includes: Obtain data block distribution information of the node to be optimized, calculate the distribution density function of the data block distribution information based on the kernel function, and generate a cumulative distribution function of the data block according to the data block size based on the distribution density function; calculate the mean and the overall standard deviation of the data block size in the data block distribution information, construct a Gini coefficient calculation formula based on the mean and the overall standard deviation, and calculate the Gini coefficient corresponding to the data inclination degree of the data block distribution information; The basic number of shards is obtained by dividing the total data volume of the node to be optimized by the optimal data volume of a single shard, and the weighted product of the basic number of shards and the Gini coefficient is calculated to obtain the optimal number of shards that takes into account data skew; based on the optimal number of shards, the cumulative distribution function is divided into equally spaced intervals, and the shard boundary value corresponding to each of the equally spaced intervals is calculated by the inverse function of the cumulative distribution function to generate an initial shard boundary sequence; Calculate the amount of data in each shard divided by the initial shard boundary sequence, normalize the deviation between the actual amount of data in each shard and the ideal amount of data to obtain a shard balance evaluation value; construct a boundary optimization objective function based on the shard balance evaluation value, calculate the gradient direction and step size of the boundary optimization objective function, iteratively adjust the initial shard boundary sequence according to the gradient direction and the step size until the shard balance evaluation value is less than a preset evaluation threshold, and obtain a final shard boundary sequence; shard the data records of the node to be optimized according to the final shard boundary sequence to generate multiple sub-table nodes.

5. The method according to claim 4, characterized in that In the reordered query tree, the node to be optimized is replaced with the data balanced sub-table node, and an independent parallel processing branch is constructed for each sub-table node. Generating the optimized query tree includes: The data volume weight is obtained by counting the proportion of the number of data records of each sub-table node to the total data volume, and the calculation volume weight is obtained by analyzing the query operation complexity of each sub-table node; the resource requirement coefficient of each sub-table node is obtained by weighted summing the data volume weight and the calculation volume weight; Calculate the optimal parallelism configuration of each of the sub-table nodes based on the resource requirement coefficient and the total parallelism available in the system; analyze the degree of data overlap between the sub-table nodes and construct a data dependency matrix between nodes; group the sub-table nodes according to the data dependency matrix, divide the sub-table nodes with a dependency degree lower than a preset dependency threshold into the same execution layer, and generate a hierarchical execution plan; Based on the optimal parallelism configuration, a corresponding parallel processing branch is constructed for each of the sub-table nodes, and the parallel processing branches with the same execution layer are organized into parallel execution units; the execution order of each parallel execution unit is determined according to the hierarchical execution scheme; in the reordered query tree, the nodes to be optimized are replaced by the parallel execution units organized according to the execution order to generate an optimized query tree structure.

6. The method according to claim 1, characterized in that Analyzing association relationships between table nodes in the optimized query tree, and creating a temporary index structure for the table nodes based on the association relationships; Integrating the data pre-filtering condition and the temporary index structure into the HIVE query execution plan to generate a final optimized execution plan includes: Obtain a set of table nodes in the optimized query tree, analyze the query operation records between each pair of table nodes in the table node set, and count the number of associated queries and the number of connection conditions between each pair of table nodes; divide the number of associated queries of each pair of table nodes by the total number of queries in which the pair of table nodes participate to obtain a query association ratio, and divide the number of connection conditions by the maximum number of connection conditions in all table node pairs to obtain a connection condition ratio; multiply the query association ratio by the connection condition ratio to obtain the association strength of the table node pair; perform type analysis on the association operation between each pair of table nodes, extract connection operation features, filtering operation features, and grouping operation features, respectively, and assign weight coefficients, and generate an association feature vector of the table node pair through weighted calculation; construct a table node association relationship model based on the association strength and the association feature vector; calculate the selectivity metric, usage frequency, and data distribution uniformity of each data column in the table node in the query, and perform weighted summation of the selectivity metric, the usage frequency, and the data distribution uniformity to obtain the index value of the data column; Sort and filter according to the index value of each data column, select the data columns with the top 10% index value as index keys, and generate multiple temporary index structure candidate solutions based on the selected index keys; calculate the index space overhead, index maintenance time overhead and query performance improvement benefits of each candidate solution, and take the difference between the weighted sum of the space overhead and time overhead and the performance improvement benefits as the comprehensive overhead of the index solution; calculate the dependency coefficient of each candidate solution in the current query environment, and multiply the ratio of the performance improvement benefits to the comprehensive overhead by the dependency coefficient to obtain the construction priority of the candidate solution; The candidate solutions are screened according to the construction priority, the data scale, construction time constraints and memory resource requirements of the selected solutions are analyzed, a parallel construction plan for the temporary index structure is generated and the creation of the temporary index structure is completed; the interactive impact of the temporary index structure and the existing data pre-filtering conditions is analyzed, and the comprehensive improvement effect on the query performance after the combination is evaluated; an execution dependency graph including the temporary index structure and the data pre-filtering conditions is constructed, and a final optimized execution plan that meets the constraints is determined based on the dependencies between nodes.

7. A database query system based on tree structure conversion and HIVE multi-table union, used to implement the method as described in any one of claims 1 to 6, characterized in that: include: The first unit is used to obtain a query statement of a HIVE database, and parse the query statement to generate an initial syntax tree; Extracting a multi-table joint query operation node in the initial syntax tree as a root node, and taking table nodes associated with the multi-table joint query operation node as child nodes to construct a query tree structure; Analyze the data distribution of each table node in the query tree structure, obtain the number of data records, data block size and data block distribution information of the table node; calculate the data volume weight coefficient and data skew index of each table node based on the number of data records, the data block size and the data block distribution information; generate a table node optimization strategy list according to the data volume weight coefficient and the data skew index; The second unit is used to reorder the table nodes in the query tree structure in the order of small to large data volume according to the data volume weight coefficient in the table node optimization strategy list to generate a reordered query tree; Extracting table nodes whose data tilt index exceeds a preset tilt threshold from the table node optimization strategy list as nodes to be optimized; performing data sharding on the data records in the nodes to be optimized according to the data block distribution information, and splitting each of the nodes to be optimized into a plurality of sub-table nodes with balanced data; In the reordered query tree, the nodes to be optimized are replaced with the data-balanced sub-table nodes, and an independent parallel processing branch is constructed for each of the sub-table nodes to generate an optimized query tree; A third unit is used to generate a HIVE query execution plan based on the optimized query tree; Extracting data features of each table node from the table node optimization strategy list, and configuring data pre-filtering conditions for each table node according to the data features; Analyzing association relationships between table nodes in the optimized query tree, and creating a temporary index structure for the table nodes based on the association relationships; Integrate the data pre-filtering condition and the temporary index structure into the HIVE query execution plan to generate a final optimized execution plan; Submit the final optimized execution plan to the HIVE distributed computing cluster, monitor the query execution status of each table node, and collect and merge the query results.

8. An electronic device, characterized in that: include: processor; a memory for storing processor-executable instructions; The processor is configured to call the instructions stored in the memory to execute the method according to any one of claims 1 to 6.

9. A computer-readable storage medium having computer program instructions stored thereon, characterized in that: When the computer program instructions are executed by a processor, the method according to any one of claims 1 to 6 is implemented.

Images