Data Change

Doris Data Change Rules

• Applications cannot directly use the delete or update statement to change data. You can use the upsert mode of the CDC to change data.
• You are not advised to frequently add or delete fields in tables during peak hours. You are advised to reserve fields to be used in the early stage of table creation. If fields must be added or deleted, or field types and comments must be modified, stop writing and modifying related tables during off-peak hours, and then re-create the tables.
  1. Create a table. The structure of the table is the same as that of the table whose fields need to be added, deleted, or modified. Add new fields to the new table, delete unnecessary fields, or modify fields whose types need to be changed.
  2. Select specified fields and insert them to the newly created table.

     INSERT INTONewly created table SELECTSpecified column FROM Existing table whose column needs to be modified;

     

     If the table contains a large amount of data, you can import the data to the new table in batches by time to reduce the instantaneous high CPU or MEM memory usage and affect the query service. 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. Therefore, you need to set the query timeout parameter query_timeout at the SQL or user level.

Doris Data Change Suggestions

When performing special large SQL operations, you can use a method similar to SELECT /*+ SET_VAR(query_timeout = xxx*/ from table to set session variables in hint mode. Do not set global system variables.