An optimization method and apparatus for large-scale data aggregation queries based on data sources

Abstract

This invention discloses an optimization method for large-scale data aggregation queries based on data sources. The method includes: during data writing, Spark performs pre-aggregation on the data according to a defined pre-aggregation expression; the generated pre-aggregated data and detailed data are written to the storage medium simultaneously within the same transaction; by manually executing a custom pre-aggregation trigger command, the existing detailed data is pre-aggregated according to the pre-aggregation expression defined by the dedicated command, generating pre-aggregated data; during data querying, the query statement is parsed, and the optimal pre-aggregation expression is matched from the defined pre-aggregation expressions for data querying. This method and apparatus, based on data sources, can significantly improve performance and reduce CPU and memory consumption.

Description

Technical Field

[0001] This invention relates to the field of data source query statistical analysis, and in particular to an optimization method and apparatus for large-scale aggregated queries based on data sources. Background Technology

[0002] When using Spark to read data from a massive file directory for query and statistical analysis, it is necessary to read all the files and perform data filtering or aggregation operations, which consumes a lot of CPU and memory and results in poor performance.

[0003] To avoid directly querying and analyzing detailed data, the standard data processing workflow involves performing a light summary of the data and storing it in a new directory, such as... Figure 1 As shown. Aggregate query requests perform calculations on lightly summarized data, but this method has the following problems:

[0004] (1) The consistency of data cannot be guaranteed when the detailed data is summarized twice. If the detailed data changes, it needs to be summarized again.

[0005] (2) Regularly scheduling the aggregated programs generates additional scheduling overhead and maintenance costs.

[0006] (3) The query statement must be modified to query the data after light summary. The field names may also change.

[0007] (4) When querying data after a light summary of detailed data, the real-time performance of the data cannot be guaranteed, as the data after a light summary is usually lagging.

[0008] (5) To add a new lightly aggregated model, it is necessary to add a new schedule and generate new lightly aggregated model data. Summary of the Invention

[0009] To address the aforementioned problems in existing technologies, this invention provides an optimized method and apparatus for large-scale aggregated queries based on data sources. Aggregated queries based on data sources can significantly improve performance and reduce CPU and memory consumption.

[0010] To achieve the above objectives, the present invention adopts the following technical solution:

[0011] In one embodiment of the present invention, an optimization method for large-scale data aggregation queries based on data sources is proposed, the method comprising:

[0012] When Spark writes data, it pre-aggregates the data according to the defined pre-aggregation expression. The generated pre-aggregated data and detailed data are written to the storage medium in the same transaction.

[0013] By manually executing a custom pre-aggregation trigger command, pre-aggregation data is generated by pre-aggregating the existing detailed data according to the pre-aggregation expression defined by the dedicated command.

[0014] When querying data, the query statement is parsed, and the optimal pre-aggregation expression is matched from the defined pre-aggregation expressions to perform the data query.

[0015] Furthermore, during data querying, the query statement is parsed, and the optimal pre-aggregation expression is matched from the defined pre-aggregation expressions to perform data querying, including:

[0016] When querying data, read the pre-aggregated expression file in the detailed data directory;

[0017] Parse the query statement and match the pre-aggregation expressions from the pre-aggregation expression file;

[0018] If a pre-aggregation expression is matched, the query statement retrieves data from the pre-aggregation data subdirectory corresponding to that pre-aggregation expression;

[0019] If multiple pre-aggregation expressions are matched, the query statement selects the pre-aggregation expression with the smallest number of specified pre-aggregation columns from the multiple pre-aggregation expressions, and then queries the data from the pre-aggregation data subdirectory corresponding to that pre-aggregation expression.

[0020] If no pre-aggregation expression is matched, data is retrieved from the detail data subdirectory.

[0021] Furthermore, when writing data, Spark generates a dedicated subdirectory in the directory where detailed data is stored, and creates a pre-aggregation expression file in this subdirectory, storing at least one pre-aggregation expression specified when the data source table was created.

[0022] Furthermore, when writing data, Spark reads the pre-aggregation expression file in the detail data directory to determine the pre-aggregation expression. Based on the pre-aggregation expression, it performs pre-aggregation on the current detail data to generate pre-aggregated data. Each time data is written, a new subdirectory is generated in the detail data directory to store the current detail data. At the same time, a subdirectory named after the pre-aggregation name is generated in the new subdirectory to store the pre-aggregated data.

[0023] Furthermore, when writing data, Spark periodically merges multiple detailed data subdirectories of the same partition. After merging, the last status field of the detailed data subdirectory of the original partition is marked as 0, and the last status field of the detailed data subdirectory of the newly generated partition is marked as 1.

[0024] In one embodiment of the present invention, an optimization device for large-scale data aggregation queries based on data sources is also proposed, the device comprising:

[0025] The write data pre-aggregation module is used by Spark to pre-aggregate data according to the defined pre-aggregation expression when writing data. The generated pre-aggregated data and detailed data are written to the storage medium in the same transaction.

[0026] The existing detailed data pre-aggregation module is used to pre-aggregate existing detailed data and generate pre-aggregated data by manually executing a custom pre-aggregation trigger command based on the pre-aggregation expression defined by the dedicated command.

[0027] The data optimization query module is used to parse query statements during data queries and match the optimal pre-aggregation expression from the defined pre-aggregation expressions for data querying.

[0028] Furthermore, the data optimization query module is specifically used for:

[0029] When querying data, read the pre-aggregated expression file in the detailed data directory;

[0030] Parse the query statement and match the pre-aggregation expressions from the pre-aggregation expression file;

[0031] If a pre-aggregation expression is matched, the query statement retrieves data from the pre-aggregation data subdirectory corresponding to that pre-aggregation expression;

[0032] If multiple pre-aggregation expressions are matched, the query statement selects the pre-aggregation expression with the smallest number of specified pre-aggregation columns from the multiple pre-aggregation expressions, and then queries the data from the pre-aggregation data subdirectory corresponding to that pre-aggregation expression.

[0033] If no pre-aggregation expression is matched, data is retrieved from the detail data subdirectory.

[0034] Furthermore, when writing data, Spark generates a dedicated subdirectory in the directory where detailed data is stored, and creates a pre-aggregation expression file in this subdirectory, storing at least one pre-aggregation expression specified when the data source table was created.

[0035] Furthermore, when writing data, Spark reads the pre-aggregation expression file in the detail data directory to determine the pre-aggregation expression. Based on the pre-aggregation expression, it performs pre-aggregation on the current detail data to generate pre-aggregated data. Each time data is written, a new subdirectory is generated in the detail data directory to store the current detail data. At the same time, a subdirectory named after the pre-aggregation name is generated in the new subdirectory to store the pre-aggregated data.

[0036] Furthermore, when writing data, Spark periodically merges multiple detailed data subdirectories of the same partition. After merging, the last status field of the detailed data subdirectory of the original partition is marked as 0, and the last status field of the detailed data subdirectory of the newly generated partition is marked as 1.

[0037] In one embodiment of the present invention, a computer device is also proposed, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, it implements the aforementioned optimization of large-scale aggregated query based on data source.

[0038] In one embodiment of the present invention, a computer-readable storage medium is also proposed, which stores an optimized computer program for performing large-scale aggregate queries based on data sources.

[0039] Beneficial effects:

[0040] 1. This invention can ensure the consistency between detailed data and pre-aggregated data.

[0041] 2. This invention performs aggregate queries based on data sources. When a pre-aggregated expression is hit, the query can greatly improve performance and reduce CPU and memory consumption.

[0042] 3. The query statement of this invention does not need to be rewritten and can still be queried through the table of detailed data.

[0043] 4. The detailed data and pre-aggregated data of this invention are generated simultaneously, ensuring real-time performance.

[0044] 5. When adding new aggregation requirements, this invention only requires modifying the pre-aggregation expression. Attached Figure Description

[0045] Figure 1 This is a schematic diagram of a typical data processing workflow;

[0046] Figure 2 This invention provides an optimized process diagram for large-scale aggregated queries based on data sources.

[0047] Figure 3 This is a schematic diagram of the data writing process of the present invention;

[0048] Figure 4 This is a schematic diagram of the data query process of the present invention;

[0049] Figure 5 This is a schematic diagram of the optimized device structure for large-scale data aggregation query based on data source according to the present invention;

[0050] Figure 6 This is a schematic diagram of the computer device structure of the present invention. Detailed Implementation

[0051] The principles and spirit of the present invention will now be described with reference to several exemplary embodiments. It should be understood that these embodiments are provided merely to enable those skilled in the art to better understand and implement the present invention, and are not intended to limit the scope of the present invention in any way. Rather, these embodiments are provided to make this disclosure more thorough and complete, and to fully convey the scope of this disclosure to those skilled in the art.

[0052] Those skilled in the art will recognize that embodiments of the present invention can be implemented as an apparatus, device, device, method, or computer program product. Therefore, this disclosure can be specifically implemented in the following forms: entirely hardware, entirely software (including firmware, resident software, microcode, etc.), or a combination of hardware and software.

[0053] According to an embodiment of the present invention, an optimized method and apparatus for large-scale data aggregation queries based on data sources are proposed. When creating a table using Spark SQL, the data source name and one or more pre-aggregation expressions are specified. The written data is automatically pre-aggregated based on the pre-aggregation expressions. For existing detailed data, a dedicated command is used to add pre-aggregation expressions. Historical detailed data is not pre-aggregated and requires manual pre-aggregation to trigger the generation of pre-aggregated data.

[0054] The principles and spirit of the present invention will be explained in detail below with reference to several representative embodiments.

[0055] Figure 2 This is a schematic diagram of the optimized method for large-scale data aggregation query based on data sources according to the present invention. For example... Figure 2 As shown, the method includes:

[0056] S1, such as Figure 3 As shown, when Spark writes data, it pre-aggregates the data according to the defined pre-aggregation expression. The generated pre-aggregated data and detailed data are in the same transaction and are written to the storage medium at the same time.

[0057] Detailed data typically comes directly from the data source and has not undergone pre-aggregation. For example, an order table might include fields such as order number, province, city, and amount. If the data is aggregated based on the province and the total amount for each province is calculated, the resulting data would contain two fields: province and amount. In this case, the order table would be considered detailed data, while the aggregated data would not.

[0058] Data processing generally involves altering the raw data, such as deleting abnormal data, converting time formats, and completing strings. The processed data is then written into a new table, which contains the detailed data.

[0059] S2. By manually executing a custom pre-aggregation trigger command, pre-aggregate the existing detailed data according to the pre-aggregation expression defined by the dedicated command, and generate pre-aggregated data.

[0060] Before defining a pre-aggregation expression, processing the original data will only generate detailed data, not pre-aggregated data. This is because the defined pre-aggregation expression only applies to newly written data. To aggregate existing detailed data, a custom command must be executed manually to generate pre-aggregated data based on the pre-aggregation expression defined in the dedicated command.

[0061] For existing detailed data (in storage media), pre-aggregation expressions can be defined using dedicated commands, for example:

[0062]

[0063] At this point, pre-aggregation of existing detailed data will not be triggered. Pre-aggregation of existing detailed data can be achieved by executing a command that triggers pre-aggregation, such as: `ALTER TABLE table_name REBUILD PREAGG`, where uppercase words are keywords and lowercase `table_name` is the specific table name. Based on the defined pre-aggregation expression, the detailed data is pre-aggregated to generate pre-aggregated data.

[0064] S3, such as Figure 4 As shown, during data querying, the query statement is parsed, and the optimal pre-aggregation expression is matched from the defined pre-aggregation expressions to perform data querying without rewriting the query statement;

[0065] The rules for matching query statements with pre-aggregation expressions are as follows:

[0066] (1) The query statement must be a pre-aggregated type statement;

[0067] (2) The SELECT column of the query statement must be a subset of the pre-aggregated column expression specified in the pre-aggregated expression;

[0068] (3) The aggregate function expression of the query statement must be a subset of the aggregate function expression specified in the pre-aggregate expression;

[0069] If multiple pre-aggregation expressions are matched, the one with the smallest amount of data is selected, i.e., the pre-aggregation expression with the smallest number of columns specified for pre-aggregation. For example, there are two defined pre-aggregation expressions: expression 1 with GROUP_BY = "prov,city,area" and expression 2 with GROUP_BY = "prov,city". When executing the query statement (pre-aggregating based on prov): "select prov,sum(flow)from table_name group by prov", this query statement matches both expression 1 and expression 2. However, expression 2 has the smallest number of columns specified for pre-aggregation in GROUP_BY, so expression 2 is selected for pre-aggregation.

[0070] If no pre-aggregation expression is matched, then a query for detailed data is performed.

[0071] When Spark writes data, it periodically merges pre-aggregated data and further aggregates the pre-aggregated data to avoid scanning a large number of small files during queries.

[0072] It should be noted that although the operation of the method of the present invention has been described in a specific order in the above embodiments and figures, this does not require or imply that the operations must be performed in that specific order, or that all the operations shown must be performed to achieve the desired result. Additionally or alternatively, certain steps may be omitted, multiple steps may be combined into one step, and / or one step may be broken down into multiple steps.

[0073] To provide a clearer explanation of the optimization of large-scale aggregate queries based on data sources, a specific embodiment will be used for illustration below. However, it is worth noting that this embodiment is only for better illustrating the present invention and does not constitute an improper limitation of the present invention.

[0074] The specific implementation steps are as follows:

[0075] 1. Data source table creation

[0076] When creating the table, specify the name of the data source and the pre-aggregation expression, as shown in the example below:

[0077]

[0078] Here, the data source name is specified as AGG via USING, and zero or more pre-aggregation expressions are specified via PREE_AGG in TBLPROPERTIES.

[0079] The LOCATION parameter specifies the directory where the table's data is stored as / data / table_name.

[0080] 2. Define the pre-aggregation expression

[0081] The pre-aggregate expression specifies three parts: the name of the pre-aggregate, the column expression of the pre-aggregate, and the expression of the aggregate function.

[0082] The syntax for pre-aggregation expressions is defined as follows:

[0083]

[0084] PRE_AGG indicates that a pre-aggregation expression will be defined next, and zero or more pre-aggregation expressions can be specified at the same time.

[0085] NAME is used to specify the name of the pre-aggregation, which is globally unique.

[0086] GROUP_BY is used to specify the column expression for pre-aggregation.<group keys expr> This is the column expression used for aggregation.

[0087] METRIC is used to specify the expression for aggregate functions.<metric expr> It is an aggregate function expression.

[0088] Example as follows:

[0089]

[0090]

[0091] The statement above defines the logic for pre-aggregation, named pre_agg_city. It aggregates based on the two fields of province and city, and calculates the mean and sum of the flow.

[0092] The converted SQL query statement is as follows:

[0093] select province,city,avg(flow),sum(flow)

[0094] from xx

[0095] Grouped by province and city;

[0096] When writing data, the pre-aggregation expressions specified during table creation are simultaneously written to a file. Within the directory where detailed data is stored, a dedicated subdirectory contains a pre-aggregation expression file that stores all specified pre-aggregation expressions. The purpose of storing pre-aggregation expressions in a single file is to allow specifying either the table name or the corresponding detailed data directory during data writing. When writing data by table name, the pre-aggregation expression for that table can be retrieved. When writing data through the detailed data directory, the defined pre-aggregation expressions must be read from the pre-aggregation expression file.

[0097] For example, in the data directory below, the subdirectories data1, data2, and data3 are used to store data, while in the _pre_agg subdirectory, a file is specified to store pre-aggregation expressions.

[0098]

[0099] The example above defines two pre-aggregation expressions.

[0100] There are two ways to obtain the pre-aggregation expression: it can be obtained through the table creation statement, or it can be obtained through the pre-aggregation expression file in the subdirectory under the detailed data directory.

[0101] 3. Data writing

[0102] (1) Definition of data source for data writing

[0103] Create a new data source class that inherits from the abstract classes `reatableRelationProvider` and `DataSourceRegister`, and implement the relevant methods as follows:

[0104]

[0105]

[0106] In the code of the class above, two methods are overridden: createRelation and shortName.

[0107] `createRelation` is used to implement the logic for writing data, and `shortName` is the name of the data source.

[0108] The createRelation method has four parameters, which are explained below:

[0109] (a)sqlContext: The context of SparkSQL;

[0110] (b)mode: The writing mode, including append and overwrite writing methods.

[0111] (c) parameters: This section contains the relevant parameters, including which table to write to. To obtain the table name, use parameters("table_name"). After obtaining the table name, you can retrieve the pre-aggregate expressions defined on that table and the location of the table data.

[0112] (d)df: The data to be written, which is a DataFrame in Spark.

[0113] (2) Write data using the defined data source

[0114] In Spark, you can specify the data source name as agg using the format function and specify the table name to be written to using saveAsTable.

[0115] Here's an example of writing data (specifying the table name):

[0116] / / df represents the data to be written, which is a DataFrame in Spark.

[0117] / / Specify the data writing format as agg, and specify the table name.

[0118] df.write.format("agg").saveAsTable("table_name")

[0119] Here's an example of writing data (by specifying a path):

[0120] / / path is the path where the data is stored

[0121] val path=" / data / log"

[0122] / / df represents the data to be written, which is a DataFrame in Spark.

[0123] / / Specify the data writing format as agg, specify the path

[0124] df.write.format("agg").save(path)

[0125] During data writing, the pre-aggregation expression file in the subdirectory under the detailed data directory is read to determine the pre-aggregation expression. Based on the pre-aggregation expression, the current detailed data is pre-aggregated to generate pre-aggregated data. The detailed data and pre-aggregated data are written to the storage medium simultaneously. Each time the data is written to the storage medium, a new subdirectory is generated, and the current detailed data is written to this new subdirectory.

[0126] Subdirectory rules: hash value of partition field_universal unique identifier_status value.

[0127] (a) Logic for generating the hash value of the partition field

[0128] If the data does not need to be partitioned, generate a hash value for the string "all".

[0129] If the data has only one partition field, generate a hash value for the value of that partition field.

[0130] If there are multiple partition fields, the values ​​of the multiple partition fields are merged together to generate a single hash value.

[0131] (b) Random Numbers

[0132] Each time data is written, a universally unique identifier is generated to ensure that each detailed data write is to a new subdirectory.

[0133] (c) State value

[0134] The default status value is 1, which means that the subdirectory is enabled and data can be queried from this subdirectory.

[0135] If the status value is 0, it means that this subdirectory is abandoned and no data will be queried from this subdirectory.

[0136] Each subdirectory generates another subdirectory, whose name is the name of the pre-aggregated data, and stores the pre-aggregated data.

[0137] When writing data, the current detailed data is written to a new subdirectory, and the pre-aggregated data is written to a subdirectory named after the pre-aggregation name under the new subdirectory.

[0138] The following is an example of a table of contents:

[0139]

[0140] The configuration file agg.conf located in the _pre_agg directory is as follows:

[0141]

[0142]

[0143] The ".parquet" file is the file that stores detailed data.

[0144] The directory above has two subdirectories: f86f5b6c98ea4e00c_2857418_1 and f86f5b6c98ea4e00c_3457476_1.

[0145] Let's take f86f5b6c98ea4e00c_2857418_1 as an example:

[0146] part003.parquet and part004.parquet are detailed data files.

[0147] pre_agg_city and pre_agg_province are subdirectories named after the pre-aggregates and store the pre-aggregated data.

[0148] 4. Data Query

[0149] (1) Define the data source query interface

[0150] Create a new data source class that inherits from the abstract classes RelationProvider and DataSourceRegister, and implement the relevant methods as follows:

[0151]

[0152]

[0153] In the code of the class above, two methods are overridden: createRelation and shortName.

[0154] `createRelation` is used to implement the data query logic, and `shortName` is the name of the data source.

[0155] The createRelation method has two parameters, which are explained below:

[0156] (a)sqlContext: The context of SparkSQL.

[0157] (b) parameters: Parameters related to data query. You can obtain the table or data directory to query through this parameter. To get the table name, use parameters("table_name"). After obtaining the table name, you can retrieve the pre-aggregate expressions defined on that table and the location of the table data storage.

[0158] To obtain the data directory for the query, use parameters("path"). After obtaining the data path, you can retrieve the pre-aggregation expressions defined on that path.

[0159] (2) Query parsing interception

[0160] Extends the syntax of Spark SQL, parses Spark SQL SQL, generates new logical and physical plans based on pre-aggregated expressions, ensures that the pre-aggregated expression is hit, and reads data from subdirectories of the pre-aggregated data.

[0161] The specific implementation is as follows:

[0162] (a) Create a new SQL extension class that inherits from the SparkSessionExtensions class.

[0163] (b) Override the injectParser method of the SparkSessionExtensions class.

[0164] (c) `injectParser` is used to intercept user query SQL statements. If the table queried by the SQL statement is a pre-aggregated table, the SQL statement is parsed to determine whether it hits (matches) the pre-aggregated expression. For query SQL statements that hit the pre-aggregated expression, the data source read by the query SQL statement is changed to read data from a subdirectory of the pre-aggregated data.

[0165] (3) Query data

[0166] In Spark, data is read by specifying the name of the data source in the format through the data source interface.

[0167] The read data is registered in a table, and then a query SQL statement is run based on this table.

[0168] Here is an example of querying data:

[0169] / / Path to data storage

[0170] val dataPath=" / data / log"

[0171] / / Specify the data format as agg, specify the storage path

[0172] / / df represents the retrieved data, which is a DataFrame in Spark. val df = spark.read.format("agg").save(dataPath)

[0173] / / Register the read data as a table named log.

[0174] df.createOrReplaceTempView("log")

[0175] With the table, you can then write SQL statements.

[0176] / / For example:

[0177] sql="select province,sum(flow)from log group by province"

[0178] When performing data queries, the pre-aggregation expression file in the detailed data directory is read first, the query SQL statement is parsed, and the optimal pre-aggregation expression is matched from the pre-aggregation expression file.

[0179] If a pre-aggregation expression is matched, the query statement retrieves data from the pre-aggregation data subdirectory corresponding to that pre-aggregation expression, thus avoiding aggregation from a large amount of detailed data.

[0180] If multiple pre-aggregation expressions are matched, the query statement selects the pre-aggregation expression with the smallest number of columns specified by GROUP_BY from among the multiple pre-aggregation expressions, and then queries the data from the pre-aggregation data subdirectory corresponding to the pre-aggregation expression, thereby avoiding aggregation from a large amount of detailed data.

[0181] If no pre-aggregation expression is matched, data is retrieved from the detail data subdirectory.

[0182] The rules for matching SQL queries with pre-aggregated expressions are as follows:

[0183] (a) The query SQL statement must be a pre-aggregated type statement.

[0184] (b) The select columns of a query SQL statement must be a subset of the column expressions specified by GROUP_BY in the pre-aggregate expression.

[0185] (c) The aggregate function expression of the query SQL statement must be a subset of the aggregate function expression specified by METRIC in the pre-aggregate expression.

[0186] For example, the following pre-aggregation expression:

[0187]

[0188] Example SQL statement 1, which can match pre-aggregated expressions:

[0189] select city,sum(flow)from log group by city;

[0190] Example SQL statement 2, which can match pre-aggregated expressions:

[0191] select province,city,avg(flow),sum(flow)from log group by province,city;

[0192] The SQL query example 3 cannot match the pre-aggregation expression because max(flow) is not in the pre-aggregation expression.

[0193] select city,max(flow)from log group by city;

[0194] When multiple pre-aggregation expressions exist, the query SQL statement selects the optimal pre-aggregation expression. Example:

[0195] The pre-aggregation expression defines two at the same time:

[0196]

[0197]

[0198] For example, the query SQL statement:

[0199] select province,sum(flow)from log group by province;

[0200] The SQL query above satisfies both defined pre-aggregate expressions. However, according to the principle of optimality, the pre-aggregate expression named `pre_agg_province` is better because it specifies only one column using `group_by`, while the pre-aggregate expression named `pre_agg_city` specifies two columns using `group_by`. Therefore, the pre-aggregate expression named `pre_agg_province` performs aggregation based on the province, resulting in less data.

[0201] 5. Pre-aggregation of detailed data on existing inventory

[0202] Before defining a pre-aggregation expression, processing the original data will only generate detailed data, not pre-aggregated data. This is because the pre-aggregation expression only applies to newly written data.

[0203] If you need to aggregate existing detailed data, you can manually execute a custom SQL command to generate pre-aggregated data based on the pre-aggregation expression defined by the dedicated command.

[0204] For existing detailed data, pre-aggregation expressions can be defined using dedicated commands, for example:

[0205]

[0206] At this point, pre-aggregation of existing detailed data will not be triggered. Pre-aggregation of existing detailed data can be achieved by executing a pre-aggregation triggered SQL command, for example: `ALTER TABLE table_name REBUILD PRE_AGG`, where uppercase words are keywords and lowercase `table_name` is the specific table name. Based on the defined pre-aggregation expression, pre-aggregation of existing detailed data is performed, generating pre-aggregated data.

[0207] When Spark performs data writing, it scans the detailed data subdirectories to see which subdirectories do not have pre-aggregated subdirectories. If a subdirectory does not have a pre-aggregated subdirectory, it performs the pre-aggregation operation to generate pre-aggregated data.

[0208] 6. Further merging of pre-aggregated data

[0209] To avoid generating too many small files in the pre-aggregated subdirectories, which would degrade the performance of reading pre-aggregated data, Spark periodically performs a merge operation on multiple detailed data subdirectories in the same partition when writing data.

[0210] Specify the time interval for merging partition directories, for example, merging directories every 10 minutes.

[0211] The merge time is passed through the parameter "mergeInterval" in the Spark data source. Assuming the parameter name is "mergeInterval" and "mergeInterval=10" is specified, it means that the detailed data subdirectories are scanned every 10 minutes to see if they need to be merged.

[0212] After the merge, the final status field of the detailed data subdirectory of the original partition is marked as 0, and the final status field of the detailed data subdirectory of the newly generated partition is marked as 1.

[0213] The directory before the merge:

[0214]

[0215] Merged directory:

[0216]

[0217]

[0218] For the detailed data subdirectories of partitions whose final status field is marked as 0, the background system periodically cleans up and deletes these subdirectories.

[0219] When reading data, the detailed data subdirectories of partitions whose last status field is marked as 0 will not be read.

[0220] Based on the same inventive concept, this invention also proposes an optimization device for large-scale data aggregation queries based on data sources. The implementation of this device can refer to the implementation of the methods described above, and repeated details will not be repeated. The term "module" used below can refer to a combination of software and / or hardware that implements a predetermined function. Although the device described in the following embodiments is preferably implemented in software, hardware implementation, or a combination of software and hardware, is also possible and contemplated.

[0221] Figure 5 This is a schematic diagram of the optimized device structure for large-scale data aggregation and querying based on data sources according to the present invention. (See diagram for example.) Figure 5 As shown, the device includes:

[0222] The data pre-aggregation module 101 is used by Spark to pre-aggregate data according to the defined pre-aggregation expression when writing data. The generated pre-aggregated data and detailed data are written to the storage medium in the same transaction.

[0223] When Spark writes data, it generates a dedicated subdirectory in the directory where the detailed data is stored, and creates a pre-aggregation expression file in the subdirectory, which stores at least one pre-aggregation expression specified when the data source table is created.

[0224] When Spark writes data, it reads the pre-aggregation expression file in the detail data directory, determines the pre-aggregation expression, and performs pre-aggregation on the current detail data according to the pre-aggregation expression to generate pre-aggregated data. Each time data is written, a new subdirectory is generated in the detail data directory to store the current detail data, and a subdirectory named after the pre-aggregation name is generated in the new subdirectory to store the pre-aggregated data.

[0225] When writing data, Spark periodically merges multiple detailed data subdirectories of the same partition. After merging, the last status field of the detailed data subdirectory of the original partition is marked as 0, and the last status field of the detailed data subdirectory of the newly generated partition is marked as 1.

[0226] The existing detailed data pre-aggregation module 102 is used to pre-aggregate the existing detailed data according to the pre-aggregation expression defined by the dedicated command by manually executing a custom pre-aggregation trigger command, and generate pre-aggregated data.

[0227] The data optimization query module 103 is used to parse the query statement during data querying and match the optimal pre-aggregation expression from the defined pre-aggregation expressions for data querying; specifically as follows:

[0228] When querying data, read the pre-aggregated expression file in the detailed data directory;

[0229] Parse the query statement and match the pre-aggregation expressions from the pre-aggregation expression file;

[0230] If a pre-aggregation expression is matched, the query statement retrieves data from the pre-aggregation data subdirectory corresponding to that pre-aggregation expression;

[0231] If multiple pre-aggregation expressions are matched, the query statement selects the pre-aggregation expression with the smallest number of specified pre-aggregation columns from the multiple pre-aggregation expressions, and then queries the data from the pre-aggregation data subdirectory corresponding to that pre-aggregation expression.

[0232] If no pre-aggregation expression is matched, data is retrieved from the detail data subdirectory.

[0233] It should be noted that although several modules of the optimization device for large-scale data aggregation queries based on data sources have been mentioned in the detailed description above, this division is merely exemplary and not mandatory. In fact, according to embodiments of the present invention, the features and functions of two or more modules described above can be embodied in one module. Conversely, the features and functions of one module described above can be further divided and embodied by multiple modules.

[0234] Based on the aforementioned inventive concept, such as Figure 6 As shown, the present invention also proposes a computer device 200, including a memory 210, a processor 220, and a computer program 230 stored in the memory 210 and executable on the processor 220. When the processor 220 executes the computer program 230, it realizes the aforementioned optimization of large data aggregation query based on data source.

[0235] Based on the aforementioned inventive concept, the present invention also proposes a computer-readable storage medium storing an optimized computer program that performs the aforementioned large-scale aggregate query based on the data source.

[0236] The proposed optimization method for large-scale data aggregation queries based on data sources ensures consistency between detailed data and pre-aggregated data. Aggregation queries based on data sources, when matching pre-aggregation expressions, significantly improve performance and reduce CPU and memory consumption. Query statements do not require rewriting and can still be queried using detailed data tables. Detailed data and pre-aggregated data are generated simultaneously, ensuring real-time performance. When new aggregation requirements are added, only the pre-aggregation expression needs to be modified.

[0237] While the spirit and principles of the invention have been described with reference to several specific embodiments, it should be understood that the invention is not limited to the disclosed specific embodiments, and the division of aspects does not imply that features in these aspects cannot be combined for benefit; such division is merely for ease of description. The invention is intended to cover various modifications and equivalent arrangements included within the spirit and scope of the appended claims.

[0238] Regarding the limitation of the scope of protection of this invention, those skilled in the art should understand that various modifications or variations that can be made by those skilled in the art without creative effort based on the technical solution of this invention are still within the scope of protection of this invention.

Claims

1. An optimization method for large-scale aggregated queries based on data sources, characterized in that, The method includes: When Spark writes data, it pre-aggregates the data according to the defined pre-aggregation expression. The generated pre-aggregated data and detailed data are written to the storage medium in the same transaction. By manually executing a custom pre-aggregation trigger command, pre-aggregation data is generated by pre-aggregating the existing detailed data according to the pre-aggregation expression defined by the dedicated command. When querying data, read the pre-aggregated expression file in the detailed data directory; Parse the query statement and match the pre-aggregation expressions from the pre-aggregation expression file; If a pre-aggregation expression is matched, the query statement retrieves data from the pre-aggregation data subdirectory corresponding to that pre-aggregation expression; If multiple pre-aggregation expressions are matched, the query statement selects the pre-aggregation expression with the smallest number of specified pre-aggregation columns from the multiple pre-aggregation expressions, and then queries the data from the pre-aggregation data subdirectory corresponding to that pre-aggregation expression. If no pre-aggregation expression is matched, data is retrieved from the detail data subdirectory.

2. The optimization method for large-scale data aggregation query based on data source according to claim 1, characterized in that, When Spark writes data, it generates a dedicated subdirectory in the directory where the detailed data is stored, and creates a pre-aggregation expression file in the subdirectory, which stores at least one pre-aggregation expression specified when the data source table was created.

3. The optimization method for large-scale data aggregation query based on data source according to claim 2, characterized in that, When writing data, Spark reads the pre-aggregation expression file in the detail data directory, determines the pre-aggregation expression, and performs pre-aggregation on the current detail data according to the pre-aggregation expression to generate pre-aggregated data. Each time data is written, a new subdirectory is generated under the detailed data directory to store the current detailed data. At the same time, a subdirectory named after the pre-aggregation name is generated under the new subdirectory to store the pre-aggregated data.

4. The optimization method for large-scale data aggregation query based on data source according to claim 3, characterized in that, When writing data, Spark periodically merges multiple detailed data subdirectories of the same partition. After merging, the last status field of the detailed data subdirectory of the original partition is marked as 0, and the last status field of the detailed data subdirectory of the newly generated partition is marked as 1.

5. An optimization device for large-scale data aggregation queries based on data sources, characterized in that, The device includes: The write data pre-aggregation module is used by Spark to pre-aggregate data according to the defined pre-aggregation expression when writing data. The generated pre-aggregated data and detailed data are written to the storage medium in the same transaction. The existing detailed data pre-aggregation module is used to pre-aggregate existing detailed data and generate pre-aggregated data by manually executing a custom pre-aggregation trigger command based on the pre-aggregation expression defined by the dedicated command. The data optimization query module is used to read the pre-aggregation expression file in the detailed data directory when querying data; parse the query statement and match pre-aggregation expressions from the pre-aggregation expression file; if a pre-aggregation expression is matched, the query statement retrieves data from the pre-aggregation data subdirectory corresponding to that pre-aggregation expression; if multiple pre-aggregation expressions are matched, the query statement selects the pre-aggregation expression with the smallest number of specified pre-aggregated columns from the multiple pre-aggregation expressions, and then retrieves data from the pre-aggregation data subdirectory corresponding to that pre-aggregation expression; if no pre-aggregation expression is matched, data is retrieved from the detailed data subdirectory.

6. The optimization device for large-scale data aggregation query based on data source according to claim 5, characterized in that, When Spark writes data, it generates a dedicated subdirectory in the directory where the detailed data is stored, and creates a pre-aggregation expression file in the subdirectory, which stores at least one pre-aggregation expression specified when the data source table was created.

7. The optimization device for large-scale data aggregation query based on data source according to claim 6, characterized in that, When writing data, Spark reads the pre-aggregation expression file in the detail data directory, determines the pre-aggregation expression, and performs pre-aggregation on the current detail data according to the pre-aggregation expression to generate pre-aggregated data. Each time data is written, a new subdirectory is generated under the detailed data directory to store the current detailed data. At the same time, a subdirectory named after the pre-aggregation name is generated under the new subdirectory to store the pre-aggregated data.

8. The optimization device for large-scale data aggregation query based on data source according to claim 7, characterized in that, When writing data, Spark periodically merges multiple detailed data subdirectories of the same partition. After merging, the last status field of the detailed data subdirectory of the original partition is marked as 0, and the last status field of the detailed data subdirectory of the newly generated partition is marked as 1.

9. A computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the method according to any one of claims 1-4.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program that performs the method according to any one of claims 1-4.

Images