更新と削除 - MaxCompute - Alibaba Cloud ドキュメントセンター

MaxComputeでは、DELETEまたはUPDATEステートメントを実行して、トランザクションテーブルの特定の行のデータを削除または更新できます。

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

前提条件

DELETEまたはUpdateステートメントを実行するトランザクションテーブルに対して、[選択] および [更新] 権限が付与されています。詳細は、「MaxCompute権限」をご参照ください。

説明

従来のSQL文と同様に、MaxComputeのDELETE文とUPDATE文を使用して、テーブル内の特定の行のデータを削除または更新できます。

DELETEまたはUPDATEステートメントを実行するたびに、削除または更新操作に関する情報を格納するデルタファイルが自動的に生成されます。このファイルはユーザーには見えません。デルタファイルの生成方法を次に示します。

DELETE: デルタファイルには、txnidフィールドとrowidフィールドが含まれ、どちらもBIGINT型の値を持ちます。 rowidフィールドは、トランザクションテーブルのベースファイル内の削除された行を示します。 txnidフィールドは、行に対して実行される削除操作を示します。
例えば、t1テーブルのベースファイルはf1であり、ベースファイルの内容はa, b, c, a, bである。 delete from t1 where c1='a'; ステートメントを実行すると、f1.deltaという名前のデルタファイルが生成されます。 txnidフィールドの値がt0の場合、f1.deltaファイルの内容は ((0, t0), (3, t0)) となります。これは、IDが0および3である行がt0トランザクションから削除されることを示す。 t1テーブルで別のDELETEステートメントを再度実行すると、f2.deltaという名前の別のデルタファイルが生成されます。ファイル名は、f1ベースファイルに基づいて生成されます。 t1テーブルのデータを照会すると、システムはf1、f1.delta、およびf2.deltaファイルに基づいて削除されたデータをフィルタリングし、削除されていないデータを返します。
UPDATE: UPDATEステートメントのロジックは、DELETEおよびINSERT intoステートメントを実行するロジックに変換されます。

DELETEおよびUPDATEステートメントには、次の利点があります。

書き込まれるデータの量を減らす
DELETEおよびUPDATEステートメントを提供する前に、MaxComputeではINSERT INTOまたはINSERT OVERWRITEステートメントのみを実行して、テーブル内のデータを更新または削除できます。詳細については、「テーブルまたは静的パーティションへのデータの挿入または更新 (Insert intoおよびINSERT OVERWRITE) 」をご参照ください。テーブルまたはテーブルのパーティション内の少量のデータを更新するときにINSERT文を実行する場合は、まずSELECT文を実行してテーブルからすべてのデータを読み取り、データを更新する必要があります。次に、INSERTステートメントを実行して、すべてのデータをテーブルに挿入できます。この方法は非効率的です。ただし、上記のシナリオでDELETEまたはUPDATEステートメントを使用した場合、システムはすべてのデータをテーブルに書き込む必要はありません。これにより、書き込まれるデータ量が削減される。
説明
- 従量課金の課金方法を使用する場合、DELETE、UPDATE、またはINSERT OVERWRITEステートメントを実行することによって実行される書き込み操作に対しては課金されません。ただし、DELETEまたはUPDATEステートメントを実行する場合、MaxComputeはパーティションごとにデータをフィルタリングし、削除または更新するデータを読み取る必要があります。 SQLジョブの従量課金方法に基づいて、読み取り操作に対して課金されます。したがって、INSERT OVERWRITEステートメントと比較して、DELETEまたはUPDATEステートメントはコスト削減に役立ちません。
- サブスクリプションの課金方法を使用すると、DELETEまたはUPDATEステートメントを実行するときにデータを書き込むために消費されるリソースが少なくなります。 INSERT OVERWRITE文と比較して、DELETE文またはUPDATE文を使用すると、同じ量のリソースを使用してより多くのジョブを実行できます。
最新のデータでテーブルを読み取る
DELETEステートメントとUPDATEステートメントが提供される前に、MaxComputeでは履歴テーブルを使用してテーブル内の複数のデータエントリを更新できます。履歴テーブルを使用する場合は、start_dateやend_dateなどの補助列をテーブルに追加する必要があります。これらの列は、データエントリのライフサイクルを示します。テーブルの最新データを照会するために、システムは、タイムスタンプに基づいて大量のデータから最新データを識別しなければならない。このプロセスには時間がかかります。ただし、DELETEまたはUPDATEステートメントを実行して、データを削除または更新できます。テーブル内のデータを照会すると、システムはベースファイルとすべてのデルタファイルに基づいてテーブルの最新データを読み取ります。

重要

トランザクションテーブルでDELETEステートメントとUPDATEステートメントを複数回実行すると、トランザクションテーブルはより大きなストレージスペースを占有します。この場合、テーブル上のストレージおよび後続のクエリのコストが増加します。さらに、後続のクエリの効率が低下する。これらの問題を解決するには、ALTER TABLE COMPACTステートメントを実行して、ベースファイルをすべてのデルタファイルと定期的にマージすることをお勧めします。詳細については、「ALTER TABLE COMPACT」をご参照ください。

複数のジョブがテーブルで並列に実行されると、競合が発生する可能性があります。詳細については、「ACIDセマンティクス」をご参照ください。

シナリオ

DELETEまたはUPDATEステートメントを実行すると、テーブルまたはテーブルのパーティション内の少量のデータを低頻度で削除または更新できます。たとえば、ステートメントを実行して、データが生成された翌日に、テーブルまたはテーブルのパーティション内の5% 未満のデータを削除または更新できます。

DELETEまたはUPDATE文は、データを頻繁に削除または更新する場合、またはデータをリアルタイムでテーブルに書き込む場合には適用できません。

制限事項

DELETEステートメント、UPDATEステートメント、およびDELETEまたはUPDATEステートメントが実行されるトランザクションテーブルまたはデルタテーブルには、次の制限があります。
- トランザクションテーブルの重要なデータに対してUPDATE、DELETE、またはINSERT OVERWRITEステートメントを実行する前に、SELECTおよびINSERTステートメントを実行して、データを他のテーブルにバックアップする必要があります。
- UPDATEステートメントを実行して、Deltaテーブルの主キー列の値を変更することはできません。

注意事項

DELETEまたはUPDATEステートメントを実行して、テーブルまたはテーブルのパーティションのデータを削除または更新する場合は、次の項目に注意してください。

特定のシナリオでは、テーブル内の少量のデータに対してDELETEまたはUPDATEステートメントを実行し、後続の手順で読み取りやその他の操作を実行することはめったにありません。テーブルが占有するストレージ容量を減らすために、テーブルに対してDELETEまたはUPDATEステートメントを複数回実行した後に、ベースファイルをすべてのデルタファイルとマージすることをお勧めします。詳細については、「ALTER TABLE COMPACT」をご参照ください。
特定のシナリオでは、テーブルまたはテーブルのパーティション内の5% を超えるデータを低頻度で削除または更新し、後続の手順で頻繁に読み取り操作を実行することができます。このようなシナリオでは、INSERT OVERWRITEまたはINSERT INTOステートメントを実行することを推奨します。詳細については、「テーブルまたは静的パーティションへのデータの挿入または更新 (Insert intoおよびINSERT OVERWRITE) 」をご参照ください。
たとえば、10% のデータの削除または更新操作を1日に10回実行したいとします。この場合、テーブルに対してDELETEまたはUPDATEステートメントを実行すると、その後の読み取りパフォーマンスの総コストと消費量を見積もることを推奨します。次に、推定結果をINSERT OVERWRITEまたはINSERT INTOステートメントの実行結果と比較します。これは効率的な方法を選択するのに役立ちます。
テーブルに対してDELETEステートメントを実行するたびに、デルタファイルが自動的に生成されます。その結果、占有される保管スペースを減らすことができない。 DELETE文を実行してデータを削除し、ストレージ使用量を削減する場合は、ベースファイルをすべてのデルタファイルとマージできます。詳細については、「ALTER TABLE COMPACT」をご参照ください。
MaxComputeは、ジョブで複数のDELETEおよびUPDATEステートメントを一度に実行します。各ステートメントはリソースを消費し、料金が発生します。一度にデータのバッチを削除または更新することを推奨します。たとえば、Pythonスクリプトを実行して多数の行レベルの更新ジョブを生成および送信し、各ステートメントが1行または少数の行のデータに対してのみ実行される場合、各ステートメントは、SQLステートメントによってスキャンされた入力データの量に対応する料金を負担し、関連するコンピューティングリソースを消費します。複数のステートメントが蓄積されると、コストが大幅に増加し、システム効率が低下します。サンプル文:
- 次のステートメントを実行することを推奨します。
```
UPDATE table1 SET col1= (SELECT value1 FROM table2 WHERE table1.id = table2.id AND table1.region = table2.region);
```
- 次のステートメントは実行しないことを推奨します。
```
UPDATE table1 SET col1=1 WHERE id='2021063001'AND region='beijing';                  
UPDATE table1 SET col1=2 WHERE id='2021063002'AND region='beijing';
```

DELETE

DELETEステートメントを実行して、指定された条件を満たす1つ以上の行をトランザクションテーブルまたはデルタテーブルから削除できます。

構文

delete from <table_name> [where <where_condition>];

パラメーター
- table_name: 必須です。 DELETEステートメントを実行するトランザクションテーブルまたはデルタテーブルの名前。
- where_condition: オプション。条件に基づいてデータをフィルタリングするために使用されるWHERE句。詳細については、「WHERE句 (where_condition) 」をご参照ください。 WHERE句を持たないテーブルに対してDELETEステートメントを実行すると、テーブル内のすべてのデータが削除されます。

例

例1: acid_deleteという名前のパーティション分割されていないトランザクションテーブルを作成し、テーブルにデータを挿入します。次に、DELETEステートメントを実行して、指定された条件を満たす行をテーブルから削除します。サンプル文:

-- Create a non-partitioned transactional table named acid_delete. 
create table if not exists acid_delete(id bigint) tblproperties ("transactional"="true"); 

-- Insert data into the table. 
insert overwrite table acid_delete values(1),(2),(3),(2); 

-- Query the table to check whether data is inserted. 
select * from acid_delete; 
-- The following result is returned:
+------------+
| id         |
+------------+
| 1          |
| 2          |
| 3          |
| 2          |
+------------+

-- Delete the rows whose value of the id column is 2. If you execute the statement on the MaxCompute client (odpscmd), you must enter yes or no to confirm the deletion. 
delete from acid_delete where id = 2; 

-- Query the table to check whether the table contains only the rows whose values of the id column are 1 and 3. 
select * from acid_delete; 
-- The following result is returned:
+------------+
| id         |
+------------+
| 1          |
| 3          |
+------------+

例2: acid_delete_ptという名前のパーティション分割トランザクションテーブルを作成し、テーブルにデータを挿入します。次に、DELETEステートメントを実行して、指定された条件を満たす行をテーブルから削除します。サンプル文:

-- Create a partitioned transactional table named acid_delete_pt.  
create table if not exists acid_delete_pt(id bigint) partitioned by(ds string) tblproperties ("transactional"="true");

-- Add partitions to the table. 
alter table acid_delete_pt add if not exists partition (ds= '2019');
alter table acid_delete_pt add if not exists partition (ds= '2018');

-- Insert data into the partitions. 
insert overwrite table acid_delete_pt partition (ds='2019') values(1),(2),(3);
insert overwrite table acid_delete_pt partition (ds='2018') values(1),(2),(3);

-- Query the table to check whether data is inserted. 
select * from acid_delete_pt;
-- The following result is returned:
+------------+------------+
| id         | ds         |
+------------+------------+
| 1          | 2018       |
| 2          | 2018       |
| 3          | 2018       |
| 1          | 2019       |
| 2          | 2019       |
| 3          | 2019       |
+------------+------------+

-- Delete the rows whose values of the id and ds columns are 2 and 2019. If you execute the statement on the MaxCompute client (odpscmd), you must enter yes or no to confirm the deletion. 
delete from acid_delete_pt where ds='2019' and id = 2;

-- Query the table to check whether the rows whose values of the id and ds columns are 2 and 2019 are deleted. 
select * from acid_delete_pt;
-- The following result is returned:
+------------+------------+
| id         | ds         |
+------------+------------+
| 1          | 2018       |
| 2          | 2018       |
| 3          | 2018       |
| 1          | 2019       |
| 3          | 2019       |
+------------+------------+

例3: acid_delete_tという名前の宛先テーブルと、acid_delete_sという名前の関連テーブルを作成します。次に、関連付けられたテーブルに基づいて、指定された条件を満たす行を宛先テーブルから削除します。サンプル文:

-- Create a destination table named acid_delete_t and an associated table named acid_delete_s. 
create table if not exists acid_delete_t(id int,value1 int,value2 int) tblproperties ("transactional"="true");
create table if not exists acid_delete_s(id int,value1 int,value2 int);

-- Insert data into the tables. 
insert overwrite table acid_delete_t values(2,20,21),(3,30,31),(4,40,41);
insert overwrite table acid_delete_s values(1,100,101),(2,200,201),(3,300,301);

-- Delete the rows in the acid_delete_t table whose value of the id column does not match that of the rows in the acid_delete_s table. If you want to execute the statement on the MaxCompute client (odpscmd), you must enter yes or no to confirm the deletion. 
delete from acid_delete_t where not exists (select * from acid_delete_s where acid_delete_t.id=acid_delete_s.id);

-- Query the acid_delete_t table to check whether the table contains only the rows whose values of the id column are 2 and 3. 
select * from acid_delete_t;

-- The following result is returned:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 20         | 21         |
| 3          | 30         | 31         |
+------------+------------+------------+

例4: mf_dtという名前のDeltaテーブルを作成し、そのテーブルにデータをインポートし、DELETEステートメントを実行して、指定された条件を満たす行をテーブルから削除します。サンプル文:

-- 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 data into the table.
insert overwrite table mf_dt partition (dd='01', hh='02') values (1, 1), (2, 2), (3, 3);

-- Query the table to check whether data is inserted.                 
select * from mf_dt where dd='01' and hh='02';

-- The following result is returned:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 02 |
| 3          | 3          | 01 | 02 |
| 2          | 2          | 01 | 02 |
+------------+------------+----+----+

-- Delete the row whose value of the val column is 2 from the dd='01' and hh='02' partition. 
delete from mf_dt where val = 2  and dd='01' and hh='02';

-- Query the result table. Only the rows whose values of the val column are 1 and 3 exist in the specified partition.
select * from mf_dt where dd='01' and hh='02';

-- The following result is returned:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 02 |
| 3          | 3          | 01 | 02 |
+------------+------------+----+----+

ALTER TABLEクリアコラム

ALTER TABLE CLEAR COLUMNステートメントを実行して、共通テーブルで不要になった列のデータをディスクから消去し、列の値をnullに設定できます。これにより、ストレージコストを削減できます。

構文

ALTER TABLE <table_name> 
           [partition ( <pt_spec>[, <pt_spec>....] )] 
					 CLEAR COLUMN column1[, column2, column3, ...]
			                         [without touch];

パラメーター
- table_name: ALTER table CLEAR COLUMNステートメントを実行するテーブルの名前。
- column1 , column2...: データをクリアする必要がある列の名前。
- partition: ALTER TABLE CLEAR COLUMNステートメントを実行するパーティション。パーティションが指定されていない場合、ステートメントはテーブル内のすべてのパーティションで実行されます。
- pt_spec: パーティションの説明。このパラメーターは、(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...) 形式で設定します。
- without touch: このパラメーターを設定した場合、time when the data was last modifiedは更新されません。そうでなければ、timeは更新される。
説明
デフォルトでは、without touchパラメーターが設定されています。 without touchパラメーターが設定されていない場合でも、将来、列からデータを削除することもできます。 without touchパラメーターを設定しない場合、time when the data was last modifiedが更新されます。
制限事項
- ALTER TABLE CLEAR COLUMNステートメントは、nullableプロパティを持つ列に対しては実行できません。列のnull非許容プロパティを削除できます。
```
alter table <table_name> change column <old_col_name> null;
```
- ALTER TABLE CLEAR COLUMNステートメントは、原子性、一貫性、分離、耐久性 (ACID) テーブルでは実行できません。
- ALTER TABLE CLEAR COLUMNステートメントは、クラスタ化されたテーブルでは実行できません。
- ALTER TABLE CLEAR COLUMNステートメントを実行して、NESTED型の列内のサブ列のデータをクリアすることはできません。
- ALTER TABLE CLEAR COLUMNステートメントを実行して、テーブルのすべての列のデータをクリアすることはできません。テーブルのすべての列のデータをクリアする場合は、DROP tableステートメントを実行すると、パフォーマンスが向上します。
注意事項
- ALTER TABLE CLEAR COLUMNステートメントでは、テーブルのArchiveプロパティは変更されません。
- ALTER TABLE CLEAR COLUMNステートメントを実行して、NESTED型の列のデータをクリアできない場合があります。
  列指向ストレージモードのネストされたデータをクリアしても、そのデータのモードが無効になっている場合、エラーが発生します。
- ALTER TABLE CLEAR COLUMNステートメントの実行は、オンラインストレージサービスによって異なります。実行するジョブの数が多い場合、ステートメントをキューに入れる必要があり、完了するまでに長い時間が必要になる場合があります。
- ALTER TABLE CLEAR COLUMNステートメントを実行すると、データの読み取りおよび書き込み操作を実行するためにコンピューティングリソースが消費されます。サブスクリプションの課金方法を使用すると、コンピューティングリソースが占有されます。従量課金の課金方法を使用する場合、SQLジョブと同じ課金ルールに基づいてALTER TABLE CLEAR COLUMNステートメントを実行した場合に課金されます。従量課金方法のALTER TABLE CLEAR COLUMNステートメントは招待プレビューにあります。この課金方法を使用するユーザーは、ステートメントを無料で実行できます。

例

-- Create a table.
create table if not exists mf_cc(key string, 
     value string, 
     a1 BIGINT , 
     a2 BIGINT , 
     a3 BIGINT , 
     a4 BIGINT)  
     partitioned by(ds string, hr string);
                 
-- Add a partition to the table.
alter table mf_cc add if not exists partition (ds='20230509', hr='1641');

-- Insert data into the partition.
insert into mf_cc partition (ds='20230509', hr='1641') 
     values("key","value",1,22,3,4);
     
-- Query the partition to check whether data is inserted.     
select * from mf_cc where ds='20230509' and  hr='1641';
-- The following result is returned:
+-----+-------+------------+------------+--------+------+---------+-----+
| key | value | a1         | a2         | a3     | a4   | ds      | hr  |
+-----+-------+------------+------------+--------+------+---------+-----+
| key | value | 1          | 22         | 3      | 4    | 20230509| 1641|
+-----+-------+------------+------------+--------+------+---------+-----+
-- Clear data from columns.
alter table mf_cc partition(ds='20230509', hr='1641') 
                  clear column key,a1 
    without touch;
-- Query data from the columns.     
select * from mf_cc where ds='20230509' and  hr='1641';
-- The following result is returned. The values of the key and a1 columns become null.
+-----+-------+------------+------------+--------+------+---------+-----+
| key | value | a1         | a2         | a3     | a4   | ds      | hr  |
+-----+-------+------------+------------+--------+------+---------+-----+
| null| value | null       | 22         | 3      | 4    | 20230509| 1641|
+-----+-------+------------+------------+--------+------+---------+-----+

次の図は、ALTER table CLEAR COLUMNステートメントを複数回実行することによって、すべての列が1つずつクリアされるlineitemテーブルの合計ストレージスペースの変化を示しています。 lineitemテーブルのデータ格納形式はAliORCです。 lineitemテーブルには、BIGINT、DECIMAL、CHAR、DATE、およびVARCHARデータ型の16の列が含まれます。
ALTER TABLE CLEAR COLUMNステートメントを繰り返し実行して、テーブル内のすべての列の値を順番にnullに変更すると、テーブルが占有するストレージスペースが186,783,526バイトから236,715バイトに変わり、99.97% 削減されます。
説明
- ALTER TABLE CLEAR COLUMNステートメントの実行によって解放されるストレージスペースのサイズは、列のデータ型と列の値に関連しています。この例では、DECIMALタイプのI_extendedprice列のストレージスペースが146,538,799バイトから111,138,117バイトに変更され、24.2% 削減されます。これは、解放されるストレージスペースの平均的な割合よりもかなり大きい。
- すべての列の値がnullに設定されると、テーブルによって占有されるストレージスペースは0ではなく236,715バイトになります。これは、テーブルに対して生成されたファイルの構造がまだ存在するためです。ヌル値も少量のストレージスペースを占有し、システムはファイルのフッター情報を保持します。

UPDATE

UPDATEステートメントを実行して、トランザクションテーブルまたはデルタテーブルで指定された条件を満たす行の1つ以上の列の値を更新できます。

構文

-- Method 1
update <table_name> set <col1_name> = <value1> [, <col2_name> = <value2> ...] [WHERE <where_condition>];

-- Method 2
update <table_name> set (<col1_name> [, <col2_name> ...]) = (<value1> [, <value2> ...])[WHERE <where_condition>];

-- Method 3
UPDATE <table_name>
       SET <col1_name> = <value1> [ , <col2_name> = <value2> , ... ]
        [ FROM <additional_tables> ]
        [ WHERE <where_condition> ]

パラメーター

table_name: 必須です。 UPDATEステートメントを実行するトランザクションテーブルの名前。
col1_nameおよびcol2_name: 更新する列。少なくとも1つの列を指定する必要があります。このパラメーターには、指定したフィルター条件を満たす行を含む列の名前を指定します。
value1およびvalue2: 指定した列に割り当てる新しい値。少なくとも1つの列の値を更新する必要があります。このパラメーターは、指定されたフィルター条件を満たす行に対応する列の新しい値を指定します。
where_condition: オプション。条件に基づいてデータをフィルタリングするために使用されるWHERE句。詳細については、「WHERE句 (where_condition) 」をご参照ください。 WHERE句を持たないテーブルに対してUPDATEステートメントを実行すると、テーブル内のすべてのデータが更新されます。

additional_tables: オプション。 A from句。

UPDATEステートメントは、from句とともに使用できます。 from句は、UPDATEステートメントの使用をより便利にします。次の表では、from句の有無にかかわらず使用されるUPDATEステートメントについて説明します。

シナリオ

サンプルコード

from句なし

update target set v =
  (select min(v) from src group by k where target.k = src.key)
  where target.k in (select k from src);

from句付き

update target set v = b.v
  from (select k, min(v) v from src group by k) b
  where target.k = b.k;

上記のサンプルコードに基づいて、次の結論が得られます。

ソーステーブルの複数行のデータを使用して、ターゲットテーブルの1行のデータを更新する場合は、データソースの一意性を確保するために集計操作を記述する必要があります。集計操作のコードは、from句を使用しない場合よりも、from句を使用する方が簡単でわかりやすくなります。
結合操作中に交差データのみを更新する必要がある場合は、from句またはwhere句を使用する必要があります。 where句は、from句よりも複雑です。

例

例1: acid_updateという名前のパーティション分割されていないテーブルを作成し、テーブルにデータを挿入します。次に、UPDATEステートメントを実行して、テーブル内の指定された条件を満たす行の列を更新します。サンプル文:

-- Create a non-partitioned table named acid_update.   
create table if not exists acid_update(id bigint) tblproperties ("transactional"="true");

-- Insert data into the table. 
insert overwrite table acid_update values(1),(2),(3),(2);

-- Query the table to check whether data is inserted. 
select * from acid_update; 

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

-- Update the value 2 in the id column to 4. 
update acid_update set id = 4 where id = 2; 

-- Query the partition to check whether the value 2 is updated to 4 in the id column. 
select * from acid_update; 

-- The following result is returned:
+------------+
| id         |
+------------+
| 1          |
| 3          |
| 4          |
| 4          |
+------------+

例2: acid_updateという名前のパーティションテーブルを作成し、テーブルにデータを挿入します。次に、UPDATEステートメントを実行して、テーブル内の指定された条件を満たす行の列を更新します。サンプル文:

-- Create a partitioned table named acid_update_pt.  
create table if not exists acid_update_pt(id bigint) partitioned by(ds string) tblproperties ("transactional"="true");

-- Add a partition to the table. 
alter table acid_update_pt add if not exists partition (ds= '2019');

-- Insert data into the partition. 
insert overwrite table acid_update_pt partition (ds='2019') values(1),(2),(3);

-- Query the partition to check whether data is inserted.
select * from acid_update_pt where ds = '2019';

-- The following result is returned:
+------------+------------+
| id         | ds         |
+------------+------------+
| 1          | 2019       |
| 2          | 2019       |
| 3          | 2019       |
+------------+------------+

-- Update the value 2 of the id column in the 2019 partition to 4. 
update acid_update_pt set id = 4 where ds = '2019' and id = 2; 

-- Query the partition to check whether the value 2 is updated to 4 in the id column. 
select * from acid_update_pt where ds = '2019';

-- The following result is returned:
+------------+------------+
| id         | ds         |
+------------+------------+
| 4          | 2019       |
| 1          | 2019       |
| 3          | 2019       |
+------------+------------+

例3: 更新するacid_update_tという名前のトランザクションテーブルと、acid_update_sという名前の関連テーブルを作成します。次に、acid_update_tテーブルの複数の列の値を一度に更新します。サンプル文:

-- Create a transactional table named acid_update_t that you want to update and an associated table named acid_update_s. 
create table if not exists acid_update_t(id int,value1 int,value2 int) tblproperties ("transactional"="true");
create table if not exists acid_update_s(id int,value1 int,value2 int);

-- Insert data into the tables. 
insert overwrite table acid_update_t values(2,20,21),(3,30,31),(4,40,41);
insert overwrite table acid_update_s values(1,100,101),(2,200,201),(3,300,301);

-- Method 1: Update the values of specific columns with constants. 
update acid_update_t set (value1, value2) = (60,61);
-- Query the acid_update_t table to check whether data is updated as expected. 
select * from acid_update_t;

-- The following result is returned:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 60         | 61         |
| 3          | 60         | 61         |
| 4          | 60         | 61         |
+------------+------------+------------+

-- Method 2: Use the data in the acid_update_s table to update all rows in the acid_update_t table. If specific rows in the acid_update_t table cannot be matched, null values are assigned to the rows. 
update acid_update_t set (value1, value2) = (select value1, value2 from acid_update_s where acid_update_t.id = acid_update_s.id);
-- Query the acid_update_t table to check whether data is updated as expected. 
select * from acid_update_t;

-- The following result is returned:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 200        | 201        |
| 3          | 300        | 301        |
| 4          | NULL       | NULL       |
+------------+------------+------------+

-- Method 3: Use the data in the acid_update_s table to update the acid_update_t table. Add filter conditions for the acid_update_t table to update only the rows that intersect with those in the acid_update_s table. 
update acid_update_t set (value1, value2) = (select value1, value2 from acid_update_s where acid_update_t.id = acid_update_s.id) where acid_update_t.id in (select id from acid_update_s);
-- Query the acid_update_t table to check whether data is updated as expected. 
select * from acid_update_t;

-- The following result is returned:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 200        | 201        |
| 3          | 300        | 301        |
| 4          | NULL       | NULL       |
+------------+------------+------------+

-- Method 4: Use the aggregate results of the acid_update_t and acid_update_s tables to update the acid_update_t table. 
update acid_update_t set (id, value1, value2) = (select id, max(value1),max(value2) from acid_update_s where acid_update_t.id = acid_update_s.id group by acid_update_s.id) where acid_update_t.id in (select id from acid_update_s);
-- Query the acid_update_t table to check whether data is updated as expected. 
select * from acid_update_t;

-- The following result is returned:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 200        | 201        |
| 3          | 300        | 301        |
| 4          | NULL       | NULL       |
+------------+------------+------------+

例4: 2つのテーブルに対して結合操作を実行します。サンプル文:

-- Create a destination table named acid_update_t and create a table named acid_update_s for joins.
create table if not exists acid_update_t(id bigint,value1 bigint,value2 bigint) tblproperties ("transactional"="true");
create table if not exists acid_update_s(id bigint,value1 bigint,value2 bigint);

-- Insert data into these tables.
insert overwrite table acid_update_t values(2,20,21),(3,30,31),(4,40,41);
insert overwrite table acid_update_s values(1,100,101),(2,200,201),(3,300,301);

-- Query data from the acid_update_t table.
select * from acid_update_t;
-- The following result is returned:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 20         | 21         |
| 3          | 30         | 31         |
| 4          | 40         | 41         |
+------------+------------+------------+

-- Query data from the acid_update_s table.
select * from acid_update_s;
-- The following result is returned:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 1          | 100        | 101        |
| 2          | 200        | 201        |
| 3          | 300        | 301        |
+------------+------------+------------+

-- Use the data in the acid_update_s table to update the acid_update_t table. Add filter conditions for the acid_update_t table to update only the intersection of the two tables.
update acid_update_t set value1 = b.value1, value2 = b.value2
from acid_update_s b where acid_update_t.id = b.id;

-- Value 20 is updated to 200, value 21 is updated to 201, value 30 is updated to 300, and value 31 is updated to 301.
select * from acid_update_t;
-- The following result is returned:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 4          | 40         | 41         |
| 2          | 200        | 201        |
| 3          | 300        | 301        |
+------------+------------+------------+

例5: 3つ以上のテーブルに対して複雑な結合操作を実行します。サンプル文:

-- Create a destination table named acid_update_t and create a table named acid_update_s for joins.
create table if not exists acid_update_t(id bigint,value1 bigint,value2 bigint) tblproperties ("transactional"="true");
create table if not exists acid_update_s(id bigint,value1 bigint,value2 bigint);
create table if not exists acid_update_m(id bigint,value1 bigint,value2 bigint);

-- Insert data into these tables.
insert overwrite table acid_update_t
values(2,20,21),(3,30,31),(4,40,41),(5,50,51);
insert overwrite table acid_update_s
values (1,100,101),(2,200,201),(3,300,301),(4,400,401),(5,500,501);
insert overwrite table acid_update_m
values(3,30,101),(4,400,201),(5,300,301);

-- Query data from the acid_update_t table.
select * from acid_update_t;
-- The following result is returned:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 20         | 21         |
| 3          | 30         | 31         |
| 4          | 40         | 41         |
| 5          | 50         | 51         |
+------------+------------+------------+

-- Query data from the acid_update_s table.
select * from acid_update_s;
-- The following result is returned:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 1          | 100        | 101        |
| 2          | 200        | 201        |
| 3          | 300        | 301        |
| 4          | 400        | 401        |
| 5          | 500        | 501        |
+------------+------------+------------+

-- Query data from the acid_update_m table.
select * from acid_update_m;
-- The following result is returned:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 3          | 30         | 101        |
| 4          | 400        | 201        |
| 5          | 300        | 301        |
+------------+------------+------------+

-- Use the data in the acid_update_s table to update the acid_update_t table. Use the where clause to filter values in the acid_update_s, acid_update_t, and acid_update_m tables.
update acid_update_t
set value1 = acid_update_s.value1, value2 = acid_update_s.value2
from acid_update_s
where acid_update_t.id = acid_update_s.id
and acid_update_s.id > 2
and acid_update_t.value1 not in
(select value1 from acid_update_m where id = acid_update_t.id)
and acid_update_s.value1 not in
(select value1 from acid_update_m where id = acid_update_s.id);

-- View the update result. Only the row of data that contains the value of 5 in the id column of the acid_update_t table meets the condition. The value in the value1 column is updated to 500, and the value in the value2 column is updated to 501.
select * from acid_update_t;
-- The following result is returned:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 5          | 500        | 501        |
| 2          | 20         | 21         |
| 3          | 30         | 31         |
| 4          | 40         | 41         |
+------------+------------+------------+

例6: mf_dtという名前のDeltaテーブルを作成し、テーブルにデータをインポートし、UPDATEステートメントを実行して、指定された条件を満たす行をテーブルから削除します。サンプル文:

-- 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 data into the table.
insert overwrite table mf_dt partition (dd='01', hh='02') 
                 values (1, 1), (2, 2), (3, 3);

-- Query the table to check whether data is inserted.                 
select * from mf_dt where dd='01' and hh='02';
-- The following result is returned:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 02 |
| 3          | 3          | 01 | 02 |
| 2          | 2          | 01 | 02 |
+------------+------------+----+----+

-- Update the value of the val column in the row whose value of the pk column is 3 in the dd='01' and hh='02' partition to 30. 
-- Method 1
update mf_dt set val = 30 where pk = 3 and dd='01' and hh='02';

-- Method 2
update mf_dt set val = delta.val 
              from (select pk, val from values (3, 30) t (pk, val)) delta 
              where delta.pk = mf_dt.pk and mf_dt.dd='01' and mf_dt.hh='02';

-- View the update result. 
select * from mf_dt where dd='01' and hh='02';
-- The following result is returned. The value of the val column in the row whose value of the pk column is 3 is updated to 30. 
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 02 |
| 3          | 30         | 01 | 02 |
| 2          | 2          | 01 | 02 |
+------------+------------+----+----+

ALTERテーブルコンパクト

トランザクションテーブルのベースファイルとデルタファイルは物理ストレージを占有します。そのようなファイルを直接読み取ることはできません。トランザクションテーブルでUPDATEまたはDELETEステートメントを実行した場合、基本ファイルのデータは更新されませんが、操作ごとにデルタファイルが生成されます。この場合、より多くの削除または更新操作が実行されるほど、テーブルがより多くの記憶空間を占有する。デルタファイルの数が増えます。その結果、ストレージ使用量とその後のクエリに対してより多くの料金が請求されます。

テーブルまたはテーブルのパーティションに対してUPDATEまたはDELETEステートメントを複数回実行すると、多数のデルタファイルが生成されます。システムがテーブルからデータを読み取ると、システムはデルタファイルをロードして、削除および更新された行を識別します。デルタファイルの数が多いと、データの読み取り効率が低下します。この場合、ベースファイルをデルタファイルとマージして、ストレージ使用量を減らし、読み取り効率を向上させることができます。

構文

alter table <table_name> [partition (<partition_key> = '<partition_value>' [, ...])] compact {minor|major};

パラメーター
- table_name: 必須です。ベースファイルとデルタファイルをマージするトランザクションテーブルの名前。
- partition_key: オプション。パーティション化されたトランザクションテーブルのパーティションキー列の名前。
- partition_value: オプション。パーティション化されたトランザクションテーブルのパーティションキー列の値。
- major | minor: いずれかを指定する必要があります。マイナーコンパクションとメジャーコンパクションの違い:
  - minor: 各ベースファイルをベースファイルに基づいて生成されたすべてのデルタファイルとマージし、デルタファイルを削除します。
  - major: 各ベースファイルをベースファイルに基づいて生成されたすべてのデルタファイルとマージし、デルタファイルを削除し、小さなベースファイルをマージします。ベースファイルのサイズが32 MB未満の場合、またはデルタファイルが生成された場合、ファイルをマージした場合の効果は、INSERT OVERWRITEステートメントを実行した場合の効果と同等です。ただし、ベースファイルのサイズが32 MB以上で、デルタファイルが生成されない場合、テーブルのデータは上書きされません。
注意事項
コンパクト操作でマージされた小さなファイルは、1日後に削除されます。バックアップと復元機能を使用して、これらの小さなファイルに依存する履歴データを復元すると、復元は失敗します。

例

例1: acid_deleteテーブルのファイルをマージします。例：

alter table acid_delete compact minor;

次の応答が返されます。

Summary:
Nothing found to merge, set odps.merge.cross.paths=true if cross path merge is permitted.
OK

例2: acid_update_ptテーブルのファイルをマージします。例：

alter table acid_update_pt partition (ds = '2019') compact major;

次の応答が返されます。

Summary:
table name: acid_update_pt /ds=2019  instance count: 2  run time: 6
  before merge, file count:        8  file size: 2613  file physical size: 7839
   after merge, file count:        2  file size: 679  file physical size: 2037

OK

よくある質問

問題1:
- 問題の説明: UPDATEステートメントを実行すると、次のエラーメッセージが返されます。ODPS-0010000: System internal error - fuxi job failed, caused by: Data Set should contain exactly one row。
- 原因: 更新する行が、サブクエリによってクエリされる行と一致しません。この場合、システムはどの行を更新する必要があるかを判断できません。例：
```
update store set (s_county, s_manager) = (select d_country, d_manager from store_delta sd where sd.s_store_sk = store.s_store_sk) where s_store_sk in (select s_store_sk from store_delta);
```
  select d_country, d_manager from store_delta sd where sd.s_store_sk = store.s_store_skサブクエリを使用して、store_deltaテーブルのデータをフィルタリングします。そして、指定された条件を満たすデータを用いて、店舗テーブルのデータを更新する。たとえば、ストアテーブルのs_store_sk列を持つ3つの行は [1, 2, 3] です。 store_deltaテーブルのs_store_sk列が [1, 1] で、ストアテーブルの行と一致しない場合、前述のエラーメッセージが返されます。
- 解決策: 更新する行が、サブクエリによってクエリされる行と完全に一致することを確認します。
問題2:
- 問題の説明: DataWorks DataStudioでcompactコマンドを実行すると、次のエラーメッセージが返されます。ODPS-0130161:[1,39] Parse exception - invalid token 'minor', expect one of 'StringLiteral','DoubleQuoteStringLiteral' 。
- 原因: DataWorksの排他的リソースグループに対応するMaxComputeクライアントバージョンは、compactコマンドをサポートしていません。
- 解決策: DataWorks DingTalkグループに参加し、技術サポートチームに連絡して、排他的リソースグループに対応するMaxComputeクライアントのバージョンを更新します。