Utilisation de vues matérialisées pour résoudre les problèmes de performances de multi-clustering

Utilisation de vues matérialisées pour résoudre les problèmes de performances de multi-clustering


La récente version des vues matérialisées (MV) de Snowflake fournit une nouvelle fonctionnalité intéressante qui ajoute des améliorations de performances à Snowflake. Les vues matérialisées prennent en charge plusieurs cas d’utilisation différents, y compris les performances. Dans cet article de blog, je vais me concentrer sur l’utilisation de vues matérialisées pour résoudre un problème de performances spécifique qui survient lorsqu’un grand ensemble de données a plusieurs chemins d’accès. L’exemple présenté dans cet article montre une table de 10 milliards de lignes et deux façons différentes d’interroger les données.

La mise en place

Imaginez avoir un blog qui garde une trace de certaines métriques accessibles par différents attributs (identifiants) pour différents cas d’utilisation tels que par horodatage et PAGE_ID. Pour cet exemple, supposons que l’une des métriques suivies dans le tableau du blog est le temps nécessaire pour charger une page en millisecondes (TIME_TO_LOAD_MS).

Pour simplifier la configuration, le tableau ci-dessous illustre l’utilisation de bigint comme type de données pour le identifiants. Dans un cas d’utilisation réel, les types de données peuvent être tout ce qui est pris en charge par Snowflake ; cependant, pour augmenter la taille de l’ensemble de données et rendre le cas d’utilisation plus réaliste, j’ajoute des attributs (métrique2 à métrique9).

CREATE OR REPLACE TABLE WEBLOG (
   CREATE_MS BIGINT,
   PAGE_ID BIGINT,
   TIME_TO_LOAD_MS INTEGER,
   METRIC2 INTEGER,
   METRIC3 INTEGER,
   METRIC4 INTEGER,
   METRIC5 INTEGER,
   METRIC6 INTEGER,
   METRIC7 INTEGER,
   METRIC8 INTEGER,
   METRIC9 INTEGER
 );

Je souhaite calculer le temps de chargement moyen pour deux cas d’utilisation différents. En cas d’utilisation UN), Je vais me concentrer sur la charge moyenne pour une période donnée sur toutes les pages. Pour cas d’utilisation B), Je vais me concentrer sur le temps de chargement moyen pour un particulier Page_ID. Cas d’utilisation UN) est satisfait par une requête directement sur la table marquée WEBLOG. Dans ce cas, la requête revient rapidement car les données sont regroupées naturellement à la date de création. Cas d’utilisation B) nécessite cependant plus de temps, car les données sont filtrées par Page_IDmais il est regroupé par date de création, CREATE_MS.

Le problème : plusieurs chemins d’accès

Pour illustrer le problème, je vais générer un grand ensemble de données (10 milliards de lignes). Les données circulent dans le WEBLOG table séquentiellement en supposant que CREATE_MS est mesurée en millisecondes et les données pour le Page_ID est aléatoire.

INSERT INTO WEBLOG
SELECT
    (SEQ8())::BIGINT AS CREATE_MS
    ,UNIFORM(1,9999999,RANDOM(10002))::BIGINT PAGE_ID
    ,UNIFORM(1,9999999,RANDOM(10003))::INTEGER TIME_ON_LOAD_MS
    ,UNIFORM(1,9999999,RANDOM(10005))::INTEGER METRIC2
    ,UNIFORM(1,9999999,RANDOM(10006))::INTEGER METRIC3
    ,UNIFORM(1,9999999,RANDOM(10007))::INTEGER METRIC4
    ,UNIFORM(1,9999999,RANDOM(10008))::INTEGER METRIC5
    ,UNIFORM(1,9999999,RANDOM(10009))::INTEGER METRIC6
    ,UNIFORM(1,9999999,RANDOM(10010))::INTEGER METRIC7
    ,UNIFORM(1,9999999,RANDOM(10011))::INTEGER METRIC8
    ,UNIFORM(1,9999999,RANDOM(10012))::INTEGER METRIC9
FROM TABLE(GENERATOR(ROWCOUNT => 10000000000))
ORDER BY CREATE_ms;

Un indicateur clé pour estimer les performances dans Snowflake est la profondeur de clustering pour l’attribut de filtre. Pour récupérer la profondeur de clustering d’un attribut sur une table, spécifiez d’abord l’attribut de clustering avec un MODIFIER TABLE déclaration.

ALTER TABLE WEBLOG CLUSTER BY (CREATE_MS);

SELECT SYSTEM$CLUSTERING_INFORMATION( 'WEBLOG' , '(CREATE_MS)' );
SELECT SYSTEM$CLUSTERING_INFORMATION( 'WEBLOG' , '(PAGE_ID)' );

Profondeur de clustering pour l’attribut CREATE_MS est une bonne option car elle offre une petite valeur. Dans ce cas c’est ~1mais c’est sous-optimal (une grande valeur) pour PAGE_ID. Cependant, parce que j’ai créé les données triées par CREATE_MS, c’est un résultat attendu. D’autre part, PAGE_ID est distribué aléatoirement.

Exécution d’une requête sur WEBLOG confirme le problème. Pour rendre le problème facilement visible, il faut exécuter un entrepôt relativement petit pour un ensemble de données de 10 milliards de lignes. Les chiffres ci-dessous sont basés sur un MOYEN groupe.

SELECT COUNT(*) CNT, AVG(TIME_TO_LOAD_MS) AVG_TIME_TO_LOAD
FROM WEBLOG
WHERE CREATE_MS BETWEEN 1000000000 AND 1000001000;
SELECT COUNT(*) CNT, AVG(TIME_TO_LOAD_MS)  AVG_TIME_TO_LOAD
FROM WEBLOG
WHERE PAGE_ID=100000;

Les deux requêtes résument environ 1 000 lignes. La première requête s’exécute en 0,1 seconde environ et la seconde nécessite environ 100 secondes (en supposant un cache de données à froid). La différence substantielle se produit parce que la première requête n’examine qu’une seule micropartition, tandis que la seconde requête en analyse environ 21 000.

L’exécution des deux requêtes avec des performances égales nécessite l’utilisation d’une deuxième copie des données qui est organisée différemment, optimisant ainsi l’accès pour les deux modèles de requête.

La solution : vue matérialisée en cluster

La solution au problème réside dans deux nouvelles fonctionnalités de Snowflake : les vues matérialisées et le clustering automatique.

La création de la vue matérialisée avec Snowflake vous permet de spécifier la nouvelle clé de clustering, ce qui permet à Snowflake de réorganiser les données lors de la création initiale de la vue matérialisée.

Pour une table source d’environ 10 milliards de lignes, un MOYEN-entrepôt de taille prend environ 20 minutes pour créer la vue matérialisée. Pour augmenter les performances et réduire le temps de création de la date de test, j’ai utilisé une autre fonctionnalité de Snowflake, qui est la possibilité de redimensionner élastiquement l’entrepôt. Lors de l’exécution du code ci-dessous, vous devez modifier l’espace réservé avec le nom de votre propre entrepôt.

L’utilisation de l’exemple ci-dessous réduit le temps de construction initial de la vue matérialisée, ce qui la rend 16 fois plus rapide que l’exécution de la même instruction sur un MOYEN-grappe de taille. Lors de l’exécution du code ci-dessous, remplacez l’espace réservé par le nom de votre entrepôt.

ALTER WAREHOUSE <NAME> SET WAREHOUSE_SIZE=XXXLARGE;

CREATE OR REPLACE MATERIALIZED VIEW MV_TIME_TO_LOAD 
      (CREATE_MS, PAGE_ID, TIME_TO_LOAD_MS) CLUSTER BY (PAGE_ID)
   AS
      SELECT CREATE_MS, PAGE_ID, TIME_TO_LOAD_MS
      FROM WEBLOG; 

ALTER WAREHOUSE <NAME> SET WAREHOUSE_SIZE=MEDIUM;

Une fois la construction de la vue matérialisée terminée, validez la distribution optimale des données en récupérant les informations de clustering.

SELECT SYSTEM$CLUSTERING_INFORMATION 
   ( 'MV_TIME_TO_LOAD' , '(PAGE_ID)' );

La profondeur de regroupement, qui devrait être ~2indique qu’une requête sur la vue matérialisée par PAGE_ID devrait être beaucoup plus rapide. Combien plus rapide?

Pour obtenir une ligne de base, j’ai d’abord exécuté la requête sur la table de base. Pour obtenir des résultats plus reproductibles, il faut limiter l’impact de la mise en cache des résultats de requête. La mise en cache des résultats de requête est une autre fonctionnalité intéressante de Snowflake. cependant, lorsque vous comparez les performances, vous souhaitez comparer des pommes avec des pommes. Pour cette raison, j’ai désactivé la fonctionnalité de résultats mis en cache.

ALTER SESSION SET USE_CACHED_RESULT=FALSE;

SELECT COUNT(*),AVG(TIME_TO_LOAD_MS)
FROM WEBLOG
WHERE PAGE_ID=100000;

La deuxième requête ne prend pas les 100 secondes environ qu’elle a prises lors de l’exécution de la requête pour la première fois. Cette fois, cela prend plus près de 20 secondes. Pourquoi? Parce que vous voyez maintenant l’impact de Snowflake fournissant à la fois la mise en cache des résultats et la mise en cache des micropartitions sur le cluster d’exécution. Si c’était la première fois que vous exécutiez cette requête et que le cache du cluster était froid, cela nécessiterait encore environ 100 secondes.

Maintenant, vérifions le runtime pour la même requête par rapport à la vue matérialisée. Les tests montrent que les résultats prennent environ 0,1 seconde, ce qui est similaire à la recherche par CREATE_MS contre la table basse.

ALTER SESSION SET USE_CACHED_RESULT=FALSE;

SELECT COUNT(*),AVG(TIME_TO_LOAD_MS)  AVG_TIME_TO_LOAD
FROM MV_TIME_TO_LOAD
WHERE PAGE_ID=100000;

C’est une amélioration substantielle. Cependant, le scénario ci-dessus, en lui-même, n’est pas si impressionnant, car la création d’une nouvelle version matérialisée de la table de base via une instruction Create Table AS (CTAS) accomplit la même chose. Alors, que se passe-t-il lorsque les données de la table de base changent ? Les données de la version CTAS deviennent immédiatement obsolètes.

Mettre tous ensemble

Le véritable pouvoir des vues matérialisées vient du fait que toutes LMD changements (INSÉRER, METTRE À JOUR, SUPPRIMER) à la table de base (WEBLOG) sont automatiquement appliqués à la vue matérialisée. Non seulement les modifications sont appliquées, mais elles le sont de manière cohérente sur le plan transactionnel. Désormais, à tout moment, les données de la vue matérialisée correspondront aux données de la requête de vue lors de son application à la table source. Enfin, le fait d’avoir une vue matérialisée au-dessus d’une table source n’affecte pas la LMD performances par rapport à la table source.

Pour simuler l’activité par rapport à la table source, j’ai écrit un petit programme Python qui insère des lots de lignes avec un nombre configurable de secondes entre les lots.

import snowflake.connector
import time
import sys

def getSnowFlakeCntxt():
    # Set SnowFlake context.
    SnowFlakectx = snowflake.connector.connect(
        user=<user>,
        password=<password>,
        account=<account>
        warehouse=<warehouse>,
        database=<database>,
        schema=<schema>
)
return SnowFlakectx

def executeSnowflakeCmd(iteration,batch,seed):
    conn = getSnowFlakeCntxt()
    cs = conn.cursor()
    try:
        cs.execute("alter session set QUERY_TAG = %s", ("MV_INSERT_TEST"))
        insSQL = """
           insert into WEBLOG
           select
              ((seq8())+""" + str(iteration*batch+seed) +""")::bigint as create_ms
              ,uniform(1,9999999,random(10002))::bigint page_id
              ,uniform(1,9999999,random(10003))::integer time_to_load_ms
              ,uniform(1,9999999,random(10005))::integer metric2
              ,uniform(1,9999999,random(10006))::integer metric3
              ,uniform(1,9999999,random(10007))::integer metric4
              ,uniform(1,9999999,random(10008))::integer metric5
              ,uniform(1,9999999,random(10009))::integer metric6
              ,uniform(1,9999999,random(10010))::integer metric7
              ,uniform(1,9999999,random(10011))::integer metric8
              ,uniform(1,9999999,random(10012))::integer metric9
           from table(generator(rowcount => """ +str(batch) + """))
           order by create_ms
        """
        cs.execute(insSQL)
    finally:
        cs.close()


if __name__ == "__main__":
    iterations=int(sys.argv[1])
    batch_size=int(sys.argv[2])
    sleep_time=int(sys.argv[3])
    seed=int(sys.argv[4])
    for i in range(0,iterations):
        executeSnowflakeCmd(i,batch_size,seed)
        time.sleep(sleep_time)
    print("***** COMPLETED ******")

L’instruction ci-dessous appelle le script avec 20 itérations et chaque itération insère 600 000 lignes, avec 60 secondes entre les lots. Le dernier paramètre du script est une valeur de départ pour créer de nouvelles lignes avec un IDENTIFIANT supérieure aux rangées existantes.

 python TableInsert.py 20 600000 60 100000000 

Avec chaque lot ajouté dans la table source (BLOGUE), les modifications apportées aux données renvoyées par la requête ci-dessus sont visibles.

SELECT COUNT(*),AVG(TIME_TO_LOAD_MS)
FROM MV_TIME_ON_SITE
WHERE PAGE_ID=100000;

Pour prouver que la vue matérialisée renvoie les mêmes résultats qu’une requête sur la table source, exécutons l’instruction ci-dessous qui affichera le résultat de la table source et du MV à la fois.

SELECT 'WEBLOG', COUNT(*),AVG(TIME_TO_LOAD_MS)
FROM WEBLOG
WHERE PAGE_ID=100000
UNION ALL 
SELECT 'MV_TIME_TO_LOAD',COUNT(*),AVG(TIME_TO_LOAD_MS)
FROM MV_TIME_TO_LOAD
WHERE PAGE_ID=100000;

Conclusion

L’utilisation de vues matérialisées résout un problème de performances de base qui se produit lors de l’accès aux données d’une grande table via différents ensembles d’attributs. Les améliorations sont exponentielles et ne nécessitent pas de consacrer du temps à la création de tâches de maintenance supplémentaires, notamment MV rafraîchir les emplois.

Il existe de nombreux autres cas d’utilisation viables pour les vues matérialisées, y compris les projections et les sélections de vues matérialisées qui permettent de filtrer fréquemment les sous-ensembles interrogés à partir d’une grande table source. La prise en charge des agrégations de table unique peut également remplacer les processus manuels de calcul des agrégations pour un ou plusieurs niveaux. Les vues matérialisées prennent même en charge des fonctions populaires telles que APLATIR ou ÉCHANTILLONNAGE. Lgagner plus sur vues matérialisées et le Regroupement automatique de flocons de neige service.

abonnez-vous au blog des flocons de neige

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.