相关文章推荐
绅士的毛巾  ·  南科大卢海舟团队在三维量子霍尔效应理论取得重 ...·  5 月前    · 
任性的黑框眼镜  ·  百年电影公司派拉蒙发布动画电影《怪兽训练营》 ...·  1 年前    · 
含蓄的眼镜  ·  钢铁工业水污染物排放标准_中华人民共和国生态环境部·  1 年前    · 
健壮的火腿肠  ·  轻量级容器编排-火山引擎·  1 年前    · 
阳刚的橙子  ·  新一代欧宝轿跑：OPEL / ...·  1 年前    · 
小百科  ›  如何插入或覆写数据_云原生大数据计算服务 MaxCompute-阿里云帮助中心
date string select 命令模式
不拘小节的马克杯
1 年前

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

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

前提条件

执行 insert into 和 insert overwrite 操作前需要具备目标表的修改权限（Alter）及源表的元信息读取权限（Describe）。授权操作请参见 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 操作。

  • 对于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 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;    
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;

使用示例：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表数据
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 | 02 |
| 3          | 3          | 01 | 02 |
| 2          | 2          | 01 | 02 |
+------------+------------+----+----+
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 | 02 |
| 3          | 30         | 01 | 02 |
| 4          | 4          | 01 | 02 |
| 5          | 5          | 01 | 02 |
| 2          | 2          | 01 | 02 |
+------------+------------+----+----+
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方式产出数据的代价会比较高，尤其是基线任务会明显影响产出的时间。

 
推荐文章
绅士的毛巾  ·  南科大卢海舟团队在三维量子霍尔效应理论取得重要进展- 南方科技 ...
5 月前
任性的黑框眼镜  ·  百年电影公司派拉蒙发布动画电影《怪兽训练营》，预告片精彩纷呈 ...
1 年前
含蓄的眼镜  ·  钢铁工业水污染物排放标准_中华人民共和国生态环境部
1 年前
健壮的火腿肠  ·  轻量级容器编排-火山引擎
1 年前
阳刚的橙子  ·  新一代欧宝轿跑：OPEL / Vauxhall Calibra-太平洋汽车网-名车廊
1 年前
今天看啥   ·   Py中国   ·   codingpro   ·   藏经阁   ·   小百科   ·   link之家   ·   卧龙AI搜索
删除内容请联系邮箱 2879853325@qq.com
小百科 - 百科知识指南
© 2024 ~ 沪ICP备11025650号