6 开发者指南-6.8 SQL参考-6.8.3 数据操作语言(DML)-TDH Community Edition-星环科技

INSERT

使用 INSERT 命令执行数据插入操作，按照插入数据的场景，所涉及的 SQL 语句，主要包含插入单条数据、批量插入多条数据、子查询插入数据、插入数据至分区表。

只有 Holodesk 和 Hyperbase/ES/Transaction Orc 等事务表可以使用 INSERT VALUES 或 BATCHINSERT BATCHVALUES 语句插入数据，其他类型表只能使用 INSERT SELECT 子查询语句插入数据。

注：HOLODESK存储格式仅社区订阅版及其他商业版支持

INSERT INTO VALUES 插入单条数据

功能描述

使用 INSERT INTO VALUES 实现单表单条数据的写入，一般适用于少量数据的手动写入场景。

语法格式

INSERT INTO <table_name> VALUES(<value1>, <value2>,...);复制

参数说明

<table_name>：表名称
<value>：插入数据单个字段值

示例

插入单条数据

INSERT INTO t1 VALUES(2, 1, '2014-2-1', 'discount', 'NO20140201', 10.00,10);复制

推荐阅读

批量插入多条数据

功能描述

使用 BATCHINSERT 或 INSERT 语法手动实现批量插入功能

注意事项

目前支持百列千行级别的 BATCHINSERT

语法格式

INSERT语法

INSERT INTO <table_name>
[PARTITION (<partition_key>=<value>...) --[1]
[IF NOT EXISTS]] --[2]
VALUES (<value1>, <value2>, <value3>,...), (<value1>, <value2>, <value3>,...), (<value1>, <value2>, <value3>,...),...;复制

BATCHINSERT语法

BATCHINSERT INTO <table_name>
[PARTITION (<partition_key>=<value>...) --[1]
[IF NOT EXISTS]] --[2]
BATCHVALUES (VALUES (<value1>, <value2>, <value3>,...),
VALUES(<value1>, <value2>, <value3>,...),
VALUES (<value1>, <value2>, <value3>,...),
...);复制

参数说明

<table_name>：表名称。
<column_name>：列名。
<partition_key>：分区键。
<value>：分区对应取值。
可选项[1]，可以将数据插入分区表的指定分区中。
可选项[2]，如果 <partition_key> 指定的分区键已经存在，Inceptor 会报错，加上 IF NOT EXISTS 选项则可以让 Inceptor 不报错。

示例

批量写入

-- BATCHINSERT 语法
BATCHINSERT INTO t1 BATCHVALUES (
VALUES (3, 1, '2014-2-1', 'discount', 'NO20140201', 10.00,10),
VALUES(3, 2, '2023-2-28', 'online', 'NO20230228', 12.11,10),
VALUES (3, 3, '2019-10-1', 'wholesale', 'NO20191001', 8.99,1000));复制

-- INSERT 语法
INSERT INTO t1 VALUES(4, 1, '2022-2-1', 'online', 'NO20220201', 10.24,11),(4, 2, '2023-2-11', 'shop', 'NO20230211', 15.50,22),(4, 3, '2022-2-22', 'wholesale', 'NO20220222', 9.19,33);复制

推荐阅读

子查询插入数据

功能描述

使用 INSERT SELECT 将子查询结果数据从源表写入目标表中。

支持使用一个数据源将多个查询结果分别插入不同表中。

语法格式

INSERT INTO <table_name1>
[PARTITION (<partition_key>=<value>...) --[1]
[IF NOT EXISTS]] --[2]
(<column_name1>, <column_name1>,...)
SELECT <select_statement> FROM <source>;复制

参数说明

<table_name>：表名称。
<column_name>：列名。
<select_statement>：子查询语句结果。其中的列需要和 <table_name> 的列数量一致且数据类型一一对应。
<source>：数据源。
<partition_key>：分区键。
<value>：分区对应取值。
可选项[1]，可以将数据插入分区表的指定分区中。
可选项[2]，如果 <partition_key> 指定的存在，Inceptor 会报错，加上 IF NOT EXISTS 选项避免报错。

示例

子查询插入数据

INSERT INTO t8_sub_t1 select * from t1;复制

推荐阅读

插入数据至分区表

按照分区表中的分区键值为固定值或是一个区间，本节分为数据写入单值分区表和数据写入范围分区表。
按照分区表中的分区键的值是否确定，分区表还可以分为静态分区表、动态分区表。此处仅展示静态分区插入功能，动态分区插入功能请查看动态分区章节。
分区表中插入数据适用场景为：
- 表的大小超过 2GB。
- 表中包含历史数据，新的数据被增加到新的分区中。

功能描述

使用 INSERT INTO TABLE PARTITION 的方式将数据插入指定的分区中
支持手动插入的方式将数据写入进静态分区表中，只需要注意写入的数据符合与分区表的分区列相对应。

注意事项

在插入数据时，需手动指定分区。
在向单值静态分区表中插入数据时，由于已经指定了分区键的具体值，所以不需要查询分区列插入。

但在向范围静态分区表中插入数据时，由于并没有指定分区列的具体值，所以需要查询分区列插入，并且分区列应放置在最后且该值需要处于范围分区以内。

语法格式

数据写入单值静态分区表

INSERT INTO TABLE <table_name>
PARTITION (<partition_key1> = <value>[, <partition_key2> = <value>, ...])
SELECT <select_statement> FROM <source>;
[source,sql]复制

数据写入范围静态分区表

INSERT INTO TABLE <table_name>
PARTITION <partition_name>
SELECT <select_statement> FROM <source> WHERE <filter_statement>复制

参数说明

<table_name>：表名称。
<select_statement>：子查询语句结果，其中的列需要和 <table_name> 的列数量一致且数据类型一一对应。
<partition_key>：分区键。
<partition_name>：范围分区中的指定某个分区名称。
<value>：对应取值。
<filter_statement>：过滤条件语句，即需要过滤符合分区范围的值。

示例

数据写入单值静态分区

--添加单值分区
ALTER TABLE t3_partition ADD PARTITION (trans_time= '2014-2-11');
ALTER TABLE t3_partition ADD PARTITION (trans_time= '2014-04-30');


-- 子查询写入
INSERT INTO TABLE t3_partition
PARTITION (trans_time = '2014-2-11')
SELECT t1.trans_id, t1.acc_num, t1.trans_type, t1.stock_id, t1.price, t1.amount FROM t1;

-- 手动写入单条数据
INSERT INTO t3_partition
PARTITION (trans_time = '2014-04-30')
VALUES (5, 1, 'discount', 'NO20140201', 10.00,10);

--手动批量写入
INSERT INTO t3_partition
PARTITION (trans_time = '2014-04-30')
VALUES(6, 1, 'sales', 'NO20140201', 10.00,10), (6, 2, 'sales', 'NO20230228', 12.11,10),(6, 3, 'sales', 'NO20191001', 8.99,1000);复制

数据写入范围静态分区

--添加范围分区
ALTER TABLE t4_range_partition ADD PARTITION before2014_05 VALUES LESS THAN ('2014-05-30');

--子查询写入
INSERT INTO TABLE t4_range_partition
PARTITION before2014_05
SELECT t1.trans_id, t1.acc_num, t1.trans_type, t1.stock_id, t1.price, t1.amount, t1.trans_time FROM t1 WHERE t1.trans_time >= '2014-04-30' and t1.trans_time < '2014-05-30';

-- 手动写入单条数据
INSERT INTO t4_range_partition
PARTITION before2014_05
VALUES (3, 1, 'discount', 'NO20140201', 10.00,10, '2014-5-3');

--手动批量写入
INSERT INTO t3_partition
PARTITION before2014_05
VALUES(4, 1, 'sales', 'NO20140201', 10.00,10, '2014-5-4'), (4, 2, 'sales', 'NO20230228', 12.11,10, '2014-5-5'),(6, 3, 'sales', 'NO20191001', 8.99,1000, '2014-5-6');复制

推荐阅读

UPDATE

功能描述

使用 UPDATE TABLE 修改指定表指定列中的数据。
本产品既支持对单列数据进行修改，也可以使用查询语句对多列数据批量修改。

注意事项

如果需要对分区表中的数据进行修改时，需要修改参数 hive.crud.dynamic.partition=true（默认 false）。

语法格式

修改单列的值

UPDATE <table_name>
[PARTITION <partition_name>] --[1]
[PARTITION (<partition_key> = <value>] --[2]
SET <column_name> = <value>
[PARTITION <partition_name>]
[PARTITION (<partition_key> = <value>]
[WHERE <filter_statement>];复制

修改多列的值

UPDATE  <table_name>
[PARTITION <partition_name>]
[PARTITION (<partition_key> = <value>]
SET (<column_name1>, <column_name2>, ...) = (SELECT <select_statement>)
[WHERE <filter_statement>];复制

参数说明

<table_name>：表名称。
<column_name>：列名。
<select_statement>：子查询语句结果。其中的列需要和 <table_name> 的列数量一致且数据类型一一对应。
<filter_statement>：过滤条件语句。
<partition_name>：范围分区表中的指定分区的名称。
<partition_key>：分区键。
<value>:分区键对应值。
可选项[1]，可以对范围分区表的指定分区中的数据进行修改。
可选项[2]，可以对单值分区表的指定分区中的数据进行修改。

示例

修改单列数据

--普通表
UPDATE TABLE t1  SET trans_type = 'update';

--单值分区表
UPDATE t3_partition partition (trans_time='2014-2-11') SET trans_id=7 WHERE amount= 10;

--范围分区表
UPDATE t4_range_partition partition before2014_05 SET trans_id=7 WHERE amount= 10;复制

修改多列数据

--普通表
UPDATE t1 SET trans_id=10, amount=1000 || 'changed' WHERE trans_date > '2014-2';

--范围分区表
UPDATE t4_range_partition partition before2014_05 SET trans_id=9, trans_type=  trans_type|| ' changed' WHERE amount= 10;

--单值分区表
UPDATE t3_partition partition (trans_time='2014-2-11') SET trans_id=8, trans_type=  trans_type|| ' changed' WHERE amount= 10;复制

推荐阅读

MERGE INTO

功能描述

对一个表同时执行 INSERT 和 UPDATE 操作。MERGE 命令从一个或多个数据源中选择行来 UPDATE 或 INSERT 到一个或多个表。
MERGE INTO 的作用就是解决用 B 表更新 A 表的数据。如果A表中没有，则把 B 表的数据插入 A 表或向一个表中插入数据，如果该表已有该数据则更新，反之新增数据。

注意事项

MERGE 的源表必须有化名。
两表连接判定条件 <merge_condition> 的对应关系，必须为一一对应。
UPDATE 和 INSERT 子句是可选的，也可以加 WHERE 子句。
在 ON 条件中使用常量过滤谓词来 INSERT 所有的行到目标表中,不需要连接源表和目标表。
UPDATE 子句后面可以跟 DELETE 子句来去除一些不需要的行。
MERGE INTO 中所有的 UPDATE、INSERT、DELETE 都是针对目标表来操作的。由于 MERGE INTO 已经指定了操作的表，所以 UPDATE、INSERT、DELETE 都不需要再显示指定表名。

语法格式

-- 非分区表
MERGE INTO <table_name1> [table_rename1] --[1]
USING [table|view|subquery] <table_name2> <table_reanme2> --[2]
ON (<merge_condition>)
WHEN MATCHED THEN <update_statement>
WHEN NOT MATCHED THEN <insert_statement>;

-- 单值分区表
MERGE INTO <table_name1>
PARTITION (<partition_key> = <value>)
USING [table|view|subquery] <table_name> <table_reanme>
ON (<merge_condition>)
WHEN MATCHED THEN <update_statement>
WHEN NOT MATCHED THEN <insert_statement>;

-- 范围分区表
MERGE INTO <table_name>
partition <patition_name>
USING [table|view|subquery] <table_name> <table_reanme>
ON (<merge_condition>)
WHEN MATCHED THEN <update_statement>
WHEN NOT MATCHED THEN <insert_statement>;复制

参数说明

<table_name1>：目标表名称。
<table_name2>：源表名称。
<table_reanme>：必须需对源表指定别名。
<patition_name>：范围分区分区名称。
<partition_key>：分区键。
<value>：分区键对应值。
<merge_condition>：两表连接判定条件，需要注意此处必须为一一对应。
<update_statement>：更新目标表数据语句。
<insert_statement>：目标表插入数据语句。
可选项[1]，目标表也可以指定别名。
可选项[2]，选择源数据类型为表，视图或是子查询语句。

示例

插入并更新非分区表

--创建源表并插入数据
CREATE TABLE t11_source(trans_id INT, acc_num INT,  trans_type STRING, stock_id STRING, price DECIMAL, amount INT, trans_time DATE)
STORED AS ORC_TRANSACTION;
BATCHINSERT INTO t11_source BATCHVALUES (VALUES (7, 1, 'discount', 'NO2019098', 10.00,10,'2014-04-30'),VALUES(7, 2, 'online', 'NO20237889', 12.11,10,'2014-04-30'),VALUES (7, 3, 'wholesale', 'NO20140430', 8.99,1000,'2014-04-30'));

-- merge into 普通表
MERGE INTO t8_sub_t1 target
USING t1 source ON (target.stock_id=source.stock_id)
WHEN matched THEN UPDATE SET target.trans_type = target.trans_type || ' MERGE update'
WHEN NOT MATCHED THEN INSERT (trans_id, acc_num, trans_type, stock_id, price, amount) VALUES (source.trans_id, source.acc_num, source.trans_type || ' MERGE update', source.stock_id, source.price, source.amount);复制

插入并更新单值分区表

MERGE INTO t3_partition PARTITION (trans_time='2014-2-11') target
USING t11_source source ON (target.stock_id=source.stock_id)
WHEN matched THEN UPDATE SET target.trans_type = target.trans_type || ' MERGE update'
WHEN NOT matched THEN INSERT (trans_id, acc_num, trans_type, stock_id, price, amount) VALUES (source.trans_id, source.acc_num, source.trans_type || ' MERGE update', source.stock_id, source.price, source.amount);复制

插入并更新范围分区表

MERGE INTO t4_range_partition PARTITION before2014_05 target
USING t11_source source ON (target.stock_id=source.stock_id)
WHEN matched THEN UPDATE SET target.trans_type = target.trans_type || ' MERGE update'
WHEN NOT matched THEN INSERT (trans_id, acc_num, trans_type, stock_id, price, amount, trans_time) VALUES (source.trans_id, source.acc_num, source.trans_type || ' MERGE update', source.stock_id, source.price, source.amount, source.trans_time);复制

推荐阅读

DELETE

功能描述

使用 DELETE FROM WHERE 删除指定表中的对应数据。

语法格式

-- 非分区表
DELETE FROM <table_name> WHERE <filter_statement>;

-- 删除单值分区表中一个分区的部分内容
DELETE FROM <table_name> partition (<partition_key> = <value>) WHERE <filter_statement>;

-- 删除范围分区表中一个分区的部分内容
DELETE FROM <table_name> partition <partition_name> WHERE <filter_statement>;复制

参数说明

<table_name>：表名称。
<column_name>：列名。
<partition_key>：分区键。
<partition_name>：范围分区中的指定某个分区名称。
<filter_statement>：过滤条件语句。
<value>：分区对应取值。

示例

删除数据

-- 非分区表
DELETE FROM t1 WHERE acc_num >= 2;

-- 删除单值分区表中一个分区的部分内容
DELETE FROM t3_partition PARTITION (trans_time = '2014-04-30')
WHERE trans_id = 2;

-- 删除范围分区表中一个分区的部分内容
DELETE FROM t4_range_partition PARTITION before2014_04 WHERE trans_id != 2;复制

推荐阅读

大数据基础平台

大数据云平台

数据库

数据开发工具

智能分析工具

AI基础设施平台

软硬一体产品

金融解决方案

交通解决方案

医疗解决方案

教育解决方案

能源解决方案

制造业解决方案

政府解决方案

运营商解决方案

INSERT

INSERT INTO VALUES 插入单条数据

批量插入多条数据

子查询插入数据

插入数据至分区表

UPDATE

MERGE INTO

DELETE

咨询热线