全部产品
Search

搜索本产品

    云原生大数据计算服务 MaxCompute：插入或覆写数据（INSERT INTO | INSERT OVERWRITE）

    文档中心

    云原生大数据计算服务 MaxCompute：插入或覆写数据（INSERT INTO | INSERT OVERWRITE）

    更新时间：Jul 02, 2024

    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前后是不变的，您不必担心存储计费会产生问题。

    向动态分区插入数据的操作请参见插入或覆写动态分区数据（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有两种模式，默认模式为local zorder。local模式只是单个文件内部按照zorder排序，并不是对全局数据做一个重分布，所以如果数据分散在各个文件，那么数据的聚集程度可能也不高，无法做到最有效的Data Skipping。鉴于该问题，在新版本中支持了global zorder。

      • local zorder。

      • global zorder：如果使用global zorder模式，需要增加参数set odps.sql.default.zorder.type=global;。

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

    • zorder by的使用限制如下：

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

      • zorder by字段数目只能在2~4之间。

      • 目标表为聚簇表时，不支持zorder by子句。

      • zorder by可以与distribute by一起使用，不能与order by、cluster by或sort by一起使用。

      说明

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

    使用示例：普通表

    • 示例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）。
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子句中列的顺序，而不是表与表之间列名的对应关系。命令示例如下：

      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='02';
--返回结果。
+------------+------------+----+----+
| 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方式产出数据的代价会比较高，尤其是基线任务会明显影响产出的时间。