Overview

What is OBLOADER?

OBLOADER is a client data import tool developed in Java. OBLOADER provides extensive command-line options that allow you to import definitions and data to OceanBase Database in many complex scenarios. We recommend that you use OBLOADER along with OBDUMPER. However, in external services, OBLOADER can also import CSV files that are exported by third-party tools, such as Navicat, MyDumper, and SQL Developer. OBLOADER fully exploits the features of the distributed OceanBase Database system. The tool is particularly optimized in import performance and stability, and is enhanced to provide more operation monitoring information, bringing better user experience.

Features

OBLOADER mainly provides the following features:

• Allows you to import the DDL statements of database objects.
• Allows you to import data files in the standard CSV or SQL format.
• Allows you to import files where data is stored with a fixed length in bytes, files where data is split by a string, and files that store a mix of DDL and DML statements.
• Allows you to configure rules that control data preprocessing and field mappings between files and tables for data import.
• Supports features such as import speed limiting, memory explosion prevention, resumption after an interruption, and automatic retries.
• Allows you to specify a log directory and store bad data and conflict data.
• A generated column can be imported and used as a partitioning key.
• A confirmation step is added for you to confirm risky operations such as TRUNCATE TABLE during a full database import.
• The -Dfile.split parameter is added. By default, the parameter is set to UNSAFE in the script for coarse-grained splitting of files. You can set it to SAFE for fine-grained splitting of files.

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

Supported OceanBase Database versions

The following table describes OceanBase Database modes and versions that are supported.

Notice OBLOADER 3.0.0 supports OceanBase Community Edition 3.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 try to import 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. If case sensitivity is required, you can enclose the table name in square brackets ([ ]). Example: --table '[test]' represents the test table, and the file name format is test.group.sequence.suffix. --table '[TEST]' represents the TEST table, and the filename format is TEST.group.sequence.suffix.

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

• If a database object have dependencies on others, object definitions and data may not be imported in dependency order.

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

• When you resolve the primary key conflict in OceanBase Database V1.4.79 in MySQL mode, the use of the INSERT ... WHERE NOT EXISTS statement may result in cross-partition insertion errors.

• In OceanBase Database V1.4.x in MySQL mode, the metadata of the RANGE COLUMNS-KEY composite partitioned table is defective in the virtual routing view.

• When you use OBLOADER to import data to a table with a generated column as a partitioning key, the data of the generated column must be existed in the data file, otherwise, you need to import the data in limited mode (by specifying --public-cloud) .

• OBLOADER supports the following file formats:

  • DDL: A file in the DDL format contains only DDL statements.

  • CSV: A file in the standard CSV format contains content that complies with the RFC 4180 specification.

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

  • MIX: A file in the MIX format contains both DDL statements and DML statements.

  • POS: A file in the POS format stores data with a fixed length in bytes.

  • CUT: A file in the CUT format contains string-delimited data, which differs from the CSV format.

Limited mode

Note The limited mode is applicable to scenarios where you cannot directly specify the --sys-user or --sys-password option.

This mode applies to only services deployed on Alibaba Cloud.

• If you enable the limited mode (by specifying --public-cloud) when you import data to an OceanBase cluster deployed in a public cloud, you do not need to specify the -t or -c option. When you import data in limited mode to an OceanBase cluster deployed in a private cloud, you must specify the -t option, and additionally the -c option for using OceanBase Database Proxy (ODP). If you do not enable the limited mode, the --sys-user and --sys-password options must be specified for OBLOADER.

• The limited mode does not support all features of OBLOADER. The performance and stability are therefore affected in limited mode. OceanBase Database V2.2.30 and later versions support throttling on the server. Therefore, to ensure the stability of data import in limited mode, you can run the following command to modify throttling thresholds 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';