MaxCompute:CREATE TABLE

内部テーブル

内部テーブル（非パーティション化またはパーティション化）の作成

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>], ...)]
[AUTO PARTITIONED 
   BY (<auto_partition_expression> [AS <auto_partition_column_name>])
  [TBLPROPERTIES('ingestion_time_partition'='true')]
];

クラスター化テーブル

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

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 外部テーブルを作成する方法を示します。詳細については、「ORC 外部テーブル」をご参照ください。

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name> ( 
<col_name> <data_type>, ... ) 
STORED AS '<file_format>' 
[WITH SERDEPROPERTIES (options)]  
LOCATION '<oss_location>';

トランザクションテーブルおよび Delta テーブル

• トランザクションテーブルを作成します。このタイプのテーブルでは、UPDATE および DELETE 操作を実行できますが、一定の制限があります。

  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>]
  [CLUSTERED BY (<pk_col_name>[, <pk_col_name2>, ...] )]
  [TBLPROPERTIES ("transactional"="true" 
  [, "write.bucket.num" = "N", "acid.data.retain.hours"="hours"...])] [LIFECYCLE <days>];

  • プライマリキーを持つ Delta テーブルでは、プライマリキーの一部をハッシュクラスタリングキーとして使用できます。

    プライマリキーを持つ Delta テーブルでは、システムはデフォルトでハッシュクラスタリングによりデータを分散します。デフォルトのクラスタリングキーは、プライマリキー列の完全なセットです。ただし、特定のビジネスシナリオでは、クエリがプライマリキー列の一部を頻繁にフィルター条件として使用することがあります。このような場合、明示的にプライマリキー列の一部をクラスタリングキーとして指定することで、データがその部分に基づいて分散・格納されるようになり、フィルター処理のパフォーマンスが向上します。

  • クラスタリングキーを明示的に指定した場合、システムは指定された列に基づいてテーブルデータをハッシュバケットに分散します。クラスタリングキーは、プライマリキー列の一部である必要があります。

    クラスタリングキーを明示的に指定しない場合、システムはプライマリキー列の完全なセットをデフォルトのクラスタリングキーとして使用します。

    テーブル作成後にクラスタリングキーを変更することはできません。必ずテーブル作成時に指定してください。

CTAS および LIKE 句

• 既存のテーブルを基に新しいテーブルを作成し、データをコピーしますが、パーティションプロパティはコピーされません。この句は、外部テーブルおよび外部 Lakehouse プロジェクト内のテーブルに適用されます。

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

• 既存のテーブルと同じ構造を持つ新しいテーブルを作成しますが、データはコピーされません。この句は、外部テーブルおよび外部 Lakehouse プロジェクト内のテーブルに適用されます。

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

共通パラメーター

DROP TABLE IF EXISTS <tableName>;  -- テーブルが存在する場合は削除します。
CREATE TABLE <tableName> ...;      -- 新しいテーブルを作成します。

パーティションテーブル

クラスター化テーブル

外部テーブル

トランザクションテーブルおよび Delta テーブル

既存のテーブルからのテーブル作成

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

• 非パーティション化テーブルを作成します。

  CREATE TABLE test1 (key STRING);

• 非パーティション化テーブルを作成し、列のデフォルト値を指定します。

  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 TABLE IF NOT EXISTS sale_detail(
   shop_name     STRING,
   customer_id   STRING,
   total_price   DOUBLE)
  PARTITIONED BY (sale_date STRING, region STRING);

• 時刻ベースのデータ列を用いた時刻関数により、自動的にパーティションを生成する AUTO PARTITION パーティションテーブルを作成します。

  -- sale_date 列を月単位で切り捨てて、sale_month という名前のパーティション列を生成し、この列でテーブルをパーティション化します。
  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);

• データ取り込み時間に基づいてパーティションを自動生成する AUTO PARTITION パーティションテーブルを作成します。システムは、データが MaxCompute に書き込まれるタイミングを自動的に取得し、時刻関数を用いてパーティションを生成します。

  -- テーブル作成後、データ書き込み時にシステムが自動的にデータ取り込み時間 (_partitiontime) を取得し、日単位で切り捨てて sale_date という名前のパーティション列を生成し、この列でテーブルをパーティション化します。
  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 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");

内部テーブル

• OSS 外部パーティションテーブルからデータをコピーして内部テーブルを作成します。この内部テーブルにはパーティションプロパティは含まれません。

  1. OSS 外部テーブルと MaxCompute 内部テーブルを作成します。CREATE TABLE AS を使用して内部テーブルを作成します。

     -- 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_oss; コマンドを実行して、内部テーブルのスキーマを表示します。コマンドの出力は以下のとおりです。

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

• OSS 外部パーティションテーブルからスキーマをコピーして内部テーブルを作成します。この内部テーブルにはパーティションプロパティが含まれます。

  1. 内部テーブル from_exetbl_like を作成します。MaxCompute から OSS 外部テーブルをクエリし、CREATE TABLE LIKE を使用して内部テーブルを作成します。

     -- MaxCompute から OSS 外部テーブルをクエリします。
     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     |                                                     |
     +------------------------------------------------------------------------------------+

Delta テーブル

• Delta テーブルを作成します。

  CREATE TABLE mf_tt (pk BIGINT NOT NULL PRIMARY KEY, val BIGINT) 
    TBLPROPERTIES ("transactional"="true");

• Delta テーブルを作成し、キーとなるテーブルプロパティを設定します。

  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;

その他の方法

-- sale_detail_ctas1 という名前の新しいテーブルを作成し、sale_detail からデータをコピーしてライフサイクルを設定します。
SET odps.sql.allow.fullscan=true;
CREATE TABLE sale_detail_ctas1 LIFECYCLE 10 AS SELECT * FROM sale_detail;

CREATE TABLE sale_detail_like LIKE sale_detail LIFECYCLE 10;

-- mc_oss_extable_orc という外部テーブルと同じスキーマを持つ新しいテーブル mc_oss_extable_orc_like を作成します。
CREATE TABLE mc_oss_extable_orc_like LIKE mc_oss_extable_orc;

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