MaxCompute:動的パーティションへのデータの挿入または上書き (dynamic PARTITION)

前提条件

INSERT INTOまたはINSERT OVERWRITE操作を実行する前に、ターゲットテーブルの更新権限 (update) とソーステーブルのメタデータ読み取り権限 (Select) が必要です。 権限付与の詳細については、「MaxCompute権限」をご参照ください。

機能の説明

MaxCompute SQLを使用してデータを処理する場合、パーティションキー列の値ではなく、INSERT INTOまたはINSERT OVERWRITEのパーティションキー列の名前のみを指定する必要があります。 SELECTでパーティションキー列の値を指定すると、MaxComputeは列の値に基づいて宛先パーティションにデータを自動的に挿入します。

静的パーティションにデータを挿入する方法の詳細については、「テーブルまたは静的パーティションにデータを挿入または更新する (insert intoおよびInsert OVERWRITE) 」をご参照ください。

制限事項

注意事項

構文

INSERT {INTO|OVERWRITE} TABLE <table_name> PARTITION (<ptcol_name>[, <ptcol_name> ...]) 
<select_statement> FROM <from_statement>;

Parameters

サンプルデータ

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

--Return results.
+------------+-------------+-------------+------------+------------+
| 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      |
+------------+-------------+-------------+------------+------------+

例

次の例では、サンプルデータに示すように、sale_detailテーブルのデータを使用します。

• 例1: ソーステーブルから宛先テーブルにデータを挿入します。 region列に基づいて生成されたパーティションは、ステートメントが実行された後にのみ取得できます。 次のステートメントは例を示します。

  --Create a destination table total_revenues.
  CREATE TABLE total_revenues (revenue DOUBLE) PARTITIONED BY (region string);
  
  --Insert the data from the sale_detail table into the total_revenues table.
  SET odps.sql.allow.fullscan=true; 
  INSERT OVERWRITE TABLE total_revenues PARTITION(region) SELECT total_price AS revenue, region FROM sale_detail;
  
  --Execute the SHOW PARTITIONS statement to view the partitions of the total_revenues table.
  SHOW PARTITIONS total_revenues;
     
  --Return results.
  region=china  
  
  --Enable a full table scan only for the current session. Execute the SELECT statement to view data in the total_revenues table.  
  SET odps.sql.allow.fullscan=true; 
  SELECT * FROM total_revenues;    
  
  --Return results.
  +------------+------------+
  | revenue    | region     |
  +------------+------------+
  | 100.1      | china      |
  | 100.2      | china      |
  | 100.3      | china      |
  +------------+------------+        

• 例2: ソーステーブルから宛先テーブルにデータを挿入します。 宛先テーブルに複数のレベルのパーティションがある場合は、レベル1のパーティションsale_dateを指定する必要があります。 次のステートメントは例を示します。

  --Create a destination table sale_detail_dypart. 
  CREATE TABLE sale_detail_dypart LIKE sale_detail; 
  
  --Specify a level-1 partition and insert data into the destination table.
  SET odps.sql.allow.fullscan=true; 
  INSERT OVERWRITE TABLE sale_detail_dypart PARTITION (sale_date='2013', region)
  SELECT shop_name,customer_id,total_price,region FROM sale_detail;
  
  --Enable a full table scan only for the current session. Execute the SELECT statement to view data in the sale_detail_dypart table.
  SET odps.sql.allow.fullscan=true; 
  SELECT * FROM sale_detail_dypart;
  
  --Return results.
  +------------+-------------+-------------+------------+------------+
  | 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: select_statementの列とターゲットテーブルの動的パーティションの列の間のマッピングは、列名ではなく列の順序によって決まります。 次のステートメントは例を示します。

  --Insert data from the sale_detail table into the sale_detail_dypart table.
  SET odps.sql.allow.fullscan=true; 
  INSERT OVERWRITE TABLE sale_detail_dypart PARTITION (sale_date, region)
  SELECT shop_name,customer_id,total_price,sale_date,region FROM sale_detail;
  
  --Enable a full table scan only for the current session. Execute the SELECT statement to view data in the sale_detail_dypart table.
  SET odps.sql.allow.fullscan=true; 
  SELECT * FROM sale_detail_dypart;
  
  --Return results. The sale_date column in the dynamic partition of the sale_detail_dypart table is determined by the sale_date column in the sale_detail table. The region column in the dynamic partition of the sale_detail_dypart table is determined by the region column in the sale_detail table.
  +------------+-------------+-------------+------------+------------+
  | 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      |
  +------------+-------------+-------------+------------+------------+
  
  
  --Insert data from the sale_detail table into the sale_detail_dypart table and change the sequence of columns in select_statement.
  SET odps.sql.allow.fullscan=true; 
  INSERT OVERWRITE TABLE sale_detail_dypart PARTITION (sale_date, region)
  SELECT shop_name,customer_id,total_price,region,sale_date FROM sale_detail;
  
  --Enable a full table scan only for the current session. Execute the SELECT statement to view data in the sale_detail_dypart table.
  SET odps.sql.allow.fullscan=true; 
  SELECT * FROM sale_detail_dypart;
  
  --Return results. The sale_date column in the dynamic partition of the sale_detail_dypart table is determined by the region column in the sale_detail table. The region column in the dynamic partition of the sale_detail_dypart table is determined by the sale_date column in the sale_detail table.
  +------------+-------------+-------------+------------+------------+
  | shop_name  | customer_id | total_price | sale_date  | region     |
  +------------+-------------+-------------+------------+------------+
  | s1         | c1          | 100.1       | china      | 2013       |
  | s2         | c2          | 100.2       | china      | 2013       |
  | s3         | c3          | 100.3       | china      | 2013       |
  | s1         | c1          | 100.1       | 2013       | china      |
  | s2         | c2          | 100.2       | 2013       | china      |
  | s3         | c3          | 100.3       | 2013       | china      |
  +------------+-------------+-------------+------------+------------+

• 例4 (誤った例): 動的パーティションにデータを挿入する場合、SELECTで動的パーティションの列を指定する必要があります。 そうしないと、データの挿入に失敗します。 誤った使用法のサンプルステートメント:

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

  返される結果 :

  FAILED: ODPS-0130071:[1,24] Semantic analysis exception - wrong columns count 3 in data source, requires 4 columns (includes dynamic partitions if any)

• 例5 (不適切な例): 動的パーティションにデータを挿入するときに低レベルのサブパーティションのみを指定すると、高レベルのパーティションにデータを挿入できない場合があります。 誤った使用法のサンプルステートメント:

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

  返される結果 :

  FAILED: ODPS-0130071:[1,72] Semantic analysis exception - static partition region must be a high level partition than any dynamic partitions

• 例6: パーティションキー列のデータ型がSELECTの列のデータ型と完全に一致しない場合、MaxComputeが動的パーティションにデータを挿入するときに暗黙の変換が実行されます。 次のステートメントは例を示します。

  --Create a source table src.
  CREATE TABLE src (c INT, d STRING) PARTITIONED BY (e INT);
  
  --Add a partition to the src table.
  ALTER TABLE src ADD IF NOT EXISTS PARTITION (e=201312);
  
  --Append data to the src table.
  INSERT INTO src PARTITION (e=201312) VALUES (1,100.1),(2,100.2),(3,100.3);
  
  --Create a destination table parttable.
  CREATE TABLE parttable(a INT, b DOUBLE) PARTITIONED BY (p STRING);
  
  --Insert data from the src table into the parttable table.
  SET odps.sql.allow.fullscan=true; 
  INSERT INTO parttable PARTITION (p) SELECT c, d, CURRENT_TIMESTAMP() FROM src;
  
  --Query data in the parttable table.
  SET odps.sql.allow.fullscan=true;
  SELECT * FROM parttable;
  
  --The return result is as follows.
  +------------+------------+------------+
  | a          | b          | p          |
  +------------+------------+------------+
  | 1          | 100.1      | 2024-12-10 15:59:34.492 |
  | 2          | 100.2      | 2024-12-10 15:59:34.492 |
  | 3          | 100.3      | 2024-12-10 15:59:34.492 |
  +------------+------------+------------+

  説明

  データが順序付けられている場合、データが動的パーティションに挿入されるときにランダムに分散されます。 これにより、データの圧縮率が低下する。 この場合、Tunnelコマンドを使用してデータを動的パーティションにアップロードし、データ圧縮率を上げることを推奨します。 Tunnelコマンドの使用方法の詳細については、「動的パーティション分割に基づくApsaraDB RDSからMaxComputeへのデータ移行」をご参照ください。