Overview

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.

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

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

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 virtual machine (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 table names are case-sensitive, enclose them in brackets ([ ]). For example, --table '[test]' indicates the table named test, and the file name is in the format of test.group.sequence.suffix. --table '[TEST]' indicates the table named TEST, and the file name is in the format of 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 1.4.79 in MySQL mode, the use of the INSERT ... WHERE NOT EXISTS statement may result in cross-partition insertion errors.

• In OceanBase Database 1.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 that has a generated column as a partitioning key, the data file must contain the data of the generated column. Otherwise, use the limited mode (enabled by using the --public-cloud option) to import data.

• The --logical-database option is used to import and export logical databases by using OceanBase Database Proxy (ODP) (Sharding). At present, restricted by the features of ODP (Sharding), a schema exported by OBDUMPER is a random physical schema in database and table shards. Therefore, you cannot use OBLOADER to directly import the schema to a logical database. You need to manually modify the schema.

• 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 only to scenarios where you cannot directly specify the --sys-user or --sys-password option.
The limited mode enabled by using the --no-sys option is applicable to private cloud environments without sys tenant passwords. The limited mode enabled by using the --public-cloud option is applicable to public cloud environments without sys tenant passwords.

• When you import data from OceanBase clusters deployed in a public cloud in limited mode (enabled by using the --public-cloud option), you do not need to specify the -t and -c connection options. When you import data from OceanBase clusters deployed in a private cloud in limited mode (enabled by using the --no-sysoption), you need to specify the -t connection option, and additionally the -c option if you connect to the ODP service. 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 2.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';