You can specify command-line options for OBLOADER to import data.
Example command
The following statement shows an example of an OBLOADER command. For more information, see the following command-line options and the Scenarios topic. Notice
When you connect OBLOADER to an OceanBase cluster through 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 connect to. When you directly connect OBLOADER to an OBServer, you do not need to use the -c option to specify the cluster name.
[admin@localhost]>./obloader -h <host IP address> -P <port number> -u <user name> -p <password> --sys-password <password of a user under the sys tenant> -c <cluster name> -t <tenant name> -D <schema name> [--ddl] [--csv|--sql] [--all|--table 'table name'] -f<data file or directory>
Command-line options
| Option | Required | Description |
|---|---|---|
| -h (--host) | Yes | Specifies the IP address of the OBProxy server or OBServer to connect to. |
| -P (--port) | Yes | Specifies the port number of the OBProxy server or OBServer to connect to. |
| -c (--cluster) | No | Specifies the database cluster name. You do not need to specify this option if OBLOADER is directly connected to an OBServer. |
| -t (--tenant) | No | Specifies the name of the current tenant. |
| -u (--user) | Yes | Specifies the user name that you use to log on to the database. |
| -p (--password) | No | Specifies the password that you use to log on to the database. |
| -D (--database) | No | Specifies the name of the database. |
| -f (--file-path) | Yes | Specifies the directory that stores the data file or the absolute path of the data file. |
| --sys-user | No | The name of a user under the sys tenant. Default value: root@sys. |
| --sys-password | No | The password of the user specified by the --sys-user option. Default value: null. |
| --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 here. |
| --file-encoding | No | Specifies the file encoding format. Default value: UTF-8. |
| --ctl-path | No | Specifies the directory where the control file is stored. The extension of the control file is updated to .ctrl. |
| --map-path | No | Specifies the directory where the file that stores the mapping relationships of data fields is stored. |
| --log-path | No | Specifies the directory where the log files are stored. |
| --ddl | No | Specifies to import files in the DDL format. Default file extension: -schema.sql. |
| --sql | No | Specifies to import files in the SQL format. Default file extension: .sql. |
| --mix | No | Specifies to import files that contain both structures and data. |
| --csv | No | Specifies to import CSV files (recommended). Default file extension: .csv. |
| --pos | No | Specifies to import files in the POS format. In these files, data is organized based on the byte offsets of a fixed length. |
| --cut | No | Specifies to import files in the CUT format in which data is separated by several characters. Unlike in the CSV format, data in the CUT format is separated by a string. |
| --all | No | Specifies to import the structure definitions of all database objects. |
| --table | No | Specifies the tables to be exported. Separate multiple tables with commas (,). You can use '*' to represent all the tables in the database specified by the -D option. |
| --view | No | Specifies the views to be imported. Separate multiple views with commas (,). You can use '*' to represent all the views in the database specified by the -D option. |
| --trigger | No | Specifies the triggers to be imported. Separate multiple triggers with commas (,). You can use '*' to represent all the triggers in the database specified by the -D option. |
| --function | No | Specifies the functions to be imported. Separate multiple functions with commas (,). You can use '*' to represent all the functions in the database specified by the -D option. |
| --procedure | No | Specifies the stored procedures to be imported. Separate multiple stored procedures with commas (,). You can use '*' to represent all the stored procedures in the database specified by the -D option. |
| --sequence | No | Specifies the sequences to be imported. Separate multiple sequences with commas (,). You can use '*' to represent all the sequences in the database specified by the -D option. |
| --synonym | No | Specifies the synonyms to be imported. Separate multiple synonyms with commas (,). You can use '*' to represent all the synonyms in the database specified by the -D option. |
| --type | No | Specifies the types to be imported. Separate multiple types with commas (,). You can use '*' to represent all the types in the database specified by the -D option. |
| --type-body | No | Specifies the type bodies to be imported. Separate multiple type bodies with commas (,). You can use '*' to represent all the type bodies in the database specified by the -D option. |
| --package | No | Specifies the program packages to be imported. Separate multiple program packages with commas (,). You can use '*' to represent all the program packages in the database specified by the -D option. |
| --package-body | No | Specifies the package bodies to be imported. Separate multiple package bodies with commas (,). You can use '*' to represent all the package bodies in the database specified by the -D option. |
| --truncate-table | No | Specifies whether to truncate tables before importing them. We recommend that you manually truncate tables. When the program starts, an error may be returned, indicating that the partition information cannot be found. |
| --replace-object | No | Specifies to replace a table schema if it already exists. |
| --rw | No | Specifies the ratio between the read threads and write threads. Default value: 0.2 (1 read thread/5 write threads) |
| --slow | No | Specifies the memory usage threshold of the OBServer, exceeding which OBLOADER will slow down. Default value: 0.75. |
| --pause | No | Specifies the memory usage threshold of the OBServer, exceeding which OBLOADER will pause. Default value: 0.85. |
| --batch | No | Specifies the number of queries supported in one transaction. Default value: 200. |
| --thread | No | Specifies the number of worker threads. Default value: CPU × 2. |
| --block-size | No | Specifies the size of a file block. Default value: 64 MB. |
| --retry | No | Specifies whether to resume the import job from the last saved point after the job is interrupted. |
| --external-data | No | Specifies whether the data was exported by using a third-party tool. OBLOADER does not record the meta information of data exported by third-party tools. |
| --max-tps | No | Specifies the upper limit of the import speed in the unit of rows/second. Default value: Integer.MAX_VALUE. |
| --max-errors | No | Specifies the maximum number of errors allowed. Default value: 10. If a single thread encounters more errors than the value defined by this option, the program exits. |
| --max-wait-timeout | No | Specifies the maximum amount of time OBLOADER waits for a major compaction to finish before it times out. Default value: 3h. |
| --nls-date-format | No | Specifies 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 | Specifies 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 | Specifies the timestamp + timezone format in Oracle mode. Default value: YYYY-MM-DD HH24:MI:SS:FF9 TRZ. |
| --trail-delimiter | No | Specifies to delete the last column delimiter if the data ends with column delimiters. |
| --with-trim | No | Specifies to trim 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 | Specifies the string to replace NULL values. Default value: \N. If the value is not \N after the data is imported, the original value is a NULL string. |
| --empty-string | No | Specifies the escape character for an empty string (' '). Default value: \E. |
| --line-separator | No | Specifies the line separator. Default value: '\n'. |
| --column-separator | No | Specifies the column separator. Default value: , (comma). |
| --escape-character | No | Specifies the escape character. Default value: \ (backslash) |
| --column-delimiter | No | Specifies whether to use single quotation marks or double quotation marks. Default value: ' ' (single quotation marks). |
| --ignore-unhex | No | Specifies to skip decoding binary data, such as data of the BLOB or BINARY types, that has not been converted into a hexadecimal string. |
| --exclude-table | No | Specifies not to export table schemas. |
| --exclude-data-types | No | Specifies the data types to skip during data import. |
| --column-splitter | No | Specifies the column splitter. Unlike in the CSV format where the separator is a single character, this option allows you to use a string, which can have several characters, to separate data. |