Import data by using obloader

When the environment where the data files are located cannot be directly accessed by the OBServer, that is, when the network is disconnected from the Observer or it is inconvenient to copy the files to the local path of the OBServer, you can use obloader to write the data to OceanBase.

Unlike executing SQL statements such as LOAD DATA and LOAD DATA FROM FILES on the OBServer side, obloader reads files in the client process and interacts with the storage layer through RPC, making it suitable for scenarios such as offline package delivery and cross-subnet delivery.

Application scenarios

• Files are inaccessible to OBServer: The exported files are located on a business server, jump server, or in an isolated subnet, and the network between the OBServer and the data file location is disconnected, making it inconvenient to use the import method that allows OBServer to directly read the files.

If the files are already placed in a path accessible to OBServer or in object storage, you should first evaluate the path selection options described in LOAD DATA FROM FILES, LOAD DATA INFILE, and Overview of data import.

Command-line options

Refer to the Data import tool documentation and configure according to your actual environment.

Procedure

Execute the following command on the server where the CSV files are stored (the example uses an ODP and an Oracle-compatible tenant; replace with your actual address, port, user, and path):

bin/obloader -h <ODP or OBServer address> -P 2883 -u <user> -t <tenant name> -c <cluster name> -p \
  -D <database name> --table LINEITEM --external-data --csv \
  -f /data/tpch/bak/ \
  --truncate-table --column-separator='|' \
  --thread 16 --rpc-port 2885 --direct --parallel=16

Direct load accesses the RPC port, not the SQL port. By default, the SQL port of an OBServer is 2881 and the RPC port is 2882. For ODP V4.3.0, the RPC port defaults to 2885. When obloader bypasses ODP to connect directly to OBServer and explicitly specifies the RPC port, it can use the direct load path.

Note that in a production environment, it is generally not recommended to have clients directly connect to OBServer bypassing ODP. When accessing through ODP, the location of the primary data replica is transparent to the client, and routing is handled uniformly by ODP. However, when connecting directly to a specific OBServer, if the primary replica of the target data is not on the current node, the write process may involve cross-node transaction coordination, increasing network and transaction overhead.

• -f specifies a readable data directory on the local machine. The tool reads files on the client and writes them via RPC bypass. It is not required that OBServer can access this path.
• --rpc-port must match the RPC listening port of the current connection target (ODP or OBServer). Do not confuse it with the SQL port.
• If connecting directly to a specific OBServer, you need to know whether the primary data replica is on that node. Cross-server bypass may incur additional costs. If reachable, prefer ODP to align with the production business access method. Specific routing and network policies shall comply with operational specifications.

When data volume reaches the TB level, direct load is more conducive to throughput. You can plan the window in combination with tenant resources and the considerations outlined in Overview of data import and Full direct load.

Direct load mode configuration file (optional)

You can configure the direct_path_load section in {ob-loader-dumper}/conf/session.config.json for settings such as RPC timeout, retry, and task timeout. For example:

"direct_path_load": {
    "rpc_connect_timeout": "15000",
    "rpc_execute_timeout": "20000",
    "runtime_retry_times": "5",
    "runtime_retry_intervals": "50",
    "task_timeout": "2592000000000",
    "heartbeat_timeout": "60000000"
}

Considerations

• Direct load uses the RPC port to transfer data, not the SQL protocol port.

• Data is committed on a per-table basis, not at the session or transaction level.

• Retry or resumable upload from a breakpoint are not currently supported.

• Bit-type data is not currently supported.

• Virtual generated columns are not currently supported.

• For import tasks with a small amount of data, direct load is not recommended.

• Specifying the --replace-data command-line option does not handle unique index conflicts.

• The difference between the --thread and --parallel command-line options:

  • --thread specifies the connection pool from the client to the server, which is maintained by the client.

  • --parallel specifies the number of worker threads that the OBServer can call for data writing and sorting.

  • It is recommended to keep --thread consistent with --parallel when using them together.