Colonnes générées par Delta Lake

Delta Lake prend en charge les colonnes générées. Ce sont des colonnes d’un type spécial dont les valeurs sont générées automatiquement sur la base d’une fonction définie par l’utilisateur à partir d’autres colonnes de la table Delta. Lorsque vous écrivez dans une table avec des colonnes générées et que vous ne fournissez pas explicitement de valeurs pour celles-ci, Delta Lake calcule automatiquement les valeurs. Par exemple, vous pouvez générer automatiquement une colonne de date (pour le partitionnement de la table par date) à partir de la colonne timestamp ; les écritures dans la table ne doivent spécifier que les données de la colonne timestamp. Toutefois, si vous fournissez explicitement des valeurs pour eux, ces valeurs doivent satisfaire la contrainte (<value> <=> <generation expression>) IS TRUE. Sinon, l’écriture échoue avec une erreur.

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
)

Python

DeltaTable.create(spark) \
  .tableName("default.people10m") \
  .addColumn("id", "INT") \
  .addColumn("firstName", "STRING") \
  .addColumn("middleName", "STRING") \
  .addColumn("lastName", "STRING", comment = "surname") \
  .addColumn("gender", "STRING") \
  .addColumn("birthDate", "TIMESTAMP") \
  .addColumn("dateOfBirth", DateType(), generatedAlwaysAs="CAST(birthDate AS DATE)") \
  .addColumn("ssn", "STRING") \
  .addColumn("salary", "INT") \
  .execute()

Scala

DeltaTable.create(spark)
  .tableName("default.people10m")
  .addColumn("id", "INT")
  .addColumn("firstName", "STRING")
  .addColumn("middleName", "STRING")
  .addColumn(
    DeltaTable.columnBuilder("lastName")
      .dataType("STRING")
      .comment("surname")
      .build())
  .addColumn("lastName", "STRING", comment = "surname")
  .addColumn("gender", "STRING")
  .addColumn("birthDate", "TIMESTAMP")
  .addColumn(
    DeltaTable.columnBuilder("dateOfBirth")
     .dataType(DateType)
     .generatedAlwaysAs("CAST(dateOfBirth AS DATE)")
     .build())
  .addColumn("ssn", "STRING")
  .addColumn("salary", "INT")
  .execute()

Les colonnes générées sont stockées comme s’il s’agissait de colonnes normales. Autrement dit, ils occupent le stockage.

Les restrictions suivantes s’appliquent aux colonnes générées :

• une expression de génération peut utiliser toutes les fonctions de SQL dans Spark qui retournent toujours le même résultat lorsqu’elles reçoivent les mêmes valeurs d’argument, à l’exception des types de fonctions suivants :
  • Fonctions définies par l’utilisateur.
  • Fonctions d’agrégation.
  • Fonctions Windows.
  • Les fonctions retournent plusieurs lignes.

Delta Lake peut générer des filtres de partition pour une requête à chaque définition d’une colonne de partition par l’une des 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 l’objet col est TIMESTAMP.
• Trois colonnes de partition définies par YEAR(col), MONTH(col), DAY(col), et le type de l’objet col est TIMESTAMP.
• Quatre colonnes de partition définies par YEAR(col), MONTH(col), DAY(col), HOUR(col), et le type de l’objet 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 pouvez uniquement utiliser les formats de date avec les modèles suivants : yyyy-MM et yyyy-MM-dd-HH.
  • Dans Databricks Runtime 10.4 LTS et versions ultérieures, vous pouvez également utiliser le modèle suivant : yyyy-MM-dd.

Si une colonne de partition est définie par l’une des expressions précédentes et qu’une requête filtre des données en utilisant la colonne de base sous-jacente d’une expression de génération, Delta Lake examine la relation entre la colonne de base et la colonne générée, et alimente les filtres de partition sur la base de la colonne de partition générée si possible. Par exemple, étant donné la table suivante :

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 requête suivante :

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

Delta Lake génère automatiquement un filtre de partition afin que la requête précédente lise uniquement les données dans la partition date=2020-10-01, même si un filtre de partition n’est pas spécifié.

Comme autre exemple, étant donné le tableau suivant :

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 requête suivante :

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

Delta Lake génère automatiquement un filtre de partition afin que la requête précédente lise uniquement les données dans la partition year=2020/month=10/day=01, même si un filtre de partition n’est pas spécifié.

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

Utiliser des colonnes d’identité dans Delta Lake

Les colonnes d’identité Delta Lake sont un type de colonne générée qui attribue des valeurs uniques pour 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 create 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)
 )

Python

from delta.tables import DeltaTable, IdentityGenerator
from pyspark.sql.types import LongType

DeltaTable.create()
  .tableName("table_name")
  .addColumn("id_col1", dataType=LongType(), generatedAlwaysAs=IdentityGenerator())
  .addColumn("id_col2", dataType=LongType(), generatedAlwaysAs=IdentityGenerator(start=-1, step=1))
  .addColumn("id_col3", dataType=LongType(), generatedByDefaultAs=IdentityGenerator())
  .addColumn("id_col4", dataType=LongType(), generatedByDefaultAs=IdentityGenerator(start=-1, step=1))
  .execute()

Scala

import io.delta.tables.DeltaTable
import org.apache.spark.sql.types.LongType

DeltaTable.create(spark)
  .tableName("table_name")
  .addColumn(
    DeltaTable.columnBuilder(spark, "id_col1")
      .dataType(LongType)
      .generatedAlwaysAsIdentity().build())
  .addColumn(
    DeltaTable.columnBuilder(spark, "id_col2")
      .dataType(LongType)
      .generatedAlwaysAsIdentity(start = -1L, step = 1L).build())
  .addColumn(
    DeltaTable.columnBuilder(spark, "id_col3")
      .dataType(LongType)
      .generatedByDefaultAsIdentity().build())
  .addColumn(
    DeltaTable.columnBuilder(spark, "id_col4")
      .dataType(LongType)
      .generatedByDefaultAsIdentity(start = -1L, step = 1L).build())
  .execute()

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

Si vous le souhaitez, vous pouvez spécifier les éléments suivants :

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

La valeur de départ et la taille d’étape par défaut sont 1. Vous ne pouvez pas spécifier une taille d’étape de 0.

Les valeurs attribuées par les colonnes d’identité sont uniques et incrémentées dans le sens de l’étape spécifiée et dans des multiples de la taille d’étape spécifiée, mais ne sont pas garanties d’être contiguës. Par exemple, avec une valeur de départ de 0 et une taille d’étape 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 pour qu’elle soit définie sur GENERATED ALWAYS AS IDENTITY afin qu’il ne soit pas possible de définir manuellement des valeurs.

Les colonnes d’identité prennent uniquement en charge 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 de colonne d’identité avec des données, consultez ALTER TABLE ... Clause COLUMN.

CREATE TABLE AS SELECT (CTAS) et colonnes d’identité

Vous ne pouvez pas définir de schéma, de contraintes sur les colonnes d’identité ou d’autres spécifications de table lorsque vous utilisez une instruction (CTAS) CREATE TABLE table_name AS SELECT.

Pour créer une table à l’aide d’une colonne d’identité et la remplir de données existantes, procédez comme suit :

1. Créez une table en suivant le bon schéma, y compris la définition de colonne d’identité et d’autres propriétés de la table.
2. Exécutez une opération INSERT.

L’exemple suivant se réfère au 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.

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
SELECT id, event_date, some_value FROM old_table;

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

Limitations des colonnes d’identité

Lors de l’utilisation des colonnes d’identité, les limitations sont les suivantes :

• 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 modifier une colonne d’identité à l’aide des instructions ADD, REPLACE ou CHANGE.
• Vous ne pouvez pas mettre à jour la valeur d’une colonne d’identité pour un enregistrement existant.