Como alterar manualmente esquemas de tabela

Neste documento, descrevemos como alterar manualmente a definição do esquema para as tabelas atuais do BigQuery. Muitas alterações de esquema não têm suporte nativo no BigQuery e exigem soluções alternativas manuais. Essas modificações de esquema não aceitas incluem:

  • Como alterar o nome de uma coluna
  • Como alterar o tipo de dados de uma coluna
  • Como alterar o modo de uma coluna, além de transformar colunas REQUIRED em NULLABLE
  • Como excluir uma coluna

Para informações sobre alterações de esquema aceitas no BigQuery, consulte Como modificar esquemas de tabelas.

Como alterar o nome de uma coluna

A renomeação de uma coluna não é compatível com o Console do GCP, com a IU da Web clássica do BigQuery, com a ferramenta de linha de comando nem com a API. Se você tentar atualizar um esquema de tabelas usando uma coluna renomeada, verá o seguinte erro: BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table.

Há duas maneiras de renomear uma coluna manualmente:

  • Com uma consulta SQL: escolha esta opção se você estiver mais preocupado com a simplicidade e a facilidade de uso e menos preocupado com os custos.
  • Com a recriação da tabela: escolha esta opção se você estiver mais preocupado com os custos e menos preocupado com a simplicidade e facilidade de uso.

Opção 1: com uma consulta

Para alterar o nome de uma coluna usando uma consulta SQL, selecione todas as colunas da tabela e atribua um alias à coluna que você precisa renomear. Você pode usar o resultado da consulta para substituir a tabela existente ou criar uma nova tabela de destino. Quando você atribui um alias à coluna com um novo nome, ele precisa aderir às regras do BigQuery para nomes de colunas.

Prós

  • Usar uma consulta para gravar os dados em uma nova tabela de destino preserva os dados originais.
  • Se você usar o job de consulta para substituir a tabela original, terá custos de armazenamento para uma tabela em vez de duas, mas perderá os dados originais.

Contras

  • Renomear uma coluna usando uma consulta exige que você explore a tabela inteira. As cobranças da consulta podem ser significativas se a tabela for muito grande.
  • Se você gravar os resultados da consulta em uma nova tabela de destino, terá custos de armazenamento tanto para a tabela antiga como para a nova (a menos que você exclua a antiga).

Opção 2: exportar os dados e carregá-los em uma tabela nova

É possível renomear uma coluna exportando os dados da tabela para o Cloud Storage e carregando-os em uma tabela nova com uma definição de esquema que tenha o nome correto da coluna. Também é possível usar o job de carregamento para substituir a tabela atual.

Prós

  • Você não é cobrado pelo job de exportação nem pelo de carregamento. Atualmente, os jobs de carregamento e de exportação do BigQuery são gratuitos.
  • Se você usar o job de carregamento para substituir a tabela original, terá custos de armazenamento para uma tabela em vez de duas, mas perderá os dados originais.

Contras

  • Se você carregar os dados em uma tabela nova, terá custos de armazenamento para a tabela original e para a tabela nova (a menos que exclua a antiga).
  • Você será cobrado por armazenar os dados exportados no Cloud Storage.

Exemplo de alias de coluna

No exemplo a seguir, mostraremos uma consulta SQL padrão que seleciona todos os dados em mytable, exceto duas colunas que exigem renomeação. Um alias é usado para gerar novos nomes para as duas colunas. A column_one é renomeada newcolumn_one e column_two é renomeada newcolumn_two. O resultado da consulta é usado para substituir a tabela atual.

Console

  1. Na IU da Web do BigQuery do console, selecione o Editor de consultas.

  2. No Editor de consultas, insira a seguinte consulta para selecionar todos os dados em mydataset.mytable, exceto as duas colunas que precisam ser renomeadas. mydataset.mytable está no seu projeto padrão. A consulta usa um alias para mudar o nome de column_one para newcolumn_one e de column_two para newcolumn_two.

    SELECT
     * EXCEPT(column_one, column_two),
     column_one AS newcolumn_one, column_two AS newcolumn_two
    FROM
     mydataset.mytable
    
  3. Clique em Mais e selecione Configurações de consulta.

    Configurações de consulta

  4. Na seção Destino, marque Definir uma tabela de destino para os resultados da consulta.

    Configurar tabela de destino

  5. Nos campos abaixo:

    1. Em Nome do projeto, mantenha o projeto padrão como o valor definido. Esse é o projeto que contém mydataset.mytable.

    2. Em Nome do conjunto de dados, selecione mydataset.

    3. No campo Nome da tabela, insira mytable.

    4. Clique em OK.

  6. Na seção Preferência de gravação na tabela de destino, em Preferência de gravação, selecione Substituir tabela. Isso substituirá mytable usando os resultados da consulta.

    Substituir tabela

  7. (Opcional) Em Local de processamento, escolha o local dos dados.

  8. Clique em Salvar para atualizar as configurações e, no Editor de consultas, clique em Executar. Quando o job de consulta for concluído, as colunas em mytable terão novos nomes.

IU clássica

  1. Na IU da Web clássica do BigQuery, clique em Escrever consulta.

  2. Na caixa New Query, insira a consulta a seguir para selecionar todos os dados de mydataset.mytable, exceto as duas colunas que precisam ser renomeadas. mydataset.mytable está no seu projeto padrão. A consulta usa um alias para mudar o nome de column_one para newcolumn_one e de column_two para newcolumn_two.

    #standardSQL
    SELECT
     * EXCEPT(column_one, column_two),
     column_one AS newcolumn_one, column_two AS newcolumn_two
    FROM
     mydataset.mytable
    
  3. Clique em Mostrar opções.

  4. Na seção Tabela de destino, clique em Selecionar tabela.

  5. Na caixa de diálogo Selecionar tabela de destino:

    1. Para Projeto, deixe o valor definido como o projeto padrão. Esse é o projeto que contém mydataset.mytable.

    2. Em Dataset, selecione mydataset.

    3. No campo Table ID, insira mytable.

    4. Clique em OK.

  6. Na seção Tabela de destino, para Preferência de gravação, escolha Substituir tabela. Isso substituirá mytable usando os resultados da consulta.

  7. (Opcional) Em Processing Location, clique em Unspecified e escolha o local dos dados.

  8. Clique em Run query. Quando o job de consulta for concluído, as colunas em mytable terão novos nomes.

CLI

Digite o comando bq query a seguir para selecionar todos os dados em mydataset.mytable, exceto duas colunas que precisam ser renomeadas. O mydataset.mytable está no projeto padrão. A consulta usa um alias para mudar o nome de column_one para newcolumn_one e de column_two para newcolumn_two.

Grave os resultados da consulta em mydataset.mytable usando a sinalização --destination_table e especifique a sinalização --replace para substituir mytable. Especifique a sinalização use_legacy_sql=false para usar a sintaxe SQL padrão.

(Opcional) Forneça a sinalização --location e defina o valor do local.

bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=false \
'SELECT
  * EXCEPT(column_one,
    column_two),
  column_one AS newcolumn_one,
  column_two AS newcolumn_two
FROM
  mydataset.mytable'

API

Para alterar o nome de column_one para newcolumn_one e para alterar o nome de column_two para newcolumn_two, chame o método jobs.insert e configure um job de query. (Opcional) Especifique o local na propriedade location na seção jobReference.

A consulta SQL usada no job de consulta seria: SELECT * EXCEPT(column_one, column_two), column_one AS newcolumn_one, column_two AS newcolumn_two FROM mydataset.mytable. Essa consulta seleciona todos os dados em mytable, exceto duas colunas que exigem renomeação. Um alias é usado para gerar novos nomes para as duas colunas.

Para substituir mytable pelos resultados da consulta, inclua mydataset.mytable na propriedade configuration.query.destinationTable e especifique WRITE_TRUNCATE na propriedade configuration.query.writeDisposition. Para especificar uma nova tabela de destino, insira o nome da tabela na propriedade configuration.query.destinationTable.

Como alterar o tipo de dados de uma coluna

A alteração do tipo de dados de uma coluna não é compatível com o Console do GCP, com a IU da Web clássica do BigQuery, com a ferramenta de linha de comando nem com a API. Se você tentar atualizar uma tabela usando um esquema que especifique um novo tipo de dados para uma coluna, verá o seguinte erro: BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table.

Há duas maneiras de alterar manualmente o tipo de dados de uma coluna:

  • Com uma consulta SQL: escolha esta opção se você estiver mais preocupado com a simplicidade e a facilidade de uso e menos preocupado com os custos.
  • Com a recriação da tabela: escolha esta opção se você estiver mais preocupado com os custos e menos preocupado com a simplicidade e facilidade de uso.

Opção 1: com uma consulta

Use uma consulta SQL para selecionar todos os dados da tabela e aplicar a função cast à coluna relevante e fazendo a conversão para um tipo de dados diferente. Você pode usar os resultados da consulta para substituir a tabela ou para criar uma nova tabela de destino.

Prós

  • Usar uma consulta para gravar os dados em uma nova tabela de destino preserva os dados originais.
  • Se você usar o job de consulta para substituir a tabela original, terá custos de armazenamento para uma tabela em vez de duas, mas perderá os dados originais.

Contras

  • Alterar o tipo de dados de uma coluna usando uma consulta exige que você examine a tabela inteira. As cobranças da consulta poderão ser significativas se a tabela for muito grande.
  • Se você gravar os resultados da consulta em uma nova tabela de destino, terá custos de armazenamento tanto para a tabela antiga como para a nova (a menos que você exclua a antiga).

Opção 2: exportar os dados e carregá-los em uma tabela nova

É possível alterar o tipo de dados de uma coluna exportando os dados da tabela para o Cloud Storage e carregando-os em uma nova tabela com uma definição de esquema que especifique o tipo de dados correto para a coluna. Também é possível usar o job de carregamento para substituir a tabela atual.

Prós

  • Você não é cobrado pelo job de exportação nem pelo de carregamento. Atualmente, os jobs de carregamento e de exportação do BigQuery são gratuitos.
  • Se você usar o job de carregamento para substituir a tabela original, terá custos de armazenamento para uma tabela em vez de duas, mas perderá os dados originais.

Contras

  • Se você carregar os dados em uma tabela nova, terá custos de armazenamento para a tabela original e para a tabela nova (a menos que exclua a antiga).
  • Você será cobrado por armazenar os dados exportados no Cloud Storage.

Exemplo de CAST

No exemplo a seguir, mostraremos uma consulta SQL padrão que seleciona todos os dados de column_two e column_three em mydataset.mytable e aplica a função cast convertendo o tipo de column_one de DATE para STRING. O resultado da consulta é usado para substituir a tabela atual. A tabela substituída armazenará column_one como um tipo de dados STRING.

Quando a função CAST é usada, pode ocorrer falha na consulta se o BigQuery não conseguir executar a conversão. Para detalhes sobre regras de uso de cast no SQL padrão, consulte Como usar a função cast na documentação de referência de funções e operadores.

Console

  1. No Console do GCP, clique em Escrever nova consulta.

  2. No Editor de consultas, digite a seguinte consulta para selecionar todos os dados de column_two e column_three em mydataset.mytable e para aplicar a função cast column_one de DATE para STRING. A consulta usa um alias para aplicar a função cast column_one com o mesmo nome. mydataset.mytable está no seu projeto padrão.

    SELECT
     column_two, column_three, CAST(column_one AS STRING) AS column_one
    FROM
     mydataset.mytable
    
  3. Clique em Mais e selecione Configurações de consulta.

  4. Na seção Destino, marque Definir uma tabela de destino para os resultados da consulta.

  5. Nos campos abaixo:

    1. Em Nome do projeto, mantenha o projeto padrão como o valor definido. Esse é o projeto que contém mydataset.mytable.

    2. Em Nome do conjunto de dados, selecione mydataset.

    3. No campo Nome da tabela, insira mytable.

    4. Clique em OK.

  6. Na seção Preferência de gravação na tabela de destino, em Preferência de gravação, selecione Substituir tabela. Isso substituirá mytable usando os resultados da consulta.

  7. (Opcional) Em Local de processamento, clique em Seleção automática e escolha o local dos seus dados.

  8. Clique em Salvar para atualizar as configurações e, no Editor de consultas, clique em Executar. Quando o job de consulta for concluído, o tipo de dados de column_one será STRING.

IU clássica

  1. Na IU da Web clássica do BigQuery, clique em Escrever consulta.

  2. Na caixa New Query, insira a consulta a seguir para selecionar todos os dados de column_two e column_three em mydataset.mytable e para aplicar a função cast convertendo o tipo de column_one de DATE para STRING. A consulta usa um alias para aplicar a função cast a column_one com o mesmo nome. mydataset.mytable está no projeto padrão.

    #standardSQL
    SELECT
     column_two, column_three, CAST(column_one AS STRING) AS column_one
    FROM
     mydataset.mytable
    
  3. Clique em Mostrar opções.

  4. Na seção Tabela de destino, clique em Selecionar tabela.

  5. Na caixa de diálogo Selecionar tabela de destino:

    1. Para Projeto, deixe o valor definido como o projeto padrão. Esse é o projeto que contém mydataset.mytable.

    2. Em Conjunto de dados, selecione mydataset.

    3. No campo Código da tabela, insira mytable.

    4. Clique em OK.

  6. Na seção Tabela de destino, para Preferência de gravação, escolha Substituir tabela. Isso substituirá mytable usando os resultados da consulta.

  7. (Opcional) Em Local de processamento, clique em Não especificado e escolha o local dos dados.

  8. Clique em Executar consulta. Quando o job de consulta for concluído, o tipo de dados de column_one será STRING.

CLI

Digite o comando bq query a seguir para selecionar todos os dados de column_two e column_three em mydataset.mytable e aplicar a função cast convertendo o tipo de column_one de DATE para STRING. A consulta usa um alias para aplicar a função cast em column_one com o mesmo nome. mydataset.mytable está no projeto padrão.

Os resultados da consulta são gravados em mydataset.mytable usando a sinalização --destination_table, e a sinalização --replace é usada para substituir mytable. Especifique a sinalização use_legacy_sql=false para usar a sintaxe SQL padrão.

(Opcional) Forneça a sinalização --location e defina o valor do local.

bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=false \
'SELECT
  column_two,
  column_three,
  CAST(column_one AS STRING) AS column_one
FROM
  mydataset.mytable'

API

Para selecionar todos os dados de column_two e column_three em mydataset.mytable e aplicar a função cast convertendo o tipo de column_one de DATE em STRING, chame o método jobs.insert e configure um job query. (Opcional) Especifique o local na propriedade location na seção jobReference.

A consulta SQL usada no job de consulta seria: SELECT column_two, column_three, CAST(column_one AS STRING) AS column_one FROM mydataset.mytable. Na consulta, um alias é usado para aplicar a função cast a column_one com o mesmo nome.

Para substituir mytable pelos resultados da consulta, inclua mydataset.mytable na propriedade configuration.query.destinationTable e especifique WRITE_TRUNCATE na propriedade configuration.query.writeDisposition.

Como alterar o modo de uma coluna

Atualmente, a única alteração aceita que você pode fazer no modo de uma coluna é alterá-lo de REQUIRED para NULLABLE. Alterar o modo de uma coluna de REQUIRED para NULLABLE também é chamado de relaxamento de coluna. Para informações sobre como relaxar colunas REQUIRED para NULLABLE, consulte Como relaxar o modo de uma coluna.

Se você tentar aplicar uma alteração não aceita a um modo de coluna, um erro como este será retornado. Neste exemplo, foi feita uma tentativa de mudar o modo de uma coluna de NULLABLE para REPEATED: BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table. Field field has changed mode from NULLABLE to REPEATED.

Como exportar os dados e carregá-los em uma tabela nova

É possível alterar manualmente o modo de uma coluna exportando os dados da tabela para o Cloud Storage e, em seguida, carregando-os em uma tabela nova com uma definição de esquema que especifique o modo correto para a coluna. Também é possível usar o job de carregamento para substituir a tabela atual.

Prós

  • Você não é cobrado pelo job de exportação nem pelo de carregamento. Atualmente, os jobs de carregamento e de exportação do BigQuery são gratuitos.
  • Se você usar o job de carregamento para substituir a tabela original, terá custos de armazenamento para uma tabela em vez de duas, mas perderá os dados originais.

Contras

  • Se você carregar os dados em uma tabela nova, terá custos de armazenamento para a tabela original e para a tabela nova (a menos que exclua a antiga).
  • Você será cobrado por armazenar os dados exportados no Cloud Storage.

Como excluir uma coluna de um esquema de tabela

A exclusão de uma coluna de um esquema de tabela atual não é compatível com o Console do GCP, com a IU da Web clássica do BigQuery, com a ferramenta de linha de comando ou a API. Se você tentar atualizar uma tabela usando um esquema que remova uma coluna, verá o seguinte erro: BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table.

Há duas maneiras de excluir manualmente uma coluna:

  • Com uma consulta SQL: escolha esta opção se você estiver mais preocupado com a simplicidade e a facilidade de uso e menos preocupado com os custos.
  • Com a recriação da tabela: escolha esta opção se você estiver mais preocupado com os custos e menos preocupado com a simplicidade e facilidade de uso.

Opção 1: com uma consulta

Use uma consulta SELECT * EXCEPT para excluir as colunas que você quer remover e use o resultado da consulta para substituir a tabela ou para criar uma nova tabela de destino.

Prós

  • Usar uma consulta para gravar os dados em uma nova tabela de destino preserva os dados originais.
  • Se você usar o job de consulta para substituir a tabela original, terá custos de armazenamento para uma tabela em vez de duas, mas perderá os dados originais.

Contras

  • A exclusão de uma coluna usando uma consulta exige que você examine os dados em todas as colunas, exceto a que está removendo. As cobranças de consulta podem ser significativas se a tabela for muito grande.
  • Se você gravar os resultados da consulta em uma nova tabela de destino, terá custos de armazenamento tanto para a tabela antiga como para a nova (a menos que você exclua a antiga).

Opção 2: exportar os dados e carregá-los em uma tabela nova

Também é possível remover uma coluna exportando os dados da tabela para o Cloud Storage, excluindo os dados correspondentes das colunas que você quer remover e, em seguida, carregando-os em uma tabela nova com uma definição de esquema que não inclua as colunas removidas. Também é possível usar o job de carregamento para substituir a tabela atual.

Prós

  • Você não é cobrado pelo job de exportação nem pelo de carregamento. Atualmente, os jobs de carregamento e de exportação do BigQuery são gratuitos.
  • Se você usar o job de carregamento para substituir a tabela original, terá custos de armazenamento para uma tabela em vez de duas, mas perderá os dados originais.

Contras

  • Se você carregar os dados em uma tabela nova, terá custos de armazenamento para a tabela original e para a tabela nova (a menos que exclua a antiga).
  • Você será cobrado por armazenar os dados exportados no Cloud Storage.

Exemplo de SELECT * EXCEPT

No exemplo a seguir, mostraremos uma consulta SQL padrão que seleciona todos os dados de mydataset.mytable exceto column_two. O resultado da consulta é usado para substituir a tabela atual.

Console

  1. No Console do GCP, clique em Escrever nova consulta.

  2. No Editor de consultas, digite a seguinte consulta para selecionar todos os dados em mydataset.mytable, exceto column_two. mydataset.mytable está no seu projeto padrão.

    SELECT
     * EXCEPT(column_two)
    FROM
     mydataset.mytable
    
  3. Clique em Mais e selecione Configurações de consulta.

  4. Na seção Destino, marque Definir uma tabela de destino para os resultados da consulta.

  5. Nos campos abaixo:

    1. Em Nome do projeto, mantenha o projeto padrão como o valor definido. Esse é o projeto que contém mydataset.mytable.

    2. Em Nome do conjunto de dados, selecione mydataset.

    3. No campo Nome da tabela, insira mytable.

    4. Clique em OK.

  6. Na seção Preferência de gravação na tabela de destino, em Preferência de gravação, selecione Substituir tabela. Isso substituirá mytable usando os resultados da consulta.

  7. (Opcional) Em Local de processamento, clique em Seleção automática e escolha o local dos seus dados.

  8. Clique em Salvar para atualizar as configurações e, no Editor de consultas, clique em Executar. Quando o job de consulta for concluído, a tabela incluirá todas as colunas, exceto column_two.

IU clássica

  1. Na IU da Web clássica do BigQuery, clique em Escrever consulta.

  2. Na caixa New Query, insira a consulta a seguir para selecionar todos os dados de mydataset.mytable, exceto column_two. mydataset.mytable está no projeto padrão.

    #standardSQL
    SELECT
     * EXCEPT(column_two)
    FROM
     mydataset.mytable
    
  3. Clique em Mostrar opções.

  4. Na seção Tabela de destino, clique em Selecionar tabela.

  5. Na caixa de diálogo Selecionar tabela de destino:

    1. Para Projeto, deixe o valor definido como o projeto padrão. Esse é o projeto que contém mydataset.mytable.

    2. Em Dataset, selecione mydataset.

    3. No campo Table ID, insira mytable.

    4. Clique em OK.

  6. Na seção Tabela de destino, para Preferência de gravação, escolha Substituir tabela. Isso substituirá mytable usando os resultados da consulta.

  7. (Opcional) Em Processing Location, clique em Unspecified e escolha o local dos dados.

  8. Clique em Run query. Quando o job de consulta for concluído, a tabela incluirá todas as colunas, exceto column_two.

CLI

Digite o comando bq query a seguir para selecionar todos os dados de mydataset.mytable exceto column_two. mydataset.mytable está no projeto padrão. Os resultados da consulta são gravados em mydataset.mytable usando a sinalização --destination_table, e a sinalização --replace é usada para substituir mytable. Especifique a sinalização use_legacy_sql=false para usar a sintaxe SQL padrão.

(Opcional) Forneça a sinalização --location e defina o valor do local.

bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=false \
'SELECT
  * EXCEPT(column_two)
FROM
  mydataset.mytable'

API

Para selecionar todos os dados de mydataset.mytable, exceto column_two, chame o método jobs.insert e configure um job de query. (Opcional) Especifique o local na propriedade location na seção jobReference.

A consulta SQL usada no job de consulta seria: SELECT * EXCEPT(column_two) FROM mydataset.mytable.

Para substituir mytable pelos resultados da consulta, inclua mydataset.mytable na propriedade configuration.query.destinationTable e especifique WRITE_TRUNCATE na propriedade configuration.query.writeDisposition.

Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Precisa de ajuda? Acesse nossa página de suporte.