Add to Data in the table.

INSERT INTO #

You can use the basic INSERT INTO statement to write Data.

Syntax

• General syntax

  INSERT INTO [TABLE] [tableIdentifier] [columnsClause] VALUES
  

• settings for writing Data Format
  Data can be passed to INSERT At a Format supported by ByteHouse.

  INSERT INTO [TABLE] [tableIdentifier] [columnsClause] FORMAT format_name
  

Example

• Example 1: General write

  --step1: create a tableCREATE TABLE 
  example_table 
  ( 
      a Int8, 
      b String, 
      c Date 
  ) 
  ENGINE = CnchMergeTree 
  ORDER BY (a) 
  --step2: insert 2 rows into the table 
  INSERT INTO example_table VALUES (1, 'a', '2021-07-27'), (2, 'b', '2021-07-27')
  

• Example 2: settings for writing Data Format

  -- step 1: create a table 
  CREATE TABLE `example_table` 
  ( 
      `a` Int8, 
      `b` String, 
      `c` Date 
  ) 
  ENGINE = `CnchMergeTree` 
  ORDER BY (`a`) 
  
  -- step 2: insert 2 rows in value format into the table 
  INSERT INTO example_table FORMAT VALUES (1, 'a', '2021-07-27'), (2, 'b', '2021-07-27');
  

INSERT SELECT #

Add to As a result of a SELECT Search. Columns are mapped Accoding to their Location in the SELECT clause. However, their Name in the SELECT Expression and the INSERT table may be different. Type conversion is Carry on if necessary.
Syntax

INSERT INTO [TABLE] [tableIdentifier] [columnsClause] [selectUnionStmt]

Example

INSERT INTO example_table SELECT * FROM example_table

INSERT INFILE #

Add to Data from a File into the table. Only available for gateway-Client
Supported File format:

• .csv
• .json
• .avro
• .parquet

Syntax

INSERT INTO [TABLE] [tableIdentifier] [columnsClause] [FORMAT format_name] INFILE filepath

Example

INSERT INTO my_table FORMAT csvwithnames INFILE '/Users/my_name/Downloads/ETH-USD.csv'

INSERT OVERWRITE #

This statement's Function is to overwrite the table or certain Partition of the table.

• Overwrite is an atomic Actions
• Auto filtering is applied to Import Data that is Not as expected for the specified Partition when replacing Partition

Syntax

INSERT OVERWRITE TABLE table_name 
[ PARTITION (p1, ... | *) ] 
[ (COLUMN [, ...]) ] 
{ VALUES ([, ...] ), ([, ...]) | QUERY }

Param

• table_name: Goal Table name to be overwritten
• PARTITION: Partition to be overwritten
  • Can be Partition id or Partition tuple
  • Can be one or more, separated by commas
  • Brackets are required for the outermost layer
  • If the Partition Field is not specified, For the entire table overwrite
• COLUMN: Column(s) to be written to the Goal table
  • Can be one or more, separated by commas
  • Brackets are required for the outermost layer
• VALUES: List of Value to be written
• QUERY: Search query to be written

Example
Partition overwrite with a single Partition Key

INSERT OVERWRITE test.insert_overwrite_t PARTITION (1) SELECT * FROM test.insert_overwrite_s WHERE d = 1;

Partition VALUES overwrite with a single Partition Key

INSERT OVERWRITE test.insert_overwrite_t PARTITION (2) VALUES (2,6), (3,3);

Multiple Partition overwrite with a single Partition Key

INSERT OVERWRITE test.insert_overwrite_t PARTITION (1,2) SELECT * FROM test.insert_overwrite_s;

Multiple Partition overwrite with multiple Partition Key

INSERT OVERWRITE test.insert_overwrite_t PARTITION (('1',1), ('2',2)) SELECT * FROM test.insert_overwrite_s;

Import overwrite for specified Column with multiple Partition Key

INSERT OVERWRITE test.insert_overwrite_t PARTITION (('1',1)) (s,d,n) SELECT s,d,n FROM test.insert_overwrite_s;

Partition overwrite using Partition ID

INSERT OVERWRITE test.insert_overwrite_t PARTITION (ID '2f4b47c7cb10a88d637501e1a15cbb47') SELECT * FROM test.insert_overwrite_s;

Multiple Partition overwrite using Partition ID

INSERT OVERWRITE test.insert_overwrite_t PARTITION (ID '2f4b47c7cb10a88d637501e1a15cbb47', ID 'fe7805ebd452ed4c738bdbbab09c6617') SELECT * FROM test.insert_overwrite_s;

Overwrite the entire table

INSERT OVERWRITE test.insert_overwrite_t SELECT * FROM test.insert_overwrite_s;

INSERT THROW #

Unique Key table Support project insert throw semantics, which checks whether the unique Key already exists. If it does, an error is reported when writing.

-- example table 
CREATE TABLE mysql_test( 
    `a` Int32, 
    `b` Nullable(Int32) 
) 
ENGINE = CnchMergeTree 
ORDER BY a 
UNIQUE KEY a 

:) insert into mysql_test values (1, 1); 
:) select * from mysql_test; 
┌─a─┬─b─┐ 
│ 1 │ 1 │ 
└───┴───┘ 

:) insert into mysql_test FORMAT Values SETTINGS dedup_key_mode='throw' (1,1); 

DB::Exception: Found duplication when insert with setting dedup_key_mode=DedupKeyMode::THROW

INSERT IGNORE #

Unique Key table Support project insert ignore semantics. If the unique Key already exists, the newly written Data is ignored.

-- example table 
CREATE TABLE mysql_test( 
    `a` Int32, 
    `b` Nullable(Int32) 
) 
ENGINE = CnchMergeTree 
ORDER BY a 
UNIQUE KEY a 

:) insert into mysql_test values (1, 1); 
:) select * from mysql_test; 
┌─a─┬─b─┐ 
│ 1 │ 1 │ 
└───┴───┘ 

:) insert ignore into mysql_test values (1,2); 
:) select * from mysql_test; 
┌─a─┬─b─┐ 
│ 1 │ 1 │ 
└───┴───┘ 
1 rows in set.