MaxCompute:Create and drop tables

Limits

• A partitioned table can have a maximum of six levels of partitions. For example, if a table uses date columns as partition key columns, the six levels of the partitions are year/month/week/day/hour/minute.

• By default, a table can have a maximum of 60,000 partitions. You can adjust the maximum number of partitions in a table based on your business requirements.

For more information about the limits on tables, see MaxCompute SQL limits.

Syntax

• Create a table.

  CREATE [OR REPLACE] [EXTERNAL] TABLE [IF NOT EXISTS] <table_name> (
  <col_name> <data_type>, ... )
  [comment <table_comment>]
  [PATITIONED BY (<col_name> <data_type> [comment <col_comment>], ...)];

• Create a clustered table.

  CREATE 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];

• Create an external table. In this example, an Object Storage Service (OSS) external table is created.

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

• Create a table and specify the table type.

  • Create a transactional table. You can update or delete the data of the table after the table is created. However, specific limits are imposed on the transactional table. We recommend that you create a transactional table based on your business requirements.

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

  • Create a Delta table. You can use the primary key to perform operations on the table. For example, you can perform upserts, incremental queries, and time travel queries on the table.

    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 a table based on an existing table.

  • Create a table based on an existing table and replicate data from the existing table to the new table. Partition properties are not replicated. You can create a table based on an existing external table or an existing table of an external project that is used to implement the data lakehouse solution.

    CREATE TABLE [IF NOT EXISTS] <table_name> [LIFECYCLE <days>] AS <select_statement>;

  • Create a table that has the same schema as an existing table. Data in the existing table is not replicated. You can create a table based on an existing external table or an existing table of an external project that is used to implement the data lakehouse solution.

    CREATE TABLE [IF NOT EXISTS] <table_name> [LIFECYCLE <days>] LIKE <existing_table_name>;

Parameters

DROP TABLE IF EXISTS <tableName>;  -- If a table with the same name already exist, drop the table.
CREATE TABLE <tableName> ...;      -- Create a table.

Examples

• Create a non-partitioned table.

  CREATE TABLE test1 (key STRING);

• Create a partitioned table.

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

• Create a table to replace an existing table.

  1. Create a table named mytable and write data to the table.

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

  2. Use OR REPLACE to create a table with the same name and modify the table fields.

     CREATE OR REPLACE TABLE my_table(b string);

  3. Query data of the my_table table. The following result is returned:

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

  The following SQL statements are invalid:

  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;

• Create a table, replicate data from an existing table to the new table, and then configure the lifecycle for the new table.

  -- Create a table named sale_detail_ctas1, replicate data from the sale_detail table to the sale_detail_ctas1 table, and then configure the lifecycle for the sale_detail_ctas1 table. 
  SET odps.sql.allow.fullscan=true;
  CREATE TABLE sale_detail_ctas1 LIFECYCLE 10 AS SELECT * FROM sale_detail;

  You can execute the DESC EXTENDED sale_detail_ctas1; statement to view table details, such as the schema and lifecycle of a table.

  Note

  The sale_detail table is a partitioned table, but the sale_detail_ctas1 table that is created by using CREATE TABLE ... AS select_statement ... does not replicate partition properties. The partition key columns of the source table are considered common columns in the table that is created. The sale_detail_ctas1 table is a non-partitioned table that contains five columns.

• Create a table and use constants as column values in the SELECT clause.

  Note

  If you use constants as column values in the SELECT clause, we recommend that you specify column names. In this example, the names of the fourth and fifth columns in the sale_detail_ctas3 table contain suffixes that are similar to _c4 and _c5.

  • Column names are specified.

    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;

  • Column names are not specified.

    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 a table that uses the same schema as an existing table and configure the lifecycle for the new table.

  CREATE TABLE sale_detail_like LIKE sale_detail LIFECYCLE 10;

  You can execute the DESC EXTENDED sale_detail_like; statement to view table details, such as the schema and lifecycle of a table.

  Note

  The schema of the sale_detail_like table is the same as that of the sale_detail table. The two tables have the same properties, such as column names, column comments, and table comments, aside from the lifecycle. However, data in the sale_detail table is not replicated to the sale_detail_like table.

• Create a table that uses the same schema as an external table.

  -- Create a table named mc_oss_extable_orc_like that uses the same schema as the external table mc_oss_extable_orc. 
  CREATE TABLE mc_oss_extable_orc_like LIKE mc_oss_extable_orc;

  You can execute the DESC mc_oss_extable_orc_like; statement to view table details, such as the schema of a table.

  +------------------------------------------------------------------------------------+
  | 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     |       |                                             |
  +------------------------------------------------------------------------------------+

• Create a table that uses new data types.

  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;

• Create a hash-clustered non-partitioned table.

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

• Create a hash-clustered partitioned table.

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

• Create a range-clustered non-partitioned table.

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

• Create a range-clustered partitioned table.

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

• Creates a transactional non-partitioned table.

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

• Creates a transactional partitioned table.

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

• Create a non-partitioned table and configure default values for table fields.

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

• Create an internal table and replicate data of an partitioned external table to the internal table. Partition properties are not replicated to the internal table.

  1. Create an OSS external table and a MaxCompute internal table.

     -- Create an OSS external table and insert data into the table. 
     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;
     
     -- The following result is returned:
     a    b    c
     101    1    20241108
     102    2    20241109
     103    3    20241110
     
     
     -- Execute the CREATE TABLE AS statement to create an internal table.
     CREATE TABLE from_exetbl_oss AS SELECT * FROM max_oss_test;
     
     -- Query data of the internal table.
     SELECT * FROM from_exetbl_oss;
     
     -- All data in the internal table is returned.
     a    b    c
     101    1    20241108
     102    2    20241109
     103    3    20241110

  2. Execute the DESC from_exetbl_as_par; statement to query the schema of the internal table. The following result is returned:

     +------------------------------------------------------------------------------------+
     | 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     |       |                                             |
     +------------------------------------------------------------------------------------+

• Create an internal table and replicate data of a partitioned external table to the internal table. Partition properties are replicated to the internal table.

  1. Create an external table and an internal table.

     -- Query the external table of an external project that is used to implement the data lakehouse solution in MaxCompute.
     SELECT * FROM max_oss_test;
     -- The following result is returned:
     a    b    c
     101    1    20241108
     102    2    20241109
     103    3    20241110
     
     -- Execute the CREATE TABLE LIKE statement to create an internal table.
     CREATE TABLE from_exetbl_like LIKE max_oss_test;
     
     -- Query data of the internal table.
     SELECT * FROM from_exetbl_like;
     -- Only the schema of the internal table is returned.
     a    b    c

  2. Execute the DESC from_exetbl_like; statement to query the schema of the internal table. The following result is returned:

     +------------------------------------------------------------------------------------+
     | 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     |                                                     |
     +------------------------------------------------------------------------------------+

• Create a Delta table.

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

• Create a Delta table and configure the main table properties.

  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;

Usage notes

• Before you drop a table, confirm that the table can be dropped. Proceed with caution. If you accidentally drop a table, you can restore the table if the backup and restoration feature is enabled for the project and the table is dropped within the backup data retention period specified for the project. For more information about the backup and restoration feature, see Backup and restoration.

• After you drop a table, the volume of stored data in a MaxCompute project decreases.

Syntax

DROP TABLE [IF EXISTS] <table_name>; 

Parameters

Examples

-- Drop the sale_detail table. A success message is returned regardless of whether the sale_detail table exists. 
DROP TABLE IF EXISTS sale_detail;