MaxCompute:データの挿入または上書き (INSERT INTO | INSERT OVERWRITE)

前提条件

INSERT INTO または INSERT OVERWRITE 文を実行する前に、宛先テーブルに対する Update 権限と、ソーステーブルのメタデータに対する Select 権限が付与されていることを確認してください。 詳細については、「MaxCompute の権限」をご参照ください。

機能

動的パーティションにデータを挿入する方法については、「動的パーティションへのデータの挿入または上書き (DYNAMIC PARTITION)」をご参照ください。

制限事項

構文

INSERT {INTO|OVERWRITE} TABLE <table_name> [PARTITION (<pt_spec>)] [(<col_name> [,<col_name> ...)]]
<select_statement>
FROM <from_statement>
[ZORDER BY <zcol_name> [, <zcol_name> ...]];

次の表にパラメーターを示します:

ZORDER BY と SORT BY の違い:

• ZORDER BY 句は、ローカル Z-Ordering とグローバル Z-Ordering の 2 つのモードをサポートします。 デフォルトのモードは local Z-Ordering です。 ローカル Z-Ordering モードでは、グローバルデータではなく、単一ファイル内のデータのみが ZORDER BY 句に基づいてソートされます。 データが複数のファイルに分散している場合、データは緩やかに集約されます。 その結果、データスキップが効果的でない方法で実行される可能性があります。 この問題を解決するために、最新バージョンの MaxCompute は global Z-Ordering モードをサポートしています。 このモードを使用するには、構成 set odps.sql.default.zorder.type=global; を追加する必要があります。

  ZORDER BY 句を使用する場合は、次の 制限 に注意してください:

  • 一度にパーティションテーブル内の 1 つのパーティションのデータのみをソートするために ZORDER BY 句を実行できます。

  • ZORDER BY 句を実行するフィールドの数は 2 から 4 の範囲でなければなりません。

  • 宛先テーブルがクラスター化テーブルの場合、ZORDER BY 句はサポートされません。

  • ZORDER BY は DISTRIBUTE BY と一緒に使用できますが、ORDER BY、CLUSTER BY、または SORT BY と一緒に使用することはできません。

  説明

  ZORDER BY 句を使用してデータを挿入すると、より多くのリソースと時間が消費されます。

• SORT BY 句は、単一ファイル内のデータをソートするために使用されるメソッドを指定します。 SORT BY 句を指定しない場合、単一ファイル内のデータは local Z-Ordering モードでソートされます。

例: 一般的なテーブル

• 例 1: INSERT INTO 文を実行して、websites という名前の非パーティションテーブルにデータを追加します。 サンプル文:

  -- websites という名前の非パーティションテーブルを作成します。
  CREATE TABLE IF NOT EXISTS websites
  (id INT,
   name STRING,
   url STRING
  );
  -- apps という名前の非パーティションテーブルを作成します。
  CREATE TABLE IF NOT EXISTS apps
  (id INT,
   app_name STRING,
   url STRING
  );
  -- apps テーブルにデータを追加します。 INSERT INTO TABLE table_name の省略形は INSERT INTO table_name です。
  INSERT INTO apps (id,app_name,url) VALUES 
  (1,'Aliyun','https://www.aliyun.com');
  -- apps テーブルからデータをコピーし、そのデータを websites テーブルに追加します。
  INSERT INTO websites (id,name,url) SELECT id,app_name,url
  FROM apps;
  -- SELECT 文を実行して websites テーブルのデータを表示します。
  SELECT * FROM websites;

  次の結果が返されます:

  -- 次の結果が返されます:
  +------------+------------+------------+
  | id         | name       | url        |
  +------------+------------+------------+
  | 1          | Aliyun     | https://www.aliyun.com |
  +------------+------------+------------+

• 例 2: INSERT INTO 文を実行して、sale_detail という名前のパーティションテーブルにデータを追加します。 サンプル文:

  -- 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);
  
  -- sale_detail テーブルにパーティションを追加します。 この操作はオプションです。 事前にパーティションを作成しない場合、テーブルにデータを書き込むときにパーティションが自動的に作成されます。
  ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china');
  
  -- sale_detail テーブルにデータを追加します。 INSERT INTO TABLE table_name の省略形は INSERT INTO table_name です。 INSERT OVERWRITE TABLE table_name の TABLE キーワードは省略できません。
  INSERT INTO sale_detail PARTITION (sale_date='2013', region='china') VALUES ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);
  
  -- 現在のセッションに対してのみ全表スキャンを有効にします。 SELECT 文を実行して sale_detail テーブルのデータを表示します。
  SET odps.sql.allow.fullscan=true; 
  SELECT * FROM sale_detail;

  次の結果が返されます:

  -- 次の結果が返されます:
  +------------+-------------+-------------+------------+------------+
  | shop_name  | customer_id | total_price | sale_date  | region     |
  +------------+-------------+-------------+------------+------------+
  | s1         | c1          | 100.1       | 2013       | china      |
  | s2         | c2          | 100.2       | 2013       | china      |
  | s3         | c3          | 100.3       | 2013       | china      |
  +------------+-------------+-------------+------------+------------+

• 例 3: INSERT OVERWRITE 文を実行して、sale_detail_insert テーブルのデータを上書きします。 サンプル文:

  -- sale_detail テーブルと同じスキーマを持つ sale_detail_insert テーブルを作成します。
  CREATE TABLE sale_detail_insert LIKE sale_detail;
  
  -- sale_detail_insert テーブルにパーティションを追加します。 この操作はオプションです。 事前にパーティションを作成しない場合、テーブルにデータを書き込むときにパーティションが自動的に作成されます。
  ALTER TABLE sale_detail_insert ADD PARTITION (sale_date='2013', region='china');
  
  -- sale_detail テーブルからデータを抽出し、そのデータを sale_detail_insert テーブルに挿入します。 sale_detail_insert テーブルのパーティションキー列の名前を宣言する必要はなく、並べ替えることもできません。
  -- sale_detail_insert テーブルに静的パーティションが含まれている場合、パーティションキー列の値は PARTITION() で宣言されます。 これらの値を select_statement に含める必要はありません。 sale_detail_insert テーブルの共通列のシーケンスのみに基づいて列名を検索し、宣言された列値を sale_detail_insert テーブルの列に順番にマッピングする必要があります。 sale_detail_insert テーブルに動的パーティションが含まれている場合、パーティションキー列の名前を select_statement に含める必要があります。 詳細については、「動的パーティションへのデータの挿入または上書き (DYNAMIC PARTITION)」をご参照ください。
  SET odps.sql.allow.fullscan=true;
  INSERT OVERWRITE TABLE sale_detail_insert PARTITION (sale_date='2013', region='china')
    SELECT 
    shop_name, 
    customer_id,
    total_price 
    FROM sale_detail
    ZORDER BY customer_id, total_price;
  
  -- 現在のセッションに対してのみ全表スキャンを有効にします。 SELECT 文を実行して sale_detail_insert テーブルのデータを表示します。
  SET odps.sql.allow.fullscan=true;
  SELECT * FROM sale_detail_insert;

  次の結果が返されます:

  -- 次の結果が返されます:
  +------------+-------------+-------------+------------+------------+
  | shop_name  | customer_id | total_price | sale_date  | region     |
  +------------+-------------+-------------+------------+------------+
  | s1         | c1          | 100.1       | 2013       | china      |
  | s2         | c2          | 100.2       | 2013       | china      |
  | s3         | c3          | 100.3       | 2013       | china      |
  +------------+-------------+-------------+------------+------------+

• 例 4: INSERT OVERWRITE 文を実行して sale_detail_insert テーブルのデータを上書きし、select_statement の列のシーケンスを調整します。 ソーステーブルと宛先テーブル間のマッピングは、2 つのテーブルの列名のマッピングではなく、select_statement の列のシーケンスに基づいています。 サンプル文:

  SET odps.sql.allow.fullscan=true;
  INSERT OVERWRITE TABLE sale_detail_insert PARTITION (sale_date='2013', region='china')
      SELECT customer_id, shop_name, total_price FROM sale_detail;    
  
  SET odps.sql.allow.fullscan=true;
  SELECT * FROM sale_detail_insert;                  

  次の結果が返されます:

  +------------+-------------+-------------+------------+------------+
  | shop_name  | customer_id | total_price | sale_date  | region     |
  +------------+-------------+-------------+------------+------------+
  | c1         | s1          | 100.1       | 2013       | china      |
  | c2         | s2          | 100.2       | 2013       | china      |
  | c3         | s3          | 100.3       | 2013       | china      |
  +------------+-------------+-------------+------------+------------+

  sale_detail_insert テーブルを作成すると、列のシーケンスは shop_name string, customer_id string, and then total_price bigint と定義されます。 ただし、データは sale_detail テーブルから sale_detail_insert テーブルに customer_id, shop_name, and then total_price のシーケンスに基づいて挿入されます。 その結果、sale_detail.customer_id 列のデータは sale_detail_insert.shop_name 列に挿入され、sale_detail.shop_name 列のデータは sale_detail_insert.customer_id 列に挿入されます。

• 例 5: パーティションにデータを挿入する場合、パーティションのパーティションキー列を select_statement に含めることはできません。 次の文を実行すると、エラーが返されます。 これは、sale_date と region がパーティションキー列であるためです。 INSERT OVERWRITE または INSERT INTO 文を使用して静的パーティションにデータを挿入または更新する場合、これらの列を select_statement に含めることはできません。 不正な使用法のサンプル文:

  INSERT OVERWRITE TABLE sale_detail_insert PARTITION (sale_date='2013', region='china')
     SELECT shop_name, customer_id, total_price, sale_date, region FROM sale_detail;

• 例 6: PARTITION() の pt_spec は式ではなく定数でなければなりません。 不正な使用法のサンプル文:

  INSERT OVERWRITE TABLE sale_detail_insert PARTITION (sale_date=datepart('2016-09-18 01:10:00', 'yyyy') , region='china')
     SELECT shop_name, customer_id, total_price FROM sale_detail;

• 例 7: INSERT OVERWRITE 文を実行して、mf_src テーブルのデータを上書きし、データを mf_zorder_src テーブルに追加し、mf_zorder_src テーブルのデータをグローバル Z-Ordering モードでソートします。 サンプル文:

  -- mf_src テーブルを作成し、テーブルにデータを挿入してから、テーブルからデータをクエリします。
  CREATE TABLE mf_src (key STRING, value STRING);
  
  INSERT OVERWRITE TABLE mf_src
  SELECT a, b FROM VALUES ('1', '1'),('3', '3'),('2', '2')
  AS t(a, b);
  
  SELECT * FROM mf_src;
  
  -- 次の結果が返されます:
  +-----+-------+
  | key | value |
  +-----+-------+
  | 1   | 1     |
  | 3   | 3     |
  | 2   | 2     |
  +-----+-------+
  
  -- mf_src テーブルと同じスキーマを持つ mf_zorder_src テーブルを作成します。
  CREATE TABLE mf_zorder_src LIKE mf_src;
  
  -- mf_zorder_src テーブルにデータを挿入し、テーブル内のデータをグローバル Z-Ordering モードでソートしてから、テーブルからデータをクエリします。
  SET odps.sql.default.zorder.type=global;
  INSERT OVERWRITE TABLE mf_zorder_src
  SELECT key, value FROM mf_src 
  ZORDER BY key, value;
  
  SELECT * FROM mf_zorder_src;

  次の結果が返されます:

  -- 次の結果が返されます:
  +-----+-------+
  | key | value |
  +-----+-------+
  | 1   | 1     |
  | 2   | 2     |
  | 3   | 3     |
  +-----+-------+

• 例 8: INSERT OVERWRITE 文を実行して、target という名前の既存のテーブルのデータを上書きします。 サンプル文:

  -- target という名前の既存のテーブルのデータを上書きします。
  SET odps.sql.default.zorder.type=global;
  INSERT OVERWRITE TABLE target
  SELECT key, value FROM target 
  ZORDER BY key, value;

例: Delta テーブル

mf_dt という名前の Delta テーブルを作成し、INSERT 文を実行してテーブルにデータを挿入および上書きします。

-- mf_dt という名前の Delta テーブルを作成します。
CREATE TABLE IF NOT EXISTS mf_dt (pk BIGINT NOT NULL PRIMARY KEY, 
                  val BIGINT NOT NULL) 
                  PARTITIONED BY (dd STRING, hh STRING) 
                  tblproperties ("transactional"="true");
                  
-- mf_dt テーブルのパーティションキー列 dd と hh が両方とも 01 であるパーティションにテストデータを挿入します。
INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='01') 
                 VALUES (1, 1), (2, 2), (3, 3);

-- mf_dt テーブルの宛先パーティションからデータをクエリします。
SELECT * FROM mf_dt WHERE dd='01' AND hh='01';
-- 次の結果が返されます:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

-- INSERT INTO 文を実行して、mf_dt テーブルの宛先パーティションにデータを追加します。
INSERT INTO TABLE mf_dt PARTITION(dd='01', hh='01') 
            VALUES (3, 30), (4, 4), (5, 5);
            
SELECT * FROM mf_dt WHERE dd='01' AND hh='01';
-- 次の結果が返されます:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 30         | 01 | 01 |
| 4          | 4          | 01 | 01 |
| 5          | 5          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

-- INSERT OVERWRITE 文を実行して、mf_dt テーブルの宛先パーティションのデータを上書きします。
INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='01') 
                 VALUES (1, 1), (2, 2), (3, 3);
SELECT * FROM mf_dt WHERE dd='01' AND hh='01';
-- 次の結果が返されます:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

-- INSERT INTO 文を実行して、mf_dt テーブルのパーティションキー列 dd が 01 でパーティションキー列 hh が 02 であるパーティションにデータを書き込みます。
INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='02') 
                 VALUES (1, 11), (2, 22), (3, 32);
SELECT * FROM mf_dt WHERE dd='01' AND hh='02';
-- 次の結果が返されます:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 11         | 01 | 02 |
| 3          | 32         | 01 | 02 |
| 2          | 22         | 01 | 02 |
+------------+------------+----+----+

-- 現在のセッションに対してのみ全表スキャンを有効にします。 SELECT 文を実行して mf_dt テーブルのデータをクエリします。
SET odps.sql.allow.fullscan=true;
SELECT * FROM mf_dt;
-- 次の結果が返されます:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 11         | 01 | 02 |
| 3          | 32         | 01 | 02 |
| 2          | 22         | 01 | 02 |
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

ベストプラクティス

Z-Ordering 機能は、すべてのビジネスシナリオに適しているわけではありません。 MaxCompute は、Z-Ordering 機能を使用するかどうか、またその使用方法に関するガイドラインを提供していません。 ほとんどの場合、ビジネス要件に基づいて Z-Ordering 機能を使用するかどうかを決定する必要があります。 また、Z-Ordering 機能を使用してデータをソートする際に発生する追加のコンピューティングコストが、ストレージおよびダウンストリームの消費とコンピューティングの観点から削減されるコストよりも少ないかどうかを包括的に評価する必要もあります。 次の説明は、経験に基づいた Z-Ordering 機能の使用に関する提案です。 提案やフィードバックも歓迎します。

Z-Ordering ではなくクラスター化インデックスを優先的に使用する必要があるシナリオ

• フィルター条件が a、a と b の組み合わせ、または a、b、c の組み合わせなどのプレフィックスの組み合わせで構成されている場合、クラスター化インデックス (ORDER BY a, b, c) は ZORDER BY 句よりも効果的です。 ORDER BY 句は最初のフィールドに対してより良いソート効果を提供しますが、残りのフィールドにはほとんど影響を与えません。 ZORDER BY 句は各フィールドに同じ重みを与え、特定のフィールドに対するソート結果は、ORDER BY 句の最初の列に対するソート結果よりも効果が低くなります。

• 一部のフィールドが結合キーとして頻繁に使用される場合、範囲クラスタリングまたはハッシュクラスタリングがそれらのフィールドにより適しています。 MaxCompute の Z-Ordering 機能は、単一ファイル内のデータのみをソートするために使用できます。 SQL エンジンは、Z-Ordering 機能を使用してソートされたデータを認識できませんが、クラスター化インデックスを使用してソートされたデータは認識できます。 クエリ計画の段階でクラスター化インデックスを使用することで、結合のパフォーマンスをより良く最適化できます。

• 特定のフィールドに対して GROUP BY または ORDER BY 句を頻繁に実行する場合、クラスター化インデックスはより良いパフォーマンスを提供できます。

Z-Ordering 機能の使用に関する提案

• フィルター条件で頻繁に使用されるフィールド、特にフィルタリングのために頻繁に結合されるフィールドを選択します。

• ZORDER BY 句を実行するフィールドの数が 4 を超えないようにしてください。 多数のフィールドに対して ZORDER BY 句を実行すると、各フィールドのソートパフォーマンスが低下します。 1 つのフィールドのみをソートする必要がある場合は、Z-Ordering ではなくクラスター化インデックスを使用することをお勧めします。

• 選択されたフィールドの個別値の数が多すぎたり少なすぎたりしないようにしてください。 たとえば、性別フィールドには 2 つの個別値しかありません。 この場合、値のソートは無意味です。 ただし、フィールドに個別値が多すぎる場合、ZORDER BY 句は Z 値を計算するためにフィールドのすべての値をメモリにキャッシュする必要があるため、値のソートには高いコストがかかります。

• ソートするテーブルのデータ量が多すぎたり少なすぎたりしないようにしてください。 ソートするテーブルのデータ量が少なすぎる場合、Z-Ordering によるソートの効果は明らかではありません。 ソートするテーブルのデータ量が多すぎる場合、Z-Ordering を使用してデータをソートする際に高いコストが発生します。 たとえば、ベースラインタスクの出力が遅延する可能性があります。