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 to view the tables. Right-click the target table name in the table list, 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 OBLOADER. * The maximum size of files exported in one batch cannot exceed 2 GB before compression. To export data of a larger size, use OBDUMPER. |
| File retention period on the server | * The task status, such as Succeeded, Failed, and Cancelled, does not affect the lifecycle of the file. * A task is retained for 24 hours. When the task is cleared, files associated with the task are also cleared. * When you delete a task by clicking Delete in the Actions column of the task, the files associated with the task are also deleted. |
Single table export
The following figure shows the single table export page, which is basically the same as the batch export page.
Procedure:
Specify Task Name .
In the Single Table Export panel, ODC automatically generates a task name in Task Name field in the format of connection name_mode name_date. You can modify the task name.
Specify Export Content .
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 need to select the export content in the drop-down list of Export Content .
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.
Specify Encoding Standard .
ODC supports the following encoding formats: ASCII , ISO-8859-1 , GB2312 , GBK , GB18030 , Unicode (UTF-8) , Unicode (UTF-16) , Unicode (UTF-32) and BIG5 . You need to select the encoding standard from the drop-down list of Encoding Standard .
Specify Export Objects .
The Export Objects tab displays the Object Name and Object Type information of the current table.
Specify Export Data Settings .
If you specify Export Schema Only for Export Content , you do not need to complete 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.
Single Table Commit Quantity in SQL format: You can click Advanced next to Export Data Settings to specify this field. If you specify the SQL format for Data Format , you can specify the number of rows to be committed during the export. When this number is reached, a
COMMITcommand is executed.Skipped Data Types : You can click Advanced Settings next to Export Data Settings to specify this field. Here, you can specify the data types that you want to skip when you export the data. The data types that can be skipped are different in MySQL and Oracle modes. You can select multiple data types.
Specify information for CSV format. If you specify the CSV format for Data Format and Export Data Only or Export Schema and Data for Export Content , you can click Advanced Settings next to Export Data Settings to specify following fields.
Include Column Header : This option is selected by default to determine whether to include column headers when the export data format is CSV format.
Convert the Empty Character 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 Delimiter : This option specifies the delimiter between fields. The following signs are supported: commas ( , ), semicolons ( ; ), and colons ( : ). You can also use a character as the delimiter.
Text Identifier : This option specifies the identifier for the text content. Single quotation marks ( ' ) and double quotation marks ( " ) are supported.
Line Break Sign : This option specifies the sign for the line break. The following characters are supported: \n , \r , and \r\n .
Specify Export Schema Settings .
Select whether to add the DROP TABLE statement . If you select this option, a
DROPstatement is added to the correspondingCREATEstatement in the export file.Configure Sys Tenant Account .
Select whether to use the sys tenant account to accelerate data export . If you select this option, you need to enter the account and password of the sys tenant in the Account and Password fields. Then, you can use permissions of this account to export data, which accelerates the export task. Notice
The account and password of a user in the sys tenant are required. Do not enter @sys#cluster in the Account field. The following figure shows an example.
To export objects other than tables and views, you must specify the sys tenant account.
Otherwise, annotations and indexes are not imported or exported.
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.
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 files. For more information, see Export tasks.
Single table import
The following figure shows the single table import page, which is basically the same as the batch import page.
Procedure :
- Specify Task Name . In the Single Table Import panel, ODC automatically generates a task name in Task Name field in the format of connection name_mode name_date. You can modify the task name.
- Specify Import Format . ODC currently supports CSV files and ZIP files for single table import. You need to select the data format in the drop-down list of Import Format . For more information about formats, see Export and import formats.
- 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. To upload a file in the ZIP file format, you must first package it into a .zip file. SQL files to be uploaded can be suffixed with .sql.
- Specify Encoding Standard . ODC supports the following encoding formats: ASCII , ISO-8859-1 , GB2312 , GBK , GB18030 , Unicode (UTF-8) , Unicode (UTF-16) , Unicode (UTF-32) and BIG5 . You need to select the encoding standard from the drop-down list of Encoding Standard .
- Specify Import Content . You must specify this item when you select ZIP for Import Format . ODC currently supports Import Data Only , Import Schema Only , and Import Schema and Data . Import Schema Only imports the definition statements of the target object. You need to select the import content in the drop-down list of Import Content .
- Specify Data Format . You must specify this field when you select ZIP Files for Import Format . ODC only support importing data in CSV format and SQL format . For more information about formats, see Export and import formats. If you specify Import Schema Only for Import Content or SQL files for Import Format , you do not need to select the data format.
- Specify Import Data Settings . If you specify ZIP files for Import Format and Import Schema Only for Import Content , this step is not required. 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.
- Single Table Commit Quantity : You can click Advanced Settings next to Import Data Settings to specify this field. When you specify this field, you specify the number of rows to be committed during the data import from the source files to the database. When this number is reached, a
COMMITcommand is executed. - Skipped Data Types : You can click Advanced Settings next to Import Data Settings to specify this field. Here, you can specify the data types that you want to skip when you import the data. The data types that can be skipped are different in MySQL and Oracle modes. You can select multiple data types.
- Specify information for CSV format. If you specify CSV files for Import Format , click Advanced Settings next to Import Data Settings to specify the following fields.
- Include the Column Header : This option is selected by default to determine whether to include column headers when data is exported in CSV format.
- Convert the Empty Character 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 : This option specifies the delimiter between fields. The following signs are supported: commas ( , ), semicolons ( ; ), and colons ( : ). You can also use a character as the delimiter.
- Text Identifier : This option specifies the identifier for the text content. Single quotation marks ( ' ) and double quotation marks ( " ) are supported.
- Line Break Symbol : This option specifies the symbol for the line break. The following signs are supported: \n , \r , and \r\n .
- Specify Task Error Handling Method . ODC supports two error handling methods: Abort Task and Ignore Error and Continue . You need to specify the handling method in Task Error Handling Method.
- Configure sys Tenant Account Settings . Select whether to use the sys tenant account to accelerate data import . If you select this option, you need to enter the account and password of the sys tenant in the Account and Password fields. Then, you can use permissions of this account to import data, which accelerates the import task. Notice
- The account and password of a user in the sys tenant are required. Do not enter @sys#cluster in the Account field. The following figure shows an example.
- Otherwise, annotations and indexes are not imported or exported.
- The account and password of a user in the sys tenant are required. Do not enter @sys#cluster in the Account field. The following figure shows an example.
- Create the import task. After you specify all preceding information, you can click Import in the lower-right corner of the panel to create the import task.
- 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.