SAS Viya/CAS accessing Azure SQL Database
The SAS MS-SQL Server Data Connector enables CAS to access the Azure SQL Database table. You can load and save CAS from the Azure SQL Database table using the serial and multi-node data load mechanism.
This post identifies the components required for CAS to connect and access the Azure SQL Database.
Azure SQL Database
Azure SQL Database is a fully managed service-based database engine. It is based on the latest version of the MS-SQL server database engine. The SQL database could be a preferred choice for various cloud applications because it enables users to process both relational and non-relational structures (JSON, XML, Graph, etc.) data in the same database engine.
Data access methods
The CAS supports the Serial and Multi-node mechanism to load and save data to Azure SQL Database using SAS Data Connector to MS-SQL Server. In the case of Serial load, it requires the Data Connector at CAS controller server. In the case of the Multi-node, it requires the Data Connector at CAS controller and nodes. The following diagram describes the data path and components requires to access the Azure SQL Database table.
CAS supports the Serial and Multi-Node load/save methods for Azure SQL Database.
Select the image to see a larger version.
Mobile users: To view the image, select the "Full" version at the bottom of the page.
Pre-requisites
- SAS data connector to MS-SQL Server at CAS controller and nodes
- ODBC configuration setup at CAS controller and nodes
- User credentials to access to Azure SQL Database
- Azure SQL Database server firewall configured to allow Viya/CAS IPs connection
- Customized odbc.ini with a DSN name containing specific parameters
CryptoProtocolVersion=TLSv1.2, TLSv1.1,TLSv1, SSLv3, SSLv2
AuthenticationMethod=1
EncryptionMethod=1
ValidateServerCertificate=0 - SSL Library in the user’s LD_LIBRARY_PATH path.
You can copy the following file to .../accessclients/lib folder or use the location in LD_LIBRARY_PATH path./opt/sas/viya/home/SASEventStreamProcessingEngine/6.2/ssl/lib/libS0ssl28.so
Data load from Azure SQL Database to CAS
The following code describes the Serial and Multi-node data load from Azure SQL Database to CAS. The CASLIB statement uses the “sqlserver” data connector along with user id and password from the Azure SQL Database server and a customized DSN name from odbc.ini.
Serial Load Code:
CAS mySession SESSOPTS=(CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true);
caslib azsqldb desc='Microsoft SQL Server Caslib'
dataSource=(srctype='sqlserver',
username='viyadep',
password='xxxxxxxxxxxx',
sqlserver_dsn="sqls_gel");
proc casutil incaslib="azsqldb" outcaslib="azsqldb";
load casdata="fish_sas" casout="fish_sas" replace;
list tables;
quit;
CAS mySession TERMINATE;
Log extract:
…………
……
96
97 proc casutil incaslib="azsqldb" outcaslib="azsqldb";
NOTE: The UUID '835901a3-aec7-eb4b-9549-cd84da9f5951' is connected using session MYSESSION.
99 load casdata="fish_sas" casout="fish_sas" replace;
NOTE: Executing action 'table.loadTable'.
NOTE: Performing serial LoadTable action using SAS Data Connector to SQL Server.
NOTE: Cloud Analytic Services made the external data from fish_sas available as table FISH_SAS in caslib azsqldb.
….
……………
Multi-node Load Code:
CAS mySession SESSOPTS=(CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true);
caslib azsqldb desc='Microsoft SQL Server Caslib'
dataSource=(srctype='sqlserver',
username='viyadep',
password='xxxxxxxxxxxx',
sqlserver_dsn="sqls_gel",
numreadnodes=10,numwritenodes=10,
DRIVER_TRACE="SQL",
DRIVER_TRACEFILE="/tmp/sasdcpg.log",
DRIVER_TRACEOPTIONS="TIMESTAMP|APPEND"
);
proc casutil incaslib="azsqldb" outcaslib="azsqldb";
load casdata="fish_sas" casout="fish_sas" options=(sliceColumn="weight") replace;
list tables;
quit;
CAS mySession TERMINATE;
Log extract:
…………
……
2 proc casutil incaslib="azsqldb" outcaslib="azsqldb";
NOTE: The UUID '0e29a7e3-d26a-8f4f-96b7-83968eea7107' is connected using session MYSESSION.
83 load casdata="fish_sas" casout="fish_sas" options=(sliceColumn="weight") replace;
NOTE: Executing action 'table.loadTable'.
NOTE: Performing serial LoadTable action using SAS Data Connector to SQL Server.
WARNING: The value of numReadNodes(10) exceeds the number of available worker nodes(2). The load will proceed with numReadNodes=2.
NOTE: Cloud Analytic Services made the external data from fish_sas available as table FISH_SAS in caslib azsqldb.
….
……………
Extract from trace file of Multi-node CAS load:
…………
……
[viyadep@cas02 ~]$ more /tmp/sasdcpg.log | grep 'DRIVER SQL'
19.14.16.38: DRIVER SQL: "select * from geldb.dbo.fish_sas where 1=0 " on connection 0x0000000040034b80
19.14.16.50: DRIVER SQL: "select SLICE_SQL.Species, SLICE_SQL.Weight, SLICE_SQL.Length1, SLICE_SQL.Length2, SLICE_SQL.Length3, SLICE_SQL.Height, SLICE_SQL.Width from (select geldb.dbo.fish_sas.Species, geldb.dbo.fish_sas.Weight, geldb.dbo.fish_sas.Length1, geldb.dbo.fish_sas.Length2, geldb.dbo.fish_sas.Length3, geldb.dbo.fish_sas.Height, geldb.dbo.fish_sas.Width from geldb.dbo.fish_sas) SLICE_SQL where ( ( ( CAST(FLOOR (ABS (LOG10 (ABS (SLICE_SQL.Weight) ) ) ) AS BIGINT) % 2=0) or SLICE_SQL.Weight IS NULL) ) " on connection 0x0000000040042e20
[viyadep@cas02 ~]$
….
……………
The ODBC.ini with Customized DSN name:
…………
……
#sqls_gel start
[sqls_gel]
Driver=/opt/sas/viya/home/lib64/accessclients/lib/S0sqls28.so
Description=SAS Institute, Inc SQL Server Wire Protocol
AlternateServers=
AlwaysReportTriggerResults=0
AnsiNPW=1
ApplicationName=
ApplicationUsingThreads=1
AuthenticationMethod=1
BulkBinaryThreshold=32
BulkCharacterThreshold=-1
BulkLoadBatchSize=1024
BulkLoadFieldDelimiter=
BulkLoadOptions=2
BulkLoadRecordDelimiter=
ConnectionReset=0
ConnectionRetryCount=0
ConnectionRetryDelay=3
CryptoProtocolVersion=TLSv1.2, TLSv1.1,TLSv1, SSLv3, SSLv2
Database=geldb
EnableBulkLoad=0
EnableQuotedIdentifiers=0
EncryptionMethod=1
EnableScrollableCursors=4
FailoverGranularity=0
FailoverMode=0
FailoverPreconnect=0
FetchTSWTZasTimestamp=0
FetchTWFSasTime=1
GSSClient=native
HostName=utkuma5sqlsrv.database.windows.net
HostNameInCertificate=
InitializationString=
Language=
LoadBalanceTimeout=0
LoadBalancing=0
LoginTimeout=15
LogonID=viyadep
MaxPoolSize=100
MinPoolSize=0
PacketSize=-1
Password=XXXXXXXX
Pooling=0
PortNumber=1433
QueryTimeout=0
ReportCodePageConversionErrors=0
SnapshotSerializable=0
TrustStore=
TrustStorePassword=
ValidateServerCertificate=0
WorkStationID=
XMLDescribeType=-10
#sqls_gel end
….
……………
Additional Resources
