如何通過value操作向資料量小的表中插入資料 - MaxCompute

如果需要向表中插入少量資料，您可以通過INSERT … VALUES或VALUES TABLE操作向資料量小的表中插入資料。

執行INSERT INTO操作前需要具備目標表的修改許可權（Alter）及源表的元資訊讀取許可權（Describe）。授權操作請參見MaxCompute許可權。

本文中的命令您可以在如下工具平台執行：

功能介紹

MaxCompute支援您通過INSERT … VALUES或VALUES TABLE操作向表中插入少量資料。

功能

說明

INSERT … VALUES

在業務測試階段，您可以通過INSERT … VALUES操作向表中插入資料執行簡單測試：

如果插入幾條或十幾條資料，您可以通過INSERT … VALUES語句快速向測試表中寫入資料。
如果插入幾十條資料，您可以通過Tunnel上傳一個TXT或CSV格式的資料檔案匯入資料，詳情請參見匯入資料。

VALUES TABLE

如果您需要對插入的資料進行簡單的運算，推薦使用MaxCompute的VALUES TABLE。VALUES TABLE可以在INSERT語句和任何DML語句中使用。功能如下：

在沒有任何物理表時，您可以類比一個有任意資料的、多行的表，並進行任意運算。
取代SELECT * FROM與UNION ALL組合的方式，構造常量表。
VALUES TABLE支援特殊形式。您可以不通過FROM子句，直接執行SELECT，SELECT運算式列表中不可以出現其它表中的資料。其底層實現為從一個1行0列的匿名VALUES表中進行選取操作。在測試UDF或其它函數時，您可以通過該方式免去手工建立DUAL表的過程。

使用限制

通過INSERT … VALUES或VALUES table操作向表中插入資料時，不支援通過INSERT OVERWRITE操作指定插入列，只能通過INSERT INTO操作指定插入列。

命令格式

--INSERT … VALUES
INSERT INTO TABLE <table_name>
[PARTITION (<pt_spec>)][(<col1_name> ,<col2_name>,...)] 
VALUES (<col1_value>,<col2_value>,...),(<col1_value>,<col2_value>,...),...

--VALUES table
VALUES (<col1_value>,<col2_value>,...),(<col1_value>,<col2_value>,...),<table_name> (<col1_name> ,<col2_name>,...)...

table_name：必填。待插入資料的表名稱。該表為已經存在的表。
pt_spec：可選。需要插入資料的目標資料分割資訊，格式為(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)。如果需要更新的表為分區表，您需要指定該參數。
col_name：可選。需要插入資料的目標列名稱。
col_value：可選。目標表中列對應的列值。多個列值之間用英文逗號（,）分隔。該列值支援常量，同時還支援非常量運算式，例如自訂函數或內建函數運算式。未指定列值時，預設值為NULL。
說明
- 複雜資料類型無法構造對應的常量，例如ARRAY，您可以在VALUES中使用ARRAY類型，請參見樣本3。
- 通過VALUES寫入DATETIME或TIMESTAMP資料類型時，需要在VALUES中指定類型名稱，請參見樣本4。

使用樣本

樣本1：通過INSERT … VALUES操作向特定分區插入入資料。命令樣本如下：

--建立分區表srcp。
CREATE TABLE IF NOT EXISTS srcp (key STRING,value BIGINT) PARTITIONED BY (p STRING);

--向分區表srcp添加分區。
ALTER TABLE srcp ADD IF NOT EXISTS PARTITION (p='abc');

--向表srcp的指定分區abc中插入資料。
INSERT INTO TABLE srcp PARTITION (p='abc') VALUES ('a',1),('b',2),('c',3);

--查詢表srcp。
SELECT * FROM srcp WHERE p='abc';

--返回結果。
+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| a          | 1          | abc        |
| b          | 2          | abc        |
| c          | 3          | abc        |
+------------+------------+------------+

樣本2：通過INSERT … VALUES操作向非特定分區插入入資料。命令樣本如下：

--建立分區表srcp。
CREATE TABLE IF NOT EXISTS srcp (key STRING,value BIGINT) PARTITIONED BY (p STRING);

--向表srcp中插入資料，不指定分區。
INSERT INTO TABLE srcp PARTITION (p)(key,p) VALUES ('d','20170101'),('e','20170101'),('f','20170101');

--查詢表srcp。
SELECT * FROM srcp WHERE p='20170101';

--返回結果。
+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| d          | NULL       | 20170101   |
| e          | NULL       | 20170101   |
| f          | NULL       | 20170101   |
+------------+------------+------------+

樣本3：使用複雜資料類型構造常量，通過INSERT操作匯入資料。命令樣本如下：

--建立分區表srcp。
CREATE TABLE IF NOT EXISTS srcp (key STRING,value ARRAY<INT>) PARTITIONED BY (p STRING);

--向分區表srcp添加分區。
ALTER TABLE srcp ADD IF NOT EXISTS PARTITION (p='abc');

--向表srcp的指定分區abc中插入資料。
INSERT INTO TABLE srcp PARTITION (p='abc') SELECT 'a', ARRAY(1, 2, 3);

--查詢表srcp。
SELECT * FROM srcp WHERE p='abc';

--返回結果。
+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| a          | [1,2,3]    | abc        |
+------------+------------+------------+

樣本4：通過INSERT … VALUES操作寫入DATETIME或TIMESTAMP資料類型，需要在values中指定類型名稱。命令樣本如下：

--建立分區表srcp。
CREATE TABLE IF NOT EXISTS srcp (key STRING, value TIMESTAMP) PARTITIONED BY (p STRING);

--向分區表srcp添加分區。
ALTER TABLE srcp ADD IF NOT EXISTS PARTITION (p='abc');

--向表srcp的指定分區abc中插入資料。
INSERT INTO TABLE srcp PARTITION (p='abc') VALUES (datetime'2017-11-11 00:00:00',TIMESTAMP'2017-11-11 00:00:00.123456789');

--查詢表srcp。
SELECT * FROM srcp WHERE p='abc';

--返回結果。
+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| 2017-11-11 00:00:00 | 2017-11-11 00:00:00.123 | abc        |
+------------+------------+------------+

樣本5：通過VALUES table操作插入資料。命令樣本如下：

--建立分區表srcp。
CREATE TABLE IF NOT EXISTS srcp (key STRING,value BIGINT) PARTITIONED BY (p STRING);

--向表srcp中插入資料。
INSERT INTO TABLE srcp PARTITION (p) SELECT concat(a,b), length(a)+length(b),'20170102' FROM VALUES ('d',4),('e',5),('f',6) t(a,b);

--查詢表srcp。
SELECT * FROM srcp WHERE p='20170102';

--返回結果。
+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| d4         | 2          | 20170102   |
| e5         | 2          | 20170102   |
| f6         | 2          | 20170102   |
+------------+------------+------------+

VALUES (…), (…) t(a, b)相當於定義了一個名為t，列為a和b，資料類型分別為STRING和BIGINT的表。列的類型需要從VALUES列表中推導。

樣本6：取代SELECT * FROM與UNION ALL組合的方式，構造常量表。命令樣本如下：

SELECT 1 c UNION ALL SELECT 2 c;
--等價於如下語句。
SELECT * FROM VALUES (1), (2) t(c);

--返回結果。
+------------+
| c          |
+------------+
| 1          |
| 2          |
+------------+

樣本7：通過VALUES table的特殊形式插入資料，不帶FROM子句。命令樣本如下：

--建立分區表srcp。
CREATE TABLE IF NOT EXISTS srcp (key STRING,value BIGINT) PARTITIONED BY (p STRING);

--向表srcp中插入資料。
INSERT INTO TABLE srcp PARTITION (p) SELECT ABS(-1), LENGTH('abc'), GETDATE();

--查詢表srcp。
SET odps.sql.allow.fullscan=true;
SELECT * FROM srcp;

--返回結果樣本。
+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| 1          | 3          | 2024-12-10 16:58:56 |
+------------+------------+------------+

樣本8：使用非常量運算式，命令樣本如下：

SELECT * FROM VALUES (GETDATE()),(TO_DATE('20190101', 'yyyyMMdd')),(LASTDAY(DATETIME '2019-01-01 01:10:00')) t(d);

返回結果如下：

+------------+
| d          |
+------------+
| 2019-01-31 00:00:00 |
| 2019-01-01 00:00:00 |
| 2024-12-10 16:52:36 |
+------------+