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:

• Use o console do Google Cloud.
• Use a instrução SQL CREATE TABLE.
• 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, o modo e o valor padrão de uma coluna.

Nomes de coluna

O nome da coluna pode conter letras (a-z, A-Z), números (0-9) ou sublinhados (_) e precisa começar com uma letra ou sublinhado. Se você usa nomes de coluna flexíveis, o BigQuery aceita iniciar um nome de coluna com um número. Tenha cuidado ao iniciar colunas com um número, já que o uso de nomes de colunas flexíveis com a API BigQuery Storage Read ou a API BigQuery Storage Write requer tratamento especial. Para mais informações sobre o suporte a nomes de colunas flexíveis, consulte nomes de colunas flexíveis.

Os nomes das colunas podem ter no máximo 300 caracteres. Os nomes de colunas não podem usar nenhum dos seguintes prefixos:

• _TABLE_
• _FILE_
• _PARTITION
• _ROW_TIMESTAMP
• __ROOT__
• _COLIDENTIFIER

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. Para saber mais sobre regras de nomenclatura de coluna, consulte Nomes de coluna na referência do GoogleSQL.

Se um nome de tabela (por exemplo, test) é igual a um de seus nomes de coluna (por exemplo, test), a expressão SELECT interpreta a coluna test como um STRUCT, que contém todas as outras colunas da tabela. Para evitar essa colisão, use um dos seguintes métodos:

• Evite usar o mesmo nome para uma tabela e suas colunas.

• Atribua um alias diferente à tabela. Por exemplo, a consulta a seguir atribui um alias de tabela t à tabela project1.dataset.test:

  SELECT test FROM project1.dataset.test AS t;
  

• Inclua o nome da tabela ao fazer referência a uma coluna. Exemplo:

  SELECT test.test FROM project1.dataset.test;
  

Nomes de colunas flexíveis

Visualização

Este produto ou recurso está sujeito aos "Termos das ofertas pré-GA" na seção "Termos de Serviço gerais" dos Termos específicos do serviço. Os produtos e recursos pré-GA estão disponíveis "no estado em que se encontram" e podem ter suporte limitado. Para mais informações, consulte as descrições da fase de lançamento.

Agora você tem mais flexibilidade para nomear as colunas, incluindo mais acesso a caracteres em outros idiomas além de inglês, bem como outros símbolos.

Os nomes de colunas flexíveis são compatíveis com os seguintes caracteres:

• Qualquer letra em qualquer idioma, conforme representado pela expressão regular Unicode \p{L}.
• Qualquer caractere numérico em qualquer idioma, conforme representado pela expressão regular Unicode \p{N}.
• Qualquer caractere de pontuação do conector, incluindo sublinhados, conforme representado pela expressão regular Unicode \p{Pc}.
• Hífen ou traço, conforme representado pela expressão regular Unicode \p{Pd}.
• Qualquer marca destinada a acompanhar outro caractere, conforme representado pela expressão regular Unicode \p{M}. Por exemplo, acentos, trema ou caixas de delimitação.
• Estes caracteres especiais:
  • Um "e" comercial (&), conforme representado pela expressão regular Unicode \u0026.
  • Um sinal de porcentagem (%), conforme representado pela expressão regular Unicode \u0025.
  • Um sinal de igual (=), conforme representado pela expressão regular Unicode \u003D.
  • Um sinal de adição (+), conforme representado pela expressão regular Unicode \u002B.
  • Dois-pontos (:), conforme representado pela expressão regular Unicode \u003A.
  • Um apóstrofo ('), conforme representado pela expressão regular Unicode \u0027.
  • Um sinal de menor que (<), conforme representado pela expressão regular Unicode \u003C.
  • Um sinal de maior que (>), conforme representado pela expressão regular Unicode \u003E.
  • Um sinal numérico (#), conforme representado pela expressão regular Unicode \u0023.
  • Uma linha vertical (|), conforme representado pela expressão regular Unicode \u007c.
  • Espaço em branco.

Os nomes de colunas flexíveis não são compatíveis com os seguintes caracteres especiais:

• Um ponto de exclamação (!), conforme representado pela expressão regular Unicode \u0021.
• Aspas ("), conforme representadas pela expressão regular Unicode \u0022.
• Um cifrão ($), conforme representado pela expressão regular Unicode \u0024.
• Um parêntese esquerdo ((), conforme representado pela expressão regular Unicode \u0028.
• Um parêntese direito ()), conforme representado pela expressão regular Unicode \u0029.
• Um asterisco (*), conforme representado pela expressão regular Unicode \u002A.
• Uma vírgula (,), conforme representado pela expressão regular Unicode \u002C.
• Um ponto (.), conforme representado pela expressão regular Unicode \u002E.
• Uma barra (/), conforme representada pela expressão regular Unicode \u002F.
• Ponto e vírgula (;), conforme representado pela expressão regular Unicode \u003B.
• Um ponto de interrogação (?), conforme representado pela expressão regular Unicode \u003F.
• Um sinal de arroba (@), conforme representado pela expressão regular Unicode \u0040.
• Um colchete esquerdo ([), conforme representado pela expressão regular Unicode \u005B.
• Uma barra invertida (\), conforme representado pela expressão regular Unicode \u005C.
• Um colchete direito (]), conforme representado pela expressão regular Unicode \u005D.
• Um acento circunflexo (^), conforme representado pela expressão regular Unicode \u005E.
• Um acento grave (`), conforme representado pela expressão regular Unicode \u0060.
• Uma chave à esquerda ({), conforme representado pela expressão regular Unicode \u007B.
• Uma chave à direita (}), conforme representado pela expressão regular Unicode \u007D.
• Um til (~) conforme representado pela expressão regular Unicode \u007E.

Para acessar outras diretrizes, consulte Nomes de colunas.

Os caracteres da coluna expandida são compatíveis com a API BigQuery Storage Read e a API BigQuery Storage Write. Para usar a lista expandida de caracteres Unicode com a API BigQuery Storage Read, é necessário definir uma flag. É possível usar o atributo displayName para recuperar o nome da coluna. O exemplo a seguir mostra como definir uma flag com o cliente Python:

from google.cloud.bigquery_storage import types
requested_session = types.ReadSession()

#set avro serialization options for flexible column.
options = types.AvroSerializationOptions()
options.enable_display_name_attribute = True
requested_session.read_options.avro_serialization_options = options

Para usar a lista expandida de caracteres Unicode com a API BigQuery Storage Write, forneça o esquema com a notação column_name, a menos que você esteja usando o objeto gravador JsonStreamWriter. O exemplo a seguir mostra como fornecer o esquema:

syntax = "proto2";
package mypackage;
// Source protos located in github.com/googleapis/googleapis
import "google/cloud/bigquery/storage/v1/annotations.proto";

message FlexibleSchema {
  optional string item_name_column = 1
  [(.google.cloud.bigquery.storage.v1.column_name) = "name-列"];
  optional string item_description_column = 2
  [(.google.cloud.bigquery.storage.v1.column_name) = "description-列"];
}

Neste exemplo, item_name_column e item_description_column são nomes de marcadores que precisam estar em conformidade com a convenção de nomenclatura do buffer de protocolo. Observe que as anotações column_name sempre têm precedência sobre nomes de marcadores.

• Por padrão, o carregamento de dados do Parquet não é compatível com nomes de colunas flexíveis. Para se inscrever nesse pré-lançamento, preencha o formulário de inscrição. Depois de se registrar na visualização, todos os nomes de coluna inválidos (por exemplo, ordenação de nomes de colunas) retornam um erro. Para projetos não registrados, a solicitação de carregamento substitui os caracteres inválidos por sublinhados em vez de retornar um erro.
• Por padrão, o carregamento de dados CSV usando detecção automática de esquema não é compatível com nomes de colunas flexíveis. Para se inscrever nesse pré-lançamento, preencha o formulário de inscrição. Depois de se registrar na visualização, todos os nomes de coluna inválidos (por exemplo, ordenação de nomes de colunas) retornam um erro. Para projetos não registrados, a solicitação de carregamento substitui os caracteres inválidos por sublinhados em vez de retornar um erro.

Limitações

Nomes de colunas flexíveis não são compatíveis com tabelas externas.

Descrições de coluna

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

Valores padrão

O valor padrão de uma coluna precisa ser literal ou uma das funções abaixo:

• CURRENT_DATE
• CURRENT_DATETIME
• CURRENT_TIME
• CURRENT_TIMESTAMP
• GENERATE_UUID
• RAND
• SESSION_USER
• ST_GEOGPOINT

Tipos de dados do GoogleSQL

O GoogleSQL permite especificar os seguintes tipos de dados 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	VERDADEIRO ou FALSO, não diferencia maiúsculas de minú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.
JSON	JSON	Representa JSON, um formato leve de troca de dados.
RANGE	RANGE	Um intervalo de valores DATE, DATETIME ou TIMESTAMP

Para mais informações sobre os tipos de dados no GoogleSQL, consulte Tipos de dados do GoogleSQL.

Também é possível declarar um tipo de matriz ao consultar dados. Para mais informações, consulte 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.

Modo de arredondamento

Quando uma coluna é um tipo NUMERIC ou BIGNUMERIC, é possível definir a opção de coluna rounding_mode, que determina como os valores nessa coluna são arredondados. ao ser gravado na tabela. É possível definir a opção rounding_mode em uma coluna de nível superior ou um campo STRUCT. Os seguintes modos de arredondamento são compatíveis:

• "ROUND_HALF_AWAY_FROM_ZERO": esse modo (padrão) é arredondado para a metade de zero.
• "ROUND_HALF_EVEN": esse modo arredonda os casos na metade para o dígito par mais próximo.

Não é possível definir a opção rounding_mode para uma coluna que não seja um tipo NUMERIC ou BIGNUMERIC. Para saber mais sobre esses tipos, consulte os tipos decimais.

O exemplo a seguir cria uma tabela e insere valores arredondados com base no modo de arredondamento da coluna:

CREATE TABLE mydataset.mytable (
  x NUMERIC(5,2) OPTIONS (rounding_mode='ROUND_HALF_EVEN'),
  y NUMERIC(5,2) OPTIONS (rounding_mode='ROUND_HALF_AWAY_FROM_ZERO')
);
INSERT mydataset.mytable (x, y)
VALUES (NUMERIC "1.025", NUMERIC "1.025"),
       (NUMERIC "1.0251", NUMERIC "1.0251"),
       (NUMERIC "1.035", NUMERIC "1.035"),
       (NUMERIC "-1.025", NUMERIC "-1.025");

A tabela mytable tem esta aparência:

+-------+-------+
| x     | y     |
+-------+-------+
| 1.02  | 1.03  |
| 1.03  | 1.03  |
| 1.04  | 1.04  |
| -1.02 | -1.03 |
+-------+-------+

Para ver mais informações, consulte roundingMode em TableFieldSchema.

Especificar esquemas

Ao carregar dados ou criar uma tabela vazia, é possível especificar o esquema da tabela usando o console do Cloud ou a ferramenta de linha de comando bq. A especificação 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 um esquema de tabela:

Console

No Console do Google Cloud, é possível especificar um esquema usando a opção 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. Expanda a opção Ações e clique em Abrir.

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

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

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

     

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

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

   • Opção 1: use Adicionar campo e especifique o nome, o tipo e o modo de cada campo.
   • 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.

8. Selecione Criar tabela.

SQL

Use a instrução CREATE TABLE. Especifique o esquema usando a opção coluna. O exemplo a seguir cria uma nova tabela chamada newtable com as colunas x, y, z de tipos de número inteiro, string e booleano, respectivamente.

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

   Ir para o BigQuery

2. No editor de consultas, digite a seguinte instrução:

   CREATE TABLE IF NOT EXISTS mydataset.newtable (x INT64, y STRING, z BOOL)
     OPTIONS(
       description = 'My example table');
   

3. Clique em Executar.

Para mais informações sobre como executar consultas, acesse Executar uma consulta interativa.

bq

Forneça 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.

Ao especificar o esquema na linha de comando, não é possível incluir RECORD (STRUCT) ou RANGE não é possível incluir uma descrição de coluna nem especificar o modo da coluna. Todos os modos assumem NULLABLE como padrão. Para incluir descrições, modos e tipos RECORD e RANGE, 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 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 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:

Antes de testar esta amostra, siga as instruções de configuração do C# 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 C#.

Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de cliente.

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 = loadJob.PollUntilCompleted().ThrowOnAnyError();  // 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;

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:

Antes de testar esta amostra, siga as instruções de configuração do Go 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 Go.

Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de cliente.

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:

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.

Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de cliente.

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.

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.

Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de cliente.

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:

• O nome da coluna
• Tipo de dado
• (Opcional) Modo (se não for especificado, o modo padrão é NULLABLE)
• Opcional: os campos da coluna, se for um tipo STRUCT
• (Opcional) descrição
• Opcional: as tags de política da coluna, usadas para controle de acesso no nível do campo.
• Opcional: o comprimento máximo dos valores da coluna para os tipos STRING ou BYTES.
• Opcional: a precisão da coluna para os tipos NUMERIC ou BIGNUMERIC.
• Opcional: a escala da coluna para os tipos NUMERIC ou BIGNUMERIC.
• Opcional: a ordenação da coluna para tipos STRING
• (Opcional) Valor padrão da coluna
• Opcional: o modo de arredondamento da coluna, se a coluna for um tipo NUMERIC ou BIGNUMERIC

Observação: também é possível especificar a matriz JSON criada no arquivo de esquema usando a opção Editar como texto no Console do Cloud. Ela também pode ser usada como ponto de partida para configurar a propriedade schema na API.

Como criar um arquivo de esquema JSON

Para criar um arquivo de esquema JSON, insira um TableFieldSchema para cada coluna. Os campos name e type são obrigatórios. Todos os outros campos são opcionais.

[
  {
    "name": string,
    "type": string,
    "mode": string,
    "fields": [
      {
        object (TableFieldSchema)
      }
    ],
    "description": string,
    "policyTags": {
      "names": [
        string
      ]
    },
    "maxLength": string,
    "precision": string,
    "scale": string,
    "collation": string,
    "defaultValueExpression": string,
    "roundingMode": string
  },
  {
    "name": string,
    "type": string,
    ...
  }
]

Se a coluna é um tipo RANGE<T>, use o campo rangeElementType para descrever T, em que T precisa ser DATE, DATETIME ou TIMESTAMP.

[
  {
    "name": "duration",
    "type": "RANGE",
    "mode": "NULLABLE",
    "rangeElementType": {
      "type": "DATE"
    }
  }
]

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 já existente em um arquivo local, faça isto:

bq

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

Substitua:

• project_id: o ID do projeto;
• dataset: um conjunto de dados no projeto.
• table: o nome de um esquema de tabela já existente.
• path_to_file: o local do arquivo local em que você está gravando o esquema de tabela.

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.

Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de cliente.

Para gravar um arquivo de esquema JSON com base em uma tabela usando a biblioteca de cliente Python, chame o método Client.schema_to_json.

from google.cloud import bigquery

client = bigquery.Client()

# TODO(dev): Change the table_id variable to the full name of the
# table you want to get schema from.
table_id = "your-project.your_dataset.your_table_name"

# TODO(dev): Change schema_path variable to the path
# of your schema file.
schema_path = "path/to/schema.json"
table = client.get_table(table_id)  # Make an API request.

# Write a schema file to schema_path with the schema_to_json method.
client.schema_to_json(table.schema, schema_path)

with open(schema_path, "r", encoding="utf-8") as schema_file:
    schema_contents = schema_file.read()

# View table properties
print(f"Got table '{table.project}.{table.dataset_id}.{table.table_id}'.")
print(f"Table schema: {schema_contents}")

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

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

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 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 que esteja armazenado no Cloud Storage ou no Google Drive.

Como especificar um arquivo de esquema ao carregar dados

Para carregar dados em uma tabela usando uma definição de esquema JSON, faça o seguinte:

bq

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 no diretório atual.

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

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.

Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de cliente.

Para carregar um esquema de tabela de um arquivo JSON usando a biblioteca de cliente Python, chame o método schema_from_json.

from google.cloud import bigquery

client = bigquery.Client()

# TODO(dev): Change uri variable to the path of your data file.
uri = "gs://your-bucket/path/to/your-file.csv"
# TODO(dev): Change table_id to the full name of the table you want to create.
table_id = "your-project.your_dataset.your_table"
# TODO(dev): Change schema_path variable to the path of your schema file.
schema_path = "path/to/schema.json"
# To load a schema file use the schema_from_json method.
schema = client.schema_from_json(schema_path)

job_config = bigquery.LoadJobConfig(
    # To use the schema you loaded pass it into the
    # LoadJobConfig constructor.
    schema=schema,
    skip_leading_rows=1,
)

# Pass the job_config object to the load_table_from_file,
# load_table_from_json, or load_table_from_uri method
# to use the schema on a new table.
load_job = client.load_table_from_uri(
    uri, table_id, job_config=job_config
)  # Make an API request.

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

destination_table = client.get_table(table_id)  # Make an API request.
print(f"Loaded {destination_table.num_rows} rows to {table_id}.")

Como especificar um arquivo de esquema ao criar uma tabela

Para criar uma tabela vazia em um conjunto de dados atual usando um arquivo de esquema JSON, faça o seguinte:

bq

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 no diretório atual:

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

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.

Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de cliente.

Para carregar um esquema de tabela de um arquivo JSON usando a biblioteca de cliente Python, chame o método schema_from_json.

from google.cloud import bigquery

client = bigquery.Client()

# TODO(dev): Change table_id to the full name of the table you want to create.
table_id = "your-project.your_dataset.your_table_name"
# TODO(dev): Change schema_path variable to the path of your schema file.
schema_path = "path/to/schema.json"
# To load a schema file use the schema_from_json method.
schema = client.schema_from_json(schema_path)

table = bigquery.Table(table_id, schema=schema)
table = client.create_table(table)  # API request
print(f"Created table {table_id}.")

Como especificar um esquema na API

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

• Para especificar um esquema ao carregar dados, chame o método jobs.insert e configure a propriedade schema no recurso JobConfigurationLoad.

• Para especificar um esquema ao criar uma tabela, chame o método tables.insert e configure a propriedade schema no recurso Table.

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

Segurança de tabelas

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

A seguir

• Saiba como especificar colunas aninhadas e repetidas em uma definição de esquema.
• Saiba mais sobre a detecção automática de esquema.
• Saiba mais sobre o carregamento de dados no BigQuery.
• Saiba mais sobre Como criar e usar tabelas.