All Products
Search
Document Center

:Single table export and import

Last Updated:Apr 17, 2023

OceanBase Developer Center (ODC) allows you to import and export data and schemas from a specified table.

After you open a database connection, you can click Table in the left-side navigation pane or Tool to view the tables. Right-click the target table name in the table list or click Tool in the top navigation bar, and then click Single Table Export or Single Table Import in the list that appears to export or import data and schemas in the current table.

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 Canceled, does not affect the lifecycle of the task files.

Tasks are permanently retained. Task-related files are retained for 14 days by default.

Single table export

Image 564

Procedure

The procedure for exporting a single table is basically the same as the that of Batch export. The following example shows how to export an employee table in ODC. Procedure:

image

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

By default, the current table is exported. 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 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: 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.

Single table import

image

Procedure

Take the import of a CSV file in ODC as an example. The file contains an employee table. Procedure:

Step 1: Specify Import Format

As shown in the preceding figure, ODC supports CSV Files and ZIP Files for single table import. 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, 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: Import the target table

By default, the current table is imported. To import multiple tables, select the tables to be imported.

Step 8: 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 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 and Oracle modes. You can select multiple data types.

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

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.