Overview

What is OBLOADER?

OBLOADER is a client-side data import tool 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. You can also use OBLOADER to import files in the SQL or CSV format that are 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 Database. OBLOADER provides the following features:

• Allows you to import the DDL statements of database objects.

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

• Supports simple data cleansing rules.

• Allows you to set data preprocessing rules.

• Allows you to configure 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.

• Allows you to export data from Object Storage Service (OSS) to OceanBase Database.

• Allows you to ignore a specified number of errors.

• Supports the import of incremental data. Data can be replaced when a primary key or unique key conflict occurs.

• Supports the automatic processing of dependencies between database objects when you import schemas.

Supported OceanBase Database versions

The following table describes OceanBase Database versions that are supported by OBDUMPER.

Notice

OBLOADER and OBDUMPER V3.0.0 and later are compatible with OceanBase Database Community Edition.

You can use OBLOADER and OBDUMPER in OceanBase Database Community Edition V3.1.2 and later.

Usage 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 VM memory parameter in the script when you intend to import or export a large amount of data. Default value: -Xms4G -Xmx4G.

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

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

• On 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 only INSERT 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 in 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 strings. However, in a file in the CSV format, data is separated by single characters.

Limited mode

Note

This mode applies to only services deployed on Alibaba Cloud.

• OBLOADER provides the 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 --sys-user or --sys-password option.

• The limited mode does not support all features of OBLOADER. The performance and stability are also affected in limited 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';
  

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