Présentation des requêtes fédérées

Cette page explique comment utiliser les requêtes fédérées et fournit des conseils sur l'interrogation des données Spanner, AlloyDB et Cloud SQL à partir de BigQuery.

Les requêtes fédérées vous permettent d'envoyer une instruction de requête aux bases de données AlloyDB, Spanner ou Cloud SQL et d'obtenir le résultat en tant que table temporaire. Les requêtes fédérées utilisent l'API BigQuery Connection pour établir une connexion avec AlloyDB, Spanner ou Cloud SQL. Dans votre requête, vous utilisez la fonction EXTERNAL_QUERY pour envoyer une instruction de requête à la base de données externe, à l'aide du dialecte SQL de cette base de données. Les résultats sont convertis en types de données GoogleSQL.

Data stores compatibles

Vous pouvez utiliser des requêtes fédérées avec les datastores suivants :

Workflow

  • Identifiez le projet Google Cloud qui inclut la source de données que vous souhaitez interroger.
  • Un utilisateur bigquery.admin crée une ressource de connexion dans BigQuery.
  • L'administrateur accorde l'autorisation d'utiliser la ressource de connexion à l'utilisateur B.
    • Si l'administrateur et l'utilisateur B sont la même personne, il n'est pas nécessaire d'accorder une autorisation.
  • L'utilisateur B écrit une requête dans BigQuery à l'aide de la nouvelle fonction SQL EXTERNAL_QUERY.

Alternatives aux requêtes fédérées : tables et ensembles de données externes

Une autre option pour interroger des bases de données opérationnelles telles que Bigtable, Spanner, Cloud Storage, Google Drive et Salesforce Data Cloud consiste à utiliser des tables et des ensembles de données externes. Les ensembles de données et les tables externes vous permettent d'afficher les tables et leurs schémas, et de les interroger sans utiliser de fonction SQL EXTERNAL_QUERY. Vous n'avez pas besoin de réimporter les données dans BigQuery. Vous pouvez utiliser la syntaxe BigQuery au lieu d'écrire dans le dialecte de base de données SQL spécifique.

Régions où le service est disponible

Pour obtenir la liste des emplacements acceptés, consultez les sections suivantes :

AlloyDB et Cloud SQL

Les requêtes fédérées ne sont possibles que dans les régions gérant à la fois la source de données externe et BigQuery.

Vous pouvez créer une connexion et exécuter une requête fédérée sur plusieurs régions en suivant les règles suivantes :

Régions uniques

Une région unique BigQuery ne peut interroger qu'une ressource dans la même région.

Par exemple, si votre ensemble de données est hébergé dans la région us-east4, vous pouvez seulement interroger des instances Cloud SQL ou AlloyDB qui se trouvent dans la région us-east4. L'emplacement de traitement des requêtes correspond à la région unique BigQuery.

Zones multirégionales

Un emplacement multirégional BigQuery peut interroger n'importe quelle région de source de données figurant dans la même zone géographique (États-Unis, Union européenne). Les emplacements multirégionaux ne sont pas disponibles pour les instances Cloud SQL, car celles-ci ne sont utilisées que pour les sauvegardes.

  • Une requête exécutée dans l'emplacement multirégional BigQuery "US" peut interroger n'importe quelle région unique de la zone géographique des États-Unis, telle que us-central1, us-east4 ou us-west2.

  • Une requête exécutée dans l'emplacement multirégional BigQuery "UE" peut interroger n'importe quelle région unique correspondant à des États membres de l'Union européenne, telle que europe-north1 ou europe-west3.

  • L'emplacement d'exécution de la requête doit être identique à celui de la ressource de connexion. Par exemple, les requêtes exécutées à partir de l'emplacement multirégional "US" doivent utiliser une connexion située dans l'emplacement multirégional "US".

Les performances des requêtes varient en fonction de la proximité entre l'ensemble de données et la source de données externe. Par exemple, une requête fédérée entre un ensemble de données situé dans l'emplacement multirégional "US" et une instance Cloud SQL située dans la région us-central1 va être rapide. Cependant, si vous exécutez la même requête entre l'emplacement multirégional "US" et une instance Cloud SQL située dans la région us-east4, les performances peuvent être plus lentes.

La zone de traitement de la requête correspond à la zone multirégionale, US ou EU.

Spanner

Pour Spanner, les configurations régionales et multirégionales sont acceptées. Une région unique ou un emplacement multirégional BigQuery peuvent interroger une instance Spanner dans n'importe quelle région Spanner compatible. Pour en savoir plus, consultez Requêtes multirégionales.

Mappages des types de données

Lorsque vous exécutez une requête fédérée, les données de la source de données externe sont converties en types GoogleSQL. Pour en savoir plus, consultez la page Requêtes fédérées Cloud SQL.

Quotas et limites

  • Requêtes fédérées interrégionales : Si l'emplacement de traitement des requêtes BigQuery et l'emplacement de la source de données externe sont différents, il s'agit d'une requête interrégionale. Vous pouvez exécuter jusqu'à 1 To de requêtes interrégionales par projet et par jour. Voici un exemple de requête interrégionale.
    • L'instance Cloud SQL se trouve dans us-west1, tandis que la connexion BigQuery est basée dans l'emplacement multirégional "US". L'emplacement de traitement de la requête BigQuery est US.
  • Quota. Les utilisateurs doivent contrôler les quotas de requêtes dans la source de données externe, telle que Cloud SQL ou AlloyDB. Il n'y a pas de paramètres de quotas distincts pour les requêtes fédérées. Pour isoler la charge de travail, il est recommandé de n'interroger qu'une instance dupliquée de base de données avec accès en lecture.
  • Nombre maximal d'octets facturés autorisé. Ce champ n'est pas disponible pour les requêtes fédérées. Il n'est pas possible de calculer les octets facturés avant l'exécution effective des requêtes fédérées.
  • Nombre de connexions. Une requête fédérée peut compter au plus 10 connexions uniques.
  • Cloud SQL MySQL et PostgreSQL. Des quotas et des limites s'appliquent.

Limites

Les requêtes fédérées sont soumises aux limites suivantes :

  • Performances La requête fédérée risque de ne pas être aussi rapide que d'interroger uniquement le stockage BigQuery. BigQuery doit attendre que la base de données source exécute la requête externe et déplace les données de façon temporaire de la source de données externe vers BigQuery. En outre, la base de données source peut ne pas être optimisée pour des requêtes analytiques complexes.

    Les performances des requêtes varient également en fonction de la proximité entre l'ensemble de données et la source de données externe. Pour en savoir plus, consultez la section Régions où le service est disponible.

  • Les requêtes fédérées sont en lecture seule. La requête externe exécutée dans la base de données source doit être en lecture seule. Par conséquent, les instructions LMD ou LDD ne sont pas compatibles.

  • Types de données non compatibles. Si votre requête externe contient un type de données non géré dans BigQuery, la requête échoue immédiatement. Vous pouvez convertir le type de données non géré en un autre type de données compatible.

  • Clés de chiffrement gérées par le client (CMEK). La clé CMEK est configurée séparément pour BigQuery et pour les sources de données externes. Si vous configurez la base de données source pour qu'elle utilise CMEK, mais pas BigQuery, la table temporaire contenant les résultats d'une requête fédérée est chiffrée avec un Google-owned and Google-managed encryption key.

Tarifs

  • Si vous utilisez le modèle de tarification à la demande, le nombre d'octets renvoyés par la requête externe vous est facturé lorsque vous exécutez des requêtes fédérées à partir de BigQuery. Pour en savoir plus, consultez la section Tarifs d'analyse à la demande.

  • Si vous utilisez les éditions BigQuery, des frais vous sont facturés en fonction du nombre d'emplacements que vous utilisez. Pour en savoir plus, consultez la section Tarifs de la capacité de calcul.

Pushdown SQL

Les requêtes fédérées sont soumises à la technique d'optimisation appelée "pushdown SQL". Elles améliorent les performances d'une requête en déléguant des opérations telles que le filtrage à la source de données externes au lieu de les exécuter dans BigQuery. La réduction de la quantité de données transférées à partir de la source de données externe peut réduire le temps d'exécution des requêtes, ainsi que les coûts. Les pushdown SQL incluent l'élimination des colonnes (clauses SELECT) et le pushdown de filtres (clauses WHERE).

Lorsque vous utilisez la fonction EXTERNAL_QUERY, les pushdown SQL fonctionnent en réécrivant la requête d'origine. Dans l'exemple suivant, la fonction EXTERNAL_QUERY est utilisée pour communiquer avec une base de données Cloud SQL :

SELECT COUNT(*)
FROM (
  SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table")
  )
WHERE a = 'Y' AND b NOT IN ('COMPLETE','CANCELLED');

Remplacez CONNECTION_ID par l'ID de la connexion BigQuery.

Sans pushdown SQL, la requête suivante est envoyée à Cloud SQL :

SELECT *
FROM operations_table

Lorsque cette requête est exécutée, l'intégralité de la table est renvoyée à BigQuery, même si seules certaines lignes et colonnes sont nécessaires.

Avec les pushdown SQL, la requête suivante est envoyée à Cloud SQL :

SELECT `a`, `b`
FROM (
  SELECT * FROM operations_table) t
WHERE ((`a` = 'Y') AND (NOT `b` IN ('COMPLETE', 'CANCELLED')))

Lorsque cette requête est exécutée, seules deux colonnes et les lignes correspondant au prédicat de filtrage sont renvoyées à BigQuery.

Les pushdown SQL sont également appliqués lorsque vous exécutez des requêtes fédérées avec des ensembles de données externes Spanner.

Vous pouvez examiner les pushdowns appliqués (le cas échéant) dans le plan de requête.

Limites

Les pushdowns SQL présentent différentes limites qui varient en fonction de la source de données externe et de la façon dont vous interrogez les données.

Limites de la fédération de requêtes lorsque vous utilisez EXTERNAL_QUERY

  • Les pushdown SQL ne sont appliqués qu'aux requêtes fédérées au format SELECT * FROM T.
  • Seules les opérations d'élimination des colonnes et de pushdown de filtres sont acceptées. Plus précisément, les opérations pushdown de calcul, de jointure, de limite, de tri et d'agrégation ne sont pas acceptées.
  • Pour les pushdown de filtre, les littéraux doivent correspondre à l'un des types suivants : BOOL, INT64, FLOAT64, STRING, DATE, DATETIME, TIMESTAMP. Les littéraux qui sont des structs ne sont pas acceptés.
  • Les pushdowns de fonctions SQL ne sont appliqués qu'aux fonctions compatibles à la fois avec BigQuery et une base de données de destination.
  • Les pushdown SQL ne sont compatibles qu'avec AlloyDB, Cloud SQL et Spanner.
  • Les pushdowns SQL ne sont pas compatibles avec SAP Datasphere.

Limites de la fédération de requêtes lors de l'utilisation d'ensembles de données externes Spanner

  • Les opérations pushdown d'élimination des colonnes, de filtre, de calcul et d'agrégation partielle sont acceptées. Plus précisément, les jointures, les limites et les agrégations "order by" ne sont pas acceptées.
  • Pour les pushdown de filtre, les littéraux doivent correspondre à l'un des types suivants : BOOL, INT64, FLOAT64, STRING, DATE, DATETIME, TIMESTAMP, BYTE ou Arrays. Les littéraux qui sont des structs ne sont pas acceptés.
  • Les pushdowns de fonctions SQL ne sont appliqués qu'aux fonctions compatibles à la fois avec BigQuery et Spanner.

Fonctions compatibles par source de données

Les fonctions SQL suivantes sont acceptées par source de données. Aucune fonction n'est compatible avec SAP Datasphere.

Cloud SQL (MySQL)

  • Opérateurs logiques : AND, OR, NOT.
  • Opérateurs de comparaison : =, >, >=, <, <=, <>, IN, BETWEEN, IS NULL.
  • Opérateurs arithmétiques :+, -, * (uniquement pour INT64 et FLOAT64).

Cloud SQL pour PostgreSQL et AlloyDB

  • Opérateurs logiques : AND, OR, NOT.
  • Opérateurs de comparaison : =, >, >=, <, <=, <>, IN, BETWEEN, IS NULL.
  • Opérateurs arithmétiques :+, -, *, / (uniquement pour les types INT64, FLOAT64 et DATE, à l'exception de la soustraction DATE).

Spanner - Dialecte PostgreSQL

  • Opérateurs logiques : AND, OR, NOT.
  • Opérateurs de comparaison : =, >, >=, <, <=, <>, IN, BETWEEN, IS NULL.
  • Opérateurs arithmétiques : +, -, *, / (uniquement pour INT64, FLOAT64, NUMERIC).

Spanner - Dialecte GoogleSQL

  • Opérateurs logiques : AND, OR, NOT.
  • Opérateurs de comparaison : =, >, >=, <, <=, <>, IN, BETWEEN, IS NULL.
  • Opérateurs arithmétiques : +, -, *, / (uniquement pour INT64, FLOAT64, NUMERIC).
  • Opérateurs arithmétiques sécurisés : SAFE_ADD, SAFE_SUBTRACT, SAFE_MULTIPLY, SAFE_DIVIDE (uniquement pour INT64, FLOAT64, NUMERIC).
  • Si vous utilisez des ensembles de données externes, vous devez également :
    • Pushdown de calcul
    • Réduction partielle de l'agrégat
    • Fonctions String
    • les fonctions Math,
    • Fonctions Cast
    • Fonctions Array.

Utiliser des classements dans des sources de données externes

Une source de données externe peut avoir un classement défini sur une colonne (par exemple, l'insensibilité à la casse). Lorsque vous exécutez une requête fédérée, la base de données distante prend en compte le classement configuré.

Prenons l'exemple suivant, dans lequel vous disposez d'une colonne flag avec un classement insensible à la casse dans la source de données externe :

SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table where flag = 'Y'")

Remplacez CONNECTION_ID par l'ID de la connexion BigQuery.

La requête précédente renvoie les lignes où flag est y ou Y, car la requête est exécutée sur la source de données externe.

Toutefois, pour la fédération de requêtes avec les sources de données Cloud SQL, SAP Datasphere ou AlloyDB, si vous ajoutez un filtre à votre requête principale, la requête est exécutée côté BigQuery avec le classement par défaut. Consultez la requête suivante :

SELECT * FROM
  (
    SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table")
  )
WHERE flag = 'Y'

En raison de la collation par défaut sensible à la casse dans BigQuery, la requête précédente ne renvoie que les lignes où le signalement est Y et filtre les lignes où le signalement est y. Pour que votre clause WHERE ne soit pas sensible à la casse, spécifiez la collation dans la requête :

SELECT * FROM
  (
    SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table")
  )
WHERE COLLATE(flag, 'und:ci') = 'Y'

Étapes suivantes