Como especificar um esquema

O BigQuery permite especificar o esquema de uma tabela ao carregar dados nela e ao criar uma tabela vazia. Também é possível usar a detecção automática de esquema em formatos de dados compatíveis.

Ao carregar arquivos de exportação Avro, Parquet, ORC, Firestore ou Datastore, o esquema é recuperado automaticamente dos dados de origem autodescritivos.

É possível especificar o esquema de uma tabela das seguintes maneiras:

  • Especifique manualmente:
    • usando o Console do Cloud;
    • Inline usando a ferramenta de linha de comando bq.
  • Crie um arquivo de esquema no formato JSON.
  • Chame o método jobs.insert e configure a propriedade schema na configuração de job load.
  • Chame o método tables.insert e configure o esquema no recurso de tabela usando a propriedade schema.

Depois de carregar os dados ou criar uma tabela vazia, é possível modificar a definição do esquema da tabela.

Componentes do esquema

Quando você especifica um esquema de tabela, é necessário fornecer o nome e o tipo de dados de cada coluna. Também é possível fornecer a descrição e o modo da coluna.

Nomes de coluna

O nome da coluna precisa conter apenas letras (a-z, A-Z), números (0-9) ou sublinhados (_) e começar com uma letra ou sublinhado. O comprimento máximo é de 300 caracteres. Não é possível usar estes prefixos nos nomes da coluna:

  • _TABLE_
  • _FILE_
  • _PARTITION

Não é permitido haver nomes de coluna duplicados, mesmo com diferença de maiúsculas e minúsculas. Por exemplo, uma coluna chamada Column1 é considerada idêntica a uma coluna chamada column1.

Descrições de coluna

Cada coluna pode incluir uma descrição opcional. Ela é uma string com comprimento máximo de 1.024 caracteres.

Tipos de dados do SQL padrão

Com o SQL padrão do BigQuery, você especifica os tipos de dados a seguir no esquema. O tipo de dados é obrigatório.

Nome Tipo de dado Descrição
Número inteiro INT64 Valores numéricos sem componentes fracionários.
Ponto flutuante FLOAT64 Valores numéricos aproximados com componentes fracionários.
Numérico NUMERIC Valores numéricos exatos com componentes fracionários
BigNumeric BIGNUMERIC Valores numéricos exatos com componentes fracionários
Booleano BOOL "TRUE" ou "FALSE", indiferente a maiúsculas.
String STRING Dados em caracteres de comprimento variável (Unicode).
Bytes BYTES Dados binários de comprimento variável.
Data DATE Uma data de calendário lógico
Data/hora DATETIME Ano, mês, dia, hora, minuto, segundo e subsegundo
Hora TIME Um horário, sem depender de uma data específica.
Carimbo de data/hora TIMESTAMP Ponto absoluto no tempo, com precisão de microssegundo.
Struct (Record) STRUCT Contêiner de campos ordenados, cada um com um tipo (obrigatório) e um nome de campo (opcional).
Geografia GEOGRAPHY Um conjunto de pontos na superfície da Terra formado por pontos, linhas e polígonos com bordas geodésicas no esferóide de referência WGS84.

Para saber mais sobre os tipos de dados no SQL padrão, consulte Tipos de dados do SQL padrão.

Também é possível declarar um tipo de matriz ao consultar dados. Para saber mais, consulte Como trabalhar com matrizes.

Modos

O BigQuery é compatível com os modos para colunas a seguir. O modo é opcional. Se ele não for especificado, o padrão da coluna é NULLABLE.

Modo Descrição
Anulável A coluna permite valores NULL (padrão).
Obrigatório Os valores NULL não são permitidos.
Repetida A coluna contém uma matriz de valores do tipo especificado.

Para mais informações sobre modos, consulte mode no TableFieldSchema.

Como especificar os esquemas manualmente

Ao carregar dados ou criar uma tabela vazia, é possível especificar manualmente o esquema da tabela usando o Console do Cloud ou a ferramenta de linha de comando bq. A especificação manual de esquema é compatível quando você carrega arquivos CSV e JSON (delimitado por nova linha). Ao carregar dados de exportação Avro, Parquet, ORC, Firestore ou Datastore, o esquema é recuperado automaticamente dos dados de origem autodescritivos.

Para especificar manualmente um esquema de tabela:

Console

No Console do Cloud, especifique um esquema usando as opções Adicionar campo ou Editar como texto.

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

    Ir para o BigQuery

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

  3. No painel de detalhes, clique em Criar tabela.

  4. Na página Criar tabela, localizada na seção Origem, selecione Tabela vazia.

  5. Siga estas etapas na página Criar tabela, localizada na seção Destino:

    • Em Nome do conjunto de dados, escolha o conjunto de dados apropriado.

      Selecionar conjunto de dados.

    • No campo Nome da tabela, insira o nome da tabela que você está criando.

    • Verifique se o Tipo de tabela está definido como Tabela nativa.

  6. Na seção Esquema, insira a definição do esquema.

    • Opção 1: use Adicionar campo e especifique o Nome, Tipo e Modo de cada campo. No Console do Cloud, não é possível adicionar uma descrição de campo ao usar a opção Adicionar campo. No entanto, é possível adicionar descrições manualmente depois de carregar os dados.
    • Opção 2: clique em Editar como texto e cole o esquema na forma de uma matriz JSON. Com ela, você gera o esquema usando um processo igual ao de criação de um arquivo de esquema JSON.

  7. Clique em Criar tabela.

bq

Forneça manualmente o esquema in-line no formato field:data_type,field:data_type usando um dos comandos a seguir:

  • Se estiver carregando dados, use o comando bq load.
  • Se você estiver criando uma tabela vazia, use o comando bq mk.

Quando você especifica o esquema na linha de comando, não é possível especificar o modo da coluna ou incluir um tipo RECORD (STRUCT) e uma descrição de coluna. Todos os modos assumem NULLABLE como padrão. Para incluir descrições, modos e tipos RECORD, forneça um arquivo de esquema JSON.

Para carregar dados em uma tabela usando uma definição de esquema in-line, insira o comando load e especifique o formato de dados usando a sinalização --source_format. Se estiver carregando dados em uma tabela em um projeto diferente do seu projeto padrão, inclua o ID do projeto no seguinte formato: project_id:dataset.table_name.

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

bq --location=location load \
--source_format=format \
project_id:dataset.table_name \
path_to_source \
schema

Substitua:

  • location: o nome do seu local. A sinalização --location é opcional. Por exemplo, se 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 a unidade usando o arquivo .bigqueryrc.
  • format: NEWLINE_DELIMITED_JSON ou CSV.
  • project_id: o ID do projeto;
  • dataset: o conjunto de dados que contém a tabela em que você está carregando dados;
  • table_name: o nome da tabela em que você está carregando dados.
  • path_to_source: o local do arquivo de dados CSV ou JSON na sua máquina local ou no Cloud Storage;
  • schema: a definição do esquema in-line.

Exemplo:

Digite o comando abaixo para carregar dados de um arquivo CSV local chamado myfile.csv em mydataset.mytable no seu projeto padrão. O esquema é especificado manualmente e in-line.

bq load \
--source_format=CSV \
mydataset.mytable \
./myfile.csv \
qtr:STRING,sales:FLOAT,year:STRING

Para mais informações sobre como carregar dados no BigQuery, consulte Introdução ao carregamento de dados.

Para especificar uma definição de esquema in-line ao criar uma tabela vazia, insira o comando bq mk com a sinalização --table ou -t. Se estiver criando uma tabela em um projeto diferente do seu projeto padrão, adicione o ID do projeto ao comando no seguinte formato: project_id:dataset.table.

bq mk --table project_id:dataset.table schema

Substitua:

  • project_id: o ID do projeto;
  • dataset: um conjunto de dados no projeto;
  • table: o nome da tabela que você está criando;
  • schema: uma definição de esquema in-line.

O comando a seguir, por exemplo, cria uma tabela vazia chamada mytable no seu projeto padrão. O esquema é especificado manualmente e in-line.

bq mk --table mydataset.mytable qtr:STRING,sales:FLOAT,year:STRING

Para mais informações sobre como criar uma tabela vazia, consulte Como criar uma tabela vazia com uma definição de esquema.

C#

Para especificar o esquema de uma tabela ao carregar dados nela:


using Google.Apis.Bigquery.v2.Data;
using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryLoadTableGcsJson
{
    public void LoadTableGcsJson(
        string projectId = "your-project-id",
        string datasetId = "your_dataset_id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        var gcsURI = "gs://cloud-samples-data/bigquery/us-states/us-states.json";
        var dataset = client.GetDataset(datasetId);
        var schema = new TableSchemaBuilder {
            { "name", BigQueryDbType.String },
            { "post_abbr", BigQueryDbType.String }
        }.Build();
        TableReference destinationTableRef = dataset.GetTableReference(
            tableId: "us_states");
        // Create job configuration
        var jobOptions = new CreateLoadJobOptions()
        {
            SourceFormat = FileFormat.NewlineDelimitedJson
        };
        // Create and run job
        BigQueryJob loadJob = client.CreateLoadJob(
            sourceUri: gcsURI, destination: destinationTableRef,
            schema: schema, options: jobOptions);
        loadJob.PollUntilCompleted();  // Waits for the job to complete.
        // Display the number of rows uploaded
        BigQueryTable table = client.GetTable(destinationTableRef);
        Console.WriteLine(
            $"Loaded {table.Resource.NumRows} rows to {table.FullyQualifiedId}");
    }
}

Para especificar um esquema ao criar uma tabela vazia:


using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryCreateTable
{
    public BigQueryTable CreateTable(
        string projectId = "your-project-id",
        string datasetId = "your_dataset_id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        var dataset = client.GetDataset(datasetId);
        // Create schema for new table.
        var schema = new TableSchemaBuilder
        {
            { "full_name", BigQueryDbType.String },
            { "age", BigQueryDbType.Int64 }
        }.Build();
        // Create the table
        return dataset.CreateTable(tableId: "your_table_id", schema: schema);
    }
}

Go

Para especificar o esquema de uma tabela ao carregar dados nela:

import (
	"context"
	"fmt"

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

// importJSONExplicitSchema demonstrates loading newline-delimited JSON data from Cloud Storage
// into a BigQuery table and providing an explicit schema for the data.
func importJSONExplicitSchema(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()

	gcsRef := bigquery.NewGCSReference("gs://cloud-samples-data/bigquery/us-states/us-states.json")
	gcsRef.SourceFormat = bigquery.JSON
	gcsRef.Schema = bigquery.Schema{
		{Name: "name", Type: bigquery.StringFieldType},
		{Name: "post_abbr", Type: bigquery.StringFieldType},
	}
	loader := client.Dataset(datasetID).Table(tableID).LoaderFrom(gcsRef)
	loader.WriteDisposition = bigquery.WriteEmpty

	job, err := loader.Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}

	if status.Err() != nil {
		return fmt.Errorf("job completed with error: %v", status.Err())
	}
	return nil
}

Para especificar um esquema ao criar uma tabela vazia:

import (
	"context"
	"fmt"
	"time"

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

// createTableExplicitSchema demonstrates creating a new BigQuery table and specifying a schema.
func createTableExplicitSchema(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()

	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType},
		{Name: "age", Type: bigquery.IntegerFieldType},
	}

	metaData := &bigquery.TableMetadata{
		Schema:         sampleSchema,
		ExpirationTime: time.Now().AddDate(1, 0, 0), // Table will be automatically deleted in 1 year.
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, metaData); err != nil {
		return err
	}
	return nil
}

Java

Para especificar o esquema de uma tabela ao carregar dados nela:

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.JobInfo;
import com.google.cloud.bigquery.LoadJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableId;

// Sample to load JSON data from Cloud Storage into a new BigQuery table
public class LoadJsonFromGCS {

  public static void runLoadJsonFromGCS() {
    // 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.json";
    Schema schema =
        Schema.of(
            Field.of("name", StandardSQLTypeName.STRING),
            Field.of("post_abbr", StandardSQLTypeName.STRING));
    loadJsonFromGCS(datasetName, tableName, sourceUri, schema);
  }

  public static void loadJsonFromGCS(
      String datasetName, String tableName, String sourceUri, Schema schema) {
    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);
      LoadJobConfiguration loadConfig =
          LoadJobConfiguration.newBuilder(tableId, sourceUri)
              .setFormatOptions(FormatOptions.json())
              .setSchema(schema)
              .build();

      // Load data from a GCS JSON file into the table
      Job job = bigquery.create(JobInfo.of(loadConfig));
      // Blocks until this load table job completes its execution, either failing or succeeding.
      job = job.waitFor();
      if (job.isDone()) {
        System.out.println("Json from GCS 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());
    }
  }
}

Para especificar um esquema ao criar uma tabela vazia:

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.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.TableDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

public class CreateTable {

  public static void runCreateTable() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    Schema schema =
        Schema.of(
            Field.of("stringField", StandardSQLTypeName.STRING),
            Field.of("booleanField", StandardSQLTypeName.BOOL));
    createTable(datasetName, tableName, schema);
  }

  public static void createTable(String datasetName, String tableName, Schema schema) {
    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);
      TableDefinition tableDefinition = StandardTableDefinition.of(schema);
      TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();

      bigquery.create(tableInfo);
      System.out.println("Table created successfully");
    } catch (BigQueryException e) {
      System.out.println("Table was not created. \n" + e.toString());
    }
  }
}

Python

Para especificar o esquema de uma tabela ao carregar dados, configure a propriedade LoadJobConfig.schema.

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 create.
# table_id = "your-project.your_dataset.your_table_name"

job_config = bigquery.LoadJobConfig(
    schema=[
        bigquery.SchemaField("name", "STRING"),
        bigquery.SchemaField("post_abbr", "STRING"),
    ],
    source_format=bigquery.SourceFormat.NEWLINE_DELIMITED_JSON,
)
uri = "gs://cloud-samples-data/bigquery/us-states/us-states.json"

load_job = client.load_table_from_uri(
    uri,
    table_id,
    location="US",  # Must match the destination dataset location.
    job_config=job_config,
)  # Make an API request.

load_job.result()  # Waits for the job to complete.

destination_table = client.get_table(table_id)
print("Loaded {} rows.".format(destination_table.num_rows))

Para especificar um esquema ao criar uma tabela vazia, configure a propriedade Table.schema.

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 create.
# table_id = "your-project.your_dataset.your_table_name"

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

table = bigquery.Table(table_id, schema=schema)
table = client.create_table(table)  # Make an API request.
print(
    "Created table {}.{}.{}".format(table.project, table.dataset_id, table.table_id)
)

Como especificar um arquivo de esquema JSON

Se preferir, você pode especificar o esquema usando um arquivo de esquema JSON em vez de usar uma definição de esquema in-line. Um arquivo de esquema JSON consiste em uma matriz JSON que contém o seguinte:

Como criar um arquivo de esquema JSON

Basta inserir o comando a seguir usando um editor de texto apropriado:

[
 {
   "description": "[DESCRIPTION]",
   "name": "[NAME]",
   "type": "[TYPE]",
   "mode": "[MODE]"
 },
 {
   "description": "[DESCRIPTION]",
   "name": "[NAME]",
   "type": "[TYPE]",
   "mode": "[MODE]"
 }
]

A matriz JSON é indicada por colchetes de início e fim []. É preciso separar todas as entradas de coluna com uma vírgula: },.

Para gravar um esquema de tabela existente em um arquivo local, insira o comando a seguir:

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

É possível usar o arquivo de saída como ponto de partida do seu próprio arquivo de esquema JSON. Ao usar essa abordagem, verifique se o arquivo contém apenas a matriz JSON que representa o esquema da tabela.

Por exemplo, a matriz JSON a seguir representa um esquema de tabela básico. Este esquema tem três colunas: qtr (REQUIRED STRING), rep (NULLABLE STRING), e sales (NULLABLE FLOAT).

[
  {
    "description": "quarter",
    "mode": "REQUIRED",
    "name": "qtr",
    "type": "STRING"
  },
  {
    "description": "sales representative",
    "mode": "NULLABLE",
    "name": "rep",
    "type": "STRING"
  },
  {
    "description": "total sales",
    "mode": "NULLABLE",
    "name": "sales",
    "type": "FLOAT"
  }
]

Como usar um arquivo de esquema JSON

Depois de criar seu arquivo de esquema JSON, é possível especificá-lo usando a ferramenta de linha de comando bq. Não é possível usar um arquivo de esquema com o Console do Cloud ou a API.

Forneça manualmente o arquivo de esquema:

  • Se estiver carregando dados, use o comando bq load.
  • Se você estiver criando uma tabela vazia, use o comando bq mk.

Quando você fornece um arquivo de esquema JSON, ele precisa estar armazenado em uma localização legível localmente. Não é possível especificar um arquivo de esquema JSON armazenado no Cloud Storage ou no Drive.

Como especificar um arquivo de esquema ao carregar dados

O comando a seguir carrega dados em uma tabela usando a definição de esquema em um arquivo JSON:

bq --location=location load \
--source_format=format \
project_id:dataset.table \
path_to_data_file \
path_to_schema_file

Substitua:

  • location: o nome do seu local. A sinalização --location é opcional. Por exemplo, se 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 ou CSV.
  • project_id: o ID do projeto;
  • dataset: o conjunto de dados que contém a tabela em que você está carregando dados;
  • table: o nome da tabela em que você está carregando dados;
  • path_to_data_file: o local do arquivo de dados CSV ou JSON na sua máquina local ou no Cloud Storage;
  • path_to_schema_file: o caminho para o arquivo de esquema na máquina local.

Exemplo:

Digite o comando abaixo para carregar dados de um arquivo CSV local chamado myfile.csv em mydataset.mytable no seu projeto padrão. O esquema é especificado em myschema.json.

bq load --source_format=CSV mydataset.mytable ./myfile.csv ./myschema.json

Como especificar um arquivo de esquema ao criar uma tabela

O comando a seguir cria uma tabela vazia em um conjunto de dados existente usando a definição do esquema em um arquivo JSON:

bq mk --table project_id:dataset.table path_to_schema_file

Substitua:

  • project_id: o ID do projeto;
  • dataset: um conjunto de dados no projeto;
  • table: o nome da tabela que você está criando;
  • path_to_schema_file: o caminho para o arquivo de esquema na máquina local.

O comando a seguir, por exemplo, cria uma tabela chamada mytable em mydataset no seu projeto padrão. O esquema é especificado em myschema.json:

bq mk --table mydataset.mytable ./myschema.json

Como especificar um esquema na API

Especificar um esquema de tabela usando a API, siga estas etapas:

O processo de especificação de esquema por meio da API é semelhante ao de criação de um arquivo de esquema JSON.

Próximas etapas