テーブルまたは静的パーティションにデータを挿入または上書きする (Insert intoおよびINSERT overwrite) - MaxCompute

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

次のプラットフォームでステートメントを実行できます。

前提条件

INSERT INTOまたはINSERT OVERWRITEステートメントを実行する前に、ターゲットテーブルに対するUpdate権限と、ソーステーブルのメタデータに対するSelect権限が付与されていることを確認してください。詳細は、「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'); を実行すると、列aに1が挿入され、列bにNULLまたはデフォルト値が挿入されます。
- 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.de duplicate.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句は、yの前のxのシーケンスに基づいてデータレコードをソートします。 ZORDER BY x, y句は、類似のx値を持つ行と類似のy値を持つ行を同じ場所に配置します。 SQL SELECTステートメントでの列ベースのデータのフィルタリングとソートの場合、ORDER BY句はxに基づいてデータをフィルタリングおよびソートしますが、ZORDER BY句はxに基づいて、またはxとyの両方に基づいてデータをフィルタリングおよびソートします。これは、カラム圧縮比を増加させる。

ZORDER BY句は、ローカルZ-OrderingとグローバルZ-Orderingの2つのモードをサポートしています。デフォルトモードはローカルZ-Orderingです。ローカルZ-Orderingモードでは、グローバルデータではなく、単一ファイル内のデータのみがZORDER BY句に基づいてソートされます。データが複数のファイルに分散されている場合、データは大まかに集約されます。結果として、データスキップは、あまり効果的でない方法で実行され得る。この問題を解決するために、最新バージョンのMaxComputeはグローバルZ-Orderingモードをサポートしています。
- Local Z-Ordering。
- Global Z-Ordering: このモードを使用するには、設定set odps.sql.de fault.zorder.type=global; を追加する必要があります。
SORT BY句は、単一のファイル内のデータをソートするために使用されるメソッドを指定します。 SORT BY句を指定しない場合、1つのファイル内のデータはローカルZ-Orderingモードでソートされます。
ZORDER BY句を使用する場合は、次の制限に注意してください。
- ZORDER BY句を実行すると、パーティションテーブルの1つのパーティション内のデータのみを一度にソートできます。
- ZORDER BY句を実行するフィールドの数は、2から4の範囲である必要があります。
- ターゲットテーブルがクラスタ化テーブルの場合、ZORDER BY句はサポートされません。
- ZORDER BYはDISTRIBUTE BYと一緒に使用できますが、ORDER BY、CLUSTER BY、またはSORT BYと一緒に使用することはできません。
説明
ZORDER BY句を使用してデータを挿入すると、より多くのリソースと時間が消費されます。

例: 共通テーブル

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

-- Create a non-partitioned table named websites. 
create table if not exists websites
(id int,
 name string,
 url string
);
-- Create a non-partitioned table named apps.
create table if not exists apps
(id int,
 app_name string,
 url string
);
-- Append data to the apps table. The abbreviated form of INSERT INTO TABLE table_name is INSERT INTO table_name.
insert into apps (id,app_name,url) values 
(1,'Aliyun','https://www.aliyun.com');
-- Copy data from the apps table and append the data to the websites table.
insert into websites (id,name,url) select id,app_name,url
from  apps;
-- Execute the SELECT statement to view data in the websites table. 
select * from websites;
-- The following result is returned: 
+------------+------------+------------+
| id         | name       | url        |
+------------+------------+------------+
| 1          | Aliyun     | https://www.aliyun.com |
+------------+------------+------------+

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

-- Create a partitioned table named 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);

-- Add a partition to the sale_detail table. This operation is optional. If you do not create a partition in advance, a partition is automatically created when you write data to the table. 
alter table sale_detail add partition (sale_date='2013', region='china');

-- Append data to the sale_detail table. The abbreviated form of INSERT INTO TABLE table_name is INSERT INTO table_name. The TABLE keyword in INSERT OVERWRITE TABLE table_name cannot be omitted. 
insert into sale_detail partition (sale_date='2013', region='china') values ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);

-- Enable a full table scan only for the current session. Execute the SELECT statement to view data in the sale_detail table. 
set odps.sql.allow.fullscan=true; 
select * from sale_detail;

-- The following result is returned: 
+------------+-------------+-------------+------------+------------+
| 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テーブルのデータを上書きします。サンプル文:

-- Create the sale_detail_insert table that has the same schema as the sale_detail table. 
create table sale_detail_insert like sale_detail;

-- Add a partition to the sale_detail_insert table. This operation is optional. If you do not create a partition in advance, a partition is automatically created when you write data to the table. 
alter table sale_detail_insert add partition (sale_date='2013', region='china');

-- Extract data from the sale_detail table and insert the data into the sale_detail_insert table. Names of partition key columns in the sale_detail_insert table do not need to be declared and cannot be rearranged. 
-- If the sale_detail_insert table contains static partitions, the values of partition key columns are declared in PARTITION(). These values do not need to be included in select_statement. You need to search for column names based on only the sequence of common columns in the sale_detail_insert table and sequentially map the declared column values to the columns in the sale_detail_insert table. If the sale_detail_insert table contains dynamic partitions, the names of partition key columns must be included in select_statement. For more information, see Insert or overwrite data into dynamic partitions (DYNAMIC PARTITION). 
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;

-- Enable a full table scan only for the current session. Execute the SELECT statement to view the data in the sale_detail_insert table. 
set odps.sql.allow.fullscan=true;
select * from sale_detail_insert;

-- The following result is returned: 
+------------+-------------+-------------+------------+------------+
| 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の列のシーケンスに基づいています。サンプル文:
```
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 bigintとして定義されます。ただし、データは、customer_id、shop_name、total_priceのシーケンスに基づいて、sale_detailテーブルからsale_detail_insertテーブルに挿入されます。その結果、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テーブルのデータを上書きし、グローバルZ-Orderingモードでmf_zorder_srcテーブルのデータを並べ替えます。サンプル文:

-- Create the mf_src table, insert data into the table, and then query data from the table. 
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;

-- The following result is returned:
+-----+-------+
| key | value |
+-----+-------+
| 1   | 1     |
| 3   | 3     |
| 2   | 2     |
+-----+-------+

-- Create the mf_zorder_src table that has the same schema as the mf_src table. 
create table mf_zorder_src like mf_src;

-- Insert data into the mf_zorder_src table, sort data in the table in global Z-Ordering mode, and then query data from the table. 
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;

-- The following result is returned:
+-----+-------+
| key | value |
+-----+-------+
| 1   | 1     |
| 2   | 2     |
| 3   | 3     |
+-----+-------+

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

-- Overwrite data in an existing table named target.
set odps.sql.default.zorder.type=global;
insert overwrite table target
select key, value from target 
zorder by key, value;

例: デルタテーブル

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

-- Create a Delta table named mf_dt. 
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");
                  
-- Insert test data into the partition whose partition key columns dd and hh are both 01 in the mf_dt table. 
insert overwrite table mf_dt partition (dd='01', hh='01') 
                 values (1, 1), (2, 2), (3, 3);

-- Query data from the destination partition in the mf_dt table.                
select * from mf_dt where dd='01' and hh='01';
-- The following result is returned:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

-- Execute the INSERT INTO statement to append data to the destination partition in the mf_dt table. 
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';
-- The following result is returned:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 30         | 01 | 01 |
| 4          | 4          | 01 | 01 |
| 5          | 5          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

-- Execute the INSERT OVERWRITE statement to overwrite data in the destination partition of the mf_dt table. 
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='02';
-- The following result is returned: 
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

-- Execute the INSERT INTO statement to write data to the partition whose partition key column dd is 01 and partition key column hh is 02 in the mf_dt table. 
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';
-- The following result is returned: 
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 11         | 01 | 02 |
| 3          | 32         | 01 | 02 |
| 2          | 22         | 01 | 02 |
+------------+------------+----+----+

-- Enable a full table scan only for the current session. Execute the SELECT statement to query data in the mf_dt table. 
set odps.sql.allow.fullscan=true;
select * from mf_dt;
-- The following result is returned: 
+------------+------------+----+----+
| 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順序付け機能を使用する方法について説明します。提案やフィードバックを提供することもできます。

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

フィルタ条件がa、aとbの組み合わせ、a、b、cの組み合わせなどのプレフィックスの組み合わせで構成されている場合、クラスタ化インデックス作成 (ORDER BY a, b, c) の方がZORDER BY句よりも効果的です。 ORDER BY句は、最初のフィールドに対してより良いソート効果を提供しますが、残りのフィールドには小さな影響を与えます。 ZORDER BY句は各フィールドに同じ重みを提供し、特定のフィールドに対するソート結果は、最初の列のORDER BY句のソート結果よりも効果が低くなります。
一部のフィールドが結合キーとして頻繁に使用される場合、範囲クラスタリングまたはハッシュクラスタリングがフィールドに適しています。 MaxComputeのZ-Ordering機能を使用すると、1つのファイル内のデータのみをソートできます。 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でデータをソートするとコストが高くなります。例えば、ベースラインタスクの出力が遅延されてもよい。