Single table export and import

Overview

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 Objects in the left-side navigation pane to view the objects. Right-click the name of the target object in the object list, or click Tools in the top navigation bar and then select Export or Import from the drop-down list to export or import the data and schema in the current table.

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

Single table export

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:

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

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.

   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. The following figure shows an example.
     

   • If the account of the sys tenant is not configured, comments and indexes are not imported or exported.

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.

Single table import

Take the import of a ZIP file (6000219_import_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.

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.

Note
You can upload a single table or multiple tables.

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

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: 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. The following figure shows an example.
     

   • If the account of the sys tenant is not configured, comments and indexes are not imported or exported.

Step 12: Retain Current Configuration

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

Step 13: 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 14: 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.