Data updating method, data querying method, device, and storage medium

Abstract

Embodiments of the present application provide a data updating method, a data querying method, a device and a storage medium. The method comprises the following steps: receiving a write request for a database table; and updating the database table and a first index thereof according to the write request and a preset aggregation function, wherein the first index is used to store a pre-aggregation result of pre-aggregating records of the database table by using the aggregation function. The detection efficiency of the detection scheme provided by the embodiments of the present application is high. In the technical scheme provided by the embodiments of the present application, only an index for storing the pre-aggregation result of the database table needs to be created for the database table, and real-time consistency between the database table and the pre-aggregation result thereof can be realized. Compared with the prior art, the scheme is simple and easy to implement.

Description

Technical Field

[0001] This application relates to the field of computer technology, and in particular to a data update method, a data query method, an apparatus, and a storage medium. Background Technology

[0002] When performing OLAP (Online Analytical Processing) queries, users often need to perform aggregate analysis on certain dimensions. Aggregate queries on large-scale detailed tables can take tens or even hundreds of seconds to obtain results, resulting in low query efficiency. To address this inefficiency, a solution based on materialized views has been proposed. This involves materializing and storing the pre-aggregated results (or pre-calculated results) into a materialized view table. Subsequent aggregate queries can then directly retrieve the pre-aggregated results from the materialized view table, thus improving query efficiency.

[0003] In a materialized view-based solution, a materialized view table (referred to as a materialized view) must first be created for the source table. This materialized view table is a separate database table from the source table. Then, a materialized view log is created for the materialized view table. After the source table is updated, the changes are written to the materialized view log. Based on the refresh mechanism specified when creating the materialized view, the changes recorded in the materialized view log are then refreshed to one or more corresponding materialized view tables of the source table. After a successful refresh of a materialized view table, the records of that successfully refreshed materialized view table and other records not needed by other materialized views are deleted from the materialized view log. To ensure data consistency between the materialized view table and the source table, the source table needs to be locked when refreshing the materialized view table to prevent DML (Data Manipulation Language) operations on the source table.

[0004] It is evident that existing materialized view-based solutions are complex and challenging. Summary of the Invention

[0005] In view of the above problems, this application is made to provide a data update method, data query method, device and storage medium that solves or at least partially solves the above problems.

[0006] Therefore, in one embodiment of this application, a data update method is provided. The method includes:

[0007] Receive write requests for database tables;

[0008] Based on the write request and the preset aggregation function, update the database table and its first index; wherein, the first index is used to store the pre-aggregation result of pre-aggregating the records of the database table using the aggregation function.

[0009] In another embodiment of this application, a data query method is provided. The method includes:

[0010] Determine a query statement targeting a first index of the database table; wherein the first index is used to store pre-aggregation results of pre-aggregating records of the database table using aggregate functions; the first index includes: a memory table and a disk file;

[0011] Based on the query statement, the memory table and the disk file are queried respectively to obtain the first pre-aggregation result and the second pre-aggregation result;

[0012] Based on the aggregation functions corresponding to the first pre-aggregation result and the second pre-aggregation result, the first pre-aggregation result and the second pre-aggregation result are aggregated to obtain the aggregation result;

[0013] The aggregation result is determined as the query result of the query statement.

[0014] In another embodiment of this application, an electronic device is provided. The electronic device includes: a memory and a processor, wherein,

[0015] The memory is used to store programs;

[0016] The processor, coupled to the memory, is configured to execute the program stored in the memory to implement the method described in any of the preceding embodiments.

[0017] In another embodiment of this application, a computer-readable storage medium storing a computer program is provided, which, when executed by a computer, can implement the methods described in any of the above-described embodiments.

[0018] In database systems, updates to database tables and their indexes are synchronized and consistent. Leveraging this characteristic, the technical solution provided in this application does not create a materialized view table. Instead, it creates a first index for the database table, storing the pre-aggregated results for that database table. In this way, as an index of the database table, the pre-aggregated results contained in the first index are atomically related to the database table updates, maintaining synchronization and consistency. This ensures that user queries on the database table and the first index always return to real-time consistency. Therefore, the technical solution provided in this application only requires creating an index for storing the pre-aggregated results of the database table to achieve real-time consistency between the database table and its pre-aggregated results. Compared with existing technologies, this solution is simple and easy to implement. Attached Figure Description

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

[0020] Figure 1 A schematic flowchart illustrating a data update method provided in an embodiment of this application;

[0021] Figure 2 A schematic flowchart illustrating a data query method provided in an embodiment of this application;

[0022] Figure 3 Example diagram of a data query method provided in an embodiment of this application;

[0023] Figure 4 Example diagram of a data update method provided in an embodiment of this application;

[0024] Figure 5 This is a structural block diagram of an electronic device provided in an embodiment of this application. Detailed Implementation

[0025] Existing solutions based on materialized view tables not only suffer from complexity and difficulty in implementation, but also have the following problems:

[0026] 1. Data in the materialized view log needs to be stored, which occupies a certain amount of storage space; in addition, when the source table itself is updated, it affects the performance of DML operations on the source table; when the materialized view table is updated according to the materialized view log later, the source table needs to be locked, which will also affect the performance of DML operations on the source table.

[0027] 2. DML operations on the source table and writes to the materialized view table occur within the same transaction. Therefore, a failure to write to the materialized view table will cause DML operations on the source table to fail, affecting user experience, especially when there are many materialized views on the source table. 3. Materialized views that are not refreshed for a long time will increase the amount of data in the materialized view log, thus affecting the refresh performance of other materialized views.

[0028] 4. The update mechanisms for materialized view tables include: timed refresh, manual refresh, and immediate refresh. Each refresh mechanism has its limitations.

[0029] In the timed refresh mechanism, the materialized view table is not updated in real time; the data in the materialized view table lags behind the data in the source table. This means that querying the materialized view table cannot reflect the latest state of the source table, thus preventing query rewriting. In the manual refresh mechanism, users must manually refresh the materialized view, increasing the user burden. In the immediate refresh mechanism, the materialized view table is refreshed immediately when there is a commit operation on the source table, offering the best real-time performance. However, each refresh requires row locks on the source table, resulting in poor performance for DML operations on the source table.

[0030] In database systems, updates to database tables and their indexes are synchronized and consistent. Leveraging this characteristic, the technical solution provided in this application does not create a materialized view table. Instead, it creates a first index for the database table, storing the pre-aggregated results for that database table. In this way, the pre-aggregated results contained in the first index, as an index of the database table, are synchronized and consistent with the updates to the database table, ensuring that the results of user queries on the database table and the first index are always consistent in real time. Therefore, the technical solution provided in this application only requires creating an index for the database table to store the pre-aggregated results, achieving real-time consistency between the database table and its pre-aggregated results. Compared with existing technologies, this solution is simple and easy to implement.

[0031] To enable those skilled in the art to better understand the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are merely some embodiments of the present application, and not all embodiments. All other embodiments obtained by those skilled in the art based on the embodiments of the present application without creative effort are within the scope of protection of the present application.

[0032] Furthermore, some processes described in the specification, claims, and accompanying drawings of this application include multiple operations that appear in a specific order. These operations may be performed out of order or in parallel. Operation numbers such as 101, 102, etc., are merely used to distinguish different operations and do not represent any execution order. Additionally, these processes may include more or fewer operations, and these operations may be performed sequentially or in parallel. It should be noted that the terms "first," "second," etc., used herein are used to distinguish different messages, devices, modules, etc., and do not represent a sequential order, nor do they limit "first" and "second" to different types.

[0033] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties. Furthermore, the collection, use and processing of the relevant data must comply with the relevant laws, regulations and standards of the relevant countries and regions, and corresponding operation portals are provided for users to choose to authorize or refuse.

[0034] Figure 1 This illustration shows a flowchart of a data processing method provided in an embodiment of this application. The execution entity of this method can be a client or a server. The client can be hardware with embedded programs integrated on a terminal, application software installed on the terminal, or utility software embedded in the terminal's operating system, etc., and this embodiment does not limit its scope. The terminal can be any terminal device, including mobile phones, tablets, and in-vehicle terminal devices. The server can be a conventional server, a cloud server, or a virtual server, etc., and this embodiment does not specifically limit its scope. Figure 1 As shown, the method includes:

[0035] 101. Receive write requests for database tables.

[0036] 102. Update the database table and its first index according to the write request and the preset aggregate function.

[0037] The first index is used to store the pre-aggregation result of pre-aggregating the records of the database table using the aggregation function.

[0038] In point 101 above, the database table can be a complete user table or a fragment of a user table. The user table is used to store data about users.

[0039] In a distributed database system, the data in a user table is distributed across multiple shards. This distributes the data evenly, improves write throughput, and makes it more flexible in horizontal scaling.

[0040] Sharding is a database architecture pattern related to horizontal partitioning: it involves dividing the rows in a table into multiple distinct shards. Each shard has the same schema and columns, but each shard contains completely different rows. Similarly, the data stored in each shard is unique and independent of the data stored in other shards.

[0041] In one instance, the database table mentioned above refers to a shard of the user table.

[0042] The write request mentioned above may include one or more of the following: insert statement, delete statement, and update statement.

[0043] In step 102 above, a first index can be created in advance for the database table. This first index stores the pre-aggregation results obtained by using aggregate functions to pre-aggregate the records in the database table.

[0044] The aggregate functions may include, but are not limited to, aggregation functions such as summation (SUM), counting (COUNT), and average (AVG).

[0045] The aggregate functions described above can perform aggregates on the entire database table or on groups of the database table. When the aggregate function performs grouped aggregates on the database table, it also corresponds to a grouping field, which is a specific field in the database table.

[0046] The grouping field and calculated field of the aggregation function can be determined according to actual needs. The grouping field of the aggregation function corresponds to the grouping basis information of the aggregation function; the calculated field of the aggregation function corresponds to the pre-aggregation result of the aggregation function. The grouping field of the aggregation function is selected from multiple fields of the database table, and there can be one or more grouping fields; this embodiment does not specifically limit this. A first index of the database table is created based on the grouping field and calculated field of the aggregation function. The first index contains the grouping field and calculated field of the aggregation function. Specifically, the calculated field stores the pre-aggregation result of pre-aggregating the records of the database table using the aggregation function; the grouping field stores the grouping basis information used when pre-aggregating the records of the database table using the aggregation function.

[0047] The following examples will be based on Table 1:

[0048] Table 1:

[0049] Product Name Category price Grape fruit 5 carrot vegetable 3 tangerine fruit 4 watermelon fruit 9 Potato vegetable 2

[0050] Table 1 above is a database table containing three fields: product name, category, and price. Suppose we need to pre-aggregate the average price of products within the same category. Then, the aggregation function is an average value function, with the category as the numerator field and the average price as the calculated field. Thus, the first index can contain both the category and average price fields.

[0051] In practical applications, aggregate functions, grouping fields of aggregate functions, and calculated fields can be specified by the user according to actual needs.

[0052] In the database, updating a database table index does not depend on updating the database table itself; both updates can be processed in parallel. That is, based on the write request and the aggregate function, both the database table and the first index can be updated in parallel.

[0053] In database systems, updates to database tables and their indexes are atomic, synchronous, and consistent. Leveraging this characteristic, the technical solution provided in this application does not create a materialized view table. Instead, it creates a first index for the database table, storing the pre-aggregated results for that database table. In this way, the pre-aggregated results contained in the first index, as an index of the database table, remain synchronous and consistent with the updates to the database table, ensuring that user queries on the database table and the first index always yield real-time consistent results. Therefore, the technical solution provided in this application only requires creating an index for the database table to store the pre-aggregated results, achieving real-time consistency between the database table and its pre-aggregated results. Compared to existing technologies, this solution is simple and easy to implement.

[0054] Furthermore, since the source table and its first index are updated in parallel, the performance impact of DML operations on the source table can be reduced.

[0055] In one feasible solution, the step 102 above, "updating the database table and its first index according to the write request and the preset aggregate function," can be implemented using the following steps:

[0056] 1021. Determine the record involved in the write request and the data operation type corresponding to the record.

[0057] 1022. Update the database table according to the record and the data operation type corresponding to the record.

[0058] 1023. Update the first index according to the record, the data operation type corresponding to the record, and the aggregation function.

[0059] In one instance, the aforementioned 1021 may specifically include the following steps:

[0060] S11. When the write request includes an insert statement, the record to be inserted corresponding to the insert statement is taken as the first record involved in the write request.

[0061] S12. Determine the data operation type corresponding to the first record as the insertion type.

[0062] In another example, the aforementioned 1021 may specifically include the following steps:

[0063] S13. When the write request includes a delete statement, the record to be deleted corresponding to the delete statement is taken as the second record involved in the write request.

[0064] S14. Determine the data operation type corresponding to the second record as the deletion type.

[0065] In yet another example, the aforementioned 1021 may specifically include the following steps:

[0066] S15. When the write request includes an update statement, determine the record to be deleted and the record to be inserted corresponding to the update statement.

[0067] S16. The record to be deleted is designated as the third record involved in the write request, and the record to be inserted is designated as the fourth record involved in the write request.

[0068] S17. Determine the data operation type corresponding to the third record as the deletion type, and determine the data operation type corresponding to the fourth record as the insertion type.

[0069] An update statement essentially involves deleting the old record and then inserting the new record. Therefore, the records to be deleted and the records to be inserted can be determined based on the update statement.

[0070] To improve write throughput, the aforementioned database tables and their indexes can all be stored using the LSM (log structured mergeTrees) storage engine. The LSM storage engine maintains incremental data changes in memory. Once the amount of data in memory reaches a certain limit, it is written in batches to persistent files (i.e., disk files). Persistent files are immutable, and the background process merges multiple persistent files into a single file based on certain strategies. The advantage of the LSM storage engine is that it transforms random disk writes into sequential writes, significantly improving write throughput; however, read operations may require accessing multiple data files.

[0071] In the LSM storage engine, a database table consists of its in-memory table and its disk file; the first index also consists of its in-memory table and its disk file.

[0072] In step 1022 above, if the record is of insertion type, the record is inserted into the memory table of the database table and marked as insertion type; if the record is of deletion type, the record is inserted into the database table and marked as deletion type.

[0073] The step 1023 above, "updating the first index based on the record, the data operation type corresponding to the record, and the aggregation function," can be implemented using the following steps:

[0074] S21. Based on the data operation type corresponding to the record, the aggregation function is used to pre-aggregate the record to obtain the pre-aggregation result.

[0075] S22. Write the pre-aggregation result into the memory table of the first index.

[0076] In S21 above, the aggregation function is used to pre-aggregate only this one record.

[0077] In S22 above, the memory table of the first index contains the grouping field and the calculated field of the aggregation function. The step of "writing the pre-aggregation result into the memory table of the first index" in S22 can be implemented using the following steps:

[0078] S221. Determine the target grouping field value of the grouping field from the record.

[0079] S222. Determine the pre-aggregation result as the target calculated field value of the calculated field.

[0080] S223. Insert the target grouping field value and the target calculated field value as a record into the memory table of the first index.

[0081] Following Table 1 above, aggregate functions are used to count the number of product types within the same category. For example, if the record above is <Product Name: Potato; Category: Vegetable; Price: 2>, and the corresponding data operation type is insert, then the pre-aggregation result is: Vegetable, 1. As another example, if the record above is <Product Name: Potato; Category: Vegetable; Price: 2>, and the corresponding data operation type is delete, then the pre-aggregation result is: Vegetable, -1. The "Vegetable" field in the record is used as the target grouping field value; the pre-aggregation result is used as the target calculated field value.

[0082] Continuing with Table 1 above, the aggregation function is used to calculate the average price of goods within the same category. The record above is <Goods Name: Potato; Category: Vegetables; Price: 2>. If the data operation type corresponding to this record is insert, then the pre-aggregation result is: Vegetables, <2,1>, where <2,1> is a binary type, meaning <2,1> refers to the serialized result of 2 and 1. If the data operation type corresponding to this record is delete, then the pre-aggregation result is: Vegetables, <-2,-1>, where <-2,-1> is a binary type, meaning <-2,-1> refers to the serialized result of -2 and -1. The "Vegetables" field in the record is used as the target grouping field value; the pre-aggregation result is used as the target calculated field value.

[0083] In S223 above, the target grouping field value and the target calculated field value are inserted as a record into the memory table of the first index.

[0084] Continuing with Table 1 above, aggregate functions are used to calculate the average price of goods within the same category. The write request includes an update statement that modifies the price of carrots in Table 1 to 4. Therefore, this update statement needs to first delete the record <Carrot, Vegetable, 3> in Table 1, and then insert the record <Carrot, Vegetable, 4> into Table 1. This requires adding two records, <Vegetable, -3 / -1> and <Vegetable, 4 / 1>, to the end of the memory table with the first index, as shown in Table 2.

[0085] Table 2:

[0086] Category average price vegetable 3,1 vegetable 2,1 vegetable -3，-1 vegetable 4，1

[0087] In this embodiment, the memory table of the first index stores incremental data of the pre-aggregation results, which is used for subsequent aggregation calculations. The method may further include:

[0088] 103. When the memory table of the first index meets the disk flushing condition, the aggregation function is used to perform pre-aggregation on the pre-aggregation result in the memory table of the first index to obtain the first target pre-aggregation result.

[0089] 104. Generate the disk file of the first index based on the first target pre-aggregation result.

[0090] In step 103 above, when the amount of data in the memory table of the first index is greater than or equal to a preset threshold, the memory table of the first index is determined to meet the disk flushing condition. The aforementioned amount of data can specifically be the number of records or the size of the space occupied by the memory table.

[0091] Optionally, the step 103 above, "using the aggregation function to perform pre-aggregation on the pre-aggregation results in the memory table of the first index to obtain the first target pre-aggregation result," can be implemented using the following steps:

[0092] 1031. Group the multiple records in the memory table of the first index to obtain at least one group of records.

[0093] Records with the same grouping field value are grouped into the same group.

[0094] 1032. Using the aggregation function, perform pre-aggregation for each group of records to obtain the first target pre-aggregation result for each group of records.

[0095] Using the above Table 2, the aggregation function is used to calculate the average price of goods under the same category. Using the aggregation function, the four records of the same category are aggregated to get 3 / 1. Therefore, the disk file of the first index finally generated includes the record <vegetables, 3 / 1>, as shown in Table 3 below.

[0096] Table 3:

[0097] Category average price vegetable 3 / 1

[0098] When generating the disk file for the first index, you can add the target grouping field value to multiple records on the disk as needed, sort these records, and then add them to the disk file for the first index according to the sorting. Specifically, you can sort the records in the memory table according to the grouping field value as needed, perform aggregation calculations on records in the same group after sorting, and finally add the aggregated result data to the disk file for the first index.

[0099] In the LSM storage engine, a merge operation of multiple disk files in the first index can be triggered periodically, or when the number of multiple disk files in the first index is greater than or equal to a preset number. Therefore, the above method may also include:

[0100] 105. Obtain multiple disk files from the first index.

[0101] 106. Using the aggregation function, pre-aggregate the pre-aggregation results in the multiple disk files to obtain the second target pre-aggregation result.

[0102] 107. Based on the second target pre-aggregation result, generate a new disk file for the first index.

[0103] In step 106 above, multiple records in multiple disk files are grouped to obtain at least one group of records; records with the same grouping field value are grouped into the same group of records; using the aggregation function, pre-aggregation is performed on each group of records to obtain the second target pre-aggregation result corresponding to each group of records.

[0104] In step 107 above, the grouping field value corresponding to each group of records and the second target pre-aggregation result are added as a record to the new disk file of the first index.

[0105] The specific implementation of pre-aggregation of multiple disk files can be referred to as the aggregation of multiple pre-aggregation results in the memory table, and will not be described in detail here.

[0106] In practical applications, to ensure the atomicity and durability of the database, changes to data can be first recorded in a persistent log file, and then written to the database based on this persistent log file. This ensures that data changes can be recovered from the persistent log during failover. This persistent log is called write-ahead logging (WAL). Therefore, step 102 above, "updating the database table and its first index according to the write request and the preset aggregate function," can be implemented using the following steps:

[0107] 1024. Update the write-ahead log according to the write request.

[0108] 1025. Update the database tables based on the updated write-ahead log.

[0109] 1026. Update the first index based on the updated write-ahead log and the aggregation function.

[0110] In step 1024 above, based on the write request, the records involved in the write request and the corresponding data operation types are determined; the records involved in the write request and the corresponding data operation types are written to the write-ahead log. The records involved in the write request and the corresponding data operation types are written to the write-ahead log in the form of a table structure.

[0111] The record involved in the write request may include a record to be deleted or a record to be added; the data operation type corresponding to the record may include: insert or delete.

[0112] In step 1025 above, the database table is updated based on the records involved in the write requests in the updated write-ahead log and the data operation types corresponding to those records. The first index is updated based on the records involved in the write requests in the updated write-ahead log, the data operation types corresponding to those records, and the aggregate function.

[0113] Optionally, the above method may further include:

[0114] 108. Create a clustered index for the database table.

[0115] The clustered index of a database table contains all the data in the table; updating a database table is essentially updating its clustered index.

[0116] The phrase "update the database table and its first index according to the write request and the preset aggregation function" in section 102 above may include: updating the clustered index and the first index of the database table according to the write request and the aggregation function.

[0117] In a database system, updates to different indexes within the same database table are kept synchronized and consistent.

[0118] Figure 2 This illustration shows a flowchart of a data processing method according to another embodiment of this application. The executing entity of this method can be a server. The server can be a conventional server, a cloud server, or a virtual server, etc., and this embodiment of the application does not specifically limit this. Figure 2 As shown, the method includes:

[0119] 201. Determine the query statement for the first index of the database table.

[0120] The first index is used to store the pre-aggregation results of pre-aggregating the records of the database table using aggregation functions; the first index includes: a memory table and a disk file.

[0121] The data stored in the memory table and disk file and their generation process can be found in the corresponding content of the above embodiments, and will not be described in detail here.

[0122] 202. Based on the query statement, query the memory table and the disk file respectively to obtain the first pre-aggregation result and the second pre-aggregation result.

[0123] 203. Based on the aggregation functions corresponding to the first pre-aggregation result and the second pre-aggregation result, aggregate the first pre-aggregation result and the second pre-aggregation result to obtain the aggregation result.

[0124] 204. The aggregation result is determined as the query result of the query statement.

[0125] In step 201 above, the query statement may carry index information of the first index of the database table. This index information can uniquely identify the first index. The index information may include: the index name, the table name of the database table, etc.

[0126] In one instance, upon receiving an initial aggregate query statement for the database table, the query optimizer rewrites the initial aggregate query statement to obtain a query statement for the first index of the database table.

[0127] In step 202 above, the memory table is queried according to the query statement to obtain a first pre-aggregation result; the disk file is queried according to the query statement to obtain a second pre-aggregation result.

[0128] There can be multiple disk files, and there can also be multiple second pre-aggregation results, with each of the multiple second pre-aggregation results corresponding to one of the multiple disk files.

[0129] In the above 203, the first pre-aggregation result and the second pre-aggregation result are obtained by aggregating using the same aggregation function, that is, the aggregation function corresponding to the first pre-aggregation result and the aggregation function corresponding to the second pre-aggregation result are the same aggregation function.

[0130] Based on the aggregation function corresponding to the first pre-aggregation result, the first pre-aggregation result and the second pre-aggregation result are aggregated to obtain the aggregation result.

[0131] For example, a query is used to find the average price of fruit. The first pre-aggregated result found in the memory table is <18, 2>, and the second pre-aggregated result found in the disk file is <7, 2>. Then, according to the averaging function, <18, 2> and <7, 2> are aggregated to obtain the aggregated result: <18+7, 2+2>, which is <25, 4>. Here, <25, 4> represents the value 25 / 4.

[0132] In a distributed database system, the user table is divided into multiple shards, and the database table mentioned above is one shard of the user table. Since the first index is an index on each shard, the pre-aggregation result stored in the first index is only the result of local aggregation on that shard. To obtain the aggregation result for the entire user table, a global aggregation is needed. Specifically, for the aggregation query of this user table, steps 201 to 204 above are followed to obtain the local aggregation result corresponding to each shard; then, the aggregation function mentioned above is used to aggregate the local aggregation results corresponding to each shard to obtain the global aggregation result, such as... Figure 3 As shown. In practical applications, a distributed database system includes a central scheduling node and multiple computing nodes. Multiple shards of the user table are distributed across these computing nodes. The local aggregation result corresponding to each shard is obtained by the computing node where each shard resides; each computing node sends its obtained local aggregation result to the central scheduling node, which then performs global aggregation to obtain the global aggregation result.

[0133] Because the data in the original table has already been aggregated during the flush and compaction processes, the amount of data with the same grouping field value has been greatly reduced. Therefore, the aggregation operations involved in the query are much lighter than those performed directly on the original table.

[0134] The following will combine Figure 4 The data update method provided in the embodiments of this application will be described with examples.

[0135] Upon receiving an insert statement from a user targeting the Nth shard of the user table, the record to be inserted corresponding to the insert statement is inserted into the WAL corresponding to the Nth shard. Based on the updated WAL, the record to be inserted is added to the memory table of the clustered index of the Nth shard; based on the updated WAL and the aggregate function, the pre-aggregated result is inserted into the memory table of the first index of the Nth shard. Figure 4 The Aggcol2 shown in the figure and the corresponding grouping field value ( Figure 4 As shown in the diagram (col1), when the memory table meets the disk flushing conditions, an aggregation function is used to pre-aggregate the memory table for disk flushing to obtain the disk file File1. After successful disk flushing, the memory space occupied by the memory table is reclaimed, and a new memory table is created. When the memory table meets the disk flushing conditions again, an aggregation function is used to pre-aggregate the memory table for disk flushing to obtain the disk file File2. After successful disk flushing, the memory space occupied by the memory table is reclaimed again, and a new memory table is created. The above steps are repeated thereafter. Furthermore, aggregation functions can be used to merge and pre-aggregate multiple disk files to obtain a new disk file with a first index.

[0136] It should be noted that any steps in the method provided in this application that are not described in detail can be found in the corresponding content of the above embodiments, and will not be repeated here. Furthermore, the method provided in this application may include other parts or all of the steps in the above embodiments in addition to the steps described above; for details, please refer to the corresponding content of the above embodiments, and will not be repeated here.

[0137] The data update and data query methods provided in this application can be applied to the interactive analytics platform Hologres. Hologres is a one-stop real-time data warehouse engine that supports real-time writing, updating, and analysis of massive amounts of data. It supports standard SQL (Structured Query Language), multidimensional and ad-hoc analysis of petabyte-level data, high-concurrency and low-latency online data services, and provides an integrated online and offline full-stack data warehouse solution.

[0138] In addition, the data in the first index can be stored in a columnar format, which facilitates OLAP calculation and analysis.

[0139] In summary, this solution, based on the LSM storage engine, uses the set of pre-aggregated results as an index of the database table, namely the first index mentioned above. The first index and the database table share the same Write-Ahead Log (WAL), meaning all writes to the database table are synchronized to the first index in real time. This ensures strong consistency between the pre-aggregated results in the first index and the database table. Furthermore, because they share the same WAL, the LSM WAL replay mechanism guarantees strong consistency between the first index and the database table during failover. In other words, within the storage engine, the index serving as the source table reuses the same WAL, ensuring strong consistency and real-time performance between the materialized view and the source table data during normal writes and failover.

[0140] Since the first index reuses the same WAL as the database table, and the first index itself only stores the aggregated data, it does not incur additional overhead in terms of data storage. In principle, a database table can create any number of different first indexes, all of which reuse the same WAL as the database table and only synchronize the data they need.

[0141] Because materialized views and database tables are strongly consistent in real time, the query optimizer can rewrite certain queries on the database tables to reuse the results of materialized view aggregations, thereby improving the performance of certain queries on the database tables.

[0142] Figure 5 A schematic diagram of the structure of an electronic device according to an embodiment of this application is shown. Figure 5As shown, the electronic device includes a memory 1101 and a processor 1102. The memory 1101 can be configured to store various other data to support operation on the electronic device. Examples of such data include instructions for any application or method used to operate on the electronic device. The memory 1101 can be implemented by any type of volatile or non-volatile storage device or a combination thereof, such as Static Random Access Memory (SRAM), Electrically Erasable Programmable Read-Only Memory (EEPROM), Electrically Programmable Read Only Memory (EPROM), Programmable Read Only Memory (PROM), Read Only Memory (ROM), magnetic storage, flash memory, magnetic disk, or optical disk.

[0143] The memory 1101 is used to store programs;

[0144] The processor 1102, coupled to the memory 1101, is used to execute the program stored in the memory 1101 to implement the traffic flow determination method provided in the above-described method embodiments.

[0145] Furthermore, such as Figure 5 As shown, the electronic device also includes: communication component 1103, display 1104, power supply component 1105, audio component 1106, and other components. Figure 5 The diagram only shows some components and does not mean that the electronic device includes only these components. Figure 5 The components shown.

[0146] Accordingly, embodiments of this application also provide a computer-readable storage medium storing a computer program, which, when executed by a computer, can implement the steps or functions of the traffic flow determination method provided in the above-described method embodiments.

[0147] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs. Those skilled in the art can understand and implement this without any creative effort.

[0148] Through the above description of the embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by means of software plus necessary general-purpose hardware platforms, and of course, it can also be implemented by hardware. Based on this understanding, the above technical solutions, in essence or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM (Read Only Memory) / RAM (Random Access Memory), magnetic disk, optical disk, etc., including several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute the methods described in the various embodiments or some parts of the embodiments.

[0149] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of this application, and are not intended to limit them. Although this application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of this application.

Claims

1. A data update method, characterized in that, include: The system receives write requests for a database table, writes the records involved in the write request and the data operation type corresponding to the records into a write-ahead log to update the write-ahead log. The write-ahead log is shared by the database table and its first index. The first index is used to store the pre-aggregation results of pre-aggregating the records of the database table using aggregation functions. The database table and its first index are stored using the LSM storage engine. Update the database table based on the records involved in the write requests and the data operation types corresponding to those records in the updated write-ahead log; Using the LSM storage engine, based on the data operation type of the records involved in the write request in the updated write-ahead log, the aggregate function is used to pre-aggregate the records to obtain the pre-aggregation result, and the pre-aggregation result is written into the memory table of the first index to update the first index.

2. The method according to claim 1, characterized in that, Also includes: When the memory table of the first index meets the disk flushing condition, the aggregation function is used to perform pre-aggregation on the pre-aggregation result in the memory table of the first index to obtain the first target pre-aggregation result; Based on the first target pre-aggregation result, generate the disk file of the first index.

3. The method according to claim 2, characterized in that, Also includes: Retrieve multiple disk files from the first index; Using the aggregation function, the pre-aggregation results in the multiple disk files are pre-aggregated to obtain the second target pre-aggregation result; Based on the second target pre-aggregation result, a new disk file for the first index is generated.

4. The method according to claim 2, characterized in that, The memory table of the first index contains the grouping field and calculated field of the aggregate function; Writing the pre-aggregation result into the memory table of the first index includes: From the records, determine the target grouping field value of the grouping field; The pre-aggregation result is determined as the target calculated field value of the calculated field; The target grouping field value and the target calculated field value are inserted as a record into the memory table of the first index.

5. The method according to claim 4, characterized in that, Using the aggregation function, the pre-aggregation results in the memory table of the first index are pre-aggregated to obtain the first target pre-aggregation result, including: Group the multiple records in the memory table of the first index to obtain at least one group of records; records with the same grouping field value are grouped into the same group of records; Using the aggregation function, pre-aggregation is performed on each group of records to obtain the first target pre-aggregation result for each group of records.

6. The method according to any one of claims 1 to 5, characterized in that, Determining the record involved in the write request and the data operation type corresponding to the record includes: When the write request includes an insert statement, the record to be inserted corresponding to the insert statement is taken as the first record involved in the write request; the data operation type corresponding to the first record is determined as the insert type; or When the write request includes a delete statement, the record to be deleted corresponding to the delete statement is taken as the second record involved in the write request; the data operation type corresponding to the second record is determined as the delete type; or When the write request includes an update statement, determine the record to be deleted and the record to be inserted corresponding to the update statement; designate the record to be deleted as the third record involved in the write request, and designate the record to be inserted as the fourth record involved in the write request; determine the data operation type corresponding to the third record as the deletion type, and determine the data operation type corresponding to the fourth record as the insertion type.

7. The method according to any one of claims 1 to 5, characterized in that, Also includes: Create a clustered index on the database table; Based on the write request and the preset aggregation function, update the database table and its first index, including: Based on the write request and the aggregation function, update the clustered index and the first index of the database table.

8. A data query method, characterized in that, include: A query statement is determined targeting a first index of a database table; wherein the first index is used to store pre-aggregation results of pre-aggregation of records in the database table using aggregate functions; the first index includes: a memory table and a disk file; wherein the first index is updated using the method described in any one of claims 1 to 7. Based on the query statement, the memory table and the disk file are queried respectively to obtain the first pre-aggregation result and the second pre-aggregation result; Based on the aggregation functions corresponding to the first pre-aggregation result and the second pre-aggregation result, the first pre-aggregation result and the second pre-aggregation result are aggregated to obtain the aggregation result; The aggregation result is determined as the query result of the query statement.

9. An electronic device, characterized in that, include: Memory and processor, among which, The memory is used to store programs; The processor, coupled to the memory, is configured to execute the program stored in the memory to implement the method of any one of claims 1 to 8.

10. A computer-readable storage medium storing a computer program, characterized in that, When the computer program is executed by a computer, it can implement the method of any one of claims 1 to 8.

Images