Programmer des requêtes

Cette page décrit comment programmer des requêtes récurrentes dans BigQuery.

bêta

Ce produit ou cette fonctionnalité est disponible dans sa version préliminaire, et peut changer ou bénéficier d'une assistance limitée. Pour plus d'informations, consultez les étapes de lancement des produits.

Présentation

Vous pouvez programmer des requêtes à exécuter de façon récurrente. Les requêtes programmées doivent être écrites en SQL standard et peuvent inclure des instructions en langage de définition de données (DDL) et en langage de manipulation de données (DML). La chaîne de requête et la table de destination peuvent être paramétrées, ce qui vous permet d'organiser les résultats de la requête par date et heure.

Avant de commencer

Avant de créer une requête programmée, veuillez effectuer les actions suivantes :

• Les requêtes programmées utilisent les fonctionnalités du Service de transfert de données BigQuery. Vérifiez que vous avez effectué toutes les actions requises dans Activer le service de transfert de données BigQuery.
• Assurez-vous que vous disposez des autorisations requises suivantes :
  • BigQuery : autorisations bigquery.transfers.update pour créer le transfert programmé. Le rôle IAM bigquery.admin prédéfini au niveau du projet comprend les autorisations bigquery.transfers.update. Pour en savoir plus sur les rôles IAM dans BigQuery, consultez la page Contrôle des accès.
• Autorisez les fenêtres contextuelles dans votre navigateur à partir de bigquery.cloud.google.com, afin de pouvoir afficher la fenêtre des autorisations. Vous devez autoriser le service de transfert de données BigQuery à gérer votre requête programmée.

Options de configuration

Chaîne de requête

La chaîne de requête doit être valide et écrite en SQL standard. À chaque exécution, une requête programmée peut recevoir les paramètres de requête suivants.

Paramètres disponibles

Paramètre	Type SQL standard	Valeur
@run_time	TIMESTAMP	Représenté en temps UTC. Pour les requêtes programmées de façon récurrente, run_time représente l'heure d'exécution prévue. Par exemple, si l'exécution d'une requête programmée est définie sur "toutes les 24 heures", la différence de run_time entre deux requêtes consécutives sera exactement de 24 heures, même si la durée d'exécution réelle peut légèrement varier.
@run_date	DATE	Représente une date de calendrier logique.

Exemple

Le paramètre @run_time fait partie de la chaîne de requête de l'exemple suivant, où une requête est exécutée sur un ensemble de données public nommé hacker_news.stories.

SELECT @run_time AS time,
  title,
  author,
  text
FROM `bigquery-public-data.hacker_news.stories`
LIMIT
  1000

Tableau de destination

Lorsque vous configurez la requête programmée et que la table de destination pour afficher les résultats n'existe pas, BigQuery va tenter de créer cette table. Si vous utilisez une requête DDL ou DML, laissez la table de destination vide.

Si la table cible existe, le schéma de la table de destination peut être mis à jour en fonction des résultats de la requête si vous ajoutez des colonnes au schéma (ALLOW_FIELD_ADDITION) ou assouplissez le mode d'une colonne de REQUIRED à NULLABLE (ALLOW_FIELD_RELAXATION). Dans tous les autres cas, les modifications du schéma de table entre les exécutions de requêtes entraînent l'échec de la requête programmée.

Les requêtes peuvent référencer des tables provenant de différents projets et de différents ensembles de données. Lors de la configuration de votre requête programmée, il n'est pas nécessaire d'ajouter l'ensemble de données de destination au nom de la table. Celui-ci peut être défini séparément.

Préférence d'écriture

L'option d'écriture que vous choisissez permet de déterminer la manière dont les résultats de votre requête sont écrits sur une table de destination existante. Si vous utilisez une requête DDL ou DML, laissez une préférence d'écriture vide.

• WRITE_TRUNCATE : si la table existe déjà, BigQuery écrase les données de la table.
• WRITE_APPEND : si la table existe déjà, BigQuery ajoute les données à la table.

Une table de destination est créée, écrasée ou modifiée seulement dans le cas où BigQuery est en mesure d'exécuter correctement la requête. Ces actions de création, d'écrasement ou d'ajout prennent la forme d'une mise à jour atomique en fin de tâche.

Options de partitionnement

Les requêtes programmées peuvent générer des tables de destination partitionnées ou non partitionnées. Si vous utilisez une requête DDL ou DML, laissez le champ de partitionnement vide. Il existe deux types de tables partitionnées dans BigQuery :

• Tables partitionnées par temps d'ingestion : tables partitionnées en fonction de l'horaire d'exécution de la requête programmée
• Tables partitionnées : tables partitionnées en fonction d'une colonne TIMESTAMP ou DATE

Si la table de destination doit être partitionnée en fonction d'une colonne, vous spécifierez le nom de la colonne au moment de la Configuration de la requête programmée. Laissez le champ partitionnement vide pour les tables partitionnées avec date d'ingestion et pour les tables non partitionnées.

Découvrez plus d'informations sur les tables de partitionnement dans la section Présentation des tables partitionnées.

Exemples de partitionnement

• Table sans partitionnement
  • Table de destination : mytable
  • Champ de partitionnement : laisser vide
• Table partitionnée avec date d'ingestion
  • Table de destination : mytable$YYYYMMDD
  • Champ de partitionnement : laisser vide
• Table partitionnée en colonnes
  • Table de destination : mytable
  • Champ de partitionnement : nom de la colonne TIMESTAMP ou DATE utilisée pour partitionner la table

Paramètres disponibles

Lors de la configuration de la requête programmée, vous pouvez définir la manière dont vous souhaitez partitionner la table de destination avec des paramètres d'exécution.

Paramètre	Type de modèle	Valeur
run_time	Horodatage formaté	En temps UTC, selon le planning d'exécution. Pour les requêtes programmées de façon récurrente, run_time représente l'heure d'exécution prévue. Par exemple, si l'exécution d'une requête programmée est définie sur "toutes les 24 heures", la différence de run_time entre deux requêtes consécutives sera exactement de 24 heures, même si la durée d'exécution réelle peut légèrement varier. Consultez TransferRun.runTime.
run_date	Chaîne de date	Date du paramètre run_time au format %Y%m%d, par exemple 20180101. Ce format est compatible avec les tables partitionnées par date d'ingestion.

Système de modélisation

Les requêtes programmées supportent les paramètres d'exécution dans le nom de la table de destination avec une syntaxe de modélisation.

Syntaxe des paramètres de modélisation

La syntaxe de modélisation supporte la modélisation de base des chaînes et le décalage horaire. Les paramètres sont référencés dans les formats suivants :

• {run_date}
• {run_time[+\-offset]|"time_format"}

Paramètre	Objectif
run_date	Ce paramètre est remplacé par la date au format YYYYMMDD.
run_time	Ce paramètre accepte les propriétés suivantes : offset Décalage horaire exprimé en heures (h), minutes (m) et secondes (s), selon cet ordre. Les jours (d) ne sont pas acceptés. Les valeurs décimales sont autorisées, par exemple : 1.5h. time_format Chaîne de mise en forme. Les paramètres de mise en forme les plus courants sont les années (%Y), les mois (%m) et les jours (%d). YYYYMMDD est le suffixe requis pour les tables partitionnées ; cela équivaut à "%Y%m%d". Pour plus d'informations, consultez la section relative à la mise en forme des éléments "datetime".

Consignes d'utilisation :

• Aucun espace n'est autorisé entre run_time, offset et time format.
• Pour inclure des accolades littérales dans la chaîne, vous pouvez les échapper comme ceci : ‘\{‘ and ‘\}’.
• Pour inclure des guillemets littéraux ou une barre verticale au format time_format, comme dans “YYYY|MM|DD”, vous pouvez les échapper au format de chaîne suivant : ‘\”’ ou ‘\|’.

Exemples de paramètres de modélisation

Les exemples suivants permettent d'illustrer la manière de définir les noms d'une table de destination avec différents formats d'heure, et en incluant un décalage de l'exécution.

run_time (UTC)	Paramètre modélisé	Nom de la table de destination de sortie
2018-02-15 00:00:00	mytable	mytable
2018-02-15 00:00:00	mytable_{run_time|"%Y%m%d"}	mytable_20180215
2018-02-15 00:00:00	mytable_+25h{run_time|"%Y%m%d"}	mytable_20180216
2018-02-15 00:00:00	mytable_-1h{run_time|"%Y%m%d"}	mytable_20180214
2018-02-15 00:00:00	mytable_+1.5h{run_time|"%Y%m%d;%H"} ou mytable_+90m{run_time|"%Y%m%d;%H"}	mytable_2018021501
2018-02-15 00:00:00	{run_time+97s|"%Y%m%d"}_mytable_{run_time|"%H%M%s"}	20180215_mytable_000137

Configurer une requête programmée

Console

1. Ouvrez l'interface utilisateur Web de BigQuery dans la console GCP.

   Accéder à l'UI Web de BigQuery

2. Exécutez la requête de votre choix. Lorsque vous êtes satisfait de vos résultats, cliquez sur Schedule Query (Programmer la requête) et sur Create new scheduled query (Créer une requête programmée).

   

3. Les options de requête programmées s'ouvrent dans le volet New scheduled query (Nouvelle requête programmée).

4. Dans le volet New scheduled query (Nouvelle requête programmée) :

   • Sous Name for the scheduled query (Nom de la requête programmée), saisissez un nom pour la requête programmée, tel que My scheduled query. Le nom de la requête programmée peut être toute valeur permettant d'identifier facilement la requête en cas de modification ultérieure.
   • (Facultatif) Sous Schedule options (Options de programmation), vous pouvez conserver la valeur par défaut Daily (Tous les jours, soit toutes les 24 heures en fonction de l'heure de création) ou cliquer sur Schedule start time (Programmer une heure de début) pour modifier l'heure d'exécution. Vous pouvez également modifier l'intervalle d'exécution en Hebdomadaire, Mensuel ou Personnalisé. Lorsque le mode Personnalisé est sélectionné, une spécification temporelle de type Cron est attendue, par exemple every 3 hours. La période la plus courte autorisée est de quinze minutes. Reportez-vous à la section concernant le champ schedule sur la page relative à TransferConfig pour découvrir d'autres valeurs d'API valides.
   • Sous Dataset name (Nom de l'ensemble de données), sélectionnez l'ensemble de données de destination.
   • Sous Destination table (Table de destination) :
     • Pour une requête SQL standard, saisissez le nom de la table de destination.
     • Pour une requête DDL ou DML, laissez ce champ vide.
   • Sous Destination table write preference (Préférence d'écriture pour la table de destination), choisissez WRITE_TRUNCATE pour remplacer la table de destination ou WRITE_APPEND pour ajouter des données à la table. Pour une requête DDL ou DML, cette option n'est pas disponible.

   • Cochez la case Send email notifications (Envoyer des notifications par e-mail) pour autoriser les notifications par e-mail en cas d'échec de l'exécution des transferts.

     

5. Cliquez sur Schedule (Programmer).

6. Pour afficher l'état de vos requêtes programmées, cliquez sur Requêtes programmées dans le volet de navigation. Actualisez la page pour afficher l'état actualisé de vos requêtes programmées.

UI classique

1. Accédez à l'UI Web de BigQuery.

   Accéder à l'UI Web de BigQuery

2. Exécutez la requête de votre choix. Lorsque vous êtes satisfait de vos résultats, cliquez sur Schedule Query (Programmer la requête).

   

3. Les options de requête programmées s'ouvrent sous la zone de requête.

4. Sur la page Nouvelle requête programmée :

   • Sous Destination dataset (Ensemble de données de destination), sélectionnez l'ensemble de données approprié.
   • Sous Display name (Nom à afficher), saisissez un nom pour la requête programmée, tel que My scheduled query. Le nom de la requête programmée peut être toute valeur permettant d'identifier facilement la requête en cas de modification ultérieure.
   • Pour la Destination table (Table de destination) :
     • Pour une requête SQL standard, saisissez le nom de la table de destination.
     • Pour une requête DDL ou DML, laissez ce champ vide.
   • Pour les Write preference (Préférences d'écriture) :
     • Pour une requête SQL standard, choisissez WRITE_TRUNCATE pour remplacer la table de destination ou WRITE_APPEND pour ajouter des données à la table.
     • Pour une requête DDL ou DML, choisissez Unspecified (Non spécifié).

   • (Facultatif) Pour le Partitioning field (Champ de partitionnement) :

     • Pour une requête en SQL standard, si la table de destination est une table partitionnée en colonnes, saisissez le nom de la colonne où la table doit être partitionnée. Laissez ce champ vide pour les tables partitionnées avec date d'ingestion et les tables non partitionnées.

     • Pour une requête DDL ou DML, laissez ce champ vide.

       

   • (Facultatif) Sous Schedule (Exécution programmée), vous pouvez conserver la valeur par défaut Daily (Tous les jours, soit toutes les 24 heures en fonction de l'heure de création) ou cliquer sur Edit (Modifier) pour changer le délai. Vous pouvez également modifier l'intervalle d'exécution en Weekly (Hebdomadaire), Monthly (Mensuel) ou Custom (Personnalisé). Lorsque le mode Personnalisé est sélectionné, une spécification temporelle de type Cron est attendue, par exemple every 3 hours. La période la plus courte autorisée est de quinze minutes. Reportez-vous à la section concernant le champ schedule sur la page relative à TransferConfig pour découvrir d'autres valeurs d'API valides.

     

   • (Facultatif) Développez la section Advanced (Avancé), puis configurez les notifications d'exécution pour votre transfert. Les notifications d'exécution de transfert sont actuellement en version alpha.

     • Sous Cloud Pub/Sub topic (Sujet Cloud Pub/Sub), saisissez le nom de votre sujet Cloud Pub/Sub, par exemple projects/myproject/topics/mytopic.

     • Cochez la case Send email notifications (Envoyer des notifications par e-mail) pour autoriser les notifications par e-mail en cas d'échec de l'exécution des transferts.

       

5. Cliquez sur Ajouter.

Configurer une requête manuelle en fonction de dates de l'historique

En plus de la programmation de l'exécution ultérieure d'une requête, vous pouvez également déclencher des exécutions immédiates manuellement. Le déclenchement d'une exécution immédiate est nécessaire si votre requête utilise le paramètre run_date et qu'il existe des problèmes lors d'une exécution précédente.

Par exemple, tous les jours à 09h00, vous interrogez une table source sur les lignes correspondant à la date du jour. Cependant, vous constatez que les données n'ont pas été ajoutées à la table source au cours des trois derniers jours. Dans ce cas, vous pouvez configurer la requête pour qu'elle s'exécute sur des données historiques dans une plage de dates que vous spécifiez. Votre requête est exécutée en utilisant des combinaisons de run_date et run-time correspondant aux dates que vous avez configurées dans votre requête programmée.

Après avoir configuré une requête programmée, vous pouvez l'exécuter en utilisant une plage de dates historique en procédant comme suit :

Console

Après avoir cliqué sur Programmer pour enregistrer votre requête programmée, vous pouvez cliquer sur le bouton Scheduled queries (Requêtes programmées) pour afficher la liste des requêtes actuellement programmées. Cliquez sur n'importe quel nom pour afficher les détails de programmation de cette requête. En haut à droite de la page, cliquez sur Schedule backfill (Programmer le remplissage) pour spécifier une plage de dates historique.

Les durées d'exécution choisies sont comprises dans la plage sélectionnée, y compris la première date et à l'exclusion de la dernière date.

Notez que les plages de dates que vous fournissez sont affichées au format UTC, mais que la programmation de votre requête est affichée dans votre fuseau horaire local (voir l'exemple 2 ci-dessous pour résoudre ce problème).

Vous pouvez utiliser l'interface utilisateur classique de BigQuery pour éviter cet écart de fuseau horaire et pour définir des limites de durée plus précises.

Exemple 1

Votre requête programmée est configurée pour s'exécuter selon l'heure du Pacifique every day 09:00. Il vous manque des données pour le 1er, 2 et 3 janvier. Choisissez la plage de dates historique suivante :

Start Time = 1/1/19
End Time = 1/4/19

Votre requête s'exécute avec les paramètres run_date et run_time correspondant aux heures suivantes :

• 1/1/19 09:00 (Heure du Pacifique)
• 1/2/19 09:00 (Heure du Pacifique)
• 1/3/19 09:00 (Heure du Pacifique)

Exemple 2

Votre requête programmée est configurée pour s'exécuter selon l'heure du Pacifique every day 23:00. Il vous manque des données pour le 1er, 2 et 3 janvier. Choisissez les plages de dates historiques suivantes (les dates ultérieures sont choisies, car UTC a une date différente à 23h00, heure du Pacifique) :

Start Time = 1/2/19
End Time = 1/5/19

Votre requête s'exécute avec les paramètres run_date et run_time correspondant aux heures suivantes :

• 1/2/19 09:00 (UTC) ou 1/1/2019 23:00 (Heure du Pacifique)
• 1/3/19 09:00 (UTC) ou 1/2/2019 23:00 (Heure du Pacifique)
• 1/4/19 09:00 (UTC) ou 1/3/2019 23:00 (Heure du Pacifique)

Après avoir configuré les exécutions manuelles, actualisez la page pour les afficher dans la liste des exécutions.

UI classique

Une fois que vous avez cliqué sur Ajouter pour enregistrer votre requête programmée, les détails de votre requête programmée s'affichent. Sous les détails, cliquez sur le bouton Start Manual Runs (Démarrer les exécutions manuelles) pour spécifier une plage de dates de l'historique.

Vous pouvez affiner davantage la plage de dates pour définir une heure de début et de fin, ou laisser les champs de l'heure à 00:00:00.

Exemple 1

Si votre requête programmée est définie pour s'exécuter every day 14:00 et que vous appliquez la plage de dates de l'historique suivante :

Start Time = 2/21/2018 00:00:00 AM
End Time = 2/24/2018 00:00:00 AM

Votre requête s'exécute aux heures suivantes :

• 2/21/2018 14:00:00
• 2/22/2018 14:00:00
• 2/23/2018 14:00:00

Exemple 2

Si votre requête programmée est définie pour s'exécuter every fri at 01:05 et que vous appliquez la plage de dates de l'historique suivante :

Start Time = 2/1/2018 00:00:00 (un jeudi)
End Time = 2/24/2018 00:00:00 AM (également un jeudi)

Votre requête s'exécute aux heures suivantes :

• 2/2/2018 01:05:00
• 2/9/2018 01:05:00

Quotas

Une requête programmée est exécutée en fonction du projet et des identifiants du créateur, comme si vous exécutiez vous-même la requête. La requête programmée est soumise à tous les quotas et limites BigQuery applicables aux tâches de requête.

Limites et problèmes connus

Régions

Les requêtes inter-régions ne sont pas prises en charge et la table de destination de votre requête programmée doit se trouver dans la même région que les données interrogées. Veuillez consulter la page Zones des ensembles de données pour plus d'informations sur les régions et les zones multirégionales.

Google Drive

Vous pouvez interroger les données Google Drive dans une requête programmée. Si vous programmez une requête existante, vous devrez peut-être cliquer sur Update Credentials (Mettre à jour les informations d'identification) sur l'écran de détails de la requête programmée. Attendez 10 à 20 minutes pour que le changement prenne effet. Vous devrez peut-être vider le cache de votre navigateur. Les identifiants sont automatiquement mis à jour pour les nouvelles requêtes programmées.