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

One of the following databases is used:
  • Relational databases
    • MySQL: ApsaraDB RDS for MySQL, PolarDB for MySQL, MyBase for MySQL, PolarDB-X, and other MySQL databases
    • SQL Server: ApsaraDB RDS for SQL Server, MyBase for SQL Server, and other SQL Server databases
    • PostgreSQL: ApsaraDB RDS for PostgreSQL, PolarDB for PostgreSQL, MyBase for PostgreSQL, and other PostgreSQL databases
    • MariaDB: ApsaraDB for MariaDB TX and other MariaDB databases
    • OceanBase: ApsaraDB for OceanBase in MySQL mode, ApsaraDB for OceanBase in Oracle mode, and self-managed OceanBase databases
    • PolarDB for Oracle
    • Oracle
    • DM
    • Db2
  • NoSQL:ApsaraDB for MongoDB, ApsaraDB for Lindorm, and ApsaraDB for Cassandra
  • Data warehouses: AnalyticDB for MySQL, AnalyticDB for PostgreSQL, DLA, ApsaraDB for ClickHouse, MaxCompute, and Hologres

Procedure

  1. Go to the DMS console V5.0.
  2. In the top navigation bar, click Database Development. In the left-side navigation pane, choose Data Export > SQL Result Set Export.
    Note For a relational database, you can 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 Data Export Ticket Application page, set the parameters that are described in the following table.
    Parameter Description
    Database Name The database where you want to export an SQL result set.
    Note You must have the export permissions on this database. For more information, see View owned permissions.
    Reason Category The reason for this export. This helps you find the ticket in subsequent operations.
    Business Background The purpose or objective of this export. This reduces unnecessary communication.
    Affected Rows 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 in 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 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 that may be affected by this export. If the amount of data to be exported is large, your business may be affected. Proceed with caution.
    Stakeholder The stakeholders involved in this export. All specified stakeholders can view the ticket details and take part 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 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 Ticket Details page, 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, set the parameters that are described in the following table.
    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 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.
    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.