Compartilhar via

Colunas geradas pelo Delta Lake

  • Artigo

Importante

Esse recurso está em uma versão prévia.

O Delta Lake dá suporte a colunas geradas, que são um tipo especial de coluna cujos valores são gerados automaticamente com base em uma função especificada pelo usuário em relação a outras colunas na tabela Delta. Quando você grava em uma tabela com colunas geradas e não fornece explicitamente valores para elas, o Delta Lake computa automaticamente os valores. Por exemplo, você pode gerar automaticamente uma coluna de data (para particionar a tabela por data) da coluna Carimbo de data/hora; todas as gravações na tabela precisam especificar apenas os dados para a coluna Carimbo de data/hora. No entanto, se você fornecer valores explicitamente para eles, os valores precisarão atender à restrição (<value> <=> <generation expression>) IS TRUE ou a gravação falhará com um erro.

Importante

As tabelas criadas com colunas geradas têm uma versão de protocolo de gravador de tabela superior à padrão. Confira Como o Azure Databricks gerencia a compatibilidade de recursos do Delta Lake? para entender o controle de versão do protocolo de tabela e o que significa ter uma versão superior de uma versão do protocolo de tabela.

Criar uma tabela com as colunas geradas

O exemplo abaixo mostra como criar uma tabela com colunas geradas:

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()

As colunas geradas são armazenadas como se fossem colunas normais. Ou seja, elas ocupam o armazenamento.

As seguintes restrições se aplicam às colunas geradas:

  • Uma expressão de geração pode usar qualquer função SQL no Spark que sempre retorna o mesmo resultado quando os mesmos valores de argumento são fornecidos, exceto os seguintes tipos de funções:
    • Funções definidas pelo usuário.
    • Funções de agregação.
    • Funções de janela.
    • Funções que retornam várias linhas.

O Delta Lake pode gerar filtros de partição para uma consulta sempre que uma coluna de partição é definida por uma das seguintes expressões:

Observação

O Photon é necessário no Databricks Runtime 10.4 LTS e versões anteriores. O Photon não é necessário no Databricks Runtime 11.3 LTS e versões superiores.

  • CAST(col AS DATE) e o tipo de col é TIMESTAMP.
  • YEAR(col) e o tipo de col é TIMESTAMP.
  • Duas colunas de partição definidas por YEAR(col), MONTH(col) e o tipo de col é TIMESTAMP.
  • Três colunas de partição definidas por YEAR(col), MONTH(col), DAY(col) e o tipo de col é TIMESTAMP.
  • Quatro colunas de partição definidas por YEAR(col), MONTH(col), DAY(col), HOUR(col) e o tipo de col é TIMESTAMP.
  • SUBSTRING(col, pos, len) e o tipo de col é STRING
  • DATE_FORMAT(col, format) e o tipo de col é TIMESTAMP.
    • Você só pode usar formatos de data com os seguintes padrões: yyyy-MM e yyyy-MM-dd-HH.
    • No Databricks Runtime 10.4 LTS e posterior, você também pode usar o seguinte padrão: yyyy-MM-dd.

Se uma coluna de partição for definida por uma das expressões anteriores e uma consulta filtrar dados usando a coluna base subjacente de uma expressão de geração, o Delta Lake examinará a relação entre a coluna base e a coluna gerada e preencherá os filtros de partição com base na coluna de partição gerada, se possível. Por exemplo, considerando a seguinte tabela:

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

Se você executar a seguinte consulta:

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

O Delta Lake gera automaticamente um filtro de partição para que a consulta anterior leia apenas os dados na partição date=2020-10-01 mesmo se um filtro de partição não for especificado.

Como outro exemplo, considerando a seguinte tabela:

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)

Se você executar a seguinte consulta:

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

O Delta Lake gera automaticamente um filtro de partição para que a consulta anterior leia apenas os dados na partição year=2020/month=10/day=01 mesmo se um filtro de partição não for especificado.

Você pode usar uma cláusula EXPLAIN e verificar o plano fornecido para ver se o Delta Lake gera automaticamente filtros de partição.

Usar colunas de identidade no Delta Lake

Importante

Declarar uma coluna de identidade em uma tabela Delta desabilita transações simultâneas. Use apenas colunas de identidade em casos de uso em que as gravações simultâneas na tabela de destino não são necessárias.

As colunas de identidade Delta Lake são um tipo de coluna gerada que atribui valores exclusivos para cada registro inserido em uma tabela. O exemplo a seguir mostra a sintaxe básica para declarar uma coluna de identidade durante uma instrução 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()

Observação

As APIs do Scala e do Python para colunas de identidade estão disponíveis no Databricks Runtime 16.0 e superior.

Para ver todas as opções de sintaxe SQL para criar tabelas com colunas de identidade, consulte CREATE TABLE [USING].

Opcionalmente, você pode especificar o seguinte:

  • Um valor inicial.
  • Um tamanho de etapa, que pode ser positivo ou negativo.

O valor inicial e o tamanho da etapa são padronizados para 1. Não é possível especificar um tamanho de etapa de 0.

Os valores atribuídos por colunas de identidade são exclusivos e incrementam na direção da etapa especificada e em múltiplos do tamanho da etapa especificada, mas não têm garantia de serem contíguos. Por exemplo, com um valor inicial de 0 e um tamanho de etapa de 2, todos os valores são números pares positivos, mas alguns números pares podem ser ignorados.

Ao usar a cláusula GENERATED BY DEFAULT AS IDENTITY, as operações de inserção podem especificar valores para a coluna de identidade. Modifique a cláusula para GENERATED ALWAYS AS IDENTITY para substituir a capacidade de definir valores manualmente.

As colunas de identidade só dão suporte ao tipo BIGINT e as operações falharão se o valor atribuído exceder o intervalo compatível com BIGINT.

Para saber mais sobre como sincronizar valores de coluna de identidade com dados, consulte ALTER TABLE ... Cláusula COLUMN.

Colunas de identidade e CTAS

Não é possível definir esquema, restrições de coluna de identidade ou qualquer outra especificação de tabela ao usar uma instrução CREATE TABLE table_name AS SELECT (CTAS).

Para criar uma tabela com uma coluna de identidade e preenchê-la com dados existentes, faça o seguinte:

  1. Crie uma tabela com o esquema correto, incluindo a definição de coluna de identidade e outras propriedades da tabela.
  2. Execute uma operação INSERT.

O exemplo a seguir usa a palavra-chave DEFAULT para definir a coluna de identidade. Quando os dados inseridos na tabela incluírem valores válidos para a coluna de identidade, esses valores são usados.

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;

Limitações da coluna de identidade

Existem as seguintes limitações ao trabalhar com colunas de identidade:

  • Não há suporte para transações simultâneas em tabelas com colunas de identidade habilitadas.
  • Não é possível particionar uma tabela por uma coluna de identidade.
  • Não é possível usar ALTER TABLE para ADD, REPLACE ou CHANGE como coluna de identidade.
  • Não é possível atualizar o valor de uma coluna de identidade para um registro existente.

Observação

Para alterar o valor do campo IDENTITY de um registro existente, você deve excluir o registro e INSERT como um novo registro.