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

共通パラメーター

DROP TABLE IF EXISTS <tableName>;  -- 宛先テーブルが存在する場合は、最初に削除します。
CREATE TABLE <tableName> ...;      -- テーブルを作成します。

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

MaxCompute は、通常のパーティションテーブルと自動パーティションテーブル (AUTO PARTITION) という 2 種類のパーティションテーブルをサポートしています。さまざまなシナリオでのパーティションキー列の生成方法に基づいて、タイプを選択できます。

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

クラスタテーブルは、ハッシュクラスタテーブルと範囲クラスタテーブルに分類されます。

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

次の例は、OSS 外部テーブルを作成するためのパラメーターを示しています。外部テーブルの作成方法の詳細については、「外部テーブル」をご参照ください。

トランザクションテーブルと Delta テーブルのパラメーター

既存のテーブルに基づいてテーブルを作成するためのパラメーター

• CREATE TABLE [IF NOT EXISTS] <table_name> [LIFECYCLE <days>] AS <select_statement>; 文を使用して、テーブルを作成し、新しいテーブルにデータを複製できます。

  • ただし、ソーステーブルのパーティションプロパティとライフサイクルプロパティは、作成されたテーブルに複製されません。ソーステーブルのパーティションキー列は、作成されたテーブルでは共通列と見なされます。

  • lifecycle パラメーターを使用してテーブルを再利用することもできます。このステートメントを実行して、内部テーブルを作成し、外部テーブルのデータを内部テーブルに複製することもできます。

• CREATE TABLE [IF NOT EXISTS] <table_name> [LIFECYCLE <days>] LIKE <existing_table_name>; 文を使用して、既存のテーブルと同じスキーマを使用するテーブルを作成できます。

  • ただし、このステートメントを使用して作成されたテーブルは、ソーステーブルのデータまたはライフサイクルプロパティを複製しません。

  • lifecycle パラメーターを使用してテーブルを再利用することもできます。このステートメントを実行して、既存の外部テーブルと同じスキーマを持つ内部テーブルを作成することもできます。

非パーティションテーブルを作成する

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

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

新しいテーブルを作成する

• 既存のテーブルを置き換えるテーブルを作成します。

  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; コマンドを実行して、テーブル構造、ライフサイクル、その他の詳細を表示できます。

  説明

  ソーステーブル sale_detail はパーティションテーブルです。ただし、CREATE TABLE ... AS select_statement ... 文を使用して新しい sale_detail_ctas1 テーブルが作成されると、パーティションプロパティは複製されません。ソーステーブルのパーティションキー列は、共通列として扱われます。したがって、sale_detail_ctas1 は 5 つの列を持つ非パーティションテーブルです。

• テーブルを作成し、SELECT 句で定数を列値として使用します。

  説明

  SELECT 句で定数を列値として使用する場合は、列名を指定することをお勧めします。作成された sale_detail_ctas3 テーブルの 4 番目と 5 番目の列は、_c4 と _c5 に似ています。

  • 列名が指定されています。

    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; コマンドを実行して、テーブル構造、ライフサイクル、その他の詳細を表示できます。

  説明

  sale_detail_like テーブルのスキーマは、sale_detail テーブルのスキーマと同じです。2 つのテーブルは、ライフサイクルを除いて、列名、列コメント、テーブルコメントなどのプロパティが同じです。ただし、sale_detail テーブルのデータは 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");

内部テーブルを作成する

• 内部テーブルを作成し、パーティション外部テーブルのデータを内部テーブルに複製します。パーティションプロパティは内部テーブルに複製されません。

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

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;