All Products
Search
Document Center

:Batch export and import

更新時間:Apr 17, 2023

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

After you open a database connection, click Tool in the top navigation bar, and then select Export or 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 period of file retention in ODC.

Requirement

Description

Limits on the size of imported and exported data in ODC

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

  • The maximum size of data exported in one batch cannot exceed 2 GB before compression. To export data of a larger size, use the data export tool OBDUMPER.

File retention period on the server

  • The status of a task, such as Succeeded, Failed, and Cancelled, does not affect the lifecycle of the file.

  • A task is retained only for 24 hours. When the task is cleared, the related files are cleared.

  • You can click Delete in the Actions column of a task to delete the task. When you delete a task, the related files are also deleted.

Batch export

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

batch export 1 EN

Procedure

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.

Step 5: Go to the Data File Settings panel

image

Note

If you select Export Schema Only for Export Content, you do not need to configure the export data settings.

  1. Specify Data Format.

    ODC currently supports exporting data to files in CSV format and SQL format. If you specify Export Schema Only for Export Content, you can only export data to files in 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.

    If you select Export Schema Only for Export Content, you do not need to configure the export data settings. You need to specify the following fields:

    • 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: Retain Current Configuration

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

Step 9: 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 10: View the export task

image

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.

batch import 1 EN

Procedure

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

Note
  • If you select CSV Files for Import Format, you can skip the values of the column header in the first row.

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

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

image

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 12: Generate an import task

Step 10: Retain Current Configuration

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

Step 11: 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 12: View the import task

image

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