This topic describes how to import data in batches in OceanBase Developer Center (ODC).
Background information
ODC provides the database import and export features and the single table import and export features to facilitate database data maintenance for database developers. The database import and export features allow you to import data to or export data or schemas from selected objects in the database in batches. The single table import and export features allow you to import data to or export data from the target table. ODC supports exporting data in CSV and SQL formats and importing ZIP, SQL (batch import), and CSV (single table import) files.
After you create an import or export task, you can go to the Task Center to download the exported files and view the task details and logs.
This topic takes batch import as an example to describe how to import data in ODC.
Prerequisites
The character set is supported. OceanBase Database V2.2.30 and later versions support the following character sets: UTF8, GBK, GB18030, and UTF16. OceanBase Database of a version earlier than V2.2.30 supports only UTF8. Exported files of ODC are encoded by using UTF-8, which cannot be changed.
The connection method is supported. In ODC V2.2.1 and earlier versions, to use the import and export features, you must connect ODC to the target instance through OBProxy. If you directly connect ODC to the target instance, the import and export features are unavailable.
The root@sys account has been configured. In ODC V2.4.1 and later, you can use the sys tenant account to accelerate the import and export tasks. To export objects other than tables and views, you must configure the sys tenant account.
Procedure
Go to the Batch Import panel.
After you enter the connection, click Tool in the navigation bar in the upper part of the page, and then click Import in the drop-down list. The Batch Import panel appears on the right.
Specify Task Name .
The Batch Import panel displays the default task name automatically generated by ODC in the format of connection name_mode name_date. You can change it to a custom task name.
Specify Import Format .
ODC supports importing ZIP, SQL (batch import), and CSV (single table import) files. The SQL and CSV format are specific to the import and export tools of OceanBase Database. For more information about formats, see Export and import formats. In this topic, ZIP Files are used as an example.
Upload the file to be imported.
Click the file pool to go to the file explorer and select the file to be imported. You can also directly drag the file to the file pool to upload it. The format of the uploaded file must be the same as that of the value you specified for Import Format. To upload a file in the ZIP file format, you must first package it into a .zip file. SQL files to be uploaded can be suffixed with .sql. The format of the uploaded file must be the same as that of the value you specified for Import Format in Step 3.
Specify Encoding Standard .
ODC supports the following encoding formats: ASCII , ISO-8859-1 , GB2312 , GBK , GB1803 0 , Unicode (UTF-8) , Unicode (UTF-16) , Unicode (UTF-32) and BIG5 . You need to select the encoding standard from the drop-down list of Encoding Standard .
Specify Import Content .
ODC supports three import modes: Import Schema and Data , Import Data Only , and Import Schema Only .
You must specify this item when you select ZIP for Import Format . Import Schema Only runs the object definition statement in the imported file. After the file is imported, a new object will be created in the database. Import Data Only imports the data in the data file into the specified object. In this topic, the Import Schema and Data mode is used as an example.
Specify Data Format .
You must specify this item when you select ZIP for Import Format . ODC only support importing data in CSV format and SQL format . For more information about formats, see Export and import formats. This topic uses the CSV format as an example.
Specify Import Data Settings .
If you specify ZIP files for Import Format and Import Schema Only for Import Content , this step is not required. You need to specify the following fields:
Clear Data Before Import : If you select this option, the original data in the destination object is cleared during data import.
Batch Commit Quantity : You can click Advanced Settings next to Import Data Settings to specify this field. When you specify this field, you specify the number of rows to be committed during the data import from the source files to the database. When this number is reached, a
COMMITcommand is executed.Skipped Data Types : You can click Advanced Settings next to Import Data Settings to specify this field. Here, you can specify the data types that you want to skip when you import the data. The data types that can be skipped are different in MySQL and Oracle modes. You can select multiple data types.
Specify Import Schema Settings .
You cannot specify this field if you select Import Data Only for Import Content . This field involves operations on schemas that already exist. If you select Skip , the system skips the schema definition statement in the file to be imported and directly imports the data. The original data in the object is retained. If you select Replace , the system executes the schema definition statement in the file to be imported to recreate an object and replace the original one. Data in the original object is cleared.
Specify Task Error Handling Method .
ODC supports two error handling methods: Abort Task and Ignore Error and Continue . You need to specify the handling method in Task Error Handling Method .
Specify Sys Tenant Account .
Select whether to use the sys tenant account to accelerate data import . If you select this option, you need to enter the sys account and the password in the Account and Password fields. Then, you can use privileges of this account to import data, which accelerates the import task.
Create the import task.
Click Import in the lower-right corner of the panel to generate the import task. After the task is generated, the Task Center page appears, where you can view the task status. After the task is completed, you can view the task details and logs.