Pular para o conteúdo principal
Última atualização em

Colunas geradas pelo Delta Lake

info

Visualização

Esse recurso está em Prévia Pública.

Colunas geradas do Delta Lake compute e armazenam automaticamente valores de uma expressão definida pelo usuário sobre outras colunas na tabela. Quando o senhor escreve em uma tabela sem fornecer valores para colunas geradas, o Delta Lake as calcula automaticamente. Se o senhor fornecer valores, eles devem satisfazer (<value> <=> <generation expression>) IS TRUE ou a gravação falha. Consulte Restrições no Databricks.

Por exemplo, você pode compute uma coluna price_with_tax de base_price * 1.1 sem exigir gravações para especificar dados para price_with_tax.

Assim como as colunas regulares, as colunas geradas são armazenadas fisicamente nos arquivos de dados subjacentes da tabela.

nota

Habilitar colunas geradas atualiza o protocolo do gravador da tabela. Isso pode afetar a compatibilidade com clientes externos do Delta Lake. Consulte a compatibilidade de recursos e protocolos do Delta Lake.

Criar uma tabela com colunas geradas​

O exemplo a seguir mostra a criação de 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
)

Expressões suportadas​

Uma expressão de geração pode usar qualquer função SQL determinística que sempre retorna o mesmo resultado para as mesmas entradas. Por exemplo:

  • Aritmética: base_price * 1.1
  • Funções de strings: CONCAT(first_name, ' ', last_name), SUBSTRING(col, 1, 3)
  • Funções de data: CAST(birthDate AS DATE), YEAR(eventTime)

Os seguintes tipos de função não são suportados:

  • Funções definidas pelo usuário
  • Funções de agregação
  • Funções de janela
  • Funções que retornam várias linhas

Geração de filtro de partição​

nota

O Databricks recomenda o clustering líquido para todas as novas tabelas Delta Lake. Consulte Usar clustering líquido para tabelas.

Ao particionar uma tabela usando uma coluna gerada e consultar na coluna base, o Delta Lake deriva automaticamente filtros de partição, se possível. Não é necessário filtrar explicitamente na coluna de partição gerada. O Delta Lake infere o intervalo da partição do valor da coluna base.

O Photon é necessário no Databricks Runtime 10.4 LTS e abaixo. O Photon não é necessário no Databricks Runtime 11.3 LTS e acima.

A geração de filtro de partição é compatível com as seguintes expressões:

  • 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 LTSe acima, você também pode usar o seguinte padrão: yyyy-MM-dd.

Exemplo: partição única​

Por exemplo, dada a seguinte tabela:

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)

Se você então executar a seguinte consulta:

SQL
SELECT * FROM events
WHERE eventTime >= "2020-10-01 00:00:00" AND eventTime <= "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 que um filtro de partição não seja especificado.

Use uma cláusula EXPLAIN e verifique o plano fornecido para ver se o Delta Lake gera automaticamente quaisquer filtros de partição.

Exemplo: várias partições​

Por exemplo, dada a seguinte tabela:

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)

Se você então executar a seguinte consulta:

SQL
SELECT * FROM events
WHERE eventTime >= "2020-10-01 00:00:00" AND eventTime <= "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 que um filtro de partição não seja especificado.

Use uma cláusula EXPLAIN e verifique o plano fornecido para ver se o Delta Lake gera automaticamente quaisquer filtros de partição.

Colunas de identidade​

importante

Declarar uma coluna de identidade em uma tabela Delta Lake desativa transações concorrentes. Use colunas de identidade apenas em casos de uso em que gravações concorrentes na tabela de destino não sejam necessárias. Consulte Limitações da coluna de identidade.

As colunas de identidade do 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 de criação de tabela.

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

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

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

Opcionalmente, é possível especificar o seguinte:

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

O valor padrão tanto para o valor inicial quanto para o tamanho do passo é 1. Não é possível especificar um tamanho do passo de 0.

Os valores atribuídos pelas colunas de identidade são exclusivos e incrementados na direção do passo especificado e em múltiplos do tamanho do passo especificado, mas não há garantia de serem contíguos. Por exemplo, com um valor inicial de 0 e um tamanho de passo 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 ser GENERATED ALWAYS AS IDENTITY para substituir a capacidade de definir valores manualmente.

As colunas de identidade suportam apenas o tipo BIGINT e as operações falham se o valor atribuído exceder o intervalo suportado por BIGINT.

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

CTAS e Colunas de Identidade​

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

Para criar uma nova tabela com uma coluna de identidade e preenchê-la com dados existentes, proceda da seguinte forma:

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

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

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;

Limitações de coluna de identidade​

Aplicam-se as seguintes limitações ao trabalhar com colunas de identidade:

  • Transações concorrentes não são aceitas 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 uma coluna de identidade.
  • Não é possível atualizar o valor de uma coluna de identidade para um registro existente.
nota

Para alterar o valor de IDENTITY para um registro existente, deve-se excluir o registro e INSERT -lo como um novo registro.

Colunas geradas e máscaras de coluna​

Uma coluna gerada não pode referenciar uma coluna com uma máscara de coluna aplicada, pois o valor gerado revelaria os dados subjacentes que a máscara protege. Isto gera um erro e a query falha. Consulte Filtros de linhas e máscaras de colunas.

Aqui estão exemplos dos erros:

  • Não é possível criar uma coluna gerada cuja expressão faça referência a uma coluna mascarada. Gera COLUMN_MASKS_GENERATED_COLUMN_UNSUPPORTED.

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

  • Não é possível aplicar uma máscara de coluna a uma coluna que já é referenciada por uma coluna gerada. Gera 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;

  • As leituras de uma tabela onde uma coluna gerada já faz referência a uma coluna mascarada também são bloqueadas. Gera COLUMN_MASKS_REFERENCED_BY_GENERATED_COLUMN.READ_BLOCKED.

Para resolver todos esses erros, é preciso redesenhar a tabela para que as colunas geradas e as colunas mascaradas não se sobreponham.

Nesta página