Method, device and electronic equipment for bulk importing data into a database

Abstract

The application provides a method and device for batch importing data into a database and electronic equipment, and relates to the technical field of data writing. The method comprises the following steps: in the case that a data import request for a target database is received, the data import request is analyzed to obtain data import parameters, the data import parameters comprising a data import mode, an overwrite parameter, a primary key conflict strategy and a target storage original table; in the case that the data import parameters indicate incremental data import, offline data to be written is written into the target storage original table through a direct loading protocol; in the case that the data import parameters indicate full data import, a target temporary storage table is created based on the target storage original table, and the offline data to be written is written into the target temporary storage table through the direct loading protocol, and the target temporary storage table is renamed as the target storage original table after writing is completed. The method can improve the batch data import performance of the database.

Description

Technical Field

[0001] This application relates to the field of data writing technology, and in particular to a method, apparatus, and electronic device for batch importing data into a database. Background Technology

[0002] In fields such as finance and the internet, it is often necessary to import all data generated by offline computing platforms into online databases. In traditional batch data import methods, data needs to pass through an SQL (Structured Query Language) parser, optimizer, transaction log (Write-Ahead Log / Redo Log), and buffer pool. The CPU (Central Processing Unit) resources and I / O (Input / Output) overhead required during batch data import are extremely high, and the write path is lengthy, which limits the performance of batch data import. Summary of the Invention

[0003] This application provides a method, apparatus, and electronic device for batch importing data into a database, which can improve the performance of batch data import. The technical solution is as follows: According to one aspect of this application, a method for batch importing data into a database is provided, the method comprising: Upon receiving a data import request to the target database, the data import request is parsed to obtain data import parameters. The data import parameters include data import mode, overwrite write parameters, primary key conflict strategy, and target storage original table. The target storage original table is a storage table in the target database used to write the offline data to be written as indicated by the data import request. When the data import parameters indicate that incremental data import is to be performed, the offline data to be written is written to the target storage original table through the direct loading protocol; When the data import parameters indicate that a full data import is to be performed, a target temporary storage table is created based on the target original storage table, and the offline data to be written is written to the target temporary storage table through the direct loading protocol. After the writing is completed, the target temporary storage table is renamed to the target original storage table.

[0004] According to another aspect of this application, an apparatus for batch importing data into a database is provided, the apparatus comprising: The parsing module is used to parse the data import request when a data import request to the target database is received, and to obtain the data import parameters. The data import parameters include the data import mode, overwrite parameters, primary key conflict strategy and target storage original table. The target storage original table is the storage table in the target database used to write the offline data to be written as indicated by the data import request. The first import module is used to write the offline data to be written into the target storage original table by means of a direct loading protocol when the data import parameters indicate that incremental data import is to be performed. The second import module is used to create a target temporary storage table based on the target original storage table when the data import parameters indicate that a full data import is to be performed, and to write the offline data to be written to the target temporary storage table through the direct loading protocol. The target temporary storage table is renamed to the target original storage table after the writing is completed.

[0005] According to one aspect of this application, an electronic device is provided, comprising: a processor and a memory storing a program, the program including instructions that, when executed by the processor, cause the processor to perform the method of batch importing data into a database as described above.

[0006] According to another aspect of this application, a non-transitory computer-readable storage medium is provided that stores computer instructions for causing the computer to perform the method of batch importing data into a database as described above.

[0007] According to another aspect of this application, a computer program product is provided, comprising computer instructions stored in a computer-readable storage medium. A processor of a computer device reads the computer instructions from the computer-readable storage medium and executes the computer instructions, causing the computer device to perform the aforementioned method for batch importing data into a database.

[0008] The beneficial effects of the technical solutions provided in this application include at least the following: Upon receiving a data import request to the target database, the request is parsed to obtain data import parameters, including the data import mode, overwrite parameters, primary key conflict strategy, and the target storage source table. If the data import parameters indicate incremental data import, the offline data to be imported is written to the target storage source table via a direct load protocol. If the data import parameters indicate full data import, a target temporary storage table is created based on the target storage source table, and the offline data to be imported is written to the target temporary storage table via the direct load protocol. The target temporary storage table is then renamed to the target storage source table after the data is written. During batch data import, the direct load protocol bypasses the SQL layer and transaction layer, directly writing data to the target database. This avoids the overhead of row-by-row parsing and transaction logs, enabling extremely fast import of massive amounts of data. Furthermore, different data import methods are specifically designed for full and incremental data import scenarios: in the case of incremental data import, the creation of temporary tables and atomic renaming operations are skipped, and the offline data to be imported is directly written to the target storage source table via the direct load protocol, thus avoiding the instantaneous unavailability and doubling of storage I / O caused by large table renaming. Attached Figure Description

[0009] Further details, features, and advantages of this application are disclosed in the following description of exemplary embodiments in conjunction with the accompanying drawings, in which: Figure 1 A flowchart illustrating a method for batch importing data into a database according to an exemplary embodiment of this application is shown; Figure 2 This is a schematic diagram illustrating the complete batch data import process provided in an exemplary embodiment of this application; Figure 3 This is a schematic diagram of a device for batch importing data into a database, as provided in an embodiment of this application. Figure 4 A structural block diagram of an exemplary electronic device that can be used to implement embodiments of this application is shown. Detailed Implementation

[0010] Embodiments of this application will now be described in more detail with reference to the accompanying drawings. While some embodiments of this application are shown in the drawings, it should be understood that this application can be implemented in various forms and should not be construed as limited to the embodiments set forth herein. Rather, these embodiments are provided to provide a more thorough and complete understanding of this application. It should be understood that the drawings and embodiments of this application are for illustrative purposes only and are not intended to limit the scope of protection of this application.

[0011] It should be understood that the steps described in the method embodiments of this application may be performed in different orders and / or in parallel. Furthermore, the method embodiments may include additional steps and / or omit the steps shown. The scope of this application is not limited in this respect.

[0012] The term "comprising" and its variations as used herein are open-ended, meaning "including but not limited to". The term "based on" means "at least partially based on". The term "one embodiment" means "at least one embodiment"; the term "another embodiment" means "at least one additional embodiment"; the term "some embodiments" means "at least some embodiments". Definitions of other terms will be given in the following description. It should be noted that the concepts of "first", "second", etc., mentioned in this application are only used to distinguish different devices, modules, or units, and are not intended to limit the order of functions performed by these devices, modules, or units or their interdependencies. It should be noted that the modifications "a" and "a plurality" mentioned in this application are illustrative and not restrictive, and those skilled in the art should understand that unless explicitly indicated in the context, they should be understood as "one or more". The names of messages or information exchanged between multiple devices in the embodiments of this application are for illustrative purposes only and are not intended to limit the scope of these messages or information.

[0013] The present invention will now be described with reference to the accompanying drawings. The technical solutions provided by the embodiments of this application will be explained in detail through specific examples and application scenarios.

[0014] To address the issues of lengthy write paths and performance limitations in batch data import methods of related technologies, this application bypasses the SQL and transaction layers during the batch data import process using a direct load protocol. This directly writes data to the target database, avoiding the overhead of row-by-row parsing and transaction logs, thus achieving extremely fast import of massive amounts of data. Furthermore, different data import methods are specifically designed for full and incremental data import scenarios, further reducing the impact of incremental import tasks on online business. Please refer to [reference needed]. Figure 1 This document illustrates a flowchart of a method for batch importing data into a database according to an exemplary embodiment of this application. The method is described using an electronic device as an example. Figure 1 As shown, the method includes: Step 101: Upon receiving a data import request to the target database, the data import request is parsed to obtain the data import parameters. The data import parameters include the data import mode, overwrite parameters, primary key conflict strategy, and target storage source table. The target storage source table is the storage table in the target database used to write the offline data to be written as indicated by the data import request. Step 102: When the data import parameters indicate that incremental data import is to be performed, the offline data to be written is written to the target storage original table through the direct loading protocol. Step 103: When the data import parameters indicate that a full data import is to be performed, a target temporary storage table is created based on the target storage original table, and the offline data to be written is written to the target temporary storage table through the direct loading protocol. After the writing is completed, the target temporary storage table is renamed to the target storage original table.

[0015] Unlike the batch import process in related technologies, this application directly writes data to the storage layer of the target database through a direct loading protocol during batch data import, without the need for SQL parsing and transaction log recording. It directly generates the underlying storage format file of the target database, which significantly reduces the CPU and I / O overhead during the batch data import process, thereby improving the data writing performance of batch data import.

[0016] Considering that batch data import scenarios include both full and incremental data imports, if both incremental and full data imports use the method of first writing to a temporary table and then renaming the temporary table to a formal table, incremental data imports, even when only some data needs to be modified, would require repeatedly writing the unchanged parts to the temporary table, increasing storage I / O costs in incremental data writing scenarios. Therefore, to adapt to the performance of batch data imports in both full and incremental data import scenarios, one possible implementation is to parse the data import request upon receiving it, obtaining data import parameters. These parameters include the data import mode, overwrite parameters, primary key conflict strategy, and the target storage table. The target storage table is the storage table in the target database used to write the offline data to be written as indicated by the data import request. By analyzing the data import parameters, it is determined whether the current data import request is an incremental or full data import, and then different data import implementation methods are selected.

[0017] Specifically, when the data import parameters indicate incremental data import, the offline data to be written is written to the target storage source table via a direct load protocol. In other words, in the case of incremental data import, the creation of temporary tables and atomic renaming operations are skipped, and the offline data to be written is directly written to the target storage source table via the direct load protocol, thereby avoiding the momentary unavailability and doubling of storage I / O caused by large table renaming.

[0018] When the data import parameters indicate a full data import, a target temporary storage table is created based on the original target storage table. The offline data to be written is then written to the target temporary storage table via a direct loading protocol. After the writing process is complete, the target temporary storage table is renamed to the original target storage table. Because a full data import requires replacing all data, an empty target temporary storage table is directly written to, followed by an atomic rename operation. During the batch data import process, the target temporary storage table handles the full data writing, and only at the final write completion stage is the target temporary storage table renamed to the original target storage table (directly deleting the old target original storage table). This achieves instantaneous switching between old and new business data, enabling "all or nothing" batch data import results. It also eliminates the impact of intermediate import states on business data, ensuring data consistency and rollbackability.

[0019] When importing data into the target database, the master node and multiple worker nodes work together to execute the data import task. In one possible implementation, the master node receives the data import request to the target database and parses the request to obtain data import parameters such as import mode, overwrite parameters, primary key conflict policy, and the target storage source table.

[0020] For example, the data import mode (importMode) has three possible values: full (full import mode), inc (incremental append mode), and inc_replace (incremental replacement mode). full is the default; inc / inc_replace need to be used in conjunction with overwrite=true (overwrite write parameters are correct).

[0021] The overwrite parameter is a task-level switch. When enabled, the Master node no longer creates temporary tables or performs atomic renaming. The Worker node directly calls ObDirectLoadStatement (a JDBC driver-specific class for bypass direct write, bypass import, or direct load, specifically designed for high-speed batch writing of massive amounts of data) to write to the target storage table. In this embodiment, when importMode is inc / inc_replace, overwrite=true is mandatory; that is, when the data import mode is incremental append or incremental replace, the overwrite parameter is mandatory to be correct.

[0022] The primary key conflict strategy (dupAction) is the conflict handling action, which includes three values: STOP_ON_DUP (stop when duplicates are encountered), REPLACE (overwrite), and IGNORE (ignore). This application also imposes constraints and adapts the combination between inc / inc_replace and dupAction.

[0023] In one possible implementation, this application provides full / inc / inc_replace three-modal import on the same Direct Load bypass path. The importMode parameter unifies the three semantics of "full replacement / incremental append / incremental replacement" onto the same bypass write link, without the need to add a separate service or a separate SDK call path. The Master side decides whether to use a temporary table / atomic renaming based on importMode, and the Worker side passes importMode to ObDirectLoadStatement.loadMethod (direct load loading method). Incremental semantics skips temporary tables and atomic renaming: When importMode∈{inc,inc_replace} or overwrite=true, the Master no longer calls CommonUtil.createTempTableLikeOrigin (a general utility class for creating temporary tables based on the source table structure) to create temporary tables, and after the task is completed, it no longer calls CommonUtil.swapTempIntoOrigin (a general utility class for replacing temporary table data back to the original table) to perform atomic renaming; the Worker directly writes to the original table through ObDirectLoadStatement (OB batch direct load statement), avoiding the instantaneous unavailability and doubling of storage caused by large table renaming.

[0024] In `startMasterTask` (starting the main import task), the decision to create a temporary table is based on `isOverwrite` (whether to overwrite the write parameters): `isOverwrite` is calculated by `overwrite=true` or `importMode∈{inc, inc_replace}`. When `isOverwrite` is true, `tempTableName` is null (no temporary table exists), and the Master skips the `CommonUtil.createTempTableLikeOrigin` call (creating a temporary table with the same structure as the source table) and directly enters the `execute()` stage (batch write logic). The `execute()` stage no longer calls `setPartitionNames` (setting the list of partitions specified for this import): Unlike the full import mode, the `.setPartitionNames(partitionNames)` call (setting the list of partition names: an array / set of partition names) is commented out in incremental mode, allowing the OceanBase server to write according to its own partitioning strategy. This is because in incremental append / replacement scenarios, old data may be scattered across historical partitions, and a fixed partition list would restrict the write path.

[0025] Successful task completion: If importMode=inc / inc_replace, isOverwrite must be true. After a successful task, CommonUtil.swapTempIntoOrigin (a utility method that replaces and migrates data in the temporary table to the formal business table) will no longer be executed for atomic renaming. Instead, the final state of the original table, which already contains "old data + data added / replaced this time", will be directly preserved. If a task fails, the temporary table will no longer be cleaned up (because no temporary table was created). The taskinfo (task information entity that stores the full status, logs and results of the imported task) status will be set to FAILED and the reason for failure will be written to the database.

[0026] The Worker explicitly passes `importMode` to `ObDirectLoadStatement` (the OceanBase high-speed batch import execution object): When building `ObDirectLoadStatement`, the Worker explicitly calls `.setLoadMethod(importMode)` (setting the import mode): This shares the same `normalizeImportMode` (standard import mode) standardization method with the Master, avoiding the case sensitivity issue of "Master passing uppercase, Worker failing to parse". The Worker is no longer restricted by `setPartitionNames` (setting an array / set of partition names): Consistent with the Master, the Worker comments out `.setPartitionNames(partitionNames)` when building the statement (disabling the line setting the partition list in the code, no longer passing in specified partitions), allowing the server to write on demand.

[0027] For row-level failure handling: when a single row write fails, the Worker captures the failure and records it in batchFailedCount (the number of data batches that failed during batch import), and then the Master summarizes it into the task statistics; this is consistent with the full import mode and will not change the failure semantics due to changes in the import mode.

[0028] After the master node receives the data import request, it first calls normalizeImportMode to parse the data import mode. If the input parameter is null or a blank character, it is uniformly identified as full mode. For other non-empty valid values, extra spaces before and after the string are removed, and all are converted to lowercase letters without distinguishing between uppercase and lowercase letters. Then, isValidImportMode (to verify whether the import method of OB direct loading is compliant) is called to determine whether the data import mode belongs to {full, inc, inc_replace}. For illegal values, the illegal import mode parameter error code (IMPORT_MODE_ERROR_CODE) is returned directly.

[0029] For overwrite parameters, if the data import request includes overwrite parameters, they are directly parsed and obtained. If no overwrite parameters exist, they can be calculated based on the data import mode. For example, overwrite parameters are stored as Strings ("true" / "false") in the Bean (entity object / data carrier), and case sensitivity is ignored during comparison at the main node entry point. When calculating overwrite parameters, if the data import mode is inc / inc_replace (incremental append / incremental replace), the default overwrite parameter (Overwrite) is always true, ensuring that the subsequent "skip temporary table" logic is triggered. Users still need to explicitly pass overwrite=true when calling the function; otherwise, it will be intercepted at the entry point by IMPORT_MODE_OVERWRITE_REQUIRED_CODE (an error code requiring the mandatory use of overwrite mode), preventing "misuse of incremental parameters".

[0030] In other words, when the data import mode is incremental append or incremental replace, it is necessary to ensure that the overwrite parameter is true before executing the subsequent direct write to the target storage source table. In one possible implementation, when the data import parameters indicate incremental data import, writing the offline data to be written to the target storage source table via the direct load protocol further includes: if the data import mode is incremental append or incremental replace and the overwrite parameter is correct (overwrite=true), determining to execute incremental data import; and writing the offline data to be written to the target storage source table via the direct load protocol.

[0031] Optionally, if the data import mode is incremental append or incremental replace, and the overwrite parameter is incorrect, output the first error code, which indicates that the overwrite parameter does not match the import mode. For example, if importMode ∈ {inc, inc_replace} and the overwrite field is false (ignoring case), then IMPORT_MODE_OVERWRITE_REQUIRED_CODE (first error code) is returned directly at the preCheck entry point; the purpose of this constraint is to clearly indicate the intention of "whether to overwrite" to the caller, avoiding ambiguity of "only appending and not replacing but passing the wrong mode".

[0032] In addition to verifying the constraint relationship between the data import mode and the overwrite parameters, a pre-verification of the target database's kernel version is also required during incremental data import to avoid compatibility issues being exposed during the write phase, which could lead to incremental import failure. One possible implementation involves determining to execute incremental data import if the data import mode is incremental append or incremental replace, and the overwrite parameters are correct. This includes: if the data import mode is incremental append or incremental replace, obtaining the target database's kernel version; if the kernel version is greater than a preset version, and the overwrite parameters are correct, determining to execute incremental data import; if the kernel version is less than the preset version, outputting a second error code indicating an import mode version incompatibility error.

[0033] It should be noted that kernel version verification is only performed for scenarios where the data import mode is incremental append or incremental replace. If the data import mode is full, kernel version verification is not performed. For example, the observer version pre-verification is triggered only when importMode != full (the current data import mode is not overwrite mode). The server version string (kernel version) is obtained by calling version() (the standard driver for Java database connections, a built-in SQL function of the OB system used to query the database kernel version) via JDBC. CommonUtil.isVersionAtLeast(obVersion, "4.3.2") (a general utility class to determine whether the database version is greater than or equal to the specified version) is called to perform a segmented comparison with the minimum supported version (preset version). If the version is insufficient, an import mode version incompatibility error code is returned: IMPORT_MODE_VERSION_ERROR_CODE (i.e., the second error code), to avoid exposing compatibility issues only during the write phase.

[0034] Furthermore, during incremental data import, a compatibility check is required between the data import mode and the primary key conflict strategy. One possible implementation involves determining whether to execute the incremental data import if the data import mode is incremental append or incremental replace, and the overwrite parameters are correct. This includes: performing a compatibility check between the primary key conflict strategy and the data import mode if the data import mode is incremental append or incremental replace; and executing the incremental data import if the compatibility check passes and the overwrite parameters are correct.

[0035] Specifically, when performing compatibility checks on primary key conflict strategies and data import modes, there are two main special cases: if the data import mode is incremental append mode and the primary key conflict strategy is to overwrite the original data when there is a conflict, the compatibility check is determined to be unsuccessful, and a third error code is output, indicating that the data import mode and the primary key conflict strategy do not match; if the data import mode is incremental replacement mode, the primary key conflict strategy is replaced with a duplicate-stop strategy.

[0036] For example, if importMode=inc (data import mode is incremental append mode) and the primary key conflict policy resolution result is REPLACE (overwrite the original data when there is a conflict), then the import mode and conflict policy mismatch error code is returned: IMPORT_MODE_DUPACTION_ERROR_CODE (third error code); because REPLACE contradicts "append" under the semantics of "incremental append", it will cause "incremental append" to degenerate into "overwrite the entire row"); if importMode=inc_replace (incremental replacement mode), then regardless of the value of dupAction (primary key conflict policy), it will be forcibly rewritten to STOP_ON_DUP (placeholder, the actual conflict handling is completed by the server "replace by primary key"), and a log statement is written.

[0037] For full data import, if the data import mode is full mode, then the full data import will be executed directly, and a target temporary storage table will be created based on the target storage original table, and the subsequent full import steps will be executed.

[0038] In summary, this application provides a method for batch importing data into a database: upon receiving a data import request for a target database, the data import request is parsed to obtain data import parameters, including a data import mode, overwrite parameters, primary key conflict strategy, and the target storage source table; if the data import parameters indicate incremental data import, the offline data to be written is written to the target storage source table via a direct loading protocol; if the data import parameters indicate full data import, a target temporary storage table is created based on the target storage source table, and the offline data to be written is written to the target temporary storage table via a direct loading protocol, and the target temporary storage table is renamed to the target storage source table after the writing is completed. During batch data import, the DirectLoad protocol bypasses the SQL and transaction layers, directly writing data to the target database. This avoids the overhead of row-by-row parsing and transaction logs, enabling extremely fast import of massive amounts of data. Furthermore, different data import methods are specifically designed for full and incremental data import scenarios: In the case of incremental data import, the creation of temporary tables and atomic renaming operations are skipped, and the data to be written offline is directly written to the target storage original table through the DirectLoad protocol, thereby avoiding the momentary unavailability and doubling of storage I / O caused by renaming large tables.

[0039] In a sample example, the complete implementation process of a solution for batch importing data into a database is as follows: 1. Overall Structure and Node Responsibilities (1) The Master node receives the user's API request for database entry (POST / api / writeDB), and parses parameters such as importMode, overwrite, and dupAction from writeInfo. It calls CommonUtil.normalizeImportMode to normalize it into a lowercase string. It calls CommonUtil.isValidImportMode to verify the validity of the value (the value range is full / inc / inc_replace).

[0040] (2) Worker nodes are executed in parallel by multiple Workers; the Master writes the importMode to the WorkBean (work data entity object) by assigning the write worker thread through the assignWorker task. For example, workBean.addProperty("importMode" / / add / set property to the work bean: import mode; normalizeImportMode(masterBean.getImportMode()))) / / read the import mode parameter from the master configuration bean and then normalize the import mode; when the Worker calls ObDirectLoadStatementBuilder (OceanBase bypass direct write statement builder), it passes the import mode to the OceanBase server through .setLoadMethod(importMode) (set import mode).

[0041] (3) OceanBase cluster: Direct Load writes to the target; In incremental mode, it writes directly to the original table (targetTableForLoad = originTable) without intermediate temporary tables.

[0042] 2. Import mode and overwrite design (1) importMode normalization and validation: At the Master entry point, normalizeImportMode(null / empty → full; otherwise trim().toLowerCase()) is first called (if the input parameter is null or a blank character, it is uniformly recognized as full coverage mode; for other non-empty valid values, extra spaces before and after the string are removed, and all are converted to lowercase letters, without distinguishing between uppercase and lowercase matching configurations), and then isValidImportMode is called to determine whether it belongs to {full, inc, inc_replace}; illegal values ​​directly return IMPORT_MODE_ERROR_CODE (illegal import mode parameter error code).

[0043] (2) Overwrite switch: Stored in String form ("true" / "false") in Bean (entity object / data carrier), case is ignored when comparing at the Master entry point; at the same time, when calculating isOverwrite (whether it is overwrite), inc / inc_replace (incremental append / incremental replacement) are also regarded as "overwrite mode" - as long as importMode is inc or inc_replace, isOverwrite will definitely be true, ensuring that the subsequent "skip temporary table" logic is triggered; when the user calls, overwrite=true must still be explicitly passed, otherwise it will be intercepted at the entry point by IMPORT_MODE_OVERWRITE_REQUIRED_CODE (error code that requires the use of overwrite mode) to avoid "misuse of incremental".

[0044] (3) Correspondence between the three modes and the original table: the full mode uses "temporary table + atomic renaming" to provide strong atomicity; the inc / inc_replace mode writes directly to the original table (no longer has a temporary table, no longer has atomic renaming), emphasizing "overwrite is sufficient", so it no longer provides "all or nothing" guarantee.

[0045] 3. Multiple constraint verification in the preCheck phase (1) Observer version pre-verification: only triggered when importMode!=full (the current import mode is not overwrite mode); the server version string is obtained by calling version() (the standard driver for Java database connection, the SQL function built into the OB system, used to query the database kernel version) via JDBC, and CommonUtil.isVersionAtLeast(obVersion, "4.3.2") (a general utility class to determine whether the database version is greater than or equal to the specified version) is called to perform a segmented comparison with the minimum supported version; if the version is insufficient, IMPORT_MODE_VERSION_ERROR_CODE (import mode version incompatibility error code) is returned to avoid exposing compatibility issues only during the writing stage.

[0046] (2) Compatibility check between dupAction (primary key conflict strategy) and importMode (import mode): Parse MasterBean.getDupAction() (main task configuration entity: get primary key conflict handling strategy) as ObLoadDupActionType (ob batch import primary key conflict handling strategy enumeration); if importMode=inc (import mode is incremental append) and the parsing result is REPLACE (overwrite the original data when there is a conflict), then return IMPORT_MODE_DUPACTION_ERROR_CODE (import mode and conflict strategy mismatch error code: because REPLACE contradicts "append" under the semantics of "incremental append", it will cause "incremental append" to degenerate into "overwrite the whole row"); if importMode=inc_replace (incremental replacement), then regardless of the value of dupAction, it will be forcibly rewritten as STOP_ON_DUP (placeholder, the actual conflict handling is completed by the server "replace by primary key"), and write a log description.

[0047] (3) Overwrite mandatory constraint: If importMode∈{inc,inc_replace} and the overwrite field is false (ignore case), then IMPORT_MODE_OVERWRITE_REQUIRED_CODE (an error code that requires the use of overwrite mode) will be returned directly at the preCheck entry point. The purpose of this constraint is to prompt the caller with the intention of "whether to overwrite" in the most conspicuous way, so as to avoid the ambiguity of "only appending and not replacing but passing the wrong mode".

[0048] 4. In overwrite mode, the Master side skips temporary tables and renaming. (1) In startMasterTask (start the import main task), the decision to create a temporary table is based on isOverwrite (whether to overwrite): isOverwrite is calculated by overwrite=true or importMode∈{inc,inc_replace}; when isOverwrite is true, tempTableName=null (no temporary table exists), Master skips the CommonUtil.createTempTableLikeOrigin (create a temporary table with the same structure as the source table) call and directly enters the execute() (batch write to the database logic) stage.

[0049] (2) The execute() phase no longer calls setPartitionNames (sets the list of partitions specified for this import): Unlike the full import mode, the incremental mode comments out the call .setPartitionNames(partitionNames) (sets the list of partition names: an array / set of partition names) so that the OceanBase server writes according to its own partitioning strategy; this is because in the incremental append / replacement scenario, old data may be scattered in historical partitions, and a fixed list of partitions will restrict the writing path.

[0050] (3) Successful task completion: If importMode=inc / inc_replace, isOverwrite must be true. After the task is successful, CommonUtil.swapTempIntoOrigin (utility class method, which replaces and migrates the data in the temporary table to the formal business table) atomic renaming will no longer be executed. Instead, the final state of the original table, which already contains "old data + data added / replaced this time", will be directly retained. When the task fails, the temporary table will no longer be cleaned up (because it was not created at all). Only the taskinfo (task information entity, which stores the full status, logs and results of the imported task) status will be set to FAILED and the reason for failure will be written to the database.

[0051] 5. The Worker side passes the importMode to ObDirectLoadStatement (OceanBase high-speed batch import execution object). (1) When the Worker builds ObDirectLoadStatement (creates and assembles high-speed batch import objects), it explicitly calls .setLoadMethod(importMode) (sets the import mode): it shares the same normalizeImportMode (standard import mode) standardization method with the Master, avoiding the case problem of "Master transmits uppercase and Worker fails to parse".

[0052] (2) The Worker side is no longer restricted by setPartitionNames (sets an array / set of partition names): consistent with the Master side, the Worker side also comments out .setPartitionNames(partitionNames) when building the statement (disables the line that sets the partition list in the code and no longer passes the specified partition), allowing the server to write as needed.

[0053] (3) Handling of row-level failures: When a single row write fails, the Worker captures and records it in batchFailedCount (the number of data batches that failed in batch import), and the Master summarizes it into the task statistics after accumulation; this is consistent with the full import mode and does not change the failure semantics due to changes in the import mode.

[0054] 6. Engineering Link Consistency Guarantee (1) DB schema synchronization: The taskinfo table adds two columns: importModeVARCHAR(32) (variable length string type, maximum length 32 characters) and overwriteVARCHAR(16) (variable length string type, maximum length 16 characters). The COMMENT column is marked with "import mode: full / inc / inc_replace" and "whether to overwrite, default false", which correspond one by one with the Bean field. CommonUtil and BaseController complete the writing and reading.

[0055] (2) Email / details page display: The email template and the task details page of the dashboard simultaneously display the three fields importMode, overwrite, and dupAction; the OBKV (OB key-value store) task hides the three fields "import mode / conflict handling / whether to overwrite", and displays them differently from the BULKLOAD (batch high-speed data import) task to maintain semantic consistency between the front end and the back end.

[0056] (3) Fluctuation detection: The fluctuation detection service TaskFluctuationAlertServiceImpl is used to monitor abnormal fluctuations in the data volume and throughput of batch import tasks (sudden increase / sudden decrease / no data). An alarm is issued when the data volume exceeds the baseline. It is only triggered for BulkLoad type (batch high-speed loading / batch loading) + full (overwrite import mode) mode. The inc / inc_replace mode naturally expects the data volume to be "incremental" rather than "full". Forcing fluctuation detection will cause false alarms. Therefore, this application clearly marks "incremental mode does not trigger fluctuation detection" in the alarm summary.

[0057] (4) Exception code system: Four exception codes are introduced for incremental mode: IMPORT_MODE_ERROR_CODE=2026: illegal import mode error, IMPORT_MODE_VERSION_ERROR_CODE=2027: import mode incompatible with database version error, IMPORT_MODE_DUPACTION_ERROR_CODE=2028: import mode does not match primary key conflict strategy, and IMPORT_MODE_OVERWRITE_REQUIRED_CODE=2029: overwrite mode must be used. These four exception codes cover four typical errors: "illegal mode / version not satisfied / dupAction conflicts with mode / overwrite not enabled", which facilitates upper-level alarms.

[0058] 7. Differences between interface and fields (1) Add new input parameters: importMode (default full), overwrite (default false); retain the original dupAction; these three fields together determine the final bypass write strategy.

[0059] (2) Output parameters: The taskinfo returns two fields, importMode and overwrite, which are displayed in the email and the details page. The caller can confirm which mode the task was actually written to the database based on the return value.

[0060] (3) Mandatory constraints: inc / inc_replace must pass overwrite=true, otherwise it will be rejected directly; in inc mode, dupAction cannot be REPLACE, otherwise it will be rejected directly; in inc_replace mode, the dupAction field is ignored and rewritten as STOP_ON_DUP.

[0061] (4) Minimum server version: inc / inc_replace requires observer ≥ 4.3.2; there is no such constraint in full mode, and it is backward compatible.

[0062] Please refer to Figure 2 This is a schematic diagram illustrating a complete batch data import process provided in an exemplary embodiment of this application. For example... Figure 2 As shown, a complete batch data import process may include the following steps: S1, Master receives the request and parses parameters such as importMode / overwrite and dupAction; S2 checks if importMode is valid. If invalid, it returns failure; if valid, it continues to execute S3.

[0063] S3, determine if Inc / inc_replace and overwrite=true? If not, return failure; if yes, continue to step S4.

[0064] S4, case-sensitive, default value is full; S5, value verification; S6, determine if observer ≥ 4.3.2 (only in non-full mode); if it is greater than or equal to (yes), continue to step S7; otherwise, return failure. S7 performs a compatibility check on dupAction and importMode.

[0065] If inc+dupAction=REPLACE, then it returns failure; If it is inc_replace, then force dupAction=STOP_ON_DUP; if it is full, then retain the original dupAction and enter S8.

[0066] S8, calculate isoverwrite.

[0067] S9 determines whether to overwrite the previous data. If true, skip creating the temporary table; if false, create the temporary table.

[0068] S10 involves batch data import by worker nodes.

[0069] S11, determine whether the write was successful. If so, proceed to step S12.

[0070] S12 checks whether to overwrite the data. If true, the data has already been written directly to the original table; if false, the temporary table is atomically renamed. The batch data import process ends.

[0071] Please refer to Figure 3 This is a schematic diagram of a device for batch importing data into a database, provided in an embodiment of this application. For example, as shown... Figure 3 As shown, the device 300 includes: The parsing module 301 is used to parse the data import request when a data import request to the target database is received, and obtain the data import parameters. The data import parameters include the data import mode, overwrite parameters, primary key conflict strategy and target storage original table. The target storage original table is the storage table in the target database used to write the offline data to be written as indicated by the data import request. The first import module 302 is used to write the offline data to be written into the target storage original table through a direct loading protocol when the data import parameter indicates that incremental data import is to be performed. The second import module 303 is used to create a target temporary storage table based on the target storage original table when the data import parameters indicate that a full data import is to be performed, and to write the offline data to be written to the target temporary storage table through the direct loading protocol. The target temporary storage table is renamed to the target storage original table after the writing is completed.

[0072] Optionally, the first import module is further configured to: If the data import mode is incremental append mode or incremental replacement mode, and the overwrite write parameter is correct, then determine to execute incremental data import; The offline data to be written is written to the target storage original table using the direct loading protocol.

[0073] Optionally, the device further includes: The first output module is configured to output a first error code if the data import mode is the incremental append mode or the incremental replacement mode, and the overwrite parameter is incorrect. The first error code indicates that the overwrite parameter does not match the import mode.

[0074] Optionally, the first import module is further configured to: If the data import mode is the incremental append mode or the incremental replace mode, obtain the kernel version of the target database; If the kernel version is greater than the preset version and the overwrite parameters are correct, determine to execute incremental data import; The device further includes: The second output module is used to output a second error code if the kernel version is less than the preset version. The second error code indicates an import mode version incompatibility error.

[0075] Optionally, the first import module is further configured to: If the data import mode is the incremental append mode or the incremental replacement mode, perform a compatibility check on the primary key conflict strategy and the data import mode. If the compatibility check passes and the overwrite parameters are correct, proceed with the incremental data import.

[0076] Optionally, the first import module is further configured to: If the data import mode is the incremental append mode and the primary key conflict strategy is to overwrite the original data when there is a conflict, the compatibility check is determined to be unsuccessful, and a third error code is output. The third error code indicates that the data import mode and the primary key conflict strategy do not match. If the data import mode is the incremental replacement mode, the primary key conflict strategy will be replaced with a duplicate-stop strategy.

[0077] Optionally, the second import module is further used for: If the data import mode is full mode, determine to perform full data import and create the target temporary storage table based on the target original storage table.

[0078] An exemplary embodiment of this application also provides an electronic device, including: at least one processor; and a memory communicatively connected to the at least one processor. The memory stores a computer program executable by the at least one processor, which, when executed by the at least one processor, causes the electronic device to perform a method for batch importing data into a database according to an embodiment of this application.

[0079] An exemplary embodiment of this application also provides a non-transitory computer-readable storage medium storing a computer program, wherein the computer program, when executed by a computer's processor, is used to cause the computer to perform a method for batch importing data into a database according to an embodiment of this application.

[0080] An exemplary embodiment of this application also provides a computer program product, including a computer program, wherein, when executed by a computer's processor, the computer program is used to cause the computer to perform a method for batch importing data into a database according to an embodiment of this application.

[0081] refer to Figure 4 The present invention describes a structural block diagram of an electronic device 400 that can serve as a server or client of this application, which is an example of a hardware device that can be applied to various aspects of this application. The electronic device is intended to represent various forms of digital electronic computer devices, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processors, cellular phones, smartphones, wearable devices, and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the application described and / or claimed herein.

[0082] like Figure 4As shown, the electronic device 400 includes a computing unit 401, which can perform various appropriate actions and processes according to a computer program stored in a read-only memory (ROM) 402 or a computer program loaded from a storage unit 408 into a random access memory (RAM) 403. The RAM 403 may also store various programs and data required for the operation of the electronic device 400. The computing unit 401, ROM 402, and RAM 403 are interconnected via a bus 404. An input / output (I / O) interface 405 is also connected to the bus 404.

[0083] Multiple components in electronic device 400 are connected to I / O interface 405, including: input unit 406, output unit 407, storage unit 408, and communication unit 409. Input unit 406 can be any type of device capable of inputting information to electronic device 400. Input unit 406 can receive input digital or character information and generate key signal inputs related to user settings and / or function control of electronic device. Output unit 407 can be any type of device capable of presenting information and may include, but is not limited to, a display, speaker, video / audio output terminal, vibrator, and / or printer. Storage unit 408 may include, but is not limited to, disks and optical discs. Communication unit 409 allows electronic device 400 to exchange information / data with other devices through computer networks such as the Internet and / or various telecommunications networks, and may include, but is not limited to, modems, network cards, infrared communication devices, wireless communication transceivers, and / or chipsets, such as Bluetooth devices, WiFi devices, WiMax devices, cellular communication devices, and / or the like.

[0084] Optionally, the electronic device 400 also includes a single-channel EEG signal acquisition module (not shown in the figure). This module is used to acquire EEG signals and transmit them to the signal processor of the electronic device 400 for EEG signal processing.

[0085] The computing unit 401 can be a variety of general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of the computing unit 401 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special-purpose artificial intelligence (AI) computing chips, various computing units running machine learning model algorithms, a digital signal processor (DSP), and any suitable processor, controller, microcontroller, etc. The computing unit 401 performs the various methods and processes described above. For example, in some embodiments, the methods shown in the above embodiments can be implemented as a computer software program, which is tangibly contained in a machine-readable medium, such as storage unit 408. In some embodiments, part or all of the computer program can be loaded and / or installed on the electronic device 400 via ROM 402 and / or communication unit 409. In some embodiments, the computing unit 401 can be configured to perform the methods shown in the above embodiments by any other suitable means (e.g., by means of firmware).

[0086] The program code used to implement the methods of this application may be written in any combination of one or more programming languages. This program code may be provided to a processor or controller of a general-purpose computer, special-purpose computer, or other programmable data processing device, such that when executed by the processor or controller, the functions / operations specified in the flowcharts and / or block diagrams are implemented. The program code may be executed entirely on a machine, partially on a machine, as a standalone software package partially on a machine and partially on a remote machine, or entirely on a remote machine or server.

[0087] In the context of this application, a machine-readable medium can be a tangible medium that may contain or store a program for use by or in conjunction with an instruction execution system, apparatus, or device. A machine-readable medium can be a machine-readable signal medium or a machine-readable storage medium. Machine-readable media can be, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination of the foregoing. More specific examples of machine-readable storage media include electrical connections based on one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fibers, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination of the foregoing.

[0088] As used in this application, the terms "machine-readable medium" and "computer-readable medium" refer to any computer program product, device, and / or apparatus (e.g., disk, optical disk, memory, programmable logic device (PLD)) for providing machine instructions and / or data to a programmable processor, including machine-readable media that receive machine instructions as machine-readable signals. The term "machine-readable signal" refers to any signal for providing machine instructions and / or data to a programmable processor.

[0089] To provide interaction with a user, the systems and techniques described herein can be implemented on a computer having: a display device for displaying information to the user (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor); and a keyboard and pointing device (e.g., a mouse or trackball) through which the user provides input to the computer. Other types of devices can also be used to provide interaction with the user; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including sound input, voice input, or tactile input).

[0090] The systems and technologies described herein can be implemented in computing systems that include backend components (e.g., as a data server), or computing systems that include middleware components (e.g., an application server), or computing systems that include frontend components (e.g., a user computer with a graphical user interface or web browser through which a user can interact with implementations of the systems and technologies described herein), or any combination of such backend, middleware, or frontend components. The components of the system can be interconnected via digital data communication of any form or medium (e.g., a communication network). Examples of communication networks include local area networks (LANs), wide area networks (WANs), and the Internet.

[0091] Computer systems can include clients and servers. Clients and servers are generally located far apart and typically interact through communication networks. Client-server relationships are created by computer programs running on the respective computers and having a client-server relationship with each other.

Claims

1. A method for batch importing data into a database, characterized in that, The method includes: Upon receiving a data import request to the target database, the data import request is parsed to obtain data import parameters. The data import parameters include data import mode, overwrite write parameters, primary key conflict strategy, and target storage original table. The target storage original table is a storage table in the target database used to write the offline data to be written as indicated by the data import request. When the data import parameters indicate that incremental data import is to be performed, the offline data to be written is written to the target storage original table through the direct loading protocol; When the data import parameters indicate that a full data import is to be performed, a target temporary storage table is created based on the target original storage table, and the offline data to be written is written to the target temporary storage table through the direct loading protocol. After the writing is completed, the target temporary storage table is renamed to the target original storage table.

2. The method according to claim 1, characterized in that, When the data import parameters indicate incremental data import, the step of writing the offline data to be written to the target storage table via a direct loading protocol includes: If the data import mode is incremental append mode or incremental replacement mode, and the overwrite write parameter is correct, then determine to execute incremental data import; The offline data to be written is written to the target storage original table using the direct loading protocol.

3. The method according to claim 2, characterized in that, The method further includes: If the data import mode is the incremental append mode or the incremental replacement mode, and the overwrite write parameter is incorrect, output a first error code, which indicates that the overwrite parameter does not match the import mode.

4. The method according to claim 2, characterized in that, If the data import mode is incremental append mode or incremental replace mode, and the overwrite write parameter is correct, then determining to execute incremental data import includes: If the data import mode is the incremental append mode or the incremental replace mode, obtain the kernel version of the target database; If the kernel version is greater than the preset version and the overwrite parameters are correct, determine to execute incremental data import; The method further includes: If the kernel version is less than the preset version, a second error code is output, which indicates an import mode version incompatibility error.

5. The method according to claim 2, characterized in that, If the data import mode is incremental append mode or incremental replace mode, and the overwrite write parameter is correct, then determining to execute incremental data import includes: If the data import mode is the incremental append mode or the incremental replacement mode, perform a compatibility check on the primary key conflict strategy and the data import mode. If the compatibility check passes and the overwrite parameters are correct, proceed with the incremental data import.

6. The method according to claim 5, characterized in that, The compatibility verification of the primary key conflict strategy and the data import mode includes: If the data import mode is the incremental append mode and the primary key conflict strategy is to overwrite the original data when there is a conflict, the compatibility check is determined to be unsuccessful, and a third error code is output. The third error code indicates that the data import mode and the primary key conflict strategy do not match. If the data import mode is the incremental replacement mode, the primary key conflict strategy will be replaced with a duplicate-stop strategy.

7. The method according to claim 1, characterized in that, When the data import parameters indicate that a full data import is to be performed, creating a target temporary storage table based on the target original storage table includes: If the data import mode is full mode, determine to perform full data import and create the target temporary storage table based on the target original storage table.

8. An apparatus for batch importing data into a database, characterized in that, The device includes: The parsing module is used to parse the data import request when a data import request to the target database is received, and obtain the data import parameters. The data import parameters include the data import mode, overwrite parameters, primary key conflict strategy and target storage original table. The target storage original table is the storage table in the target database used to write the offline data to be written as indicated by the data import request. The first import module is used to write the offline data to be written into the target storage original table by means of a direct loading protocol when the data import parameters indicate that incremental data import is to be performed. The second import module is used to create a target temporary storage table based on the target original storage table when the data import parameters indicate that a full data import is to be performed, and to write the offline data to be written to the target temporary storage table through the direct loading protocol. The target temporary storage table is renamed to the target original storage table after the writing is completed.

9. An electronic device, comprising: processor; And the memory for storing programs, The program includes instructions that, when executed by the processor, cause the processor to perform the method of batch importing data into the database according to any one of claims 1-7.

10. A non-transitory computer-readable storage medium storing computer instructions, wherein, The computer instructions are used to cause the computer to perform the method of batch importing data into the database according to any one of claims 1-7.

Images