Batch export and import

Overview

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

After you open a database connection, click Tools in the top navigation bar, and then select Exportor Import from the drop-down list to go to the corresponding settings panel.

The following table describes the requirements for the size of imported and exported data and the retention period of files in ODC.

Batch export

In the Create Export Task panel, you can export multiple database objects to files in the specified format at the same time.

The following example shows how to batch export the employee table and the salary table in the ODC. Procedure:

Step 1: Specify the export content

As shown in the preceding figure, ODC supports three export modes: Export Schema and Data, Export Data Only, and Export Schema Only. Export Schema Only exports the definition statements of the target object. You can select the export content from the Export Content drop-down list.

Step 2: Select a database

Select the database where the object to export resides. The name of the database connection to which the task belongs is also displayed.

Step 3: Specify the export range

You can select Partial Export or Full Export.

• If you select Partial Export, you can select the objects you want to export in the Select Objects pane in the Export Objects section. You can use the search box to search for desired objects.

• If you select Full Export, all objects in the database are exported.

Step 4: Go to the Export Settings panel

Click Next: Export Settings to specify the file export settings and the sys tenant account.

Step 5: Go to the Data File Settings panel

1. Specify Data Format.

   ODC supports exporting data to files in the CSV format and SQL format. If you specify Export Schema Only for Export Content, you can only export data to files in the SQL format. For more information about formats, see Export and import formats.

2. 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. You can select the encoding standard from the File Encoding drop-down list.

3. Specify Data Desensitization.

   By default, desensitization is disabled. You can select and manage the names of the desensitization strategies. In the Manage Desensitization Strategies section, you can add, edit, or delete strategies. For more information, see Manage data desensitization.

4. Specify Maximum Size of a Single File.

   If you select Export Schema Only for Export Content, you do not need to set the maximum size of a single file.

   You can choose the upper limit of the exported single file.If the data file of a single table exceeds the upper limit, the file will be automatically split up. If you specify not to limit the file size, the file will not be split up.

5. Specify Data File Settings.

   • Use Global Snapshot: If you select this option, ODC exports the data in the latest global snapshot of the specified table to ensure global data consistency.

   • Batch Commit Quantity (SQL Format): You can click Advanced next to Export Data Settings to specify this field. If you select SQL Format for Data Format, you can specify the number of rows to be committed during the export. When this number is reached, a COMMIT command is executed.

   • Specify information for the CSV format. If you select CSV Format for Data Format and Export Schema and Data for Export Content, you can specify following information:

     • Include the Column Header: specifies whether to include column headers when data is exported in CSV format. This option is selected by default.

     • Convert the Empty String into a Null Value: This option is selected by default to determine whether to convert empty strings in the table to NULL values when data is exported in CSV format.

     • Field Separator: specifies the separator between fields. The following signs are supported: commas, semicolons, and colons. You can also use a character as the separator.

     • Text Identifier: specifies the identifier for the text content. Single quotation marks (') and double quotation marks (") are supported.

     • Line Break Symbol: specifies the line break symbol. The following characters are supported: \n, \r, and \r\n.

Step 6: Specify schema file settings

• If you select Export Schema Only for Export Content, you can choose whether to select Merge Exported Results into an SQL File. If you select this option, the DDL will be merged and exported as a SQL file.

• Choose whether to select Add DROP TABLE Statement before CREATE TABLE Statement. If you select this option, a DROP statement is added before the corresponding CREATE statement of an object when the schema files of the object are exported.

Step 7: Select an execution method

Select Execute immediately or Timed execution.

Step 8: Set up the sys tenant account

1. Choose whether to select Use sys Tenant Account to Accelerate Export Task. If you select this option, specify Account and Password.

2. After you specify the account and password, you can click the Test Connection button next to the password box to verify whether the account information is valid. By default, the account for the connection settings is automatically filled in. If the connection fails, we recommend that you change the password for this export.

Step 9: Retain Current Configuration

Check Retain Current Configuration to keep the current part of the data file and structure file configuration.

Step 10: Generate an export task

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

Step 11: View the export task

After the export task is generated, the Task Center page automatically appears, where you can view the task information and download exported data and schema files. For more information, see Export tasks.

Batch import

In the Create Import Task panel, you can choose to import schemas and data to multiple database objects at the same time.

Take the import of ZIP files in the ODC as an example. The file contains an employee table and a salary table. Procedure:

Step 1: Specify the import format

As shown in the preceding figure, ODC supports batch import of CSV Files, SQL Files, and ZIP Files. You can select the desired file format from the Import Format drop-down list. For more information about formats, see Export and import formats.

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

Step 3: Specify the 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. You can select the encoding standard from the File Encoding drop-down list.

Step 4: Go to the Import Settings panel

Click Next: Import Settings to specify file import settings and the sys tenant account.

Step 5: Specify Import Content

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. You can select the import content from the Import Content drop-down list.

Step 6: Select a database

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

Step 7: Specify Import Data Settings

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

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

Step 9: 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.

Step 10: Set up the sys tenant account

1. Choose whether to select Use sys Tenant Account to Accelerate Export Task. If you select this option, specify Account and Password.

2. After you specify the account and password, you can click the Test Connection button next to the password box to verify whether the account information is valid. By default, the account for the connection settings is automatically filled in. If the connection fails, we recommend that you change the password for this export.

   Notice

   • The account and password of the sys tenant are the account and password of a user in a cluster tenant. Do not enter @sys#cluster in the Account field. Example: account odc, password ******.
   • If the account of the sys tenant is not configured, indexes are not imported or exported.

Step 11: Retain Current Configuration

Check Retain Current Configuration to keep the current part of the data file and structure file configuration.

Step 12: Generate an 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.

Step 13: View the import task

After the task is generated, the Task Center page automatically appears, where you can view the task information. For more information, see Import tasks.