A data extraction and synchronization method based on kafka and log

Abstract

The present application relates to the field of information technology, and provide a kind of data extraction and synchronization method based on kafka and log, by using the increment log of each system as data basis, data user consumes log by subscribing kafka to solve the problems existing in the above scheme.In this scheme, the user of search service saves log into HBase, the user of cache service can save cache data into Redis, and the user of data synchronization can save data into own database.Because log data in kafka can be consumed repeatedly, and will be cached for a period of time, each user can consume the log saved in kafka to achieve both consistency with database and real-time data.

Description

A method for data extraction and synchronization based on Kafka and logs Technical Field

[0001] This invention relates to the field of information technology and provides a method for data extraction and synchronization based on Kafka and log. Background Technology

[0002] In traditional data extraction and synchronization schemes, there are several common approaches, namely:

[0003] 1. DBAs provide backup databases for each system, allowing users to extract the required data during off-peak hours. However, due to the different extraction times, data inconsistencies and conflicts arise among the users, and duplicate extractions lead to performance waste and data delays.

[0004] 2. A unified big data platform uses Sqoop to extract data from various systems during off-peak hours and saves it to Hive tables, then provides data services to other data users. This approach solves the consistency problem, but its timeliness is poor, basically with a T+1 timeframe.

[0005] 3. Incremental changes can be obtained based on triggers, but this can be highly intrusive to business users and can also result in some performance loss.

[0006] None of these solutions are perfect, as they all suffer from problems such as data conflicts, poor timeliness, and performance degradation.

[0007] Other information:

[0008] Kafka: An open-source stream processing platform that provides a unified, high-throughput, low-latency platform for processing real-time data.

[0009] Zookeeper: An open-source distributed service registration platform that provides open-source distributed configuration services, synchronization services, and naming registration services for large-scale distributed computing.

[0010] Canal: An open-source database middleware from Alibaba, used for incremental log parsing of MySQL-based databases, providing data subscription and consumption services.

[0011] InfluxDB: An open-source time-series database written in Go, focusing on high-performance querying and storage of time-series data. It is widely used in scenarios such as storing monitoring data in storage systems and real-time data in the IoT industry.

[0012] Grafana: A cross-platform, open-source data visualization application platform, primarily used for various graphical displays of monitoring data.

[0013] HDFS: An open-source distributed file system used to store data across all computing nodes, providing very high bandwidth to the entire cluster.

[0014] Parquet: A columnar storage format based on HDFS. Data stored in this file format can greatly improve query performance in distributed systems.

[0015] Spark: An open-source unified data processing and analysis engine, primarily used for the transmission, querying, and analysis of big data.

[0016] HBase: An open-source, non-relational distributed database, currently mainly used for storing and querying large amounts of unstructured data. Summary of the Invention

[0017] The purpose of this invention is to solve the problems of data conflict, poor timeliness, and performance loss in traditional data extraction and synchronization schemes.

[0018] To solve the above technical problems, the present invention adopts the following technical means:

[0019] A method for data extraction and synchronization based on Kafka and logs includes the following steps:

[0020] Step 1: Extract logs from the source system in real time, either incrementally or in full, and write the extracted logs to the specified log extraction and synchronization topic in Kafka.

[0021] Step 2: Extract logs from the synchronization topic, parse and anonymize them, define a unified message format, and put them into the corresponding anonymized log message topic in Kafka;

[0022] Step 3: Reload the data from Kafka into HDFS via the consumer.

[0023] Step 4: The data in HDFS is cleaned and stored in HBase for other systems to query and access.

[0024] In the above technical solution, step 1 includes the following steps:

[0025] Step 1.1: Parse the logs from the MySQL cluster, using Row mode to read the binary logs;

[0026] Step 1.2: Use Canal to read the binary log, including the following steps:

[0027] Step 1.2.1: Canal simulates the interaction protocol of MySQL Slave, pretends to be MySQL Slave, and sends dump protocol to MySQL Slave;

[0028] Step 1.2.2: The MySQL master receives the dump request and begins pushing binary logs to canal;

[0029] Step 1.2.3: Canal parses the binary log object;

[0030] Step 1.3: Extract the corresponding MySQL incremental log from the binary log obtained in Step 1.2 by subscribing to the canal server, including the following steps:

[0031] Step 1.3.1: According to the output of Canal, the log is in protobuf format. The incremental Storm module converts the data into the agreed unified message format in real time and saves it to Kafka.

[0032] Step 1.3.2: The incremental Storm module is also responsible for capturing changes to the database object collection schema in order to control the version number of log messages stored in Kafka;

[0033] Step 1.3.3: The configuration information of the incremental Storm module is stored in Zookeeper to meet high availability requirements;

[0034] Step 1.3.4: Kafka serves as both the output and a buffer and message destructuring area during the processing, ultimately obtaining incremental logs by incrementing the message version number;

[0035] Step 1.4: For log extraction scenarios where incremental extraction is insufficient, full extraction is required, including the following steps:

[0036] Step 1.4.1: Perform flow control on pull requests to avoid impacting the production database;

[0037] Step 1.4.2: The consumer performs data sharding, controlling the maximum number of bytes and records to be fetched;

[0038] Step 1.4.3: After each shard is fetched, update the version number of the log messages stored in Kafka.

[0039] Step 1.4.4: Notify Zookeeper to record the start of a full fetch process and write heartbeat information to Zookeeper;

[0040] Step 1.4.5: Check the compatibility of the database's metadata to ensure that the fetch request is valid;

[0041] Step 1.4.6: Determine the columns to be used for sharding and set priority rules from highest to lowest: Determine the sharding columns according to the priority of the user-specified columns, primary key index PK, unique index UK, and ordinary index. If there are multiple columns, take the first column.

[0042] Step 1.4.7: Split the data according to the sharding column and write the sharding results to the specified log extraction and synchronization topic in Kafka.

[0043] In the above technical solution, step 2: parsing and de-identifying the extracted logs, defining a unified message format, and placing them into the corresponding de-identified log message topic in Kafka, includes the following steps:

[0044] Step 2.1: Define a unified message format and ensure the uniqueness of the data, including the following steps:

[0045] Step 2.1.1: Define the message namespace, which consists of type + data source name + schema name + table name + version number + database number + table number. It can describe all tables and can be uniquely identified through the namespace.

[0046] Step 2.1.2: Define the _ums_op_ field to indicate that the data type is I, U, or D, where I represents insert, U represents update, and D represents delete.

[0047] Step 2.1.3: Define the _ums_ts_ field to record the timestamps of events that occur during addition, deletion, or modification. The timestamps of new data will be updated.

[0048] Step 2.1.4: Define the _ums_id_ field to indicate the unique ID of the message, ensuring the uniqueness of the message and the order of the messages;

[0049] Step 2.1.5: For full withdrawal, _ums_id_ is unique. Different ID fragments are retrieved from each concurrency level in Zookeeper to ensure uniqueness and performance. Negative numbers are filled in to avoid conflict with incremental data and to ensure that they are earlier than incremental messages.

[0050] Step 2.1.6: For incremental withdrawals, use the MySQL log file number + log offset as a unique ID. The ID is a 64-bit long integer, with the high 7 bits used for the log file number and the low 12 bits used for the log offset.

[0051] Step 2.2: For the extraction process, implement heartbeat monitoring and early warning, including the following steps:

[0052] Step 2.2.1: Insert one mental state data record for each extracted table every minute and save the sending time;

[0053] Step 2.2.2: The heartbeat table is also extracted. When a heartbeat packet is received, even if there is no data added, deleted or modified, it can prove that the link is working properly.

[0054] Step 2.2.3: Send the heartbeat data to a public topic on Kafka, then consume and save it to the open-source time-series database InfluxDB, and use the open-source data visualization application Grafana for display;

[0055] Step 2.2.4: If a heartbeat delay occurs, send an email or SMS alert via monitoring.

[0056] Step 2.3: Considering data security, real-time data anonymization is required, including the following steps:

[0057] Step 2.3.1: Default desensitization, suitable for simple scenarios, configures the default value and replaces the specified field value;

[0058] Step 2.3.2: Replace and desensitize, suitable for simple scenarios, configure to the specified value, and replace the predefined field value;

[0059] Step 2.3.3: Hash desensitization, suitable for scenarios that require mapping, uses MD5 and murmur algorithms, provides salting functionality, and ensures data consistency after hashing.

[0060] In the above technical solution, step 3: re-entering the data in Kafka into HDFS through the consumer includes the following steps:

[0061] Step 3.1: Consume messages according to the unified message format in Kafka. Use Spark to generate a consumption stream from the messages in Kafka and consume messages from different namespaces.

[0062] Step 3.2: Consume all unified messages in Kafka and save all historical information in HDFS, including the following steps:

[0063] Step 3.2.1: Replay the logs in HDFS to restore historical snapshots at any time;

[0064] Step 3.2.2: Reconstruct the historical information of each record using the linked list and analyze the information log;

[0065] Step 3.2.3: When an error occurs in the program, a new snapshot is created by re-consuming the messages through a power-back process.

[0066] Step 3.3: Save the message to HDFS and distribute it, including the following steps:

[0067] Step 3.3.1: Use the columnar storage format Parquet to save the unified message format logs to HDFS. The content of Parquet is the addition, deletion and modification information of all logs, as well as _ums_id and _ums_ts_. Since Spark natively supports Parquet well, SparkSQL can be used to query Parquet by date and time range for subsequent file merging steps.

[0068] Step 3.3.2: Distribute and store the data in different directories according to the different namespaces of the messages, that is, different tables and versions are placed in different directories;

[0069] Step 3.3.3: Since the stored Parquet files are all small files, start a job to merge the small Parquet files into large files every day.

[0070] Step 3.3.4: Add start and end times to the file directory. When backfilling data, you can decide which files to read based on the selected time range, instead of reading all the data.

[0071] In the above technical solution, step 4: the data in HDFS is cleaned and then stored in HBase, including the following steps:

[0072] Step 4.1: Process the data and determine its idempotency, including the following steps:

[0073] Step 4.1.1: Use the business message primary key and the specified column as a composite unique index to find the message column that needs to be updated;

[0074] Step 4.1.2: If a message with the same id exists, continue to check the size of the message's _ums_id. If it is larger than the existing message, it means the message is newer and has been modified, and can be inserted. If it is smaller, it means the message can be discarded and does not need to be inserted.

[0075] Step 4.1.3: Based on this logic, ensure the idempotency of the message and continue inserting or updating data.

[0076] Step 4.2: Select an appropriate rowkey and choose multiple columns to create a composite primary key according to business attributes;

[0077] Step 4.3: Select an appropriate version. The version is the update version number of the data in this column, used for determining data idempotency and time series. Use _ums_id_ + offset as the version of the row.

[0078] Step 4.4: Utilize the uniqueness and auto-incrementing nature of _ums_id_, and its comparison relationship with version itself is consistent, that is, a larger version is equivalent to a larger _ums_id_, and the corresponding version is newer;

[0079] Step 4.5: Implement idempotency of data storage in HBase using set operations, including the following steps:

[0080] Step 4.5.1: Query the target database based on the primary key in the collection to obtain an existing data collection;

[0081] Step 4.5.2: Compare the original dataset with the retrieved target dataset, and categorize them into two types:

[0082] 1. Insert non-existent data;

[0083] 2. For existing data, compare _ums_id_, and ultimately only update the target database with rows that have larger _ums_id_, discarding the smaller ones.

[0084] Because of the above-mentioned technical means, the present invention has the following beneficial effects:

[0085] This data extraction and synchronization solution based on Kafka and logs addresses the problems of the aforementioned solutions by using incremental logs from various systems as the data foundation. Data users consume logs by subscribing to Kafka. In this solution, users providing search services store logs in HBase, users providing caching services can store cached data in Redis, and users synchronizing data can store data in their own databases. Since log data in Kafka can be consumed repeatedly and is cached for a period of time, each user can maintain consistency with the database and ensure data real-time performance by consuming logs stored in Kafka. Attached Figure Description

[0086] Figure 1 shows the actual implementation and application view. Detailed Implementation

[0087] The embodiments of the present invention will be described in detail below. Although the present invention will be described and illustrated in conjunction with some specific embodiments, it should be noted that the present invention is not limited to these embodiments. On the contrary, any modifications or equivalent substitutions made to the present invention should be covered within the scope of the claims of the present invention.

[0088] Furthermore, to better illustrate the present invention, numerous specific details are set forth in the following detailed embodiments. Those skilled in the art will understand that the present invention can be practiced without these specific details.

[0089] A method for data extraction and synchronization based on Kafka and logs includes the following steps:

[0090] Step 1: Extract logs from the source system in real time, either incrementally or in full, and write the extracted logs to the specified log extraction and synchronization topic in Kafka.

[0091] Step 2: Extract logs from the synchronization topic, parse and anonymize them, define a unified message format, and put them into the corresponding anonymized log message topic in Kafka;

[0092] Step 3: Reload the data from Kafka into HDFS via the consumer.

[0093] Step 4: The data in HDFS is cleaned and stored in HBase for other systems to query and access.

[0094] In the above technical solution, step 1 includes the following steps:

[0095] Step 1.1: Parse the logs from the MySQL cluster, using Row mode to read the binary logs;

[0096] Step 1.2: Use Canal to read the binary log, including the following steps:

[0097] Step 1.2.1: Canal simulates the interaction protocol of MySQL Slave, pretends to be MySQL Slave, and sends dump protocol to MySQL Slave;

[0098] Step 1.2.2: The MySQL master receives the dump request and begins pushing binary logs to canal;

[0099] Step 1.2.3: Canal parses the binary log object;

[0100] Step 1.3: Extract the corresponding MySQL incremental log from the binary log obtained in Step 1.2 by subscribing to the canal server, including the following steps:

[0101] Step 1.3.1: According to the output of Canal, the log is in protobuf format. The incremental Storm module converts the data into the agreed unified message format in real time and saves it to Kafka.

[0102] Step 1.3.2: The incremental Storm module is also responsible for capturing changes to the database object collection schema in order to control the version number of log messages stored in Kafka;

[0103] Step 1.3.3: The configuration information of the incremental Storm module is stored in Zookeeper to meet high availability requirements;

[0104] Step 1.3.4: Kafka serves as both the output and a buffer and message destructuring area during the processing, ultimately obtaining incremental logs by incrementing the message version number;

[0105] Step 1.4: For log extraction scenarios where incremental extraction is insufficient, full extraction is required, including the following steps:

[0106] Step 1.4.1: Perform flow control on pull requests to avoid impacting the production database;

[0107] Step 1.4.2: The consumer performs data sharding, controlling the maximum number of bytes and records to be fetched;

[0108] Step 1.4.3: After each shard is fetched, update the version number of the log messages stored in Kafka.

[0109] Step 1.4.4: Notify Zookeeper to record the start of a full fetch process and write heartbeat information to Zookeeper;

[0110] Step 1.4.5: Check the compatibility of the database's metadata to ensure that the fetch request is valid;

[0111] Step 1.4.6: Determine the columns to be used for sharding and set priority rules from highest to lowest: Determine the sharding columns according to the priority of the user-specified columns, primary key index PK, unique index UK, and ordinary index. If there are multiple columns, take the first column.

[0112] Step 1.4.7: Split the data according to the sharding column and write the sharding results to the specified log extraction and synchronization topic in Kafka.

[0113] In the above technical solution, step 2: parsing and de-identifying the extracted logs, defining a unified message format, and placing them into the corresponding de-identified log message topic in Kafka, includes the following steps:

[0114] Step 2.1: Define a unified message format and ensure the uniqueness of the data, including the following steps:

[0115] Step 2.1.1: Define the message namespace, which consists of type + data source name + schema name + table name + version number + database number + table number. It can describe all tables and can be uniquely identified through the namespace.

[0116] Step 2.1.2: Define the _ums_op_ field to indicate that the data type is I, U, or D, where I represents insert, U represents update, and D represents delete.

[0117] Step 2.1.3: Define the _ums_ts_ field to record the timestamps of events that occur during addition, deletion, or modification. The timestamps of new data will be updated.

[0118] Step 2.1.4: Define the _ums_id_ field to indicate the unique ID of the message, ensuring the uniqueness of the message and the order of the messages;

[0119] Step 2.1.5: For full withdrawal, _ums_id_ is unique. Different ID fragments are retrieved from each concurrency level in Zookeeper to ensure uniqueness and performance. Negative numbers are filled in to avoid conflict with incremental data and to ensure that they are earlier than incremental messages.

[0120] Step 2.1.6: For incremental withdrawals, use the MySQL log file number + log offset as a unique ID. The ID is a 64-bit long integer, with the high 7 bits used for the log file number and the low 12 bits used for the log offset.

[0121] Step 2.2: For the extraction process, implement heartbeat monitoring and early warning, including the following steps:

[0122] Step 2.2.1: Insert one mental state data record for each extracted table every minute and save the sending time;

[0123] Step 2.2.2: The heartbeat table is also extracted. When a heartbeat packet is received, even if there is no data added, deleted or modified, it can prove that the link is working properly.

[0124] Step 2.2.3: Send the heartbeat data to a public topic on Kafka, then consume and save it to the open-source time-series database InfluxDB, and use the open-source data visualization application Grafana for display;

[0125] Step 2.2.4: If a heartbeat delay occurs, send an email or SMS alert via monitoring.

[0126] Step 2.3: Considering data security, real-time data anonymization is required, including the following steps:

[0127] Step 2.3.1: Default desensitization, suitable for simple scenarios, configures the default value and replaces the specified field value;

[0128] Step 2.3.2: Replace and desensitize, suitable for simple scenarios, configure to the specified value, and replace the predefined field value;

[0129] Step 2.3.3: Hash desensitization, suitable for scenarios that require mapping, uses MD5 and murmur algorithms, provides salting functionality, and ensures data consistency after hashing.

[0130] In the above technical solution, step 3: re-entering the data in Kafka into HDFS through the consumer includes the following steps:

[0131] Step 3.1: Consume messages according to the unified message format in Kafka. Use Spark to generate a consumption stream from the messages in Kafka and consume messages from different namespaces.

[0132] Step 3.2: Consume all unified messages in Kafka and save all historical information in HDFS, including the following steps:

[0133] Step 3.2.1: Replay the logs in HDFS to restore historical snapshots at any time;

[0134] Step 3.2.2: Reconstruct the historical information of each record using the linked list and analyze the information log;

[0135] Step 3.2.3: When an error occurs in the program, a new snapshot is created by re-consuming the messages through a power-back process.

[0136] Step 3.3: Save the message to HDFS and distribute it, including the following steps:

[0137] Step 3.3.1: Use the columnar storage format Parquet to save the unified message format logs to HDFS. The content of Parquet is the addition, deletion and modification information of all logs, as well as _ums_id and _ums_ts_. Since Spark natively supports Parquet well, SparkSQL can be used to query Parquet by date and time range for subsequent file merging steps.

[0138] Step 3.3.2: Distribute and store the data in different directories according to the different namespaces of the messages, that is, different tables and versions are placed in different directories;

[0139] Step 3.3.3: Since the stored Parquet files are all small files, start a job to merge the small Parquet files into large files every day.

[0140] Step 3.3.4: Add start and end times to the file directory. When backfilling data, you can decide which files to read based on the selected time range, instead of reading all the data.

[0141] In the above technical solution, step 4: the data in HDFS is cleaned and then stored in HBase, including the following steps:

[0142] Step 4.1: Process the data and determine its idempotency, including the following steps:

[0143] Step 4.1.1: Use the business message primary key and the specified column as a composite unique index to find the message column that needs to be updated;

[0144] Step 4.1.2: If a message with the same id exists, continue to check the size of the message's _ums_id. If it is larger than the existing message, it means the message is newer and has been modified, and can be inserted. If it is smaller, it means the message can be discarded and does not need to be inserted.

[0145] Step 4.1.3: Based on this logic, ensure the idempotency of the message and continue inserting or updating data.

[0146] Step 4.2: Select an appropriate rowkey and choose multiple columns to create a composite primary key according to business attributes;

[0147] Step 4.3: Select an appropriate version. The version is the update version number of the data in this column, used for determining data idempotency and time series. Use _ums_id_ + offset as the version of the row.

[0148] Step 4.4: Utilize the uniqueness and auto-incrementing nature of _ums_id_, and its comparison relationship with version itself is consistent, that is, a larger version is equivalent to a larger _ums_id_, and the corresponding version is newer;

[0149] Step 4.5: Implement idempotency of data storage in HBase using set operations, including the following steps:

[0150] Step 4.5.1: Query the target database based on the primary key in the collection to obtain an existing data collection;

[0151] Step 4.5.2: Compare the original dataset with the retrieved target dataset, and categorize them into two types:

[0152] 1. Insert non-existent data;

[0153] 2. For existing data, compare `_ums_id_`, and ultimately only update the target database with rows having larger `_ums_id_` values; smaller ones are discarded. Example

[0154] In practical applications, taking a real-time loan marketing scenario as an example:

[0155] As shown in Figure 1, System A has a function responsible for borrower credit assessment. Borrowers need to provide information that proves their creditworthiness, such as credit reports and bank statements. When borrowers fill in their credit information in System A through a web frontend or mobile app, they may be unable to continue for various reasons. Although this borrower may be a high-quality potential customer, before this synchronous solution is implemented, because it is impossible or takes a long time to know this customer information, such customers are actually lost.

[0156] After applying this synchronization scheme, the information already filled in by the borrower is recorded in the source database of system A. At this time, the synchronization scheme reads the binary log of the database through Canal, extracts the incremental information in the log, converts the information into a de-identified custom unified message format, saves it to Canal, and then extracts the message in Kafka, converts it into column data, and saves it to HBase. At this time, the data can be queried and read by other systems.

[0157] System B is a strategy scoring system that can tag customer information. At this time, System B reads the customer credit information in HBase, makes a judgment and score. If it is judged to be a potential high-quality customer, it will immediately push the customer information to the customer service system.

[0158] Customer service personnel can reach the borrower in a very short time through outbound calls, provide customer care, convert potential customers into real customers, and complete real-time marketing.

[0159] The entire synchronization solution has a processing delay of 5 minutes for business operations plus 1 minute for synchronization processing, ensuring that data can be synchronized within 6 minutes.

[0160] Since both Kafka and HBase in the solution support distributed and high concurrency, the entire solution ensures high availability, high concurrency, and low latency for data synchronization.

Claims

1. A data extraction and synchronization method based on Kafka and log, characterized in that: The process includes the following steps: Step 1: Extract logs from the source system in real time, either incrementally or fully, and write the extracted logs to a specified log extraction and synchronization topic in Kafka; Step 2: Parse and anonymize the logs in the log extraction and synchronization topic, define a unified message format, and place them into the corresponding anonymized log message topic in Kafka; Step 3: Re-enter the data from Kafka into HDFS through the consumer; Step 4: Clean the data in HDFS and store it in HBase. This is for other systems to query and call; Step 4 includes the following steps: Step 4.1: Process the data and determine its idempotency, including the following steps: Step 4.1.1: Use the business message primary key and specified column as a composite unique index to find the message column that needs to be updated; Step 4.1.2: If there is a message with the same name, continue to judge the size of the message's _ums_id_. If it is larger than the existing one, it means the message is newer and has been modified, and can be inserted. If it is smaller, it means the message can be discarded and does not need to be inserted; Step 4.1.3: Based on this logic, ensure the idempotency of the message. Continue inserting or updating data; Step 4.2: Select rowkey, and select multiple columns as a composite primary key according to business attributes; Step 4.3: Select version, where version is the update version number of the data in this column, used for data idempotency and time series judgment, using _ums_id_ + offset as the row version; Step 4.4: Utilize the uniqueness and auto-increment of _ums_id_, consistent with the comparison relationship of version itself, that is, a larger version is equivalent to a larger _ums_id_, and the corresponding version is newer; Step 4.5: Implement idempotency of data storage in HBase using set operations, including the following steps: Step 4.5.1: Query the target database based on the primary key in the set to obtain an existing data set; Step 4.5.2: Compare the original data set with the queried target data set, and divide them into two categories:

1. Data that does not exist, i.e., this part of the data is inserted; 2. Data that exists, compare _ums_id_, and finally only update the target database with those rows with larger _ums_id_, and discard the smaller ones directly.

2. The data extraction and synchronization method based on Kafka and log according to claim 1, characterized in that: Step 1 includes the following steps: Step 1.1: Parse logs from the MySQL cluster, reading binary logs in Row mode; Step 1.2: Use Canal to read binary logs, including the following steps: Step 1.2.1: Canal simulates the interaction protocol of the MySQL Slave, disguising itself as the MySQL Slave and sending a dump request to the MySQL Slave; Step 1.2.2: The MySQL master receives the dump request and begins pushing binary logs to Canal; Step 1.2.3: Canal parses the binary log object; Step 1.3: By subscribing to the Canal Server, extract the binary logs obtained in Step 1.

2. The incremental MySQL logs in the log file include the following steps: Step 1.3.1: According to the Canal output, the logs are in protobuf format. The incremental Storm module converts the data into a unified message format in real time and saves it to Kafka; Step 1.3.2: The incremental Storm module is also responsible for capturing changes in the database object collection schema to control the version number of the log messages saved in Kafka; Step 1.3.3: The configuration information of the incremental Storm module is stored in ZooKeeper to meet high availability requirements; Step 1.3.4: Kafka serves as both the output and a buffer and message destructuring area during processing, ultimately obtaining the incremental logs by incrementing the message version number; Step 1.4: For log extraction scenarios where incremental extraction cannot meet the requirements, full extraction is required, including the following steps: Step 1.4.1 Step 1.4.2: Perform flow control on pull requests to avoid impacting the production database; Step 1.4.3: The consumer shards the data, controlling the maximum number of bytes and records to be pulled; Step 1.4.4: After each shard is pulled, the version number of the log message stored in Kafka is updated; Step 1.4.5: Notify Zookeeper to record the start of a full pull process and write heartbeat information to Zookeeper; Step 1.4.6: Check the compatibility of the database metadata to ensure the pull request is valid; Step 1.4.7: Determine the columns to be used for sharding and set priority rules from largest to smallest: determine the sharding columns according to the priority of the user-specified columns, primary key index PK, unique index UK, and ordinary index. If there are multiple columns, the first column is selected; Step 1.4.8: Split the data according to the sharding columns and write the sharding results to the specified log extraction and synchronization topic in Kafka.

3. The data extraction and synchronization method based on Kafka and log according to claim 1, characterized in that: Step 2: Parse and anonymize the extracted logs, define a unified message format, and place them into the corresponding anonymized log message topic in Kafka. This includes the following steps: Step 2.1: Define a unified message format and ensure data uniqueness, including the following steps: Step 2.1.1: Define the message namespace, consisting of type + data source name + schema name + table name + version number + shard number + shard number, which can describe all tables and uniquely identify them through the namespace; Step 2.1.2: Define the _ums_op_ field, indicating that the data type is I, U, or D, where I represents insert, U represents update, and D represents delete. ete, delete; Step 2.1.3: Define the _ums_ts_ field to record the timestamp of the event that occurred (add, delete, or modify). The timestamp of the new data will be updated; Step 2.1.4: Define the _ums_id_ field to indicate the unique ID of the message, ensuring the uniqueness of the message and the order of the messages; Step 2.1.5: For full withdrawal, _ums_id_ is unique. Different ID fragments are taken from each concurrency level in Zookeeper to ensure uniqueness and performance. Filling in negative numbers will not conflict with incremental data and also ensures that they are earlier than incremental messages; Step 2.1.6: For incremental withdrawal, use the MySQL log file number + The log offset is used as a unique ID, which is a 64-bit long integer. The high 7 bits are used for the log file number, and the low 12 bits are used for the log offset. Step 2.2: For the extraction process, heartbeat monitoring and early warning are performed, including the following steps: Step 2.2.1: Insert one heartbeat data record into each extracted table every minute and save the sending time; Step 2.2.2: The heartbeat table is also extracted. When a heartbeat packet is received, even if there is no data added, deleted, or modified, it can prove that the link is working properly; Step 2.2.3: Send the heartbeat data to a public topic on Kafka, and then consume and save it to the open-source time-series database InfluxDB, using open-source data visualization. The application Grafana is used for visualization; Step 2.2.4: If a heartbeat delay occurs, an email or SMS alert is sent via monitoring; Step 2.3: Considering data security, real-time data anonymization is required, including the following steps: Step 2.3.1: Default anonymization, suitable for simple scenarios, configured to default values ​​and replaced with predefined field values; Step 2.3.2: Replacement anonymization, suitable for simple scenarios, configured to specified values ​​and replaced with predefined field values; Step 2.3.3: Hash anonymization, suitable for scenarios requiring mapping, using MD5 and murmur algorithms, providing salting functionality to ensure data consistency after hashing.

4. The data extraction and synchronization method based on Kafka and log according to claim 1, characterized in that: Step 3: Reload the data from Kafka into HDFS via the consumer, including the following steps: Step 3.1: Consume messages according to the unified message format in Kafka. Use Spark to generate a consumption stream from the messages in Kafka and consume messages from different namespaces; Step 3.2: Consume all unified messages in Kafka and save all historical information in HDFS, including the following steps: Step 3.2.1: Replay the logs in HDFS to restore historical snapshots at any time; Step 3.2.2: Restore the historical information of each record using a linked list and analyze the information logs; Step 3.2.3: When an error occurs in the program, re-consume the messages through a backfeedback mechanism to create a new snapshot; Step 3.4: Save the messages to HDFS and perform distributed storage, including the following steps: Step 3.3.1: Use a columnar storage format. Parquet stores a unified message format log in HDFS. The Parquet content includes all log entries, deletions, and modifications, as well as `_ums_id` and `_ums_ts_`. Due to Spark's excellent native support for Parquet, SparkSQL is used to query Parquet by date and time range for subsequent file merging. Step 3.3.2: Distribute the data to different directories based on the message's namespace; different tables and versions are placed in different directories. Step 3.3.3: Since the stored Parquet files are small, a job is started to merge these small files into larger files daily. Step 3.3.4: Add start and end times to the file directories. During data refeeding, the selected time range determines which files to read, rather than reading all data.

Images