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. 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 transférée de Spanner vers l'espace de stockage BigQuery.

Vous n'avez pas besoin de créer de connexion pour interroger les 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 avec une fonction EXTERNAL_QUERY. Cela peut être utile si vous souhaitez interroger une 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 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 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é. 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 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 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 directement visibles 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

Requêtes interrégionales

BigQuery accepte les requêtes fédérées lorsque 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.

Pendant la période de preview, le transfert de données est gratuit. Toutefois, vous pouvez consulter votre utilisation avec les SKU suivants :

  • Transfert de données réseau intrarégional et interzones sortant
  • Transfert de données sortantes interrégions vers le même continent pour les instances gratuites
  • Transfert de données réseau sortantes interrégions vers un autre continent pour les instances gratuites

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 possédant des instances dupliquées en lecture/écriture ou en lecture seule.

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

  • Région multirégionale BigQuery US : région Spanner us-central1
  • Région multirégionale 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 dans 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