全部产品
Search
文档中心

云原生大数据计算服务 MaxCompute:插入或覆写数据(INSERT INTO | INSERT OVERWRITE)

更新时间:Dec 26, 2023

MaxCompute支持通过insert intoinsert overwrite操作向目标表或静态分区中插入、更新数据。

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

前提条件

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

功能介绍

在使用MaxCompute SQL处理数据时,insert intoinsert overwrite操作可以将select查询的结果保存至目标表中。二者的区别是:

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

  • insert overwrite:先清空表或静态分区中的原有数据,再向表或静态分区中插入数据。

    说明
    • MaxCompute的insert语法与通常使用的MySQL或Oracle的insert语法有差别。在insert overwrite后需要加table关键字,非直接使用table_nameinsert into可以省略table关键字。

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

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

使用限制

  • 执行insert intoinsert 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 intoinsert overwrite操作。

  • 对于Transaction Table2.0类型的表有如下限制。

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

    • Transaction Table2.0表用Insert Into写入数据时,相同PK值的多行默认不去重,都会写入表中,但如果设置Flag( odps.sql.insert.acidtable.deduplicate.enable)的值为true,则会去重后再写入表中。

    • Transaction Table2.0表Update语法不支持修改PK列。

命令格式

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 bycluster bysort 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_detailsale_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_dateregion为分区列,不允许出现在静态分区的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;

使用示例:Transaction Table2.0类型表

  • 示例表:创建Transaction Table2.0表

  • --创建Transaction Table2.0表
    create table mf_tt6 (pk bigint not null primary key, 
                      val bigint not null) 
                      partitioned by (dd string, hh string) 
                      tblproperties ("transactional"="true");
    说明

    目前Transaction Table2.0处于邀测阶段,默认不支持直接使用,如需使用此功能,请单击申请开通,在新功能试用申请页面申请开通使用Transaction Table2.0功能。

  • 写入表数据

    --插入Transaction Table2.0表数据
    insert overwrite table mf_tt6 partition (dd='01', hh='01') 
                     values (1, 1), (2, 2), (3, 3);
                     
    select * from mf_tt6 where dd='01' and hh='01';
    --返回
    +------------+------------+----+----+
    | pk         | val        | dd | hh |
    +------------+------------+----+----+
    | 1          | 1          | 01 | 01 |
    | 3          | 3          | 01 | 01 |
    | 2          | 2          | 01 | 01 |
    +------------+------------+----+----+
                     
    insert into table mf_tt6 partition(dd='01', hh='01') 
                values (3, 30), (4, 4), (5, 5);
    select * from mf_tt6 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 table mf_tt6 partition (dd='01', hh='02') 
                     values (1, 1), (2, 2), (3, 3);
    select * from mf_tt6 where dd='01' and hh='02';
    --返回
    +------------+------------+----+----+
    | pk         | val        | dd | hh |
    +------------+------------+----+----+
    | 1          | 1          | 01 | 02 |
    | 3          | 3          | 01 | 02 |
    | 2          | 2          | 01 | 02 |
    +------------+------------+----+----+
    insert into table mf_tt6 partition(dd='01', hh='02') 
                values (3, 30), (4, 4), (5, 5);
    select * from mf_tt6 where dd='01' and hh='02';
    --返回:
    +------------+------------+----+----+
    | pk         | val        | dd | hh |
    +------------+------------+----+----+
    | 1          | 1          | 01 | 02 |
    | 3          | 30         | 01 | 02 |
    | 4          | 4          | 01 | 02 |
    | 5          | 5          | 01 | 02 |
    | 2          | 2          | 01 | 02 |
    +------------+------------+----+----+
  • 更新/删除表

    --Transaction Table2.0表Update / Delete:
    update mf_tt6 set val = delta.val 
                  from (select pk, val from values (1, 10), (2, 20) t (pk, val)) delta 
                  where delta.pk = mf_tt6.pk and mf_tt6.dd='01' and mf_tt6.hh='01';
    --查询
    select * from mf_tt6 where dd='01' and hh='01';
    --返回
    +------------+------------+----+----+
    | pk         | val        | dd | hh |
    +------------+------------+----+----+
    | 1          | 10         | 01 | 01 |
    | 3          | 30         | 01 | 01 |
    | 4          | 4          | 01 | 01 |
    | 5          | 5          | 01 | 01 |
    | 2          | 20         | 01 | 01 |
    +------------+------------+----+----+
    update mf_tt6 set val = 40 where pk = 4 and dd='01' and hh='01';
    select * from mf_tt6 where dd='01' and hh='01';
    --返回
    +------------+------------+----+----+
    | pk         | val        | dd | hh |
    +------------+------------+----+----+
    | 1          | 10         | 01 | 01 |
    | 3          | 30         | 01 | 01 |
    | 4          | 40         | 01 | 01 |
    | 5          | 5          | 01 | 01 |
    | 2          | 20         | 01 | 01 |
    +------------+------------+----+----+
  • 删除记录

    --删除记录
    delete from mf_tt6 where val = 5  and dd='01' and hh='01';
    select * from mf_tt6 where dd='01' and hh='01';
    --返回
    +------------+------------+----+----+
    | pk         | val        | dd | hh |
    +------------+------------+----+----+
    | 1          | 10         | 01 | 01 |
    | 3          | 30         | 01 | 01 |
    | 4          | 40         | 01 | 01 |
    | 2          | 20         | 01 | 01 |
    +------------+------------+----+----+
  • merge into

    --Transaction Table2.0表Merge Into
    --创建Transaction Table2.0表
    create table mf_delta as 
           select pk, val 
             from values (1, 10), (2, 20), (6, 60) t (pk, val);
             merge into mf_tt6 using mf_delta 
                     on mf_tt6.pk = mf_delta.pk and mf_tt6.dd='01' and mf_tt6.hh='01' 
                   when matched and (mf_tt6.pk > 1) then  update set mf_tt6.val = mf_delta.val 
                   when matched then delete 
                   when not matched then insert values (mf_delta.pk, mf_delta.val, '01', '01');
    --查询
    select * from mf_delta;
    --返回
    +------+------+
    | pk   | val  |
    +------+------+
    | 1    | 10   |
    | 2    | 20   |
    | 6    | 60   |
    +------+------+

最佳实践

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方式产出数据的代价会比较高,尤其是基线任务会明显影响产出的时间。