Import schema and data

This topic provides information on importing the schema and data of database objects.

Background information

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

You can create an import task to import only schemas of database objects, only data of database objects, or both.

• Import both schema and data: Import both the definition statements and data of database objects.

• Import data only: Import only data of table or view objects.

• Import schema only: Import only definition statements of database objects.

Formats of imported files

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

The ZIP file here is a compressed package that is suffixed with .zip. When you create an import task, if you select ZIP Files as the Import Content, you must specify whether Data Format in the zip file is CSV Format or SQL Format.

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.

• The status of a task, such as Succeeded, Failed, and Canceled, does not affect the lifecycle of the task files. Tasks are permanently retained. Task-related files are retained for 14 days by default.

• Instances earlier than OceanBase Database V2.2.30 support only UTF8.

• In ODC V2.2.1 and earlier, to use the import feature, you must connect to the target instance by using ODP. ODC does not support the import feature for direct connections.

• In ODC V2.4.1 and later versions, you can configure the sys tenant account to improve the import speed.

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 Import Task.

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

   Note

   • ODC supports importing ZIP, SQL, and CSV files.
   • ODC allows you to skip the column header in the first row when you import a CSV file.
   • ODC allows you to import a single table or multiple tables, you can also skip existing schemas when you import a ZIP file.

3. Upload the file to be imported.

   Click the file pool to go to the file explorer and select the files to be imported. You can also directly drag the files to the file pool to upload them. The format of the uploaded file must be the same as that of the value you specified for Import Format. Upload files suffixed with .zip if you have selected ZIP Files for Import Format, upload files suffixed with .sql if you have selected SQL Files for Import Format, and upload files suffixed with .csv if you have selected CSV Files for Import Format.

4. Select the desired file encoding.

   ODC supports the following file encodings: 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 specify file import settings and the sys tenant account.

6. Specify the content to be imported.

   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 data source name of the target database of the task is also displayed.

8. Specify the data to be imported.

   If you select ZIP Files for Import Format and Import Data Only or Import Schema and Data for Import Content, this option must be specified. 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 imported 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 schema to be imported.

   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 Task Error Handling Method and the execution method.

    • 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. Set the sys tenant account.

    1. Select Use sys Tenant Account to Accelerate Import Task and specify Account and Password.

    2. After you enter the account and password, click Test Connection next to the password field to test whether the account information is correct. By default, the account set for the connection is automatically filled in. If the connection fails, we recommend that you change the password for this import task.

       Notice

       • The account and password of the sys tenant are the account and password of a user in the cluster. Do not enter @sys#cluster in the Account field. For example, the account of the root@sys user is root.
       • If the sys tenant account is not configured, the import and export speed may be affected.

12. Retain the current configuration.

    After specifying all information, select Retain Current Configuration in the lower-left corner of the panel to save the configuration of the current data files and schema files.

13. 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.

14. View the import task.

    After the task is generated, the Task Center tab automatically appears You can view the task information on this tab.

View an import task

Task list

After an import task is generated, the Tasks panel appears. In the Import list, you can view the approval status, basic information, and logs of the task.

The following table describes the columns in the Import task list.

Column	Description
No.	The ID of the import task.
Type	The type of the task specified when you created the task.
Ticket Description	The description that you entered when you initiated the task.
Current Reviewer	The reviewer of the task. Click the icon to search for and filter the operator.
Created By	The user who created the import task. You can click the search icon to search for tasks created by a specific creator.
The time when the task was created.	The date and time when the task was generated.
Status	The current status of the task. ODC provides different management actions for tasks in different states.
Actions	Click View to go to the Task Details page to view the task information and task logs.

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 data