DDL Log Synchronization Across Sharded Databases for OLAP Consistency
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In database sharding systems, the execution of Data Definition Language (DDL) changes across multiple database sub-libraries often occurs at different times, leading to inconsistent table structures and challenges in real-time data aggregation for On-Line Analytical Processing (OLAP).
Innovation Solution
A data processing method that acquires and parses transaction change logs from each database sub-library to track the execution progress of DDL changes, using a synchronization middleware to synchronize and record timestamps and change content, ensuring consistent execution across all sub-libraries before aggregating data into a unified data warehouse.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If DDL changes are executed across multiple database sub-libraries in parallel, then data processing productivity is improved, but table structure consistency deteriorates
Solution Approach 1:
The synchronization middleware obtains DDL change information from the master table before executing it across database sub-libraries. It records the DDL change timestamp and uses this information to coordinate execution timing, ensuring that all sub-libraries apply the same structural changes in a controlled manner rather than allowing uncoordinated parallel execution.
Solution Approach 2:
The system implements feedback mechanisms where the synchronization middleware continuously monitors the execution status of DDL changes across database sub-libraries. It compares timestamps and change information to detect inconsistencies and triggers re-synchronization operations when table structure differences are detected, ensuring eventual consistency.
2Stability of the object's composition
If DDL changes are executed at the same time instant across all database sub-libraries, then table structure consistency is improved, but system complexity increases
Solution Approach 1:
The synchronization middleware acts as an intermediary between the master table and multiple database sub-libraries. It receives DDL change information from the master table, processes it by recording timestamps and change details, and then distributes coordinated execution commands to all sub-libraries. This mediator approach simplifies the overall system by centralizing the coordination logic rather than requiring complex peer-to-peer synchronization protocols.
3Manufacturing precision
If transaction change logs are acquired in chronological order, then data aggregation accuracy is improved, but real-time monitoring capability deteriorates
Solution Approach 1:
The system adds a timestamp dimension to the DDL change information acquired from database sub-libraries. Instead of relying solely on chronological order of log acquisition, it uses the timestamp attribute to sort and track DDL execution times. This additional dimensional information enables the synchronization middleware to accurately determine execution sequences and maintain data aggregation accuracy while improving real-time monitoring through efficient timestamp-based comparisons.
Data Source
Figure 1~3
AI summary
A data processing method. In the method, a target transaction change log of each database sub-library among a plurality of database sub-libraries is acquired and parsed. The target transaction change log is a most recently generated transaction change log in the database sub-library. If an event type indicated by the target transaction change log is a Data Definition Languages (DDL) event, information of the DDL event, which has been recorded in the target transaction change log, is recorded in a target event slot corresponding to the DDL event.