Usa la detección automática de esquemas

Detección automática de esquemas

La detección automática de esquemas está disponible cuando cargas datos en BigQuery y cuando consultas una fuente de datos externa.

Cuando la detección automática está habilitada, BigQuery inicia el proceso de inferencia mediante la selección de un archivo aleatorio en la fuente de datos y el análisis de hasta 100 filas de datos que usa como muestra representativa. Luego, BigQuery examina cada campo y trata de asignarle un tipo de datos según los valores de la muestra.

Puedes ver el esquema detectado de una tabla de las formas siguientes:

  • Usa Cloud Console.
  • Usa el comando bq showde la herramienta de línea de comandos bq.

En ocasiones, cuando BigQuery detecta esquemas, puede cambiar el nombre de un campo para que sea compatible con la sintaxis SQL de BigQuery.

Para obtener más información sobre las conversiones de tipos de datos, consulta las siguientes páginas:

Carga datos con la detección automática de esquemas

Para habilitar la detección automática de esquemas cuando se cargan datos, usa uno de los siguientes enfoques:

  • En Cloud Console, en la sección Esquema, en Detección automática, marca Esquema y parámetros de entrada.
  • En la herramienta de línea de comandos de bq, usa el comando bq load con el parámetro --autodetect.

Cuando la detección automática de esquemas está habilitada, BigQuery hace un esfuerzo para inferir automáticamente el esquema de los archivos CSV y JSON.

La detección automática de esquemas no se usa con los archivos Avro, ORC o Parquet, ni con los archivos de exportación de Firestore o Datastore. Cuando cargas estos archivos en BigQuery, el esquema de la tabla se recupera de forma automática de los datos de origen autodescriptivos.

Para usar la detección automática de esquemas cuando cargas datos JSON o CSV, haz lo siguiente:

Console

  1. En Cloud Console, ve a la página de BigQuery.

    Ir a BigQuery

  2. En el panel Explorador, expande tu proyecto y selecciona un conjunto de datos.

  3. Expande la opción Acciones y haz clic en Abrir.

  4. En el panel de detalles, haz clic en Crear tabla  (Create table).

  5. En la página Crear tabla, en la sección Fuente haz lo siguiente:

    • En Crear tabla desde, selecciona el tipo de fuente que desees.
    • En el campo de origen, busca el depósito Archivo/Cloud Storage o ingresa el URI de Cloud Storage. Ten en cuenta que no puedes incluir varios URI en Cloud Console, pero se admiten comodines. El bucket de Cloud Storage debe estar en la misma ubicación que el conjunto de datos que contiene la tabla que quieres crear.

      Seleccionar archivo.

    • En Formato del archivo (File format), selecciona CSV o JSON.

  6. En la página Crear tabla, en la sección Destino, realiza lo siguiente:

    • En Nombre del conjunto de datos (Dataset name), selecciona el conjunto de datos que corresponda.

      Seleccionar conjunto de datos.

    • En el campo Nombre de tabla, ingresa el nombre de la tabla que quieres crear.

    • Verifica que Tipo de tabla esté configurado como Tabla nativa.

  7. Haz clic en Crear tabla.

bq

Ejecuta el comando bq load con el parámetro --autodetect.

Opcional: Proporciona la marca --location y configura el valor en tu ubicación.

Con el siguiente comando, se carga un archivo mediante la detección automática de esquemas:

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

Reemplaza lo siguiente:

  • LOCATION: El nombre de tu ubicación. La marca --location es opcional. Por ejemplo, si usas BigQuery en la región de Tokio, configura el valor de la marca como asia-northeast1. Puedes configurar un valor predeterminado para la ubicación mediante el archivo .bigqueryrc.
  • FORMAT: Ya sea NEWLINE_DELIMITED_JSON o CSV.
  • DATASET: Es el conjunto de datos que contiene la tabla en la que deseas cargar los datos.
  • TABLE: Es el nombre de la tabla en la que se están cargando los datos.
  • PATH_TO_SOURCE: Es la ubicación del archivo CSV o JSON.

Ejemplos:

Ingresa el siguiente comando para cargar myfile.csv desde tu máquina local en una tabla llamada mytable que se almacena en un conjunto de datos llamado mydataset.

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

Ingresa el siguiente comando para cargar myfile.json desde tu máquina local en una tabla llamada mytable que se almacena en un conjunto de datos llamado mydataset.

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

API

  1. Crea un trabajo load orientado a los datos de origen. Para obtener información sobre la creación de trabajos, consulta Ejecuta trabajos de BigQuery de manera programática. Especifica tu ubicación en la propiedad location de la sección jobReference.

  2. Para especificar el formato de datos, configura la propiedad sourceFormat. Para usar la detección automática de esquemas, este valor se debe configurar como NEWLINE_DELIMITED_JSON o CSV.

  3. Usa la propiedad autodetect para establecer la detección automática de esquemas en true.

Go

Antes de probar este ejemplo, sigue las instrucciones de configuración para Go que se encuentran en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Si deseas obtener más información, consulta la documentación de referencia de la API de Go de BigQuery.

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

Node.js

Antes de probar este ejemplo, sigue las instrucciones de configuración para Node.js que se encuentran en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Para obtener más información, consulta la Documentación de referencia de la API de Node.js de BigQuery.

// 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 probar este ejemplo, sigue las instrucciones de configuración para PHP incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Si deseas obtener más información, consulta la documentación de referencia de la API de PHP de BigQuery.

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';
// $tableId    = 'us_states';

// 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);
// 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 habilitar la detección automática de esquemas, configura la propiedad LoadJobConfig.autodetect como True.

Antes de probar esta muestra, sigue las instrucciones de configuración para Python incluidas en la Guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Si deseas obtener más información, consulta la documentación de referencia de la API de Python de BigQuery.

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 probar este ejemplo, sigue las instrucciones de configuración para Ruby incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. A fin de obtener más información, consulta la documentación de referencia de la API de 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

Detección automática de esquemas para fuentes de datos externas

La detección automática de esquemas se puede usar con fuentes externas de datos CSV, JSON y de Hojas de cálculo. Cuando la detección automática de esquemas está habilitada, BigQuery hace un esfuerzo para inferir automáticamente el esquema de los datos de origen. Si no habilitas la detección automática de esquemas para estas fuentes, debes proporcionar un esquema explícito.

La detección automática de esquemas no se usa con los archivos externos autodescriptivos, incluidos los formatos Avro, Parquet y ORC, o con los archivos de exportación de Firestore o Datastore. Cuando creas una tabla vinculada a uno de estos tipos de archivos, BigQuery recupera de forma automática el esquema de los datos de origen autodescriptivos.

Con Cloud Console, puedes habilitar la detección automática de esquemas si marcas la opción Esquema y parámetros de entrada para Detección automática.

Mediante la herramienta de línea de comandos de bq, puedes habilitar la detección automática de esquemas mediante la creación de un archivo de definición de tablas para datos de CSV, de JSON o de Hojas de cálculo de Google. Cuando uses la herramienta de bq a fin de crear un archivo de definición de tablas, pasa la marca --autodetect al comando mkdef para habilitar la detección automática de esquemas o pasa la marca --noautodetect. para inhabilitar la detección automática.

Cuando usas la marca --autodetect, autodetect se configura como true en el archivo de definición de tablas. Cuando usas la marca --noautodetect, autodetect se configura como false. Si no proporcionas una definición de esquema para la fuente de datos externa cuando creas una definición de tablas y no usas las marcas --noautodetect o --autodetect, autodetect se configura de forma predeterminada como true.

Cuando crees un archivo de definición de tablas mediante la API, configura el valor de la propiedad autodetect como true o false. La detección automática se habilita con la configuración de autodetect como true Configurar autodetect como false inhabilita la detección automática.

Detalles de la detección automática

Además de detectar los detalles del esquema, la detección automática reconoce lo siguiente:

Compresión

BigQuery reconoce la compresión de archivos compatible con gzip cuando se abre un archivo.

Delimitador CSV

BigQuery detecta los siguientes delimitadores:

  • coma ( , )
  • barra vertical ( | )
  • tabulador ( \t )

Encabezado CSV

BigQuery infiere los encabezados mediante la comparación de la primera fila del archivo con otras filas en el conjunto de datos. Si la primera línea solo contiene strings y las otras contienen otros tipos de datos, BigQuery supone que la primera fila es una fila de encabezado. En ese caso, BigQuery asigna nombres de columnas en función de los nombres del campo en la fila de encabezado. Los nombres pueden modificarse a fin de cumplir con las reglas de nomenclatura para las columnas en BigQuery. Por ejemplo, los espacios se reemplazarán con guiones bajos.

De lo contrario, BigQuery supone que la primera fila es una fila de datos y asigna nombres de columnas genéricos como string_field_1. Ten en cuenta que, después de que se crea una tabla, los nombres de las columnas no se pueden actualizar en el esquema, pero puedes cambiar los nombres de forma manual después de que se crea la tabla. Otra opción es proporcionar un esquema explícito, en lugar de usar la detección automática.

Es posible que tengas un archivo CSV con una fila de encabezado, en la que todos los campos de datos sean strings. En ese caso, BigQuery no detectará de manera automática que la primera fila es un encabezado. Usa la opción -skip_leading_rows para omitir la fila de encabezado. De lo contrario, el encabezado se importará como datos. También considera proporcionar un esquema explícito en este caso para que puedas asignar nombres de columnas.

Saltos de línea con comillas en CSV

BigQuery detecta los caracteres de salto de línea con comillas dentro de un campo CSV y no interpreta el carácter de salto de línea con comillas como un límite de fila.

Valores de fecha y hora

BigQuery detecta los valores de fecha y hora según el formato de los datos de origen.

Los valores de las columnas DATE deben tener el siguiente formato: YYYY-MM-DD.

Los valores en las columnas TIME deben tener el siguiente formato: HH:MM:SS[.SSSSSS] (el componente de segundos fraccionarios es opcional).

Para las columnas TIMESTAMP, BigQuery detecta varios formatos de marca de tiempo, entre los que se incluyen los siguientes:

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

Una marca de tiempo también puede contener un desplazamiento de UTC o el designador de zona UTC (“Z”).

Estos son algunos ejemplos de los valores que BigQuery detectará de manera automática como valores de marca de tiempo:

  • 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

Si BigQuery no reconoce el formato, cargará la columna como un tipo de datos string. En ese caso, es posible que debas procesar de forma previa los datos de origen antes de cargarlos. Por ejemplo, si exportas datos CSV desde una hoja de cálculo, configura el formato de fecha para que coincida con uno de los ejemplos que se muestran aquí. Como alternativa, puedes transformar los datos después de cargarlos en BigQuery.

Campos repetidos y anidados en JSON

BigQuery infiere los campos anidados y repetidos en archivos JSON. Si el valor de un campo es un objeto JSON, BigQuery carga la columna como un tipo RECORD. Si el valor de un campo es un array, BigQuery carga la columna como una columna repetida. Para ver un ejemplo de datos JSON con datos anidados y repetidos, consulta Carga datos JSON anidados y repetidos.