AI 助理
备案控制台
文档
输入文档关键字查找
首页 云原生大数据计算服务 MaxCompute 操作指南 开发参考：SQL SQL参考 DML操作 插入或覆写数据（INSERT INTO | INSERT OVERWRITE）

插入或覆写数据（INSERT INTO | INSERT OVERWRITE）

更新时间：
产品详情
我的收藏

MaxCompute支持通过INSERT INTO或INSERT OVERWRITE操作向目标表或静态分区中插入、覆写数据。

前提条件

执行INSERT INTO和INSERT OVERWRITE操作前需要具备目标表的更新权限（Update）及源表的元信息读取权限（Select）。授权操作请参见MaxCompute权限。

功能介绍

在使用MaxCompute SQL处理数据时，INSERT INTO或INSERT OVERWRITE操作可以将SELECT查询的结果保存至目标表中。二者的区别是：

  • INSERT INTO：直接向表或静态分区中插入数据。您可以在INSERT语句中直接指定分区值，将数据插入指定的分区。如果您需要插入少量测试数据，可以配合VALUES使用。

  • INSERT OVERWRITE：先清空表或静态分区中的原有数据，再向表或静态分区中插入数据。

    说明

    • MaxCompute的INSERT语法与通常使用的MySQL或Oracle的INSERT语法有差别。在INSERT OVERWRITE后需要加TABLE关键字，非直接使用table_name。INSERT INTO可以省略TABLE关键字。

    • 在反复对同一个分区执行INSERT OVERWRITE操作时，您通过DESC命令查看到的数据分区Size会不同。这是因为从同一个表的同一个分区SELECT出来再INSERT OVERWRITE回相同分区时，文件切分逻辑发生变化，从而导致数据的Size发生变化。数据的总长度在INSERT OVERWRITE前后是不变的，您不必担心存储计费会产生问题。

    • 并发写入场景，MaxCompute会根据ACID保障并发写入操作。关于ACID的具体语义，请参见ACID语义。

向动态分区插入数据的操作请参见插入或覆写动态分区数据（DYNAMIC PARTITION）。

使用限制

  • 执行INSERT INTO和INSERT OVERWRITE操作更新表或静态分区数据的使用限制如下：

    • INSERT INTO：不支持向聚簇表中追加数据。

    • INSERT OVERWRITE：不支持指定插入列，只能使用INSERT INTO。例如CREATE TABLE t(a STRING, b STRING); INSERT INTO t(a) VALUES ('1');，a列插入1，b列为NULL或默认值。

    • MaxCompute对正在操作的表没有锁机制，不要同时对一个表执行INSERT INTO或INSERT OVERWRITE操作。

  • 对于Delta Table类型的表有如下限制。

    • Delta Table表用INSERT OVERWRITE写入数据时，相同PK值的多行记录在写入表之前会先去重，只选择第一行写入，最终写入的结果依赖于计算过程的记录顺序，无法手动指定。由于该操作写入的是全量数据，因此默认去重也是尽可能保证PK唯一性的属性。

    • Delta Table表用INSERT INTO写入数据时，相同PK值的多行默认不去重，都会写入表中，但如果设置Flag（odps.sql.insert.acidtable.deduplicate.enable）的值为true，则会去重后再写入表中。

命令格式

INSERT {INTO|OVERWRITE} TABLE <table_name> [PARTITION (<pt_spec>)] [(<col_name> [,<col_name> ...)]]
<select_statement>
FROM <from_statement>
[ZORDER BY <zcol_name> [, <zcol_name> ...]];

参数说明如下：

参数名

是否必填

描述

table_name

是

需要插入数据的目标表名称。

pt_spec

否

需要插入数据的分区信息，不允许使用函数等表达式，只能是常量。

格式为(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)。

col_name

否

需要插入数据的目标表的列名称。

INSERT OVERWRITE不支持指定[(<col_name> [,<col_name> ...)]。

select_statement

是

SELECT子句，从源表中查询需要插入目标表的数据。

更多SELECT信息，请参见SELECT语法。

说明

  • 源表与目标表的对应关系依赖于SELECT子句中列的顺序，而不是表与表之间列名的对应关系。

  • 如果目标表是静态分区，向某个分区插入数据时，分区列不允许出现在SELECT子句中。

from_statement

是

FROM子句，表示数据来源。例如，源表名称。

ZORDER BY <zcol_name> [, <zcol_name> ...]

否

向表或分区写入数据时，支持根据指定的一列或多列（select_statement对应表中的列），把排序列数据相近的行排列在一起，提升查询时的过滤性能，在一定程度上降低存储成本。需要注意的是，ORDER BY x, y会严格按照先x后y的顺序对数据进行排序，ZORDER BY x, y会把相近的<x, y>尽量排列在一起。当SQL查询语句的过滤条件中包含排序列时，ORDER BY后的数据仅对包含x的表达式有较好的过滤效果，ZORDER BY后的数据对包含x或同时包含x、y的表达式均有较好的过滤效果，列压缩比例更高。

ZORDER BY与SORT BY区别如下：

  • ZORDER BY有local zorder和global zorder两种模式，默认模式为local zorder。local模式只是单个文件内部按照zorder排序，并不是对全局数据做一个重分布，所以如果数据分散在各个文件，那么数据的聚集程度可能也不高，无法做到最有效的Data Skipping。鉴于该问题，在新版本中支持了global zorder。如果使用global zorder模式，需要增加参数SET odps.sql.default.zorder.type=global;。

    ZORDER BY的使用限制如下：

    • 对于分区表，一次只允许对1个分区进行ZORDER BY排序。

    • ZORDER BY字段数目只能在2~4之间。

    • 目标表为聚簇表时，不支持ZORDER BY子句。

    • ZORDER BY可以与DISTRIBUTE BY一起使用，不能与ORDER BY、CLUSTER BY或SORT BY一起使用。

    说明

    使用ZORDER BY子句写入数据时，会占用较多资源，比不排序花费的时间更多。

  • SORT BY语句用于指定单个文件内部排序的方式，如果不写SORT BY，则单个文件内部按照local zorder排序。

使用示例：普通表

  • 示例1：执行INSERT INTO命令向非分区表websites中追加数据。命令示例如下：

    --创建一张非分区表websites。
CREATE TABLE IF NOT EXISTS websites
(id INT,
 name STRING,
 url STRING
);
--创建一张非分区表apps
CREATE TABLE IF NOT EXISTS apps
(id INT,
 app_name STRING,
 url STRING
);
--向表apps追加数据。其中：insert into table table_name可以简写为insert into table_name
INSERT INTO apps (id,app_name,url) VALUES 
(1,'Aliyun','https://www.aliyun.com');
--复制apps的表数据追加至websites表
INSERT INTO websites (id,name,url) SELECT id,app_name,url
FROM apps;
--执行select语句查看表websites中的数据。
SELECT * FROM websites;

    返回结果如下：

    --返回结果。
+------------+------------+------------+
| id         | name       | url        |
+------------+------------+------------+
| 1          | Aliyun     | https://www.aliyun.com |
+------------+------------+------------+

  • 示例2：执行INSERT INTO命令向分区表sale_detail中追加数据。命令示例如下：

    --创建一张分区表sale_detail。
CREATE TABLE IF NOT EXISTS sale_detail
(
shop_name     STRING,
customer_id   STRING,
total_price   DOUBLE
)
PARTITIONED BY (sale_date STRING, region STRING);

--向源表增加分区。非必需操作，如果不提前创建，写入时会自动创建该分区。
ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china');

--向源表追加数据。其中：insert into table table_name可以简写为insert into table_name，但insert overwrite table table_name不可以省略table关键字。
INSERT INTO sale_detail PARTITION (sale_date='2013', region='china') VALUES ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);

--开启全表扫描，仅此Session有效。执行select语句查看表sale_detail中的数据。
SET odps.sql.allow.fullscan=true; 
SELECT * FROM sale_detail;

    返回结果如下：

    +------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

  • 示例3：执行INSERT OVERWRITE命令向表sale_detail_insert中覆写数据。命令示例如下：

    --创建目标表sale_detail_insert，与sale_detail有相同的结构。
CREATE TABLE sale_detail_insert LIKE sale_detail;

--给目标表增加分区。非必需操作，如果不提前创建，写入时会自动创建该分区。
ALTER TABLE sale_detail_insert ADD PARTITION (sale_date='2013', region='china');

--从源表sale_detail中取出数据插入目标表sale_detail_insert。注意不需要声明目标表字段，也不支持重排目标表字段顺序。
--对于静态分区目标表，分区字段赋值已经在partition()部分声明，不需要在select_statement中包含，只要按照目标表普通列顺序查出对应字段，按顺序映射到目标表即可。动态分区表则需要在select中包含分区字段，详情请参见插入或覆写动态分区数据（DYNAMIC PARTITION）。
SET odps.sql.allow.fullscan=true;
INSERT OVERWRITE TABLE sale_detail_insert PARTITION (sale_date='2013', region='china')
  SELECT 
  shop_name, 
  customer_id,
  total_price 
  FROM sale_detail
  ZORDER BY customer_id, total_price;

--开启全表扫描，仅此Session有效。执行select语句查看表sale_detail_insert中的数据。
SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail_insert;

    返回结果如下：

    +------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

  • 示例4：执行INSERT OVERWRITE命令向表sale_detail_insert中覆写数据，调整SELECT子句中列的顺序。源表与目标表的对应关系依赖于SELECT子句中列的顺序，而不是表与表之间列名的对应关系。命令示例如下：

    SET odps.sql.allow.fullscan=true;
INSERT OVERWRITE TABLE sale_detail_insert PARTITION (sale_date='2013', region='china')
    SELECT customer_id, shop_name, total_price FROM sale_detail;    

SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail_insert;

    返回结果如下：

    +------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| c1         | s1          | 100.1       | 2013       | china      |
| c2         | s2          | 100.2       | 2013       | china      |
| c3         | s3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

    在创建sale_detail_insert表时，列的顺序为shop_name string、customer_id string、total_price bigint，而从sale_detail向sale_detail_insert插入数据的顺序为customer_id、shop_name、total_price。此时，会将sale_detail.customer_id的数据插入sale_detail_insert.shop_name，将sale_detail.shop_name的数据插入sale_detail_insert.customer_id。

  • 示例5：向某个分区插入数据时，分区列不允许出现在SELECT子句中。如下语句会返回报错，sale_date和region为分区列，不允许出现在静态分区的SELECT子句中。错误命令示例如下：

    INSERT OVERWRITE TABLE sale_detail_insert PARTITION (sale_date='2013', region='china')
   SELECT shop_name, customer_id, total_price, sale_date, region FROM sale_detail;

  • 示例6：PARTITION的值只能是常量，不可以为表达式。错误命令示例如下：

    INSERT OVERWRITE TABLE sale_detail_insert PARTITION (sale_date=datepart('2016-09-18 01:10:00', 'yyyy') , region='china')
   SELECT shop_name, customer_id, total_price FROM sale_detail;

  • 示例7：执行INSERT OVERWRITE命令向表mf_src和表mf_zorder_src中覆写数据，并使用global zorder模式对表mf_zorder_src进行排序。命令示例如下：

    --创建目标表mf_src。
CREATE TABLE mf_src (key STRING, value STRING);

INSERT OVERWRITE TABLE mf_src
SELECT a, b FROM VALUES ('1', '1'),('3', '3'),('2', '2')
AS t(a, b);

SELECT * FROM mf_src;

--返回结果
+-----+-------+
| key | value |
+-----+-------+
| 1   | 1     |
| 3   | 3     |
| 2   | 2     |
+-----+-------+

--创建目标表mf_zorder_src，与mf_src有相同的结构。
CREATE TABLE mf_zorder_src LIKE mf_src;

--使用global zorder模式排序。
SET odps.sql.default.zorder.type=global;
INSERT OVERWRITE TABLE mf_zorder_src
SELECT key, value FROM mf_src 
ZORDER BY key, value;

SELECT * FROM mf_zorder_src;

    返回结果如下：

    +-----+-------+
| key | value |
+-----+-------+
| 1   | 1     |
| 2   | 2     |
| 3   | 3     |
+-----+-------+

  • 示例8：执行INSERT OVERWRITE命令覆写存量表target数据。命令示例如下：

    -- target表是存量表
SET odps.sql.default.zorder.type=global;
INSERT OVERWRITE TABLE target
SELECT key, value FROM target 
ZORDER BY key, value;

使用示例：Delta Table类型表

示例：创建Delta Table表mf_dt，并执行INSERT命令插入并覆写数据。

--创建Delta Table表mf_dt。
CREATE TABLE IF NOT EXISTS mf_dt (pk BIGINT NOT NULL PRIMARY KEY, 
                  val BIGINT NOT NULL) 
                  PARTITIONED BY (dd STRING, hh STRING) 
                  tblproperties ("transactional"="true");
                  
--向mf_dt表dd='01'和hh='01'的分区中插入测试数据。
INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='01') 
                 VALUES (1, 1), (2, 2), (3, 3);

--查询mf_dt表目标分区中的数据                
SELECT * FROM mf_dt WHERE dd='01' AND hh='01';
--返回结果
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

--使用insert into向mf_dt表目标分区中追加数据。
INSERT INTO TABLE mf_dt PARTITION(dd='01', hh='01') 
            VALUES (3, 30), (4, 4), (5, 5);
            
SELECT * FROM mf_dt WHERE dd='01' AND hh='01';
--返回结果
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 30         | 01 | 01 |
| 4          | 4          | 01 | 01 |
| 5          | 5          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

--使用insert overwrite向mf_dt表目标分区的覆盖写入数据。
INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='01') 
                 VALUES (1, 1), (2, 2), (3, 3);
SELECT * FROM mf_dt WHERE dd='01' AND hh='01';
--返回结果。
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

--使用insert into向mf_dt表dd='01'和hh='02'的分区写入数据。
INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='02') 
                 VALUES (1, 11), (2, 22), (3, 32);
SELECT * FROM mf_dt WHERE dd='01' AND hh='02';
--返回结果。
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 11         | 01 | 02 |
| 3          | 32         | 01 | 02 |
| 2          | 22         | 01 | 02 |
+------------+------------+----+----+

--开启全表扫描，仅此Session有效。执行select语句查看表mf_dt中的数据。
SET odps.sql.allow.fullscan=true;
SELECT * FROM mf_dt;
--返回结果。
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 11         | 01 | 02 |
| 3          | 32         | 01 | 02 |
| 2          | 22         | 01 | 02 |
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

最佳实践

Z-Order功能并不是适用于所有场景，也没有统一的规则来指导是否应该用Z-Order及如何使用。很多时候都需要根据具体案例去尝试改造，综合评估改造Z-Order后生成数据带来的额外计算成本，相对于存储成本的节省和下游消费计算成本的节省，是否有收益。下面提供一些经验上的建议，同时也需要靠各位用户在使用过程中一起提炼和总结。

优先考虑Clustered Index而不是Z-Order的场景

  • 如果过滤条件基本都是某个前缀的组合，比如a、a和b、a和b和c，那么使用Clustered Index（即ORDER BY a, b, c）更有效，此时不要使用ZORDER BY。因为ORDER BY对第一个字段有非常好的排序效果，对后面字段影响较少；而ZORDER BY对每个字段给予了相同的权重，仅看某一列的排序是不如ORDER BY的第一个字段的。

  • 如果某些字段经常出现在JOIN KEY上，这些字段使用Hash或Range Clustering更合适。因为MaxCompute Z-Order的实现仅仅在文件内进行了排序，而SQL引擎对Z-Order的数据分布没有感知；但是SQL引擎是能够感知Clustered Index的，因此在做查询计划阶段能够更好地优化JOIN的性能。

  • 如果某些字段经常需要进行GROUP BY和ORDER BY，那么使用Clustered Index可以获得更好的性能。

Z-Order使用建议

  • 选取经常出现在过滤条件中的字段，尤其是经常联合在一起过滤的字段。

  • ZORDER BY的字段数越多，每个字段的排序性能会越差，因此字段数不宜超过4个。如果只有一个字段，那就应该使用Clustered Index而不是Z-Order。

  • 选取的字段的distinct value不宜太小或太大。太小的极端情况就是性别字段，只有两个值，排序并没有多大意义。太大的极端情况就是基本没有重复的，这样排序的代价会很高，因为MaxCompute的Z-Order实现需要将字段出现的所有值缓存在内存中来计算ZValue。

  • 表的数据量也不宜太小或太大。如果数据量太小，Z-Order无法看出效果。而数据量太大，按照Z-Order方式产出数据的代价会比较高，尤其是基线任务会明显影响产出的时间。

上一篇：DML操作下一篇：插入或覆写动态分区数据（DYNAMIC PARTITION）
文档推荐