This topic describes the common business scenarios in which OBLOADER is used and provides the corresponding examples.
The following table describes the database information that is used in the examples.
Database information item |
Example value |
|---|---|
| Cluster name | cluster_a |
| IP address of the OceanBase Database Proxy (ODP) host | xx.x.x.x |
| ODP port number | 2883 |
| Tenant name of the cluster | mysql |
| Name of the root/proxyro user under the sys tenant | **u*** |
| Password of the root/proxyro user under the sys tenant | ****** |
| User account (with read/write privileges) under the business tenant | test |
| Password of the user under the business tenant | ****** |
| Schema name | USERA |
Import DDL definition files
Scenario: Import all supported database object definitions in the /output directory to the USERA schema. For OceanBase Database of a version earlier than V4.0.0, the password of the sys tenant must be provided.
Sample code:
$./obloader -h xx.x.x.x -P 2883 -u test -p ****** --sys-user **u*** --sys-password ****** -c cluster_a -t mysql -D USERA --ddl --all -f /output
Task result example:
...
All Load Tasks Finished:
---------------------------------------------------------
| No.# | Type | Name | Count | Status |
---------------------------------------------------------
| 1 | TABLE | table | 1 -> 1 | SUCCESS |
---------------------------------------------------------
Total Count: 1 End Time: 2023-05-11 16:02:08
...
Note
The --sys-user option is used to connect to a user with required privileges in the sys tenant. If the --sys-user option is not specified during import, the default value --sys-user root takes effect.
Import CSV data files
Scenario: Import all supported CSV data files in the /output directory to the USERA schema. For OceanBase Database of a version earlier than V4.0.0, the password of the sys tenant must be provided. For more information about the CSV data file specifications, see RFC 4180. CSV data files (.csv files) store data in the form of plain text. You can open CSV data files by using a text editor or Excel.
Task result example:
...
All Load Tasks Finished:
---------------------------------------------------------
| No.# | Type | Name | Count | Status |
---------------------------------------------------------
| 1 | TABLE | table | 1 -> 1 | SUCCESS |
---------------------------------------------------------
Total Count: 1 End Time: 2023-05-11 16:02:08
...
Sample code:
$./obloader -h xx.x.x.x -P 2883 -u test -p ****** --sys-user **u*** --sys-password ****** -c cluster_a -t mysql -D USERA --csv --table '*' -f /output
Import SQL data files
Scenario: Import all supported SQL data files in the /output directory to the USERA schema. For OceanBase Database of a version earlier than V4.0.0, the password of the sys tenant must be provided. SQL data files (.sql files) store INSERT SQL statements. You can open SQL data files by using a text editor or SQL editor.
Sample code:
$./obloader -h xx.x.x.x -P 2883 -u test -p ****** --sys-user **u*** --sys-password ****** -c cluster_a -t mysql -D USERA --sql --table '*' -f /output
Task result example:
...
All Load Tasks Finished:
---------------------------------------------------------
| No.# | Type | Name | Count | Status |
---------------------------------------------------------
| 1 | TABLE | table | 1 -> 1 | SUCCESS |
---------------------------------------------------------
Total Count: 1 End Time: 2023-05-11 16:02:08
...
Import POS data files
Scenario: Import all supported POS data files in the /output directory to the USERA schema. For OceanBase Database of a version earlier than V4.0.0, the password of the sys tenant must be provided. You must specify the path where the control files reside during the import. POS data files (.dat files by default) organize data based on a byte offset position with a fixed length. To import a POS data file, you must define the fixed length of each field by using a control file. You can open POS data files by using a text editor.
Sample code:
$./obloader -h xx.x.x.x -P 2883 -u test -p ****** --sys-user **u*** --sys-password ****** -c cluster_a -t mysql
-D USERA --table '*' -f /output --pos --ctl-path /output
Note
The table name defined in the database must be in the same letter case as its corresponding control file name. Otherwise, OBLOADER fails to recognize the control file. For more information about the rules for defining control files, see Preprocessing functions.
Import CUT data files
Scenario: Import all supported CUT data files in the /output directory to the USERA schema. For OceanBase Database of a version earlier than V4.0.0, the password of the sys tenant must be provided. To import a CUT data file, you must specify the path of the control file and use |@| as the column separator string. CUT data files (.dat files) use a character or character string to separate values. You can open CUT data files by using a text editor.
Sample code:
$./obloader -h127.1 -P2881 -u test -p ****** --sys-user **u*** --sys-password ****** -c cluster_a -t mysql -D USERA --table '*' -f /output --cut --column-splitter '|@|' --ctl-path /output
Task result example:
...
All Load Tasks Finished:
---------------------------------------------------------
| No.# | Type | Name | Count | Status |
---------------------------------------------------------
| 1 | TABLE | table | 1 -> 1 | SUCCESS |
---------------------------------------------------------
Total Count: 1 End Time: 2023-05-11 16:02:08
...
Note
The table name defined in the database must be in the same letter case as its corresponding control file name. Otherwise, OBLOADER fails to recognize the control file. For more information about the rules for defining control files, see Preprocessing functions.
Import data files from Amazon S3 to OceanBase Database
Scenario: Import all supported CSV data files in an Amazon S3 bucket to the USERA schema. For OceanBase Database of a version earlier than V4.0.0, the password of the sys tenant must be provided.
Sample code:
$./obloader -h xx.x.x.x -P 2883 -u test -p ****** --sys-user **u*** --sys-password ****** -c cluster_a -t mysql -D USERA --csv --table '*' -f /output --storage-uri 's3://obloaderdumper/obdumper?region=cn-north-1&access-key=******&secret-key=******'
--storage-uri 's3://obloaderdumper/obdumper?region=cn-north-1&access-key=******&secret-key=******' option is the uniform resource identifier (URI) of the storage space, its components include:
Component |
Description |
|---|---|
s3 |
The S3 storage scheme. |
obloaderdumper |
The name of the S3 bucket. |
/obdumper |
The data storage path in the S3 bucket. |
region=cn-north-1&access-key=******&secret-key=****** |
The parameters required for the request.
|
Task result example:
...
All Load Tasks Finished:
---------------------------------------------------------
| No.# | Type | Name | Count | Status |
---------------------------------------------------------
| 1 | TABLE | table | 3 -> 3 | SUCCESS |
---------------------------------------------------------
Total Count: 3 End Time: 2023-05-11 16:02:08
...
Note
The --storage-uri option must be used in combination with the -f option.
OBLOADER loads database object definitions and table data from the S3 bucket specified in the --storage-uri option to OceanBase Database. The running logs of OBLOADER are stored in the path specified in the -f option.
Import data files from Alibaba Cloud OSS to OceanBase Database
Scenario: Import all supported CSV data files in an Alibaba Cloud OSS bucket to the USERA schema. For OceanBase Database of a version earlier than V4.0.0, the password of the sys tenant must be provided.
Sample code:
$./obloader -h xx.x.x.x -P 2883 -u test -p ****** --sys-user **u*** --sys-password ****** -c cluster_a -t mysql -D USERA --csv --table '*' -f /output --storage-uri 'oss://antsys-oceanbasebackup/backup_obloader_obdumper/obdumper?endpoint=https://cn-hangzhou-alipay-b.oss-cdn.aliyun-inc.com&access-key=******&secret-key=******'
--storage-uri 'oss://antsys-oceanbasebackup/backup_obloader_obdumper/obdumper?endpoint=https://cn-hangzhou-alipay-b.oss-cdn.aliyun-inc.com&access-key=******&secret-key=******' option is the uniform resource identifier (URI) of the storage space, its components include:
Component |
Description |
|---|---|
oss |
The OSS storage scheme. |
antsys-oceanbasebackup |
The name of the OSS bucket. |
/backup_obloader_obdumper/obdumper |
The data storage path in the OSS bucket. |
endpoint=https://cn-hangzhou-alipay-b.oss-cdn.aliyun-inc.com&access-key=******&secret-key=****** |
The parameters required for the request.
|
Task result example:
...
All Load Tasks Finished:
---------------------------------------------------------
| No.# | Type | Name | Count | Status |
---------------------------------------------------------
| 1 | TABLE | table | 3 -> 3 | SUCCESS |
---------------------------------------------------------
Total Count: 3 End Time: 2023-05-11 16:02:08
...
Note
The --storage-uri option must be used in combination with the -f option.
OBLOADER loads database object definitions and table data from the Alibaba Cloud OSS bucket specified in the --storage-uri option to OceanBase Database. The running logs of OBLOADER are stored in the path specified in the -f option.
Import data files from Apache Hadoop to OceanBase Database
Scenario: Import all supported CSV data files in a Hadoop node to the USERA schema. For OceanBase Database of a version earlier than V4.0.0, the password of the sys tenant must be provided.
Sample code:
$./obloader -h xx.x.x.x -P 2883 -u test -p ****** --sys-user **u*** --sys-password ****** -c cluster_a -t mysql -D USERA --csv --table '*' -f /output --storage-uri 'hdfs://***.*.*.*:9000/chang/parquet?hdfs-site-file=/data/0/zeyang/hdfs-site.xml&core-site-file=/data/0/zeyang/core-site.xml'
--storage-uri 'hdfs://***.*.*.*:9000/chang/parquet?hdfs-site-file=/data/0/zeyang/hdfs-site.xml&core-site-file=/data/0/zeyang/core-site.xml' option is the uniform resource identifier (URI) of the storage space, its components include:
Component |
Description |
|---|---|
hdfs |
The Hadoop storage scheme. |
***.*.*.*:9000 |
The name of the Hadoop node. |
/chang/parquet |
The data storage path in the Hadoop node. |
hdfs-site-file=/data/0/zeyang/hdfs-site.xml&core-site-file=/data/0/zeyang/core-site.xml |
The parameters required for the request.
|
Task result example:
...
All Load Tasks Finished:
---------------------------------------------------------
| No.# | Type | Name | Count | Status |
---------------------------------------------------------
| 1 | TABLE | table | 3 -> 3 | SUCCESS |
---------------------------------------------------------
Total Count: 3 End Time: 2023-05-11 16:02:08
...
Note
The --storage-uri option must be used in combination with the -f option.
OBLOADER loads database object definitions and table data from the Apache Hadoop node specified in the --storage-uri option to OceanBase Database. The running logs of OBLOADER are stored in the path specified in the -f option.
Import database objects and data to a public cloud
Scenario description: When you cannot provide the password of the sys tenant, import all supported database objects and data in the /output directory to the USERA schema in a public cloud.
Sample code:
$./obloader -h xx.x.x.x -P 2883 -u test -p ****** -D USERA --ddl --csv --public-cloud --all -f /output
Import database objects and data to a private cloud
Scenario description: When you cannot provide the password of the sys tenant, import all supported database objects and data in the /output directory to the USERA schema in a private cloud.
Sample code:
$./obloader -h xx.x.x.x -P 2883 -u test -p ****** -c cluster_a -t mysql -D USERA --ddl --csv --no-sys --all -f /output