Requêtes fédérées Spanner

Les requêtes fédérées dans BigQuery vous permettent d'interroger les données résidant dans Spanner en temps réel, sans avoir à les copier ni à les déplacer. Vous pouvez interroger des données Spanner en créant un ensemble de données externe Spanner ou en utilisant la fonction EXTERNAL_QUERY.

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 ressourcesGoogle Cloud , 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, comme se connecter, lire des données ou administrer. 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 Accorder des 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 ultrapré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 un contrôle ultraprécis des accès.

Vous êtes probablement un utilisateur du contrôle précis des accès 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 du contrôle des accès ultraprécis 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 devez généralement disposer des autorisations suivantes :

  • L'autorisation IAM spanner.databases.useRoleBasedAccess, qui est souvent accordée via le rôle roles/spanner.fineGrainedAccessUser.
  • L'autorisation d'utiliser le rôle de base de données spécifique, qui est 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 les données Spanner à l'aide d'une fonction EXTERNAL_QUERY. 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 devez également disposer des autorisations appropriées sur la base de données Spanner, qui dépendent de votre statut d'utilisateur FGAC.
    • Si vous utilisez le contrôle des accès ultraprécis :
      • Vous devez disposer des rôles IAM requis pour utiliser le 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 référencés par vos requêtes. Votre administrateur de base de données accorde des droits à l'aide de l'instruction LDD GRANT (ou de l'équivalent PostgreSQL).
    • Si vous n'utilisez pas le contrôle ultrapré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 des accès ultraprécis de Spanner, lorsque vous exécutez une requête fédérée avec une fonction EXTERNAL_QUERY, 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 du contrôle précis des accès dans la base de données Spanner, indépendamment 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 la section 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 fonction EXTERNAL_QUERY.

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 utile 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 Data Boost (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 les données, telles que roles/spanner.databaseReader pour les utilisateurs sans contrôle ultraprécis des accès ou les autorisations de contrôle ultrapré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 lorsque vous créez 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 sur les 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 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 les données en parallèle" est toujours utilisée.

Pour activer les lectures parallèles lorsque vous utilisez 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 voir 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 les 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 accepte 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 la page Tarifs de Spanner.

Le transfert de données vous est facturé en fonction des SKU suivants :

  • Transfert de données réseau sortant interzone intrarégional
  • Transfert de données réseau interrégional sortant vers le même continent
  • Transfert de données réseau interrégional sortant 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 qui dispose d'instances répliquées 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 :

  • Emplacement multirégional BigQuery US : région Spanner us-central1
  • Emplacement multirégional BigQuery EU : 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 d'un 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.

Étapes suivantes