2.6. SAS

2.6.1. Introduction

This tutorial is in complement to the SAS documentation on ODBC setup.

We will guide you through the process of:

1. Installing the driver

2. Configuring the datasource

3. Test the connection

4. Try a few examples

It is not a SAS foundation nor a CAS tutorial, but a few examples are there to make sure the setup worked.

2.6.2. Installation

1. Download the ODBC driver Linux tarball

2. Extract its contents

   You should have two folders:

   • bin folder containing the installer, that shall not be used in this tutorial.

   • lib folder where you will find both the qdb api, and the ODBC driver.

   Note that if you have a microarchitecture older than Haswell you will need the core2 qdb library.

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

4. 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
   

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

6. Restart your SAS Studio services.

2.6.3. Configuring the datasource

Here is a list of the parameters you can use in the datasource.

Parameter	Description
LOGDIR	A path to the folder in which you wish our log file to be stored
URI	A cluster URI you wish to connect to
UID	The username you log in with
PWD	The password associated with the user
KEY	The cluster public key file content
DSN	The name of the datasource
DESCRIPTION	A description of this specific datasource

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

[ODBC Data Sources]
quasardb = QuasarDB ODBC Data Source

Then adapt the following template to your parameters:

[quasardb]
Driver       = /opt/sas/spre/home/lib64/accessclients/lib/libqdb_odbc_driver.so
Setup        = /opt/sas/spre/home/lib64/accessclients/lib/libqdb_odbc_driver.so
Description  = QuasarDB ODBC Data Source
URI          = qdb://127.0.0.1:2836
UID          =
PWD          =
KEY          =
LogDir       = /var/log/qdb

2.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

2.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.