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 de BigQuery et Spanner permet à BigQuery d'interroger les données résidant dans Spanner en temps réel, sans avoir à les copier ni à les déplacer.

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

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

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éé, les 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.

Si vous créez un ensemble de données externe, vous n'avez pas besoin de créer de connexion pour interroger les données Spanner.

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 interroger la base de données Spanner qui utilise le dialecte PostgreSQL ou 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 Identity and Access Management (IAM) "Utilisateur de connexion BigQuery" (roles/bigquery.connectionUser). Vous devez également demander à votre administrateur de vous attribuer l'un des rôles suivants :
    • Si vous utilisez le contrôle des accès ultraprécis, vous devez avoir accès à un rôle de base de données disposant du droit SELECT sur tous les objets de schéma Spanner dans vos requêtes.
    • Si vous n'utilisez pas le contrôle des accès ultraprécis, vous avez besoin du rôle IAM "Lecteur de bases de données Cloud Spanner" (roles/spanner.databaseReader).

    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. Pour en savoir plus, consultez la page À propos du contrôle des accès ultraprécis.

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. Toutes les requêtes que vous exécutez avec cette connexion utilisent ensuite 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 quasiment nul 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 base 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 la page 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.

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 plus d'informations, consultez la section Lire des données en parallèle dans la documentation Spanner.

Toutefois, cette option est limitée aux requêtes répondant à l'une des conditions suivantes :

Les autres requêtes renvoient une erreur. 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 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 seront 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 consulter 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 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

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