Créer des tables d'objets

Ce document explique comment accéder aux données non structurées dans BigQuery en créant une table d'objets.

Pour créer une table d'objets, vous devez effectuer les tâches suivantes :

  1. Créez une connexion pour lire les informations des objets à partir de Cloud Storage.
  2. Accordez l'autorisation de lire les informations Cloud Storage au compte de service associé à la connexion.
  3. Créez la table d'objets et associez-la à la connexion à l'aide de l'instruction CREATE EXTERNAL TABLE.

Avant de commencer

  1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

  2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  3. Make sure that billing is enabled for your Google Cloud project.

  4. Enable the BigQuery and BigQuery Connection API APIs.

    Enable the APIs

    5.

  5. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  6. Make sure that billing is enabled for your Google Cloud project.

  7. Enable the BigQuery and BigQuery Connection API APIs.

    Enable the APIs

    8.
  8. Assurez-vous que votre administrateur BigQuery a créé une connexion et configuré l'accès à Cloud Storage.

Rôles requis

Pour utiliser des tables d'objets, vos utilisateurs ont besoin des autorisations IAM suivantes en fonction de leur rôle dans votre organisation. Pour en savoir plus sur les rôles utilisateur, consultez la page Modèle de sécurité. Pour en savoir plus sur l'attribution d'autorisations, consultez la page Afficher les rôles pouvant être attribués sur des ressources.

  • Administrateur de lac de données

    Pour obtenir les autorisations nécessaires pour vous connecter à Cloud Storage, demandez à votre administrateur de vous accorder le rôle d'administrateur de connexion BigQuery (roles/bigquery.connectionAdmin) sur le projet.

    Pour obtenir les autorisations nécessaires pour créer et gérer des buckets Cloud Storage, demandez à votre administrateur de vous attribuer le rôle d'administrateur de l'espace de stockage (roles/storage.admin) sur le projet.

    Ce rôle prédéfini contient les autorisations nécessaires pour se connecter à Cloud Storage, et créer et gérer des buckets Cloud Storage. Pour afficher les autorisations exactes requises, développez la section Autorisations requises :

    Autorisations requises

    • bigquery.connections.create
    • bigquery.connections.get
    • bigquery.connections.list
    • bigquery.connections.update
    • bigquery.connections.use
    • bigquery.connections.delete
    • storage.bucket.*
    • storage.object.*

  • Administrateur d'entrepôt de données

    Pour obtenir les autorisations nécessaires pour créer des tables d'objets, demandez à votre administrateur de vous accorder les rôles suivants sur le projet :

    • Rôle Éditeur de données BigQuery (roles/bigquery.dataEditor).
    • Rôle Administrateur de connexion BigQuery (roles/bigquery.connectionAdmin).

    Ce rôle prédéfini contient les autorisations requises pour créer des tables d'objets. Pour afficher les autorisations exactes requises, développez la section Autorisations requises :

    Autorisations requises

    • bigquery.tables.create
    • bigquery.tables.update
    • bigquery.connections.delegate

  • Analyste de données

    Pour obtenir les autorisations nécessaires pour interroger des tables d'objets, demandez à votre administrateur de vous accorder les rôles suivants sur le projet :

    • Rôle Lecteur de données BigQuery (roles/bigquery.dataViewer).
    • Rôle Utilisateur de connexion BigQuery (roles/bigquery.connectionUser).

    Ce rôle prédéfini contient les autorisations requises pour interroger des tables d'objets. Pour afficher les autorisations exactes requises, développez la section Autorisations requises :

    Autorisations requises

    • bigquery.jobs.create
    • bigquery.tables.get
    • bigquery.tables.getData
    • bigquery.readsessions.create

    Vous pouvez également obtenir ces autorisations avec des rôles personnalisés ou d'autres rôles prédéfinis.

Créer des tables d'objets

Avant de créer une table d'objets, vous devez disposer d'un ensemble de données existant pour la contenir. Pour en savoir plus, consultez la page Créer des ensembles de données.

Pour créer une table d'objets, procédez comme suit :

SQL

Utilisez l'instruction CREATE EXTERNAL TABLE.

  1. Dans la console Google Cloud, accédez à la page BigQuery.

    Accéder à BigQuery

  2. Dans l'éditeur de requête, saisissez l'instruction suivante :

    CREATE EXTERNAL TABLE `PROJECT_ID.DATASET_ID.TABLE_NAME`
WITH CONNECTION `PROJECT_ID.REGION.CONNECTION_ID`
OPTIONS(
  object_metadata = 'SIMPLE',
  uris = ['BUCKET_PATH'[,...]],
  max_staleness = STALENESS_INTERVAL,
  metadata_cache_mode = 'CACHE_MODE');

    Remplacez les éléments suivants :

    • PROJECT_ID : ID de votre projet.
    • DATASET_ID : ID de l'ensemble de données devant contenir la table d'objets.
    • TABLE_NAME : nom de la table d'objets.
    • REGION : région ou emplacement multirégional contenant la connexion.
    • CONNECTION_ID : ID de la connexion de ressource cloud à utiliser avec cette table d'objets. La connexion détermine le compte de service utilisé pour lire les données depuis Cloud Storage.

      Lorsque vous affichez les détails de la connexion dans la console Google Cloud, il s'agit de la valeur de la dernière section de l'ID de connexion complet affiché dans ID de connexion (par exemple, projects/myproject/locations/connection_location/connections/myconnection).

    • BUCKET_PATH : chemin d'accès au bucket Cloud Storage contenant les objets représentés par la table d'objets, au format ['gs://bucket_name/[folder_name/]*'].

      Vous pouvez utiliser un astérisque (*) en caractère générique dans chaque chemin pour limiter les objets inclus dans la table d'objets. Par exemple, si le bucket contient plusieurs types de données non structurées, vous pouvez créer la table d'objets seulement pour des objets PDF en spécifiant ['gs://bucket_name/*.pdf']. Pour en savoir plus, consultez la section Gestion des caractères génériques dans les URI Cloud Storage.

      Vous pouvez spécifier plusieurs buckets pour l'option uris en fournissant plusieurs chemins d'accès, par exemple ['gs://mybucket1/*', 'gs://mybucket2/folder5/*'].

      Pour en savoir plus sur l'utilisation des URI Cloud Storage dans BigQuery, consultez la page Chemin d'accès aux ressources Cloud Storage.

    • STALENESS_INTERVAL : indique si les métadonnées mises en cache sont utilisées par les opérations sur la table d'objets et indique le niveau nécessaire de fraîcheur des métadonnées mises en cache pour que l'opération puisse les utiliser. Pour en savoir plus sur les considérations liées à la mise en cache des métadonnées, consultez la section Mise en cache des métadonnées pour améliorer les performances.

      Pour désactiver la mise en cache des métadonnées, spécifiez 0. Il s'agit de la valeur par défaut.

      Pour activer la mise en cache des métadonnées, spécifiez une valeur de littéral d'intervalle comprise entre 30 minutes et 7 jours. Par exemple, spécifiez INTERVAL 4 HOUR pour un intervalle d'obsolescence de quatre heures. Avec cette valeur, les opérations sur la table utilisent les métadonnées mises en cache si elles ont été actualisées au cours des quatre dernières heures. Si les métadonnées mises en cache sont plus anciennes, l'opération extrait les métadonnées de Cloud Storage.

    • CACHE_MODE : indique si le cache de métadonnées est actualisé automatiquement ou manuellement. Pour en savoir plus sur les considérations liées à la mise en cache des métadonnées, consultez la section Mise en cache des métadonnées pour améliorer les performances.

      Définissez cet élément sur AUTOMATIC pour que le cache de métadonnées soit actualisé à un intervalle défini par le système, généralement entre 30 et 60 minutes.

      Définissez la valeur sur MANUAL si vous souhaitez actualiser le cache de métadonnées selon une programmation que vous déterminez. Dans ce cas, vous pouvez appeler la procédure système BQ.REFRESH_EXTERNAL_METADATA_CACHE pour actualiser le cache.

      Vous devez définir CACHE_MODE si STALENESS_INTERVAL est défini sur une valeur supérieure à 0.

  3. Cliquez sur Exécuter.

Pour en savoir plus sur l'exécution des requêtes, consultez Exécuter une requête interactive.

Exemples

L'exemple suivant crée une table d'objets avec un intervalle d'obsolescence du cache de métadonnées d'un jour :

CREATE EXTERNAL TABLE `my_dataset.object_table`
WITH CONNECTION `us.my-connection`
OPTIONS(
  object_metadata = 'SIMPLE',
  uris = ['gs://mybucket/*'],
  max_staleness = INTERVAL 1 DAY,
  metadata_cache_mode = 'AUTOMATIC'
);

L'exemple suivant crée une table d'objets pour les objets de trois buckets Cloud Storage :

CREATE EXTERNAL TABLE `my_dataset.object_table`
WITH CONNECTION `us.my-connection`
OPTIONS(
  object_metadata = 'SIMPLE',
  uris = ['gs://bucket1/*','gs://bucket2/folder1/*','gs://bucket3/*']
);

L'exemple suivant crée une table d'objets seulement pour les objets PDF d'un bucket Cloud Storage :

CREATE EXTERNAL TABLE `my_dataset.object_table`
WITH CONNECTION `us.my-connection`
OPTIONS(
  object_metadata = 'SIMPLE',
  uris = ['gs://bucket1/*.pdf']
);

bq

Utilisez la commande bq mk.

bq mk --table \
--external_table_definition=BUCKET_PATH@REGION.CONNECTION_ID \
--object_metadata=SIMPLE \
--max_staleness=STALENESS_INTERVAL \
--metadata_cache_mode=CACHE_MODE \
PROJECT_ID:DATASET_ID.TABLE_NAME

Remplacez les éléments suivants :

  • PROJECT_ID : ID de votre projet.
  • DATASET_ID : ID de l'ensemble de données devant contenir la table d'objets.
  • TABLE_NAME : nom de la table d'objets.
  • REGION : région ou emplacement multirégional contenant la connexion.
  • CONNECTION_ID : ID de la connexion de ressource cloud à utiliser avec cette table externe. La connexion détermine le compte de service utilisé pour lire les données depuis Cloud Storage.

    Lorsque vous affichez les détails de la connexion dans la console Google Cloud, il s'agit de la valeur de la dernière section de l'ID de connexion complet affiché dans ID de connexion (par exemple, projects/myproject/locations/connection_location/connections/myconnection).

  • BUCKET_PATH : chemin d'accès au bucket Cloud Storage contenant les objets représentés par la table d'objets, au format gs://bucket_name/[folder_name/]*.

    Vous pouvez utiliser un astérisque (*) en caractère générique dans chaque chemin pour limiter les objets inclus dans la table d'objets. Par exemple, si le bucket contient plusieurs types de données non structurées, vous pouvez créer la table d'objets seulement pour des objets PDF en spécifiant gs://bucket_name/*.pdf. Pour en savoir plus, consultez la section Gestion des caractères génériques dans les URI Cloud Storage.

    Vous pouvez spécifier plusieurs buckets pour l'option uris en fournissant plusieurs chemins d'accès, par exemple gs://mybucket1/*,gs://mybucket2/folder5/*.

    Pour en savoir plus sur l'utilisation des URI Cloud Storage dans BigQuery, consultez la page Chemin d'accès aux ressources Cloud Storage.

  • STALENESS_INTERVAL : indique si les métadonnées mises en cache sont utilisées par les opérations sur la table d'objets et indique le niveau nécessaire de fraîcheur des métadonnées mises en cache pour que l'opération puisse les utiliser. Pour en savoir plus sur les considérations liées à la mise en cache des métadonnées, consultez la section Mise en cache des métadonnées pour améliorer les performances.

    Pour désactiver la mise en cache des métadonnées, spécifiez 0. Il s'agit de la valeur par défaut.

    Pour activer la mise en cache des métadonnées, spécifiez une valeur d'intervalle comprise entre 30 minutes et 7 jours, à l'aide du format Y-M D H:M:S décrit dans la documentation sur les type de données INTERVAL. Par exemple, spécifiez 0-0 0 4:0:0 pour un intervalle d'obsolescence de quatre heures. Avec cette valeur, les opérations sur la table utilisent les métadonnées mises en cache si elles ont été actualisées au cours des quatre dernières heures. Si les métadonnées mises en cache sont plus anciennes, l'opération extrait les métadonnées de Cloud Storage.

  • CACHE_MODE : indique si le cache de métadonnées est actualisé automatiquement ou manuellement. Pour en savoir plus sur les considérations liées à la mise en cache des métadonnées, consultez la section Mise en cache des métadonnées pour améliorer les performances.

    Définissez cet élément sur AUTOMATIC pour que le cache de métadonnées soit actualisé à un intervalle défini par le système, généralement entre 30 et 60 minutes.

    Définissez la valeur sur MANUAL si vous souhaitez actualiser le cache de métadonnées selon une programmation que vous déterminez. Dans ce cas, vous pouvez appeler la procédure système BQ.REFRESH_EXTERNAL_METADATA_CACHE pour actualiser le cache.

    Vous devez définir CACHE_MODE si STALENESS_INTERVAL est défini sur une valeur supérieure à 0.

Exemples

L'exemple suivant crée une table d'objets avec un intervalle d'obsolescence du cache de métadonnées d'un jour :

bq mk --table \
--external_table_definition=gs://mybucket/*@us.my-connection \
--object_metadata=SIMPLE \
--max_staleness=0-0 1 0:0:0 \
--metadata_cache_mode=AUTOMATIC \
my_dataset.object_table

L'exemple suivant crée une table d'objets pour les objets de trois buckets Cloud Storage :

bq mk --table \
--external_table_definition=gs://bucket1/*,gs://bucket2/folder1/*,gs://bucket3/*@us.my-connection \
--object_metadata=SIMPLE \
my_dataset.object_table

L'exemple suivant crée une table d'objets seulement pour les objets PDF d'un bucket Cloud Storage :

bq mk --table \
--external_table_definition=gs://bucket1/*.pdf@us.my-connection \
--object_metadata=SIMPLE \
my_dataset.object_table

API

Appelez la méthode tables.insert. Incluez un objet ExternalDataConfiguration avec le champ objectMetadata défini sur SIMPLE dans la ressource Table que vous transmettez.

L'exemple suivant montre comment appeler cette méthode à l'aide de curl :

ACCESS_TOKEN=$(gcloud auth print-access-token) curl \
-H "Authorization: Bearer ${ACCESS_TOKEN}" \
-H "Content-Type: application/json" \
-X POST \
-d '{"tableReference": {"projectId": "my_project", "datasetId": "my_dataset", "tableId": "object_table_name"}, "externalDataConfiguration": {"objectMetadata": "SIMPLE", "sourceUris": ["gs://mybucket/*"]}}' \
https://www.googleapis.com/bigquery/v2/projects/my_project/datasets/my_dataset/tables

Terraform

Cet exemple crée une table d'objets avec la mise en cache des métadonnées activée avec l'actualisation manuelle.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.

Les champs de clés à spécifier pour une table d'objets sont google_bigquery_table.external_data_configuration.object_metadata, google_bigquery_table.external_data_configuration.metadata_cache_mode et google_bigquery_table.max_staleness. Pour en savoir plus sur chaque ressource, consultez la documentation Terraform de BigQuery. 


# This queries the provider for project information.
data "google_project" "default" {}

# This creates a connection in the US region named "my-connection-id".
# This connection is used to access the bucket.
resource "google_bigquery_connection" "default" {
  connection_id = "my-connection-id"
  location      = "US"
  cloud_resource {}
}

# This grants the previous connection IAM role access to the bucket.
resource "google_project_iam_member" "default" {
  role    = "roles/storage.objectViewer"
  project = data.google_project.default.project_id
  member  = "serviceAccount:${google_bigquery_connection.default.cloud_resource[0].service_account_id}"
}

# This defines a Google BigQuery dataset.
resource "google_bigquery_dataset" "default" {
  dataset_id = "my_dataset_id"
}

# This creates a bucket in the US region named "my-bucket" with a pseudorandom suffix.
resource "random_id" "bucket_name_suffix" {
  byte_length = 8
}
resource "google_storage_bucket" "default" {
  name                        = "my-bucket-${random_id.bucket_name_suffix.hex}"
  location                    = "US"
  force_destroy               = true
  uniform_bucket_level_access = true
}

# This defines a BigQuery object table with manual metadata caching.
resource "google_bigquery_table" "default" {
  deletion_protection = false
  table_id            = "my-table-id"
  dataset_id          = google_bigquery_dataset.default.dataset_id
  external_data_configuration {
    connection_id = google_bigquery_connection.default.name
    autodetect    = false
    # `object_metadata is` required for object tables. For more information, see
    # https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/bigquery_table#object_metadata
    object_metadata = "SIMPLE"
    # This defines the source for the prior object table.
    source_uris = [
      "gs://${google_storage_bucket.default.name}/*",
    ]

    metadata_cache_mode = "MANUAL"
  }

  # This ensures that the connection can access the bucket
  # before Terraform creates a table.
  depends_on = [
    google_project_iam_member.default
  ]
}

Pour appliquer votre configuration Terraform dans un projet Google Cloud, suivez les procédures des sections suivantes.

Préparer Cloud Shell

  1. Lancez Cloud Shell.

  2. Définissez le projet Google Cloud par défaut dans lequel vous souhaitez appliquer vos configurations Terraform.

    Vous n'avez besoin d'exécuter cette commande qu'une seule fois par projet et vous pouvez l'exécuter dans n'importe quel répertoire.

    export GOOGLE_CLOUD_PROJECT=PROJECT_ID

    Les variables d'environnement sont remplacées si vous définissez des valeurs explicites dans le fichier de configuration Terraform.

Préparer le répertoire

Chaque fichier de configuration Terraform doit avoir son propre répertoire (également appelé module racine).

  1. Dans Cloud Shell, créez un répertoire et un nouveau fichier dans ce répertoire. Le nom du fichier doit comporter l'extension .tf, par exemple main.tf. Dans ce tutoriel, le fichier est appelé main.tf. 
    mkdir DIRECTORY && cd DIRECTORY && touch main.tf

  2. Si vous suivez un tutoriel, vous pouvez copier l'exemple de code dans chaque section ou étape.

    Copiez l'exemple de code dans le fichier main.tf que vous venez de créer.

    Vous pouvez également copier le code depuis GitHub. Cela est recommandé lorsque l'extrait Terraform fait partie d'une solution de bout en bout.

  3. Examinez et modifiez les exemples de paramètres à appliquer à votre environnement.
  4. Enregistrez les modifications.
  5. Initialisez Terraform. Cette opération n'est à effectuer qu'une seule fois par répertoire. 
    terraform init

    Vous pouvez également utiliser la dernière version du fournisseur Google en incluant l'option -upgrade : 

    terraform init -upgrade

Appliquer les modifications

  1. Examinez la configuration et vérifiez que les ressources que Terraform va créer ou mettre à jour correspondent à vos attentes : 
    terraform plan

    Corrigez les modifications de la configuration si nécessaire.

  2. Appliquez la configuration Terraform en exécutant la commande suivante et en saisissant yes lorsque vous y êtes invité : 
    terraform apply

    Attendez que Terraform affiche le message "Apply completed!" (Application terminée).

  3. Ouvrez votre projet Google Cloud pour afficher les résultats. Dans la console Google Cloud, accédez à vos ressources dans l'interface utilisateur pour vous assurer que Terraform les a créées ou mises à jour.

Interroger des tables d'objets

Vous pouvez interroger une table d'objets comme n'importe quel autre BigQuery, par exemple :

SELECT *
FROM mydataset.myobjecttable;

L'interrogation d'une table d'objets renvoie des métadonnées pour les objets sous-jacents. Pour en savoir plus, consultez la section Schéma d'une table d'objets.

Étapes suivantes