Sélectionner une page

Query Profile : comprendre comment l’utiliser

Le query profile vous permet de comprendre comment Snowflake exécute vos requêtes et ainsi de les améliorer. Dans cet article, nous expliquerons le fonctionnement de ce query profile et présenterons comment l’utiliser pour améliorer vos requêtes les moins performantes.

Query Profile : qu’est ce que c’est ?

Pour chaque requête SQL dans Snowflake, il existe un plan de requête correspondant produit par l’optimiseur de requête. Ce plan contient l’ensemble des instructions ou « étapes » requises pour traiter toute instruction SQL. Étant donné que Snowflake détermine automatiquement la manière optimale d’exécuter une requête, un plan de requête peut sembler différent de l’ordre logique de l’instruction SQL associée.

Dans Snowflake, le plan de requête est un DAG composé d’opérateurs connectés par des liens. Les opérateurs traitent un ensemble de lignes. Les exemples d’opérations incluent l’analyse d’une table, le filtrage de lignes, la jointure de données, l’agrégation, etc. Les liens transmettent des données entre les opérateurs.

Le query profile est une fonctionnalité de l’interface utilisateur de Snowflake qui vous donne des informations détaillées sur l’exécution d’une requête. Il contient une représentation visuelle du plan de requête, avec tous les nœuds et liens représentés. Les détails d’exécution et les statistiques sont fournis pour chaque nœud ainsi que pour la requête globale.

Query Profile : quand et pourquoi l’utiliser ? 

Le profil de requête doit être utilisé chaque fois que des informations de diagnostic supplémentaires sur une requête sont nécessaires. Un exemple courant consiste à comprendre pourquoi une requête s’exécute d’une certaine manière. Le profil de requête peut aider à révéler les étapes de la requête qui prennent beaucoup plus de temps à traiter que les autres. De même, vous pouvez utiliser le profil de requête pour déterminer pourquoi une requête est toujours en cours d’exécution et où elle se bloque.

Une autre application utile du profil de requête consiste à comprendre pourquoi une requête n’a pas renvoyé le résultat souhaité. En étudiant attentivement les liens entre les nœuds, vous pouvez potentiellement identifier les parties de votre requête qui entraînent des lignes supprimées ou des doublons, ce qui peut expliquer vos résultats inattendus

Il est possible d’afficher le profil de requête à partir de l’interface, le volet des résultats inclura un lien vers le profil de requête lorsque celle-ci aura été exécutée. Il est également possible de l’afficher via le volet Activity > Query History.

Query Profile : comment l’utiliser ? 

Prenons comme premier exemple une requête simple sur le dataset disponible dans chaque compte TPCH :

SELECT O_ORDERSTATUS, count(*) as NOMBRE , sum(O_TOTALPRICE) as PRIX
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.ORDERS
where O_ORDERPRIORITY = ‘2-HIGH’
group by O_ORDERSTATUS
order by PRIX;

Nous obtenons le profil de requête suivant : 

Le profile de requête est constitué de deux parties.

Sur la gauche, une succession de noeuds reliés par des flèches représente les différentes étapes de la requêtes SQL. Ces noeuds ont différents noms selon le travail effectué par Snowflake. Dans notre cas nous avons les 5 étapes :

  • TableScan suivi de son numéro ID entre crochet 2 : L’opérateur TableScan est un opérateur fondamental dans l’exécution des requêtes Snowflake. Il lit les données des tables ou des segments de table et constitue généralement la première étape de son processus de pipeline pour la plupart des requêtes. Chaque instruction FROM de la requête correspond généralement à un opérateur TableScan dans le pipeline;
  • Filter : C’est opérateur applique les conditions WHERE de la requête. Une utilisation efficace de l’opérateur Filtre peut réduire considérablement la quantité de données qui doivent être traitées par les opérateurs suivants dans le pipeline, ce qui accélère l’exécution des requêtes.
  • Aggregate : L’ opérateur Aggregate est responsable du calcul de toutes les fonctions d’agrégation dans une requête. Si la requête spécifie plusieurs fonctions d’agrégation, ces expressions sont regroupées en un seul opérateur ;
  • Sort : Comme son nom l’indique, il est question ici du tri venant de la clause ORDER BY ;
  • Result : L’ opérateur de résultat simple apparaîtra comme l’étape finale pour la plupart des requêtes SELECT – il renvoie les dernières lignes au client qui a émis la requête. Il enregistre également le résultat dans le cache de résultats de Snowflake. Cela vous permet de récupérer les résultats ultérieurement sans réexécuter l’intégralité du pipeline.

Il existe de nombreux opérateurs différents, tous sont consultables dans la documentation Snowflake sur ce lien.

Pour chaque noeud, le pourcentage affiché comme dans l’encadré 1 représente la proportion du temps global qu’occupe le noeud en particulier.

Enfin, les flèches (encadrés 2a et 2b) représentent le flux d’informations entre les noeuds avec le sens et le nombre de ligne qui transit d’un noeuds à l’autre. Cette feature est très utile pour contrôler le nombre de lignes créées et potentiellement détecter une explosion du nombre de ligne après une mauvaise jointure par exemple.

Dans la deuxième partie sur la droite, le profil de requête contient des statistiques utiles pour effectuer un diagnostique de chaque partie de la requête SQL.

Pour en souligner quelques-uns :

  • Un résumé du temps d’exécution (encadré n°4). Cela montre quel % du temps total d’exécution de la requête a été consacré à différents compartiments. Les 4 options listées ici incluent :
    • Traitement : temps consacré aux opérations de traitement des requêtes telles que les jointures, les agrégations, les filtres, les tris, etc.
    • E/S de disque local : temps passé à lire/écrire des données depuis/vers le stockage SSD local. Cela inclurait des éléments tels que le déversement sur le disque ou la lecture de données mises en cache à partir d’un SSD local.
    • E/S disque distant : temps passé à lire/écrire des données à partir du stockage distant (c’est-à-dire S3 ou Azure Blob). Cela inclurait des choses comme le déversement sur un disque distant ou la lecture de vos ensembles de données.
    • Initialisation : il s’agit d’un surcoût pour démarrer votre requête sur l’entrepôt. D’après notre expérience, il est toujours extrêmement faible et relativement constant
  • Les statistiques de la requête (encadré n°5). Des informations telles que le nombre de partitions analysées sur toutes les partitions possibles peuvent être trouvées ici. Moins de partitions analysées signifie que la requête est bien élaguée. Si votre entrepôt ne dispose pas de suffisamment de mémoire pour traiter votre requête et déborde sur le disque, ces informations seront reflétées ici.
  • Pourcentage du temps d’exécution total passé sur chaque nœud (encadré n°6). Affiché en haut à droite de chaque nœud, il indique le pourcentage du temps d’exécution total consacré à cet opérateur. Dans cet exemple, 94,7 % du temps d’exécution total a été consacré à l’opérateur TableScan. Ces informations sont utilisées pour remplir la liste « Nœuds les plus chers » en haut à droite du profil de requête, qui trie simplement les nœuds en fonction du pourcentage du temps d’exécution total.

    Query Profile : comment l’exploiter ? 

    Le cas d’utilisation le plus courant du profil de requête consiste à comprendre pourquoi une requête particulière ne fonctionne pas correctement. Voici quelques indicateurs que vous pouvez rechercher dans le profil de requête pour expliquer les mauvaises performances de celle-ci :

    • Le query profile vous permet d’identifier les requêtes présentant un « Local ou Remote disk spilling ». Avoir du « spilling » signifie que votre entrepôt n’a pas assez de mémoire pour traiter les données et doit les stocker temporairement ailleurs. Ce transfert de données sur un espace disque distant est extrêmement lent et dégradera considérablement les performances de vos requêtes. Pour plus d’information, vous pouvez consulter cette page de la communité Snowflake détaillant l’impact du spillover sur les performances des requêtes.
    • Le nombre de partitions est analysées par rapport au nombre total de partitions. De même que pour le transfert de données sur disque distant, la lecture de données à partir d’un disque distant est également très lente. Un grand nombre de partitions analysées signifie que votre requête doit faire beaucoup de travail pour lire des données distantes.
    • Le nombre de lignes entre deux noeuds. De manière générale, la bonne pratique est de limiter le plus rapidement possible le nombre de lignes pour accélérer la requête via des filtres. Un trop grand nombre de lignes doit être un signal d’alarme vous incitant à revoir votre script pour davantage filtrer vos données. Dans le cas particulier des jointures, le nombre de lignes sortantes peut augmenter anormalement, cela peut indiquer que vous avez mal spécifié votre clé de jointure. Il est également conseiller d’éviter le cas particulier des jointures cartésiennes qui produisent un ensemble de résultats correspondant au nombre de lignes de la première table multiplié par le nombre de lignes de la seconde table.  En effet, en raison du volume de données produites, ces jointures sont lentes et sont susceptibles d’entraînent souvent des problèmes de mémoire (et donc de spillover).
    • Opérateurs en aval bloqués par un CTE unique. Snowflake calcule chaque CTE une fois. Si un opérateur s’appuie sur ce CTE, il doit attendre la fin du traitement. Dans certains cas, il peut être plus avantageux de répéter le CTE en tant que sous-requête pour permettre un traitement parallèle.
    • Tri précoce et inutile. Il est courant que les utilisateurs ajoutent un tri inutile au début de leur requête. Les tris sont coûteux et doivent être évités à moins que cela ne soit absolument nécessaire.
    • Calcul répété de la même vue. Chaque fois qu’une vue est référencée dans une requête, elle doit être calculée. Si la vue contient des jointures, des agrégations ou des filtres coûteux, il peut parfois être plus efficace de matérialiser la vue en premier.
    • Un très grand profil de requête avec beaucoup de nœuds. Certaines requêtes sont trop complexes et peuvent être grandement améliorées en les simplifiant. Décomposer une requête en plusieurs requêtes plus simples est une technique efficace.