All Products
Search

This Product

    MaxCompute:Create and drop tables

    Document Center

    MaxCompute:Create and drop tables

    Last Updated:Jan 20, 2026

    This topic describes the syntax, parameters, and examples for creating and dropping tables in MaxCompute.

    Statement

    Description

    Required permission

    Supported platforms

    CREATE TABLE

    Creates standard, partitioned, external, clustered, or transactional tables.

    CreateTable

    Supported platforms:

    DROP TABLE

    Drops a table and deletes its data.

    Drop (on the table)

    Create a table

    Creates a table in the current project.

    Limits

    • Maximum partition levels: 6 (e.g., year/month/week/day/hour/minute).

    • Maximum partitions per table: 60,000 (default).

    For a full list of limits, see MaxCompute SQL limits.

    Syntax

    • Standard table (partitioned or non-partitioned)

      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>], ...)]
[AUTO PARTITIONED 
   BY (<auto_partition_expression> [AS <auto_partition_column_name>])
  [TBLPROPERTIES('ingestion_time_partition'='true')]
];

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

    • External table (Example: OSS)

      See Create an OSS external 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>';

    • Transactional and Delta tables

      • Transactional table: Supports UPDATE/DELETE.

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

      • Delta table: Supports upserts, incremental queries, and time travel.

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

    • CTAS and LIKE clauses

      • CTAS: Creates a table and copies data. Partition properties are NOT copied.

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

      • Create Like: Copies the schema but not the data.

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

    Parameters

    Common parameters

    Parameter

    Required

    Description

    Remarks

    OR REPLACE

    No

    Replaces the table if it already exists.

    Equivalent to dropping the table (if it exists) and then creating it.

    Note

    Cannot be used with IF NOT EXISTS, AS SELECT, or LIKE.

    EXTERNAL

    No

    Creates an external table.

    N/A

    IF NOT EXISTS

    No

    Creates the table only if it does not already exist.

    If the table exists, the operation is ignored.

    table_name

    Yes

    The name of the table.

    Length limit: 128 bytes. Case-insensitive. Allowed characters: letters, digits, and underscores (_).

    PRIMARY KEY(pk)

    No

    Specifies primary key columns.

    Supported only for Delta tables. Must be NOT NULL and unique.

    col_name

    Yes

    The name of the column.

    Length limit: 128 bytes. Case-insensitive.

    COMMENT

    No

    Comment for the table or column.

    Max length: 1,024 bytes.

    data_type

    Yes

    The data type of the column.

    See Data types.

    NOT NULL

    No

    Prohibits NULL values in the column.

    See Modify table properties.

    DEFAULT

    No

    The default value used when none is specified.

    Dynamic functions like NOW() are not supported.

    LIFECYCLE

    No

    The data retention period in days.

    A positive integer.

    • Non-partitioned tables: Dropped if no data is modified for N days.

    • Partitioned tables: Partitions are dropped if no data is modified for N days. The table definition remains.

    Parameters for partitioned tables

    Standard Partitioned Tables

    Parameter

    Required

    Description

    Remarks

    PARTITIONED BY

    Yes

    Specifies partition columns.

    Mutually exclusive with AUTO PARTITIONED BY.

    col_name

    Yes

    The name of the partition column.

    Length limit: 128 bytes. Case-insensitive.

    data_type

    Yes

    The data type of the partition column.

    MaxCompute V1.0: STRING only.
    MaxCompute V2.0: TINYINT, SMALLINT, INT, BIGINT, VARCHAR, and STRING.

    AUTO PARTITION Tables

    Parameter

    Required

    Description

    Remarks

    AUTO PARTITIONED BY

    Yes

    Specifies auto-partitioning logic.

    Mutually exclusive with PARTITIONED BY.

    Expression

    Yes

    Expression for partition key generation.

    Currently supports TRUNC_TIME.

    Parameters for clustered tables

    Hash Clustered Tables

    Parameter

    Required

    Description

    Remarks

    CLUSTERED BY

    Yes

    Specifies the columns used for hash clustering.

    Best practice: Use high-cardinality columns or join keys.

    SORTED BY

    Yes

    Specifies the sort order within buckets.

    Should match CLUSTERED BY columns for optimal performance.

    INTO N BUCKETS

    Yes

    Specifies the number of buckets.

    Recommended: Powers of 2 (e.g., 512, 1024). Maximum: 4096.

    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 by copying (CTAS)

    -- Create table and copy data (partition properties are lost)
CREATE TABLE sale_detail_ctas1 LIFECYCLE 10 AS SELECT * FROM sale_detail;

    Drop a table

    Drops a table and deletes its data. This operation is irreversible.

    Precautions

    • Warning: Dropped tables cannot be recovered unless the backup and restore feature is enabled.

    Syntax

    DROP TABLE [IF EXISTS] <table_name>;

    Examples

    DROP TABLE IF EXISTS sale_detail;

    References