Insert

Scenarios

• To import a few test records, use the INSERT INTO VALUES statement.

• To transform data from a source table using extract, transform, and load (ETL) and import it into a new StarRocks table, use the INSERT INTO SELECT statement.

• To overwrite a few test records, use the INSERT OVERWRITE VALUES statement.

• To transform data from a source table using ETL and overwrite data in a new StarRocks table, use the INSERT OVERWRITE SELECT statement.

Considerations

• By default, StarRocks fails an INSERT operation if any data does not match the target table format—for example, if a string exceeds the allowed length. To filter out invalid data and continue the INSERT operation, set the session variable enable_insert_strict to false.

• Frequently using INSERT statements to import small batches of data creates too many data versions, which degrades query performance. Do not use INSERT statements frequently for data import or as a routine daily import task in a production environment. If your scenario requires streaming or frequent small-batch imports, use Stream Load or Routine Load.

• When you run an INSERT OVERWRITE statement, the system creates temporary partitions for the target partitions, writes data into them, and then atomically replaces the target partitions with the temporary ones. All these steps run on the Leader FE node. If the Leader FE node breaks down during this process, the INSERT OVERWRITE operation fails, and the system deletes all temporary partitions created during the operation.

Syntax

INSERT { INTO | OVERWRITE } [db_name.]<table_name>
[ PARTITION (<partition_name> [, ...] ) ]
[ TEMPORARY PARTITION (<temporary_partition_name> [, ...] ) ]
[ WITH LABEL <label>]
[ (<column_name>[, ...]) ]
{ VALUES ( { <expression> | DEFAULT } [, ...] ) | <query> }

The following table describes the parameters.

Related configurations

You can configure the following settings for INSERT import tasks.

FE configuration

Session variables

Preparations

Check permissions

The INSERT operation requires INSERT permission on the target table. If your user account lacks this permission, grant it using GRANT. The syntax is GRANT INSERT ON TABLE <table_name> IN DATABASE <database_name> TO { ROLE <role_name> | USER <user_identity>}.

Create database and tables

Create a database named load_test in StarRocks, and create two tables inside it: the target table insert_wiki_edit and the source table source_wiki_edit.

CREATE DATABASE IF NOT EXISTS load_test;
USE load_test;
CREATE TABLE insert_wiki_edit
(
    event_time      DATETIME,
    channel         VARCHAR(32)      DEFAULT '',
    user            VARCHAR(128)     DEFAULT '',
    is_anonymous    TINYINT          DEFAULT '0',
    is_minor        TINYINT          DEFAULT '0',
    is_new          TINYINT          DEFAULT '0',
    is_robot        TINYINT          DEFAULT '0',
    is_unpatrolled  TINYINT          DEFAULT '0',
    delta           INT              DEFAULT '0',
    added           INT              DEFAULT '0',
    deleted         INT              DEFAULT '0'
)
DUPLICATE KEY(
    event_time,
    channel,
    user,
    is_anonymous,
    is_minor,
    is_new,
    is_robot,
    is_unpatrolled
)
PARTITION BY RANGE(event_time)(
    PARTITION p06 VALUES LESS THAN ('2015-09-12 06:00:00'),
    PARTITION p12 VALUES LESS THAN ('2015-09-12 12:00:00'),
    PARTITION p18 VALUES LESS THAN ('2015-09-12 18:00:00'),
    PARTITION p24 VALUES LESS THAN ('2015-09-13 00:00:00')
)
DISTRIBUTED BY HASH(user);
CREATE TABLE source_wiki_edit
(
    event_time      DATETIME,
    channel         VARCHAR(32)      DEFAULT '',
    user            VARCHAR(128)     DEFAULT '',
    is_anonymous    TINYINT          DEFAULT '0',
    is_minor        TINYINT          DEFAULT '0',
    is_new          TINYINT          DEFAULT '0',
    is_robot        TINYINT          DEFAULT '0',
    is_unpatrolled  TINYINT          DEFAULT '0',
    delta           INT              DEFAULT '0',
    added           INT              DEFAULT '0',
    deleted         INT              DEFAULT '0'
)
DUPLICATE KEY(
    event_time,
    channel,user,
    is_anonymous,
    is_minor,
    is_new,
    is_robot,
    is_unpatrolled
)
PARTITION BY RANGE(event_time)(
    PARTITION p06 VALUES LESS THAN ('2015-09-12 06:00:00'),
    PARTITION p12 VALUES LESS THAN ('2015-09-12 12:00:00'),
    PARTITION p18 VALUES LESS THAN ('2015-09-12 18:00:00'),
    PARTITION p24 VALUES LESS THAN ('2015-09-13 00:00:00')
)
DISTRIBUTED BY HASH(user);

Import using INSERT INTO VALUES

Use the INSERT INTO VALUES statement to directly import data into a specified table. Separate multiple rows with commas (,). For details, see INSERTINSERT.

The following example uses the label insert_load_wikipedia to import two rows into the source table source_wiki_edit.

INSERT INTO load_test.source_wiki_edit
WITH LABEL insert_load_wikipedia
VALUES
    ("2015-09-12 00:00:00","#en.wikipedia","AustinFF",0,0,0,0,0,21,5,0),
    ("2015-09-12 00:00:00","#ca.wikipedia","helloSR",0,1,0,1,0,3,23,0);

Import using INSERT INTO SELECT

Use the INSERT INTO SELECT statement to transform data from a source table using ETL and import it into an internal StarRocks table. The source table can be one or more internal or foreign tables. The target table must be an internal StarRocks table. After running this statement, the system imports the results of the SELECT query into the target table.

• The following example uses the label insert_load_wikipedia_1 to import data from the source table into the target table.

  INSERT INTO load_test.insert_wiki_edit
  WITH LABEL insert_load_wikipedia_1
  SELECT * FROM load_test.source_wiki_edit;

• The following example uses the label insert_load_wikipedia_2 to import data into partitions p06 and p12 of the target table. If you do not specify target partitions, data imports into the entire table. If you specify partitions, data imports only into those partitions.

  INSERT INTO load_test.insert_wiki_edit PARTITION(p06, p12)
  WITH LABEL insert_load_wikipedia_2
  SELECT * FROM load_test.source_wiki_edit;

  If you clear partitions p06 and p12, previously inserted data in those partitions disappears.

  TRUNCATE TABLE load_test.insert_wiki_edit PARTITION(p06, p12);
  SELECT * FROM load_test.insert_wiki_edit;

• The following example uses the label insert_load_wikipedia_3 to import only the event_time and channel columns from the source table into the corresponding columns of the target table. Other columns receive their default values.

  INSERT INTO load_test.insert_wiki_edit
  WITH LABEL insert_load_wikipedia_3 
  (
      event_time, 
      channel
  )
  SELECT event_time, channel FROM load_test.source_wiki_edit;

Overwrite using INSERT OVERWRITE VALUES

Use the INSERT OVERWRITE VALUES statement to overwrite data in a specified table. Separate multiple rows with commas (,). For details, see INSERTINSERT.

Query the source table to confirm existing data.

SELECT * FROM load_test.source_wiki_edit;

The system returns the following result.

The result contains two records: the first has event_time 2015-09-12, channel #ca.wikipedia, user helloSR, and delta/added/deleted values of 3/23/0; the second has event_time 2015-09-12, channel #en.wikipedia, user AustinFF, and delta/added/deleted values of 21/5/0.

The following example uses the label insert_load_wikipedia_ow to overwrite two rows in the source table source_wiki_edit.

INSERT OVERWRITE load_test.source_wiki_edit
WITH LABEL insert_load_wikipedia_ow
VALUES
    ("2015-09-12 00:00:00","#cn.wikipedia","GELongstreet",0,0,0,0,0,36,36,0),
    ("2015-09-12 00:00:00","#fr.wikipedia","PereBot",0,1,0,1,0,17,17,0);

Query the source table again. The result shows the data was successfully overwritten. The new result contains two records: the first has event_time 2015-09-12 00:00:00, channel #cn.wikipedia, user GELongstreet, and delta/added/deleted values of 36/36/0; the second has event_time 2015-09-12 00:00:00, channel #fr.wikipedia, user PereBot, and delta/added/deleted values of 17/17/0.

Overwrite using INSERT OVERWRITE SELECT

Use the INSERT OVERWRITE SELECT statement to overwrite data in the target table with transformed data from the source table. This statement performs ETL on data from the source table and overwrites data in an internal StarRocks table. The source table can be one or more internal or foreign tables. The target table must be an internal StarRocks table. After running this statement, the system overwrites the target table with the results of the SELECT query. For details, see INSERTINSERT.

• The following example uses the label insert_load_wikipedia_ow_1 to overwrite the target table with data from the source table.

  INSERT OVERWRITE load_test.insert_wiki_edit
  WITH LABEL insert_load_wikipedia_ow_1
  SELECT * FROM load_test.source_wiki_edit;

• The following example uses the label insert_load_wikipedia_ow_2 to overwrite partitions p06 and p12 of the target table. If you do not specify target partitions, data overwrites the entire table. If you specify partitions, data overwrites only those partitions.

  INSERT OVERWRITE load_test.insert_wiki_edit PARTITION(p06, p12)
  WITH LABEL insert_load_wikipedia_ow_2
  SELECT * FROM load_test.source_wiki_edit;

  If you clear partitions p06 and p12, previously inserted data in those partitions disappears.

  TRUNCATE TABLE load_test.insert_wiki_edit PARTITION(p06, p12);
  SELECT * FROM load_test.insert_wiki_edit;

  Note

  For tables partitioned by list expressions (PARTITION BY column), INSERT OVERWRITE can create new partitions in the target table by specifying partition key values. Existing partitions are overwritten normally.

  The following example creates a partitioned table activity and automatically creates a new partition during data import:

  CREATE TABLE load_test.activity (
  id INT          NOT NULL,
  dt VARCHAR(10)  NOT NULL
  ) ENGINE=OLAP 
  DUPLICATE KEY(`id`)
  PARTITION BY (`id`, `dt`)
  DISTRIBUTED BY HASH(`id`);
  INSERT OVERWRITE load_test.activity
  PARTITION(id='4', dt='2022-01-01')
  WITH LABEL insert_activity_auto_partition
  VALUES ('4', '2022-01-01');

• The following example uses the label insert_load_wikipedia_ow_3 to overwrite only the event_time and channel columns in the target table with data from the source table. Other columns receive their default values.

  INSERT OVERWRITE load_test.insert_wiki_edit
  WITH LABEL insert_load_wikipedia_ow_3 
  (
      event_time, 
      channel
  )
  SELECT event_time, channel FROM source_wiki_edit;

View import tasks

Use one of the following methods to view import tasks.

• Method 1: View import tasks in EMR StarRocks Manager. For details, see View import task details.

• Method 2: View import progress using the loads view in the StarRocks Information Schema database. This feature is available starting from version 3.1.

  SELECT * FROM information_schema.loads;

  For details about fields in the loads view, see loads.

  If you submitted multiple import tasks, filter by LABEL to find a specific task. Example:

  SELECT * FROM information_schema.loads WHERE LABEL = 'insert_load_wikipedia_ow';

  In the result, the STATE field shows the task status.

  • If the state is CANCELLED, check the ERROR_MSG field to identify the cause.

  • If the state is FINISHED, the task succeeded.