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

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

   

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

   1. Specify the file format.

      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.

   2. Upload the file to be imported.

      Click the file pool to go to the file resource manager and select the file to import, or drag the file to the file pool to upload it. The file format must be consistent with the selected import file format. A ZIP-compressed file must have the .zip extension, an SQL file must have the .sql extension, and a CSV file must have the .csv extension.

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

3. Click Next: Import Settings to configure the import file and click Submit.

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

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

   3. 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 compatible and Oracle compatible modes. You can select multiple data types.

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

   5. 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 Immediate Execution or Scheduled Execution.

   6. Specify a description.

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

4. After the task is generated, you can view the task information in the Import list of the Tickets page.

View an import task

1. Go to Tickets > Data Import, click the name of the target task in the list to view the task details. You can also click the ··· in the Actions column to perform more operations.

   • Execute: When the task execution mode is manual, you can execute the task.
   • Terminate: When the task execution mode is periodic or scheduled, you can terminate the task. You can also select the target tasks and click Batch Terminate at the top to terminate multiple tasks.
   • Edit: When the task status is disabled, you can edit the task.
   • Delete: When the task status is completed or terminated, you can delete the task.
   • View: View the task details.
   • Clone: Clone the task execution content to generate a new task with the same configuration.
   • Share: Copy the task access address.

2. In the task details panel that appears, the Basic Information tab is displayed by default. You can view the task type, database, variable configuration, and execution mode.

   Note

   You can configure whether to allow query results to be queried and downloaded in the system settings. For more information, see System settings.

3. Click the Workflow tab to view the task status, approval status, execution status, and execution result.

4. Click the Logs tab to view all logs and alert logs of the task.

   Item	Description
   All Logs	All logs display the INFO, ERROR, and WARN logs of the task. You can click the Find, Download, and Copy buttons to search for, download, or copy the logs.
   Alert Logs	Alert logs display the ERROR and WARN logs of the task. When the task fails, you can view the error information in the alert logs. You can click the Find, Download, and Copy buttons to search for, download, or copy the alert logs.

References

• Export schemas and data