ApsaraDB for OceanBase:Export schema and data

Background information

OceanBase Developer Center (ODC) allows you to export schemas and data of database objects in batches.

You can create an export task to export only schemas of database objects, only data of database objects, or both.

• Export both schema and data: Export both the definition statements and data of database objects.

• Export data only: Export only data of table or view objects.

• Export schema only: Export only definition statements of database objects.

Export format

ODC exports data in a .zip file, which contains the metadata file MANIFEST.bin, the schema definition files of the exported objects, and the data files.

When you create an export task, if you set Data Format to SQL Format, the .zip file contains schema definition files named in the format of object name-schema.sql and data files named in the format of object name.sql. The object names are the same as those in the database.

If you set Data Format to CSV Format, the .zip file contains schema definition files named in the format of object name-schema.sql and data files named in the format of object name.csv. The object names are the same as those in the database.

Considerations

• In web ODC, the maximum size of files exported in one batch cannot exceed 2GB before compression. To export data of a larger size, use OceanBase OBDUMPER.

• At most five concurrent export tasks are supported. Subsequent tasks need to wait in the queue.

• By default, files related to export tasks are retained for 14 days.

• In OceanBase V4.0.0 and earlier versions, configuring the sys tenant account and password in the data source can improve export speed. ODC can calculate the optimal parallel export strategy based on partition locations, and OceanBase V4.0.0 and earlier versions require querying and determining partition locations through the sys tenant.

• For OceanBase versions after V4.0.0, it is not necessary to configure the sys tenant account and password in the data source. ODC supports querying and determining partition locations through the views of the business tenant.

Create an export task

Assume that you want to batch export the schemas and data of the employee and salary stables from the odc_test database to a local disk.

1. On the Ticket tab, choose Export > Create New.

   

2. In the Create Export Task panel, select the content to be exported.

   

3. Select a database.

   Select the database where the object to export resides. The data source name of the target database of the task is also displayed.

   Important

   OceanBase Database does not support the export feature for built-in databases in MySQL mode.

4. Select the export range.

   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 field to search for desired objects.

   • If you select Full Export, all objects in the database are exported.

5. Go to the Export Settings panel.

   Click Next: Export Settings to specify the file export settings.

6. Configure the data files.

   

   Note

   If you set Export Content to Export Schema Only, you do not need to configure a data file.

   Parameter	Description
   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.
   File Encoding	ODC supports the following file encodings: ASCII, ISO-8859-1, GB2312, GBK, GB18030, Unicode (UTF-8), Unicode (UTF-16), Unicode (UTF-32), and BIG5.
   Maximum Size of a Single File	If you set Export Content to Export Schema Only, you do not need to configure this parameter. You can select the maximum size of a single-table data file. If the size of a file exceeds the maximum size, the file is automatically split into smaller ones for the export. If you do not specify the maximum size, the files are not split into small ones.
   CSV Settings	If you select the CSV format for the export and set Export Content to Export Schema and Data, you can specify the following information: Include Column Header: specifies whether to include column headers when data is exported in CSV format. This option is selected by default. Convert Empty Character String into 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.
   Batch Commit Quantity (SQL Format)	When you export data in the SQL format, after you specify this field, a COMMIT statement is executed when the number of exported data rows reaches the specified quantity.
   Use Global Snapshot	After you select this option, ODC will export the data in the globally latest snapshot for the specified table. You can select this option to ensure global data consistency.

7. Configure schema files.

   • If you set Export Content to Export Schema Only, you can select whether to merge the export results into one SQL file. If you select this option, the DDL statements are merged into and exported as an 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.

8. Specify the task execution method.

   Select Execute Immediately or Timed Execution.

9. Retain the current configuration.

   After specifying all information, select Retain Current Configuration in the lower-left corner of the panel to save the configuration of the current data files and schema files.

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.

View an export task

Task list

After an export task is generated, the Tickets tab automatically appears. You can view the approval status and basic information of the task in the task list, and download the exported data and schema files.

The following table describes the columns in the export task list.

Task information

1. As shown in the preceding figure, click View in the Export task list.

2. In the Task Details panel, click the Task Information tab to view the basic information of the task, export files, and export objects.

   

   Parameter	Description
   Basic task information	The execution status, ID, database, type, risk level, execution method, creator, and creation time of the task are displayed.
   Export file information	The export content, data format, file encoding standard, data export settings, schema export settings, and error handling method are displayed.
   Export object information	Displays the information about the objects during data export, including Object Name, Object Type, Schema Processing Status, Processed Quantity, and Data Processing Status.

Task process

In the Task Details panel of the target task, click the Task Process tab to view the initiation, approval, and execution information of the task.

Task logs

In the Task Details panel of the target task, click the Task Logs tab to view all logs and alert logs of the task.

In addition, the toolbar provides the following buttons:

Export the generated file

In the Task Details panel of the target task, click Download in the lower-right corner to export the generated file to your local disk.