Como modificar esquemas de tabelas

Neste documento, você verá como modificar definições de esquema para tabelas atuais do BigQuery. O BigQuery tem compatibilidade nativa com as seguintes modificações de esquema:

  • Adicionar colunas a uma definição de esquema
  • Como excluir ou descartar uma coluna de uma definição de esquema
  • Relaxar o modo de uma coluna de REQUIRED para NULLABLE.

É válido criar uma tabela sem definir um esquema inicial e, depois, adicionar uma definição de esquema a ela.

Todas as outras modificações de esquema são incompatíveis e exigem soluções alternativas manuais, 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;

Para informações sobre alterações de esquema incompatíveis que exijam soluções alternativas, consulte Como alterar esquemas de tabela manualmente.

Como adicionar colunas à definição de esquema de uma tabela

É possível adicionar colunas à definição de esquema de uma tabela existente:

  • manualmente (cria uma coluna vazia);
  • quando você usa um job de carregamento ou de consulta para substituir uma tabela;
  • quando você anexa dados a uma tabela usando um job de carregamento ou de consulta.

Qualquer coluna que você adicionar precisará aderir às regras do BigQuery para nomes de colunas. Para mais informações sobre como criar componentes de esquema, consulte Como especificar um esquema.

Como adicionar manualmente uma coluna vazia

Use estes métodos para adicionar uma coluna vazia a uma tabela atual:

  • Como usar o Console do Cloud
  • Use o comando bq update da ferramenta de linha de comando bq.
  • Chame o tables.patch método de API
  • Como usar a instrução de linguagem de definição de dados (DDL, na sigla em inglês) ALTER TABLE ADD COLUMN.
  • Como usar bibliotecas de cliente

Ao adicionar novas colunas a um esquema de tabela atual, as colunas precisarão ser NULLABLE ou REPEATED. Não é possível adicionar uma coluna REQUIRED a um esquema de tabela atual. Se você tentar adicionar uma coluna REQUIRED a um esquema de tabela existente na API ou na ferramenta de linha de comando bq, o seguinte erro será retornado: as colunas BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table. Cannot add required columns to an existing schema. REQUIRED poderão ser adicionadas somente quando você criar uma tabela ao carregar dados ou criar uma tabela vazia com uma definição de esquema.

Depois de adicionar uma nova coluna à definição de esquema da tabela, é possível carregar os dados na nova coluna usando:

Para adicionar colunas vazias à definição de esquema de uma tabela:

Console

  1. No Console do Cloud, acesse a página do BigQuery.

    Ir para o BigQuery

  2. No painel Explorer, expanda o projeto e o conjunto de dados e selecione a tabela.

  3. No painel de detalhes, clique na guia Visualizar.

  4. Clique em Editar esquema. Talvez seja necessário rolar para ver esse botão.

  5. Na página Esquema atual, em Novos campos, clique em Adicionar campo.

    • Em Nome, digite o nome da coluna.
    • Em Tipo, escolha o tipo de dados.
    • Em Modo, escolha NULLABLE ou REPEATED.
  6. Ao terminar de adicionar colunas, clique em Salvar.

SQL

Para adicionar uma nova coluna a uma tabela atual usando a instrução DDL ALTER TABLE ADD COLUMN, siga estas etapas:

  1. No Console do Cloud, acesse a página do BigQuery.

    Ir para o BigQuery

  2. Clique em Escrever nova consulta.

  3. Digite a instrução DDL no campo Editor de consultas.

        ALTER TABLE mydataset.mytable
          ADD COLUMN A STRING
     

  4. Clique em Executar.

Para mais informações sobre como usar o SQL padrão, consulte Como alternar dialetos SQL.

bq

Emita o comando bq update e forneça um arquivo de esquema JSON. Se a tabela que você está atualizando estiver em um projeto diferente do padrão, adicione o ID do projeto ao nome do conjunto de dados neste formato: project_id:dataset.

bq update project_id:dataset.table schema

Em que:

  • project_id é o ID do projeto.
  • dataset é o nome do conjunto de dados que contém a tabela que você está atualizando;
  • table é o nome da tabela que você está atualizando.
  • schema é o caminho para o arquivo de esquema JSON na sua máquina local.

Ao especificar o esquema usando a ferramenta de linha de comando bq, não é possível incluir um tipo RECORD (STRUCT) ou uma descrição de coluna, nem especificar o modo da coluna. Todos os modos assumem NULLABLE como padrão.

Se você tentar adicionar colunas usando uma definição de esquema in-line, será necessário fornecer toda a definição de esquema, incluindo as novas colunas. Como não é possível especificar modos de coluna usando uma definição de esquema in-line, a atualização tentará alterar qualquer coluna REQUIRED atual para NULLABLE. Isso resultará na mensagem de erro a seguir: BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table. Field field has changed mode from REPEATED to NULLABLE.

O método preferido de adicionar colunas a uma tabela existente usando a ferramenta de linha de comando bq é fornecer um arquivo de esquema JSON.

Para adicionar colunas vazias ao esquema de uma tabela usando um arquivo de esquema JSON, siga estas etapas:

  1. Primeiro, emita o comando bq show com a sinalização --schema e grave o esquema da tabela atual em um arquivo. Se a tabela que você está atualizando estiver em um projeto diferente do padrão, adicione o ID do projeto ao nome do conjunto de dados neste formato: project_id:dataset.

    bq show \
    --schema \
    --format=prettyjson \
    project_id:dataset.table > schema_file
    

    Em que:

    • project_id é o ID do projeto.
    • dataset é o nome do conjunto de dados que contém a tabela que você está atualizando;
    • table é o nome da tabela que você está atualizando.
    • schema_file é o arquivo de definição de esquema gravado na máquina local.

    Por exemplo, para gravar a definição de esquema de mydataset.mytable em um arquivo, digite o comando a seguir. mydataset.mytable está no projeto padrão.

       bq show \
       --schema \
       --format=prettyjson \
       mydataset.mytable > /tmp/myschema.json
    
  2. Abra o arquivo de esquema em um editor de texto. Ele será semelhante a este:

    [
      {
        "mode": "REQUIRED",
        "name": "column1",
        "type": "STRING"
      },
      {
        "mode": "REQUIRED",
        "name": "column2",
        "type": "FLOAT"
      },
      {
        "mode": "REPEATED",
        "name": "column3",
        "type": "STRING"
      }
    ]
    
  3. Adicione as novas colunas ao final da definição de esquema. Caso tente adicionar novas colunas em outro local na matriz, este erro será retornado: BigQuery error in update operation: Precondition Failed.

    Com um arquivo JSON, é possível especificar descrições, modos NULLABLE ou REPEATED e tipos RECORD para novas colunas. Por exemplo, com a definição de esquema da etapa anterior, a nova matriz JSON será semelhante ao exemplo a seguir. Neste exemplo, é adicionada uma nova coluna NULLABLE chamada column4. column4 inclui uma descrição.

      [
        {
          "mode": "REQUIRED",
          "name": "column1",
          "type": "STRING"
        },
        {
          "mode": "REQUIRED",
          "name": "column2",
          "type": "FLOAT"
        },
        {
          "mode": "REPEATED",
          "name": "column3",
          "type": "STRING"
        },
        {
          "description": "my new column",
          "mode": "NULLABLE",
          "name": "column4",
          "type": "STRING"
        }
      ]
      

    Para mais informações sobre como trabalhar com arquivos de esquema JSON, consulte Como especificar um arquivo de esquema JSON.

  4. Depois de atualizar o arquivo de esquema, emita o comando a seguir para atualizar o esquema da tabela. Se a tabela que você está atualizando estiver em um projeto diferente do padrão, adicione o ID do projeto ao nome do conjunto de dados neste formato: project_id:dataset.

    bq update project_id:dataset.table schema
    

    Em que:

    • project_id é o ID do projeto.
    • dataset é o nome do conjunto de dados que contém a tabela que você está atualizando;
    • table é o nome da tabela que você está atualizando.
    • schema é o caminho para o arquivo de esquema JSON na sua máquina local.

    Por exemplo, digite o comando a seguir para atualizar a definição de esquema de mydataset.mytable no projeto padrão. O caminho para o arquivo de esquema na máquina local é /tmp/myschema.json.

    bq update mydataset.mytable /tmp/myschema.json
    

API

Chame o método tables.patch e use a propriedade schema para adicionar colunas vazias à definição de esquema. Como o método tables.update substitui todo o recurso da tabela, é melhor usar o método tables.patch.

Go

import (
	"context"
	"fmt"

	"cloud.google.com/go/bigquery"
)

// updateTableAddColumn demonstrates modifying the schema of a table to append an additional column.
func updateTableAddColumn(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	tableRef := client.Dataset(datasetID).Table(tableID)
	meta, err := tableRef.Metadata(ctx)
	if err != nil {
		return err
	}
	newSchema := append(meta.Schema,
		&bigquery.FieldSchema{Name: "phone", Type: bigquery.StringFieldType},
	)
	update := bigquery.TableMetadataToUpdate{
		Schema: newSchema,
	}
	if _, err := tableRef.Update(ctx, update, meta.ETag); err != nil {
		return err
	}
	return nil
}

Java

Antes de testar esta amostra, siga as instruções de configuração do Java no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Java.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.FieldList;
import com.google.cloud.bigquery.LegacySQLTypeName;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.Table;
import java.util.ArrayList;
import java.util.List;

public class AddEmptyColumn {

  public static void runAddEmptyColumn() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableId = "MY_TABLE_NAME";
    String newColumnName = "NEW_COLUMN_NAME";
    addEmptyColumn(newColumnName, datasetName, tableId);
  }

  public static void addEmptyColumn(String newColumnName, String datasetName, String tableId) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      Table table = bigquery.getTable(datasetName, tableId);
      Schema schema = table.getDefinition().getSchema();
      FieldList fields = schema.getFields();

      // Create the new field/column
      Field newField = Field.of(newColumnName, LegacySQLTypeName.STRING);

      // Create a new schema adding the current fields, plus the new one
      List<Field> fieldList = new ArrayList<Field>();
      fields.forEach(fieldList::add);
      fieldList.add(newField);
      Schema newSchema = Schema.of(fieldList);

      // Update the table with the new schema
      Table updatedTable =
          table.toBuilder().setDefinition(StandardTableDefinition.of(newSchema)).build();
      updatedTable.update();
      System.out.println("Empty column successfully added to table");
    } catch (BigQueryException e) {
      System.out.println("Empty column was not added. \n" + e.toString());
    }
  }
}

Node.js

Antes de testar esta amostra, siga as instruções de configuração do Node.js no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery Node.js.


// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function addEmptyColumn() {
  // Adds an empty column to the schema.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = 'my_dataset';
  // const tableId = 'my_table';
  const column = {name: 'size', type: 'STRING'};

  // Retrieve current table metadata
  const table = bigquery.dataset(datasetId).table(tableId);
  const [metadata] = await table.getMetadata();

  // Update table schema
  const schema = metadata.schema;
  const new_schema = schema;
  new_schema.fields.push(column);
  metadata.schema = new_schema;

  const [result] = await table.setMetadata(metadata);
  console.log(result.schema.fields);
}

Python

Antes de testar esta amostra, siga as instruções de configuração do Python no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Python.

Anexe um novo objeto SchemaField a uma cópia de Table.schema e substitua o valor da propriedade Table.schema pelo esquema atualizado.
from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the table
#                  to add an empty column.
# table_id = "your-project.your_dataset.your_table_name"

table = client.get_table(table_id)  # Make an API request.

original_schema = table.schema
new_schema = original_schema[:]  # Creates a copy of the schema.
new_schema.append(bigquery.SchemaField("phone", "STRING"))

table.schema = new_schema
table = client.update_table(table, ["schema"])  # Make an API request.

if len(table.schema) == len(original_schema) + 1 == len(new_schema):
    print("A new column has been added.")
else:
    print("The column has not been added.")

Como adicionar uma coluna aninhada a um RECORD

Além de adicionar novas colunas a um esquema de tabela, também é possível acrescentar novas colunas aninhadas a um RECORD. O processo de adição de uma nova coluna aninhada é muito parecido com o de adição de uma nova coluna comum.

Console

Ainda não é possível adicionar um novo campo aninhado a uma coluna RECORD atual no Console do Cloud.

bq

Emita o comando bq update e forneça um arquivo de esquema JSON que inclua o campo aninhado na definição de esquema da coluna RECORD atual. Se a tabela que você está atualizando estiver em um projeto diferente do padrão, adicione o ID do projeto ao nome do conjunto de dados neste formato: project_id:dataset.

bq update project_id:dataset.table schema

Em que:

  • project_id é o ID do projeto.
  • dataset é o nome do conjunto de dados que contém a tabela que você está atualizando;
  • table é o nome da tabela que você está atualizando.
  • schema é o caminho para o arquivo de esquema JSON na sua máquina local.

Ao especificar o esquema usando a ferramenta de linha de comando bq, não é possível incluir um tipo RECORD (STRUCT) ou uma descrição de coluna, nem especificar o modo da coluna. Todos os modos assumem NULLABLE como padrão. Sendo assim, se você for adicionar uma nova coluna aninhada a um RECORD, será necessário fornecer um arquivo de esquema JSON.

Para adicionar uma coluna aninhada a um RECORD usando um arquivo de esquema JSON, siga estas etapas:

  1. Primeiro, emita o comando bq show com a sinalização --schema e grave o esquema da tabela atual em um arquivo. Se a tabela que você está atualizando estiver em um projeto diferente do padrão, adicione o ID do projeto ao nome do conjunto de dados neste formato: project_id:dataset.table.

    bq show \
    --schema \
    --format=prettyjson \
    project_id:dataset.table > schema_file
    

    Em que:

    • project_id é o ID do projeto.
    • dataset é o nome do conjunto de dados que contém a tabela que você está atualizando;
    • table é o nome da tabela que você está atualizando.
    • schema_file é o arquivo de definição de esquema gravado na máquina local.

    Por exemplo, para gravar a definição de esquema de mydataset.mytable em um arquivo, digite o comando a seguir. mydataset.mytable está no projeto padrão.

    bq show \
    --schema \
    --format=prettyjson \
    mydataset.mytable > /tmp/myschema.json
    
  2. Abra o arquivo de esquema em um editor de texto. O esquema será semelhante ao mostrado abaixo. Neste exemplo, column3 é uma coluna repetida aninhada. As colunas aninhadas são nested1 e nested2. A matriz fields lista os campos aninhados em column3.

    [
      {
        "mode": "REQUIRED",
        "name": "column1",
        "type": "STRING"
      },
      {
        "mode": "REQUIRED",
        "name": "column2",
        "type": "FLOAT"
      },
      {
        "fields": [
          {
            "mode": "NULLABLE",
            "name": "nested1",
            "type": "STRING"
          },
          {
            "mode": "NULLABLE",
            "name": "nested2",
            "type": "STRING"
          }
        ],
        "mode": "REPEATED",
        "name": "column3",
        "type": "RECORD"
      }
    ]
    
  3. Adicione a nova coluna aninhada ao final da matriz fields. Neste exemplo, nested3 é a nova coluna aninhada.

      [
        {
          "mode": "REQUIRED",
          "name": "column1",
          "type": "STRING"
        },
        {
          "mode": "REQUIRED",
          "name": "column2",
          "type": "FLOAT"
        },
        {
          "fields": [
            {
              "mode": "NULLABLE",
              "name": "nested1",
              "type": "STRING"
            },
            {
              "mode": "NULLABLE",
              "name": "nested2",
              "type": "STRING"
            },
            {
              "mode": "NULLABLE",
              "name": "nested3",
              "type": "STRING"
            }
          ],
          "mode": "REPEATED",
          "name": "column3",
          "type": "RECORD"
        }
      ]
      

    Para mais informações sobre como trabalhar com arquivos de esquema JSON, consulte Como especificar um arquivo de esquema JSON.

  4. Depois de atualizar o arquivo de esquema, emita o comando a seguir para atualizar o esquema da tabela. Se a tabela que você está atualizando estiver em um projeto diferente do padrão, adicione o ID do projeto ao nome do conjunto de dados neste formato: project_id:dataset.

    bq update project_id:dataset.table schema
    

    Em que:

    • project_id é o ID do projeto.
    • dataset é o nome do conjunto de dados que contém a tabela que você está atualizando;
    • table é o nome da tabela que você está atualizando.
    • schema é o caminho para o arquivo de esquema JSON na sua máquina local.

    Por exemplo, digite o comando a seguir para atualizar a definição de esquema de mydataset.mytable no projeto padrão. O caminho para o arquivo de esquema na máquina local é /tmp/myschema.json.

    bq update mydataset.mytable /tmp/myschema.json
    

API

Chame o método tables.patch e use a propriedade schema para adicionar colunas aninhadas à definição de esquema. Como o método tables.update substitui todo o recurso da tabela, é melhor usar o método tables.patch.

Como adicionar uma coluna ao substituir ou anexar dados

É possível adicionar novas colunas a uma tabela atual ao carregar dados nela e optar por substituí-la. Ao substituir uma tabela atual, o esquema dos dados sendo carregados é usado para substituir o esquema dessa tabela. Para informações sobre como substituir uma tabela usando um job de carregamento, consulte estes tópicos:

Também é possível adicionar novas colunas a uma tabela existente quando você adiciona dados a ela usando um job de carregamento ou de consulta.

Como adicionar uma coluna a um job de anexação de carregamento

Use estes métodos para adicionar colunas a uma tabela e anexar dados a ela em um job de carregamento:

  • Use o comando bq load da ferramenta de linha de comando bq.
  • Chamada do método jobs.insert da API e configuração de um job de load
  • Como usar bibliotecas de cliente

O Console do Cloud não é compatível com a inclusão de uma coluna em uma tabela atual durante uma operação de anexação.

Ao adicionar colunas usando uma operação de anexação em um job de carregamento, o esquema atualizado pode ser:

  • detectado automaticamente (para arquivos CSV e JSON);
  • especificado em um arquivo de esquema JSON (para arquivos CSV e JSON);
  • recuperado dos dados de origem autoexplicativos dos arquivos de exportação Avro, ORC, Parquet e Cloud Datastore.

Se você especificar o esquema em um arquivo JSON, as novas colunas precisarão ser definidas nele. Se não houver novas definições de coluna, o erro a seguir será retornado ao tentar anexar os dados: Error while reading data, error message: parsing error in row starting at position int: No such field: field..

Ao adicionar novas colunas durante uma operação de anexação, os valores nelas são definidos como NULL nas linhas atuais.

Para adicionar uma nova coluna ao anexar dados a uma tabela durante um job de carregamento, siga estas etapas:

Console

Não é possível adicionar novas colunas a uma tabela atual ao carregar dados usando o Console do Cloud.

bq

Use o comando bq load para carregar os dados e especifique a sinalização --noreplace para indicar que você os está anexando a uma tabela atual.

Se os dados sendo anexados estiverem em CSV ou em formato JSON delimitado por nova linha, especifique a sinalização --autodetect para usar a detecção automática de esquema ou forneça o esquema em um arquivo de esquema JSON. As colunas adicionadas podem ser inferidas automaticamente dos arquivos de exportação do Avro ou do Cloud Datastore.

Defina a sinalização --schema_update_option como ALLOW_FIELD_ADDITION para indicar que o dados sendo anexados contêm novas colunas.

Se a tabela sendo anexada estiver em um conjunto de dados em um projeto diferente do padrão, adicione o ID do projeto ao nome do conjunto de dados no formato a seguir: project_id:dataset.

Opcional: forneça a sinalização --location e defina o valor do local.

Digite o comando load a seguir:

bq --location=location load \
--noreplace \
--autodetect \
--schema_update_option=ALLOW_FIELD_ADDITION \
--source_format=format \
project_id:dataset.table \
path_to_source \
schema

Em que:

  • location é o nome do local. A sinalização --location é opcional. Por exemplo, se você estiver usando o BigQuery na região de Tóquio, defina o valor da sinalização como asia-northeast1. É possível definir um valor padrão para o local usando o arquivo .bigqueryrc.
  • format é NEWLINE_DELIMITED_JSON, CSV, AVRO, PARQUET, ORC ou DATASTORE_BACKUP.
  • project_id é o ID do projeto.
  • dataset é o nome do conjunto de dados que contém a tabela.
  • table é o nome da tabela que você está anexando.
  • path_to_source é um URI do Cloud Storage completo, uma lista de URIs separada por vírgula ou o caminho para um arquivo de dados na máquina local.
  • schema é o caminho para um arquivo de esquema JSON local. Um arquivo de esquema é necessário somente para arquivos CSV e JSON quando --autodetect não estiver especificado. Os esquemas do Avro e do Cloud Datastore são inferidos com base nos dados de origem.

Por exemplo:

Digite os comandos a seguir para adicionar um arquivo local do Avro, /tmp/mydata.avro, a mydataset.mytable usando um job de carregamento. Como os esquemas podem ser inferidos automaticamente a partir dos dados do Avro, não é necessário usar a sinalização --autodetect. mydataset está no projeto padrão.

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_ADDITION \
--source_format=AVRO \
mydataset.mytable \
/tmp/mydata.avro

Digite o comando a seguir para anexar um arquivo de dados JSON delimitado por nova linha no Cloud Storage a mydataset.mytable usando um job de carregamento. A sinalização --autodetect é usada para detectar as novas colunas. mydataset está no projeto padrão.

bq load \
--noreplace \
--autodetect \
--schema_update_option=ALLOW_FIELD_ADDITION \
--source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable \
gs://mybucket/mydata.json

Digite o comando a seguir para anexar um arquivo de dados JSON delimitado por nova linha no Cloud Storage a mydataset.mytable usando um job de carregamento. O esquema contendo as novas colunas está especificado em um arquivo de esquema JSON local, /tmp/myschema.json. mydataset está em myotherproject, e não no projeto padrão.

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_ADDITION \
--source_format=NEWLINE_DELIMITED_JSON \
myotherproject:mydataset.mytable \
gs://mybucket/mydata.json \
/tmp/myschema.json

API

Chame o método jobs.insert. Em seguida, configure um job de load e defina as propriedades a seguir:

  • Faça referência aos dados no Cloud Storage usando a propriedade sourceUris.
  • Especifique o formato de dados definindo a propriedade sourceFormat.
  • Especifique o esquema na propriedade schema.
  • Especifique a opção de atualização de esquema usando a propriedade schemaUpdateOptions.
  • Defina a disposição de gravação da tabela de destino como WRITE_APPEND usando a propriedade writeDisposition.

Go

import (
	"context"
	"fmt"
	"os"

	"cloud.google.com/go/bigquery"
)

// createTableAndWidenLoad demonstrates augmenting a table's schema to add a new column via a load job.
func createTableAndWidenLoad(projectID, datasetID, tableID, filename string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType},
	}
	meta := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, meta); err != nil {
		return err
	}
	// Now, import data from a local file, but specify field additions are allowed.
	// Because the data has a second column (age), the schema is amended as part of
	// the load.
	f, err := os.Open(filename)
	if err != nil {
		return err
	}
	source := bigquery.NewReaderSource(f)
	source.AutoDetect = true   // Allow BigQuery to determine schema.
	source.SkipLeadingRows = 1 // CSV has a single header line.

	loader := client.Dataset(datasetID).Table(tableID).LoaderFrom(source)
	loader.SchemaUpdateOptions = []string{"ALLOW_FIELD_ADDITION"}
	job, err := loader.Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}
	if err := status.Err(); err != nil {
		return err
	}
	return nil
}

Java

Antes de testar esta amostra, siga as instruções de configuração do Java no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Java.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.FormatOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobId;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.JobInfo.SchemaUpdateOption;
import com.google.cloud.bigquery.JobInfo.WriteDisposition;
import com.google.cloud.bigquery.LegacySQLTypeName;
import com.google.cloud.bigquery.LoadJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.TableId;
import com.google.common.collect.ImmutableList;
import java.util.UUID;

public class AddColumnLoadAppend {

  public static void runAddColumnLoadAppend() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "/path/to/file.csv";
    addColumnLoadAppend(datasetName, tableName, sourceUri);
  }

  public static void addColumnLoadAppend(String datasetName, String tableName, String sourceUri)
      throws Exception {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, tableName);

      // Add a new column to a BigQuery table while appending rows via a load job.
      // 'REQUIRED' fields cannot  be added to an existing schema, so the additional column must be
      // 'NULLABLE'.
      Schema newSchema =
          Schema.of(
              Field.newBuilder("name", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.REQUIRED)
                  .build(),
              // Adding below additional column during the load job
              Field.newBuilder("post_abbr", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.NULLABLE)
                  .build());

      LoadJobConfiguration loadJobConfig =
          LoadJobConfiguration.builder(tableId, sourceUri)
              .setFormatOptions(FormatOptions.csv())
              .setWriteDisposition(WriteDisposition.WRITE_APPEND)
              .setSchema(newSchema)
              .setSchemaUpdateOptions(ImmutableList.of(SchemaUpdateOption.ALLOW_FIELD_ADDITION))
              .build();

      // Create a job ID so that we can safely retry.
      JobId jobId = JobId.of(UUID.randomUUID().toString());
      Job loadJob = bigquery.create(JobInfo.newBuilder(loadJobConfig).setJobId(jobId).build());

      // Load data from a GCS parquet file into the table
      // Blocks until this load table job completes its execution, either failing or succeeding.
      Job completedJob = loadJob.waitFor();

      // Check for errors
      if (completedJob == null) {
        throw new Exception("Job not executed since it no longer exists.");
      } else if (completedJob.getStatus().getError() != null) {
        // You can also look at queryJob.getStatus().getExecutionErrors() for all
        // errors, not just the latest one.
        throw new Exception(
            "BigQuery was unable to load into the table due to an error: \n"
                + loadJob.getStatus().getError());
      }
      System.out.println("Column successfully added during load append job");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Column not added during load append \n" + e.toString());
    }
  }
}

Node.js

Antes de testar esta amostra, siga as instruções de configuração do Node.js no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery Node.js.

// Import the Google Cloud client libraries
const {BigQuery} = require('@google-cloud/bigquery');

// Instantiate client
const bigquery = new BigQuery();

async function addColumnLoadAppend() {
  // Adds a new column to a BigQuery table while appending rows via a load job.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const fileName = '/path/to/file.csv';
  // const datasetId = 'my_dataset';
  // const tableId = 'my_table';

  // In this example, the existing table contains only the 'Name', 'Age',
  // & 'Weight' columns. 'REQUIRED' fields cannot  be added to an existing
  // schema, so the additional column must be 'NULLABLE'.
  const schema = 'Name:STRING, Age:INTEGER, Weight:FLOAT, IsMagic:BOOLEAN';

  // Retrieve destination table reference
  const [table] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .get();
  const destinationTableRef = table.metadata.tableReference;

  // Set load job options
  const options = {
    schema: schema,
    schemaUpdateOptions: ['ALLOW_FIELD_ADDITION'],
    writeDisposition: 'WRITE_APPEND',
    destinationTable: destinationTableRef,
  };

  // Load data from a local file into the table
  const [job] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .load(fileName, options);

  console.log(`Job ${job.id} completed.`);
  console.log(`New Schema:`);
  console.log(job.configuration.load.schema.fields);

  // Check the job's status for errors
  const errors = job.status.errors;
  if (errors && errors.length > 0) {
    throw errors;
  }
}

Python

Antes de testar esta amostra, siga as instruções de configuração do Python no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Python.

# from google.cloud import bigquery
# client = bigquery.Client()
# project = client.project
# dataset_ref = bigquery.DatasetReference(project, 'my_dataset')
# filepath = 'path/to/your_file.csv'

# Retrieves the destination table and checks the length of the schema
table_id = "my_table"
table_ref = dataset_ref.table(table_id)
table = client.get_table(table_ref)
print("Table {} contains {} columns.".format(table_id, len(table.schema)))

# Configures the load job to append the data to the destination table,
# allowing field addition
job_config = bigquery.LoadJobConfig()
job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND
job_config.schema_update_options = [
    bigquery.SchemaUpdateOption.ALLOW_FIELD_ADDITION
]
# In this example, the existing table contains only the 'full_name' column.
# 'REQUIRED' fields cannot be added to an existing schema, so the
# additional column must be 'NULLABLE'.
job_config.schema = [
    bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="NULLABLE"),
]
job_config.source_format = bigquery.SourceFormat.CSV
job_config.skip_leading_rows = 1

with open(filepath, "rb") as source_file:
    job = client.load_table_from_file(
        source_file,
        table_ref,
        location="US",  # Must match the destination dataset location.
        job_config=job_config,
    )  # API request

job.result()  # Waits for table load to complete.
print(
    "Loaded {} rows into {}:{}.".format(
        job.output_rows, dataset_id, table_ref.table_id
    )
)

# Checks the updated length of the schema
table = client.get_table(table)
print("Table {} now contains {} columns.".format(table_id, len(table.schema)))

Como adicionar uma coluna em um job de anexação de consulta

Use estes métodos para adicionar colunas a uma tabela e anexar os resultados da consulta a ela:

  • Use o comando bq query da ferramenta de linha de comando bq.
  • Chamada do método jobs.insert da API e configuração de um job de query
  • Como usar bibliotecas de cliente

O Console do Cloud não é compatível com a inclusão de uma coluna durante uma operação de anexação.

Ao adicionar colunas usando uma operação de anexação em um job de consulta, o esquema dos resultados dessa consulta é usado para atualizar o esquema da tabela de destino. Não é possível consultar uma tabela em um local e gravar os resultados em uma tabela de outro local.

Para adicionar uma nova coluna ao anexar dados a uma tabela durante um job de consulta:

Console

Não é possível adicionar novas colunas a uma tabela atual ao anexar resultados de consulta usando o Console do Cloud.

bq

Use o comando bq query para consultar seus dados e especifique a sinalização --destination_table para indicar qual tabela está sendo anexada.

Para mostrar que você está anexando resultados de consulta a uma tabela de destino atual, especifique a sinalização --append_table.

Defina a sinalização --schema_update_option como ALLOW_FIELD_ADDITION para indicar que os resultados da consulta sendo anexados contêm novas colunas.

Especifique a sinalização use_legacy_sql=false para usar a sintaxe SQL padrão para a consulta.

Se a tabela sendo anexada estiver em um conjunto de dados em um projeto diferente do padrão, adicione o ID do projeto ao nome do conjunto de dados no formato a seguir: project_id:dataset. A tabela sendo consultada e a tabela de destino precisam estar no mesmo local.

Opcional: forneça a sinalização --location e defina o valor do local.

bq --location=location query \
--destination_table project_id:dataset.table \
--append_table \
--schema_update_option=ALLOW_FIELD_ADDITION \
--use_legacy_sql=false \
'query'

Em que:

  • location é o nome do local. A sinalização --location é opcional. Por exemplo, se você estiver usando o BigQuery na região de Tóquio, defina o valor da sinalização como asia-northeast1. É possível definir um valor padrão para o local usando o arquivo .bigqueryrc. Não é possível anexar resultados de consulta em uma tabela de outro local.
  • project_id é o ID do projeto.
  • dataset é o nome do conjunto de dados que contém a tabela que você está anexando.
  • table é o nome da tabela que você está anexando.
  • query é uma consulta na sintaxe SQL padrão.

Por exemplo:

Digite o comando a seguir para consultar mydataset.mytable no projeto padrão e para anexar os resultados da consulta a mydataset.mytable2, que também está no projeto padrão.

bq query \
--destination_table mydataset.mytable2 \
--append_table \
--schema_update_option=ALLOW_FIELD_ADDITION \
--use_legacy_sql=false \
'SELECT
   column1,column2
 FROM
   mydataset.mytable'

Digite o comando a seguir para consultar mydataset.mytable no projeto padrão e para anexar os resultados da consulta a mydataset.mytable2 em myotherproject.

bq query \
--destination_table myotherproject:mydataset.mytable2 \
--append_table \
--schema_update_option=ALLOW_FIELD_ADDITION \
--use_legacy_sql=false \
'SELECT
   column1,column2
 FROM
   mydataset.mytable'

API

Chame o método jobs.insert. Em seguida, configure um job de query e defina as propriedades a seguir:

  • Especifique a tabela de destino usando a propriedade destinationTable.
  • Defina a disposição de gravação da tabela de destino como WRITE_APPEND usando a propriedade writeDisposition.
  • Especifique a opção de atualização de esquema usando a propriedade schemaUpdateOptions.
  • Especifique a consulta SQL padrão usando a propriedade query.

Go

import (
	"context"
	"fmt"

	"cloud.google.com/go/bigquery"
)

// createTableAndWidenQuery demonstrates how the schema of a table can be modified to add columns by appending
// query results that include the new columns.
func createTableAndWidenQuery(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	// First, we create a sample table.
	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType, Required: true},
		{Name: "age", Type: bigquery.IntegerFieldType, Required: true},
	}
	original := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, original); err != nil {
		return err
	}
	// Our table has two columns.  We'll introduce a new favorite_color column via
	// a subsequent query that appends to the table.
	q := client.Query("SELECT \"Timmy\" as full_name, 85 as age, \"Blue\" as favorite_color")
	q.SchemaUpdateOptions = []string{"ALLOW_FIELD_ADDITION"}
	q.QueryConfig.Dst = client.Dataset(datasetID).Table(tableID)
	q.WriteDisposition = bigquery.WriteAppend
	q.Location = "US"
	job, err := q.Run(ctx)
	if err != nil {
		return err
	}
	_, err = job.Wait(ctx)
	if err != nil {
		return err
	}
	return nil
}

Java

Antes de testar esta amostra, siga as instruções de configuração do Java no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Java.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.JobInfo.SchemaUpdateOption;
import com.google.cloud.bigquery.JobInfo.WriteDisposition;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableResult;
import com.google.common.collect.ImmutableList;

public class RelaxTableQuery {

  public static void runRelaxTableQuery() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    relaxTableQuery(projectId, datasetName, tableName);
  }

  // To relax all columns in a destination table when you append data to it during a query job
  public static void relaxTableQuery(String projectId, String datasetName, String tableName)
      throws Exception {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, tableName);

      String sourceTable = "`" + projectId + "." + datasetName + "." + tableName + "`";
      String query = "SELECT word FROM " + sourceTable + " WHERE word like '%is%'";

      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              // Use standard SQL syntax for queries.
              // See: https://cloud.google.com/bigquery/sql-reference/
              .setUseLegacySql(false)
              .setSchemaUpdateOptions(ImmutableList.of(SchemaUpdateOption.ALLOW_FIELD_RELAXATION))
              .setWriteDisposition(WriteDisposition.WRITE_APPEND)
              .setDestinationTable(tableId)
              .build();

      Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).build());

      queryJob = queryJob.waitFor();

      // Check for errors
      if (queryJob == null) {
        throw new Exception("Job no longer exists");
      } else if (queryJob.getStatus().getError() != null) {
        // You can also look at queryJob.getStatus().getExecutionErrors() for all
        // errors, not just the latest one.
        throw new Exception(queryJob.getStatus().getError().toString());
      }

      // Get the results.
      TableResult results = queryJob.getQueryResults();

      // Print all pages of the results.
      results
          .iterateAll()
          .forEach(
              rows -> {
                rows.forEach(row -> System.out.println("row: " + row.toString()));
              });

      System.out.println("Successfully relaxed all columns in destination table during query job");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Columns not relaxed during query job \n" + e.toString());
    }
  }
}

Node.js

Antes de testar esta amostra, siga as instruções de configuração do Node.js no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery Node.js.

// Import the Google Cloud client libraries
const {BigQuery} = require('@google-cloud/bigquery');

// Instantiate client
const bigquery = new BigQuery();

async function addColumnQueryAppend() {
  // Adds a new column to a BigQuery table while appending rows via a query job.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = 'my_dataset';
  // const tableId = 'my_table';

  // Retrieve destination table reference
  const [table] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .get();
  const destinationTableRef = table.metadata.tableReference;

  // In this example, the existing table contains only the 'name' column.
  // 'REQUIRED' fields cannot  be added to an existing schema,
  // so the additional column must be 'NULLABLE'.
  const query = `SELECT name, year
    FROM \`bigquery-public-data.usa_names.usa_1910_2013\`
    WHERE state = 'TX'
    LIMIT 10`;

  // Set load job options
  const options = {
    query: query,
    schemaUpdateOptions: ['ALLOW_FIELD_ADDITION'],
    writeDisposition: 'WRITE_APPEND',
    destinationTable: destinationTableRef,
    // Location must match that of the dataset(s) referenced in the query.
    location: 'US',
  };

  const [job] = await bigquery.createQueryJob(options);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();
  console.log(`Job ${job.id} completed.`);

  // Print the results
  console.log('Rows:');
  rows.forEach(row => console.log(row));
}

Python

Antes de testar esta amostra, siga as instruções de configuração do Python no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Python.

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the destination table.
# table_id = "your-project.your_dataset.your_table_name"

# Retrieves the destination table and checks the length of the schema.
table = client.get_table(table_id)  # Make an API request.
print("Table {} contains {} columns".format(table_id, len(table.schema)))

# Configures the query to append the results to a destination table,
# allowing field addition.
job_config = bigquery.QueryJobConfig(
    destination=table_id,
    schema_update_options=[bigquery.SchemaUpdateOption.ALLOW_FIELD_ADDITION],
    write_disposition=bigquery.WriteDisposition.WRITE_APPEND,
)

# Start the query, passing in the extra configuration.
query_job = client.query(
    # In this example, the existing table contains only the 'full_name' and
    # 'age' columns, while the results of this query will contain an
    # additional 'favorite_color' column.
    'SELECT "Timmy" as full_name, 85 as age, "Blue" as favorite_color;',
    job_config=job_config,
)  # Make an API request.
query_job.result()  # Wait for the job to complete.

# Checks the updated length of the schema.
table = client.get_table(table_id)  # Make an API request.
print("Table {} now contains {} columns".format(table_id, len(table.schema)))

Como excluir colunas da definição de esquema de uma tabela

É possível excluir colunas da definição de esquema de uma tabela atual manualmente.

Excluir manualmente uma coluna

É possível excluir uma coluna de uma tabela atual usando a instrução ALTER TABLE DROP COLUMN da linguagem de definição de dados (DDL, na sigla em inglês).

Como relaxar o modo de uma 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. E possível relaxar colunas REQUIRED:

  • manualmente;
  • ao substituir uma tabela usando um job de carregamento ou de consulta;
  • ao anexar dados a uma tabela usando um job de consulta.

Como alterar manualmente colunas REQUIRED para NULLABLE

Use estes métodos para alterar manualmente o modo de uma coluna de REQUIRED para NULLABLE:

  • Como usar o Console do Cloud
  • Use o comando bq update da ferramenta de linha de comando bq.
  • Chame o tables.patch método de API
  • use bibliotecas de cliente.

Para alterar manualmente o modo de uma coluna de REQUIRED para NULLABLE:

Console

  1. No Console do Cloud, acesse a página do BigQuery.

    Ir para o BigQuery

  2. No painel Explorer, expanda o projeto e o conjunto de dados e selecione a tabela.

  3. No painel de detalhes, clique na guia Visualizar.

  4. Clique em Editar esquema. Talvez seja necessário rolar para ver esse botão.

  5. Na página Esquema atual, localize o campo que você quer alterar.

  6. Na lista suspensa Modo para esse campo, selecione NULLABLE.

  7. Clique em Save.

bq

  1. Primeiro, emita o comando bq show com a sinalização --schema e grave o esquema da tabela atual em um arquivo. Se a tabela que você está atualizando estiver em um projeto diferente do padrão, adicione o ID do projeto ao nome do conjunto de dados neste formato: project_id:dataset.

    bq show \
    --schema \
    --format=prettyjson \
    project_id:dataset.table > schema_file
    

    Em que:

    • project_id é o ID do projeto.
    • dataset é o nome do conjunto de dados que contém a tabela que você está atualizando;
    • table é o nome da tabela que você está atualizando.
    • schema_file é o arquivo de definição de esquema gravado na máquina local.

    Por exemplo, para gravar a definição de esquema de mydataset.mytable em um arquivo, digite o comando a seguir. mydataset.mytable está no projeto padrão.

      bq show \
      --schema \
      --format=prettyjson \
      mydataset.mytable > /tmp/myschema.json
    
  2. Abra o arquivo de esquema em um editor de texto. Ele será semelhante a este:

    [
      {
        "mode": "REQUIRED",
        "name": "column1",
        "type": "STRING"
      },
      {
        "mode": "REQUIRED",
        "name": "column2",
        "type": "FLOAT"
      },
      {
        "mode": "REPEATED",
        "name": "column3",
        "type": "STRING"
      }
    ]
    
  3. Altere o modo de uma coluna atual de REQUIRED para NULLABLE. Neste exemplo, o modo de column1 é relaxado.

    [
      {
        "mode": "NULLABLE",
        "name": "column1",
        "type": "STRING"
      },
      {
        "mode": "REQUIRED",
        "name": "column2",
        "type": "FLOAT"
      },
      {
        "mode": "REPEATED",
        "name": "column3",
        "type": "STRING"
      }
    ]
    

    Para mais informações sobre como trabalhar com arquivos de esquema JSON, consulte Como especificar um arquivo de esquema JSON.

  4. Depois de atualizar o arquivo de esquema, emita o comando a seguir para atualizar o esquema da tabela. Se a tabela que você está atualizando estiver em um projeto diferente do padrão, adicione o ID do projeto ao nome do conjunto de dados neste formato: project_id:dataset.

    bq update project_id:dataset.table schema
    

    Em que:

    • project_id é o ID do projeto.
    • dataset é o nome do conjunto de dados que contém a tabela que você está atualizando;
    • table é o nome da tabela que você está atualizando.
    • schema é o caminho para o arquivo de esquema JSON na sua máquina local.

    Por exemplo, digite o comando a seguir para atualizar a definição de esquema de mydataset.mytable no projeto padrão. O caminho para o arquivo de esquema na máquina local é /tmp/myschema.json.

      bq update mydataset.mytable /tmp/myschema.json
    

API

Chame tables.patch e use a propriedade schema para alterar uma coluna REQUIRED para NULLABLE na definição do esquema. Como o método tables.update substitui todo o recurso da tabela, é melhor usar o método tables.patch.

Go

import (
	"context"
	"fmt"

	"cloud.google.com/go/bigquery"
)

// relaxTableAPI demonstrates modifying the schema of a table to remove the requirement that columns allow
// no NULL values.
func relaxTableAPI(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydatasetid"
	// tableID := "mytableid"
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	// Setup: We first create a table with a schema that's restricts NULL values.
	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType, Required: true},
		{Name: "age", Type: bigquery.IntegerFieldType, Required: true},
	}
	original := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	if err := client.Dataset(datasetID).Table(tableID).Create(ctx, original); err != nil {
		return err
	}

	tableRef := client.Dataset(datasetID).Table(tableID)
	meta, err := tableRef.Metadata(ctx)
	if err != nil {
		return err
	}
	// Iterate through the schema to set all Required fields to false (nullable).
	var relaxed bigquery.Schema
	for _, v := range meta.Schema {
		v.Required = false
		relaxed = append(relaxed, v)
	}
	newMeta := bigquery.TableMetadataToUpdate{
		Schema: relaxed,
	}
	if _, err := tableRef.Update(ctx, newMeta, meta.ETag); err != nil {
		return err
	}
	return nil
}

Java

Antes de testar esta amostra, siga as instruções de configuração do Java no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Java.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.LegacySQLTypeName;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.Table;

public class RelaxColumnMode {

  public static void runRelaxColumnMode() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableId = "MY_TABLE_NAME";
    relaxColumnMode(datasetName, tableId);
  }

  public static void relaxColumnMode(String datasetName, String tableId) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      Table table = bigquery.getTable(datasetName, tableId);

      // Create new relaxed schema based on the existing table schema
      Schema relaxedSchema =
          Schema.of(
              // The only supported modification you can make to a column's mode is changing it from
              // REQUIRED to NULLABLE
              // Changing a column's mode from REQUIRED to NULLABLE is also called column relaxation
              // INFO: LegacySQLTypeName will be updated to StandardSQLTypeName in release 1.103.0
              Field.newBuilder("word", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.NULLABLE)
                  .build(),
              Field.newBuilder("word_count", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.NULLABLE)
                  .build(),
              Field.newBuilder("corpus", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.NULLABLE)
                  .build(),
              Field.newBuilder("corpus_date", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.NULLABLE)
                  .build());

      // Update the table with the new schema
      Table updatedTable =
          table.toBuilder().setDefinition(StandardTableDefinition.of(relaxedSchema)).build();
      updatedTable.update();
      System.out.println("Table schema successfully relaxed.");
    } catch (BigQueryException e) {
      System.out.println("Table schema not relaxed \n" + e.toString());
    }
  }
}

Node.js

Antes de testar esta amostra, siga as instruções de configuração do Node.js no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery Node.js.

// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function relaxColumn() {
  /**
   * Changes columns from required to nullable.
   * Assumes existing table with the following schema:
   * [{name: 'Name', type: 'STRING', mode: 'REQUIRED'},
   * {name: 'Age', type: 'INTEGER'},
   * {name: 'Weight', type: 'FLOAT'},
   * {name: 'IsMagic', type: 'BOOLEAN'}];
   */

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = 'my_dataset';
  // const tableId = 'my_table';

  const newSchema = [
    {name: 'Name', type: 'STRING', mode: 'NULLABLE'},
    {name: 'Age', type: 'INTEGER'},
    {name: 'Weight', type: 'FLOAT'},
    {name: 'IsMagic', type: 'BOOLEAN'},
  ];

  // Retrieve current table metadata
  const table = bigquery.dataset(datasetId).table(tableId);
  const [metadata] = await table.getMetadata();

  // Update schema
  metadata.schema = newSchema;
  const [apiResponse] = await table.setMetadata(metadata);

  console.log(apiResponse.schema.fields);
}

Python

Antes de testar esta amostra, siga as instruções de configuração do Python no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Python.

Substitua a propriedade Table.schema por uma lista de objetosSchemaField com a propriedade mode definida como 'NULLABLE'

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'my_dataset'
# table_id = 'my_table'

original_schema = [
    bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="REQUIRED"),
]

dataset_ref = bigquery.DatasetReference(project, dataset_id)
table_ref = dataset_ref.table(table_id)
table = bigquery.Table(table_ref, schema=original_schema)
table = client.create_table(table)
assert all(field.mode == "REQUIRED" for field in table.schema)

# SchemaField properties cannot be edited after initialization.
# To make changes, construct new SchemaField objects.
relaxed_schema = [
    bigquery.SchemaField("full_name", "STRING", mode="NULLABLE"),
    bigquery.SchemaField("age", "INTEGER", mode="NULLABLE"),
]
table.schema = relaxed_schema
table = client.update_table(table, ["schema"])

assert all(field.mode == "NULLABLE" for field in table.schema)

Como mudar REQUIRED para NULLABLE em um job de carregamento ou de consulta

É possível relaxar colunas REQUIRED para NULLABLE no esquema de uma tabela atual ao carregar dados nela e optar por substituí-la. Ao substituir uma tabela atual, o esquema dos dados sendo carregados é usado para substituir o esquema dessa tabela. Para informações sobre como substituir uma tabela usando um job de carregamento, consulte estes tópicos:

Também é possível relaxar as colunas REQUIRED para NULLABLE no esquema de uma tabela atual ao anexar dados a ela usando um job de consulta.

Como mudar REQUIRED para NULLABLE em um job de anexação de carregamento

Use estes métodos para relaxar o modo de uma coluna e anexar dados a uma tabela em um job de carregamento:

O Console do Cloud não é compatível com a alteração do modo de uma coluna durante uma operação de anexação.

Ao relaxar o modo de uma coluna usando uma operação de anexação em um job de carregamento, é possível:

  • relaxar o modo de colunas individuais especificando um arquivo de esquema JSON, ao anexar dados de arquivos CSV e JSON;
  • relaxar colunas para null no esquema do Avro, do ORC ou do Parquet e permitir a inferência de esquema para detectar as colunas relaxadas.

Para relaxar uma coluna de REQUIRED para NULLABLE ao anexar dados a uma tabela durante um job de carregamento, siga estas etapas:

Console

Ainda não é possível transformar o modo de coluna usando o Console do Cloud.

bq

Use o comando bq load para carregar os dados e especifique a sinalização --noreplace para indicar que você os está anexando a uma tabela atual.

Se os dados que você anexar estiverem em CSV ou em formato JSON delimitado por nova linha, especifique as colunas de relaxamento em um arquivo de esquema JSON local. Se preferir, use a sinalização --autodetect para utilizar a detecção de esquemas e descobrir colunas de relaxamento nos dados de origem. Para mais informações sobre como relaxar os modos de colunas usando um arquivo de esquema JSON, consulte Como alterar manualmente as colunas REQUIRED para NULLABLE.

As colunas de relaxamento podem ser inferidas automaticamente de arquivos Avro, ORC e Parquet. O relaxamento de coluna não se aplica a anexos de exportação do Cloud Datastore. As colunas em tabelas criadas durante o carregamento de arquivos de exportação do Datastore são sempre NULLABLE.

Defina a sinalização --schema_update_option como ALLOW_FIELD_RELAXATION para indicar que os dados sendo anexados contêm colunas relaxadas.

Se a tabela sendo anexada estiver em um conjunto de dados em um projeto diferente do padrão, adicione o ID do projeto ao nome do conjunto de dados no formato a seguir: project_id:dataset.

Opcional: forneça a sinalização --location e defina o valor do local.

Digite o comando load a seguir:

bq --location=location load \
--noreplace \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--source_format=format \
project_id:dataset.table \
path_to_source \
schema

Em que:

  • location é o nome do local. A sinalização --location é opcional. Por exemplo, se você estiver usando o BigQuery na região de Tóquio, defina o valor da sinalização como asia-northeast1. É possível definir um valor padrão para o local usando o arquivo .bigqueryrc.
  • format é NEWLINE_DELIMITED_JSON, CSV, PARQUET, ORC ou AVRO Os arquivos DATASTORE_BACKUP não exigem relaxamento da coluna. As colunas em tabelas criadas de arquivos de exportação do Datastore são sempre NULLABLE.
  • project_id é o ID do projeto.
  • dataset é o nome do conjunto de dados que contém a tabela.
  • table é o nome da tabela que você está anexando.
  • path_to_source é um URI do Cloud Storage completo, uma lista de URIs separada por vírgula ou o caminho para um arquivo de dados na máquina local.
  • schema é o caminho para um arquivo de esquema JSON local. Esta opção é usada apenas para arquivos CSV e JSON. As colunas relaxadas são inferidas automaticamente de arquivos Avro.

Por exemplo:

Digite os comandos a seguir para adicionar um arquivo local do Avro, /tmp/mydata.avro, a mydataset.mytable usando um job de carregamento. Como as colunas relaxadas podem ser automaticamente inferidas dos dados do Avro, não é necessário especificar um arquivo de esquema. mydataset está no projeto padrão.

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--source_format=AVRO \
mydataset.mytable \
/tmp/mydata.avro

Digite o comando a seguir para anexar dados de um arquivo JSON delimitado por nova linha no Cloud Storage a mydataset.mytable usando um job de carregamento. O esquema contendo as colunas relaxadas está em um arquivo de esquema JSON local — /tmp/myschema.json. mydataset está no projeto padrão.

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable \
gs://mybucket/mydata.json \
/tmp/myschema.json

Digite o comando a seguir para anexar dados em um arquivo CSV na máquina local a mydataset.mytable usando um job de carregamento. O comando usa a detecção automática de esquema para descobrir colunas relaxadas nos dados de origem. mydataset está em myotherproject, e não no projeto padrão.

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--source_format=CSV \
--autodetect \
myotherproject:mydataset.mytable \
mydata.csv

API

Chame o método jobs.insert. Em seguida, configure um job de load e defina as propriedades a seguir:

  • Faça referência aos dados no Cloud Storage usando a propriedade sourceUris.
  • Especifique o formato de dados definindo a propriedade sourceFormat.
  • Especifique o esquema na propriedade schema.
  • Especifique a opção de atualização de esquema usando a propriedade schemaUpdateOptions.
  • Defina a disposição de gravação da tabela de destino como WRITE_APPEND usando a propriedade writeDisposition.

Go

import (
	"context"
	"fmt"
	"os"

	"cloud.google.com/go/bigquery"
)

// relaxTableImport demonstrates amending the schema of a table to relax columns from
// not allowing NULL values to allowing them.
func relaxTableImport(projectID, datasetID, tableID, filename string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType, Required: true},
		{Name: "age", Type: bigquery.IntegerFieldType, Required: true},
	}
	meta := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, meta); err != nil {
		return err
	}
	// Now, import data from a local file, but specify relaxation of required
	// fields as a side effect while the data is appended.
	f, err := os.Open(filename)
	if err != nil {
		return err
	}
	source := bigquery.NewReaderSource(f)
	source.AutoDetect = true   // Allow BigQuery to determine schema.
	source.SkipLeadingRows = 1 // CSV has a single header line.

	loader := client.Dataset(datasetID).Table(tableID).LoaderFrom(source)
	loader.SchemaUpdateOptions = []string{"ALLOW_FIELD_RELAXATION"}
	job, err := loader.Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}
	if err := status.Err(); err != nil {
		return err
	}
	return nil
}

Java

Antes de testar esta amostra, siga as instruções de configuração do Java no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Java.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.CsvOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.LoadJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.Table;
import com.google.cloud.bigquery.TableId;
import com.google.common.collect.ImmutableList;

// Sample to append relax column in a table.
public class RelaxColumnLoadAppend {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.csv";
    relaxColumnLoadAppend(datasetName, tableName, sourceUri);
  }

  public static void relaxColumnLoadAppend(String datasetName, String tableName, String sourceUri) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      // Retrieve destination table reference
      Table table = bigquery.getTable(TableId.of(datasetName, tableName));

      // column as a 'REQUIRED' field.
      Field name =
          Field.newBuilder("name", StandardSQLTypeName.STRING).setMode(Field.Mode.REQUIRED).build();
      Field postAbbr =
          Field.newBuilder("post_abbr", StandardSQLTypeName.STRING)
              .setMode(Field.Mode.REQUIRED)
              .build();
      Schema schema = Schema.of(name, postAbbr);

      // Skip header row in the file.
      CsvOptions csvOptions = CsvOptions.newBuilder().setSkipLeadingRows(1).build();

      // Set job options
      LoadJobConfiguration loadConfig =
          LoadJobConfiguration.newBuilder(table.getTableId(), sourceUri)
              .setSchema(schema)
              .setFormatOptions(csvOptions)
              .setSchemaUpdateOptions(
                  ImmutableList.of(JobInfo.SchemaUpdateOption.ALLOW_FIELD_RELAXATION))
              .setWriteDisposition(JobInfo.WriteDisposition.WRITE_APPEND)
              .build();

      // Create a load job and wait for it to complete.
      Job job = bigquery.create(JobInfo.of(loadConfig));
      job = job.waitFor();
      // Check the job's status for errors
      if (job.isDone() && job.getStatus().getError() == null) {
        System.out.println("Relax column append successfully loaded in a table");
      } else {
        System.out.println(
            "BigQuery was unable to load into the table due to an error:"
                + job.getStatus().getError());
      }
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Column not added during load append \n" + e.toString());
    }
  }
}

Node.js

Antes de testar esta amostra, siga as instruções de configuração do Node.js no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery Node.js.

// Import the Google Cloud client libraries
const {BigQuery} = require('@google-cloud/bigquery');

// Instantiate client
const bigquery = new BigQuery();

async function relaxColumnLoadAppend() {
  // Changes required column to nullable in load append job.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const fileName = '/path/to/file.csv';
  // const datasetId = 'my_dataset';
  // const tableId = 'my_table';

  // In this example, the existing table contains the 'Name'
  // column as a 'REQUIRED' field.
  const schema = 'Age:INTEGER, Weight:FLOAT, IsMagic:BOOLEAN';

  // Retrieve destination table reference
  const [table] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .get();
  const destinationTableRef = table.metadata.tableReference;

  // Set load job options
  const options = {
    schema: schema,
    schemaUpdateOptions: ['ALLOW_FIELD_RELAXATION'],
    writeDisposition: 'WRITE_APPEND',
    destinationTable: destinationTableRef,
  };

  // Load data from a local file into the table
  const [job] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .load(fileName, options);

  console.log(`Job ${job.id} completed.`);

  // Check the job's status for errors
  const errors = job.status.errors;
  if (errors && errors.length > 0) {
    throw errors;
  }
}

Python

Antes de testar esta amostra, siga as instruções de configuração do Python no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Python.

# from google.cloud import bigquery
# client = bigquery.Client()
# project = client.project
# dataset_ref = bigquery.DatasetReference(project, 'my_dataset')
# filepath = 'path/to/your_file.csv'

# Retrieves the destination table and checks the number of required fields
table_id = "my_table"
table_ref = dataset_ref.table(table_id)
table = client.get_table(table_ref)
original_required_fields = sum(field.mode == "REQUIRED" for field in table.schema)
# In this example, the existing table has 3 required fields.
print("{} fields in the schema are required.".format(original_required_fields))

# Configures the load job to append the data to a destination table,
# allowing field relaxation
job_config = bigquery.LoadJobConfig()
job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND
job_config.schema_update_options = [
    bigquery.SchemaUpdateOption.ALLOW_FIELD_RELAXATION
]
# In this example, the existing table contains three required fields
# ('full_name', 'age', and 'favorite_color'), while the data to load
# contains only the first two fields.
job_config.schema = [
    bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="REQUIRED"),
]
job_config.source_format = bigquery.SourceFormat.CSV
job_config.skip_leading_rows = 1

with open(filepath, "rb") as source_file:
    job = client.load_table_from_file(
        source_file,
        table_ref,
        location="US",  # Must match the destination dataset location.
        job_config=job_config,
    )  # API request

job.result()  # Waits for table load to complete.
print(
    "Loaded {} rows into {}:{}.".format(
        job.output_rows, dataset_id, table_ref.table_id
    )
)

# Checks the updated number of required fields
table = client.get_table(table)
current_required_fields = sum(field.mode == "REQUIRED" for field in table.schema)
print("{} fields in the schema are now required.".format(current_required_fields))

Como mudar REQUIRED para NULLABLE em um job de anexação de consulta

Use estes métodos para relaxar todas as colunas de uma tabela e anexar os resultados da consulta a ela:

  • Use o comando bq query da ferramenta de linha de comando bq.
  • Chamada do método jobs.insert da API e configuração de um job de consulta
  • Como usar bibliotecas de cliente

O Console do Cloud não é compatível com a transformação de colunas durante uma operação de anexação.

Ao transformar colunas usando uma operação de anexação em um job de consulta, defina a sinalização --schema_update_option como ALLOW_FIELD_RELAXATION para transformar todos os campos obrigatórios na tabela de destino. Não é possível relaxar colunas individuais em uma tabela de destino usando um anexo de consulta.

Para relaxar todas as colunas em uma tabela de destino ao anexar dados a ela durante um job de consulta, siga estas etapas:

Console

Ainda não é possível transformar o modo de coluna usando o Console do Cloud.

bq

Use o comando bq query para consultar seus dados e especifique a sinalização --destination_table para indicar qual tabela está sendo anexada.

Para mostrar que você está anexando resultados de consulta a uma tabela de destino atual, especifique a sinalização --append_table.

Defina a sinalização --schema_update_option como ALLOW_FIELD_RELAXATION para indicar que todas as colunas REQUIRED na tabela sendo anexada precisam ser alteradas para NULLABLE.

Especifique a sinalização use_legacy_sql=false para usar a sintaxe SQL padrão para a consulta.

Se a tabela sendo anexada estiver em um conjunto de dados em um projeto diferente do padrão, adicione o ID do projeto ao nome do conjunto de dados no formato a seguir: project_id:dataset.

Opcional: forneça a sinalização --location e defina o valor do local.

bq --location=location query \
--destination_table project_id:dataset.table \
--append_table \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--use_legacy_sql=false \
'query'

Em que:

  • location é o nome do local. A sinalização --location é opcional. Por exemplo, se você estiver usando o BigQuery na região de Tóquio, defina o valor da sinalização como asia-northeast1. É possível definir um valor padrão para o local usando o arquivo .bigqueryrc.
  • project_id é o ID do projeto.
  • dataset é o nome do conjunto de dados que contém a tabela que você está anexando.
  • table é o nome da tabela que você está anexando.
  • query é uma consulta na sintaxe SQL padrão.

Por exemplo:

Digite o comando a seguir para consultar mydataset.mytable no projeto padrão e para anexar os resultados da consulta a mydataset.mytable2, que também está no projeto padrão. Esse comando altera todas as colunas REQUIRED na tabela de destino para NULLABLE.

bq query \
--destination_table mydataset.mytable2 \
--append_table \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--use_legacy_sql=false \
'SELECT
   column1,column2
 FROM
   mydataset.mytable'

Digite o comando a seguir para consultar mydataset.mytable no projeto padrão e para anexar os resultados da consulta a mydataset.mytable2 em myotherproject. Esse comando altera todas as colunas REQUIRED na tabela de destino para NULLABLE.

bq query \
--destination_table myotherproject:mydataset.mytable2 \
--append_table \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--use_legacy_sql=false \
'SELECT
   column1,column2
 FROM
   mydataset.mytable'

API

Chame o método jobs.insert. Em seguida, configure um job de query e defina as propriedades a seguir:

  • Especifique a tabela de destino usando a propriedade destinationTable.
  • Defina a disposição de gravação da tabela de destino como WRITE_APPEND usando a propriedade writeDisposition.
  • Especifique a opção de atualização de esquema usando a propriedade schemaUpdateOptions.
  • Especifique a consulta SQL padrão usando a propriedade query.

Go

import (
	"context"
	"fmt"

	"cloud.google.com/go/bigquery"
)

// relaxTableQuery demonstrates relaxing the schema of a table by appending query results to
// enable the table to allow NULL values.
func relaxTableQuery(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType, Required: true},
		{Name: "age", Type: bigquery.IntegerFieldType, Required: true},
	}
	meta := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, meta); err != nil {
		return err
	}
	// Now, append a query result that includes nulls, but allow the job to relax
	// all required columns.
	q := client.Query("SELECT \"Beyonce\" as full_name")
	q.QueryConfig.Dst = client.Dataset(datasetID).Table(tableID)
	q.SchemaUpdateOptions = []string{"ALLOW_FIELD_RELAXATION"}
	q.WriteDisposition = bigquery.WriteAppend
	q.Location = "US"
	job, err := q.Run(ctx)
	if err != nil {
		return err
	}
	_, err = job.Wait(ctx)
	if err != nil {
		return err
	}
	return nil
}

Java

Antes de testar esta amostra, siga as instruções de configuração do Java no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Java.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.JobInfo.SchemaUpdateOption;
import com.google.cloud.bigquery.JobInfo.WriteDisposition;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableResult;
import com.google.common.collect.ImmutableList;

public class RelaxTableQuery {

  public static void runRelaxTableQuery() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    relaxTableQuery(projectId, datasetName, tableName);
  }

  // To relax all columns in a destination table when you append data to it during a query job
  public static void relaxTableQuery(String projectId, String datasetName, String tableName)
      throws Exception {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, tableName);

      String sourceTable = "`" + projectId + "." + datasetName + "." + tableName + "`";
      String query = "SELECT word FROM " + sourceTable + " WHERE word like '%is%'";

      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              // Use standard SQL syntax for queries.
              // See: https://cloud.google.com/bigquery/sql-reference/
              .setUseLegacySql(false)
              .setSchemaUpdateOptions(ImmutableList.of(SchemaUpdateOption.ALLOW_FIELD_RELAXATION))
              .setWriteDisposition(WriteDisposition.WRITE_APPEND)
              .setDestinationTable(tableId)
              .build();

      Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).build());

      queryJob = queryJob.waitFor();

      // Check for errors
      if (queryJob == null) {
        throw new Exception("Job no longer exists");
      } else if (queryJob.getStatus().getError() != null) {
        // You can also look at queryJob.getStatus().getExecutionErrors() for all
        // errors, not just the latest one.
        throw new Exception(queryJob.getStatus().getError().toString());
      }

      // Get the results.
      TableResult results = queryJob.getQueryResults();

      // Print all pages of the results.
      results
          .iterateAll()
          .forEach(
              rows -> {
                rows.forEach(row -> System.out.println("row: " + row.toString()));
              });

      System.out.println("Successfully relaxed all columns in destination table during query job");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Columns not relaxed during query job \n" + e.toString());
    }
  }
}

Python

Antes de testar esta amostra, siga as instruções de configuração do Python no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Python.

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the destination table.
# table_id = "your-project.your_dataset.your_table_name"

# Retrieves the destination table and checks the number of required fields.
table = client.get_table(table_id)  # Make an API request.
original_required_fields = sum(field.mode == "REQUIRED" for field in table.schema)

# In this example, the existing table has 2 required fields.
print("{} fields in the schema are required.".format(original_required_fields))

# Configures the query to append the results to a destination table,
# allowing field relaxation.
job_config = bigquery.QueryJobConfig(
    destination=table_id,
    schema_update_options=[bigquery.SchemaUpdateOption.ALLOW_FIELD_RELAXATION],
    write_disposition=bigquery.WriteDisposition.WRITE_APPEND,
)

# Start the query, passing in the extra configuration.
query_job = client.query(
    # In this example, the existing table contains 'full_name' and 'age' as
    # required columns, but the query results will omit the second column.
    'SELECT "Beyonce" as full_name;',
    job_config=job_config,
)  # Make an API request.
query_job.result()  # Wait for the job to complete.

# Checks the updated number of required fields.
table = client.get_table(table_id)  # Make an API request.
current_required_fields = sum(field.mode == "REQUIRED" for field in table.schema)
print("{} fields in the schema are now required.".format(current_required_fields))

Segurança de tabelas

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