A data lake data processing method and system

Abstract

This invention discloses a data lake data processing method and system. The method includes: parsing business snapshot information from the data submitted in this confirmed transaction and segmenting the data according to the time granularity of the business snapshot to obtain incremental data for each business snapshot's corresponding data version; updating the mapping relationship between business snapshots and data versions in the G-SCD table based on the business snapshot information and the corresponding data versions, and synchronizing the incremental data for each business snapshot's corresponding data version to the data lake for storage. Upon receiving a data query request, the method queries the mapping relationship between business snapshots and data versions in the G-SCD table based on the business snapshot information, obtains the data for the queried data version, and provides it to the querying user. This method avoids storing all data for each version during data storage, preventing significant data redundancy. During querying, it can retrieve the data corresponding to each version without merging old and new data, improving query efficiency and speed.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, and in particular to a data lake data processing method and system. Background Technology

[0002] In data warehouse scenarios, star schemas or snowflake schemas are typically used to link fact tables and dimension tables. Dimensions in the dimension tables are updated over time. During data warehouse construction, a dedicated Slowly Changing Dimension (SCD) integration module can be used to update the dimension tables. SCD means that the values ​​of dimension fields in the dimension tables are adjusted over time, and in subsequent analyses, historical data still uses the old dimension values, while new data uses the current dimension values.

[0003] In traditional data warehouses, different methods for handling new dimension values ​​can be selected based on how users utilize the dimensions, resulting in various types of SCDs. For example, the types listed in Table 1 below:

[0004] Table 1

[0005]

[0006] In traditional data warehouses, some dimension tables have dimensions that change infrequently. In some scenarios, only the latest dimension values ​​need to be stored, which is where Type 1 can be used; in others, all historical records need to be retained, which is where Type 2 can be used. The classic SCD Type 2 solutions in traditional data warehouse systems include the following two:

[0007] Option 1:

[0008] The incremental data table at time T+1 is constructed in a streaming manner and merged with the partition data at time T in the offline table to generate the partition data at time T+1 in the offline table.

[0009] Option 2:

[0010] Save the offline base table, and save the incremental data at each time point T as an independent incremental data table. When querying the data at time point T, merge the base table and the incremental data table at time point T. Summary of the Invention

[0011] The inventors of this application have discovered that if Scheme 1 is used, since only a small portion of the data may need to be updated, there will be a large amount of redundancy and duplication between the data in each partition and the data in the previous partition, resulting in a significant waste of storage resources. If Scheme 2 is used, the base table and the incremental data table need to be merged during data querying. Structured Query Language (SQL) is difficult to maintain, and the query performance is poor. Data merging will cause delays and long query times.

[0012] In view of the above problems, the present invention is proposed to provide a data lake data processing method and system that overcomes or at least partially solves the above problems.

[0013] This invention provides a data lake data processing method, comprising:

[0014] For the data submitted in this confirmation, the business snapshot information is parsed out and the data is segmented according to the time granularity of the business snapshot to obtain the incremental data of the data version corresponding to each business snapshot;

[0015] Based on the business snapshot information and the corresponding data version, update the mapping relationship between business snapshots and data versions in the pre-created G-SCD table, and synchronize the incremental data of the data version corresponding to each business snapshot to the data lake for storage; G-SCD refers to a slowly changing dimension based on fixed granularity.

[0016] In some optional embodiments, the step of parsing the business snapshot information from the data submitted in this confirmation and segmenting the data according to the time granularity of the business snapshot to obtain incremental data for each business snapshot's corresponding data version includes:

[0017] After receiving the data submission confirmation from the distributed subscription system, the streaming computing engine parses the submission time information from the data submission information and processes it according to the pre-set data format of the business snapshot to obtain the business snapshot information; and

[0018] The streaming computing engine segments the data submitted in this confirmation according to the time granularity of the business snapshot, and segments the data of different business snapshots to obtain the data version corresponding to each business snapshot and the incremental data of the incremental data of that data version.

[0019] In some optional embodiments, updating the mapping relationship between business snapshots and data versions in the pre-created G-SCD table based on business snapshot information and corresponding data versions includes:

[0020] The streaming computing engine updates the business snapshot at a specified position in the pre-created G-SCD table according to the parsed business snapshot information and the specified business snapshot format, and obtains the latest data version corresponding to the business snapshot and updates the latest data version corresponding to the business snapshot to the G-SCD table.

[0021] In some optional embodiments, the above method further includes:

[0022] According to the preset backtracking cycle or based on the data backtracking command, the data backtracking and repair process is initiated: the data is rolled back to the specified historical moment, the data correctness is checked, and the erroneous data is repaired.

[0023] In some optional embodiments, the above method further includes:

[0024] Initiate storage point operations according to the preset storage period or based on storage instructions: add storage point markers to data versions that need to be permanently retained. Data versions with added storage point markers will be permanently retained when historical data is cleaned up.

[0025] In some optional embodiments, before updating the mapping between business snapshots and data versions in the pre-created G-SCD table, the following steps are also included:

[0026] Create the G-SCD table;

[0027] The G-SCD table includes a table identifier for identifying the G-SCD table, the granularity of the business snapshot, the business snapshot field, the data format of the business snapshot field, and the data version corresponding to the business snapshot.

[0028] This invention provides a data lake data processing method, comprising:

[0029] Upon receiving a data query request, the business snapshot information included in the data query request is parsed out.

[0030] Based on the business snapshot information, query the mapping relationship between business snapshots and data versions in the G-SCD table to determine the data version being queried; G-SCD refers to a slowly changing dimension based on fixed granularity.

[0031] The system retrieves the version of the data requested and provides it to the user who made the query.

[0032] In some optional embodiments, based on the business snapshot information, the mapping relationship between business snapshots and data versions in the G-SCD table is queried to determine the queried data version, including:

[0033] Based on the business snapshot information, the business snapshot field in the G-SCD table is queried to determine the business snapshot whose data format and content match the data in the G-SCD table. The queried data version is then determined based on the correspondence between the business snapshot and the data version. In some optional embodiments, the data of the queried data version is obtained and provided to the querying user, including:

[0034] Based on the determined data version, the old data portion and / or the incremental data portion of the data version involved in the previous data version are determined, and the old data portion and the incremental data portion are obtained respectively to obtain the data of the data version of the query and provide it to the querying user.

[0035] In some optional embodiments, the above method further includes:

[0036] Before providing the query version of the data to the querying user, add business snapshot information to the data.

[0037] This invention provides a data processing system, including a distributed subscription system, a streaming computing engine, a query engine, and a data lake;

[0038] The distributed subscription system is used to submit data to the streaming computing engine;

[0039] The streaming computing engine is used to execute the data lake data processing method described above, and after processing the confirmed submitted data, it synchronizes it to the data lake for storage.

[0040] The query engine is used to execute the data lake data processing methods described above. Based on the received data query request, it retrieves the queried data from the data lake and provides it to the query user.

[0041] This invention provides a computer storage medium storing computer-executable instructions, which, when executed by a processor, implement the aforementioned data lake data processing method.

[0042] This invention provides a data processing device, including: a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the program, it implements the data lake data processing method described above.

[0043] The beneficial effects of the above-described technical solutions provided in the embodiments of the present invention include at least the following:

[0044] The data lake data processing method provided in this invention, during data storage, parses the business snapshot information of the data submitted in the current confirmation, establishes a mapping relationship between the business snapshot and the data version, and segments the data to store incremental data for each data version. This approach eliminates the need to store all data for each data version, avoiding significant data redundancy and reducing storage resource waste. During data querying, based on the business snapshot information in the query request, the corresponding business snapshot in the G-SCD table is determined. Based on the mapping relationship between the business snapshot and the data version, the queried data version is determined, and the corresponding version of the data is retrieved. There is no need to merge old and new data, optimizing query performance and improving query efficiency and speed.

[0045] Other features and advantages of the invention will be set forth in the description which follows, and will be apparent in part from the description, or may be learned by practicing the invention. The objects and other advantages of the invention may be realized and obtained by means of the structures particularly pointed out in the written description, claims, and drawings.

[0046] The technical solution of the present invention will be further described in detail below with reference to the accompanying drawings and embodiments. Attached Figure Description

[0047] The accompanying drawings are provided to further illustrate the invention and form part of the specification. They are used in conjunction with embodiments of the invention to explain the invention and do not constitute a limitation thereof. In the drawings:

[0048] Figure 1 This is a flowchart of the data lake data processing method in Embodiment 1 of the present invention;

[0049] Figure 2 This is a schematic diagram illustrating the principle of data lake data processing in an embodiment of the present invention;

[0050] Figure 3 This is a flowchart of the data lake data processing method in Embodiment 2 of the present invention;

[0051] Figure 4 This is a schematic diagram of the structure of a data lake data processing device according to an embodiment of the present invention;

[0052] Figure 5 This is a schematic diagram of another data lake data processing device in an embodiment of the present invention;

[0053] Figure 6 This is a schematic diagram of the data lake data processing system in an embodiment of the present invention. Detailed Implementation

[0054] Exemplary embodiments of the present disclosure will now be described in more detail with reference to the accompanying drawings. While exemplary embodiments of the present disclosure are shown in the drawings, it should be understood that the present disclosure may be implemented in various forms and should not be limited to the embodiments set forth herein. Rather, these embodiments are provided so that this disclosure will be thorough and complete, and will fully convey the scope of the disclosure to those skilled in the art.

[0055] To address the data redundancy and resource waste issues inherent in the first data processing method of existing technologies, and the poor query performance and long query times due to data merging delays in the second data processing method, this invention provides a data lake data processing method, related apparatus, and system. By setting up specially configured tables and based on the mapping relationship between business snapshots and data versions, data storage avoids data redundancy and resource waste. Data queries do not require merging of old and new data, improving query performance, reducing latency and waiting time during the query process, and increasing query speed and efficiency. Typically, a business snapshot refers to a record of the state of data storage at a specific moment.

[0056] The data lake data processing method provided in this invention offers a new approach to implementing classic SCD Type 2 scenarios in traditional data warehouse systems, based on specific functional data lake formats / databases. It solves problems such as wasted storage resources and the need for multiple tables that may exist in traditional data warehouse architectures. This solution requires the database or data lake format to have batch and stream integration, multi-version management, time travel, rollback, and savepoint permanent version storage capabilities, which are achieved by upgrading and modifying the big data query engine and offline / streaming computing engine. This method reduces storage resource costs, improves query performance, and considers user experience, enabling the practical application of classic SCD scenarios in the data lake / database field.

[0057] The following is a detailed description through specific embodiments.

[0058] Example 1

[0059] Embodiment 1 of the present invention provides a data lake data processing method, the process of which is as follows: Figure 1 As shown, its implementation principle can be found in [reference needed]. Figure 2 As shown, this embodiment describes the data storage process, which includes the following steps:

[0060] Step S101: For the data submitted in this confirmation, parse out the business snapshot information and perform data segmentation according to the time granularity of the business snapshot to obtain the incremental data of the data version corresponding to each business snapshot.

[0061] After receiving the data submission confirmation from the distributed subscription system, the streaming computing engine parses the submission time information from the data submission information and processes it according to the pre-set data format of the business snapshot to obtain the business snapshot information. The streaming computing engine then segments the submitted data according to the time granularity of the business snapshot, dividing it into data for different business snapshots, obtaining the data version corresponding to each business snapshot and the incremental data for that version. Optionally, the streaming computing engine determines whether the submitted data belongs to the same business snapshot based on the submission time of the data submitted by the distributed subscription system. If they do not belong to the same business snapshot, the submitted data is segmented to obtain data for different business snapshots. The parsed business snapshot information is generally a time, such as October 1st, which indicates a business snapshot on October 1st, recording the data status on October 1st.

[0062] Step S102: Based on the business snapshot information and the corresponding data version, update the mapping relationship between business snapshots and data versions in the pre-established G-SCD table, and synchronize the incremental data of the data version corresponding to each business snapshot to the data lake for storage.

[0063] The streaming engine updates the pre-created G-SCD table at a specified location based on the parsed business snapshot information, according to the specified business snapshot format. It determines the latest data version corresponding to the business snapshot and updates the G-SCD table with this latest data version. G-SCD stands for Grammatically Based Slowly Changing Dimension, which can also be described as a slowly changing dimension based on business snapshots. The streaming engine synchronizes the incremental data of each business snapshot's data version to the data lake (DeltaLake) for storage.

[0064] See Figure 2 As shown, the database management system (MySQL) synchronizes data to the distributed subscription system (Kafka), and the distributed subscription system synchronizes data to the streaming computing engine (e.g., Spark Streaming). For example, data shards Partitions0, Partitions1, Partitions2, and Partitions3 are synchronized to Spark Streaming in batch processing (Batch X). In Spark Streaming, batch data is split by time granularity and merged sequentially. For example, data from business snapshot T and business snapshot T+1 are split and synchronized to the data lake (Detla Lake). In the data lake, the mapping relationship between business snapshots and data versions is stored. For example, version V6 corresponds to business snapshot T-1 (T-1 Snapshot), i.e., V6->T-1, and so on, V7->T, V8->T, V9->T+1, V10->T+1. The mapping relationship between data versions and business snapshots is shown in Table 1, as well as the incremental data for each data version.

[0065] Table 1

[0066]

[0067]

[0068] After the streaming computing engine performs data partitioning, it submits the data to the data lake storage in an ordered manner. This means that when new data arrives, the data from previous versions should already be stored. For example, if version V10 data arrives, version V9 data is assumed to be already stored. To avoid errors, a data backtracking mechanism can be used to ensure ordered data storage.

[0069] In existing technologies, a business snapshot at a certain point in time typically records the data state at that specific moment. For example, when storing full data in Scheme 1, it records which data partition the data at a certain moment is stored in. When storing incremental data in Scheme 2, the business snapshot records the basic data tables and incremental data tables involved in the data at a certain moment. Since existing technologies do not have the concept of data versioning, nor do they map business snapshots to data versions, it is not convenient to directly obtain the data at a certain moment. It is necessary to compare and merge the basic data tables and incremental data tables involved in order to read the data at a certain moment.

[0070] In some optional embodiments, the above method further includes: initiating a data backtracking and repair process according to a preset backtracking cycle or based on a data backtracking instruction: rolling back the data to a specified historical moment, checking the data correctness, and repairing erroneous data. In a production environment, it is difficult to guarantee that every written data is correct, and there is often a need to backtrack / repair data. By utilizing the rollback capability of the database / data lake format, data can be rolled back to a certain historical moment, and then the data can be traced again to repair problematic data.

[0071] In some optional embodiments, the above method further includes: initiating a storage point operation according to a preset storage period or based on a storage instruction: adding storage point markers to data versions that need to be permanently retained, wherein data versions with added storage point markers are permanently retained when historical data is cleaned up. In the prior art, Solution 1 stores the full amount of data, so cleaning up historical versions will not affect the latest version of the data. In the prior art, Solution 2, although it stores incremental data, its storage mechanism ensures that incremental data at any given time is not cleaned up; therefore, there is no related processing mechanism for storage point markers. However, in this application, because the concept of data versions is introduced and a mechanism for periodically cleaning up historical data is designed, data that needs to be retained long-term is marked by adding storage point markers to prevent it from being cleaned up.

[0072] Most database / data lake formats periodically clean up metadata or data to optimize performance and reduce storage resource waste. This application's business scenario requires the permanent storage of the data version corresponding to each business snapshot until the user deletes it. Therefore, this solution uses the Savepoint command supported by the database / data lake format to permanently save the specified data version, ensuring the validity of the data version corresponding to the business snapshot.

[0073] In some optional embodiments, the above method further includes: before updating the mapping relationship between business snapshots and data versions in the pre-created G-SCD table, pre-creating the G-SCD table, which includes a table identifier for identifying the G-SCD table, the granularity of the business snapshot, the business snapshot field, the data format of the business snapshot field, and the data version corresponding to the business snapshot. When creating the G-SCD table, the relevant parameters listed above can be specified.

[0074] In the method described in this embodiment, the data submitted to the data lake storage meets the following requirements:

[0075] 1) Each confirmed commit belongs to only one business snapshot;

[0076] 2) Strictly ensure the monotonically increasing nature of business snapshot data, that is, ensure that there will be no business snapshot data with a value less than T after the business snapshot T is reached, and each commit contains only the data of one business snapshot.

[0077] In offline scenarios, the value of a specified business snapshot can be displayed. The value of the business snapshot representing the latest data version can be directly compared with the value of the business snapshot in this commit to determine whether to agree to the submission. This ensures that the submitted data meets the above requirements.

[0078] In real-time scenarios, the format of business snapshots is specified by watermarks, and the submitted data can be guaranteed to meet the above requirements through the following three aspects: 1) Data is required to be partitioned by key, and data with the same key falls into the same Kafka partition; 2) Data within the same Kafka partition is strictly ordered; 3) For cases where two business snapshot ranges of data appear in the same streaming batch at close time, a cache is set up to identify the arrival of the data of the later business snapshot before submitting the data of the previous business snapshot; thus ensuring the monotonically increasing nature of business snapshots and the requirement that each commit belongs to only one business snapshot.

[0079] At each commit, the business snapshot value is recorded and saved, thus obtaining the mapping relationship between the business snapshot and the data version.

[0080] Example 2

[0081] Embodiment 2 of the present invention provides a data lake data processing method, the process of which is as follows: Figure 3 As shown, this embodiment describes the data query process, which includes the following steps:

[0082] Step S201: After receiving the data query request, parse out the business snapshot information included in the data query request.

[0083] The data query request includes the query conditions entered by the user. The query engine parses the data query request and extracts information such as business snapshots included in the query conditions.

[0084] Step S202: Based on the parsed business snapshot information, query the mapping relationship between business snapshots and data versions in the G-SCD table to determine the data version being queried.

[0085] Based on the parsed business snapshot information, query the business snapshot field in the G-SCD table to determine the business snapshot whose data format and content match in the G-SCD table, and determine the data version corresponding to the business snapshot information based on the correspondence between business snapshots and data versions.

[0086] Step S203: Obtain the data version of the queried data and provide it to the querying user.

[0087] Based on the determined data version, the system identifies the older data portion and / or the incremental data portion of the preceding data version. The system then retrieves the older and incremental data portions to obtain the data for the queried data version and provides it to the user. In practical applications, when querying data from a business snapshot, the latest version of that business snapshot in the data lake is returned. For example, using the mapping relationship shown in Table 1, when querying data for business snapshot T, the system returns the data version of the last business snapshot in the data lake that is T, i.e., V9.

[0088] For example, if the queried data version includes 10 data entries, 3 new and 7 old, and the business snapshot records the data changes, it's clear which data versions need to be retrieved from which parts. Specifically, the 3 new entries represent the incremental data of the queried data version and can be directly retrieved. The 7 old entries are from earlier data versions, and only these 7 new entries need to be retrieved from those earlier versions, without needing to retrieve the other data. The 3 new entries and 7 old entries can then be retrieved and provided to the user together. For instance, if the 10 data entries are stored in 10 files, with one record per file, and if entries 8, 9, and 10 are updated, and the business snapshot records that files 8, 9, and 10 are obsolete and new files 8ˊ, 9ˊ, and 10ˊ are created, then using the method described in this application, only files 1, 2, 3, 4, 5, 6, 7, 8ˊ, 9ˊ, and 10ˊ need to be retrieved.

[0089] Using the second solution in the existing technology requires reading the incremental data of the queried data version and all data of the previous version. Since there is no business snapshot to guide us, we do not know which parts of the old data need to be read. Therefore, we need to read them all and then merge the incremental data and the old data of the previous version. For example, we remove the old data corresponding to the three new data and then merge the three new data into the old data. As we can see, the method of this application does not need to merge the two data tables of basic data and incremental data of the previous version. We can directly read the required data without merging and removing data after reading. This results in better query performance and faster processing speed.

[0090] In some optional embodiments, the method further includes adding business snapshot information to the data before providing the queried data version to the querying user. The business snapshot field serves as a specified condition for the user's query, but its value is merely an identifier and is not actually written to the data file. Therefore, when the user uses the SQL query in the above operation flow, the business snapshot column (i.e., the dt column in the SQL) will not be displayed. Thus, the query engine must be used to populate the specified business snapshot value so that the returned result displays the business snapshot value, ensuring complete consistency of the query SQL result.

[0091] The method described in this embodiment is fully compatible with the SQL query method of traditional data warehouse SCD solutions, avoiding the understanding and migration costs of business operations and achieving transparency for users.

[0092] Based on the same inventive concept, embodiments of the present invention also provide a data lake data processing apparatus, which can be installed in a flow calculation engine or other network device, and the structure of the apparatus is as follows. Figure 4 As shown, it includes:

[0093] Data processing module 11 is used to parse the business snapshot information from the data submitted in this confirmation and to perform data segmentation according to the time granularity of the business snapshot to obtain incremental data for each business snapshot corresponding to the data version.

[0094] The data mapping module 12 is used to update the mapping relationship between business snapshots and data versions in the pre-created G-SCD table according to the business snapshot information and the corresponding data version, and to synchronize the incremental data of the data version corresponding to each business snapshot to the data lake for storage.

[0095] The aforementioned data processing module 11 is specifically used to, after receiving the data confirmation submission information from the distributed subscription system, parse the submission time information from the data submission information, process it according to the pre-set data format of the business snapshot, and obtain the business snapshot information; and to segment the data submitted in this confirmation according to the time granularity of the business snapshot, segment the data of different business snapshots, and obtain the data version corresponding to each business snapshot and the incremental data of the incremental data of that data version.

[0096] The aforementioned data mapping module 12 is specifically used by the streaming computing engine to update the business snapshot at a specified position in the pre-created G-SCD table according to the parsed business snapshot information and the specified business snapshot format, and to obtain the latest data version corresponding to the business snapshot and update the latest data version corresponding to the business snapshot to the G-SCD table.

[0097] The aforementioned data processing module 11 is also used to initiate a data backtracking and repair process according to a preset backtracking cycle or based on a data backtracking instruction: roll back the data to a specified historical moment, check the data correctness, and repair erroneous data.

[0098] The aforementioned data processing module 11 is also used to initiate storage point operations according to a preset storage period or based on storage instructions: adding storage point markers to data versions that need to be permanently retained, wherein data versions with added storage point markers will be permanently retained when cleaning up historical data.

[0099] The aforementioned data mapping module 12 is also used to create a G-SCD table, where G-SCD refers to a slowly changing dimension based on a fixed granularity. The G-SCD table includes a table identifier for identifying the G-SCD table, the granularity of the business snapshot, the business snapshot field, the data format of the business snapshot field, and the data version corresponding to the business snapshot.

[0100] Based on the same inventive concept, embodiments of the present invention also provide another data lake data processing apparatus, which can be installed in a query engine or other network device, and the structure of the apparatus is as follows: Figure 5 As shown, it includes:

[0101] Information receiving module 21 is used to parse the business snapshot information included in the data query request after receiving the data query request;

[0102] The query processing module 22 is used to query the mapping relationship between business snapshots and data versions in the G-SCD table based on business snapshot information, and to determine the data version to be queried.

[0103] The data providing module 23 is used to obtain the data version of the queried data and provide it to the querying user.

[0104] The query processing module 22 described above is specifically used to query the business snapshot field in the G-SCD table based on the business snapshot information, determine the business snapshot in the G-SCD table whose data format and content match, and determine the data version to be queried based on the correspondence between the business snapshot and the data version.

[0105] The aforementioned data providing module 23 is specifically used to determine, based on the determined data version being queried, the old data portion in the prior data version involved in the data version and / or the incremental data portion of the data version, and obtain the data of the data version being queried from the old data portion and the incremental data portion respectively, and provide it to the querying user.

[0106] The aforementioned data providing module 23 is also used to add business snapshot information to the data before providing the data version to the querying user.

[0107] Based on the same inventive concept, embodiments of the present invention also provide a data processing system, the structure of which is as follows: Figure 6 As shown, it includes: a distributed subscription system 3, a streaming computing engine 1, a query engine 2, and a data lake 4;

[0108] Distributed subscription system 3 is used to submit data to the streaming computing engine;

[0109] Streaming engine 1 is used to confirm that submitted data has been processed and then synchronized to the data lake for storage; Streaming engine 1 can be configured as follows: Figure 4 The data processing device shown can perform, for example Figure 1 The data lake data processing method is shown.

[0110] Query Engine 2 is used to retrieve the requested data from Data Lake 4 and provide it to the querying user based on the received data query request. Query Engine 2 can be configured as follows: Figure 5 The data processing device shown can perform, for example Figure 3 The data lake data processing method is shown.

[0111] This invention also provides a computer storage medium storing computer-executable instructions, which, when executed by a processor, implement the data lake data processing method described above.

[0112] This invention also provides a data processing device, including: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the above-described data lake data processing method.

[0113] The above method and system are applicable to data lake formats / database architectures that meet the required functionalities. They implement a Type 2 solution that retains all historical records, allowing queries to be performed at fixed business snapshot times, and can be applied to relevant Alibaba Cloud products. This method is based on a database / data lake format and requires a corresponding query and computing engine. The database (HBase, etc.), data lake format (DeltaLake, Hudi, Iceberg, etc.), query engine (Hive, Spark, Presto, etc.), and computing engine (Spark, Flink, etc.) must all possess or implement the corresponding functionalities to achieve this solution. Specifically:

[0114] I. Database / data lake formats must have or implement the following capabilities:

[0115] 1. Allows the definition of tables with special configurations.

[0116] Binding operations are implemented through specially configured tables. For example, a G-SCD table can be defined, where synchronized data must carry business snapshot information. The streaming computing engine will only execute subsequent data storage operations after parsing the business snapshot information. The time granularity of the business snapshot can be days, hours, or other time granularities. Furthermore, this G-SCD table can have multi-version management capabilities, managing multiple data versions.

[0117] When creating a G-SCD table, you can specify the parameter representation of G-SCD and other related parameters. An example is shown below:

[0118] --Specify G-SCD parameter identifiers and other related parameters in the table

[0119]

[0120] Wherein: "delta.gscdTypeTable" = "true" indicates that the table is a G-SCD table; "delta.gscdGranularity" = "1day" specifies the time granularity of the business snapshot, such as a day; "delta.gscdColumn" = "dt" defines the business snapshot field; "delta.gscdColumnFormat" = "yyyy-MM-dd" specifies the format of the business snapshot field;

[0121] For example, if the business snapshot field is in the format yyyy-MM-dd and the time granularity is "days", then when the transmitted data is yyyy-MM-dd, such as 2020-01-01, it can be correctly recognized. However, if the business snapshot field is in the format yyyyMMdd, then when the transmitted data is yyyy-MM-dd, such as 2020-01-01, it cannot be correctly recognized, and the submission will fail. Therefore, it is necessary to transmit the correct information.

[0122] 2. Batch processing and distribution are integrated.

[0123] Data lakes can support real-time writing and / or streaming writing, and have the ability to automatically trigger savepoints. Automatic savepoints are triggered after all the data in the current business snapshot is ready, meaning that the data version corresponding to this time will be permanently retained.

[0124] 3. Multi-version management and TimeTravel query.

[0125] For example, a G-SCD table needs multi-version management to distinguish data from each version, and to establish a mapping relationship between data versions and business snapshots.

[0126] TimeTravel queries refer to the ability to query historical data versions. For example, for multiple data versions 7, 8, 9, and 10, it is not only necessary to be able to query the latest data version 10, but also to be able to query versions 7, 8, and 9.

[0127] 4. Savepoint permanently saves a specified version;

[0128] Because data lake storage is periodically cleaned, some historical versions of data are deleted during this process. To ensure that certain versions are not deleted and can still be queried, it's necessary to add identifiers to indicate which business snapshots or data versions cannot be deleted. This allows data queries to use only the data versions specified. In other words, it specifies that certain data versions cannot be deleted. For example, if data version 7 corresponds to a business snapshot dated October 1st, and data version 10 corresponds to a business snapshot dated October 2nd, then data versions 7 and 10 cannot be deleted, while data versions 8 and 9 can be deleted. For a given business snapshot, only the latest data version corresponding to that snapshot needs to be retained. This functionality can be implemented in a streaming computing engine.

[0129] 5. Rollback rolls back to a specified version.

[0130] Rollback allows you to revert to a specified version and quickly refresh the data for your business, preventing data storage errors.

[0131] II. A streaming computing engine must possess or implement the following capabilities:

[0132] 1. Receive and parse the specified information format (expression) and extract the business snapshot field.

[0133] Different business processes may express information differently. For example, online click events or transaction events typically include click time or transaction time, which may be accurate to the millisecond level. When extracting a business snapshot, the snapshot time granularity is usually in days, and there are also format requirements. When using click time or transaction time as a business snapshot, it needs to be saved at the day level. Therefore, an expression is needed to parse the day-level time from the click time or transaction time and save it as the business snapshot. In other words, it extracts a business snapshot information in a specified format from upstream data. This capability is generally used during data storage and writing.

[0134] 2. When writing data in streaming / batch mode, carry the business snapshot field.

[0135] When writing data, it is also necessary to write business snapshots, data versions, and incremental data so that the corresponding data version can be retrieved based on the business snapshot when querying and retrieving data. When writing data, the business snapshot field is written; when the time granularity is in days, the business snapshot field is generally the date; when the time granularity is in hours, the business snapshot field is generally the hour.

[0136] 3. Possesses the ability to split data based on business snapshots;

[0137] The streaming computing engine processes a batch of confirmed data together. This batch of data may include data from different business snapshots, such as data from October 1st and October 2nd. If this batch of data is submitted downstream simultaneously, it's impossible to know which business snapshot the data belongs to. The churn calculation engine can segment the data. For example, it can first sort the data by time, then divide it into two parts: one part from October 1st and another part from October 2nd. These parts are then submitted separately: first, the October 1st data is submitted, and this part is attributed to October 1st; then, the October 2nd data is submitted, and this part is attributed to October 2nd.

[0138] III. The query engine must have or implement the following capabilities:

[0139] The query engine can be located downstream of Delta Lake, possibly Spark or another engine.

[0140] 1. Identify tables with special configurations, such as G-SCD tables;

[0141] During the query process, it is necessary to be able to identify the query field. Generally, a query field is specified, such as "dt", which is time. This field is not actually stored in the actual data file, so it is necessary to have the ability to convert it. That is to say, the field will only be converted into an actual field when it is recognized that the table is a specially configured table.

[0142] 2. Convert the query fields of the business snapshot into a specified data version (also known as a data table version).

[0143] Suppose we want to find that dt equals 20200101, we need to perform a mapping transformation to determine the data version corresponding to this business snapshot information, such as data version V9. The received query request contains business snapshot information, but it needs to be converted into a data version so that the corresponding version of the data can be retrieved.

[0144] This functionality can be implemented in the query engine or within the database / data lake format.

[0145] 3. Use the values ​​from the business snapshot in the query request to populate the query results;

[0146] For example, when locating a business snapshot using the G-SCD column identified by dt, the following query statement can be used:

[0147] SELECT id,body FROM gscd_target_table WHERE dt='2021-01-01'

[0148] That is, query the gscd_target_table table for data that meets the query condition dt = '2021-01-01'.

[0149] Specifically, we need to query data in the G-SCD table where the value of the dt column is 2020-01-01. Since this field is in the query conditions and does not actually exist in the data file, it is usually empty in the returned data. Therefore, we want to fill in this value so that the returned data includes the business snapshot information.

[0150] Figure 6 The system shown can realize data storage and retrieval, combined with Figure 2The implementation principle illustrated is as follows: During data storage, data is synchronized to Kafka, and a Spark Streaming task is launched to process the data. This Streaming task automatically identifies and parses the business snapshot fields based on user configuration, splits the data into batches, writes it to the Delta Lake data table, and periodically executes Savepoint to mark specified versions for permanent retention. Based on user configuration, such as parameters for the business snapshot, relevant information is retrieved, including business snapshot information. If there are multiple business snapshot data sets, they need to be split and submitted separately, clearly distinguishing which business snapshot version the split data is written to.

[0151] When performing data queries, users query the G-SCD table through a query engine (such as Spark). The user inputs a query condition and sends a query request containing that condition, which includes business snapshot information. The query engine automatically retrieves the data version corresponding to that time and reads the data for that version. The query results can be used for further analysis or downstream ETL processing.

[0152] Regarding the data lake data processing apparatus and system in the above embodiments, the specific manner in which each module performs its operations has been described in detail in the embodiments related to the method, and will not be elaborated upon here.

[0153] The methods, apparatus, and systems described in these embodiments of the invention can achieve batch and stream processing integration, eliminating the need for two separate tables—an incremental table and a base table—requiring only a mapping table for business snapshots and data versions, thus saving storage resources. Leveraging the data versioning capabilities of the data lake format / database itself, it manages incremental changes without requiring the retention of all historical data at a time granularity. Compared to the traditional first approach, which uses a mapping table and uses snapshot guidance to read the desired data, eliminating the need for redundant data storage. During data queries, there is no need to merge multiple data tables to respond to queries, and the query engine and the capabilities of the database / data lake format can be used to improve query efficiency.

[0154] The above solution retains the original SQL statements, allowing users to still execute queries for snapshot data within the desired business time granularity using similar partitioning fields, much like how business snapshots are implemented with partitioning. In contrast, the existing solution one, which uses data partitioning for business snapshot isolation, requires a business snapshot field (e.g., dt=2020-01-01). This snapshot field can be migrated to the G-SCD table, reducing migration costs. The user query interface of the query engine remains unchanged and requires no special processing. The user query interface enables human-computer interaction; the user inputs query conditions, the device sends these conditions back to the system backend, and the system backend then retrieves relevant data based on the query conditions for the original query method.

[0155] This solution leverages the data versioning capabilities and time-travel querying features of a data lake format / database. Each data commit only saves the currently updated portion as a new version, and each business snapshot corresponds to a specific version. This eliminates the issue of saving unchanged data to the latest version, thus avoiding wasted storage resources. Furthermore, a single table can be used to store both existing and incremental data, eliminating the need to merge multiple tables for data retrieval and avoiding the performance overhead associated with multi-table merge queries.

[0156] Unless otherwise specifically stated, terms such as processing, calculation, operation, determination, display, etc., may refer to the actions and / or processes of one or more processing or computing systems or similar devices that represent the manipulation and conversion of data representing physical (e.g., electronic) quantities within the registers or memory of the processing system into other data similarly representing physical quantities within the memory, registers, or other such information storage, transmission, or display devices of the processing system. Information and signals can be represented using any of a variety of different techniques and methods. For example, data, instructions, commands, information, signals, bits, symbols, and chips mentioned throughout the above description can be represented by voltage, current, electromagnetic waves, magnetic fields or particles, light fields or particles, or any combination thereof.

[0157] It should be understood that the specific order or hierarchy of steps in the disclosed process is an example of an exemplary method. Based on design preferences, it should be understood that the specific order or hierarchy of steps in the process may be rearranged without departing from the scope of this disclosure. The appended method claims provide elements of various steps in an exemplary order and are not intended to limit the scope to the specific order or hierarchy described.

[0158] In the detailed description above, various features are combined together in a single embodiment to simplify this disclosure. This approach to disclosure should not be construed as reflecting an intention that embodiments of the claimed subject matter require more features than are explicitly stated in each claim. Rather, as reflected in the appended claims, the invention is presented with fewer features than all of the features in a single disclosed embodiment. Therefore, the appended claims are hereby explicitly incorporated into the detailed description, with each claim representing a separate preferred embodiment of the invention.

[0159] Those skilled in the art will also understand that the various illustrative logic blocks, modules, circuits, and algorithm steps described in conjunction with the embodiments herein can be implemented as electronic hardware, computer software, or a combination thereof. To clearly illustrate the interchangeability between hardware and software, the various illustrative components, blocks, modules, circuits, and steps described above are generally described in terms of their functionality. Whether such functionality is implemented as hardware or software depends on the specific application and the design constraints imposed on the overall system. Those skilled in the art can implement the described functionality in alternative ways for each specific application; however, such implementation decisions should not be construed as departing from the scope of this disclosure.

[0160] The steps of the methods or algorithms described in conjunction with the embodiments herein can be directly embodied in hardware, software modules executed by a processor, or a combination thereof. The software modules can reside in RAM memory, flash memory, ROM memory, EPROM memory, EEPROM memory, registers, hard disks, removable disks, CD-ROMs, or any other form of storage medium well known in the art. An exemplary storage medium is connected to the processor, enabling the processor to read information from and write information to the storage medium. Of course, the storage medium can also be a component of the processor. The processor and storage medium can reside in an ASIC. The ASIC can reside in a user terminal. Alternatively, the processor and storage medium can exist as discrete components in the user terminal.

[0161] For software implementation, the techniques described in this application can be implemented using modules (e.g., procedures, functions, etc.) that perform the functions described in this application. This software code can be stored in memory units and executed by a processor. The memory units can be implemented within the processor or outside the processor; in the latter case, they are communicatively coupled to the processor via various means, as is well known in the art.

[0162] The foregoing description includes examples of one or more embodiments. It is certainly impossible to describe all possible combinations of components or methods in order to describe the above embodiments, but those skilled in the art will recognize that further combinations and arrangements of the various embodiments are possible. Therefore, the embodiments described herein are intended to cover all such changes, modifications, and variations that fall within the scope of the appended claims. Furthermore, the term "comprising" as used in the specification or claims is interpreted in a manner similar to the term "including," as interpreted when used as a conjunction in the claims. Additionally, the use of any term "or" in the specification of the claims is intended to mean "non-exclusive or."

Claims

1. A data lake data processing method, characterized in that, include: For the data submitted in this confirmation, the business snapshot information is parsed out and the data is segmented according to the time granularity of the business snapshot to obtain the incremental data corresponding to each business snapshot; The incremental data corresponding to each business snapshot is synchronized to the data lake for storage, thus obtaining the data version corresponding to each business snapshot. Based on the business snapshot information and the corresponding data version, update the mapping relationship between the business snapshot and the data version in the pre-created G-SCD table. The G-SCD refers to the slowly changing dimension based on the time granularity of the business snapshot.

2. The method as described in claim 1, characterized in that, The data submitted in this confirmation is parsed to extract business snapshot information, and the data is segmented according to the time granularity of the business snapshot to obtain incremental data for each business snapshot's corresponding data version, including: After receiving the data submission confirmation from the distributed subscription system, the streaming computing engine parses the submission time information from the data submission information and processes it according to the pre-set data format of the business snapshot to obtain the business snapshot information; and The streaming computing engine segments the data submitted in this confirmation according to the time granularity of the business snapshot, and segments the data of different business snapshots to obtain the data version corresponding to each business snapshot and the incremental data of that data version.

3. The method as described in claim 1, characterized in that, The step of updating the mapping relationship between business snapshots and data versions in the pre-created G-SCD table based on business snapshot information and corresponding data versions includes: The streaming computing engine updates the business snapshot at a specified position in the pre-created G-SCD table according to the parsed business snapshot information and the specified business snapshot format, and obtains the latest data version corresponding to the business snapshot and updates the latest data version corresponding to the business snapshot to the G-SCD table.

4. The method as described in claim 1, characterized in that, Also includes: According to the preset backtracking cycle or based on the data backtracking command, the data backtracking and repair process is initiated: the data is rolled back to the specified historical moment, the data correctness is checked, and the erroneous data is repaired.

5. The method as described in claim 1, characterized in that, Also includes: Initiate storage point operations according to the preset storage period or based on storage instructions: add storage point markers to data versions that need to be permanently retained. Data versions with added storage point markers will be permanently retained when historical data is cleaned up.

6. The method according to any one of claims 1-5, characterized in that, Before updating the mapping between business snapshots and data versions in the pre-created G-SCD table, the following steps are also included: Create the G-SCD table; The G-SCD table includes a table identifier for identifying the G-SCD table, the granularity of the business snapshot, the business snapshot field, the data format of the business snapshot field, and the data version corresponding to the business snapshot.

7. A data lake data processing method, characterized in that, include: Upon receiving a data query request, the business snapshot information included in the data query request is parsed out. Based on the business snapshot information, the mapping relationship between business snapshots and data versions in the G-SCD table is queried to determine the data version being queried. The G-SCD refers to the slowly changing dimension based on the time granularity of the business snapshot. The data versions corresponding to each business snapshot in the G-SCD table are obtained when the data processing engine synchronizes the incremental data corresponding to each business snapshot to the data lake. The incremental data corresponding to each business snapshot is obtained by the data processing engine by dividing the data according to the time granularity of the business snapshot. The data of the queried data version is then provided to the querying user.

8. The method as described in claim 7, characterized in that, Based on the business snapshot information, query the mapping relationship between business snapshots and data versions in the G-SCD table to determine the queried data version, including: Based on the business snapshot information, query the business snapshot field in the G-SCD table to determine the business snapshot whose data format and content match in the G-SCD table, and determine the data version to be queried based on the correspondence between the business snapshot and the data version.

9. The method as described in claim 7, characterized in that, The system retrieves the data version requested by the user and provides it to the user, including: Based on the determined data version, the old data portion and / or the incremental data portion of the previous data version involved in the data version are determined, and the data of the data version of the query is obtained by acquiring the old data portion and / or the incremental data portion respectively and provided to the querying user.

10. The method according to any one of claims 7-9, characterized in that, Also includes: Before providing the query version of the data to the querying user, add business snapshot information to the data.

11. A data processing system, characterized in that, This includes a distributed subscription system, a streaming computing engine, a query engine, and a data lake; The distributed subscription system is used to submit data to the streaming computing engine; The streaming computing engine is used to execute the data lake data processing method according to any one of claims 1-6, and to process the confirmed submitted data and then synchronize it to the data lake for storage; The query engine is used to execute the data lake data processing method according to any one of claims 7-10, and to retrieve the queried data from the data lake and provide it to the query user based on the received data query request.

12. A computer storage medium, characterized in that, The computer storage medium stores computer-executable instructions, which, when executed by a processor, implement the data lake data processing method according to any one of claims 1-10.

13. A data processing device, characterized in that, include: A memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor, when executing the program, implements the data lake data processing method according to any one of claims 1-10.

Images