MaxCompute:Insert or overwrite data (INSERT INTO | INSERT OVERWRITE)

Prerequisites

To perform INSERT INTO and INSERT OVERWRITE operations, you must have Update permission on the target table and Select permission on the source table. For more information about how to grant permissions, see MaxCompute Permissions.

Overview

When processing data with MaxCompute SQL, you can use the INSERT INTO or INSERT OVERWRITE statement to save the result of a SELECT query to a target table. The differences are as follows:

• INSERT INTO: Appends data to a table or a static partition. You can specify partition values in the INSERT statement to insert data into a specific partition. To insert a small amount of test data, you can use this statement with VALUES.

• INSERT OVERWRITE: Clears the existing data in a table or static partition before inserting the new data.

  Note

  • The MaxCompute INSERT syntax differs from the INSERT syntax in MySQL or Oracle. You can omit the TABLE keyword from both INSERT INTO and INSERT OVERWRITE statements.

  • When you repeatedly perform an INSERT OVERWRITE operation on the same partition, the partition Size that is returned by the DESC command may be different. This is because when you SELECT data from a partition and then use INSERT OVERWRITE to write the data back to the same partition, the file splitting logic changes. As a result, the Size of the data also changes. The total data length remains the same before and after the INSERT OVERWRITE operation, and no extra storage fees are incurred.

For information about how to insert data into dynamic partitions, see Insert or overwrite data in dynamic partitions (DYNAMIC PARTITION).

Limitations

Syntax

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

The following table describes the parameters.

The differences between ZORDER BY and SORT BY are as follows:

• ZORDER BY has two modes: local zorder and global zorder. The default mode is local zorder. The local mode only sorts data by z-order within individual files and does not redistribute the data globally. Therefore, if data is spread across multiple files, the data clustering may be low, which prevents effective Data Skipping. To address this issue, newer versions support global zorder. To use the global zorder mode, set the following parameter: SET odps.sql.default.zorder.type=global;.

  ZORDER BY has the following limitations:

  • For partitioned tables, you can perform a ZORDER BY sort on only one partition at a time.

  • The ZORDER BY column count must be between 2 and 4.

  • When the target table is a clustered table, the ZORDER BY clause is not supported.

  • ZORDER BY can be used with DISTRIBUTE BY, but cannot be used with ORDER BY, CLUSTER BY, or SORT BY.

  Note

  When you write data using the ZORDER BY clause, the operation consumes more resources and takes more time than not sorting.

• The SORT BY statement is used to specify how data is sorted within a single file. If you do not specify SORT BY, data within a single file is sorted by local zorder.

Examples: regular tables

• Example 1: Run the INSERT INTO command to append data to the non-partitioned table websites. The command is as follows:

  --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 keyword TABLE in `INSERT INTO TABLE ` is optional.
  INSERT INTO apps (id,app_name,url) VALUES 
  (1,'Aliyun','https://www.aliyun.com');
  --Copy data from the apps table and append it to the websites table.
  INSERT INTO websites (id,name,url) SELECT id,app_name,url
  FROM apps;
  --Run the SELECT statement to view the data in the websites table.
  SELECT * FROM websites;

  The following result is returned:

  -- The result.
  +------------+------------+------------+
  | id         | name       | url        |
  +------------+------------+------------+
  | 1          | Aliyun     | https://www.aliyun.com |
  +------------+------------+------------+

• Example 2: Run the INSERT INTO command to append data to the partitioned table sale_detail. The following is a sample command:

  -- 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 source table. This step is optional. If the partition does not exist, it is automatically created when you write data.
  ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china');
  
  -- Append data to the source table. The TABLE keyword after INSERT INTO and INSERT OVERWRITE is optional.
  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 for the current session only. Run a 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      |
  +------------+-------------+-------------+------------+------------+

• Example 3: Run the INSERT OVERWRITE command to overwrite data in the sale_detail_insert table. The following is a sample command:

  -- Create a target table named sale_detail_insert with the same schema as sale_detail.
  CREATE TABLE sale_detail_insert LIKE sale_detail;
  
  -- Add a partition to the target table. This step is optional. If the partition does not exist, it is automatically created when you write data.
  ALTER TABLE sale_detail_insert ADD PARTITION (sale_date='2013', region='china');
  
  -- Overwrite a static partition. For static partitions, partition columns are specified in the PARTITION() clause and must not be in the SELECT list. The columns in the SELECT list are mapped to the target table's columns by position.
  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;
  
  -- Enable a full table scan for the current session only. Run a SELECT statement to view 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      |
  +------------+-------------+-------------+------------+------------+

• Example 4: Execute the INSERT OVERWRITE command to overwrite data in the sale_detail_insert table and change the order of columns in the SELECT clause. The mapping between the source table and the target table is based on the order of columns in the SELECT clause, not on the column names. The command is as follows:

  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;                  

  The following result is returned:

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

  When the sale_detail_insert table was created, the column order was:

  +-------------------+--------------------+-------------------+
  | shop_name STREING | customer_id  STRING| total_price BIGINT| 
  +-------------------+--------------------+-------------------+

  The order of inserting data from sale_detail into sale_detail_insert is as follows:

  +---------------------+--------------------+-------------------+
  | customer_id  STRING | shop_name STREING  | total_price BIGINT| 
  +---------------------+--------------------+-------------------+

  In this case, the data from sale_detail.customer_id is inserted into sale_detail_insert.shop_name, and the data from sale_detail.shop_name is inserted into sale_detail_insert.customer_id.

• Example 5: When you insert data into a partition, the partition columns cannot appear in the SELECT clause. The following statement returns an error because sale_date and region are partition columns, which are not allowed in the SELECT clause for a static partition. An example of an incorrect command is as follows:

  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;

• Example 6: The value of PARTITION can only be a constant, not an expression. An example of an incorrect command is as follows:

  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;

• Example 7: Run the INSERT OVERWRITE command to overwrite data in the mf_src and mf_zorder_src tables and sort the mf_zorder_src table in global zorder mode. The following is a sample command:

  -- Create the target table 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;
  
  -- The result is returned:
  +-----+-------+
  | key | value |
  +-----+-------+
  | 1   | 1     |
  | 3   | 3     |
  | 2   | 2     |
  +-----+-------+
  
  -- Create the target table mf_zorder_src with the same schema as mf_src.
  CREATE TABLE mf_zorder_src LIKE mf_src;
  
  -- Use the global z-order mode for sorting.
  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     |
  +-----+-------+

• Example 8: Run the INSERT OVERWRITE command to overwrite the data in the existing target table. The command is as follows:

  -- The 'target' table is an existing table.
  SET odps.sql.default.zorder.type=global;
  INSERT OVERWRITE TABLE target
  SELECT key, value FROM target 
  ZORDER BY key, value;

Examples: Delta Table

Example: Create the Delta Table mf_dt and execute the INSERT command to insert and overwrite data.

-- 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 where dd='01' and hh='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 in the target partition of the mf_dt table.                
SELECT * FROM mf_dt WHERE dd='01' AND hh='01';
-- The result is returned:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

-- Use 'INSERT INTO' to append data to the target partition of 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 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 |
+------------+------------+----+----+

-- Use 'INSERT OVERWRITE' to overwrite data in the target 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='01';
-- The result is returned:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

-- Use 'INSERT OVERWRITE' to write data to the partition where dd='01' and hh='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 result is returned:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 11         | 01 | 02 |
| 3          | 32         | 01 | 02 |
| 2          | 22         | 01 | 02 |
+------------+------------+----+----+

-- Enable a full table scan for the current session only. Run a SELECT statement to view data in the mf_dt table.
SET odps.sql.allow.fullscan=true;
SELECT * FROM mf_dt;
-- The 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 |
+------------+------------+----+----+

Best practices

Z-ordering is not suitable for all scenarios. You may need to experiment with your use case to determine if the storage and query performance benefits justify the extra computational cost of writing Z-ordered data. The following sections provide general recommendations.