创建和删除表

使用限制

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

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

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

命令格式

• 创建新表。

  CREATE [OR REPLACE] [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>]
  [PATITIONED BY (<col_name> <data_type> [comment <col_comment>], ...)];

• 创建聚簇表。

  CREATE EXTERNAL TABLE [IF NOT EXISTS] <table_name> (
  <col_name> <data_type>, ... )
  [CLUSTERED BY | RANGE CLUSTERED BY (<col_name> [, <col_name>, ...]) 
  [SORTED BY (<col_name> [ASC | DESC] [, <col_name> [ASC | DESC] ...])] 
  INTO <number_of_buckets> buckets];

• 创建外部表。以OSS外部表为例。

  CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name> (
  <col_name> <data_type>, ... )
  STORED BY '<StorageHandler>'  
  WITH SERDEPROPERTIES (options) 
  LOCATION '<oss_location>';

• 创建表并指定表类型。

  • 指定表为Transactional表。后续可以对该表执行更新或删除表数据操作，但是Transactional表有部分使用限制，请根据需求创建。

    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>]
    [TBLPROPERTIES ("transactional"="true")];

  • 指定表为Delta Table表。结合PRIMARY KEY，后续可以做upsert、增量查询和Time Travel等操作。

    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>]
    [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> [LIFECYCLE <days>] LIKE <existing_table_name>;

参数说明

DROP TABLE IF EXISTS <tableName>;  -- 假如目标表存在，则先删除它
CREATE TABLE <tableName> ...;      -- 创建表

使用示例

• 创建非分区表。

  CREATE TABLE test1 (key STRING);

• 创建分区表。

  CREATE TABLE IF NOT EXISTS sale_detail(
   shop_name     STRING,
   customer_id   STRING,
   total_price   DOUBLE)
  PARTITIONED BY (sale_date STRING, region STRING);

• 创建新表，替换原有表。

  1. 创建原有表mytable，并写入数据。

     CREATE OR REPLACE TABLE my_table(a bigint);
     
     INSERT INTO my_table(a) VALUES (1),(2),(3);

  2. 使用OR REPLACE创建新的同名表，并修改字段。

     CREATE OR REPLACE TABLE my_table(b string);

  3. 查询my_table表，返回结果如下。

     +------------+
     | b          | 
     +------------+
     +------------+

  以下为非法的SQL语句：

  CREATE OR REPLACE TABLE IF NOT EXISTS my_table(b STRING);
  CREATE OR REPLACE TABLE my_table AS SELECT;
  CREATE OR REPLACE TABLE my_table LIKE newtable;

• 创建新表，将已有表数据复制到新表，并设置生命周期。

  -- 创建一个新表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列的非分区表。

• 创建新表，在SELECT语句中使用常量作为列的值。

  说明

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

  • 指定列的名字。

    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;

  • 不指定列的名字。

    SET odps.sql.allow.fullscan=true;
    
    CREATE TABLE sale_detail_ctas3
    AS
    SELECT shop_name, customer_id, total_price, '2013', 'China' 
    FROM 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表中。

• 创建新表，与外部表具有相同的表结构。

  -- 创建一个新表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     |       |                                             |
  +------------------------------------------------------------------------------------+

• 创建使用新数据类型的表。

  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;

• 创建Hash聚簇非分区表。

  CREATE TABLE t1 (a STRING, b STRING, c BIGINT) CLUSTERED BY (c) SORTED BY (c) INTO 1024 buckets;

• 创建Hash聚簇分区表。

  CREATE TABLE t2 (a STRING, b STRING, c BIGINT) 
  PARTITIONED BY (dt STRING) CLUSTERED BY (c) SORTED BY (c) into 1024 buckets;

• 创建Range聚簇非分区表。

  CREATE TABLE t3 (a STRING, b STRING, c BIGINT) RANGE CLUSTERED BY (c) SORTED BY (c) INTO 1024 buckets;

• 创建Range聚簇分区表。

  CREATE TABLE t4 (a STRING, b STRING, c BIGINT) 
  PARTITIONED BY (dt STRING) RANGE CLUSTERED BY (c) SORTED BY (c); 

• 创建Transactional非分区表。

  CREATE TABLE t5(id bigint) tblproperties("transactional"="true");

• 创建Transactional分区表。

  CREATE TABLE IF NOT EXISTS t6(id bigint) 
  PARTITIONED BY (ds string) tblproperties ("transactional"="true");

• 创建非分区表，并为字段指定默认值。

  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
  );

• 创建内部表复制外部分区表数据，内部表不包含分区属性。

  1. 创建OSS外部表和MaxCompute内部表。

     -- 创建oss外部表并插入数据。
     CREATE EXTERNAL table max_oss_test(a int, b int, c int) 
     stored AS TEXTFILE
     location "oss://oss-cn-hangzhou-internal.aliyuncs.com/<bucket_name>";
     
     INSERT INTO max_oss_test VALUES 
     (101, 1, 20241108),
     (102, 2, 20241109),
     (103, 3, 20241110);
     
     SELECT * FROM max_oss_test;
     
     -- 结果
     a    b    c
     101    1    20241108
     102    2    20241109
     103    3    20241110
     
     
     -- 使用create table as创建内部表
     CREATE TABLE from_exetbl_oss AS SELECT * FROM max_oss_test;
     
     -- 查询新建内部表
     SELECT * FROM from_exetbl_oss;
     
     -- 返回结果，全表数据都有
     a    b    c
     101    1    20241108
     102    2    20241109
     103    3    20241110

  2. 执行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     |       |                                             |
     +------------------------------------------------------------------------------------+

• 创建内部表复制外部分区表数据，内部表包含分区属性。

  1. 创建外部表和内部表。

     -- MaxCompute侧查询湖仓一体外部表
     SELECT * FROM max_oss_test;
     -- 返回结果
     a    b    c
     101    1    20241108
     102    2    20241109
     103    3    20241110
     
     -- 使用create table like创建内部表
     CREATE TABLE from_exetbl_like LIKE max_oss_test;
     
     -- 查询新建内部表
     SELECT * FROM from_exetbl_like;
     -- 返回结果，只有表结构出现
     a    b    c

  2. 执行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     |                                                     |
     +------------------------------------------------------------------------------------+

• 创建Delta Table表。

  CREATE TABLE mf_tt (pk bigint NOT NULL PRIMARY KEY, val bigint) 
    tblproperties ("transactional"="true");

• 创建Delta Table表，设置主要表属性。

  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;

注意事项

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

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

命令格式

DROP TABLE [IF EXISTS] <table_name>; 

参数说明

使用示例

--删除表sale_detail。无论sale_detail表是否存在，均返回成功。
DROP TABLE IF EXISTS sale_detail;