Implicit and explicit passthrough to a database using the SQL procedure

IanBD
IanBD
Altair Employee
edited September 2022 in Altair RapidMiner

When processing data on a database in your SAS language programs, you might find it useful to have the control of processing tables on a database or on your local machine. Altair SLC enables this control in the SQL procedure through either Implicit passthrough or Explicit passthrough.

    • Implicit passthrough uses the SQL statements supported in the SQL procedure. Statements are prepared and processed before being passed through a library engine connection for the database. Some SQL statements are passed to the database for processing; the specific statements are determined by Altair SLC.
    • Explicit passthrough enables all SQL statements to be passed to the database for processing without any prior preparation. This enables your SAS language program to use both standard SQL statements and database-specific SQL statements to process data stored in the database.

In these examples, placeholders are used for the credentials for the connection to the database. When replicating these examples, replace the option values with your own connection credentials for your database.

Implicit passthrough

For implicit passthrough, processing is carried out in Altair SLC. For example, you can fetch the dataset by specifying the corresponding member of the library in the SQL procedure:

LIBNAME POSTGRES POSTGRESQL USER=user PASSWORD='password' SERVER=server DATABASE=database; PROC SQL;   CREATE TABLE TABLE1 AS SELECT * FROM POSTGRES.TABLE; QUIT;

Using this method follows the SAS language conventions by utilising the POSTGRESQL libname engine as any other library in your program.

Explicit passthrough

For explicit passthrough, all processing is done on the database that you connect to. This has an advantage in processing power if you are working with big data as the processing is taken off of your local machine. This method being fully contained in the SQL procedure requires users to be more familiar with SQL than SAS language.

PROC SQL;   CONNECT TO POSTGRESQL(SERVER=server                         USER=user                         PASSWORD='password'                         DATABASE=database);   CREATE TABLE TABLE1 AS SELECT * FROM CONNECTION TO POSTGRESQL(SELECT * FROM TABLE);   DISCONNECT FROM POSTGRESQL; QUIT;

Here, the table is fetched from the POSTGRESQL database.

Similarly, if you want to use explicit passthrough without fetching any tables, you can use the EXECUTE statement, for example:

PROC SQL;   CONNECT TO POSTGRESQL(SERVER=server                         USER=user                         PASSWORD='password'                         DATABASE=database);   EXECUTE(DROP TABLE1) BY POSTGRESQL;   DISCONNECT FROM POSTGRESQL; QUIT;

Here, using explicit passthrough, the EXECUTE statement drops the table, table1 on the POSTGRESQL database. No tables are accessed or fetched on your local machine.

Tagged:

Categories