Method and system for realizing synchronous updating of heterogeneous data sources based on chdb

Abstract

The invention discloses a method and system for synchronously updating heterogeneous data sources based on chdb, and the method comprises the steps: carrying out the data grouping of the data of a source end and a target end through employing the same data grouping strategy; the method comprises the following steps: converting a primary key field of data into a hash value, and performing modular operation grouping; loading the data of the source end and the target end of each group, and converting the data into a DataFrame format of Python Pandas; performing CHDB table registration on the DataFrame format data of the source end and the target end of each group, executing SQL statements of SQL all-external connection, and identifying deletion, updating, insertion or no change of the data; and executing corresponding data operation on the target data source according to the comparison result. According to the method and the system, the source data and the target data are loaded into the chdb for quick comparison, records needing to be newly added, updated and deleted are identified, and then corresponding data operation is executed, so that accurate synchronization between heterogeneous data sources is realized, and the method and the system are suitable for a large-scale data processing scene.

Description

Technical Field

[0001] The present invention relates to the field of data synchronization, and in particular to a method and system for realizing synchronous update of heterogeneous data sources based on chdb. Background Art

[0002] In the data synchronization scenario, the challenge is how to ensure data consistency between the data source and the target storage while maintaining business continuity. The data source stores a complete data set, and our task is to regularly update this data to the target storage, which may be a database, Elasticsearch, Redis, or other types of storage systems.

[0003] To achieve this goal, the following two synchronization strategies are usually adopted:

[0004] Strategy 1: Full replacement synchronization

[0005] First, clear the existing data in the target storage, and then perform a full data synchronization. This method is simple and direct, but it has obvious disadvantages: during the data synchronization process, the target storage will temporarily be unable to provide data services, which may seriously affect the normal operation of the business when the data volume is large and the synchronization takes a long time.

[0006] Strategy 2: Incremental synchronization

[0007] Incremental synchronization, which involves a detailed comparison of the data on the data source and the target storage. Identify the data items that need to be deleted, updated, or inserted. Specifically, if the data exists in the target storage but not on the data source, it is marked as deleted; conversely, if the data exists on the data source but not on the target storage, it is marked as inserted; if the data exists in both but the content is different, it is marked as updated.

[0008] However, since the data source and target storage may use different storage technologies, it may be complicated to compare such heterogeneous data. Although big data processing frameworks such as Spark and Flink can be used to handle such comparisons, these tools consume large amounts of resources and may not be suitable for lightweight scenarios with limited resources or high performance requirements.

[0009] When optimizing the data synchronization process, we should consider introducing a more efficient incremental synchronization mechanism, and at the same time evaluate whether it is necessary to use a big data processing framework, or find a solution that is more suitable for lightweight scenarios, so as to achieve efficient data synchronization and consistency maintenance without sacrificing business continuity. Summary of the invention

[0010] In order to solve the above problems existing in the prior art, the present invention provides a method and system for realizing synchronous update of heterogeneous data sources based on chdb, and utilizes the high-performance analysis capability of chdb to realize efficient data synchronization and update between heterogeneous data sources. The method and system load the source data and the target data into chdb for rapid comparison, identify the records that need to be added, updated and deleted, and then perform the corresponding data operations, thereby realizing accurate synchronization between heterogeneous data sources, and is suitable for large-scale data processing scenarios.

[0011] To achieve the above purpose, the present invention adopts the following technical solutions:

[0012] In one embodiment of the present invention, a method for implementing synchronous update of heterogeneous data sources based on chdb is proposed, and the method includes:

[0013] Use the same data grouping strategy to group the data on the source and target ends; convert the primary key field of the data into a hash value and perform a modulo operation to group the data;

[0014] Load the source and target data of each group and convert them into Python Pandas DataFrame format;

[0015] Register the CHDB table for the DataFrame format data of the source and target ends of each group, and execute the SQL statement of SQL full outer join to identify the deletion, update, insertion or no change of data;

[0016] Perform corresponding data operations on the target data source based on the comparison results.

[0017] Furthermore, the data grouping strategy is as follows:

[0018] Based on the data source type, data is divided into relational and non-relational databases;

[0019] For relational databases, use SQL statements and Hash functions for grouping;

[0020] For non-relational databases, use data source API and programming logic for grouping.

[0021] Furthermore, the SQL full outer join performs a full comparison by associating the primary key fields of the source table and the target table. The comparison logic is as follows:

[0022] Data records that exist in the target table but not in the source table are marked as deleted;

[0023] If the data record does not exist in the target table but exists in the source table, it is marked as newly added;

[0024] The data record exists in both tables, but the field values ​​are different and marked as updated;

[0025] Data records exist in both tables and have the same field values, so they are marked as unchanged.

[0026] In one embodiment of the present invention, a system for implementing synchronous update of heterogeneous data sources based on chdb is also proposed, and the system includes:

[0027] The data grouping module is used to group the data at the source and target ends using the same data grouping strategy; the primary key field of the data is converted into a hash value and grouped by performing a modulo operation;

[0028] The data loading module is used to load the data from the source and target ends of each group and convert them into the Python Pandas DataFrame format;

[0029] The data identification module is used to register the CHDB table for the DataFrame format data of the source and target ends of each group, and execute the SQL statement of SQL full outer join to identify the deletion, update, insertion or no change of data;

[0030] The data operation module is used to perform corresponding data operations on the target data source according to the comparison results.

[0031] Furthermore, the data grouping strategy is as follows:

[0032] Based on the data source type, data is divided into relational and non-relational databases;

[0033] For relational databases, use SQL statements and Hash functions for grouping;

[0034] For non-relational databases, use data source API and programming logic for grouping.

[0035] Furthermore, the SQL full outer join performs a full comparison by associating the primary key fields of the source table and the target table. The comparison logic is as follows:

[0036] Data records that exist in the target table but not in the source table are marked as deleted;

[0037] If the data record does not exist in the target table but exists in the source table, it is marked as newly added;

[0038] The data record exists in both tables, but the field values ​​are different and marked as updated;

[0039] Data records exist in both tables and have the same field values, so they are marked as unchanged.

[0040] In one embodiment of the present invention, a computer device is also proposed, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, the aforementioned method for synchronously updating heterogeneous data sources based on chdb is designed.

[0041] In one embodiment of the present invention, a computer-readable storage medium is further provided, wherein the computer-readable storage medium stores a computer program for executing a method for synchronously updating heterogeneous data sources based on chdb.

[0042] Beneficial effects:

[0043] 1. The present invention uses the chdb high-performance memory database to perform data comparison and improve comparison efficiency.

[0044] 2. The present invention introduces a data grouping mechanism to solve the memory overflow problem caused by large amounts of data.

[0045] 3. The present invention combines Python Pandas and chdb to achieve flexible data processing and efficient SQL operations.

[0046] 4. The present invention supports multiple heterogeneous data sources, such as relational databases, Elasticsearch, Redis, etc. BRIEF DESCRIPTION OF THE DRAWINGS

[0047] Figure 1 It is a flow chart of a method for realizing synchronous update of heterogeneous data sources based on chdb of the present invention;

[0048] Figure 2 It is a schematic diagram of the system structure of realizing synchronous update of heterogeneous data sources based on chdb of the present invention;

[0049] Figure 3 It is a schematic diagram of the computer device structure of the present invention. DETAILED DESCRIPTION

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

[0051] Those skilled in the art will appreciate that the embodiments of the present invention may be designed as a system, system, device, method or computer program product. Therefore, the present disclosure may be specifically designed in the following forms, namely: complete hardware, complete software (including firmware, resident software, microcode, etc.), or a combination of hardware and software.

[0052] According to the implementation mode of the present invention, a method for realizing synchronization and update of heterogeneous data sources based on chdb is proposed. The challenge faced by full data synchronization and update is to realize efficient and accurate data synchronization between the data source and the target storage. The data source maintains the full data, and the target storage may include a variety of storage systems such as database, Elasticsearch, and Redis. Chdb is used to register the data tables of the data source and the target, and SQL statements are used to compare data associations.

[0053] chdb is a lightweight embedded database module designed for resource-constrained environments and application scenarios that require fast response. It integrates ClickHouse's high-performance vectorization engine, supports standard SQL syntax, is highly scalable, supports user-defined functions and aggregate functions, and optimizes memory usage through memory mapping and zero-copy technology. In addition, chdb also supports cross-language integration and Pandas DataFrame, providing seamless and efficient data processing capabilities for data analysis and machine learning tasks.

[0054] Through comparison, data that does not exist in the target storage is marked as newly added, data that does not exist on the data source end is marked as deleted, and data that exists on both ends but has inconsistent content is marked as updated.

[0055] The principle and spirit of the present invention are explained in detail below with reference to several representative embodiments of the present invention.

[0056] Figure 1 This is a flow chart of the method for implementing synchronous update of heterogeneous data sources based on chdb of the present invention. Figure 1 As shown, the method includes:

[0057] 1. Start: The starting point of the process.

[0058] 2. Data grouping: According to the data source type, the data is divided into relational data and other data source groups such as ES.

[0059] In view of the memory overflow problem that chdb may encounter when processing large-scale data, a data grouping strategy is introduced to group the data on the data source and target ends, and use the same data grouping strategy. By converting the comparison field (usually the primary key) into a hash value and performing a modular operation grouping, the data volume can be effectively dispersed and the memory pressure can be reduced.

[0060] 3. Relational database grouping: Use SQL statements and Hash functions to group data.

[0061] 4. Grouping of other data sources such as ES: Use data source API and programming logic to implement data grouping.

[0062] 5. Traverse each group: Traverse the groups and use the source and target data with the same group ID as a pair of data for comparison.

[0063] 6. Load the same packet data at the source and target: Load the packet data of each pair of source and target.

[0064] 7. Load the grouped data on the source and target ends into DataFrame format: Convert the data into Python Pandas' DataFrame format for subsequent processing.

[0065] 8. Register the grouped data on the source and target ends as CHDB tables: Register the DataFrame as a CHDB table.

[0066] 9. Full outer join, identify new, updated, deleted and unchanged data: Use SQL statements to compare data and identify data differences. Based on the comparison results, determine whether the data is new, updated, deleted or unchanged.

[0067] 10. Perform data synchronization operations: Perform corresponding data operations on the target data source based on the identified data operation type.

[0068] 11. End: The end point of the data synchronization process.

[0069] It should be noted that, although the operations of the method of the present invention are described in a specific order in the above embodiments and the accompanying drawings, this does not require or imply that the operations must be performed in the specific order, or that all the operations shown must be performed to achieve the desired results. Additionally or alternatively, some steps may be omitted, multiple steps may be combined into one step, and / or one step may be decomposed into multiple steps.

[0070] In order to explain more clearly the above-mentioned method for realizing synchronous update of heterogeneous data sources based on chdb, a specific embodiment is described below. However, it is worth noting that this embodiment is only for better illustrating the present invention and does not constitute an improper limitation to the present invention.

[0071] Example:

[0072] 1. Data Grouping

[0073] Data grouping is designed to improve the efficiency and stability of data comparison, especially when dealing with large data sets. By distributing data into multiple groups for comparison, we can avoid the problem of memory overflow caused by excessive data volume in a single process.

[0074] CHDB provides excellent performance, but when faced with massive data, the risk of memory overflow may cause the comparison process to be interrupted. To overcome this challenge, we adopted the following strategies:

[0075] (1) Grouping based on primary key fields: Group the data using the primary key fields, convert them into hash values, and evenly distribute the data to different groups through modulo operations (for example, modulo 3).

[0076] (2) Grouping and unique group ID: Each group is assigned a unique group ID to ensure that data items with the same hash value are classified into the same group, thereby simplifying the subsequent data processing process.

[0077] (3) Adapting to different data sources: Regardless of whether the data source is MySQL, Elasticsearch, or Redis, we use data source-specific APIs to read data and apply hash grouping technology to optimize memory usage.

[0078] (4) Structured data loading: Through grouping, we provide a structured dataset for data loading and recognition, which not only improves memory efficiency but also simplifies the data processing process.

[0079] Data grouping strategy:

[0080] According to the type of data source, select the corresponding API to read data.

[0081] Relational database grouping: For relational data sources, such as ClickHouse, we use SQL statements combined with Hash functions to group. Taking the table pm_flux as an example, we use the cityHash64 function to hash the devid field and divide it into N shards based on the modulo operation result. The following is an example of the specific operation:

[0082] Group 0:

[0083] sql

[0084] SELECT data_time,devid,in_flow,out_flow

[0085] FROM pm_flux

[0086] WHERE cityHash64(devid)%3=0;

[0087] Group 1:

[0088] sql

[0089] SELECT data_time,devid,in_flow,out_flow

[0090] FROM pm_flux

[0091] WHERE cityHash64(devid)%3=1;

[0092] Group 2:

[0093] sql

[0094] SELECT data_time,devid,in_flow,out_flow

[0095] FROM pm_flux

[0096] WHERE cityHash64(devid)%3=2;

[0097] After executing the above SQL statements, the obtained sub-result sets are merged to form the final grouped data set.

[0098] Non-relational database grouping: For non-relational databases such as Elasticsearch, we use scrollAPI to achieve efficient reading of large-scale data. During the reading process, data hash grouping is implemented through programming logic to ensure effective management of data in memory. Scroll API is a tool used by Elasticsearch to efficiently obtain large amounts of data. It creates a data snapshot and then reads it in batches, using cursors to track progress. This method avoids memory issues caused by loading large amounts of data at once and maintains data consistency during queries. Scroll API is particularly suitable for scenarios that require processing massive amounts of data, such as data migration and full-volume analysis. It is like reading a long scroll, reading only a small part each time until the entire content is read.

[0099] This differentiated data grouping method can adapt to the characteristics of different data sources, while optimizing the data processing process, improving efficiency and reducing resource consumption.

[0100] 2. Data loading

[0101] Receive grouping information, and then load the corresponding grouping data of the source and target ends according to this information. Traverse the groups and treat the source and target data with the same group ID as a pair of data for comparison. Load each pair of source and target grouping data and convert the data into a format suitable for further processing, such as Python Pandas' DataFrame, to ensure that the data identification module can efficiently process and analyze structured data sets.

[0102] For example, for group 0, convert the data on the source and target ends into Python Pandas DataFrames respectively.

[0103]

[0104] 3. Data Identification

[0105] The core function of data identification is to receive source and target group data and register it as a CHDB table to achieve efficient SQL comparison. By executing SQL statements with full outer joins, it accurately identifies data records that need to be deleted, updated, inserted, or unchanged, and generates corresponding data operation instructions, providing accurate execution basis for data operations and ensuring the accuracy and efficiency of the data synchronization process.

[0106] SQL full outer join compares the primary key fields of the source table and the target table. The comparison logic is as follows:

[0107] (1) Data records that exist in the target table but not in the source table are marked as deleted.

[0108] (2) If the data record does not exist in the target table but exists in the source table, it is marked as newly added.

[0109] (3) The data record exists in both tables, but the field values ​​are different and are marked as updated.

[0110] (4) Data records exist in both tables and have the same field values, so they are marked as unchanged.

[0111] Load the data into the generated DataFrame registry, associate the SQL for comparison, and execute it, as shown in the following example:

[0112]

[0113]

[0114] Shows sample data for two tables, one for the source table source_table and the other for the target table target_table. The following is the sample data:

[0115] The sample data of the source table (source_table) is as follows in Table 1:

[0116] Table 1

[0117]

[0118] The sample data of the target table (target_table) is shown in Table 2 below:

[0119] Table 2

[0120]

[0121] The query result sample data is shown in Table 3 below:

[0122] Table 3

[0123]

[0124] In the above sample results:

[0125] devid1 exists in both tables, but the data time is different, indicating that the data has been updated.

[0126] devid2 also exists in both tables, with the same data time, but different in_flow and out_flow, which also indicates that the data has been updated.

[0127] devid3 is only in the source table, indicating that this is a newly added record.

[0128] devid4 is only in the target table, indicating that this record has been deleted in the source table.

[0129] 4. Data Operation

[0130] The function and role of data operation is to receive precise operation instructions generated by data recognition, including adding, deleting, and updating, etc. According to these instructions, corresponding data operations are performed on the target data source to ensure that the consistency and accuracy of the data are maintained, thereby completing the final step of data synchronization.

[0131] Based on the same inventive concept, the present invention also proposes a system for implementing synchronous update of heterogeneous data sources based on chdb. The implementation of the system can refer to the implementation of the above method, and the repeated parts will not be repeated. The term "module" used below can be a combination of software and / or hardware designed for a predetermined function. Although the system described in the following embodiments is preferably designed with software, the design of hardware, or a combination of software and hardware is also possible and conceived.

[0132] Figure 2 This is a schematic diagram of the system structure of the present invention based on chdb to achieve synchronous update of heterogeneous data sources. Figure 2 As shown, the system includes:

[0133] The data grouping module 101 is used to group the data at the source end and the target end using the same data grouping strategy; by converting the primary key field of the data into a hash value and performing a modular operation to group the data;

[0134] In view of the memory overflow problem that chdb may encounter when processing large-scale data, a data grouping strategy is introduced to group the data on the data source and target ends, and use the same data grouping strategy. By converting the comparison field (usually the primary key) into a hash value and performing a modular operation grouping, the data volume can be effectively dispersed and the memory pressure can be reduced.

[0135] The data grouping strategy is as follows:

[0136] Based on the data source type, data is divided into relational and non-relational databases;

[0137] For relational databases, use SQL statements and Hash functions for grouping;

[0138] For non-relational databases, use data source API and programming logic for grouping.

[0139] The data loading module 102 is used to load the data of the source and target ends of each group and convert them into the DataFrame format of Python Pandas for subsequent processing.

[0140] The data identification module 103 is used to register the source and target DataFrame format data of each group in the CHDB table, and execute the SQL statement of the SQL full outer join to identify the deletion, update, insertion or no change of the data;

[0141] SQL full outer join compares the primary key fields of the source table and the target table. The comparison logic is as follows:

[0142] Data records that exist in the target table but not in the source table are marked as deleted;

[0143] If the data record does not exist in the target table but exists in the source table, it is marked as newly added;

[0144] The data record exists in both tables, but the field values ​​are different and marked as updated;

[0145] Data records exist in both tables and have the same field values, so they are marked as unchanged.

[0146] The data operation module 104 is used to perform corresponding data operations on the target data source according to the comparison result.

[0147] It should be noted that although several modules of the system for implementing synchronous update of heterogeneous data sources based on chdb are mentioned in the above detailed description, this division is only exemplary and not mandatory. In fact, according to an embodiment of the present invention, the features and functions of two or more modules described above can be concretized in one module. Conversely, the features and functions of one module described above can be further divided into multiple modules for concretization.

[0148] Based on the above invention concept, Figure 3 As shown, the present invention also proposes a computer device 200, including a memory 210, a processor 220, and a computer program 230 stored in the memory 210 and executable on the processor 220. When the processor 220 executes the computer program 230, the aforementioned method for synchronously updating heterogeneous data sources based on chdb is designed.

[0149] Based on the aforementioned inventive concept, the present invention further proposes a computer-readable storage medium, which stores a computer program for executing the aforementioned method for synchronously updating heterogeneous data sources based on chdb.

[0150] The method and system for realizing synchronous update of heterogeneous data sources based on chdb proposed in the present invention have the following highlights:

[0151] 1. Use chdb high-performance memory database for data comparison to improve comparison efficiency.

[0152] 2. Introduce a data grouping mechanism to solve the memory overflow problem caused by large amounts of data.

[0153] 3. Combine Python Pandas and chdb to achieve flexible data processing and efficient SQL operations.

[0154] 4. Support multiple heterogeneous data sources, such as relational databases, Elasticsearch, Redis, etc.

[0155] Although the spirit and principle of the present invention have been described with reference to several specific embodiments, it should be understood that the present invention is not limited to the disclosed specific embodiments, and the division of various aspects does not mean that the features in these aspects cannot be combined to benefit, and such division is only for the convenience of expression. The present invention is intended to cover various modifications and equivalent arrangements contained in the spirit and scope of the attached claims.

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

Claims

1. A method for synchronously updating heterogeneous data sources based on chdb, characterized in that: The method includes: Use the same data grouping strategy to group the data on the source and target ends; convert the primary key field of the data into a hash value and perform a modulo operation to group the data; Load the source and target data of each group and convert them into Python Pandas DataFrame format; Register the CHDB table for the DataFrame format data of the source and target ends of each group, and execute the SQL statement of SQL full outer join to identify the deletion, update, insertion or no change of data; Perform corresponding data operations on the target data source based on the comparison results.

2. The method for realizing synchronous update of heterogeneous data sources based on chdb according to claim 1 is characterized in that: The data grouping strategy is as follows: Based on the data source type, data is divided into relational and non-relational databases; For relational databases, use SQL statements and Hash functions for grouping; For non-relational databases, use data source API and programming logic for grouping.

3. The method for realizing synchronous update of heterogeneous data sources based on chdb according to claim 1 is characterized in that: The SQL full outer join performs a full comparison by associating the primary key fields of the source table and the target table. The comparison logic is as follows: Data records that exist in the target table but not in the source table are marked as deleted; If the data record does not exist in the target table but exists in the source table, it is marked as newly added; The data record exists in both tables, but the field values ​​are different and marked as updated; Data records exist in both tables and have the same field values, so they are marked as unchanged.

4. A system for synchronously updating heterogeneous data sources based on chdb, characterized in that: The system includes: The data grouping module is used to group the data at the source and target ends using the same data grouping strategy; the primary key field of the data is converted into a hash value and grouped by performing a modulo operation; The data loading module is used to load the data of the source and target ends of each group and convert them into the Python Pandas DataFrame format; The data identification module is used to register the CHDB table for the DataFrame format data of the source and target ends of each group, and execute the SQL statement of SQL full outer join to identify the deletion, update, insertion or no change of data; The data operation module is used to perform corresponding data operations on the target data source according to the comparison results.

5. The system for implementing synchronous update of heterogeneous data sources based on chdb according to claim 4 is characterized in that: The data grouping strategy is as follows: Based on the data source type, data is divided into relational and non-relational databases; For relational databases, use SQL statements and Hash functions for grouping; For non-relational databases, use data source API and programming logic for grouping.

6. The system for implementing synchronous update of heterogeneous data sources based on chdb according to claim 4 is characterized in that: The SQL full outer join performs a full comparison by associating the primary key fields of the source table and the target table. The comparison logic is as follows: Data records that exist in the target table but not in the source table are marked as deleted; If the data record does not exist in the target table but exists in the source table, it is marked as newly added; The data record exists in both tables, but the field values ​​are different and marked as updated; Data records exist in both tables and have the same field values, so they are marked as unchanged.

7. A computer device comprising a memory, a processor and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the computer program, the method according to any one of claims 1 to 3 is implemented.

8. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program for executing the method according to any one of claims 1 to 3.

Images