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

前提条件

INSERT INTO および INSERT OVERWRITE 操作を実行するには、ターゲットテーブルに対する Update 権限と、ソーステーブルに対する Select 権限が必要です。権限の付与に関する詳細については、「MaxCompute 権限」をご参照ください。

概要

MaxCompute SQL でデータを処理する場合、INSERT INTO または INSERT OVERWRITE 文を使用して、SELECT クエリの結果をターゲットテーブルに保存できます。違いは次のとおりです：

• INSERT INTO：テーブルまたは静的パーティションにデータを追記します。INSERT 文でパーティション値を指定して、特定のパーティションにデータを挿入できます。少量のテストデータを挿入するには、この文を VALUES とともに使用します。

• INSERT OVERWRITE：テーブルまたは静的パーティションの既存のデータをクリアしてから、新しいデータを挿入します。

  説明

  • MaxCompute の INSERT 構文は、MySQL や Oracle の INSERT 構文とは異なります。INSERT INTO と INSERT OVERWRITE の両方の文で TABLE キーワードを省略できます。

  • 同じパーティションに対して INSERT OVERWRITE 操作を繰り返し実行すると、DESC コマンドによって返されるパーティションの Size が異なる場合があります。これは、パーティションからデータを SELECT し、そのデータを INSERT OVERWRITE を使用して同じパーティションに書き戻す際に、ファイル分割ロジックが変更されるためです。その結果、データの Size も変化します。INSERT OVERWRITE 操作の前後で合計データ長は同じままであり、追加のストレージ料金は発生しません。

動的パーティションへのデータ挿入方法については、「動的パーティションへのデータの挿入または上書き (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 には、ローカル zorder とグローバル zorder の 2 つのモードがあります。デフォルトモードは local zorder です。ローカルモードは、個々のファイル内でのみデータを Z オーダーでソートし、データをグローバルに再配布しません。そのため、データが複数のファイルに分散している場合、データのクラスタリングが低くなり、効果的なデータスキップが妨げられる可能性があります。この問題に対処するため、新しいバージョンでは global zorder がサポートされています。global zorder モードを使用するには、次のパラメーターを設定します：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 zorder でソートされます。

例：通常のテーブル

• 例 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 キーワードは省略可能です。
  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);
  
  -- ソーステーブルにパーティションを追加します。この手順は任意です。パーティションが存在しない場合は、データ書き込み時に自動的に作成されます。
  ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china');
  
  -- ソーステーブルにデータを追記します。INSERT INTO および INSERT OVERWRITE の後の 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;
  
  -- ターゲットテーブルにパーティションを追加します。この手順は任意です。パーティションが存在しない場合は、データ書き込み時に自動的に作成されます。
  ALTER TABLE sale_detail_insert ADD PARTITION (sale_date='2013', region='china');
  
  -- 静的パーティションを上書きします。静的パーティションの場合、パーティション列は PARTITION() 句で指定され、SELECT リストに含めることはできません。SELECT リストの列は、位置に基づいてターゲットテーブルの列にマッピングされます。
  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 句の列の順序を変更します。ソーステーブルとターゲットテーブルのマッピングは、列名ではなく SELECT 句の列の順序に基づいています。コマンドは次のとおりです：

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

  sale_detail から sale_detail_insert へのデータ挿入の順序は次のとおりです：

  +---------------------+--------------------+-------------------+
  | customer_id  STRING | shop_name STRING   | total_price DOUBLE| 
  +---------------------+--------------------+-------------------+

  この場合、sale_detail.customer_id のデータは sale_detail_insert.shop_name に挿入され、sale_detail.shop_name のデータは sale_detail_insert.customer_id に挿入されます。

• 例 5：パーティションにデータを挿入する場合、パーティション列は SELECT 句に含めることはできません。次の文はエラーを返します。なぜなら、sale_date と region はパーティション列であり、静的パーティションの SELECT 句では許可されていないためです。不正なコマンドの例を次に示します：

  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 の値は定数のみ可能で、式は使用できません。不正なコマンドの例を次に示します：

  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 オーダーモードでソートします。コマンドの例を次に示します：

  -- ターゲットテーブル 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;
  
  -- ソートにグローバル Z オーダーモードを使用します。
  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 テーブル

例：Delta テーブル mf_dt を作成し、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='01' かつ 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 OVERWRITE' を使用して 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 オーダーはすべてのシナリオに適しているわけではありません。ユースケースで実験して、ストレージとクエリパフォーマンスの利点が、Z オーダーデータを書き込むための追加の計算コストを正当化するかどうかを判断する必要がある場合があります。以下のセクションでは、一般的な推奨事項を説明します。