The SAS Viya platform supports the Service Principal authentication-based access to the Azure DataBricks database with the CDATA JDBC drivers. This access is available for both SPARK LIBNAME and SPARK CASLIB statements against the Azure Databricks database. To access using Service Principal, the SAS user is required to include the Azure environment details, Databricks properties, and the Service Principle credential in the properties= option of the LIBNAME and CASLIB statement.   In this post, I discuss the configuration parts of the Azure Service Principal, Databricks workspace, and Service Principal permission at DataBricks for accessing the Azure Databricks database.   The CDATA JDBC driver is distributed with the SAS Viya software deployment and is the default driver for SAS users to connect to the DataBricks database. With the CDATA JDBC connection string properties, SAS users can provide the Azure TenantID, SubscriptionID, ResourceGroup, DatabricksWorksSpace, ClientID, ClientSecrete, and AuthScheme. The JDBC connection will acquire an access token from the Databricks environment using the connection properties when AuthScheme is service principal. The Azure Service Principal (ClientId) must have a valid secret and the Databricks API permission as part of the configuration.   The CDATA documentation to access the Azure DataBricks using Service Principal.   Azure Service Principle (application) configuration:   When creating an Entra ID Service Principle (application) for Databricks access, apart from standard API permissions, you must include the “Azure DataBricks” API permission with the user_impersonation delegated role.   The Azure tenant admin must have consented to these roles and the status must be green with text like granted for specific domain/organization.   Select any image to see a larger version. Mobile users: If you do not see this image, scroll to the bottom of the page and select the "Full" version of this post.     The Azure Service Principle (ClientId) must have a valid secret.   Databricks Workspace Configuration:   The PAT (Personal Access Token) Option must be enabled at Databricks Workspace for PAT, SSO, and Service Principal (SP) based access path to work. The user processes connecting to Azure Databricks with SSO or SP authentication using the JDBC driver initiate a PAT token at Databricks instance, even though it’s the SSO or SP based connection.     Add an Entra ID Service Principle(application) to the Databricks environment for DataBricks workspace, and database tables access.     Use the “Microsoft Entra ID Managed” option while adding and provide the application ID from Azure Service Principle (application) configuration steps. Under Entitlement section, select the “Databricks SQL Access” and “Allow Workspace access” with status active.       Grant access privileges to user (geldm) to use the Service principle. The users or groups who need to use the service principle must be granted with “Service Principle: user” privilege. The user with “Service Principle: manager” privilege, by default does not get ability to use it. The “Service Principle: user” privilege must be granted to the user (geldm) to use the Service Principle.   At the Service Principle details, under the Permission tab, you can add and update the additional privileges to users with the required privileges and permissions.     The Entra ID Service Principal (application) added to the DataBricks workspace must be part of the admin group for Service Principle-based authentication. Behind the scenes, the authentication process creates a DataBricks PAT token for SSO or Service Principal users. As per the DataBricks configuration, only admin group users can manage and create PAT tokens at DataBricks.     Service Principal based access from SAS Compute Server to Azure DataBricks   When the Service Principal is configured with the required API permission and a valid credential. The DataBricks Workspace is included with a valid Service principle and permissions. The SAS users can use the Service Principal in properites= option of the LIBNAME statement to access the Azure Databricks database using SPARK LIBNAME engine.   The LIBNAME with the Service Principal is supported with the CDATA JDBC driver. The driver is distributed with the SAS Viya software deployment and is the default driver for SAS users to connect to the DataBricks database.   The following code describes the Service Principal based access from the SAS Compute Server to the Azure Databricks database. Notice that there is no user ID and password but have Service Principal, credential, workspace name, and other information in the properties= option of the LIBNAME statement.   Code:   %let MYRSGRP=clove-r-0302-viya4-data ; %let MYAZWS=ws-clove-r-0302-viya4-data; %let MYDBRICKS=adb-3290662697234059.19.azuredatabricks.net; %let MYHTTPPATH=sql/protocolv1/o/3290662697234059/0916-145442-7dn19gjc; %let MYTNTID=a708fb09-1d96-416a-ad34-72fa07ff196d; %let MYSUBID=d588dad0-2004-4b14-a34e-6bf0519e32e4; %let MYCLNT_ID=49200cb0-a10c-420e-8ba5-6d06ee6ac1f6; /* AppReg client secret */ %let MYCLNT_SECRET=H8N8Q~XXXXXX~XXXXXXX; %let MYCATALOG=hive_metastore; %let MYSCHEMA=default; /* Azure Service Principal based LIBNAME to Databricks */ libname spkdb spark platform=databricks server="&MYDBRICKS" database="&MYSCHEMA" port=443 httppath="&MYHTTPPATH" properties="Catalog=&MYCATALOG;AuthScheme=AzureServicePrincipal;AzureTenantId=&MYTNTID;AzureClientId=&MYCLNT_ID;AzureClientSecret=&MYCLNT_SECRET;AzureSubscriptionId=&MYSUBID;AzureResourceGroup=&MYRSGRP;AzureWorkspace=&MYAZWS;QueryPassthrough=true;DefaultColumnSize=1024;Other=ConnectRetryWaitTime=20;" bulkload=no character_multiplier=1 dbmax_text=50; Proc SQL outobs=5; select * from spkdb.baseball_prqt ; run; quit; data spkdb.prdsal2_sas3 ; set sashelp.prdsal2 ; run;   Log:   ......... ..... 82 %let MYRSGRP=birch-p03073-viya4-data ; 83 %let MYAZWS=ws-birch-p03073-viya4-data; 84 %let MYDBRICKS=adb-3916581250425199.19.azuredatabricks.net; 85 %let MYHTTPPATH=sql/protocolv1/o/3916581250425199/0927-154850-ssq0x1nk; 86 87 98 /* Azure Service Principal based LIBNAME to Databricks */ 99 libname spkdb spark platform=databricks 100 server="&MYDBRICKS" 101 database="&MYSCHEMA" 102 port=443 103 httppath="&MYHTTPPATH" 104 properties="Catalog=&MYCATALOG;AuthScheme=AzureServicePrincipal;AzureTenantId=&MYTNTID;AzureClientId=&MYCLNT_ID; 104! AzureClientSecret=&MYCLNT_SECRET;AzureSubscriptionId=&MYSUBID;AzureResourceGroup=&MYRSGRP;AzureWorkspace=&MYAZWS; 104! QueryPassthrough=true;DefaultColumnSize=1024;Other=ConnectRetryWaitTime=20;" NOTE: The quoted string currently being processed has become more than 262 bytes long. You might have unbalanced quotation marks. 105 bulkload=no 106 character_multiplier=1 107 dbmax_text=50; NOTE: Libref SPKDB was successfully assigned as follows: Engine: SPARK Physical Name: jdbc:cdata:databricks:Server=adb-3916581250425199.19.azuredatabricks.net;Database=default;HTTPPath=sql/protocolv1/o/3916581250 425199/0927-154850-ssq0x1nk;QueryPassthrough=true;UseCloudFetch=true;Catalog=hive_metastore;AuthScheme=AzureServicePrincipal;A zureTenantId=a708fb09-1d96-416a-ad34-72fa07ff196d;AzureClientId=49200cb0-a10c-420e-8ba5-6d06ee6ac1f6;AzureClientSecret=H8N8Q~v 2KTT1VCbjXXXXXXXXXX;AzureSubscriptionId=d588dad0-2004-4b14-a34e-6bf0519e32e4;AzureResourceGroup=birch-p03073-viy a4-data;AzureWorkspace=ws-birch-p03073-viya4-data;QueryPassthrough=true;DefaultColumnSize=1024;Other=ConnectRetryWaitTime=20; 108 109 Proc SQL outobs=5; 110 select * from spkdb.baseball_prqt ; WARNING: Statement terminated early due to OUTOBS=5 option. 111 run; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. 112 quit; NOTE: The PROCEDURE SQL printed page 3. NOTE: PROCEDURE SQL used (Total process time): real time 1.40 seconds cpu time 0.07 seconds 113 114 data spkdb.prdsal2_sas3 ; 115 set sashelp.prdsal2 ; 116 run; NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables. NOTE: There were 23040 observations read from the data set SASHELP.PRDSAL2. NOTE: The data set SPKDB.PRDSAL2_SAS3 has 23040 observations and 11 variables. NOTE: DATA statement used (Total process time): .... ...........   Results:     Service Principal based access from CAS to Azure DataBricks   When the Service Principal is configured with the required API permission and a valid credential. The DataBricks Workspace is included with a valid Service principle and permissions. The SAS users can use the Service Principal in properites= option of the CASLIB statement to access and load CAS from the Azure Databricks database.   The CASLIB with the Service Principal is supported with the CDATA JDBC driver. The driver is distributed with the SAS Viya software deployment and is the default driver for SAS users to connect to the DataBricks database.   The following code describes the Service Principal based access from the CAS to the Azure Databricks database. Notice that there is no user ID and password but have Service Principal, credential, workspace name, and other information in the properties= option of the CASLIB statement.   Code:   %let MYRSGRP=clove-r-0302-viya4-data ; %let MYAZWS=ws-clove-r-0302-viya4-data; %let MYDBRICKS=adb-3290662697234059.19.azuredatabricks.net; %let MYHTTPPATH=sql/protocolv1/o/3290662697234059/0916-145442-7dn19gjc; %let MYTNTID=a708fb09-1d96-416a-ad34-72fa07ff196d; %let MYSUBID=d588dad0-2004-4b14-a34e-6bf0519e32e4; %let MYCLNT_ID=49200cb0-a10c-420e-8ba5-6d06ee6ac1f6; /* AppsReg client secret */ %let MYCLNT_SECRET=H8N8Q~XXXXXXXXXXXXXXXXXXX; %let MYCATALOG=hive_metastore; %let MYSCHEMA=default; CAS mySession SESSOPTS=( CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true); /* Azure Service Principal based CASLIB to Databricks */ caslib Cdtspkcaslib datasource=(srctype='spark', platform=databricks, schema="&MYSCHEMA", server="&MYDBRICKS", httpPath="&MYHTTPPATH", bulkload=no, port=443, properties="Catalog=&MYCATALOG;AuthScheme=AzureServicePrincipal;AzureTenantId=&MYTNTID;AzureClientId=&MYCLNT_ID;AzureClientSecret=&MYCLNT_SECRET;AzureSubscriptionId=&MYSUBID;AzureResourceGroup=&MYRSGRP;AzureWorkspace=&MYAZWS;QueryPassthrough=true;DefaultColumnSize=1024;Other=ConnectRetryWaitTime=20;" ) libref=dbxlib ; /* Save CAS table to DataBricks database table */ proc casutil outcaslib="Cdtspkcaslib" incaslib="Cdtspkcaslib"; load data=sashelp.cars casout="cars" replace; save casdata="cars" casout="cars_sas" replace; list files; quit; /* Load CAS from DataBricks database table */ proc casutil outcaslib="Cdtspkcaslib" incaslib="Cdtspkcaslib" ; load casdata="cars_sas" casout="cars_sas" replace; list tables; quit; CAS mySession TERMINATE;   Log:   ... ....... 97 /* Azure Service Principal based CASLIB to Databricks */ 98 caslib Cdtspkcaslib datasource=(srctype='spark', 99 platform=databricks, 100 schema="&MYSCHEMA", 101 server="&MYDBRICKS", 102 httpPath="&MYHTTPPATH", 103 bulkload=no, 104 port=443, 105 properties="Catalog=&MYCATALOG;AuthScheme=AzureServicePrincipal;AzureTenantId=&MYTNTID;AzureClientId=&MYCLNT_ID; 105! AzureClientSecret=&MYCLNT_SECRET;AzureSubscriptionId=&MYSUBID;AzureResourceGroup=&MYRSGRP;AzureWorkspace=&MYAZWS; 105! QueryPassthrough=true;DefaultColumnSize=1024;Other=ConnectRetryWaitTime=20;" NOTE: The quoted string currently being processed has become more than 262 bytes long. You might have unbalanced quotation marks. 106 ) libref=dbxlib ; NOTE: Executing action 'table.addCaslib'. NOTE: 'CDTSPKCASLIB' is now the active caslib. …. …. …. 107 108 /* Save CAS table to DataBricks database table */ 109 proc casutil outcaslib="Cdtspkcaslib" incaslib="Cdtspkcaslib"; NOTE: The UUID '70d1e97b-dd79-564d-ae5b-91243d85c31e' is connected using session MYSESSION. 110 load data=sashelp.cars casout="cars" replace; NOTE: The INCASLIB= option is ignored when using the DATA= option in the LOAD statement. NOTE: Executing action 'table.addTable'. NOTE: Action 'table.addTable' used (Total process time): NOTE: real time 0.017585 seconds NOTE: cpu time 0.029144 seconds (165.73%) NOTE: total nodes 3 (12 cores) NOTE: total memory 94.03G NOTE: memory 3.51M (0.00%) NOTE: bytes moved 68.08K NOTE: SASHELP.CARS was successfully added to the "CDTSPKCASLIB" caslib as "CARS". 111 save casdata="cars" casout="cars_sas" replace; NOTE: Executing action 'table.save'. NOTE: Performing serial SaveTable action using SAS Data Connector to Spark. NOTE: Cloud Analytic Services saved the file cars_sas in caslib CDTSPKCASLIB. NOTE: Action 'table.save' used (Total process time): NOTE: real time 17.310049 seconds NOTE: cpu time 0.129744 seconds (0.75%) NOTE: total nodes 3 (12 cores) NOTE: total memory 94.03G NOTE: memory 7.41M (0.01%) NOTE: The Cloud Analytic Services server processed the request in 17.310049 seconds. 112 list files; NOTE: Executing action 'table.caslibInfo'. …… …… ….. 115 /* Load CAS from DataBricks database table */ 116 proc casutil outcaslib="Cdtspkcaslib" incaslib="Cdtspkcaslib" ; NOTE: The UUID '70d1e97b-dd79-564d-ae5b-91243d85c31e' is connected using session MYSESSION. 117 load casdata="cars_sas" casout="cars_sas" replace; NOTE: Executing action 'table.loadTable'. NOTE: Performing serial LoadTable action using SAS Data Connector to Spark. NOTE: Cloud Analytic Services made the external data from cars_sas available as table CARS_SAS in caslib Cdtspkcaslib. NOTE: Action 'table.loadTable' used (Total process time): NOTE: real time 3.580677 seconds NOTE: cpu time 0.072706 seconds (2.03%) NOTE: total nodes 3 (12 cores) NOTE: total memory 94.03G NOTE: memory 8.63M (0.01%) NOTE: bytes moved 127.68K NOTE: The Cloud Analytic Services server processed the request in 3.580677 seconds. 118 list tables; … ..   Results:     Important Links: CDATA document - access the Azure DataBricks using Service Principal     ... View more

The SAS Viya platform supports Single-Sign-On(SSO) authentication access to the Azure DataBricks environment with the CDATA and Databrick JDBC drivers. The SSO is supported for both SPARK LIBNAME and SPARK CASLIB statements against the Databricks database. The LIBNAME and CASLIB statement must include option AUTH=OAUTH2 to use the SSO authenticated connection to DataBricks. The CDATA JDBC driver is distributed with the SAS Viya software deployment and is the default driver for SAS to connect to the DataBricks database.   The SAS Viya platform can be configured with Microsoft Entra ID as an OIDC provider for initial user authentication. The steps to configure the SAS Viya platform for using OIDC with Microsoft Entra ID are listed in SAS documentation.   In this post, I discuss the critical configuration parts of the Entra ID OIDC Application, Databricks workspace, and user permission at DataBricks when using SSO-based access to the Databricks database.   Entra ID OIDC application configuration:   When creating an Entra ID OIDC application for Databricks access, apart from standard API permissions, you must include the “Azure DataBricks” API permission with the user_impersonation delegated role.   The Azure tenant admin must have consented to these roles and the status must be green with text like granted for specific domain/organization.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.     Databricks Workspace Configuration:   The PAT (Personal Access Token) Option must be enabled at Databricks Workspace for PAT, SSO, and Service Principal (SP) based access path to work. The user processes connecting to Azure Databricks with SSO or SP authentication using the JDBC driver initiate a PAT token at Databricks instance, even though it’s the SSO or SP connection.     The Databricks admin user can add additional Entra ID users to the Databricks environment for DataBricks workspace, and database tables access.     All Entra ID users added to the DataBricks workspace must be part of the admin group for SSO authentication. Behind the scenes, the OAuth process creates a DataBricks PAT token for SSO users. As per the DataBricks configuration, only admin group users can manage and create PAT tokens at DataBricks.       SSO-based access from SAS Compute Server to Azure DataBricks   When the SAS Viya platform is configured with Entra ID OIDC application to provide initial user authentication and Databricks Workspace is configured with Entra ID user access permissions. SAS users can use SSO-based access from SAS Compute Server to the Azure Databricks database using SPARK LIBNAME engine with AUTH=OAUTH2 option.   The following code describes the SSO-based access from the SAS Compute Server to the Azure Databricks database. Notice that there is no user ID and password but have an option AUTH=OAUTH2 as part of the LIBNAME statement.   Code:   %let MYDBRICKS=adb-3916581250425199.19.azuredatabricks.net; %let MYHTTPPATH=sql/protocolv1/o/3916581250425199/0927-154850-ssq0x1nk; %let MYDRIVERCLASS="cdata.jdbc.databricks.DatabricksDriver"; %let MYCATALOG=hive_metastore; %let MYSCHEMA=default; libname CdtSpark spark platform=databricks driverClass=&MYDRIVERCLASS auth=oauth2 server="&MYDBRICKS" database="&MYSCHEMA" httpPath="&MYHTTPPATH" port=443 bulkload=no character_multiplier=1 dbmax_text=50 properties="Catalog=&MYCATALOG;Other=ConnectRetryWaitTime=20;DefaultColumnSize=1024;" ; Proc SQL outobs=5; select * from CdtSpark.baseball_prqt ; run;quit; data CdtSpark.prdsal2_sas2 ; set sashelp.prdsal2 ; run;   Log:   80 %let MYDBRICKS=adb-3916581250425199.19.azuredatabricks.net; 81 %let MYHTTPPATH=sql/protocolv1/o/3916581250425199/0927-154850-ssq0x1nk; 82 83 %let MYDRIVERCLASS="cdata.jdbc.databricks.DatabricksDriver"; 84 %let MYCATALOG=hive_metastore; 85 %let MYSCHEMA=default; 86 87 libname CdtSpark spark platform=databricks 88 driverClass=&MYDRIVERCLASS 89 auth=oauth2 90 server="&MYDBRICKS" 91 database="&MYSCHEMA" 92 httpPath="&MYHTTPPATH" 93 port=443 94 bulkload=no 95 character_multiplier=1 96 dbmax_text=50 97 properties="Catalog=&MYCATALOG;Other=ConnectRetryWaitTime=20;DefaultColumnSize=1024;" 98 ; NOTE: Libref CDTSPARK was successfully assigned as follows: Engine: SPARK Physical Name: jdbc:cdata:databricks:Server=adb-3916581250425199.19.azuredatabricks.net;Database=default;HTTPPath=sql/protocolv1/o/3916581250 425199/0927-154850-ssq0x1nk;QueryPassthrough=true;UseCloudFetch=true;InitiateOAuth=OFF;AuthScheme=AzureAD;OAuthAccessToken=*** ***;Catalog=hive_metastore;Other=ConnectRetryWaitTime=20;DefaultColumnSize=1024; 99 100 Proc SQL outobs=5; 101 select * from CdtSpark.baseball_prqt ; WARNING: Statement terminated early due to OUTOBS=5 option. 102 run;quit; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: The PROCEDURE SQL printed page 1. NOTE: PROCEDURE SQL used (Total process time): real time 1.66 seconds cpu time 0.07 seconds 103 104 data CdtSpark.prdsal2_sas2 ; 105 set sashelp.prdsal2 ; 106 run; NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables. NOTE: There were 23040 observations read from the data set SASHELP.PRDSAL2. NOTE: The data set CDTSPARK.PRDSAL2_SAS2 has 23040 observations and 11 variables. 107   Results:       SSO-based access from CAS to Azure DataBricks   When the SAS Viya platform is configured with Entra ID OIDC application to provide initial user authentication and Databricks Workspace is configured with Entra ID user access permissions. The SSO-based access can be used to load CAS from the Azure Databricks database using SPARK CASLIB with the AUTH=OAUTH2 option.   The following code describes the SSO-based access from CAS to the Azure Databricks database. Notice that there is no user ID and password but have an option AUTH=OAUTH2 as part of the CASLIB statement.   Code:   %let MYDBRICKS=adb-3916581250425199.19.azuredatabricks.net; %let MYHTTPPATH=sql/protocolv1/o/3916581250425199/0927-154850-ssq0x1nk; %let MYDRIVERCLASS=cdata.jdbc.databricks.DatabricksDriver; %let MYCATALOG=hive_metastore; %let MYSCHEMA=default; CAS mySession SESSOPTS=( CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true); /* SSO based CASLIB to Databricks */ caslib Cdtspkcaslib datasource=(srctype='spark', platform=databricks, auth=oauth2, schema="&MYSCHEMA", server="&MYDBRICKS", httpPath="&MYHTTPPATH", driverclass="&MYDRIVERCLASS", bulkload=no, port=443, useSsl=yes, charMultiplier=1, dbmaxText=50, properties="Catalog=&MYCATALOG;DefaultColumnSize=255;Other=ConnectRetryWaitTime=20" ); /* Load CAS from DataBricks database table */ proc casutil outcaslib="Cdtspkcaslib" incaslib="Cdtspkcaslib" ; load casdata="iot_device" casout="iot_device" replace; list tables; quit; /* Save CAS data to DataBricks database table */ proc casutil outcaslib="Cdtspkcaslib" incaslib="Cdtspkcaslib"; load data=sashelp.cars casout="cars" replace; save casdata="cars" casout="cars_sas" replace; list files; quit; CAS mySession TERMINATE;   Log:   90 /* SSO based CASLIB to Databricks */ 91 caslib Cdtspkcaslib datasource=(srctype='spark', 92 platform=databricks, 93 auth=oauth2, 94 schema="&MYSCHEMA", 95 server="&MYDBRICKS", 96 httpPath="&MYHTTPPATH", 97 driverclass="&MYDRIVERCLASS", 98 bulkload=no, 99 port=443, 100 useSsl=yes, 101 charMultiplier=1, 102 dbmaxText=50, 103 properties="Catalog=&MYCATALOG;DefaultColumnSize=255;Other=ConnectRetryWaitTime=20" 104 ); NOTE: Executing action 'table.addCaslib'. NOTE: 'CDTSPKCASLIB' is now the active caslib. NOTE: Cloud Analytic Services added the caslib 'CDTSPKCASLIB'. 105 106 /* Load CAS from DataBricks database table */ 107 proc casutil outcaslib="Cdtspkcaslib" incaslib="Cdtspkcaslib" ; NOTE: The UUID 'c5e703f1-0786-fe4d-b8df-19f813db3445' is connected using session MYSESSION. 108 load casdata="iot_device" casout="iot_device" replace; NOTE: Executing action 'table.loadTable'. NOTE: Performing serial LoadTable action using SAS Data Connector to Spark. NOTE: Cloud Analytic Services made the external data from iot_device available as table IOT_DEVICE in caslib Cdtspkcaslib. NOTE: Action 'table.loadTable' used (Total process time): 111 112 /* Save CAS data to DataBricks database table */ 113 proc casutil outcaslib="Cdtspkcaslib" incaslib="Cdtspkcaslib"; NOTE: The UUID 'c5e703f1-0786-fe4d-b8df-19f813db3445' is connected using session MYSESSION. 114 load data=sashelp.cars casout="cars" replace; NOTE: The INCASLIB= option is ignored when using the DATA= option in the LOAD statement. NOTE: Executing action 'table.addTable'. NOTE: Action 'table.addTable' used (Total process time): NOTE: SASHELP.CARS was successfully added to the "CDTSPKCASLIB" caslib as "CARS". 115 save casdata="cars" casout="cars_sas" replace; NOTE: Executing action 'table.save'. NOTE: Performing serial SaveTable action using SAS Data Connector to Spark. NOTE: Cloud Analytic Services saved the file cars_sas in caslib CDTSPKCASLIB. NOTE: Action 'table.save' used (Total process time):   Results:     Important Links:   Authenticate to Databricks on Microsoft Azure by Using Single Sign-On Scenario: OIDC with Microsoft Entra ID (Azure Active Directory) LIBNAME Statement for Spark     Find more articles from SAS Global Enablement and Learning here. ... View more

With the SAS Viya Cloud Data Exchange (CDE) environment, the MS-SQL Server data source is one of the widely used data sources. The CDE Data Agent supports the MS-SQL Server database access. The Remote Data Agent can access the MS-SQL Server database using an ODBC driver connection. The MS-SQL Server ODBC driver is provided with the SAS Viya and Remote Data Agent deployment. The CDE administrator must prepare an odbc.ini with MS-SQL Server connection details and place it at a location mounted to the Data Agent container. An administrator can configure the MS-SQL Server data source at both (Remote and Co-located) Data Agents.   In this post, I discuss the configuration of the MS-SQL Server data source at the Remote Data agent.   The Remote Data Agent is a containerized software and runs with docker runtime. The CDE administrator can mount an NFS location with an odbc.ini file while starting the Remote Data Agent container.   The following pics describe the local/NFS folder with an odbc.ini file mounted to the Remote Data Agent container and access to the MS-SQL Server database.     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 following steps describe the MS-SQL Server data source configuration at the Remote Data Agent Server.   Prepare an odbc.ini file with MS-SQL Server connection details The CDE connection to the MS-SQL Server is an ODBC connection. The MS-SQL ODBC driver is provided with the SAS Viya and Remote Data Agent deployment. There is no need for an additional Unix ODBC driver or MS-SQL Server driver. An administrator must prepare an odbc.ini file with the SQL Server database information and place it in a location (mounted) reachable from the Remote Data Agent container. The folder and files containing the odbc.ini file should have read permission for user “sas” (uid 1001:1001). The following is an odbc.ini example, you can customize it before using it in your environment.   Code:   RDA_HOST=`hostname ` ; echo $RDA_HOST ; tee /viya-share/gelenv/config/access-clients/odbc/odbc.ini > /dev/null << EOF [rmtsql] Driver=/opt/sas/viya/home/lib64/accessclients/lib/S0sqls28.so Description=SAS ACCESS to MS SQL Server Address=$RDA_HOST,1433 AlternateServers= AnsiNPW=Yes ConnectionRetryCount=0 ConnectionRetryDelay=3 Database= FetchTSWTZasTimestamp=0 FetchTWFSasTime=0 LoadBalancing=0 LogonID= Password= QuotedId=Yes ReportCodepageConversionErrors=0 ReportDateTimeType=1 SnapshotSerializable=0 QEWSD=2457822 EOF sudo chown 1001:1001 -R /viya-share/gelenv/config/access-clients/odbc;   Update the sas-access.properties with the MS-SQL Server environment variable The sas-access.properties file is meant for SAS/ACCESS engine-related environment variables. Users can include and update the respective access engine environment variables. The following variable is related to the MS-0SQL ODBC client.   Code:   ODBCINI=/gelenv/config/access-clients/odbc/odbc.ini   Start the Remote Data Agent service with the odbc.ini location mounted Start the RDA service with the local data folder (e.g. /viya-share/gelenv) mounted to the RDA container. The mounted folder contains the odbc folder odbc.ini file.   Code:   ./container-manager start \ --license-path SASViyaV4_9CV11D_license.jwt \ --sasdata sasdata \ --vars da-vars.env \ --data sasdata/data \ --mount /viya-share/gelenv:/gelenv \ --access-vars sas-access.properties \ sas-data-agent-server-remote   Log in and authenticate a user for sas-viya CLI The sas-viya cli is the user interface to manage and maintain the configurations at the SAS Cloud Data Exchange. An administrator must log in and authenticate a user for sas-viya CLI before creating data source connections.   Code:   export GELLOW_NAMESPACE=gelenv export SAS_CLI_PROFILE=${GELLOW_NAMESPACE:-Default} ; export SSL_CERT_FILE=~/.certs/${GELLOW_NAMESPACE}_trustedcerts.pem ; sas-viya -k auth login --user geldmui@gelenable.sas.com --password '!wQ0eXXXXXXX$T@c' ; sas-viya profile set-output text ;   Create an MS-SQL Security Domain at the RDA server Create an MS-SQL domain on the RDA server to keep the SAS Viya user identity and database credentials in pairs.   Code:   sas-viya dagentsrv servers set-default --data-agent sas-data-agent-server-remote sas-viya dagentsrv security domains create --domain MSSQLDOM1 sas-viya dagentsrv security domains list   Log:   jumpuser@utkuma-p04215-jump-vm:~$ sas-viya dagentsrv security domains create --domain MSSQLDOM1 The domain was successfully created. jumpuser@utkuma-p04215-jump-vm:~/CDERemoteDA_Deploy$ jumpuser@utkuma-p04215-jump-vm:~/CDERemoteDA_Deploy$ sas-viya dagentsrv security domains list ID Type Label Description kerberos GSSCredential Kerberos credentials captured by logon and cached for delegation DefaultAuth password The default authentication domain for SAS Viya EsriAuth password EsriAuth Esri authentication credential domain EsriPortalAuth password EsriPortalAuth Local Esri portal authentication credential domain EsriPortalRefreshToken token EsriPortalRefreshToken Esri portal OAuth2 refresh credential domain azure_oidc oauth2.0 azure_oidc MSSQLDOM1 shadow jumpuser@utkuma-p04215-jump-vm:~$   Add Viya user identity to MS-SQL Security Domain at RDA server Add the SAS Viya user identity with the MS-SQL Server database user ID and password to access the database objects from the SAS Viya applications. The MS-SQL Server database user password used in the following statement is a one-time user credentials setup. The Password of the database user never leaves the on-premises Remote Data Agent Server. It resides in the SAS Secrets Manager (Vault) at the Remote Data Agent Server.   Code:   ### sastest1@gelenable.sas.com - SAS Viya user id. ### ### SA – MS-SQL Server database user ### ### xxxxxxxxxx – MS-SQL Server database password for user SA ### sas-viya dagentsrv security credentials create --domain MSSQLDOM1 --identity sastest1@gelenable.sas.com --username SA --password 'xxxxxxxxxx' sas-viya dagentsrv security credentials create --domain MSSQLDOM1 --identity sasadm@gelenable.sas.com --username SA --password 'xxxxxxxxxx' sas-viya dagentsrv security credentials create --domain MSSQLDOM1 --identity geldmui@gelenable.sas.com --username SA --password 'xxxxxxxxxx' sas-viya dagentsrv security credentials list --domain MSSQLDOM1   Log:   jumpuser@utkuma-p04215-jump-vm:~$ sas-viya dagentsrv security credentials list --domain MSSQLDOM1 Identity Username Identity Type sastest1@gelenable.sas.com SA user geldmui@gelenable.sas.com SA user sasadm@gelenable.sas.com SA user jumpuser@utkuma-p04215-jump-vm:~$   Create an MS-SQL Server data source The following statement creates an MS-SQL Server data source service with the ODBC DSN name defined in the odbc.ini. The statement uses the MS-SQL security domain name and has the database user-id password saved with the SAS Viya user identity.   Code:    sas-viya dagentsrv data-services create sqlsvr --name mssqlsrv1 --driver odbc --dsn rmtsql --domain MSSQLDOM1 --register-all sas-viya dagentsrv services list   Log:   jumpuser@utkuma-p04215-jump-vm:~$ sas-viya dagentsrv services list Name Type Domain Version Options BASE base --- 2.6 --- __SERVER__ server --- 2.6 PURGE_CACHE=30;CASE_SENSITIVITY=(OBJECT=F;COLUMN=F);CACHE=(NAME=AS;TIMEOUT=300) mssqlsrv1 sqlserver MSSQLDOM1 2.6 CONOPTS=(DRIVER=odbc;ODBC_DSN=rmtsql);CASE_SENSITIVITY=(OBJECT=F;COLUMN=F) jumpuser@utkuma-p04215-jump-vm:~$   Test the MS-SQL Server data source When the following statement is executed, log in to the MS-SQL Server database using the Viya identity user to fetch the database password from the MS-SQL security domain and SAS security vault. The Viya identity user is authenticated against the Viya platform using the sas-viya CLI.   Code:   sas-viya dagentsrv data-sources test --name mssqlsrv1   Log:   jumpuser@utkuma-p04215-jump-vm:~$ sas-viya dagentsrv data-sources test --name mssqlsrv1 Successfully connected to data source mssqlsrv1. jumpuser@utkuma-p04215-jump-vm:~$   List MS-SQL Server database table and data from MS-SQL data source With a successful test connection to an MS-SQL data source, the user can list the data table and data from the MS-SQL Server database using the sas-viya CLI.   Code:   sas-viya dagentsrv data-sources tables list --data-source mssqlsrv1 --catalog geldm --schema dbo sas-viya dagentsrv sql --sql "select * from geldm.dbo.test_table " --dsn mssqlsrv1   Log:   jumpuser@utkuma-p04215-jump-vm:~$ sas-viya dagentsrv data-sources tables list --data-source mssqlsrv1 --catalog geldm --schema dbo Name Type Native Catalog test_table TABLE geldm jumpuser@utkuma-p04215-jump-vm:~$ jumpuser@utkuma-p04215-jump-vm:~$ sas-viya dagentsrv sql --sql "select * from geldm.dbo.test_table " --dsn mssqlsrv1 id name value == ==== ===== 1 Pen 100 2 Pencil 200 3 Book 300 4 Notebook 400 jumpuser@utkuma-p04215-jump-vm:~$   SAS Access to MS-SQL Data Source via Remote Data Agent With the MS-SQL data source configured at the Remote Data Agent, users can access MS-SQL Server database tables from the SAS Compute Server using the CDE LIBNAME statement. The following code describes the access to an MS-SQL Server database table via Remote Data Agent data source.   Code:   LIBNAME cdems1 cde dataagentname="sas-data-agent-server-remote" dsn=mssqlsrv1 catalog=geldm schema=dbo preserve_tab_names=yes; data cdems1.FISH_SAS ; set sashelp.fish ; run; Proc SQL outobs=20; select * from cdems1.FISH_SAS ; run;quit;   Log:   81 LIBNAME cdems1 cde dataagentname="sas-data-agent-server-remote" 82 dsn=mssqlsrv1 catalog=geldm schema=dbo preserve_tab_names=yes; NOTE: Libref CDEMS1 was successfully assigned as follows: Engine: CDE Physical Name: mssqlsrv1 84 data cdems1.FISH_SAS ; 85 set sashelp.fish ; 86 run; NOTE: There were 159 observations read from the data set SASHELP.FISH. NOTE: The data set CDEMS1.FISH_SAS has 159 observations and 7 variables. NOTE: DATA statement used (Total process time): real time 1.65 seconds cpu time 0.12 seconds 87   CAS Access to MS-SQL Server Data Source via Remote Data Agent With the MS-SQL data source configured at the Remote Data Agent, users can access MS-SQL Server database tables from the CAS using the CDE data connector. The following code describes the access to an MS-SQL Server database table via Remote Data Agent data source using CDE CASLIB.   Code:   CAS mySession SESSOPTS=(CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true); caslib cdems datasource=( srctype="clouddex", dataAgentName="sas-data-agent-server-remote", catalog="geldm", schema="dbo", conopts="dsn=mssqlsrv1" ) libref=cdems; proc casutil incaslib="cdems"; list files ; run; quit; /* CAS load from CDE RDA MS-SQL table */ proc casutil incaslib="cdems" outcaslib="cdems"; load casdata="TEST_TABLE" casout="TEST_TABLE" replace ; list tables ; run; quit; /* Save CAS table to RDA MS-SQL database */ proc casutil incaslib="cdems" outcaslib="cdems" ; droptable casdata="SAS_CARS" quiet ; load data=sashelp.cars casout="SAS_CARS" ; save casdata="SAS_CARS" casout="SAS_CARS" replace ; list files ; quit ; cas mysession terminate;   Log:   81 82 caslib cdems datasource=( 83 srctype="clouddex", 84 dataAgentName="sas-data-agent-server-remote", 85 catalog="geldm", 86 schema="dbo", 87 conopts="dsn=mssqlsrv1" 88 ) libref=cdems; NOTE: Executing action 'table.addCaslib'. NOTE: 'CDEMS' is now the active caslib. NOTE: Cloud Analytic Services added the caslib 'CDEMS'. NOTE: Action 'table.addCaslib' used (Total process time): NOTE: real time 0.011011 seconds 94 95 /* CAS load from CDE RDA MS-SQL table */ 96 proc casutil incaslib="cdems" outcaslib="cdems"; NOTE: The UUID '0249bc7c-a935-124e-98f3-a8a8f3c3f622' is connected using session MYSESSION. 97 load casdata="TEST_TABLE" casout="TEST_TABLE" replace ; NOTE: Executing action 'table.loadTable'. NOTE: Connecting using the OAuth token for CAS user 'geldmui@gelenable.sas.com'. WARNING: Using server default session timeout of 3600 NOTE: Cloud Analytic Services made the external data from TEST_TABLE available as table TEST_TABLE in caslib cdems. NOTE: Action 'table.loadTable' used (Total process time): NOTE: real time 0.831338 seconds NOTE: SASHELP.CARS was successfully added to the "CDEMS" caslib as "SAS_CARS". 106 save casdata="SAS_CARS" casout="SAS_CARS" replace ; NOTE: Executing action 'table.save'. NOTE: Performing serial SaveTable action using SAS Data Connector to Cloud Data Exchange. NOTE: Connecting using the OAuth token for CAS user 'geldmui@gelenable.sas.com'. WARNING: Using server default session timeout of 3600 NOTE: Cloud Analytic Services saved the file SAS_CARS in caslib CDEMS. NOTE: Action 'table.save' used (Total process time): NOTE: real time 7.707428 seconds    Many thanks to Brian Hess, @bhess, for sharing the expert knowledge and help on this post.   Important Links: Remote SAS Data Agent: Deployment and Administration Guide   Posts: SAS Cloud Data Exchange for the SAS Viya Platform SAS Viya Cloud Data Exchange Deployment and Configuration (Part -1) SAS Viya Cloud Data Exchange Deployment and Configuration (Part -2) Configuring BASE SAS Data Source at Remote Data Agent (Cloud Data Exchange) Configuring Oracle Data Source at Remote Data Agent (Cloud Data Exchange) Configuring SAS Federated Data Source at Remote Data Agent (Cloud Data Exchange) Configuring DB2 Data Source at Remote Data Agent (Cloud Data Exchange)     Find more articles from SAS Global Enablement and Learning here. ... View more

With the SAS Viya 2023.08 stable release and onwards, the Cloud Data Exchange (CDE) ODBC driver for Windows is available for SAS Viya users to securely access the on-premises and cloud storage data from third-party applications. Users can deploy and configure the CDE ODBC driver for Windows at a client application server and securely access the data via the CDE Remote (RDA) and Co-located (CDA) Data Agent Server. The CDE ODBC driver is a 64-bit Windows platform driver.   In this post, I discuss the deployment and configuration of the CDE ODBC driver at the client machine and access to the on-premises and cloud storage data.   The CDE ODBC driver enables users to leverage the CDE components for third-party applications. The third-party applications connect to CDE RDA using ODBC driver to access data behind the on-premises firewall.   The following pics describe the client machine having a third-party application with the CDE ODBC driver and accessing the on-premises and cloud storage data via RDA and CDA.     Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Pre-Requisite   The SAS Viya stable-2023.08 or above environment including Remote Data Agent Server. ODBC driver for CDE deployed at the client machine having third-party applications. ODBC data source configured at the client machine against the CDE RDA and CDA servers.   How to download the CDE ODBC driver for Windows?   The CDE ODBC driver for Windows can be downloaded from support.sas.com using the SAS user profile.   https://support.sas.com/downloads/browse.htm?fil=&cat=40        How to deploy CDE ODBC driver on the client machine   Start an admin DOS prompt on the Windows client machine and execute the .msi file using the following statement to deploy the CDE ODBC driver.   Code:   C:\Users\student\Downloads>msiexec /i SASCDEODBCLibrariesInstaller.msi   How to configure ODBC data source on the client machine   With the ODBC Data source configuration manager, create the system data source connection on the client machine using the data source name, server name, port, and schema information from the Remote and Co-located Data Agent server. The SAS Viya user ID and password are used in the ODBC data source configuration to log in to RDA, and CDA.   While configuring the ODBC driver and connection, the CDA and RDA must be reachable from the client machine over the network and security firewall.   The following pics and steps describe the ODBC data source connection against the Co-located Data Agent (CDA) server. At the client machine, start the ODBC Data Source manager with the administrator user.     Use the Add option to create a new system DSN connection at the client machine. With CDE ODBC deployed at the client machine, you will have an option to use it.     In the SAS Cloud Data Exchange Server DSN setup window, the Server name, Port, and Schema name are used from the Co-located Data Agent server. The SAS Viya user ID and password are used in the configuration to log in to CDA.     When the Remote Data Agent server is available over the firewall and network from the client machine, a user can also configure SAS Cloud Data Exchange Server ODBC DSN against the Remote Data (RDA) server. The following pics describe the ODBC DSN configuration against the RDA server.     Third-party application access to CDA and RDA data sources   The third-party application that supports the Win ODBC driver can use the CDE ODBC data source name to access the data in their environment. The following pics describe a client application Power-BI accessing data from the cloud storage data using CDA.     With the CDECDA ODBC data source configured at the client machine the application Power-BI has an option to choose the data source.     Very first-time the Power-BI application may ask username and password to log in to the CDA environment through the CDECDA ODBC data source.     The Power-BI application data navigator will list the available data table from the schema for the user’s report.     Based on the selected data user can prepare the desired Power BI Report.     Summary: The CDE ODBC driver enables SAS Viya users to let a third-party application also use the SAS data sets via the Cloud Data Exchange environment.   Important Links:   Remote SAS Data Agent: Deployment and Administration Guide   Post: SAS Cloud Data Exchange for the SAS Viya Platform. SAS Viya Cloud Data Exchange Deployment and Configuration (Part -1) SAS Viya Cloud Data Exchange Deployment and Configuration (Part -2) Configuring BASE SAS Data Source at Remote Data Agent (Cloud Data Exchange) Configuring Oracle Data Source at Remote Data Agent (Cloud Data Exchange)  Configuring SAS Federated Data Source at Remote Data Agent (Cloud Data Exchange) Configuring DB2 Data Source at Remote Data Agent (Cloud Data Exchange)     Find more articles from SAS Global Enablement and Learning here. ... View more

In the SAS Viya Cloud Data Exchange (CDE) environment, the Remote Data Agent supports the DB2 database access as well along with other databases. The DB2 client configuration at the On-Premises server is a vital task in the process of DB2 data source configuration. The CDE administrator is required to deploy a compatible DB2 client to the location mounted with the Data Agent container.   In this post, I discuss the configuration of the DB2 data source at the Remote Data agent.   The Remote Data Agent is a containerized software and runs with docker runtime. The CDE administrator can mount an NFS location with the DB2 client deployed while starting the Remote Data Agent container.   The following pics describe the local/NFS folder with the DB2 client mounted to the Remote Data Agent container and access to the DB2 database.   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 following steps describe the local/NFS folder with the DB2 client configuration and the Remote Data Agent mount to the same location with the DB2 data source configuration.   Deploy Compatible DB2 Client   The CDE administrator is required to deploy a compatible DB2 client to a location reachable from the remote data agent server. The DB2 client compatibility should be tested against the DB2 database server.   The DB2 client can be downloaded by using the instructions listed at the site https://www.ibm.com/docs/en/db2/11.5?topic=clients-installing-data-server-unix-linux.   The download file is a compressed JAR file. It needs to be uncompressed on Unix server as user “sas” (uid 1001:1001). Place the compressed file “v11.5.8_linuxx64_client.tar.gz” under /tmp folder to execute the following statement.   Code:   sudo su – sas cd tar -xvf /tmp/v*linuxx64_client.tar.gz ./client/db2_install -b $HOME/sqllib -f sysreq -y   Note: -f sysreq is used to not check the minimum requirements on the server   Configure and Validate DB2 Client against DB2 Database   Source the DB2 client profile on Unix server as user “sas” (uid 1001:1001).   Code:   . $HOME/sqllib/db2profile Create a DB2 database DSN profile under client configuration on the Unix server as user “sas” (uid 1001:1001).   Code:   db2cli writecfg add -database sample -host $(hostname -f) -port 50000 db2cli writecfg add -dsn sampledsn -database sample -host $(hostname -f) -port 50000 cat $HOME/sqllib/cfg/db2dsdriver.cfg Validate the access to DB2 DSN using db2cli on the Unix server as user “sas” (uid 1001:1001).   Code: db2cli validate -dsn sampledsn -connect -user db2inst1 -passwd   Log:   sas@p04206-jump-vm:~$ db2cli validate -dsn sampledsn -connect -user db2inst1 -passwd XXXXX ====================================================================== Client information for the current copy: ====================================================================== Client Package Type : IBM Data Server Client Client Version (level/bit): DB2 v11.5.8.0 (s2209201700/64-bit) Client Platform : Linux/X8664 Install/Instance Path : /home/sas/sqllib …. …. … ====================================================================== Connection attempt for data source name "sampledsn": ====================================================================== [SUCCESS] ====================================================================== The validation is completed. ====================================================================== sas@p04206-jump-vm:~$   Make the DB2 Client Accessible from the CDE RDA container   Prepare a folder structure as user “sas” at the shared location available to the RDA container to copy the customized DB2 client files.   Code:   mkdir -p /viya-share/gelenv/config/access-clients/db2client mkdir -p /viya-share/gelenv/config/access-clients/db2 ls -l /viya-share/gelenv/config/access-clients/   Copy the DB2 Client files as user “sas” from the home folder to the shared location available to the RDA container.   Code:   cp -R -L $HOME/sqllib /viya-share/gelenv/config/access-clients/db2client/sqllib chmod -R 755 /viya-share/gelenv/config/access-clients/db2client/   Update the db2profile as user “sas” with new paths at the shared location.   Code:   export DB2_NET_CLIENT_PATH=/viya-share/gelenv/config/access-clients/db2client/sqllib sed -i 's|^DB2DIR=.*|DB2DIR='$DB2_NET_CLIENT_PATH'|g' $DB2_NET_CLIENT_PATH/db2profile sed -i 's|^INSTHOME=.*|INSTHOME=/viya-share/gelenv/config/access-clients/db2|g' $DB2_NET_CLIENT_PATH/db2profile # DB2INSTANCE should already be set to the user who installed the client (sas for us) # DB2INSTANCE=   Copy the db2profile to the shared location db2 folder.   Code:   export DB2_NET_CLIENT_PATH=/viya-share/gelenv/config/access-clients/db2client/sqllib source $DB2_NET_CLIENT_PATH/db2profile export DB2_APPL_DATA_PATH=/viya-share/gelenv/config/access-clients/db2 export DB2_APPL_CFG_PATH=/viya-share/gelenv/config/access-clients/db2 $DB2_NET_CLIENT_PATH/bin/db2ccprf -f -t /viya-share/gelenv/config/access-clients/db2 chmod -R 755 /viya-share/gelenv/config/access-clients/db2   Update the sas-access.properties with the DB2 environment Variable   The sas-access.properties file is meant for SAS/ACCESS engine-related environment variables. Users can include and update the respective access engine environment variables. The following variable is related to the DB2 client location and configuration.   Code:   cd ~/CDERemoteDA_Deploy DB2CLIENTPATH=/viya-share/gelenv/config/access-clients/db2client/sqllib DB2PATH=/viya-share/gelenv/config/access-clients/db2 tee sas-access-db2.properties > /dev/null << EOF ########################## # SAS/ACCESS to DB2 ########################## CUR_INSTHOME= CUR_INSTNAME= DASWORKDIR= DB2DIR=${DB2CLIENTPATH} DB2INSTANCE=sas DB2LIB=${DB2CLIENTPATH} DB2_HOME=${DB2CLIENTPATH} DB2_NET_CLIENT_PATH=${DB2CLIENTPATH} IBM_DB_DIR=${DB2CLIENTPATH} IBM_DB_HOME=${DB2CLIENTPATH} IBM_DB_INCLUDE=${DB2CLIENTPATH} IBM_DB_LIB=${DB2CLIENTPATH} INSTHOME=${DB2PATH} INST_DIR=${DB2CLIENTPATH} PREV_DB2_PATH= DB2=${DB2CLIENTPATH}/lib64:${DB2CLIENTPATH}/lib64/gskit:${DB2CLIENTPATH}/lib32 DB2_BIN=${DB2CLIENTPATH}/bin:${DB2CLIENTPATH}/adm:${DB2CLIENTPATH}/misc EOF more sas-access-db2.properties cat sas-access-db2.properties >> sas-access.properties   Start the RDA service with the DB2 client location folder   Start the RDA service with the local shared folder (e.g. /viya-share/gelenv) mounted to the RDA container. The mounted folder contains the DB2 client and configuration files.   Code:   ./container-manager start \ --license-path SASViyaV4_9CV11D_license.jwt \ --sasdata sasdata \ --vars da-vars.env \ --data sasdata/data \ --mount /viya-share/gelenv:/viya-share/gelenv \ --access-vars sas-access.properties \ sas-data-agent-server-remote   Create a DB2 Security Domain at RDA server   Create a DB2 security domain on the RDA server to keep the SAS Viya user identity and DB2 database credentials in pairs.   Code:   sas-viya dagentsrv servers set-default --data-agent sas-data-agent-server-remote sas-viya dagentsrv security domains create --domain DB2DOM1 sas-viya dagentsrv security domains list   Log:   jumpuser@p04206-jump-vm:~/CDERemoteDA_Deploy$ sas-viya dagentsrv security domains create --domain DB2DOM1 The domain was successfully created. jumpuser@utkuma-p04206-jump-vm:~/CDERemoteDA_Deploy$ jumpuser@p04206-jump-vm:~/CDERemoteDA_Deploy$ sas-viya dagentsrv security domains list ID Type Label Description kerberos GSSCredential Kerberos credentials captured by logon and cached for delegation DefaultAuth password The default authentication domain for SAS Viya EsriAuth password EsriAuth Esri authentication credential domain sas.naturalLanguageConversations.connectors connectorDomain EsriPortalAuth password EsriPortalAuth Local Esri portal authentication credential domain EsriPortalRefreshToken token EsriPortalRefreshToken Esri portal OAuth2 refresh credential domain DB2DOM1 shadow jumpuser@p04206-jump-vm:~/CDERemoteDA_Deploy$   • Add Viya user identity to DB2 Security Domain at RDA server   Add the SAS Viya user identity with the Oracle database user ID and password to access the database objects from the SAS Viya applications. The Oracle database user password used in the following statement is a one-time user credentials setup. The Password of the database user never leaves the on-premises Remote Data Agent Server. It resides in the SAS Secrets Manager (Vault) at the Remote Data Agent Server.   Code:   ### gatedemo001@gelenable.sas.com - SAS Viya user id. ### ### db2inst1 – DB2 database user ### ### xxxxxxxxxx – DB2 database password for user db2inst1 ### sas-viya dagentsrv security credentials create --domain DB2DOM1 --identity gatedemo001@gelenable.sas.com --username db2inst1 --password ‘xxxxxxx’ sas-viya dagentsrv security credentials create --domain DB2DOM1 --identity sasadm@gelenable.sas.com --username db2inst1 --password 'xxxxxxxxxx' sas-viya dagentsrv security credentials create --domain DB2DOM1 --identity geldmui@gelenable.sas.com --username db2inst1 --password 'xxxxxxxxxx' sas-viya dagentsrv security credentials list --domain DB2DOM1   Log:   jumpuser@p04206-jump-vm:~/CDERemoteDA_Deploy$ sas-viya dagentsrv security credentials list --domain DB2DOM1 Identity Type UserId gatedemo001@gelenable.sas.com user db2inst1 geldmui@gelenable.sas.com user db2inst1 sasadm@gelenable.sas.com user db2inst1 jumpuser@p04206-jump-vm:~/CDERemoteDA_Deploy$   Create a DB2 data source at RDA   The following statement creates a DB2 data service at the Remote Data Agent using DB2 DSN name as a database name. The statement uses the DB2 domain name which has the database user-id password saved with the SAS Viya user identity.   Code:   sas-viya dagentsrv data-services create db2 --name db2srv12 --domain DB2DOM1 --database-name sampledsn sas-viya dagentsrv services list   Log:   jumpuser-p04206-jump-vm:~$ sas-viya dagentsrv data-services create db2 --name db2srv12 --domain DB2DOM1 --database-name sampledsn The data service was successfully created. jumpuser@p04206-jump-vm:~$ jumpuser@p04206-jump-vm:~$ sas-viya dagentsrv services list Name Type Domain Version Options BASE base --- 2.5 --- __SERVER__ server --- 2.5 PURGE_CACHE=30;CASE_SENSITIVITY=(OBJECT=F;COLUMN=F);CACHE=(NAME=AS;TIMEOUT=300) db2srv12 db2 DB2DOM1 2.5 CONOPTS=(DRIVER=DB2;DATABASE=sampledsn);CASE_SENSITIVITY=(OBJECT=T;COLUMN=T) jumpuser@p04206-jump-vm:~$   Test the DB2 Remote Data Agent data source   In this case, the login to the DB2 database is using the Viya identity user to fetch the database password from the DB2 security domain and SAS security vault. The Viya identity user is authenticated against the Viya platform using the sas-viya CLI.   Code:   sas-viya dagentsrv data-sources test --name db2srv12   Log:   jumpuser@04206-jump-vm:~$ sas-viya dagentsrv data-sources test --name db2srv12 Successfully connected to data source db2srv12. jumpuser@p04206-jump-vm:~$   List the DB2 database table and data from a DB2 data source table   With a successful test connection to the DB2 data source, the user can list the data table and data from the DB2 database using the sas-viya CLI.   Code:   sas-viya dagentsrv data-sources tables list --data-source db2srv12 --catalog db2srv12 --schema DB2INST1 sas-viya dagentsrv sql --sql "select * from DB2INST1.department " --dsn db2srv12   Log:   jumpuser@p04206-jump-vm:~$ sas-viya dagentsrv data-sources tables list --data-source db2srv12 --catalog db2srv12 --schema DB2INST1 Name Type Native Catalog ACT TABLE NULL ADEFUSR MATERIALIZED QUERY TABLE NULL CL_SCHED TABLE NULL DEPARTMENT TABLE NULL DEPT ALIAS NULL EMP ALIAS NULL EMP_ACT ALIAS NULL … ….. … jumpuser@p04206-jump-vm:~$ sas-viya dagentsrv sql --sql "select * from DB2INST1.department " --dsn db2srv12 ADMRDEPT DEPTNAME DEPTNO LOCATION MGRNO ======== ======== ====== ======== ===== A00 SPIFFY COMPUTER SERVICE DIV. A00 000010 A00 PLANNING B01 000020 A00 INFORMATION CENTER C01 000030 A00 DEVELOPMENT CENTER D01 D01 MANUFACTURING SYSTEMS D11 000060 D01 ADMINISTRATION SYSTEMS D21 000070 …. ….. jumpuser@p04206-jump-vm:~$   SAS Access to DB2 Data Source at Remote Data Agent (RDA)   With the DB2 data source configured at the Remote Data Agent, users can access the DB2 data tables from the SAS Compute Server using the CDE LIBNAME statement. The following code describes the access of a DB2 database table from a DB2 RDA data source.   Code:   LIBNAME cdedb2 cde dataagentname="sas-data-agent-server-remote" dsn=db2srv12 preserve_tab_names=yes; data cdedb2.FISH_SAS ; set sashelp.fish ; run; Proc SQL outobs=20; select * from cdedb2.FISH_SAS ; run;quit;   Log:   79 80 LIBNAME cdedb2 cde dataagentname="sas-data-agent-server-remote" 81 dsn=db2srv12 preserve_tab_names=yes; NOTE: Libref CDEDB2 was successfully assigned as follows: Engine: CDE Physical Name: db2srv12 82 83 80 81 data cdedb2.FISH_SAS ; 82 set sashelp.fish ; 83 run; NOTE: There were 159 observations read from the data set SASHELP.FISH. NOTE: The data set CDEDB2.FISH_SAS has 159 observations and 7 variables. NOTE: DATA statement used (Total process time): real time 1.41 seconds cpu time 0.10 seconds 84 85 80 Proc SQL outobs=20; 81 select * from cdedb2.FISH_SAS ; WARNING: Statement terminated early due to OUTOBS=20 option. 82 run;quit; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: The PROCEDURE SQL printed page 1. NOTE: PROCEDURE SQL used (Total process time): real time 0.16 seconds cpu time 0.05 seconds 83   CAS Access to DB2 Data Source at Remote Data Agent   With the DB2 data source configured at the Remote Data Agent, users can access the DB2 data tables from the CAS using the CDE data connector. The following code describes the CAS access to the DB2 table from a DB2 data source using the CDE CASLIB statement.   Code:   CAS mySession SESSOPTS=(CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true); caslib cdedb12 datasource=( srctype="clouddex", dataAgentName="sas-data-agent-server-remote", catalog="DB2SRV12", schema="DB2INST1", conopts="dsn=db2srv12" ) libref=cdedb12; proc casutil incaslib="cdedb12"; list files ; run;quit; /* CAS load from CDE RDA DB2 table */ proc casutil incaslib="cdedb12" outcaslib="cdedb12"; load casdata="DEPT" casout="DEPT" replace ; list tables ; run;quit; /* Save CAS table to RDA DB2 database */ proc casutil incaslib="cdedb12" outcaslib="cdedb12" ; droptable casdata="SAS_CARS" quiet ; load data=sashelp.cars casout="SAS_CARS" ; save casdata="SAS_CARS" casout="SAS_CARS" replace ; list files ; quit ; cas mysession terminate;   Log:   81 82 caslib cdedb12 datasource=( 83 srctype="clouddex", 84 dataAgentName="sas-data-agent-server-remote", 85 catalog="DB2SRV12", 86 schema="DB2INST1", 87 conopts="dsn=db2srv12" 88 ) libref=cdedb12; NOTE: Executing action 'table.addCaslib'. NOTE: 'CDEDB12' is now the active caslib. NOTE: Cloud Analytic Services added the caslib 'CDEDB12'. NOTE: Action 'table.addCaslib' used (Total process time): NOTE: real time 0.011457 seconds 80 /* CAS load from CDE RDA DB2 table */ 81 proc casutil incaslib="cdedb12" outcaslib="cdedb12"; NOTE: The UUID 'a2d24d50-926b-184c-90a0-756067585be5' is connected using session MYSESSION. 82 load casdata="DEPT" casout="DEPT" replace ; NOTE: Executing action 'table.loadTable'. NOTE: Connecting using the OAuth token for CAS user 'geldmui@gelenable.sas.com'. WARNING: Using server default session timeout of 3600 NOTE: Cloud Analytic Services made the external data from DEPT available as table DEPT in caslib cdedb12. NOTE: Action 'table.loadTable' used (Total process time): NOTE: real time 0.902593 seconds NOTE: SASHELP.CARS was successfully added to the "CDEDB12" caslib as "SAS_CARS". 84 save casdata="SAS_CARS" casout="SAS_CARS" replace ; NOTE: Executing action 'table.save'. NOTE: Performing serial SaveTable action using SAS Data Connector to Cloud Data Exchange. NOTE: Connecting using the OAuth token for CAS user 'geldmui@gelenable.sas.com'. WARNING: Using server default session timeout of 3600 NOTE: Cloud Analytic Services saved the file SAS_CARS in caslib CDEDB12. NOTE: Action 'table.save' used (Total process time): NOTE: real time 3.448311 seconds   Important Links:   Cloud Data Exchange for the SAS Viya Platform   Cloud Data Exchange for the SAS Viya Platform SAS Viya Cloud Data Exchange Deployment and Configuration (Part -1) SAS Viya Cloud Data Exchange Deployment and Configuration (Part -2) Configuring BASE SAS Data Source at Remote Data Agent (Cloud Data Exchange) Configuring Oracle Data Source at Remote Data Agent (Cloud Data Exchange) Configuring SAS Federated Data Source at Remote Data Agent (Cloud Data Exchange)   Find more articles from SAS Global Enablement and Learning here. ... View more

In the 1st part of this series, I talked about having an Azure Storage Account (ADLS2) with NFS 3.0 protocol, Hierarchical namespace, and Azure Blob Storage CSI driver enabled on the AKS cluster, you can mount the Blob Storage to AKS Pods. You can create a Persistent Volume (PV) and Persistence Volume Claim (PVC) against Azure Blob Storage using the azureblob-nfs-premium storage class and mount it to the SAS Compute Pod and CAS Pods to access the sas7bdat data files.   In the second part of the series, I talk about the configuration for Azure Storage Account and AKS to NFS mount the Compute and CAS pod to Azure Blob storage.     Pre-requisite   An Azure Storage Account with Hierarchical namespace enabled. Azure Storage Account with NFS 3.0 Protocol enabled. AKS cluster with Azure Blob Storage CSI driver enabled. AKS cluster with NFS 3.0 or above Protocol. Storage Account network access to AKS VNet and Subnet. Azure CLI Ver 2.48 and onwards.   The following picture describes the SAS Compute and CAS Pod mount to Azure Blob Storage.    Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.     Storage Account configuration   In this process, you can use either a Standard or Premium ADLS2 Storage Account. The Premium Storage account has better performance. When creating an Azure Storage Account, include the Hierarchical namespace and NFS 3.0 Protocol. Also, making it part of the AKS VNet and Subnet network, means it allows the network traffic from the AKS cluster. The ACL of the Blob Storage folder with read/write permission for the required user group and users.   The Azure Storage Account (ADLS2) with Hierarchical Name Space, NFS 3. 0 protocol, and selected network (AKS VNet and Subnet) access.     The Azure Storage Account (ADLS2) network access to AKS Vnet and subnet Network.     The ACL for specific Blob folders with read and write permission. Provision the required read/write permission for user groups and users to the Blob storage folder.       AKS Cluster configuration   The AKS cluster must support NFS 3.0 and above protocol with Azure Blob CSI drive installed.   If required, you can enable the Azure Blob CSI driver and storage class using the following statement. To execute the following update statement, you must have Azure CLI Ver 2.48 and above.   Code: az account set --subscription "PSGEL286 SAS Viya 4: Data Management on Azure Cloud" RG_NAME=mydata_rg AKSCLUSTER=p03039-aks echo $RG_NAME echo $AKSCLUSTER az aks update -g $RG_NAME -n $AKSCLUSTER --enable-blob-driver --load-balancer-managed-outbound-ip-count 2   Verify the AKS cluster for Azure Blob CSI driver and Storage class. Notice the azureblob-nfs-premium and azureblob-fuse-premium storage class in the list.   Code: kubectl get sc   Log: [cloud-user@pdcesx11142 scripts]$ kubectl get sc NAME PROVISIONER RECLAIMPOLICY VOLUMEBINDINGMODE ALLOWVOLUMEEXPANSION A azureblob-fuse-premium blob.csi.azure.com Delete Immediate true 1 azureblob-nfs-premium blob.csi.azure.com Delete Immediate true 1 azurefile file.csi.azure.com Delete Immediate true 1 … ……. ………….. [cloud-user@pdcesx11142 scripts]$     Create PV and PVC against Azure Blob Storage   Having azureblob-nfs-premium driver and storage class at the AKS cluster, you can create a Persistence Volume (PV) and Persistence Volume Claim (PVC) against the Azure Blob Storage using the azureblob-nfs-premium storage class.   If required, you can create a customized storage class as well using blob.csi.azure.com driver provider.   The following statement can be used to create a .yaml file for Blob PV for NFS mount. When creating multiple PVs make sure to have a unique attribute value for volumeHandle: unique-volumeid101 .   Code: export NS=<mynamespace> export STRG_RG=<mystrgrg> export STRG_ACC=<mystrgacct> export STRG_FILE=<myfsdata> cat > ~/project/deploy/${NS}/site-config/data-access/pv-blob-nfs.yaml <<-EOF apiVersion: v1 kind: PersistentVolume metadata: annotations: pv.kubernetes.io/provisioned-by: blob.csi.azure.com name: pv-azblob spec: capacity: storage: 5G accessModes: - ReadWriteMany persistentVolumeReclaimPolicy: Retain # If set as "Delete" container would be removed after pvc deletion storageClassName: azureblob-nfs-premium csi: driver: blob.csi.azure.com readOnly: false # make sure volumeid is unique for every identical storage blob container in the cluster # character `#` is reserved for internal use and cannot be used in volumehandle volumeHandle: unique-volumeid101 volumeAttributes: resourceGroup: ${STRG_RG} storageAccount: ${STRG_ACC} containerName: ${STRG_FILE} protocol: nfs EOF   The following statement can be used to create a .yaml file for Blob PVC for NFS mount.   Code: export NS=<mynamespace> cat > ~/project/deploy/${NS}/site-config/data-access/pvc-blob-nfs.yaml <<-EOF kind: PersistentVolumeClaim apiVersion: v1 metadata: name: pvc-azblob spec: accessModes: - ReadWriteMany resources: requests: storage: 3Gi volumeName: pv-azblob storageClassName: azureblob-nfs-premium EOF   The following statement can be used to create a Blob PV and PVC for NFS mount. Apply the .yaml file to the AKS cluster under specified namespace to get the Blob PV and PVC.   Code: export NS=<mynamespace> kubectl config set-context --current --namespace=${NS} kubectl apply -f ~/project/deploy/${NS}/site-config/data-access/pv-blob-nfs.yaml kubectl apply -f ~/project/deploy/${NS}/site-config/data-access/pvc-blob-nfs.yaml kubectl -n ${NS} get pv pv-azblob kubectl -n ${NS} get pvc pvc-azblob   Log: [cloud-user@pdcesx03039 data-access]$ kubectl -n ${NS} get pv pv-azblob NAME CAPACITY ACCESS MODES RECLAIM POLICY STATUS CLAIM STORAGECLASS REASON AGE pv-azblob 5G RWX Retain Bound gelenv/pvc-azblob azureblob-nfs-premium 3h28m [cloud-user@pdcesx03039 data-access]$ [cloud-user@pdcesx03039 data-access]$ kubectl -n ${NS} get pvc pvc-azblob NAME STATUS VOLUME CAPACITY ACCESS MODES STORAGECLASS AGE pvc-azblob Bound pv-azblob 5G RWX azureblob-nfs-premium 3h28m [cloud-user@pdcesx03039 data-access]$     Mount the Azure Blob PVC to SAS Compute and CAS Pods   With Azure Blob PV and PVC at the AKS cluster, you can mount it to Compute and CAS Pods to store and access sas7bdat files.   The following statement can be used to create a .yaml file for Compute Pod to mount Blob PVC.   Code: export NS=<mynamespace> cat > ~/project/deploy/${NS}/site-config/data-access/blobdata-mounts-job.yaml <<-EOF # General example for adding mounts to CAS workers # PatchTransformer apiVersion: builtin kind: PatchTransformer metadata: name: blobdata-mounts-job patch: |- ## Azure Blob container - kubernetes will mount these for you - op: add path: /template/spec/containers/0/volumeMounts/- value: name: blob01 mountPath: "/mnt/blob01" - op: add path: /template/spec/volumes/- value: name: blob01 persistentVolumeClaim: claimName: pvc-azblob target: kind: PodTemplate annotationSelector: sas.com/sas-access-config=true labelSelector: "sas.com/template-intent=sas-launcher" EOF   The following statement can be used to create a .yaml file for CAS Pod to mount Blob PVC.   Code: export NS=<mynamespace> cat > ~/project/deploy/${NS}/site-config/data-access/blobdata-mounts-cas.yaml <<-EOF # General example for adding mounts to CAS workers # PatchTransformer apiVersion: builtin kind: PatchTransformer metadata: name: blobdata-mounts-cas patch: |- ## Azure File Share - kubernetes will mount these for you - op: add path: /spec/controllerTemplate/spec/containers/0/volumeMounts/- value: name: blob01 mountPath: "/mnt/blob01" - op: add path: /spec/controllerTemplate/spec/volumes/- value: name: blob01 persistentVolumeClaim: claimName: pvc-azblob target: kind: CASDeployment annotationSelector: sas.com/sas-access-config=true EOF   The following statement can be used to create a .yaml file for CAS Pod to allow the access to mounted path.   Code: export NS=<mynamespace> cat > ~/project/deploy/${NS}/site-config/data-access/cas-add-allowlist-paths.yaml << EOF apiVersion: builtin kind: PatchTransformer metadata: name: cas-add-allowlist-paths patch: |- - op: add path: /spec/appendCASAllowlistPaths/- value: /mnt/blob01 target: group: viya.sas.com kind: CASDeployment name: .* version: v1alpha1 EOF   Update the kustomization.yaml to include the additional .yaml files under the “transformers” section.   Code: transformers: ………. …….. - site-config/data-access/blobdata-mounts-cas.yaml ## To mount Azure BLOB PVC to CAS - site-config/data-access/blobdata-mounts-job.yaml ## To mount Azure BLOB PVC to Compute Server - site-config/data-access/cas-add-allowlist-paths.yaml ## To Allow Path in CAS …… …..   Build and apply the manifestation to the AKS cluster and recycle the CAS and SAS Compute pod.   Code: export NS=<mynamespace> cd ~/project/deploy/${NS}/ kustomize build -o site.yaml kubectl -n ${NS} apply -f site.yaml kubectl -n ${NS} delete pods -l casoperator.sas.com/server=default kubectl -n ${NS} delete pod --selector='app=sas-compute' kubectl -n ${NS} delete pod --selector='app=sas-launcher'   When CAS Pods are running, validate the Blob Storage is mounted to CAS Pods.   Code: ##validate the disk is correctly mounted kubectl exec sas-cas-server-default-controller -c sas-cas-server -- touch /mnt/blob01/sample_data/test2.txt kubectl exec sas-cas-server-default-controller -- ls -l /mnt/blob01/sample_data   Log: [cloud-user@pdcesx03039 gelenv]$ kubectl exec sas-cas-server-default-controller -c sas-cas-server -- touch /mnt/blob01/data/test2.txt [cloud-user@pdcesx03039 gelenv]$ [cloud-user@pdcesx03039 gelenv]$ kubectl exec sas-cas-server-default-controller -- ls /mnt/blob01/sample_data Defaulted container "sas-cas-server" out of: sas-cas-server, sas-backup-agent, sas-consul-agent, sas-certframe (init), sas-config-init (init) test2.txt [cloud-user@pdcesx03039 gelenv]$   If the SAS Compute Server is in a lockdown state, update the Compute service:autoexec_code configuration instance from the SAS Environment Manager application to include the required path.       SAS Compute Server Access to Azure Blob Storage PVC   With Azure Blob PVC mounted to the SAS Compute Server, it can read and write sas7bdat files to Azure Blob Storage (ADLS2). The Azure Blob Storage is available to the SAS Compute Server using NFS 3 protocol and Azure Blob CSI driver. The following statement can be used to save and read a sas7bdat file to Azure Blob Storage using a PATH-based LIBNAME statement.   Code: /* # LIBNAME Statement for Azure Blob Share Location */ libname azshrlib "/mnt/blob01/sample_data" ; data azshrlib.fish_sas ; set sashelp.fish ; run; Proc SQL outobs=20; select * from azshrlib.fish_sas ; run;quit;   Log: 80 /* # LIBNAME Statement for Azure Blob Share Location */ 81 82 libname azshrlib "/mnt/blob01/sample_data" ; NOTE: Libref AZSHRLIB was successfully assigned as follows: Engine: V9 Physical Name: /mnt/blob01/sample_data 83 84 data azshrlib.fish_sas ; 85 set sashelp.fish ; 86 run; NOTE: There were 159 observations read from the data set SASHELP.FISH. NOTE: The data set AZSHRLIB.FISH_SAS has 159 observations and 7 variables. NOTE: DATA statement used (Total process time): real time 0.46 seconds cpu time 0.00 seconds 87 88 Proc SQL outobs=20; 89 select * from azshrlib.fish_sas ; WARNING: Statement terminated early due to OUTOBS=20 option. 90 run;quit; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: The PROCEDURE SQL printed page 11. NOTE: PROCEDURE SQL used (Total process time): real time 0.06 seconds cpu time 0.04 seconds 91     CAS load/Save from Azure Blob Storage PVC   With Azure Blob PVC mounted to the CAS, it can load/save data from sas7bdat files located at Azure Blob Storage (ADLS2). The Azure Blob Storage is available to CAS using NFS 3 protocol and Azure Blob CSI driver. CAS can access the sas7bdat data files saved by the SAS Compute Server and vice versa.   The following statement can be used to load/save a sas7bdat file to Azure Blob Storage using a PATH-based CASLIB statement.   Code: CAS mySession SESSOPTS=(CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true); CASLIB azlib DATASOURCE=(SRCTYPE="PATH") path="/mnt/blob01/sample_data" ; /* Save a CAS table into a sas7bdat file. */ proc casutil incaslib="azlib" outcaslib="azlib"; load data=sashelp.cars casout="cars" replace; save casdata="cars" casout="cars.sas7bdat" replace; list files; quit; /* Load CAS from sas7bdat data files*/ proc casutil incaslib="azlib" outcaslib="azlib"; load casdata="cars.sas7bdat" casout="cars_7bdat" replace; load casdata="fish_sas.sas7bdat" casout="fish_7bdat" replace; list tables; quit; CAS mySession TERMINATE;   Log: ……. ….. 82 CASLIB azlib DATASOURCE=(SRCTYPE="PATH") path="/mnt/blob01/sample_data" ; NOTE: Executing action 'table.addCaslib'. NOTE: 'AZLIB' is now the active caslib. NOTE: Cloud Analytic Services added the caslib 'AZLIB'. NOTE: Action 'table.addCaslib' used (Total process time): NOTE: real time 0.039090 seconds NOTE: cpu time 0.017151 seconds (43.88%) NOTE: total nodes 3 (12 cores) NOTE: total memory 94.03G NOTE: memory 1.41M (0.00%) NOTE: Action to ADD caslib AZLIB completed for session MYSESSION. 83 84 /* Save a CAS table into a sas7bdat file. */ 85 proc casutil incaslib="azlib" outcaslib="azlib"; NOTE: The UUID 'dde2b86d-583b-e842-8736-8fb4390b584a' is connected using session MYSESSION. …. …. 88 save casdata="cars" casout="cars.sas7bdat" replace; NOTE: Executing action 'table.save'. NOTE: Cloud Analytic Services saved the file cars.sas7bdat in caslib AZLIB. NOTE: Action 'table.save' used (Total process time): NOTE: real time 0.216190 seconds NOTE: cpu time 0.041189 seconds (19.05%) NOTE: total nodes 3 (12 cores) NOTE: total memory 94.03G NOTE: memory 4.52M (0.00%) NOTE: The Cloud Analytic Services server processed the request in 0.21619 seconds. …. ……. 92 /* Load CAS from sas7bdat table */ 93 proc casutil incaslib="azlib" outcaslib="azlib"; NOTE: The UUID 'dde2b86d-583b-e842-8736-8fb4390b584a' is connected using session MYSESSION. 95 load casdata="cars.sas7bdat" casout="cars_7bdat" replace; NOTE: Executing action 'table.loadTable'. NOTE: Cloud Analytic Services made the file cars.sas7bdat available as table CARS_7BDAT in caslib azlib. NOTE: Action 'table.loadTable' used (Total process time): NOTE: real time 0.071273 seconds NOTE: cpu time 0.039901 seconds (55.98%) NOTE: total nodes 3 (12 cores) NOTE: total memory 94.03G NOTE: memory 7.08M (0.01%) NOTE: bytes moved 68.08K NOTE: The Cloud Analytic Services server processed the request in 0.071273 seconds. 100 load casdata="fish_sas.sas7bdat" casout="fish_7bdat" replace; NOTE: Executing action 'table.loadTable'. NOTE: Cloud Analytic Services made the file fish_sas.sas7bdat available as table FISH_7BDAT in caslib azlib. NOTE: Action 'table.loadTable' used (Total process time): NOTE: real time 0.052144 seconds NOTE: cpu time 0.042362 seconds (81.24%) NOTE: total nodes 3 (12 cores) NOTE: total memory 94.03G NOTE: memory 7.06M (0.01%) NOTE: bytes moved 11.14K NOTE: The Cloud Analytic Services server processed the request in 0.052144 seconds. ……. …………… …………….     Azure Blob Storage with sas7bdat files       Performance   To be practical, the performance of mounted blob storage is not going to be like a local disk or local NFS drive. You must be prepared for slow performance. The performance of the data file access from Azure Blob Storage to SAS Compute Server and CAS environment depends on various components, including the capacity of the VM Server used in the AKS Node-pool. The usage of Standard Vs Premium Storage Account. The network interface and I/O throughput of VM machines. The high-end Azure VM in the AKS Node-pool with the Premium Storage Account would perform better.   The following test result is from a Standard and Premium Storage Account with the “Standard_E4s_v3” type VM machine for CAS and Standard_D8s_v3 type VM machine for the SAS Compute Server.   SAS Compute Server time to save sas7bdat data to ADLS2 Blob Storage     SAS Compute Server time to read sas7bdat data (Proc Freq) from ADLS2 Blob Storage     CAS time to load sas7bdat data file from ADLS2 Blob Storage       Conclusion   Considering the features and limitations of Azure Storage Account Blob Storage and VM in AKS Node-pool, you can mount the Blob storage to SAS Compute Server and CAS pods to access the sas7bdat files. This could be a useful option to migrate the existing sas7bdat files from the on-premises environment to cloud-based object storage (Blob). It enables SAS users to read and write sas7bdat files to Blob storage from the SAS Compute Server and CAS. The sas7bdat data files created by either SAS Compute Server or CAS can be read by both and vice versa.   Important Link: Using Azure Blob Storage for sas7bdat files with SAS Viya – Part 1         Find more articles from SAS Global Enablement and Learning here. ... View more

Azure Data Lake (ADLS) Blob Storage is a cloud-based distributed file storage service. Often the question is asked, can we use the Azure Blob Storage to store sas7bdat files and access them from the SAS Viya applications? When a customer migrates from a traditional SAS 9.4 to the SAS Viya environment, they like to bring existing sas7bdat data files to cloud storage and access them from the SAS Compute Server and the CAS environment.   In short, the answer to the above question is Yes!   An Azure Storage Account (ADLS2) with Hierarchical-namespace is a POSIX-compliant. The Azure Blob storage supports the Network File System (NFS) 3.0 protocol. This support provides Linux file system compatibility at object storage and enables Linux clients to mount a container in blob storage.   The Azure Blob Storage Container Storage Interface (CSI) driver is a CSI specification-compliant driver used by AKS to manage the lifecycle of Azure Blob Storage. The CSI is a standard for exposing block and file storage systems to containerized applications on Kubernetes.   With NFS 3.0 protocol and Hierarchical namespace enabled on an Azure Storage Account (ADLS2) and Azure Blob Storage CSI driver enabled on the AKS cluster, you can create a Persistent Volume (PV) and Persistence Volume Claim (PVC) against Azure Blob Storage using azureblob-nfs-premium storage class. The Azure CSI Blob Driver has two storage classes (azureblob-nfs-premium and azureblob-fuse-premium) for blob storage. The PVC can be mounted to the SAS Compute Pod and CAS Pods using either of the storage classes. This enables SAS Viya users to use Azure Blob Storage to store sas7bdat files and access them from the SAS Compute Server and CAS environment.   Access to Azure Blob storage from SAS Compute Server and CAS is a vital step for customers migrating from SAS 9.4 to Viya and looking to store the sas7bdat file at ADLS blob storage. The sas7bdat data files saved by the SAS Compute Server can be accessed by CAS and vice versa.   The data access path from ADLS2 Blob storage to SAS Compute Server and CAS.   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 AKS Cluster is enabled with the CSI Blob driver and Storage class.     The Azure Storage Account (ADLS2) is enabled with Hierarchical Name Space, NFS 3. 0 protocol, and selected network access.     The Azure Blob Storage (ADLS2) with sas7bdat files.     The SAS Compute Server reads and writes sas7bdat files to Azure Blob Storage (ADLS2).     The CAS load/save data from sas7bdat files located at Azure Blob Storage (ADLS2). The sas7bdat files saved by the SAS Compute Server load to CAS.     Stay tuned in for configuration details for Azure Storage Account and AKS to mount the Azure Blob Storage to SAS Viya Compute and CAS Pods to access sas7bdat files.   Important Links: Use Azure Blob storage Container Storage Interface (CSI) driver Create and use a volume with Azure Blob storage in Azure Kubernetes Service (AKS)     Find more articles from SAS Global Enablement and Learning here. ... View more

A SAS Federated Data Source is a common place for end users to access data from multiple data sources. A Cloud Data Exchange (CDE) administrator can create a Federated Data Source in the SAS Viya CDE environment from existing data sources like Oracle, DB2, and BASE SAS tables. The Federated Data Source can have a table schema defined from combinations of multiple data source tables using a few columns from each source data table. The data can also be masked in a Federated Data Source table schema.   In this post, I discuss the configuration of the Federated Data Source at the Remote Data agent.   The Federated Data Source schema is defined using existing data sources. The client configuration must be deployed and configured at the Remote Data Agent Server to support the various data sources like Oracle, DB2, and MS-SQL Server before using it in the Federated Data Source.   Pre-requisite   • There are at least two or more data sources (e.g. BASE, Oracle) defined at the Remote Data Agent Server.   The following pics describe the Federated Data Source from multiple data sources at the Remote Data Agent Server.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Steps to create a Federated Data Source   The following steps describe the creation of a Federated Data Source at the Remote Data Agent using various existing data sources.   List the predefined data sources   A CDE Administrator should know the list of existing data sources to create a new Federated Data Source.   Code:   sas-viya dagentsrv servers set-default --data-agent sas-data-agent-server-remote sas-viya dagentsrv data-sources list   Log:   jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv servers set-default --data-agent sas-data-agent-server-remote The default data agent server was successfully set. jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv data-sources list Name ID Type ADMIN ADMIN DSN BASE BASE DSN BASE BASE__DATA_SERVICE__ Service orasrv1 orasrv1 DSN orasrv1 orasrv1__DATA_SERVICE__ Service jumpuser@p02190-jump-vm:/viya-share/gelenv/data$   Create a Federated Data Source.   The following statement creates a Federated Data Source using Oracle and BASE data service at the Remote Data Agent Server.   Code:   sas-viya dagentsrv dsns create federated --name DEMOFED --using "BASE,orasrv1"   Log:   umpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv dsns create federated --name DEMOFED --using "BASE,orasrv1" The data source name was successfully created.   List and test the Federated Data Source.   Code:   sas-viya dagentsrv data-sources list sas-viya dagentsrv data-sources test --name DEMOFED   Log:   jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv data-sources list Name ID Type ADMIN ADMIN DSN BASE BASE DSN BASE BASE__DATA_SERVICE__ Service DEMOFED DEMOFED DSN orasrv1 orasrv1 DSN orasrv1 orasrv1__DATA_SERVICE__ Service jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv data-sources test --name DEMOFED Successfully connected to data source DEMOFED. jumpuser@p02190-jump-vm:/viya-share/gelenv/data$   List tables from Federated Data Source.   You can access the tables from both the underlying data sources (Oracle and BASE). You can list the table from Oracle and BASE data sources by specifying the corresponding catalog and schema in the CLI statement.   Code:   as-viya dagentsrv data-sources tables list --data-source DEMOFED --catalog ORASRV1 --schema GELDM sas-viya dagentsrv data-sources tables list --data-source DEMOFED --catalog BASECATR1 --schema SCHEMAR1   Log:   jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv data-sources tables list --data-source DEMOFED --catalog ORASRV1 --schema GELDM Name Type Native Catalog CARSVIEW1 VIEW NULL fish_sas TABLE NULL SAS_CAR TABLE NULL sas_cars TABLE NULL TEST_TABLE TABLE NULL TEST2 TABLE NULL TESTVIEW1 VIEW NULL jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv data-sources tables list --data-source DEMOFED --catalog BASECATR1 --schema SCHEMAR1 Name Type Native Catalog FISH_SAS TABLE BASECATR1 SAS_CARS TABLE BASECATR1 TEST TABLE BASECATR1 TEST_TABLE TABLE BASECATR1 jumpuser@p02190-jump-vm:/viya-share/gelenv/data$   List a few rows from the Federated Data Source tables.   You can access the data from Oracle and BASE data sources by specifying the corresponding catalog and schema in the CLI statement.   Code:   sas-viya dagentsrv sql --sql "select * from ORASRV1.GELDM.TEST_TABLE where id < 10 " --dsn DEMOFED sas-viya dagentsrv sql --sql "select * from BASECATR1.SCHEMAR1.SAS_CARS where Make='Acura' " --dsn DEMOFED   Log:   jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv sql --sql "select * from ORASRV1.GELDM.TEST_TABLE where id < 10 " --dsn DEMOFED DATE_VALUE ID TEXT_VALUE ========== == ========== 1 EanOFzMlzSHKhhPTolDW 2 jBctxUPyQscSbnNiRsNU 3 UVwYWRHVwoELUyXIeEPF 4 RtvXPzHgJtXrLHXtBSRE 5 bMbpjSRxWGfODrkwgDaA 6 ozMjNApZjIrSpHdOpNVO 7 YwPcHobUBKFEPLabfJBa 8 pvnsRhySuOywHQHAKiBA 9 nYDsRnmPPCLJzQNwaZiq jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv sql --sql "select * from BASECATR1.SCHEMAR1.SAS_CARS where Make='Acura' " --dsn DEMOFED Cylinders DriveTrain EngineSize Horsepower Invoice Length MPG_City MPG_Highway MSRP Make Model Origin Type Weight Wheelbase ========= ========== ========== ========== ======= ====== ======== =========== ==== ==== ===== ====== ==== ====== ========= 6 All 3.5 265 33337 189 17 23 36945 Acura MDX Asia SUV 4451 106 4 Front 2.4 200 24647 183 22 29 26990 Acura TSX 4dr Asia Sedan 3230 105 6 Front 3.5 225 39014 197 18 24 43755 Acura 3.5 RL 4dr Asia Sedan 3880 115 6 Rear 3.2 290 79978 174 17 24 89765 Acura NSX coupe 2dr manual S Asia Sports 3153 100 4 Front 2 200 21761 172 24 31 23820 Acura RSX Type S 2dr Asia Sedan 2778 101 6 Front 3.2 270 30299 186 20 28 33195 Acura TL 4dr Asia Sedan 3575 108 6 Front 3.5 225 41100 197 18 24 46100 Acura 3.5 RL w/Navigation 4dr Asia Sedan 3893 115 jumpuser@p02190-jump-vm:/viya-share/gelenv/data$   Create a new object schema (data masked and un-masked view) in Federated Data Source   Create a View (Schema) in Federated Data Source.   You can create a view in Federated Data Source based on two different data source tables. The data reside at the original place (Database / BASE table), only schema is created at Federated Data Source.   Code:   sas-viya dagentsrv views create --dsn DEMOFED --name baseorav2 --sql "select a.ID, a.DATE_VALUE, b.TEXT_VALUE from BASECATR1.SCHEMAR1.TEST_TABLE_ORA_IMPORTED a , ORASRV1.GELDM.TEST_TABLE b where a.ID = b.ID and a.ID < 10 "   Log:   jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv views create --dsn DEMOFED --name baseorav2 --sql "select a.ID, a.DATE_VALUE, b.TEXT_VALUE from BASECATR1.SCHEMAR1.TEST_TABLE_ORA_IMPORTED a , ORASRV1.GELDM.TEST_TABLE b where a.ID = b.ID and a.ID < 10 " View successfully created. jumpuser@p02190-jump-vm:/viya-share/gelenv/data$   Access the data from View (Schema) in Federated Data Source.   Code:   sas-viya dagentsrv sql --sql "select * from BASEORAV2 " --dsn DEMOFED   Log:   jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv sql --sql "select * from baseorav2 " --dsn DEMOFED DATE_VALUE ID TEXT_VALUE ========== == ========== 1 EanOFzMlzSHKhhPTolDW 2 jBctxUPyQscSbnNiRsNU 3 UVwYWRHVwoELUyXIeEPF 4 RtvXPzHgJtXrLHXtBSRE 5 bMbpjSRxWGfODrkwgDaA 6 ozMjNApZjIrSpHdOpNVO 7 YwPcHobUBKFEPLabfJBa 8 pvnsRhySuOywHQHAKiBA 9 nYDsRnmPPCLJzQNwaZiq jumpuser@p02190-jump-vm:/viya-share/gelenv/data$   Create a View (Schema) with data masked at Federated Data Source.   You can create a view in Federated Data Source with data columns masked. The original source data columns are not masked but access to data via Federated view is masked. This feature enables the CDE administrator to mask some data from specific user groups.   Code:   sas-viya dagentsrv views create --dsn DEMOFED --name carsviewmask --sql "select MAKE, MODEL,TYPE, syscat.dm.mask('ENCRYPT',PUT("INVOICE",8.), 'alg', 'AES') AS INVOICE_MASK from ORASRV1.GELDM.SAS_CARS "   Log:   jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv views create --dsn DEMOFED --name carsviewmask --sql "select MAKE, MODEL,TYPE, syscat.dm.mask('ENCRYPT',PUT("INVOICE",8.), 'alg', 'AES') AS INVOICE_MASK from ORASRV1.GELDM.SAS_CARS " View successfully created. jumpuser@p02190-jump-vm:/viya-share/gelenv/data$   Access the data from View with masked data at Federated Data Source.   You can create a view in Federated Data Source with data columns masked. The original source data columns are not masked but access to data via Federated view is masked. This feature enables the CDE administrator to mask some data from specific user groups.   Code:   sas-viya dagentsrv sql --sql "select * from CARSVIEWMASK where Make='Acura' " --dsn DEMOFED   Log:   jumpuser@p02190-jump-vm:/viya-share/gelenv/data$ sas-viya dagentsrv sql --sql "select * from CARSVIEWMASK where Make='Acura' " --dsn DEMOFED INVOICE_MASK Make Model Type ============ ==== ===== ==== CAA68FFFB0069143BA851C3B381DCB0C720E Acura MDX SUV A9BFE48C4ECE7328D6D9F13BF3376E59574A Acura TSX 4dr Sedan AED8143F92CA41424701E3EC79EBC859EDD2 Acura 3.5 RL 4dr Sedan 45F12849A89770A4128638BF9814549E5C92 Acura NSX coupe 2dr manual S Sports CC094DBC9DB884557BD329B535104C67593B Acura RSX Type S 2dr Sedan 64226CB01D9DFF2842A0215E6E67F60D4856 Acura TL 4dr Sedan DE3AB29C43489B60976D4FADA095A58B817A Acura 3.5 RL w/Navigation 4dr Sedan jumpuser@p02190-jump-vm:/viya-share/gelenv/data$   SAS Compute Server Access to Federated Data Source at Remote Data Agent   With the Federated Data Source configured and schema defined at the Remote Data Agent, users can access Federated data tables from the SAS Compute Server using the CDE LIBNAME statement. The following code describes the access of a Federated table.   Code:   IBNAME cdefed1 cde dataagentname="sas-data-agent-server-remote" dsn=DEMOFED preserve_tab_names=yes; Proc SQL outobs=5; select * from cdefed1.carsviewmask; run;quit;   Log:   80 81 LIBNAME cdefed1 cde dataagentname="sas-data-agent-server-remote" 82 dsn=DEMOFED preserve_tab_names=yes; NOTE: Libref CDEFED1 was successfully assigned as follows: Engine: CDE Physical Name: DEMOFED 83 91 92 Proc SQL outobs=5; 93 select * from cdefed1.carsviewmask; WARNING: Statement terminated early due to OUTOBS=5 option. 94 run;quit; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: The PROCEDURE SQL printed page 12. NOTE: PROCEDURE SQL used (Total process time): real time 1.00 seconds cpu time 0.08 seconds   CAS Access to Federated Data Source at Remote Data Agent   With the Federated Data Source configured and schema defined at the Remote Data Agent, users can access Federated data tables from the CAS using the CDE data connector. The following code describes the CAS access to the Federated table using the CDE CASLIB statement.   Code:   CAS mySession SESSOPTS=(CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true); caslib cdefed datasource=( srctype="clouddex", dataAgentName="sas-data-agent-server-remote", conopts="dsn=DEMOFED" ) libref=cdefed; /* CAS load from CDE RDA Federated table */ proc casutil incaslib="cdefed" outcaslib="cdefed"; load casdata="baseorav2" casout="baseorav2" replace ; list tables ; run; quit; cas mysession terminate;   Log:   3 84 caslib cdefed datasource=( 85 srctype="clouddex", 86 dataAgentName="sas-data-agent-server-remote", 87 conopts="dsn=DEMOFED" 88 ) libref=cdefed; NOTE: Executing action 'table.addCaslib'. NOTE: 'CDEFED' is now the active caslib. NOTE: Cloud Analytic Services added the caslib 'CDEFED'. 81 /* CAS load from CDE RDA Federated table */ 82 proc casutil incaslib="cdefed" outcaslib="cdefed"; NOTE: The UUID '60e9bfc7-ac8d-ff4e-b122-33ed678dee4d' is connected using session MYSESSION. 83 load casdata="baseorav2" casout="baseorav2" replace ; NOTE: Executing action 'table.loadTable'. NOTE: Connecting using the OAuth token for CAS user 'geldmui@gelenable.sas.com'. WARNING: Using server default session timeout of 3600 NOTE: Cloud Analytic Services made the external data from baseorav2 available as table BASEORAV2 in caslib cdefed. NOTE: Action 'table.loadTable' used (Total process time):   Important Links:   Cloud Data Exchange for the SAS Viya Platform   Communities posts: SAS Cloud Data Exchange for the SAS Viya Platform SAS Viya Cloud Data Exchange Deployment and Configuration (Part -1) SAS Viya Cloud Data Exchange Deployment and Configuration (Part -2) Configuring BASE SAS Data Source at Remote Data Agent (Cloud Data Exchange) Configuring Oracle Data Source at Remote Data Agent (Cloud Data Exchange)     Find more articles from SAS Global Enablement and Learning here. ... View more