All Products
Search
Document Center

Data export

Last Updated: May 09, 2020

Background

  • When you query data in the SQLConsole, the system limits the total number of rows returned in a single query for front-end performance and data security considerations. If you need to analyze a large amount of data or extract relevant data for business operations, you can use the data export feature.
    • Only the SQL SELECT syntax used for queries is supported.
    • Currently, DMS Enterprise allows you to export data from the following types of databases: MySQL, PostgreSQL, SQL Server, Oracle, OceanBase, and Distributed Relational Database Service (DRDS).

Procedure

  1. Choose Task Type > Data Export in the left-side navigation pane and click +Data Export. On the page that appears, set parameters for creating a data change ticket.

    Parameter Setting method Value Description
    Reason Select an option from the drop-down list. The administrator and DBA can maintain this parameter as required on the System Management > Configuration Management page.
    Business Background Manually enter the value. Describe in detail the purpose of this data export and the expected R&D objective.
    Stakeholder Select an option from the drop-down list. All stakeholders can view the ticket details and assist R&D engineers in advancing the approval process. Except the administrator and DBA, users irrelevant to the ticket are not allowed to view the ticket details.
    Database Select an option from the drop-down list. The value must be a database on which you have the export permission. You cannot submit a data export ticket if you only have the permission to query databases or export data in tables.
    Exported Rows Manually enter the value. Estimate the number of data rows that will be affected in this data export. You can count the number by running the COUNT statement in the SQLConsole.
    SQL Statements to Export Manually enter the value. The SQL statements must be executable. DMS Enterprise will automatically check whether the syntax of the SQL statements is correct when you submit the ticket. If the syntax is incorrect, the ticket cannot be submitted.
  2. Submit the data export ticket and confirm the ticket information.

    Focus on the difference between the number of actually affected data rows and the expected number of data rows to be affected. Avoid an unexpected data export due to incorrect logic written in SQL statements.

  3. Wait for the ticket to be approved.

    • If the ticket does not pass the approval, it is automatically closed.
    • If you need to modify the script information, you can withdraw the ticket before exporting data and submit a new ticket.

    Note: The administrator and DBA can flexibly configure security rules and approval processes for database instances of their enterprise on the System Management > Security Rules page.

  4. Select one of the following export methods:

    • Export to CSV.
    • Export to Excel.
    • Export to SQL Script: The exported data is written in the form of INSERT statements in the exported script. You can use the script to write the exported data to other databases or tables.
    • Download Exported File: The most recently exported file is downloaded by default. If you have not exported a file yet, you will be prompted to export a file first.

Tips

  • If you need to perform a data export that has been performed before, you can find the original ticket and click Create As in the upper-right corner of the page to copy the ticket and submit a new ticket.
  • If you need to modify the script information, you can withdraw and modify the ticket after it is submitted, regardless of whether it is approved or not. This will prevent the wrong operation from being performed after the original ticket is approved.
  • The administrator and DBA can set a security rule for a database instance on the System Management > Instance Management page to specify approval processes for tickets. We recommend that you also use tickets to manage routine data exports in your test environment. Considering the impact on R&D efficiency, you can configure an approval-free process.
  • In the case of database sharding and table partitioning, if you have configured a logical database, a logical table, and a routing algorithm in DMS Enterprise, you can conveniently submit a single ticket to perform a data export within the logical database and table without having to submit a ticket for each physical database and table.
    • If you have configured a routing algorithm and the WHERE clause contains a routing field, the system can automatically locate the corresponding physical database and table to perform the data export.
    • If you have not configured a routing algorithm, the WHERE clause does not contain a routing field, or the type of a routing field does not comply with the schema definition, then the SQL statements will be run on each physical database and table. This results in a longer execution time. We recommend that you avoid these scenarios.

      You can use this method in scenarios where a data export needs to be performed on all physical tables. Such scenarios, however, rarely occur.

    • You can aggregate physical tables by using COUNT(*) or GROUP BY.