INSERT

S’applique à : Databricks SQL Databricks Runtime

Insère de nouvelles lignes dans une table et tronque ou partitionne éventuellement celle-ci. Vous spécifiez les lignes insérées par des expressions de valeur ou le résultat d’une requête.

Databricks SQL prend en charge cette instruction uniquement pour les tables Delta Lake.

Syntaxe

INSERT { OVERWRITE | INTO } [ TABLE ] table_name
    [ PARTITION clause ]
    [ ( column_name [, ...] ) | BY NAME ]
    query

INSERT INTO [ TABLE ] table_name
    REPLACE WHERE predicate
    query

Notes

Lorsque vous INSERT INTO une table Delta, l’application et l’évolution du schéma sont prises en charge. Si le type de données d’une colonne ne peut pas être converti en toute sécurité vers le type de données d’une table Delta, une exception de runtime est levée. Si l'évolution du schéma est activée, de nouvelles colonnes peuvent exister en tant que dernières colonnes de votre schéma (ou colonnes imbriquées) pour que le schéma puisse évoluer.

Paramètres

• INTO ou OVERWRITE

  Si vous spécifiez OVERWRITE, ce qui suit s’applique :

  • À défaut de partition_spec, la table est tronquée avant l’insertion de la première ligne.
  • Sinon, toutes les partitions correspondant à partition_spec sont tronquées avant l’insertion de la première ligne.

  Si vous spécifiez INTO, toutes les lignes insérées s’ajoutent aux lignes existantes.

• table_name

  Identifie la table à insérer. Le nom ne doit pas inclure une spécification temporelle. Si la table est introuvable, Azure Databricks génère une erreur TABLE_OR_VIEW_NOT_FOUND.

  table_name ne doit pas être une table source contenant une clé étrangère.

• Clause PARTITION

  Paramètre facultatif qui spécifie une partition cible pour l’insertion. Vous pouvez également spécifier la partition partiellement.

  Lorsque vous spécifiez une partition column = value statique, cette colonne ne doit pas être répétée dans la liste d’insertion de colonne.

• ( column_name [, …] )

  Liste facultative de colonnes de la table. La commande d’insertion peut spécifier une colonne particulière de la table au maximum une fois.

  S’applique à : Databricks SQL Databricks Runtime 12.2 LTS et versions ultérieures

  • Si cette commande omet une colonne, Databricks SQL attribue la valeur par défaut correspondante à la place.
  • Si le schéma de table cible ne définit aucune valeur par défaut pour la colonne insérée, Databricks SQL attribue NULL pour une colonne pouvant accepter la valeur Null. Sinon, Databricks SQL génère une erreur.

  Si aucune liste de colonnes n’est équivalente à la spécification de toutes les colonnes, sauf pour les colonnes auxquelles des valeurs ont été attribuées dans la clause PARTITION, dans l’ordre défini dans la table.

• BY NAME

  S’applique à : Databricks SQL Databricks Runtime 13.3 LTS et versions ultérieures

  Lorsque cette clause est utilisée à la place d’une liste de colonnes explicite, la commande utilise les noms de colonnes de query exposés pour produire la liste de colonnes dans l’ordre de query. Comme pour une liste de colonnes explicite, chaque colonne doit exister dans la table cible et ne doit pas être dupliquée. Si une colonne présente dans table_name ne fait pas partie de la liste de colonnes implicite, la valeur DEFAULT est utilisée à la place.

  BY NAME correspond également aux attributs des structs par nom.

  Aucune colonne dans query ne peut correspondre à une colonne spécifiée dans la clause PARTITION ou à une colonne générée.

• REPLACE WHERE boolean_expression

  S’applique à : Databricks SQL Databricks Runtime 12.2 LTS et versions ultérieures

  Si table_name est une table Delta Lake, supprimez les lignes correspondantes à boolean_expression avant d’insérer les lignes correspondantes àboolean-expression spécifiées dans query. Les lignes dans query lesquelles ne correspondent boolean_expression pas sont ignorées.

  boolean_expression peut être toute expression produisant un résultat de type BOOLEAN.

  Consultez Remplacement sélectif arbitraire avec replaceWhere.

• query

  Requête qui produit les lignes à insérer.

  Vous devez faire correspondre le nombre de colonnes retournées par la requête avec la liste d’insertion de colonnes spécifiée ou implicite.

  Si un type de données ne peut pas être converti en toute sécurité en un type de données de colonne correspondant, une exception de runtime est levée.

  S’applique à : Databricks SQL Databricks Runtime 11.3 LTS et versions ultérieures

  • Si query se compose d’une clause VALUES, expression peut être DEFAULT.
  • Si query se compose d’une clause SELECT, named_expression peut être DEFAULT.
  • DEFAULT insère l’expression définie DEFAULT explicitement de la colonne correspondante dans table_name, ou NULL si aucune n’est définie.

  Si l'évolution du schéma est activée, de nouvelles colonnes peuvent exister en tant que dernières colonnes de votre schéma (ou colonnes imbriquées) pour que le schéma puisse évoluer.

Exemples

Dans cette section :

• INSERT INTO
• Insérer avec une liste de colonnes
• Insérer avec une spécification de partition et une liste de colonnes
• INSERT à l’aide de la clause BY NAME
• REPLACE WHERE
• INSERT OVERWRITE

INSERT INTO

INSERT à l’aide de VALUES

> CREATE TABLE students (name VARCHAR(64), address VARCHAR(64) DEFAULT 'unknown', student_id INT)
  PARTITIONED BY (student_id);

-- Single row insert using a `VALUES` clause specifying all columns.
> INSERT INTO students VALUES
    ('Amy Smith', '123 Park Ave, San Jose', 111111);

-- Single row insert using an implicit default for address
> INSERT INTO students(name, student_id) VALUES('Grayson Miller', 222222);

-- Single row insert using an explicit DEFAULT keyword for address
> INSERT INTO students VALUES('Youna Kim', DEFAULT, 333333);

--  Multi-row insert using a `VALUES` clause
> INSERT INTO students VALUES
    ('Bob Brown', '456 Taylor St, Cupertino', 444444),
    ('Cathy Johnson', '789 Race Ave, Palo Alto', 555555);

-- Multi-row insert using a mix of DEFAULT and literals
> INSERT INTO students VALUES
    ('Gwyneth Zhao', '120 Main St, Rockport', 666666),
    ('Jackson Peterson', DEFAULT, 777777);

> SELECT * FROM students;
            name                  address student_id
 ---------------- ------------------------ ----------
        Amy Smith   123 Park Ave, San Jose     111111
   Grayson Miller                  unknown     222222
        Youna Kim                  unknown     333333
        Bob Brown 456 Taylor St, Cupertino     444444
    Cathy Johnson  789 Race Ave, Palo Alto     555555
     Gwyneth Zhao    120 Main St, Rockport     666666
 Jackson Peterson                  unknown     777777

Insérer à l’aide d’une sous-requête

-- Assuming the persons table has already been created and populated.
> SELECT * FROM persons;
          name                   address       ssn
 ------------- ------------------------- ---------
 Dora Williams 134 Forest Ave, Melo Park 123456789
   Eddie Davis   245 Market St, Milpitas 345678901

> INSERT INTO students PARTITION (student_id = 444444)
    SELECT name, address FROM persons WHERE name = "Dora Williams";

> SELECT * FROM students;
          name                   address student_id
 ------------- ------------------------- ----------
     Amy Smith    123 Park Ave, San Jose     111111
     Bob Brown  456 Taylor St, Cupertino     222222
 Cathy Johnson   789 Race Ave, Palo Alto     333333
 Dora Williams 134 Forest Ave, Melo Park     444444

Insérer à l’aide d’une clause TABLE

-- Assuming the visiting_students table has already been created and populated.
> SELECT * FROM visiting_students;
          name               address student_id
 ------------- --------------------- ----------
 Fleur Laurent 345 Copper St, London     777777
 Gordon Martin  779 Lake Ave, Oxford     888888

> INSERT INTO students TABLE visiting_students;

> SELECT * FROM students;
          name                   address student_id
 ------------- ------------------------- ----------
     Amy Smith     123 Park Ave,San Jose     111111
     Bob Brown  456 Taylor St, Cupertino     222222
 Cathy Johnson   789 Race Ave, Palo Alto     333333
 Dora Williams 134 Forest Ave, Melo Park     444444
 Fleur Laurent     345 Copper St, London     777777
 Gordon Martin      779 Lake Ave, Oxford     888888

Insérer dans un répertoire

> CREATE TABLE students (name VARCHAR(64), address VARCHAR(64), student_id INT)
    PARTITIONED BY (student_id)
    LOCATION "/mnt/user1/students";

> INSERT INTO delta.`/mnt/user1/students` VALUES
    ('Amy Smith', '123 Park Ave, San Jose', 111111);
> SELECT * FROM students;
          name                   address student_id
 ------------- ------------------------- ----------
     Amy Smith    123 Park Ave, San Jose     111111

Insérer avec une liste de colonnes

> INSERT INTO students (address, name, student_id) VALUES
    ('Hangzhou, China', 'Kent Yao', 11215016);
> SELECT * FROM students WHERE name = 'Kent Yao';
      name                address student_id
 --------- ---------------------- ----------
 Kent Yao         Hangzhou, China   11215016

Insérer avec une spécification de partition et une liste de colonnes

> INSERT INTO students PARTITION (student_id = 11215017) (address, name) VALUES
    ('Hangzhou, China', 'Kent Yao Jr.');
> SELECT * FROM students WHERE student_id = 11215017;
         name                address student_id
 ------------ ---------------------- ----------
 Kent Yao Jr.        Hangzhou, China   11215017

INSERT à l’aide de la clause BY NAME

> CREATE TABLE target(n INT, text STRING, s STRUCT<a INT, b INT>);
> INSERT INTO target BY NAME SELECT named_struct('b', 2, 'a', 1) AS s, 0 AS n, 'data' AS text;
> SELECT * FROM target;
  0  data  {"a":1,"b":2}

> CREATE OR REPLACE TABLE target(n INT, arr ARRAY<STRUCT<a INT, b INT>>);
> INSERT INTO target BY NAME SELECT array(named_struct('b', 2, 'a', 1)) AS arr, 0 AS n;
> INSERT INTO target BY NAME SELECT array(named_struct('b', 2, 'a', 1)) AS arr;
> SELECT * FROM target;
  0     [{"a":1,"b":2}]
  NULL  [{"a":1,"b":2}]

> INSERT INTO target BY NAME SELECT array(named_struct('b', 2, 'a', 1)) AS arr, 0 AS badname;
Error

> INSERT INTO target BY NAME SELECT array(named_struct('b', 2, 'a', 1)) AS arr, 0 AS n, 1 AS n;
 Error: INSERT_COLUMN_ARITY_MISMATCH.TOO_MANY_DATA_COLUMNS

REPLACE WHERE

> CREATE TABLE sales(tx_date DATE, amount INTEGER);
> INSERT INTO sales VALUES
   (DATE'2022-10-01', 1234),
   (DATE'2022-10-02', 2345),
   (DATE'2022-10-03', 3456),
   (DATE'2022-11-01', 3214);

-- Replace any rows with a transaction date in October 2022.
> INSERT INTO sales REPLACE WHERE tx_date BETWEEN '2022-10-01' AND '2022-10-31'
   VALUES (DATE'2022-10-01', 1237),
          (DATE'2022-10-02', 2378),
          (DATE'2022-10-04', 2456),
          (DATE'2022-10-05', 6328);
> SELECT * FROM sales ORDER BY  tx_date;
 tx_date    amount
 ---------- ------
 2022-10-01   1237
 2022-10-02   2378
 2022-10-04   2456
 2022-10-05   6328
 2022-11-01   3214

INSERT OVERWRITE

Insérer à l’aide d’une clause VALUES

-- Assuming the students table has already been created and populated.
> SELECT * FROM students;
          name                   address student_id
 ------------- ------------------------- ----------
     Amy Smith    123 Park Ave, San Jose     111111
     Bob Brown  456 Taylor St, Cupertino     222222
 Cathy Johnson   789 Race Ave, Palo Alto     333333
 Dora Williams 134 Forest Ave, Melo Park     444444
 Fleur Laurent     345 Copper St, London     777777
 Gordon Martin      779 Lake Ave, Oxford     888888
   Helen Davis 469 Mission St, San Diego     999999
    Jason Wang     908 Bird St, Saratoga     121212

> INSERT OVERWRITE students VALUES
    ('Ashua Hill', '456 Erica Ct, Cupertino', 111111),
    ('Brian Reed', '723 Kern Ave, Palo Alto', 222222);

> SELECT * FROM students;
       name                 address student_id
 ---------- ----------------------- ----------
 Ashua Hill 456 Erica Ct, Cupertino     111111
 Brian Reed 723 Kern Ave, Palo Alto     222222

Insérer à l’aide d’une sous-requête

-- Assuming the persons table has already been created and populated.
> SELECT * FROM persons;
          name                   address       ssn
 ------------- ------------------------- ---------
 Dora Williams 134 Forest Ave, Melo Park 123456789
   Eddie Davis    245 Market St,Milpitas 345678901

> INSERT OVERWRITE students PARTITION (student_id = 222222)
    SELECT name, address FROM persons WHERE name = "Dora Williams";

> SELECT * FROM students;
          name                   address student_id
 ------------- ------------------------- ----------
    Ashua Hill   456 Erica Ct, Cupertino     111111
 Dora Williams 134 Forest Ave, Melo Park     222222

Insérer à l’aide d’une clause TABLE

-- Assuming the visiting_students table has already been created and populated.
> SELECT * FROM visiting_students;
          name               address student_id
 ------------- --------------------- ----------
 Fleur Laurent 345 Copper St, London     777777
 Gordon Martin  779 Lake Ave, Oxford     888888

> INSERT OVERWRITE students TABLE visiting_students;

> SELECT * FROM students;
          name               address student_id
 ------------- --------------------- ----------
 Fleur Laurent 345 Copper St, London     777777
 Gordon Martin  779 Lake Ave, Oxford     888888

Insérer remplacer un répertoire

> CREATE TABLE students (name VARCHAR(64), address VARCHAR(64), student_id INT)
    PARTITIONED BY (student_id)
    LOCATION "/mnt/user1/students";

> INSERT OVERWRITE delta.`/mnt/user1/students` VALUES
    ('Amy Smith', '123 Park Ave, San Jose', 111111);
> SELECT * FROM students;
          name                   address student_id
 ------------- ------------------------- ----------
     Amy Smith    123 Park Ave, San Jose     111111

Articles connexes

• COPY
• DELETE
• MERGE
• PARTITION
• query
• UPDATE