本文为您介绍如何通过客户端创建、查看、删除、重命名和修改表信息。

创建表

创建表的语法格式,如下所示。
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [DEFAULT value] [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name [, col_name, ...]) [SORTED BY (col_name [ASC | DESC] [, col_name [ASC | DESC] ...])] INTO number_of_buckets BUCKETS] -- 用于创建Hash Clustering表时设置表的Shuffle和Sort属性。
[STORED BY StorageHandler] -- 仅限外部表。
[WITH SERDEPROPERTIES (Options)] -- 仅限外部表。
[LOCATION OSSLocation]; -- 仅限外部表。
[LIFECYCLE days]
[AS select_statement]
 CREATE TABLE [IF NOT EXISTS] table_name
 LIKE existing_table_name
  • 创建表时,如果不指定if not exists选项而存在同名表,则返回出错。若指定此选项,则无论是否存在同名表,即使原表结构与要创建的目标表结构不一致,均返回成功。已存在的同名表的元信息不会被改动。
  • 表名与列名均对大小写不敏感,不能有特殊字符,只能用英文的a-z,A-Z及数字和下划线_,且建议以字母开头,名称的长度不超过128字节。
  • 单表的列定义个数最多1200个。
  • 数据类型:BIGINT、DOUBLE、BOOLEAN、DATETIME、DECIMAL和STRING等,MaxCompute2.0版本扩展了很多数据类型
    说明 目前MaxCompute SQL中使用到新数据类型(TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP、BINARY)时,需要用set命令开启:
    • Session级别:要使用新数据类型,需在SQL语句前加上set语句set odps.sql.type.system.odps2=true;,并与SQL语句一起提交执行。
    • Project级别:支持对Project级别进行新类型打开。Project Owner可根据需要对Project进行设置,命令为:
      setproject odps.sql.type.system.odps2=true;
      setproject的详细说明请参见其他操作。关于Project级别开启数据类型时的注意事项,请参见数据类型
  • DEFAULT value 指定列的默认值,当INSERT操作不指定该列时,该列写入默认值。
  • partitioned by指定表的分区字段,目前支持TINYINT、SMALLINT、INT、BIGINT、VARCHAR和STRING类型。

    分区值不允许有双字节字符(如中文),必须是以英文字母a-z,A-Z开始后可跟字母数字,名称的长度不超过128字节。允许的字符包括:空格、冒号(:)、下划线(_)、美元符($)、井号(#)、点(.),感叹号(!)和(@),出现其他字符行为未定义,例如(\t)、(\n)、(/)等。当利用分区字段对表进行分区时,新增分区、更新分区内数据和读取分区数据均不需要做全表扫描,可以提高处理效率。

  • 一张表最多允许60000个分区,单表的分区层次不能超过6级。
  • 注释内容是长度不超过1024字节的有效字符串。
  • Lifecycle表的生命周期,单位:天。create table like语句不会复制源表的生命周期属性。
  • clustered by指定Hash Key。MaxCompute将对指定列进行Hash运算,按照Hash值分散到各个Bucket里面。
    • 为避免数据倾斜和热点,取得较好的并行执行效果,clustered by列适宜选择取值范围大,重复键值少的列。此外,为了达到join优化的目的,也应该考虑选取常用的Join/Aggregation Key,即类似于传统数据库中的主键。
    • sorted by用于指定在Bucket内字段的排序方式。建议sorted byclustered by一致,以取得较好的性能。此外,当sorted by子句指定之后,MaxCompute将自动生成索引,并且在查询的时候利用索引来加快执行。
    • INTO number_of_buckets BUCKETS指定了哈希桶的数目。这个数字必须填写,且由数据量大小来决定。此外,缺省条件下MaxCompute只能支持最多1111个Reducer,所以此处最多也只支持1111个哈希桶。您可以使用set odps.sql.reducer.instances=xxx;来提升这个限制,但最大不得超过4000,否则会影响性能。

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

      • 哈希桶大小适中:经验值是每个Bucket的大小在500M左右比较合理。例如,分区大小估计为500G,初略估算Bucket数目应该设为1000,这样平均每个Bucket大小约为500M。对于特别大的表,500M的限制可以突破,每个Bucket在2-3G左右比较合适。同时,可以结合set odps.sql.reducer.instances=xxx;来突破1111个桶的限制。
      • 对于需要经常join的两个表,哈希桶数目应设为一样,这样才能够优化join,省略掉Shuffle和Sort步骤。如果按照上述原则计算两个表的哈希桶数不一致,建议统一使用数字大的Bucket Number,保证合理的并发度和执行效率。
    • Hash Clustering表还有以下优点:
      • 优化Bucket Pruning。
      • 优化Aggregation。
      • 优化存储。
    • Hash Clustering表有以下限制:
      • 不支持insert into,只能通过insert overwrite来添加数据。
      • 不支持Tunnel直接Upload到Range Cluster表,因为Tunnel上传数据是无序的。
  • 有关外部表的更多详情请参见访问OSS非结构化数据
假设创建表sale_detail来保存销售记录,该表使用销售时间sale_date和销售区域 region作为分区列,建表语句如下所示。
create table if not exists sale_detail
(
shop_name     string,
customer_id   string,
total_price   double
)
partitioned by (sale_date string,region string);
-- 创建一张分区表sale_detail。
通过create table...as select...语句创建表,并在建表的同时将数据复制到新表中,如下所示。
create table sale_detail_ctas1 as
select * from sale_detail;
此时,如果表sale_detail中存在数据,上面的示例会将表sale_detail的数据全部复制到表sale_detail_ctas1
说明 此处sale_detail是一张分区表,而通过create table...as select...语句创建的表不会复制分区属性,只会把源表的分区列作为目标表的一般列处理,即sale_detail_ctas1是一个含有5列的非分区表。
create table...as select...语句中,如果在select子句中使用常量作为列的值,建议指定列的名字,如下所示。
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;

则创建的表sale_detail_ctas3的第四、五列类似于_c5_c6

如果希望源表和目标表具有相同的表结构,可以尝试使用create table...like操作,如下所示:
create table sale_detail_like like sale_detail;

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

创建Hash Clustering表示例

CREATE TABLE T1 (a string, b string, c bigint) CLUSTERED BY (c) SORTED by (c) INTO 1024 BUCKETS; --创建Hash Clustering非分区表。
CREATE TABLE T1 (a string, b string, c bigint) PARTITIONED BY (dt string) CLUSTERED BY (c) SORTED by (c) INTO 1024 BUCKETS; --创建Hash Clustering分区表。

查看表信息

查看表信息的语法格式,如下所示。
desc <table_name>;
desc extended <table_name>; --查看外部表信息。
示例
  • 假设查看上述示例中表sale_detail的信息,可输入如下命令。
    desc sale_detail;
    结果如下所示。
    odps@ $odps_project>desc sale_detail;
    +--------------------------------------------------------------------+
    | Owner: ALIYUN$maojing.mj@alibaba-inc.com | Project: $odps_project
                      |
    | 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     |
       |
    +--------------------------------------------------------------------+
    OK
  • 假设查看上述示例表sale_detail_like中的信息,可输入如下命令。
    desc sale_detail_like
    结果如下所示。
    odps@ $odps_project>desc sale_detail_like;
    +--------------------------------------------------------------------+
    | Owner: ALIYUN$maojing.mj@alibaba-inc.com | Project: $odps_project
                      |
    | 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     |       |
       |
    +--------------------------------------------------------------------+
    | Partition Columns:
       |
    +--------------------------------------------------------------------+
    | sale_date       | string     |
       |
    | region          | string     |
       |
    +--------------------------------------------------------------------+
    OK
可见,除生命周期属性外,sale_detail_like的其它属性(字段类型,分区类型等)均与sale_detail完全一致。查看表信息的更多详情请参见表操作
说明 通过describe table查看到的Size包含了在回收站的数据Size。如果您需要清空回收站,可以先执行purge table table_name,然后再执行describe table查看除回收站以外的数据大小。您也可以执行show recyclebin查看本项目中回收站内的数据明细。

如果您查看表sale_detail_ctas1的信息,会发现sale_dateregion两个字段仅会作为普通列存在,而不是表的分区。

  • 随着MaxCompute类型系统的不断扩充(参见数据类型),执行desc命令后可以返回的类型也随着增多。要使用MaxCompute新类型,SQL语句都需要配合设置开关新类型打开语句,但desc命令无需打开便可返回新类型。
    说明 若对desc table的输出结果有解析依赖,请及时更新适配解析MaxCompute新数据类型。
    示例
    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
    ;
    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) |       |                                            |
    +------------------------------------------------------------------------------------+
您也可以使用DESC EXTENDED table_name;命令查看Hash Clustering Table的Clustering属性,如下所示,Clustering属性将显示在Extended Info中。

+------------------------------------------------------------------------------------+
| Owner: ALIYUN$xxxxxxx@aliyun.com | Project: xxxxx |
| 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: 91a3395d3ef64b4d9ee1d28527552864 |
| IsArchived: false |
| PhysicalSize: 0 |
| FileNum: 0 |
| ClusterType: hash |
| BucketNum: 1024 |
| ClusterColumns: [c] |
| SortColumns: [c ASC] |
+------------------------------------------------------------------------------------+
对于聚集属性分区表,除了可以使用上述命令查看Table属性,还可以通过以下命令查看分区的属性。
DESC EXTENDED table_name partition(pt_spec);

使用select命令查看表中的详细信息,请参见SELECT语法介绍

查看建表语句

查看建表语句的语法格式,如下所示。
SHOW CREATE TABLE <table_name>;
说明 该命令可生成创建Table的SQL DDL语句,方便您通过SQL重建Schema。

删除表

删除表的语法格式,如下所示。
DROP TABLE [IF EXISTS] table_name;
说明
  • 如果不指定if exists选项而表不存在,则返回异常。若指定此选项,无论表是否存在,皆返回成功。
  • 删除外部表时,OSS上的数据不会被删除。
示例
create table sale_detail_drop like sale_detail;
    drop table sale_detail_drop;
    --若表存在,成功返回;若不存在,异常返回。
    drop table if exists sale_detail_drop2;
    --无论是否存在sale_detail_drop2表,均成功返回。

重命名表

重命名表的语法格式,如下所示。
ALTER TABLE table_name RENAME TO new_table_name;
说明
  • rename操作仅修改表的名字,不改动表中的数据。
  • 如果已存在与new_table_name同名表,则报错。
  • 如果table_name不存在,则报错。
示例
create table sale_detail_rename1 like sale_detail;
alter table sale_detail_rename1 rename to sale_detail_rename2;

修改表Owner

MaxCompute SQL支持通过changeowner命令来修改表的拥有人(表Owner),相应的语法格式如下所示。
alter table table_name changeowner to 'ALIYUN$xxx@aliyun.com';

修改表的注释

修改表的注释的语法格式,如下所示。
ALTER TABLE table_name SET COMMENT 'tbl comment';
说明
  • table_name必须是已存在的表。
  • comment最长1024字节。
示例
alter table sale_detail set comment 'new coments for table sale_detail';

通过MaxCompute的desc命令可以查看表中comment的修改,详情请参见常用命令>表操作中的describe table

修改表的修改时间

MaxCompute SQL提供touch操作用来修改表的LastDataModifiedTime,可将表的LastDataModifiedTime修改为当前时间。

修改表的修改时间的语法格式,如下所示。
ALTER TABLE table_name TOUCH;
说明
  • table_name不存在,则报错返回。
  • 此操作会改变表的LastDataModifiedTime的值,此时,MaxCompute会认为表的数据有变动,生命周期的计算会重新开始。

修改表的Hash Clustering属性

对于分区表,我们支持通过alter table语句增加或者去除Hash Clustering属性。

增加表的Hash Clustering属性语句,如下所示。

ALTER TABLE table_name     
[CLUSTERED BY (col_name [, col_name, ...]) [SORTED BY (col_name [ASC | DESC] [, col_name [ASC | DESC] ...])] INTO number_of_buckets BUCKETS]
去除表的Hash Clustering属性的语法格式,如下所示。
ALTER TABLE table_name NOT CLUSTERED;
说明
  • alter table改变聚集属性,只对于分区表有效,非分区表一旦聚集属性建立就无法改变。
  • 由于alter table只影响新分区,所以该语句不可以再指定partition alter table语句适用于存量表,在增加了新的聚集属性之后,新的分区将做Hash Cluster存储。

清空非分区表里的数据

将指定的非分区表中的数据清空,该命令不支持分区表。对于分区表,可以用ALTER TABLE table_name DROP PARTITION的方式将分区里的数据清除。

清空非分区表里的数据的语法格式,如下所示。
TRUNCATE TABLE table_name;