MaxCompute:Create and drop tables

Common 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 select a type based on how you want partition key columns to be generated in different scenarios.

Parameters for clustered tables

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

Parameters for external tables

The following example shows the parameters for creating an OSS external table. For more information about how to create external tables, see External tables.

Parameters for Transaction Table and Delta Table

Parameters for creating tables based on existing 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 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 uses the same schema as an existing table.

  • However, tables created using this statement do not replicate data or 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 and use a time calculation function to generate partitions based on the time-type data column.

  -- Truncate the sale_date column by month to generate a partition column named sale_month, and partition the table based on this partition 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 that partitions are generated based on the data write time. In this case, the system automatically obtains the time when data is written to MaxCompute and generates partitions using the time calculation function.

  -- After the table is created, when data is written to the table, the system automatically obtains the data write time _partitiontime, truncates it by day, generates a partition column named sale_date, and partitions the table based on this partition 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 original table 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 the my_table table. The following code shows the returned result:

     +------------+ | 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 configure the lifecycle. 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; command to view the table structure, lifecycle, and other details.

  Note

  The source table sale_detail is a partitioned table. However, when the new sale_detail_ctas1 table is created using the CREATE TABLE ... AS select_statement ... statement, partition properties are not replicated. The partition key columns of the source table are treated as common columns. Therefore, sale_detail_ctas1 is a non-partitioned table with 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. The fourth and fifth columns of the created sale_detail_ctas3 table 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; command to view the table structure, lifecycle, and other details.

  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 has the same schema as an external table.

  -- Create a table named mc_oss_extable_orc_like that has 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; command to view the table structure and other 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 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 or range-clustered table

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

• 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 an internal table

• 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. 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 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 structure of the internal table. The following information 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 internal table named from_exetbl_like.

     -- Query the data lakehouse external table from MaxCompute. 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 structure of the internal table. The following information 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 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;