SAS Viya: SAS Intelligent Decisioning – Data Query Configuration

[#1]

In SAS Intelligent Decisioning on the new SAS Viya, you can use Data Queries as part of your decision flow.  A data query allows you to use SQL to return records from a database or SAS data set.  Any decisions that involve a data query file can only be published to the SAS Micro Analytic Service (MAS).  To query a database, you first need to configure MAS in order to interact with the database.  Because SAS Viya has been containerized, the data query configuration has changed since I described it for the previous version in this article.

There are three main steps for configuring MAS to use data query files in SAS Intelligent Decisioning for the new SAS Viya.  Note:  You must have administrative privileges to configure MAS.

1. Enter the connection string information for the third-party database in the microanalyticserviceservice.properties for the Micro Analytic Score service in SAS Environment Manager.
   Select any image to see a larger version.
   Mobile users: To view the images, select the "Full" version at the bottom of the page.
2. Restart the MAS service by entering the following statements:

   # Find pod name for MAS
   kubectl -n NAMESPACE get pods | grep microanalytic

   # Restart MAS pod
   kubectl -n NAMESPACE delete pod MAS_POD_NAME
3. In SAS Intelligent Decisioning, create a Custom Code Data Query file and validate the SQL statement to ensure the configuration is correct and you can communicate with the third-party database. For more information on creating a data query file, refer to the documentation.  If the connection is successful (and your SQL is correct), you will receive the message below when you validate your query.

Currently, the only databases supported for data queries in the new SAS Viya are Oracle and Postgres.

Postgres Configuration Example

In SAS Environment Manager, I edit the sas.microanalyticserviceservice.properties section and for the connectionstring property I enter the following:

driver=sql;conopts=(driver=postgres;catalog=mypg;uid=<username>;pwd=<password>;server=<server name>;port=5432;DB=<DB name>;schema=<schema name>)

Next, I restart the MAS service.  After waiting a few minutes for the service to be fully up and running, I validate a data query in SAS Intelligent Decisioning for one of the tables in my Postgres database.

In my query above I refer to the custom catalog name mypg when referencing the table since my connection string has the schema associated with it.

Working with data query results has not changed since SAS Viya 3.5, so for more information refer to this previous article on that topic. 

Summary

For more information on data queries, refer to the documentation on data queries and the documentation for configuring its support.

Acknowledgements

Thanks to my colleague, Mark Thomas, for his assistance in restarting MAS for the writing of this article.