This topic describes how to export data in batches in OceanBase Developer Center (ODC).
Background information
ODC provides the batch import and export features and the table import and export features, to facilitate database data maintenance for database developers. The import and export features allow you to import data or schema to or export them from database objects. ODC supports the CSV format and SQL format for data export, and supports ZIP files, SQL files, and CSV files for data import. SQL files can be imported in batches, and CSV files are supported in single table import.
After you create an import or export task, you can go to the Tasks page to download the exported file and view the task details and logs.
This topic takes batch export as an example to describe how to export data in ODC.
Prerequisites
The character set is supported. OceanBase Database V2.2.30 and later versions support the following character sets: UTF-8, GBK, GB18030, and UTF-16. OceanBase Database of a version earlier than V2.2.30 supports only UTF-8. Exported files of ODC are encoded by using UTF-8, which cannot be changed.
The connection method is supported. In ODC V2.2.1 and earlier versions, to use the import and export features, you must connect ODC to the target instance through OBProxy. If you directly connect ODC to the target instance, the import and export features are unavailable.
The root@sys account has been configured. In ODC V2.4.1 and later versions, you can optionally configure a sys tenant account to accelerate the execution of export and import tasks. If you are exporting objects other than tables and views, the sys tenant account must be configured.
Procedure
Go to the Export Settings panel.
After you enter the connection, click Tool in the navigation bar on the top of the page, and click Export in the drop-down list. Then, the Export Settings panel appears on the right.
Specify Task Name .
The Export Settings panel displays the default task name automatically generated by ODC in the format of connection name_mode name_date. You can change it to a custom 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 and generates an SQL file suffixed with .sql. Export Data Only exports the actual data stored in the object. The file format of exported data varies depending on the value of Export Format . In this example, the Export Content is Export Schema and Data .
Specify Data Format .
ODC supports exporting data to the CSV Format or SQL Format . If the Export Content is Export Schema Only , you do not need to specify the data format. For more information about formats, see Export and import formats. In this example, the Data Format is CSV Format .
Specify Encoding Standard .
ODC supports the following encoding standards: ASCII , ISO-8859-1 , GB2312 , GBK , GB1803 0 , Unicode (UTF-8) , Unicode (UTF-16) , Unicode (UTF-32) , and BIG5 . You need to select an encoding standard from the Encoding Standard drop-down list.
Specify Export Objects .
In the Export Objects table, select the objects you want to export. The table displays information on the Object Name and Object Type . You can filter objects by Object Type or search for target objects in the search box. You can select a batch of or all objects in the table. You can also filter objects by object type. The maximum size of data exported in one batch in ODC cannot exceed 250 MB before compression. Therefore, if the size of the data in the selected objects exceeds 250 MB, the data in the generated data file will be incomplete.
Specify Export Data Settings .
If the Export Content is Export Schema Only, you do not need to configure data export settings. The following options are included:
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 : You can click Advanced Settings next to Export Data Settings to specify this option. If the Data Format is SQL format, you can specify this option to add a
COMMITcommand to the exported file after a certain number of rows is exported.Skipped Data Types : You can click Advanced Settings next to Export Data Settings to specify this option. This option decides the data types to be skipped when exporting data. The data types that can be skipped are different in MySQL and Oracle modes. You can select multiple data types.
Specify information for the CSV format.
If you select the CSV format for Data Format in Step 4, you need to specify the information for the CSV format. Click Advanced Settings next to Export Data Settings to specify the following information:
Include Column Header : This option is selected by default. It determines whether to include column headers when the export format is CSV format.
Convert Empty String to Null Value : This option is selected by default. It specifies whether to convert empty strings in the table to
NULLvalues when the export format is CSV format.Field Delimiter : This option specifies the delimiter between fields. The following delimiter characters are supported: commas ( , ), Semicolons ( ; ), and colons ( : ). You can also specify another deliminter that is one character long.
Text Identifier : This option specifies the identifier for text content. Single quotation marks ( ' ) and double quotation marks ( " ) are supported.
Line Break Character : The option specifies the line break character. The following characters are supported: \n , \r , and \r\n .
Specify Export Schema Settings .
This option specifies whether to Add DROP Statement . If you select this option, a
DROPstatement is added before the correspondingCREATEstatement for the object.Configure Sys Tenant Account .
You can specify whether to use the sys tenant account to accelerate data export . If you select this option, you need to enter the sys account and password in Account and Password fields that appear. After you specify this option, data export will be performed with the permissions of the sys tenant, which accelerates the execution of the export task. If you are exporting objects other than tables and views, the sys tenant account must be configured. Notice
OceanBase Database of a version earlier than V2.2.30 supports only UTF-8.
In ODC V2.2.1 and earlier versions, to use the import and export features, you must connect ODC to the target instance through OBProxy. If you directly connect ODC to the target instance, the import and export features are unavailable.
In ODC V2.4.1 and later versions, you can optionally configure a sys tenant account to accelerate the execution of export and import tasks. If you are exporting objects other than tables and views, the sys tenant account must be configured.
Generate an export task.
After you select all the objects that you want to export, you can click Export in the lower-right corner of the panel to generate an export task. After the task is generated, the Tasks page automatically appears, where you can view the running status of the export task. After the task is completed, you can download the exported data file and view the task details and logs.