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 |
|
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
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:
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
If you select Export Schema Only for Export Content, you do not need to configure the export data settings.
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.
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.
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.
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.
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 correspondingCREATE
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
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
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.
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
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.