how to import data in the sql window - Data Management - Alibaba Cloud Documentation Center

This topic describes how to import data into a database by submitting a ticket on the SQL Console page.

Scenarios

Table data migration
Table data backup
Data analytics

Prerequisites

The database is one of the following types:
- Relational databases
  - MySQL: RDS for MySQL, PolarDB for MySQL, MyBase for MySQL, PolarDB-X, AnalyticDB for MySQL, and other MySQL sources.
  - SQL Server: RDS for SQL Server, MyBase for SQL Server, and other SQL Server sources.
  - PostgreSQL: RDS for PostgreSQL, PolarDB for PostgreSQL, MyBase for PostgreSQL, AnalyticDB for PostgreSQL, and other PostgreSQL sources.
  - MariaDB: RDS for MariaDB and other MariaDB sources.
  - OceanBase
  - PolarDB for PostgreSQL (Compatible with Oracle)
  - Dameng
  - DB2
  - Oracle
- Non-relational databases
  - Redis
  - MongoDB
You are 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.
A file that contains the data to import is available. The file can be in SQL, CSV, or XLSX format.
Important
The fields in the data to import must match the fields in the target table. Otherwise, the import fails.

Notes

The data import feature supports executing SQL statements in batches. By default, 1,000 SQL statements are executed in each batch. However, the operations are not performed in a single transaction.
Do not use the SET @@SESSION.SQL_LOG_BIN= 0 command in SQL statements. DMS does not support modifying SQL_LOG_BIN from the command line. Executing this statement causes the operation to fail.

Note

An operation performed in a single transaction means that if one SQL statement fails, all SQL statements are canceled and the database is rolled back to its state before the execution.

Procedure

The following steps use a database in Security Collaboration mode as an example.

Log on to the DMS console V5.0.
On the navigation pane on the left of the home page, find the target database.
Note
If you use the console in Simple mode, click Database Instance in the navigation pane on the left. In the database instance list, find the target database.
Double-click the target database to go to the SQL Console page.
In the table list area, right-click the target table and click Import.
Note
You can also go 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, such as data insertion, deletion, modification, and query, and table schema changes. For more information, see Normal Data Modify.
In the Data Import panel, configure the following parameters.

Parameter	Description
Database	The destination database for the data import. You can select only one database. Note For instances in Flexible Management or Stable Change Control 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	The reason for the data import.
Business Background	A detailed description of the business background for this operation. This reduces communication costs.
Execution Method	The execution method for the ticket: Submitter executes after approval. Automatic execution after approval. Last approver executes.
File Encoding	The file encoding of the database: UTF-8 GBK ISO-8859-1 Automatic Identification
Import Mode	The 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 less secure but faster than Security Mode. Note By default, security rules do not allow data import in Speed Mode. You can enable this check item in Security Rules > SQL Change > Basic Configuration Items for the associated instance. Security Mode: Parses the file during the precheck phase and caches the SQL or CSV data. 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	The type of the file 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 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 have a header row (attributes) or contain only data.
Target Table	The target table for the data import.
Data Location	The location of the data: The First Row Is The Header: The first row of the table contains field names. The First Row Is Data: The first row of the table contains data.
Write Mode	The write mode for the file: INSERT: The database checks the primary key during data insertion. An error is reported if a duplicate key is found. 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 primary key or unique index value, the existing row is deleted and the new row is inserted. Note RDS for MySQL, PolarDB for MySQL, PolarDB-X, AnalyticDB for MySQL, and OceanBase databases support these three write modes. Other databases support only the `INSERT` mode.
Attachment	Click Upload File to upload an attachment. Note Supported file types: SQL, CSV, TXT, XLSX, and ZIP. The maximum file size is 5 GB.
Other Options (Optional)	Specifies whether to ignore errors. Do not ignore (cleared): This is the default setting. If an error occurs, the system stops executing subsequent SQL statements and reports an error. Ignore (selected): If an execution fails, the system ignores the error and continues to execute subsequent SQL statements.
Rollback SQL Statement (Optional)	If an SQL execution error or exception occurs, you can run a rollback SQL statement to undo the operation and restore the database to its state before the execution. You can provide the rollback SQL statement in one of the following ways: Text: Write the rollback SQL statement in the input box below. Attachment: Upload a rollback SQL file. Note Supported file types: SQL, TXT, and ZIP. The maximum file size is 15 MB.
Change Stakeholder (Optional)	The stakeholders of the change. All specified stakeholders can view the ticket and collaborate. Users who are not stakeholders cannot view the ticket, except for administrators and DBAs.
Ticket Attachment (Optional)	You can upload images or documents with relevant information to supplement the ticket.

Click Submit and wait for the precheck to complete.
The system prechecks the uploaded SQL. If the file is a CSV file, the system generates the corresponding INSERT statements.
Note
If an error is reported during the type check phase of the precheck, adjust the security rules associated with the instance based on the error message. For more information, see Data change.
In the Approval step, click Submit for Approval. In the Prompt message, click OK.
After the ticket is approved, in the Execute area, click Execute Change.
In the Task Settings dialog box, select an Execution Strategy for the ticket and click Confirm Execution.
- Execute Immediately: This is the default option.
- Scheduled Execution: Specify a custom start time for the ticket.
  Note
  - In the Execute area, you can view the task execution status, SQL check details, and scheduling logs.
  - After the task is complete, you can query the imported data in the SQL window of the target database.
Wait for the task to complete. When Task Execution Completed appears in the Complete area, the task is complete.