Joining Tables from Multiple Caslibs with fedSql.execDirect

Difficulty Level

Beginner

Published on : 04/08/2022

Expert Advice

Michael
Responsable de l'infrastructure Viya.

f you frequently join a small "lookup" table from one caslib to a massive "fact" table in another, ensure the small table is duplicated across all CAS nodes (using the REPLICATE option during load). This allows the join to occur locally on every worker node, drastically reducing network traffic and execution time.

The fedSql.execDirect action allows joining tables from different CAS libraries (caslibs) in a single query. For queries involving multiple caslibs, a two-level name (caslib.table_name) must be used. A query joining tables from multiple caslibs is not eligible for implicit FedSQL pass-through. This example illustrates the creation and manipulation of temporary in-memory CAS data for standalone demonstrations, covering simple joins, filters, aggregations, and the creation of FedSQL views.

Data Analysis

Type : INTERNAL_CREATION

Examples use generated data (datalines) or SASHELP for demonstrations. The Customers, Products, and Sales tables are created in CAS memory from DATA steps.

1 Code Block

PROC FEDSQL Data

Explanation :
This example shows a simple join between the 'Customers_Basic' and 'Sales_Basic' tables based on the customer ID (CUSTID). It selects CUSTID, customer name, and total sales, and stores the result in a new table 'results_basic'.

Copied!

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 Code Block

PROC FEDSQL Data

Explanation :
This example extends the join by including the 'Products_Common' table. It filters the results to show only sales to customers located in 'USA' and sorts the results by total sales in descending order.

Copied!

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 Code Block

PROC FEDSQL Data

Explanation :
This advanced example calculates the total sales for each product by joining 'Products_Adv' and 'Sales_Adv', then filters the results to include only products with total sales exceeding 1,000,000. Results are sorted by total sales.

Copied!

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 Code Block

PROC FEDSQL Data

Explanation :
This example demonstrates deeper integration with Viya by creating a FedSQL view named 'customer_sales_view' from the 'Customers_Viya' and 'Sales_Viya' tables. Subsequently, the view is queried to display records where total sales exceed 500,000.

Copied!

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;

This material is provided "as is" by We Are Cas. There are no warranties, expressed or implied, as to merchantability or fitness for a particular purpose regarding the materials or code contained herein. We Are Cas is not responsible for errors in this material as it now exists or will exist, nor does We Are Cas provide technical support for it.

Difficulty Level

Published on : 04/08/2022

Expert Advice

Data Analysis

1 Code Block

2 Code Block

3 Code Block

4 Code Block

Related Documentation