Connecting to SQream Using SAS Viya

Overview

SAS Viya is a cloud-enabled analytics engine used for producing useful insights. The Connect to SQream Using SAS Viya page describes how to connect to SAS Viya, and describes the following:

Installing SAS Viya

The Installing SAS Viya section describes the following:

Downloading SAS Viya

Integrating with SQream has been tested with SAS Viya v.03.05 and newer.

To download SAS Viya, see SAS Viya.

Installing the JDBC Driver

The SQream JDBC driver is required for establishing a connection between SAS Viya and SQream.

To install the JDBC driver:

  1. Download the JDBC driver.

  2. Unzip the JDBC driver into a location on the SAS Viya server.

    SQream recommends creating the directory /opt/sqream on the SAS Viya server.

Configuring SAS Viya

After installing the JDBC driver, you must configure the JDBC driver from the SAS Studio so that it can be used with SQream Studio.

To configure the JDBC driver from the SAS Studio:

  1. Sign in to the SAS Studio.

  2. From the New menu, click SAS Program.

  3. Configure the SQream JDBC connector by adding the following rows:

    options sastrace='d,d,d,d' 
    sastraceloc=saslog 
    nostsuffix 
    msglevel=i 
    sql_ip_trace=(note,source) 
    DEBUG=DBMS_SELECT;
    
    options validvarname=any;
    
    libname sqlib jdbc driver="com.sqream.jdbc.SQDriver"
       classpath="/opt/sqream/sqream-jdbc-4.0.0.jar" 
       URL="jdbc:Sqream://sqream-cluster.piedpiper.com:3108/raviga;cluster=true" 
       user="rhendricks"
       password="Tr0ub4dor3"
       schema="public" 
       PRESERVE_TAB_NAMES=YES
       PRESERVE_COL_NAMES=YES;
    

For more information about writing a connection string, see Connect to SQream DB with a JDBC Application and navigate to Connection String.

Operating SAS Viya

The Operating SAS Viya section describes the following:

Using SAS Viya Visual Analytics

This section describes how to use SAS Viya Visual Analytics.

To use SAS Viya Visual Analytics:

  1. Log in to SAS Viya Visual Analytics using your credentials:

  1. Click New Report.

  2. Click Data.

  3. Click Data Sources.

  4. Click the Connect icon.

  5. From the Type menu, select Database.

  6. Provide the required information and select Persist this connection beyond the current session.

  7. Click Advanced and provide the required information.

  8. Add the following additional parameters by clicking Add Parameters:

Name

Value

class

com.sqream.jdbc.SQDriver

classPath

<path_to_jar_file>

url

jdbc:Sqream://<IP>:<port>/<database>;cluster=true

username

<username>

password

<password>

  1. Click Test Connection.

  2. If the connection is successful, click Save.

If your connection is not successful, see Troubleshooting SAS Viya below.

Troubleshooting SAS Viya

The Best Practices and Troubleshooting section describes the following best practices and troubleshooting procedures when connecting to SQream using SAS Viya:

Inserting Only Required Data

When using SAS Viya, SQream recommends using only data that you need, as described below:

  • Insert only the data sources you need into SAS Viya, excluding tables that don’t require analysis.

  • To increase query performance, add filters before analyzing. Every modification you make while analyzing data queries the SQream database, sometimes several times. Adding filters to the datasource before exploring limits the amount of data analyzed and increases query performance.

Creating a Separate Service for SAS Viya

SQream recommends creating a separate service for SAS Viya with the DWLM. This reduces the impact that Tableau has on other applications and processes, such as ETL. In addition, this works in conjunction with the load balancer to ensure good performance.

Locating the SQream JDBC Driver

In some cases, SAS Viya cannot locate the SQream JDBC driver, generating the following error message:

java.lang.ClassNotFoundException: com.sqream.jdbc.SQDriver

To locate the SQream JDBC driver:

  1. Verify that you have placed the JDBC driver in a directory that SAS Viya can access.

  2. Verify that the classpath in your SAS program is correct, and that SAS Viya can access the file that it references.

  3. Restart SAS Viya.

For more troubleshooting assistance, see the SQream Support Portal.

Supporting TEXT

In SAS Viya versions lower than 4.0, casting TEXT to CHAR changes the size to 1,024, such as when creating a table including a TEXT column. This is resolved by casting TEXT into CHAR when using the JDBC driver.