Usar a deteção automática de esquemas

Deteção automática do esquema

A deteção automática de esquemas permite ao BigQuery inferir o esquema para dados CSV, JSON ou do Google Sheets. A deteção automática de esquemas está disponível quando carrega dados para o BigQuery e quando consulta uma origem de dados externa.

Quando a deteção automática está ativada, o BigQuery infere o tipo de dados de cada coluna. O BigQuery seleciona um ficheiro aleatório na origem de dados e analisa até às primeiras 500 linhas de dados para usar como amostra representativa. Em seguida, o BigQuery examina cada campo e tenta atribuir um tipo de dados a esse campo com base nos valores no exemplo. Se todas as linhas numa coluna estiverem vazias, a deteção automática usa o tipo de dados STRING para a coluna.

Se não ativar a deteção automática de esquemas para dados CSV, JSON ou do Google Sheets, tem de fornecer o esquema manualmente quando criar a tabela.

Não tem de ativar a deteção automática de esquemas para ficheiros Avro, Parquet, ORC, de exportação do Firestore ou de exportação do Datastore. Estes formatos de ficheiros são autodescritivos, pelo que o BigQuery infere automaticamente o esquema da tabela a partir dos dados de origem. Para ficheiros Parquet, Avro e Orc, pode fornecer opcionalmente um esquema explícito para substituir o esquema inferido.

Pode ver o esquema detetado para uma tabela das seguintes formas:

• Use a Trusted Cloud consola.
• Use o comando bq show da ferramenta de linhas de comando bq.

Quando o BigQuery deteta esquemas, pode, em raras ocasiões, alterar o nome de um campo para o tornar compatível com a sintaxe do GoogleSQL.

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

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

Carregar dados através da deteção automática de esquemas

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

• Na Trusted Cloud consola, na secção Esquema, para Detetar automaticamente, selecione a opção Esquema e parâmetros de entrada.
• Na ferramenta de linhas de comando bq, use o comando bq load com o parâmetro --autodetect.

Quando a deteção automática de esquemas está ativada, o BigQuery faz uma tentativa de melhor esforço para inferir automaticamente o esquema para ficheiros CSV e JSON. A lógica de deteção automática infere os tipos de campos de esquema lendo até às primeiras 500 linhas de dados. As linhas iniciais são ignoradas se a flag --skip_leading_rows estiver presente. Os tipos de campos baseiam-se nas linhas com mais campos. Por conseguinte, a deteção automática deve funcionar como esperado, desde que exista, pelo menos, uma linha de dados com valores em todas as colunas/campos.

A deteção automática do esquema não é usada com ficheiros Avro, ficheiros Parquet, ficheiros ORC, ficheiros de exportação do Firestore ou ficheiros de exportação do Datastore. Quando carrega estes ficheiros para o BigQuery, o esquema da tabela é automaticamente obtido dos dados de origem autodescritivos.

Para usar a deteção automática de esquemas quando carrega dados JSON ou CSV:

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

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

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

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
}

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

// 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();

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

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

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

Deteção automática de esquemas para origens de dados externas

A deteção automática de esquemas pode ser usada com origens de dados externos CSV, JSON e do Google Sheets. Quando a deteção automática de esquemas está ativada, o BigQuery faz uma tentativa de inferir automaticamente o esquema a partir dos dados de origem. Se não ativar a deteção automática de esquemas para estas origens, tem de fornecer um esquema explícito.

Não precisa de ativar a deteção automática de esquemas quando consulta ficheiros externos Avro, Parquet, ORC, de exportação do Firestore ou de exportação do Datastore. Estes formatos de ficheiros são autodescritivos, pelo que o BigQuery infere automaticamente o esquema da tabela a partir dos dados de origem. Para ficheiros Parquet, Avro e Orc, pode fornecer opcionalmente um esquema explícito para substituir o esquema inferido.

Usando a Trusted Cloud consola, pode ativar a deteção automática de esquemas selecionando a opção Esquema e parâmetros de entrada para Deteção automática.

Com a ferramenta de linha de comandos bq, pode ativar a deteção automática de esquemas quando criar um ficheiro de definição de tabelas para dados CSV, JSON ou do Google Sheets. Quando usar a ferramenta bq para criar um ficheiro de definição de tabela, transmita a flag --autodetect para o comando mkdef para ativar a deteção automática de esquemas ou transmita a flag --noautodetect para desativar a deteção automática.

Quando usa a flag --autodetect, a definição autodetect é definida como true no ficheiro de definição da tabela. Quando usa a flag --noautodetect, a definição autodetect é definida como false. Se não fornecer uma definição de esquema para a origem de dados externa quando criar uma definição de tabela e não usar a flag --noautodetect ou --autodetect, a definição autodetect é predefinida como true.

Quando cria um ficheiro de definição de tabela através da API, defina o valor da propriedade autodetect como true ou false. Definir autodetect como true ativa a deteção automática. Se definir autodetect como false, a deteção automática é desativada.

Detalhes da deteção automática

Além de detetar detalhes do esquema, a deteção automática reconhece o seguinte:

Compressão

O BigQuery reconhece a compressão de ficheiros compatível com gzip quando abre um ficheiro.

Valores de data e hora

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

Os valores nas colunas DATE têm de estar no seguinte formato: YYYY-MM-DD.

Os valores nas colunas TIME têm de estar no seguinte formato: HH:MM:SS[.SSSSSS] (o componente de fração de segundo é opcional).

Para colunas TIMESTAMP, o BigQuery deteta uma grande variedade de formatos de data/hora, incluindo, entre outros:

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

Uma data/hora também pode conter um desvio UTC ou o designador de zona UTC ("Z").

Seguem-se alguns exemplos de valores que o BigQuery deteta automaticamente como valores de indicação de tempo:

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

Se a deteção automática não estiver ativada e o seu valor estiver num formato não presente nos exemplos anteriores, o BigQuery só pode carregar a coluna como um tipo de dados STRING. Pode ativar a deteção automática para que o BigQuery reconheça estas colunas como datas/horas. Por exemplo, o BigQuery só carrega 2025-06-16T16:55:22Z como uma data/hora se ativar a deteção automática.

Em alternativa, pode pré-processar os dados de origem antes de os carregar. Por exemplo, se estiver a exportar dados CSV de uma folha de cálculo, defina o formato de data de modo a corresponder a um dos exemplos apresentados aqui. Também pode transformar os dados depois de os carregar para o BigQuery.

Deteção automática do esquema para dados CSV

Delimitador CSV

O BigQuery deteta os seguintes delimitadores:

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

Cabeçalho do CSV

O BigQuery infere os cabeçalhos comparando a primeira linha do ficheiro com outras linhas no ficheiro. Se a primeira linha contiver apenas strings e as outras linhas contiverem outros tipos de dados, o BigQuery assume que a primeira linha é uma 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 cumprir as regras de nomenclatura para colunas no BigQuery. Por exemplo, os espaços são substituídos por sublinhados.

Caso contrário, o BigQuery assume que a primeira linha é uma linha de dados e atribui nomes de colunas genéricos, como string_field_1. Tenha em atenção que, depois de criar uma tabela, não é possível atualizar os nomes das colunas no esquema, embora possa alterar os nomes manualmente depois de criar a tabela. Outra opção é fornecer um esquema explícito em vez de usar a deteção automática.

Pode ter um ficheiro CSV com uma linha de cabeçalho, em que todos os campos de dados são strings. Nesse caso, o BigQuery não deteta automaticamente que a primeira linha é um cabeçalho. Use a opção --skip_leading_rows para ignorar a linha de cabeçalho. Caso contrário, o cabeçalho é importado como dados. Considere também fornecer um esquema explícito neste caso para poder atribuir nomes de colunas.

Novas linhas entre aspas no CSV

O BigQuery deteta carateres de nova linha entre aspas num campo CSV e não interpreta o caráter de nova linha entre aspas como um limite de linha.

Deteção automática de esquemas para dados JSON

Campos JSON aninhados e repetidos

O BigQuery infere campos aninhados e repetidos em ficheiros JSON. Se um valor de campo for um objeto JSON, o BigQuery carrega a coluna como um tipo RECORD. Se o valor de um campo for uma matriz, o BigQuery carrega a coluna como uma coluna repetida. Para ver um exemplo de dados JSON com dados aninhados e repetidos, consulte o artigo Carregar dados JSON aninhados e repetidos.

Conversão de string

Se ativar a deteção automática de esquemas, o BigQuery converte as strings em tipos booleanos, numéricos ou de data/hora, quando possível. Por exemplo, usando os seguintes dados JSON, a deteção automática do esquema converte o campo id numa coluna INTEGER:

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

Para mais informações, consulte o artigo Carregar dados JSON a partir do Cloud Storage.

Deteção automática de esquemas para o Google Sheets

Para o Sheets, o BigQuery deteta automaticamente se a primeira linha é uma linha de cabeçalho, de forma semelhante à deteção automática de ficheiros CSV. Se a primeira linha for identificada como um cabeçalho, o BigQuery atribui nomes de colunas com base nos nomes dos campos na linha do cabeçalho e ignora a linha. Os nomes podem ser modificados para cumprir as regras de nomenclatura para colunas no BigQuery. Por exemplo, os espaços são substituídos por sublinhados.

Segurança da mesa

Para controlar o acesso a tabelas no BigQuery, consulte o artigo Controle o acesso a recursos com a IAM.