OBLOADER & OBDUMPER V4.3.0

OBLOADER & OBDUMPER V4.3.0 released in April 2024 supports decompressing CSV, CUT, POS, and SQL files to be exported, importing and exporting Apache Avro files, importing data from and exporting data to Tencent Cloud Object Storage (COS) and Huawei Cloud Object Storage Service (OBS), exporting schemas of columnstore tables in OceanBase Database V4.3.0, and generating empty files in specific export scenarios. This version also optimizes user experience in different aspects.

Version information

• Current version: V4.3.0

• Previous version: V4.2.8.2

• Release date: April 30, 2024

Supported OceanBase Database versions

The following table lists the OceanBase Database versions supported by OBLOADER & OBDUMPER V4.3.0.

Considerations

• The following command-line option adjustments are made in OBLOADER & OBDUMPER V4.3.0:

  • The --storage-uri option is deprecated. Its functionality is integrated into the -f/--file-path option.

    Note that this deprecated option can still be used. We recommend that you replace it in the following ways:

    • To use the local file system, specify -f '/home/admin/foo/bar'.

    • To use Alibaba Cloud OSS, specify -f 'oss://mybucket/foo/bar?endpoint=myendpoint&access-key=myak&secret-key=mysk'.

      When you export database object definitions and table data to another file system instead of the local file system, by default, OBDUMPER will create a temporary directory named tmp in its root directory to store temporary files. You can use the --tmp-path option to manually specify a temporary directory.

  • The default value of the --compact-schema option is true. When you export data from OceanBase Database V4.0.0 or later, you can specify this option for OBDUMPER to obtain DDL statements by using the SHOW CREATE TABLE ... statement, instead of constructing DDL statements based on metadata queried from views, which is the default schema export strategy.

    This adjustment is made for compatibility with changes of some standard views in OceanBase Database to improve the overall performance and support columnar storage. You can still set the --compact-schema option to false to use the default schema export strategy.

• When the amount of data in a table does not reach the block size specified by the --block-size option, which is 1 GB by default, the exported file is named in the format of <table name>.<file name extension> in this version, and in the format of <table name>.<block sequence number>.<file name extension> in earlier versions. Assume that you want to export table data in the CSV format:

  • If the amount of data in the t_test table is 500 MB, which does not exceed the default value 1 GB of the --block-size option, only one file named t_test.csv will be generated after the export.

  • If the amount of data in the t_test table is 1.2 GB, which exceeds the default value 1 GB of the --block-size option, two files respectively named t_test.0.csv and t_test.1.csv will be generated after the export.

When you upgrade OBLOADER & OBDUMPER from a version earlier than V4.2.8 to V4.3.0, take note of the following considerations:

• OBLOADER & OBDUMPER V4.3.0 allows you to specify the value of -u/--user in the three-segment format of <user>@<tenant>#<cluster>. For backward compatibility, you can still use the -t/--tenant and -c/--cluster options.

• The behavior for parsing POS files is changed. In this version, the parser processes a whole line. In other words, in addition to the byte length defined in the control file, the parser will further read a line break for each line. You may need to modify the control file to adapt to this change. You can run the following command to calculate the number of bytes in the first line of the example.dat file:

  expr $(head -n 1 example.dat | wc -c) - 1
  

When you upgrade OBLOADER & OBDUMPER from a version earlier than V4.2.7 to V4.3.0, take note of the following considerations:

• The command-line option --file-name has been deprecated.

  • You do not need to merge sub-files exported from a single table. To export the data of a table to a single file, you can set the --block-size option to 0 to specify not to split the exported table data into file blocks.

    Note that though the tool does not limit the file size, some file systems or object storage services still limit the size of a single file. For example, in the ext2, ext3, and ext4 file systems, the size of a single file is limited to 2 TB.

  • By default, the tool will generate a complex nested directory structure. You can specify the --no-nested-dir option so that the tool does not generate nested subdirectories.

• The command-line option --upload-behavior has been deprecated. For the write strategies of different data sources, see the feature description in the following sections.

• After the file write behavior is changed in OBLOADER & OBDUMPER V4.2.7, the tool has to downgrade the export performance to ensure that the number of exported files is predictable. If you want to improve the export performance, you can enable parallel write so that the tool writes the data of each table to multiple sub-files, which cannot be merged into one file. For more information, see the corresponding description in earlier versions. Perform the following steps:

  1. Use a text editor to open the obdumper file in the bin directory in the root directory of the tool.

  2. Find the Java startup parameter -Denable.parallel.write and change its value from false to true.

New features

OBLOADER

• The --avro file format option is provided for you to import data files in the standard Apache Avro format.

• You can decompress CSV, CUT, POS, and SQL files exported by OBDUMPER before import. The following command-line options are added in this version:

  • --compress

    Indicates whether the to-be-imported files have been compressed by OBDUMPER during export. It is a Boolean value.

  • --compression-algo

    Indicates the compression algorithm by which the to-be-imported files are compressed by OBDUMPER during export. The value is a string. Valid values are zstd, zlib, gzip, and snappy. The default value is zstd.

  • --compression-level

    The compression level for the corresponding compression algorithm. The value is an integer. Compression levels supported for different compression algorithms are as follows:

    • zstd: 1 to 22

    • zlib and gzip: -1 to 9.

    • snappy: You cannot specify a compression level for this compression algorithm.

• This version allows you to import database object definitions and table data from Tencent Cloud Object Storage (COS) and Huawei Cloud Object Storage Service (OBS). Here are two examples of using the -f/--file-path option:

  • Tencent Cloud COS

    -f 'cos://mybucket/foo?region=ap-shanghai&access-key=myak&secret-key=mysk'
    -f 'cos://mybucket/foo?endpoint=yourendpoint&access-key=myak&secret-key=mysk'
    

  • Huawei Cloud OBS

    -f 'obs://mybucket/foo?region=cn-north-1&access-key=myak&secret-key=mysk'
    -f 'obs://mybucket/foo?endpoint=yourendpoint&access-key=myak&secret-key=mysk'
    

OBDUMPER

• The --avro file format option is provided for you to export table data to a file in the Apache Avro format.

• This version allows you to compress and export files in the CSV, CUT, POS, or SQL format. The following command-line options are added in this version:

  • --compress

    Specifies whether to compress data during export. It is a Boolean value.

  • --compression-algo

    The compression algorithm. The value is a string. Valid values are zstd, zlib, gzip, and snappy. The default value is zstd.

  • --compression-level

    The compression level for the specified compression algorithm. The value is an integer. Compression levels supported for different compression algorithms are as follows:

    • zstd: 1 to 22

    • zlib and gzip: -1 to 9.

    • snappy: You cannot specify a compression level for this compression algorithm.

• This version allows you to export database object definitions and table data to Tencent Cloud COS and Huawei Cloud OBS.

• OBDUMPER exports data to the specified object storage service in Multipart-Upload mode. By default, the local file system is used as the temporary directory for storing temporary file blocks. You can also use the --tmp-path option to specify a storage directory. You can modify the following parameters in the runtime script of OBDUMPER to adapt to the actual data export environment:

  • upload.buffer.type

    The buffer type. Valid values are disk and bytebuffer. The default value is disk, which specifies to store file blocks in the disk. In this case, The total size of file blocks that can be stored is subject to the maximum available space of the disk. bytebuffer specifies to store file blocks in the memory. This ensures high performance but occupies much JVM stack memory space.

  • upload.buffer.size

    The size of each file block, in bytes. The default value is 64MB.

  • upload.active.blocks

    The maximum number of blocks (being uploaded or waiting to be uploaded in the queue) concurrently processed by each write thread. The default value is 2. When the specified value is exceeded, the tool is blocked until a file block is uploaded and removed from the queue.

    The --thread option specifies the maximum number of write threads. You can use the following formula to estimate the space that may be occupied by the buffer when the tool runs: Value of thread × Value of active.blocks × Value of buffer.size.

• In this version, if you specify the --partition option to export data of a specific partition or the --where option to export data that meets the specified condition, you can also specify the --retain-empty-file option to generate an empty file when the specified partition or query result set is empty.

Fixed issues

• Fixed the issue where an error occurs when columns are quickly dropped during export in Oracle-compatible tenants of OceanBase Database V4.2.1 BP4 and later.

• Fixed the issue where a NullPointerException (NPE) error occurs when the partition from which data is to be exported does not exist.

• Fixed the issue where an NPE error occurs if the control file contains only part of the files when you export data in the POS format.

• Fixed the issue where the file name extension is truncated when both a file name and file name extension are specified for the -f option during export.

• Fixed the issue where database objects cannot be filtered by using wildcards during DDL import.

• Fixed the issue where the import task is interrupted and cannot be resumed when the imported data rows do not match the data types of the destination table.

• Fixed the issue where the value '\E' in a CSV file changes to NULL after it is imported to a LOB field.

Known issues

• The exported database object definitions may contain the name of the database to which the object belongs.

• If you do not specify the password of the sys tenant on the command line, OBDUMPER cannot export table group definitions from OceanBase Database of a version earlier than V2.2.70.

• If you do not specify the password of the sys tenant on the command line, OBDUMPER cannot export index definitions from OceanBase Database of a version earlier than V2.2.50 in the Oracle compatible mode.

• If you do not specify the password of the sys tenant on the command line, OBDUMPER cannot export partition information of unique indexes from OceanBase Database of a version earlier than V2.2.70 in the Oracle compatible mode.

• If you do not specify the password of the sys tenant on the command line, OBDUMPER cannot export definitions of unique indexes on partitioned tables from OceanBase Database V2.2.70 or later in the Oracle compatible mode.

• OBDUMPER cannot export PL object definitions from OceanBase Database of a version earlier than V2.2.30 in the MySQL compatible mode.

• When you use OBLOADER to import data, an error occurs if the specified file format, such as --sql or --csv, does not match the actual file format.

• After you enable case sensitivity for object names, such as table names and view names, in OceanBase Database in the MySQL compatible mode, objects are imported or exported in a case-insensitive manner.

• The high-availability mode "sequential" of OceanBase Connector/J is unavailable for now. You can use the "loadbalance" mode instead.

• If you specify the --remove-newline option to remove line breaks, unexpected escape characters will be generated. Therefore, we recommend that you do not use this option.

• If you use direct load in a multi-table restore scenario, the performance may be undesirable.

Considerations

• In a CUT file, each data record is stored in an entire line. When you specify the --cut option on the OBDUMPER command line, if the exported data contains a single-character field separator, OBDUMPER escapes special characters in the data, such as separators, carriage returns, and line breaks. For example, if the data is abc|def and the separator is |, the exported data is abc\|def.

• 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 each data line in the file. Otherwise, the data cannot be correctly imported to the database.

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

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

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

• In OceanBase Database V4.0.0 or later, if the schema of a table has been changed, 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 export the most recent baseline data.

• When you use OBDUMPER of a version earlier than V4.2.0 to export data from a MySQL-compatible tenant of OceanBase Database, OceanBase Connector/J converts the zero values of date and time type fields in the database to NULL. If a column has a NOT NULL constraint, an error will be reported during the export. OBDUMPER V4.2.0 can export zero values of date and time type fields, 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 will be reported when zero-value data is exported. Take note of this consideration on zero values when you export data of the DATE, DATETIME, TIME, YEAR, or TIMESTAMP type from a MySQL-compatible tenant of OceanBase Database.

• Feature differences in schema export in the MySQL compatible and Oracle compatible modes:

  Compatibility Mode	Password of the sys tenant provided	Password of the sys tenant not provided
  MySQL compatible	Tables, views, table groups, stored procedures, and functions are supported.	The export behavior is basically the same as that when the password of the sys tenant is provided, except for the following known issues: Table group definitions cannot be exported for OceanBase Database of a version earlier than V2.2.70. Partition information of unique indexes cannot be exported for OceanBase Database of a version earlier than V2.2.70. Index definitions cannot be exported for Oracle-compatible tenants of OceanBase Database V2.2.30 or earlier. Definitions of unique indexes on partitioned tables cannot be exported for Oracle-compatible tenants of OceanBase Database of a version ranging from V2.2.70 to V4.0.0.
  Oracle compatible	Tables, views, triggers, synonyms, sequences, stored procedures, functions, packages, table groups, and types