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.

A fin de ver el esquema que se detecta para una tabla, haz lo siguiente:

  • Desde la línea de comandos, usa el comando bq show.
  • Usa Cloud Console o la IU web clásica para ver el esquema de la tabla.

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

Para obtener información sobre las conversiones de tipos de datos, consulta lo siguiente:

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

Para habilitar la detección automática de esquemas cuando se cargan datos, haz lo siguiente:

  • Cloud Console: En la sección Esquema, en Detección automática, marca la opción Esquema y parámetros de entrada.
  • IU web clásica de BigQuery: En la sección Esquema, marca la opción Detección automática.
  • bq: Usa el comando bq load con el parámetro --autodetect.

Cuando está habilitado, BigQuery trata de inferir de forma automática el esquema para 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 IU web de BigQuery.
    Ir a Cloud Console

  2. En la sección Recursos del panel de navegación, selecciona un conjunto de datos.

  3. Haz clic en Crear tabla (Create table).

    Crear tabla.

  4. En la sección Fuente (Source) de la página Crear tabla (Create table), haz lo siguiente:

    • En Crear tabla desde, selecciona el tipo de fuente que desees.
    • En el campo de origen, busca el archivo o depósito de Cloud Storage o ingresa el URI de Cloud Storage. Ten en cuenta que no puedes incluir varios URI en la IU web de BigQuery, pero sí se admiten comodines. El depósito 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.

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

  6. Haz clic en Crear tabla.

IU clásica

  1. Ve a la IU web de BigQuery.
    Ir a la IU web de BigQuery

  2. Haz clic en el ícono de flecha hacia abajo Ícono de flecha hacia abajo. junto al nombre de tu conjunto de datos en el menú de navegación.

  3. Haz clic en Crear tabla nueva.

    Nota: En la IU, el proceso para cargar datos es el mismo que se usa para crear una tabla.
  4. En la página Crear tabla:

    • En Datos de origen, haz clic en Crear desde la fuente.
    • En Tabla de destino, selecciona tu conjunto de datos y luego ingresa el nombre de la tabla en el campo Nombre de la tabla de destino.
    • En Esquema (Schema), haz clic en Automatically detect a fin de determinar el esquema.

      Vínculo de detección automática.

    • 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: Es 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 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 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. Si deseas obtener más información, consulta la documentación de referencia de la API de BigQuery para Node.js.

// 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 BigQuery para PHP.

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 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
# 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 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. Si deseas 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

Cuando creas una tabla vinculada a una fuente de datos externa, habilita la detección automática de esquemas:

  • En Cloud Console, en Detección automática, marca la opción Esquema y parámetros de entrada.
  • En la IU web clásica de BigQuery, marca la opción Detección automática.

Cuando se habilita esta función, BigQuery intenta inferir de forma automática el esquema de las fuentes de datos externas CSV y JSON.

Actualmente, no puedes habilitar la detección automática de esquemas para las fuentes de datos externas de Hojas de cálculo de Google mediante Cloud Console o la IU web clásica. Además, la detección automática de esquemas no se usa con los archivos Avro externos ni 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 la interfaz de línea de comandos, puedes habilitar la detección automática de esquemas cuando creas un archivo de definición de tablas para datos CSV, JSON o de Hojas de cálculo de Google. Cuando uses la interfaz de línea de comandos para crear un archivo de definición de tablas, pasa la marca --autodetect al comando mkdef a fin de habilitar la detección automática de esquemas o 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 y se inhabilita con la configuración de autodetect como false.

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 contiene solo strings y las otras no, BigQuery supone que la primera fila es una fila de encabezado.

Líneas nuevas con comillas CSV

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

Fechas

Cuando utilizas la detección de esquemas para datos JSON o CSV, en los valores en las columnas DATE de debe usar un separador de guión (-) y el siguiente formato: YYYY-MM-DD (año-mes-día).

Marcas de tiempo

BigQuery detecta una amplia gama de formatos de marca de tiempo, entre los cuales se incluyen los siguientes:

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

Una marca de tiempo también puede contener una compensación UTC y el designador de zona UTC: Z. También se admiten valores de marca de tiempo basados en números enteros.

Cuando utilizas la detección de esquemas para datos JSON o CSV, en los valores en las columnas TIMESTAMP se debe usar un separador de guión (-) para la parte de la fecha de la marca de tiempo, y la fecha debe mostrarse en el siguiente formato: YYYY-MM-DD (año-mes-día). En la parte de hh:mm:ss (horas-minutos-segundos) de la marca de tiempo, se debe usar un separador de dos puntos (:).

Ejemplos de marca de tiempo

Los siguientes son ejemplos de formatos de marca de tiempo que BigQuery detecta de forma automática:

  • 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