Usa la detección automática de esquemas

Detección automática de esquemas

La detección automática de esquemas permite que BigQuery infiera el esquema para los datos de CSV, JSON u Hojas de cálculo de Google. 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 infiere el tipo de datos para cada columna. BigQuery selecciona un archivo aleatorio en la fuente de datos y analiza hasta las primeras 500 filas de datos para usar como muestra representativa. Luego, BigQuery examina cada campo y trata de asignarle un tipo de datos según los valores de la muestra. Si todas las filas de una columna están vacías, la detección automática se establecerá de forma predeterminada en el tipo de datos STRING para la columna.

Si no habilitas la detección automática de esquemas para los datos de formato CSV, JSON o de Hojas de cálculo de Google, debes proporcionar el esquema de forma manual cuando crees la tabla.

No es necesario habilitar la detección automática de esquemas para los archivos de exportación de Avro, Parquet, ORC, Firestore export o Datastore. Estos formatos de archivo son autodescriptivos, por lo que BigQuery infiere de forma automática el esquema de tabla a partir de los datos de origen. Para los archivos de Parquet, Avro y Orc, puedes proporcionar un esquema explícito a fin de anular el esquema inferido.

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

• Uso de consola de Google Cloud.
• El comando bq show de la herramienta de línea de comandos de bq

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

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

• Conversión de tipos de datos cuando se cargan datos desde Datastore
• Conversión de tipos de datos cuando se cargan datos desde Firestore
• Conversiones de Avro
• Conversiones de Parquet
• Conversiones de ORC

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 la consola de Google Cloud, en la sección Esquema, en Detección automática, marca la opción Esquema y parámetros de entrada.
• En la herramienta de línea de comandos de bq load, usa el comando --autodetect con el parámetro .

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 lógica de detección automática infiere los tipos de campo del esquema mediante la lectura de las primeras 500 filas de datos. Las líneas iniciales se omiten si la marca --skip_leading_rows está presente. Los tipos de campo se basan en las filas que tienen más campos. Por lo tanto, la detección automática debería funcionar siempre y cuando haya al menos una fila de datos que tenga valores en cada columna o campo.

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:

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

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

No necesitas habilitar la detección automática de esquemas cuando consultas archivos de exportación externos de Avro, Parquet, ORC, Firestore export o Datastore. Estos formatos de archivo son autodescriptivos, por lo que BigQuery infiere de forma automática el esquema de tabla a partir de los datos de origen. Para los archivos de Parquet, Avro y Orc, puedes proporcionar un esquema explícito a fin de anular el esquema inferido.

Con la consola de Google Cloud, puedes habilitar la detección automática de esquemas si marcas la opción Esquema y parámetros de entrada en 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 bq para 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 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.

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.

Detección automática de esquemas para datos CSV

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 archivo. Si la primera línea solo contiene cadenas y las otras contienen otros tipos de datos, BigQuery supone que la primera fila es una fila de encabezado. BigQuery asigna nombres de columnas según los nombres de campo en la fila del 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 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.

Detección automática de esquemas para datos JSON

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.

Conversión de strings

Si habilitas la detección automática de esquemas, BigQuery convierte las strings en tipos booleanos, numéricos o de fecha y hora cuando sea posible. Por ejemplo, con los siguientes datos de JSON, la detección automática de esquemas convierte el campo id en una columna INTEGER:

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

Si deseas obtener más información, consulta Carga datos JSON desde Cloud Storage.

Detección automática de esquemas para Hojas de cálculo de Google

En el caso de las Hojas de cálculo, BigQuery detecta de forma automática si la primera fila es una fila de encabezado, similar a la detección automática de los archivos CSV. Si se identifica una primera línea como encabezado, BigQuery asigna nombres de columnas según los nombres de campo en la fila del encabezado y omite la fila. 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.

Seguridad de las tablas

Para controlar el acceso a las tablas en BigQuery, consulta Introducción a los controles de acceso a tablas.