全部产品
MaxCompute

DDL 语句

更新时间:2017-08-24 17:07:41   分享:   

表操作

创建表

创建表的语法格式如下所示:

  1. CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
  2. [(col_name data_type [COMMENT col_comment], ...)]
  3. [COMMENT table_comment]
  4. [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  5. [LIFECYCLE days]
  6. [AS select_statement]
  7. [STORED BY StorageHandler] -- 仅限外部表
  8. [WITH SERDEPROPERTIES (Options)] -- 仅限外部表
  9. [LOCATION OSSLocation];-- 仅限外部表
  10. CREATE TABLE [IF NOT EXISTS] table_name
  11. LIKE existing_table_name

说明:

  • 在创建表时,如果不指定 if not exists 选项而存在同名表,则返回出错;若指定此选项,则无论是否存在同名表,即使原表结构与要创建的目标表结构不一致,均返回成功。已存在的同名表的元信息不会被改动;

  • 表名与列名均对大小写不敏感,不能有特殊字符,只能用英文的 a-z,A-Z 及数字和下划线_,且以字母开头,名称的长度不超过 128 字节;

  • 单表的列定义个数最多 10000 个;

  • 数据类型:bigint,double,boolean,datetime,decimal及string等,2.0版本扩展了很多数据类型

    注意:要使用新数据类型,需在建表语句前加语句set odps.sql.type.system.odps2=true;

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

  • 一张表最多允许 60000 个分区;单表的分区层次不能超过 6 级;

  • 注释内容是长度不超过 1024 字节的有效字符串;

  • lifecycle 表的生命周期,单位——天;create table like 语句不会复制源表的生命周期属性;

  • 有关外部表的更多介绍请参见: MaxCompute 上如何处理非结构化数据

示例如下:

假设创建表 sale_detail 来保存销售记录,该表使用销售时间(sale_date)和销售区域 (region)作为分区列,建表语句如下所示:

  1. create table if not exists sale_detail(
  2. shop_name string,
  3. customer_id string,
  4. total_price double)
  5. partitioned by (sale_date string,region string);
  6. -- 创建一张分区表 sale_detail

通过 create table … as select ..语句创建表,并在建表的同时将数据复制到新表中,如下所示:

  1. create table sale_detail_ctas1 as
  2. 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 子句中使用常量作为列的值,建议指定列的名字,如下所示:

  1. create table sale_detail_ctas2 as
  2. select shop_name,
  3. customer_id,
  4. total_price,
  5. '2013' as sale_date,
  6. 'China' as region
  7. from sale_detail;

如果不加列的别名,如下所示:

  1. create table sale_detail_ctas3 as
  2. select shop_name,
  3. customer_id,
  4. total_price,
  5. '2013',
  6. 'China'
  7. from sale_detail;

则创建的表 sale_detail_ctas3 的第四、五列会是类似_c5_c6。如果希望源表和目标表具有相同的表结构,可以尝试使用create table ... like操作,如下所示:

  1. create table sale_detail_like like sale_detail;

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

查看表信息

查看表信息的语法格式如下所示:

  1. desc <table_name>;

示例如下:

  • 假设查看上述示例中表 sale_detail 的信息,可输入如下命令:

    1. desc sale_detail;

    结果如下所示:

    1. odps@ $odps_project>desc sale_detail;
    2. +--------------------------------------------------------------------+
    3. | Owner: ALIYUN$maojing.mj@alibaba-inc.com | Project: $odps_project
    4. |
    5. | TableComment:
    6. |
    7. +--------------------------------------------------------------------+
    8. | CreateTime: 2017-06-28 15:05:17
    9. |
    10. | LastDDLTime: 2017-06-28 15:05:17
    11. |
    12. | LastModifiedTime: 2017-06-28 15:05:17
    13. |
    14. +--------------------------------------------------------------------+
    15. | InternalTable: YES | Size: 0
    16. |
    17. +--------------------------------------------------------------------+
    18. | Native Columns:
    19. |
    20. +--------------------------------------------------------------------+
    21. | Field | Type | Label | Comment
    22. |
    23. +--------------------------------------------------------------------+
    24. | shop_name | string | |
    25. |
    26. | customer_id | string | |
    27. |
    28. | total_price | double | |
    29. |
    30. +--------------------------------------------------------------------+
    31. | Partition Columns:
    32. |
    33. +--------------------------------------------------------------------+
    34. | sale_date | string |
    35. |
    36. | region | string |
    37. |
    38. +--------------------------------------------------------------------+
    39. OK
  • 假设查看上述示例表 sale_detail_like 中的信息,可输入如下命令:

    1. desc sale_detail_like

    结果如下所示:

    1. odps@ $odps_project>desc sale_detail_like;
    2. +--------------------------------------------------------------------+
    3. | Owner: ALIYUN$maojing.mj@alibaba-inc.com | Project: $odps_project
    4. |
    5. | TableComment:
    6. |
    7. +--------------------------------------------------------------------+
    8. | CreateTime: 2017-06-28 15:42:17
    9. |
    10. | LastDDLTime: 2017-06-28 15:42:17
    11. |
    12. | LastModifiedTime: 2017-06-28 15:42:17
    13. |
    14. +--------------------------------------------------------------------+
    15. | InternalTable: YES | Size: 0
    16. |
    17. +--------------------------------------------------------------------+
    18. | Native Columns:
    19. |
    20. +--------------------------------------------------------------------+
    21. | Field | Type | Label | Comment
    22. |
    23. +--------------------------------------------------------------------+
    24. | shop_name | string | |
    25. |
    26. | customer_id | string | |
    27. |
    28. | total_price | double | |
    29. |
    30. +--------------------------------------------------------------------+
    31. | Partition Columns:
    32. |
    33. +--------------------------------------------------------------------+
    34. | sale_date | string |
    35. |
    36. | region | string |
    37. |
    38. +--------------------------------------------------------------------+
    39. OK

    由上可见,除生命周期属性外,sale_detail_like 的其他属性(字段类型,分区类型等)均与 sale_detail 完全一致。查看表信息的更多介绍请参见: Describe Table。如果您查看表 sale_detail_ctas1 的信息,会发现 sale_date,region 两个字段仅会作为普通列存在,而不是表的分区。

删除表

删除表的语法格式如下所示:

  1. DROP TABLE [IF EXISTS] table_name;

注意:

  • 如果不指定 if exists 选项而表不存在,则返回异常;若指定此选项,无论表是否存在,皆返回成功;
  • 删除外部表时,OSS 上的数据不会被删除。

示例如下:

  1. create table sale_detail_drop like sale_detail;
  2. drop table sale_detail_drop;
  3. --若表存在,成功返回;若不存在,异常返回;
  4. drop table if exists sale_detail_drop2;
  5. --无论是否存在 sale_detail_drop2 表,均成功返回。

重命名表

重命名表的语法格式如下所示:

  1. ALTER TABLE table_name RENAME TO new_table_name;

注意:

  • rename 操作仅修改表的名字,不改动表中的数据;
  • 如果已存在与 new_table_name 同名表,报错;
  • 如果 table_name 不存在,则报错。

示例如下:

  1. create table sale_detail_rename1 like sale_detail;
  2. alter table sale_detail_rename1 rename to sale_detail_rename2;

修改表的注释

修改表的注释的语法格式如下所示:

  1. ALTER TABLE table_name SET COMMENT 'tbl comment';

注意:

  • table_name 必须是已存在的表;
  • comment 最长 1024 字节。

示例如下:

  1. alter table sale_detail set comment 'new coments for table sale_detail';

通过 MaxCompute 的desc命令可以查看表中 comment 的修改,请参见:常用命令-表操作中的 Describe Table

修改表的生命周期属性

MaxCompute 提供数据生命周期管理功能,方便用户释放存储空间,简化回收数据的流程。

修改表的生命周期属性的语法格式如下所示:

  1. ALTER TABLE table_name SET lifecycle days;

注意:

  • days 参数为生命周期时间,只接受正整数。单位:天;
  • 如果表 table_name 是非分区表,自最后一次数据被修改开始计算,经过 days 天后数据仍未被改动,则此表无需用户干预,将会被 MaxCompute 自动回收(类似 drop table 操作);
  • 在 MaxCompute 中,每当表的数据被修改后,表的 LastDataModifiedTime 将会被更新,因此,MaxCompute 会根据每张表的 LastDataModifiedTime 以及 lifecycle 的设置来判断是否要回收此表;
  • 如果 table_name 是分区表,则根据各分区的 LastDataModifiedTime 判断该分区是否该被回收。关于 LastDataModifiedTime 的介绍请参考 查看表信息
  • 不同于非分区表,分区表的最后一个分区被回收后,该表不会被删除。生命周期只能设定到表级别,不能再分区级设置生命周期。创建表时即可指定生命周期。

示例如下:

  1. create table test_lifecycle(key string) lifecycle 100;
  2. -- 新建test_lifecycle表,生命周期为100天。
  3. alter table test_lifecycle set lifecycle 50;
  4. -- 修改test_lifecycle表,将生命周期设为50天。

修改表的修改时间

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

修改表的修改时间的语法格式如下所示:

  1. ALTER TABLE table_name TOUCH;

注意:

  • table_name 不存在,则报错返回;
  • 此操作会改变表的”LastDataModifiedTime”的值,此时,MaxCompute 会认为表的数据有变动,生命周期的计算会重新开始。

清空非分区表里的数据

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

清空非分区表里的数据的语法格式如下所示:

  1. TRUNCATE TABLE table_name;

视图操作

创建视图

创建视图的语法格式如下所示:

  1. CREATE [OR REPLACE] VIEW [IF NOT EXISTS] view_name
  2. [(col_name [COMMENT col_comment], ...)]
  3. [COMMENT view_comment]
  4. [AS select_statement]

注意:

  • 创建视图时,必须有对视图所引用表的读权限;
  • 视图只能包含一个有效的 select 语句;
  • 视图可以引用其它视图,但不能引用自己,也不能循环引用;
  • 不允许向视图写入数据,例如使用 insert into 或者 insert overwrite 操作视图;
  • 当视图建好以后,如果视图的引用表发生了变更,有可能导致视图无法访问,例如:删除被引用表。用户需要自己维护引用表及视图之间的对应关系;
  • 如果没有指定 if not exists,在视图已经存在时用create view会导致异常。这种情况可以用 create or replace view 来重建视图,重建后视图本身的权限保持不变。

示例如下:

  1. create view if not exists sale_detail_view
  2. (store_name, customer_id, price, sale_date, region)
  3. comment 'a view for table sale_detail'
  4. as select * from sale_detail;

删除视图

删除视图的语法格式如下所示:

  1. DROP VIEW [IF EXISTS] view_name;

注意:

  • 如果视图不存在且没有指定 if exists,则报错。

示例如下:

  1. DROP VIEW IF EXISTS sale_detail_view;

重命名视图

重命名视图的语法格式如下所示:

  1. ALTER VIEW view_name RENAME TO new_view_name;

注意:

  • 如果已存在同名视图,则报错。

示例如下:

  1. create view if not exists sale_detail_view
  2. (store_name, customer_id, price, sale_date, region)
  3. comment 'a view for table sale_detail'
  4. as select * from sale_detail;
  5. alter view sale_detail_view rename to market;

分区/列操作

添加分区

添加分区的语法格式如下所示:

  1. ALTER TABLE TABLE_NAME ADD [IF NOT EXISTS] PARTITION partition_spec
  2. partition_spec:
  3. : (partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, ...)

注意:

  • 仅支持新增分区,不支持新增分区字段;
  • 如果未指定 if not exists 而同名的分区已存在,则出错返回;
  • 目前 MaxCompute 单表支持的分区数量上限为 6 万;
  • 对于多级分区的表,如果想添加新的分区,必须指明全部的分区值。

示例如下:

假设为表 sale_detail 添加一个分区,如下所示:

  1. alter table sale_detail add if not exists partition (sale_date='201312', region='hangzhou');
  2. -- 成功添加分区,用来存储201312月杭州地区的销售记录。
  3. alter table sale_detail add if not exists partition (sale_date='201312', region='shanghai');
  4. -- 成功添加分区,用来存储201312月上海地区的销售记录。
  5. alter table sale_detail add if not exists partition(sale_date='20111011');
  6. -- 仅指定一个分区sale_date,出错返回
  7. alter table sale_detail add if not exists partition(region='shanghai');
  8. -- 仅指定一个分区region,出错返回

删除分区

删除分区的语法格式如下所示:

  1. ALTER TABLE TABLE_NAME DROP [IF EXISTS] PARTITION partition_spec;
  2. partition_spec:
  3. : (partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, ...)

注意:

  • 如果分区不存在且未指定 if exists,则出错返回。

示例如下:

假设从表 sale_detail 中删除一个分区,如下所示:

  1. alter table sale_detail drop if exists partition(sale_date='201312',region='hangzhou');
  2. -- 成功删除 2013 12 月杭州分区的销售。

添加列

添加列的语法格式如下所示:

  1. ALTER TABLE table_name ADD COLUMNS (col_name1 type1, col_name2 type2...)

注意:

  • 添加的新列不支持指定顺序,默认在最后一列。

修改列名

修改列名的语法格式如下所示:

  1. ALTER TABLE table_name CHANGE COLUMN old_col_name RENAME TO new_col_name;

注意:

  • old_col_name 必须是已存在的列;
  • 表中不能有名为 new_col_name 的列。

修改列、分区注释

修改列、分区注释的语法格式如下所示:

  1. ALTER TABLE table_name CHANGE COLUMN col_name COMMENT comment_string;

注意:

  • COMMENT 内容最长 1024 字节。

同时修改列名及列注释

同时修改列名及列注释的语法格式如下所示:

  1. ALTER TABLE table_name CHANGE COLUMN old_col_name new_col_name column_type COMMENT column_comment;

注意:

  • old_col_name 必须是已存在的列;
  • 表中不能有名为 new_col_name 的列;
  • COMMENT 内容最长 1024 字节。

修改表、分区的修改时间

MaxCompute SQL 提供touch操作用来修改分区的LastDataModifiedTime。效果会将分区的LastDataModifiedTime修改为当前时间。

修改表、分区的修改时间的语法格式如下所示:

  1. ALTER TABLE table_name TOUCH PARTITION(partition_col='partition_col_value', ...);

注意:

  • table_name 或 partition_col 不存在,则报错返回;
  • 指定的 partition_col_value 不存在,则报错返回;
  • 此操作会改变表的”LastDataModifiedTime”的值,此时, MaxCompute 会认为表或分区的数据有变动,生命周期的计算会重新开始。

修改分区值

MaxCompute SQL 支持通过 rename 操作更改对应表的分区值。

修改分区值的语法格式如下所示:

  1. ALTER TABLE table_name PARTITION (partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, ...)
  2. RENAME TO PARTITION (partition_col1 = partition_col_newvalue1, partition_col2 = partiton_col_newvalue2, ...);

注意:

  • 不支持修改分区列列名,只能修改分区列对应的值;
  • 修改多级分区的一个或者多个分区值,多级分区的每一级的分区值都必须写上。
本文导读目录
本文导读目录
以上内容是否对您有帮助?