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 schemas and data to 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 OceanBase Databse. Specifically, OBLOADER enables you to perform the following operations:

• Import data definition language (DDL) statements of object schemas in the database.

• Import data files in the CSV or SQL format.

• Import files in complex data formats such as POS, CUT, and MIX formats.

• Set simple data cleansing rules.

• Set data preprocessing rules.

• Configure the field mappings between files and tables.

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

• Specify a log directory and store bad data and conflict data.

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

• Import data from Object Storage Service (OSS) into OceanBase Database.

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.

• All imported data files are named in the table.group.sequence.suffix format.

• 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.

• In MySQL mode of OceanBase Database V1.4.79, the use of the insert ... where not exists statement pattern for resolving primary key conflicts may result in cross-partition insertion.

• In the Windows operating system, the name of a database object cannot contain the following characters: \ / : * ? " < > |. Otherwise, the file cannot be exported.

• In the Windows operating system, the exclamation point (!) cannot be used as a delimiter.

• In MySQL mode of OceanBase Database V1.4.x, the metadata of the RANGE_COLUMNS + KEY is defective in the virtual routing view.

• OceanBase Database in Oracle mode does not support the import and export of virtual column data. This issue is fixed in MySQL mode where virtual columns are ignored during data export. Otherwise, virtual data cannot be imported.

• OBLOADER supports the following file formats:

  • DDL: A file in the DDL format contains only 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 in the sys tenant. Therefore, you do not need to specify the option --sys-user or --sys-password.

• The limited mode (public cloud connection mode) does not support all of the features of OBLOADER. The performance and stability are also not as good as in regular mode. In addition, only OceanBase Database V2.2.30 and later 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';
  

• When you specify the --public-cloud option to use public cloud services in limited mode, you do not need to specify the option -t or -c.