All Products
Search
Document Center

Write, update, delete, and import data

Last Updated: Oct 18, 2019

Write multiple data entries at a time

You can use the INSERT INTO or REPLACE INTO statement to write multiple data entries to a table at a time. This can improve data write performance. Pay attention to the following suggestions:

  • You can use each INSERT INTO or REPLACE INTO statement to write more than 1,000 rows of data, but ensure that the total size of written data does not exceed 16 MB.

  • The latency when you write multiple data entries at a time is higher than that when you write a data entry.

  • If an error occurs, you need to retry to ensure that data is written. Then, you can use the primary key of the table to remove duplicate data generated due to the retry.

Update data

AnalyticDB for MySQL provides multiple methods for updating data. Pay attention to the following suggestions:

  • When data is updated at a high frequency, rows are overwritten based on the primary key, and the client can obtain all field information of the rows, you can use the REPLACE INTO statement to update multiple data entries at a time.

  • When data is updated at a low frequency and based on the primary key, you can use the REPLACE INTO or UPDATE statement to update a data entry.

  • When data is updated at a low frequency and based on any conditions, you can use the UPDATE statement to update data.

Delete data, a partition, or a table

AnalyticDB for MySQL provides multiple methods for deleting data. Pay attention to the following suggestions:

  • When data is deleted at a low frequency and based on the primary key, you can use the DELETE FROM WHERE PK='xxx' statement to delete data.

  • When data is deleted at a low frequency and based on any conditions, you can use the DELETE statement to delete data.

  • You can use the TRUNCATE TABLE db_name.table_name PARTITION partition_name statement to delete a specified subpartition.

  • You can use the TRUNCATE TABLE db_name.table_name statement to delete the data of a specified table, including all of its subpartitions.

Import multiple data entries at a time and import data in real time

  • Import multiple data entries at a time: applicable when you import large amounts of data. You can query old data during the import process. After data is imported, you can use the imported new data to replace old data with one click. When the import fails and only part of new data is imported, the system automatically discards the imported new data and rolls back to old data. In this case, you can still query old data.

    For example, we recommend that you use the INSERT OVERWRITE INTO SELECT statement to import multiple data entries at a time from MaxCompute or Object Storage Service (OSS) to AnalyticDB for MySQL. For more information, see Import OSS data to AnalyticDB for MySQL.

    Note: When you import multiple data entries from a table, import tasks are run in sequence in the system. When you import multiple data entries from multiple tables, import tasks are run in parallel in the system. By default, two import tasks are run in parallel.

    For example, when you import data from MaxCompute to AnalyticDB for MySQL, data in different partitions of a table in MaxCompute is queued and imported in sequence in the system.

  • Import data in real time: applicable when you import a small amount of data, such as millions of data entries in a table.

    For example, we recommend that you use the INSERT INTO SELECT FROM statement to import data from ApsaraDB RDS for MySQL or MySQL deployed on Elastic Compute Service (ECS) instances to AnalyticDB for MySQL. For more information, see Import MySQL data to AnalyticDB for MySQL.

Note: When you import data to AnalyticDB for MySQL, import tasks use the resources in AnalyticDB for MySQL. If data needs to be imported during a data query, we recommend that you import data when the queries per second (QPS) is low.