MaxCompute:CREATE TABLE

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 [external] table [if not exists] <table_name>
 [(<col_name> <data_type> [not null] [default <default_value>] [comment <col_comment>], ...)]
 [comment <table_comment>]
 [partitioned by (<col_name> <data_type> [comment <col_comment>], ...)]
 -- Configure the shuffle and sort properties of a clustered table that you want to create. 
 [clustered by | range clustered by (<col_name> [, <col_name>, ...]) [sorted by (<col_name> [asc | desc] [, <col_name> [asc | desc] ...])] into <number_of_buckets> buckets] 
 -- Used only for external tables. 
 [stored by StorageHandler] 
 -- Used only for external tables. 
 [with serdeproperties (options)] 
 -- Used only for external tables. 
 [location <osslocation>] 
 -- Set the table to a transactional table. You can later modify or delete the data of the transactional table. Transactional tables have specific limits. Create a transactional table base on your business requirements. 
 [tblproperties("transactional"="true")]   
 [lifecycle <days>];

-- Create a table based on an existing table and replicate data from the existing table to the new table. Partition properties are not replicated. 
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. 
create table [if not exists] <table_name> like <existing_table_name> [lifecycle <days>];

Parameters

Examples:

• Example 1: Create a non-partitioned table named test1.

  create table test1 (key STRING);

• Example 2: Create a partitioned table named sale_detail.

  create table if not exists sale_detail(
   shop_name     STRING,
   customer_id   STRING,
   total_price   DOUBLE)
  partitioned by (sale_date STRING, region STRING); 

• Example 3: 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.

  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.

  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 created table. The sale_detail_ctas1 is a non-partitioned table that has five columns.

• Example 4: Create a table named sale_detail_ctas2 and use constants as column values in the SELECT clause.

  -- Column names are specified. 
  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. 
  create table sale_detail_ctas3
  as
  select shop_name, customer_id, total_price, '2013', 'China' 
  from sale_detail;

  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.

• Example 5: Create a table named sale_detail_like that uses the same schema as the sale_detail table and configure the lifecycle for the sale_detail_like 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.

  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.

• Example 6: Create a table named test_newtype 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
  ;

• Example 7: Create a hash-clustered table named t1. This table is a non-partitioned table.

  create table t1 (a STRING, b STRING, c BIGINT) clustered by (c) sorted by (c) into 1024 buckets; 

• Example 8: Create a hash-clustered table named t2. This table is a partitioned table.

  create table t2 (a STRING, b STRING, c BIGINT) partitioned by (dt STRING) clustered by (c) sorted by (c) into 1024 buckets; 

• Example 9: Create a range-clustered table named t3. This table is a non-partitioned table.

  create table t3 (a STRING, b STRING, c BIGINT) range clustered by (c) sorted by (c) into 1024 buckets;

• Example 10: Create a range-clustered table named t4. This table is a partitioned table.

  create table t4 (a STRING, b STRING, c BIGINT) partitioned by (dt STRING) range clustered by (c) sorted by (c); 

• Example 11: Create a transactional table named t5. This table is a non-partitioned table.

  create table t5(id bigint) tblproperties("transactional"="true");

• Example 12: Create a transactional table t6. This table is a partitioned table.

  create table if not exists t6(id bigint) partitioned by(ds string) tblproperties ("transactional"="true");

Related statements

• ALTER TABLE: Alters information about a table.

• TRUNCATE: Clears data in a specified table.

• DROP TABLE: Drops a partitioned table or a non-partitioned table.

• DESC TABLE/VIEW: Views the information about MaxCompute internal tables, views, materialized views, external tables, clustered tables, or transactional tables.

• SHOW: Queries SQL DDL statements that are used to list all tables and views in a project or list all partitions in a table.