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.
  • 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;
  • 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 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;
  • ao anexar dados a uma tabela usando um job de carregamento ou de consulta.

Qualquer coluna adicionada 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:

  • Use o Console do Cloud ou a IU da Web clássica do BigQuery.
  • Como usar o comando bq update da ferramenta de linha de comando.
  • Chamada do método de API tables.patch
  • Use 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. Ao tentar adicionar uma coluna REQUIRED a um esquema de tabela atual na CLI ou na API, o erro a seguir 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 só podem ser adicionadas com a criação de uma tabela ao carregar dados ou com a criação de 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 painel Recursos, selecione sua tabela.

  2. Abaixo do Editor de consultas, role até a parte inferior da seção Esquema e clique em Editar esquema.

Editar esquema de tabela

  1. Role até a parte inferior do painel aberto e clique em Adicionar campo.

    • Em Nome, digite o nome da coluna.
    • Em Tipo, escolha o tipo de dados.
    • Em Modo, escolha NULLABLE ou REPEATED.

  2. Ao terminar de adicionar colunas, clique em Salvar.

IU clássica

  1. No painel de navegação, selecione a tabela.

  2. Na página Detalhes da tabela, clique em Adicionar novos campos.

  3. Na seção Novos campos:

    • Em Nome, digite o nome da coluna.
    • Em Tipo, escolha o tipo de dados.

    • Em Modo, escolha NULLABLE ou REPEATED.

      Atualizar esquema de tabela

  4. Ao terminar de adicionar colunas, clique em Adicionar à tabela.

CLI

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 sendo atualizada;
  • 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 na linha de comando, não há como 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 melhor método para adicionar colunas a uma tabela atual usando a CLI é 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 sendo atualizada;
    • 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 sendo atualizada;
    • 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)
	}

	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
}

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 do BigQuery para 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 Python.

Anexe um novo objeto SchemaField a uma cópia do Table.schema e substitua o valor da propriedade Table.schema pelo esquema atualizado (páginas em inglês).
from google.cloud import bigquery

# TODO(developer): 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.

IU clássica

Atualmente, não é possível adicionar um novo campo aninhado a uma coluna RECORD na IU da Web clássica do BigQuery.

CLI

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 sendo atualizada;
  • 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 na linha de comando, não há como 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 sendo atualizada;
    • 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 sendo atualizada;
    • 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.
  • Chamada do método jobs.insert da API e configuração de um job de load
  • Use bibliotecas de cliente

O Console do Cloud e a IU da Web clássica do BigQuery ainda não são compatíveis 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.

IU clássica

Não é possível adicionar novas colunas a uma tabela atual ao carregar dados usando a IU da Web clássica do BigQuery.

CLI

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 para seu 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 sendo anexada;
  • path_to_source é um URI totalmente qualificado do Cloud Storage, uma lista de URIs separada por vírgulas 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.

Exemplos:

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

	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
}

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 do BigQuery para 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 Python. 

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_ref = client.dataset('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.
  • Chamada do método jobs.insert da API e configuração de um job de query
  • Use bibliotecas de cliente

O Console do Cloud e a IU da Web clássica do BigQuery ainda não são compatíveis 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.

IU clássica

Não é possível adicionar novas colunas a uma tabela atual quando você anexa resultados da consulta usando a IU da Web clássica do BigQuery.

CLI

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 para seu 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 sendo anexada;
  • table é o nome da tabela sendo anexada;
  • query é uma consulta na sintaxe SQL padrão.

Exemplos:

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

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

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 do BigQuery para 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 Python. 

from google.cloud import bigquery

# TODO(developer): 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)
job_config.schema_update_options = [
    bigquery.SchemaUpdateOption.ALLOW_FIELD_ADDITION
]
job_config.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 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. É 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:

  • Use o Console do Cloud ou a IU da Web clássica do BigQuery.
  • Como usar o comando bq update da ferramenta de linha de comando.
  • Chamada do método de API tables.patch
  • Use bibliotecas de cliente

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

Console

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

IU clássica

  1. Expanda o conjunto de dados e selecione a tabela.

  2. Na página Detalhes da tabela, clique na guia Esquema.

  3. Clique na seta para baixo à direita da coluna REQUIRED e escolha entre:

    • Tornar NULLABLE: relaxa o modo da coluna individual.

    • Todas as REQUIRED como NULLABLE: altera todas as colunas REQUIRED na definição de esquema para NULLABLE.

      Relaxar modo de coluna

  4. Na caixa de diálogo Confirmar alteração do modo, clique em OK para alterar o modo para NULLABLE. Essa operação não pode ser desfeita.

CLI

  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 sendo atualizada;
    • 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 sendo atualizada;
    • 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)
	}

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

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 do BigQuery para 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 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"),
]
table_ref = client.dataset(dataset_id).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 e a IU da Web clássica do BigQuery ainda não são compatíveis 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 relaxar o modo de coluna usando o Console do Cloud.

IU clássica

Não é possível relaxar o modo de uma coluna atual quando você anexa dados a uma tabela em um job de carregamento usando a IU da Web clássica do BigQuery.

CLI

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 para seu 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 a partir 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 sendo anexada;
  • path_to_source é um URI totalmente qualificado do Cloud Storage, uma lista de URIs separada por vírgulas 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.

Exemplos:

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

	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
}

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 do BigQuery para 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 Python. 

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_ref = client.dataset('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:

O Console do Cloud e a IU da Web clássica do BigQuery ainda não são compatíveis com o relaxamento de colunas durante uma operação de anexação.

Ao relaxar 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 relaxar 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 relaxar o modo de coluna usando o Console do Cloud.

IU clássica

Não é possível relaxar colunas em uma tabela de destino quando você anexa resultados da consulta usando a IU da Web do BigQuery.

CLI

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 para seu 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 sendo anexada;
  • table é o nome da tabela sendo anexada;
  • query é uma consulta na sintaxe SQL padrão.

Exemplos:

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

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

from google.cloud import bigquery

# TODO(developer): 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)
job_config.schema_update_options = [
    bigquery.SchemaUpdateOption.ALLOW_FIELD_RELAXATION
]
job_config.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))