A real-time data warehouse ETL method for parallel parsing of business library logs

Abstract

The present invention discloses a real-time data warehouse ETL method for parallel parsing of business library logs. The real-time data warehouse ETL method for parallel parsing of business library logs includes the following steps: the business library generates a change log, a log parsing service parses and replays the change log, the log parsing service sends the parsed structure to a message queue, and a real-time synchronization program consumes the data in the message queue to achieve data synchronization. In the industry, in the log parsing process, in order to achieve accurate once, the serial parsing method is basically maintained. This method will cause serious delays under high load. Using parallel instead of serial has higher throughput. Parsing logs in a serial manner also relies on the performance of a single-core CPU. Even if the server is multi-core, it cannot fully utilize the resources. Parallel parsing can use cheaper CPUs to reduce usage costs. It can be deployed across servers and use cheaper servers for parallel parsing to achieve horizontal expansion.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular to a real-time data warehouse ETL method for parallel parsing of business library logs. Background Art

[0002] Currently, the first step in building a real-time data warehouse is to synchronize data from traditional databases to the data warehouse. This requires meeting the following two characteristics: 1. Data timeliness (delay): The time it takes for data to be synchronized from the traditional database to the data warehouse; 2. Data accuracy (exactly once, at least once): Data is verified multiple times before being synchronized from the traditional database to the data warehouse to confirm its correctness. During the verification process, all data must be verified accurately once. The most common architecture is: MySQL->Binlog->Canal->Kafka->Flink (Spark)->Hive (Hudi). To ensure the "exactly once" nature of data, various solutions are used. The coupled components all have corresponding compatible features, and among the components that replay historical updates, common components have the problem of single-threaded consumption. For further detailed explanation, the applicant will use Canal as an example to evaluate the problems and defects of existing similar products. In the process of parsing binlogs, Canal adopts a single-threaded parsing method to ensure the order of consumption. This serialization will not cause accumulation and data delay when the upstream binlog log generation is slower than Canal parsing. However, when a large number of binlog logs are generated, Canal will consume accumulation. If the upstream is always under high pressure, the delay will increase, causing a vicious cycle and eventually crashing. Summary of the Invention

[0003] (1) Technical problems solved

[0004] In view of the shortcomings of the existing technology, the present invention provides a real-time data warehouse ETL method for parallel parsing of business library logs, which solves the problems raised in the above background technology.

[0005] (2) Technical methods

[0006] To achieve the above objectives, the present invention provides the following technical method: a real-time data warehouse ETL method for parallel parsing of business library logs. The real-time data warehouse ETL method for parallel parsing of business library logs includes: the business library generates a change log, a log parsing service parses and replays the change log, the log parsing service sends the parsed structure to a message queue, and a real-time synchronization program consumes the data in the message queue to achieve data synchronization. The specific operations are as follows:

[0007] Step 1: Generate a change log in the business library

[0008] (1) Determine whether the data page is in memory. If not, read the data from disk into memory and return the data row;

[0009] (2) If the data page is in memory, the data row is returned directly;

[0010] (3) Perform data update operations;

[0011] (4) Data is written to memory, and redolog is written to memory at the same time;

[0012] (5) Execute the commit operation; (This commit is an SQL command operation, not the commit status of the data);

[0013] (6) After executing the commit command, a two-stage commit operation is performed;

[0014] (7) Write the redolog in memory to disk. At this time, the redolog is in the prepare state.

[0015] (8) Write the log file to disk;

[0016] (9) Commit the transaction. At this time, the transaction is in the commit state;

[0017] Step 2: Log parsing service parses and replays change logs

[0018] (1) First, a heartbeat thread is started. This heartbeat thread only acts on the parser module and the sink module, and pushes a heartbeat message to the sink module every second;

[0019] (2) Before creating a connection with the database node, some preparation work needs to be done, such as determining the value of the log file's FORMAT and the log file's _row_image parameter;

[0020] (3) Establish a connection with the database. The Master address port and the corresponding database username and password are configured in the instance.properties file. This information is used to establish a connection with the Master.

[0021] (4) Get the ServerId from the database;

[0022] (5) Get the last location information, that is, the location of the log file read after the last communication with the Master. This location information will also be written to the meta.dat file. Therefore, if it is not in the memory, it will try to read it from the file.

[0023] (6) Reconnect, because the state may change during the position search process and needs to be disconnected and rebuilt;

[0024] (7) Call the dump() method to start obtaining pbinglog data, and register a callback event to receive the returned log file data;

[0025] (8) Sleep for 10 seconds and re-execute the above process;

[0026] Step 3: The log parsing service sends the parsed structure to the message queue

[0027] (1) When the callback event in the second step is returned, the processing flow is to call the parse() method of the log file Parser class to parse the returned binary log file data LogEvent into a canal-encapsulated Entry event. The LogEvent contains an event type eventType, which distinguishes in detail whether the event is a query operation, a write operation, or a heartbeat, etc.

[0028] (2) Call the add() method of the EventTransactionBuffer class to add the Entry event to a ringbuffer of size 1024. Under certain conditions, the EventTransactionBuffer will push the Entry event in the ringbuffer to the sink module;

[0029] Step 4: Real-time synchronization of data in the program consumption message queue to achieve data synchronization

[0030] (1) The synchronization program pulls data from the message queue in batch time;

[0031] (2) De-duplication of data based on its uniqueness;

[0032] (3) Use open source API to write to the database.

[0033] Preferably, the business library configures a record log file and evaluates the size of the log file according to the downstream consumption capacity.

[0034] Preferably, the log parsing components are distributedly deployed on each server.

[0035] Preferably, the log parsing component monitors the generation of upstream log files, and performs parsing processing if the monitored file number belongs to the service processing.

[0036] Preferably, after the log parsing component completes the parsing, the result data is sent to a message queue component that supports repeatable consumption.

[0037] Preferably, the business end implements a business program that supports repeatable consumption of message queue components to ensure data accuracy.

[0038] (3) Beneficial effects

[0039] The present invention provides a real-time data warehouse ETL method for parallel parsing of business library logs, which has the following beneficial effects:

[0040] (1) This parallel parsing of business library logs in real-time data warehouse ETL method, in the log parsing process, in order to achieve exactly once, the industry basically maintains the serial parsing method. This method will cause serious delays under high load. Using parallel instead of serial can achieve higher throughput.

[0041] (2) The real-time data warehouse ETL method for parallel parsing of business library logs uses a serial method to parse logs. At the same time, it relies on the performance of a single-core CPU. Even if the server has multiple cores, it cannot fully utilize the resources. Parallel parsing can use cheaper CPUs.

[0042] (3) In the specific implementation process of the real-time data warehouse ETL method for parallel parsing of business library logs, in addition to parallel processing, we can also deploy it across servers and use cheaper servers for parallel parsing to achieve horizontal expansion. BRIEF DESCRIPTION OF THE DRAWINGS

[0043] Figure 1 It is a schematic diagram of the process of the present invention. DETAILED DESCRIPTION

[0044] Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making any creative work shall fall within the scope of protection of the present invention.

[0045] The present invention provides a technical method: a real-time data warehouse ETL method for parallel parsing of business library logs, comprising: a business library generating a change log, a log parsing service parsing and replaying the change log, the log parsing service sending the parsed structure to a message queue, and a real-time synchronization program consuming the data in the message queue to achieve data synchronization. The specific operations are as follows:

[0046] Step 1: The business library generates a change log, configures the log file, and evaluates the size of the log file based on the downstream consumption capacity.

[0047] (1) Determine whether the data page is in memory. If not, read the data from disk into memory and return the data row;

[0048] (2) If the data page is in memory, the data row is returned directly;

[0049] (3) Perform data update operations;

[0050] (4) Data is written to memory, and redolog is written to memory at the same time;

[0051] (5) Execute the commit operation; (This commit is an SQL command operation, not the commit status of the data);

[0052] (6) After executing the commit command, a two-stage commit operation is performed;

[0053] (7) Write the redolog in memory to disk. At this time, the redolog is in the prepare state.

[0054] (8) Write the log file to disk;

[0055] (9) Commit the transaction. At this time, the transaction is in the commit state;

[0056] Step 2: The log parsing service parses and replays the change log. The log parsing component is distributed and deployed on each server. The log parsing component monitors the generation of upstream log files. If the monitored file number belongs to the service processing, it will be parsed and processed.

[0057] (1) First, a heartbeat thread is started. This heartbeat thread only acts on the parser module and the sink module, and pushes a heartbeat message to the sink module every second;

[0058] (2) Before creating a connection with the database node, some preparation work needs to be done, such as determining the value of the log file's FORMAT and the log file's _row_image parameter;

[0059] (3) Establish a connection with the database. The Master address port and the corresponding database username and password are configured in the instance.properties file. This information is used to establish a connection with the Master.

[0060] (4) Get the ServerId from the database;

[0061] (5) Get the last location information, that is, the location of the log file read after the last communication with the Master. This location information will also be written to the meta.dat file. Therefore, if it is not in the memory, it will try to read it from the file.

[0062] (6) Reconnect, because the state may change during the position search process and needs to be disconnected and rebuilt;

[0063] (7) Call the dump() method to start obtaining pbinglog data, and register a callback event to receive the returned log file data;

[0064] (8) Sleep for 10 seconds and re-execute the above process;

[0065] Step 3: The log parsing service sends the parsed structure to the message queue. After the log parsing component completes the parsing, it sends the result data to the message queue component that supports repeatable consumption.

[0066] (1) When the callback event in the second step is returned, the processing flow is to call the parse() method of the log file Parser class to parse the returned binary log file data LogEvent into a canal-encapsulated Entry event. The LogEvent contains an event type eventType, which distinguishes in detail whether the event is a query operation, a write operation, or a heartbeat, etc.

[0067] (2) Call the add() method of the EventTransactionBuffer class to add the Entry event to a ringbuffer of size 1024. Under certain conditions, the EventTransactionBuffer will push the Entry event in the ringbuffer to the sink module;

[0068] Step 4: The real-time synchronization program consumes the data in the message queue to achieve data synchronization. The business end implements business programs that support repeatable consumption of message queue components to ensure data accuracy.

[0069] (1) The synchronization program pulls data from the message queue in batch time;

[0070] (2) De-duplication of data based on its uniqueness;

[0071] (3) Use open source API to write to the database.

[0072] In summary, during the specific implementation of this real-time data warehouse ETL method for parallel parsing of business library logs, the industry basically maintains a serial parsing method in order to achieve accurate parsing during the log parsing process. This method will cause serious delays under high load. Using parallel instead of serial can achieve higher throughput. However, using a serial method to parse logs also relies on the performance of a single-core CPU. Even if the server has multiple cores, it cannot fully utilize the resources. Parallel parsing can use cheaper CPUs to reduce usage costs. In the specific implementation process, in addition to parallel processing, we can also deploy across servers and use cheaper servers for parallel parsing to achieve horizontal expansion.

[0073] It should be noted that, in this document, relational terms such as first and second, etc., are used only to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any actual relationship or order between these entities or operations. Moreover, the terms "comprises," "comprising," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that includes a list of elements includes not only those elements but also other elements not explicitly listed, or elements inherent to such process, method, article, or apparatus.

[0074] While embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions, and variations may be made to these embodiments without departing from the principles and spirit of the invention, and that the scope of the invention is defined by the appended claims and their equivalents.

Claims

1. A real-time data warehouse ETL method for parallel parsing of business library logs, characterized by: The real-time data warehouse ETL method for parallel parsing of business library logs includes the following steps: the business library generates a change log, the log parsing service parses and replays the change log, the log parsing service sends the parsed structure to the message queue, and the real-time synchronization program consumes the data in the message queue to achieve data synchronization. The specific operations are as follows: Step 1: Generate a change log in the business library (1) Determine whether the data page is in memory. If not, read the data from disk into memory and return the data row; (2) If the data page is in memory, the data row is returned directly; (3) Perform data update operations; (4) Data is written to memory, and redolog is written to memory at the same time; (5) Execute the commit operation. This commit is an SQL command operation, not the commit status of the data. (6) After executing the commit command, a two-stage commit operation is performed; (7) Write the redolog in memory to disk. At this time, the redolog is in the prepare state; (8) Write log files to disk; (9) Commit the transaction. At this time, the transaction is in the commit state; Step 2: Log parsing service parses and replays change logs (1) First, a heartbeat thread will be started. This heartbeat thread only acts on the parser module and the sink module, and pushes a heartbeat message to the sink module every second; (2) Before creating a connection with the database node, you need to do some preparation to determine the value of the log file's FORMAT and the log file's _row_image parameter; (3) Establish a connection with the database. The Master address port and the corresponding database username and password are configured in the instance.properties file. This information is used here to establish a connection with the Master. (4) Get the ServerId from the database; (5) Get the last location information, that is, the location of the log file read after the last communication with the Master. This location information will also be written to the meta.dat file, so if it is not in the memory, it will try to read from the file; (6) Reconnect, because the state may change during the position search process and needs to be disconnected and rebuilt; (7) Call the dump() method to start obtaining pbinglog data, and register a callback event to receive the returned log file data; (8) Sleep for 10 seconds and then re-execute the above step 2; Step 3: The log parsing service sends the parsed structure to the message queue (1) When the callback event in the second step is returned, the processing flow is to call the parse() method of the log file Parser class to parse the returned binary log file data LogEvent into a canal-encapsulated Entry event. The LogEvent contains an event type eventType, which distinguishes in detail whether the event is a query operation, a write operation, or various types of heartbeats; (2) Call the add() method of the EventTransactionBuffer class to add the Entry event to a ringbuffer of size 1024. EventTransactionBuffer will push the Entry event in the ringbuffer to the sink module; Step 4: Real-time synchronization of data in the program consumption message queue to achieve data synchronization (1) The synchronization program pulls data from the message queue in batch time; (2) De-duplication of data based on its uniqueness; (3) Use open source API to write to the database.

2. A real-time data warehouse ETL method for parallel parsing of business library logs according to claim 1, characterized in that: The business library configures and records log files, and evaluates the size of the log files based on the downstream consumption capacity.

3. A real-time data warehouse ETL method for parallel parsing of business library logs according to claim 1, characterized in that: The log parsing components are distributed and deployed on each server.

4. A real-time data warehouse ETL method for parallel parsing of business library logs according to claim 1, characterized in that: The log parsing component monitors the generation of upstream log files, and performs parsing processing if the monitored file number belongs to the service processing.

5. A real-time data warehouse ETL method for parallel parsing of business library logs according to claim 4, characterized in that: After the log parsing component completes the parsing, the result data is sent to the message queue component that supports repeatable consumption.

6. A real-time data warehouse ETL method for parallel parsing of business library logs according to claim 4, characterized in that: The business side implements business procedures that support repeatable consumption of message queue components to ensure data accuracy.

Images