Données d’échantillon : TPC-DS

TPC-DS est un benchmark qui modélise le système d’aide à la décision d’un fournisseur de produits de détail. Il contient des données sur les clients, les commandes et les produits. Snowflake fournit des versions 10TB et 100TB des données TPC-DS que vous pouvez explorer, dans des schémas nommés respectivement TPCDS_SF10TCL et TPCDS_SF100TCL, au sein de la base de données partagée SNOWFLAKE_SAMPLE_DATA.

Comme décrit dans la spécification TPC Benchmark™ DS (TPC-DS) :

« Afin de tenir compte de l’énorme éventail de types de requêtes et de comportements d’utilisateur rencontrés par un système d’aide à la décision, TPC-DS utilise un modèle de requête généralisé. Ce modèle permet au benchmark de capturer des aspects importants de la nature interactive et itérative des requêtes de traitement analytique de ligne (OLAP), des requêtes complexes de longue durée de l’exploration de données et de la découverte de connaissances, et du comportement planifié des requêtes de rapport bien connues ».

Dans ce chapitre :

Comment ajouter l’ensemble des données TPC-DS à mon compte ?

Vous pouvez accéder aux ensembles de données TPC-DS qui sont fournis par Snowflake directement à partir de Snowflake Marketplace dans Snowsight. Voir Accès aux données TPC-DS à partir de Snowflake Marketplace. Vous pouvez également accéder à la liste des requêtes TPC-DS en téléchargeant ce script.

Entités, relations et caractéristiques de base de données

L’ensemble des données TPC-DS se compose de 7 tables de faits et de 17 dimensions dans les schémas suivants :

  • TPCDS_SF100TCL : la version 100 TB (facteur d’échelle 100 000) représente 100 millions de clients et plus de 500 000 éléments stockés, avec des données de ventes couvrant trois canaux (magasins, catalogues et Internet) sur une période de cinq ans. La plus grande table, STORE_SALES, contient près de 300 milliards de lignes, et les tables de faits contiennent plus de 560 milliards de lignes au total.

  • TPCDS_SF10TCL : la version 10 TB (facteur d’échelle 10 000) représente 65 millions de clients et plus de 400 000 éléments stockés, avec des données de ventes couvrant trois canaux (magasins, catalogues, et Internet) sur une période de cinq ans. La plus grande table, STORE_SALES, contient près de 29 milliards de lignes, et les tables de faits contiennent plus de 56 milliards de lignes au total.

Les relations entre les faits et les dimensions sont représentées par des liaisons sur des clés de substitution. Les relations détaillées sont trop nombreuses pour être affichées ici, mais peuvent être trouvées dans les spécifications TPC-DS.

Définitions de requête

TPC-DS contient un ensemble de 99 requêtes dont la complexité et l’étendue des données numérisées varient considérablement. Chaque requête TPC-DS pose une question métier et inclut la requête correspondante pour y répondre. Nous avons généré des échantillons de toutes les 99 requêtes TPC-DS que vous pouvez explorer. Vous pouvez également utiliser les outils du kit de benchmark TPC-DS pour générer de nombreuses versions différentes de ces requêtes qui varient en fonction des valeurs de paramètre.

Ci-dessous, nous ne décrivons qu’une seule de ces requêtes. Vous pouvez trouver plus d’informations sur TPC-DS et toutes les requêtes impliquées dans les spécifications officielles TPC-DS.

Le script TPC-DS, fourni par Snowflake, contient la liste complète des requêtes TPC-DS. Vous pouvez enregistrer le fichier dans votre système de fichiers local pour référence.

Un exemple : Valeurs aberrantes du centre d’appel sur les ventes de catalogue (Q57)

Cette requête examine les données de la table CATALOG_SALES d’une année et révèle les catégories et les marques dont les ventes mensuelles varient de plus de 10 % par rapport à la moyenne pour un centre d’appels donné.

Question métier

Trouvez les marques et catégories d’article pour chaque centre d’appels et leurs chiffres de ventes mensuels pour une année donnée où le chiffre d’affaires mensuel s’écarte de plus de 10% du chiffre d’affaires mensuel moyen de l’année, classées par écart et centre d’appels. Faites un rapport sur l’écart de ventes par rapport au mois précédent et au mois suivant.

Définition de la requête fonctionnelle

La requête répertorie les totaux suivants :

  • Prix étendu

  • Prix étendu réduit

  • Prix étendu réduit plus taxe

  • Quantité moyenne

  • Prix étendu moyen

  • Remise moyenne

Ces agrégats sont regroupés par RETURNFLAG et LINESTATUS, et sont répertoriés par ordre croissant de RETURNFLAG et LINESTATUS. Un décompte du nombre d’éléments de ligne dans chaque groupe est inclus :

use schema snowflake_sample_data.tpcds_sf10Tcl;

-- QID=TPC-DS_query57

with
v1 as(
  select i_category, i_brand,
         cc_name,
         d_year, d_moy,
         sum(cs_sales_price) sum_sales,
         avg(sum(cs_sales_price)) over
           (partition by i_category, i_brand, cc_name, d_year) avg_monthly_sales,
         rank() over (partition by i_category, i_brand, cc_name order by d_year, d_moy) rn
  from item, catalog_sales, date_dim, call_center
  where cs_item_sk = i_item_sk and
        cs_sold_date_sk = d_date_sk and
        cc_call_center_sk= cs_call_center_sk and
          (
            d_year = 2001 or
          ( d_year = 2001-1 and d_moy =12) or
          ( d_year = 2001+1 and d_moy =1)
          )
  group by i_category, i_brand,
           cc_name , d_year, d_moy),
v2 as(
  select v1.i_brand
    ,v1.d_year, v1.d_moy
    ,v1.avg_monthly_sales
    ,v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum
  from v1, v1 v1_lag, v1 v1_lead
  where v1.i_category = v1_lag.i_category and
    v1.i_category = v1_lead.i_category and
    v1.i_brand = v1_lag.i_brand and
    v1.i_brand = v1_lead.i_brand and
    v1.cc_name = v1_lag. cc_name and
    v1.cc_name = v1_lead. cc_name and
    v1.rn = v1_lag.rn + 1 and
    v1.rn = v1_lead.rn - 1)
select  *
from v2
where d_year = 2001 and
  avg_monthly_sales > 0 and
  case when avg_monthly_sales > 0
       then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales
       else null
       end > 0.1
order by sum_sales - avg_monthly_sales, nsum
limit 100;
Copy

Accès aux données TPC-DS à partir de Snowflake Marketplace

Vous pouvez accéder directement aux données TPC-DS en allant sur Snowflake Marketplace dans Snowsight. Vous pouvez créer et demander votre propre instance des ensembles de données suivants :

Les données de l’ensemble des données Iceberg gérées sont physiquement stockées dans le format Iceberg, plutôt que dans le format de table propriétaire de Snowflake. Vous pouvez obtenir les deux ensembles de données et comparer le comportement des deux formats.

Pour obtenir ces ensembles de données :

  1. Recherche de TPC-DS dans Snowflake Marketplace. (Connectez-vous à Snowsight si vous y êtes invité)

  2. Sélectionnez l’un des ensembles de données TPC-DS.

  3. Sélectionnez Get.

    Demandez l’accès à votre administrateur si nécessaire. Il se peut que votre rôle de connexion n’ait pas accès à ces ensembles de données.

  4. Sous Options, attribuez un nom défini par l’utilisateur à votre base de données TPC-DS et sélectionnez le rôle que vous utiliserez pour y accéder. Vous pouvez également utiliser les sélections par défaut.

  5. Sélectionnez Get it for Free.

    En quelques secondes, vous devez voir la fenêtre contextuelle suivante, qui indique que votre instance de la base de données TPC-DS a été créée et peut être consultée et interrogée.

    Fenêtre contextuelle indiquant que l'ensemble de données TPC-DS a été écrit dans une base de données que vous pouvez interroger.

  6. Sélectionnez Query Data.

  7. Interrogez les données de la base de données, en utilisant soit la feuille de calcul fournie, soit le script TPC-DS, qui contient toutes les requêtes.

Si vous avez déjà utilisé Get pour créer une de ces bases de données, vous pouvez y accéder en sélectionnant Open dans les résultats de la recherche du Marketplace.

Langage: Français