ApsaraDB for OceanBase:Import schema and data

Background information

OceanBase Developer Center (ODC) allows you to import schemas and data of database objects in batches.

You can create import tasks in ODC to import both schemas and data, only data, or only schemas of database objects.

• Import schemas and data: You can import both the definition statements and data of database objects.

• Import only data: You can import only the data of table or view objects.

• Import only schemas: You can import only definition statements of database objects.

Import formats

ODC supports importing ZIP (batch import and single-table import), SQL (batch import), and CSV (single-table import) files.

A ZIP file here is a compressed package that is suffixed with .zip. In the Create Import Task panel, select ZIP Files for Import Format, and then select CSV Format or SQL Format for Data Format, which specifies the data format of the package.

SQL files supported for batch data import are suffixed with .sql and contain statements that can be directly executed in the database. These statements include SELECT, INSERT, UPDATE, DELETE, CREATE, and DROP.

CSV files supported for single table import are data files that are suffixed with .csv or .txt. These files support custom delimiters.

Considerations

• The maximum size of files imported in one batch in web ODC cannot exceed 2 GB after compression. To import data of a larger size, use the data import tool OBLOADER.

• At most five concurrent import tasks are supported. Subsequent tasks need to wait in the queue.

• By default, files related to import tasks are retained for 14 days.

• In OceanBase V4.0.0 and earlier versions, configuring the sys tenant account and password in the data source can improve import speed. ODC can calculate the optimal parallel import strategy based on partition locations, and OceanBase V4.0.0 and earlier versions require querying and determining partition locations through the sys tenant.

• For OceanBase versions after V4.0.0, it is not necessary to configure the sys tenant account and password in the data source. ODC supports querying and determining partition locations through the views of the business tenant.

Create an import task

Assume that you want to import the schemas and data of the employee and salary tables from your local disk to the odc_test database.

1. On the Ticket tab, choose Import > Create New.

   

2. In the Create Import Task panel, select the format of the file to be imported.

   

   Note

   • In ODC V4.2.3 and later, you can import schemas and data of a MySQL database.

   • At present, ODC supports importing CSV, SQL, and ZIP files in batches.

   • If you select CSV Files for Import Format, header fields of the first row can be skipped during the import.

   • If you select ZIP Files for Import Format, you can import a single table or multiple tables and skip existing schemas.

3. 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 the value you specified for Import Format. Upload a file suffixed with .zip if you have selected ZIP Files for Import Format, upload a file suffixed with .sql if you have selected SQL Files for Import Format, and upload a file suffixed with .csv if you have selected CSV Files for Import Format.

4. Specify File Encoding.

   ODC supports the following encoding standards: ASCII, ISO-8859-1, GB2312, GBK, GB18030, Unicode (UTF-8), Unicode (UTF-16), Unicode (UTF-32), and BIG5.

5. Go to the Import Settings panel.

   Click Next: Import Settings to complete file import settings.

6. Specify Import Content.

   

   Note

   • You must specify this parameter when you select ZIP Files for Import Format.

   • ODC supports Import Data Only, Import Schema Only, and Import Schema and Data. Import Schema Only imports the definition statements of the target object.

7. Select a database.

   Select the database to which the data object is to be imported. The name of the data source for the database to which the task belongs is also displayed.

8. Complete the settings in the Import Data Settings section.

   If you select ZIP Files for Import Format and Import Data Only or Import Schema and Data for Import Content, you must configure data import settings. 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 next to Import Data Settings to specify this field. After you specify this field, a COMMIT statement is executed when the number of exported data rows reaches the specified quantity.

   • Skipped Data Type(Optional): You can specify the data types to be skipped during data import. The data types that can be skipped are different in MySQL and Oracle modes. You can select multiple data types.

9. Specify the Import Schema Settings field.

   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 re-create an object and replace the original one. Data in the original object is cleared.

10. Specify the error handling method and execution mode.

    • ODC supports two error handling methods: Abort Task and Ignore Error and Continue. You need to select the handling method as required.

    • Select Execute Immediately or Timed Execution.

11. Choose whether to retain the current settings.

    You can select Retain Current Configuration in the lower-left corner to save the current data file and schema file settings.

12. Generate the import task.

    After you specify all preceding information, you can click Submit in the lower-right corner of the panel to create an import task.

13. View the import task.

    After the task is generated, the Tickets tab automatically appears, where you can view the task information.

View an import task

Task list

After an import task is generated, the Ticket tab automatically appears, where you can view the status, information, and logs of the task.

The following table describes the information displayed in the import task list.

Task information

1. As shown in the preceding figure, click View in the Import task list.

2. In the Task Details panel, click the Task Information tab to view the basic information of the task, imported files, and imported objects.

   Parameter	Description
   Basic task information	Displays information in fields such as Execution Status, Task ID, Database, Task Type, Risk Level, Execution Method, Created By, and Created At.
   Import file information	Displays information in fields such as Import File Format, Import File, File Encoding, Data Format, and Task Error Handling.
   Import object information	Displays information about the objects upon data import in fields such as Object Name, Object Type, Processed Quantity, and Data Processing Status.

Task process

In the Task Details panel, click the Task Process tab to view the status information, such as Initiated Task, Approval, and Execution.

Task logs

In the Task Details panel, click the Task Logs tab to view all logs and alert logs of the task.

In addition, the toolbar provides the following buttons:

References

Export schema and data