テーブルまたは静的パーティションへのデータの挿入または上書き (INSERT INTO および INSERT OVERWRITE) - MaxCompute

MaxCompute では、INSERT INTO または INSERT OVERWRITE 文を実行して、テーブルまたは静的パーティションにデータを挿入したり、データを上書きしたりできます。

以下のプラットフォームで文を実行できます。

前提条件

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

機能

MaxCompute SQL を使用してデータを処理する場合、INSERT INTO または INSERT OVERWRITE 文を実行して、SELECT 文の実行結果をデスティネーションテーブルに保存できます。2 つの文の違い：

INSERT INTO: テーブルまたはテーブルの静的パーティションにデータを挿入します。この文でパーティションキー列の値を指定して、指定したパーティションにデータを挿入できます。少量のテストデータを挿入する場合は、この文を VALUES と共に使用できます。
INSERT OVERWRITE: 指定したテーブルまたは静的パーティションをクリアし、テーブルまたはテーブルの静的パーティションにデータを挿入します。
説明
- MaxCompute の INSERT 構文は、MySQL や Oracle の構文とは異なります。TABLE キーワードと table_name を INSERT OVERWRITE の後に追加する必要があります。TABLE キーワードは、INSERT INTO を使用する場合には追加する必要はありません。
- 同じパーティションに対して INSERT OVERWRITE 文を複数回実行すると、DESC コマンドを実行するたびに、データが挿入されるパーティションのサイズが異なる場合があります。これは、テーブル内の同じパーティションに対して SELECT 文と INSERT OVERWRITE 文を順番に実行した後に、ファイルを分割するロジックが変更されるためです。INSERT OVERWRITE 文を実行した後、データの全長は同じままです。これはストレージ料金には影響しません。

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

制限

INSERT INTO 文または INSERT OVERWRITE 文を実行して、テーブルまたはテーブルの静的パーティションにデータを挿入または更新する場合、次の制限事項に注意してください。
- INSERT INTO: この文を使用して、クラスタ化テーブルにデータを挿入することはできません。
- INSERT OVERWRITE: この文では、データを挿入する列を指定することはできません。列を指定する必要がある場合は、列名を指定した INSERT INTO 文を使用します。たとえば、CREATE TABLE t(a STRING, b STRING); INSERT INTO t(a) VALUES ('1'); を実行すると、1 が列 a に挿入され、NULL またはデフォルト値が列 b に挿入されます。
- MaxCompute は、INSERT 操作が実行されているテーブルのロックメカニズムを提供していません。同じテーブルに対して INSERT INTO 文または INSERT OVERWRITE 文を同時に実行しないことをお勧めします。

INSERT INTO 文と INSERT OVERWRITE 文には、Delta テーブルに対する次の制限があります。
- INSERT OVERWRITE 文を実行して Delta テーブルにデータを挿入する場合、システムは同じプライマリキー値を持つ複数のデータ行から重複データを削除し、計算プロセス中に最初にランク付けされた行のみをテーブルに挿入します。テーブルに挿入されるデータレコードを指定することはできません。INSERT OVERWRITE 文は、完全データを Delta テーブルに挿入します。デフォルトの重複排除により、プライマリキー値の一意性を確保できます。
- INSERT INTO 文を実行して Delta テーブルにデータを挿入する場合、システムは同じプライマリキー値を持つデータを重複排除しませんが、デフォルトですべてのデータをテーブルに挿入します。Flag(odps.sql.insert.acidtable.deduplicate.enable) を true に設定すると、システムは最初にデータを重複排除します。

構文

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

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

パラメーター	必須	説明
table_name	はい	データを挿入するテーブルの名前。
pt_spec	いいえ	データを挿入するパーティション。定数のみがサポートされています。関数などの式はサポートされていません。このパラメーターの値は、`(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)` 形式です。
col_name	いいえ	データを挿入するテーブルの列の名前。`INSERT OVERWRITE` では、`[(<col_name> [,<col_name> ...)]` を指定することはできません。
select_statement	はい	ソーステーブルからデスティネーションテーブルに挿入するデータをクエリするために使用される `SELECT` 句。`SELECT` 句の詳細については、「SELECT 構文」をご参照ください。説明ソーステーブルとデスティネーションテーブル間のマッピングは、テーブル内の列名間のマッピングではなく、`select_statement` 内の列シーケンスに基づいています。デスティネーションテーブルに静的パーティションがあり、静的パーティションにデータを挿入する場合、`select_statement` にパーティションキー列を含めることはできません。
from_statement	はい	`FROM` 句。この句はデータソースを指定します。たとえば、この句でソーステーブルの名前を指定できます。
ZORDER BY <zcol_name> [, <zcol_name> ...]	いいえ	テーブルまたはパーティションにデータを書き込む場合、この句を使用して、select_statement で指定された列に基づいて、類似したデータレコードを持つ行を同じ場所に配置できます。これにより、クエリのフィルタリングパフォーマンスが向上し、ストレージコストが削減されます。`ORDER BY x, y` 句は、x が y の前に来るシーケンスに基づいてデータレコードをソートします。`ZORDER BY x, y` 句は、類似した x 値を持つ行と類似した y 値を持つ行を同じ場所に配置します。SQL SELECT 文での列ベースのデータフィルタリングとソートの場合、`ORDER BY` 句は x に基づいてデータをフィルタリングおよびソートしますが、`ZORDER BY` 句は x に基づいて、または x と y の両方基づいてデータをフィルタリングおよびソートします。これにより、列圧縮率が向上します。

ZORDER BY と SORT BY の違い:

ZORDER BY 句は、ローカル Z オーダーとグローバル Z オーダーの 2 つのモードをサポートしています。デフォルトモードは local Z-Ordering です。ローカル Z オーダーモードでは、グローバルデータではなく、単一ファイル内のデータのみが 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 saledetail;    

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 bigint の順に定義されます。ただし、データは sale_detail テーブルから sale_detail_insert テーブルに、customer_id、shop_name、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 オーダーモードでソートします。サンプル文:

-- 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 オーダーモードでソートしてから、テーブルからデータをクエリします。
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");
                  
-- パーティションキー列 dd と hh が両方とも 01 であるパーティションにテストデータを挿入します。mf_dt テーブルにあります。
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 文を実行して、パーティションキー列 dd が 01 でパーティションキー列 hh が 02 であるパーティションにデータを書き込みます。mf_dt テーブルにあります。
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 オーダー機能は、すべてのビジネスシナリオに適しているわけではありません。MaxCompute は、Z オーダー機能を使用するかどうか、および使用方法に関するガイドラインを提供していません。ほとんどの場合、ビジネス要件に基づいて Z オーダー機能を使用するかどうかを決定する必要があります。また、Z オーダー機能を使用してデータをソートするときに発生する追加の計算コストが、ストレージとダウンストリームの消費と計算の点で削減されるコストよりも少ないかどうかを包括的に評価する必要があります。次の説明では、経験に基づいた Z オーダー機能の使用に関する提案を示します。提案やフィードバックも提供できます。

クラスタ化インデックスを Z オーダーよりも優先的に使用する必要があるシナリオ

フィルター条件がプレフィックスの組み合わせで構成されている場合 (a、a と b の組み合わせ、a、b、c の組み合わせなど)、クラスタ化インデックス (ORDER BY a, b, c) は ZORDER BY 句よりも効果的です。ORDER BY 句は最初のフィールドに対してより良いソート効果を提供しますが、残りのフィールドへの影響はわずかです。ZORDER BY 句は各フィールドに同じ重みを与え、特定のフィールドに対するソート結果は、ORDER BY 句の最初の列に対するソート結果よりも効果が低くなります。
一部のフィールドが結合キーとして頻繁に使用される場合、レンジクラスタリングまたはハッシュクラスタリングがフィールドに適しています。MaxCompute の Z オーダー機能は、単一ファイル内のデータのみをソートするために使用できます。SQL エンジンは、Z オーダー機能を使用してソートされたデータには認識できませんが、クラスタ化インデックスを使用してソートされたデータには認識できます。クエリ計画の段階でクラスタ化インデックスを使用することにより、結合のパフォーマンスを最適化できます。
特定のフィールドに対して GROUP BY 句または ORDER BY 句を頻繁に実行する場合、クラスタ化インデックスはより良いパフォーマンスを提供できます。

Z オーダー機能の使用に関する推奨事項

フィルター条件で頻繁に使用されるフィールド、特にフィルタリングのために頻繁に結合されるフィールドを選択します。
ZORDER BY 句を実行するフィールドの数が 4 を超えないようにしてください。多数のフィールドで ZORDER BY 句を実行すると、各フィールドのソートパフォーマンスが低下します。ソートする必要があるフィールドが 1 つだけの場合は、Z オーダーではなくクラスタ化インデックスを使用することをお勧めします。
選択されたフィールドの個別値の数は、過度に大きくても小さくてもいけません。たとえば、gender フィールドには 2 つの個別値しかありません。この場合、値のソートは無意味です。ただし、フィールドに過度に多数の個別値がある場合、ZORDER BY 句は Z 値を計算するためにフィールドのすべての値をメモリにキャッシュする必要があるため、値のソートには高いコストが発生します。
ソートするテーブルのデータ量が過度に大きくても小さくてもいけません。ソートするテーブルのデータ量が非常に少ない場合、Z オーダーによるソートの効果は明らかではありません。ソートするテーブルのデータ量が非常に大きい場合、Z オーダーを使用してデータをソートするときに高いコストが発生します。たとえば、ベースラインタスクの出力が遅延する可能性があります。