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

Atualizar esquemas de tabela com evolução do esquema

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:

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.

Alterações manuais de esquema​

É possível utilizar ALTER TABLE instruções para alterar explicitamente o esquema de uma tabela sem gravar novos dados.

Adicionar colunas​

Utilize ALTER TABLE ... ADD COLUMNS para adicionar uma ou mais colunas a uma tabela existente, especificando opcionalmente a posição e um comentário:

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

Por padrão, a anulabilidade é true.

Exemplo: Adicionar campos aninhados​

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

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

Alterar comentários da coluna e ordenação​

Use ALTER TABLE ... ALTER COLUMN para atualizar o comentário de uma coluna ou reordená-la em relação a outras colunas:

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

Exemplo: Alterar campos aninhados​

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

Substituir colunas​

Use ALTER TABLE ... REPLACE COLUMNS para redefinir a lista completa de colunas de uma tabela, incluindo adicionar, remover, reordenar ou renomear colunas em uma única operação:

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

Exemplo: Substituir campos aninhados​

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

Renomear colunas​

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

Exemplo: Renomear campos aninhados​

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.

Remover colunas​

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.

nota

Soltar uma coluna de metadados não exclui os dados subjacentes da coluna em arquivos. Para limpar os dados da coluna descartada:

  • Use REORG TABLE para regravar arquivos.
  • Em seguida, use 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)

Alterar tipo ou 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​

Use WITH SCHEMA EVOLUTION ou defina mergeSchema como true para fazer alterações no esquema com base no esquema dos dados a serem INSERT ou MERGE em uma tabela existente.

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

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​

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

Considere o seguinte:

  • 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 serão anexadas ao final da estrutura na tabela de destino.

INSERT com evolução do esquema usando SQL​

Use a cláusula WITH SCHEMA EVOLUTION em declaraçõ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.

INSERT com evolução do esquema usando API de DataFrame​

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

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

INSERT com evolução do esquema com transmissão estructurada​

O exemplo a seguir demonstra o uso da opção mergeSchema com o Auto Loader para transmissão estructurada. 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​

Para MERGE, a evolução do esquema permite a resolução de discrepâncias de esquema entre a tabela de destino e a de origem. 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 11.3 LTS e abaixo, apenas as ações INSERT * ou UPDATE SET * podem ser usadas para evolução do esquema com merge.

No Databricks Runtime 12.2 LTS e acima, colunas e campos de estrutura presentes na tabela de origem podem ser especificados pelo nome em ações de inserção ou atualização.

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

MERGE com evolução do esquema usando SQL, Python e Scala​

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

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 é 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) Este comportamento está disponível no Databricks Runtime 12.2 LTS e acima; o Databricks Runtime 11.3 LTS e abaixo falham 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.

Quando a evolução do esquema está desativada, 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 aplica-se à lista de colunas na tabela de origem e permite excluir colunas da evolução do esquema. Uma nova coluna na origem, não presente na tabela de destino, não é adicionada ao esquema de destino se estiver listada na cláusula EXCEPT. Colunas excluídas que já estão presentes no destino são definidas como null.

Exemplos de EXCLUDE com MERGE​

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
spark.conf.set("spark.databricks.delta.schema.autoMerge.enabled", True)
nota

A Databricks não recomenda essa abordagem para produção. Configurar uma configuração de sessão ampla pode levar a alterações de esquema não intencionais em várias operações e torna mais difícil entender quais operações evoluem o esquema.

Em vez disso, habilite a evolução do esquema para cada operação de gravação:

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 default, sobrescrever os dados em uma tabela não sobrescreve o esquema. Ao sobrescrever uma tabela usando mode("overwrite") sem replaceWhere, você ainda pode querer sobrescrever o esquema dos dados que estão sendo gravados.

Para substituir o esquema e o particionamento da tabela, defina a opção overwriteSchema como true:

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

Você não pode especificar overwriteSchema como true ao usar a substituição dinâmica de partição. Consulte substituições de partição dinâmica com partitionOverwriteMode (legado).

Nesta página