Command-line options

OBLOADER allows you to specify the information required for import in command-line options. For more information about the options and their scenarios and examples, see Options and Usage examples.

Overview

Option styles

OBLOADER supports the Unix and GNU styles of command-line options.

• Unix style: An option is prefixed with a hyphen and each option is a single character, such as ps -e. In this style, you can omit the space between an option and its value, such as -p******.

• GNU style: An option is prefixed with double hyphens and each option is a single character or a string, such as ps --version. An option and its value must be separated with a space, such as --table 'test'.

Option categories

Command-line options in OBLOADER are classified into basic options and advanced options.

• Basic options: general options of OBLOADER, including connection options (such as the database connection method), feature options (such as the file format, database object type, and storage path), and other options.

• Advanced options: feature options (such as the timestamp format, allowlist- and blocklist-based table/column filtering settings, and error handling method), performance options, and other options.

Required options

When using OBLOADER to import data, you must include at least the following options: connection options, format options, database object type options, and storage path options.

Example statement:

$./obloader -h xx.x.x.x -P 2883 -u test@mysql#cluster_a -p ****** -D USERA --csv --table '*' -f /output

In this statement, -h, -P, -u, -p, and -D are connection options; --csv is a file format option; --table is a database object type option; and -f specifies the storage path.

Basic options

Connection options

OBLOADER needs to connect to OceanBase Database to read and write data. You can specify the following options to connect to OceanBase Database.

Import types

Direct load

OBLOADER 4.2.6 and later support direct load.

• --rpc-port= rpc_port_num

  Specifies the port number of the OBServer RPC endpoint. This option is used in conjunction with --direct and --parallel to indicate that data is imported in direct load mode by connecting to the OBServer RPC endpoint.

  Note

  • This option is applicable only to OceanBase Database 4.2.0 RC2 and later.
  • Obtain the OBServer RPC endpoint as follows:
    • When connecting to the ODP service endpoint:
      • In an OceanBase Database environment, the ODP RPC port is 3307 by default.
      • In an OceanBase Database environment, the default port is 2885. If you need to customize the port, you can specify it using the -s option when you start ODP.
    • When connecting to the OBServer service endpoint, you can query the DBA_OB_SERVERS system view in the sys tenant to obtain the OBServer RPC endpoint, which is 2882 by default.

• --direct

  Specifies direct load mode. This option is used in conjunction with --rpc-port and --parallel.

  Note

  • OBLOADER does not support binary data types in direct load mode.
  • OBLOADER supports direct load mode for connecting to OBServer and ODP. The corresponding version requirements are as follows:
    • When connecting to OBServer: the OBServer version must be 4.2.0 or later.
    • When connecting to ODP: the ODP version must be 4.1.3 or later, and the OBServer version must be 4.2.1 or later.

• --parallel= parallel_num

  Specifies the parallelism for data loading in direct load mode. This option is used in conjunction with --rpc-port and --direct.

Feature options

File formats

Database object types

• --all

  This option is used to import all supported database object definitions and table data. When used with --ddl, it indicates importing all database object definition files. When used with --csv, --sql, --cut, or --pos, it indicates importing all corresponding data files. To import all database object definitions and table data, you can specify the --all option with any data format option.

  Note

  The --all option is mutually exclusive with any other database object option. If both --all and another database object option are specified, the --all option takes precedence.

• --table-group 'table_group_name [,table_group_name...]' | --table-group '*'

  This option is used to import table group definitions. Except for not supporting data import, the description is the same as that of the --table option.

• --table 'table_name [,table_name...]' | --table '*'

  This option is used to import table definitions or table data. When used with --ddl, it indicates importing only table definitions. When used with any data format option, it indicates importing only table data. If multiple tables are specified, separate the table names with commas (,). By default, table names in OceanBase Database in Oracle mode are imported in uppercase, and table names in OceanBase Database in MySQL mode are imported in lowercase. For example, in OceanBase Database in Oracle mode, --table 'test' and --table 'TEST' both indicate the TEST table. In OceanBase Database in MySQL mode, --table 'test' and --table 'TEST' both indicate the test table. If you want to distinguish between uppercase and lowercase, enclose the table name in brackets ([ ]). For example, --table '[test]' indicates the test table, and --table '[TEST]' indicates the TEST table. If the table name is specified as an asterisk (*), it indicates importing all table definitions or table data.

  Notice

  If you use a control file for import, the case of the table names specified by --table must match the case in the database. Otherwise, the control file will not take effect.

• --view 'view_name [, view_name...]' | --view '*'

  This option is used to import view definitions. Except for not supporting data import, the description is the same as that of the --table option.

• --trigger 'trigger_name [, trigger_name...]' | --trigger '*'

  This option is used to import trigger definitions. Except for not supporting data import, the description is the same as that of the --table option.

• --sequence 'sequence_name [, sequence_name...]' | --sequence '*'

  This option is used to import sequence definitions. Except for not supporting data import, the description is the same as that of the --table option. This option is applicable only to OceanBase Database in Oracle mode.

• --synonym 'synonym_name [, synonym_name...]' | --synonym '*'

  This option is used to import synonym definitions. Except for not supporting data import, the description is the same as that of the --table option. This option is applicable only to OceanBase Database in Oracle mode.

• --type 'type_name [, type_name...]' | --type '*'

  This option is used to import type definitions. Except for not supporting data import, the description is the same as that of the --table option. This option is applicable only to OceanBase Database in Oracle mode.

• --type-body 'typebody_name [, typebody_name...]' | --type-body '*'

  This option is used to import type body definitions. Except for not supporting data import, the description is the same as that of the --table option. This option is applicable only to OceanBase Database in Oracle mode.

• --package 'package_name [, package_name...]' | --package '*'

  This option is used to import package definitions. Except for not supporting data import, the description is the same as that of the --table option. This option is applicable only to OceanBase Database in Oracle mode.

• --package-body 'packagebody_name [, packagebody_name...]' | --package-body '*'

  This option is used to import package body definitions. Except for not supporting data import, the description is the same as that of the --table option. This option is applicable only to OceanBase Database in Oracle mode.

• --function 'function_name [, function_name...]' | --function '*'

  This option is used to import function definitions. Except for not supporting data import, the description is the same as that of the --table option.

• --procedure 'procedure_name [, procedure_name...]' | --procedure '*'

  This option is used to import stored procedure definitions. Except for not supporting data import, the description is the same as that of the --table option.

File path

• -f 'file_path', --file-path= 'file_path'

  Specifies the absolute path of the local disk where the data file is stored. When you import data files from Aliyun OSS, you must specify the -f option to save the generated logs and binary files.

• --storage-uri 'storage_uri_string'

  Specifies the uniform resource locator (URL) of the storage. OBLOADER 4.2.0 and later versions support importing database object definitions and table data from Aliyun OSS or Amazon S3. OBLOADER 4.2.1 and later versions support importing database object definitions and table data from Hadoop.

  The syntax of 'storage_uri_string' is as follows:

  [scheme://host]path[?parameters]
  
  parameters: key[=value],...
  

  The following table describes the components of the URL.

  Component	Description
  scheme	The storage type. Valid values: Aliyun OSS, Amazon S3, and Hadoop. If the specified scheme is not supported, an error is returned.
  host	The name of the storage space. When you import data from OSS or S3, the host indicates the bucket. For more information, see OSS Bucket. When you import data from Apache Hadoop, the host indicates the Hadoop node. The format is either <ip>:<port> or <cluster_name>.
  path	The resource path of the data to be imported from the storage space. The path** must** start with /.
  parameters	The parameters required for the request. Each parameter can be a key or a key-value pair.

  Example: Import data from S3

  --storage-uri 's3://bucket/path?region={region}&access-key={accessKey}&secret-key={secretKey}'
  

  • s3: the scheme is s3.
  • bucket: the name of the S3 storage space.
  • path: the resource path of the data to be imported from S3.
  • ?region={region}&access-key={accessKey}&secret-key={secretKey}: the values of the region, access-key, and secret-key parameters.

  Supported parameters:

  Parameter	Whether a value is required	Description	Supported storage type	Supported version
  endpoint	Yes	Specifies the endpoint of the OSS host. Specifies the domain name endpoint for accessing S3.	OSS S3	4.2.0 4.2.5
  region	Yes	Specifies the terminal node, which is the physical location of the S3 bucket.	S3	4.2.0
  storage-class	Yes	Specifies the Amazon S3 storage class.	S3	4.2.0
  access-key	Yes	Specifies the access account for the storage.	OSS/S3	4.2.0
  secret-key	Yes	Specifies the access key for the storage.	OSS/S3	4.2.0
  hdfs-site-file	Yes	Specifies the hdfsSiteFile configuration file, which contains the Apache Hadoop configuration information for setting the storage and access rules of Apache Hadoop, such as the block size and the number of replicas.	Apache Hadoop	4.2.1
  core-site-file	Yes	Specifies the hdfsSiteFile configuration file, which contains the core configuration information of the Hadoop cluster, such as the URI and default file system of the Apache Hadoop file system.	Apache Hadoop	4.2.1
  principal	Yes	Specifies the Kerberos authentication identifier.	Apache Hadoop	4.2.1
  keytab-file	Yes	Specifies the absolute path of the Keytab file for authorizing users or services to access the system resources.	Apache Hadoop	4.2.1
  krb5-conf-file	Yes	Specifies the Kerberos configuration file path.	Apache Hadoop	4.2.1

  Note

  • When you import database object definitions and table data from Aliyun OSS, you must specify the endpoint, access-key, and secret-key parameters.
  • When you import database object definitions and table data from Amazon S3, you must specify the region, access-key, and secret-key parameters.

• --ctl-path 'control_path'

  Specifies the absolute path of the local disk where the control file is stored. You can configure built-in processing functions in the control file. Before data is imported, the configured functions are used to preprocess the data. For example, you can convert the case of a string or determine whether a string is empty. For more information about the control file, see Data Processing. When you specify this option in the command line, you must enclose the parameter value in single quotation marks. For example: --ctl-path '/home/controls/'.

• --log-path 'log_path'

  Specifies the output directory of the OBLOADER running logs. If you do not specify this option, the running logs of OBLOADER are output to the directory specified by the -f option. In most cases, you do not need to use redirection to output the logs.

Other options

• -H, --help

  Displays the help for the CLI tool.

• -V, --version

  Displays the version number of the current tool.

Advanced options

Features

Timestamp format

• --nls-date-format 'date-format-string'

  Specifies the date format for the database connection in OceanBase Database's Oracle mode. Default value: YYYY-MM-DD HH24:MI:SS.

• --nls-timestamp-format 'timestamp-format-string'

  Specifies the timestamp format for the database connection in OceanBase Database's Oracle mode. Default value: YYYY-MM-DD HH24:MI:SS:FF9.

• --nls-timestamp-tz-format 'timestamp-tz-format-string'

  Specifies the timestamp format with timezone for the database connection in OceanBase Database's Oracle mode. Default value: YYYY-MM-DD HH24:MI:SS:FF9 TZR.

Allowlist- and blocklist-based filtering

• Table-level filtering

  • --exclude-table 'table_name [, table_name...]'

    Specifies the tables to exclude when importing table definitions or data. Table names support pattern matching. Example: --exclude-table 'test1,test*,*test,te*st' This parameter excludes the following tables when importing table definitions or data:

    • test1

    • All tables whose names start with test

    • All tables whose names end with test

    • All tables whose names start with te and end with st

  • --exclude-data-types 'datatype [, datatype...]'

    Specifies the data types to exclude when importing data.

  • --file-regular-expression

    Specifies the regular expression to use when importing a single table file. This option is applicable only to single-table imports. Example: --file-regular-expression ".*" matches all files in the directory using the ".*" regular expression, where * matches zero or more arbitrary characters. Another example: --file-regular-expression ".*\\.csv" matches files ending with ".csv" using the ".*\.csv" regular expression, where "\." matches the actual period character ".".

• Column-level filtering

  • --exclude-column-names 'column_name [, column_name...]'

    Specifies the column names to exclude when importing data.

    Notice

    • The specified column names must match the case of the column names in the table structure.
    • In the imported data file, the excluded columns do not have corresponding data, and the order of the imported columns must match the order of the columns in the table.

Error handling

• --max-discards int_num

  Specifies the maximum number of duplicate records allowed per table. If the number of duplicate records in any table exceeds this limit, the import for that table is stopped, and the table is marked as failed in the log. Other tables are not affected. Default value: -1, which means to ignore duplicate records and continue the import.

  Note

  This option takes effect only if the table contains a primary key or a unique key.

• --retry

  Specifies whether to retry the import task from the point of interruption. Recommendation: If the imported data volume exceeds 80%, use this option for resuming the import to avoid re-importing (which may result in a small number of duplicate records). If the imported data volume is not significant, consider clearing the table and starting the import again for better efficiency.

  Notice

  • The load.ckpt file is a checkpoint file generated during the tool's runtime, located in the directory specified by -f. If the load.ckpt file does not exist, this option cannot be used.
  • This option is not applicable to direct load imports.

• --max-errors int_num

  Specifies the maximum number of errors allowed per table during import. If the number of errors in any table exceeds this limit, the import for that table is stopped, and the table is marked as failed in the log. The optional values are 0, -1, and a positive integer N. If the value is set to -1, it means to ignore errors and continue the import. Default value: 1000.

• --strict= 'strict_string'

  Specifies whether to terminate the process upon encountering bad or discarded records during import. Default value: true, which means to terminate the process (System exit 1) if bad or discarded records are found in the imported data. false means to not affect the process's exit status (System exit 0) even if bad or discarded records are found.

  Note

  This option can be used with --max-discards or --max-errors to skip errors when the number of duplicates or errors is within the specified range. For more information, see Error handling.

• --replace-data

  Specifies whether to replace duplicate data in the table. This option is applicable only to tables with a defined primary key or unique key (including non-null fields). If a large amount of duplicate data (more than 30% of the total data) exists in the file, it is recommended to clear the table and re-import the data. Data replacement is less efficient than importing to an empty table. This option is effective only when used with --csv, --sql, or --cut. It does not take effect with the --ddl option.

  Notice

  • If duplicate data exists in the file or table, the data in the table will be replaced with the data in the file.
  • For tables without a primary key or unique key, this option appends data to the table.
  • If you do not need to replace duplicate data, do not specify this option in the command line to avoid affecting your business operations.

Performance options

• --rw float_num

  Specifies the proportion of file parsing threads to the total number of threads. Default value: 1. When used with the --thread option, the number of file parsing threads is calculated as --thread value * --rw value.

• --slow float_num

  Specifies the threshold for entering slow import mode. When OceanBase memory usage reaches 75%, OBLOADER automatically slows down to prevent continuous memory usage spikes. Default value: 0.75.

• --pause float_num

  Specifies the threshold for entering pause mode. When OceanBase memory usage reaches 85%, OBLOADER automatically stops importing to prevent high memory levels. Default value: 0.85.

• --batch int_num

  Specifies the size of batch writes. It is recommended to set this option inversely proportional to the table width, but avoid setting it too high to prevent database memory overflow. Default value: 200.

  Note

  OBLOADER 4.2.0 and later versions support adaptive --batch default values based on the Java Virtual Machine's memory.

• --thread int_num

  Specifies the number of concurrent threads. This option directly corresponds to the number of write threads. When used with the --rw option, it calculates the number of file parsing threads using the formula: --thread value * --rw value. The default value is CPU * 2, with a maximum of 32 if the CPU count exceeds 16.

  Note

  OceanBase Database V4.2.1 and later versions support parallel DDL import. When importing database object definitions from versions earlier than V4.2.1, you do not need to specify this option.

• --block-size

  Specifies the threshold for splitting file blocks, supporting LONG data types. For example, --block-size 64 indicates that a single file should not exceed 64MB. The unit is MB by default, with a default value of 64. By default, OBLOADER automatically splits large files into multiple logical subfiles of 64MB each, without occupying additional storage space.

• --max-tps int_num

  Specifies the maximum import TPS. Specifying this option ensures a stable import rate.

• --max-wait-timeout int_num

  Specifies the maximum waiting time for OceanBase server-side compaction. The unit is hours by default. When the OceanBase server is in a compaction state, the client enters pause mode, and the maximum waiting time cannot exceed the value specified by this option. Default value: 3.

Other options

• --truncate-table

  Specifies whether to truncate tables in the target database before importing data. This option is only applicable when used with any data format option. When used with --all or --table '*', it truncates all tables in the database. If only specific tables need to be truncated, you can explicitly specify --table 'test1,test2,[....]'. When used with the --with-data-files option, it truncates only tables that have corresponding data files.

  Notice

  • When using --all or --table '*' with --truncate-table, the program will truncate all tables in the target database, even if there are no corresponding data files in the directory specified by the -f option.
  • Do not use this option to truncate tables in the target database or target tables. It is strongly recommended to manually truncate tables based on business needs to avoid affecting operations.

• --with-data-files

  When used with the --truncate-table or --delete-from-table option, it specifies truncating or clearing only tables that have corresponding data files. Specifying this option alone has no effect.

• --delete-from-table

  Specifies whether to delete all data from tables in the target database before importing data. This option is only applicable when used with any data format option. When used with --all or --table '*', it deletes all data from all tables in the database. If only specific tables need to be cleared, you can explicitly specify --table 'test1,test2,[....]'. When used with the --with-data-files option, it deletes only data from tables that have corresponding data files.

  Notice

  • When using --all or --table '*' with --delete-from-table, the program will delete all data from all tables in the target database, even if there are no corresponding data files in the directory specified by the -f option.
  • Do not use this option to delete data from the target database or target tables, especially large tables. It is strongly recommended to manually delete data based on business needs to avoid affecting operations.

• --replace-object

  Specifies whether to replace existing database object definitions when importing database object definitions. For tables and synonyms, it uses a delete-and-create approach. For functions and stored procedures, it uses CREATE OR REPLACE. This option is only applicable when used with the --ddl or --mix option and does not affect other data format options like --csv or --sql.

  Notice

  • If an object already exists in the target database, it will be forcibly replaced with the object definition saved in the file.
  • If you do not need to replace database objects, do not use this option to avoid affecting operations.

• --session-config

  Specifies the connection configuration file. A default configuration file is provided in the package: <tool_root_directory>/conf/session.config.json. You do not need to configure it separately. It is recommended to specify this option only when you need to use the same package to load multiple connection configurations.

• --external-data

  Specifies whether the imported dataset was exported by a third-party tool. When OBDUMPER exports data, it generates a MANIFEST.bin file in the directory specified by the -f option to save metadata. OBLOADER imports data by default, parsing this metadata file. If the file is missing or the data was exported by a third-party tool without a metadata file, you can specify this option to skip metadata parsing during import.

Options