Connecting to SQream Using SAS Viya¶
SAS Viya is used to connect to a SQream cluster. The Connecting to SQream Using SAS Viya page describes how to connect to SAS Viya.
The Connecting to SQream Using Tableau page describes the following:
Integrating with SQream has been tested with SAS Viya v.03.05 and newer.
To install SAS Viya, see SAS Viya.
After installing SAS Viya, you must install the JDBC driver.
To install the JDBC driver:
Download the JDBC driver.
Unzip the JDBC driver into a location on the SAS Viya server.
SQream recommends creating the directory
/opt/sqreamon the SAS Viya server.
After installing the JDBC driver, you must configure the JDBC driver from the SAS Studio.
To configure the JDBC driver from the SAS Studio:
Sign in to the SAS Studio.
From the New menu, click SAS Program.
Create a sample program to explore the data, as shown below:
The sample program above does the following:
Line 8: Starts a JDBC session named
sqlibassociated with the SQream driver. This statement extends the SAS global
libnamestatement so you can assign a libref to your data source. The libref feature lets you reference a table in SQream directly from a DATA step or SAS procedure.
Line 10: Provides SAS Viya with the location of the SQream JDBC driver. This step is required because SAS Viya does not support the SAS_ACCESS_CLASSPATH environment variable for this connection.
Lines 8-15: Associates the libref with the SQream driver to be used as
sqlib.tablename. The libref is
sqliband uses the JDBC engine to connect to the
The database name is
masterand the schema is
For more information about writing a connection string, see Connect to SQream DB with a JDBC Application and navigate to Connection String.
Lines 17-20: Prepares data by loading it from the customer’s table into the in-memory space in SAS Viya.
Lines 21-23: DATA step. In this step, standard SAS naming conventions are used to reference the data, with
sqlibas the libref and
nbaas the table name.
Run the program by clicking Run.
The current SAS program is run.
If the sample runs correctly, the following new tabs appear:
The query results are displayed in the Results tab, which shows your query results.
After configuring the JDBC driver from the SAS Studio, you can browse your data and workbooks.
To browse your data and workbooks:
From the panel on the left, Libraries.
The library that you created (
SQLIB) is populated, and the
nbatable is displayed. You can double-click the table name to expand the table and show the columns.
Locate the workbook you created in the data step in the WORK tree item.
The workbook is named
sqlib.nba. You can double-click the table name to expand the table tree.
This section describes how to use SAS visual analytics.
To use SAS visual analytics:
Log in to SAS Visual Analytics using your credentials:
Click New Report.
Click Data Sources.
Click the Connect icon.
From the Type menu, select Database.
Provide the required information and select Persist this connection beyond the current session.
Click Advanced and provide the required information.
Add the following additional parameters by clicking Add Parameters:
Click Test Connection.
If the connection is successful, click Save.
If your connection is not successful, see Best Practices and Troubleshooting below.
This section describes the following best practices and troubleshooting procedures when connecting to SQream using SAS Viya:
When using Tableau, 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 analyze and increases query performance.
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.
In some cases, SAS Viya cannot locate the SQream JDBC driver, generating the following error message:
To locate the SQream JDBC driver:
Verify that you have placed the JDBC driver in a directory that SAS Viya can access.
Verify that the classpath in your SAS program is correct, and that SAS Viya can access the file that it references.
Restart SAS Viya.
For more troubleshooting assistance, see the SQream Support Portal.