Data Management (DMS) allows you to import large amounts of data to a database in a quick manner. This helps you save costs of both human labor and resources.

Prerequisites

The database to which you want to import data is managed in a MySQL instance, an ApsaraDB for OceanBase instance, or a PolarDB-X instance.

Background information

  • Scenarios
    • Initialize application data.
    • Import data to DMS in the event of emergencies. For example, import backup data to roll back a misoperation.
  • Supported file formats
    • TXT files. Each TXT file for importing data can be up to 5 GB in size.
    • SQL scripts. Each SQL script for importing data can be up to 5 GB in size. For database instances in Security Collaboration mode, the default security rules for data import allow only INSERT and REPLACE statements to be used. To allow other SQL statements to be used, modify the default security rules for data import or create security rules for data import as a database administrator (DBA) or DMS administrator. Perform the following steps: In the top navigation bar of the DMS console, choose System Management > Security > Security Rules. On the Security Rules page, find the target security rule set and click Edit in the Actions column. On the Details page, click the SQL Correct tab. Set the Checkpoints parameter to Batch Data import rules and modify the default security rules or create security rules as needed. If you are a common user, ask a DBA or DMS administrator for help.
    • CSV files. A CSV file is a text file that uses commas (,) to separate values. The first row must be column names.

Usage notes

  • If you need to execute SQL statements to import only a small amount of data, we recommend that you submit a Normal Data Modify or Lock-Free Data Modify ticket. This helps ensure the stability of the destination database. For more information, see Common data change and Data change without locking.
  • If you submit a Large Data Import ticket to import a large amount of data to the destination table, the table will be locked even if you have set OnlineDDL to Open(DMS OnlineDDL first) for the instance of the destination database.
  • We recommend that you use SQL statements with better performance to import data, such as UPDATE and DELETE statements, which use indexes of primary keys, and INSERT statements.
  • For database instances in Security Collaboration mode, the default security rules for data import allow only INSERT and REPLACE statements to be used. To allow other SQL statements to be used, modify the default security rules for data import or create security rules for data import as a DBA or DMS administrator. If you are a common user, ask a DBA or DMS administrator for help.

Procedure

  1. Log on to the DMS console.
  2. In the top navigation bar, choose Data Plans > Data Import.
  3. On the Data Import page, set the parameters as described in the following table.
    Parameter Description
    Reason Category Required. The reason for the data import operation.
    Business Background Required. The detailed description of the business background of the data import operation.
    Change Stakeholder Optional. The stakeholders of the data import operation.
    Database Required. The destination database of the data import operation.
    Note If the destination database is managed in an instance in Flexible Management or Stable Change mode, make sure that the database is logged on. Otherwise, the database will not appear in the drop-down list.
    File Encoding Required. The encoding algorithm to be used by the destination database. Valid values:
    • UTF-8
    • GBK
    • ISO-8859-1
    • Automatic Identification
    SQL Statements for Change Required. The file that contains the SQL statements for importing data. Select a file format and upload a file. You can upload an SQL script or a CSV file. If you select the CSV file format, a field appears next to CSV. Enter the name of the destination table in the field.
    SQL Statements for Rollback Optional. The SQL statements for rolling back the data import operation. Valid values: Text and Attachment.
    • Text: the SQL statements for rolling back the data import operation. Enter the SQL statements in the field.
    • Attachment: the file that contains the SQL statements for rolling back the data import operation. You can upload a TXT file or an SQL script. The file can be up to 15 MB in size.
    Attachments Optional. The one or more images or files that contain other necessary information for the ticket.
  4. Click Submit and wait until the precheck is complete. If the ticket fails the precheck, find out the issue based on the prompt, resolve the issue, and then submit the ticket again.
    Note If the uploaded file is an SQL script, DMS prechecks the SQL statements in the uploaded file. If the uploaded file is a CSV file, DMS generates INSERT statements based on the uploaded file.
  5. Submit the ticket for approval.
  6. After the ticket is approved, click Execute Change in the Execute step.
  7. In the Task Settings dialog box, set the control items. If you do not want the task to be run immediately, turn off the Execute Immediately switch and specify a point in time for DMS to automatically run the task. Then, click Confirm Execution.
    Note During the execution, DMS reads the SQL statements in streaming mode and executes the SQL statements in batches. Each batch of SQL statements is 1 MB in size.

Sample files

  • Click CSV file to download a sample CSV file for importing data.
  • Click SQL script to download a sample SQL script for importing data.