文档

表操作

更新时间:

表是MaxCompute的数据存储单元。数据仓库的开发、分析及运维都需要对表数据进行处理。本文以具体示例为您介绍如何创建、删除和查看表等常用表操作。

表操作常用命令如下。表操作详情请参见表操作

类型

功能

角色

操作入口

创建表

创建非分区表或分区表。

具备项目空间创建表权限(CreateTable)的用户。

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

修改表的所有人

修改表的所有人,即表Owner。

项目空间Owner

删除表

删除非分区表或分区表。

具备删除表权限(Drop)的用户。

查看表或视图信息

查看MaxCompute内部表、视图或外部表的信息。

具备读取表元信息权限(Describe)的用户。

查看分区信息

查看某个分区表具体的分区信息。

具备读取表元信息权限(Describe)的用户。

列出项目空间下的表和视图

列出项目空间下所有的表、视图或符合某规则(支持正则表达式)的表、视图。

具备项目空间查看对象列表权限(List)的用户。

列出所有分区

列出一张表中的所有分区。

具备项目空间查看对象列表权限(List)的用户。

关于分区和列的操作详情请参见分区和列操作

关于表生命周期的操作详情请参见生命周期操作

创建表

创建非分区表、分区表、外部表或聚簇表。

  • 限制条件

    • 分区表的分区层级不能超过6级。例如某张表以日期为分区列,分区层级为年/月/周/日/时/分

    • 一张表允许的分区个数支持按照具体的项目配置,默认为6万个。

    更多表的限制条件,请参见SQL使用限制项

  • 命令格式

    --创建新表。
     create [external] table [if not exists] <table_name>
     [primary key (<pk_col_name>, <pk_col_name2>),(<col_name> <data_type> [not null] [default <default_value>] [comment <col_comment>], ...)]
     [comment <table_comment>]
     [partitioned by (<col_name> <data_type> [comment <col_comment>], ...)]
     
    --用于创建聚簇表时设置表的Shuffle和Sort属性。
     [clustered by | range clustered by (<col_name> [, <col_name>, ...]) [sorted by (<col_name> [asc | desc] [, <col_name> [asc | desc] ...])] into <number_of_buckets> buckets] 
    
    --仅限外部表。
     [stored by StorageHandler] 
     --仅限外部表。
     [with serdeproperties (options)] 
     --仅限外部表。
     [location <osslocation>] 
    
    --指定表为Transactional1.0表,后续可以对该表执行更新或删除表数据操作,但是Transactional表有部分使用限制,请根据需求创建。
     [tblproperties("transactional"="true")]
    
    --指定表为Transactional2.0表,后续可以做upsert,增量查询,time-travel等操作
     [tblproperties ("transactional"="true" [, "write.bucket.num" = "N", "acid.data.retain.hours"="hours"...])] [lifecycle <days>]
    ;
    
    --基于已存在的表创建新表并复制数据,但不复制分区属性。支持外部表和湖仓一体外部项目中的表。
    create table [if not exists] <table_name> [lifecycle <days>] as <select_statement>;
    
    --基于已存在的表创建具备相同结构的新表但不复制数据,支持外部表和湖仓一体外部项目中的表。
    create table [if not exists] <table_name> like <existing_table_name> [lifecycle <days>];
  • 通用参数

    • external:可选。表示创建的表为外部表。

    • if not exists:可选。如果不指定if not exists选项而存在同名表,会报错。如果指定if not exists,只要存在同名表,即使原表结构与要创建的目标表结构不一致,均返回成功。已存在的同名表的元数据信息不会被改动。

    • table_name:必填。表名。表名大小写不敏感,不能有特殊字符,只能包含a~z、A~Z、数字和下划线(_)。建议以字母开头,名称的长度不超过128字节,否则报错。

    • col_name:可选。表的列名。列名大小写不敏感,不能有特殊字符,只能包含a~z、A~Z、数字、下划线(_)或中文。建议以字母开头,名称的长度不超过128字节,否则报错。

    • col_comment:可选。列的注释内容。注释内容为长度不超过1024字节的有效字符串,否则报错。

    • data_type:可选。列的数据类型,包含BIGINT、DOUBLE、BOOLEAN、DATETIME、DECIMAL和STRING等多种数据类型,详情请参见数据类型版本说明

    • not null:可选。禁止该列的值为NULL。更多修改非空属性信息,请参见修改表的列非空属性

    • default_value:可选。指定列的默认值,当insert操作不指定该列时,该列写入默认值。

      说明

      当前默认值不支持函数,例如getdate()now()等。

    • table_comment:可选。表注释内容。注释内容为长度不超过1024字节的有效字符串,否则报错。

    • lifecycle:可选。表的生命周期,仅支持正整数。单位:天。

      • 非分区表:自最后一次修改表数据开始计算,经过days天后数据无改动,则您无需干预此表,MaxCompute会自动回收(类似drop table操作)。

      • 分区表:系统根据各分区的LastModifiedTime判断是否需要回收分区。不同于非分区表,分区表的最后一个分区被回收后,该表不会被删除。生命周期只能设定到表级别,不支持在分区级别设置生命周期。

  • 分区表参数

    partitioned by (<col_name> <data_type> [comment <col_comment>], ...:可选。指定分区表的分区字段。

    • col_name:表的分区列名。列名大小写不敏感,不能有特殊字符,只能包含a~z、A~Z、数字、下划线(_)或中文。建议以字母开头,名称的长度不超过128字节,否则报错。

    • data_type:分区列的数据类型。MaxCompute 1.0版本仅支持STRING类型。MaxCompute 2.0版本扩充了分区类型,包含TINYINT、SMALLINT、INT、BIGINT、VARCHAR和STRING类型。详情请参见数据类型版本说明。当使用分区字段对表进行分区时,新增分区、更新分区内数据和读取分区数据均不需要做全表扫描,提高处理效率。

    • col_comment:分区列的注释内容。注释内容为长度不超过1024字节的有效字符串,否则报错。

    说明

    分区值不能包含双字节字符(如中文),必须以字母开头,包含字母、数字和允许的字符,长度不超过255字节。允许的字符包括空格、冒号(:)、下划线(_)、美元符号($)、井号(#)、英文句点(.)、感叹号(!)和at(@),其他字符的行为未定义,例如转义字符\t\n/

  • 聚簇表参数

    clustered by | range clustered by (<col_name> [, <col_name>, ...]) [sorted by (<col_name> [asc | desc] [, <col_name> [asc | desc] ...])] into <number_of_buckets> buckets:可选。用于创建聚簇表时设置表的Shuffle和Sort属性。

    聚簇表分为Hash聚簇表和Range聚簇表两种。

    Hash聚簇表

    • clustered by:指定Hash Key。MaxCompute将对指定列进行Hash运算,按照Hash值分散到各个Bucket中。为避免数据倾斜和热点,并取得较好的并行执行效果,clustered by列适宜选择取值范围大,重复键值少的列。此外,为了达到join优化的目的,也应该考虑选取常用的Join或Aggregation Key,即类似于传统数据库中的主键。

    • sorted by:指定Bucket内字段的排序方式。建议sorted byclustered by保持一致,以取得较好的性能。此外,当指定sorted by子句后,MaxCompute将自动生成索引,并且在查询时利用索引来加快执行。

    • number_of_buckets:指定哈希桶的数量。该值必须填写,且由数据量大小决定。此外,MaxCompute默认支持最多1111个Reducer,所以此处最多只支持1111个哈希桶。您可以使用set odps.stage.reducer.num =<并发数>;来提升这个限制,但最大不得超过4000,否则会影响性能。

      选择哈希桶数目时,请您遵循以下两个原则:

      • 哈希桶大小适中:建议每个Bucket的大小为500 MB左右。例如,分区大小估计为500 GB,粗略估算Bucket数目应该设为1000,这样平均每个Bucket大小约为500 MB。对于特别大的表,500 MB的限制可以突破,每个Bucket在2 GB~3 GB左右比较合适。同时,可以结合set odps.stage.reducer.num =<并发数>;来突破1111个桶的限制。

      • 对于join优化场景,去除Shuffle和Sort步骤能显著提升性能。因此要求两个表的哈希桶数目成倍数关系,例如256和512。建议哈希桶的数量统一使用2n,例如512、1024、2048或4096,这样系统可以自动进行哈希桶的分裂和合并,也可以去除Shuffle和Sort的步骤,提升执行效率。

    Range聚簇表

    • range clustered by:指定范围聚簇列。MaxCompute将对指定列进行分桶运算,按照分桶编号分散到各个Bucket中。

    • sorted by:指定Bucket内字段的排序方式,使用方法与Hash聚簇表相同。

    • number_of_buckets:指定哈希桶的数量。Range聚簇表的Bucket桶数没有Hash聚簇表的2n最佳实践,在数据量分布合理的情况下,任何桶数都可以。Range聚簇表的Bucket数不是必须的,可以省略,此时系统会根据数据量自动决定最佳的Bucket数目。

    • 当Join或Aggregation的对象是Range聚簇表,且Join Key或Group Key是Range Clustering Key或其前缀时,可以通过控制Flag消除数据的重分布,即Shuffle Remove,提升执行效率。您可以通过set odps.optimizer.enable.range.partial.repartitioning=true/false;进行设置,默认关闭。

      说明
      • 聚簇表的优点:

        • 优化Bucket Pruning。

        • 优化Aggregation。

        • 优化存储。

      • 聚簇表的限制:

        • 不支持insert into,只能通过insert overwrite来添加数据。

        • 不支持Tunnel直接Upload到Range聚簇表,因为Tunnel上传数据是无序的。

        • 不支持备份恢复功能。

  • 外部表参数

    • stored by StorageHandler:可选。按照外部表数据格式指定StorageHandler。

    • with serdeproperties (options):可选。外部表的授权、压缩、字符解析等相关参数。

    • osslocation:可选。外部表数据OSS存储位置,详情请参见创建OSS外部表

  • Transaction Table1.0与Transaction Table2.0表参数

    说明
    • 仅设置tblproperties("transactional"="true")表示表类型为Transaction Table1.0,除此之外还设置primary key (PK)或其他Transaction表参数时,表示表类型为Transaction Table2.0。

    • 目前Transaction Table2.0处于邀测阶段,默认不支持直接使用,如果示例中涉及到Transaction Table2.0功能,您可以点击申请开通,在新功能试用申请页面申请开通使用Transaction Table2.0功能后,再运行相关命令。详情请参见Transaction Table2.0概述

    Transaction Table1.0表参数

    tblproperties("transactional"="true"):可选。设置表为Transactional表。后续可以对Transactional表执行updatedelete操作实现行级更新或删除数据。更多信息,请参见更新或删除数据(UPDATE | DELETE)

    Transactional表的使用限制如下:

    • MaxCompute只允许在创建表时设置Transactional属性。已创建的表不允许通过alter table方式修改Transactional属性,执行如下语句会报错:

      alter table not_txn_tbl set tblproperties("transactional"="true");
      --报错。
      FAILED: Catalog Service Failed, ErrorCode: 151, Error Message: Set transactional is not supported
    • 在创建表时,不支持将聚簇表、外部表设置为Transactional表。

    • 不支持MaxCompute内部表、外部表、聚簇表与Transactional表互转。

    • 不支持自动合并Transactional表文件,需要手动执行合并操作,详情请参见合并Transactional表文件

    • 不支持merge partition操作。

    • 其他系统的作业访问Transactional表有一些限制,例如Graph不支持读写;Spark、PAI只支持读,不支持写。

    Transaction Table2.0表参数

    • primary key (PK)

      创建Transaction Table2.0类型表时必填,可包含多列,语法遵循标准SQL primary key语法,PK列必须设置not null,不允许修改。 设置后,后续表数据会根据PK列进行去重,Unique约束在单个partition范围内有效,或非分区表内有效。

    • tblproperties ("transactional"="true" [, "write.bucket.num" = "N", "acid.data.retain.hours"="hours"...])]

      • transactional:创建Transaction Table2.0类型表时必填,必须设置为true。表示符合MaxCompute的ACID表的事务特性,采用MVCC事务管理模式,保证快照隔离级别。

      • write.bucket.num:可选,默认取值为16,取值范围为(0, 4096]。表示每个partition或者非分区表的分桶数量,也表示数据写入的并发节点数量。分区表支持修改,新分区默认生效,非分区表不支持修改。该参数用法可参考如下建议:

        • 如果是通过Tunnel导入,代表Tunnel并发节点数,设置结果会影响导入流量,也会受Tunnel最大并发节点数约束。

        • 如果是通过SQL写入,代表写入数据的Reducer的并发度,受Reducer最大并发节点数约束。

        • 建议每个Bucket的数据写入大小为500 MB左右。例如,分区大小估计为500 GB,粗略估算Bucket数目应该设为1000,这样平均每个Bucket大小约为500 MB。对于特别大的表,500 MB的限制可以突破,每个Bucket在2 GB~3 GB左右比较合适。

      • acid.data.retain.hours:可选,默认取值为72,取值范围为[0, 168]。表示TimeTravel可查询数据历史状态的时间范围(单位为小时)。

        • 取值为0时表示不保留数据历史状态,也就是不支持timetravel查询。

        • 如果数据历史状态存在时间超过了此参数设置的值,可被删除或者Compact。

        • 如果SQL Timetraval查询的时间早于该参数的值,会直接报错,比如属性值为72小时,Timetraval要查询72小时之前的数据历史状态,会直接报错。

      • acid.incremental.query.out.of.time.range.enabled:可选,默认false。True表示增量查询指定的endTimestamp可大于表最大的数据Commit Time,在endTimestamp大于当前时间场景下,用户多次查询可能得到不同的结果,因为可能有新的数据插入。支持修改表的此参数取值。

    • Transaction Table2.0其他通用参数的设置要求

      • lifecycle: 表生命周期必须大于等于timetravel可查询的生命周期,即lifecycle >= acid.data.retain.hours / 24。创建表时会做检查,不符合会报错。

      • cluster by:Transaction Table2.0不支持设置该参数,实际上Transaction Table2.0内部实现采用类似HashClusterTable存储结构,同时设置可能引发冲突。

      • 其他不支持的特性:不支持设置range cluster by、external外表、Create Table As。

    • 其他使用限制:

      • 当前不支持除MaxCompute SQL外的其他引擎系统的作业,当前只支持MaxCompute SQL使用Transaction Table2.0表,其他引擎(如MaxCompute Spark、Hologres等)暂不支持。

      • 存量普通表不支持修改为transactional表。

  • 基于已有数据/表新建表

    • 通过create table [if not exists] <table_name> [lifecycle <days>] as <select_statement>;语句可以再创建一个表,并在建表的同时将数据复制到新表中。

      • 但通过该语句创建的表不会复制分区属性,只会把源表的分区列作为目标表的一般列处理,也不会复制源表的生命周期属性。

      • 您还可以通过lifecycle参数回收表。同时也支持创建内部表复制外部表的数据。

    • 通过create table [if not exists] <table_name> like <existing_table_name> [lifecycle <days>];语句可以再创建一个表,使目标表和源表具有相同的表结构。

      • 但通过该语句创建的表不复制数据,也不会复制源表的生命周期属性。

      • 您还可以通过lifecycle参数回收表。同时也支持创建内部表复制外部表的结构。

  • 使用示例

    • 示例1:创建非分区表test1。

      create table test1 (key STRING);
    • 示例2:创建一张分区表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); 
    • 示例3:创建一个新表sale_detail_ctas1,将sale_detail的数据复制到sale_detail_ctas1中,并设置生命周期。

      SET odps.sql.allow.fullscan=true;
      create table sale_detail_ctas1 lifecycle 10 as select * from sale_detail;

      您可以通过desc extended sale_detail_ctas1;命令查看到表的结构及生命周期等详细信息。

      此处sale_detail是一张分区表,而通过create table ... as select_statement ...语句创建的表sale_detail_ctas1不会复制分区属性,只会把源表的分区列作为目标表的一般列处理。即sale_detail_ctas1是一个含有5列的非分区表。

    • 示例4:创建一个新表sale_detail_ctas2,在select子句中使用常量作为列的值。

      SET odps.sql.allow.fullscan=true;
      --指定列的名字。
      create table sale_detail_ctas2
      as
      select shop_name, customer_id, total_price, '2013' as sale_date, 'China' as region
      from sale_detail;
      --不指定列的名字。
      create table sale_detail_ctas3
      as
      select shop_name, customer_id, total_price, '2013', 'China' 
      from sale_detail;
      说明

      如果在select子句中使用常量作为列的值,建议您指定列的名字。创建的表sale_detail_ctas3的第四、五列类似于_c4_c5

    • 示例5:创建一个新表sale_detail_like,与sale_detail具有相同的表结构,并设置生命周期。

      create table sale_detail_like like sale_detail lifecycle 10;

      您可以通过desc extended sale_detail_like;命令查看到表的结构及生命周期等详细信息。

      sale_detail_like的表结构与sale_detail完全相同。除生命周期属性外,列名、列注释以及表注释等均相同。但sale_detail中的数据不会被复制到sale_detail_like表中。

    • 示例6:创建一个新表mc_oss_extable_orc_like,与外部表mc_oss_extable_orc具有相同的表结构。

      create table mc_oss_extable_orc_like like mc_oss_extable_orc;

      您可以通过desc mc_oss_extable_orc_like;命令查看表结构等详细信息。

      +------------------------------------------------------------------------------------+
      | Owner: ALIYUN$****@***.aliyunid.com | Project: max_compute_7u************yoq              |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2022-08-11 11:10:47                                      |
      | LastDDLTime:              2022-08-11 11:10:47                                      |
      | LastModifiedTime:         2022-08-11 11:10:47                                      |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                                  |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field           | Type       | Label | Comment                                     |
      +------------------------------------------------------------------------------------+
      | id              | string     |       |                                             |
      | name            | string     |       |                                             |
      +------------------------------------------------------------------------------------+
    • 示例7:创建使用新数据类型的表test_newtype。

      set odps.sql.type.system.odps2=true;
      CREATE TABLE test_newtype (
          c1 TINYINT
          ,c2 SMALLINT
          ,c3 INT
          ,c4 BIGINT
          ,c5 FLOAT
          ,c6 DOUBLE
          ,c7 DECIMAL
          ,c8 BINARY
          ,c9 TIMESTAMP
          ,c10 ARRAY<MAP<BIGINT,BIGINT>>
          ,c11 MAP<STRING,ARRAY<BIGINT>>
          ,c12 STRUCT<s1:STRING,s2:BIGINT>
          ,c13 VARCHAR(20))
      LIFECYCLE 1
      ;
    • 示例8:创建Hash聚簇非分区表t1。

      create table t1 (a STRING, b STRING, c BIGINT) clustered by (c) sorted by (c) into 1024 buckets; 
    • 示例9:创建Hash聚簇分区表t2。

      create table t2 (a STRING, b STRING, c BIGINT) partitioned by (dt STRING) clustered by (c) sorted by (c) into 1024 buckets; 
    • 示例10:创建Range聚簇非分区表t3。

      create table t3 (a STRING, b STRING, c BIGINT) range clustered by (c) sorted by (c) into 1024 buckets;
    • 示例11:创建Range聚簇分区表t4。

      create table t4 (a STRING, b STRING, c BIGINT) partitioned by (dt STRING) range clustered by (c) sorted by (c); 
    • 示例12:创建Transactional非分区表t5。

      create table t5(id bigint) tblproperties("transactional"="true");
    • 示例13:创建Transactional分区表t6。

      create table if not exists t6(id bigint) partitioned by(ds string) tblproperties ("transactional"="true");
    • 示例14:创建非分区表test_default,并为字段指定默认值。

      create table test_default
      (
      tinyint_name tinyint not NULL default 1Y,
      smallint_name SMALLINT not NULL DEFAULT 1S,
      int_name INT not NULL DEFAULT 1,
      bigint_name BIGINT not NULL DEFAULT 1,
      binary_name BINARY ,
      float_name FLOAT ,
      double_name DOUBLE not NULL DEFAULT 0.1,
      decimal_name DECIMAL(2, 1) not NULL DEFAULT 0.0BD,
      varchar_name VARCHAR(10) ,
      char_name CHAR(2) ,
      string_name STRING not NULL DEFAULT 'N',
      boolean_name BOOLEAN not NULL DEFAULT TRUE
      );
    • 示例15:使用create table [if not exists] <table_name> [lifecycle <days>] as <select_statement>;语句创建内部表复制外部分区表数据,内部表不包含分区属性。

      -- MaxCompute侧查询湖仓一体外部表
      select * from hive_external2_1.myhive_0110;
      -- 结果
      a    b    c
      101    1    20230110
      102    2    20230110
      103    3    20230110
      
      -- 使用create table as创建内部表
      create table from_exetbl_as_par as select * from hive_external2_1.myhive_0110_par;
      
      -- 查询新建内部表
      select * from from_exetbl_as_par;
      -- 返回结果,全表数据都有
      a    b    c
      101    1    20230110
      102    2    20230110
      103    3    20230110
      
      
      -- 查询新建内部表结构
      desc from_exetbl_as_par;
      -- 返回结果
      +------------------------------------------------------------------------------------+
      | Owner:                    ALIYUN$***********                                       |
      | Project:                  ***_*****_***                                            |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2023-01-10 15:16:33                                      |
      | LastDDLTime:              2023-01-10 15:16:33                                      |
      | LastModifiedTime:         2023-01-10 15:16:33                                      |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 919                                                |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field           | Type       | Label | Comment                                     |
      +------------------------------------------------------------------------------------+
      | a               | string     |       |                                             |
      | b               | string     |       |                                             |
      | c               | string     |       |                                             |
      +------------------------------------------------------------------------------------+
    • 示例16:使用create table [if not exists] <table_name> like <existing_table_name> [lifecycle <days>];语句创建内部表复制外部分区表结构,内部表包含分区属性。

      -- MaxCompute侧查询湖仓一体外部表
      select * from hive_external2_1.myhive_0110_par;
      -- 返回结果
      a    b    c
      101    1    20230110
      102    2    20230110
      103    3    20230110
      
      -- 使用create table like创建内部表
      create table from_exetbl_like like hive_external2_1.myhive_0110_par;
      
      -- 查询新建内部表
      select * from from_exetbl_like;
      -- 返回结果,只有表结构出现
      a    b    c
      
      -- 查询内部表结构
      desc from_exetbl_like;
      -- 返回结果
      +------------------------------------------------------------------------------------+
      | Owner:                    ALIYUN$************                                      |
      | Project:                  ***_*****_***                                            |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2023-01-10 15:09:47                                      |
      | LastDDLTime:              2023-01-10 15:09:47                                      |
      | LastModifiedTime:         2023-01-10 15:09:47                                      |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                                  |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field           | Type       | Label | Comment                                     |
      +------------------------------------------------------------------------------------+
      | a               | string     |       |                                             |
      | b               | string     |       |                                             |
      +------------------------------------------------------------------------------------+
      | Partition Columns:                                                                 |
      +------------------------------------------------------------------------------------+
      | c               | string     |                                                     |
      +------------------------------------------------------------------------------------+
    • 示例17:创建Transaction Table2.0表

      create table mf_tt (pk bigint not null primary key, 
                          val bigint) 
                          tblproperties ("transactional"="true");
    • 示例18,创建Transaction Table2.0表,设置主要表属性

      create table mf_tt2 (pk bigint not null, 
                        pk2 bigint not null, 
                        val bigint, 
                        val2 bigint, primary key (pk, pk2)
                       ) 
                   tblproperties ("transactional"="true", 
                                  "write.bucket.num" = "64", 
                                  "acid.data.retain.hours"="120") 
                   lifecycle 7;

修改表的所有人

修改表的所有人,即表Owner。

  • 命令格式

    alter table <table_name> changeowner to <new_owner>;
  • 参数说明

    • table_name:必填。待修改Owner的表名。

    • new_owner:必填。修改后的Owner账号。如果要修改Owner为RAM用户,格式为:RAM$<UID>:<ram_name>,其中UID为阿里云账号的账号ID,ram_name为RAM用户显示名称。

      说明

      修改表Owner为RAM用户,需确保RAM用户已加入到表所在的项目中。

  • 使用示例

    • 将表test1的所有人修改为ALIYUN$xxx@aliyun.com。

      alter table test1 changeowner to 'ALIYUN$xxx@aliyun.com';
    • 将表test1的所有人修改为名称为ram_test的RAM用户。

      alter table test1 changeowner to 'RAM$13xxxxxxxxxxx:ram_test';

删除表

删除非分区表或分区表。

  • 注意事项

    • 请谨慎操作,确认表可以删除后,再执行删除操作。如果误删了表,当项目开启了备份恢复功能,且删除操作未超过项目设置的备份数据保留天数时,则可以恢复表。更多备份恢复信息,请参见备份与恢复

    • 删除表之后,MaxCompute项目的存储量会降低。

  • 命令格式

    drop table [if exists] <table_name>; 
  • 参数说明

    • if exists:可选。如果不指定if exists且表不存在,则返回异常。如果指定if exists,无论表是否存在,均返回成功。

    • table_name:必填。待删除的表名。

  • 使用示例

    --删除表sale_detail。无论sale_detail表是否存在,均返回成功。
    drop table if exists sale_detail; 

查看表或视图信息

查看MaxCompute内部表、视图、外部表、聚簇表或Transactional表的信息。如果您需要查看表的详细数据信息,请参见SELECT语法

  • 命令格式

    --查看表或视图信息。
    desc <table_name|view_name> [partition (<pt_spec>)]; 
    --查看外部表、聚簇表或Transactional表信息。也可以查看内部表的扩展信息。
    desc extended <table_name>; 
  • 参数说明

    • table_name:必填。待查看表的名称。

    • view_name:必填。待查看视图的名称。

    • pt_spec:可选。待查看分区表的指定分区。格式为(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)

    • extended:如果表为外部表、聚簇表或Transactional表,需要包含此参数。显示表的扩展信息。也可以查看内部表的扩展信息,例如列的非空属性。

  • 使用示例

    • 示例1:查看创建的test1表的信息。

      desc test1;

      返回结果如下。

      +------------------------------------------------------------------------------------+
      | Owner: ALIYUN$maoXXX@alibaba-inc.com | Project: $project_name                      |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2020-11-16 17:47:48                                      |
      | LastDDLTime:              2020-11-16 17:47:48                                      |
      | LastModifiedTime:         2020-11-16 17:47:48                                      |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                                  |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field           | Type       | Label | Comment                                     |
      +------------------------------------------------------------------------------------+
      | key             | string     |       |                                             |
      +------------------------------------------------------------------------------------+
    • 示例2:查看创建的sale_detail表的信息。

      desc sale_detail;

      返回结果如下。

      +--------------------------------------------------------------------+
      | Owner: ALIYUN$maoXXX@alibaba-inc.com | Project: $project_name      |
      | TableComment:                                                      |
      +--------------------------------------------------------------------+
      | CreateTime:               2017-06-28 15:05:17                      |
      | LastDDLTime:              2017-06-28 15:05:17                      |
      | LastModifiedTime:         2017-06-28 15:05:17                      |
      +--------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                  |
      +--------------------------------------------------------------------+
      | Native Columns:                                                    |
      +--------------------------------------------------------------------+
      | Field           | Type       | Label | Comment                     |
      +--------------------------------------------------------------------+
      | shop_name       | string     |       |                             |
      | customer_id     | string     |       |                             |
      | total_price     | double     |       |                             |
      +--------------------------------------------------------------------+
      | Partition Columns:                                                 |    
      +--------------------------------------------------------------------+
      | sale_date       | string     |                                     |
      | region          | string     |                                     |
      +--------------------------------------------------------------------+
    • 示例3:查看创建的sale_detail_ctas1表的详细信息。

      desc extended sale_detail_ctas1;

      返回结果如下。

      +------------------------------------------------------------------------------------+
      | Owner: ALIYUN$maoXXX@alibaba-inc.com | Project: $project_name                      |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2021-07-07 15:29:53                                      |
      | LastDDLTime:              2021-07-07 15:29:53                                      |
      | LastModifiedTime:         2021-07-07 15:29:53                                      |
      | Lifecycle:                10                                                       |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                                  |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
      +------------------------------------------------------------------------------------+
      | shop_name | string |       |               | true     | NULL         |              |
      | customer_id | string |       |               | true     | NULL         |              |
      | total_price | double |       |               | true     | NULL         |              |
      | sale_date | string |       |               | true     | NULL         |              |
      | region   | string |       |               | true     | NULL         |              |
      +------------------------------------------------------------------------------------+
      | Extended Info:                                                                     |
      +------------------------------------------------------------------------------------+
      | TableID:                  98cb8a38733c49eabed4735173818147                         |
      | IsArchived:               false                                                    |
      | PhysicalSize:             0                                                        |
      | FileNum:                  0                                                        |
      | StoredAs:                 AliOrc                                                   |
      | CompressionStrategy:      normal                                                   |
      +------------------------------------------------------------------------------------+

      sale_date和region两个字段仅会作为普通列存在,而不是表的分区。

    • 示例4:查看创建的sale_detail_ctas2表的信息。

      desc sale_detail_ctas2;

      返回结果如下。

      +--------------------------------------------------------------------+
      | Owner: ALIYUN$xxxxx@alibaba-inc.com | Project: $project_name       |
      | TableComment:                                                      |
      +--------------------------------------------------------------------+
      | CreateTime:               2017-06-28 15:42:17                      |
      | LastDDLTime:              2017-06-28 15:42:17                      |
      | LastModifiedTime:         2017-06-28 15:42:17                      |
      +--------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                  |
      +--------------------------------------------------------------------+
      | Native Columns:                                                    |
      +--------------------------------------------------------------------+
      | Field           | Type       | Label | Comment                     |
      +--------------------------------------------------------------------+
      | shop_name       | string     |       |                             |
      | customer_id     | string     |       |                             |
      | total_price     | double     |       |                             |
      | sale_date       | string     |       |                             |
      | region          | string     |       |                             |
      +--------------------------------------------------------------------+
    • 示例5:查看创建的sale_detail_like表的详细信息。

      desc extended sale_detail_like;

      返回结果如下。

      +------------------------------------------------------------------------------------+
      | Owner: ALIYUN$xxxxx@alibaba-inc.com | Project: $project_name                       |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2021-07-07 15:40:38                                      |
      | LastDDLTime:              2021-07-07 15:40:38                                      |
      | LastModifiedTime:         2021-07-07 15:40:38                                      |
      | Lifecycle:                10                                                       |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                                  |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
      +------------------------------------------------------------------------------------+
      | shop_name | string |       |               | true     | NULL         |              |
      | customer_id | string |       |               | true     | NULL         |              |
      | total_price | double |       |               | true     | NULL         |              |
      +------------------------------------------------------------------------------------+
      | Partition Columns:                                                                 |
      +------------------------------------------------------------------------------------+
      | sale_date       | string     |                                                     |
      | region          | string     |                                                     |
      +------------------------------------------------------------------------------------+
      | Extended Info:                                                                     |
      +------------------------------------------------------------------------------------+
      | TableID:                  61782ff7713f426e9d6f91d5deeac99a                         |
      | IsArchived:               false                                                    |
      | PhysicalSize:             0                                                        |
      | FileNum:                  0                                                        |
      | StoredAs:                 AliOrc                                                   |
      | CompressionStrategy:      normal                                                   |
      +------------------------------------------------------------------------------------+

      除生命周期属性外,sale_detail_like的其它属性(字段类型、分区类型等)均与sale_detail完全一致。

      说明

      通过desc table_name查看到的Size包含了在回收站的数据Size。如果您需要清空回收站,可以先执行purge table table_name,然后再执行desc table_name查看除回收站以外的数据大小。您也可以执行show recyclebin查看本项目中回收站内的数据明细。

    • 示例6:查看创建的test_newtype表的信息。

      desc test_newtype;

      返回结果如下。

      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field           | Type       | Label | Comment                                     |
      +------------------------------------------------------------------------------------+
      | c1              | tinyint    |       |                                             |
      | c2              | smallint   |       |                                             |
      | c3              | int        |       |                                             |
      | c4              | bigint     |       |                                             |
      | c5              | float      |       |                                             |
      | c6              | double     |       |                                             |
      | c7              | decimal    |       |                                             |
      | c8              | binary     |       |                                             |
      | c9              | timestamp  |       |                                             |
      | c10             | array<map<bigint,bigint>> |       |                              |
      | c11             | map<string,array<bigint>> |       |                              |
      | c12             | struct<s1:string,s2:bigint> |       |                            |
      | c13             | varchar(20) |       |                                            |
      +------------------------------------------------------------------------------------+
      
      OK
    • 示例7:查看创建的Hash聚簇非分区表t1的信息。聚簇属性将显示在Extended Info中。

      desc extended t1;

      返回结果如下。

      +------------------------------------------------------------------------------------+
      | Owner: ALIYUN$xxxxx@alibaba-inc.com | Project: $project_name                       |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2020-11-16 18:00:56                                      |
      | LastDDLTime:              2020-11-16 18:00:56                                      |
      | LastModifiedTime:         2020-11-16 18:00:56                                      |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                                  |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
      +------------------------------------------------------------------------------------+
      | a        | string |       |               | true     | NULL         |              |
      | b        | string |       |               | true     | NULL         |              |
      | c        | bigint |       |               | true     | NULL         |              |
      +------------------------------------------------------------------------------------+
      | Extended Info:                                                                     |
      +------------------------------------------------------------------------------------+
      | TableID:                  e6b06f705dc34a36a5b72e5af486cab7                         |
      | IsArchived:               false                                                    |
      | PhysicalSize:             0                                                        |
      | FileNum:                  0                                                        |
      | StoredAs:                 AliOrc                                                   |
      | CompressionStrategy:      normal                                                   |
      | ClusterType:              hash                                                     |
      | BucketNum:                1024                                                     |
      | ClusterColumns:           [c]                                                      |
      | SortColumns:              [c ASC]                                                  |
      +------------------------------------------------------------------------------------+
      
      OK
    • 示例8:查看创建的Hash聚簇分区表t2的信息。聚簇属性将显示在Extended Info中。

      desc extended t2;

      返回结果如下。

      +------------------------------------------------------------------------------------+
      | Owner: ALIYUN$xxxxx@alibaba-inc.com | Project: $project_name                       |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime: 2017-12-25 11:18:26                                                    |
      | LastDDLTime: 2017-12-25 11:18:26                                                   |
      | LastModifiedTime: 2017-12-25 11:18:26                                              |
      | Lifecycle: 2                                                                       |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES | Size: 0                                                       |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field | Type   | Label | Comment                                                   |
      +------------------------------------------------------------------------------------+
      | a     | string |       |                                                           |
      | b     | string |       |                                                           |
      | c     | bigint |       |                                                           |
      +------------------------------------------------------------------------------------+
      | Partition Columns:                                                                 |
      +------------------------------------------------------------------------------------+
      | dt    | string |                                                                   |
      +------------------------------------------------------------------------------------+
      | Extended Info:                                                                     |
      +------------------------------------------------------------------------------------+
      | TableID: 91a3395d3ef64b4d9ee1d2852755                                              |
      | IsArchived: false                                                                  |
      | PhysicalSize: 0                                                                    |
      | FileNum: 0                                                                         |
      | ClusterType: hash                                                                  |
      | BucketNum: 1024                                                                    |
      | ClusterColumns: [c]                                                                |
      | SortColumns: [c ASC]                                                               |
      +------------------------------------------------------------------------------------+
      
      OK
    • 示例9:查看Range聚簇非分区表t3的信息。聚簇属性将显示在Extended Info中。

      desc extended t3;

      返回结果如下。

      +------------------------------------------------------------------------------------+
      | Owner: ALIYUN$xxxxx@alibaba-inc.com | Project: $project_name                       |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2020-11-16 18:01:05                                      |
      | LastDDLTime:              2020-11-16 18:01:05                                      |
      | LastModifiedTime:         2020-11-16 18:01:05                                      |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                                  |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
      +------------------------------------------------------------------------------------+
      | a        | string |       |               | true     | NULL         |              |
      | b        | string |       |               | true     | NULL         |              |
      | c        | bigint |       |               | true     | NULL         |              |
      +------------------------------------------------------------------------------------+
      | Extended Info:                                                                     |
      +------------------------------------------------------------------------------------+
      | TableID:                  38d170aca2684f4baadbbe1931a6ae1f                         |
      | IsArchived:               false                                                    |
      | PhysicalSize:             0                                                        |
      | FileNum:                  0                                                        |
      | StoredAs:                 AliOrc                                                   |
      | CompressionStrategy:      normal                                                   |
      | ClusterType:              range                                                    |
      | BucketNum:                1024                                                     |
      | ClusterColumns:           [c]                                                      |
      | SortColumns:              [c ASC]                                                  |
      +------------------------------------------------------------------------------------+
      
      OK
    • 示例10:查看Range聚簇分区表t4的信息。聚簇属性将显示在Extended Info中。

      desc extended t4;

      返回结果如下。

      +------------------------------------------------------------------------------------+
      | Owner: ALIYUN$xxxxx@alibaba-inc.com | Project: $project_name                       |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2020-11-16 19:17:48                                      |
      | LastDDLTime:              2020-11-16 19:17:48                                      |
      | LastModifiedTime:         2020-11-16 19:17:48                                      |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                                  |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
      +------------------------------------------------------------------------------------+
      | a        | string |       |               | true     | NULL         |              |
      | b        | string |       |               | true     | NULL         |              |
      | c        | bigint |       |               | true     | NULL         |              |
      +------------------------------------------------------------------------------------+
      | Partition Columns:                                                                 |
      +------------------------------------------------------------------------------------+
      | dt              | string     |                                                     |
      +------------------------------------------------------------------------------------+
      | Extended Info:                                                                     |
      +------------------------------------------------------------------------------------+
      | TableID:                  6ebc3432e283449188c861427bcd6ee4                         |
      | IsArchived:               false                                                    |
      | PhysicalSize:             0                                                        |
      | FileNum:                  0                                                        |
      | StoredAs:                 AliOrc                                                   |
      | CompressionStrategy:      normal                                                   |
      | ClusterType:              range                                                    |
      | BucketNum:                0                                                        |
      | ClusterColumns:           [c]                                                      |
      | SortColumns:              [c ASC]                                                  |
      +------------------------------------------------------------------------------------+
      
      OK
    • 示例11:查看非分区表t5是否为Transactional表。

      说明

      推荐您使用MaxCompute客户端查看表是否为Transactional表,需要将MaxCompute客户端升级到0.35.4版本。其他工具可能会存在版本升级未就绪的问题,导致查询结果不显示Transactional信息。

      desc extended t5;

      返回结果如下。

      +------------------------------------------------------------------------------------+
      | Owner: ALIYUN$xxxxx@aliyun.com | Project: $project_name                            |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2021-02-18 10:56:27                                      |
      | LastDDLTime:              2021-02-18 10:56:27                                      |
      | LastModifiedTime:         2021-02-18 10:56:27                                      |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                                  |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
      +------------------------------------------------------------------------------------+
      | id       | bigint |       |               | true     | NULL         |              |
      +------------------------------------------------------------------------------------+
      | Extended Info:                                                                     |
      +------------------------------------------------------------------------------------+
      ...
      | Transactional:            true                                                     |
      +------------------------------------------------------------------------------------+
    • 示例12:查看分区表t6是否为Transactional表。

      说明

      推荐您使用MaxCompute客户端查看表是否为Transactional表,需要将MaxCompute客户端升级到0.35.4版本。其他工具可能会存在版本升级未就绪的问题,导致查询结果不显示Transactional信息。

      desc extended t6;

      返回结果如下。

      +------------------------------------------------------------------------------------+
      | Owner: ALIYUN$xxxxx@test.aliyunid.com | Project: $project_name                     |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2021-02-18 15:34:54                                      |
      | LastDDLTime:              2021-02-18 15:34:54                                      |
      | LastModifiedTime:         2021-02-18 15:34:54                                      |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                                  |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field           | Type       | Label | Comment                                     |
      +------------------------------------------------------------------------------------+
      | id              | bigint     |       |                                             |
      +------------------------------------------------------------------------------------+
      | Partition Columns:                                                                 |
      +------------------------------------------------------------------------------------+
      | ds              | string     |                                                     |
      +------------------------------------------------------------------------------------+
      | Extended Info:                                                                     |
      +------------------------------------------------------------------------------------+
      ...
      | Transactional:            true                                                     |
      +------------------------------------------------------------------------------------+

查看分区信息

查看某个分区表具体的分区的信息。

  • 命令格式

    desc <table_name> partition (<pt_spec>);
  • 参数说明

    • table_name:必填。待查看分区信息的分区表名称。

    • pt_spec:必填。待查看的分区信息。格式为partition_col1=col1_value1, partition_col2=col2_value1...。对于有多级分区的表,必须指明全部的分区值。

  • 使用示例

    --查询分区表sale_detail的分区信息。
    desc sale_detail partition (sale_date='201310',region='beijing');

    返回结果如下。

    +------------------------------------------------------------------------------------+
    | PartitionSize: 2109112                                                             |
    +------------------------------------------------------------------------------------+
    | CreateTime:               2015-10-10 08:48:48                                      |
    | LastDDLTime:              2015-10-10 08:48:48                                      |
    | LastModifiedTime:         2015-10-11 01:33:35                                      |
    +------------------------------------------------------------------------------------+
    OK

列出项目下的表和视图

列出项目下所有的表和视图,或符合某规则的表和视图。

  • 命令格式

    --列出项目下所有的表和视图。
    show tables;
    --列出项目下表名或视图名与chart匹配的表。
    show tables like '<chart>';
  • 使用示例

    --列出项目下表名与sale*匹配的表。*表示任意字段。
    show tables like 'sale*';              

    返回结果如下。

    ALIYUN$account_name:sale_detail
    ......
    --ALIYUN是系统提示符,表示您是阿里云主账号用户。如果您是阿里云RAM用户,系统提示符为RAM。

列出所有分区

列出一张表中的所有分区。当表不存在或为非分区表时,返回报错。

  • 命令格式

    show partitions <table_name>; 
  • 参数说明

    table_name:必填。待查看分区信息的分区表名称。

  • 使用示例

    --列出sale_detail中的所有分区。
    show partitions sale_detail;

    返回结果如下。

    sale_date=201310/region=beijing
    sale_date=201312/region=shenzhen
    sale_date=201312/region=xian
    sale_date=2014/region=shenzhen
    
    OK

相关文档

  • 本页导读 (1)
文档反馈