Accéder à BigQuery depuis AlloyDB

Cette page explique comment utiliser Lakehouse Federation pour accéder aux données stockées ou accessibles à l'aide de BigQuery à partir d'une interface AlloyDB pour PostgreSQL.

Le wrapper de données externes est compatible avec un large éventail de ressources BigQuery, ce qui vous permet d'interroger les éléments suivants :

Grâce à cette intégration, vous pouvez traiter les ensembles de données BigQuery comme des tables locales dans votre environnement PostgreSQL pour effectuer des analyses inter-moteurs. Pour en savoir plus, consultez la présentation de Lakehouse Federation dans AlloyDB.

Cette page suppose que vous disposez d'un cluster et d'une instance principale AlloyDB, ainsi que d'un ensemble de données et de tables BigQuery. Pour en savoir plus, consultez Créer des ensembles de données et Créer et utiliser des tables.

Avant de commencer

  1. Assurez-vous que l' bigquery_fdw.enabled indicateur est configuré sur l' instance AlloyDB pour PostgreSQL.
  2. Familiarisez-vous avec les types de données BigQuery compatibles et les mappages de colonnes.
  3. Connectez-vous à votre Google Cloud compte. Si vous débutez sur Google Cloud, créez un compte pour évaluer les performances de nos produits en conditions réelles. Les nouveaux clients bénéficient également de 300 $ de crédits sans frais pour exécuter, tester et déployer des charges de travail.

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

    Roles required to select or create a project

    • Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
    • Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

    Go to project selector

  5. Verify that billing is enabled for your Google Cloud project.

  6. Enable the AlloyDB, Compute Engine, Resource Manager, and BigQuery APIs.

    Roles required to enable APIs

    To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains the serviceusage.services.enable permission. Learn how to grant roles.

    Enable the APIs

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

    Roles required to select or create a project

    • Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
    • Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

    Go to project selector

  8. Verify that billing is enabled for your Google Cloud project.

  9. Enable the AlloyDB, Compute Engine, Resource Manager, and BigQuery APIs.

    Roles required to enable APIs

    To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains the serviceusage.services.enable permission. Learn how to grant roles.

    Enable the APIs

    10.

  10. Activez les APIs Cloud nécessaires pour créer une instance AlloyDB pour PostgreSQL et vous y connecter.

    Activer les API

  11. À l'étape Confirmer le projet, cliquez sur Suivant pour confirmer le nom du projet que vous allez modifier.

  12. À l'étape Activer les API, cliquez sur Activer pour activer les éléments suivants :

    • API AlloyDB
    • API Compute Engine
    • API Cloud Resource Manager
    • API Service Networking
    • API BigQuery Storage
    • API BigQuery

    L'API Service Networking est requise si vous prévoyez de configurer la connectivité réseau à AlloyDB à l'aide d'un réseau VPC qui réside dans le même Google Cloud projet qu'AlloyDB.

    Les API Compute Engine et Cloud Resource Manager sont requises si vous prévoyez de configurer la connectivité réseau à AlloyDB à l'aide d'un réseau VPC réseau qui réside dans un autre Google Cloud projet.

Rôles requis

Pour accorder un accès en lecture à l'ensemble de données BigQuery au compte de service du cluster AlloyDB, vous avez besoin des autorisations suivantes. Pour en savoir plus, consultez Accorder à AlloyDB l'accès à l'ensemble de données BigQuery.

  • Lecteur de données BigQuery (roles/bigquery.dataViewer) ou tout rôle personnalisé disposant des autorisations bigquery.tables.get et bigquery.tables.getData. Lorsqu'il est accordé sur une table ou une vue, ce rôle fournit des autorisations pour lire les données et les métadonnées de la table ou de la vue.
  • Utilisateur de sessions de lecture BigQuery (roles/bigquery.readSessionUser) ou tout rôle personnalisé disposant des autorisations bigquery.readsessions.create et bigquery.readsessions.getData. Permet de créer et d'utiliser des sessions de lecture.
  • Utilisateur de job BigQuery (roles/bigquery.jobUser) ou tout rôle personnalisé disposant des autorisations bigquery.jobs.create. Fournit des autorisations permettant d'exécuter des tâches, y compris des requêtes, dans le projet à l'aide de l'API BigQuery. Ce rôle ne peut être accordé que sur les ressources Resource Manager (projets, dossiers et organisations).
  • Lecteur des objets de l'espace de stockage (roles/storage.objectViewer) ou tout rôle personnalisé disposant des autorisations storage.objects.get. Fournit des autorisations pour accéder aux tables externes BigQuery. Doit être accordé au niveau du projet ou du bucket.

Accorder à AlloyDB l'accès à l'ensemble de données BigQuery

Une fois la fonctionnalité Lakehouse Federation activée sur votre cluster AlloyDB, vous devez accorder au compte de service du cluster AlloyDB l'accès à l'ensemble de données BigQuery.

La Google Cloud console accorde automatiquement les autorisations requises au compte de service du cluster lorsque vous connectez une table BigQuery à l'aide d'AlloyDB Studio.

Pour accorder l'accès à l'aide de la gcloud CLI, procédez comme suit :

gcloud

Pour utiliser la gcloud CLI, vous pouvez installer et initialiser la Google Cloud CLI, ou utiliser Cloud Shell.

  1. Ouvrez la gcloud CLI. Si la gcloud CLI n'est pas installée, installez-la et initialisez-la, ou utilisez Cloud Shell.

  2. Exécutez la gcloud beta alloydb clusters describe commande :

    gcloud beta alloydb clusters describe CLUSTER --region=REGION

    Remplacez les éléments suivants :

    • CLUSTER : ID du cluster AlloyDB.
    • REGION: emplacement du cluster AlloyDB, par exemple asia-east1 ou us-east1. Consultez la liste complète des régions dans Emplacements AlloyDB.

    Le résultat contient un champ serviceAccountEmail, qui correspond au compte de service de ce cluster. Vous pouvez également trouver le compte de service sur la page Présentation du cluster.

  3. Accordez les autorisations requises. Pour en savoir plus, consultez Contrôler l'accès aux ressources avec IAM.

    Si le compte de service du cluster ne dispose pas des autorisations requises, les erreurs suivantes s'affichent lorsqu'une requête est exécutée sur la table BigQuery :

    • The user does not have bigquery.readsessions.create permissions
    • Permission bigquery.tables.get denied on table
    • Permission bigquery.tables.getData denied on table

Configurer l'extension

Pour configurer l'extension, procédez comme suit :

Console

  1. Accédez à la page Clusters.

    Accéder aux clusters

  2. Cliquez sur l'ID du cluster que vous souhaitez utiliser.

  3. Dans le menu de navigation, cliquez sur AlloyDB Studio.

  4. Connectez-vous à votre base de données.

  5. Dans le volet Explorateur, développez le schéma approprié.

  6. Cliquez sur le menu Actions à côté de Tables BigQuery , puis sur Connecter une table BigQuery.

  7. Dans le volet Connecter une table BigQuery , choisissez un projet source, un ensemble de données source et une table.

  8. La table Vérifier et sélectionner des colonnes affiche les colonnes de la table sélectionnée. Sélectionnez les colonnes que vous souhaitez mapper.

  9. Dans le champ Nom de la table, saisissez un nom pour la table étrangère.

  10. Facultatif : Cliquez sur Afficher la commande SQL pour afficher la commande générée.

  11. Cliquez sur Connecter la table. Une boîte de dialogue affiche la progression. Une fois le processus terminé, vous pouvez interroger la table comme n'importe quelle table dans AlloyDB.

psql

  1. Créez l'extension.

    1. Connectez-vous à l'instance AlloyDB à l'aide du client psql en suivant les instructions de la section Connecter un client psql à une instance. Vous pouvez également utiliser AlloyDB Studio. Pour en savoir plus, consultez Gérer vos données à l'aide de la Google Cloud console.

    2. Exécutez la commande suivante :

      CREATE EXTENSION bigquery_fdw;

  2. Créez un serveur externe pour définir les paramètres de connexion de l'ensemble de données BigQuery distant.

    CREATE SERVER BIGQUERY_SERVER_NAME FOREIGN DATA WRAPPER bigquery_fdw;

    Remplacez les éléments suivants :

    • BIGQUERY_SERVER_NAME: identifiant unique du serveur externe. Définissez-le une seule fois dans une base de données donnée. Vous pouvez remplacer BIGQUERY_SERVER_NAME par le nom de votre serveur.

  3. Créez le mappage utilisateur en exécutant la commande CREATE USER MAPPING, qui mappe un utilisateur PostgreSQL local que vous souhaitez connecter au serveur externe.

    CREATE USER MAPPING FOR USERNAME SERVER BIGQUERY_SERVER_NAME ;

    Remplacez les éléments suivants :

    • USERNAME: nom d'utilisateur de base de données ou utilisateur IAM qui accède à la table étrangère.
    • BIGQUERY_SERVER_NAME: identifiant unique du serveur externe que vous avez créé.

  4. Définissez les tables étrangères qui correspondent aux tables auxquelles vous souhaitez accéder dans BigQuery à l'aide de la commande CREATE FOREIGN TABLE. Cette commande vous permet de définir la structure d'une table distante. La table étrangère peut contenir toutes les colonnes de la table source dans BigQuery ou un sous-ensemble de celles-ci.

    CREATE FOREIGN TABLE TABLENAME (
COLUMN1_NAME DATA_TYPE,
COLUMN2_NAME DATA_TYPE,
... ) SERVER BIGQUERY_SERVER_NAME OPTIONS (project BIGQUERY_PROJECT_ID,
dataset BIGQUERY_DATASET_NAME,
table BIGQUERY_TABLE_NAME
[, mode EXECUTION_MODE]);

    Remplacez les éléments suivants :

    • TABLENAME: nom de la table étrangère dans la base de données AlloyDB locale.
    • COLUMNX_NAME: nom de la colonne AlloyDB. Le nom de la colonne doit correspondre exactement au nom de la colonne correspondante dans la table source BigQuery. X indique que la table peut être créée avec plusieurs colonnes. Le nom doit également correspondre à la casse exacte de la colonne BigQuery. Si le nom de la colonne BigQuery contient des majuscules (par exemple, employeeID), l'identifiant AlloyDB doit être placé entre guillemets doubles (par exemple, "employeeID") pour conserver les lettres mixtes ou majuscules.
    • DATA_TYPE : type de données de la colonne.
    • BIGQUERY_SERVER_NAME: identifiant unique du serveur externe que vous avez créé.
    • BIGQUERY_PROJECT_ID: ID du projet dans lequel réside l'ensemble de données BigQuery.
    • BIGQUERY_DATASET_NAME: nom de l'ensemble de données BigQuery pour la table.
    • BIGQUERY_TABLE_NAME: nom de la table BigQuery.
    • EXECUTION_MODE : facultatif. L'option mode peut être query pour utiliser l'API BigQuery pour les requêtes complexes, storage pour utiliser l'API BigQuery Storage pour des lectures groupées plus rapides ou auto pour choisir automatiquement entre les modes. auto est la valeur par défaut. Pour en savoir plus, consultez Modes d'exécution du wrapper de données externes BigQuery.

    Une fois la table étrangère créée, vous pouvez l'interroger de la même manière que n'importe quelle table dans AlloyDB.

Modes d'exécution du wrapper de données externes BigQuery

Le mode d'exécution détermine comment AlloyDB pour PostgreSQL interagit avec BigQuery pour récupérer des données. Le wrapper de données externes BigQuery est compatible avec deux modes d'exécution : query et storage. Il est essentiel de choisir le bon mode, car chacun présente des caractéristiques de performances et des tarifs différents. Pour plus d'informations, consultez la page Tarifs de BigQuery.

Mode Requête

Ce mode utilise l'API BigQuery pour récupérer des données à partir de BigQuery. Il utilise le moteur de calcul de BigQuery pour exécuter des requêtes complexes en envoyant des filtres et des agrégations. Autrement dit, les clauses WHERE, les clauses GROUP BY et les agrégations sont exécutées sur BigQuery avant de renvoyer les données à PostgreSQL. Ce mode permet également d'interroger les vues et les tables externes BigQuery.

Étant donné que cette API fournit des réponses structurées en lignes paginées adaptées aux petits ensembles de résultats, la lecture de grands ensembles de données présente des limites de débit et des latences plus élevées comparé à l'alternative en streaming de l'API BigQuery Storage.

Mode Stockage

Ce mode utilise l'API BigQuery Storage pour récupérer des données à partir de BigQuery. Il permet des lectures à haut débit en envoyant des données structurées via le réseau dans un format de sérialisation binaire. Il s'agit du mode préféré pour analyser de grandes tables dans BigQuery.

Toutefois, ce mode présente certaines limites. Toutes les opérations SQL complexes ne peuvent pas être envoyées à l'API BigQuery Storage. Par exemple, les agrégations ne peuvent pas être envoyées à BigQuery et doivent être exécutées dans AlloyDB. Ce mode n'est pas non plus compatible avec l'interrogation des vues et des tables externes BigQuery.

Mode Auto

Le mode par défaut est défini sur auto si vous ne le définissez pas dans la commande CREATE FOREIGN TABLE. Lorsque vous utilisez le mode auto, AlloyDB sélectionne l'API sous-jacente pour équilibrer les performances et maximiser les opérations SQL envoyées à BigQuery.

Étape suivante