Simplifiez les requêtes de traitement analytique en ligne (OLAP) dans Amazon Redshift à l’aide des nouvelles instructions SQL : ROLLUP, CUBE et GROUPING SETS

Amazon Redshift est un entrepôt de données massivement parallèle entièrement géré à l’échelle du pétaoctet qui permet d’analyser rapidement, simplement et de manière économique toutes vos données à l’aide de SQL standard et de vos outils de business intelligence (BI) existants.

Nous investissons en permanence pour faciliter l’analyse avec Redshift en simplifiant les instructions. SQL et en ajoutant de nouveaux opérateurs. Nous ajoutons désormais les extensions d’agrégation SQL ROLLUP, CUBE et GROUPING SETS pour effectuer plusieurs opérations d’agrégation dans une seule instruction et inclure facilement des sous-totaux, des totaux et des collections de sous-totaux dans une requête.

Dans ce blog post, nous allons expliquer comment utiliser ces extensions pour simplifier vos requêtes dans Amazon Redshift.

Présentation de la solution

Le traitement analytique en ligne (OLAP) est un outil efficace pour les analystes de données et les analystes fonctionnels d’aujourd’hui. Il vous aide à visualiser vos indicateurs stratégiques à différents niveaux d’agrégation dans un seul écran. Un analyste peut utiliser les agrégations OLAP pour analyser les tendances d’achat en regroupant les clients par démographiques, géographiques et psychographiques, puis résumer les données pour rechercher des tendances. Cela peut inclure l’analyse de la fréquence des achats, des délais entre les achats et des types d’articles achetés. Une telle analyse peut fournir un aperçu des préférences et du comportement des clients, qui peut être utilisé pour éclairer les stratégies marketing et le développement de produits. Par exemple, un analyste de données peut interroger les données pour afficher une feuille de calcul montrant les ventes d’un certain type de produits de l’entreprise aux États-Unis au mois de juillet, comparer les chiffres de revenus avec ceux des mêmes produits en septembre, puis voir une comparaison des ventes d’autres produits aux États-Unis pendant la même période.

Traditionnellement, les analystes fonctionnels et les analystes de données utilisent un ensemble de requêtes SQL UNION pour atteindre le niveau de détail et les regroupements souhaités. Cependant, cela peut être très long et fastidieux à écrire et à maintenir. De plus, le niveau de détail et les regroupements qui peuvent être atteints avec cette approche sont limités, car l’utilisateur doit écrire plusieurs requêtes pour chaque niveau de détail et regroupement différent.

De nombreux clients envisagent de migrer vers Amazon Redshift depuis d’autres systèmes d’entrepôt de données qui prennent en charge les clauses GROUP BY d’OLAP. Pour rendre ce processus de migration aussi transparent que possible, Amazon Redshift prend désormais en charge ROLLUP, CUBE et GROUPING SETS. Cela permettra une migration plus fluide des charges de travail OLAP, avec un minimum de réécriture. Enfin, cela se traduira par une transition plus rapide et rationalisée vers Amazon Redshift. Les analystes fonctionnels et les analystes de données peuvent désormais écrire une seule requête SQL pour remplacer plusieurs requêtes UNION.

Dans les sections suivantes, nous allons utiliser des données sur les soldes des comptes fournisseurs du jeu de données TPC-H comme exemple pour démontrer l’utilisation des extensions ROLLUP, CUBE et GROUPING SETS. TPC-H est un outil de référence pour l’aide à la décision. Il comprend une série de requêtes ad hoc axées sur les besoins des entreprises, ainsi que des modifications simultanées des données. Les requêtes et les données utilisées pour alimenter la base ont été sélectionnées avec soin pour assurer leur pertinence dans l’ensemble du secteur. Ce jeu de données comprend les soldes des comptes fournisseurs dans différentes régions et pays. Nous démontrons comment trouver les sous-totaux et les totaux généraux des soldes des comptes à chaque niveau national, régional et une combinaison des deux. Toutes ces questions analytiques peuvent être traitées par un utilisateur métier en exécutant de simples instructions SQL en une seule ligne. Parallèlement aux agrégations, ce billet démontre également comment les résultats peuvent être retracés jusqu’aux attributs ayant participé à la génération des sous-totaux.

Préparation des données

Pour configurer le cas d’utilisation, suivez les étapes suivantes :

1. Allez sur la console Amazon Redshift, puis dans le volet de navigation et choisissez Éditeur de requêtes v2.

L’éditeur de requêtes v2 s’ouvre dans un nouvel onglet du navigateur.

2. Créez une table d’exemple de fournisseurs et insérez des données échantillons :

create table supp_sample (supp_id integer, region_nm char(25), nation_nm char(25), acct_balance numeric(12,2));


INSERT INTO public.supp_sample (supp_id,region_nm,nation_nm,acct_balance)
VALUES
(90470,'AFRICA                   ','KENYA                    ',1745.57),
(99910,'AFRICA                   ','ALGERIA                  ',3659.98),
(26398,'AMERICA                  ','UNITED STATES            ',2575.77),
(43908,'AMERICA                  ','CANADA                   ',1428.27),
(3882,'AMERICA                  ','UNITED STATES            ',7932.67),
(42168,'ASIA                     ','JAPAN                    ',343.34),
(68461,'ASIA                     ','CHINA                    ',2216.11),
(89676,'ASIA                     ','INDIA                    ',4160.75),
(52670,'EUROPE                   ','RUSSIA                   ',2469.40),
(32190,'EUROPE                   ','RUSSIA                   ',1119.55),
(19587,'EUROPE                   ','GERMANY                  ',9904.98),
(1134,'MIDDLE EAST              ','EGYPT                    ',7977.48),
(35213,'MIDDLE EAST              ','EGYPT                    ',737.28),
(36132,'MIDDLE EAST              ','JORDAN                   ',5052.87);

Nous avons pris un échantillon du résultat de la requête suivante exécutée sur le jeu de données TPC-H. Vous pouvez utiliser la requête suivante et prendre des exemples d’enregistrements pour essayer l’instruction SQL décrite dans ce billet :

select s_suppkey supp_id, r.r_name region_nm,n.n_name nation_nm, s.s_acctbal acct_balance
from supplier s, nation n, region r
where
s.s_nationkey = n.n_nationkey
and n.n_regionkey = r.r_regionkey;

Examinons les données d’exemple avant d’exécuter les instructions SQL à l’aide des extensions GROUPING SETS, ROLLUP et CUBE.

La table supp_sample comprend les soldes des comptes fournisseurs de diverses nations et régions du monde. Voici les définitions des attributs :

• supp_id – L’identifiant unique de chaque fournisseur
• region_nm – La région dans laquelle le fournisseur opère
• nation_nm – La nation dans laquelle le fournisseur opère
• acct_balance – Le solde du compte fournisseur en cours

GROUPING SETS

GROUPING SETS est une extension d’agrégation SQL pour regrouper les résultats d’une requête par une ou plusieurs colonnes dans une seule instruction. Vous pouvez utiliser GROUPING SETS au lieu d’effectuer plusieurs requêtes SELECT avec différentes clés GROUP BY et fusionner (UNION) leurs résultats.

Dans cette section, nous montrons comment trouver :

• Les soldes des comptes agrégés pour chaque région
• Les soldes des comptes agrégés pour chaque nation
• Les résultats fusionnés des deux agrégations

Exécutez l’instruction SQL suivante à l’aide de GROUPING SETS :

SELECT region_nm, nation_nm, sum(acct_balance) as total_balance
FROM supp_sample
GROUP BY GROUPING SETS (region_nm, nation_nm);

Comme le montre la capture d’écran suivante, l’ensemble de résultats comprend les soldes des comptes agrégés par region_nm, suivis de nation_nm, puis les deux résultats combinés dans une seule sortie.

ROLLUP

La fonction ROLLUP génère des résultats agrégés à plusieurs niveaux de regroupement, en commençant par le niveau le plus détaillé, puis en agrégeant jusqu’au niveau suivant. Elle regroupe les données par des colonnes particulières et des lignes supplémentaires qui représentent les sous-totaux, et suppose une hiérarchie entre les colonnes GROUP BY.

Dans cette section, nous montrons comment trouver ce qui suit :

• Les soldes des comptes pour chaque combinaison de region_nm et nation_nm
• Les soldes des comptes cumulés pour chaque region_nm
• Les soldes des comptes cumulés pour toutes les régions

Utilisez l’instruction SQL suivante avec ROLLUP :

SELECT region_nm, nation_nm, sum(acct_balance) as total_balance
FROM supp_sample
GROUP BY ROLLUP (region_nm, nation_nm)
ORDER BY region_nm,nation_nm;

Le résultat suivant montre les valeurs cumulées à partir de chaque combinaison de region_nm et nation_nm et se cumule dans la hiérarchie de nation_nm à region_nm. Les lignes avec une valeur pour region_nm et une valeur NULL pour nation_nm représentent les sous-totaux pour la région (marqués en vert). Les lignes avec une valeur NULL pour region_nm et nation_nm ont le total général – les soldes des comptes cumulés pour toutes les régions (marqués en rouge).

ROLLUP est structurellement équivalent à la requête GROUPING SETS suivante :

SELECT region_nm, nation_nm, sum(acct_balance) as total_balance
FROM supp_sample
GROUP BY GROUPING SETS((region_nm, nation_nm), (region_nm), ())
ORDER BY region_nm,nation_nm;

Vous pouvez réécrire la requête ROLLUP précédente en utilisant GROUPING SETS. Cependant, l’utilisation de ROLLUP est une instruction beaucoup plus simple et lisible pour ce cas d’utilisation.

CUBE

CUBE regroupe les données par les colonnes fournies, en renvoyant des lignes de sous-totaux supplémentaires représentant les totaux à tous les niveaux des colonnes de regroupement, en plus des lignes regroupées. CUBE renvoie les mêmes lignes que ROLLUP, tout en ajoutant des lignes de sous-totaux supplémentaires pour chaque combinaison de colonne de regroupement non couverte par ROLLUP.

Dans cette section, nous montrons comment trouver ce qui suit :

• Les sous-totaux des soldes des comptes pour chaque nation_nm
• Les sous-totaux des soldes des comptes pour chaque region_nm
• Les sous-totaux des soldes des comptes pour chaque groupe de combinaison region_nm et nation_nm
• Le solde total des comptes pour toutes les régions

Exécutez l’instruction SQL suivante avec CUBE :

SELECT region_nm, nation_nm, sum(acct_balance) as total_balance
FROM supp_sample
WHERE region_nm in ('AFRICA','AMERICA','ASIA') GROUP BY CUBE(region_nm, nation_nm)
ORDER BY region_nm, nation_nm;

Dans la requête précédente, nous avons ajouté un filtre pour limiter les résultats afin de faciliter l’explication. Vous pouvez supprimer ce filtre dans votre test pour afficher les données de toutes les régions.

Dans les jeux de résultats suivants, vous pouvez voir les sous-totaux au niveau de la région (marqués en vert). Ces enregistrements de sous-totaux sont les mêmes enregistrements générés par ROLLUP. De plus, CUBE a généré des sous-totaux pour chaque nation_nm (marqués en jaune). Enfin, vous pouvez également voir le total général pour les trois régions mentionnées dans la requête (marqué en rouge).

CUBE est structurellement équivalent à la requête GROUPING SETS suivante :

SELECT region_nm, nation_nm, sum(acct_balance) as total_balance
FROM supp_sample
WHERE region_nm in ('AFRICA','AMERICA','ASIA') -- added the filter to limit results.  You can remove this filter in your test to view data for all regions
GROUP BY GROUPING SETS((region_nm, nation_nm), (region_nm), (nation_nm), ())
ORDER BY region_nm;

Vous pouvez réécrire la requête CUBE précédente en utilisant GROUPING SETS. Cependant, l’utilisation de CUBE est une instruction beaucoup plus simple et lisible pour cette utilisation.

Valeurs NULL

NULL est une valeur valide dans une colonne qui participe à GROUPING SETS, ROLLUP et CUBE, et elle n’est pas agrégée avec les valeurs NULL ajoutées explicitement à l’ensemble de résultats pour satisfaire le schéma du renvoi de tuples.

Créons un exemple de table orders contenant des détails sur les articles commandés, les descriptions des articles et la quantité des articles :

-- Créer une table d’exemple orders and y insérer des données
CREATE TABLE orders(item_no int,description varchar,quantity int);
INSERT INTO orders(item_no,description,quantity)
VALUES
(101,'apples',10),
(102,null,15),
(103,'banana',20);

--Visualiser les données 
SELECT * FROM orders;

Nous utilisons la requête ROLLUP suivante pour agréger les quantités par item_no et description :

SELECT item_no, description, sum(quantity)
FROM orders
GROUP BY ROLLUP(item_no, description)
ORDER BY 1,2;

Dans le résultat suivant, il y a deux lignes de sortie pour item_no 102. La ligne marquée en vert est la donnée d’entrée, et la ligne marquée en rouge est le sous-total ajouté par la fonction ROLLUP.

Cela démontre que les valeurs NULL dans l’entrée sont distinctes des valeurs NULL ajoutées par les extensions d’agrégation SQL.

Fonctions Grouping et Grouping_ID

GROUPING indique si une colonne de la liste GROUP BY est agrégée ou non. GROUPING(expr) renvoie 0 si un tuple est regroupé sur expr ; sinon, il renvoie 1. GROUPING_ID(expr1, expr2, … , exprN) renvoie une représentation entière du bitmap composé de GROUPING(expr1), GROUPING(expr2), …, GROUPING(exprN).

Cette fonctionnalité nous aide à comprendre clairement le grain d’agrégation, à découper et analyser les données, et à appliquer des filtres lorsque les utilisateurs métier effectuent une analyse. Elle fournit également une traçabilité pour les agrégations générées.

Par exemple, utilisons la table supp_sample précédente. La requête ROLLUP suivante utilise les fonctions GROUPING et GROUPING_ID :

SELECT region_nm,
nation_nm,
sum(acct_balance) as total_balance,
GROUPING(region_nm) as gr,
GROUPING(nation_nm) as gn,
GROUPING_ID(region_nm, nation_nm) as grn
FROM supp_sample
GROUP BY ROLLUP(region_nm, nation_nm)
ORDER BY region_nm;

Dans l’ensemble de résultats suivant, les lignes regroupées au niveau nation_nm ont 1 valeur pour gn. Cela indique que le total_balance est la valeur agrégée pour toutes les valeurs nation_nm dans la région. La dernière ligne a une valeur gr de 1. Cela indique que total_balance est une valeur agrégée au niveau de la région, incluant toutes les nations. Le grn est une représentation entière du bitmap (11 en binaire traduit en 3 en représentation entière).

Évaluation des performances

Les performances sont souvent un facteur clé, et nous voulions nous assurer d’offrir les fonctionnalités SQL les plus performantes dans Amazon Redshift. Nous avons effectué des tests de performance avec le jeu de données publique TPC-H de 3 To sur un cluster Amazon Redshift de différentes tailles (5 nœuds Ra3-4XL, 2 nœuds Ra3-4XL, 2 nœuds Ra3-XLPLUS). De plus, nous avons désactivé la mise en cache des requêtes afin que les résultats des requêtes ne soient pas mis en cache. Cela nous permet de mesurer les performances de la base de données plutôt que sa capacité à servir des résultats depuis le cache. Les résultats ont été cohérents sur plusieurs exécutions.

Nous avons chargé les fichiers fournisseur, région et nation du jeu de données publique de 3 To et créé une vue sur ces trois tables, comme indiqué dans le code suivant. Cette requête joint les trois tables pour créer un enregistrement unifié. L’ensemble de données joint est utilisé pour l’évaluation des performances.

CREATE VIEW v_supplier_balances as
SELECT r.r_name region_nm,n.n_name nation_nm, s.s_acctbal acct_balance
FROM supplier s, nation n, region r
WHERE
s.s_nationkey = n.n_nationkey
and n.n_regionkey = r.r_regionkey;

Nous avons exécuté les requêtes SELECT d’exemple suivantes en utilisant GROUPING SETS, CUBE et ROLLUP, et capturé les métriques de performance dans les tableaux suivants.
ROLLUP :

SELECT region_nm, nation_nm, sum(acct_balance) as total_balance
FROM v_supplier_balances
GROUP BY ROLLUP (region_nm, nation_nm)
ORDER BY region_nm;

CUBE :

SELECT region_nm, nation_nm, sum(acct_balance) as total_balance
FROM v_supplier_balances
GROUP BY CUBE(region_nm, nation_nm)
ORDER BY region_nm;

GROUPING SETS :

SELECT region_nm, nation_nm, sum(acct_balance) as total_balance
FROM v_supplier_balances
GROUP BY GROUPING SETS(region_nm, nation_nm)
ORDER BY region_nm;

Lorsque nous avons exécuté le même ensemble de requêtes pour ROLLUP et CUBE et exécuté avec UNION ALL, nous avons constaté de meilleures performances avec les fonctionnalités ROLLUP et CUBE.

Nettoyage

Pour nettoyer vos ressources, supprimez les tables et vues que vous avez créées en suivant l’exemple de ce billet.

Conclusion

Dans ce blog post, nous avons parlé des nouvelles extensions d’agrégation ROLLUP, CUBE et GROUPING SETS ajoutées à Amazon Redshift. Nous avons également abordé les cas d’utilisation généraux, les exemples de mise en œuvre et les résultats de performance. Vous pouvez simplifier vos requêtes d’agrégation existantes en utilisant ces nouvelles extensions d’agrégation SQL et les utiliser pour le développement futur afin de créer des requêtes plus simples et lisibles.

Article original : https://thinkwithwp.com/blogs/big-data/simplify-online-analytical-processing-olap-queries-in-amazon-redshift-using-new-sql-constructs-such-as-rollup-cube-and-grouping-sets/