Requêtes fédérées Spanner

En tant qu'analyste de données, vous pouvez interroger des données dans Spanner à partir de BigQuery en utilisant les requêtes fédérées.

La fédération BigQuery Spanner permet à BigQuery d'interroger des données résidant dans Spanner en temps réel, sans les copier ni les déplacer.

Vous pouvez interroger des données Spanner de deux manières :

  • Créez un ensemble de données externe Spanner.
  • Utilisez une EXTERNAL_QUERY fonction.

Comprendre les rôles et les autorisations

Lorsque vous interrogez Spanner à partir de BigQuery, vous rencontrez deux types de rôles distincts qui gèrent l'accès à différents niveaux.

  • Rôles IAM : ces rôles régissent l'accès aux Google Cloud ressources, y compris aux instances et aux bases de données Spanner. Ils déterminent quels comptes principaux peuvent accéder au service Spanner et effectuer des actions au niveau de l'instance ou de la base de données, telles que la connexion, la lecture de données ou l'administration. Vous gérez les rôles Identity and Access Management (IAM) via la console IAM ou la Google Cloud CLI. Par exemple, roles/bigquery.connectionUser et roles/spanner.databaseReader. Pour en savoir plus, consultez Rôles IAM Spanner et attribution d'autorisations.

  • Rôles de base de données Spanner : ces rôles sont définis dans une base de données Spanner à l'aide d'instructions LDD telles que CREATE ROLE et GRANT. Ils contrôlent l'accès précis à des objets de schéma spécifiques, tels que des tables, des colonnes et des vues, dans la base de données. Cela fait partie du contrôle précis des accès (FGAC). Vous utilisez un rôle de base de données si votre organisation implémente le FGAC pour gérer les autorisations dans la base de données.

Déterminer si vous êtes un utilisateur FGAC

Pour déterminer les autorisations appropriées à demander, vous devez déterminer si vous êtes un utilisateur FGAC. Pour ce faire, demandez à votre administrateur de base de données Spanner si votre accès à la base de données Spanner est géré par le contrôle précis des accès.

Vous êtes probablement un utilisateur FGAC si votre administrateur accorde des autorisations à votre compte en l'attribuant à un rôle de base de données Spanner spécifique (par exemple, en accordant à votre compte le rôle IAM roles/spanner.databaseRoleUser sur une ressource de rôle de base de données). Dans ce cas, vous devez connaître le nom des rôles de base de données que vous pouvez utiliser. Vous devez configurer la connexion BigQuery pour utiliser l'un de ces rôles de base de données.

Vous n'êtes probablement pas un utilisateur FGAC si votre administrateur accorde à votre compte des rôles IAM plus larges au niveau de la base de données, tels que roles/spanner.databaseReader. Dans ce cas, vous n'avez pas besoin d'utiliser un rôle de base de données spécifique lors de la connexion.

Comparer l'application des rôles

Bien qu'IAM contrôle l'accès à la ressource de base de données elle-même, les rôles de base de données Spanner contrôlent les autorisations pour les objets de cette base de données.

Pour utiliser un rôle de base de données FGAC, vous avez généralement besoin des autorisations suivantes :

  • L'autorisation IAM spanner.databases.useRoleBasedAccess, souvent accordée via le rôle roles/spanner.fineGrainedAccessUser.
  • L'autorisation d'utiliser le rôle de base de données spécifique, accordée via le rôle roles/spanner.databaseRoleUser avec une condition IAM.

Pour en savoir plus sur la configuration de ces autorisations, consultez Configurer le FGAC.

Utiliser des ensembles de données externes

Le moyen le plus simple d'interroger des tables Spanner consiste à créer un ensemble de données externe. Une fois l'ensemble de données externe créé, vos tables de la base de données Spanner correspondante sont visibles dans BigQuery et vous pouvez les utiliser dans vos requêtes (par exemple, dans des jointures, des unions ou des sous-requêtes). Toutefois, aucune donnée n'est déplacée de Spanner vers l'espace de stockage BigQuery.

Vous n'avez pas besoin de créer de connexion pour interroger des données Spanner si vous créez un ensemble de données externe.

Utiliser la fonction EXTERNAL_QUERY

Comme pour les autres bases de données fédérées, vous pouvez également interroger des données Spanner avec une EXTERNAL_QUERY fonction. Cela peut être utile si vous souhaitez mieux contrôler les paramètres de connexion.

Avant de commencer

  • Assurez-vous que votre administrateur BigQuery a créé une connexion Spanner et l'a partagée avec vous. Consultez la section Choisir la connexion adaptée.
  • Pour obtenir les autorisations nécessaires pour interroger une instance Spanner, demandez à votre administrateur de vous accorder le rôle IAM "Utilisateur de connexion BigQuery" (roles/bigquery.connectionUser) sur la connexion. Vous avez également besoin des autorisations appropriées sur la base de données Spanner, selon que vous êtes un utilisateur FGAC ou non.
    • Si vous utilisez le contrôle précis des accès :
      • Vous avez besoin des rôles IAM requis pour utiliser FGAC. Ces rôles sont généralement roles/spanner.fineGrainedAccessUser et roles/spanner.databaseRoleUser. Les rôles sont utilisés avec une condition qui spécifie le rôle de base de données.
      • Le rôle de base de données Spanner que vous spécifiez dans la connexion doit disposer du droit SELECT sur tous les objets de schéma auxquels vos requêtes font référence. Votre administrateur de base de données accorde des droits à l'aide de l'instruction GRANT LDD (ou de l'équivalent PostgreSQL).
    • Si vous n'utilisez pas le contrôle précis des accès, vous avez besoin du rôle IAM "Lecteur de bases de données Spanner" (roles/spanner.databaseReader) sur la base de données.

    Pour plus d'informations sur l'attribution de rôles IAM, consultez la page Gérer l'accès aux projets, aux dossiers et aux organisations.

Choisir la connexion adaptée

Si vous utilisez le contrôle précis des accès de Spanner, lorsque vous exécutez une requête fédérée avec une EXTERNAL_QUERY fonction, vous devez utiliser une connexion Spanner spécifiant un rôle de base de données. Ce rôle de base de données fait partie de la configuration FGAC dans la base de données Spanner, distincte de vos rôles IAM. Toutes les requêtes que vous exécutez avec cette connexion utilisent ensuite les autorisations accordées à ce rôle de base de données.

Si vous utilisez une connexion qui ne spécifie pas de rôle de base de données, vous devez disposer des rôles IAM indiqués dans Avant de commencer.

Interroger les données

Pour envoyer une requête fédérée à Spanner à partir d'une requête GoogleSQL, utilisez la EXTERNAL_QUERY fonction.

Formulez votre requête Spanner en langage GoogleSQL ou PostgreSQL, selon le dialecte spécifié de la base de données.

L'exemple suivant envoie une requête fédérée à une base de données Spanner nommée orders et joint les résultats à une table BigQuery nommée mydataset.customers :

SELECT c.customer_id, c.name, rq.first_order_date
FROM mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'my-project.us.example-db',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id''') AS rq
  ON rq.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, rq.first_order_date;

Spanner Data Boost

Data Boost est une fonctionnalité sans serveur entièrement gérée qui fournit des ressources de calcul indépendantes pour les charges de travail Spanner compatibles. Data Boost vous permet d'exécuter des requêtes d'analyse et des exportations de données avec un impact minimal sur les charges de travail existantes sur l'instance Spanner provisionnée. Data Boost vous permet d'exécuter des requêtes fédérées avec une capacité de calcul indépendante distincte de vos instances provisionnées afin d'éviter toute incidence sur les charges de travail existantes sur Spanner. Data Boost est plus efficace lorsque vous exécutez des requêtes ad hoc complexes ou lorsque vous souhaitez traiter de grandes quantités de données sans affecter la charge de travail Spanner existante. L'exécution de requêtes fédérées avec Data Boost peut réduire considérablement la consommation de processeur et, dans certains cas, la latence des requêtes.

Avant de commencer

Pour obtenir l'autorisation dont vous avez besoin pour activer l'accès à Data Boost, demandez à votre administrateur de vous accorder le rôle IAM "Lecteur de bases de données Cloud Spanner avec DataBoost" (roles/spanner.databaseReaderWithDataBoost) sur la base de données Spanner. Pour en savoir plus sur l'attribution de rôles, consultez Gérer l'accès aux projets, aux dossiers et aux organisations.

Ce rôle prédéfini contient l'autorisation spanner.databases.useDataBoost, qui est requise pour permettre l'accès à Data Boost.

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

Le rôle IAM roles/spanner.databaseReaderWithDataBoost accorde l'autorisation d'utiliser Data Boost. Ce rôle est requis en plus des autorisations de base nécessaires pour lire des données, telles que roles/spanner.databaseReader pour les utilisateurs non FGAC ou les autorisations de contrôle précis des accès appropriées.

L'utilisation d'ensembles de données externes avec Spanner utilise toujours Data Boost et nécessite donc l'autorisation spanner.databases.useDataBoost.

Activer Data Boost

Lorsque vous utilisez des ensembles de données externes, Data Boost est toujours utilisé et vous n'avez pas besoin de l'activer manuellement.

Si vous souhaitez utiliser Data Boost pour vos requêtes EXTERNAL_QUERY, vous devez l'activer lors de la création d'une connexion utilisée par votre requête.

Lire des données en parallèle

Spanner peut diviser certaines requêtes en fragments plus petits, ou partitions, et extraire les partitions en parallèle. Pour en savoir plus, y compris pour obtenir la liste des limites, consultez Lire des données en parallèle dans la documentation Spanner.

Pour afficher le plan d'exécution d'une requête Spanner, consultez la section Comprendre comment Spanner exécute les requêtes.

Lorsque vous exécutez des requêtes fédérées avec des ensembles de données externes, l'option "Lire des données en parallèle" est toujours utilisée.

Pour activer les lectures parallèles lorsque vous utilisez les EXTERNAL_QUERY, activez-les lorsque vous créez la connexion.

Gérer la priorité d'exécution des requêtes

Lorsque vous exécutez des requêtes fédérées avec une fonction EXTERNAL_QUERY, vous pouvez attribuer une priorité (high, medium ou low) à des requêtes individuelles en spécifiant l'option query_execution_priority :

SELECT *
FROM EXTERNAL_QUERY(
  'my-project.us.example-db',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id''',
  '{"query_execution_priority":"high"}');

La priorité par défaut est medium.

Les requêtes de priorité high sont en concurrence avec le trafic des données transactionnelles. Les requêtes dont la priorité est low sont optimales et peuvent être préemptées par une charge en arrière-plan, telle que des sauvegardes planifiées.

Lorsque vous exécutez des requêtes fédérées avec des ensembles de données externes, toutes les requêtes ont toujours la priorité medium.

Afficher un schéma de table Spanner

Si vous utilisez des ensembles de données externes, vos tables Spanner sont visibles directement dans BigQuery Studio, et vous pouvez voir leurs schémas.

Toutefois, vous pouvez également afficher les schémas sans définir d'ensembles de données externes. Vous pouvez également utiliser la fonction EXTERNAL_QUERY pour interroger les vues information_schema afin d'accéder aux métadonnées de la base de données. L'exemple suivant renvoie des informations sur les colonnes de la table MyTable :

Base de données Google SQL 

SELECT *
FROM EXTERNAL_QUERY(
  'my-project.us.example-db',
  '''SELECT t.column_name, t.spanner_type, t.is_nullable
    FROM information_schema.columns AS t
    WHERE
      t.table_catalog = ''
      AND t.table_schema = ''
     AND t.table_name = 'MyTable'
    ORDER BY t.ordinal_position
  ''');

Base de données PostgreSQL 

SELECT * from EXTERNAL_QUERY(
'my-project.us.postgresql-example-db',
'''SELECT t.column_name, t.data_type, t.is_nullable
   FROM information_schema.columns AS t
   WHERE t.table_schema = 'public' AND t.table_name = 'MyTable'
   ORDER BY t.ordinal_position''');

Pour plus d'informations, consultez les références de schéma d'informations suivantes dans la documentation Spanner :

Tarifs

Requêtes interrégionales

BigQuery est compatible avec les requêtes fédérées dans lesquelles les instances Spanner et les ensembles de données BigQuery se trouvent dans des régions différentes. Ces requêtes entraînent des frais de transfert de données Spanner supplémentaires. Pour en savoir plus, consultez Tarifs de Spanner.

Les frais de transfert de données sont facturés en fonction des références suivantes :

  • Transfert de données sortantes interzones intrarégionales du réseau
  • Transfert de données sortantes interrégionales du réseau vers le même continent
  • Transfert de données sortantes interrégionales du réseau vers un autre continent

Le transfert de données est facturé en fonction de la région BigQuery dans laquelle vous exécutez la requête et de la région Spanner la plus proche disposant de répliques en lecture/écriture ou en lecture seule.

Pour les configurations multirégionales BigQuery (US ou EU), les coûts de transfert de données depuis Spanner sont déterminés comme suit :

  • Multirégion US BigQuery : région Spanner us-central1
  • Multirégion EU BigQuery : région Spanner europe-west1

Exemple :

  • BigQuery (multirégion US) et Spanner (us-central1) : des frais s'appliquent pour le transfert de données au sein de la même région.
  • BigQuery (multirégion US) et Spanner (us-west4) : des frais s'appliquent pour le transfert de données entre régions au sein du même continent.

Dépannage

Cette section vous aide à résoudre les problèmes que vous pouvez rencontrer lors de l'envoi d'une requête fédérée à Spanner.

Problème : la requête n'est pas partitionnable à la racine.
Résolution : Si vous configurez la connexion pour lire des données en parallèle, le premier opérateur du plan d'exécution de la requête doit être "Distributed Union" ou votre plan d'exécution ne doit comporter aucun opérateur de ce type. Pour résoudre cette erreur, affichez le plan d'exécution de la requête et réécrivez la requête. Pour en savoir plus, consultez la page Comprendre comment Spanner exécute les requêtes.
Problème : échéance dépassée.
Résolution : Sélectionnez l'option permettant de lire les données en parallèle et de réécrire la requête pour qu'elle puisse être partitionnée à la racine. Pour en savoir plus, consultez la page Comprendre comment Spanner exécute les requêtes.

Étape suivante