MaxCompute:テーブルの作成と削除

内部テーブル

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

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 句

• 既存のテーブル (外部テーブルや外部レイクハウスプロジェクト内のテーブルを含む) に基づいて新しいテーブルを作成し、データをコピーしますが、パーティションプロパティはコピーしません。

  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> ...;      -- 新しいテーブルを作成します。

パーティションテーブルのパラメーター

クラスター化テーブルのパラメーター

外部テーブルのパラメーター

トランザクションテーブルと 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 バケット;

• ハッシュクラスター化パーティション化テーブルを作成します。

  CREATE TABLE t2 (a STRING, b STRING, c BIGINT) 
  PARTITIONED BY (dt STRING) CLUSTERED BY (c) SORTED BY (c) INTO 1024 バケット;

• レンジクラスター化非パーティション化テーブルを作成します。

  CREATE TABLE t3 (a STRING, b STRING, c BIGINT) RANGE CLUSTERED BY (c) SORTED BY (c) INTO 1024 バケット;

• レンジクラスター化パーティション化テーブルを作成します。

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

内部テーブル

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

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

     -- OSS 外部テーブルを作成し、データを挿入します。
     CREATE EXTERNAL TABLE max_oss_test(a INT, b INT) PARTITIONED BY (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     |       |                                             |
     +------------------------------------------------------------------------------------+

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

  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;