A method for implementing distributed computing by integrating ultra-large-scale metadata access and computing in analytical databases

Abstract

The present invention discloses a method for integrating ultra-large-scale metadata access and computing in an analytical database to realize distributed computing, comprising the following steps: S1, metadata management, treating metadata as ordinary data, using ordinary data tables to store metadata in columnar form, but requiring definition of the metadata table structure; S2, query planning and optimization, comprising two steps: ordinary execution and two-stage fusion execution; S3, optimization stage, comprising two steps: metadata logic optimization and data execution plan logic optimization; S4, execution stage, distributing the optimized SQL logic plan to each computing node, first reading metadata for distributed computing, obtaining a data file reading list with the lowest cost, handing the list over to the data table processing stage, and then reading the data file to perform specific business logic statistical calculations.

Description

Technical Field

[0001] The present invention relates to the field of IT application technology, and in particular to a method for integrating ultra-large-scale metadata access and computing to achieve distributed computing in an analytical database. Background Art

[0002] As data volumes grow, cloud data warehouses are built using a storage-and-computing separation architecture. These systems will need to scale to hundreds of exabytes of data in the near future. This growth in data size also leads to a continuous increase in the number of stored objects and the size of metadata.

[0003] In cloud data warehouses using data lakes (S3, HDFS, etc.) as storage platforms, data files are typically split into files of a certain size and stored in a columnar format to improve query efficiency. Smaller files store fewer items in each file. This allows you to narrow the search scope by reducing the number of data files read during query planning. However, smaller files are not necessarily better. Smaller files require more files to be opened, which in turn incurs costs.

[0004] Storage metadata is divided into two categories: logical metadata and physical metadata. Currently, there are the following approaches: coarse-grained metadata; fine-grained metadata for single-point optimization; support for fine-grained metadata, which is used in phases but not integrated with query plans; support for fine-grained metadata, which is used to manage metadata in distributed storage but is used during the optimization phase. Traditional approaches attempt to reduce the amount of metadata to achieve scalability, but this sacrifices query performance. Summary of the Invention

[0005] In order to overcome the shortcomings of the existing technology, the present invention provides a method for integrating ultra-large-scale metadata access and calculation in an analytical database to achieve distributed computing. Fine-grained ultra-large-scale metadata (billions, tens of billions, and hundreds of billions) is stored in ordinary tables, and the same distributed query processing and data management technologies as general data are used to process ultra-large-scale metadata. The access and processing of metadata are integrated with the processing of general data. During the query, the original query statement is rewritten to integrate the metadata access and the calculation of the original query. The SQL optimizer is used to simultaneously optimize the integrated logical plan tree of data and metadata, thereby improving computing efficiency and solving the problem of using ultra-large-scale metadata.

[0006] To solve the above technical problems, the present invention provides the following technical solution: a method for integrating ultra-large-scale metadata access and computing in an analytical database to implement distributed computing, comprising the following steps:

[0007] S1. Metadata management: Treat metadata as ordinary data and use ordinary data tables to store metadata in columnar format. However, the metadata table structure needs to be defined.

[0008] S2, query planning and optimization, including normal execution and two-stage fusion execution;

[0009] S3, optimization phase, includes two steps: metadata logic optimization and data execution plan logic optimization;

[0010] S4, the execution phase, distributes the optimized SQL logic plan to each computing node. First, metadata is read for distributed computing to obtain the lowest-cost data file read list. This list is then passed to the data table processing phase, which then reads the data files and performs specific business logic statistical calculations.

[0011] Furthermore, in step S1, each row in the system table (CMETA) corresponds to a data file. When building CMETA for a given table, it is necessary to traverse its "potential" nested type structure to collect the "leaf" fields of the nested types (i.e., ARRAY and STRUCT). The process of building the metadata structure schema for CMETA can be described by a recursive algorithm, which is applied to each column in the table.

[0012] Furthermore, in step S2, the normal execution is to open each data file, use some metadata stored in the data file (usually stored in the file header or tail), compare the information in the Where filter condition with the metadata, and determine whether the data in the data block needs to be read;

[0013] Furthermore, in step S2, the two-phase fusion execution delays reading the physical metadata of the table until execution (until the partition is actually assigned to the worker node).

[0014] Furthermore, the two-stage fusion execution includes

[0015] S21, optimize the original SQL, the query planner first uses only logical metadata to generate a query plan with folded constants and pushed-down filters;

[0016] S22. Generate metadata access SQL. The data file locator list generated by this query may contain some false positives, but this does not affect the final query results. The filter conditions in the original query will filter out data records that do not meet the requirements.

[0017] S23, rewrite the original SQL, merge the metadata access SQL generated in S22 with the original SQL to form a new SQL;

[0018] S24: Optimize the fused SQL statements again. The optimized SQL statements are then optimized again to achieve the overall optimal result.

[0019] Furthermore, in step S3, metadata logic optimization is performed based on metadata statistics to reduce the amount of metadata data that needs to be read. Based on the metadata of the metadata table, the rules are optimized and applied to the SQL logical plan tree to generate the optimal execution plan for the metadata itself.

[0020] Furthermore, in step S3, the data execution plan logic optimization utilizes the optimization capabilities of the SQL optimizer, applies optimization rules, and optimizes the data execution logic plan.

[0021] Compared with the prior art, the present invention has the following beneficial effects:

[0022] When using fine-grained metadata in the present invention, fine-grained ultra-large-scale metadata (billions, tens of billions, and hundreds of billions) is stored in ordinary tables, and the same distributed query processing and data management technology as general data is used to process ultra-large-scale metadata, integrating metadata access and processing with general data processing. The optimization rules of the SQL optimizer can be used to optimize the access of the metadata itself, thereby improving computing efficiency and solving the problem of using ultra-large-scale metadata; the present invention stores metadata in ordinary data tables, so the metadata itself also contains metadata (coarse-grained), which is divided into two layers: metadata (coarse-grained) of the metadata table - metadata table. Coarse-grained metadata of the metadata is used for optimization in the optimization stage. The two layers of metadata can reduce the time consumption in the optimization stage, and the optimization time is acceptable. Analytical databases are naturally suitable for large-scale data processing. Analytical databases are naturally suitable for large-scale data processing. DETAILED DESCRIPTION

[0023] In order to make the technical means, creative features, purpose and efficacy of the present invention easy to understand, the present invention is further described below in conjunction with specific examples, but the following examples are only preferred embodiments of the present invention, not all. Based on the examples in the embodiments, other embodiments obtained by those skilled in the art without making creative work are within the scope of protection of the present invention. The experimental methods in the following examples, unless otherwise specified, are conventional methods, and the materials, reagents, etc. used in the following examples, unless otherwise specified, can be obtained from commercial channels.

[0024] Example 1

[0025] The present invention provides a method for implementing distributed computing by integrating ultra-large-scale metadata access and computing in an analytical database, comprising the following steps:

[0026] S1. Metadata management: Metadata is treated as ordinary data and stored in a columnar format using ordinary data tables. However, the metadata table structure needs to be defined. Each row in the system table (CMETA) corresponds to a data file. When building CMETA for a given table, its "potential" nested type structure needs to be traversed to collect the "leaf" fields of the nested types (i.e., ARRAY and STRUCT). The process of building the metadata structure schema for CMETA can be described by a recursive algorithm that is applied to each column in the table.

[0027] S2, query planning and optimization, including two steps: normal execution and two-stage fusion execution. Normal execution is to open each data file, use some metadata stored in the data file (usually stored in the file header or tail), compare the information in the Where filter condition with the metadata, and determine whether to read the data in the data block. Two-stage fusion execution delays the reading of the physical metadata of the table and delays the reading of the physical metadata until the execution time (until the partition is actually assigned to the worker node). Two-stage fusion execution includes

[0028] S21, optimize the original SQL, the query planner first uses only logical metadata to generate a query plan with folded constants and pushed-down filters;

[0029] S22. Generate metadata access SQL. The data file locator list generated by this query may contain some false positives, but this does not affect the final query results. The filter conditions in the original query will filter out data records that do not meet the requirements.

[0030] S23, rewrite the original SQL, merge the metadata access SQL generated in S22 with the original SQL to form a new SQL;

[0031] S24: Optimize the fused SQL statements again. The optimized SQL statements are then optimized again to achieve the overall optimal result.

[0032] S3, the optimization phase, includes two steps: metadata logic optimization and data execution plan logic optimization. Metadata logic optimization involves tailoring metadata based on statistical information to reduce the amount of metadata required to be read. Based on the metadata of the metadata table, rules are optimized and applied to the SQL logical plan tree to generate the optimal execution plan for the metadata itself. Data execution plan logic optimization utilizes the optimization capabilities of the SQL optimizer and applies optimization rules to optimize the data execution logic plan.

[0033] S4, the execution phase, distributes the optimized SQL logic plan to each computing node. First, metadata is read for distributed computing to obtain the lowest-cost data file read list. This list is then passed to the data table processing phase, which then reads the data files and performs specific business logic statistical calculations.

[0034] In a specific embodiment of the present invention, metadata is treated as ordinary data and stored in columnar form using ordinary data tables. However, the metadata table structure needs to be defined. For example, a sales record table, Sales, contains complex field types of repeated (ARRAY) and nested (STRUCT). The DDL definition is as follows:

[0035]

[0036]

[0037] Each row in the system table (CMETA) corresponds to a data file. When building CMETA for a given table, its "potential" nested type structure needs to be traversed to collect the "leaf" fields of the nested types (i.e., ARRAY and STRUCT). The process of building the metadata structure schema for CMETA can be described by the following recursive algorithm, which is applied to each column in the table:

[0038]

[0039]

[0040] The CMETA of the Sales table is as follows:

[0041]

[0042]

[0043] Take the following query as an example:

[0044] SELECT SUM(totalSale)

[0045] FROM Sales

[0046] WHERE orderTimestamp BETWEEN

[0047] '2019-05-21 12:30:00'AND'2019-05-22 21:30:00'

[0048] The simplest way to execute this query is to open each data file, use the metadata stored in the data file (usually stored in the file header or footer), compare the metadata with the information in the Where filter, and determine whether to read the data in the data block. BigQuery uses distributed processing technology to query CMETA and skip data blocks, which can achieve better performance.

[0049] For tables with relatively small amounts of data, such as just a few dozen data files, metadata is relatively sparse, and the cost of reading metadata is negligible. However, for tables with millions or even tens of millions of data files, the cost of reading metadata before query execution can be very high, resulting in longer query execution times. Depending on the size of the table, we have verified that latency increases by tens of milliseconds (10GB tables) to tens of minutes (PB-level tables). To avoid this, the present invention delays reading the physical metadata of the table until execution time (until the partition is actually assigned to a worker node). The query planning phase involves three major steps: optimizing the original SQL, generating metadata access SQL, and rewriting the original SQL.

[0050] The query plan is then rewritten as a semi-join of the original query with CMETA on the _datafile_locator column. The right side of the semi-join is a scan of CMETA, which can also contain filters derived from the WHERE clause in the original query. The query generates a list of paths to all data blocks that need to be read. The constant start_timestamp is the snapshot timestamp of the query execution. In the case of time-travel reads, this timestamp comes from the WHERE clause in the user's SQL.

[0051] (1) Optimize original SQL

[0052] The query planner first uses only logical metadata to generate a query plan with folded constants and pushed-down filters;

[0053] (2) Generate metadata access SQL

[0054] SELECT_block_locator

[0055] FROM CMETA_Sales

[0056] WHERE

[0057] orderTimestamp.min_value<='2019-05-22 21:30:00'

[0058] AND

[0059] orderTimestamp.max_value>='2019-05-21 12:30:00'

[0060] AND creation_timestamp<=start_timestamp

[0061] AND(deletion_timestamp IS NULL

[0062] OR deletion_timestamp>start_timestamp)

[0063] The data file locator list generated by this query may contain some false positives, but this does not affect the final query results. The filter conditions in the original query will filter out data records that do not meet the conditions.

[0064] (3) Rewrite the original SQL

[0065] Merge the metadata access SQL generated in (2) with the original SQL to form a new SQL. Assuming that each row in Sales has a _datafile_locator virtual column, the original query is rewritten as:

[0066]

[0067]

[0068] (4) Fusion, integration, and optimization

[0069] The merged SQL is then optimized again using the optimizer to achieve the overall optimality.

[0070] The subquery on CMETA first generates a list of data files. When executed, the list of data files is broadcast to the other end of the Join, that is, the original query. A table can have millions of data files, but only the data files in the list generated by the query on CMETA are processed by the original query. If the data in the table is partitioned by ts, the data files in the list are several orders of magnitude smaller than the total data files in the table. In addition, although T may have as many as 10,000 columns, the columns read from it only include:

[0071] _datafile_locator data file path

[0072] orderTimestamp.min_value data

[0073] orderTimestamp.max_value

[0074] creation_timestamp

[0075] deletion_timestamp

[0076] After that, optimization is carried out. The optimization phase is divided into two small phases: metadata logic optimization and data execution plan logic optimization;

[0077] (1) Metadata logic optimization

[0078] Perform metadata pruning based on metadata statistics to reduce the amount of metadata that needs to be read. Based on the metadata in the metadata table, optimize the application rules of the SQL logical plan tree to generate the optimal execution plan for the metadata itself.

[0079] (2) Data execution plan logic optimization

[0080] Leverage the optimization capabilities of the SQL optimizer, apply optimization rules, and optimize the data execution logic plan;

[0081] The execution phase is essentially similar to the existing analytical database process. The optimized SQL logic plan is distributed to each computing node. First, metadata is read for distributed computing to obtain the lowest-cost data file read list. This list is then passed to the data table processing phase, which then reads the data files and performs specific business logic statistical calculations.

[0082] When using fine-grained metadata in the present invention, the metadata is treated as ordinary data, and the optimization rules of the SQL optimizer can be used to optimize the access to the metadata itself; the present invention stores metadata in ordinary data tables, so the metadata itself also contains metadata (coarse-grained), which is divided into two layers: metadata (coarse-grained) of the metadata table - metadata table. Coarse-grained metadata of the metadata is used for optimization in the optimization stage. The two layers of metadata can reduce the time consumption in the optimization stage, and the optimization time is acceptable. Analytical databases are naturally suitable for large-scale data processing. Analytical databases are naturally suitable for large-scale data processing.

[0083] While embodiments of the invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions, and alterations may be made to these embodiments without departing from the principles and spirit of the invention, the scope of which is defined by the appended claims and their equivalents.

Claims

1. A method for implementing distributed computing by integrating ultra-large-scale metadata access and computing in an analytical database, characterized by: The following steps are involved: S1. Metadata management: Treat metadata as ordinary data and use ordinary data tables to store metadata in columnar format. However, the metadata table structure needs to be defined. S2, query planning and optimization, including normal execution and two-stage fusion execution; S3, optimization phase, includes two steps: metadata logic optimization and data execution plan logic optimization; S4, the execution phase, distributes the optimized SQL logic plan to each computing node. First, metadata is read for distributed computing to obtain the lowest-cost data file read list. This list is then passed to the data table processing phase, which then reads the data files and performs specific business logic statistical calculations. In the S2 step, the two-stage fusion execution delays the reading of the physical metadata of the table, delaying the reading of the physical metadata until the execution time, until the partition is actually allocated to the working node.

2. The method for implementing distributed computing by integrating ultra-large-scale metadata access and computing in an analytical database according to claim 1, characterized in that: In step S1, each row in the system table corresponds to a data file. When building CMETA for a given table, it is necessary to traverse its "potential" nested type structure to collect the "leaf" fields of the nested type. The process of building the metadata structure schema for CMETA is described by a recursive algorithm that is applied to each column in the table.

3. The method for implementing distributed computing by integrating ultra-large-scale metadata access and computing in an analytical database according to claim 1, characterized in that: In step S2, the normal execution is to open each data file, use some metadata stored in the data file, which is stored in the file header or tail, compare the information in the Where filter condition with the metadata, and determine whether the data in the data block needs to be read.

4. The method for implementing distributed computing by integrating ultra-large-scale metadata access and computing in an analytical database according to claim 1, characterized in that: The two-stage fusion execution includes: S21, optimize the original SQL, the query planner first uses only logical metadata to generate a query plan with folded constants and pushed-down filters; S22. Generate metadata access SQL. The data file path list generated by this query may contain some false positives, but this does not affect the final query results. The filter conditions in the original query will filter out data records that do not meet the requirements. S23, rewrite the original SQL, merge the metadata access SQL generated in S22 with the original SQL to form a new SQL; S24. Fusion SQL is then optimized. The fused SQL is then optimized again using the optimizer to achieve overall optimization.

5. The method for implementing distributed computing by integrating ultra-large-scale metadata access and computing in an analytical database according to claim 1, characterized in that: In the S3 step, metadata logic optimization is performed based on metadata statistical information to reduce the amount of metadata data that needs to be read. Based on the metadata of the metadata table, the application rules of the SQL logical plan tree are optimized to generate the optimal execution plan for the metadata itself.

6. The method for implementing distributed computing by integrating ultra-large-scale metadata access and computing in an analytical database according to claim 1, characterized in that: In the S3 step, data execution plan logic optimization utilizes the optimization capabilities of the SQL optimizer and applies optimization rules to optimize the data execution logic plan.

Images