Export SQL result sets

Usage notes

• The limits on exporting an SQL result set, such as the number of rows you can export for free and the maximum table size for a single export, vary based on the control mode.

• To export data from another database within the same instance, log in to that database in DMS with a high-privilege account to prevent permission errors.

• For a logical database, the maximum export size is 512 MB. Data that exceeds this limit is truncated.

Procedure

1. Log in to DMS 5.0.

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

   Note

   For database types other than NoSQL, you can also run an SQL statement in the SQL Console window, and then choose Export File > Export more data from the results tab to create a data export ticket.

3. Configure the parameters for the data export ticket.

   Note

   The following example uses an ApsaraDB RDS for MySQL instance in Security Collaboration control mode. The parameters may vary slightly for other database types.

   Parameter	Required	Description
   Database name	Yes	Select the target database. Note You must have the required permissions to export data from the database. For more information, see View my permissions.
   Reason category	Yes	Select a reason for the data export. This choice aids in future ticket tracking.
   Business background	Yes	Describe the reason for or purpose of the data export in detail to reduce communication overhead.
   Affected rows	Yes	To estimate the number of data rows that will be affected by this export, you can use the count command in the SQL Console. Note During the precheck phase, the system displays the actual number of affected rows. You can verify if this number meets your expectations and identify any anomalies to prevent data security incidents.
   Skip validation	No	Determines whether to skip the validation process. If you select Skip Validation, you must also provide a reason. Warning If you select Skip Validation, DMS does not verify the number of affected rows. Exporting a large volume of data may pose risks to your business. Proceed with caution.
   Stakeholders	No	Users specified as stakeholders can view and collaborate on the ticket. Users who are not specified (except for administrators and DBAs) cannot view the ticket.
   Export SQL statement	Yes	Enter an SQL statement that can be directly executed, such as SELECT * FROM testtable. DMS automatically checks the syntax when you submit the request. You cannot submit the request if the syntax is incorrect. Note Only SELECT query statements are supported.
   Embed watermark	Yes	Specifies whether to embed a watermark in the exported file. Yes: Embeds a watermark in the exported file. No: Does not embed a watermark.
   Embedding method	Yes	Select an embedding method: Data watermark: Embeds identification information into the data. This watermark is not easily perceptible to data users. File watermark: Embeds identification information into the file. This watermark is directly visible to data users.
   File watermark message	Yes	Enter the file watermark message.
   Data watermark message	Yes	Enter the data watermark message.
   Embedded field	No	Enter the field where the watermark will be embedded. Note If you do not specify a field, the system selects one by default.
   Primary key / unique key	No	Enter one or more primary or unique keys, separated by commas (,). Note Providing this information significantly improves the robustness of the data watermark.
   Attachments	No	You can upload images or documents to provide additional information about the export.

4. After you configure the parameters, click Submit and await approval.

   The system runs an SQL precheck.

5. Click Submit for Approval and wait for the request to be approved.

   You can view the approval progress in the Approval section of the ticket details page.

   Important

   You must perform the export within 24 hours after the ticket is approved. After this period, the ticket expires, and you must create a new one. For an instance in Security Collaboration mode, you can adjust the time window for performing the export after approval. To do this, navigate to the Security Rules > Data Export settings associated with the instance, and modify the Period for Performing Export after Export Request of SQL Result Set Is Approved parameter.

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

7. In the Export Settings dialog box, configure the export settings.

   Parameter	Description
   Format (Required)	The file format for the export. Supported formats include CSV, EXCEL, SQL, and JSON (for NoSQL). Note If you choose to embed a file watermark, select EXCEL as the export format. If this parameter does not appear, it may be because the global parameter Enable file watermark for SQL result set exports is enabled. Go to O&M > Configuration Management, disable this parameter, and then perform the export operation again.
   Character set (Required)	The character set for the exported content. Supported character sets include Default Character Set, GBK, UTF-8, and ISO-8859-1.
   Execution strategy (Required)	The execution strategy: Execute immediately: The system starts the export immediately. Schedule: The system starts the export at a specified time. This is useful for avoiding peak business hours. Note Once a scheduled execution is set, its parameters cannot be modified.
   Export mode (Required)	The export mode: Speed Mode: The export task cannot be interrupted. Ordinary Mode: The export task can be interrupted.
   Export time (Required)	The timeout period for the export task, in seconds. The value can range from 0 to 2,147,483. If you do not configure this parameter, the instance-level export timeout is used by default. To view the instance-level export timeout, see Register a database instance. Note If the task duration exceeds the specified time before the export is complete, the task fails.
   Add field comment (Optional)	This parameter appears only when the database type is MySQL and the export format is CSV. If you enable this parameter, DMS adds a row with field comments below the field name row in the exported results.

   Note

   The system performs the export based on the specified configurations. You can view the export progress in the console.

8. After the export is complete, click Download Exported File.

   Important

   By default, once the export is complete, you can download the exported file at any time. For an instance in Security Collaboration mode, you can set a time interval for downloading the file. To do this, navigate to the Security Rules > Data Export settings associated with the instance, and configure the Period for Downloading Exported File after Export Request of SQL Result Set Is Approved parameter. For example, if you set the interval to 1, you can download the file within 1 hour after the ticket is approved. After 1 hour, the download link expires.