OBLOADER allows you to import required data by specifying command-line options.
Sample command
The following code provides an example on how to import data. For more information, see the following Options table and the Scenarios topic.
If you connect OBLOADER to an OceanBase cluster through 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 name> -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 be connected to. | |
| -P (--port) | Yes | The port number of the OBProxy or OBServer to be connected to. | |
| -c (--cluster) | No | The cluster name of the cluster. You do not need to specify this option if OBDUMPER 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 a 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 | Uses 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 name extension: -schema.sql. | |
| --sql | No | Imports files in the SQL format. Default file name extension: .sql. | |
| --mix | No | Imports files that contain both schemas and data. | |
| --csv | No | Imports CSV files. We recommend that you use this option. Default file name 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. Different from data in the CSV format, data in the CUT format is separated by strings. | |
| --all | No | Imports the definitions of all database objects. | |
| --table | No | The table to be exported. 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. | |
| --replace-object | No | Replaces a table schema if the table schema already 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, which is 1 read thread to 5 write threads. | |
| --slow | No | The memory usage threshold of the OBServer, exceeding which OBLOADER will slow down. Default value: 0.75. | |
| --pause | No | The memory usage threshold of the OBServer, exceeding which OBLOADER will pause. Default value: 0.85. | |
| --batch | No | The number of queries supported in a single transaction. Default value: 200. | |
| --thread | No | The number of worker threads. Default value: CPU × 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-wait-timeout | No | The maximum amount of time OBLOADER waits for a major compaction to finish before it times out, in hours. Default value: 3. | |
| --trail-delimiter | No | Deletes the last column delimiter if the data ends with column delimiters. | |
| --with-trim | No | Truncates 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 | The quotation marks. Default value: ' '. | |
| --exclude-table | No | * If you specify a table name, the schema and data of the specified table are not exported. Example: --exclude-table 'a,b,c'. * If you do not specify a table, all table schemas and data are exported. Example: --exclude-table ''. * You can use asterisks (*) for fuzzy matching. For example, you can specify --exclude-table '*ab*' to exclude tables whose names contain ab. |
|
| --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 a 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 Object Storage Service (OSS) bucket. | |
| --secret-key | No | The secret key used to access the OSS bucket. | |
| --bucket-uri | No | The URI of the bucket. URI format: oss://bucket/key1/key2 Example: oss:// oceanbasepublic/tools/obdumper. | |
| --max-discards | No | Limits the number of discards 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. |
|
| --max-errors | No | The maximum number of errors that can be ignored. You can ignore all errors. By default, at most 10 errors can be ignored. If more than 10 errors occur for a thread, the program exits. This option applies to a single object. For example, when you import Table A and Table B, if the errors generated by Table A reach the specified value of the max-errors option, OBLOADER stops the import of Table A and continues to import Table B. * --max-errors=0: OBLOADER reports an error and stops the import once an error is generated. * --max-errors=N: The N represents a positive integer. OBLOADER reports an error and stops the import when N errors occur. * --max-errors=-1: OBLOADER ignores the errors and continues the import. Notice However, some errors cannot be ignored and must be eliminated before you can continue the import. For more information about errors that cannot be ignored, see FAQ about OBLOADER . |
|
| --replace-data | No | Supports the import of incremental data, and allows you to overwrite and insert data when a primary key or unique key conflict occurs. You can import files in the --sql, --csv, or --cut format. The --sql format is preferred. |
|
| --truncate-table | No | Specifies whether to clear tables before the import. We recommend that you do not use this option. Instead, we recommend that you manually clear tables. When an application starts, an error may be reported to indicate that the partition information cannot be found. |