Data Management:Import data

Scenarios

• Table data migration

• Table data backup

• Table data analysis

Prerequisites

• One of the following types of databases is used:

  Database types

  • Relational databases

    • MySQL: ApsaraDB RDS for MySQL, PolarDB for MySQL, MyBase for MySQL, PolarDB for Xscale, AnalyticDB for MySQL, and MySQL databases from other sources

    • SQL Server: ApsaraDB RDS for SQL Server, MyBase for SQL Server, and SQL Server databases from other sources

    • PostgreSQL: ApsaraDB RDS for PostgreSQL, PolarDB for PostgreSQL, MyBase for PostgreSQL, AnalyticDB for PostgreSQL, and PostgreSQL databases from other sources

    • MariaDB: ApsaraDB for MariaDB and MariaDB databases from other sources

    • OceanBase

    • PolarDB for PostgreSQL(Compatible with Oracle)

    • DM

    • Db2

    • Oracle

  • Non-relational databases

    • ApsaraDB for Redis

    • ApsaraDB for MongoDB

• The destination instance is connected.

• The destination database contains at least one table. For more information about how to create a table, see the "Create a table" section of the Manage a database on the SQLConsole tab topic.

• The file that contains the data to be imported is ready. The file type is SQL, CSV, or XLSX.

  Important

  The fields of the data to be imported must be the same as those of the destination table. Otherwise, an error is reported.

Notes

The data import feature allows you to execute SQL statements in batches. By default, 1,000 SQL statements are executed in each batch. However, these SQL statements are not executed in an SQL transaction.

Procedure

In the following example, the destination database is managed in Security Collaboration mode.

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

2. In the left-side navigation pane of the homepage, find the database to which you want to import data.

   Note

   If the console is in Simple mode, click the Database instance tab on the left side of the homepage. In the database instance list that appears, find the destination database.

3. Double-click the destination database to go to the SQL Console tab.

4. In the navigation tree of the SQL Console tab, right-click the destination table and choose Import.

   Note

   You can also choose Database Development > Data Change > Normal Data Modify in the top navigation bar. On the page that appears, you can enter SQL statements or upload SQL script files to change data. The Normal Data Modify feature of DMS allows you to enter SQL statements to perform various operations. For example, you can insert, delete, modify, or query data in the table, or change the schema of the table. For more information, see Change regular data.

5. In the Data Import dialog box, configure the following parameters.

6. Parameter	Description
   Database	The destination database to which you want to import data. Only one database can be selected. Note If the destination database is managed in the Flexible Management or Stable Change mode, make sure that you have logged on to the database. Otherwise, the database is not displayed for selection.
   Associated Iterations	Optional. The iteration that you want to associate with the data import. You can click Select/Change Iteration to select an iteration.
   Reason Category	The reason for the data import.
   Business Background	The purpose or objective of the data import. Enter a detailed description to reduce unnecessary communication.
   Execution Method	The way in which you want the ticket to be run. Valid values: After Audit Approved, Order Submitter Execute After Audit Approved, Auto Execute Last Auditor Execute
   File Encoding	The file encoding type that you want to use for the destination database. Valid values: UTF-8 GBK ISO-8859-1 Automatic Identification
   Import mode	The import mode. Valid values: Speed Mode: In the Execute step, the SQL statements in the uploaded file are read and directly executed to import data to the destination database. The speed mode is less secure but faster than the security mode. Note By default, the Speed Mode is disabled for a database instance based on security rules. To enable the speed mode, go to the Details page of security rules, click the SQL Correct tab, and then set the Checkpoints parameter to Basic Configuration Item. Security mode: In the Precheck step, the uploaded file is parsed, and the SQL statements or CSV file data in the uploaded file is cached. In the Execute step, the cached SQL statements are read and executed to import data, or the cached CSV file data is read and imported to the destination database. The security mode is more secure but slower than the speed mode.
   File type	The format of the file for the data import. Valid values: SQL Script: By default, you can use only the INSERT and REPLACE statements to import data to database instances that are managed in the Security Collaboration mode. If you want to use other SQL statements to import data, modify the security rules for data import as a database administrator (DBA) or DMS administrator. You can modify the security rules by performing the following operations: Go to the Details page of the security rule set that is applied to the database instance and click the SQL Correct tab. In the list below the Batch Data import rules checkpoint, edit the security rules as needed. CSV: The delimiters in the file must be commas (,). Excel: The file can contain table headers and data, or contain only data. Table headers contain the attributes of data.
   Target Table Name	The destination table to which data is to be imported.
   Data Location	The type of the file for the data import. Valid values: 1st behavioral attributes: The first row of the table contains field names. 1st behavioral data: The first row of the table contains data.
   Write mode	The mode that you want to use to write the imported data to the destination table. Valid values: INSERT: The database checks the primary key when data is written. If a duplicate primary key value exists, an error message is returned. INSERT_IGNORE: If the imported data contains data records that are the same as those in the destination table, the data records in the imported data are ignored. REPLACE_INTO: If the imported data contains a row that has the same value for the primary key or unique index as a row in the destination table, the original row that contains the primary key or unique index is deleted, and the new row is inserted. Note You can use one of the three statements to write data to the ApsaraDB RDS for MySQL, PolarDB for MySQL, PolarDB-X, AnalyticDB for MySQL, and OceanBase databases. You can use only the INSERT statement to write data to other databases.
   Attachment	The file for the data import. Click Upload a file to upload a file. Note Supported file types include SQL, CSV, TXT, XLSX, and ZIP. The maximum size of the uploaded file is 5 GB.
   Other Options	Optional. This parameter specifies whether to skip errors. By default, the checkbox is cleared. If an error occurs, DMS stops executing SQL statements and sends an error message. If you select the check box, DMS skips errors and continues to execute SQL statements.
   SQL Statements for Rollback	Optional. If an SQL statement fails to be executed or an error occurs, DMS executes the SQL statements for rollback to undo the executed SQL operations and restore the database to the state before the SQL operations are performed. Valid values: Text: the SQL statements for rollback. Enter the SQL statements in the SQL Text field. Attachment: the SQL file for rollback. Upload the SQL file. Note Supported file types include SQL, TXT, and ZIP. The maximum size of the file is 15 MB.
   Change Stakeholder	Optional. The stakeholders involved in the data import. All specified stakeholders can view ticket details and assist in the approval process. Irrelevant users other than DMS administrators and DBAs are not allowed to view the ticket details.
   Attachments	Optional. The images or files that contain other necessary information of the ticket.

7. Click Submit and wait until the precheck is complete.

   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.

   Note

   If an error is reported during the type check of the Precheck step, adjust the security rule set that is applied to the database 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. In the Execute step, click Execute Change.

10. In the Task Settings dialog box, set the Execution Strategy parameter and click Confirm Execution. Valid values:

    • Running immediately: By default, this option is selected.

    • Schedule: If you select this option, you must specify a start time for the task.

      Note

      • In the Execute step, you can view the execution status, SQL check details, and scheduling logs of the task.

      • After the task is complete, you can open the SQL Console tab of the destination database and query the imported data.

11. Wait until the task is complete. If Task Execution Completed is displayed in the Complete step, the task is complete.