Viya: Push processing to database

[Patrick]

Hi experts,

Seeking some guidance. 

I'm currently working on a demo which goes as follows:
Assume we've got quite a bit of SAS code running in SAS 9.4 against SAS data set (.sas7bdat).
Build a demo where you show us how to run this SAS code with as little change as possible against data stored in CAS, Hadoop (HDFS & S3) and Redshift using both SAS9.4M5 and SAS Viya 3.3.  ...but try to push processing to where the data resides.

All environments are in a single AWS cloud.

What I've done so far:
I've created a piece of code with multiple simple PROC's and data steps, wrapped a macro around it, and then define libnames with the same libref but pointing to the different sources (i.e. libref source pointing to Hadoop HDFS then call macro, libref source pointing to Redshift then call macro).

This is all working nicely out of SAS9.4M5 and it works even with almost no code change when pointing to CAS (using a CAS libname).

But now comes the real Viya bit (very interesting but lots of novelty in it for me)
If the data resides in Hadoop or Redshift and I want to push processing to where the data resides with a (parent) process under SAS 9.4M5 then is my understanding correct that I still need SAS/Connect to the Viya environment and then execute the code directly in Viya.
I've seen that I could define CAS Libs pointing to Hadoop or Redshift and point a library in SAS9.4 to this CAS lib but as I understand it (not sure) this would pull the data from the source into CAS and my process would then execute against CAS.
Is that correct? Do I need to have my code in an rsubmit block? And if yes how would the library definition need to look like for something like Hadoop/Redshift so that processing can get pushed to the database out of Viya (or just post the link to the relevant docu/blog I've missed so far - best with an example).

....and this is not about writing Viya specific code but trying to process SAS9.4 code under Viya with as little change as possible. 

Thanks,

Patrick

[AhmedAl_Attar · Accepted solution]

Hi @Patrick

If I'm not mistaken, Here are few fundamental things you need to keep in mind.

- Viya/CAS ---> Data resides in Memory, and distributed across multiple nodes

- Hadoop/HDFS ---> Data resides on Disk, and distributed across multiple nodes

- Redshift ---> Data resides on Disk, in a Columnar format 

Any time, you use SAS Viya/CAS, your data has to be loaded into Memory first (CAS) before it can be processed!

- Introduction to SAS/ACCESS Interface to Hadoop

- Introduction to SAS/ACCESS Interface to Amazon Redshift

So while you can demonstrate the ability to Push processing down to the database using SAS 9.x Mz, It will not apply to SAS Viya.

Viya processes data in Memory, if you want utilize the features of the CAS, otherwise your can use the features of the Workspace server within Viya, which is not much different than your SAS 9.4

That's just my take, maybe someone else can you give better/alternative perspective.

Ahmed

[Patrick]

@AhmedAl_Attar

Thank you for your explanations - and sorry for replying so late. 

I've got things working in the end and learned a lot on the way. Your answer certainly helped me to take some of the steps quicker.