向表中添加数据。
Data can be inserted with this basic query format.
语法
INSERT INTO [TABLE] [tableIdentifier] [columnsClause] VALUES
示例
--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')
数据可以以 ByteHouse 支持的格式传递给 INSERT。
语法
INSERT INTO [TABLE] [tableIdentifier] [columnsClause] FORMAT format_name
示例
-- 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');
插入 SELECT 查询的结果。列按照它们在 SELECT 子句中的位置进行映射。然而,它们在 SELECT 表达式和 INSERT 表中的名称可能不同。如有必要,会进行类型转换。
语法
INSERT INTO [TABLE] [tableIdentifier] [columnsClause] [selectUnionStmt]
示例
INSERT INTO example_table SELECT * FROM example_table
从文件向表中插入数据。仅限于 gateway-client
支持的文件格式:
.csv
.json
.avro
.parquet
语法
INSERT INTO [TABLE] [tableIdentifier] [columnsClause] [FORMAT format_name] INFILE filepath
示例
INSERT INTO my_table FORMAT csvwithnames INFILE '/Users/my_name/Downloads/ETH-USD.csv'
Description
该语句的功能是重写表或表的某些分区
INSERT OVERWRITE TABLE table_name [ PARTITION (p1, ... | *) ] [ (COLUMN [, ...]) ] { VALUES ([, ...] ), ([, ...]) | QUERY }
单分区键的分区重写
INSERT OVERWRITE test.insert_overwrite_t PARTITION (1) SELECT * FROM test.insert_overwrite_s WHERE d = 1;
单分区键的分区VALUES重写
INSERT OVERWRITE test.insert_overwrite_t PARTITION (2) VALUES (2,6), (3,3);
单分区键的多分区重写
INSERT OVERWRITE test.insert_overwrite_t PARTITION (1,2) SELECT * FROM test.insert_overwrite_s;
多分区键的多分区重写
INSERT OVERWRITE test.insert_overwrite_t PARTITION (('1',1), ('2',2)) SELECT * FROM test.insert_overwrite_s;
多分区键指定列的导入重写
INSERT OVERWRITE test.insert_overwrite_t PARTITION (('1',1)) (s,d,n) SELECT s,d,n FROM test.insert_overwrite_s;
使用Partition ID的分区重写
INSERT OVERWRITE test.insert_overwrite_t PARTITION (ID '2f4b47c7cb10a88d637501e1a15cbb47') SELECT * FROM test.insert_overwrite_s;
使用Partition ID的多分区重写
INSERT OVERWRITE test.insert_overwrite_t PARTITION (ID '2f4b47c7cb10a88d637501e1a15cbb47', ID 'fe7805ebd452ed4c738bdbbab09c6617') SELECT * FROM test.insert_overwrite_s;
整表重写
INSERT OVERWRITE test.insert_overwrite_t SELECT * FROM test.insert_overwrite_s;