MaxCompute：CREATE TABLE

限制條件

• 分區表的分區層級不能超過6級。例如某張表以日期為分區列，分區層級為年/月/周/日/時/分。

• 一張表允許的分區個數支援按照具體的專案配置，預設為6萬個。

更多表的限制條件，請參見SQL使用限制項。

命令格式

建立內部表

CREATE [OR REPLACE] TABLE [IF NOT EXISTS] <table_name> (
<col_name> <data_type>, ... )
[comment <table_comment>]
[PATITIONED BY (<col_name> <data_type> [comment <col_comment>], ...)];

建立聚簇表

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

建立外部表格

以OSS外部表格為例，通過內建文本資料解析器建立外部表格。

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表。後續可以對該表執行更新或刪除表資料操作，但是Transactional表有部分使用限制，請根據需求建立。

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

• 指定表為Delta Table表。結合PRIMARY KEY，後續可以做upsert、增量查詢和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>];

基於已存在表建立新表

• 基於已存在的表建立新表並複製資料，但不複製分區屬性。支援外部表格和湖倉一體外部項目中的表。

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

• 基於已存在的表建立具備相同結構的新表，但不複製資料。支援外部表格和湖倉一體外部項目中的表。

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

參數說明

DROP TABLE IF EXISTS <tableName>;  -- 假如目標表存在，則先刪除它
CREATE TABLE <tableName> ...;      -- 建立表

使用樣本

建立非分區表

CREATE TABLE test1 (key STRING);

建立分區表

建立一張分區表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);

建立新表替換原表

1. 建立原有表mytable，並寫入資料。

   CREATE OR REPLACE TABLE my_table(a bigint);
   
   INSERT INTO my_table(a) VALUES (1),(2),(3);

2. 使用OR REPLACE建立新的同名表，並修改欄位。

   CREATE OR REPLACE TABLE my_table(b string);

3. 查詢my_table表，返回結果如下。

   +------------+
   | b          | 
   +------------+
   +------------+

以下為非法的SQL語句：

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;

建立新表並複製已有表資料至新表

建立新表，將已有表資料複製到新表，並設定生命週期。

-- 建立一個新表sale_detail_ctas1，將sale_detail的資料複製到sale_detail_ctas1中，並設定生命週期。
SET odps.sql.allow.fullscan=true;
CREATE TABLE sale_detail_ctas1 LIFECYCLE 10 AS SELECT * FROM sale_detail;

您可以通過DESC EXTENDED sale_detail_ctas1;命令查看到表的結構及生命週期等詳細資料。

建立新表並將SELECT語句常量作為列值

• 指定列的名字。

  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;

• 不指定列的名字。

  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 TABLE sale_detail_like LIKE sale_detail LIFECYCLE 10;

您可以通過DESC EXTENDED sale_detail_like;命令查看到表的結構及生命週期等詳細資料。

建立新表與外部表格結構相同

-- 建立一個新表mc_oss_extable_orc_like，與外部表格mc_oss_extable_orc具有相同的表結構。
CREATE TABLE mc_oss_extable_orc_like LIKE mc_oss_extable_orc;

您可以通過DESC mc_oss_extable_orc_like;命令查看錶結構等詳細資料。

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

建立使用新資料類型的表

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;

建立Hash聚簇表

• 非分區表

  CREATE TABLE t1 (a STRING, b STRING, c BIGINT) CLUSTERED BY (c) SORTED BY (c) INTO 1024 buckets;

• 分區表

  CREATE TABLE t2 (a STRING, b STRING, c BIGINT) 
  PARTITIONED BY (dt STRING) CLUSTERED BY (c) SORTED BY (c) into 1024 buckets;

建立Range聚簇表

• 非分區表

  CREATE TABLE t3 (a STRING, b STRING, c BIGINT) RANGE CLUSTERED BY (c) SORTED BY (c) INTO 1024 buckets;

• 分區表

  CREATE TABLE t4 (a STRING, b STRING, c BIGINT) 
  PARTITIONED BY (dt STRING) RANGE CLUSTERED BY (c) SORTED BY (c); 

建立Transactional表

• 非分區表

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

• 分區表

  CREATE TABLE IF NOT EXISTS t6(id bigint) 
  PARTITIONED BY (ds string) tblproperties ("transactional"="true");

建立非分區表並為欄位指定預設值

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

建立內部表並複製外部分區表資料

• 內部表不包含分區屬性。

  1. 建立OSS外部表格和MaxCompute內部表。

     -- 建立oss外部表格並插入資料。
     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;
     
     -- 結果
     a    b    c
     101    1    20241108
     102    2    20241109
     103    3    20241110
     
     
     -- 使用create table as建立內部表
     CREATE TABLE from_exetbl_oss AS SELECT * FROM max_oss_test;
     
     -- 查詢建立內部表
     SELECT * FROM from_exetbl_oss;
     
     -- 返回結果，全表資料都有
     a    b    c
     101    1    20241108
     102    2    20241109
     103    3    20241110

  2. 執行DESC from_exetbl_as_par；查詢內部表結構。返回結果如下。

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

• 內部表包含分區屬性。

  1. 建立內部表from_exetbl_like。

     -- MaxCompute側查詢湖倉一體外部表格
     SELECT * FROM max_oss_test;
     -- 返回結果
     a    b    c
     101    1    20241108
     102    2    20241109
     103    3    20241110
     
     -- 使用create table like建立內部表
     CREATE TABLE from_exetbl_like LIKE max_oss_test;
     
     -- 查詢建立內部表
     SELECT * FROM from_exetbl_like;
     -- 返回結果，只有表結構出現
     a    b    c

  2. 執行DESC from_exetbl_like；查詢內部表結構。返回結果如下。

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

相關命令

• ALTER TABLE：修改表操作。

• TRUNCATE：將指定表中的資料清空。

• DROP TABLE：刪除分區表或非分區表。

• DESC TABLE/VIEW：查看MaxCompute內部表、視圖、物化視圖、外部表格、聚簇表或Transactional表的資訊。

• SHOW：查看錶的SQL DDL語句、列出專案下所有的表和視圖或列出一張表中的所有分區。