Atualizar esquema da tabela

As tabelas suportam a evolução do esquema, permitindo modificações na estrutura da tabela conforme 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 tipos em colunas existentes, veja Ampliar tipos com evolução automática do esquema

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

importante

As atualizações de esquema entram em conflito com todas as operações de gravação simultâneas. A Databricks recomenda coordenar as alterações de esquema para evitar conflitos de gravação.

A atualização do esquema de uma tabela encerra qualquer transmissão que leia dessa tabela. Para continuar o processamento, reinicie a transmissão usando os métodos descritos em Considerações de produção para transmissão estruturada.

Atualize 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.

Atualize explicitamente o esquema para alterar os comentários ou a ordem das colunas

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 as 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

Atualize explicitamente o esquema para renomear colunas

nota

Esse recurso está disponível em Databricks Runtime 10.4 LTS e acima.

Para renomear colunas sem reescrever os dados existentes das colunas, você deve ativar o mapeamento de colunas para a tabela. Consulte Renomear e soltar 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 soltar colunas com o mapeamento de colunas do Delta Lake.

Atualize explicitamente o esquema para eliminar colunas

nota

Esse recurso está disponível em Databricks Runtime 11.3 LTS e acima.

Para eliminar colunas como uma operação somente de metadados sem reescrever nenhum arquivo de dados, o senhor deve ativar o mapeamento de colunas para a tabela. Consulte Renomear e soltar colunas com o mapeamento de colunas do Delta Lake.

importante

Eliminar uma coluna dos metadados não exclui os dados subjacentes da coluna nos arquivos. Para limpar os dados da coluna descartada, o senhor pode usar REORG TABLE para reescrever os arquivos. O senhor pode então 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)

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

Você pode alterar o tipo ou o nome de uma coluna ou eliminar uma coluna reescrevendo a tabela. Para fazer isso, use 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(...)
)

Permitir a evolução do esquema

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

• Use a sintaxe INSERT WITH SCHEMA EVOLUTION: Funciona com instruções INSERT . Inclua WITH SCHEMA EVOLUTION na sintaxe SQL.
• Use 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 lotes ou gravações transmissão. Defina .option("mergeSchema", "true") em operações de escrita individuais.
• Defina a configuração do Spark (legado): Define spark.databricks.delta.schema.autoMerge.enabled como true para toda a SparkSession. Não recomendado para uso em produção.

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 Spark .

Quando você usa opções ou sintaxe para habilitar a evolução do esquema em uma operação de escrita, isso tem precedência sobre a configuração Spark .

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

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

As maiúsculas e minúsculas são preservadas ao acrescentar uma nova coluna. Novas colunas são adicionadas ao final do esquema da tabela. Se as colunas adicionais estiverem em uma estrutura, elas serão anexadas ao final da estrutura na tabela de destino.

evolução do esquema de sintaxe para INSERT

Você pode 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 existem na tabela de destino, essas colunas serão adicionadas automaticamente ao esquema target_table . As linhas existentes recebem NULL valores para as novas colunas.

INSERT com evolução do esquema usando API DataFrame

O exemplo a seguir demonstra o uso da opção mergeSchema com um lote de operações de gravação:

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

INSERT com evolução do esquema na 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 opção “evolução do esquema” permite resolver incompatibilidades de esquema entre a tabela de destino e a tabela de origem em “ merge”. Ele lida com os dois casos a seguir:

1. Existe uma coluna na tabela de origem, mas não na tabela de destino, e ela é 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.

   • Isso só se aplica quando o nome da coluna e a estrutura na fonte “ merge ” correspondem exatamente à atribuição de destino.

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

   Estes 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 *

   Esses 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. Existe uma coluna na tabela de destino, mas não na tabela de origem.

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

   • Permanecem inalterados para UPDATE SET *.

   • Estão definidos como NULL para INSERT *.

   • Ainda pode ser modificado explicitamente 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.

O senhor deve ativar manualmente a evolução automática do esquema. Consulte Habilitar a evolução do esquema.

nota

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

Em Databricks Runtime 13.3 LTS e acima, o senhor pode usar a evolução do esquema com structs aninhados dentro de mapas, como map<int, struct<a: int, b: int>>.

evolução do esquema syntax for merge

No Databricks Runtime 15.4 LTS e versões superiores, você pode especificar a evolução do esquema em uma instrução 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()

Exemplo 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 foi alterado para (key, old_value, new_value). Os registros existentes com correspondências são atualizados com o new_value na fonte, deixando old_value inalterado, e os registros sem correspondência têm NULL inserido para new_value. Veja 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 foi 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 têm NULL inserido para new_value, deixando old_value inalterado. Veja a nota (1).

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

Excluir colunas com merge

Em Databricks Runtime 12.2 LTS e acima, o senhor pode usar cláusulas EXCEPT nas condições de merge para excluir explicitamente as colunas. O comportamento da palavra-chave EXCEPT varia de acordo com a ativação ou não da evolução do esquema.

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 a evolução do esquema com a 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

Essa 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 gravações de INSERT e lotes/transmissão, use .option("mergeSchema", "true") ou INSERT WITH SCHEMA EVOLUTION
• Para declarações MERGE , use MERGE WITH SCHEMA EVOLUTION

Configurar uma configuração válida para toda a sessão pode levar a alterações de esquema não intencionais em várias operações e dificulta a compreensão de quais operações alteram o esquema.

Quando você usa opções ou sintaxe para habilitar a evolução do esquema em uma operação de escrita, isso tem precedência sobre a configuração 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.