Upload Data to CAS using EG Tasks
Many SAS 9 customers are familiar with and make heavy use of SAS Enterprise Guide for analytics and reporting. It is a tool on which they rely daily to meet their business needs. As many of those customers transition to SAS Viya there will be a need to access data and capabilities available in CAS from SAS Enterprise Guide. SAS 9 in general, and SAS Enterprise Guide in specific, contain built-in features to assist with this transition. In this post we will walk through the configuration required to assist with uploading data from a SAS 9 environment to CAS.
A couple of months ago Nicolas Robert wrote a very informative article about loading data to CAS from SAS Data Integration Studio. There are obvious similarities with certain portions of the setup and configuration. I recommend reading his post before continuing.
A Query from Halfway around the Globe
The GEL team recently received a query from our colleagues in Japan about uploading data from EG and SAS 9.4 to CAS in a Viya environment. The question was specifically if SAS/CONNECT is still required in both the SAS Viya and SAS 9 environments when using the "Upload to CAS" task within EG. My immediate thought was "of course SAS/CONNECT is required in both environments to use this task". But I would soon learn otherwise.
Common Steps with EM and other SAS 9.4M5 Clients
In my previous post, we walked through the steps to get Enterprise Miner configured to talk to CAS. If you've read that post or read the SAS Viya 3.3 Administration documentation regarding encryption, you are aware that one of the keys steps required for a SAS 9.4M5 environment to talk with CAS is to store the appropriate certificates from the CAS environment to the SAS 9 environment. The steps for storing these certificates can be viewed in the administration guide and a more general overview can be found in my previous blog. This blog assumes these steps have been completed.
Testing the EG "Upload to CAS" Task (using SAS/CONNECT)
In addition to the certificates it will be necessary to verify the SAS/CONNECT setup, create a CAS server and define a CAS libname in metadata. As you would expect these are used as the target for uploading to CAS.
The key steps required to test the connection to CAS are summarized here.
- Verify SAS/CONNECT setup
- Define a CAS server definition in metadata
- Define a CAS libname in metadata
- Test upload of a local SAS dataset to CAS
The "Upload to CAS" task was introduced in SAS Enterprise Guide 7.13 therefore this is the minimum level required. The testing done here used SAS Enterprise Guide 7.15.
SAS/CONNECT
If SAS/CONNECT is installed in the SAS 9.4M5 environment and the default configuration is in place then there should be no need to take any action here other than verify the setup. The key item to check is that the Remote Machine defined in the Connect Server - Options tab points to the SAS 9.4M5 machine. This simply means that the CONNECT Spawner will start a CONNECT server on that machine.
In the test RACE environment the deployment was a single Windows machine deployment. Therefore the Remote Host was set to the local host name. In SAS 9.4M4 this would require setting the remote host to the machine where the SAS Viya Connect Spawner was installed. Hopefully this statement will make sense momentarily.
Click any image to see a larger version.
CAS Server Definition in Metadata
Since data will be uploaded to CAS a CAS server definition is required in metadata. This is a simple server definition using the "New Server Wizard" and selecting the "SAS Cloud Analytic Services Server". The key here is to ensure it is pointing to your CAS controller. It is important to verify the "Authentication type" be none. Otherwise it will attempt to add user and password parameters to the CAS library definition, and these are not valid parameters and result in an error.
CAS Library Definition
Now that the CAS server has been defined, define a CAS library using the "New Library Wizard". Select the "Cloud Analytic Services Library" template and then the SAS server, specify the CASLIB to be used, define a Libref, and select the CAS server defined in the previous step.
At this point the pieces are in place to test.
Test "Upload to CAS" task
You may be a little confused that we didn't define a SAS/CONNECT connection to the Viya 3.3. environment, but just hang in there.
Add a table to the project, such as SASHELP.HUMID, and then add the "Upload to CAS" task to our project. The "Upload to CAS" task will prompt the user with the following message indicating that new capabilities in SAS 9.4M5. What it doesn't mention is that you may have to use the program editor to use these statements. More on this later.
At this point we should have something that looks like this. We need to change the target Library to the library created above and add credentials, as they are not stored.
Our flow looks like this.
If we then run the task, it appears to finish without errors.
First, let's look at the generated code. This may give you a clue why a remote CONNECT session to SAS Viya is not required.
So what happened when it ran? Let's look at the log. It successfully started a SAS/CONNECT session.
In addition it successfully allocated the CAS Public library using the credentials used to start the SAS/CONNECT session.
And successfully uploaded the HUMID dataset to the Public CASLIB. Also note that the file was promoted.
If we check in Environment Manager the table is available.
It is probably pretty clear now why the SAS/CONNECT session to Viya is not required. The local SAS/CONNECT session uses the new SAS 9.4M5 integration capabilities to communicate directly with CAS using the CAS libname defined in metadata. Therefore SAS/CONNECT needs to be deployed in the existing SAS 9.4M5 environment where the EG workspace server resides, but SAS/CONNECT is not required in the SAS Viya environment to load data using the "Upload to CAS" task. Of course data can also be loaded using the native PROCs in SAS 9.4M5 and the program editor within EG. The intent here was to try to use built-in tasks to communicate with CAS.
If you recall earlier it was noted that the SAS 9.4M4 would require defining the SAS/CONNECT spawner and server on SAS Viya. Since SAS 9.4M4 does not contain integration with SAS Viya it is necessary to deploy SAS/CONNECT with SAS Viya so the code from the "Upload to CAS" task can rsubmit code to the Viya SAS/CONNECT server. That code then runs in the Viya environment which has the obvious capability to interact with CAS.
Using Built-in SAS 9.4M5 Integration
The "Upload to CAS" task gets the job done using SAS/CONNECT, but ideally it would use one of the built-in integration capabilities of SAS 9.4M5.
One of the potential tasks to perform an upload is the "Query Builder" task. For example the PROC SQL statement generated by the "Query Builder" task reads from SASHELP.SHOES and will attempt to create a table in WORK by default. In order to write to CAS the target library should be changed to a CAS library such as CASPUBLIC.
The following screenshots show key selections required to a load table to CAS.
The first steps are to change the target library to a CAS library and then select "Options for This Query".
By default the query builder will load the table as a session table. In order to promote the table add the "PROMOTE=YES" option to the Options window as seen below.
If you then preview the generated code you will see that the PROMOTE option is added to the output dataset of the query.
With this configuration in place, running the task will load the data to the CAS library and promote it.
From this example you can see that the "Query Builder" task can be used to load data to CAS and promote tables so that they can be used globally. As a result this task is taking advantage of the built-in integration capabilities of SAS 9.4M5.
If the CAS library and related tables are not visible within SAS EG this can be resolved by taking the steps in SAS Note 20760. The following screenshot shows the updates to the library in metadata.
These EG tasks work well when the authentication mechanism is the same for the client and the server, or the credentials are the same in both environments. The credentials used for the client are also used when authenticating to CAS. If this is not the case additional steps will likely need to be taken.
Final Thoughts
Although some integration is built into SAS Enterprise Guide not all tasks are updated to take full advantage of SAS 9.4M5 integration capabilities. If you are just loading data to CAS the "Upload to CAS" task will get the job done via local SAS/CONNECT and SAS 9 integration features. The "Query Builder" task may also be used but will require adding the PROMOTE option to the output dataset. A third option is to use the program task to define libraries and load data to CAS.
My thanks to Satoshi Kawakami, Shohei Matsuura, and Nicolas Robert for sharing their thoughts and experiences on this topic.
Feel free to share your tips regarding the use SAS 9.4M5 integration features within SAS Enterprise Guide below.
Thanks for reading!
