Product Introduction

What is OBLOADER?

OBLOADER is a data import tool in the form of a client developed in Java. OBLOADER provides extensive command-line options that allow you to import structures and data to the OceanBase Database in many complex scenarios. OBLOADER is usually used in combination with OBDUMPER. However, you can also use OBLOADER to import files in SQL or CSV format that were exported by using third-party tools, such as Navicat, Mydumper, and SQL Developer. OBLOADER fully exploits the features of the distributed OceanBase system and is particularly optimized in import performance.

Features

You can use OBLOADER to import schemas and data to the OceanBase Databse. Specifically, OBLOADER provides the following features:

• Allows you to import data definition language (DDL) statements of object schemas in the database.

• Allows you to import data files in the CSV or SQL format.

• Allows you to import files in complex data formats such as POS, CUT, and MIX formats.

• Allows you to set data preprocessing rules.

• Allows you to configure the field mappings between files and tables.

• Supports features such as throttling, memory explosion prevention, resumption after an interruption, and automatic retries.

• Allows you to specify a log directory and store bad data and conflict data.

• Supports data import in limited mode, in which you do not need to specify account information of users under the sys tenant.

Supported OceanBase Database versions

OBLOADER supports the following OceanBase Database versions:

Notes

• For more information about the standard CSV format, see the RFC 4180 specifications. We recommend that you import data in strict accordance with the RFC 4180 specifications.

• You need to modify the virtual machine (VM) memory parameter, which is defaulted as -Xms4G -Xmx4G, in the script when you intend to import or export a large amount of data.

• The object names, data file names, and rule file names specified by command-line options must be capitalized in the same way. By default, uppercase letters are used in Oracle mode and lowercase letters are used in MySQL mode.

• The imported data files must be named in the format of table name.<any characters>.extension .

• If the database contains tables with foreign keys, schemas and data may not be sequentially imported, and the import may fail.

• Tables without primary keys do not support import resumption after an interruption.

• When you import data, make sure that the specified object name and data file name are capitalized in the same way. If the database contains both object names in uppercase and object names in lowercase, use double quotation marks (" ") to enclose the object names and file names in lowercase.

• OBLOADER supports the following file formats:

  • DDL: A file in the DDL format contains only data definition language (DDL) statements.

  • SQL: A file in the SQL format contains onlyINSERT statements. Each statement occupies one line without line breaks.

  • MIX: A file in the MIX format can contain mixed types of statements, such as DDL and DML statements.

  • POS: In a file of the POS format, the data is generated based on the positions of byte offsets.

  • CUT: A file in the CUT format stores data separated by a string. In the CUT format, the separator can contain several characters, whereas in the CSV format, the separator is one character.

Limited mode

OBLOADER provides a limited mode. You can import data in limited mode by specifying the --public-cloud option. In limited mode, OBLOADER does not rely on users under the system tenant. Therefore, you do not need to specify the --sys-user or the --sys-password option.

The limited mode does not support all of the features of OBLOADER. Performance and stability are also not as good as in regular mode. In addition, only OceanBase V2.2.30 and later versions support throttling on the server. Therefore, when you use the limited mode, run the following command to modify the throttling settings on the server:

alter system set freeze_trigger_percentage=50;
alter system set minor_merge_concurrence=64;
alter system set writing_throttling_trigger_percentage=80 tenant='xxx';