SAS Cloud Data Exchange for the SAS Viya Platform

[#1]

The Cloud Data Exchange(CDE) for SAS Viya platform is available with the 2023.03 release. The CDE allows SAS users to connect a database which could be either co-located at SAS Viya platform or located at an on-premises data center. The CDE enables user to securely exchange on-premises data from behind a firewall to the SAS Viya platform in the cloud. All data transfer/exchange occurs on secured standard-based communications along with sophisticated authentication and authorization. The on-premises data center requires a single port opened through the firewall to facilitate the data exchange.

The CDE Data Agents comes with the standard SAS Viya platform and does not require a separate additional license. The CDE includes two SAS Data Agent to facilitate the data access and exchange. A Co-located SAS Data Agent, used for accessing data available at the SAS Viya platform. A Remote SAS Data Agent, used for accessing data available at on-premises data center (behind a firewall) from SAS Viya platform in cloud. There can be multiple Remote SAS Data Agents associated with a single SAS Viya deployment. The Data Agents are containerized run time service. The Co-located SAS Data Agent container starts with the standard SAS Viya platform. The Remote SAS Data Agent is a on-premises server that runs as a docker container.

The following picture describes an overall data architecture diagram of the SAS Viya platform, co-located Data Agent, and Remote Data Agent to exchange data between various locations.

Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.

The CDE enables users to directly load CAS from on-premises data and save it back to the on-premises server. The CDE access engine is available to both SAS Compute Server and CAS. A SAS user can use the CDE LIBNAME statement from the SAS Compute server and the CDE CASLIB statement from CAS to access the on-premises data sources. The CDE also enables users to move/copy on-premises data files to SAS Viya cloud storage and vice versa.

The SAS Data Agent administration and data access is supported by a microservice that provides a set of REST interfaces. A Command Line Interface 'sas-viya' CLI is available to administer the SAS Data Agents. A SAS user from the "SAS Data Agent Administrators" group can execute administrative tasks like creating domains and data services at the SAS Data Agent. A SAS user from the “Data Agent Power Users” group can perform tasks like DML (insert, update, and delete), DQL(select), and DDL (create and drop table) at SAS Data Agent.

The CDE Data Agent supports following data sources to exchange data.

The configuration of communication between the Remote Data Agent and the SAS Viya platform is a challenging task. An admin user has to have some degree of expertise in configuring the SAS Viya platform with encrypted (TLS..) communication. Stay tuned in for the next blog on how to deploy and configure Remote Data Agent to securely communicate with the SAS Viya Platform.

Many thanks to my colleague Brian Hess for sharing the expert knowledge and help on this post.

Important Links:

Cloud Data Exchange for the SAS Viya Platform

CDE Supported Data Services  

Find more articles from SAS Global Enablement and Learning here.

[domroy]

Hi !

I'm migrating a lot of on-prem programs to the SAS Viya platform. Most are using SQL Pass-Through facilities to access the on-prem databases.

When those programs will be migrated to the cloud, will they be able to access the on-prem databases using Cloud Data Exchange without changing their SQL pass-through code?

I didn't find any example of programs using CDE and SQL pass-through in the SAS documentation. I didn't find any mention that it is not possible to do it.

So I'm questioning if SQL Pass-Through is supported by CDE?

Thanks.

[LinusH]

@domroy here's what I've found:

• The engine does not support sending native SQL syntax directly to a third-party DBMS through the PROC SQL explicit pass-through facility.
• You can submit SAS FedSQL statements to CDE via the PROC SQL explicit pass-through facility. FedSQL is the server’s native language

https://documentation.sas.com/doc/en/pgmsascdc/v_042/dataagenteng/n1n1betbq2s17bn1b8n8og0c0xrn.htm

[domroy]

Hi Linus,

Much appreciated to have a clear answer.

This is a sad news since we were taught that explicit SQL pass-through was the most effective way to query an external database and most of our programs are built this way.

I'm looking for a clear path to convert our programs to FedSQL.

Thanks.

[Patrick]

@domroy , @LinusH 

It should be possible via a "double hop" (couldn't actually try it yet myself but got told from someone who should know).

Afaik the DB sends messages back to the agent but at least in the current release the agent doesn't pass-on these messages to the SAS client. The SAS client only gets the messages from the agent to which the cde engine connects.

I'll provide feedback to the docu so hopefully this will become official at some point in time. Right now I'd treat this as an undocumented feature.

proc sql;
connect to cde (dataagentname="sas-data-agent-server-colocated" dsn=TSTAUTO_ORACLE);    
select * from connection to cde   /* from SAS client to agent */
(
  select * from connection to TSTAUTO_ORACLE_CDA /* from agent to DB server */
  (
    select * from cars where rownum=1 /* This is native ORACLE syntax */ 
    )
  );    
quit;

[EyalGonen]

Hi @Patrick @domroy @LinusH 

Now SQL Pass Through via CDE is officially documented at: https://documentation.sas.com/doc/en/pgmsascdc/v_051/dataagenteng/n0febhwsuihialn1dvi0dif3m9g8.htm