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 Automatically detect.
  • 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 página de BigQuery.

    Ir a la página BigQuery

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

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

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

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