Data Management (DMS) provides the SQL Result Set Export feature. You can use this feature to write SQL statements in DMS and export relevant data based on your needs.

Prerequisites

The following databases are used:
  • Relational databases:
    • MySQL series: self-managed MySQL, ApsaraDB RDS for MySQL, PolarDB for MySQL, PolarDB-X, and ApsaraDB OceanBase for MySQL
    • SQL Server series: self-managed SQL Server and ApsaraDB RDS for SQL Server
    • PostgreSQL series: self-managed PostgreSQL, ApsaraDB RDS for PostgreSQL, and PolarDB for PostgreSQL
    • Oracle series: self-managed Oracle and PolarDB-O
    • MariaDB
  • Data warehouses: AnalyticDB for MySQL, AnalyticDB for PostgreSQL, Data Lake Analytics (DLA), ApsaraDB for ClickHouse, and MaxCompute
  • NoSQL databases: ApsaraDB for MongoDB, ApsaraDB for HBase, ApsaraDB for Lindorm, and ApsaraDB for Cassandra
Note For more information, see Supported database types and features.

Procedure

  1. Log on to the DMS console.
  2. In the top navigation bar, move the pointer over the More icon and choose Data Plans > SQL Result Set Export.
    Note To create a data export ticket, you can also choose Export File > Export more data in the execution result section of the SQLConsole tab after an SQL statement is executed.
  3. On the SQL Result Set Export tab, set the parameters that are described in the following table.
    Set the parameters of an SQL Result Set Export ticket
    Parameter Description
    Database Name The database whose SQL result sets you want to export.
    Note You must have the export permission on this database. For more information, see View owned permissions.
    Reason Category The reason for this data export. This helps you find the ticket in subsequent operations.
    Business Background The purpose or objective of this data export. This reduces unnecessary communication.
    Affected Rows The estimated number of data rows to be affected by this data export. To obtain the actual number of affected rows, you can use the COUNT function in SQL statements in the SQLConsole.
    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 can ensure data security.
    Skip Validation Specifies whether to skip validation. If you select Skip Validation, you must enter a reason in the field next to the check box.
    Warning After you select Skip Validation, DMS does not check the number of rows to be affected by this data export. If the amount of data to be exported is large, your business may be affected. Proceed with caution.
    Stakeholder The stakeholders of this data export. All specified stakeholders can view the ticket details and assist in the approval process. Irrelevant users other than DMS administrators and database administrators (DBAs) are not allowed to view the ticket details.
    Export Statement The SQL statements that can be executed to export data. Example: SELECT * FROM testtable. DMS verifies the syntax of the SQL statements when you submit the ticket. If the syntax is invalid, you cannot submit the ticket.
    Note Only SELECT statements are supported.
    Attachments The images or files that are uploaded to add more information about this data export.
  4. After you complete the configurations, click Submit and wait for approval.
    DMS prechecks the SQL statements.
  5. Click Submit for Approval and wait for approval.
    Note On the Data Export Ticket Details tab, you can view the approval progress in the Approval step.
  6. In the Execute/Automatic Execution step, click Export.
  7. In the Export Settings dialog box, configure export settings.
    Parameter Description
    Format The format of the file to be exported. Valid values: CSV, EXCEL, SQL, and JSON. The JSON format is available only for a NoSQL database.
    Character set The character set of the file to be exported. Valid values: Default Character Set, GBK, UTF-8, and ISO-8859-1.
    Execution Strategy The strategy 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 time that you specify. You can specify a time during off-peak hours.
    Then, DMS exports the file based on your configurations. You can view 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 on-premises machine.
    Download Exported File