Retool + BigQuery : requêtes sur grands volumes avec procédures stockées et agent Gemini — Documentation

Introduction

Cette documentation explique comment contourner les limites de volume de données lors de l'utilisation de BigQuery dans Retool, en appliquant de bonnes pratiques de performance, et en mettant en place l'une (ou les deux) des approches suivantes :

Procédures stockées BigQuery — exécuter la logique d'agrégation et de mise en forme côté BigQuery, puis ne renvoyer à Retool qu'un résultat déjà synthétisé.
Agent conversationnel BigQuery (Gemini Data Analytics) — interroger les données via un agent rattaché à BigQuery, consommé depuis Retool (chat ou réponse « structurée » à afficher dans l'interface).

Pourquoi ces limites existent

Sur Retool, les requêtes BigQuery peuvent devenir bloquantes lorsque le volume de résultats est trop important :

Retool Cloud — les requêtes ne doivent généralement pas dépasser 100 MB de résultats.
Retool Self-hosted — le seuil est plus élevé, généralement 500 MB.

Même en l'absence de limite technique, renvoyer des dizaines/centaines de milliers de lignes au navigateur est rarement une bonne idée :

ralentissements et freeze du navigateur,
interface moins fluide,
coûts BigQuery inutiles (scans + transferts),
difficultés de partage de rapports (résultats trop lourds).

L'objectif est donc de déplacer un maximum de calcul côté BigQuery et de n'envoyer à Retool que la donnée utile, déjà agrégée, filtrée et formatée.

Cas d'usage

Les outils ETL (ex. Funnel) consolident les métriques publicitaires (Google Ads, Meta, TikTok…) et e-commerce (Shopify) sur BigQuery, produisant une base très volumineuse.

Le besoin métier est de permettre à des account managers de :

générer un rapport sur une période (7 derniers jours, mois précédent, etc.),
comparer à une période équivalente (même durée il y a 6 mois, ou N semaines auparavant),
partager facilement ce rapport avec un client.

Accès et authentification

Les deux approches ci-dessous reposent sur un compte de service Google Cloud.

La création du compte de service, l'export de clé JSON, et la configuration des permissions sont décrites dans la documentation Intégration BigQuery.

1. Procédures stockées BigQuery

Quand utiliser cette option

Utilisez une procédure stockée si :

vous connaissez précisément le format attendu (tableau d'agrégats, KPI, séries temporelles),
vous souhaitez des résultats reproductibles et testables,
vous voulez centraliser la logique (mêmes calculs pour Retool, Looker, exports, etc.).

Principe

Une procédure stockée est un bloc SQL nommé, stocké dans un dataset, qui :

accepte des paramètres (dates, client, canal, etc.),
exécute des transformations/agrégations,
renvoie un résultat final plus léger.

Créer une procédure stockée dans BigQuery

Dans BigQuery, ouvrez l'éditeur SQL et adaptez le gabarit suivant.

Points importants :

la procédure doit être créée dans un dataset (ex. reporting),
les paramètres doivent correspondre aux filtres nécessaires au rapport,
la requête finale doit être pensée pour limiter le volume renvoyé.

sql

CREATE OR REPLACE PROCEDURE `<DATASET>.<NOM_DE_PROCEDURE>`(
  start_date DATE,
  end_date   DATE,
  client_id  STRING
)
BEGIN
  WITH combined AS (
    SELECT
      date,
      channel,
      campaign,
      clicks,
      impressions,
      spend,
      conversions
    FROM `<PROJECT>.<DATASET>.<TABLE>`
    WHERE date BETWEEN start_date AND end_date
      AND client = client_id
  )
  SELECT
    channel,
    SUM(clicks)                              AS clicks,
    SUM(impressions)                         AS impressions,
    SUM(spend)                               AS spend,
    SUM(conversions)                         AS conversions,
    SAFE_DIVIDE(SUM(conversions), SUM(clicks)) AS cvr
  FROM combined
  GROUP BY channel
  ORDER BY spend DESC;
END;

Appeler la procédure depuis Retool

Dans Retool (requête BigQuery), vous appelez la procédure avec :

Appel de la procédure stockée depuis une query Retool

Adaptez la conversion DATE / DATETIME selon vos inputs, et vérifiez le format des variables Retool (string vs date).

2. Agent BigQuery (Gemini Data Analytics) depuis Retool

Quand utiliser cette option

Utilisez cette option si :

vous voulez offrir un mode conversationnel (questions/réponses),
vous voulez générer un texte de synthèse (ex. commentaire automatique du rapport),
vous avez des utilisateurs non techniques qui doivent interroger la donnée.

Permissions minimales

Le compte (ou service account) qui appelle l'API doit disposer au minimum du rôle :

Gemini Data Analytics Data Agent User (Beta)

Créer et configurer l'agent dans BigQuery

Dans BigQuery, créez un agent depuis l'onglet Conversations puis Create Conversations.

Création d'un agent dans BigQuery Conversations

Définissez ses instructions : quelles sources il peut utiliser, quel format de sortie vous attendez (texte, JSON, bullets…).

Créer la ressource REST API dans Retool

Dans Retool : Resources → Create new → REST API

Renseignez le Host (base URL) :

https://geminidataanalytics.googleapis.com/v1beta/projects/<PROJECT_ID>/location/

Pour récupérer PROJECT_ID, utilisez la console GCP (ID projet).

Authentification :

Sélection Google Service Account dans Retool

Choisir Google Service Account
Coller la clé JSON (service account)

Scopes :

https://www.googleapis.com/auth/cloud-platform

Pour récupérer le PROJECT_ID depuis la Google Console :

Récupération du Project ID dans Google Cloud Console

Récupérer le nom unique de l'agent (dataAgent)

Avant d'appeler l'agent, vous devez récupérer son identifiant (champ name).

Dans Retool Query Library :

Méthode : GET
Path : global/dataAgents

Dans la réponse, repérez la propriété name de l'agent souhaité.

Réponse JSON avec la propriété name de l'agent

Créer la requête d'appel de l'agent

Configuration de la query d'appel de l'agent dans Retool

Vous envoyez un body JSON incluant messages (l'historique) et dataAgentContext.dataAgent (le nom unique de l'agent) :

json

{
  "messages": {{ history }},
  "dataAgentContext": {
    "dataAgent": "<AGENT_NAME>"
  }
}

history prend en input le tableau des messages. Deux modes de gestion de la conversation :

Stateless — vous renvoyez tout l'historique dans messages à chaque appel.
Stateful (conversationReference) — vous envoyez un identifiant de conversation et Google Cloud conserve l'historique côté serveur.

Format attendu de messages :

json

[
  {
    "userMessage": { "text": "" }
  },
  {
    "systemMessage": {
      "text": {
        "parts": [],
        "textType": ""
      }
    }
  }
]

Intégrer dans le composant Retool "LLM Chat"

Ajoutez un composant LLM Chat dans votre application Retool. Cela crée automatiquement une query associée.

Augmenter le timeout — les appels à l'agent peuvent être plus longs qu'une requête SQL classique. Augmentez le timeout de la query associée avant les tests.

Configuration du timeout de la query dans Retool

Transformer le résultat — dans la query : General → Transform results :

return data
  .filter(msg => msg.systemMessage?.text?.textType === "FINAL_RESPONSE")
  .flatMap(msg => msg.systemMessage.text.parts)
  .join("\n\n");

Types de messages (textType) :

Valeur	Description
`TEXT_TYPE_UNSPECIFIED`	Type de texte par défaut.
`FINAL_RESPONSE`	Réponse finale à la question de l'utilisateur.
`THOUGHT`	Plan de réflexion généré par l'outil de réflexion.
`PROGRESS`	Message informatif sur la progression de l'agent.

Convertir le format messageHistory du composant LLM Chat — la structure par défaut n'est pas directement compatible avec l'API Gemini Data Analytics :

const history = llmChat.messageHistory;
history.push({ role: "user", content: llmChat.inputValue });

return history
  .filter(msg => msg.content !== "" && msg.content != null)
  .map(msg => {
    if (msg.role === "user") {
      return { userMessage: { text: msg.content } };
    }
    return {
      systemMessage: {
        text: {
          parts: [msg.content],
          textType: "FINAL_RESPONSE"
        }
      }
    };
  });

Résultat final du composant LLM Chat intégré dans Retool

Adaptez llmChat au nom exact de votre composant dans Retool.