Planifier des requêtes

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

Beta

This is a beta release of programmer des requêtes dans in BigQuery. might be changed in backward-incompatible ways and is not subject to any SLA or deprecation policy.

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 planifié. 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 : ‘\”’ or ‘\|’.

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

Interface utilisateur 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 trois heures. Consultez le champ schedule sous TransferConfig pour obtenir des valeurs supplémentaires 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

Outre la programmation d'exécution de requêtes, vous pouvez également configurer une requête pour qu'elle s'exécute en fonction de données de l'historique selon une plage de dates spécifiée. Votre requête est exécutée selon la plage de dates définie au même moment que celle spécifiée lors de la création de la requête programmée.

Après avoir Configuré une requête programmée, vous pouvez exécuter la requête selon une plage de dates de l'historique.

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 planifié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.