Alterar esquemas de tabela manualmente

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.
• Alterar o tipo de dados de uma coluna.
• Alterar o modo de uma coluna, além de transformar colunas REQUIRED em NULLABLE.
• Excluir uma coluna.

Para mais informações sobre alterações de esquema compatíveis no BigQuery, consulte Como modificar esquemas de tabelas.

Cuidado: as opções descritas nesta página envolvem a substituição da tabela ou o carregamento dos dados em uma nova tabela. Para tabelas particionadas por tempo de processamento, essas operações destroem as informações de particionamento originais. Após a operação, todos os dados da tabela residem em uma única partição.

Alterar o nome de uma coluna

A renomeação de uma coluna não é compatível com o console do Google Cloud, com a ferramenta de linha de comando bq ou 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 colunas manualmente:

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

Opção 1: usar consultas

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.

Vantagens

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

Desvantagens

• Renomear uma coluna usando uma consulta requer a verificação da tabela inteira. É possível que as cobranças da consulta sejam elevadas 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).
• As colunas renomeadas que eram obrigatórias se tornam anuláveis.

Exemplo de alias de coluna

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

Console

1. No console do Google Cloud, selecione o Editor de consultas.

2. No Editor de consultas, insira a consulta a seguir para selecionar todos os dados em mydataset.mytable exceto pelas duas colunas que precisam ser renomeadas. mydataset.mytable está no projeto padrão. Na consulta, é usado um alias para alterar o nome de column_one para newcolumn_one e para alterar o nome 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.

4. Na seção Destino:

   1. Selecione Definir uma tabela de destino para os resultados da consulta.

   2. Em Conjunto de dados, escolha myproject.mydataset.

   3. Em ID da tabela, insira mytable.

   4. Em Preferência de gravação na tabela de destino, selecione em Substituir tabela. Isso substitui mytable usando os resultados da consulta.

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

bq

Digite o seguinte comando bq query para selecionar todos os dados em mydataset.mytable, exceto pelas duas colunas que precisam ser renomeadas. mydataset.mytable está no projeto padrão. Na consulta, é usado um alias para alterar o nome de column_one para newcolumn_one e para alterar o nome 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 alterar o nome de column_two para newcolumn_two, chame o método jobs.insert e configure um job query. Opcional: especifique seu local na property location da 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 property configuration.query.destinationTable e especifique WRITE_TRUNCATE na property configuration.query.writeDisposition. Para especificar uma nova tabela de destino, insira o nome da tabela na property configuration.query.destinationTable.

Opção 2: exportar e carregar dados em uma nova tabela

É 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. Você também pode usar o job de carregamento para substituir a tabela atual.

Vantagens

• Você não é cobrado pelo job de exportação nem pelo job 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.

Desvantagens

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

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 Google Cloud, com a ferramenta de linha de comando bq ou com a API. Se você tentar atualizar uma tabela usando um esquema que especifique um novo tipo de dado para uma coluna, isso resultará no 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 estiver mais preocupado com a simplicidade e a facilidade de uso, e não com os custos.
• Com a recriação da tabela: escolha esta opção se você estiver mais preocupado com os custos, e não com a simplicidade e a facilidade de uso.

Opção 1: usar consultas

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. É possível usar os resultados da consulta para substituir a tabela ou para criar uma nova tabela de destino.

Vantagens

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

Desvantagens

• Alterar o tipo de dados de uma coluna usando uma consulta exige a verificação da tabela inteira. É possível que as cobranças da consulta sejam elevadas 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).

CAST exemplo

No exemplo a seguir, você verá uma consulta SQL padrão que seleciona todos os dados de column_two e column_three em mydataset.mytable e converte column_one de DATE para STRING. O resultado da consulta é usado para substituir a tabela atual. A tabela substituída armazena 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 Google Cloud, 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 converter column_one de DATE para STRING. Na consulta, um alias é usado para aplicar a função cast a column_one com o mesmo nome. mydataset.mytable está no 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 a seguir:

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

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

   3. No campo Nome da tabela, insira mytable.

   4. Clique em OK.

6. Na seção Preferência de gravação da tabela de destino, em Preferência de gravação, selecione Substituir tabela. Isso substitui 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.

bq

Insira o seguinte comando bq query para selecionar todos os dados de column_two e column_three em mydataset.mytable e para converter column_one de DATE para STRING. Na consulta, um alias é usado para aplicar a função cast a 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 para converter column_one de DATE para STRING, chame o método jobs.insert e configure um job query. Opcional: especifique seu local na property location da 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 property configuration.query.destinationTable e especifique WRITE_TRUNCATE na property configuration.query.writeDisposition.

Opção 2: exportar e carregar dados em uma nova tabela

É 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. Você também pode usar o job de carregamento para substituir a tabela atual.

Vantagens

• Você não é cobrado pelo job de exportação nem pelo job 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.

Desvantagens

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

Alterar modo de coluna

Atualmente, a única modificação compatível com o modo de uma coluna é alterá-la de REQUIRED para NULLABLE. A alteração do modo de uma coluna de REQUIRED para NULLABLE também é chamada 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..

Exportar e carregar dados em uma nova tabela

É 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. Você também pode usar o job de carregamento para substituir a tabela atual.

Vantagens

• Você não é cobrado pelo job de exportação nem pelo job 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.

Desvantagens

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

Excluir colunas a partir do esquema da tabela

A exclusão de uma coluna de um esquema de tabela atual não é compatível com o console do Google Cloud, com a ferramenta de linha de comando bq ou com 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 colunas manualmente:

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

Opção 1: usar consultas

Há duas instruções SQL que podem ser usadas para excluir uma coluna:

• SELECT * EXCEPT
• ALTER TABLE DROP COLUMN

A seção a seguir mostra como usar uma consulta SELECT * EXCEPT que exclui a coluna (ou as colunas) que você quer remover e usa o resultado da consulta para substituir a tabela ou criar uma nova tabela de destino.

Para usar a instrução ALTER TABLE DROP COLUMN, consulte os exemplos na página Linguagem de definição de dados.

Vantagens

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

Desvantagens

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

SELECT * EXCEPT exemplo

No exemplo a seguir, vemos 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 Google Cloud, clique em Escrever nova consulta.

2. No Editor de consultas, insira a seguinte consulta para selecionar todos os dados de mydataset.mytable, exceto column_two. mydataset.mytable está no 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 a seguir:

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

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

   3. No campo Nome da tabela, insira mytable.

   4. Clique em OK.

6. Na seção Preferência de gravação da tabela de destino, em Preferência de gravação, selecione Substituir tabela. Isso substitui 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.

bq

Insira o seguinte comando bq query 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 query. Opcional: especifique seu local na property location da 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 property configuration.query.destinationTable e especifique WRITE_TRUNCATE na property configuration.query.writeDisposition.

Opção 2: exportar e carregar dados em uma nova tabela

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. Você também pode usar o job de carregamento para substituir a tabela atual.

Vantagens

• Você não é cobrado pelo job de exportação nem pelo job 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.

Desvantagens

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

Segurança de tabelas

Para controlar o acesso a tabelas no BigQuery, consulte Introdução aos controles de acesso a tabelas.