Maxcompute SQL语法

DDL

• 表操作

  •  创建表

    --创建新表。
     create [external] table [if not exists] <table_name>
     [(<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>] 
     --指定表为Transactional表，后续可以对该表执行更新或删除表数据操作，但是Transactional表有部分使用限制，请根据需求创建。
     [tblproperties("transactional"="true")]   
     [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>];

  • 使用示例
    • 示例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中，并设置生命周期。

      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子句中使用常量作为列的值。

      --指定列的名字。
      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：创建使用新数据类型的表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
      ;

    • 示例7：创建Hash聚簇非分区表t1。

      create table t1 (a STRING, b STRING, c BIGINT) clustered by (c) sorted by (c) into 1024 buckets; 

    • 示例8：创建Hash聚簇分区表t2。

      create table t2 (a STRING, b STRING, c BIGINT) partitioned by (dt STRING) clustered by (c) sorted by (c) into 1024 buckets; 

    • 示例9：创建Range聚簇非分区表t3。

      create table t3 (a STRING, b STRING, c BIGINT) range clustered by (c) sorted by (c) into 1024 buckets;

    • 示例10：创建Range聚簇分区表t4。

      create table t4 (a STRING, b STRING, c BIGINT) partitioned by (dt STRING) range clustered by (c) sorted by (c); 

    • 示例11：创建Transactional非分区表t5。

      create table t5(id bigint) tblproperties("transactional"="true");

    • 示例12：创建Transactional分区表t6。

      create table if not exists t6(id bigint) partitioned by(ds string) tblproperties ("transactional"="t

  •  修改表的所有人
    • 命令格式

      alter table <table_name> changeowner to <new_owner>;

    • 使用示例

      --将表test1的所有人修改为ALIYUN$xxx@aliyun.com。
      alter table test1 changeowner to 'ALIYUN$xxx@aliyun.com';

  •  重命名表：重命名表的名称。仅修改表的名字，不改动表中的数据。
    • 命令格式

      alter table <table_name> rename to <new_table_name>;

    • 使用示例

      alter table sale_detail rename to sale_detail_rename;

  • 清空非分区表里的数据：将指定的非分区表中的数据清空，该命令不支持分区表。对于分区表，可以用alter table table_name drop partition命令清除分区中的数据。

    • 命令格式

      truncate table <table_name>;

  • 删除表：删除非分区表或分区表。

    • 命令格式

      drop table [if exists] <table_name>; 

    • 使用示例

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

  •  查看表或视图信息
    • 命令格式

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

  •  查看建表语句
    • 命令格式

      show create table <table_name>;

    • 使用示例

      --查看表sale_detail的建表语句。
      show create table sale_detail;

      返回结果如下。

      CREATE TABLE IF NOT EXISTS doc_test_dev.sale_detail(shop_name STRING,customer_id STRING,total_price DOUBLE) PARTITIONED BY (sale_date STRING,region STRING) STORED AS ALIORC;