Como usar a detecção automática de esquema

Detecção automática de esquema

Com a detecção automática de esquemas, o BigQuery infere o esquema para dados CSV, JSON ou Planilhas Google. A detecção automática de esquema está disponível ao carregar dados no BigQuery e ao fazer uma consulta em uma fonte de dados externa.

Quando a detecção automática está ativada, o BigQuery infere o tipo de dados de cada coluna. O BigQuery seleciona um arquivo aleatório na fonte de dados e verifica até as primeiras 500 linhas de dados para usar como uma amostra representativa. Em seguida, o BigQuery examina cada campo e tenta atribuir a ele um tipo de dados com base nos valores contidos na amostra. Se todas as linhas em uma coluna estiverem vazias, a detecção automática usará o tipo de dados STRING da coluna como padrão.

Se você não ativar a detecção automática de esquema para dados CSV, JSON ou Planilhas Google, forneça o esquema manualmente ao criar a tabela.

Você não precisa ativar a detecção automática de esquema para arquivos de exportação Avro, Parquet, ORC, Firestore ou Datastore. Esses formatos de arquivo são autodescritivos, portanto, o BigQuery infere automaticamente o esquema da tabela dos dados de origem. Para arquivos Parquet, Avro e Orc, é possível fornecer um esquema explícito para substituir o esquema inferido.

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

• Use o console do Google Cloud.
• use o comando bq show da ferramenta de linha de comando bq;

Quando o BigQuery detecta esquemas, é possível que, em raras ocasiões, ele altere o nome de um campo para torná-lo compatível com a sintaxe do GoogleSQL.

Para informações sobre conversões de tipos de dados, consulte:

• Conversão do tipo de dados ao carregar dados do Cloud Datastore
• Conversão do tipo de dados ao carregar dados do Firestore
• Conversões Avro
• Conversões Parquet
• Conversões ORC

Como carregar dados usando a detecção automática de esquema

Para ativar a detecção automática de esquema ao carregar dados, use uma destas abordagens:

• No console do Google Cloud, na seção Esquema, em Detectar automaticamente, marque a opção Parâmetros de esquema e entrada.
• Na ferramenta de linha de comando bq, use o comando bq load com o parâmetro --autodetect.

Quando a detecção automática de esquema estiver ativada, o BigQuery tentará inferir automaticamente o esquema dos arquivos CSV e JSON. A lógica de detecção automática infere os tipos de campo de esquema lendo até as primeiras 500 linhas de dados. As linhas iniciais serão ignoradas se a sinalização --skip_leading_rows estiver presente. Os tipos de campo são baseados nas linhas que têm mais campos. Portanto, a detecção automática deve funcionar conforme o esperado, desde que haja pelo menos uma linha de dados com valores em cada coluna/campo.

A detecção automática de esquema não é usada com arquivos Avro, Parquet e ORC ou arquivos de exportação do Firestore e do Datastore. Ao carregar esses arquivos no BigQuery, o esquema da tabela é recuperado automaticamente a partir dos dados de origem autodescritivos.

Para usar a detecção automática de esquema ao carregar dados JSON ou CSV:

Console

1. No Console do Google Cloud, acesse 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, na seção Origem:

   • Em Criar tabela de, selecione o tipo de fonte.

   • No campo de origem, procure o intervalo Arquivo/Armazenamento em nuvem ou insira o URI do Cloud Storage. Não é possível incluir vários URIs no console do Google Cloud, mas os caracteres curinga são compatíveis. O intervalo do Cloud Storage precisa estar no mesmo local que o conjunto de dados que contém a tabela que você está criando.

     

   • Em Formato do arquivo, selecione CSV ou JSON.

6. Na página Criar tabela, na seção Destino:

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

     

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

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

7. Clique em Criar tabela.

bq

Emita o comando bq load com o parâmetro --autodetect.

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

O seguinte comando carrega um arquivo usando a detecção automática de esquema:

bq --location=LOCATION load \
--autodetect \
--source_format=FORMAT \
DATASET.TABLE \
PATH_TO_SOURCE

Substitua:

• LOCATION: o nome do seu 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 ou CSV.
• 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_SOURCE: o local do arquivo CSV ou JSON.

Exemplos:

Digite o comando a seguir e carregue myfile.csv da máquina local para uma tabela chamada mytable armazenada em um conjunto de dados chamado mydataset.

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

Digite o comando a seguir e carregue myfile.json da máquina local para uma tabela chamada mytable armazenada em um conjunto de dados chamado mydataset.

bq load --autodetect --source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable ./myfile.json

API

1. Crie um job load que aponte para os dados de origem. Para informações sobre a criação de jobs, consulte Como executar jobs do BigQuery de maneira programática. Especifique seu local na propriedade location na seção jobReference.

2. Especifique o formato de dados definindo a propriedade sourceFormat. Para usar a detecção automática de esquema, esse valor precisa ser definido como NEWLINE_DELIMITED_JSON ou CSV.

3. Use a propriedade autodetect para definir a detecção automática de esquema como true.

Go

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

// importJSONAutodetectSchema demonstrates loading data from newline-delimited JSON data in Cloud Storage
// and using schema autodetection to identify the available columns.
func importJSONAutodetectSchema(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.AutoDetect = true
	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
}

Java

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
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.TableId;

// Sample to load JSON data with autodetect schema from Cloud Storage into a new BigQuery table
public class LoadJsonFromGCSAutodetect {

  public static void runLoadJsonFromGCSAutodetect() {
    // 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";
    loadJsonFromGCSAutodetect(datasetName, tableName, sourceUri);
  }

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

      TableId tableId = TableId.of(datasetName, tableName);
      LoadJobConfiguration loadConfig =
          LoadJobConfiguration.newBuilder(tableId, sourceUri)
              .setFormatOptions(FormatOptions.json())
              .setAutodetect(true)
              .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 Autodetect 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());
    }
  }
}

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.CsvOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.LoadJobConfiguration;
import com.google.cloud.bigquery.TableId;

// Sample to load CSV data with autodetect schema from Cloud Storage into a new BigQuery table
public class LoadCsvFromGcsAutodetect {

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

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

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

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

      LoadJobConfiguration loadConfig =
          LoadJobConfiguration.newBuilder(tableId, sourceUri)
              .setFormatOptions(csvOptions)
              .setAutodetect(true)
              .build();

      // Load data from a GCS CSV 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() && job.getStatus().getError() == null) {
        System.out.println("CSV Autodetect 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());
    }
  }
}

Node.js

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

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

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

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

/**
 * This sample loads the JSON file at
 * https://storage.googleapis.com/cloud-samples-data/bigquery/us-states/us-states.json
 *
 * TODO(developer): Replace the following lines with the path to your file.
 */
const bucketName = 'cloud-samples-data';
const filename = 'bigquery/us-states/us-states.json';

async function loadJSONFromGCSAutodetect() {
  // Imports a GCS file into a table with autodetected schema.

  // Instantiate clients
  const bigquery = new BigQuery();
  const storage = new Storage();

  // Configure the load job. For full list of options, see:
  // https://cloud.google.com/bigquery/docs/reference/rest/v2/Job#JobConfigurationLoad
  const metadata = {
    sourceFormat: 'NEWLINE_DELIMITED_JSON',
    autodetect: true,
    location: 'US',
  };

  // Load data from a Google Cloud Storage file into the table
  const [job] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .load(storage.bucket(bucketName).file(filename), metadata);
  // load() waits for the job to finish
  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;
  }
}
loadJSONFromGCSAutodetect();

PHP

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

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

use Google\Cloud\BigQuery\BigQueryClient;

/**
 * Imports data to the given table from json file present in GCS by auto
 * detecting options and schema.
 *
 * @param string $projectId The project Id of your Google Cloud Project.
 * @param string $datasetId The BigQuery dataset ID.
 * @param string $tableId The BigQuery table ID.
 */
function import_from_storage_json_autodetect(
    string $projectId,
    string $datasetId,
    string $tableId = 'us_states'
): void {
    // instantiate the bigquery table service
    $bigQuery = new BigQueryClient([
      'projectId' => $projectId,
    ]);
    $dataset = $bigQuery->dataset($datasetId);
    $table = $dataset->table($tableId);

    // create the import job
    $gcsUri = 'gs://cloud-samples-data/bigquery/us-states/us-states.json';
    $loadConfig = $table->loadFromStorage($gcsUri)->autodetect(true)->sourceFormat('NEWLINE_DELIMITED_JSON');
    $job = $table->runJob($loadConfig);

    // check if the job is complete
    $job->reload();
    if (!$job->isComplete()) {
        throw new \Exception('Job has not yet completed', 500);
    }
    // check if the job has errors
    if (isset($job->info()['status']['errorResult'])) {
        $error = $job->info()['status']['errorResult']['message'];
        printf('Error running job: %s' . PHP_EOL, $error);
    } else {
        print('Data imported successfully' . PHP_EOL);
    }
}

Python

Para ativar a detecção automática de esquema, defina a propriedade LoadJobConfig.autodetect (em inglês) como True.

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

# Set the encryption key to use for the destination.
# TODO: Replace this key with a key you have created in KMS.
# kms_key_name = "projects/{}/locations/{}/keyRings/{}/cryptoKeys/{}".format(
#     "cloud-samples-tests", "us", "test", "test"
# )
job_config = bigquery.LoadJobConfig(
    autodetect=True, 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, 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))

Ruby

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

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

require "google/cloud/bigquery"

def load_table_gcs_json_autodetect dataset_id = "your_dataset_id"
  bigquery = Google::Cloud::Bigquery.new
  dataset  = bigquery.dataset dataset_id
  gcs_uri  = "gs://cloud-samples-data/bigquery/us-states/us-states.json"
  table_id = "us_states"

  load_job = dataset.load_job table_id,
                              gcs_uri,
                              format:     "json",
                              autodetect: true
  puts "Starting job #{load_job.job_id}"

  load_job.wait_until_done! # Waits for table load to complete.
  puts "Job finished."

  table = dataset.table table_id
  puts "Loaded #{table.rows_count} rows to table #{table.id}"
end

Detecção automática de esquema para fontes de dados externas

A detecção automática de esquema pode ser usada com fontes de dados externas CSV, JSON e Planilhas Google. Quando a detecção automática de esquema está ativada, o BigQuery tenta inferir automaticamente o esquema a partir dos dados de origem. Se você não ativar a detecção automática de esquema para essas fontes, será necessário fornecer um esquema explícito.

Você não precisa ativar a detecção automática de esquema ao consultar arquivos externos Avro, Parquet, ORC, Firestore ou Datastore. Esses formatos de arquivo são autodescritivos, portanto, o BigQuery infere automaticamente o esquema da tabela a partir dos dados de origem. Para arquivos Parquet, Avro e Orc, é possível fornecer um esquema explícito para substituir o esquema inferido.

Com o console do Google Cloud, ative a detecção automática de esquema marcando a opção Parâmetros de esquema e entrada para Detectar automaticamente.

Com a ferramenta de linha de comando bq, é possível ativar a detecção automática de esquema ao criar um arquivo de definição de tabela para dados CSV, JSON ou Planilhas Google. Ao usar a ferramenta bq para criar um arquivo de definição de tabela, transmita a sinalização --autodetect para o comando mkdef para ativar a detecção automática de esquema ou a sinalização --noautodetect para desativar a detecção automática.

Ao usar a sinalização --autodetect, a configuração autodetect é definida como true na tabela arquivo de definição. Ao usar a sinalização --noautodetect, a configuração autodetect é definida como false. Se você não fornecer uma definição de esquema para a fonte dedados externa ao criar uma definição de tabela e não usar a sinalização --noautodetect ou --autodetect, a configuração autodetect será definida como true por padrão.

Ao criar um arquivo de definição de tabela usando a API, defina o valor da propriedade autodetect como true ou false. Ao definir autodetect como true, a detecção automática será ativada. Definir autodetect como false desativa a detecção automática.

Detalhes da detecção automática

Além de detectar detalhes do esquema, a detecção automática reconhece:

Compactação

O BigQuery reconhece a compactação de arquivos compatíveis com gzip durante a abertura deles.

Valores de data e hora

O BigQuery detecta valores de data e hora com base na formatação dos dados de origem.

Os valores nas colunas DATE precisam estar no seguinte formato: YYYY-MM-DD

Os valores nas colunas TIME precisam estar no seguinte formato: HH:MM:SS[.SSSSSS] (o componente fracionado de segundo é opcional).

Para as colunas TIMESTAMP, o BigQuery detecta uma grande variedade de formatos de carimbo de data/hora, incluindo, mas não se limitando a:

• YYYY-MM-DD HH:MM
• YYYY-MM-DD HH:MM:SS
• YYYY-MM-DD HH:MM:SS.SSSSSS
• YYYY/MM/DD HH:MM

Um carimbo de data/hora também pode conter uma compensação de UTC ou o regulador de fuso horário do UTC ("Z").

Veja alguns exemplos de valores que o BigQuery detecta automaticamente como valores de carimbo de data/hora:

• 2018-08-19 12:11
• 2018-08-19 12:11:35.220
• 2018/08/19 12:11
• 2018-08-19 07:11:35.220 -05:00

Se o BigQuery não reconhecer o formato, ele carregará a coluna como um tipo de dados de string. Nesse caso, talvez seja necessário pré-processar os dados de origem antes de carregá-los. Por exemplo, se você estiver exportando dados CSV de uma planilha, defina o formato de data para corresponder a um dos exemplos mostrados aqui. Como alternativa, transforme os dados depois de carregá-los no BigQuery.

Detecção automática de esquema para dados CSV

Delimitador CSV

O BigQuery detecta os seguintes delimitadores:

• vírgula (,)
• barra vertical (|)
• Tabulação (\t)

Cabeçalho CSV

No BigQuery, os cabeçalhos são inferidos pela comparação da primeira linha do arquivo com outras linhas no arquivo. Se houver apenas strings na primeira linha e nas outras linhas houver outros tipos de dados, o BigQuery presumirá que a primeira linha é de cabeçalho. O BigQuery atribui nomes de colunas com base nos nomes dos campos na linha do cabeçalho. Os nomes podem ser modificados para atender às regras de nomenclatura de colunas no BigQuery. Por exemplo, os espaços serão substituídos por sublinhados.

Caso contrário, o BigQuery considera que a primeira linha é de dados e atribui nomes de coluna genéricos, como string_field_1. Depois que uma tabela é criada, não é possível atualizar os nomes das colunas no esquema, mas é possível alterá-los manualmente. Outra opção é fornecer um esquema explícito em vez de usar a detecção automática.

É possível ter um arquivo CSV com uma linha de cabeçalho, em que todos os campos de dados são strings. Nesse caso, o BigQuery não detectará automaticamente que a primeira linha é um cabeçalho. Use a opção --skip_leading_rows para pular a linha do cabeçalho. Caso contrário, o cabeçalho será importado como dados. Considere também fornecer um esquema explícito neste caso, para que seja possível atribuir nomes de coluna.

Novas linhas entre aspas em CSV

No BigQuery, os caracteres de nova linha entre aspas dentro de um campo CSV são detectados, mas não são interpretados como limite de linha.

Detecção automática de esquema para dados JSON

Campos aninhados e repetidos no JSON

No BigQuery, os campos aninhados e repetidos são inferidos em arquivos JSON. Se um valor de campo for um objeto JSON, o BigQuery carregará a coluna como um tipo RECORD. Se um valor de campo for uma matriz, o BigQuery carregará a coluna como uma coluna repetida. Para um exemplo de dados JSON com dados aninhados e repetidos, consulte Como carregar dados JSON aninhados e repetidos.

Conversão de string

Se você ativar a detecção automática de esquema, o BigQuery converterá strings em tipos booleanos, numéricos ou de data/hora, quando possível. Por exemplo, com os dados JSON a seguir, a detecção automática de esquema converte o campo id em uma coluna INTEGER:

{ "name":"Alice","id":"12"}
{ "name":"Bob","id":"34"}
{ "name":"Charles","id":"45"}

Para mais informações, consulte Como carregar dados JSON do Cloud Storage.

Detecção automática de esquema nas Planilhas Google

Nas Planilhas, o BigQuery detecta automaticamente se a primeira linha é uma linha de cabeçalho, semelhante à detecção automática de arquivos CSV. Se a primeira linha for identificada como um cabeçalho, o BigQuery atribuirá nomes de colunas com base nos nomes dos campos na linha do cabeçalho e pulará a linha. Os nomes podem ser modificados para atender às regras de nomenclatura de colunas no BigQuery. Por exemplo, os espaços serão substituídos por sublinhados.

Segurança de tabelas

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