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 des tables et leurs schémas, et de les interroger sans utiliser de fonction SQL EXTERNAL_QUERY. Vous n'avez pas besoin de renvoyer les données dans BigQuery et vous pouvez utiliser la syntaxe BigQuery au lieu d'écrire dans le dialecte SQL spécifique de la base de données SQL.

Régions où le service est disponible

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. Pour obtenir la liste des emplacements acceptés, consultez les sections suivantes :

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, AlloyDB, ou des bases de données Spanner 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. Un emplacement multirégional BigQuery peut également interroger une instance Spanner située dans le même emplacement multirégional.

  • 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.

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.

  • Project. Vous devez créer la ressource de connexion dans le même projet que l'instance Cloud SQL ou AlloyDB.

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>", "select * from operations_table")
  )
WHERE a = 'Y' AND b NOT IN ('COMPLETE','CANCELLED');

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'ensemble de la table est renvoyé à 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 lors de l'exécution de requêtes fédérées avec des 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 manière 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 pushdowns de calcul, de jointure, de limite, de tri et d'agrégation ne sont pas acceptés.
  • 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 structures ne sont pas acceptés.
  • Les pushdowns de fonctions SQL ne sont appliqués que pour les fonctions compatibles à la fois avec BigQuery et une base de données de destination.
  • Les pushdowns 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 lorsque vous utilisez des ensembles de données externes Spanner

  • Les opérations pushdown d'élimination des colonnes, de filtrage, de calcul et d'agrégation partielle sont acceptées. Plus précisément, les opérations de jointure, de limite et de tri par 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, BYTE ou Arrays. Les littéraux qui sont des structures ne sont pas acceptés.
  • Les pushdowns de fonctions SQL ne sont appliqués que pour les 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 prise en charge pour 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).
  • Lorsque vous utilisez des ensembles de données externes, vous devez également :
    • Calcul pushdown,
    • pushdown d'agrégation partielle,
    • Fonctions chaîne,
    • Fonctions mathématiques,
    • Fonctions Cast
    • Fonctions array

Étape suivante