Export structures and data

Background information

OceanBase Database Connector (ODC) supports batch export of database object structures and data.

You can use ODC export tasks to export the structures and data of database objects, only the data, or only the structures.

• Export structure and data: Exports the definition statements and data of database objects.

• Export data only: Exports only the data of tables or views.

• Export structure only: Exports only the definition statements of database objects.

Export file format

The ODC export tool exports data to a ZIP file. The file contains the MANIFEST.bin metadata file, and the structure definition and data files for the exported objects in the schema.

When you create an export task, if you set Data Format to SQL Format, the structure definition file in the package is an SQL file named object_name-schema.sql. The data file is an SQL file named object_name.sql. The object name is the same as the actual object name in the database.

If you set Data Format to CSV Format, the structure definition file in the package is an SQL file named object_name-schema.sql. The data file is a CSV file named object_name.csv. The object name is the same as the actual object name in the database.

Notes

• The web version of ODC limits the size of an export to 2 GB of uncompressed data. Data that exceeds this limit cannot be exported. To export large amounts of data, use the OBDUMPER data export tool.

• A maximum of five export tasks can run in parallel. Subsequent tasks wait in a queue.

• Files related to export tasks are retained for 14 days by default.

• For data sources in OceanBase V4.0.0 or earlier, you can increase the export speed by configuring the sys tenant account and password. This allows ODC to calculate the optimal parallel export policy based on partition locations. For objects other than tables and views, OceanBase V4.0.0 and earlier versions require the sys tenant to query partition locations.

• For data sources in OceanBase versions later than V4.0.0, you do not need to configure the sys tenant account and password. ODC can query partition locations through the views of the business tenant.

Create an export task

Example: Batch export the structures and data of the employee and salary tables in the odc_test database to a local disk.

Item	Example value
Data source	mysql410
Database name	odc_test
Table name	employee, salary

1. On the Tickets tab, click Export > Create Export.

2. In the Export panel that appears, select the content to export.

3. Select the database.

   Select the database that contains the objects to export. The name of the data source for the target database is also displayed.

   Important

   In OceanBase MySQL mode, the export feature is not supported for built-in databases.

4. Select the export scope.

   You can select Partial Export or Full Database Export.

   • Partial Export: In the Select Objects window for Export Objects, select the objects to export. You can use the search box to search for objects directly.

   • Full Database Export: Exports all objects in the current database.

     Note

     ODC V4.2.2 and later versions support exporting type objects.

5. Go to the export settings panel.

   Click Next: Export Settings to configure the exported files.

6. Configure data files.

   Note

   You do not need to configure data files when Export Content is set to Export Structure Only.

   Setting	Description
   Data Format	ODC supports exporting data in CSV Format and SQL Format. When Export Content is set to Export Structure Only, only SQL Format is supported.
   File Encoding	ODC supports encoding formats such as ASCII, ISO-8859-1, GB2312, GBK, GB18030, Unicode (UTF-8), Unicode (UTF-16), Unicode (UTF-32), and BIG5.
   Maximum Size of a Single File	You do not need to set a maximum file size when Export Content is set to Export Structure Only. You can set a maximum size for a single table data file. If the file size exceeds the limit, the file is automatically split. If you select No Limit, the file is not split.
   CSV Settings	When you select CSV format and set Export Content to Export Structure and Data, you can specify the following information: Include Column Header: This option is checked by default. Select whether to include column headers when exporting data in CSV format. Convert Empty Strings to Nulls: This option is checked by default. Select whether to convert empty strings in the table to the null value \N when exporting data in CSV format. Field Separator: Set the separator between fields. You can select , (comma), ; (semicolon), or : (colon) as the field separator. You can also use a custom single-character separator. Text Qualifier: Set the qualifier for text content. You can select ' (single quotation mark) or " (double quotation mark) as the text qualifier. Line Feed: Set the line feed character. You can select \n, \r, or \r\n as the line feed character.
   Batch Commit Size (SQL Format)	When you export data in SQL format, you can specify a batch commit size. A COMMIT command is added to the export file after the specified number of data records are exported.
   Use Global Snapshot	If you check this option, ODC exports data from the latest snapshot point of the specified tables in the global scope. This ensures global data consistency.

7. Configure structure files.

   • When Export Content is set to Export Structure Only, you can choose whether to Merge export results into a single SQL file. If you select this option, the DDL statements are merged and exported into a single SQL file.

   • Select whether to Add Drop Statement Before Create Statement. If you select this option, a corresponding DROP statement is added before the CREATE statement for each object when you export object structure files.

8. Set the task execution method.

   Select Immediate Execution or Scheduled Execution.

9. You can keep the current configuration.

   After you specify all the information, you can select Save current configuration in the lower-left corner of the panel to save the current data file and structure file configurations.

10. Create the export task.

    After you specify all the information, click the Export button in the lower-right corner of the panel to create the export task.

View export tasks

Task list

After an export task is created, the Tickets panel appears automatically. In the Export list, you can view the task approval status and task information, and download the exported files.

The Export task list contains the following information:

Item	Description
ID	The ID of the export task.
Type	Displays the task type specified when the task was created.
Ticket Description	Displays the description entered when the task was initiated.
Current Handler	The approver. Click the search icon to search for and filter handlers.
Creator	The user who created the export task. Click the search icon to search for and filter creators.
Creation Time	Displays the date and time when the task was created.
Status	Displays the current status of the task. The available management operations vary based on the status.
Operation	Click the View button to go to the task details page to view the Task Details and Task Logs of the target task.

Task information

1. Click the View button in the Export task list.

2. The task details panel appears. Click Task Information to view basic information, export file information, and export object information.

   Item	Description
   Basic Information	Displays information such as the execution status, Task ID, Database, Task Type, Threat Level, Execution Method, Creator, and Creation Time.
   Export File Information	Displays the settings selected when the export task was created, such as Export Content, Data Format, File Encoding, Export Data Settings, and Export Structure Settings.
   Export Object Information	Displays the data export completion status for the objects selected in the task. This includes information such as Object Name, Object Type, Structure Processing Status, Actual Processed Count, and Data Processing Status.

Task flow

In the task details panel, click the Task Flow tab to view the status of each stage, such as Initiate Task, Approval, and Execution.

Task logs

In the task details panel, click the Task Logs tab to view all logs and alert logs for the task.

Item	Description
All Logs	Displays all log information for the task, including INFO, ERROR, and WARN logs.
Alert Logs	Alert logs separately display the ERROR and WARN logs for the task. When a task fails, you can view the alert logs to get error messages.

The toolbar also provides the following buttons:

Function	Description
Find	Click this button to find log information.
Download	Click this button to download the log information.
Copy	Click this button to copy the log information.

Export files

In the lower-right corner of the task details panel, click the Download button to download the file to your local computer.

References

• Importing structure and data