3.6. SAS

3.6. SAS

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

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

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

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:

../../_images/sas_foundation_get_data.png

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:

../../_images/sas_foundation_get_data.png

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.