Data query method and device, electronic equipment, storage medium and computer program product

Abstract

This application provides a data query method, apparatus, electronic device, storage medium, and computer program product. The method includes: receiving a query request, the query request carrying a query-related SQL statement; generating a physical execution plan based on the SQL statement; wherein the physical execution plan includes first information of a first task, the first task being a non-distributed task, and the first information indicating statistical indicators of a data lake table related to the first task; executing the first task based on the physical execution plan to obtain the indicator values ​​of the statistical indicators of the data lake table related to the first task; and outputting query results based on the indicator values ​​of the statistical indicators of the data lake table related to the first task. The solution provided by this application can save computing resources and improve the real-time performance of data queries.

Description

Technical Field

[0001] This application relates to the field of big data processing technology, and in particular to a data query method, apparatus, electronic device, storage medium and computer program product. Background Technology

[0002] In recent years, lake warehouse architecture has become the mainstream open-source big data architecture. Among related technologies, several mainstream Online Analytical Processing (OLAP) engines, such as StarRocks and Apache Doris, are commonly used to analyze data lake tables (or data lake external tables). However, due to the large data scale of data lake tables and the limited computing resources of OLAP, current OLAP aggregation queries for data lake tables suffer from high response latency and high computational resource consumption, making it difficult to meet users' real-time query needs. Aggregate queries refer to operations such as count, sum, max, and min, for example, analyzing website traffic, counting user visits, total ad clicks, and quarterly or annual e-commerce transaction volume. Summary of the Invention

[0003] This application provides a data query method, apparatus, electronic device, storage medium, and computer program product.

[0004] The technical solution of this application embodiment is implemented as follows: Receive a query request, which carries query-related Structured Query Language (SQL) statements; A physical execution plan is generated based on the SQL statement; wherein, the physical execution plan includes first information of the first task, the first task is a non-distributed task, and the first information indicates the statistical indicators of the data lake table related to the first task; The first task is executed based on the physical execution plan to obtain the index values ​​of the statistical indicators of the data lake table related to the first task; Based on the statistical values ​​of the data lake table related to the first task, output the query results.

[0005] This application provides a data query device, including: A receiving unit is configured to receive a query request, wherein the query request carries a query-related Structured Query Language (SQL) statement. A generation unit is used to generate a physical execution plan based on the SQL statement; wherein the physical execution plan includes first information of a first task, the first task being a non-distributed task, and the first information indicating statistical indicators of the data lake table related to the first task; The processing unit is configured to execute the first task based on the physical execution plan, obtain the index values ​​of the statistical indicators of the data lake table related to the first task, and output the query results based on the index values ​​of the statistical indicators of the data lake table related to the first task.

[0006] This application provides an electronic device, including a processor and a memory for storing a computer program that can run on the processor, wherein the processor, when running the computer program, implements the data query method provided in this application.

[0007] This application provides a storage medium storing a computer program or executable instructions, which, when executed by a processor, implements the data query method provided in this application.

[0008] This application provides a computer program product, including a computer program or computer executable instructions. When the computer program or computer executable instructions are executed by a processor, they implement the data query method provided in this application.

[0009] The data query method, apparatus, electronic device, storage medium, and computer program product provided in this application embodiment receive a query request, the query request carrying a query-related SQL statement; generate a physical execution plan based on the SQL statement; wherein, the physical execution plan includes first information of a first task, the first task being a non-distributed task, the first information indicating statistical indicators of a data lake table related to the first task; execute the first task based on the physical execution plan to obtain the indicator values ​​of the statistical indicators of the data lake table related to the first task; and output query results based on the indicator values ​​of the statistical indicators of the data lake table related to the first task. In the above scheme, the electronic device obtains the index values ​​of the statistical indicators of the data lake table related to the first task by executing the non-distributed first task, and outputs the query results based on the obtained index values. Compared with the scheme of scheduling other electronic devices to execute distributed computing tasks, collecting the local query results calculated by each scheduled electronic device, and then summarizing them to obtain the final query result, the first task only needs to obtain the index values ​​of the statistical indicators of the data lake table related to the first task, without scanning and calculating the data lake table data. This can save computing resources and reduce query time. Moreover, the electronic device can output the query results locally based on the obtained index values, without needing to collect and summarize the local query results calculated by each scheduled electronic device. This can further reduce query time and improve the real-time performance of data query. Attached Figure Description

[0010] Figure 1 This is a flowchart illustrating a data query method provided in an embodiment of this application; Figure 2 This is a schematic diagram of the architecture of a data query system provided in an embodiment of this application; Figure 3 This is a flowchart illustrating another data query method provided in an embodiment of this application; Figure 4 This is a schematic diagram of a data query device provided in an embodiment of this application; Figure 5 This is a schematic diagram of the electronic device structure provided in the embodiments of this application.

[0011] It should be noted that the terms "first" and "second" mentioned above are only used to distinguish between different options and do not represent the degree of superiority or inferiority of the options or their priority in the implementation process. Detailed Implementation

[0012] In recent years, lakehouse architecture has become the mainstream open-source big data architecture. The industry commonly uses mainstream OLAP engines such as StarRocks and Apache Doris to analyze data lake tables, with typical data lake tables including Apache Hive and Apache Iceberg. While OLAP engines possess excellent computing power, data lake tables are typically large tables with massive amounts of data. Frequently used queries, such as aggregation queries, often experience high response latency and high computational resource consumption. Aggregation queries, including count, sum, max, and min, are common queries in OLAP engines, used for tasks such as analyzing website traffic, counting user visits, total ad clicks, and quarterly or annual e-commerce transaction volumes. These queries have very high real-time requirements, but due to the large data scale of data lake tables and the limited computing resources of OLAP, current OLAP aggregation queries for data lake tables struggle to meet the real-time demands of business applications.

[0013] To address the slow response time of OLAP aggregation operations on data lake tables, some basic solutions are proposed, including: Option 1. Expand OLAP compute nodes to improve the concurrency of aggregation queries. Data lake tables have a large data volume; expanding OLAP compute nodes can accelerate aggregation queries to some extent. Option 2. Utilize materialized views to accelerate aggregation queries. Materialized views can perform some pre-calculations for aggregations, resulting in significant speed improvements for aggregations and other queries. Option 3. Transform the data lake's external tables into internal tables for the OLAP engine using Extract-Transform-Load (ETL) tasks. OLAP engines typically have efficient internal storage, and converting them to internal tables allows the use of internal storage indexing mechanisms to accelerate aggregate queries.

[0014] While the above solutions have some effect, they still have the following problems: For Option 1, the data lake table data volume is getting larger and larger, but the OLAP computing nodes cannot be expanded indefinitely; moreover, too many computing nodes mean that the computing cost is too high, which is difficult for enterprises to afford.

[0015] For Scheme 2, the materialized view essentially achieves space-for-time trade-off through pre-computation, which will result in more redundant space storage; at the same time, the pre-computation of the materialized view will have a large delay, making it difficult to meet real-time performance requirements.

[0016] For Option 3, the ETL process of converting the data lake table to the internal table will result in redundant storage of a copy of the data in the internal table. In addition, the ETL process has high latency and is complex, making it difficult to apply in real-time aggregation scenarios.

[0017] Based on this, in various embodiments of this application, the electronic device obtains the index values ​​of the statistical indicators of the data lake table related to the first task by executing a non-distributed first task, and outputs the query results based on the obtained index values. Compared with the scheme of scheduling other electronic devices to execute distributed computing tasks, collecting the local query results calculated by each scheduled electronic device, and then performing summary processing to obtain the final query result, the first task only needs to obtain the index values ​​of the statistical indicators of the data lake table related to the first task, without scanning and calculating the data of the data lake table. This can save computing resources and reduce query time. Moreover, the electronic device can output the query results locally based on the obtained index values, without needing to collect the local query results calculated by each scheduled electronic device and perform summary processing. This can further reduce query time and improve the real-time performance of data query.

[0018] To make the objectives, technical solutions, and advantages of this application clearer, the application will be further described in detail below with reference to the accompanying drawings. The described embodiments should not be regarded as limitations on this application. All other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0019] This application provides a data query method applied to electronic devices, including but not limited to terminals, servers, and service terminals capable of running or loading an OLAP engine; of course, electronic devices can also be understood as OLAP management nodes. The OLAP engine can also run or be loaded on a distributed cluster, which can consist of multiple electronic devices. Some electronic devices can run or load OLAP management nodes, while other electronic devices can run or load OLAP compute nodes. The management node is mainly responsible for receiving user query requests, parsing SQL statements, generating physical execution plans, and returning query results; the compute nodes are mainly responsible for executing the computational tasks issued by the management node, realizing the scanning and computation of data. Figure 1 As shown, the method includes: Step 101: Receive query request.

[0020] The query request carries the relevant SQL statement.

[0021] Here, the terminal device can send query requests to the server, and the terminal device runs or loads the OLAP engine client; the electronic device receives the query requests sent by the terminal device. Queries can include aggregate queries, which can be understood as queries that include one or more aggregate operators such as count, sum, max, min, and avg; query requests can carry one or more SQL statements; for example, a query request could be to find the total number of rows in the unique identifier (id, identifier) ​​field of the tbl table, and the SQL statement included in the query request could be select count(id) from tbl; the OLAP engine client can include a Java Database Connectivity (JDBC) client and an Open Database Connectivity (ODBC) client.

[0022] Step 102: Generate a physical execution plan based on the SQL statement.

[0023] The physical execution plan includes first information about a first task, which is a non-distributed task, and the first information indicates statistical metrics of the data lake table related to the first task.

[0024] The statistical metrics of a data lake table can include, but are not limited to, one or more of the following: the number of rows, maximum value, minimum value, sum, and average value of different fields in the data lake table; the statistical metrics of a data lake table can be included in the metrics information of the data lake table, and the metrics information of the data lake table can include not only the statistical metrics of the data lake table, but also the metric values ​​of the statistical metrics of the data lake table; the data lake table related to the first task can be understood as the data lake table related to the aggregate query in the SQL statement.

[0025] Here, upon receiving a query request, the electronic device can determine the fields carried by the aggregation operators in each SQL statement and the name of the data lake table to be queried, based on the SQL statements carried in the query request. This allows it to determine the statistical indicators of the data lake table related to the first task corresponding to each SQL statement, thus obtaining the first information for each SQL statement. Furthermore, it parses each SQL statement in the query request into a corresponding structured object. Based on the structured object corresponding to each SQL statement, it generates a logical execution plan for each SQL statement. The logical execution plan consists of logical operators, and each logical operator has a corresponding set of physical operators. Based on the logical execution plan and the first information, it generates a physical execution plan for each SQL statement. The structured object includes, but is not limited to, an abstract syntax tree (AST), with one AST corresponding to each SQL statement. The first task can be understood as a direct task executed solely by the electronic device; the first information can be understood as the initialization information for the first task.

[0026] Understandably, when a query request carries multiple SQL statements, electronic devices can process each SQL statement independently, with each SQL statement corresponding to a structured object, a logical execution plan, and a physical execution plan.

[0027] The following section uses a structured object as an abstract syntax tree to illustrate the process of generating a physical execution plan based on an SQL statement.

[0028] For example, electronic devices decompose SQL statements into multiple independent, semantically defined minimum units (i.e., tokens), resulting in a token stream. This token stream is then organized into a tree-like structured data according to the SQL's syntactic structure, yielding an abstract syntax tree (Abstract Syntax Tree). A depth-first traversal of the Abstract Syntax Tree can be performed to extract query semantics. Based on the extracted query semantics, logical operators are generated. These logical operators are then sorted according to the data processing order to obtain a logical execution plan. Query semantics can include one or more of the following: query type, target field, related tables, filtering conditions, aggregation operators, etc. Logical operators are atomic, standardized, and physically implementation-free basic data processing operation units defined to implement SQL query semantics; they are the smallest constituent units of the logical execution plan. Logical operators can include, but are not limited to, one or more of the following: scan operators, filtering operators, projection operators, join operators, and aggregation operators, etc. The data processing order can be understood as performing simple data processing first, followed by complex data processing.

[0029] Based on the first information and optimization rules, the electronic device selects the corresponding physical operator with the minimum execution cost from its physical operator candidate set for each logical operator in the logical plan; and sorts the selected physical operators according to the order among the logical operators in the logical plan to obtain the physical execution plan.

[0030] Here, a physical operator can be understood as an atomic execution unit that can be directly scheduled and run in order to implement the semantics of a logical operator; a candidate set of physical operators can be understood as a set of all predefined physical operators corresponding to each logical operator, and each physical operator in the candidate set can implement the semantics of the corresponding logical operator, with differences only in the physical implementation method; execution cost can be understood as the system resources and time overhead required to execute a physical operator; optimization rules can be understood as predefined rules for matching the corresponding physical operators to logical operators and the specific execution method of the physical operators; the execution method of physical operators can include, but is not limited to, one or more of the following: serial execution, parallel execution, distributed execution, and non-distributed execution.

[0031] The electronic device can identify whether an SQL statement contains aggregate operators. If an aggregate operator is present, it can determine the first information required for the first task based on the data lake table name, the type of aggregate operator, and the field names carried by the aggregate operator. The physical execution plan then instructs the electronic device to execute the first task, along with the first information required for its execution. The physical execution plan can be understood as the specific scheme for the electronic device to execute the SQL statement, including the execution steps, required resources, and time. The first information can be understood as statistical indicators related to the first task, determined at least based on the data lake table name, aggregate operator type, and field names carried by the aggregate operator in the SQL statement.

[0032] For example, the SQL statement is `select count(id) from tbl`, where `select` is the query keyword, `count` is the aggregate operator, `id` is the field name carried by the aggregate operator, `from` is the data source keyword, and `tbl` is the data lake table name. The electronic device recognizes that the SQL statement contains an aggregate operator of type `count`, with the field name `id` and the data lake table name `tbl`. Therefore, based on `count`, `id`, and `tbl`, it determines that the first information required for the first task is to query the row count statistics of the `id` field in the `tbl` data lake table. The physical execution plan then instructs the electronic device to execute the first task, along with the first information required for that task.

[0033] The electronic device can also identify whether the SQL statement contains filtering conditions; if it does, it can identify whether the fields carried in the filtering conditions are partitioning fields; if the filtering conditions carry partitioning fields, it can further identify whether the SQL statement contains aggregate operators; if it contains aggregate operators, it can determine the first information required for the first task based on the name of the data lake table to be queried, the type of aggregate operator, the field name carried by the aggregate operator, and the partitioning field carried in the filtering conditions; and instruct the electronic device to execute the first task and the first information required for the first task in the physical execution plan.

[0034] Among these, identifying whether a field carried in the filter condition is a partitioning field can include: Electronic devices can retrieve the metadata of the data lake tables from the cluster storing all data lake tables in the SQL statement; the metadata of all data lake tables can be stored in the cluster's database, and the metadata may include the partition field specified when the table was created; based on the metadata of the data lake table, it is determined whether the field carried in the filter condition belongs to the partition used to create the table. If the field carried in the filter condition is consistent with the partition field in the metadata, then the field carried in the filter condition is the partition field; if the field carried in the filter condition is inconsistent with the partition field in the metadata, then the field carried in the filter condition is not the partition field.

[0035] The partition used for table creation can be understood as the partition field specified when creating a data lake table.

[0036] For example, the SQL statement is `select count(id) from tbl where dt=2024`, where `select` is the query keyword, `count` is the aggregate operator, `id` is the field name carried by the aggregate operator, `from` is the data source keyword, `tbl` is the data lake table name, `where` is the filter keyword, `dt=2024` is the filter condition, and `dt` is the field carried by the filter condition. The electronic device can retrieve the metadata of the data lake table tbl from the cluster storing all data lake tables. Based on the metadata of the data lake table tbl, it identifies that the field dt carried in the filter condition is consistent with the partition field in the metadata, thus determining that the field dt carried in the filter condition is the partition field. The electronic device can further identify that the SQL statement contains an aggregation operator, the aggregation operator type is count, the field name carried by the aggregation operator is id, and the data lake table name is tbl. Therefore, based on dt, count, id, and tbl, it can determine that the first information required by the first task is the statistical indicator of the number of rows in the id field of the data lake table. The physical execution plan instructs the electronic device to start the first task, which is used to obtain the indicator value of the statistical indicator of the number of rows in the id field of the partition dt=2024 of the data lake table tbl.

[0037] Step 103: Execute the first task based on the physical execution plan to obtain the index values ​​of the statistical indicators of the data lake table related to the first task.

[0038] Here, the electronic device can execute the first task according to the physical execution plan generated in step 102, and obtain the index values ​​of the statistical indicators of the data lake table related to the first task by executing the first task. The first task is used to obtain the index values ​​of the statistical indicators of the data lake table related to the first task.

[0039] Electronic devices can obtain the statistical values ​​of the data lake table related to the first task by executing the first task and querying the statistical values ​​of the data lake table related to the first task in the internal table. The electronic device can also perform a first task to obtain the statistical values ​​of the relevant data lake table, based on its type. Data lake table types can include Hive and Iceberg types, which are two different table formats. A table format can be understood as a specification of the data storage structure, metadata structure, and management methods. For Hive data lake tables, the electronic device can access the Hive Metastore (HMS) and parse the specific statistical indicator values ​​through the HMS statistics. If the statistical indicator values ​​are not found in the HMS statistics, the electronic device can continue to access the Parquet metadata file (Footer) to ultimately obtain the statistical indicator values ​​for the Hive data lake table. For Iceberg data lake tables, the electronic device can obtain the statistical indicator values ​​from the Iceberg data lake table's manifest files. If the statistical indicator values ​​are not found in the manifest files, the electronic device can continue to access the Parquet metadata file (Footer) to ultimately obtain the statistical indicator values ​​for the Iceberg data lake table.

[0040] Among them, Parquet files can be understood as the actual business data files of the data lake table. Footer is a component of Parquet files, which does not store actual business data, but only the metadata of the file. Manifest files are metadata files specific to Iceberg type data lake tables, used to record a list of all Parquet files in the Iceberg table, partition information, etc. HMS is an independent metadata service that stores table metadata, physical file storage paths, and other information.

[0041] For example, based on the generated physical execution plan, the electronic device performs the first task by querying the number of rows in the `id` field of the data lake table `tbl` in an internal table. This internal table can be used to store the metric values ​​of one or more data lake tables.

[0042] Even when the internal table is empty, the electronic device can still obtain the metric values ​​of the data lake table related to the first task. Based on the generated physical execution plan, the electronic device executes the first task. If, during the first task's query of the internal table, it finds that the internal table does not store the metric values ​​of the required statistical indicators, it can obtain the metric values ​​of the data lake table related to the first task, depending on the type of data lake table.

[0043] Step 104: Output the query results based on the statistical values ​​of the data lake table related to the first task.

[0044] Here, the electronic device can directly output the statistical values ​​of the data lake table related to the first task as the query result, or it can process the statistical values ​​of the data lake table related to the first task and output the processing result as the query result. Outputting the query result can include, but is not limited to, one or more of the following: returning the query result to the requester who sent the query request; displaying the query result on the screen of the electronic device; or displaying the query result on a display device connected to the electronic device.

[0045] Understandably, when only one statistical indicator from the data lake table related to the first task is needed, or when multiple statistical indicators from the data lake tables related to the first task only need to be output sequentially, the electronic device can directly output the indicator values ​​of the statistical indicators from the data lake tables related to the first task as the query result.

[0046] When the query result is the value of the statistical metric of the data lake table related to the first task obtained by the electronic device, the query result can be directly returned to the terminal via the Remote Procedure Call (RPC) protocol. For example, if the SQL statement is `select count(id) from tbl`, the value of the statistical metric of the number of rows in the `id` field of the data lake table `tbl` is the query result.

[0047] For example, given the statistical values ​​of at least two data lake tables related to the first task, the electronic device can process these values ​​to obtain query results; the query results are then returned to the terminal via the RPC protocol. This processing can include summation, averaging, finding the maximum value, and finding the minimum value. For instance, given the SQL statement `select count(id) from tbl where dt=2024 and dt=2025`, the electronic device obtains two statistical values ​​for the data lake tables related to the first task: the number of rows in the `id` field of the partition `dt=2024` in data lake table `tbl` and the number of rows in the `id` field of the partition `dt=2025` in data lake table `tbl`. The electronic device needs to sum these two values ​​to obtain the query results.

[0048] The RPC protocol can include the Thrift protocol and the gRPC protocol.

[0049] In this embodiment, a query request is received, the query request carrying a Structured Query Language (SQL) statement related to the query; a physical execution plan is generated based on the SQL statement; wherein, the physical execution plan includes first information of a first task, the first task being a non-distributed task, and the first information indicating statistical indicators of a data lake table related to the first task; the first task is executed based on the physical execution plan to obtain the indicator values ​​of the statistical indicators of the data lake table related to the first task; and a query result is output based on the indicator values ​​of the statistical indicators of the data lake table related to the first task. In the above scheme, the electronic device obtains the index values ​​of the statistical indicators of the data lake table related to the first task by executing the non-distributed first task, and outputs the query results based on the obtained index values. Compared with the scheme of scheduling other electronic devices to execute distributed computing tasks, collecting the local query results calculated by each scheduled electronic device, and then summarizing them to obtain the final query result, the first task only needs to obtain the index values ​​of the statistical indicators of the data lake table related to the first task, without scanning and calculating the data lake table data. This can save computing resources and reduce query time. Moreover, the electronic device can output the query results locally based on the obtained index values, without needing to collect and summarize the local query results calculated by each scheduled electronic device. This can further reduce query time and improve the real-time performance of data query.

[0050] To reduce the time to return query results and improve real-time performance, the first task can query the internal table of the electronic device to obtain the index values ​​of the statistical indicators of the data lake table related to the first task. Based on this, in one embodiment, the first task is used to query the internal table of the electronic device, which includes statistical indicators and index values ​​of one or more data lake tables. The step of executing the first task based on the physical execution plan and obtaining the statistical index values ​​of the data lake table related to the first task includes: The first task is executed based on the physical execution plan, and the statistical values ​​of the data lake table related to the first task are retrieved from the internal table.

[0051] Here, the electronic device executes the first task based on the generated physical execution plan. Based on the first information of the first task, it queries the internal table for the statistical values ​​of the relevant data lake table. For example, the SQL statement is `select count(id) from tbl where dt=2024`. The first information could be the statistical metric of the number of rows in the `id` field of the data lake table `tbl`. The electronic device can then query the internal table for the specific value of this statistical metric.

[0052] In this embodiment, the first task is used to query an internal table of the electronic device. The internal table includes statistical indicators and their values ​​for one or more data lake tables. The step of executing the first task based on the physical execution plan to obtain the statistical indicator values ​​of the data lake tables related to the first task includes: executing the first task based on the physical execution plan and querying the statistical indicator values ​​of the data lake tables related to the first task from the internal table. Since the internal table is stored locally on the electronic device and contains the required statistical indicator values ​​of the data lake tables related to the first task, the first task prioritizes querying the statistical indicator values ​​of the data lake tables related to the first task from the internal table, which can reduce the time to return query results and improve real-time performance.

[0053] To reduce the time required for the first task to return query results, decrease query response time, and improve query efficiency, the electronic device can obtain the metric values ​​of one or more data lake tables' statistical indicators and store these values ​​in an internal table. Based on this, in one embodiment, before receiving the query request, the method further includes: Obtain the index values ​​of the statistical indicators of the one or more data lake tables; The internal tables are constructed or updated based on the statistical values ​​of the one or more data lake tables.

[0054] Here, before receiving a query request, the electronic device can obtain the metric values ​​of all statistical indicators for the data lake tables. Data lake table types can include Hive and Iceberg types. For Hive data lake tables, the electronic device can access the Hive metadata repository (HMS) and parse the specific statistical indicator values ​​through the HMS statistics information. If the statistical indicator values ​​are not found in the HMS statistics information, the electronic device can access the footer metadata file of the Parquet file and ultimately obtain the metric values ​​of the Hive data lake table through the footer file. For Iceberg data lake tables, the electronic device can obtain the metric values ​​of the statistical indicators from the manifest files of the Iceberg data lake table. If the metric values ​​are not found in the manifest files, the electronic device can continue to access the footer metadata file of the Parquet file and ultimately obtain the metric values ​​of the Iceberg data lake table through the footer file.

[0055] Before receiving a query request, the electronic device can also retrieve the metric values ​​of one or more specified data lake tables. These specified data lake tables can be designated by the user according to business requirements or based on actual needs; no restrictions are placed here. The process by which the electronic device retrieves the metric values ​​of one or more specified data lake tables is the same as the process described above for retrieving the metric values ​​of all data lake tables, and will not be repeated here.

[0056] Understandably, when the internal table is empty, if the electronic device receives a query request and finds that the internal table does not store the required statistical indicator values ​​during the first task query, it can obtain the required statistical indicator values ​​by following the steps described above for obtaining the statistical indicator values ​​of the data lake table.

[0057] When an electronic device obtains the statistical values ​​of one or more data lake tables, it can write these values ​​into an internal table.

[0058] It's understandable that data lakes can include partitioned tables and non-partitioned tables. A partitioned table can be understood as logically splitting all data into multiple sub-tables (partitions) according to preset rules, with these sub-tables physically stored in multiple independent data files. A non-partitioned table can be understood as storing all data from the entire table in a single physical data file. For partitioned tables, electronic devices can construct internal tables based on the data lake table name, aggregation operator type, fields carried by the aggregation operator, partition field, and the metric values ​​of one or more data lake tables obtained from the SQL statement. For example, in the SQL statement `select count(id) from tbl where dt=2024`, the electronic device can write the data lake table name `tbl`, the `count` aggregation type of the `id` field, the partition `dt=24024`, and the metric value of the number of rows in the `id` field of the partition `dt=2024` in data lake table `tbl` into an internal table, thus constructing the internal table. For non-partitioned tables, electronic devices can construct internal tables based on the name of the data lake table, the type of aggregation operator, the fields carried by the aggregation operator, and the metric values ​​of one or more data lake tables obtained from the SQL statement. For example, for the SQL statement `select count(id) from tbl`, the electronic device can write the name of the data lake table `tbl`, the `count` aggregation type of the `id` field, and the metric value of the number of rows in the `id` field of the entire data lake table `tbl` into the internal table, thus constructing the internal table.

[0059] To ensure consistency between the statistical values ​​of the data lake tables stored in the internal tables and those stored in HMS Statistics, Manifest files, or Footers, electronic devices can periodically synchronize these values. Based on the retrieved statistical values ​​from HMS Statistics, Manifest files, or Footers and their most recent update time, the system determines whether the values ​​of the statistical values ​​stored in the internal tables need updating. If the most recent update time of a statistical value is later than the time it was written to the internal table, then the value needs updating. If the most recent update time is earlier than or equal to the time it was written to the internal table, then the value does not need updating. If it is determined that the value of the statistical value stored in the internal tables needs updating, then the value is updated in the internal tables.

[0060] The timing of electronic device synchronization can be adjusted according to the write and update frequency of the data lake. Since data lakes are generally write-infrequent and read-infrequent, minute-level timing synchronization can usually meet the synchronization requirements.

[0061] In this embodiment, the statistical indicator values ​​of one or more data lake tables are obtained; based on the statistical indicator values ​​of the one or more data lake tables, the internal table is constructed or updated. Thus, by directly obtaining the required statistical indicator values ​​from the internal table, compared to scheduling other electronic devices to perform distributed computing tasks, collecting the local query results calculated by each scheduled electronic device, and then summarizing and processing them to obtain the final query result, computational resources can be saved and the real-time performance of data queries can be improved. Furthermore, by periodically synchronizing the latest statistical indicator values, the accuracy of the statistical indicator values ​​stored in the internal table can also be improved.

[0062] In an optional embodiment, generating a physical execution plan based on the SQL statement includes: Identify the second information in the SQL statement, the second information including a first aggregation operator, or including a partition field and a first aggregation operator, wherein the first aggregation operator is an aggregation operator that meets the aggregation pushdown requirements; Based on the second information, the first information is determined; Based on the first information and the structured object corresponding to the SQL statement, the physical execution plan is generated.

[0063] Here, the electronic device determines whether the SQL statements carried in the query request include second information. The second information includes at least an aggregation operator that meets the aggregation pushdown requirements, i.e., the first aggregation operator, and may also include a partition field. Specifically, if any SQL statement carried in the query request includes a partition field, the partition field is identified from that SQL statement. If any SQL statement carried in the query request includes an aggregation operator that meets the aggregation pushdown requirements, the aggregation operator that meets the aggregation pushdown requirements is identified as the first aggregation operator. Based on the type of the first aggregation operator in the identified second information, the field name carried by the first aggregation operator, and the name of the data lake table to be queried, the required statistical indicators (i.e., the first information) of the data lake table are determined; or, based on the identified partition field, the partition value corresponding to the partition field, the type of the first aggregation operator, the field name carried by the first aggregation operator, and the name of the data lake table to be queried, the required statistical indicators (i.e., the first information) of the data lake table can be determined.

[0064] The electronic device parses each SQL statement in the query request into a corresponding structured object; generates a logical execution plan for each SQL statement based on the structured object; selects the physical operator with the lowest execution cost from its physical operator candidate set for each logical operator in the logical plan according to optimization rules, and binds first information to the first aggregate operator if the SQL statement includes the first aggregate operator; sorts the selected physical operators according to the order of logical operators in the logical plan to obtain the physical execution plan.

[0065] In cases where the SQL statement contains a partition field and the first aggregation operator meets the aggregation pushdown requirements, the electronic device can identify the partition field and the first aggregation operator in the SQL statement to obtain the second information. Based on the partition field, the partition value corresponding to the partition field, the type of the first aggregation operator, the field name carried by the first aggregation operator, and the name of the data lake table to be queried in the second information, the required statistical indicators of the data lake table (i.e., the first information) can be determined.

[0066] If the SQL statement does not have a partition field and the first aggregation operator meets the aggregation pushdown requirements, the electronic device can identify the first aggregation operator in the SQL statement and obtain the second information. Based on the type of the first aggregation operator, the field name carried by the first aggregation operator, and the name of the data lake table to be queried, the required statistical indicators of the data lake table (i.e., the first information) can be determined.

[0067] For partitioned tables, the requirement that the first aggregate operator meets the aggregation pushdown requirement can be understood as the SQL statement not containing a filter condition, or the SQL statement containing a filter condition and the field carried in the filter condition is a partition field; for non-partitioned tables, the requirement that the first aggregate operator meets the aggregation pushdown requirement can be understood as the SQL statement not containing a filter condition.

[0068] Aggregate pushdown can be understood as pushing the completion of aggregation calculations down from the computing layer to the data storage layer. This reduces the amount of data transferred from the storage layer to the computing layer, reduces network and computing node overhead, and improves aggregation query efficiency.

[0069] It is understandable that for partitioned tables, the statistical metrics of a data lake table can include both partition-level and table-level statistical metrics; for non-partitioned tables, the statistical metrics of a data lake table can include table-level statistical metrics.

[0070] For a partitioned table, such as the order table tbl, which includes four fields: year dt, order number id, order amount, and order discount, the year field serves as the partition field. Each value of the partition field corresponds to a partition. For the SQL statement select max(amount) from tbl where dt=2024, the required partition-level statistics can be understood as the statistics of orders in 2024. Partition-level statistics can include the number of rows (row_count), maximum value (max), minimum value (min), sum (sum), and average value (avg) of the order number field; the number of rows, maximum value, minimum value, sum, and average value of the order amount field; and the number of rows, maximum value, minimum value, sum, and average value of the order discount field.

[0071] The electronic device can identify the SQL statement `select max(amount) from tbl where dt=2024` by using the filter keyword `where`, and if it does contain the filter condition `dt=2024`, it can identify that the field `dt` in the filter condition is a partition field. The electronic device can further identify the SQL statement containing the aggregate operator `max`. Based on the data lake table name `tbl`, the aggregate operator type `max`, the field name `amount` carried by the aggregate operator, and the partition field `dt` in the filter condition, the electronic device can determine the first information required for the first task. This first information can be understood as the maximum value of the `amount` field in the 2024 partition of the data lake table `tbl`. The physical execution plan then instructs the execution of the first task, along with the first information required for its execution. The first task can be understood as querying the maximum value of the `amount` field in the 2024 partition of the data lake table `tbl` in an internal table and returning the query result to the terminal.

[0072] For partitioned tables, there can be multiple partitioning fields. For example, the order table tbl includes five fields: year (dt), order number (id), order amount (amount), order discount (discount), and branch office (office). The year and branch office fields serve as partitioning fields. Each set of values ​​for the year and branch office fields corresponds to a partition. For the SQL statement `select max(amount) from tbl where dt=2024 and office=branch1`, the required partition-level statistical indicators can be understood as the statistical indicators for orders from branch1 in 2024. Partition-level statistical indicators can include the number of rows, maximum value, minimum value, sum, and average value of the order number field; the number of rows, maximum value, minimum value, sum, and average value of the order amount field; and the number of rows, maximum value, minimum value, sum, and average value of the order discount field.

[0073] For non-partitioned tables, such as the order table tbl, which includes three fields: order number (id), order amount (amount), and order discount (discount), and does not have partitioning fields, the required table-level statistics for the SQL statement `select max(amount) from tbl` can be understood as the statistics for the entire order table. Table-level statistics can include the total number of rows in the table, the maximum (max), minimum (min), sum (sum), and average (avg) of the order amount field, and the maximum, minimum, sum, and average of the order discount field.

[0074] The electronic device can identify SQL statements like `select max(amount) from tbl` that do not contain filtering conditions based on the `where` keyword; if filtering conditions are present, it can identify that the `dt` field in the filtering conditions is a partition field; the electronic device can identify SQL statements containing the aggregate operator `max`; based on the data lake table name `tbl`, the aggregate operator type `max`, and the field name `amount` carried by the aggregate operator in the SQL statement, the electronic device can determine the first information required for the first task. This first information can be understood as the maximum value of the `amount` field in the data lake table `tbl`; the physical execution plan can then instruct the execution of the first task, along with the first information required for its execution. The first task can be understood as querying the maximum value of the `amount` field in the data lake table `tbl` in the internal table and returning the query result to the terminal.

[0075] Understandably, when an SQL statement queries a partitioned table but does not include filtering conditions, electronic devices can generate a physical execution plan based on table-level statistics.

[0076] For some complex SQL statements, the statistical indicators of the data lake table alone are insufficient to obtain the query results. Electronic devices can generate a physical execution plan based on the SQL statement. This plan includes a first task and a second task, where the second task can be a distributed computing task initiated on a compute node. Based on the physical execution plan, the electronic device executes the first task, obtaining the indicator values ​​of the statistical indicators of the data lake table related to that task. The electronic device also schedules compute nodes to execute distributed computing tasks, scanning and computing the data in the data lake table. It receives the partial query results from the compute nodes and summarizes the indicator values ​​of the statistical indicators of the data lake table related to the first task with the partial query results, outputting the final query result.

[0077] For example, the SQL statement `select id, amount, sum(amount) over() from tbl where dt=2024` means querying the amount of each order in 2024 and the total amount of all orders. Electronic devices can instruct the initiation of the first task in the physical plan to obtain the query results of `sum(amount) over()`, and instruct other electronic devices to execute the second task (distributed task) to obtain data from the data lake table.

[0078] In this embodiment, second information in the SQL statement is identified. This second information includes a first aggregation operator, or a partition field and a first aggregation operator, where the first aggregation operator is one that meets the aggregation pushdown requirements. Based on the second information, the first information is determined. Based on the first information and the structured object corresponding to the SQL statement, the physical execution plan is generated. This allows queries on partitioned tables to fully utilize partition-level statistical indicators to return the required query results, and queries on non-partitioned tables to utilize table-level statistical indicators to return the required query results. This reduces the need to schedule other electronic devices to perform distributed computing tasks, saves computing resources, and improves query real-time performance.

[0079] In one alternative embodiment, the metadata of the internal table includes one or more of the following: The statistical values ​​of data lake tables; The name of the data lake table; Data lake table types; Database name; Data aggregation type; Update time; Data source.

[0080] Here, electronic devices can predefine the metadata of internal tables. The metadata of internal tables can be used to describe the structure of internal tables, such as the table name and the fields they contain. The fields of internal tables can include: data source (catName, catalog name), database name (dbName), data lake table name (tblName), data lake table type (tableType), data aggregation type (aggType, to determine the aggregation type such as count, sum, avg, min, or max), the value of the statistical metric of the data lake table (aggValue), and update time (updateTime, the time point when the metrics were last synchronized).

[0081] Here, the data source can be understood as a namespace, representing different data lakes, such as Data Lake 1 and Data Lake 2. Different data lakes will have different databases, and the databases will have different data lake tables. The data lake table type can be used to determine whether the data lake table is of Hive or Iceberg type. The data aggregation type can be used to determine which aggregation operator is count, max, min, sum, or avg. The statistical index value of the data lake table can be understood as the specific value of the statistical index corresponding to different aggregation types. The update time can be understood as the last time each row of data in the internal table was written.

[0082] Understandably, an internal table can consist of a single table. When using an internal table to record the metric values ​​of one or more data lake tables, since multiple fields may exist within the same data lake table, to distinguish the metric values ​​of different fields within the same data lake table, the data aggregation type can be written in the format "field name_aggregation type". This distinguishes the same aggregation type for different fields, such as field1_max and field2_max representing the maximum value aggregation type for field1 and field2, respectively.

[0083] When an electronic device executes the first task based on the physical execution plan, and queries the statistical indicator values ​​of the data lake table related to the first task from the internal table, it can execute the SQL statement select aggValue from metricsTbl where aggType=amount_max and catName=datalake1 and dbName=database1 and tblName=tbl based on the maximum value of the order amount field in the data lake table tbl, and obtain the indicator value from the internal table.

[0084] An internal table can also consist of two tables. For example, internal table 1 can contain fields such as data source, database, data lake table name, data lake table type, and unique ID, while internal table 2 can contain fields such as unique ID, partition, data aggregation type, and field name (fieldName).

[0085] Electronic devices can execute an SQL statement based on the maximum value of the "amount" field in the data lake table tbl, such as the first piece of information. The statement would be: `select internal table2.aggValue from internal table1 inner join internal table2 on internal table1.id=internal table2.id where internal table2.aggType=max and internal table2.fieldName=amount and internal table1.catName=data lake1 and internal table1.dbName=database1 and internal table1.tblName=tbl`, to obtain the metric value from the internal table.

[0086] Internal tables can also consist of multiple tables. For example, internal table 1 can contain fields such as data source, database, data lake table name, data lake table type, and unique ID; internal table 2 can contain fields such as unique ID, partition, data aggregation type, and field name; and internal table 3 can contain fields such as unique ID, partition, data aggregation type, and field name. Internal tables 2 and 3 can be used to store the statistical indicators and their values ​​for different data lake tables. In other words, a corresponding internal table can be set up for each data lake table to store its statistical indicators and their values.

[0087] In this embodiment, the metadata of the internal table includes one or more of the following: the metric values ​​of the data lake table's statistical indicators; the name of the data lake table; the type of the data lake table; the database name; the data aggregation type; the update time; and the data source. In this way, the internal table can store the metric values ​​of one or more data lake tables' statistical indicators, facilitating electronic devices to retrieve query results from the internal table, reducing the need to schedule other electronic devices to perform distributed computing tasks, saving computing resources, and improving query real-time performance.

[0088] Since the SQL statements contained in user-submitted query requests may be complex and contain useless information, the SQL statements in the user-submitted query requests can be rewritten before generating the physical execution plan to improve query efficiency. Based on this, in an optional embodiment, before generating the physical execution plan based on the SQL statement, the method further includes: Based on the third information, the SQL statement is optimized to obtain an optimized SQL statement; wherein the third information indicates one or more of the following: deleting SQL clauses that affect query efficiency; deleting invalid or redundant expressions in the SQL statement; rewriting SQL statements that include set characters, where the set characters are characters that affect query efficiency; The process of generating a physical execution plan based on the SQL statement includes: Generate a physical execution plan based on the optimized SQL statement.

[0089] Here, the electronic device can optimize the SQL statement based on the third information to obtain the optimized SQL statement; the third information can be understood as one or more preset SQL statement optimization rules; when the third information contains multiple SQL statement optimization rules, the electronic device can determine whether the current SQL statement conforms to the optimization rules one by one, and optimize the SQL statement if it conforms to the optimization rules.

[0090] After rewriting the SQL statement, the electronic device can generate a physical execution plan based on the optimized SQL statement.

[0091] When a third-party instruction indicates the need to remove invalid or redundant expressions in an SQL statement, the electronic device can remove such expressions if it determines they are invalid or redundant. For example, in the SQL statement `select count(id) from tbl limit 1;`, the `limit` expression at the end is redundant. If `limit` is included, the OLAP engine might prioritize applying the `limit` rule, meaning it might read and return only one row of data before counting all rows for the `id`. In this case, the SQL needs to be rewritten to eliminate `limit` expressions in SQL statements containing aggregate operators. Another example is the SQL statement `select count(id) from tbl where dt>=2024 and dt=2025`. The expression `dt>=2024` is redundant and should be removed.

[0092] When the third information indicates that an SQL statement containing a specified character should be rewritten, and that the specified character is one that affects query efficiency, the electronic device can rewrite the SQL statement if it determines that the specified character in the SQL statement is one that affects query efficiency. For example, the SQL statement is `select count(*) from userTable where phone is not null`. If `count(*)` is not added with the `where phone is not null` filter condition, it will count all users. However, the SQL statement actually needs to count the number of users with mobile phone numbers. The current SQL statement has an extra step of filtering calculation compared to `select count(phone) from userTable`. Therefore, the electronic device will rewrite the SQL statement `select count(*) from userTable where phone is not null` to `select count(phone) from userTable`.

[0093] When the third piece of information indicates the need to delete SQL clauses that affect query efficiency, the electronic device can delete those clauses if it determines that the SQL statement contains such clauses. For example, the SQL statement is `select count(id) from tbl where concat(dt,'') = '2026'`, where `concat` represents string concatenation, and `concat(dt,'')` concatenates the value of the `dt` field with an empty string. Without SQL statement optimization, `concat(dt,'')` would prevent the electronic device from recognizing the partition field when generating the physical execution plan, thus failing to initiate distributed computing tasks to obtain query results and reducing query real-time performance. Therefore, the electronic device will rewrite the SQL statement `select count(id) from tbl where concat(dt,'') = '2026'` as `select count(id) from tbl where dt = '2026'`. SQL clauses that affect query efficiency can be understood as those that reduce query performance.

[0094] In this embodiment, the SQL statement is optimized based on third information to obtain an optimized SQL statement. The third information indicates one or more of the following: deleting SQL clauses that affect query efficiency; deleting invalid or redundant expressions in the SQL statement; rewriting SQL statements containing set characters, where the set characters are characters that affect query efficiency. Generating a physical execution plan based on the SQL statement includes generating a physical execution plan based on the optimized SQL statement. This allows for the initial optimization of the SQL statement, reducing the impact on query real-time performance caused by non-standard or unreasonable expressions in the SQL statement itself.

[0095] The following section provides a more detailed description of this application with reference to application examples.

[0096] Figure 2 This application illustrates a data query system architecture. The data query system may include four parts: an OLAP client, an optimizer aggregation and pushdown module, a first task, a module for obtaining the values ​​of statistical indicators from the data lake table, and a module for returning query results. The OLAP client is installed on a terminal device and is used to send user-submitted query requests to the electronic device. The optimizer aggregation and pushdown module is used to generate a physical execution plan based on the SQL statement carried in the query request. The first task is used to obtain the values ​​of statistical indicators from the data lake table, and based on the values ​​of the statistical indicators from the data lake table related to the first task, the query results are output. The optimizer can be understood as a cost-based optimizer (CBO).

[0097] Figure 3 A flowchart illustrating another data query method in this application is shown.

[0098] In this application example, the data query method applied to electronic devices includes the following steps: Step 301: Store the statistical values ​​of the data lake table in an internal table.

[0099] Here, electronic devices can predefine internal table metadata, which can be used to describe the structure of internal tables, such as the table name and the fields they contain. The fields of internal tables can include: data source (catName, catalog name), database name (dbName), data lake table name (tblName), data lake table type (tableType), data aggregation type (aggType, to determine the aggregation type such as count, sum, avg, min, or max), the value of the statistical metric of the data lake table (aggValue), and update time (updateTime, the time point when the metrics were last synchronized).

[0100] Here, the data source can be understood as a namespace, representing different data lakes, such as Data Lake 1 and Data Lake 2. Different data lakes will have different databases, and the databases will have different data lake tables. The data lake table type can be used to determine whether the data lake table is of Hive or Iceberg type. The data aggregation type can be used to determine which aggregation operator is count, max, min, sum, or avg. The statistical index value of the data lake table can be understood as the specific value of the statistical index corresponding to different aggregation types. The update time can be understood as the last time each row of data in the internal table was written.

[0101] An internal table can consist of two tables. For example, internal table 1 can contain fields such as data source, database, data lake table name, data lake table type, and unique ID, while internal table 2 can contain fields such as unique ID, partition, data aggregation type, and field name (fieldName).

[0102] Electronic devices can execute an SQL statement based on the maximum value of the "amount" field in the data lake table tbl, such as the first piece of information. The statement would be: `select internal table2.aggValue from internal table1 inner join internal table2 on internal table1.id=internal table2.id where internal table2.aggType=max and internal table2.fieldName=amount and internal table1.catName=data lake1 and internal table1.dbName=database1 and internal table1.tblName=tbl`, to obtain the metric value from the internal table.

[0103] Data lake tables can be of two types: Hive and Iceberg. These are two different table formats, which can be understood as the structured definition and management methods for data storage and metadata. For Hive data lake tables, electronic devices can access the HMS (Hardware Management System) and parse the values ​​of specific statistical indicators through HMS Statistics. If the statistical indicator values ​​are not found in HMS Statistics, the electronic device can access the footer file (the metadata file in Parquet) to obtain the statistical indicator values ​​for the Hive data lake table. For Iceberg data lake tables, electronic devices can obtain the statistical indicator values ​​from the Iceberg data lake table's Manifest files. If the statistical indicator values ​​are not found in the Manifest files, the electronic device can access the footer file (the metadata file in Parquet) to obtain the statistical indicator values ​​for the Iceberg data lake table.

[0104] Since the data lake table and OLAP engine belong to a storage-compute separation architecture, OLAP needs to initialize the remote file system client first to obtain the data lake metrics information, then obtain the metadata file, and parse the metrics information. This method can affect the real-time performance of the OLAP engine when there are network fluctuations or concurrency limitations of the remote data lake. Therefore, in this application example, a high-performance OLAP internal table is used to store the statistical indicators and their values ​​in real time, thereby improving query efficiency.

[0105] Step 302: Parse the SQL statement.

[0106] Here, the terminal device sends a query request to the server through the installed OLAP engine client; the electronic device receives the query request sent by the terminal device. The query can include aggregate queries, which can be understood as queries that include one or more aggregate operators such as count, sum, max, min, and avg; the query request can carry one or more SQL statements; for example, a query request could be to query the total number of rows in the id field of the tbl table, and the SQL statement included in the query request could be select count(id) from tbl; the OLAP engine client can include Java Database Connectivity clients and Open Database Connectivity clients.

[0107] Upon receiving a query request, the electronic device can parse the SQL statement in the query request; decompose the SQL statement into multiple independent tokens with clear semantics, obtaining a token stream; and organize the token stream into tree-like structured data according to the SQL syntax structure, obtaining an abstract syntax tree. The parsed SQL statement can be represented in the form of an abstract syntax tree, which facilitates subsequent processing by the electronic device.

[0108] Step 303: Perform aggregation pushdown.

[0109] Here, the aggregation pushdown of electronic devices can be understood as generating a physical execution plan based on SQL statements, where the SQL statements can be in the form of parsed abstract syntax trees.

[0110] Step 303 may include steps 304 to 306.

[0111] Step 304: Rewrite the SQL statement.

[0112] Here, when the third information indicates the need to remove invalid or redundant expressions in the SQL statement, the electronic device can remove invalid or redundant expressions based on the determination that the SQL statement contains such expressions. For example, in the SQL statement `select count(id) from tbl limit 1;`, the `limit` expression at the end of the SQL statement is redundant. If `limit` is included, the OLAP engine may prioritize applying the `limit` rule, that is, first read the data and return 1 row of data, and then use `count` to calculate the total number of rows for `id`. In this case, the SQL needs to be rewritten to eliminate the `limit` expression for SQL statements containing aggregate operators.

[0113] When the SQL statement is an abstract syntax tree, the electronic device can traverse each node of the abstract syntax tree, determine whether to optimize the subtree of the current node based on the third information, optimize the subtree if optimization is required, and continue to traverse the next node; otherwise, directly traverse the next node.

[0114] Step 305: Generate a logical execution plan.

[0115] Here, the electronic device can perform a depth-first traversal of the optimized abstract syntax tree to extract query semantics; based on the extracted query semantics, generate logical operators; and sort the logical operators according to the data processing order to obtain a logical execution plan.

[0116] The query semantics can include query type, target field, related tables, filtering conditions, aggregation operators, etc.; logical operators can be understood as the basic data processing operation units that implement SQL query semantics, defined as atomic, standardized, and without physical implementation details, and are the smallest building blocks for constructing a logical execution plan; logical operators can include, but are not limited to, one or more of the following: scan operators, filtering operators, projection operators, join operators, and aggregation operators, etc.; the data processing order can be understood as performing simple data processing first, followed by complex data processing.

[0117] Step 306: Generate a physical execution plan based on the custom optimizer rules.

[0118] Here, the custom optimizer rule can be understood as identifying the first aggregation operator that meets the aggregation pushdown requirements and specifying a predefined rule for the non-distributed execution mode for the first aggregation operator. That is, for partitioned tables, if the SQL statement does not contain filtering conditions, or if the SQL statement contains filtering conditions and the fields carried in the filtering conditions are partitioning fields, the aggregation operator in the SQL statement can be identified as the first aggregation operator; for non-partitioned tables, if the SQL statement does not contain filtering conditions, the aggregation operator in the SQL statement can be identified as the first aggregation operator.

[0119] According to the custom optimizer rules, the electronic device selects the corresponding physical operator with the lowest execution cost from its physical operator candidate set for each logical operator in the logical plan, and binds the first information to the first aggregate operator if the SQL statement includes the first aggregate operator; the selected physical operators are sorted according to the order of logical operators in the logical plan to obtain the physical execution plan.

[0120] In cases where the SQL statement contains a partition field and the first aggregation operator meets the aggregation pushdown requirements, the electronic device can identify the partition field and the first aggregation operator in the SQL statement to obtain the second information. Based on the partition field, the partition value corresponding to the partition field, the type of the first aggregation operator, the field name carried by the first aggregation operator, and the name of the data lake table to be queried in the second information, the required statistical indicators of the data lake table (i.e., the first information) can be determined.

[0121] If the SQL statement does not have a partition field and the first aggregation operator meets the aggregation pushdown requirements, the electronic device can identify the first aggregation operator in the SQL statement and obtain the second information. Based on the type of the first aggregation operator, the field name carried by the first aggregation operator, and the name of the data lake table to be queried, the required statistical indicators of the data lake table (i.e., the first information) can be determined.

[0122] For a partitioned table, such as the order table tbl, which includes four fields: year dt, order number id, order amount, and order discount, the year field serves as the partition field. Each value of the partition field corresponds to a partition. For the SQL statement select max(amount) from tbl where dt=2024, the required partition-level statistics can be understood as the statistics of orders in 2024. Partition-level statistics can include the number of rows, maximum value, minimum value, sum, and average value of the order number field; the number of rows, maximum value, minimum value, sum, and average value of the order amount field; and the number of rows, maximum value, minimum value, sum, and average value of the order discount field.

[0123] The electronic device can identify the SQL statement `select max(amount) from tbl where dt=2024` by using the filter keyword `where`, and if it does contain the filter condition `dt=2024`, it can identify that the field `dt` in the filter condition is a partition field. The electronic device can further identify the SQL statement containing the aggregate operator `max`. Based on the data lake table name `tbl`, the aggregate operator type `max`, the field name `amount` carried by the aggregate operator, and the partition field `dt` in the filter condition, the electronic device can determine the first information required for the first task. This first information can be understood as the maximum value of the `amount` field in the 2024 partition of the data lake table `tbl`. The physical execution plan then instructs the execution of the first task, along with the first information required for its execution. The first task can be understood as querying the maximum value of the `amount` field in the 2024 partition of the data lake table `tbl` in an internal table and returning the query result to the terminal.

[0124] Step 307: Perform the first task.

[0125] The first task is a lightweight thread task of the management node.

[0126] Here, the electronic device executes a first task based on the generated physical execution plan. This first task queries the electronic device's internal tables, which include statistical metrics and their values ​​from one or more data lake tables. By executing this first task, the need to initiate distributed task scanning and data computation is avoided, thus accelerating aggregation queries and improving the efficiency of returning aggregation results.

[0127] Step 308: Obtain the statistical values ​​of the data lake table.

[0128] Here, the electronic device executes the first task based on the generated physical execution plan, and queries the statistical values ​​of the data lake table related to the first task from the internal table.

[0129] For example, the SQL statement is `select max(amount) from tbl where dt=2024`. The first piece of information is the maximum value of the `amount` field in the 2024 partition of the data lake table `tbl`. The electronic device performing the first task can be understood as querying the maximum value of the `amount` field in the 2024 partition of the data lake table `tbl` in the internal table.

[0130] For example, the SQL statement is `select count(id) from tbl where dt=2024`. The first piece of information is the number of rows in the order number field of the 2024 partition of the data lake table `tbl`. The electronic device performing the first task can be understood as querying the number of rows in the order number field of the 2024 partition of the data lake table `tbl` in the internal table.

[0131] Step 309: Output the query results.

[0132] Here, once the electronic device obtains the statistical values ​​of the data lake table related to the first task, it can output the query results via the RPC protocol based on the statistical values ​​of the data lake table related to the first task.

[0133] For example, the SQL statement is `select count(id) from tbl where dt=2024`. The first piece of information is the number of rows in the order number field of the 2024 partition of the data lake table `tbl`. The electronic device performing the first task can be understood as querying the number of rows in the order number field of the 2024 partition of the data lake table `tbl` in the internal table, and directly returning the query result to the client via an RPC protocol (such as Thrift, gRPC). The client can then deserialize and parse the aggregated result.

[0134] To implement the data query method of this application embodiment, this application embodiment also provides a data query device, which is installed on an electronic device, such as... Figure 4 As shown, the device includes: The receiving unit 401 is used to receive a query request, wherein the query request carries a query-related Structured Query Language (SQL) statement; The generation unit 402 is used to generate a physical execution plan based on the SQL statement; wherein, the physical execution plan includes first information of a first task, the first task is a non-distributed task, and the first information indicates the statistical indicators of the data lake table related to the first task; Processing unit 403 is configured to execute the first task based on the physical execution plan, obtain the index values ​​of statistical indicators of the data lake table related to the first task, and output query results based on the index values ​​of statistical indicators of the data lake table related to the first task.

[0135] In an optional embodiment, the first task is used to query an internal table of the electronic device, the internal table including statistical indicators of one or more data lake tables and indicator values ​​of the statistical indicators; The processing unit 403 is further configured to execute the first task based on the physical execution plan and query the statistical index values ​​of the data lake table related to the first task from the internal table.

[0136] In an optional embodiment, before receiving the query request, the processing unit 403 is further configured to: Obtain the index values ​​of the statistical indicators of the one or more data lake tables; The internal tables are constructed or updated based on the statistical values ​​of the one or more data lake tables.

[0137] In an optional embodiment, the generation unit 402 is further configured to: Identify the second information in the SQL statement, the second information including a first aggregation operator, or including a partition field and a first aggregation operator, wherein the first aggregation operator is an aggregation operator that meets the aggregation pushdown requirements; Based on the second information, the first information is determined; Based on the first information and the structured object corresponding to the SQL statement, the physical execution plan is generated.

[0138] In an optional embodiment, the metadata of the internal table includes one or more of the following: The statistical values ​​of data lake tables; The name of the data lake table; Data lake table types; Database name; Data aggregation type; Update time; Data source.

[0139] In an optional embodiment, the device further includes: An optimization unit is configured to optimize the SQL statement based on third information to obtain an optimized SQL statement; wherein the third information indicates one or more of the following: Remove SQL clauses that affect query efficiency; remove invalid or redundant expressions from SQL statements; rewrite SQL statements that include set characters to characters that affect query efficiency. The generating unit 402 is also used for: Generate a physical execution plan based on the optimized SQL statement.

[0140] In practical applications, the generation unit 402, the processing unit 403, and the optimization unit can be implemented by the processor in the information processing device, and the receiving unit 401 can be implemented by the processor in the information processing device in combination with the communication interface.

[0141] It should be noted that the information processing device provided in the above embodiments is only illustrated by the division of the above program modules. In practical applications, the above processing can be assigned to different program modules as needed, that is, the internal structure of the device can be divided into different program modules to complete all or part of the processing described above. In addition, the information processing device and the information processing method embodiments provided in the above embodiments belong to the same concept, and their specific implementation process can be found in the method embodiments, which will not be repeated here.

[0142] Based on the hardware implementation of the above program modules, and in order to implement the method of the embodiments of this application, the embodiments of this application also provide an electronic device, such as... Figure 5 As shown, the electronic device 500 includes: Communication interface 501 enables information exchange with other network nodes; The processor 502 is connected to the first communication interface 501 to enable information interaction with other network nodes and, when running a computer program, executes the methods provided by one or more technical solutions on the first node side. The computer program is stored in the memory 503.

[0143] Specifically, the communication interface 501 is used to receive query requests, the query requests carrying query-related Structured Query Language (SQL) statements; The processor 502 is configured to generate a physical execution plan based on the SQL statement; wherein the physical execution plan includes first information of a first task, the first task being a non-distributed task, and the first information indicating statistical metrics of a data lake table related to the first task. The first task is executed based on the physical execution plan to obtain the index values ​​of the statistical indicators of the data lake table related to the first task; based on the index values ​​of the statistical indicators of the data lake table related to the first task, the query results are output.

[0144] In an optional embodiment, the first task is used to query an internal table of the electronic device, the internal table including statistical indicators of one or more data lake tables and indicator values ​​of the statistical indicators; The processor 502 is also used for: The first task is executed based on the physical execution plan, and the statistical values ​​of the data lake table related to the first task are retrieved from the internal table.

[0145] In an optional embodiment, the processor 502 is further configured to: Obtain the index values ​​of the statistical indicators of the one or more data lake tables; The internal tables are constructed or updated based on the statistical values ​​of the one or more data lake tables.

[0146] In an optional embodiment, the processor 502 is further configured to: Identify the second information in the SQL statement, the second information including a first aggregation operator, or including a partition field and a first aggregation operator, wherein the first aggregation operator is an aggregation operator that meets the aggregation pushdown requirements; Based on the second information, the first information is determined; Based on the first information and the structured object corresponding to the SQL statement, the physical execution plan is generated.

[0147] In an optional embodiment, the metadata of the internal table includes one or more of the following: The statistical values ​​of data lake tables; The name of the data lake table; Data lake table types; Database name; Data aggregation type; Update time; Data source.

[0148] In an optional embodiment, before generating the physical execution plan based on the SQL statement, the processor 502 is further configured to: Based on the third information, the SQL statement is optimized to obtain an optimized SQL statement; wherein the third information indicates one or more of the following: deleting SQL clauses that affect query efficiency; deleting invalid or redundant expressions in the SQL statement; rewriting SQL statements that include set characters, where the set characters are characters that affect query efficiency; The processor 502 is also used to: generate a physical execution plan based on the optimized SQL statement.

[0149] Of course, in practical applications, the various components in the first electronic device 500 are coupled together via a bus system 504. It can be understood that the bus system 504 is used to implement communication between these components. In addition to a data bus, the bus system 504 also includes a power bus, a control bus, and a status signal bus. However, for clarity, in... Figure 5 The general designated all buses as Bus System 504.

[0150] The memory 503 in this embodiment is used to store various types of data to support the operation of the electronic device 500. Examples of such data include any computer program used to operate on the electronic device 500.

[0151] The methods disclosed in the embodiments of this application can be applied to, or implemented by, the processor 502. The processor 502 may be an integrated circuit chip with signal processing capabilities. During implementation, each step of the above method can be completed by the integrated logic circuitry of the hardware in the processor 502 or by instructions in software form. The processor 502 may be a general-purpose processor, a digital signal processor (DSP), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. The processor 502 can implement or execute the methods, steps, and logic block diagrams disclosed in the embodiments of this application. A general-purpose processor may be a microprocessor or any conventional processor, etc. The steps of the methods disclosed in the embodiments of this application can be directly manifested as execution by a hardware decoding processor, or execution by a combination of hardware and software modules in the decoding processor. The software modules may be located in a storage medium, specifically in memory 503. The processor 502 reads information from memory 503 and, in conjunction with its hardware, completes the steps of the aforementioned method.

[0152] In an exemplary embodiment, the electronic device 500 may be implemented by one or more application-specific integrated circuits (ASICs), DSPs, programmable logic devices (PLDs), complex programmable logic devices (CPLDs), field-programmable gate arrays (FPGAs), general-purpose processors, controllers, microcontrollers (MCUs), microprocessors, or other electronic components to perform the aforementioned method.

[0153] It is understood that the memory 503 in this embodiment can be volatile memory or non-volatile memory, or both. Non-volatile memory can be read-only memory (ROM), programmable read-only memory (PROM), erasable programmable read-only memory (EPROM), electrically erasable programmable read-only memory (EEPROM), ferromagnetic random access memory (FRAM), flash memory, magnetic surface memory, optical disc, or compact disc read-only memory (CD-ROM); magnetic surface memory can be disk storage or magnetic tape storage. Volatile memory can be random access memory (RAM), which is used as an external cache. By way of example, but not limitation, many forms of RAM are available, such as Static Random Access Memory (SRAM), Synchronous Static Random Access Memory (SSRAM), Dynamic Random Access Memory (DRAM), Synchronous Dynamic Random Access Memory (SDRAM), Double Data Rate Synchronous Dynamic Random Access Memory (DDRSDRAM), Enhanced Synchronous Dynamic Random Access Memory (ESDRAM), SyncLink Dynamic Random Access Memory (SLDRAM), and Direct Rambus Random Access Memory (DRRAM).The memories described in the embodiments of this application are intended to include, but are not limited to, these and any other suitable types of memories.

[0154] In an exemplary embodiment, this application also provides a storage medium, namely a computer storage medium, specifically a computer-readable storage medium, such as a memory 503 storing a computer program, which can be executed by the processor 502 of the electronic device 500 to complete the steps described in the aforementioned method. The computer-readable storage medium may be a memory such as FRAM, ROM, PROM, EPROM, EEPROM, Flash Memory, magnetic surface memory, optical disc, or CD-ROM.

[0155] For example, this application also provides a computer program product, including a computer program that can be executed by a processor 502 of an electronic device 500 to perform the steps described in the foregoing method.

[0156] It should be noted that terms such as "first" and "second" are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. "Multiple" can refer to two or more items, and "multiple" can refer to two or more items. The term "and / or" in this document merely describes the relationship between related objects, indicating that three relationships can exist. For example, A and / or B can represent: A existing alone, A and B existing simultaneously, and B existing alone. Furthermore, the term "one or more" in this document refers to any combination of at least two of the multiple elements. For example, including one or more of A, B, and C can represent including any one or at least two or more elements selected from the set consisting of A, B, and C.

[0157] Furthermore, the technical solutions described in the embodiments of this application can be combined arbitrarily without conflict.

[0158] The above description is merely a preferred embodiment of this application and is not intended to limit the scope of protection of this application.

Claims

1. A data query method, characterized by, Applied to electronic devices, the method includes: Receive a query request, the query request carrying a structured query language (SQL) statement related to the query; A physical execution plan is generated based on the SQL statement; wherein, the physical execution plan includes first information of the first task, the first task is a non-distributed task, and the first information indicates the statistical indicators of the data lake table related to the first task; The first task is executed based on the physical execution plan to obtain the index values ​​of the statistical indicators of the data lake table related to the first task; Based on the statistical values ​​of the data lake table related to the first task, output the query results.

2. The method according to claim 1, characterized in that, The first task is to query the internal tables of the electronic device, which include statistical indicators of one or more data lake tables and the indicator values ​​of the statistical indicators; The step of executing the first task based on the physical execution plan and obtaining the statistical index values ​​of the data lake table related to the first task includes: The first task is executed based on the physical execution plan, and the statistical values ​​of the data lake table related to the first task are retrieved from the internal table.

3. The method of claim 2, wherein, Before receiving the query request, the method further includes: Obtain the index values ​​of the statistical indicators of the one or more data lake tables; The internal tables are constructed or updated based on the statistical values ​​of the one or more data lake tables.

4. The method according to claim 1, characterized in that, The process of generating a physical execution plan based on the SQL statement includes: Identify the second information in the SQL statement, the second information including a first aggregation operator, or including a partition field and a first aggregation operator, wherein the first aggregation operator is an aggregation operator that meets the aggregation pushdown requirements; Based on the second information, the first information is determined; Based on the first information and the structured object corresponding to the SQL statement, the physical execution plan is generated.

5. The method according to claim 2, characterized in that, The metadata of the internal tables includes one or more of the following: The statistical values ​​of data lake tables; The name of the data lake table; Data lake table types; Database name; Data aggregation type; Update time; Data source.

6. The method according to any one of claims 1 to 5, characterized in that, Before generating the physical execution plan based on the SQL statement, the method further includes: Based on the third information, the SQL statement is optimized to obtain an optimized SQL statement; wherein the third information indicates one or more of the following: Remove SQL clauses that affect query efficiency; remove invalid or redundant expressions from SQL statements; rewrite SQL statements that include set characters to characters that affect query efficiency. The process of generating a physical execution plan based on the SQL statement includes: Generate a physical execution plan based on the optimized SQL statement.

7. A data query apparatus, characterized by comprising: The device includes: A receiving unit is configured to receive a query request, wherein the query request carries a query-related Structured Query Language (SQL) statement. A generation unit is used to generate a physical execution plan based on the SQL statement; wherein the physical execution plan includes first information of a first task, the first task being a non-distributed task, and the first information indicating statistical indicators of the data lake table related to the first task; The processing unit is configured to execute the first task based on the physical execution plan, obtain the index values ​​of the statistical indicators of the data lake table related to the first task, and output the query results based on the index values ​​of the statistical indicators of the data lake table related to the first task.

8. An electronic device, characterized in that, This includes a processor and memory for storing computer programs that can run on the processor. When the processor is used to run the computer program, it performs the steps of the method according to any one of claims 1 to 6.

9. A storage medium having stored thereon a computer program or executable instructions, characterized in that, When the computer program or executable instructions are executed by a processor, they implement the steps of the method according to any one of claims 1 to 6.

10. A computer program product comprising a computer program or executable instructions, characterized in that, When the computer program or executable instructions are executed by a processor, they implement the steps of the method according to any one of claims 1 to 6.

Images