全部产品
Search
文档中心

云原生大数据计算服务 MaxCompute:VALUES

更新时间:Dec 20, 2024

如果需要向表中插入少量数据,您可以通过INSERT … VALUESVALUES TABLE操作向数据量小的表中插入数据。

执行INSERT INTO操作前需要具备目标表的修改权限(Alter)及源表的元信息读取权限(Describe)。授权操作请参见MaxCompute权限

功能介绍

MaxCompute支持您通过INSERT … VALUESVALUES TABLE操作向表中插入少量数据。

功能

说明

INSERT … VALUES

在业务测试阶段,您可以通过INSERT … VALUES操作向表中插入数据执行简单测试:

  • 如果插入几条或十几条数据,您可以通过INSERT … VALUES语句快速向测试表中写入数据。

  • 如果插入几十条数据,您可以通过Tunnel上传一个TXTCSV格式的数据文件导入数据,详情请参见导入数据

VALUES TABLE

如果您需要对插入的数据进行简单的运算,推荐使用MaxCompute的VALUES TABLEVALUES TABLE可以在INSERT语句和任何DML语句中使用。功能如下:

  • 在没有任何物理表时,您可以模拟一个有任意数据的、多行的表,并进行任意运算。

  • 取代SELECT * FROMUNION ALL组合的方式,构造常量表。

  • VALUES TABLE支持特殊形式。您可以不通过FROM子句,直接执行SELECTSELECT表达式列表中不可以出现其它表中的数据。其底层实现为从一个1行0列的匿名VALUES表中进行选取操作。在测试UDF或其它函数时,您可以通过该方式免去手工创建DUAL表的过程。

使用限制

通过INSERT … VALUESVALUES 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,列为ab,数据类型分别为STRING和BIGINT的表。列的类型需要从VALUES列表中推导。

  • 示例6:取代SELECT * FROMUNION 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 |
    +------------+