Data import refers to the process of writing external data into an internal table of OceanBase AI Database. The data may be located in external storage such as local paths, object storage, or HDFS, or it may come from other databases.
When choosing an import method, it is recommended to first determine the import approach (who reads the file and what SQL/tools are used for writing), and then confirm the location of the data and network accessibility.
Import methods
When writing data from a file or a readable external path to an internal table, you can first choose from the following three methods in most scenarios. They correspond to different execution entry points, rather than being classified by "data source type".
Import method |
Data location |
Application scenarios |
More reading |
|---|---|---|---|
| LOAD DATA FROM FILES | Object storage URLs and external file paths readable by the server | Suitable for large-scale file import, parallel loading of multiple files, and scenarios where files do not need to be copied first to the local disk of the OBServer. | Import data by using the LOAD DATA statement |
| LOAD DATA INFILE | Server-side paths accessible to the OBServer process | Suitable for scenarios where the file is already locally mounted on the node or shared and mounted. | Import data by using the LOAD DATA statement |
| LOAD DATA LOCAL INFILE | Client local file | Suitable for small to medium batch processing, test and validation, or scenarios where files are stored only locally on the client. | Import data by using the LOAD DATA statement |
| INSERT INTO ... SELECT FROM FILES | External files readable on the query side | Suitable for scenarios where column mapping, filtering, expression evaluation, and other transformations need to be performed in SQL. | Use the INSERT INTO SELECT FROM FILES statement for direct load |
| INSERT INTO ... SELECT (internal table / external table) | Internal tables, external tables, or other query results | Suitable for scenarios such as analytical result backfilling and data exchange between internal and external tables. | Import data from external tables using INSERT INTO SELECT |
| CREATE TABLE AS SELECT (CTAS) | Defines a new table by using SELECT and loads data into it |
Suitable for scenarios where table creation and data loading are completed simultaneously. | Import data via direct load using the CREATE TABLE AS SELECT statement |
| Import with tools | Files held by the client, transit server, or isolated network | Suitable for scenarios with limited network bandwidth, import links requiring dedicated tool protocols, or batch import task orchestration. | Import data by using obloader |
What is direct load
OceanBase AI Database supports enabling direct load for some import statements to shorten the regular write path, reduce hot write pressure on the SQL layer and MemTable, thereby improving throughput for large-scale loading.
OceanBase AI Database uses direct load technology to skip intermediate steps and directly write data to SSTables, while reducing resource consumption (CPU, memory), and supporting efficient full or incremental data import.
Notice
It is not recommended to perform upgrades during a direct load task, as this may cause the direct load task to fail.
Technical architecture
Traditional import path (blue path):
Client -> SQL parsing -> Transaction processing -> MemTable -> Minor/Major compaction -> SSTable
Disadvantages: Long path, high resource consumption, limited write speed.
Direct load path (green path):
Client -> Type conversion -> Primary key sorting (optional) -> Direct write to Major SSTable (full) or Mini SSTable (incremental)
Advantages:
- Skips intermediate modules such as SQL, transactions, and MemTables, reducing resource consumption.
- Data is written directly to underlying storage, avoiding the overhead of multiple major compactions.
Core mechanisms of direct load
Skip intermediate layers: Bypass SQL parsing, transactions, and MemTables to write directly to SSTables.
Type conversion and sorting:
- Data type conversion (for example, converting CSV data into the OceanBase table structure).
- Sorting by primary key (required for full import to ensure the order of the Major SSTable).
Conceptually, direct load is an import execution mode, not an independent data source or separate syntax category.
That is to say, users typically do not first choose "direct load" and then select syntax; instead, they first choose an import method such as LOAD DATA, INSERT INTO SELECT, CREATE TABLE AS SELECT, or obloader based on business requirements, and then determine whether these paths are suitable for direct load execution under current conditions.
When hints such as APPEND and DIRECT(...) are enabled, or when relying on the tenant-level parameter default_load_mode, the optimizer and execution engine will determine whether the direct load mode can be used based on conditions such as statement type, execution plan, parallelism capability, and object constraints.
Application scenarios
Data migration and synchronization. For data migration and synchronization, a large amount of data in various formats often needs to be migrated from different data sources to OceanBase AI Database. The performance of traditional SQL interfaces may not meet timeliness requirements.
Traditional ETL. After data is extracted and transformed at the source, a large amount of data usually needs to be loaded to the target within a short time. Direct load technology improves data import performance and the efficiency of the data loading phase.
Loading data from text files or other data sources into OceanBase AI Database: direct load technology can also improve data loading efficiency.
Relationship between direct load and import syntax
A common misconception when understanding direct load is that it is parallel to syntaxes such as LOAD DATA, INSERT INTO SELECT, and CTAS. Instead, it represents the relationship between execution modes and the syntaxes they support.
You can think of it this way:
- Statements like
LOAD DATA,INSERT INTO SELECT, andCREATE TABLE AS SELECTdefine "how to read data and write it to the target table". - Direct load defines "whether to use a more efficient writing method for these statements when conditions are met".
Therefore, the same import statement may have different execution methods under different conditions:
- A statement might be executed using the regular insertion method in different OceanBase versions due to version-specific behavior differences.
- Under the premise of meeting hints, parameters, parallel capabilities, and object limitations, a statement may also follow the direct load path.
For example:
LOAD DATAis more suitable for file import scenarios. Whether direct load is used depends on specific hints, mode, and execution conditions.- Whether
INSERT INTO ... SELECT ...can use direct load often depends on prerequisites such as parallel DML (PDML). For details, see Parallel DML. CREATE TABLE AS SELECTcan also execute with direct load when loading data while creating a table, provided conditions are met.
Typical statements supporting direct load
The following table shows the relationship between common import statements and direct load.
Statement type |
Relationship with direct load |
|---|---|
LOAD DATA |
In the FROM FILES form, it can be used together with APPEND, DIRECT(...), parallel(N), and other methods. |
INSERT INTO ... SELECT ... |
The direct load path can be used when conditions such as parallel DML are met. This usually requires the use of enable_parallel_dml, parallel(N), DIRECT(...), and other settings. |
CREATE TABLE AS SELECT |
In scenarios where you create a table and load data simultaneously, you can use APPEND, DIRECT(...), and parallel(N) together to evaluate the direct load capability. |
| Tool import (such as obloader) | Whether to use a loading path equivalent to direct load depends on the tool version, parameter configuration, and the tool's internal implementation. |
Full direct load and incremental direct load
Direct load can generally be divided into two categories: full direct load and incremental direct load.
Full direct load
- Full direct load is used to directly write a complete dataset into the database's data files in one operation. This method bypasses the SQL layer interfaces, directly allocating space and inserting data into the data files, thereby improving data import efficiency.
- Full direct load is typically used for database initialization, data migration, or when a large amount of data needs to be loaded quickly.
Incremental direct load
- Incremental direct load is used to directly write newly added data into the database's data files without going through the SQL interface, especially when a large amount of data already exists. This method bypasses the SQL layer's data processing and directly writes new data into the data files, improving data write efficiency.
- Incremental direct load is typically used in high-throughput data write scenarios, such as large-scale real-time data collection or log writing.
To understand the difference between the two, you can consider the write path in relation to OceanBase's LSM-Tree storage layer. OceanBase's storage layer mainly includes:
- MemTable: A write structure in memory that supports high-concurrency updates.
- Mini SSTable: A disk structure formed by the minor compaction of a MemTable.
- Major SSTable: A stable data file formed after a major compaction, suitable for efficient queries.
In regular imports, data typically goes through a complete transaction write and minor compaction pipeline. In direct load, however, the execution engine shortens this path as much as possible to improve large-scale loading efficiency. The differences between full direct load and incremental direct load are mainly reflected in the target write format, conflict handling method, and applicable scenarios.
Considerations
Considerations for full direct load
When using full direct load, note the following limitations and considerations at the direct load capability level:
- A table lock is acquired during direct load, preventing other data writes to the table. The table remains read-only throughout the process.
- Full direct load is suitable for the initial import of large tables, data migration of 10 GB to TB levels, and scenarios where CPU and memory resources are limited. This is because the execution path of direct load is shorter, saving CPU overhead.
- Two write operation statements cannot be executed simultaneously during import (i.e., one table cannot be written to at a time). A table lock is acquired at the beginning of the import, and only read operations are allowed throughout the import process.
- Trigger usage is not supported.
- Tables with generated columns are not supported (some indexes create hidden generated columns, for example, KEY idx_c2 (c2(16)) GLOBAL).
- Liboblog and flashback queries are not supported.
- When using the
LOAD DATAstatement orINSERT INTO SELECTstatement for specified partition direct load, note that the target table cannot be a replicated table and must not contain auto-increment columns, identity columns, or Global Indexes. - When using
LOAD DATAfor direct load, refer to LOAD DATA for syntax and statement-level usage restrictions.
Considerations for incremental direct load
The incremental direct load strategy divides data into two parts: the portion that conflicts with the existing data's primary key is written to a Mini SSTable (incremental SSTable), while the non-conflicting portion is written to another Major SSTable (baseline SSTable). This allows the new incremental direct load strategy to perform multiple imports without degrading query performance, especially when there is little conflicting data.
When using incremental direct load, in addition to the general limitations mentioned above for full direct load, note the following incremental direct load capability level limitations:
- Tables with foreign keys do not support incremental direct load.
- Tables with
GLOBALindexes do not support incremental direct load. - Tables with constraints do not support incremental direct load.
- Tables without a primary key that have more than one unique
LOCALindex do not support incremental direct load. - Tables with a primary key and a unique index do not support incremental direct load.
When using LOAD DATA or INSERT INTO SELECT for incremental direct load, refer to LOAD DATA and INSERT for statement-level usage restrictions, respectively.
Considerations
When using direct load, note the following general considerations (applicable to both full and incremental loads):
- Between two major compactions, it is recommended that consecutive incremental direct loads do not exceed 16 times. Each import results in an additional data file that has not yet been incorporated into the baseline; after too many imports, the system will be unable to write. After major compaction is completed, these files will be merged, and importing can continue. For more information about major compaction, see Overview of major compaction management.
- Incremental direct load supports target tables with non-unique local indexes.
Note
This section describes the general considerations for the direct load feature. For usage restrictions on specific SQL statements, refer to the corresponding syntax documents: LOAD DATA syntax, INSERT syntax.