Command-line options|V2.2.2|OceanBase Loader and Dumper| docs|Distributed Database

OBLOADER allows you to import required data by specifying command-line options.

Sample command

The following command provides an example on how to import data. For more information, see the following option list and the Scenarios topic. Notice

If you connect OBLOADER to an OceanBase cluster by using OceanBase DataBase Proxy (OBProxy), use the -h and -P options to specify the IP address and port number of the OBProxy host. Then, use the -c option to specify the name of the cluster to be connected to. If you directly connect OBLOADER to an OBServer, you do not need to use the -c option to specify the name of the cluster to be connected to.

[admin@localhost]>./obloader  -h <host IP address> -P <port number> -u <username> -p <password>  --sys-password <password of a user under the SYS tenant> -c <cluster> -t <tenant> -D <--database> [--ddl] [--csv|--sql] [--all|--table 'table name'] -f<data file or directory>

Options

Option	Required	Description
-h(--host)	Yes	The IP address of the OBProxy or OBServer to connect to.
-P(--port)	Yes	The port number of the OBProxy or OBServer to connect to.
-c(--cluster)	No	The cluster name of the database. You do not need to specify this option if OBLOADER is directly connected to an OBServer.
-t(--tenant)	No	The tenant name of the user.
-u(--user)	Yes	The username that you use to log on to the database.
-p(--password)	No	The password that you use to log on to the database.
-D(--database)	No	The name of the database.
-f(--file-path)	Yes	The directory that stores the data file or the absolute path of the data file.
--sys-user	No	The username of the user under the SYS tenant. Default value: root@sys.
--sys-password	No	The password of the user specified by the --sys-user option. By default, this option is left empty.
--public-cloud	No	Specifies whether to use the limited mode to import data. You do not need to specify the `--sys-user` option in limited mode.
--file-suffix	No	The extension of the data file. Do not confuse a file extension and a file format. You can use regular expressions.
--file-encoding	No	The file encoding format. Default value: UTF-8.
--ctl-path	No	The directory where the control file is stored. The extension of the control file is updated to .ctrl from .ctl.
--map-path	No	The directory where the file that stores data field mappings is stored.
--log-path	No	The directory to which log files are exported.
--ddl	No	Imports files in the DDL format. Default file extension: -schema.sql.
--sql	No	Imports files in the SQL format. Default file extension: .sql.
--mix	No	Imports files that contain both schemas and data.
--csv	No	Imports files in the CSV format. We recommend that you use this option. Default file extension: .csv.
--pos	No	Imports files in the POS format. In these files, data is organized based on the byte offsets of a fixed length.
--cut	No	Imports files in the CUT format in which data is separated by several characters. Unlike data in the CSV format, data in the CUT format is separated by strings.
--all	No	Imports the schemas of all database objects.
--table	No	The table to be imported. Separate multiple tables with commas (,). If you set this option to an asterisk (*), all tables in the database specified by the -D option are imported.
--view	No	The view to be imported. Separate multiple views with commas (,). If you set this option to an asterisk (*), all views in the database specified by the -D option are imported.
--trigger	No	The trigger to be imported. Separate multiple triggers with commas (,). If you set this option to an asterisk (*), all triggers in the database specified by the -D option are imported.
--function	No	The function to be imported. Separate multiple functions with commas (,). If you set this option to an asterisk (*), all functions in the database specified by the -D option are imported.
--procedure	No	The stored procedure to be imported. Separate multiple stored procedures with commas (,). If you set this option to an asterisk (*), all stored procedures in the database specified by the -D option are imported.
--sequence	No	The sequence to be imported. Separate multiple sequences with commas (,). If you set this option to an asterisk (*), all sequences in the database specified by the -D option are imported.
--synonym	No	The synonym to be imported. Separate multiple synonyms with commas (,). If you set this option to an asterisk (*), all synonyms in the database specified by the -D option are imported.
--type	No	The type to be imported. Separate multiple types with commas (,). If you set this option to an asterisk (*), all types in the database specified by the -D option are imported.
--type-body	No	The type body to be imported. Separate multiple type bodies with commas (,). If you set this option to an asterisk (*), all type bodies in the database specified by the -D option are imported.
--package	No	The program package to be imported. Separate multiple program packages with commas (,). If you set this option to an asterisk (*), all program packages in the database specified by the -D option are imported.
--package-body	No	The package body to be imported. Separate multiple package bodies with commas (,). If you set this option to an asterisk (*), all package bodies in the database specified by the -D option are imported.
--truncate-table	No	Specifies whether to clear table data before the import. We recommend that you do not use this option. Instead, we recommend that you manually clear table data. If you use this option, an error may be returned when an application starts, indicating that the partition information cannot be found.
--replace-object	No	Replaces a table schema if the table schema exists. We recommend that you do not use this option.
--rw	No	The ratio between the read threads and write threads. Default value: 0.2 (1 read thread/5 write threads).
--slow	No	The memory usage threshold of the OBServer. Default value: 0.75. If the threshold is exceeded, OBLOADER slows down.
--pause	No	The memory usage threshold of the OBServer. Default value: 0.85. If the threshold is exceeded, OBLOADER is paused.
--batch	No	The maximum number of queries supported in a single transaction. Default value: 200.
--thread	No	The number of worker threads. Default value: Number of CPU cores × 2.
--block-size	No	The size of a file block, in MB. Default value: 64.
--retry	No	Specifies whether to resume the import from the last saved point after the import is interrupted.
--external-data	No	Records data if it is exported by using a third-party tool. OBLOADER does not check the meta information about data exported by third-party tools.
--max-tps	No	The upper limit of the import speed in the unit of rows/second. Default value: Integer.MAX_VALUE.
--max-errors	No	The maximum number of errors allowed. Default value: 10. If a single thread encounters more errors than the upper limit, the program exits.
--max-wait-timeout	No	The maximum amount of time OBLOADER waits for a major compaction to finish before it times out. Unit: hours. Default value: 3.
--nls-date-format	No	The date and time format in Oracle mode. Default value: YYYY-MM-DD HH24:MI:SS. This option is supported in OBLOADER V2.1.11 and later.
--nls-timestamp-format	No	The timestamp format in Oracle mode. Default value: YYYY-MM-DD HH24:MI:SS:FF9. This option is supported in OBLOADER V2.1.11 and later.
--nls-timestamp-tz-format	No	The timestamp and timezone format in Oracle mode. Default value: YYYY-MM-DD HH24:MI:SS:FF9 TRZ.
--trail-delimiter	No	Deletes the last column delimiter if the data ends with column delimiters.
--with-trim	No	Deletes the leading and trailing spaces of the data.
--skip-header	No	Specifies whether to skip the first row in a CSV file. By default, the first row is not skipped.
--null-string	No	The string to replace `NULL`. Default value: \N. If the value is not \N after the data is imported, the original value is a NULL string.
--empty-string	No	The escape character for an empty string (' '). Default value: \E.
--line-separator	No	The row delimiter. Default value: '\n'.
--column-separator	No	The column delimiter. Default value: ','.
--escape-character	No	The escape character. Default value: '\'.
--column-delimiter	No	Uses single quotation marks (') or double quotation marks ("). By default, single quotation marks (') are used.
--ignore-unhex	No	Skips the decoding of binary data, such as data of the `BLOB` or `BINARY` type, that is not converted into a hexadecimal string.
--exclude-table	No	* If you specify a table name, the schema and data of the specified table are not imported. This option does not support fuzzy matching. Example:`--exclude-table 'a,b,c'`. * If you do not specify a table, all table schemas and data are imported. Example: `--exclude-table ''`.
--exclude-data-types	No	The data types to skip during data import.
--column-splitter	No	The column delimiter. Different from the CSV format where the delimiter is a single character, this option allows you to use a string, which can have several characters, to separate data.
--oss-endpoint	No	The endpoint of the region where the Object Storage Service (OSS) bucket resides. For example, for the China (Hangzhou) region, the value must be https://oss-cn-hangzhou.aliyuncs.com.
--access-key	No	The account used to access the OSS bucket.
--secret-key	No	The secret key used to access the OSS bucket.
--bucket-uri	No	The URI of the bucket. Format: oss://bucket/key1/key2. Example: oss:// oceanbasepublic/tools/obdumper.
--max-discards	No	The maximum number of discards that can be generated by a single table. This option applies to a single object. For example, when you import Table A and Table B, if the discards generated by Table A reach the specified value of the `max-discards` option, OBLOADER stops the import of Table A and continues to import Table B. * `--max-discards`=0: OBLOADER sends an alert and stops the import once a discard is generated. * `--max-discards`=N: The N represents a positive integer. OBLOADER sends an alert and stops the import when N discards are generated. * `--max-discards`=-1: OBLOADER ignores the alert and continues the import.