Atualizar esquema da tabela

Tabelas suportam evolução do esquema, permitindo modificações na estrutura da tabela à medida que os requisitos de dados mudam. Os seguintes tipos de alterações são suportados:

• Adicionando novas colunas em posições arbitrárias
• Reordenando colunas existentes
• Renomeando colunas existentes
• Ampliação de tipo de colunas existentes, consulte Ampliar tipos com evolução automática do esquema

Faça estas alterações explicitamente usando DDL ou implicitamente usando DML.

importante

Atualizações de esquema conflitam com todas as operações de gravação concorrentes. A Databricks recomenda a coordenação de alterações de esquema para evitar conflitos de gravação.

Ao atualizar o esquema de uma tabela, as transmissões que leem dessa tabela são interrompidas. Para continuar o processamento, reinicie a transmissão utilizando os métodos descritos em Considerações de produção para transmissão estructurada.

Atualizar explicitamente o esquema para adicionar colunas

SQL

ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment] [FIRST|AFTER colA_name], ...)

Por padrão, a anulabilidade é true.

Para adicionar uma coluna a um campo aninhado, utilize:

SQL

ALTER TABLE table_name ADD COLUMNS (col_name.nested_col_name data_type [COMMENT col_comment] [FIRST|AFTER colA_name], ...)

Por exemplo, se o esquema antes de executar o ALTER TABLE boxes ADD COLUMNS (colB.nested STRING AFTER field1) for:

- root
| - colA
| - colB
| +-field1
| +-field2

o esquema depois é:

- root
| - colA
| - colB
| +-field1
| +-nested
| +-field2

nota

A adição de colunas aninhadas é compatível apenas para estruturas. Matrizes e mapas não são compatíveis.

Atualizar explicitamente o esquema para alterar o comentário ou a ordenação da coluna

SQL

ALTER TABLE table_name ALTER [COLUMN] col_name (COMMENT col_comment | FIRST | AFTER colA_name)

Para alterar uma coluna em um campo aninhado, use:

SQL

ALTER TABLE table_name ALTER [COLUMN] col_name.nested_col_name (COMMENT col_comment | FIRST | AFTER colA_name)

Por exemplo, se o esquema antes de executar o ALTER TABLE boxes ALTER COLUMN colB.field2 FIRST for:

- root
| - colA
| - colB
| +-field1
| +-field2

o esquema depois é:

- root
| - colA
| - colB
| +-field2
| +-field1

Atualizar explicitamente o esquema para substituir colunas

SQL

ALTER TABLE table_name REPLACE COLUMNS (col_name1 col_type1 [COMMENT col_comment1], ...)

Por exemplo, ao executar o seguinte DDL:

SQL

ALTER TABLE boxes REPLACE COLUMNS (colC STRING, colB STRUCT<field2:STRING, nested:STRING, field1:STRING>, colA STRING)

se o esquema anterior for:

- root
| - colA
| - colB
| +-field1
| +-field2

o esquema depois é:

- root
| - colC
| - colB
| +-field2
| +-nested
| +-field1
| - colA

Atualizar explicitamente o esquema para renomear colunas

nota

Este recurso está disponível no Databricks Runtime 10.4 LTS e acima.

Para renomear colunas sem alterar os dados existentes das colunas, é necessário ativar o mapeamento de colunas para a tabela. Consulte Renomear e eliminar colunas com o mapeamento de colunas do Delta Lake.

Para renomear uma coluna:

SQL

ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name

Para renomear um campo aninhado:

SQL

ALTER TABLE table_name RENAME COLUMN col_name.old_nested_field TO new_nested_field

Por exemplo, quando você executa o seguinte comando:

SQL

ALTER TABLE boxes RENAME COLUMN colB.field1 TO field001

Se o esquema anterior for:

- root
| - colA
| - colB
| +-field1
| +-field2

Então o esquema depois é:

- root
| - colA
| - colB
| +-field001
| +-field2

Consulte Renomear e eliminar colunas com o mapeamento de colunas do Delta Lake.

Atualizar explicitamente o esquema para soltar colunas

nota

Este recurso está disponível no Databricks Runtime 11.3 LTS e acima.

Para eliminar colunas como uma operação que afeta apenas os metadados sem reescrever arquivos de dados, é necessário ativar o mapeamento de colunas para a tabela. Consulte Renomear e eliminar colunas com o mapeamento de colunas do Delta Lake.

importante

Soltar uma coluna de metadados não exclui os dados subjacentes da coluna em arquivos. Para limpar os dados da coluna descartada, você pode usar REORG TABLE para regravar arquivos. Em seguida, você pode usar o VACUUM para excluir fisicamente os arquivos que contêm os dados da coluna descartada.

Para soltar uma coluna:

SQL

ALTER TABLE table_name DROP COLUMN col_name

Para soltar várias colunas:

SQL

ALTER TABLE table_name DROP COLUMNS (col_name_1, col_name_2)

Atualizar explicitamente o esquema para alterar o tipo ou o nome da coluna

Você pode modificar o tipo ou nome de uma coluna ou eliminar uma coluna reescrevendo a tabela. Para fazer isto, utilize a opção overwriteSchema.

O exemplo a seguir mostra a alteração de um tipo de coluna:

Python

(spark.read.table(...)
  .withColumn("birthDate", col("birthDate").cast("date"))
  .write
  .mode("overwrite")
  .option("overwriteSchema", "true")
  .saveAsTable(...)
)

O exemplo a seguir mostra a alteração de um nome de coluna:

Python

(spark.read.table(...)
  .withColumnRenamed("dateOfBirth", "birthDate")
  .write
  .mode("overwrite")
  .option("overwriteSchema", "true")
  .saveAsTable(...)
)

Habilitar evolução do esquema

Habilitar a evolução do esquema usando um dos seguintes métodos:

• Utilize a sintaxe INSERT WITH SCHEMA EVOLUTION: Funciona com instruções INSERT. Inclua WITH SCHEMA EVOLUTION na sintaxe SQL.
• Utilize a sintaxe MERGE WITH SCHEMA EVOLUTION: Funciona com instruções MERGE. Inclua WITH SCHEMA EVOLUTION na sintaxe SQL ou use .withSchemaEvolution() na API Databricks.
• Defina a opção mergeSchema: Funciona com gravações em lotes ou gravações de transmissão. Definir .option("mergeSchema", "true") em operações de gravação individuais.
• Definir a configuração do Spark (legado): define spark.databricks.delta.schema.autoMerge.enabled para true para toda a SparkSession. Não recomendado para uso em produção.

A Databricks recomenda habilitar a evolução do esquema para cada operação de escrita usando a sintaxe WITH SCHEMA EVOLUTION ou a opção mergeSchema em vez de definir uma configuração do Spark.

Quando são usadas opções ou sintaxe para habilitar a evolução do esquema em uma operação de gravação, isso prevalece sobre a configuração do Spark.

Habilitar a evolução do esquema para gravações para adicionar novas colunas

As colunas que estão presentes na consulta de origem, mas ausentes da tabela de destino, são adicionadas automaticamente como parte de uma transação de escrita quando a evolução do esquema está ativada. Consulte Habilitar a evolução do esquema.

O caso é preservado ao anexar uma nova coluna. Novas colunas são adicionadas ao final do esquema da tabela. Se as colunas adicionais estiverem em uma estrutura, elas são anexadas ao final da estrutura na tabela de destino.

Sintaxe de evolução do esquema para INSERT

É possível usar a cláusula WITH SCHEMA EVOLUTION em instruções INSERT para habilitar a evolução do esquema:

SQL

INSERT WITH SCHEMA EVOLUTION INTO target_table
SELECT * FROM source_table

Se a consulta em source_table retornar colunas que não existam na tabela de destino, essas colunas serão adicionadas automaticamente ao esquema target_table. Linhas existentes recebem valores NULL para as novas colunas.

INSERIR com evolução do esquema usando a API do DataFrame

O exemplo a seguir demonstra o uso da opção mergeSchema com uma operação de gravação em lotes:

Python

Python

(spark.read
  .table("source_table")
  .write
  .option("mergeSchema", "true")
  .mode("append")
  .saveAsTable("target_table")
)

Scala

Scala

spark.read
  .table("source_table")
  .write
  .option("mergeSchema", "true")
  .mode("append")
  .saveAsTable("target_table")

Inserção com evolução do esquema em transmissão

O exemplo a seguir demonstra o uso da opção mergeSchema com o Auto Loader. Consulte O que é o Auto Loader?.

Python

(spark.readStream
  .format("cloudFiles")
  .option("cloudFiles.format", "json")
  .option("cloudFiles.schemaLocation", "<path-to-schema-location>")
  .load("<path-to-source-data>")
  .writeStream
  .option("mergeSchema", "true")
  .option("checkpointLocation", "<path-to-checkpoint>")
  .trigger(availableNow=True)
  .toTable("table_name")
)

Evolução automática do esquema para merge

A evolução do esquema permite que os usuários resolvam discrepâncias de esquema entre a tabela de destino e a tabela de origem durante a operação de mesclagem (merge). Ele lida com os dois casos a seguir:

1. Uma coluna existe na tabela de origem, mas não na tabela de destino, e é especificada por nome em uma atribuição de ações de inserção ou atualização. Alternativamente, uma ação UPDATE SET * ou INSERT * está presente.

   Essa coluna será adicionada ao esquema de destino, e seus valores serão preenchidos a partir da coluna correspondente na origem.

   • Isto só se aplica quando o nome e a estrutura da coluna na origem do merge correspondem exatamente à atribuição de destino.

   • A nova coluna deve estar presente no esquema de origem. A atribuição da nova coluna na cláusula de ação não a define.

   Esses exemplos permitem a evolução do esquema:

   SQL

   -- The column newcol is present in the source but not in the target. It will be added to the target.
   UPDATE SET target.newcol = source.newcol
   
   -- The field newfield doesn't exist in struct column somestruct of the target. It will be added to that struct column.
   UPDATE SET target.somestruct.newfield = source.somestruct.newfield
   
   -- The column newcol is present in the source but not in the target.
   -- It will be added to the target.
   UPDATE SET target.newcol = source.newcol + 1
   
   -- Any columns and nested fields in the source that don't exist in target will be added to the target.
   UPDATE SET *
   INSERT *

   Estes exemplos não acionam a evolução do esquema se a coluna newcol não estiver presente no esquema source:

   SQL

   UPDATE SET target.newcol = source.someothercol
   UPDATE SET target.newcol = source.x + source.y
   UPDATE SET target.newcol = source.output.newcol

2. Uma coluna na tabela de destino não está presente na tabela de origem.

   O esquema de destino não é alterado. Estas colunas:

   • São deixados inalterados para UPDATE SET *.

   • Estão definidos como NULL para INSERT *.

   • Ainda pode ser explicitamente modificado se atribuído na cláusula de ação.

   Por exemplo:

   SQL

   UPDATE SET *  -- The target columns that are not in the source are left unchanged.
   INSERT *  -- The target columns that are not in the source are set to NULL.
   UPDATE SET target.onlyintarget = 5  -- The target column is explicitly updated.
   UPDATE SET target.onlyintarget = source.someothercol  -- The target column is explicitly updated from some other source column.

É preciso habilitar manualmente a evolução automática do esquema. Consulte Habilitar a evolução do esquema.

nota

No Databricks Runtime 12.2 LTS e acima, colunas e campos struct presentes na tabela de origem podem ser especificados por nome nas ações de inserção ou atualização. No Databricks Runtime 11.3 LTS e abaixo, apenas INSERT * ou UPDATE SET * ações podem ser usadas para evolução do esquema com merge.

No Databricks Runtime 13.3 LTS e acima, você pode usar a evolução do esquema com structs aninhados em mapas, como map<int, struct<a: int, b: int>>.

Sintaxe de evolução do esquema para merge

No Databricks Runtime 15.4 LTS e acima, pode-se especificar a evolução do esquema em uma instrução de merge usando SQL ou APIs de tabela:

SQL

SQL

MERGE WITH SCHEMA EVOLUTION INTO target
USING source
ON source.key = target.key
WHEN MATCHED THEN
  UPDATE SET *
WHEN NOT MATCHED THEN
  INSERT *
WHEN NOT MATCHED BY SOURCE THEN
  DELETE

Python

Python

from delta.tables import *

(targetTable
  .merge(sourceDF, "source.key = target.key")
  .withSchemaEvolution()
  .whenMatchedUpdateAll()
  .whenNotMatchedInsertAll()
  .whenNotMatchedBySourceDelete()
  .execute()
)

Scala

Scala

import io.delta.tables._

targetTable
  .merge(sourceDF, "source.key = target.key")
  .withSchemaEvolution()
  .whenMatched()
  .updateAll()
  .whenNotMatched()
  .insertAll()
  .whenNotMatchedBySource()
  .delete()
  .execute()

Exemplos de operações de merge com evolução do esquema

Aqui estão alguns exemplos dos efeitos da merge operação com e sem a evolução do esquema.

Colunas	Consulta (em SQL)	Comportamento sem evolução do esquema (padrão)	Comportamento com evolução do esquema
Colunas-alvo: key, value Colunas de origem: key, value, new_value	SQL MERGE INTO target_table t USING source_table s ON t.key = s.key WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *	O esquema da tabela permanece inalterado; somente as colunas value são key atualizadas/inseridas.	O esquema da tabela é alterado para (key, value, new_value). Os registros existentes com correspondências são atualizados com o value e new_value na origem. Novas linhas são inseridas com o esquema (key, value, new_value).
Colunas-alvo: key, old_value Colunas de origem: key, new_value	SQL MERGE INTO target_table t USING source_table s ON t.key = s.key WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *	UPDATE e INSERT as ações geram um erro porque a coluna de destino não old_value está na origem.	O esquema da tabela é alterado para (key, old_value, new_value). Os registros existentes com correspondências são atualizados com o new_value na origem deixando o old_value inalterado. Novos registros são inseridos com o key, new_value e NULL especificados para o old_value.
Colunas-alvo: key, old_value Colunas de origem: key, new_value	SQL MERGE INTO target_table t USING source_table s ON t.key = s.key WHEN MATCHED THEN UPDATE SET new_value = s.new_value	UPDATE linha um erro porque a coluna new_value não existe na tabela de destino.	O esquema da tabela é alterado para (key, old_value, new_value). Os registros existentes com correspondências são atualizados com o new_value na origem, deixando old_value inalterados, e registros incompatíveis NULL inseridos para new_value. Consulte a nota (1).
Colunas-alvo: key, old_value Colunas de origem: key, new_value	SQL MERGE INTO target_table t USING source_table s ON t.key = s.key WHEN NOT MATCHED THEN INSERT (key, new_value) VALUES (s.key, s.new_value)	INSERT linha um erro porque a coluna new_value não existe na tabela de destino.	O esquema da tabela é alterado para (key, old_value, new_value). Novos registros são inseridos com os key, new_value e NULL especificados para o old_value. Os registros existentes NULL inseridos para new_value deixando old_value inalterado. Consulte a nota (1).

(1) Esse comportamento está disponível no Databricks Runtime 12.2 LTS e acima; erro do Databricks Runtime 11.3 LTS e abaixo nesta condição.

Excluir colunas com merge

No Databricks Runtime 12.2 LTS e acima, você pode usar cláusulas EXCEPT em condições de merge para excluir colunas explicitamente. O comportamento da palavra-chave EXCEPT varia dependendo se a evolução do esquema está ativada ou não.

Com a evolução do esquema desabilitada, a palavra-chave EXCEPT se aplica à lista de colunas na tabela de destino e permite excluir colunas de UPDATE ou INSERT ações. As colunas excluídas são definidas como null.

Com a evolução do esquema habilitada, a palavra-chave EXCEPT se aplica à lista de colunas na tabela de origem e permite excluir colunas da evolução do esquema. Uma nova coluna na fonte que não esteja presente no destino não será adicionada ao esquema de destino se estiver listada na cláusula EXCEPT. Colunas excluídas que já estão presentes no destino estão definidas como null.

Os exemplos a seguir demonstram esta sintaxe:

Colunas	Consulta (em SQL)	Comportamento sem evolução do esquema (padrão)	Comportamento com evolução do esquema
Colunas-alvo: id, title, last_updated Colunas de origem: id, title, review, last_updated	SQL MERGE INTO target t USING source s ON t.id = s.id WHEN MATCHED THEN UPDATE SET last_updated = current_date() WHEN NOT MATCHED THEN INSERT * EXCEPT (last_updated)	As linhas correspondentes são atualizadas definindo o campo last_updated como a data atual. Novas linhas são inseridas utilizando valores para id e title. O campo excluído last_updated está definido como null. O campo review é ignorado porque não está no destino.	As linhas correspondentes são atualizadas definindo o campo last_updated como a data atual. O esquema foi desenvolvido para adicionar o campo review. Novas linhas são inseridas usando todos os campos de origem, exceto last_updated, que é definido como null.
Colunas-alvo: id, title, last_updated Colunas de origem: id, title, review, internal_count	SQL MERGE INTO target t USING source s ON t.id = s.id WHEN MATCHED THEN UPDATE SET last_updated = current_date() WHEN NOT MATCHED THEN INSERT * EXCEPT (last_updated, internal_count)	INSERT linha um erro porque a coluna internal_count não existe na tabela de destino.	As linhas correspondentes são atualizadas definindo o campo last_updated como a data atual. O campo review é adicionado à tabela de destino, mas o campo internal_count é ignorado. As novas linhas inseridas têm last_updated definido como null.

Habilitar evolução do esquema com configuração do Spark (legado)

Você pode definir a configuração do Spark spark.databricks.delta.schema.autoMerge.enabled para true para habilitar a evolução do esquema para todas as operações de gravação na SparkSession atual:

Python

Python

spark.conf.set("spark.databricks.delta.schema.autoMerge.enabled", True)

Scala

Scala

spark.conf.set("spark.databricks.delta.schema.autoMerge.enabled", true)

SQL

SQL

SET spark.databricks.delta.schema.autoMerge.enabled=true

importante

Esta abordagem não é recomendada para uso em produção. Em vez disso, habilite a evolução do esquema para cada operação de gravação:

• Para INSERT e gravações em lote/transmissão, use .option("mergeSchema", "true") ou INSERT WITH SCHEMA EVOLUTION
• Para declarações MERGE, utilize MERGE WITH SCHEMA EVOLUTION

A definição de uma configuração em todo o escopo da sessão pode levar a alterações de esquema não intencionais em várias operações e dificulta o entendimento de quais operações evoluem o esquema.

Quando são usadas opções ou sintaxe para habilitar a evolução do esquema em uma operação de gravação, isso prevalece sobre a configuração do Spark.

Substituir esquema de tabela

Por padrão, substituir os dados em uma tabela não substitui o esquema. Ao sobrescrever uma tabela usando mode("overwrite") sem replaceWhere, talvez você ainda queira substituir o esquema dos dados que estão sendo gravados. Você substitui o esquema e o particionamento da tabela definindo a opção overwriteSchema como true:

Python

df.write.option("overwriteSchema", "true")

importante

Você não pode especificar overwriteSchema como true ao usar a substituição dinâmica de partição.