MaxCompute:Insérer ou écraser des données (INSERT INTO | INSERT OVERWRITE)

Vous pouvez exécuter les commandes présentées dans cette rubrique sur les plateformes suivantes :

• Client MaxCompute (odpscmd)

• Analyse SQL

• Utiliser Cloud Shell (odpscmd)

• DataWorks

• Développer et soumettre un script SQL

Prérequis

Pour effectuer des opérations INSERT INTO et INSERT OVERWRITE, vous devez disposer de l'autorisation Update sur la table cible et de l'autorisation Select sur la table source. Pour plus d'informations sur l'octroi des autorisations, consultez la rubrique Autorisations MaxCompute.

Présentation

Lors du traitement des données avec MaxCompute SQL, vous pouvez utiliser l'instruction INSERT INTO ou INSERT OVERWRITE pour enregistrer le résultat d'une requête SELECT dans une table cible. Les différences sont les suivantes :

• INSERT INTO : ajoute des données à une table ou à une partition statique. Vous pouvez spécifier les valeurs de partition dans l'instruction INSERT pour insérer des données dans une partition spécifique. Pour insérer une petite quantité de données de test, vous pouvez utiliser cette instruction avec VALUES.

• INSERT OVERWRITE : efface les données existantes dans une table ou une partition statique avant d'insérer les nouvelles données.

  Remarque

  • La syntaxe INSERT de MaxCompute diffère de la syntaxe INSERT de MySQL ou Oracle. Vous pouvez omettre le mot-clé TABLE dans les instructions INSERT INTO et INSERT OVERWRITE.

  • Lorsque vous effectuez plusieurs fois une opération INSERT OVERWRITE sur la même partition, la valeur Size renvoyée par la commande DESC peut varier. En effet, lorsque vous SELECT des données depuis une partition puis utilisez INSERT OVERWRITE pour les réécrire dans la même partition, la logique de fractionnement des fichiers change. Par conséquent, la Size des données évolue également. La longueur totale des données reste identique avant et après l'opération INSERT OVERWRITE et aucun frais de stockage supplémentaire n'est engagé.

Pour savoir comment insérer des données dans des partitions dynamiques, consultez la rubrique Insérer ou écraser des données dans des partitions dynamiques (DYNAMIC PARTITION).

Limites

Syntaxe

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

Le tableau suivant décrit les paramètres.

Les différences entre ZORDER BY et SORT BY sont les suivantes :

• ZORDER BY dispose de deux modes : local zorder et global zorder. Le mode par défaut est local zorder. Le mode local trie les données par ordre Z uniquement au sein de fichiers individuels et ne redistribue pas les données globalement. Par conséquent, si les données sont réparties sur plusieurs fichiers, le regroupement des données peut être faible, ce qui empêche un Data Skipping efficace. Pour résoudre ce problème, les versions plus récentes prennent en charge global zorder. Pour utiliser le mode global zorder, définissez le paramètre suivant : SET odps.sql.default.zorder.type=global;.

  ZORDER BY présente les limites suivantes :

  • Pour les tables partitionnées, vous ne pouvez effectuer un tri ZORDER BY que sur une seule partition à la fois.

  • Le nombre de colonnes ZORDER BY doit être compris entre 2 et 4.

  • Lorsque la table cible est une table clusterisée, la clause ZORDER BY n'est pas prise en charge.

  • ZORDER BY peut être utilisé avec DISTRIBUTE BY, mais ne peut pas être utilisé avec ORDER BY, CLUSTER BY ni SORT BY.

  Remarque

  Lorsque vous écrivez des données en utilisant la clause ZORDER BY, l'opération consomme plus de ressources et prend plus de temps que sans tri.

• L'instruction SORT BY sert à spécifier comment les données sont triées au sein d'un fichier unique. Si vous ne spécifiez pas SORT BY, les données au sein d'un fichier unique sont triées par local zorder.

Exemples : tables classiques

• Exemple 1 : exécutez la commande INSERT INTO pour ajouter des données à la table non partitionnée websites. La commande est la suivante :

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

  Le résultat suivant est renvoyé :

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

• Exemple 2 : exécutez la commande INSERT INTO pour ajouter des données à la table partitionnée sale_detail. Voici un exemple de commande :

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

  Le résultat suivant est renvoyé :

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

• Exemple 3 : exécutez la commande INSERT OVERWRITE pour écraser les données de la table sale_detail_insert. Voici un exemple de commande :

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

  Le résultat suivant est renvoyé :

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

• Exemple 4 : exécutez la commande INSERT OVERWRITE pour écraser les données de la table sale_detail_insert et modifier l'ordre des colonnes dans la clause SELECT. Le mappage entre la table source et la table cible repose sur l'ordre des colonnes dans la clause SELECT, et non sur les noms de colonnes. La commande est la suivante :

  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;                  

  Le résultat suivant est renvoyé :

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

  Lors de la création de la table sale_detail_insert, l'ordre des colonnes était le suivant :

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

  L'ordre d'insertion des données de sale_detail dans sale_detail_insert est le suivant :

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

  Dans ce cas, les données de sale_detail.customer_id sont insérées dans sale_detail_insert.shop_name, et les données de sale_detail.shop_name sont insérées dans sale_detail_insert.customer_id.

• Exemple 5 : lors de l'insertion de données dans une partition, les colonnes de partition ne peuvent pas apparaître dans la clause SELECT. L'instruction suivante renvoie une erreur car sale_date et region sont des colonnes de partition, ce qui n'est pas autorisé dans la clause SELECT pour une partition statique. Voici un exemple de commande incorrecte :

  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;

• Exemple 6 : la valeur de PARTITION ne peut être qu'une constante, pas une expression. Voici un exemple de commande incorrecte :

  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;

• Exemple 7 : exécutez la commande INSERT OVERWRITE pour écraser les données des tables mf_src et mf_zorder_src, et triez la table mf_zorder_src en mode global zorder. Voici un exemple de commande :

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

  Le résultat suivant est renvoyé :

  +-----+-------+
  | key | value |
  +-----+-------+
  | 1   | 1     |
  | 2   | 2     |
  | 3   | 3     |
  +-----+-------+

• Exemple 8 : exécutez la commande INSERT OVERWRITE pour écraser les données de la table target existante. La commande est la suivante :

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

Exemples : Delta Table

Exemple : créez la Delta Table mf_dt et exécutez la commande INSERT pour insérer et écraser des données.

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

Bonnes pratiques

Le tri Z-order ne convient pas à tous les scénarios. Il peut être nécessaire de tester votre cas d'utilisation pour déterminer si les avantages en matière de stockage et de performances des requêtes justifient le coût de calcul supplémentaire lié à l'écriture de données triées par Z-order. Les sections suivantes fournissent des recommandations générales.