Data real-time warehousing processing method, device and equipment for heterogeneous database

Abstract

The invention provides a data real-time warehousing processing method, device and equipment for heterogeneous databases. The method comprises the following steps: acquiring configuration information tables and synchronization item configuration tables of at least two heterogeneous source databases; obtaining a synchronization item field record table according to the configuration information table and the synchronization item configuration table; generating a storage table according to the synchronization item field record table; receiving to-be-synchronized data respectively sent by the at least two heterogeneous source databases through the independent threads respectively started by the at least two heterogeneous source databases; initializing the storage table according to the to-be-synchronized data to obtain an initial storage table; performing data synchronization on the initial storage table according to the to-be-synchronized data to generate a data warehouse table; and when the configuration information tables or the synchronization item configuration tables of the at least two heterogeneous source databases are changed, updating the data warehouse table to obtain an updated data warehouse table. According to the scheme, data source change without code change can be realized by changing configuration.

Description

Technical Field

[0001] The present invention relates to the field of data processing technology, and in particular to a method, device and equipment for real-time warehousing of data in a heterogeneous database. Background Art

[0002] With the diversification of corporate groups' businesses and the continuous expansion of their operating scale, in order to be closer to the market, optimize resource allocation and meet regional regulatory requirements, group headquarters usually establish multiple subsidiaries, branches or branches in different regions. While this group operation model brings economies of scale and market advantages, it also inevitably introduces severe challenges such as data decentralization, heterogeneity and complex management and control.

[0003] Traditional cross-system data integration methods often involve lengthy processes and fixed cycles (e.g., T+1 or longer). Business personnel (including management) are unable to obtain the latest, global business data (such as the group's real-time sales totals, inventory distribution, cash flow status, etc.), causing information acquisition to lag significantly behind the rapidly changing market environment and the need for rapid internal decision-making. Existing native real-time heterogeneous data integration methods require technical personnel to continuously write code to achieve information acquisition. Each time a new data source is added, code must be rewritten and recompiled and deployed, significantly increasing the technical personnel's workload, hindering project progress and making the management of heterogeneous data sources more difficult. Therefore, there is an urgent need for a real-time data warehousing solution that can effectively address the unified integration of group-level data and meet the needs of timely access and analysis, without the need for continuous code writing. Summary of the Invention

[0004] The present invention provides a method, device and equipment for real-time data warehousing processing in heterogeneous databases, which solves the problem of difficulty in achieving real-time unified integrated access to group-level data without changing the system code.

[0005] In order to solve the above technical problems, the technical solutions of the present invention are as follows:

[0006] An embodiment of the present invention provides a method for real-time data warehousing processing in a heterogeneous database, comprising:

[0007] Obtain configuration information tables and synchronization item configuration tables of at least two heterogeneous source databases;

[0008] Obtaining a synchronization item field record table according to the configuration information table and the synchronization item configuration table;

[0009] Generate a storage table according to the synchronization item field record table;

[0010] Receiving, through independent threads respectively started by the at least two heterogeneous source databases, data to be synchronized respectively sent by the at least two heterogeneous source databases;

[0011] Initialize the storage table according to the data to be synchronized to obtain an initial storage table;

[0012] Synchronize the data in the initial storage table according to the data to be synchronized to generate a data warehouse table;

[0013] When the configuration information tables or synchronization item configuration tables of at least two heterogeneous source databases change, the data warehouse table is updated to obtain an updated data warehouse table.

[0014] Optionally, a synchronization item field record table is obtained according to the configuration information table and the synchronization item configuration table, including:

[0015] Generate a query statement according to the configuration information table and the synchronization item configuration table;

[0016] Query the source database system table according to the query statement to obtain the source database table structure;

[0017] The source database table structure is stored to obtain a synchronization item field record table.

[0018] Optionally, generating a storage table according to the synchronization item field record table includes:

[0019] Generate a table creation statement according to the synchronization item field record table;

[0020] Execute the table creation statement to generate a storage table.

[0021] Optionally, receiving the to-be-synchronized data respectively sent by the at least two heterogeneous source databases through independent threads respectively started by the at least two heterogeneous source databases includes:

[0022] The initial full data and the incremental data to be synchronized respectively sent by the at least two heterogeneous source databases are received through independent threads respectively started by the at least two heterogeneous source databases.

[0023] Optionally, initializing the storage table according to the data to be synchronized to obtain an initial storage table includes:

[0024] Storing the initial full amount of data in the data to be synchronized to obtain an initial log table;

[0025] Restoring the data type of the initial full data and storing it to obtain an initial data table;

[0026] An initial storage table is obtained according to the initial log table and the initial data table.

[0027] Optionally, synchronizing the initial storage table according to the data to be synchronized to generate a data warehouse table includes:

[0028] Synchronize the initial log table according to the incremental data to be synchronized in the data to be synchronized to obtain an incremental log table;

[0029] Restoring the data type of the incremental data to be synchronized to obtain intermediate data;

[0030] Synchronize the initial data table according to the intermediate data to obtain an incremental data table;

[0031] A data warehouse table is obtained according to the incremental log table and the incremental data table.

[0032] Optionally, when the configuration information tables or synchronization item configuration tables of at least two heterogeneous source databases change, an updated data warehouse table is generated, including:

[0033] Obtaining an updated synchronization item field record table according to the configuration information table and the synchronization item configuration table;

[0034] Determining an updated storage table according to the updated synchronization item field record table;

[0035] An independent thread started by the source database receives the data to be synchronized sent by the source database.

[0036] Initialize the storage table according to the data to be synchronized to obtain an initial storage table;

[0037] The initial storage table is synchronized according to the data to be synchronized to generate an updated data warehouse table.

[0038] An embodiment of the present invention further provides a device for real-time data warehousing processing in a heterogeneous database, comprising:

[0039] An acquisition module, configured to acquire configuration information tables and synchronization item configuration tables of at least two heterogeneous source databases;

[0040] A processing module is configured to obtain a synchronization item field record table based on the configuration information table and the synchronization item configuration table; generate a storage table based on the synchronization item field record table; receive the data to be synchronized sent by the at least two heterogeneous source databases respectively through independent threads respectively started by the at least two heterogeneous source databases; initialize the storage table based on the data to be synchronized to obtain an initial storage table; synchronize data on the initial storage table based on the data to be synchronized to generate a data warehouse table; and update the data warehouse table when the configuration information table or the synchronization item configuration table of at least two heterogeneous source databases changes to obtain an updated data warehouse table.

[0041] An embodiment of the present invention further provides a computing device, comprising: a processor and a memory storing a computer program, wherein the computer program executes the above method when executed by the processor.

[0042] An embodiment of the present invention further provides a computer-readable storage medium storing instructions, which, when executed on a computer, enable the computer to execute the above method.

[0043] The technical solution of the present invention includes at least the following effects:

[0044] The above-mentioned scheme of the present invention obtains the configuration information table and synchronization item configuration table of at least two heterogeneous source databases; obtains the synchronization item field record table according to the configuration information table and the synchronization item configuration table; generates a storage table according to the synchronization item field record table; receives the data to be synchronized respectively sent by the at least two heterogeneous source databases through independent threads started by the at least two heterogeneous source databases; initializes the storage table according to the data to be synchronized to obtain an initial storage table; synchronizes the initial storage table according to the data to be synchronized to generate a data warehouse table; when the configuration information table or the synchronization item configuration table of at least two heterogeneous source databases changes, updates the data warehouse table to obtain an updated data warehouse table, which can timely obtain business data reflecting the global status at the current time, can eliminate the process of code rewriting and deployment caused by data source changes, and realizes the "configuration as code" of real-time warehousing of heterogeneous data sources. BRIEF DESCRIPTION OF THE DRAWINGS

[0045] Figure 1 This is a flow chart of a method for real-time warehousing of data in a heterogeneous database provided by an embodiment of the present invention;

[0046] Figure 2 This is a structural diagram of a real-time data warehousing processing device for heterogeneous databases provided by an embodiment of the present invention;

[0047] Figure 3 It is a structural diagram of a computing device provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0048] Exemplary embodiments of the present invention will be described in more detail below with reference to the accompanying drawings. Although exemplary embodiments of the present invention are shown in the accompanying drawings, it should be understood that the present invention can be implemented in various forms and should not be limited by the embodiments set forth herein. Rather, these embodiments are provided to enable a more thorough understanding of the present invention and to fully convey the scope of the present invention to those skilled in the art.

[0049] like Figure 1 As shown, an embodiment of the present invention provides a method for real-time data warehousing processing in a heterogeneous database, comprising:

[0050] Step 11: Obtain configuration information tables and synchronization item configuration tables of at least two heterogeneous source databases;

[0051] Step 12: Obtain a synchronization item field record table according to the configuration information table and the synchronization item configuration table;

[0052] Step 13: Generate a storage table based on the synchronization item field record table;

[0053] Step 14: receiving the data to be synchronized respectively sent by the at least two heterogeneous source databases through independent threads respectively started by the at least two heterogeneous source databases;

[0054] Step 15: Initialize the storage table according to the data to be synchronized to obtain an initial storage table;

[0055] Step 16: Synchronize the initial storage table according to the data to be synchronized to generate a data warehouse table;

[0056] Step 17: When the configuration information tables or synchronization item configuration tables of at least two heterogeneous source databases change, update the data warehouse table to obtain an updated data warehouse table.

[0057] In this embodiment, in step 11, the manually configured heterogeneous database connection information in the configuration information table is obtained, and the connection information includes: data source code, database connection information, database user name, database password, database type and database driver information; the data source is dynamically managed based on the configuration information table, and dynamic addition, modification and deletion of data sources are supported; after the data source configuration is loaded, it is stored in the local cache and refreshed from the configuration information table automatically or manually at regular intervals to ensure the consistency of the data source information.

[0058] Get the manually configured data table information to be synchronized in the synchronization item configuration table. The data table information to be synchronized includes: synchronization item configuration ID, table name, data source code, synchronization status and other information; load the synchronization item configuration table into the cache when the system starts. The cache can use "table name + data source code" as key information and refresh automatically or manually at regular intervals to ensure that configuration changes in the synchronization item configuration table can take effect in a timely manner.

[0059] In step 12, a synchronization item field record table is obtained based on the configuration information table and the synchronization item configuration table. The synchronization item field record table mainly records the specific information of the data table that needs to be imported from the heterogeneous database into the data warehouse, mainly including synchronization item configuration ID, field name, field type, field length and field decimal point and other information.

[0060] In step 13, the table structure is established for all data tables that need to be imported from the heterogeneous databases into the data warehouse. This is the process of determining the storage table. Each data table that needs to be imported from the heterogeneous database corresponds to two storage tables in the data warehouse: the log table and the data table.

[0061] In step 14, the configuration information table is scanned periodically to check whether there are any updates to the data. If a new data source is added, a new asynchronous thread is started. The asynchronous thread is responsible for receiving the incremental data (or initial full data) generated by the data source. An independent asynchronous thread is started for each heterogeneous source database. The asynchronous thread continuously scans and pulls the incremental data (or initial full data) from the consumer message queue in batches to obtain the data to be synchronized, thereby achieving real-time log consumption. The data to be synchronized includes the initial full data to be synchronized and the incremental data to be synchronized.

[0062] In step 15, the synchronization status in the synchronization item configuration table is 2 In the snapshot state, the storage table needs to be initialized, that is, the full table data to be transmitted is read. The number of data items read in each batch can be set. The full table data is converted into JSON data format and pushed to the message queue; the corresponding asynchronous thread takes out the initial full data to be synchronized (full table data) from the message queue and stores it in the initial storage table in the data warehouse. The initial storage table is the initial data table consistent with the current source database and the initial log table that records the initialization operation.

[0063] In step 16, the synchronization status in the synchronization item configuration table is 3 Incremental synchronization captures data changes through the database change capture tool and sends them to the message queue; the corresponding asynchronous thread takes out the incremental data to be synchronized from the message queue, uses the incremental data to be synchronized to update the initial storage table, and obtains the data warehouse table.

[0064] Step 17: When the configuration information table and the synchronization item configuration table are changed, it means that the source database and the data table to be synchronized are changed respectively. The updated data warehouse table will be obtained again according to the process of steps 11 to 16.

[0065] In addition, the management and monitoring of tasks can be achieved by storing log processing information in the database to meet the traceability management requirements of the synchronization process. The log processing information includes monitoring indicators such as processing time, throughput and backlog; an interface is provided to query the synchronization progress, and support statistical queries by table and data source classification; an alarm trigger mechanism is provided, and when the synchronization delay exceeds the set threshold or the number of consecutive failures reaches the set upper limit, an alarm text message and an alarm email can be sent to the administrator; if the system scans that the data table structure in the heterogeneous database has changed (such as adding a new field or modifying the type), the data table structure in the data warehouse will be automatically updated through the preset code method to ensure that the data table structure in the data warehouse is consistent with the data table structure in the heterogeneous database, and incremental synchronization will be suspended during the data table structure change process.

[0066] This technical solution can achieve changes to data sources and specific data tables by changing the configuration information of the configuration information table and the synchronization item configuration table, thereby changing the log table information and data table information in the data warehouse to form a new data warehouse table. There is no need to rewrite code and recompile and deploy, which reduces the workload of technical personnel.

[0067] In an optional embodiment of the present invention, step 12 may include:

[0068] Step 121: Generate a query statement based on the configuration information table and the synchronization item configuration table;

[0069] Step 122: query the source database system table according to the query statement to obtain the source database table structure;

[0070] Step 123: Store the source database table structure to obtain a synchronization item field record table.

[0071] In this embodiment, the source database connection information in the configuration information table is used to connect to the heterogeneous database, thereby reading the table name information, data source code and synchronization status in the synchronization item configuration table. When the synchronization status is 0 During initialization, a database query statement is generated based on the read table name information; the statement is executed to query the system table of the source database, and the source database table structure obtained from the query is saved in the synchronization item field record table. The synchronization item field record table includes all structural information of the table, such as field name, field type, whether it is empty, and whether it is a primary key.

[0072] In an optional embodiment of the present invention, step 13 may include:

[0073] Step 131: Generate a table creation statement based on the synchronization item field record table;

[0074] Step 132: execute the table creation statement to determine the storage table.

[0075] In this embodiment, the table structure information of the table to be synchronized in the source database is obtained from the synchronization item field record table. The corresponding table creation statement can be automatically generated based on the table structure information. After the table creation statement is executed in the data warehouse, a corresponding storage table can be created for each data table to be synchronized. The storage table includes a log table with a table structure and a data table with a table structure. The structure of the log table includes the table name, data source code, log sequence number, timestamp information, operation type, and log data body. The structure of the data table depends on the table structure of the source database and is completely consistent with the table structure of the source database. When the storage table is established, the synchronization state of the synchronization item configuration table is converted to 2 Snapshot state to allow subsequent steps to be processed.

[0076] In an optional embodiment of the present invention, step 14 may include:

[0077] Step 141 : receiving initial full data and incremental data to be synchronized respectively sent by at least two heterogeneous source databases through independent threads respectively started by the at least two heterogeneous source databases.

[0078] In this embodiment, data source configurations are periodically scanned for updates. If a new data source is added (i.e., new data is added to the configuration information table), a separate asynchronous thread is launched to receive the JSON-formatted incremental logs generated by that data source. Each heterogeneous source database corresponds to an enabled asynchronous thread. This asynchronous thread continuously scans and pulls batches of data to be synchronized from the message queue to ensure real-time log reception. This data to be synchronized includes both the initial full data for the initial storage table initialization and the incremental data to be synchronized for subsequent synchronizations of the initial storage table.

[0079] In an optional embodiment of the present invention, step 15 may include:

[0080] Step 151: storing the initial full amount of data in the data to be synchronized to obtain an initial log table;

[0081] Step 152: Restore the data type of the initial full data and store it to obtain an initial data table;

[0082] Step 153: Obtain an initial storage table according to the initial log table and the initial data table.

[0083] In this embodiment, it is detected that the synchronization status of the synchronization item configuration table is 2 In the snapshot state, the asynchronous thread reads the initial full data to be synchronized from the message queue. The JSON-formatted initial full data to be synchronized obtained from the message queue specifically includes: specific data information, a snowflake sequence generated using the snowflake algorithm, the data source code corresponding to the heterogeneous database, the data table name of the current operation, the current operation type, and the system time. After reading the initial full data to be synchronized, the specific data information is saved as a log data body in the log table, using the snowflake sequence generated by the snowflake algorithm as the log sequence number, the data source code corresponding to the heterogeneous database and the data table name of the current operation as the data source code and table name, the "initialization addition" operation type, and the system time as the operation time. This results in an initial log table that has both a table structure and records the initial full data import operation. The JSON-formatted initial full data to be synchronized obtained from the message queue is converted according to the data type and data length format recorded in the synchronization item field record table. The converted data is sequentially stored in each field of the data table, resulting in a data warehouse data table that is completely consistent with the data table in the source database, namely the initial data table. The initial log table and the initial data table are the initial storage tables. When the initialization operation is completed, the synchronization state of the synchronization item configuration table is converted to 3 Incremental synchronization to allow subsequent steps to be processed.

[0084] In an optional embodiment of the present invention, in step 16, synchronizing the initial storage table according to the data to be synchronized to generate a data warehouse table may include:

[0085] Step 161: Synchronize the initial log table with the incremental data to be synchronized in the data to be synchronized to obtain an incremental log table.

[0086] Step 162: Restore the data type of the incremental data to be synchronized to obtain intermediate data;

[0087] Step 163: Synchronize the initial data table according to the intermediate data to obtain an incremental data table;

[0088] Step 164: Generate a data warehouse table based on the incremental log table and the incremental data table.

[0089] In this embodiment, it is detected that the synchronization status of the synchronization item configuration table is 3 Incremental synchronization, at this time, the asynchronous thread reads the incremental data to be synchronized in the message queue, and saves the specific data information in the incremental data to be synchronized in the JSON format obtained from the message queue as a log data body into the log table, and stores the log sequence number, data source code, table name, system time and the corresponding "insert, update or delete" operation type in sequence, thereby obtaining the latest synchronized incremental log table at the current time. The incremental data to be synchronized in the JSON format obtained from the message queue is converted according to the data type and data length format recorded in the synchronization item field record table to obtain intermediate data in the correct format, and the initial data table is synchronized according to the current operation type. The synchronization operation includes inserting, updating and deleting the data in the initial data table, thereby obtaining the latest synchronized incremental data table at the current time. The incremental log table and incremental data table that are latest synchronized at the current time are the data warehouse table.

[0090] In an optional embodiment of the present invention, in step 17, when the configuration information tables or synchronization item configuration tables of at least two heterogeneous source databases change, updating the data warehouse table to obtain an updated data warehouse table includes:

[0091] Step 171: Obtain an updated synchronization item field record table according to the configuration information table and the synchronization item configuration table;

[0092] Step 172, determining an updated storage table according to the updated synchronization item field record table;

[0093] Step 173: Receive the data to be synchronized sent by the source database through an independent thread started by the source database;

[0094] Step 174, initializing the storage table according to the data to be synchronized to obtain an initial storage table;

[0095] Step 175 : synchronize the initial storage table with the data to be synchronized to obtain an updated data warehouse table.

[0096] In this embodiment, the configuration information table or the synchronization item configuration table changes, that is, the connected heterogeneous database or the data table that needs to be synchronized to the data warehouse changes. According to the changed configuration information table or synchronization item configuration table, the updated synchronization item field record table can be obtained, and finally the updated data warehouse table can be obtained according to the same steps, thereby realizing real-time warehousing of data from multiple heterogeneous databases.

[0097] A specific embodiment of the method for real-time warehousing of data in a heterogeneous database provided by the embodiment of the present invention is as follows:

[0098] Step 1: Obtain a configuration information table having heterogeneous database connection information and a synchronization item configuration table having information of tables to be synchronized.

[0099] Step 2: Generate a database query statement based on the table name information; execute the statement to query the system table of the source database, save the source database table structure obtained by the query into the synchronization item field record table, and obtain the synchronization item field record table.

[0100] Step 3: Obtain the table structure information of the table to be synchronized in the source database from the synchronization item field record table, automatically generate the corresponding table creation statement, and execute the table creation statement in the data warehouse to create the corresponding storage table.

[0101] Step 4: Open an asynchronous thread for each heterogeneous source database.

[0102] In step 5, the asynchronous thread reads the initial full data to be synchronized in the message queue, stores the initial full data to be synchronized in JSON format in the data warehouse, and obtains the initial log table; converts the format into the corresponding format in the synchronization item field record table, stores it in the data warehouse, and obtains the initial data table.

[0103] Step 6: The asynchronous thread reads the incremental data to be synchronized in the message queue, and saves the incremental data to be synchronized in the JSON format obtained from the message queue into the log table to obtain the incremental log table; the incremental data to be synchronized in the JSON format obtained from the message queue is converted according to the format in the synchronization item field record table, and saved into the data table to obtain the incremental data table.

[0104] Step 7: When the configuration information table or the synchronization item configuration table changes, obtain the updated synchronization item field record table, and finally obtain the updated data warehouse table according to the same steps as steps 1 to 5, so as to realize real-time warehousing of data from multiple heterogeneous databases.

[0105] The above embodiment of the present invention implements real-time synchronization of heterogeneous databases to a data warehouse through the following steps, specifically including:

[0106] Related configuration table:

[0107] Configuration information table (manual configuration) ds_config

[0108] Field Name Field Type describe ds_code varchar Data source encoding ds_url varchar Database Connection ds_username varchar Database user name ds_password varchar Database password ds_type varchar Database type. mysql, sqlserver ds_driverclass varchar Database Driver

[0109] Synchronization item configuration table (manual configuration) sync_config_item

[0110] Field Name Field Type describe item_id varchar Synchronization item configuration id table_name varchar Table name ds_code varchar Data source encoding status varchar Synchronization status. 0 = initialization, 2 = snapshot, 3 = incremental synchronization

[0111] Synchronization item field record table (automatically obtained by the system) sync_config_item_col

[0112] Field Name Field Type describe item_id varchar Synchronization item configuration id field_name varchar Field Name field_type varchar Field Type field_length int Field length field_point int Field decimal point

[0113] Log table (dynamically created) sync_table_log_{dsCode}_{tableName}

[0114] Field Name Field Type describe table_name varchar Table name ds_code varchar Data source encoding rsn varchar Log receiving sequence number. Snowflake ID log_time varchar Log time body text Incremental log data body

[0115] Data table (dynamically created based on source table metadata) sync_{dsCode}_{tableName}

[0116] Field Name Field Type describe field1 Type 1 Source table sub-segment 1 field2 Type 2 Source table field 2 … … … fieldN Type n Source table field n

[0117] Step 1: Configure the data source

[0118] Description: Manually configure the connection information of the source database (heterogeneous database) and dynamically manage the data source based on the configuration.

[0119] Implementation details:

[0120] Load and manage multiple heterogeneous data sources through the dynamic data source management component, and support dynamic addition, modification and deletion of data sources.

[0121] After the data source configuration is loaded, it is stored in the local cache and refreshed from the configuration table periodically or manually to ensure the consistency of the data source information.

[0122] Step 2: Configure the synchronization item configuration table

[0123] Description: Configure the table item information in the heterogeneous source database that needs to be synchronized to the data warehouse in sync_config_item.

[0124] Implementation details:

[0125] When the system starts, the synchronization item configuration is loaded into the cache (such as the Redis Hash structure), and the cache is refreshed periodically or manually to ensure that the configuration changes can take effect in a timely manner.

[0126] The cache uses table name and data source code (tableName#dsCode) as keys to store detailed table configurations for quick query.

[0127] Step 3: Start a thread for asynchronously receiving incremental logs for each source database

[0128] Description: Periodically scans the data source configuration to see if it has been updated. If a new source database is added, a separate asynchronous thread is started to receive the incremental logs in JSON format generated by the source database.

[0129] Implementation details:

[0130] Incremental logs are generated by database change capture tools (such as Debezium), recording data changes (including addition, update, and deletion operations and data before and after the change) in JSON format and sent to the message queue (Redis List).

[0131] Start a scheduled task thread for each data source to continuously scan and pull JSON incremental logs from the message queue (Redis List) in batches to ensure real-time log reception.

[0132] Based on the configured cache, the received logs are saved to the corresponding log tables according to the table name + data source code.

[0133] The log receiving thread uses concurrency control to avoid repeated log consumption by multiple instances and capture exceptions during the log receiving process (for example, when log consumption fails, it is temporarily stored in Redis and awaits manual processing).

[0134] Incremental log structure: The log contains fields such as operation (operation type) and logs (change data), as follows:

[0135] Add and delete

[0136] {

[0137] "_index": "erp-anj-res-2025-07-07",

[0138] "_source": {

[0139] "@timestamp": "2025-07-07T03:26:06",

[0140] "db_name": "anj_1076",

[0141] "ds_code": "ANJ",

[0142] "operation": "insert\delete",

[0143] "logs": "{\"fdname\":\"hz_code\", \"dictlist\":\"Zhang San\", \"pym\":\"ZJY\", \"RowState\":null, \"RowGuid\":\"B5353C67-822D-43B2-8147-87485D68E13B\"}",

[0144] "table": "gldict"

[0145] }

[0146] }

[0147] renew

[0148] {

[0149] "_index": "erp-xh-res-2025-04-07",

[0150] "_source": {

[0151] "@timestamp": "2025-04-07T01:34:26",

[0152] "db_name": "xhyy_zr",

[0153] "ds_code": "XH",

[0154] "operation": "update",

[0155] "logs": "{\"before_values\":{\"fdname\":\"banci\", \"dictlist\":\"Class 2\", \"pym\":\"EB\", \"RowState\":null, \"RowGuid\":\"C156D59D-EB98-4B93-B219-EBB31D009364\"}, \"after_values\":{\"fdname\":\"banci\", \"dictlist\":\"Class 2\", \"pym\":\"EB\", \"RowState\":\"1\", \"RowGuid\":\"C156D59D-EB98-4B93-B219-EBB31D009364\"}}",

[0156] "table": "gldict"

[0157] }

[0158] }

[0159] Step 4: Automatically generate storage tables (log table + data table)

[0160] Description: When the synchronization item configuration is added or changed and status = 0, the source table structure will be obtained through JDBC, and a corresponding storage table (log table + data table) will be dynamically created in the data warehouse for each table to be synchronized.

[0161] Implementation details:

[0162] Obtaining the source table structure: When the system starts or the configuration is updated, cache data is configured based on the synchronization item. For synchronization items with status = 0, the source database is connected through JDBC. The source database's system tables are used to obtain the source table's field information (name, type, length, primary key identifier, etc.) and store it in the sync_config_item_col table.

[0163] The table structure is created by obtaining the source table field information, dynamically assembling the SQL table creation statement and executing it, and using Redis distributed locks to avoid repeated table creation in multiple instances.

[0164] When the table creation phase is completed, the current synchronization item status is changed to 2.

[0165] Step 5: Initialize all table data (get the initial storage table)

[0166] Description: When the synchronization item configuration is added or changed and status = 2, the source table data will be fully read through JDBC and initialized to the corresponding data table in the data warehouse to ensure initial data consistency.

[0167] Implementation details:

[0168] Full initialization trigger condition: When the synchronization item status changes to status=2.

[0169] Initialization process:

[0170] Read all data from the source table through JDBC streaming (to avoid memory overflow), reading a fixed batch (such as 5,000 records) each time.

[0171] Convert the full data into an initialization log in JSON format (operation = init_insert), including field values ​​(serializing special types such as byte arrays and timestamps), and send it to the incremental log processing queue.

[0172] The initialization log and incremental log use unified processing logic to ensure that all data is correctly written into the data warehouse data table.

[0173] After initialization is complete, update the synchronization item status to 3.

[0174] Step 6: Start the incremental log asynchronous processing thread

[0175] Description: When the synchronization item configuration is added or changed and status = 3, a thread is started to asynchronously process the received JSON incremental log and synchronize the changes to the data warehouse tables (incremental data table and incremental log table).

[0176] Implementation details:

[0177] Log processing thread: Each synchronization table corresponds to a SyncProcessor instance, and the built-in thread pool (logApplyExecutor) processes the incremental logs of the table, ensuring serial processing of single-table logs and avoiding concurrent update conflicts.

[0178] Log parsing and SQL generation:

[0179] Parse JSON incremental logs to extract operation types (operation: insert / update / delete) and change data (before_values / after_values ​​in logs).

[0180] Generate corresponding SQL according to the operation type:

[0181] Insert: Generates an INSERT ON DUPLICATE KEY UPDATE statement to ensure that data is updated when a primary key conflict occurs.

[0182] Update: If the primary key has not changed, update the field directly; if the primary key has changed, delete the old primary key data first and then insert the new data.

[0183] Delete: Generate a DELETE statement to delete the corresponding data based on the primary key.

[0184] Execute SQL to synchronize data:

[0185] Data table: Execute the generated SQL to synchronize the changes to the latest data table in the data warehouse.

[0186] Log table: Write each change into the history table using an INSERT statement, recording the change log number, operation type, and data before and after the change.

[0187] Exception handling and monitoring:

[0188] When log processing fails, the pending queue is cleared, the exception log is recorded, and the system goes into hibernation and tries again.

[0189] Monitor the synchronization status (such as processing time and number of success / failure records) through scheduled tasks, and update the synchronization metrics in the cache (such as LogConsumeMetrics).

[0190] Step 7: Synchronization status management and monitoring

[0191] Description: Manages the status of synchronization tasks and provides monitoring indicators to ensure that the synchronization process is observable and manageable.

[0192] Implementation details:

[0193] Status management: Use the status field in the sync_config_item table to record the synchronization status (such as initialization, full, and incremental) and update it when the status changes.

[0194] Monitoring indicators:

[0195] Record log processing time, throughput, backlog and other indicators, and store them in Redis or database.

[0196] Provides an interface to query the synchronization status and supports synchronization progress statistics by table and data source.

[0197] Alarm mechanism: When the synchronization delay exceeds the threshold or the number of consecutive failures reaches the upper limit, an alarm (such as email or SMS) is triggered.

[0198] Step 8: Ensure data consistency

[0199] Description: Ensure data consistency between the source database and the data warehouse, and handle possible conflicts and exceptions.

[0200] Implementation details:

[0201] Idempotence design: Log sequence numbers (RSNs) are used to ensure consistent results when the same log is processed repeatedly.

[0202] Data verification: Regularly compare the data in the source database and the data warehouse, and trigger the repair process when differences are found.

[0203] Transaction management: Use database transactions to ensure the atomicity of updates to log tables and data tables.

[0204] Step 9: Table structure change processing

[0205] Description: When the source table structure changes, the table structure of the data warehouse is automatically updated synchronously.

[0206] Implementation details:

[0207] Regularly check for changes in source table structure (such as adding new fields or modifying types).

[0208] Use the alterDateMeta method to update the table structure of the data warehouse table to ensure consistency with the source table.

[0209] During table structure changes, incremental synchronization is suspended to ensure data consistency.

[0210] Unified access and dynamic management of heterogeneous data sources

[0211] Based on the data source configuration in the ds_config table, the dynamic data source component (DynamicRoutingDataSource) enables unified access to multiple types of heterogeneous databases (such as MySQL, Oracle, SQL Server, etc.), supports dynamic addition, deletion, modification, and query of data sources, and takes effect without restarting the system.

[0212] Combining local cache (DsConfigs) with a timed refresh mechanism ensures real-time and consistency of data source configuration, solving the complex problem of data source management in heterogeneous environments.

[0213] Standardization and asynchronous processing of JSON incremental logs

[0214] A standardized JSON incremental log format is defined, including core fields such as rsn (log sequence number), operation (operation type), logs (change data), and @timestamp (timestamp). It uniformly adapts to the four types of operations: init_insert (full initialization), insert, update, and delete, to achieve normalized processing of change logs from different databases.

[0215] An asynchronous thread pool (ScheduledExecutorService+ThreadPoolExecutor) is used to consume logs in parallel, and a Redis queue is used to buffer and distribute logs. Combined with a distributed lock (RLockUtil), this avoids repeated processing of multiple instances and improves log processing efficiency.

[0216] Seamless connection between full initialization and incremental synchronization

[0217] During the full initialization phase, the initSyncDataLog method is used to convert the full data of the source table into a JSON log of the init_insert type, simulating the incremental log process to write it into the data warehouse, ensuring that the processing logic of the full data and incremental logs is consistent.

[0218] The end point of full initialization is recorded based on the RSN (Receive Sequence Number). Incremental synchronization starts consuming logs from this point, achieving seamless switching between full and incremental data and avoiding data duplication or omission.

[0219] Automatic Generation and Adaptation of Data Warehouse Table Structure

[0220] Automatically obtain the source table structure (field name, type, primary key) based on JDBC, and automatically generate the corresponding data table (storing the latest data) and log table (sync_table_log_{dsCode}_{tableName}, storing change history) in the data warehouse through the initDataMeta method of SyncProcessor.

[0221] The log table uses fields such as rsn (received sequence number) and body (JSON log content) to fully trace the change trajectory and support data backtracking and consistency verification.

[0222] Synchronous state consistency control in distributed environments

[0223] The synchronization lifecycle is managed through the status field of the sync_config_item table (0 = initialization, 2 = snapshot, 3 = incremental synchronization), and the status synchronization between multiple instances is achieved by combining the configuration information of the Redis cache (RedisKeys.getSyncConfigKey).

[0224] Each SyncProcessor seizes table-level synchronization permissions through a distributed lock (RedisKeys.getSyncProcessorLockKey), ensuring that logs for the same table are processed by only a single instance, thus avoiding concurrency conflicts in a distributed environment.

[0225] Fault tolerance and self-healing mechanism

[0226] When log processing fails, Redis temporarily stores the failed log (me.redisUtil.lSet(key,collect,-1)), and triggers a scheduled retry or manual intervention process to avoid data loss.

[0227] When the system is shut down, the thread pool is gracefully shut down (executor.shutdown()) through the @PreDestroy hook function, resources are released, and the last processing position is recorded to ensure that the download can be resumed from the breakpoint after restart.

[0228] Configurable synchronization strategy and filtering mechanism

[0229] Based on the sync_config_item table, data table items that need to be synchronized can be dynamically added.

[0230] (2) Technical Effects

[0231] Through the above technical innovations, the present invention achieves the following significant effects:

[0232] Real-time improvement

[0233] The asynchronous processing mechanism of incremental logs controls synchronization delays to seconds (the average time from log generation to synchronization to the data warehouse is less than 1 second), meeting the needs of real-time data analysis and monitoring scenarios.

[0234] Enhanced heterogeneous compatibility

[0235] It supports synchronization of mainstream heterogeneous databases such as MySQL, Oracle, and SQL Server. Through JSON log standardization and JDBC universal interface, it eliminates the adaptation cost caused by database type differences. Adapting to new database types only requires adding driver configuration.

[0236] Increased automation

[0237] The entire process requires no manual intervention: table structure is automatically generated, full initialization and incremental synchronization are automatically switched, and abnormal logs are automatically retried, reducing manual operation costs by more than 90% and reducing human errors.

[0238] Data consistency assurance

[0239] Through RSN sequence numbers, distributed locks, and transaction mechanisms, we ensure the idempotence (duplicate logs do not affect the final data) and atomicity (updates to the data table and log table succeed or fail at the same time) of log processing, achieving data consistency of 99.99%.

[0240] Scalability and flexibility

[0241] Adding a new data source or synchronization table only requires modifying the configuration table, without the need for code development. It supports concurrent processing of tens of thousands of logs per second (dynamically expanded through the thread pool) to meet business growth needs.

[0242] Reduced operation and maintenance costs

[0243] Synchronization status visualization (based on the status field and monitoring indicators) and exception log traceability (sync_table_log table) shorten problem location time to minutes, improving operation and maintenance efficiency by more than 50%.

[0244] Efficient resource utilization

[0245] The thread pool is isolated by table (single table thread) to avoid resource preemption; full initialization uses a semaphore (MaxInitCount) to limit the number of concurrent connections (default is 5) to prevent system overload and improve resource utilization by 30%.

[0246] Data traceability

[0247] The log table (sync_table_log_{dsCode}_{tableName}) fully records the details of each data change (operation type, data before and after the change, and timestamp), supporting data auditing, backtracking, and troubleshooting to meet compliance requirements.

[0248] In summary, the present invention provides an efficient, reliable, and easily scalable solution for real-time synchronization of heterogeneous databases into warehousing. It is suitable for scenarios such as enterprise-level data middle platforms and real-time data warehouses, and significantly reduces the complexity and cost of data integration.

[0249] The real-time data warehousing processing method for heterogeneous databases proposed in the present invention eliminates the need to constantly add and change code when changes occur in heterogeneous databases and data tables to be synchronized. Manual modification of configuration information can replace code modification and redeployment, saving the workload of technical personnel and enabling the addition of new heterogeneous data sources without restarting the system.

[0250] like Figure 2 As shown, the embodiment of the present invention further provides a real-time data warehousing processing device 20 for a heterogeneous database, comprising:

[0251] An acquisition module 21 is configured to acquire configuration information tables and synchronization item configuration tables of at least two heterogeneous source databases;

[0252] The processing module 22 obtains a synchronization item field record table based on the configuration information table and the synchronization item configuration table; generates a storage table based on the synchronization item field record table; receives the data to be synchronized sent by the at least two heterogeneous source databases respectively through independent threads started by the at least two heterogeneous source databases; initializes the storage table based on the data to be synchronized to obtain an initial storage table; synchronizes the initial storage table based on the data to be synchronized to generate a data warehouse table; and updates the data warehouse table when the configuration information table or the synchronization item configuration table of at least two heterogeneous source databases changes to obtain an updated data warehouse table.

[0253] Optionally, the processing module 22 is specifically configured to:

[0254] Generate a query statement according to the configuration information table and the synchronization item configuration table;

[0255] Query the source database system table according to the query statement to obtain the source database table structure;

[0256] The source database table structure is stored to obtain a synchronization item field record table.

[0257] Optionally, the processing module 22 is further configured to:

[0258] Generate a table creation statement according to the synchronization item field record table;

[0259] Execute the table creation statement to generate a storage table.

[0260] Optionally, receiving the to-be-synchronized data respectively sent by the at least two heterogeneous source databases through independent threads respectively started by the at least two heterogeneous source databases includes:

[0261] The initial full data and the incremental data to be synchronized respectively sent by the at least two heterogeneous source databases are received through independent threads respectively started by the at least two heterogeneous source databases.

[0262] Optionally, initializing the storage table according to the data to be synchronized to obtain an initial storage table includes:

[0263] Storing the initial full amount of data in the data to be synchronized to obtain an initial log table;

[0264] Restoring the data type of the initial full data and storing it to obtain an initial data table;

[0265] An initial storage table is obtained according to the initial log table and the initial data table.

[0266] Optionally, synchronizing the initial storage table according to the data to be synchronized to generate a data warehouse table includes:

[0267] Synchronize the initial log table according to the incremental data to be synchronized in the data to be synchronized to obtain an incremental log table;

[0268] Restoring the data type of the incremental data to be synchronized to obtain intermediate data;

[0269] Synchronize the initial data table according to the intermediate data to obtain an incremental data table;

[0270] A data warehouse table is obtained according to the incremental log table and the incremental data table.

[0271] Optionally, when the configuration information tables or synchronization item configuration tables of at least two heterogeneous source databases change, an updated data warehouse table is generated, including:

[0272] Obtaining an updated synchronization item field record table according to the configuration information table and the synchronization item configuration table;

[0273] Determining an updated storage table according to the updated synchronization item field record table;

[0274] An independent thread started by the source database receives the data to be synchronized sent by the source database.

[0275] Initialize the storage table according to the data to be synchronized to obtain an initial storage table;

[0276] The initial storage table is synchronized according to the data to be synchronized to generate an updated data warehouse table.

[0277] It should be noted that this device is a device corresponding to the above method, and all implementation methods in the above method embodiment are applicable to this embodiment and can achieve the same technical effect.

[0278] like Figure 3 As shown, an embodiment of the present invention further provides a computing device 30, including a processor 31, a memory 32, and a program or instruction stored in the memory 32 and executable on the processor 31. When the program or instruction is executed by the processor 31, each process of the embodiment of the above-mentioned method for real-time warehousing of data in a heterogeneous database is implemented, and the same technical effect can be achieved. To avoid repetition, it will not be described here. It should be noted that the computing device in the embodiment of the present invention includes the above-mentioned mobile electronic device and non-mobile electronic device.

[0279] Those skilled in the art will appreciate that the units and algorithm steps of each example described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, or a combination of computer software and electronic hardware. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Professionals and technicians can use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of the present invention.

[0280] Those skilled in the art will clearly understand that, for the convenience and brevity of description, the specific working processes of the systems, devices and units described above can refer to the corresponding processes in the aforementioned method embodiments and will not be repeated here.

[0281] In the embodiments provided by the present invention, it should be understood that the disclosed devices and methods can be implemented in other ways. For example, the device embodiments described above are merely illustrative. For example, the division of units is merely a logical function division. In actual implementation, there may be other division methods, such as multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. In addition, the mutual coupling or direct coupling or communication connection shown or discussed can be through some interface, indirect coupling or communication connection of devices or units, which can be electrical, mechanical or other forms.

[0282] The units described as separate components may or may not be physically separate, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed across multiple network units. Some or all of these units may be selected to achieve the purpose of this embodiment according to actual needs.

[0283] In addition, each functional unit in each embodiment of the present invention may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit.

[0284] If the functions are implemented as software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, or the portion that contributes to the prior art, or a portion of the technical solution, can be embodied in the form of a software product. This computer software product, stored in a storage medium, includes instructions for causing a computer device (such as a personal computer, server, or network device) to execute all or part of the steps of the various embodiments of the method of the present invention. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, mobile hard drives, ROM, RAM, magnetic disks, or optical disks.

[0285] In addition, it should be pointed out that in the apparatus and method of the present invention, it is obvious that each component or each step can be decomposed and / or recombined. These decompositions and / or recombinations should be regarded as equivalent solutions of the present invention. Moreover, the steps of performing the above-mentioned series of processing can naturally be performed in chronological order according to the order of description, but they do not necessarily need to be performed in chronological order, and some steps can be performed in parallel or independently of each other. For those of ordinary skill in the art, it can be understood that all or any steps or components of the method and apparatus of the present invention can be implemented in hardware, firmware, software or a combination thereof in any computing device (including a processor, storage medium, etc.) or a network of computing devices. This can be achieved by those of ordinary skill in the art using their basic programming skills after reading the description of the present invention.

[0286] Therefore, the purpose of the present invention can also be achieved by running a program or a group of programs on any computing device. The computing device can be a well-known general-purpose device. Therefore, the purpose of the present invention can also be achieved simply by providing a program product containing program code for implementing the method or device. That is to say, such a program product also constitutes the present invention, and the storage medium storing such a program product also constitutes the present invention. Obviously, the storage medium can be any well-known storage medium or any storage medium developed in the future. It should also be pointed out that in the device and method of the present invention, it is obvious that each component or each step can be decomposed and / or recombined. These decompositions and / or recombinations should be regarded as equivalent schemes of the present invention. In addition, the steps of performing the above-mentioned series of processing can naturally be performed in chronological order according to the order of description, but do not necessarily need to be performed in chronological order. Certain steps can be performed in parallel or independently of each other.

[0287] The above is a preferred embodiment of the present invention. It should be pointed out that for ordinary technicians in this technical field, several improvements and modifications can be made without departing from the principles of the present invention. These improvements and modifications should also be regarded as the scope of protection of the present invention.

Claims

1. A method for real-time data warehousing in heterogeneous databases, characterized in that: include: Obtain configuration information tables and synchronization item configuration tables of at least two heterogeneous source databases; Obtaining a synchronization item field record table according to the configuration information table and the synchronization item configuration table; Generate a storage table according to the synchronization item field record table; Receiving, through independent threads respectively started by the at least two heterogeneous source databases, data to be synchronized respectively sent by the at least two heterogeneous source databases; Initialize the storage table according to the data to be synchronized to obtain an initial storage table; Synchronize the data in the initial storage table according to the data to be synchronized to generate a data warehouse table; When the configuration information tables or synchronization item configuration tables of at least two heterogeneous source databases change, the data warehouse table is updated to obtain an updated data warehouse table.

2. The method for real-time data warehousing of heterogeneous databases according to claim 1 is characterized in that: According to the configuration information table and the synchronization item configuration table, a synchronization item field record table is obtained, including: Generate a query statement according to the configuration information table and the synchronization item configuration table; Query the source database system table according to the query statement to obtain the source database table structure; The source database table structure is stored to obtain a synchronization item field record table.

3. The method for real-time data warehousing of heterogeneous databases according to claim 1 is characterized in that: Generate a storage table based on the synchronization item field record table, including: Generate a table creation statement according to the synchronization item field record table; Execute the table creation statement to generate a storage table.

4. The method for real-time data warehousing of heterogeneous databases according to claim 1 is characterized in that: Receiving, by means of independent threads respectively started by the at least two heterogeneous source databases, data to be synchronized respectively sent by the at least two heterogeneous source databases, includes: The initial full data and the incremental data to be synchronized respectively sent by the at least two heterogeneous source databases are received through independent threads respectively started by the at least two heterogeneous source databases.

5. The method for real-time data warehousing of heterogeneous databases according to claim 4 is characterized in that: Initializing the storage table according to the data to be synchronized to obtain an initial storage table includes: Storing the initial full amount of data in the data to be synchronized to obtain an initial log table; Restoring the data type of the initial full data and storing it to obtain an initial data table; An initial storage table is obtained according to the initial log table and the initial data table.

6. The method for real-time data warehousing of heterogeneous databases according to claim 5 is characterized in that: Synchronizing the initial storage table according to the data to be synchronized to generate a data warehouse table includes: Synchronize the initial log table according to the incremental data to be synchronized in the data to be synchronized to obtain an incremental log table; Restoring the data type of the incremental data to be synchronized to obtain intermediate data; Synchronize the initial data table according to the intermediate data to obtain an incremental data table; A data warehouse table is obtained according to the incremental log table and the incremental data table.

7. The method for real-time data warehousing of heterogeneous databases according to claim 1 is characterized in that: When the configuration information tables or synchronization item configuration tables of at least two heterogeneous source databases change, an updated data warehouse table is generated, including: Obtaining an updated synchronization item field record table according to the configuration information table and the synchronization item configuration table; Determining an updated storage table according to the updated synchronization item field record table; An independent thread started by the source database receives the data to be synchronized sent by the source database. Initialize the storage table according to the data to be synchronized to obtain an initial storage table; The initial storage table is synchronized according to the data to be synchronized to generate an updated data warehouse table.

8. A real-time data warehousing processing device for heterogeneous databases, characterized in that: The real-time data warehousing processing device of the heterogeneous database includes: An acquisition module, configured to acquire configuration information tables and synchronization item configuration tables of at least two heterogeneous source databases; A processing module is configured to obtain a synchronization item field record table based on the configuration information table and the synchronization item configuration table; generate a storage table based on the synchronization item field record table; receive the data to be synchronized sent by the at least two heterogeneous source databases respectively through independent threads respectively started by the at least two heterogeneous source databases; initialize the storage table based on the data to be synchronized to obtain an initial storage table; synchronize data on the initial storage table based on the data to be synchronized to generate a data warehouse table; and update the data warehouse table when the configuration information table or the synchronization item configuration table of at least two heterogeneous source databases changes to obtain an updated data warehouse table.

9. A computing device, characterized in that include: A processor and a memory storing a computer program, wherein when the computer program is executed by the processor, the method according to any one of claims 1 to 7 is performed.

10. A computer-readable storage medium, characterized in that The device stores instructions, which, when executed on a computer, enable the computer to execute the method according to any one of claims 1 to 7.

Images