Introduction

OBLOADER

What is OBLOADER?

OBLOADER is a client tool that is developed in Java. At present, OBLOADER applies only to OceanBase Database. You can use OBLOADER to import database definitions and table data files from the storage media to OceanBase Database. We recommend that you use OBLOADER in combination with OBDUMPER. OBLOADER is compatible with the CSV files exported by using client tools such as mysqldump or Mydumper. Therefore, you can use OBLOADER in data migration. OBLOADER provides various types of built-in data preprocessing functions to improve its data import performance. The automatic fault tolerance mechanism ensures data import stability. Rich monitoring information is provided for you to observe the import performance and progress in real time.

Features

OBLOADER mainly provides the following features:

• Allows you to import database object definitions and table data from local disks, Apache Hadoop, Alibaba Cloud Object Storage Service (OSS), and Amazon Simple Storage Service (S3).

• Allows you to import SQL files exported by mysqldump.

• Allows you to import data files in the standard CSV, INSERT SQL, ORC, or Parquet format.

• Allows you to set data preprocessing rules and configure field mappings between files and tables.

• Supports features such as import throttling, memory exhaustion prevention, resumption after an interruption, and automatic retries.

• Allows you to specify a custom log directory to store bad data and conflicting data during import.

• Automatically splits large files without consuming additional storage space.

• Supports encryption of sensitive parameters specified in commands, such as the database account and password and cloud storage account and password.

Considerations

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

• To improve performance, you can 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 object dependency exists in the database, object definitions and data may not be imported in strict dependency order.

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

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

• When you specify the --cut option on the OBLOADER command line, do not use the --trail-delimiter option if no field separator or separator string exists at the end of the data line in the file. Otherwise, data cannot be correctly imported to the database.

• Before you use OBLOADER to import data to OceanBase Database V3.2.4 or later, set the system parameter open_cursors to a large 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;.

• When you import DDL statements, use the --mix option instead of the --ddl option if -f is set to a non-standard directory structure (a directory structure not generated by OBDUMPER). When the --sql option is specified, for data in the file data format, ensure that one statement inserts only one record. Otherwise, specify --mix instead of --sql to import the data.

• OBLOADER supports the following file formats:

  • DDL: A file in the DDL format contains only DDL statements but no table data.
  • 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 ORC format contains standard Apache ORC content. The default compression algorithm is zstd.
  • Parquet: A file in the Parquet format contains standard Apache Parquet content. The default compression algorithm is zstd.
  • MIX: A file in the MIX format contains mixed types of standard SQL statements, such as DDL and DML statements.
  • POS: A file in the POS format contains data at a fixed byte length. Currently, the length cannot be specified.
  • CUT: A file in the CUT format contains data separated by strings. However, in a file in the CSV format, data is separated by single character.

What is OBDUMPER?

OBDUMPER is a client tool that is developed in Java. At present, OBDUMPER applies only to OceanBase Database. You can use OBDUMPER to export objects and table data from OceanBase Database to the storage media in the specified file format. To use OBDUMPER in logical backup, you can directly integrate OBDUMPER to the database O&M system. OBDUMPER cannot be used in incremental backup. Compared with other client export tools such as mysqldump, OBDUMPER has the following benefits:

• Quick data export: Multiple data query strategies are designed to significantly improve the export performance.

• Versatile data exchange capabilities: Data in tables can be exported to multiple types of storage media in different formats.

• Powerful data processing capabilities: Data is compressed, encrypted, desensitized, and preprocessed before being exported.

Features

OBDUMPER provides the following features:

• Allows you to export database object definitions and table data to local disks, Alibaba Cloud Object Storage Service (OSS), and Amazon Simple Storage Service (S3).

• Allows you to export table data to files in the CSV, INSERT SQL, ORC, or Parquet format.

• Allows you to specify a partition name to export only the data in the specified table partition.

• Allows you to specify global filters to export only the data that meets the specified conditions.

• Allows you to configure data preprocessing rules to convert and desensitize data before export.

• Allows you to specify system change numbers (SCNs) or timestamps to export only the historical snapshot data of desired transaction points or time points.

• Allows you to export data from a follower replica, which is different from a standby cluster, of an OceanBase cluster.

• Allows you to specify a custom query statement to export only the result set of the query statement.

• Allows you to use the latest snapshot version to export globally consistent data without locking tables.

• Supports encryption of sensitive parameters specified in commands, such as the database account and password and cloud storage account and password.

Considerations

• For more information about the standard CSV format, see the RFC 4180 specifications. We recommend that you export data in strict accordance with the RFC 4180 specifications.

• You need to modify the VM memory parameter in the script when you try to export a large amount of data.

• The object names, data file names, and control 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 exported data files are named in the table.group.sequence.suffix format.

• If a foreign key contains several columns, the order of the columns might change after the export. Example: FOREIGN KEY(c1,c2) REFERENCE (c1,c2).

• OceanBase Database V1.4.72 in MySQL mode does not allow you to export the definitions of unique prefix indexes, for example, UNIQUE(c1(10)).

• OceanBase Database V1.4.x in MySQL mode does not allow you to export the definitions of generated columns. For example, columns generated by using the GENERATED ALWAYS AS (expr) statement cannot be exported.

• OceanBase Database V2.2.7x in MySQL mode does not allow you to export vertical partitions. For example, partitions created by using the PARTITION BY COLUMN statement cannot be exported.

• OceanBase Database V2.2.7x in MySQL mode does not allow you to export the definitions of SYNONYM objects.

• OceanBase Database V2.2.7x in MySQL mode does not allow you to export columns that store index data. For example, columns in STORING(COLUMN_LIST) cannot be exported.

• OceanBase Database in Oracle mode does not allow you to export data of the INTERVAL DAY(2) TO SECOND(0) type.

• On the Windows OS, the exclamation point (!) cannot be used as a separator. An object name in the database cannot contain the following special characters: \ / : * ? " < > |. Otherwise, neither the database object definitions nor the table data can be exported.

• By default, when the table data to be exported contains a generated column, the data of the generated column is exported. You can use the --exclude-virtual-columns option to specify not to export the data of the generated column.

• When you use the --date-value-format, --time-value-format, --datetime-value-format, --timestamp-value-format, --timestamp-tz-value-format, or --timestamp-ltz-value-format option, specify the time value in the correct format. Otherwise, an error will be reported during data export.

• When you export a CUT file, if a field contains the specified field separator, the separator character in the field is escaped. For example, if the content of a field is abc|def and | is the field separator (--column-splitter "|"), the exported field is converted to abc\|def.

• When you specify the --logical-database option on the command line, the definition of a random physical database shard is exported and the shard cannot be directly imported into the database. You need to manually convert the exported physical shard to a logical one before you import it to the database for business use.

• When you specify the --partition option on the command line to export data in a partition, you need to specify the name of the subpartition for a composite partitioned table. OBDUMPER cannot export data in a partition of a composite partitioned table. If the specified partition name does not exist, OBDUMPER returns an error.

• When you specify the --add-extra-message option on the command line to export table definitions, OBDUMPER exports the name of the table group to which each table belongs. This option depends on privileges of the sys tenant. If OBDUMPER does not have privileges of the sys tenant, do not specify this option.

• The --no-sys option indicates that you cannot provide the password of the sys tenant in a private cloud environment. In OBLOADER & OBDUMPER V3.3.0 and later versions, public cloud environments and private cloud environments are treated differently. The --public-cloud option is used only for public cloud environments, and the --no-sys option is used only for private cloud environments.

• Before you use OBDUMPER to export data from OceanBase Database V3.2.4 or later, set the system parameter open_cursors to a large value. Otherwise, an error may occur during the export. After the data is exported, reset the system parameter to the initial value, for example, ALTER SYSTEM SET open_cursors = 65535;.

• If the schema of a table has been changed in OceanBase Database V4.0.0 or later, you cannot use OBDUMPER to export the baseline data, namely the consistent snapshot data, obtained after the last major compaction. You can manually initiate a major compaction and then re-export the most recent baseline data.

• When you import DDL statements, use the --mix option instead of the --ddl option if -f is set to a non-standard directory structure (a directory structure not generated by OBDUMPER). When the --sql option is specified, for data in the file data format, ensure that one statement inserts only one record. Otherwise, specify --mix instead of --sql to import the data.

• When you use OBDUMPER of a version earlier than V4.2.0 to export data from a MySQL tenant of OceanBase Database, the Java Database Connectivity (JDBC) driver converts the zero date and time values in the database to NULL. If a column has a NOT NULL constraint, an error is reported during the export. OBDUMPER V4.2.0 can export zero date and time values, but cannot identify whether the original data is NULL or zero values during export and forcibly converts the data to zero values by default. In addition, exported zero values of the DATETIME and TIMESTAMP data types may be distorted into non-zero approximate values. If the sql_mode variable in the database is specified with the NO_ZERO_DATE or NO_ZERO_IN_DATE constraint, an error is reported when zero-value data is exported. In a MySQL tenant of OceanBase Database, data types that involve the preceding zero-value issues include DATE, DATETIME, TIME, YEAR, and TIMESTAMP.

• Feature differences in schema export

  

Flashback export

To use the flashback export feature, you need to set the system variable undo_retention to an appropriate value.

Assume that t1 is the point in time of the Undo operation, and t2 = t1 + 900s. You can find data within the [t1, t2] range when you execute a query at t2. After you set undo_retention, the current session takes effect only on the data that is generated after t1. The default value of this system variable is 0, in seconds. Sample statement for setting the undo_retention variable:

SET global undo_retention=900;

• Currently, you can only query the v$ob_timestamp_service view in the sys tenant to obtain the valid OceanBase SCN.

• You can query data after the latest major compaction at a specific point in time. For example, if you started a major compaction at t1, the earliest data that you can query is the data at t1.

• If the table that you want to query has been deleted and is in the recycle bin, you must restore it from the recycle bin.

• Flashback queries are affected by minor compactions. If a minor compaction has been performed and the undo_retention variable is not specified, flashback queries cannot be performed.

• After the undo_retention variable is set, you can query data within the time range of [t1, t1 + undo_retention], where t1 is the point in time when the minor compaction occurs.

Supported OceanBase Database versions

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