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'échantillon.

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

  • Utilisez la commande bq show de l'outil de ligne de commande.
  • Utilisez la console GCP 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 Schéma, cochez l'option Détection automatique.
  • CLI : utilisez 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. Ouvrez l'UI Web de BigQuery dans Cloud Console.
    Accéder à Cloud Console

  2. Sélectionnez un jeu de données dans la section Ressources du panneau de navigation.

  3. Cliquez sur Create table (Créer une table) dans la partie droite de la fenêtre.

    Créer une table

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

    • Dans le champ 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 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 volet de navigation, cliquez sur la flèche vers le bas flèche vers le bas à côté du nom de l'ensemble de données, puis sur 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.
  3. Sur la page Créer une table :

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

      lien vers la détection automatique

    • Cliquez sur Créer une table.

CLI

Exécutez la commande bq load avec l'option --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
    

Où :

  • location est le nom de votre emplacement. L'option --location est facultative. Par exemple, si vous utilisez BigQuery dans la région de Tokyo, vous pouvez définir la valeur de l'option sur asia-northeast1. Vous pouvez spécifier une valeur par défaut pour votre emplacement à l'aide du fichier .bigqueryrc.
  • format correspond à NEWLINE_DELIMITED_JSON ou à CSV.
  • dataset est l'ensemble de données contenant la table dans laquelle vous chargez les données.
  • table est le 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 le fichier myfile.csv depuis votre ordinateur local dans une table nommée mytable, qui est 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 le fichier myfile.json depuis votre ordinateur local dans une table nommée mytable, qui est 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 BigQuery 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. Définissez l'autodétection de schéma sur true à l'aide de la propriété autodetect.

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 en langage 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

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

Pour activer la détection automatique de schéma lorsque vous créez une table liée à une source de données externe :

  • 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'aide de l'interface de ligne de commande, vous pouvez 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 la CLI pour créer un fichier de définition de table, vous pouvez transmettre l'option --autodetect à la commande mkdef pour activer la détection automatique de schéma ou transmettre 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, mais que ce n'est pas le cas pour les autres lignes, BigQuery suppose que la première ligne est une ligne d'en-tête.

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.

Dates

Lorsque vous utilisez la détection de schéma avec des données JSON ou CSV, les valeurs des colonnes DATE doivent utiliser le tiret (-) comme séparateur pour la partie date de l'horodatage et la date doit être au format suivant : YYYY-MM-DD (année-mois-jour).

Horodatages

BigQuery détecte un grand nombre de formats d'horodatage, y compris, mais sans s'y limiter :

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

Un horodatage peut également contenir un décalage UTC et l'indicateur de zone UTC Z. Les valeurs d'horodatage basées sur des nombres entiers sont également acceptées.

Lorsque vous utilisez la détection de schéma avec des données JSON ou CSV, les valeurs des colonnes TIMESTAMP doivent utiliser le tiret (-) comme séparateur pour la partie date de l'horodatage et la date doit être au format suivant : YYYY-MM-DD (année-mois-jour). La partie hh:mm:ss (heure-minute-seconde) de l'horodatage doit utiliser le signe deux-points (:) comme séparateur.

Exemples d'horodatage

Vous trouverez ci-dessous des exemples de formats d'horodatage détectés automatiquement par BigQuery :

  • 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