Incremental data synchronization field completion method and device, electronic equipment and storage medium

Abstract

The application provides a kind of incremental data synchronization field completion method, device, electronic equipment and computer readable storage medium.The incremental data synchronization field completion method provided by the application comprises: obtaining the original data to be completed from log data, or obtaining the original data to be completed from log data and full data, and obtaining the primary key field value and non-primary key field value of the original data to be completed, the primary key field value is generated when the original data to be completed;The original data to be completed is grouped according to the primary key field value, and a plurality of grouped data is obtained, and the plurality of grouped data is sorted respectively, and a plurality of sorted grouped data is obtained;The non-primary key field value in the plurality of sorted grouped data is completed respectively using the window function, and the data after field completion is obtained.The incremental data synchronization field completion method of the application improves the accuracy of field completion.

Description

Technical Field

[0001] This invention relates to the field of computer data processing technology, and in particular to an incremental data synchronization field completion method, apparatus, electronic device, and computer-readable storage medium. Background Technology

[0002] Incremental data synchronization is a crucial component of Data Transmission Service (DTS), used to achieve real-time synchronization and updates between the destination and source databases. Incremental data synchronization can be applied in various fields. For example, in the property insurance industry, policy data and transaction data may require real-time synchronization, making incremental data synchronization a viable option. Currently, incremental data synchronization solutions generally fall into two categories: one uses Sqoop (an open-source tool primarily used for data transfer between Hadoop and traditional databases) to extract data based on a data change time field; the other uses OGG (Oracle GoldenGate) operation logs to achieve incremental synchronization.

[0003] In an incremental synchronization scheme using OGG operation logs, returning all field data in each iteration could negatively impact Oracle performance, thus affecting online production services. Therefore, in this scheme, Oracle and OGG generally do not allow sending data for unupdated fields in update types; only updated (modified) fields are permitted. However, in OGG incremental synchronization, unupdated fields need to be completed for use in subsequent merge operations.

[0004] To address the issue of field completion in business data such as policy data and transaction data, the existing solution involves retrieving all fields of the update type data by connecting to an Oracle slave database. However, due to the latency between the Oracle slave and the master database, and sometimes the latency is quite long, the field completion (reverse lookup) results from connecting to the Oracle slave database are inaccurate. This leads to data anomalies in the OGG incremental data synchronization ODS (Operational Data Store) solution, which in turn affects the overall data quality of the data warehouse. Summary of the Invention

[0005] The purpose of this invention is to provide an incremental data synchronization field completion method, apparatus, electronic device, and computer-readable storage medium to solve the technical problem of inaccurate field completion results in the prior art.

[0006] The technical solution of the present invention is as follows: a method for incremental data synchronization field completion is provided, comprising the following steps:

[0007] Obtain the original data to be completed from the log data, or obtain the original data to be completed from both the log data and the full data, and obtain the primary key field value and non-primary key field value of the original data to be completed, wherein the primary key field value is generated when the original data to be completed is generated;

[0008] The original data to be completed is grouped according to the primary key field value to obtain multiple grouped data. The multiple grouped data are then sorted to obtain multiple sorted grouped data.

[0009] Window functions are used to complete the non-primary key field values ​​in the sorted grouped data to obtain the data with completed fields.

[0010] Furthermore, obtaining the original data to be completed from log data, or obtaining the original data to be completed from both log data and full data, includes: obtaining the original data to be completed from Oracle GoldenGate log data, or obtaining the original data to be completed from both the full data of the data warehouse tool and the log data of Oracle GoldenGate.

[0011] Furthermore, the original data to be completed is grouped according to the primary key field value to obtain multiple grouped data, including: grouping the original data to be completed according to the primary key field value so that the primary key field values ​​in the same group are the same, and the primary key field values ​​in different groups are different, thus obtaining multiple grouped data.

[0012] Further, the step of sorting the multiple groups of data to obtain multiple sorted groups of data includes:

[0013] According to the order of the transaction completion time of the original data to be supplemented, the multiple groups of data are sorted to obtain multiple sorted groups of data.

[0014] Furthermore, after obtaining the primary key field value and non-primary key field value of the original data to be completed, the process also includes:

[0015] Determine whether the string corresponding to the null value in the non-primary key field value includes the non-primary key field name and the null value or only includes the null value. If the string corresponding to the null value in the non-primary key field value includes the non-primary key field name and the null value, then replace the string corresponding to the null value with a string composed of special characters to obtain the non-primary key field value after string replacement.

[0016] Furthermore, window functions are used to complete the non-primary key field values ​​in the multiple sorted grouped data to obtain the data with completed fields, including:

[0017] Using window functions, the non-primary key field values ​​that contain only null strings in the sorted grouped data are filled in, and the non-primary key field values ​​after the string replacement are restored to obtain the data with filled in the fields.

[0018] Furthermore, window functions are used to complete the non-primary key field values ​​that contain only null strings in the sorted grouped data, including:

[0019] Using window functions, the first non-null value of the non-primary key field from the back of the sorted grouped data is obtained. The first non-null value of the non-primary key field is used as the real value and assigned to the corresponding grouped data, including all non-primary key field values ​​that only contain null strings.

[0020] Another technical solution of the present invention is as follows: an incremental data synchronization field completion device is also provided, including a data acquisition module, a data grouping module, and a field completion module;

[0021] The data acquisition module is used to acquire the original data to be completed from log data, or to acquire the original data to be completed from log data and full data, and to acquire the primary key field value and non-primary key field value of the original data to be completed, wherein the primary key field value is generated when the original data to be completed is generated;

[0022] The data grouping module is used to group the original data to be completed according to the primary key field value of the original data to be completed, to obtain multiple grouped data, and to sort the multiple grouped data respectively to obtain multiple sorted grouped data;

[0023] The field completion module is used to complete the non-primary key field values ​​in multiple sorted grouped data using window functions, so as to obtain the data with completed fields.

[0024] Another technical solution of the present invention is as follows: an electronic device is also provided, including a memory and a processor. The memory stores a computer program that can be executed by the processor. When the processor executes the computer program, it implements the incremental data synchronization field completion method as described in any of the above technical solutions.

[0025] Another technical solution of the present invention is as follows: a computer-readable storage medium is also provided, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, it implements the incremental data synchronization field completion method as described in any of the above technical solutions.

[0026] The beneficial effects of this invention are as follows: It obtains the original data to be completed from log data, or from both log data and full data, and acquires the primary key field value and non-primary key field value of the original data to be completed, wherein the primary key field value is generated during the generation of the original data to be completed; it groups the original data to be completed according to the primary key field value, obtaining multiple groups of data, and sorts each of the multiple groups of data, obtaining multiple sorted groups of data; it uses window functions to complete the non-primary key field values ​​in each of the multiple sorted groups of data, obtaining data with completed fields; through the above method, it avoids the delay between the slave and master databases, improves the accuracy of field completion, and thus avoids data anomalies during OGG incremental data synchronization, improving the overall data quality of the data warehouse. Attached Figure Description

[0027] Figure 1 This is a flowchart illustrating the incremental data synchronization field completion method according to an embodiment of the present invention;

[0028] Figure 2 This is a schematic diagram of the incremental data synchronization field completion device according to an embodiment of the present invention;

[0029] Figure 3 This is a schematic diagram of the structure of an electronic device according to an embodiment of the present invention. Detailed Implementation

[0030] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of the present invention, and not all of them. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative effort are within the scope of protection of the present invention.

[0031] In the description of this application, the terms "first," "second," etc., are used only for distinguishing purposes and should not be construed as indicating or implying relative importance or order. In this specification, the terms "comprising," "including," "having," and variations thereof mean "including but not limited to," unless otherwise specifically emphasized.

[0032] In this application, "multiple" refers to two or more. Therefore, "multiple" can also be understood as "at least two". "At least one" can be understood as one or more, such as one, two, or more. For example, "including at least one" means including one, two, or more, and is not limited to which ones are included. For instance, including at least one of A, B, and C could mean including A, B, C, A and B, A and C, B and C, or A and B and C.

[0033] In this document, the term "embodiment" means that a particular feature, structure, or characteristic described in connection with an embodiment may be included in at least one embodiment of the invention. The appearance of this phrase in various places throughout the specification does not necessarily refer to the same embodiment, nor is it a separate or alternative embodiment mutually exclusive with other embodiments. It will be explicitly and implicitly understood by those skilled in the art that the embodiments described herein can be combined with other embodiments.

[0034] Figure 1 This is a flowchart illustrating the incremental data synchronization field completion method according to an embodiment of the present invention. It should be noted that if substantially the same result is obtained, the incremental data synchronization field completion method of the present invention does not necessarily follow the same pattern. Figure 1 The illustrated process sequence is limited. For example... Figure 1 As shown, this incremental data synchronization field completion method mainly includes the following steps:

[0035] S1, obtain the original data to be completed from the log data, or obtain the original data to be completed from the log data and the full data, and obtain the primary key field value and non-primary key field value of the original data to be completed, wherein the primary key field value is generated when the original data to be completed is generated;

[0036] It should be noted that the primary key field values ​​are generally not missing, while the non-primary key field values ​​may be missing.

[0037] S2, group the original data to be completed according to the primary key field value to obtain multiple grouped data, sort the multiple grouped data respectively to obtain multiple sorted grouped data;

[0038] S3, using window functions to complete the non-primary key field values ​​in the multiple sorted grouped data respectively, to obtain the data with completed fields.

[0039] This invention, in its embodiments, obtains the original data to be completed from log data, or from both log data and full data, and acquires the primary key field value and non-primary key field value of the original data to be completed, wherein the primary key field value is generated during the generation of the original data to be completed; the original data to be completed is grouped according to the primary key field value to obtain multiple grouped data, and the multiple grouped data are sorted to obtain multiple sorted grouped data; window functions are used to complete the non-primary key field values ​​in the multiple sorted grouped data to obtain data with completed fields; this avoids the latency between the slave and master databases, improves the accuracy of field completion, and thus avoids data anomalies during OGG incremental data synchronization, thereby improving the overall data quality of the data warehouse.

[0040] In an optional implementation, obtaining the original data to be completed from log data, or obtaining the original data to be completed from log data and full data, includes: obtaining the original data to be completed from Oracle GoldenGate log data, or obtaining the original data to be completed from full data of the data warehouse tool and Oracle GoldenGate log data.

[0041] In one specific embodiment, the original data to be supplemented can be business data such as policy data and transaction data. The original data to be supplemented can be obtained from the log data of Oracle GoldenGate, or from the full data of the data warehouse tool and the log data of Oracle GoldenGate. For example, the previous day's data (snapshot data) can be obtained from the full data of the data warehouse tool Hive, and the data of the current day can be obtained from the log data of Oracle GoldenGate. When incremental synchronization is achieved by operating the log through ogg, the fields that actually need to be supplemented can be the unupdated field data of the update type data in the log data of Oracle GoldenGate. Therefore, the original data to be supplemented needs to include the log data of Oracle GoldenGate.

[0042] In one optional implementation, the original data to be completed is grouped according to the primary key field value to obtain multiple grouped data, including: grouping the original data to be completed according to the primary key field value so that the primary key field values ​​in the same group are the same, and the primary key field values ​​in different groups are different, thus obtaining multiple grouped data.

[0043] In one specific embodiment, some of the original data tables to be completed are shown in Table 1. The data in Table 1 is log data in Oracle GoldenGate.

[0044] Table 1 contains a portion of the original data that needs to be completed.

[0045] current_ts op_type id name address age 2019-01-0112:00:00:00.0001 I 1 Zhang San 2019-01-0112:00:00:01.0001 U 1 China 22 2019-01-0112:00:00:01.0151 U 1 Shenzhen, China

[0046] In Table 1, current_ts represents the transaction completion time of the data operation, op_type represents the data operation type, the primary key field value is the data id, and non-primary key fields can include name, address, and age, etc. The primary key field value (i.e., data id) is generated when the original data to be completed is generated. The primary key field value is fixed and will not be missing, while the non-primary key field values ​​may be missing. Since some of the original data to be completed in Table 1 have the same primary key field value, they belong to the same group. The first row of Table 1 can be the data from the full data of the previous day, and the second and third rows of Table 1 can be the log data in Oracle GoldenGate on the current day.

[0047] In an optional implementation, sorting the multiple groups of data to obtain multiple sorted groups of data includes:

[0048] According to the order of the transaction completion time of the original data to be supplemented, the multiple groups of data are sorted to obtain multiple sorted groups of data.

[0049] In one specific embodiment, the transaction completion time of the original data to be completed is the transaction completion time of the original data to be completed corresponding to the operation. As shown in Table 1, the transaction completion time of the first row of data is 2019-01-01 12:00:00:00.0001, the transaction completion time of the second row of data is 2019-01-01 12:00:00:01.0001, and the transaction completion time of the third row of data is 2019-01-01 12:00:00:01.0151. The first row of data, the second row of data, and the third row of data have been sorted in the order of transaction completion time in Table 1.

[0050] In an optional implementation, after obtaining the primary key field value and non-primary key field value of the original data to be completed, the method further includes:

[0051] Determine whether the string corresponding to the null value in the non-primary key field value includes the non-primary key field name and the null value or only includes the null value. If the string corresponding to the null value in the non-primary key field value includes the non-primary key field name and the null value, then replace the string corresponding to the null value with a string composed of special characters to obtain the non-primary key field value after string replacement.

[0052] In one specific embodiment, the window function `last_value()` is used to complete the non-primary key field values ​​in multiple sorted grouped data. This involves using `last_value()` to perform a reverse lookup to complete the non-primary key field values ​​in the sorted grouped data. Since in business systems, there may be cases where a non-primary key field value in a data table is set to null. In such cases, the string corresponding to the null value includes the corresponding non-primary key field name and the null value. If the window function `last_value()` is used to complete the set null value or to complete other null values ​​(non-set null values, whose strings only contain the null value) without considering the set null value, errors will occur. Therefore, a string composed of special characters can be used to replace the corresponding null value string before using the window function `last_value()` to perform the reverse lookup.

[0053] In practice, when dealing with a non-primary key field named 'name', the business system might use `update tableName setname = null`. This means the business system sets a null value for a non-primary key field named 'name' in the table named 'tableName', essentially setting the snapshot data with a value to null (`setnull`). The string corresponding to a null value in the non-primary key field 'name' would then be "name = null". If the window function `last_value(name, true)` is used for reverse lookup, errors will occur. These errors can manifest as either filling the null value with other non-null values ​​or filling other null values ​​without considering the set null value.

[0054] In another specific embodiment, the Oracle GoldenGate log data (JSON data) can be replaced with a string composed of special characters (e.g., the string "※★※") to replace the "null" in the string "name=null" corresponding to the set null value, thus obtaining a special string (e.g., "name=※★※").

[0055] In an optional implementation, window functions are used to complete the non-primary key field values ​​in the multiple sorted grouped data to obtain data with completed fields, including:

[0056] Using window functions, the non-primary key field values ​​that contain only null strings in the sorted grouped data are filled in, and the non-primary key field values ​​after the string replacement are restored to obtain the data with filled in the fields.

[0057] In one specific embodiment, after replacing the corresponding null value string with a string composed of special characters to obtain the non-primary key field value after string replacement, when using window functions to complete the non-primary key field values ​​in multiple sorted grouped data respectively, these non-primary key field values ​​after string replacement will not be treated as null values, and therefore no completion operation is performed. Only the non-primary key field values ​​containing only null value strings need to be completed, while the non-primary key field values ​​after string replacement need to be restored. This field completion method avoids errors caused by the set null values ​​and can ensure the accuracy of field completion. The timing of restoring the non-primary key field values ​​after string replacement should be no later than before the field-completed data is written to the operation data storage (ods).

[0058] In an optional implementation, window functions are used to complete the non-primary key field values ​​that contain only null strings in the sorted grouped data, including:

[0059] Using window functions, the first non-null value of the non-primary key field from the back of the sorted grouped data is obtained. The first non-null value of the non-primary key field is used as the real value and assigned to the corresponding grouped data, including all non-primary key field values ​​that only contain null strings.

[0060] In one specific embodiment, if the non-primary key field is 'name', the original data to be completed is grouped according to the primary key field value, sorted in ascending order according to the transaction completion time 'current_ts', and then the 'last_value(name, true)' window function is used to complete the 'name' field value if it is null. 'last_value(name, true)' means that within the corresponding group, the first non-null character (field value) is found from bottom to top (from back to front), and this character is assigned the corresponding null value as the real value. For example, if the value of the non-primary key field 'name' in the second row of Table 1 is null, and the first non-null value found is 'Zhang San', then the value of the non-primary key field 'name' in the second row is 'Zhang San'. Similarly, the value of the 'name' field in the third row is 'Zhang San', and so on. The value of the non-primary key field 'age' in the third row is 22, thus obtaining a partially completed data table, as shown in Table 2.

[0061] Table 2 Partial Data Completion Table

[0062] current_ts op_type id name address age 2019-01-0112:00:00:00.0001 I 1 Zhang San 2019-01-0112:00:00:01.0001 U 1 Zhang San China 22 2019-01-0112:00:00:01.0151 U 1 Zhang San Shenzhen, China 22

[0063] The incremental data synchronization field completion method provided in this invention obtains the original data to be completed from log data, or from both log data and full data, and obtains the primary key field value and non-primary key field value of the original data to be completed, wherein the primary key field value is generated when the original data to be completed is generated; the original data to be completed is grouped according to the primary key field value to obtain multiple grouped data, and the multiple grouped data are sorted to obtain multiple sorted grouped data; window functions are used to complete the non-primary key field values ​​in the multiple sorted grouped data to obtain the field-completed data; this method can realize field completion of business data such as policy data and transaction data, avoid the delay between the slave and master databases, improve the accuracy of field completion, and thus avoid data anomalies in OGG incremental data synchronization, thereby improving the overall data quality of the data warehouse.

[0064] It should be noted that in the existing technical solution, due to the latency issue of the slave database, some data in the OGG incremental data may become abnormal, which will eventually lead to the data quality deviation of the data warehouse ODS layer, thus causing certain adverse effects on downstream data backtracking and verification.

[0065] The incremental data synchronization field completion method provided in this invention uses window functions to complete missing field values ​​by replaying data, avoiding reverse lookups from the Oracle slave database. This solves the latency problem that occurs when reverse lookingups from the slave database in the prior art, improves the quality of replay data, improves the accuracy of incremental data merging results, improves the data quality of OGG incremental data synchronization, and improves the data quality of the ODS layer. It can lay a solid foundation for subsequent data warehouse construction and also provide a certain data foundation for subsequent linked tables.

[0066] The incremental data synchronization field completion method provided in this invention can be built on artificial intelligence. It uses AI technology to acquire and process relevant data, achieving unattended incremental data synchronization field completion. Artificial intelligence (AI) is the theory, method, technology, and application system that uses digital computers or machines controlled by digital computers to simulate, extend, and expand human intelligence, perceive the environment, acquire knowledge, and use that knowledge to obtain optimal results.

[0067] Foundational technologies for artificial intelligence generally include sensors, dedicated AI chips, cloud computing, distributed storage, big data processing, operating / interactive systems, and mechatronics. AI software technologies mainly encompass computer vision, robotics, biometrics, speech processing, natural language processing, and machine learning / deep learning.

[0068] Figure 2 This is a schematic diagram of the incremental data synchronization field completion device according to an embodiment of the present invention, as shown below. Figure 2 As shown, the incremental data synchronization field completion device 20 includes a data acquisition module 21, a data grouping module 22, and a field completion module 23;

[0069] The data acquisition module 21 is used to acquire the original data to be completed from log data, or to acquire the original data to be completed from both log data and full data, and to acquire the primary key field value and non-primary key field value of the original data to be completed. The primary key field value is generated when the original data to be completed is generated. It should be noted that the primary key field value is generally not missing, while the non-primary key field value may be missing.

[0070] The data grouping module 22 is used to group the original data to be completed according to the primary key field value of the original data to be completed, to obtain multiple grouped data, and to sort the multiple grouped data respectively to obtain multiple sorted grouped data;

[0071] The field completion module 23 is used to complete the non-primary key field values ​​in the sorted grouped data using window functions to obtain the data with completed fields.

[0072] In this embodiment of the invention, the data acquisition module 21 obtains the original data to be completed from log data, or from both log data and full data, and acquires the primary key field value and non-primary key field value of the original data to be completed, wherein the primary key field value is generated when the original data to be completed is generated; the data grouping module 22 groups the original data to be completed according to the primary key field value, obtaining multiple grouped data, and sorts each of the multiple grouped data to obtain multiple sorted grouped data; the field completion module 23 uses window functions to complete the non-primary key field values ​​in the multiple sorted grouped data to obtain the field-completed data; this avoids the delay between the slave and master databases, improves the accuracy of field completion, and thus avoids data anomalies during OGG incremental data synchronization, thereby improving the overall data quality of the data warehouse.

[0073] In an optional implementation, the data acquisition module 21 acquires the original data to be completed from log data, or acquires the original data to be completed from both log data and full data, including: acquiring the original data to be completed from Oracle GoldenGate log data, or acquiring the original data to be completed from both the full data of the data warehouse tool and the log data of Oracle GoldenGate.

[0074] In one specific embodiment, the original data to be completed can be obtained from the Oracle GoldenGate log data, or from the full data of the data warehouse tool and the Oracle GoldenGate log data. When performing incremental synchronization by operating the logs through ogg, the fields that actually need to be completed can be the unupdated field data of the update type data in the Oracle GoldenGate log data. Therefore, the original data to be completed needs to include the Oracle GoldenGate log data.

[0075] In an optional implementation, the data grouping module 22 groups the original data to be completed according to the primary key field value of the original data to be completed, and obtains multiple grouped data, including: grouping the original data to be completed according to the primary key field value of the original data to be completed, such that the primary key field values ​​in the same group are the same, and the primary key field values ​​in different groups are different, and obtains multiple grouped data.

[0076] In an optional implementation, the data grouping module 22 sorts the multiple grouped data respectively to obtain multiple sorted grouped data, including:

[0077] According to the order of the transaction completion time of the original data to be supplemented, the multiple groups of data are sorted to obtain multiple sorted groups of data.

[0078] In an optional implementation, the incremental data synchronization field completion device 20 further includes a string replacement module. The string replacement module is used to determine, after obtaining the primary key field value and non-primary key field value of the original data to be completed, whether the string corresponding to the null value in the non-primary key field value includes the non-primary key field name and the null value or only includes the null value. If the string corresponding to the null value in the non-primary key field value includes the non-primary key field name and the null value, then the string corresponding to the null value is replaced with a string composed of special characters to obtain the non-primary key field value after string replacement.

[0079] In one specific embodiment, the window function `last_value()` is used to complete the non-primary key field values ​​in multiple sorted grouped data. This involves using `last_value()` to perform a reverse lookup to complete the non-primary key field values ​​in the sorted grouped data. Since in business systems, there may be cases where a non-primary key field value in a data table is set to null. In such cases, the string corresponding to the null value includes the corresponding non-primary key field name and the null value. If the window function `last_value()` is used to complete the set null value or to complete other null values ​​(non-set null values, whose strings only contain the null value) without considering the set null value, errors will occur. Therefore, a string composed of special characters can be used to replace the corresponding null value string before using the window function `last_value()` to perform the reverse lookup.

[0080] In practice, when dealing with a non-primary key field named 'name', the business system might use `update tableName setname = null`. This means the business system sets a null value for a non-primary key field named 'name' in the table named 'tableName', essentially setting the snapshot data with a value to null (`setnull`). The string corresponding to a null value in the non-primary key field 'name' would then be "name = null". If the window function `last_value(name, true)` is used for reverse lookup, errors will occur. These errors can manifest as either filling the null value with other non-null values ​​or filling other null values ​​without considering the set null value.

[0081] In another specific embodiment, the Oracle GoldenGate log data (JSON data) can be replaced with a string composed of special characters (e.g., the string "※★※") to replace the "null" in the string "name=null" corresponding to the set null value, thus obtaining a special string (e.g., "name=※★※").

[0082] In an optional implementation, the field completion module 23 uses window functions to complete the non-primary key field values ​​in multiple sorted grouped data to obtain field-completed data, including: using window functions to complete the non-primary key field values ​​in multiple sorted grouped data that only include null strings, and restoring the non-primary key field values ​​after string replacement to obtain field-completed data.

[0083] In one specific embodiment, after replacing the corresponding null value string with a string composed of special characters to obtain the non-primary key field value after string replacement, when using window functions to complete the non-primary key field values ​​in multiple sorted grouped data respectively, these non-primary key field values ​​after string replacement will not be treated as null values, and therefore no completion operation is performed. Only the non-primary key field values ​​that only include null value strings need to be completed, while the non-primary key field values ​​after string replacement need to be restored. This field completion method avoids errors caused by the set null values ​​and can ensure the accuracy of field completion.

[0084] In an optional implementation, the field completion module 23 uses a window function to complete the non-primary key field values ​​that only include null strings in the multiple sorted grouped data. The module further includes: using a window function to obtain the first non-null non-primary key field value from the end of the multiple sorted grouped data, using the first non-null non-primary key field value as the true value, and assigning the true value to all non-primary key field values ​​that only include null strings in the corresponding grouped data.

[0085] In one specific implementation, if the non-primary key field is name, the original data to be completed is grouped according to the primary key field value, sorted in ascending order according to the transaction completion time current_ts, and then the last_value(name, true) window function is used to complete the null value of the name field. last_value(name, true) means that in the corresponding group, the first non-null character (field value) is found from bottom to top (from back to front), and that character will be assigned the corresponding null value as the real value.

[0086] The incremental data synchronization field completion device provided in this embodiment of the invention obtains the original data to be completed from log data, or from log data and full data, through a data acquisition module 21, and obtains the primary key field value and non-primary key field value of the original data to be completed, wherein the primary key field value is generated when the original data to be completed is generated; the data grouping module 22 groups the original data to be completed according to the primary key field value of the original data to be completed, obtaining multiple grouped data, and sorts the multiple grouped data respectively, obtaining multiple sorted grouped data; the field completion module 23 uses window functions to complete the non-primary key field values ​​in the multiple sorted grouped data respectively, obtaining the field-completed data; this avoids the delay between the slave and master databases, improves the accuracy of field completion, and thus avoids data anomalies in OGG incremental data synchronization, improving the overall data quality of the data warehouse.

[0087] In existing technical solutions, due to the latency issue of the slave database, some data in the OGG incremental data may become abnormal, which will eventually lead to data quality deviation in the data warehouse ODS layer, thus causing certain adverse effects on downstream data backtracking and verification.

[0088] The incremental data synchronization field completion device provided in this invention uses window functions to complete missing field values ​​by replaying data, avoiding reverse lookups from the Oracle slave database. This solves the latency problem that occurs when reverse lookups from the slave database in the prior art, improves the quality of replaying data, improves the accuracy of incremental data merging results, improves the data quality of OGG incremental data synchronization, and improves the data quality of the ODS layer. It can lay a solid foundation for subsequent data warehouse construction and also provide a certain data foundation for subsequent linked tables.

[0089] Figure 3 This is a schematic diagram of the structure of an electronic device according to an embodiment of the present invention. Figure 3 As shown, the electronic device 30 includes a processor 31 and a memory 32 communicatively connected to the processor 31.

[0090] The memory 32 stores program instructions for implementing the incremental data synchronization field completion method of any of the above embodiments.

[0091] The processor 31 is used to execute program instructions stored in the memory 32 to perform incremental data synchronization field completion.

[0092] The processor 31 can also be referred to as a CPU (Central Processing Unit). The processor 31 may be an integrated circuit chip with signal processing capabilities. The processor 31 can also be a general-purpose processor, a digital signal processor (DSP), an application-specific integrated circuit (ASIC), a field-programmable gate array (FPGA), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components. A general-purpose processor can be a microprocessor or any conventional processor.

[0093] This invention provides a storage medium that stores program instructions capable of implementing all the methods described above. The storage medium can be non-volatile or volatile. These program instructions can be stored in the storage medium as a software product, including several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) or processor to execute all or part of the steps of the methods described in the various embodiments of this invention. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks, or terminal devices such as computers, servers, mobile phones, and tablets.

[0094] In the several embodiments provided by this invention, it should be understood that the disclosed apparatus and methods can be implemented in other ways. For example, the apparatus embodiments described above are merely illustrative; for instance, the division of modules is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple modules or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be through some interfaces; the indirect coupling or communication connection between apparatuses or modules may be electrical, mechanical, or other forms.

[0095] Furthermore, the functional modules in the various embodiments of the present invention can be integrated into one processing unit, or each module can exist physically separately, or two or more modules can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit. The above are merely embodiments of the present invention and do not limit the patent scope of the present invention. Any equivalent structural or procedural transformations made based on the description and drawings of the present invention, or direct or indirect applications in other related technical fields, are similarly included within the patent protection scope of the present invention.

[0096] The above description is merely an embodiment of the present invention. It should be noted that those skilled in the art can make improvements without departing from the inventive concept of the present invention, but these improvements all fall within the protection scope of the present invention.

Claims

1. A method for incremental data synchronization field completion, characterized in that, Includes the following steps: The original data to be completed is obtained from the log data, or from the log data and the full data. The primary key field value and the non-primary key field value of the original data to be completed are obtained. It is determined whether the string corresponding to the null value in the non-primary key field value includes the non-primary key field name and the null value or only includes the null value. If the string corresponding to the null value in the non-primary key field value includes the non-primary key field name and the null value, the string corresponding to the null value is replaced with a string composed of special characters to obtain the non-primary key field value after string replacement. The primary key field value is generated when the original data to be completed is generated. The original data to be completed is grouped according to the primary key field value to obtain multiple grouped data. The multiple grouped data are then sorted to obtain multiple sorted grouped data. Window functions are used to complete the non-primary key field values ​​in the sorted grouped data to obtain the data with completed fields.

2. The incremental data synchronization field completion method according to claim 1, characterized in that, The step of obtaining the original data to be completed from log data, or obtaining the original data to be completed from log data and full data, includes: obtaining the original data to be completed from Oracle GoldenGate log data, or obtaining the original data to be completed from the full data of the data warehouse tool and the log data of Oracle GoldenGate.

3. The incremental data synchronization field completion method according to claim 1, characterized in that, The original data to be completed is grouped according to the primary key field value to obtain multiple grouped data, including: grouping the original data to be completed according to the primary key field value so that the primary key field value in the same group is the same, and the primary key field value in different groups is different, thus obtaining multiple grouped data.

4. The incremental data synchronization field completion method according to claim 1, characterized in that, The step of sorting the multiple groups of data to obtain multiple sorted groups of data includes: According to the order of the transaction completion time of the original data to be supplemented, the multiple groups of data are sorted to obtain multiple sorted groups of data.

5. The incremental data synchronization field completion method according to claim 1, characterized in that, Using window functions, the non-primary key field values ​​in the multiple sorted grouped data are completed to obtain the data with completed fields, including: Using window functions, the non-primary key field values ​​that contain only null strings in the sorted grouped data are filled in, and the non-primary key field values ​​after the string replacement are restored to obtain the data with filled in the fields.

6. The incremental data synchronization field completion method according to claim 5, characterized in that, Using window functions to complete the non-primary key field values ​​that contain only null strings in multiple sorted grouped data sets, the method also includes: Using window functions, the first non-null value of a non-primary key field from the back of the sorted grouped data is obtained. The first non-null value of a non-primary key field is used as the real value, and the real value is assigned to all non-primary key field values ​​in the corresponding grouped data that contain only null strings.

7. An incremental data synchronization field completion device, characterized in that, It includes a data acquisition module, a data grouping module, and a field completion module; The data acquisition module is used to acquire the original data to be completed from log data, or to acquire the original data to be completed from both log data and full data, and to acquire the primary key field value and non-primary key field value of the original data to be completed. It determines whether the string corresponding to the null value in the non-primary key field value includes the non-primary key field name and the null value or only includes the null value. If the string corresponding to the null value in the non-primary key field value includes the non-primary key field name and the null value, it replaces the string corresponding to the null value with a string composed of special characters to obtain the non-primary key field value after string replacement. The primary key field value is generated when the original data to be completed is generated. The data grouping module is used to group the original data to be completed according to the primary key field value of the original data to be completed, to obtain multiple grouped data, and to sort the multiple grouped data respectively to obtain multiple sorted grouped data; The field completion module is used to complete the non-primary key field values ​​in multiple sorted grouped data using window functions, so as to obtain the data with completed fields.

8. An electronic device, comprising a memory and a processor, wherein the memory stores a computer program executable by the processor, characterized in that, When the processor executes the computer program, it implements the incremental data synchronization field completion method as described in any one of claims 1 to 6.

9. A computer-readable storage medium storing a computer program, characterized in that, When the computer program is executed by the processor, it implements the incremental data synchronization field completion method as described in any one of claims 1 to 6.

Images