Import data

Prerequisites

• The database is one of the following types:

  • MySQL: RDS for MySQL, PolarDB for MySQL, MyBase for MySQL, PolarDB-X, AnalyticDB for MySQL, or a self-managed MySQL database.

  • SQL Server: RDS for SQL Server, MyBase for SQL Server, or a self-managed SQL Server database.

  • PostgreSQL: RDS for PostgreSQL, PolarDB for PostgreSQL, MyBase for PostgreSQL, AnalyticDB for PostgreSQL, or a self-managed PostgreSQL database.

  • MariaDB: RDS for MariaDB or a self-managed MariaDB database.

  • OceanBase.

  • PolarDB for PostgreSQL (Compatible with Oracle).

  • DM.

  • Db2.

  • Oracle.

  • Redis.

  • MongoDB.

• You have logged on to the destination database in DMS.

• You have the Change permission on the destination database.

Usage notes

• The size of a single SQL, CSV, or Excel file to be imported cannot exceed 5 GB.

• DMS supports uploading ZIP attachments. To import multiple files in the same ticket, you can compress the files into a ZIP package and upload it.

• The data import feature does not support modifying multiple databases in the same ticket. To modify multiple databases, submit a Normal Data Change ticket. For more information, see Normal Data Change.

• To modify only a small amount of data, use a Normal Data Change or DML Lock-Free Change ticket to ensure stability.

• If a batch data import script contains schema evolution SQL statements, these statements are executed in native mode, not in lock-free mode, even if lock-free schema evolution is enabled for the instance.

• To avoid affecting database performance during batch data import, use high-performance SQL statements, such as INSERT, UPDATE, and DELETE statements that use primary key indexes.

• The CSV or Excel file to be imported (Sample files) can contain only field names and field values. Otherwise, the import fails.

Procedure

1. Log on to the DMS console V5.0.

2. Move the pointer over the icon in the upper-left corner of the DMS console and choose All Features > Database Development > Data Change > Data Import.

   Note

   If you use the DMS console in normal mode, choose Database Development > Data Change > Data Import in the top navigation bar.

3. On the Data Change Ticket Application page, configure the ticket parameters. The following table describes some of the parameters.

   Parameter	Description
   Database	Search for the destination database for the data import. You can select only one database. Note For a database instance that is managed in Flexible Management or Stable Change mode, you must log on to the instance in DMS before you can select it from the drop-down list. You can also enter the endpoint of the database to search for it. Example: test_db@rm-2zegs****.mysql.rds.aliyuncs.com:3306 [example].
   Business Background	Enter the background information about your business requirements to improve communication efficiency.
   File Encoding	The file encoding of the database. By default, Auto-detect is selected. Other values: UTF-8. GBK. ISO-8859-1.
   Import Mode	The data import mode. Two import modes are supported: Speed Mode: In the execution phase, DMS reads the file and directly runs the SQL statements on the specified destination database. This mode is less secure but more efficient than the security mode. Note By default, the security rules do not allow data import in Speed Mode. You can enable this feature in Security Rules > SQL Change > Basic Configuration Items for the instance. Security Mode: In the precheck phase, DMS parses the file and caches the SQL or CSV data. In the execution phase, DMS reads the data from the cache and runs it on the specified destination database. This mode is more secure but less efficient than the speed mode.
   File Type	Select the file type of the data to import: SQL Script: In Security Collaboration mode, only the INSERT and REPLACE command types are allowed by default. To change this, a DBA or an administrator can modify the settings in Security Rules > SQL Change > Batch Data Import. CSV Format: The separator in the file must be a comma. Excel Format: The Excel file can contain a header row (attributes) or only data. Note If the format of the imported file is CSV or Excel, make sure that you have created a table for the data to be imported.
   Destination Table	Select the destination table for the data import. Note This parameter appears when you set File Type to CSV or Excel.
   Data Location	Select the location of the data: First row contains attributes: The first row of the table contains field names. First row contains data: The first row of the table contains data. Note This parameter appears when you set File Type to CSV or Excel.
   Write Mode	Select the write mode for the file: INSERT: The database checks the primary key during data insertion. If a duplicate value is found, an error is reported. INSERT_IGNORE: If the table already contains an identical record, the new data is ignored. REPLACE_INTO: If a row in the table has the same value for the primary key or a unique index, the existing row is deleted and the new data is inserted. Note This parameter appears when you set File Type to CSV or Excel. RDS for MySQL, PolarDB for MySQL, PolarDB-X, AnalyticDB for MySQL, and OceanBase databases support three write modes: INSERT INTO, INSERT IGNORE, and REPLACE INTO. Other databases support only INSERT INTO.
   Attachment	Supports Upload File (local file) and Upload OSS File. To upload a file stored in Object Storage Service (OSS), you must first add the destination OSS data source to DMS. After that, click Upload OSS File, select the destination bucket, and enter the file path (for example, examplefolder/example.sql). Note Supported file types are SQL, CSV, TXT, XLSX, and ZIP. The attachment to be imported cannot exceed 5 GB. To add an OSS data source, see Add cloud database instances.
   Other Options (Optional)	Select whether to ignore exceptions. Do not ignore (cleared): This is the default setting. If an exception occurs, the system stops running subsequent SQL statements and reports an error. Ignore (selected): If the execution fails, the system ignores the exception and continues to run subsequent SQL statements.

4. Click Submit Application. Wait for the precheck to pass. If the precheck fails, identify the cause based on the prompt and click Retry.

   Note

   • The system prechecks the uploaded SQL file. For a CSV file, the system generates the corresponding INSERT statements.

   • If an error occurs during the type check in the precheck phase, you must adjust the security rules associated with the instance based on the error message. For more information, see Data change.

5. In the Approval section, click Submit for Approval. In the Prompt dialog box, click Confirm.

6. After the ticket is approved, in the Execution section, click Execute Change.

7. In the Task Settings dialog box, select an execution method for the ticket.

   The procedure consists of the following steps:

   • Execute Immediately: This is the default setting. The ticket is executed immediately after you click Confirm Execution.

   • Scheduled Execution: Select a start time for the task. The system automatically runs the task at the specified time after you click Confirm Execution.

   Note

   • During execution, the system reads the parsed SQL in a stream and runs it on the database in batches (1 MB per batch).

   • In the Execution section, you can view the task execution status, task SQL check details, and scheduling logs.

   • If a paused task is restarted, it runs from the beginning or from the point where it was paused, depending on the import mode.

     • Speed mode: If a paused task is restarted, the script is run or the data file is imported from the beginning.

     • Security mode: If a paused task is restarted, the script is run or the data file is imported from the point where it was paused.

8. When the task is complete, the data import ticket is finished.

9. (Optional) Query the imported data.

   You can go to the SQL Console page to query the imported data. For more information, see Get started with SQL Console.

Sample files

• Sample SQL attachment

• Sample CSV file (first row contains field names)

• Sample CSV file (first row contains data)

• Sample Excel attachment (first row contains field names)

• Sample Excel attachment (first row contains data)

• Sample JSON attachment

FAQ

• Q: What do I do if a "permission denied for schema XXXXX" error occurs during data import?

  A: This error indicates that the account used to log on to the database in DMS does not have the required permission. You can log on to the database in DMS using a privileged account and retry the data import. Alternatively, you can import the data to another public database. To switch the database account, see Edit instance information.

  If the error persists, you can use Data Transmission Service (DTS) to migrate data. For more information, see Overview of migration solutions.

• Q: What do I do if the data import ticket fails the precheck and the system reports that submitting XXXX change statements is not allowed?

  A: An administrator or a DBA must modify the SQL change security rules to allow a specific type of SQL statement to be run during the import. The following example shows how to allow the CREATE_TABLE statement:

  1. In the DMS console, go to the Security Rules page.

  2. Edit the destination rule. In the pane on the left of the rule details page, click SQL Change.

  3. Select Batch Data Import Rules and click Edit to the right of the Allow batch import of INSERT statements rule.

  4. Add the CREATE_TABLE SQL type and click Submit. For more information, see SQL change.

  5. After the rule is modified, return to the data import ticket details page and click Retry in the Precheck section.

• Q: How do I upload a file that is larger than 5 GB?

  A: You can use one of the following methods as needed:

  • Split the file into multiple smaller files and submit multiple tickets to upload them.

  • Use Navicat to remotely connect to the database and then upload the file.

• Q: How do I delete imported data if an error occurs during the data import?

  A: You can select a deletion method based on the amount of data to delete.

  • For a small amount of table data

    You can run the DELETE statement in the DMS SQL Console. For more information, see Use SQL statements to change table data.

  • For a large amount of table data

    You can submit a Normal Data Change or DML Lock-Free Change ticket to delete the table data. The DML Lock-Free Change feature lets you modify a large amount of table data without locking the table. For more information, see Normal Data Change and DML Lock-Free Change.