OBLOADER 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 standard formats of CSV, SQL, ORC, and Parquet.

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

• Allows you to import a generated column and use it as a partitioning key.

• Adds a confirmation step for you to confirm risky operations such as TRUNCATE TABLE during a full database import.

• Adds the -Dfile.split parameter. 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 Java virtual machine (JVM) 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 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 import a data file that uses a generated column as the partitioning key, the data file must contain data of the generated column. Otherwise, specify the --no-sys option when you import data.

• 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.
  • ORC: A file in the standard Apache ORC format.
  • Parquet: A file in the standard Apache Parquet format.
  • 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.

• Before you use OBLOADER to import data to OceanBase Database V3.2.4.0 and later, set the system parameter open_cursors to a larger value. Otherwise, an error may occur during the import. After the data is imported, reset the system parameter to the initial value, for example, ALTER SYSTEM SET open_cursors = 65535;.