MaxCompute:Create and Drop Tables

Limits

• A partitioned table can have up to six levels of partitions, such as year/month/week/day/hour/minute.

• By default, a table can have up to 60,000 partitions, which can be adjusted according to business needs.

For detailed information on table limits, see Limits on SQL Usage.

Command Format

• Create an internal table.

  CREATE [OR REPLACE] TABLE [IF NOT EXISTS] <table_name> (
  <col_name> <data_type>, ... )
  [comment <table_comment>]
  [PARTITIONED 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 a foreign table.

  For instance, to create an OSS foreign table using the built-in text data parser, refer to Create an OSS Foreign Table.

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

• Create a table and specify its type.

  • Designate the table as transactional to enable updates or deletions post-creation, subject to certain restrictions. Consider your business requirements when creating a transactional table.

    CREATE [EXTERNAL] TABLE [IF NOT EXISTS] <table_name> (
    <col_name <data_type> [NOT NULL] [DEFAULT <default_value>] [comment <col_comment>], ...   
    [comment <table_comment>]
    [TBLPROPERTIES ("transactional"="true")];

  • Designate the table as a Delta table to utilize primary key operations, such as upserts, incremental queries, and time travel queries.

    CREATE [EXTERNAL] TABLE [IF NOT EXISTS] <table_name> (
    <col_name <data_type> [NOT NULL] [DEFAULT <default_value>] [comment <col_comment>], ...   
    [PRIMARY KEY (<pk_col_name>[, <pk_col_name2>, ...] )]) 
    [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.

  • Replicate data from an existing table to a new one, excluding partition properties. This can be done for foreign tables or tables from external projects implementing the data lakehouse solution.

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

  • Create a table with the same schema as an existing table without replicating its data. This applies to foreign tables or tables from external projects implementing the data lakehouse solution.

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

Parameter Description

DROP TABLE IF EXISTS <tableName>;  -- If the destination table exists, drop it first
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 the existing table mytable and write data to it.

     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 its fields.

     CREATE OR REPLACE TABLE my_table(b string);

  3. Query the my_table table to see the changes.

     +------------+
     | 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, and set a lifecycle for the new table.

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

  Run the DESC EXTENDED sale_detail_ctas1; command to view table details like schema and lifecycle.

  Note

  The sale_detail table is partitioned, yet the CREATE TABLE ... AS select_statement ... statement generates the sale_detail_ctas1 table without inheriting the partition attributes of the original table. In the new table, the partition key columns from the source table are treated as regular columns. As a result, the sale_detail_ctas1 table is a standard table with five columns and no partitioning.

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

  Note

  When using constants as column values in the SELECT clause, it's recommended to specify column names. Otherwise, columns like the fourth and fifth in the created table sale_detail_ctas3 will default to names such as _c4 and _c5.

  • Specify column names.

    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;

  • Do not specify column names.

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

  CREATE TABLE sale_detail_like LIKE sale_detail LIFECYCLE 10;

  Use the DESC EXTENDED sale_detail_like; command to view details like schema and lifecycle.

  Note

  The schema of the sale_detail_like table matches that of the sale_detail table, including properties like column names, comments, and table comments, except for the lifecycle. Data from the sale_detail table is not replicated to the sale_detail_like table.

• Create a table with the same schema as a foreign table.

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

  Run the DESC mc_oss_extable_orc_like; command to view the schema details.

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

• Create a transactional non-partitioned table.

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

• Create 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 set 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 from a partitioned external table. Partition properties are not replicated.

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

     -- Create an OSS foreign 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;
     
     -- Result
     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. Query the internal table DESC from_exetbl_as_par; to see the schema.

     +------------------------------------------------------------------------------------+
     | 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 from a partitioned external table, including partition properties.

  1. Create the internal table from_exetbl_like.

     -- Query the external table of the data lakehouse solution from the MaxCompute side
     SELECT * FROM max_oss_test;
     -- Result
     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. Query the internal table DESC from_exetbl_like; to see the schema.

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

Precautions

• Exercise caution when dropping a table. Ensure the table is eligible for deletion. If a table is dropped by mistake, it may be recoverable if the backup and restoration feature is active for the project and the table is dropped within the specified backup data retention period. For more information about backup and restoration, see Backup and Restoration.

• Dropping a table will reduce the amount of data stored in a MaxCompute project.

Command Format

DROP TABLE [IF EXISTS] <table_name>; 

Parameter Description

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;