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.

• Provides a wide range of data cleansing features.

• Provides various error handling strategies.

• Allows you to logically split a data file before import to leverage the performance of multiple CPU cores.

• 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. In the file name format, group indicates the sub-task ID (determined based on the task splitting strategy), sequence indicates the file sequence number (a new file is generated when the file size exceeds the value specified by --block-size), and suffix indicates the file name extension.

• During import, the file name format that can be identified by OBLOADER is table name.file name extension. You can customize a file search rule by specifying the --file-regular-expression option.

• If the database objects to be imported have dependencies such as foreign key dependencies among tables and dependencies of triggers on sequences, we recommend that you import the objects based on the dependency order. If you specify the --all or --table '*' option for import, the import order cannot be strictly ensured.

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

• At present, resumable transmission is not supported for tables without a primary key.

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

• Distinguish the --mix and --ddl options during the import of DDL files. The --ddl option can be used to import files that contain only one DDL statement whereas the --mix option does not have this limitation.

• 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 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 content that complies with the Apache ORC specification and uses zstd compression by default.
  • Parquet: A file in the Parquet format contains content that complies with the Apache Parquet specification and uses zstd compression by default.
  • 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 length in bytes. Currently, data at a fixed length in characters is not supported.
  • CUT: A file in the CUT format uses a single character or multiple characters to separate data columns and does not contain delimiters. There are different from the standard CSV format.

OBDUMPER

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 OSS, and Amazon 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 JVM 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, the names are in uppercase in Oracle mode and in lowercase in MySQL mode. If table names are case-sensitive, enclose them in brackets ([ ]). For example, --table '[test]' indicates that the table name is test and the file name format is test.group.sequence.suffix;. --table '[TEST]' indicates that the table name is TEST and the file name format is TEST.group.sequence.suffix. In the file name format, group indicates the sub-task ID (determined based on the task splitting strategy), sequence indicates the file sequence number (a new file is generated when the file size exceeds the value specified by --block-size), and suffix indicates the file name extension.

• If a foreign key contains several columns, the order of the columns might change after the export, for 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, and --timestamp-ltz-value-format options, use a format template that can be identified by Java to avoid errors.

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

• The --no-sys option specifies that the password of the sys tenant cannot be provided in OceanBase Database. OBLOADER & OBDUMPER V3.3.0 and later distinguish ApsaraDB for OceanBase and OceanBase Database. The --public-cloud option applies only to ApsaraDB for OceanBase, and the --no-sys option applies only to OceanBase Database.

• 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 export 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. Here is an example of setting the undo_retention variable:

SET global undo_retention=900;

• You can query the v$ob_timestamp_service view in the sys tenant for the valid OceanBase system change number (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.