Many common data platforms already in use are being refactored and delivered as service offerings to Azure cloud customers. Azure offers database service technologies that are familiar to many organizations. It is important to understand the terminology and the different database services to best meet the demands of your business use case or application. Benefits to organizations are reducing hardware and software footprint to manage. Databases that scale automatically to meet business demand and software that optimizes and creates backups means organizations can spend more time deriving insights from their data and less time managing infrastructure.
SAS provides efficient SAS Data Connectors and SAS In-Database Technologies support to Azure database variants. A data storage running in Azure cloud is much like an on-premise database, but instead Microsoft manages the software and hardware. Azure services can take care of the scalability and high availability of the database with Database as a Service (DBaaS) offerings and minimal user input. SAS integrates with Azure cloud databases whether SAS is running on-premise or in the cloud.
SAS AND AZURE SQL DATABASE
Organizations can connect and access data from an Azure SQL Database using SAS/Access to Microsoft SQL Server from SAS. All the features from SAS/Access to SQL Server running on-premise would be available in the cloud as well. Running the SQL database in the cloud gives organizations elasticity to scale the database. Let’s look at code samples to connect and access data from using SAS library and CAS library.
LIBNAME STATEMENT TO CONNECT TO AZURE SQL DB
Azure SQL Database connection information typically specified in the odbc.ini file on the SAS servers, along with a data source name (DSN). Specify the DSN name in the libname connection parameter.
libname mydblib sqlsvr noprompt="uid=sgfdemo; pwd=demopassword; dsn=Azure SQL Database;" stringdates=yes;
The SQLSVR connection parameter enables the LIBNAME statement to use SAS/Access to SQL Server to connect to Azure SQL Database.
SAS VIYA CAS LIBRARY TO CONNECT TO AZURE SQL DB
Similarly, SAS/Access to SQL Server can be used to connect to an Azure SQL Database using a data connector. This approach is helpful to organizations with streamlined data movement when SAS Viya is running in the cloud to load and save data between Azure SQL Database and SAS Viya in-memory servers.
cas casauto;
caslib azsqldb desc='Microsoft SQL Server Caslib' dataSource=(srctype='sqlserver'
username='sas'
password='demopassword'
sqlserver_dsn="Azure SQL Database"
catalog='*');
Azure SQL Database connection information has to be added to the odbc.ini files in all the CAS cluster nodes. This facilitates optimized data movement between multiple CAS worker nodes and Azure SQL Database.
SRCTYPE – Source type SQLSERVER is the key parameter to connect to an Azure SQL Database using SAS Viya Data Connector to Microsoft SQL Server.
Load and Save SAS Dataset to Azure SQL DB
With one table in the Azure SQL Database, let’s load another table from a SAS Viya in- memory cluster to the Azure SQL Database. In the following code sample, first we are loading a SAS data set named cars to a CAS in-memory cluster. Then the distributed CAS in-memory table is saved to the Azure SQL Database.
proc casutil;
load data=sashelp.cars outcaslib="azsqldb" casout="cars" replace;
save casdata="cars" casout="cars" replace;
quit;
One parameter to mention here, OUTCASLIB, contains the name of the CAS library that we created earlier to access the Azure SQL Database. Information about other parameters has been furnished in the ADLS section above.
SAS VIYA AND AZURE MYSQL DATABASE
MySQL is another widely used relational database management system that is accessible from SAS Viya as an Azure MySQL database with elastic server configurations to support various data volumes. SAS Data Connector to MySQL can be used to access, load, and save data between CAS in-memory cluster nodes and an Azure MySQL Database.
Other flavors of MySQL databases such as Azure MariaDB Database can also be accessed using SAS Data Connector to MySQL. Steps are exactly the same as MySQL, just modify the server host to Azure MariaDB, along with the appropriate credentials.
cas casauto;
caslib azurems desc='MySQL Caslib' dataSource=(srctype='mysql' host='sgfdemo.mysql.database.azure.com' username='sas@sgfdemo' password='demopassword' database="demo")
/* global */
;
The MySQL client must be installed on all the CAS cluster nodes. The MySQL client library path must be added to the CAS configuration settings file on the nodes.
SRCTYPE – Source type MYSQL is the key parameter to connect to an Azure MySQL Database using SAS/Access to MySQL.
LOAD AND SAVE SAS DATASET TO AZURE MYSQL DATABASE
proc casutil;
load data=sashelp.cars outcaslib="azurems" casout="cars" replace;
save casdata="cars" casout="cars" replace; list files;
quit;
SAS Viya uses SAS Data Connector to MySQL to save a CAS in-memory table named cars to an Azure MySQL Database. The Save CAS action, which is part of the TABLES CAS action set has been called to perform a serial save in this case.
SAS VIYA AND AZURE POSTGRESQL DATABASE
The SAS platform can access data from another popular relational database management system(RDBMS), PostgreSQL. Azure PostgreSQL Database is a Database as a Service (DBaaS) offering to support data movement as needed through various server configurations based on data volume and demand.
SAS LIBRARY TO LOAD SAS DATASET
libname azurepg postgres server="sgfdemo.postgres.database.azure.com" port=5432
user=sas@sgfdemo password='demopassword' database=postgres;
/*Load a SAS dataset to Azure PostgreSQL database*/
data azurepg.cars; set sashelp.cars; run;
LIBNAME statement uses the POSTGRES keyword to utilize SAS/Access Engine to PostgreSQL to connect to an Azure PostgreSQL Database. In this example, a SAS dataset named cars is loaded to the destination cloud PostgreSQL database.
CAS LIBRARY TO AZURE POSTGRESQL
cas casauto;
caslib postgrescaslib desc='PostgreSQL Caslib' dataSource=(srctype='postgres' server='sgfdemo.postgres.database.azure.com' username='sas@sgfdemo'
password='demopassword' database="postgres");
The CAS library postgrescaslib is created by connecting to the destination Azure PostgreSQL Database. Source type POSTGRES is the keyword that utilizes SAS Data Connector to PostgreSQL.
Load and Save SAS Dataset to Azure PostgreSQL DB
proc casutil;
load data=sashelp.class outcaslib="postgrescaslib" casout="class" replace;
save casdata="class" casout="class" replace;
list files;
quit;
SAS Viya uses SAS Data Connector to PostgreSQL to save the CAS in-memory table named cars to the Azure PostgreSQL Database. The Save CAS action, which is part of the TABLES CAS action set has been called to perform a serial save in this case.
The LIST FILES action in the CASUTIL procedure show the cars table has been saved to the database in the cloud.
Once the data is loaded to CAS in-memory servers for distributed parallel processing or SAS compute server, advanced analytics and model methods can be applied to get relevant insight out of the prepared data. Whether the final decision is to keep data on-premise or in the cloud or a hybrid approach, SAS Data Connector supports virtually any data sources to access, load, move, model data efficiently, or navigate through the analytic life cycle. It is important to think about the use case for your database and the type of data you plan to store before you select an Azure database service.
