Using Explicit SQL Pass-Through with fedSql.execDirect

The `fedSql.execDirect` function in SAS^© Viya^™ 4 offers the ability to execute native SQL statements directly on external databases, without FedSQL attempting to parse or modify the syntax. This is particularly useful for leveraging provider-specific database features, such as proprietary functions or query optimizations. The `CONNECTION TO <caslib_name> (native_SQL)` component is used in the `FROM` clause to specify the connection to the data source via an existing caslib and to encapsulate the native SQL query. The documentation highlights that only native syntax that returns a result set is accepted within this component. The provided example uses a `caslib` named `TDcaslib` (simulated here) which is supposed to be connected to a Teradata database, and retrieves the unique positions from an `employees` table.

Data Analysis

Type : CREATION_INTERNE

The examples will create demonstration data (ephemeral CAS tables) to simulate access to an 'employees' table that would normally reside in an external database via a caslib named 'TDcaslib'. This ensures the autonomy of the examples without requiring a real connection to a Teradata database.

1 Code Block

DATA STEP / PROC CAS fedSql.execDirect Data

Explanation :
This example illustrates the minimal use of `fedSql.execDirect` to send a native SQL query to an external database via a caslib named `TDcaslib`. The native query `select unique Pos from employees` is executed directly on the data source. To make the example autonomous, a `casuser.employees` table is first created in CAS to simulate the structure of the data that would be expected on the external base.

Copied!

1	/* Création d'une table CAS temporaire pour simuler la table 'employees' sur Teradata */
2	/* Dans un environnement réel, la table 'employees' existerait sur Teradata. */
3	/* Ici, 'TDcaslib' est une caslib logique pointant vers la table casuser.employees. */
4	DATA casuser.employees;
5	INPUT EmployeeID Name $ Pos $;
6	DATALINES;
7	101 John Manager
8	102 Jane Developer
9	103 Mike Developer
10	104 Sarah Sales Associate
11	105 Chris Manager
12	106 Emily Executive Assistant
13	;
14	RUN;
15
16	PROC CAS;
17	/* Utilisation de fedSql.execDirect pour exécuter une requête SQL native sur la source de données via TDcaslib. */
18	/* La clause CONNECTION TO TDcaslib() encapsule la requête native pour Teradata. */
19	fedSql.execDirect query='
20	select pos from connection to TDcaslib
21	( select unique Pos from employees )';
22	QUIT;
23
24	/* Nettoyage de la table temporaire */
25	PROC CAS;
26	TABLE.dropTable casin='casuser.employees';
27	QUIT;

2 Code Block

PROC CAS fedSql.execDirect

Explanation :
This intermediate example refines the SQL pass-through query by adding a `WHERE` clause to filter employees with the position 'Developer' and an `ORDER BY` clause to sort the results by name. This demonstrates how more complex SQL options (filtering and sorting) can be passed directly to the external database via `fedSql.execDirect`.

Copied!

1	/* Pré-requis: La table casuser.employees est supposée exister et être chargée en CAS, comme dans l'Exemple 1. */
2	/* Nous la recréons pour garantir l'autonomie si cet exemple est exécuté seul. */
3	DATA casuser.employees;
4	INPUT EmployeeID Name $ Pos $;
5	DATALINES;
6	101 John Manager
7	102 Jane Developer
8	103 Mike Developer
9	104 Sarah Sales Associate
10	105 Chris Manager
11	106 Emily Executive Assistant
12	;
13	RUN;
14
15	PROC CAS;
16	/* Cet exemple utilise une clause WHERE et ORDER BY dans la requête native passée via CONNECTION TO. */
17	/* Les données des employés avec la position 'Developer' sont filtrées et triées par nom. */
18	fedSql.execDirect query='
19	select EmployeeID, Name, Pos from connection to TDcaslib
20	( select EmployeeID, Name, Pos from employees where Pos = ''Developer'' order by Name )';
21	QUIT;
22
23	/* Nettoyage de la table temporaire */
24	PROC CAS;
25	TABLE.dropTable casin='casuser.employees';
26	QUIT;

3 Code Block

PROC CAS fedSql.execDirect

Explanation :
This advanced example shows how to execute a complex aggregation (counting employees by position) directly on the external database using `GROUP BY` and `ORDER BY` clauses in the native pass-through query. This optimizes performance by delegating intensive processing (here, aggregation) to the source database, thereby reducing the transfer of unnecessary data to CAS.

Copied!

1	/* Pré-requis: La table casuser.employees est supposée exister et être chargée en CAS, comme dans l'Exemple 1. */
2	/* Nous la recréons pour garantir l'autonomie si cet exemple est exécuté seul. */
3	DATA casuser.employees;
4	INPUT EmployeeID Name $ Pos $;
5	DATALINES;
6	101 John Manager
7	102 Jane Developer
8	103 Mike Developer
9	104 Sarah Sales Associate
10	105 Chris Manager
11	106 Emily Executive Assistant
12	;
13	RUN;
14
15	PROC CAS;
16	/* Cet exemple utilise une requête native avec agrégation et groupement exécutée sur la source de données. */
17	/* Il compte le nombre d'employés par position et trie les résultats. */
18	fedSql.execDirect query='
19	select Pos, count(EmployeeID) as NumberOfEmployees
20	from connection to TDcaslib
21	( select EmployeeID, Pos from employees )
22	group by Pos
23	order by NumberOfEmployees desc';
24	QUIT;
25
26	/* Nettoyage de la table temporaire */
27	PROC CAS;
28	TABLE.dropTable casin='casuser.employees';
29	QUIT;

4 Code Block

PROC CAS fedSql.execDirect / table.fetch

Explanation :
This example demonstrates a key functionality of CAS integration: the ability to take the result set of a native SQL pass-through query and materialize it directly into a new distributed in-memory table in CAS (`casuser.UniquePositions`). This approach is essential for workflows where source data needs to be further processed using CAS's analytical capabilities and performance.

Copied!

1	/* Pré-requis: La table casuser.employees est supposée exister et être chargée en CAS, comme dans l'Exemple 1. */
2	/* Nous la recréons pour garantir l'autonomie si cet exemple est exécuté seul. */
3	DATA casuser.employees;
4	INPUT EmployeeID Name $ Pos $;
5	DATALINES;
6	101 John Manager
7	102 Jane Developer
8	103 Mike Developer
9	104 Sarah Sales Associate
10	105 Chris Manager
11	106 Emily Executive Assistant
12	;
13	RUN;
14
15	PROC CAS;
16	/* Exécute une requête pass-through et charge le résultat directement dans une nouvelle table CAS. */
17	/* L'option 'replace=true' permet de recréer la table si elle existe déjà. */
18	fedSql.execDirect query='
19	create table casuser.UniquePositions {options replace=true} as
20	select distinct Pos from connection to TDcaslib
21	( select Pos from employees )';
22
23	/* Affiche le contenu de la nouvelle table CAS créée à partir du résultat du pass-through. */
24	TABLE.fetch casin='casuser.UniquePositions';
25	QUIT;
26
27	/* Nettoyage */
28	PROC CAS;
29	TABLE.dropTable casin='casuser.UniquePositions';
30	TABLE.dropTable casin='casuser.employees';
31	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.

Retour à la liste

Expert Advice

Stéphanie

Spécialiste Machine Learning et IA.

« If you find yourself writing standard SQL that doesn't require database-specific functions, try implicit pass-through first; FedSQL often optimizes those queries automatically. »