Data Management Service (DMS) provides the data import feature for you to import a large amount of data to a database at a time. This reduces the costs of labors and material resources.
Scenarios
- Initialize the data of applications.
- Import data from a platform in case of emergency and quickly roll back data.
Supported file types
- TXT files: The maximum size of a file that can be imported is 5 GB.
- SQL scripts: The maximum size of a file that can be imported is 5 GB. In the Secure Collaboration mode, only the INSERT and REPLACE statements are available by default. To execute other SQL statements, database administrators (DBAs) or DMS administrators can follow these steps to create security rules: In the top navigation bar of the DMS console, choose System Management > Security > Security Rules. On the Security Rules page, select the target rule set and click Edit in the Actions column. On the Details page that appears, click the SQL Correct tab. Set the Checkpoints parameter to Batch Data import rules and create security rules as needed.
- CSV files: The column delimiter must be the comma (,) and the column names must occupy the first row.
Supported database types
- MySQL
- OceanBase
- ApsaraDB for PolarDB-X
Important notes
- If you only need to change a small amount of data, we recommend that you submit a Normal Data Modify or Lock-Free Data Modify ticket. For more information, see Data change and Data change without locking tables.
- If you submit a Large Data Import ticket and execute SQL statements, the system does not change schemas without locking tables even though you select Open(DMS OnlineDDL first) for the instance.
- To avoid affecting the performance of the target database, we recommend that you use SQL statements with good performance, such as the
INSERT,UPDATE, andDELETEstatements. The UPDATE statement uses the primary key and unique index. - In the Secure Collaboration mode, only the
INSERTandREPLACEstatements are available by default. DBAs or DMS administrators can create security rules to execute other SQL statements.
Procedure
- Log on to the DMS console.
- In the top navigation bar, choose Data Plans > Data Import.
On the Data Import page that appears, submit a ticket. The following table describes the parameters in the ticket.
Parameter Description Reason Category Required. The reason for the data import. Business Background Required. The business background of the data import. Change Stakeholder Optional. The stakeholder of the data import. Database Required. The target database of the data import. If the database is in the Flexible Management or Stable Change mode, you must log on to the database before you select the database. File Encoding Required. The encoding format of the files to be imported. Valid values:
- UTF-8
- GBK
- ISO-8859-1
- Automatic IdentificationSQL Statements for Change Required. The executable SQL statements for importing data. Select SQL Script or CSV as needed, and upload the files to be imported. If you select CSV, enter the name of the target table in the field next to CSV. SQL Statements for Rollback Optional. The executable SQL statements for rolling back the data import.
- Text: Enter the rollback statements in the field.
- Attachment: Upload SQL files in the .txt or .sql format. The maximum size of a file can be 15 MB.Attachments Optional. The images or files to be uploaded for adding more information about the data import. Click Submit. Wait for the ticket to be prechecked. If the ticket fails the precheck, troubleshoot issues as prompted and submit the ticket again.
If you upload SQL files, the system automatically checks the SQL statements that you upload. If you upload CSV files, the system generates the corresponding INSERT statement.
After the ticket passes the precheck, click Submit for Approval in the Approval step. Wait for the ticket to be approved.
- After the ticket is approved, click Execute Change in the Execute step.
- In the Task Settings dialog box that appears, turn on or off the Execute Immediately switch as needed. If you turn off the Execute Immediately switch, select the execution time of the SQL statements for data import. Then, click Confirm Execution.
During execution, the system reads the parsed SQL statements and executes them in batches to import data to the target database. The size of SQL statements is 1 MB each batch.