MaxCompute:Create and Drop Tables

General parameters

DROP TABLE IF EXISTS <tableName>;  -- If the destination table exists, drop it first
CREATE TABLE <tableName> ...;      -- Create a table

Parameters for partitioned tables

MaxCompute supports two types of partitioned tables: regular partitioned tables and auto-partitioned tables (AUTO PARTITION). You can choose the appropriate type based on how you want partition columns to be generated in different scenarios. For more information, see Overview of partitioned tables.

Clustered table parameters

Clustered tables are classified into hash-clustered tables and range-clustered tables.

Parameters for foreign tables

The following example uses parameters for creating an OSS foreign table. For more information about how to create foreign tables, see Foreign Tables.

Transaction Table and Delta Table parameters

Parameters for creating tables based on existing data tables

• You can use the CREATE TABLE [IF NOT EXISTS] <table_name> [LIFECYCLE <days>] AS <select_statement>; statement to create a table and replicate data from an existing table to the new table.

  • However, partition properties and the lifecycle property of the source table are not replicated to the created table. The partition key columns of the source table are considered common columns in the created table.

  • You can also use the lifecycle parameter to reclaim the table. You can also execute this statement to create an internal table and replicate data of an external table to the internal table.

• You can use the CREATE TABLE [IF NOT EXISTS] <table_name> [LIFECYCLE <days>] LIKE <existing_table_name>; statement to create a table that has the same schema as an existing table.

  • However, tables created by using this statement do not replicate data or replicate the lifecycle property of the source table.

  • You can also use the lifecycle parameter to reclaim the table. You can also execute this statement to create an internal table that has the same schema as an existing external table.

Create a non-partitioned table

• Create a non-partitioned table

  CREATE TABLE test1 (key STRING);

• 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 a partitioned table

• Create a regular 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 an AUTO PARTITION table that generates partitions based on time-type data columns using time calculation functions.

  -- Truncate the sale_date column by month to generate a partition column named sale_month, and partition the table based on this column
  CREATE TABLE IF NOT EXISTS auto_sale_detail(
   shop_name     STRING,
   customer_id   STRING,
   total_price   DOUBLE,
   sale_date    DATE )
  AUTO PARTITIONED BY (TRUNC_TIME(sale_date, 'month') AS sale_month);

• Create an AUTO PARTITION table and specify partition generation based on data write time. The system automatically captures the time when data is written to MaxCompute and generates partitions using time calculation functions.

  -- After the table is created, when data is written, the system automatically captures the data write time _partitiontime, truncates it by day, generates a partition column named sale_date, and partitions the table based on this column
  CREATE TABLE IF NOT EXISTS auto_sale_detail2(
   shop_name     STRING,
   customer_id   STRING,
   total_price   DOUBLE,
   _partitiontime TIMESTAMP_NTZ)
  AUTO PARTITIONED BY (TRUNC_TIME(_partitiontime, 'day') AS sale_date)
  TBLPROPERTIES('ingestion_time_partition'='true');

Create a new table

• 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. The result shown in the following figure 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 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 and use 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.

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

  Run the DESC EXTENDED sale_detail_like; command to view table 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 that uses 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 hash/range clustered tables

• 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 transaction tables

• 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 internal tables

• Create an internal table and replicate data of a 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;
     
     -- 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. Execute DESC from_exetbl_as_par; to query the internal table structure. 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 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 the internal table
     SELECT * FROM from_exetbl_like;
     -- Only the schema of the internal table is returned
     a    b    c

  2. Execute DESC from_exetbl_like; to query the internal table structure. 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 Delta tables

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