Aller au contenu principal
Dernière mise à jour le

Colonnes générées par Delta Lake

info

Aperçu

Cette fonctionnalité est en aperçu public.

Les colonnes générées par Delta Lake calculent et stockent automatiquement les valeurs d'une expression définie par l'utilisateur sur d'autres colonnes de la table. Lorsque vous écrivez dans une table sans fournir de valeurs pour les colonnes générées, Delta Lake les calcule automatiquement. Si vous fournissez des valeurs, elles doivent satisfaire à (<value> <=> <generation expression>) IS TRUE ou l'écriture échoue. Voir Contraintes sur Databricks.

Par exemple, vous pouvez compute une colonne price_with_tax à partir de base_price * 1.1 sans avoir à spécifier de données pour price_with_tax par des écritures.

Comme les colonnes régulières, les colonnes générées sont physiquement stockées dans les fichiers de données sous-jacents de la table.

remarque

L'activation des colonnes générées met à niveau le protocole d'écriture de table. Cela pourrait affecter la compatibilité avec les clients Delta Lake externes. Voir Compatibilité des fonctionnalités et protocoles de Delta Lake.

Créer une table avec des colonnes générées​

L'exemple suivant montre comment créer une table avec des colonnes générées :

SQL
CREATE TABLE default.people10m (
  id INT,
  firstName STRING,
  middleName STRING,
  lastName STRING,
  gender STRING,
  birthDate TIMESTAMP,
  dateOfBirth DATE GENERATED ALWAYS AS (CAST(birthDate AS DATE)),
  ssn STRING,
  salary INT
)

Expressions prises en charge​

Une expression de génération peut utiliser toute fonction SQL déterministe qui renvoie toujours le même résultat pour les mêmes entrées. Par exemple :

  • Arithmétique : base_price * 1.1
  • Fonctions de chaîne : CONCAT(first_name, ' ', last_name), SUBSTRING(col, 1, 3)
  • Fonctions de date : CAST(birthDate AS DATE), YEAR(eventTime)

Les types de fonctions suivants ne sont pas pris en charge :

  • Fonctions définies par l'utilisateur
  • Fonctions d'agrégation
  • Fonctions de fenêtre
  • Fonctions renvoyant plusieurs lignes

Génération de filtres de partition​

remarque

Databricks recommande le clustering liquide pour toutes les nouvelles tables Delta Lake. Voir Utiliser le clustering liquide pour les tables.

Lorsque vous partitionnez une table à l'aide d'une colonne générée et que vous interrogez la colonne de base, Delta Lake déduit automatiquement les filtres de partition si possible. Vous n’êtes pas obligé de filtrer explicitement sur la colonne de partition générée. Delta Lake déduit la plage de partitions de la valeur de la colonne de base.

Photon est requis dans Databricks Runtime 10.4 LTS et versions antérieures. Photon n'est pas requis dans Databricks Runtime 11.3 LTS et versions ultérieures.

La génération de filtre de partition est prise en charge pour les expressions suivantes :

  • CAST(col AS DATE) et le type de col est TIMESTAMP.
  • YEAR(col) et le type de col est TIMESTAMP.
  • Deux colonnes de partition définies par YEAR(col), MONTH(col) et le type de col est TIMESTAMP.
  • Trois colonnes de partition définies par YEAR(col), MONTH(col), DAY(col) et le type de col est TIMESTAMP.
  • Quatre colonnes de partition définies par YEAR(col), MONTH(col), DAY(col), HOUR(col) et le type de col est TIMESTAMP.
  • SUBSTRING(col, pos, len) et le type de col est STRING
  • DATE_FORMAT(col, format) et le type de col est TIMESTAMP.
    • Vous ne pouvez utiliser que les formats de date avec les modèles suivants : yyyy-MM et yyyy-MM-dd-HH.
    • Dans Databricks Runtime 10.4 LTS et versions supérieures, vous pouvez également utiliser le modèle suivant : yyyy-MM-dd.

Exemple : partition unique​

Par exemple, le tableau suivant :

SQL
CREATE TABLE events(
eventId BIGINT,
data STRING,
eventType STRING,
eventTime TIMESTAMP,
eventDate date GENERATED ALWAYS AS (CAST(eventTime AS DATE))
)
PARTITIONED BY (eventType, eventDate)

Si vous exécutez ensuite la query suivante :

SQL
SELECT * FROM events
WHERE eventTime >= "2020-10-01 00:00:00" AND eventTime <= "2020-10-01 12:00:00"

Delta Lake génère automatiquement un filtre de partition afin que la query précédente ne lise les données de la partition date=2020-10-01 que même si aucun filtre de partition n'est spécifié.

Utilisez une clause EXPLAIN et vérifiez le plan fourni pour voir si Delta Lake génère automatiquement des filtres de partition.

Exemple : plusieurs partitions​

Par exemple, le tableau suivant :

SQL
CREATE TABLE events(
eventId BIGINT,
data STRING,
eventType STRING,
eventTime TIMESTAMP,
year INT GENERATED ALWAYS AS (YEAR(eventTime)),
month INT GENERATED ALWAYS AS (MONTH(eventTime)),
day INT GENERATED ALWAYS AS (DAY(eventTime))
)
PARTITIONED BY (eventType, year, month, day)

Si vous exécutez ensuite la query suivante :

SQL
SELECT * FROM events
WHERE eventTime >= "2020-10-01 00:00:00" AND eventTime <= "2020-10-01 12:00:00"

Delta Lake génère automatiquement un filtre de partition afin que la query précédente ne lise les données de la partition year=2020/month=10/day=01 que même si aucun filtre de partition n'est spécifié.

Utilisez une clause EXPLAIN et vérifiez le plan fourni pour voir si Delta Lake génère automatiquement des filtres de partition.

Colonnes d'identité​

important

La déclaration d’une colonne d’identité sur une table Delta Lake désactive les transactions concurrentes. N'utilisez les colonnes d'identité que dans les cas où des écritures simultanées vers la table cible ne sont pas requises. Consultez les Limitations des colonnes d'identité.

Les colonnes d’identité Delta Lake sont un type de colonne générée qui attribue des valeurs uniques à chaque enregistrement inséré dans une table. L’exemple suivant montre la syntaxe de base pour déclarer une colonne d’identité lors d’une instruction de création de table :

SQL
CREATE TABLE table_name (
  id_col1 BIGINT GENERATED ALWAYS AS IDENTITY,
  id_col2 BIGINT GENERATED ALWAYS AS IDENTITY (START WITH -1 INCREMENT BY 1),
  id_col3 BIGINT GENERATED BY DEFAULT AS IDENTITY,
  id_col4 BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH -1 INCREMENT BY 1)
 )
remarque

Les APIs Scala et Python pour les colonnes d'identité sont disponibles dans Databricks Runtime 16.0 et versions ultérieures.

Pour afficher toutes les options de syntaxe SQL pour la création de tables avec des colonnes d'identité, consultez CREATE TABLE [USING].

Vous pouvez éventuellement spécifier ce qui suit :

  • Une valeur de départ.
  • Une taille de pas, qui peut être positive ou négative.

La valeur de départ et la taille du pas sont default à 1. Vous ne pouvez pas spécifier une taille de pas de 0.

Les valeurs attribuées par les colonnes d'identité sont uniques et s'incrémentent dans la direction du pas spécifié et par multiples de la taille du pas spécifié, mais ne sont pas garanties d'être contiguës. Par exemple, avec une valeur de départ de 0 et un pas de 2, toutes les valeurs sont des nombres pairs positifs, mais certains nombres pairs peuvent être ignorés.

Lorsque vous utilisez la clause GENERATED BY DEFAULT AS IDENTITY, les Opérations d'insertion peuvent spécifier des valeurs pour la colonne d'identité. Modifiez la clause en GENERATED ALWAYS AS IDENTITY pour annuler la possibilité de définir manuellement les valeurs.

Les colonnes d'identité ne prennent en charge que le type BIGINT, et les opérations échouent si la valeur attribuée dépasse la plage prise en charge par BIGINT.

Pour en savoir plus sur la synchronisation des valeurs des colonnes d’identité avec les données, consultez la clause ALTER TABLE ... COLUMN.

CTAS et colonnes d'identité​

Vous ne pouvez pas définir de schéma, de contraintes de colonne d'identité ou toute autre spécification de table lors de l'utilisation d'une instruction CREATE TABLE table_name AS SELECT (CTAS).

Pour créer une nouvelle table avec une colonne d'identité et la remplir avec des données existantes, procédez comme suit :

  1. Créez une table avec le schéma correct, y compris la définition de la colonne d'identité et les autres propriétés de la table.
  2. Exécutez une opération INSERT.

L'exemple suivant utilise le mot-clé DEFAULT pour définir la colonne d'identité. Si les données insérées dans la table incluent des valeurs valides pour la colonne d'identité, ces valeurs sont utilisées.

SQL
CREATE OR REPLACE TABLE new_table (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 5),
  event_date DATE,
  some_value BIGINT
);

-- Inserts records including existing IDs
INSERT INTO new_table (id, event_date, some_value)
SELECT id, event_date, some_value FROM old_table;

-- Insert records and generate new IDs
INSERT INTO new_table (event_date, some_value)
SELECT event_date, some_value FROM new_records;

Limitations des colonnes d'identité​

Les limitations suivantes existent lorsque vous travaillez avec des colonnes d’identité :

  • Les transactions simultanées ne sont pas prises en charge sur les tables avec des colonnes d'identité activées.
  • Vous ne pouvez pas partitionner une table par une colonne d’identité.
  • Vous ne pouvez pas utiliser ALTER TABLE pour ADD, REPLACE ou CHANGE une colonne d'identité.
  • Vous ne pouvez pas mettre à jour la valeur d'une colonne d'identité pour un enregistrement existant.
remarque

Pour modifier la valeur IDENTITY d'un enregistrement existant, vous devez supprimer l'enregistrement et le INSERT en tant que nouvel enregistrement.

Colonnes générées et masques de colonne​

Une colonne générée ne peut pas référencer une colonne à laquelle un masque de colonne est appliqué, car la valeur générée révélerait les données sous-jacentes que le masque protège. Cela génère une erreur et la query échoue. Voir Filtres de lignes et masques de colonne.

Voici des exemples d'erreurs :

  • Vous ne pouvez pas créer de colonne générée dont l’expression référence une colonne masquée. Lève COLUMN_MASKS_GENERATED_COLUMN_UNSUPPORTED.

    SQL
    CREATE TABLE tbl (
      a INT MASK masking_function,
      generated_col INT GENERATED ALWAYS AS (a + 1)
    ) USING DELTA;

  • Vous ne pouvez pas appliquer de masque de colonne à une colonne qu'une colonne générée référence déjà. Déclenche COLUMN_MASKS_REFERENCED_BY_GENERATED_COLUMN.ADD_MASK.

    SQL
    CREATE TABLE tbl (
      a INT,
      generated_col INT GENERATED ALWAYS AS (a + 1)
    ) USING DELTA;

    ALTER TABLE tbl ALTER COLUMN a SET MASK masking_function;

  • Les lectures à partir d'une table où une colonne générée référence déjà une colonne masquée sont également bloquées. Lève COLUMN_MASKS_REFERENCED_BY_GENERATED_COLUMN.READ_BLOCKED.

Pour résoudre toutes ces erreurs, vous devez reconcevoir la table afin que les colonnes générées et les colonnes masquées ne se chevauchent pas.

Sur cette page