OBLOADER & OBDUMPER V4.3.0

OBLOADER & OBDUMPER V4.3.0 released in April 2024 supports compressing 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.

Upgrade 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, but 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 Object Storage Service (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 creates 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 V4.3.1, 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 is 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 are 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 V4.3.1, the parser processes whole lines. In other words, in addition to the byte length defined in the control file, the parser further reads 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 --file-name option is 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 although 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 generates a complex nested directory structure. You can specify the --no-nested-dir option so that the tool does not generate nested subdirectories.

• The --upload-behavior option is deprecated. For information about 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 versions earlier than V4.2.7. The procedure is as follows:

  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

• Provides the --avro option for you to import data files in the standard Apache Avro format.

• Provides the following options for you to decompress and load compressed files in the CSV, CUT, POS, or SQL format that are exported by OBDUMPER:

  • --compress

    Specifies whether to decompress data during import. 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. The default level is 3.

    • zlib: -1 to 9. The default level is -1.

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

• Allows you to import database object definitions and table data from Tencent Cloud COS and Huawei Cloud 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

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

• Provides the following options for you to compress and export files in the CSV, CUT, POS, or SQL format:

  • --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. The default level is 3.

    • zlib: -1 to 9. The default level is -1.

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

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

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

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

Bug fixes

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

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

• Fixed the issue where an NPE is thrown if the control file contains only part of the tables when you export data in the POS format.

• Fixed the issue where the file name extension is truncated when both a file name and a 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 target 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 a specified 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 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 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 but earlier than V4.0.0 in Oracle compatible mode.

• OBDUMPER cannot export PL object definitions from OceanBase Database of a version earlier than V2.2.30 in 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.

• In MySQL compatible mode of OceanBase Database, after you enable case sensitivity for object names such as table names and view names, objects are still 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 are 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 a separate line. When you specify the --cut option on the command line of OBDUMPER, if the exported data contains a single-character field delimiter, OBDUMPER escapes special characters in the data, such as delimiters, carriage returns, and line breaks. For example, if the data is abc|def and the delimiter is |, the exported data is abc\|def.

• When you specify the --cut option on the command line of OBLOADER, do not use the --trail-delimiter option if no field delimiter or delimiter 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 to the database. You must manually convert the exported physical shard to a logical one before you import it into the database for business use.

• When you specify the --partition option on the command line to export data in a partition, you must 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 the privileges of the sys tenant. If OBDUMPER does not have the 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 OceanBase Database in MySQL compatible mode, OceanBase Connector/J converts the zero values of date and time type columns 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 values of date and time columns, but cannot identify whether the original data is NULL or zero values during the 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 approximate non-zero 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 values are imported. Take note of this consideration on zero values when you export data of the DATE, DATETIME, TIME, YEAR, or TIMESTAMP type from OceanBase Database in MySQL compatible mode.

• Feature differences in schema export in Oracle compatible and MySQL 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 OceanBase Database V2.2.30 or earlier in Oracle compatible mode. Definitions of unique indexes on partitioned tables cannot be exported for OceanBase Database of a version ranging from V2.2.70 (inclusive) to V4.0.0 in Oracle compatible mode.
  Oracle compatible	Tables, views, triggers, synonyms, sequences, stored procedures, functions, packages, table groups, and types are supported.