3.6. SAS

3.6.1. Introduction

This tutorial is designed to help you set up ODBC for SAS and connect it to QuasarDB. It complements the SAS documentation on ODBC setup. While it’s not a tutorial for SAS Foundation or CAS, we provide a few examples to ensure your ODBC setup is working correctly.

3.6.2. Installation

If you are new to ODBC driver please refer to the official ODBC guideline the Installation section.

It’s important to note that you must download and install the ODBC driver before you can begin using SAS. Additionally, if your system’s microarchitecture predates Haswell, you will require the core2 qdb library.

1. Copy the two libraries in /opt/sas/spre/home/lib64/accessclients/lib

2. Export the ODBC related environment variables in:

   • /opt/sas/viya/config/etc/workspaceserver/default/workspaceserver_usermods.sh

   • /opt/sas/viya/config/etc/sysconfig/compsrv/default/sas-compsrv

   • /opt/sas/viya/config/etc/cas/default/cas_usermods.settings

   export ODBCHOME=/opt/sas/spre/home/lib64/accessclients
   export ODBCINI=$ODBCHOME/odbc.ini
   export ODBCINST=$ODBCHOME/odbcinst.ini
   export ODBCSYSINI=$ODBCHOME
   export LD_LIBRARY_PATH=$ODBCHOME/lib:$LD_LIBRARY_PATH
   

3. Alternatively you can extract the tarball to a specific location of your choosing, and modify the LD_LIBRARY_PATH to include their path.

4. Restart your SAS Studio services.

3.6.3. Configuring the datasource

For the complete list of the parameters refer to Driver parameters section in official ODBC guideline.

If you have not configired yet DSN please refer to the official ODBC DSN section.

Add the datasource name to /opt/sas/spre/home/lib64/accessclients/odbc.ini:

3.6.4. Test the connection

Let’s first check the connection with qdbsh, you can find it in our utils package.

$> qdbsh --cluster qdb://127.0.0.1:2836

...

qdbsh > CREATE TABLE test_quasardb_odbc(col int64)

qdbsh > INSERT INTO test_quasardb_odbc($timestamp, col) VALUES(2020-01-01, 1)

qdbsh > SELECT * FROM test_quasardb_odbc
$timestamp                                 $table   col
--------------------------------------------------------
2020-01-01T00:00:00.000000000Z test_quasardb_odbc     1

Returned 1 row in 2,073 us
Scanned 1 point in 2,073 us (482 rows/sec)

Now that we verified that the connection works correctly using qdbsh, it’s time to test it with ODBC.

If everything went well in the previous steps, we should be able to source /opt/sas/viya/config/etc/workspaceserver/default/workspaceserver_usermods.sh and use iSQL to test our connection.

Depending on your system you’ll need to install the unixodbc package containing iSQL.

$> . /opt/sas/viya/config/etc/workspaceserver/default/workspaceserver_usermods.sh

$> isql quasardb
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT * FROM test_quasardb_odbc
+--------------------------------+-------------------+---------------------+
| $timestamp                     | $table            | col                 |
+--------------------------------+-------------------+---------------------+
| 2020-01-01T00:00:00.000000000Z | test_quasardb_odbc| 1                   |
+--------------------------------+-------------------+---------------------+
SQLRowCount returns 1
1 rows fetched

3.6.5. Examples

We’re almost ready to test SAS Studio, let’s restart its services before we try some examples.

First an example using SAS Foundation, you can copy/paste the following code and execute it in SAS Studio.

libname qtest odbc dsn=quasardb;
proc print data=qtest.test_quasardb_odbc; run;
libname qtest clear;

Then confirm the result:

We just checked that SAS foundation is working properly.

Let’s make sure CAS is working as well.

CAS mySession SESSOPTS=(CASLIB=casuser,metrics=true);
LIBNAME mycas SASIOCA SESSREF=mySession;

Caslib odbclib
    datasource=(
            srctype="odbc"
            conopts="DSN=quasardb;"
    );
caslib _all_ assign;

proc cas;
    table.loadTable / path="test_quasardb_odbc" casout={name="ts_data"};
run;
    table.fetch  / table="ts_data";
quit;

cas mySession DISCONNECT;
cas mySession TERMINATE;

You can confirm the result:

If an error shows up you can find the cause in the logs.

For example if I try to enter an incorrect timeseries name, I would get the following message:

ERROR: ERROR: General error: could not query, error: An entry matching the provided alias cannot be found.
ERROR: Function failed.
ERROR: The action stopped due to errors.