Optimisation des performances de stockage¶

Cette rubrique traite des optimisations du stockage qui peuvent améliorer les performances des requêtes, telles que le stockage de données similaires ensemble, la création de structures de données optimisées et la définition d’ensembles de données spécialisés. Snowflake propose trois de ces stratégies de stockage : le clustering automatique, l’optimisation de la recherche et les vues matérialisées.

En général, ces stratégies de stockage n’améliorent pas sensiblement les performances des requêtes qui s’exécutent déjà en une seconde ou plus.

Les stratégies présentées dans cette rubrique ne sont qu’un moyen parmi d’autres d’améliorer les performances des requêtes. Pour les stratégies liées aux ressources de calcul utilisées pour exécuter une requête, voir Optimisation des performances des entrepôts.

Introduction aux stratégies de stockage¶

Clustering automatique¶

Snowflake stocke les données d’une table dans des micro-partitions. Parmi ces micro-partitions, Snowflake organise (c’est-à-dire regroupe en cluster) les données en fonction de leurs dimensions. Si une requête est filtrée, jointe ou agrégée selon ces dimensions, moins de micropartitions doivent être analysées pour obtenir des résultats, ce qui accélère considérablement la requête.

Vous pouvez définir une clé de cluster pour modifier l’organisation par défaut des micropartitions afin que les données soient regroupées autour de dimensions spécifiques (c’est-à-dire des colonnes). Le choix d’une clé de cluster améliore les performances des requêtes qui filtrent, joignent ou agrègent les colonnes définies dans la clé de cluster.

Snowflake active le clustering automatique pour maintenir le clustering de la table dès que vous définissez une clé de cluster. Une fois activé, le clustering automatique met à jour les micropartitions au fur et à mesure que de nouvelles données sont ajoutées à la table. En savoir plus

Service d’optimisation de la recherche¶

Le service d’optimisation de la recherche améliore les performances des requêtes de recherche ponctuelle (c’est-à-dire des « recherches d’une aiguille dans une botte de foin ») qui renvoient un petit nombre de lignes d’une table à l’aide de filtres très sélectifs. Le service d’optimisation de la recherche est idéal lorsqu’il est essentiel d’avoir des requêtes de recherche ponctuelle de faible latence (par exemple, recherche de journaux d’enquête, détection de menaces ou d’anomalies, et tableaux de bord critiques avec des filtres sélectifs).

Le service d’optimisation de la recherche réduit la latence des requêtes de recherche ponctuelle en construisant une structure de données persistante optimisée pour un type de recherche particulier.

Vous pouvez activer le service d’optimisation de la recherche pour une table entière ou pour des colonnes spécifiques. Tant qu’elles sont suffisamment sélectives, les recherches d’égalité, les recherches de sous-chaînes <label-search_optimization_service_queries_wildcard_regexp> et les recherches de géométrie <label-search_optimization_service_queries_geo> sur ces colonnes peuvent être accélérées de manière significative.

Le service d’optimisation de la recherche prend en charge les données structurées et les données semi-structurées (voir types de données prises en charge).

Le service d’optimisation de la recherche nécessite Snowflake Enterprise Edition ou une version supérieure. En savoir plus

Vues matérialisées¶

Une vue matérialisée est un ensemble de données précalculé dérivé d’une instruction SELECT qui est stocké en vue d’une utilisation ultérieure. Étant donné que les données sont pré-calculées, l’interrogation d’une vue matérialisée est plus rapide que l’exécution d’une requête sur la table de base de la vue définie. Par exemple, si vous spécifiez SELECT SUM(column1) lors de la création de la vue matérialisée, une requête qui renvoie SUM(column1) à partir de la vue s’exécute plus rapidement car column1 a déjà été agrégé.

Les vues matérialisées sont conçues pour améliorer les performances des requêtes pour les charges de travail composées de modèles de requêtes communs et répétés qui renvoient un petit nombre de lignes et/ou de colonnes relatifs à la table de base.

Une vue matérialisée ne peut pas être basée sur plus d’une table.

Les vues matérialisées nécessitent Snowflake Enterprise Edition ou une version plus récente. En savoir plus

Sélection d’une stratégie d’optimisation¶

Différents types de requêtes bénéficient de différentes stratégies de stockage. Vous pouvez utiliser les sections suivantes pour découvrir la stratégie la mieux adaptée à une charge de travail.

Le clustering automatique est l’option la plus large qui peut bénéficier à une série de requêtes qui accèdent aux mêmes colonnes d’une table. Un administrateur sélectionne souvent les requêtes les plus importantes en fonction des exigences de fréquence et de latence, puis choisit une clé de cluster qui maximise les performances de ces requêtes. Le clustering automatique est utile lorsque de nombreuses requêtes filtrent, joignent ou agrègent les mêmes colonnes.

Le Service d’optimisation de la recherche et les vues matérialisées ont un champ d’application plus restreint. Lorsque des requêtes spécifiques accèdent à un sous-ensemble bien défini des données d’une table, l’administrateur peut utiliser les caractéristiques de la requête pour décider si l’utilisation du service d’optimisation de la recherche ou d’une vue matérialisée peut améliorer les performances. Par exemple, les administrateurs peuvent identifier les requêtes de recherche ponctuelle importantes et mettre en œuvre le service d’optimisation de la recherche pour une table ou une colonne. De même, l’administrateur peut optimiser des schémas d’interrogation spécifiques en créant une vue matérialisée.

Vous pouvez mettre en œuvre plusieurs de ces stratégies pour une table, et une requête individuelle comportant plusieurs filtres peut potentiellement bénéficier à la fois du clustering automatique et du service d’optimisation de la recherche. Cependant, l’activation du service d’optimisation de la recherche ou la création d’une vue matérialisée sur une table en cluster peut s’avérer plus coûteuse. Pour savoir pourquoi cela augmente les coûts de calcul, reportez-vous à Coûts permanents (dans cette rubrique).

Si plusieurs stratégies sont susceptibles d’améliorer les performances d’une requête particulière, il est préférable de commencer par le clustering automatique ou le service d’optimisation de la recherche, car d’autres requêtes présentant des schémas d’accès similaires pourraient également être améliorées.

Différenciation des considérations¶

Ce qui suit n’est pas une comparaison exhaustive des stratégies de stockage, mais plutôt les considérations les plus importantes à prendre en compte pour les différencier.

Clustering automatique:

L’augmentation la plus importante des performances provient d’une clause WHERE qui filtre sur une colonne de la clé de cluster, mais elle peut également améliorer les performances d’autres clauses et fonctions qui agissent sur cette même colonne (par exemple, les jointures et les agrégations).
Idéal pour les requêtes portant sur des intervalles ou les requêtes avec un filtre d’inégalité. Améliore également un filtre d’égalité, mais le service d’optimisation de la recherche est généralement plus rapide pour les requêtes de recherche ponctuelle.
Disponible dans la Standard Edition de Snowflake.
Il ne peut y avoir qu’une seule clé de cluster ^[1] Si différentes requêtes sur une table agissent sur différentes colonnes, envisagez d’utiliser le service d’optimisation de la recherche ou une vue matérialisée à la place.

Service d’optimisation de la recherche:

Améliore les requêtes de recherche ponctuelle qui renvoient un petit nombre de lignes. Si la requête renvoie plus de quelques enregistrements, envisagez plutôt le clustering automatique.
Comprend la prise en charge des requêtes de recherche ponctuelle qui :
- Font correspondre des sous-chaînes ou des expressions régulières à l’aide de prédicats tels que LIKE et RLIKE.
- Recherchent des champs spécifiques dans les colonnes VARIANT, ARRAY ou OBJECT.
- Utilisent des fonctions géospatiales avec des valeurs GEOGRAPHY.

Vue matérialisée:

Améliorent les calculs intensifs et fréquents tels que l’agrégation et l’analyse de données semi-structurées (pas seulement le filtrage).
Généralement axé sur le calcul d’une requête/sous-requête spécifique.
Améliorent les requêtes sur les tables externes.

^[1] S’il existe une raison importante de définir plusieurs clés de cluster, vous pouvez créer plusieurs vues matérialisées, chacune avec sa propre clé de cluster.

Requêtes prototypiques¶

Les exemples suivants visent à mettre en évidence le type de requête qui s’exécute généralement plus rapidement avec une stratégie de stockage donnée.

Requête prototype pour le clustering

Le clustering automatique permet d’améliorer les performances des requêtes de plage avec des balayages de table importants. Par exemple, la requête suivante s’exécutera plus rapidement si la colonne shipdate est la clé de cluster de la table, car la clause WHERE balaie un grand nombre de données.

SELECT
  SUM(quantity) AS sum_qty,
  SUM(extendedprice) AS sum_base_price,
  AVG(quantity) AS avg_qty,
  AVG(extendedprice) AS avg_price,
  COUNT(*) AS count_order
FROM lineitem
WHERE shipdate >= DATEADD(day, -90, to_date('2023-01-01));

Copy

Pour un autre exemple de requête qui pourrait s’exécuter plus rapidement si la table était clusterisée, voir Avantages de la définition de clés de clustering (pour les très grandes tables).

Requête prototype pour l’optimisation de la recherche

Le service d’optimisation de la recherche peut améliorer les performances des requêtes de type ponctuel qui parcourent une grande table pour renvoyer un petit sous-ensemble d’enregistrements. Par exemple, la requête suivante s’exécutera plus rapidement avec le service d’optimisation de la recherche si la colonne sender_ip comporte un grand nombre de valeurs distinctes.

SELECT error_message, receiver_ip
FROM logs
WHERE sender_ip IN ('198.2.2.1', '198.2.2.2');

Copy

Pour examiner d’autres requêtes susceptibles de s’exécuter plus rapidement avec le service d’optimisation de la recherche, reportez-vous aux exemples suivants :

Prototype de requête pour la vue matérialisée

Une vue matérialisée peut améliorer les performances des requêtes qui accèdent à un petit sous-ensemble de données à l’aide d’opérations coûteuses telles que l’agrégation. Supposons par exemple qu’un administrateur ait agrégé la colonne totalprice lors de la création d’une vue matérialisée mv_view1. La requête suivante sur la vue matérialisée s’exécutera plus rapidement que sur la table de base.

SELECT
  orderdate,
  SUM(totalprice)
FROM mv_view1
GROUP BY 1;

Copy

Pour d’autres cas d’utilisation où les vues matérialisées peuvent accélérer les requêtes, voir Exemples de cas d’utilisation pour les vues matérialisées.

Considérations relatives à la mise en œuvre et aux coûts¶

Cette section aborde les considérations de coût liées à l’utilisation d’une stratégie de stockage pour améliorer les performances des requêtes, ainsi que les considérations de mise en œuvre pour équilibrer les coûts et les performances.

Investissement initial¶

La mise en œuvre d’une stratégie de stockage peut nécessiter plus de temps et d’investissements financiers initiaux que d’autres types d’optimisation des performances (par exemple, la réécriture des instructions SQL ou l”optimisation de l’entrepôt qui exécute la requête), mais les améliorations des performances peuvent être significatives.

Snowflake utilise des ressources de calcul sans serveur pour mettre en œuvre chaque stratégie de stockage, ce qui consomme des crédits avant que vous puissiez tester dans quelle mesure l’optimisation améliore les performances. En outre, Snowflake peut prendre beaucoup de temps pour mettre en œuvre le clustering automatique et le service d’optimisation de la recherche (par exemple, une semaine pour une très grande table).

Le service d’optimisation de la recherche et les vues matérialisées nécessitent également l’édition Enterprise Edition ou une version supérieure, ce qui augmente le prix d’un crédit.

Coûts permanents¶

Les stratégies de stockage entraînent des coûts de calcul et de stockage.

Coûts de calcul

Snowflake utilise des ressources de calcul sans serveur pour maintenir les optimisations de stockage à mesure que de nouvelles données sont ajoutées à une table. Plus une table est modifiée, plus les coûts de maintenance sont élevés. Si une table est constamment mise à jour, le coût de l’optimisation du stockage peut être prohibitif.

Le coût de la maintenance des vues matérialisées ou du service d’optimisation de la recherche peut être important lorsque le clustering automatique est activé pour la table sous-jacente. Avec le clustering automatique, Snowflake reclusterise constamment ses micro-partitions autour des dimensions de la clé de cluster. Chaque fois que la table de base est reclusterisée, Snowflake doit utiliser des ressources de calcul sans serveur pour mettre à jour le stockage utilisé par les vues matérialisées et le service d’optimisation de la recherche. Par conséquent, les activités de clustering automatique sur la table de base peuvent entraîner des coûts de maintenance pour les vues matérialisées et le service d’optimisation de la recherche au-delà du coût des commandes DML sur la table de base.

Coûts de stockage

Clustering automatique: Contrairement au service d’optimisation de la recherche et aux vues matérialisées, le clustering automatique réorganise les données existantes au lieu de créer un espace de stockage supplémentaire. Toutefois, le reclustering peut entraîner des coûts de stockage supplémentaires s’il augmente la taille du stockage Fail-safe. Pour plus de détails, reportez-vous à Conséquences du reclustering sur les crédits et le stockage.
Optimisation de la recherche / Vues matérialisées: Les vues matérialisées et le service d’optimisation de la recherche entraînent des coûts de stockage supplémentaires, qui sont facturés au tarif standard.

Estimation des coûts¶

Service d’optimisation de la recherche: Vous pouvez exécuter la fonction SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS pour estimer le coût de l’ajout du service d’optimisation de la recherche à une colonne ou à une table entière. Les coûts estimés sont proportionnels au nombre de colonnes qui seront activées et à l’importance des modifications récentes apportées à la table.

Stratégie de mise en œuvre¶

Les coûts de calcul et de stockage d’une stratégie de stockage pouvant être importants, il est préférable de commencer modestement et de suivre attentivement les coûts initiaux et continus avant de s’engager dans une mise en œuvre plus importante. Par exemple, vous pouvez choisir une clé de cluster pour une ou deux tables seulement, puis évaluer le coût avant de choisir une clé pour d’autres tables.

Lors du suivi des coûts permanents associés à une stratégie de stockage, n’oubliez pas que les entrepôts virtuels ne consomment des crédits que pendant la durée d’exécution d’une requête, de sorte qu’une requête plus rapide coûte moins cher à exécuter. Snowflake recommande de rapporter soigneusement le coût d’exécution d’une requête avant l’optimisation du stockage et de le comparer au coût d’exécution de la même requête après l’optimisation du stockage afin qu’il puisse être pris en compte dans l’évaluation des coûts.