MaxCompute:CREATE TABLE

制限事項

• パーティションテーブルには、最大 6 レベルのパーティションを設定できます。たとえば、テーブルがパーティションキー列として日付列を使用する場合、パーティションの 6 つのレベルは year/month/week/day/hour/minute です。

• デフォルトでは、テーブルには最大 60,000 個のパーティションを設定できます。ビジネス要件に基づいて、テーブル内のパーティションの最大数を調整できます。

テーブルの制限事項の詳細については、「MaxCompute SQL の制限事項」をご参照ください。

構文

内部テーブルの作成

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>], ...)]
;

クラスター化テーブルの作成

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

テーブルを作成してテーブルタイプを指定する

• トランザクションテーブルを作成します。テーブルの作成後に、テーブルのデータを更新または削除できます。ただし、トランザクションテーブルには特定の制限が課せられます。必要に応じてトランザクションテーブルを作成することをお勧めします。

  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 テーブルを作成します。プライマリキーを使用してテーブルに対する操作を実行できます。たとえば、テーブルに対してアップサート、増分クエリ、およびタイムトラベルクエリを実行できます。

  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 と同じスキーマを持つ mc_oss_extable_orc_like という名前のテーブルを作成します。
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;

ハッシュクラスター化テーブルの作成

• 非パーティションテーブル

  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;

レンジクラスター化テーブルの作成

• 非パーティションテーブル

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

トランザクションテーブルの作成

• 非パーティションテーブル

  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 内部テーブル、ビュー、マテリアライズドビュー、外部テーブル、クラスター化テーブル、およびトランザクションテーブルに関する情報を取得します。

• SHOW: テーブルの SQL DDL 文を取得したり、プロジェクト内のすべてのテーブルとビューを一覧表示したり、テーブル内のすべてのパーティションを一覧表示したりします。