Import data

Use cases

• Table data migration

• Table data backup

• Table data analysis

Prerequisites

• The target database is one of the following types:

  • Relational databases

    • MySQL: ApsaraDB RDS for MySQL, PolarDB for MySQL, MyBase for MySQL, PolarDB-X, AnalyticDB for MySQL, and other self-managed MySQL databases.

    • SQL Server: ApsaraDB RDS for SQL Server, MyBase for SQL Server, and other self-managed SQL Server databases.

    • PostgreSQL: ApsaraDB RDS for PostgreSQL, PolarDB for PostgreSQL, MyBase for PostgreSQL, AnalyticDB for PostgreSQL, and other self-managed PostgreSQL databases.

    • MariaDB: ApsaraDB RDS for MariaDB and other self-managed MariaDB databases.

    • OceanBase

    • PolarDB for PostgreSQL (Oracle-compatible)

    • DM Database

    • Db2

    • Oracle

  • NoSQL databases

    • Redis

    • MongoDB

• You have logged on to the target instance.

• The target database contains at least one table. For more information about how to create a table, see Create a table.

• You have a data file ready for import. The file can be in SQL, CSV, or XLSX format.

  Important

  The fields in the data file must match the fields in the target table. Otherwise, an error will occur.

Usage notes

• The data import feature executes SQL statements in batches, with a default of 1,000 statements per batch. This process does not guarantee transactional integrity for the entire set of SQL statements.

• Do not use the SET @@SESSION.SQL_LOG_BIN= 0 command in SQL statements. DMS does not support modifying SQL_LOG_BIN by using commands. Executing this statement will cause the operation to fail.

Procedure

The following procedure demonstrates the steps for a database in Security Collaboration mode.

1. Log in to DMS 5.0.

2. In the database instance list on the left, find the target database.

   Note

   If you are using the console in Simple mode, click Database instance on the left and find the target database in the list.

3. Double-click the target database to open the SQL Console page.

4. In the table list area, right-click the target table and click Import.

   Note

   Alternatively, you can navigate to Database Development > Data Change > Normal Data Modify to submit SQL statements or upload an SQL script file. The Normal Data Modify feature in DMS supports more SQL statement types for operations such as creating, retrieving, updating, and deleting data, and changing table schemas. For more information, see Normal Data Modify.

5. On the Data Import panel, configure the following settings.

6. Parameter	Description
   Database	The target database for the import operation. You can select only one database. Note For an instance that uses the Flexible Management or Stable Change management mode, you must log on to the database before you can select it.
   Associated iterations (Optional)	Click Select/Change Iteration to associate an existing iteration.
   Reason category	Select a reason for the data import.
   Business background	Describe the business background of this operation in detail to reduce communication delays.
   Execution method	Select how you want to execute the ticket: After Audit Approved, Order Submitter Execute. After Audit Approved, Auto Execute. Last Auditor Execute.
   File encoding	Select the file encoding: UTF-8 GBK ISO-8859-1 Automatic Identification
   Import mode	Select an import mode. Two modes are supported: Speed Mode: Reads the file during the execution phase and runs the SQL statements directly on the target database. This mode is faster but less secure than Security mode. Note By default, security rules do not allow data import in Speed Mode. You can enable this check in the Basic Configuration Item section of the SQL Correct tab for the security rules associated with the instance. Security mode: Parses the file and caches the SQL or CSV data during the precheck phase. During the execution phase, it reads the data from the cache and runs it on the target database. This mode is more secure but slower than Speed Mode.
   File type	Select the type of the file to import: SQL Script: In Security Collaboration mode, only INSERT and REPLACE commands are allowed by default. To change this, a DBA or administrator can modify the rules in Security Rules > SQL Correct > Batch Data import rules. CSV: The delimiter in the file must be a comma (,). Excel: The Excel file can contain a header row (attributes) or only data.
   Target table	Select the target table for the data import.
   Data location	Specify the location of the data: First row contains headers: The first row of the table contains field names. First row contains data: The first row of the table contains data.
   Write mode	Select a write mode: INSERT: Inserts new rows. The operation fails if a primary key value duplicates an existing one. INSERT_IGNORE: If a new row's primary key value duplicates an existing one, the new row is ignored and the existing row is kept. REPLACE_INTO: If a new row has the same primary key or unique index value as an existing row, the existing row is deleted before the new row is inserted. Note ApsaraDB RDS for MySQL, PolarDB for MySQL, PolarDB-X, AnalyticDB for MySQL, and OceanBase support these three write modes. Other databases support only INSERT.
   Attachment	Click Upload File to upload an attachment. Note Supported file types: SQL, CSV, TXT, XLSX, and ZIP. The maximum attachment size is 5 GB.
   Other options (Optional)	Specify whether to ignore errors. Do not ignore (default): The task stops if an error occurs. Ignore: The task ignores any errors and continues to execute the remaining SQL statements.
   SQL statements for rollback (Optional)	If an SQL statement fails or an error occurs, you can use rollback statements to undo the changes and restore the database to its previous state. You can provide the statements in one of the following ways: Text: Enter the rollback statements in the text box below. Attachment: Upload a file that contains the rollback statements. Note Supported file types are SQL, TXT, and ZIP. The file size cannot exceed 15 MB.
   Change stakeholder (Optional)	All specified stakeholders can view the ticket and collaborate on the task. Users who are not specified as stakeholders cannot view the ticket, except for administrators and DBAs.
   Ticket attachments (Optional)	Upload images or documents to supplement the ticket details.

7. Click Submit and wait for the precheck to complete.

   During this phase, the system prechecks your uploaded SQL. If you upload a CSV file, the system generates the corresponding INSERT statements.

   Note

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

8. In the Approval step, click Submit for Approval. In the Prompt message, click OK.

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

10. In the Task Settings dialog box, select an Execution Strategy for the ticket and click Confirm Execution.

    • Execute Immediately: This is the default option.

    • Schedule Execution: Specify a custom start time for the ticket.

      Note

      • You can view the task status, SQL check details, and scheduling logs in the Execute section.

      • After the task is complete, you can query the imported data in the SQL Console of the target database.

11. The task is complete when Task Execution Completed appears in the Complete section.