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

Detecção automática de esquema

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 inicia o processo de inferência selecionando um arquivo aleatório na fonte de dados e verificando até 100 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.

Para ver o esquema detectado de uma tabela:

  • Use o comando bq show da ferramenta da linha de comando
  • Usar o Console do Cloud ou a IU da Web clássica para visualizar o esquema da tabela

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 SQL pertinente a ele.

Para saber mais sobre conversões de tipos de dados, consulte:

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

Para ativar a detecção automática de esquema ao carregar dados, siga estas instruções:

  • Console do Cloud: na seção Esquema, em Detecção automática, marque a opção Parâmetros de esquema e entrada.
  • IU da Web clássica do BigQuery: na seção Esquema, marque a opção Detectar automaticamente.
  • CLI: use o comando bq load com o parâmetro --autodetect.

Quando ativada, o BigQuery tenta inferir automaticamente o esquema dos arquivos CSV e JSON.

A detecção automática de esquema não é usada com arquivos Avro, Parquet e ORC ou arquivos de exportação do Firestore ou 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. Abra a IU da Web do BigQuery no Console do Cloud.
    Acessar o Console do Cloud

  2. Selecione um conjunto de dados na seção Recursos do painel de navegação.

  3. Clique em Criar tabela no lado direito da janela.

    Criar tabela

  4. Na seção Origem da página Criar tabela, faça o seguinte:

    • 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 na IU da Web do BigQuery, mas há compatibilidade com caracteres curinga. O intervalo do Cloud Storage precisa estar no mesmo local que o conjunto de dados que contém a tabela que você está criando.

      Selecionar arquivo

    • Em Formato de arquivo, selecione CSV ou JSON.

  5. Na página Criar tabela, 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. Clique em Criar tabela.

IU clássica

  1. Acesse a IU da Web do BigQuery.
    Acessar a IU da Web do BigQuery

  2. Clique no ícone de seta para baixo ícone de seta para baixo ao lado do nome do conjunto de dados na navegação e clique em Criar nova tabela.

    Observação: na IU, o processo de carregamento de dados é igual ao de criação de uma tabela.
  3. Na página Criar tabela:

    • Em Dados de origem, clique em Criar da origem.
    • Em Tabela de destino, escolha o conjunto de dados e insira o nome da tabela no campo Nome da tabela de destino.
    • Em Esquema, clique em Detectar automaticamente para determinar o esquema.

      link da detecção automática

    • Clique em Criar tabela.

CLI

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

(Opcional) Forneça a sinalização --location e defina o valor como seu 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
    

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. Defina um valor padrão para o local usando o arquivo .bigqueryr;
  • format é NEWLINE_DELIMITED_JSON ou CSV;
  • dataset é o conjunto de dados que contém a tabela em que os dados serão carregados;
  • table é o nome da tabela em que os dados serão carregados;
  • path_to_source é o local do arquivo CSV ou JSON.

Exemplos:

Digite o seguinte comando para carregar myfile.csv de sua máquina local para uma tabela chamada mytable, que é armazenada em um conjunto de dados chamado mydataset.

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

Digite o seguinte comando para carregar myfile.json de sua máquina local para uma tabela chamada mytable, que é 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 via programação. 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. Defina a detecção automática do esquema como true usando a propriedade autodetect.

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

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
    }
    

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 (em inglês).

// 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 do BigQuery para PHP (em inglês).

Ver no GitHub (em inglês) Feedback
use Google\Cloud\BigQuery\BigQueryClient;
    use Google\Cloud\Core\ExponentialBackoff;

    /** Uncomment and populate these variables in your code */
    // $projectId  = 'The Google project ID';
    // $datasetId  = 'The BigQuery dataset ID';

    // instantiate the bigquery table service
    $bigQuery = new BigQueryClient([
        'projectId' => $projectId,
    ]);
    $dataset = $bigQuery->dataset($datasetId);
    $table = $dataset->table('us_states');

    // 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);
    // poll the job until it is complete
    $backoff = new ExponentialBackoff(10);
    $backoff->execute(function () use ($job) {
        print('Waiting for job to complete' . PHP_EOL);
        $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 do BigQuery para Python (em inglês).

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

    dataset_ref = client.dataset(dataset_id)
    job_config = bigquery.LoadJobConfig()
    job_config.autodetect = True
    job_config.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, dataset_ref.table("us_states"), job_config=job_config
    )  # API request
    print("Starting job {}".format(load_job.job_id))

    load_job.result()  # Waits for table load to complete.
    print("Job finished.")

    destination_table = client.get_table(dataset_ref.table("us_states"))
    print("Loaded {} rows.".format(destination_table.num_rows))

Ruby

Antes de testar este exemplo, 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 do BigQuery para Ruby.

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

Para ativar a detecção automática de esquema quando você cria uma tabela vinculada a uma fonte de dados externa:

  • No Console do Cloud, em Detectar automaticamente, marque a opção Parâmetros de esquema e entrada.
  • Na IU da Web clássica do BigQuery, marque a opção Detectar automaticamente.

Quando ativado, o BigQuery faz uma tentativa de inferir automaticamente o esquema para fontes de dados CSV e JSON externas.

Atualmente, não é possível ativar a detecção automática de esquema no Planilhas Google para fontes de dados externas com o Console do Cloud ou a IU da Web clássica. Além disso, não é possível usar a detecção automática de esquema com arquivos externos Avro ou arquivos de exportação do Firestore ou do Datastore. Ao criar uma tabela vinculada a um desses tipos de arquivo, o BigQuery recupera o esquema automaticamente dos dados de origem autodescritivos.

Usando a CLI, é 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 CLI para criar um arquivo de definição de tabela, é possível passar a sinalização --autodetect para o comando mkdef, para que a detecção automática de esquema seja ativada. Se preferir, é possível passar 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 de definição de arquivo. 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 de dados 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. Se definir autodetect como false, ela será desativada.

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.

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 conjunto de dados. Se houver apenas strings na primeira linha e nas outras não, presume-se que a primeira linha seja de cabeçalho.

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.

Datas

Ao usar a detecção de esquema para dados JSON ou CSV, os valores nas colunas DATE precisam usar um traço (-) e estar no seguinte formato: YYYY-MM-DD (ano-mês-dia).

Carimbos de data/hora

O BigQuery detecta vários formatos de carimbo de data/hora, como, por exemplo:

  • yyyy-mm-dd
  • yyyy-mm-dd hh:mm:ss
  • yyyy-mm-dd hh:mm:ss.mmm

Um carimbo de data/hora também pode conter uma compensação de UTC e o regulador de fuso horário do UTC: Z. Carimbos com base em um número inteiro também são compatíveis.

Ao usar a detecção de esquema para dados JSON ou CSV, os valores nas colunas TIMESTAMP precisam usar um traço (-) para a parte da data do carimbo de data/hora. A data precisa estar no seguinte formato: YYYY-MM-DD (ano-mês-dia). A parte hh:mm:ss (hora-minuto-segundo) do carimbo de data/hora precisa usar um separador de dois pontos (:).

Exemplos de carimbo de data/hora

Veja a seguir exemplos de formatos de carimbo de data/hora detectados automaticamente pelo BigQuery:

  • 253402300799
  • 2018-07-05 12:54:00 UTC
  • 2018-08-19 07:11:35.220 -05:00
  • 2018-08-19 12:11:35.220 UTC
  • 2018-08-19T12:11:35.220Z
  • 2.53402300799e11
  • 2018-08-19 12:11:35.220000
  • 2018-08-19 12:11:35.220