Utiliser la détection automatique de schéma

Détection automatique de schéma

La détection automatique de schéma est disponible lorsque vous chargez des données dans BigQuery et lorsque vous interrogez une source de données externe.

Lorsque la détection automatique est activée, BigQuery lance le processus d'inférence en sélectionnant un fichier aléatoire dans la source de données et en analysant jusqu'à 100 lignes de données à utiliser comme échantillon représentatif. BigQuery procède ensuite à l'examen de chaque champ et essaie de leur affecter un type de données en fonction des valeurs de l'exemple.

Pour afficher le schéma détecté pour une table, procédez comme suit :

  • À partir de la ligne de commande, utilisez la commande bq show.
  • Utilisez Cloud Console ou l'interface utilisateur Web classique.

Dans de rares cas, lorsque BigQuery détecte des schémas, certains noms de champ peuvent être modifiés pour être compatibles avec la syntaxe SQL BigQuery.

Pour plus d'informations sur les conversions de type de données, consultez les sections suivantes :

Charger des données à l'aide de la détection automatique de schéma

Pour activer la détection automatique de schéma lors du chargement des données, procédez comme suit :

  • Cloud Console : dans la section Schéma, pour la Détection automatique, cochez l'option Schéma et paramètres d'entrée.
  • Interface utilisateur Web classique de BigQuery : dans la section Schema (Schéma), cochez l'option Automatically detect (Détection automatique).
  • bq : exécutez la commande bq load avec le paramètre --autodetect.

Lorsque cette option est activée, BigQuery tente au mieux de déduire automatiquement le schéma des fichiers CSV et JSON.

La détection automatique de schéma n'est pas utilisée avec les fichiers Avro, Parquet ou ORC, et les fichiers d'exportation Firestore ou Datastore. Lorsque vous chargez ces fichiers dans BigQuery, le schéma de la table est automatiquement extrait des données sources auto-descriptives.

Pour utiliser la détection automatique de schéma lorsque vous chargez des données JSON ou CSV, procédez comme suit :

Console

  1. Dans Cloud Console, accédez à l'interface utilisateur Web de BigQuery.
    Accéder à Cloud Console

  2. Dans la section Ressources du panneau de navigation, sélectionnez un ensemble de données.

  3. Cliquez sur Create table (Créer une table).

    Créer une table

  4. Dans la section Source de la page Créer une table :

    • Dans le champ Create table from (Créer une table à partir de), sélectionnez le type de source souhaité.
    • Dans le champ source, recherchez le fichier ou le bucket Cloud Storage, ou saisissez l'URI Cloud Storage. Sachez que vous ne pouvez pas inclure plusieurs URI dans l'UI Web de BigQuery. En revanche, les caractères génériques sont acceptés. Le bucket Cloud Storage doit se trouver au même emplacement que l'ensemble de données contenant la table que vous créez.

      Sélectionner un fichier

    • Dans le champ File format (Format de fichier), sélectionnez CSV ou JSON.

  5. Dans la section Destination de la page Créer une table :

    • Sous Dataset name (Nom de l'ensemble de données), sélectionnez l'ensemble de données approprié.

      Sélectionner un ensemble de données

    • Dans le champ Nom de la table, saisissez le nom de la table que vous créez.

    • Vérifiez que Type de table est défini sur Table native.

  6. Cliquez sur Créer une table.

UI classique

  1. Accédez à l'UI Web de BigQuery.
    Accéder à l'UI Web de BigQuery

  2. Dans le panneau de navigation, à côté du nom de votre ensemble de données, cliquez sur la flèche vers le bas Flèche vers le bas.

  3. Cliquez sur Create new table (Créer une table).

    Remarque : Dans l'interface utilisateur, le processus de chargement des données est identique au processus de création d'une table.
  4. Sur la page Créer une table :

    • Dans la section Données sources, cliquez sur Créer à partir de la source.
    • Dans la section Table de destination, choisissez votre ensemble de données et entrez le nom de la table dans le champ Nom de la table de destination.
    • Dans la section Schema (Schéma), cliquez sur Automatically detect (Détection automatique) pour déterminer le schéma.

      Lien vers la détection automatique

    • Cliquez sur Create Table (Créer une table).

bq

Exécutez la commande bq load avec le paramètre --autodetect.

(Facultatif) Spécifiez l'option --location et définissez la valeur correspondant à votre emplacement.

La commande suivante charge un fichier à l'aide de la détection automatique de schéma :

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

Remplacez les éléments suivants :

  • LOCATION : nom de votre emplacement. L'option --location est facultative. Par exemple, si vous utilisez BigQuery dans la région de Tokyo, définissez la valeur de l'option sur asia-northeast1. Vous pouvez définir une valeur par défaut correspondant à l'emplacement en utilisant le fichier .bigqueryrc.
  • FORMAT : NEWLINE_DELIMITED_JSON ou CSV.
  • DATASET : ensemble de données contenant la table dans laquelle vous chargez des données.
  • TABLE : nom de la table dans laquelle vous chargez des données.
  • path_to_source : correspond à l'emplacement du fichier de données CSV ou JSON.

Exemples :

Saisissez la commande suivante pour charger myfile.csv à partir de votre ordinateur local dans une table nommée mytable stockée dans un ensemble de données nommé mydataset.

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

Saisissez la commande suivante pour charger myfile.json à partir de votre ordinateur local dans une table nommée mytable stockée dans un ensemble de données nommé mydataset.

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

API

  1. Créez une tâche load qui pointe vers les données sources. Pour en savoir plus sur la création de tâches, consultez la page Exécuter des tâches de manière automatisée. Spécifiez l'emplacement dans la propriété location de la section jobReference.

  2. Spécifiez le format de données en définissant la propriété sourceFormat. Pour utiliser la détection automatique de schéma, cette valeur doit être définie sur NEWLINE_DELIMITED_JSON ou CSV.

  3. Utilisez la propriété autodetect pour définir la détection automatique de schéma sur true.

Go

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Go décrite dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence sur l'API BigQuery 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

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Node.js décrite dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery 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

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour PHP dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery 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

Pour activer la détection automatique de schéma, définissez la propriété LoadJobConfig.autodetect sur True.

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Python décrite dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery en langage Python.

# 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

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Ruby décrite dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery 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

Détection automatique de schéma pour les sources de données externes

Lorsque vous créez une table associée à une source de données externe, activez la détection automatique de schéma :

  • Dans Cloud Console, sous Détection automatique, cochez l'option Schéma et paramètres d'entrée.
  • Dans l'interface utilisateur Web classique de BigQuery, cochez l'option Détection automatique.

Lorsque cette option est activée, BigQuery tente au mieux de déduire automatiquement le schéma des sources de données externes CSV et JSON.

Il n'est actuellement pas possible d'activer la détection automatique de schéma pour les sources de données Google Sheets externes à l'aide de Cloud Console ou de l'UI Web classique. De plus, la détection automatique de schéma n'est pas utilisée avec les fichiers Avro externes, ni avec les fichiers d'exportation Firestore ou Datastore. Lorsque vous créez une table liée à l'un de ces types de fichiers, BigQuery extrait automatiquement le schéma à partir des données sources auto-descriptives.

L'interface de ligne de commande vous permet d'activer la détection automatique de schéma lorsque vous créez un fichier de définition de table pour les données CSV, JSON ou Google Sheets. Lorsque vous utilisez l'interface de ligne de commande pour créer un fichier de définition de table, transmettez l'option --autodetect à la commande mkdef pour activer la détection automatique de schéma ou transmettez l'option --noautodetect pour désactiver la détection automatique.

Lorsque vous utilisez l'option --autodetect, le paramètre autodetect est défini sur true dans le fichier de définition de table. Lorsque vous utilisez l'option --noautodetect, le paramètre autodetect est défini sur false dans le fichier de définition de table. Si vous ne fournissez pas de définition de schéma pour la source de données externe lorsque vous créez une définition de table et que vous n'utilisez pas l'option --noautodetect ou --autodetect, le paramètre autodetect est défini par défaut sur true.

Lorsque vous créez un fichier de définition de table à l'aide de l'API, définissez la valeur de la propriété autodetect sur true ou false. Si la propriété autodetect est définie sur true, la détection automatique est activée. Si la propriété autodetect est définie sur false, la détection automatique est désactivée.

Détails de la détection automatique

En plus de détecter les détails du schéma, la détection automatique reconnaît les éléments suivants :

Compression

BigQuery reconnaît la compression de fichier compatible avec gzip lors de l'ouverture d'un fichier.

Délimiteur de fichier CSV

BigQuery détecte les délimiteurs suivants :

  • Virgule ( , )
  • Barre verticale ( | )
  • Tabulation ( \t )

En-tête CSV

BigQuery déduit les en-têtes en comparant la première ligne du fichier avec les autres lignes de l'ensemble de données. Si la première ligne ne contient que des chaînes et que les autres lignes contiennent d'autres types de données, BigQuery suppose que la première ligne est une ligne d'en-tête. Dans ce cas, BigQuery attribue des noms de colonnes en fonction des noms de champs dans la ligne d'en-tête. Les noms peuvent être modifiés pour répondre aux règles de dénomination des colonnes de BigQuery. Par exemple, les espaces seront remplacés par des traits de soulignement.

Sinon, BigQuery considère que la première ligne est une ligne de données et attribue des noms de colonnes génériques tels que string_field_1. Notez qu'une fois la table créée, les noms de colonne ne peuvent pas être mis à jour dans le schéma, mais vous pouvez les modifier manuellement après la création de la table. Une autre option consiste à fournir un schéma explicite au lieu d'utiliser la détection automatique.

Vous pouvez avoir un fichier CSV avec une ligne d'en-tête, où tous les champs de données sont des chaînes. Dans ce cas, BigQuery ne détecte pas automatiquement que la première ligne est un en-tête. Utilisez l'option -skip_leading_rows pour ignorer la ligne d'en-tête. Sinon, l'en-tête sera importé en tant que ligne de données. Pensez également à fournir un schéma explicite dans ce cas, afin de pouvoir attribuer des noms de colonne.

Lignes entre guillemets de fichier CSV

BigQuery détecte les nouvelles lignes entre guillemets dans un champ CSV et ne les interprète pas comme une limite de ligne.

Valeurs de date et d'heure

BigQuery détecte les valeurs de date et d'heure en fonction de la mise en forme des données sources.

Les valeurs des colonnes DATE doivent respecter le format suivant : YYYY-MM-DD.

Les valeurs des colonnes TIME doivent être au format suivant : HH:MM:SS[.SSSSSS] (le composant fractionnaire d'une seconde est facultatif).

Pour les colonnes TIMESTAMP, BigQuery détecte un large éventail de formats d'horodatage, y compris, mais sans s'y limiter :

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

Un horodatage peut également contenir un décalage UTC ou l'indicateur de zone UTC ("Z").

Voici quelques exemples de valeurs que BigQuery détecte automatiquement en tant que valeurs d'horodatage :

  • 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 ne reconnaît pas le format, il charge la colonne en tant que type de données de chaîne. Dans ce cas, vous devrez peut-être prétraiter les données sources avant de les charger. Par exemple, si vous exportez des données CSV à partir d'une feuille de calcul, définissez le format de date afin qu'il corresponde à l'un des exemples présentés ci-dessous. Vous pouvez également transformer les données après leur chargement dans BigQuery.