SAS Viya : Maîtrisez les jointures SQL complexes entre plusieurs Caslibs (Tuto FedSQL)

Niveau de difficulté

Débutant

Publié le : 19/04/2020

Le Conseil de l'Expert

Michael
Responsable de l'infrastructure Viya.

La force majeure de fedSql.execDirect réside dans sa capacité à agir comme un chef d'orchestre entre vos différentes sources de données. Contrairement à une étape DATA classique, FedSQL est nativement conçu pour l'architecture MPP (Massively Parallel Processing) de CAS.

Lorsqu'une jointure implique plusieurs caslibs, FedSQL gère intelligemment le mouvement des données entre les nœuds pour optimiser le traitement. Notez bien que si le 'Pass-Through' implicite est désactivé lors de jointures multi-caslibs, c'est pour permettre au moteur FedSQL de prendre le relais et d'appliquer ses propres algorithmes de jointure distribuée, garantissant ainsi une scalabilité maximale, que vous manipuliez des milliers ou des milliards de lign

L'action fedSql.execDirect permet de joindre des tables provenant de différentes bibliothèques de cas (caslibs) en une seule requête. Pour les requêtes impliquant plusieurs caslibs, un nom à deux niveaux (caslib.nom_de_table) doit être utilisé. Une requête joignant des tables de plusieurs caslibs n'est pas éligible à la transmission implicite (implicit pass-through) de FedSQL. Cet exemple illustre la création et la manipulation de données temporaires en mémoire CAS pour des démonstrations autonomes, couvrant des jointures simples, des filtres, des agrégations et la création de vues FedSQL.

Analyse des données

Type : CREATION_INTERNE

Les exemples utilisent des données générées (datalines) ou SASHELP pour les démonstrations. Les tables Customers, Products et Sales sont créées en mémoire CAS à partir de DATA steps.

1 Bloc de code

PROC FEDSQL Data

Explication :
Cet exemple montre une jointure simple entre les tables 'Customers_Basic' et 'Sales_Basic' basées sur l'identifiant client (CUSTID). Il sélectionne le CUSTID, le nom du client et le total des ventes, et stocke le résultat dans une nouvelle table 'results_basic'.

Copié !

1	/* --- Données de démonstration pour l'exemple 1 --- */
2	DATA Customers_data;
3	INFILE DATALINES dsd;
4	INPUT CUSTID NAME $ COUNTRY $;
5	DATALINES;
6	1,Peter Frank,USA
7	2,Jim Stewart,USA
8	3,Janet Chien,Japan
9	4,Qing Ziao,Japan
10	5,Humberto Sertu,Argentina
11	;
12	RUN;
13
14	DATA Sales_data;
15	INFILE DATALINES dsd;
16	INPUT PRODID CUSTID TOTALS;
17	DATALINES;
18	3421,4,781183
19	1424,3,555789
20	3975,5,899453
21	3421,2,2789654
22	3234,1,189400
23	1424,1,123456
24	3422,2,987654
25	;
26	RUN;
27
28	PROC CASUTIL;
29	load DATA=Customers_data outcaslib="casuser" casout="Customers_Basic" replace;
30	load DATA=Sales_data outcaslib="casuser" casout="Sales_Basic" replace;
31	QUIT;
32
33	/* --- Exemple SAS --- */
34	PROC CAS;
35	fedSql.execDirect query="
36	create table results_basic {options replace=true} as
37	select
38	C.CUSTID,
39	C.NAME,
40	S.TOTALS
41	from
42	casuser.Customers_Basic as C,
43	casuser.Sales_Basic as S
44	where
45	C.CUSTID = S.CUSTID
46	";
47	fedSql.execDirect query="select * from results_basic";
48	QUIT;

2 Bloc de code

PROC FEDSQL Data

Explication :
Cet exemple étend la jointure en incluant la table 'Products_Common'. Il filtre les résultats pour n'afficher que les ventes aux clients situés aux 'USA' et trie les résultats par le total des ventes par ordre décroissant.

Copié !

1	/* --- Données de démonstration pour l'exemple 2 --- */
2	DATA Customers_data;
3	INFILE DATALINES dsd;
4	INPUT CUSTID NAME $ COUNTRY $;
5	DATALINES;
6	1,Peter Frank,USA
7	2,Jim Stewart,USA
8	3,Janet Chien,Japan
9	4,Qing Ziao,Japan
10	5,Humberto Sertu,Argentina
11	;
12	RUN;
13
14	DATA Products_data;
15	INFILE DATALINES dsd;
16	INPUT PRODID PRODUCT $;
17	DATALINES;
18	3234,Rice
19	1424,Corn
20	3421,Wheat
21	3422,Oat
22	3975,Barley
23	;
24	RUN;
25
26	DATA Sales_data;
27	INFILE DATALINES dsd;
28	INPUT PRODID CUSTID TOTALS;
29	DATALINES;
30	3421,4,781183
31	1424,3,555789
32	3975,5,899453
33	3421,2,2789654
34	3234,1,189400
35	1424,1,123456
36	3422,2,987654
37	;
38	RUN;
39
40	PROC CASUTIL;
41	load DATA=Customers_data outcaslib="casuser" casout="Customers_Common" replace;
42	load DATA=Products_data outcaslib="casuser" casout="Products_Common" replace;
43	load DATA=Sales_data outcaslib="casuser" casout="Sales_Common" replace;
44	QUIT;
45
46	/* --- Exemple SAS --- */
47	PROC CAS;
48	fedSql.execDirect query="
49	create table results_common {options replace=true} as
50	select
51	P.PRODUCT,
52	C.NAME,
53	S.TOTALS,
54	C.COUNTRY
55	from
56	casuser.Products_Common as P,
57	casuser.Sales_Common as S,
58	casuser.Customers_Common as C
59	where
60	P.PRODID = S.PRODID and
61	C.CUSTID = S.CUSTID and
62	C.COUNTRY = 'USA'
63	order by S.TOTALS desc
64	";
65	fedSql.execDirect query="select * from results_common";
66	QUIT;

3 Bloc de code

PROC FEDSQL Data

Explication :
Cet exemple avancé calcule le total des ventes pour chaque produit en joignant 'Products_Adv' et 'Sales_Adv', puis filtre les résultats pour n'inclure que les produits dont le total des ventes dépasse 1 000 000. Les résultats sont triés par le total des ventes.

Copié !

1	/* --- Données de démonstration pour l'exemple 3 --- */
2	DATA Customers_data;
3	INFILE DATALINES dsd;
4	INPUT CUSTID NAME $ COUNTRY $;
5	DATALINES;
6	1,Peter Frank,USA
7	2,Jim Stewart,USA
8	3,Janet Chien,Japan
9	4,Qing Ziao,Japan
10	5,Humberto Sertu,Argentina
11	;
12	RUN;
13
14	DATA Products_data;
15	INFILE DATALINES dsd;
16	INPUT PRODID PRODUCT $;
17	DATALINES;
18	3234,Rice
19	1424,Corn
20	3421,Wheat
21	3422,Oat
22	3975,Barley
23	;
24	RUN;
25
26	DATA Sales_data;
27	INFILE DATALINES dsd;
28	INPUT PRODID CUSTID TOTALS;
29	DATALINES;
30	3421,4,781183
31	1424,3,555789
32	3975,5,899453
33	3421,2,2789654
34	3234,1,189400
35	1424,1,123456
36	3422,2,987654
37	;
38	RUN;
39
40	PROC CASUTIL;
41	load DATA=Customers_data outcaslib="casuser" casout="Customers_Adv" replace;
42	load DATA=Products_data outcaslib="casuser" casout="Products_Adv" replace;
43	load DATA=Sales_data outcaslib="casuser" casout="Sales_Adv" replace;
44	QUIT;
45
46	/* --- Exemple SAS --- */
47	PROC CAS;
48	fedSql.execDirect query="
49	create table top_selling_products {options replace=true} as
50	select
51	P.PRODUCT,
52	SUM(S.TOTALS) as TOTAL_SALES
53	from
54	casuser.Products_Adv as P,
55	casuser.Sales_Adv as S
56	where
57	P.PRODID = S.PRODID
58	group by
59	P.PRODUCT
60	having
61	SUM(S.TOTALS) > 1000000
62	order by
63	TOTAL_SALES desc
64	";
65	fedSql.execDirect query="select * from top_selling_products";
66	QUIT;

4 Bloc de code

PROC FEDSQL Data

Explication :
Cet exemple démontre une intégration plus poussée avec Viya en créant une vue FedSQL nommée 'customer_sales_view' à partir des tables 'Customers_Viya' et 'Sales_Viya'. Ensuite, la vue est interrogée pour afficher les enregistrements où le total des ventes est supérieur à 500 000.

Copié !

1	/* --- Données de démonstration pour l'exemple 4 --- */
2	DATA Customers_data;
3	INFILE DATALINES dsd;
4	INPUT CUSTID NAME $ COUNTRY $;
5	DATALINES;
6	1,Peter Frank,USA
7	2,Jim Stewart,USA
8	3,Janet Chien,Japan
9	4,Qing Ziao,Japan
10	5,Humberto Sertu,Argentina
11	;
12	RUN;
13
14	DATA Sales_data;
15	INFILE DATALINES dsd;
16	INPUT PRODID CUSTID TOTALS;
17	DATALINES;
18	3421,4,781183
19	1424,3,555789
20	3975,5,899453
21	3421,2,2789654
22	3234,1,189400
23	1424,1,123456
24	3422,2,987654
25	;
26	RUN;
27
28	PROC CASUTIL;
29	load DATA=Customers_data outcaslib="casuser" casout="Customers_Viya" replace;
30	load DATA=Sales_data outcaslib="casuser" casout="Sales_Viya" replace;
31	QUIT;
32
33	/* --- Exemple SAS --- */
34	PROC CAS;
35	/* Créer une vue FedSQL sur des tables CAS */
36	fedSql.execDirect query="
37	create view customer_sales_view {options replace=true} as
38	select
39	C.NAME,
40	C.COUNTRY,
41	S.TOTALS
42	from
43	casuser.Customers_Viya as C,
44	casuser.Sales_Viya as S
45	where
46	C.CUSTID = S.CUSTID
47	";
48
49	/* Interroger la vue FedSQL */
50	fedSql.execDirect query="select * from customer_sales_view where TOTALS > 500000";
51	QUIT;

L'Astuce Pro

Attention au piege de la performance lors des jointures multi-caslibs. Comme indique dans la description, FedSQL ne peut pas utiliser le Implicit Pass-Through (push-down) vers la base de donnees source si la requete implique plusieurs caslibs differentes (par exemple une table Oracle et une table Hadoop). Cela force le moteur CAS a charger l integralite des donnees brutes en memoire avant de realiser la jointure. Pour eviter de saturer la RAM, appliquez des clauses WHERE restrictives sur chaque table source ou creez des vues intermediaires pre-filtrees avant de lancer votre jointure finale.

Ce matériel est fourni "tel quel" par We Are Cas. Il n'y a aucune garantie, expresse ou implicite, quant à la qualité marchande ou à l'adéquation à un usage particulier concernant le matériel ou le code contenu dans les présentes. We Are Cas n'est pas responsable des erreurs dans ce matériel tel qu'il existe maintenant ou existera, et We Are Cas ne fournit pas de support technique pour celui-ci.

Documentation liée

Aucune documentation spécifique pour cette catégorie.