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 :

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

  • Console GCP : 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 Cloud Firestore ou Cloud 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 la console GCP.
    Accéder à la console GCP

  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 Create table (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'interface utilisateur 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 Create Table (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 le champ Table type (Type de table) est défini sur Native table (Table native).

  6. Cliquez sur Create table.

UI classique

  1. Accédez à l'interface utilisateur 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 saisissez le nom de la table dans le champ Table name (Nom de la table).
    • 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 Créer une table.

CLI

Exécutez la commande bq load avec l'indicateur --autodetect.

(Facultatif) Spécifiez l'indicateur --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'indicateur --location est facultatif. Par exemple, si vous utilisez BigQuery dans la région de Tokyo, définissez la valeur de l'indicateur sur asia-northeast1. Vous pouvez définir une valeur par défaut correspondant à l'emplacement en utilisant le 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 myfile.csv depuis votre ordinateur local vers la table mytable stockée dans l'ensemble de données mydataset.

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

Saisissez la commande suivante pour charger myfile.csv depuis votre ordinateur local vers la table mytable stockée dans l'ensemble de données mydataset.

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

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. Indiquez votre 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 la détection automatique 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 de l'API BigQuery Go.

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
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())
}

Python

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 relatif à l'utilisation des bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence sur l'API BigQuery Python.

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

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

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 la console GCP, pour 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 externes de Google Sheets à l'aide de la console GCP. 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 Cloud Firestore ou Cloud 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'indicateur --autodetect à la commande mkdef pour activer la détection automatique de schéma ou transmettre l'indicateur --noautodetect pour désactiver la détection automatique.

Lorsque vous utilisez l'indicateur --autodetect, le paramètre autodetect est défini sur true dans le fichier de définition de table. Lorsque vous utilisez l'indicateur ---noautodetect, le paramètre "autodetect" est défini sur false. 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'indicateur --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 pour les données JSON ou CSV, les valeurs des colonnes DATE doivent utiliser un séparateur de tiret (-) et avoir le 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 pour 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 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
Cette page vous a-t-elle été utile ? Évaluez-la :

Envoyer des commentaires concernant…

Besoin d'aide ? Consultez notre page d'assistance.