A lock-free atomic batch write method for columnar database based on temporary intermediate table

Abstract

This invention provides a lock-free atomic batch write method for columnar databases based on temporary intermediate tables, comprising the following steps: S100: receiving data from a data source, splitting the data according to preset splitting conditions, grouping the data according to the splitting results, and storing the grouped data in a memory buffer; S200: when the memory buffer meets the triggering conditions, creating a corresponding temporary intermediate table in the target database for each batch of grouped data to be processed, naming the temporary intermediate table using a preset naming strategy, and then appending the data in the memory buffer to the temporary intermediate table through the corresponding interface; S300: performing different atomic merge operations according to different data operation scenarios; S400: committing the above transactions and deleting the temporary intermediate table; transforming complex row updates into batch appends and set deletions that columnar databases excel at, avoiding the high cost of processing updates in columnar storage.

Description

Technical Field

[0001] This invention relates to the technical field of data synchronization, and specifically to a lock-free atomic batch write method for columnar databases based on temporary intermediate tables. Background Technology

[0002] In heterogeneous data synchronization architectures, to achieve real-time data analysis, it is typically necessary to synchronize data from an online transactional database (OLTP, such as MySQL) to a heterogeneous target database (such as columnar databases like DuckDB and ClickHouse) in real time. The data synchronization process consists of two phases: initial full data loading and subsequent real-time synchronization of incremental logs (binlog).

[0003] In existing technologies, the following methods are typically used for data writing: 1) Row-by-row playback method: The synchronization tool parses the MySQL Binlog events and executes a corresponding INSERT, UPDATE, or DELETE SQL statement in the target columnar database for each WriteRows, UpdateRows, or DeleteRows event.

[0004] 2) Direct batch insertion method: The synchronization tool accumulates a certain amount of data (such as 1000 records) in memory, concatenates them into a huge INSERT INTO target_table VALUES (...), (...)... statement, and directly executes the write to the target table.

[0005] The defects and shortcomings of the above data writing method are as follows: 1) Severe write amplification and poor performance: Columnar databases store data column-wise and with high compression. For row-by-row update or delete operations, the database often needs to read the entire data block or even rewrite the entire file partition to apply the changes. High-frequency random write operations can lead to extremely severe write amplification, causing the CPU to be fully loaded and the disk I / O throughput to be extremely low.

[0006] 2) Intense lock contention, blocking queries: When performing batch writes directly to the target table, the database typically adds an exclusive write lock to the table or partition. Because the data parsing, transmission, and disk write processes are time-consuming (especially with large batches), the target table remains locked for an extended period, blocking read queries from the front-end analysis business and severely impacting system availability.

[0007] 3) SQL Protocol and Parameter Limitations: When using the direct batch insert method, the length of the concatenated SQL statement is limited by the database protocol's `max_allowed_packet` size, and the number of parameters in the prepared statement usually has a hard upper limit on placeholders. For wide tables with a large number of columns (e.g., 200 columns or more), this means that the number of rows that can be written in a single batch is significantly limited (e.g., only about 300 rows can be written), making it impossible to fully utilize the advantages of columnar storage for large-volume sequential writes, severely restricting throughput.

[0008] 4) Data consistency and atomicity risks: During direct batch writes, if the network is interrupted or the program crashes, some written data may remain in the target final table. Furthermore, if a batch of binlogs contains multiple modifications to the same primary key (e.g., insert followed by update), simple batch inserts can lead to primary key conflicts or data version errors.

[0009] Therefore, there is an urgent need to provide a new solution to address the defects and shortcomings of the existing technologies. Summary of the Invention

[0010] To address the shortcomings and deficiencies in the existing technology, this invention provides a lock-free atomic batch write method for columnar databases based on temporary intermediate tables.

[0011] The specific solution provided by this invention is as follows: A lock-free atomic batch write method for columnar databases based on temporary intermediate tables, characterized by the following steps: S100: Receives data from the data source, breaks down the data according to preset breakdown conditions, groups the data according to the breakdown results, and stores the grouped data into the memory buffer. S200: Determine whether the memory buffer meets the triggering conditions. When the result is that the memory buffer meets the triggering conditions, create a corresponding temporary intermediate table in the target database for each batch of grouped data to be processed, and name the temporary intermediate table using a preset naming strategy. Then, append the data in the memory buffer to the temporary intermediate table through the corresponding interface. S300: After the data in the temporary intermediate table is written and written to disk, a database transaction is started, and different atomic merge operations are performed according to the different operation scenarios of the data. If an error occurs during the execution of step S300, all modifications to the target formal table in this transaction are undone, the target formal table is restored to the state before this operation, and step S300 is re-executed. S400: Commit the above transaction and delete the temporary intermediate table.

[0012] As a further preferred embodiment of the present invention, step S100, which involves disassembling the data according to preset disassembly conditions, includes the following steps: S101: Extract and decompose the foreimage data from the data source into deletion operation data, and extract and decompose the afterimage data from the data source into insertion operation data; S102: The original deleted data from the data source is retained and decomposed into deleted operation data, and the original inserted data from the data source is retained and decomposed into inserted operation data.

[0013] As a further preferred embodiment of the present invention, in step S100, when grouping the data according to the disassembly results, the data is grouped according to the database name where the data is located in the data source, the target formal table name, and the operation type of the disassembly results.

[0014] In a further preferred embodiment of the present invention, in step S200, the triggering condition includes: 1) The memory buffer stores more data than the preset size; or 2) The memory buffer has been storing data for longer than the preset time.

[0015] As a further preferred embodiment of the present invention, in step S200, when naming the temporary intermediate table using a preset naming strategy, a naming strategy including hash encoding, operation type and nanosecond-level timestamp is used to name the temporary intermediate table. The name of the temporary intermediate table shall include at least the following fields: fixed prefix, hash code of database name, hash code of target formal table name, operation type, and nanosecond-level timestamp.

[0016] As a further preferred embodiment of the present invention, in step S200, appending data in the memory buffer to the temporary intermediate table through the corresponding interface includes: directly appending data in the memory buffer to the temporary intermediate table in binary or raw format through the Appender interface of the columnar database.

[0017] As a further preferred embodiment of the present invention, in step S300, when performing different atomic merging operations according to different data operation scenario types, the different data operation scenario types include at least: S301: Data insertion operation type; S302: Data deletion operation type; S303: Full data load operation type.

[0018] As a further preferred embodiment of the present invention, in step S300, when performing different atomic merging operations according to different data operation scenario types, the atomic merging operation performed for the data insertion operation type includes the following steps: S3011: Set cleanup: Traverse the target formal table and obtain all primary key records in the target formal table; compare all primary key records in the target formal table with the primary key records existing in the temporary intermediate table one by one; delete the primary key records that are the same in the target formal table and the temporary intermediate table, and keep the primary key records that are different in the target formal table and the temporary intermediate table. S3012: Deduplication and Insertion: Determine if there are multiple records with the same primary key in the temporary intermediate table. If so, first group the records with the same primary key according to the primary key, then sort the records with the same primary key according to the offset or row number, keep the last record in each group, delete the rest of the data, and then insert it into the target formal table.

[0019] As a further preferred embodiment of the present invention, in step S300, when performing different atomic merging operations according to different data operation scenario types, the atomic merging operations performed for the data deletion operation type include: S3021: Use a temporary intermediate table as a filter to delete records in batches that match the primary key of the target main table and the temporary intermediate table.

[0020] As a further preferred embodiment of the present invention, in step S300, when performing different atomic merging operations according to different data operation scenario types, the atomic merging operations performed for the full data loading operation type include: S3031: During the full initialization phase, all internal data of the temporary intermediate table is imported into the target formal table.

[0021] Compared with existing technologies, the technical effects that this invention can achieve include: 1) This invention provides a lock-free atomic batch write method for columnar databases based on temporary intermediate tables. By extracting and decomposing the previous image data from the data source into deletion operation data, extracting and decomposing the subsequent image data from the data source into insertion operation data, and combining this with retaining and decomposing the original deletion data from the data source into deletion operation data and retaining and decomposing the original insertion data from the data source into insertion operation data, the complex row update is transformed into batch append and set delete, which columnar databases excel at, thus avoiding the high cost of in-place updates in columnar storage.

[0022] 2) This invention provides a lock-free atomic batch write method for columnar databases based on temporary intermediate tables. It adopts a dynamic lock-free temporary intermediate table mechanism: by using a preset naming strategy to name the temporary intermediate table, that is, to name the temporary intermediate table using a naming strategy that includes hash encoding, operation type and nanosecond-level timestamp, the time-consuming network transmission and data disk writing process is physically isolated from the target formal table, completely eliminating the blocking of business queries during the data writing stage.

[0023] 3) This invention provides a lock-free atomic batch write method for columnar databases based on temporary intermediate tables. Data in the memory buffer is appended to the temporary intermediate table through the corresponding interface. That is, the data in the memory buffer is directly appended to the temporary intermediate table in binary or raw format through the Appender interface of the columnar database. Instead of using standard INSERT SQL statements, the underlying API (Appender) is called to directly perform memory-to-file dumping, which greatly reduces CPU overhead.

[0024] 4) This invention provides a lock-free atomic batch write method for columnar databases based on temporary intermediate tables. Based on set cleanup and deduplication insertion algorithms, it can automatically handle out-of-order and duplicate update problems within binlog batches during the SQL merging stage, ensuring data consistency and idempotency.

[0025] 5) This invention provides a lock-free atomic batch write method for columnar databases based on temporary intermediate tables, which significantly improves write performance: According to actual tests, compared with the method of executing SQL row by row, this invention reduces CPU utilization by 80% and increases throughput by 100 to 500 times when processing binlog streams of more than 200,000 records / second, and can fully utilize the disk and sequentially write bandwidth.

[0026] 6) This invention provides a lock-free atomic batch write method for columnar databases based on temporary intermediate tables, with zero awareness of business queries: Since the locking of the target formal table only occurs at the millisecond level of transaction commit, the front-end business query is almost unaware of the existence of the lock, realizing true high-concurrency read and write.

[0027] 7) This invention provides a lock-free atomic batch write method for columnar databases based on temporary intermediate tables, ensuring strong data consistency: the atomicity of data updates is guaranteed by wrapping the merging logic within database transactions. Even if the system crashes due to power failure, only the residual temporary tables need to be cleaned up after restarting, and the target data will not be corrupted, eliminating the need for complex breakpoint resume logic. Attached Figure Description

[0028] Figure 1 The diagram shown is a flowchart of the steps of the method provided by the present invention. Detailed Implementation

[0029] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0030] In the description of this invention, it should be noted that the terms "upper," "lower," "inner," "outer," "front end," "rear end," "both ends," "one end," and "the other end," etc., indicate the orientation or positional relationship based on the orientation or positional relationship shown in the accompanying drawings. They are used only for the convenience of describing this invention and for simplifying the description, and do not indicate or imply that the device or element referred to must have a specific orientation, or be constructed and operated in a specific orientation. Therefore, they should not be construed as limitations on this invention. Furthermore, the terms "first" and "second" are used for descriptive purposes only and should not be construed as indicating or implying relative importance.

[0031] In the description of this invention, it should be noted that, unless otherwise explicitly specified and limited, the terms "installed," "equipped with," "connected," etc., should be interpreted broadly. For example, "connection" can be a fixed connection, a detachable connection, or an integral connection; it can be a mechanical connection or an electrical connection; it can be a direct connection or an indirect connection through an intermediate medium; it can be a connection within two components. Those skilled in the art can understand the specific meaning of the above terms in this invention based on the specific circumstances.

[0032] [First Embodiment] like Figure 1 The first embodiment of the present invention provides a lock-free atomic batch write method for columnar databases based on temporary intermediate tables, comprising the following steps: S100: Receives data from a data source. It can receive data from a data source (such as a MySQLBinlog stream or a full query result stream) by setting a buffer, splits the data according to preset splitting conditions, groups the data according to the splitting results, and stores the grouped data into a memory buffer. In this embodiment, disassembling the data according to preset disassembly conditions includes the following steps: S101: Extract and decompose the before image data from the data source into delete operation data, and extract and decompose the after image data from the data source into insert operation data. For the column update (UpdateRows) event in the binary log, it contains two data images: before image and after image. In this embodiment, it is decomposed into two independent atomic operations: extract the before image data and decompose it into delete operation data, which is used to clear the old version of the Delete operation data; extract the after image data and decompose it into insert operation data, which is used to write the new version of the Insert operation data.

[0033] S102: The original deleted data from the data source is retained and decomposed into deleted operation data, and the original inserted data from the data source is retained and decomposed into inserted operation data. For the original deleted data DeleteRows (Delete) and inserted data WriteRows (Insert), they are directly retained and decomposed into inserted operation data and deleted operation data.

[0034] By using this decomposition and pass-through method, all complex binlog events (such as Insert, Update, Delete, etc.) can be normalized into a set of simple Insert and Delete operations.

[0035] Based on this, when grouping the data according to the decomposition results, the data is grouped according to the database name where the data is located in the data source, the name of the target formal table, and the operation type of the decomposition results.

[0036] in, Database name: refers to the name of the database from which the data originates; Target Official Table Name: Refers to the name of the target official table corresponding to this data; Operation types for disassembly results: These include insertion or deletion operations on the data obtained after disassembly; For example, if the database name is A1, the target table name is B1, and the operation type is a delete operation represented by D, the grouping method is to group the database name, the target table name, and the operation type of the decomposed result as a whole, which can be represented as: A1-B1-D.

[0037] S200: Determine whether the memory buffer meets the triggering conditions. When the result is that the memory buffer meets the triggering conditions, create a corresponding temporary intermediate table in the target database for each batch of grouped data to be processed, and name the temporary intermediate table using a preset naming strategy. Then, append the data in the memory buffer to the temporary intermediate table through the corresponding interface. In this embodiment, the triggering conditions include: 1) The memory buffer stores more data than the preset size; or 2) The memory buffer has been storing data for longer than the preset time.

[0038] When using a preset naming strategy to name temporary intermediate tables, a naming strategy that includes hash encoding, operation type, and nanosecond-level timestamp is used. The name of the temporary intermediate table must include at least the following fields: fixed prefix, hash encoding of the database name, hash encoding of the target formal table name, operation type, and nanosecond-level timestamp.

[0039] For example, the intermediate table can be named as: stage_{hex(db)}_{hex(table)}_{op}_{timestamp}; The meanings of each field are as follows: "stage" indicates a fixed prefix for temporary intermediate tables; hex(db) is a hash code for the database name, which is used to distinguish different databases; hex(table) is the hash code of the target official table name, which is used to distinguish different target official tables; op indicates the operation type, namely, insert or delete operation; The timestamp represents a nanosecond-level timestamp, which is used to ensure the uniqueness of table names; This naming strategy ensures that table names are globally unique, allows multiple threads to process different batches of the same logical table in parallel, and does not lock the target formal table at all during this stage. It physically isolates the most time-consuming network transmission and data write-to-disk process from the target formal table, completely eliminating the blocking of business queries during the data write stage.

[0040] Appending data from the memory buffer to a temporary intermediate table via the corresponding interface includes: directly appending data from the memory buffer to the temporary intermediate table in binary or raw format via the underlying Appender interface of the columnar database. Instead of using standard INSERT SQL statements, it calls the underlying API (Appender) to directly perform memory-to-file dumping, which significantly reduces CPU overhead. This process bypasses the SQL parser and optimizer, achieving write speeds close to the disk bandwidth limit.

[0041] S300: After the data in the temporary intermediate table is written and persisted to disk, a database transaction is initiated, and different atomic merge operations are performed according to the different data operation scenarios. In this embodiment, when performing different atomic merge operations according to the different data operation scenarios, the different data operation scenarios include at least the following: S301: Data insertion operation type. Atomic merge operations performed for data insertion operation types include: S3011: Set cleanup: Traverse the target formal table and obtain all primary key records in the target formal table; compare all primary key records in the target formal table with the primary key records existing in the temporary intermediate table one by one; delete the primary key records that are the same in the target formal table and the temporary intermediate table, and keep the primary key records that are different in the target formal table and the temporary intermediate table. This involves using a semi-join deletion method, where the primary key in a temporary intermediate table is used to search for and delete records with the same primary key in the target formal table.

[0042] It doesn't require actually merging the data from the two tables; it only needs to check if the primary key of a record in the target table exists in the temporary intermediate table. If it does, the record is deleted. This process is known as the semi-join deletion method.

[0043] For example: The target formal table contains: (id=1, name=A); (id=2, name=B); (id=3, name=C); The temporary intermediate table contains: (id=2); (id=3); After performing this cleanup step, the records with id=2 and id=3 in the target table will be deleted, leaving only the following record: (id=1, name=A); The cleanup process for this collection can be implemented in the following way: DELETE FROM target_table WHERE id IN (SELECT id FROM staging_table); If the primary key consists of multiple fields, then all primary key fields that are the same as those in the temporary intermediate table and exist in the target formal table will be matched and deleted together.

[0044] Therefore, the purpose of this cleanup step is to first delete the old records in the target formal table that have duplicate primary keys with the temporary intermediate table, in order to prepare for the subsequent insertion of new version data.

[0045] S3012: Deduplication and Insertion: Determine if there are multiple records with the same primary key in the temporary intermediate table. If so, first group the records with the same primary key according to the primary key, then sort the records with the same primary key according to the offset or row number, keep the last record in each group, delete the rest of the data, and then insert it into the target formal table.

[0046] In this embodiment, deduplication means that if there are multiple records with the same primary key in the temporary intermediate table, only the last one is kept and then inserted into the target formal table.

[0047] Its specific practices include: First, group multiple records with the same primary key by the primary key, then sort them by offset or row number. Take the last record in each sorted group, which is considered to be the latest version corresponding to that primary key, and do not insert other earlier records.

[0048] For example, the temporary intermediate table contains the following data: (id=1,name=A,row_no=10); (id=1,name=B,row_no=11); (id=2,name=C,row_no=12); The primary key id=1 appeared twice, indicating that the same primary key data changed multiple times in the same batch.

[0049] After grouping by primary key id, sort by row number (row_no) in descending order: If the data with id=1 and row_no=11 is the latest version, then keep it; if the data with row_no=10 is the old version, then delete it. For id=2, there is only one record, so it is kept directly.

[0050] Therefore, the data ultimately inserted into the target table is: (id=1,name=B,row_no=11); (id=2,name=C,row_no=12); The cleanup process for this collection can be implemented in the following way: SELECT* FROMstaging_table QUALIFYROW_NUMBER()OVER(PARTITIONBYidORDERBYrow_noDESC)=1; Similarly, if the primary key consists of multiple fields, then the multiple primary key fields are treated as a whole and the above deduplication and insertion steps are performed.

[0051] Therefore, the purpose of this deduplication and insertion step is to select the latest data for each primary key from the temporary intermediate table before insertion, so as to avoid repeated insertion of the same primary key and ensure that the writing result is correct.

[0052] This scenario encompasses both the original WriteRows event and the After Image resulting from the decomposition of UpdateRows. Since a batch may contain multiple changes to the same row (e.g., inserting ID=1 at time T1, updating ID=1 at time T2), this embodiment employs a strategy of "delete first, then insert" combined with "window function deduplication": the eventual consistency check of the data can be performed in memory using procedures such as the QUALIFY clause, transforming multiple random updates into a single batch deletion and a single batch insertion, consistent with columnar storage characteristics.

[0053] S302: Data deletion operation type; this scenario covers both the original DeleteRows event and the Before Image after disassembling UpdateRows; the atomic merge operations performed for this data deletion operation type include: S3021: Using a temporary intermediate table as a filter, batch delete records whose primary keys match those in the target main table and the temporary intermediate table. This can be implemented using the following program: DELETE FROM target_table WHERE (pk_col1, ...) IN (SELECT pk_col1, ... FROM staging_table); in, pk_col1 represents a primary key field, such as id or a combination of multiple primary key fields; staging_table represents a temporary intermediate table; The meaning of this statement is: use the primary key in the temporary intermediate table to match and delete the corresponding matching record in the target formal table.

[0054] S303: Full data load operation type. The atomic merge operations performed for the full data load operation type include: S3031: During the full initialization phase, all internal data of the temporary intermediate table is imported into the target formal table.

[0055] If an error occurs during the execution of step S300, all modifications to the target formal table within this transaction will be undone, restoring the target formal table to its state before this operation. After the rollback, the data in the target formal table will not change, and step S300 will be re-executed. The number of retries can be set according to actual needs. S400: Commit the above transaction and delete the temporary intermediate table. The temporary intermediate table is only used for intermediate data processing and is not stored as the final result. It will be deleted after the transaction is completed.

[0056] It will be apparent to those skilled in the art that the present invention is not limited to the details of the exemplary embodiments described above, and that the invention can be implemented in other specific forms without departing from its spirit or essential characteristics. Therefore, the embodiments should be considered in all respects as exemplary and non-limiting, and the scope of the invention is defined by the appended claims rather than the foregoing description. Thus, all variations falling within the meaning and scope of equivalents of the claims are intended to be included within the present invention. No reference numerals in the claims should be construed as limiting the scope of the claims.

Claims

1. A lock-free atomic batch write method for columnar databases based on temporary intermediate tables, characterized in that: Includes the following steps: S100: Receives data from the data source, breaks down the data according to preset breakdown conditions, groups the data according to the breakdown results, and stores the grouped data into the memory buffer. S200: Determine whether the memory buffer meets the triggering conditions. When the result is that the memory buffer meets the triggering conditions, create a corresponding temporary intermediate table in the target database for each batch of grouped data to be processed, and name the temporary intermediate table using a preset naming strategy. Then, append the data in the memory buffer to the temporary intermediate table through the corresponding interface. S300: After the data in the temporary intermediate table is written and written to disk, a database transaction is started, and different atomic merge operations are performed according to the different operation scenarios of the data. If an error occurs during the execution of step S300, all modifications to the target formal table in this transaction are undone, the target formal table is restored to the state before this operation, and step S300 is re-executed. S400: Commit the above transaction and delete the temporary intermediate table.

2. The lock-free atomic batch write method for columnar databases based on temporary intermediate tables according to claim 1, characterized in that: In step S100, disassembling the data according to preset disassembly conditions includes the following steps: S101: Extract and decompose the foreimage data from the data source into deletion operation data, and extract and decompose the afterimage data from the data source into insertion operation data; S102: The original deleted data from the data source is retained and decomposed into deleted operation data, and the original inserted data from the data source is retained and decomposed into inserted operation data.

3. The lock-free atomic batch write method for columnar databases based on temporary intermediate tables according to claim 1, characterized in that: In step S100, when grouping the data according to the decomposition results, the data is grouped according to the database name where the data is located in the data source, the name of the target formal table, and the operation type of the decomposition results.

4. A lock-free atomic batch write method for columnar databases based on temporary intermediate tables according to claim 1, characterized in that: In step S200, the triggering conditions include: 1) The memory buffer stores more data than the preset size; or 2) The memory buffer has been storing data for longer than the preset time.

5. The lock-free atomic batch write method for columnar databases based on temporary intermediate tables according to claim 1, characterized in that: In step S200, when naming the temporary intermediate table using a preset naming strategy, a naming strategy that includes hash encoding, operation type, and nanosecond-level timestamp is used to name the temporary intermediate table. The name of the temporary intermediate table shall include at least the following fields: fixed prefix, hash code of database name, hash code of target formal table name, operation type, and nanosecond-level timestamp.

6. A lock-free atomic batch write method for columnar databases based on temporary intermediate tables according to claim 1, characterized in that: In step S200, appending data from the memory buffer to the temporary intermediate table via the corresponding interface includes: directly appending data from the memory buffer to the temporary intermediate table in binary or raw format via the Appender interface of the columnar database.

7. The lock-free atomic batch write method for columnar databases based on temporary intermediate tables according to claim 1, characterized in that: In step S300, when performing different atomic merging operations according to different data operation scenario types, the different data operation scenario types include at least: S301: Data insertion operation type; S302: Data deletion operation type; S303: Full data load operation type.

8. The lock-free atomic batch write method for columnar databases based on temporary intermediate tables according to claim 7, characterized in that: In step S300, when performing different atomic merge operations according to different data operation scenario types, the atomic merge operation performed for the data insertion operation type includes the following steps: S3011: Set cleanup: Traverse the target formal table and obtain all primary key records in the target formal table; compare all primary key records in the target formal table with the primary key records existing in the temporary intermediate table one by one; delete the primary key records that are the same in the target formal table and the temporary intermediate table, and keep the primary key records that are different in the target formal table and the temporary intermediate table. S3012: Deduplication and Insertion: Determine if there are multiple records with the same primary key in the temporary intermediate table. If so, first group the records with the same primary key according to the primary key, then sort the records with the same primary key according to the offset or row number, keep the last record in each group, delete the rest of the data, and then insert it into the target formal table.

9. A lock-free atomic batch write method for columnar databases based on temporary intermediate tables according to claim 7, characterized in that: In step S300, when performing different atomic merge operations according to different data operation scenario types, the atomic merge operations performed for data deletion operation types include: S3021: Use a temporary intermediate table as a filter to delete records in batches that match the primary key of the target main table and the temporary intermediate table.

10. A lock-free atomic batch write method for columnar databases based on temporary intermediate tables according to claim 7, characterized in that: In step S300, when performing different atomic merge operations according to different data operation scenario types, the atomic merge operations performed for the full data load operation type include: S3031: During the full initialization phase, all internal data of the temporary intermediate table is imported into the target formal table.

Images