Data Management:Export SQL result sets

Usage notes

• Limits are imposed on the SQL Result Set Export feature based on the control mode of your database instance. Such limits include the maximum number of rows that can be exported for free and the maximum capacity of tables that can be exported at a time. For more information, see Control modes.

• To export data from other databases of the same database instance, make sure that the account that you use to log on to the current database in DMS is a privileged account before you export the data. This prevents export failures caused by insufficient permissions.

Procedure

1. Log on to the DMS console V5.0.

2. In the top navigation bar, choose Database Development > Data Export > SQL Result Set Export.

   Note

   You can also export data from a relational database or data warehouse on the SQLConsole tab of the database. To do so, execute SQL statements on the SQLConsole tab of the database. Then, choose Export File > Export more data to export an SQL result set on the execution result tab.

3. On the Ticket Application page, configure the parameters that are described in the following table.

   Note

   In this example, an ApsaraDB RDS for MySQL instance that is managed in Security Collaboration mode is used. The parameters that you need to configure for other types of databases are slightly different from the parameters in this example.

   Parameter	Required	Description
   Database Name	Yes	The database from which you want to export an SQL result set. Note You must have the permissions to export SQL result sets from the database. For more information, see View owned permissions.
   Reason Category	Yes	The reason for this export. This helps you find the ticket in subsequent operations.
   Business Background	Yes	The purpose or objective of this export. This reduces unnecessary communication.
   Affected Rows	Yes	The estimated number of data rows to be affected by this export. To obtain the actual number of affected rows, you can use the COUNT function on the SQL statements on the SQLConsole tab. Note In the Precheck step, DMS displays the actual number of affected rows. You can check whether the number of affected rows is as expected. This ensures data security.
   Skip Validation	No	Specifies whether to skip validation. If you select Skip Validation, you must enter a reason in the field below the check box. Warning After you select Skip Validation, DMS does not check the number of rows that may be affected by this export. If a large amount of data is to be exported, your business may be affected. Proceed with caution.
   Stakeholder	No	The stakeholders that are involved in this export. All the specified stakeholders can view the ticket details and participate in the approval process. Irrelevant users except for DMS administrators and database administrators (DBAs) have no access to the ticket details.
   Export Statement	Yes	The SQL statements that can be executed. Example: SELECT * FROM testtable. DMS verifies the syntax of the SQL statements when you submit the ticket. The ticket can be submitted only if its syntax is valid. Note Only SELECT statements are supported.
   Embed Watermark	Yes	Specifies whether to embed a watermark into the exported file. Valid values: Yes No
   Embedding Method	Yes	The method in which a watermark is embedded in the exported file. Valid values: Data Watermark: The identification information is embedded into data in a way that makes the watermark imperceptible to data users. File Watermark: The identification information is embedded into the exported file in a way that makes the watermark visible to data users.
   File Watermark Message	Yes	The content of the watermark to be embedded into the exported file.
   Data Watermark Message	Yes	The content of the watermark to be embedded into data.
   Embedded Field	No	The field into which the watermark is to be embedded. Note If you do not specify a field, DMS selects a field by default.
   Primary key / Unique Key	No	One or more primary keys or unique keys. Separate multiple keys with commas (,). Note To improve the robustness of the watermark, we recommend that you specify this parameter.
   Attachments	No	The images or files that are uploaded to add more information about this export.

4. Click Submit.

   DMS prechecks the SQL statements. Wait until the precheck is complete.

5. Click Submit for Approval and wait for approval.

   On the Ticket Details page, you can view the approval progress in the Approval step.

   Important

   You must export an SQL result set within 24 hours after the ticket is approved. Otherwise, you cannot export the SQL result set. In this case, you must create another SQL Result Set Export ticket. If the database from which you want to export an SQL result set is managed in Security Collaboration mode, you can set the period during which the SQL result set can be exported after the ticket is approved. To do so, perform the following operations: Go to the Details page of the security rule set that is applied to the database instance, click Data Export in the left-side bar, and then click Edit in the Actions column of the Period for Performing Export after Export Request of SQL Result Set Is Approved configuration item to modify the configuration value.

6. In the Execute/Automatic Execution step, click Export.

7. In the Export Settings dialog box, configure the parameters that are described in the following table.

   Parameter	Description
   Format	Required. The format of the file to be exported. Valid values: CSV, EXCEL, SQL, and JSON. The JSON format is available only for NoSQL databases. Note If you set the Embed Watermark parameter to Yes, set this parameter to EXCEL.
   Character Set	Required. The character set of the file to be exported. Valid values: Default Character Set, GBK, UTF-8, and ISO-8859-1.
   Execution Strategy	Required. The strategy used to export the file. Valid values: Running immediately: DMS immediately exports the file after you click Confirm Execution. Schedule: DMS exports the file at the point in time that you specify. You can specify a point in time during off-peak hours. Note The configurations cannot be modified after you set this parameter to Schedule and confirm the configurations.
   Export Mode	Required. The export mode of the file. Valid values: Speed Mode: If you select this mode, the export task cannot be terminated before it is complete. Ordinary Mode: If you select this mode, the export task can be terminated during the export.

   Note

   DMS exports the file based on your configurations. You can check the export progress in the Execute/Automatic Execution step.

8. After the file is exported, click Download Exported File to download the file to your local machine.

   Important

   By default, after the file is exported, you can download the exported file at any time. If the database from which you want to export an SQL result set is managed in Security Collaboration mode, you can set the period during which the exported file can be downloaded. To do so, perform the following operations: Go to the Details page of the security rule set that is applied to the database instance, click Data Export in the left-side bar, and then click Edit in the Actions column of the Period for Downloading Exported File after Export Request of SQL Result Set Is Approved configuration item to modify the configuration value. For example, if you set the value to 1, you can download the exported file only within 1 hour after the ticket is approved.