SAS 9.4 M7 access to Azure Synapse SQL Pool ( SQL DW)
With SAS 9.4 M7 release, SAS users can access Azure Synapse SQL-Pool (formerly SQL DW) database table using SAS/ACCESS Interface to MS-SQL Server. It also enables SAS users to access various types of data files (parquet, orc, Json, etc..) stored at ADLS2 via Azure Synapse. User can read these data files into a columnar data table under Azure Synapse before reading into SAS processes.
This post is about accessing Azure Synapse SQL Pool (SQL DW) database from SAS 9.4 M7 environment.
What is Azure Synapse SQL Pool ?
Azure Synapse SQL Pool (formerly SQL DW) represents a collection of analytic resources that are provisioned when using Synapse SQL. Azure Synapse SQL Pool is the MS-SQL server database running at cloud in distributed mode on n-number of servers/machines.
The SAS/ACCESS Interface to MS-SQL enables users to access the Azure Synapse database. It also supports BULK data load to Azure Synapse SQL Pool database using Azure Blob Storage for intermediate data files. The Bulk load process uploads the large/bulk data into an ADLS2 Blob Container before using the COPY INTO statement to push the data into the Synapse database table.
Pre-requisites
- SAS 9.4 M7 and SAS/ACCESS interface to MS-SQL Server at SAS compute server
- ODBC configuration to access MS-SQL server (Azure Synapse) database
- Customized DSN name in odbc.ini to access Azure Synapse database
- User access to Azure Synapse database
- For Bulk Load :
- User permission to Azure Storage Account with “Contributor” and “Storage Blob Data Contributor” role
- Azure user application with permission to access Azure Data lake and Azure Storage
- Azure Access Keys at SAS Compute Server to access Blob Storage
- MS-SQL server configured with Managed Identity or Shared Access Signature (SAS) token to access Blob Storage
Data access path
The following picture describes the SAS 9.4 environment access to Azure Synapse SQL Pool (SQL DW) database table. It also describes the data path for BULK data upload from SAS 9.4 to the Azure Synapse Database table.
Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.
Azure Storage Account configuration for BULK Load
To bulk data load to Azure Synapse SQL Pool database, a user must have blob storage location defined with read-write permission. An operational Azure Application enabled with native communication to obtain Azure Access Key at SAS Compute server.
The details steps are discussed in this article Cas Accessing Azure Data-lake Files, for configuring Azure user application and Storage Account to access ALDS2 Blob storage.
Azure MS-SQL Server configuration for BULK Load
To bulk load the Azure Synapse database, the MS-SQL server requires access to Azure ADLS2 Blob Storage with read-write permission. You can use Blob Storage Shared Access Signature(SAS) token in the LIBNAME statement or configure the MS-SQL server with “--assign-identity” and assign the “Storage Blob Data Contributor” role to SQL server “principle id” for seamless access to Blob Storage. The following Azure CLI describes the creation of the MS-SQL Server with “--assign-identity”, and role assignment to Server Principle id.
CLI Code:
STUDENT=”Myname”
AKS_MY_LOCATION=”eastus2”
AKS_MY_RESOURCE_GROUP="${STUDENT}viya4"
AKS_MY_USER=viyadep
srv=sqlsrv
sql_server_name=$AKS_MY_RESOURCE_GROUP$srv
sql_server_admin_user=$AKS_MY_USER
sql_server_admin_pwd=”XXXXXXXXX”
sqldw_name=geldwpool
### Create a SQL Server with –assign-identity ####
sql_serv_sts=`az sql server create \
--name $sql_server_name \
--admin-user $sql_server_admin_user \
--admin-password $sql_server_admin_pwd \
--resource-group $AKS_MY_RESOURCE_GROUP \
--location $AKS_MY_LOCATION \
--enable-public-network true \
--assign-identity \
-o tsv `
### Create an Azure Synapse MS-SQL POOL Database in SQL Server ###
sql_serv_dw_sts=`az sql dw create \
--name $sqldw_name \
--server $sql_server_name \
--resource-group $AKS_MY_RESOURCE_GROUP \
--service-objective DW100c \
--tags name=$STUDENT \
--zone-redundant false `
### Assign Storage Blob Data Contributor Role to MS-SQL server managed Identity at Resource group level scope .###
spID=`az resource list -n $sql_server_name --query [*].identity.principalId --out tsv `
rgID=`az group show -g $AKS_MY_RESOURCE_GROUP --query id -o tsv`
server_role_tst=`az role assignment create --assignee $spID --role 'Storage Blob Data Contributor' --scope $rgID`
Customized DSN
A customized DSN entry in the odbc.ini file to access the Azure SQL Pool database with hostname, database name, and SSL lib path, etc.
…….
………………
[sqls_gelws]
Driver=/opt/sas/viya/home/lib64/accessclients/lib/S0sqls28.so
Description=SAS ACCESS to MS SQL Server
AEKeyCacheTTL=-1
AEKeystorePrincipalId=
AEKeystoreClientSecret=
AlternateServers=
AlwaysReportTriggerResults=0
AnsiNPW=1
ApplicationIntent=0
ApplicationName=
ApplicationUsingThreads=1
AuthenticationMethod=1
BulkBinaryThreshold=32
BulkCharacterThreshold=-1
BulkLoadBatchSize=1024
BulkLoadFieldDelimiter=
BulkLoadOptions=2
BulkLoadRecordDelimiter=
BulkLoadThreshold=2
ColumnEncryption=Disabled
ConnectionReset=0
ConnectionRetryCount=0
ConnectionRetryDelay=3
CryptoProtocolVersion=TLSV1,TLSV1.1,TLSV1.2,SSLv3, SSLv2
Database=geldwpool
Domain=
EnableBulkLoad=0
EnableQuotedIdentifiers=0
EnableServerSideCursors=1
EnableScrollableCursors=4
EncryptionMethod=1
FailoverGranularity=0
FailoverMode=0
FailoverPreconnect=0
FetchTSWTZasTimestamp=0
FetchTWFSasTime=1
GSSClient=native
HostName=utkumaviya4sqlsrv.database.windows.net
HostNameInCertificate=
#IANAAppCodePage=
InitializationString=
KeepAlive=0
Language=
LoadBalanceTimeout=0
LoadBalancing=0
LoginTimeout=15
LogonID=viyadep
MaxPoolSize=100
MinPoolSize=0
MultiSubnetFailover=0
PacketSize=-1
Pooling=0
PortNumber=1433
PRNGSeedFile=/dev/random
PRNGSeedSource=0
ProxyHost=
ProxyMode=0
ProxyPassword=
ProxyPort=
ProxyUser=
QueryTimeout=0
ReportCodepageConversionErrors=0
SnapshotSerializable=0
TrustStore=
TrustStorePassword=
ValidateServerCertificate=0
WorkStationID=
XMLDescribeType=-10
SSLLibName=/usr/lib64/libssl.so.10
CryptoLibName=/usr/lib64/libcrypto.so.10
…….
………………
SAS 9.4 M7 Access to Azure Synapse SQL Pool Database
With SAS 9.4 M7 environment configured with customized DSN entry in odbc.ini, you can use the following code to write and read data table into Azure Synapse Database table.
Code:
%let MYUID=viyadep;
%let MYPWD=XXXXXXX;
%let MYDSN=sqls_gelws;
libname azsqlws sqlsvr noprompt="uid=&MYUID;pwd=&MYPWD;dsn=&MYDSN;" stringdates=yes;
data azsqlws.fish_sas ;
set sashelp.fish ;
run;
Proc SQL outobs=20;
select * from azsqlws.fish_sas ;
run;quit;
Log extract :
....
..............
5 libname azsqlws sqlsvr noprompt="uid=&MYUID;pwd=&MYPWD;dsn=&MYDSN;" stringdates=yes;
NOTE: Libref AZSQLWS was successfully assigned as follows:
Engine: SQLSVR
Physical Name: sqls_gelws
6
7 data azsqlws.fish_sas ;
8 set sashelp.fish ;
9 run;
NOTE: There were 159 observations read from the data set SASHELP.FISH.
NOTE: The data set AZSQLWS.fish_sas has 159 observations and 7 variables.
10
11 Proc SQL outobs=20;
12 select * from azsqlws.fish_sas ;
WARNING: Statement terminated early due to OUTOBS=20 option.
13 run;quit;
..........
.................
Result Output:
Species Weight Length1 Length2 Length3 Height Width
---------------------------------------------------------------------
Perch 700 34.5 37 39.4 10.835 6.2646
Roach 290 24 26 29.2 8.8768 4.4968
Bream 600 29.4 32 37.2 15.438 5.58
Roach 150 20.4 22 24.7 5.8045 3.7544
Bream 500 28.7 31 36.2 14.3714 4.8146
Pike 567 43.2 46 48.7 7.792 4.87
Bream 650 31 33.5 38.7 14.4738 5.7276
Bream 475 28.4 31 36.2 14.2628 5.1042
Roach 0 19 20.5 22.8 6.4752 3.3516
Bream 290 24 26.3 31.2 12.48 4.3056
Perch 5.9 7.5 8.4 8.8 2.112 1.408
Bream 714 32.7 36 41.5 16.517 5.8515
Roach 120 18.6 20 22.2 6.216 3.5742
Perch 265 25.4 27.5 28.9 7.0516 4.335
Perch 556 32 34.5 36.5 10.2565 6.3875
Bream 500 29.1 31.5 36.4 13.7592 4.368
Bream 975 37.4 41 45.9 18.6354 6.7473
Bream 500 26.8 29.7 34.5 14.1795 5.2785
Whitefish 270 23.6 26 28.7 8.3804 4.2476
Bream 340 23.9 26.5 31.1 12.3778 4.6961
Bulk Load from SAS 9.4 M7 to Azure Synapse SQL Pool Database
With SAS 9.4 M7 environment configured with customized DSN entry in odbc.ini, MS-SQL Server configured to access ADLS2 blob storage, and Azure user application configured to access resources using device code method, you can use the following code to BULK load SAS datasets to Azure Synapse database table.
To bulk load SAS datasets with a special character (like “”,’,`,!,#,$,@, etc.) in data elements use “BL_USE_ESCAPE=YES” in the LIBNAME statement. The use of this option in the LIBNAME statement will slow down the performance as it requires additional scanning/processing of data elements.
Very first-time usage of BULK load statement generates an error message for Azure authentication. The SAS log displays the Microsoft device authentication URL and device code to authenticate and generate Azure Access Key.
Code:
%let MYSTRGACC="utkumaviya4adls2";
%let MYSTRGFS="fsdata";
%let MYFOLDER="bl_data";
%let MYAZUID="MANAGED IDENTITY";
%let MYDNSSUFFIX="dfs.core.windows.net" ;
%let MYTNTID="b1c14d5c-3625-45b3-XXXXXXXXXXXXXXXXX";
%let MYAPPID="a2e7cfdc-93f8-4f12-XXXXXXXXXXXXXXXXX";
%let MYUID=viyadep;
%let MYPWD=XXXXXXX;
%let MYDSN=sqls_gelws;
options sastrace=',,,ds' sastraceloc=saslog nostsuffix;
options azuretenantid=&MYTNTID;
libname azsqlws sqlsvr noprompt="uid=&MYUID;pwd=&MYPWD;dsn=&MYDSN;" stringdates=yes
BULKLOAD=YES
BL_ACCOUNTNAME=&MYSTRGACC
BL_FILESYSTEM=&MYSTRGFS
BL_FOLDER=&MYFOLDER
BL_IDENTITY=&MYAZUID
BL_DNSSUFFIX=&MYDNSSUFFIX
BL_APPLICATIONID=&MYAPPID
BL_DELETE_DATAFILE=NO
;
Proc SQL ;
drop table azsqlws.prdsal2_sas ;
run;quit;
data azsqlws.prdsal2_sas ;
set sashelp.prdsal2 ;
run;
Proc SQL outobs=20;
select * from azsqlws.prdsal2_sas ;
run;quit;
Very-first time Error Log:
…………
…………………………..
ERROR: To sign in, use a web browser to open the page https://microsoft.com/devicelogin and enter the code EQDQJKQWN to
authenticate.
NOTE: The DATA step has been abnormally terminated.
…………
…………………………..
Log extract :
.....
..............
1 %let MYSTRGACC="utkumaviya4adls2";
2
3 %let MYSTRGFS="fsdata";
4 %let MYFOLDER="bl_data";
5 %let MYAZUID="MANAGED IDENTITY";
6 %let MYDNSSUFFIX="dfs.core.windows.net" ;
7 %let MYTNTID="b1c14d5c-3625-45b3-XXXXXXXXXXXXXXXXX";
8 %let MYAPPID="a2e7cfdc-93f8-4f12-XXXXXXXXXXXXXXXXX";
9
10 /* Note : variable value in quotes generate errors, So keep it without quotes. */
11 %let MYUID=viyadep;
12 %let MYPWD=XXXXXXXX;
13 %let MYDSN=sqls_gelws;
14
15 options sastrace=',,,ds' sastraceloc=saslog nostsuffix;
16 options azuretenantid=&MYTNTID;
17
18
19 libname azsqlws sqlsvr noprompt="uid=&MYUID;pwd=&MYPWD;dsn=&MYDSN;" stringdates=yes
20 BULKLOAD=YES
^L2 The SAS System 21:12 Wednesday,
March 31, 2021
21 BL_ACCOUNTNAME=&MYSTRGACC
22 BL_FILESYSTEM=&MYSTRGFS
23 BL_FOLDER=&MYFOLDER
24 BL_IDENTITY=&MYAZUID
25 BL_DNSSUFFIX=&MYDNSSUFFIX
SQLSRV: AUTOCOMMIT is NO for connection 0
26 BL_APPLICATIONID=&MYAPPID
27 BL_DELETE_DATAFILE=NO
28 ;
NOTE: Libref AZSQLWS was successfully assigned as follows:
Engine: SQLSVR
Physical Name: sqls_gelws
......
.............
33
34 data azsqlws.prdsal2_sas ;
35 set sashelp.prdsal2 ;
36 run;
SQLSRV_3: Prepared: on connection 1
SELECT * FROM "prdsal2_sas" WHERE 0=1
Summary Statistics for SQLSVR are:
Total SQL prepare seconds were: 0.000027
Total seconds used by the SQLSVR ACCESS engine were 0.030228
SQLSRV: AUTOCOMMIT is NO for connection 2
^L3 The SAS System 21:12 Wednesday,
March 31, 2021
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
SQLSRV_4: Executed: on connection 2
CREATE TABLE "prdsal2_sas" ("COUNTRY" varchar(10),"STATE" varchar(22),"COUNTY" varchar(20),"ACTUAL" money,"PREDICT"
money,"PRODTYPE" varchar(10),"PRODUCT" varchar(10),"YEAR" smallint,"QUARTER" int,"MONTH" date,"MONYR" date)
SQLSRV: COMMIT performed on connection 2.
SQLSRV: Bulkload seconds used for file open: 0.000327
SQLSRV: Bulkload seconds used for setup: 0.004687
NOTE: There were 23040 observations read from the data set SASHELP.PRDSAL2.
NOTE: The data set AZSQLWS.prdsal2_sas has 23040 observations and 11 variables.
SQLSRV: Bulkload seconds used for writes: 0.023119
SQLSRV: Bulkload seconds used for file close: 0.000051
SQLSRV: Bulkload seconds used for upload: 0.401595
SQLSRV_5: Executed: on connection 2
COPY INTO "prdsal2_sas" ("COUNTRY","STATE","COUNTY","ACTUAL","PREDICT","PRODTYPE","PRODUCT","YEAR","QUARTER","MONTH","M
ONYR") FROM
'https://utkumaviya4adls2.dfs.core.windows.net/fsdata/bl_data/SASSRBL_8E265198-7861-7843-94D3-0565FDE3EFFD-00.dat','htt
ps://utkumavi
ya4adls2.dfs.core.windows.net/fsdata/bl_data/SASSRBL_8E265198-7861-7843-94D3-0565FDE3EFFD-01.dat' with ( file_type='CSV
',
fieldterminator='0x07', rowterminator='0x0A', CREDENTIAL = ( IDENTITY = 'MANAGED IDENTITY' ) )
SQLSRV: Bulkload seconds used for COPY: 1.226117
SQLSRV: COMMIT performed on connection 2.
SQLSRV: Bulkload total seconds used: 1.651064
SQLSRV: COMMIT performed on connection 2.
Summary Statistics for SQLSVR are:
Total SQL execution seconds were: 1.696367
Total SQL prepare seconds were: 0.000027
Total seconds used by the SQLSVR ACCESS engine were 2.162346
SQLSRV: COMMIT performed on connection 2.
……
…………………
38 Proc SQL outobs=20;
39 select * from azsqlws.prdsal2_sas ;
SQLSRV: AUTOCOMMIT is NO for connection 2
SQLSRV: AUTOCOMMIT turned ON for connection id 2
SQLSRV_6: Prepared: on connection 2
SELECT * FROM "prdsal2_sas"
SQLSRV_7: Executed: on connection 2
Prepared statement SQLSRV_6
WARNING: Statement terminated early due to OUTOBS=20 option.
..........
.................
Result Output:
COUNTRY STATE COUNTY ACTUAL PREDICT PRODTYPE PRODUCT
YEAR QUARTER MONTH MONYR
------------------------------------------------------------------------------------------------------------------------------
U.S.A. California 1825.0000 756.0000 FURNITURE SOFA
1995 2 1993-04-01 1995-04-01
U.S.A. Illinois Winnebago 29.0000 48.0000 OFFICE CHAIR
1995 1 1993-02-01 1995-02-01
Mexico Baja California Norte 75.0000 1121.0000 OFFICE DESK
1997 2 1993-04-01 1997-04-01
Canada Saskatchewan 305.0000 614.0000 OFFICE CHAIR
1997 2 1993-06-01 1997-06-01
U.S.A. California 1783.0000 568.0000 FURNITURE SOFA
1995 1 1993-02-01 1995-02-01
U.S.A. Illinois Winnebago 11.0000 130.0000 FURNITURE BED
1995 4 1993-12-01 1995-12-01
…………
…………………………..
The following screenshot describes the intermediate file staged at Azure ADLS2 Blob Storage before copying it into the Synapse Database table. The intermediate files did not get deleted from the Blob Storage folder due to “BL_DELETE_DATAFILE=NO” in the LIBNAME statement, by default it’s set to ‘YES’.
Constraints/Limitations
- The BULK load process supports data to load into a new Synapse database table. It creates a new table to save the data at the Azure Synapse database. The data append to an existing Synapse database table is not yet supported.
- As a workaround, the user can BULK load the data into Synapse “Temporary/Staging” database with access to create a new table. Later, execute an explicit database SQL from the SAS program to append the data into an existing database table.
- The Synapse Bulk Load applies to one-way traffic, which means the process uploads data from SAS 9.4 to Azure Synapse. The Bulk download from Azure Synapse to SAS 9.4 is not supported.
Important Links:
Bulk Loading with Microsoft SQL Server
CAS accessing Azure Data Lake files
What is Azure Synapse SQL Pool ?
Find more articles from SAS Global Enablement and Learning here.
