Data Change

Doris Data Change Rules

• Do not directly use the delete or update statement to change data. Instead, use the upsert of the CDC.
• Avoid frequently adding or deleting fields in tables during peak hours. Reserve fields for future use when you create tables. If fields must be added or deleted, or field types and comments must be modified, stop writing and modifying tasks on the target table during off-peak hours and then re-create a table.
  1. Create a table. The structure of the table is the same as that of the table you want to modify. Add new fields to the new table, delete unnecessary fields, or change field types.
  2. Specify fields and insert them to the newly created table.

     INSERT INTONewly created table SELECTSpecified fields FROM Existing table whose columns need to be modified;

     

     To prevent high CPU or memory usage and minimize the impact on query service, you can import data to a new table in batches based on time if the table has a significant amount of data. The command is as follows:

     insert into tab1 select col from tab where date <= xx;

  3. Exchange the names of the two tables. For more information, see Exchange Tables.

     ALTER TABLE [db.]tbl1 REPLACE WITH TABLE tbl2 [PROPERTIES('swap' = 'true')];

• Some queries may take a long time and consume a lot of memory and CPU resources. You can set the query timeout parameter query_timeout that works on SQL statements or for a user.

Doris Data Change Suggestions

When you run large SQL statements, set session variables with hint by using a method similar to SELECT /*+ SET_VAR(query_timeout = xxx*/ from table. Do not set global system variables.