Import schemas 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

Import schemas and data of an OceanBase database

Example: Import the schemas and data of the employee and salary tables on the local disk to the odc_test database.

1. On the Tickets tab, choose Data Import > Create New.

   

2. In the task panel that appears, 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.
   • At present, ODC supports three import modes: Import Data Only, Import Schema Only, and Import Data and Schema. The Import Schema Only mode specifies to import the definition statement of the target object.

7. Select the 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 the MySQL compatible and Oracle compatible 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 Now or Scheduled 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.

Import schemas and data of a MySQL database

Example: Import the schemas and data of the employee and salary tables on the local disk to the odc_test database.

1. On the Tickets tab, choose Data Import > Create New.

   

2. In the task panel that appears, select the format of the file to be imported.

   

   Note

   • 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.
   • At present, ODC supports three import modes: Import Data Only, Import Schema Only, and Import Data and Schema. The Import Schema Only mode specifies to import the definition statement of the target object.

7. Select the 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.

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 Now or Scheduled 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.

Import schemas and data of an Oracle database

Example: Import the schemas and data of the employee and student tables on the local disk to the ODC_TEST database.

1. On the Tickets tab, choose Data Import > Create New.

2. In the task panel that appears, select the format of the file to be imported.

   Note

   • 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.
   • At present, ODC supports three import modes: Import Data Only, Import Schema Only, and Import Data and Schema. The Import Schema Only mode specifies to import the definition statement of the target object.

7. Select the 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 Types (Optional): You can specify to skip the BLOB and CLOB types during data import.

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 Now or Scheduled 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 Tickets 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. In the import task list shown in the preceding figure, click View in the Actions column of a task to view information about the task.

2. In the Task Details panel, click the Task Information tab to view the basic task information, file import information, and object information.

   Item	Description
   Basic task information	The execution status, ID, database, type, risk level, execution method, creator, and creation time of the task are displayed.
   File import information	The import format, file to be imported, file encoding standard, data format, and error handling method specified when the import task was created are displayed.
   Object information	The information about the import objects is displayed in the following columns: Object Name, Object Type, Actual Processing Quantity, and Data Processing Status.

Task process

In the Task Details panel of the target task, click the Task Process tab to view the initiation, approval, and execution information of the task.

Task logs

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

In addition, the toolbar provides the following icons:

References

• Export schemas and data