With the new SAS Viya release (2021.1 and later), CAS can read and write Parquet data files to Azure ADLS2 Blob storage, in addition to CSV and, ORC data files. The data read and write for the parquet file is supported by ADLS CASLIB with the parallel data load mechanism. The CAS worker PODs are loading Parquet data file in parallel from ADLS2 Blob storage.   CAS supported data file type at ADLS2 (Blob Storage) ORC - Serial method to load/save CAS data files to ADLS2 CSV - Parallel method to load CAS, serial method to save CAS data to ADLS2 Parquet - Parallel method to load and save CAS data files to ADLS2   Pre-requisites User access to Azure Storage Account with Storage Blob Data Contributor role Azure application with permission to access Azure Data lake and Azure Storage Azure File Share mounted to CAS PODs using K8s PVC for the access key CAS System parameter Azureauthcacheloc= set to use Azure Access key from File Share PVC   Data access path The following picture describes the CAS load/save from parquet data files stored at 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.   To load CAS in parallel from ADLS2 Blob Storage, it requires access to an Azure Access Key file from each CAS worker's PODs. You can mount a PVC to CAS PODs and configure the CAS system parameter cas.AZUREAUTHCACHELOC= to share the Azure Access Key file amongst the CAS PODs.     The detailed steps are discussed in the article manage-azure-access-key-with-azureauthcacheloc-part-2 , for creating and mounting a PVC to CAS PODs against Azure File Share and updating system parameter cas.AZUREAUTHCACHELOC= .     The detailed steps are discussed in the article cas-accessing-azure-data-lake-files, for configuring Azure user application and Storage Account to access ALDS2 Blob storage.     The following screenshot describes the location of the Azure Access Key file (.json file) shared amongst the CAS PODs.       CAS save/load to/from Parquet data files at ADLS2 Blob storage   With the CAS PODs mounted to Azure File share and AZUREAUTHCACHELOC= parameter set to Azure File Share, you can use the following code to save and load Parquet data files to ADLS2 blob storage. The ADLS CASLIB will share the Azure Access key amongst the CAS PODs from the central location.     Code:   %let MYSTRGACC="utkumaviya4adls2"; %let MYSTRGFS="fsdata"; %let MYTNTID="b1c14d5c-XXXXXXXX-xxxxxxxxxxxx" ; %let MYAPPID="a2e7cfdc-XXXXXXXXXXXX-xxxxxxxxxxxx"; CAS mySession SESSOPTS=(CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true); caslib ADLS2 datasource=( srctype="adls" accountname=&MYSTRGACC filesystem=&MYSTRGFS dnsSuffix=dfs.core.windows.net timeout=50000 tenantid=&MYTNTID applicationId=&MYAPPID ) path="/sample_data" subdirs; proc casutil incaslib="ADLS2"; list files ; run;quit; /* Save CAS Data to ADLS2 storage data file (parquet) */ proc casutil incaslib="ADLS2" outcaslib="ADLS2" ; load data=sashelp.cars casout="cars" replace; save casdata="cars" casout="cars.parquet" replace; run;quit; /* CAS load from Parquet data files at ADLS2 storage */ proc casutil incaslib="ADLS2" outcaslib="ADLS2"; load casdata="cars.parquet" casout="cars_parquet" replace ; list tables ; run;quit; cas mysession terminate;   Log extract :   ..... .............. 78 /* Save CAS Data to ADLS2 storage data file (parquet) */ 79 proc casutil incaslib="ADLS2" outcaslib="ADLS2" ; NOTE: The UUID '2d06260a-99c8-5543-b9b4-478a03fd394f' is connected using session MYSESSION. 80 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 "ADLS2" caslib as "CARS". 81 save casdata="cars" casout="cars.parquet" replace; NOTE: Executing action 'table.save'. NOTE: Cloud Analytic Services saved the file cars.parquet in caslib ADLS2. NOTE: Action 'table.save' used (Total process time): ...... ............. .................. 78 /* CAS load from Parquet data files at ADLS2 storage */ 79 proc casutil incaslib="ADLS2" outcaslib="ADLS2"; NOTE: The UUID '2d06260a-99c8-5543-b9b4-478a03fd394f' is connected using session MYSESSION. 81 load casdata="cars.parquet" casout="cars_parquet" replace ; NOTE: Executing action 'table.loadTable'. NOTE: Cloud Analytic Services made the external data from cars.parquet available as table CARS_PARQUET in caslib ADLS2. NOTE: Action 'table.loadTable' used (Total process time): NOTE: The Cloud Analytic Services server processed the request in 0.739576 seconds. .......... .................   The following screenshot describes Parquet data file saved to Azure ADLS2 Blob storage.       CAS and Parquet data files at ASDLS2 - facts and features CAS can read single .parquet files or n-number of .parquet files from a subfolder. CAS can read parquet files and folders with .parquet extensions only. When CAS is loaded from an ADLS2 sub-folder containing n-number of .parquet data files with matching file structure, all of the data files become part of a consolidated single CAS table. CAS can read simple and plain structure parquet data files from ADLS2. If the data files contain complex data types like "struct", "array", "Json", etc. these columns cannot be read into separate multiple columns. Before loading a complex (containing arrays, structs, map, etc.) parquet data file into CAS, read it using a third-party application (Azure Synapse or Databricks or HDInsight cluster) into tabular format. CAS tables saved to parquet files at ADLS2 are uncompressed. Parquet files created by CAS at ADLS2 can be read by third-party applications like Azure Synapse, Databricks, SPARK, etc. If a partitioned CAS table is saved into parquet data files at ADLS2, the data files does not retain the CAS partition. CAS creates parquet data files at ADLS2 based on number of CAS worker nodes and size of the CAS table. It does not create partitioned data files based on data columns. CAS does not support data read by data column partition from a sub-folder containing partitioned parquet data file. A global CAS table loaded from parquet data file does not retain the same format into in-memory table, it converts into SASHDAT format. This results into considerable data size difference between parquet data file and CAS table size (e.g. ~ 330 MB parquet data files = ~ 5.8 GB CAS table (~16 times). If parquet data file structure has 20 columns and looking to load CAS from just 5 columns data, is not supported .   Important Links: CAS accessing Azure Data Lake files Manage Azure Access Key with AZUREAUTHCACHELOC= (Part -2)   cas.AZUREAUTHCACHELOC AZUREAUTHCACHELOC System Option Azure Data Lake Storage Data Source       Find more articles from SAS Global Enablement and Learning here. ... View more

With SAS Viya 3.5 release, SAS Viya users can access Azure ADLS2 Blob Storage to read and write data file types CSV, ORC, and Parquet in Viya 4. The access to ADLS2 Blob Storage is supported by using ADLS CASLIB. While working with ADLS CASLIB, one of the challenges is managing the Azure Access Key file (.json file) in the CAS environment. It is a tedious task for a Multi-Node CAS environment. It is even more challenging in Viya 4 with a Multi-Node CAS environment running on POD/Container at K8S services.   This blog post talks about how to manage the Azure Access Key file for ADLS CASLIB. The blog post is in two-part, the first part talks about the overall issue and resolution in Viya 3.5, and second part extends to Viya 4.     CAS Supported data file types at Azure ADLS2 Blob Storage: CSV and ORC data files (with Viya 3.5) Parquet data files (with Viya 4)   With the default CAS setting, very first-time access of ADLS CASLIB generates an error message with a device-code, and Microsoft link to authenticate the user access. The device code registration process authorizes the user and generates an Azure Access key file (.json file) under the ‘cas’ user home directory   Error msg from Very first-time access of ADLS CASLIB :   NOTE: Executing action 'table.fileInfo'. ERROR: To sign in, use a web browser to open the page https://microsoft.com/devicelogin and enter the code AMUPDJ8N7 to authenticate. ERROR: Pending end-user authorization. ERROR: The action stopped due to errors. NOTE: Action 'table.fileInfo' used (Total process time):   The device code displayed in the SAS log is from the CAS controller server only and not from all CAS worker nodes. If you register the device using SAS log information, it will generate the Azure Access Key file (.json) at the CAS controller server only. With the Access Key file at CAS controller, the data load for the ORC data file succeeds because CAS is using the serial method to load. Whereas, the data load for CSV/Parquet data file fails with the error msg “Pending end-user authorization” and no device code in the log. The data load for CSV/Parquet data file fails because CAS is using the Parallel method to load     Error msg from very first-time access of ADLS CASLIB for CSV/Parquet data file load :   NOTE: bytes moved 114.62K NOTE: The Cloud Analytic Services server processed the request in 0.479026 seconds. 85 load casdata="sas_orsales.csv" casout="sas_orsales_CSV" replace ; NOTE: Executing action 'table.loadTable'. ERROR: Pending end-user authorization. ERROR: The action stopped due to errors. NOTE: Action 'table.loadTable' used (Total process time):   To load CAS in parallel from ADLS2 Blob Storage, it requires an Azure Access Key file at each CAS worker node. To generate the Access Key file at each CAS worker node, it requires you to log in to each CAS worker nodes one by one and view the .json file to register the device code. It is a very tedious task when you have CAS with an n-number of worker nodes. It's even more difficult with Viya 4.0 running on K8S services.     Instead of generating the Azure Access Key file at each CAS worker node, you can use cas.AZUREAUTHCACHELOC= system parameter to share the Key file from a central location. This parameter enables you to assign the file location to store and access the Azure Access Key file. You can mount a secured shared network drive to each CAS worker node with access for user cas. Assign the same network drive location to cas.AZUREAUTHCACHELOC= parameter. Very first-time access of ADLS CASLIB will generate the Azure Access Key file at the central location after registering the device code. Next time access to ADLS CASLIB will share the same central Key file to each CAS worker node.     The following diagram describe the usage of cas.AZUREAUTHCACHELOC= parameter to store Azure Access Key file at a central location and shared by CAS controller and CAS Worker nodes.         Update AZUREAUTHCACHELOC= system parameter in Viya 3.5   Mount a shared network drive / Azure File Share to each CAS Nodes   The following example describes an Azure File Share mount to each CAS Worker nodes. The statement to mount the Azure File Share is available at the “connect VM” tab. It provides detailed syntax for each OS environment. You can execute the statement for the respective OS, and a new file system will be available to CAS servers.   An Azure File Share and “connect VM” option.     When clicked on the “connect VM” tab, it pops-up with the mount statement. You can execute the respective statement at each CAS node manually or using an ansible tool.         An example entry from the “/etc/fstab” at CAS server :   //utkumaviya4.file.core.windows.net/aksshare /mnt/aksshare cifs nofail,vers=3.0,credentials=/etc/smbcredentials/utkumaviya4.cred,dir_mode=0777,file_mode=0777,serverino   Apply cas.AZUREAUTHCACHELOC= parameter   Once Shared Network drive or File Share munted to the CAS servers, you can configure the AZUREAUTHCACHELOC parameter. The "cas.AZUREAUTHCACHELOC=" is assigned by adding an entry in the "casconfig_usermod.lua" file. You can also use vars.yml to assign the CAS system variable as a deployment step. The following example describes the AZUREAUTHCACHELOC= assignment with an Azure File Share mounted to each CAS node.     An example entry from casconfig_usermod.lua :   cas.AZUREAUTHCACHELOC="/mnt/aksshare/AzureAccessKeys/cas/"     The CAS server after a change is applied to AZUREAUTHCACHELOC parameter and restart.      With the AZUREAUTHCACHELOC parameter set to a central location, the access to ADLS CASLIB reads the Azure Access Key file from a central location. The data load for the ADLS2 CSV data file will not fail as each CAS Worker node find the access key file at a central location.   %let MYSTRGACC="utkuma5adls2strg"; %let MYSTRGFS="fsutkuma5adls2strg"; %let MYTNTID="b1c14d5c-XXXXXXXX-xxxxxxxxxxxx" ; %let MYAPPID="a2e7cfdc-XXXXXXXXXXXX-xxxxxxxxxxxx"; CAS mySession SESSOPTS=(CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true); caslib ADLS2 datasource=( srctype="adls" accountname=&MYSTRGACC filesystem=&MYSTRGFS dnsSuffix=dfs.core.windows.net timeout=50000 tenantid=&MYTNTID applicationId=&MYAPPID ) path="/sample_data" subdirs; proc casutil incaslib="ADLS2"; list files ; run; quit; /* CAS load from ADLS2 storage data file */ proc casutil incaslib="ADLS2" outcaslib="ADLS2"; load casdata="sas_orsales.csv" casout="sas_orsales_CSV" replace ; list tables ; run; quit; cas mysession terminate;     The Azure Access Key file (.json file) created at Azure File Share.         The Azure Access Key File.   [viyadep@cas02 etc]$ cat /mnt/aksshare/AzureAccessKeys/cas/.sasadls_10002.json {"refresh_token":"0.AAAAXEXXXXXXXXXXXXXXXXXXXX-XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX   Summary: Azure Access Key file (.json file) can be generated once and saved at a central location to share with CAS nodes using cas.AZUREAUTHCACHELOC parameter. In-case of CAS environment/POD/Container recycled/rebuild, use existing Azure Access Key at a central location. Azure Access Key file contains refresh_token option, by which it will refresh the Access Key in-case expired after a certain time. The Azure Access Key generated at the CAS server is not tied to the Physical Hardware Server. It’s tied to the Azure user id, Azure application id, and Azure Resources. The Azure Access Key is valid as long as the password did not change or expired for Azure User who generated the key     Stay tuned-in for second part, featuring managing Azure Access key in Viya 4.       Important Links: cas.AZUREAUTHCACHELOC AZUREAUTHCACHELOC System Option Azure Data Lake Storage Data Source     ... View more

The SAS Viya/CAS and Viya SPRE support data file access from Azure Blob Storage (ADLS2). The support is for limited data file types. The SPRE supports ORC and CSV data files read/write using ORC LIBNAME engine. The SAS Viya 3.5/CAS supports ORC and CSV data files read/write using ADLS CASLIB. The SAS Viya 4.0 supports additional parquet data files read/write using ADLS CASLIB. The BASE SAS 9.4 does not have a LIBNAME engine to read/write data files from ADLS2 Blob storage.   With the limited option, how do you load CAS or read into SAS 9.4 if you have avro, json, parquet, xml etc. data files at Azure Blob storage?   Azure HDInsight can help in this situation! Azure HDInsight can read all these data files from the Blob storage as Hive tables. The SAS application can access these HDI hive table using SAS/ACCESS Interface to Hadoop or SAS Data connector to Hadoop.   The following steps integrate Blob Storage with the HDI cluster and enable users to read and write Azure Blob Storage data files from BASE SAS9.4 and Viya CAS.     Create Azure HDInsight cluster with access to Blob Storage Create the Azure HDInsight Cluster with a Managed Azure Identity having access to Blob Storage. It enables the HDI cluster for seamless access to Azure Blob Storage. The following screen describes the Azure Managed Identity with access to Blob Storage.       The following screen describes the usage of ADLS2 Blob Storage and Managed Identity while creating the HDInsight cluster.     Create HDI Hive database and table with data files at ADLS2 To enable the HDI Hive services to read and write data to ADLS2 storage, it requires schema created with data files located at ADLS2. With access to the HDInsight Hadoop cluster, you can collect the Hadoop Jar and config files in the SAS environment. The HDInsight Hadoop cluster allows ssh to login to the master node server. A valid user can log in to the HDI master node and execute the SAS-Hadoop-Tracer script to collect required Hadoop Jars and config files.     Sample code to create HDI Hive database   %let HDINM=hdiutkuma8; %let MYSTRGACC=utkuma8adls2strg; %let MYSTRGFS=fsdata; option set = SAS_HADOOP_CONFIG_PATH = "/opt/sas/viya/config/data/HDIHadoopData/conf"; option set = SAS_HADOOP_JAR_PATH = "/opt/sas/viya/config/data/HDIHadoopData/lib"; options sastrace = ',,,sd' sastraceloc = saslog ; PROC SQL ; connect to hadoop as hvlib (uri="jdbc:hive2://&HDINM..azurehdinsight.net:443/default;ssl=true?hive.server2.transport.mode=http;hive.server2.thrift.http.path=hive2" server="&HDINM..azurehdinsight.net" user="admin" pw="XXXXXXX" schema="default"); execute (CREATE database geltest LOCATION "abfs://&MYSTRGFS@&MYSTRGACC..dfs.core.windows.net/hive_db" ) by hvlib; quit;   By executing the above code, it creates an HDI Hive database with a data folder located at ADLS2. When BASE SAS 9.4 or CAS uses "geltest" database/schema name in LIBNAME or CASLIB, it will access data from ADLS2. Any data save from BASE SAS 9.4 or CAS will end-up at ADLS2 storage.       Sample code to create HDI Hive table For existing datafiles (created by third-party applications), you can create table schema using the following example statement. Notice the Database/Schema name and data file location used as ADSL2 Blob Storage location.     %let HDINM=hdiutkuma8; %let MYSTRGACC=utkuma8adls2strg; %let MYSTRGFS=fsdata; option set = SAS_HADOOP_CONFIG_PATH = "/opt/sas/viya/config/data/HDIHadoopData/conf"; option set = SAS_HADOOP_JAR_PATH = "/opt/sas/viya/config/data/HDIHadoopData/lib"; options sastrace = ',,,sd' sastraceloc = saslog ; PROC SQL ; connect to hadoop as hvlib (uri="jdbc:hive2://&HDINM..azurehdinsight.net:443/geltest;ssl=true?hive.server2.transport.mode=http;hive.server2.thrift.http.path=hive2" server="&HDINM..azurehdinsight.net" user="admin" pw="XXXXXX" schema="geltest"); execute (drop table flight_reporting ) by hvlib; execute (CREATE EXTERNAL TABLE flight_reporting ( YEAR varchar(4) , MONTH varchar(4), ORIGIN_AIRPORT_ID varchar(10), ORIGIN_AIRPORT_SEQ_ID varchar(10), ORIGIN_CITY_MARKET_ID varchar(10), ORIGIN_CITY_NAME varchar(20), DEST_AIRPORT_ID varchar(10), DEST_AIRPORT_SEQ_ID varchar(10), DEST_CITY_MARKET_ID varchar(10), DEST_CITY_NAME varchar(20) ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' STORED AS TEXTFILE LOCATION "abfs://&MYSTRGFS@&MYSTRGACC..dfs.core.windows.net/flight_data" ) by hvlib;       SAS 9.4 access to ADLS2 data files via HDI The SAS 9.4 (M6/7) can access hive tables from the HDInsight Hadoop cluster using SAS/ACCESS Interface to Hadoop technology. The following diagram describes the data access path from SAS 9.4 to Blob Storage via HDInsight.     Pre-requisites SAS/ACCESS Interface to Hadoop at SAS Compute server. HDInsight Hadoop cluster with Hive Catalog. HDI Hadoop Jars and Configuration files at SAS Compute server. JAVA_HOME path in LD_LIBRARY_PATH at SAS Compute server. Database/Table schema created at Hive with data file location to ADLS2.   The SAS Embedded Process ( SAS EP) for Hadoop is not supported at the HDInsight Hadoop cluster, hence the in-database processing components (Code Accelerator, Data Quality Accelerator, etc.) are not supported.   Sample code to read/write Blob Storage data files via HDInsight The following code describes the connection to HDInsight using Hadoop LIBNAME engine. The LIBNAME statement using “geltest” as schema having data folder located at ADLS2. The data save using this LIB creates a subfolder and data files at ADLS2.   %let HDINM=hdiutkuma8; option set = SAS_HADOOP_CONFIG_PATH = "/opt/sas/viya/config/data/HDIHadoopData/conf"; option set = SAS_HADOOP_JAR_PATH = "/opt/sas/viya/config/data/HDIHadoopData/lib"; options sastrace = ',,,sd' sastraceloc = saslog ; libname hdilib HADOOP uri="jdbc:hive2://&HDINM..azurehdinsight.net:443/geltest;ssl=true?hive.server2.transport.mode=http;hive.server2.thrift.http.path=hive2" server="&HDINM..azurehdinsight.net" user="admin" pw="XXXXXXX" schema="geltest" DBCREATE_TABLE_OPTS="stored as parquet" ; data hdilib.prdsal2; set sashelp.prdsal2; run;   Data File at ADLS2:      CAS access to ADLS2 data files via HDI CAS supports the Serial mechanism to load and save data to Azure HDInsight Hive table using SAS Data Connector to Hadoop. The following diagram describes the data path access from CAS to Blob Storage via HDInsight.     Pre-requisites SAS Data Connector to Hadoop at CAS Controller. HDInsight Hadoop cluster with Hive Catalog. HDI Hadoop Jars and Configuration files at CAS Controller JAVA_HOME path in LD_LIBRARY_PATH at CAS configuration setting. Database/Table schema created at Hive with data file location to ADLS2. The SAS Embedded Process ( SAS EP) for Hadoop is not yet supported at the HDInsight Hadoop cluster. Hence, the parallel data load to CAS is not supported. There is a known bug with the Multi-Node load method, the SAS data connector for Hadoop does not work well with specific hive numeric data column to create the split query. Hence, the Multi-Node data load/save to CAS is not supported.   Sample code to read/write Blob Storage data files via HDInsight %let HDINM=hdiutkuma8; CAS mySession SESSOPTS=(CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true); caslib cashive datasource=(srctype="hadoop", server="&HDINM..azurehdinsight.net", username="admin", pwd="XXXXXXX", schema=geltest, uri="jdbc:hive2://&HDINM..azurehdinsight.net:443/geltest;ssl=true?hive.server2.transport.mode=http;hive.server2.thrift.http.path=hive2", hadoopconfigdir="/opt/sas/viya/config/data/HDIHadoopData/conf", hadoopjarpath="/opt/sas/viya/config/data/HDIHadoopData/lib"); proc casutil incaslib="cashive" outcaslib="cashive" ; load data=sashelp.prdsal3 casout="prdsal3" replace; save casdata="prdsal3" casout="prdsal3" replace; quit; proc casutil incaslib="cashive" outcaslib="cashive"; load casdata="prdsal3" casout="prdsal3_new" replace; list tables; quit; CAS mySession TERMINATE;     Important Link: Azure HDInsight Hadoop Data Connector   Blog: SAS Viya/CAS accessing Azure HDInsight   ... View more

With SAS 9.4, SAS PROC S3 enables you to access and manage objects at Amazon S3. SAS PROC S3 is a base SAS procedure which enables you to create buckets, folders, and files at S3. The Amazon S3 is an object storage platform with a simple web service interface to store and retrieve any amount of data.   Before you can use PROC S3, you need an Amazon Web Service (AWS) Access Key ID and Secret Access Key. The Access Key Id and Secret Access Key can be generated from the AWS Console window. If you have an AWS account with admin privileges, you can generate the Access Key Id and Secret Access Key.   The SAS PROC S3 reads security information from configuration files to make a connection to AWS resources. The configuration files are available and accessible from SAS client machine. The configuration file contains primarily the AWS Access Key Id and Secret Access Key. The configuration file can be either AWS command line Interface (CLI) configuration files or the PROC S3 configuration file. The PROC S3 reads both config file during execution. The AWS CLI configuration allows you to configure the profile based connection at SAS client machine.   Unless specified, PROC S3 reads AWS CLI configuration files from default location i.e. “/userhome/.aws/” and uses the default profile. You can specify an alternate location for configuration and credentials file by using AWSCONFIG= and AWSCREDENTIALS= options in PROC S3 statement. To connect S3 using specific profile use PROFILE= and CREDENTIALSPROFILE= options in PROC S3 statement.   A local PROC S3 configuration file can also be used to connect AWS resources. The default PROC S3 configuration file is tks3.conf on Windows and .tks3.conf on Unix and located under user home directory. To specify customized config file name and location use CONFIG= option with PROC S3 statement.   Configuration options that are specified in AWS CLI configuration files override options specified in the PROC S3 configuration file. Options that are specified in the S3 procedure override options that are set in configuration files.   A sample AWS CLI configuration files:   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   A sample local PROC S3 configuration file:     The following list of statements are supported by SAS PROC S3: CREATE - To create an S3 bucket. COPY - To copy a source S3 location object to destination S3 location. DELETE - To delete an S3 location or object. DESTORY - To delete an S3 bucket (empty bucket). GET - To retrieve an S3 object to SAS client machine. GETDIR - To retrieve the contents of an S3 directory. INFO - To print information about an S3 location. LIST - To print contents from an S3 location MKDIR - To create a directory in an S3 location. PUT - To copy files or directory from SAS client machine to S3 location. PUTDIR - To copy a directory from SAS client machine to S3 location. RMDIR - To delete a directory from an S3 location. The following additional statements are supported in SAS 9.4 M5. BUCKET - To set the Acceleration Transfer mode for the specified bucket. GETACCEL - To retrieve the Transfer Acceleration status for a bucket. The following example code describes the creation of an S3 bucket using PROC S3. Note: The first PROC S3 statement is reading AWS CLI configuration files to connect to S3. The second PROC S3 statement is reading a local PROC S3 configuration file to connect S3.   73 PROC S3 PROFILE="sastest1"; 74 CREATE "/sasgel"; 75 run; NOTE: PROCEDURE S3 used (Total process time): real time 0.37 seconds cpu time 0.06 seconds   OR   74 PROC S3 config="/home/utkuma/.tks3.conf"; 75 CREATE "/sasgel"; 76 run; NOTE: PROCEDURE S3 used (Total process time): real time 0.36 seconds cpu time 0.03 seconds   The following screenshot describes a new S3 bucket (sasgel) stored in Amazon S3.     The following example code describes the data file load(write) from SAS Client machine location to an S3 bucket location using PROC S3.   73 PROC S3 PROFILE="sastest1"; 74 PUT "/gelcontent/demo/DM/data/customers.sashdat" "/sasgel/customers.sashdat"; 75 PUT "/gelcontent/demo/DM/data/Prospect_List2.csv" "/sasgel/Prospect_List2.csv"; 76 LIST "/sasgel"; 77 run; Prospect_List2.csv 220088 2018-08-03T20:14:50.000Z customers.sashdat 16272 2018-08-03T20:14:50.000Z NOTE: PROCEDURE S3 used (Total process time): real time 0.95 seconds cpu time 0.08 seconds   The following screenshot describes an Amazon S3 bucket (sasgel) with data files.     The following example code describes the data file deletion from an S3 bucket(location) using PROC S3.   73 PROC S3 PROFILE="sastest1"; 74 DELETE "/sasgel/customers.sashdat"; 75 LIST "/sasgel"; 76 run; NOTE: Deleted object /sasgel/customers.sashdat. Prospect_List2.csv 220088 2018-08-03T20:14:50.000Z NOTE: PROCEDURE S3 used (Total process time): real time 0.25 seconds cpu time 0.06 seconds   The following screenshot describes an Amazon S3 bucket (sasgel) with data files.     The SAS PROC S3 creates additional threads to read and write a data file larger than 5 MB size. These threads enable parallel processing for faster data transfer between SAS and AWS. Transfer Acceleration: Transfer Acceleration is a special mode that is used for faster data load to an S3 location. In this case, data is routed by Amazon to S3 location over the optimized network path. To utilize this feature Amazon may charge you additional data transfer fees. For more information see the AWS documentation.   The GET, GETDIR, PUT and PUTDIR statements take advantage of this mode when it’s enabled for an S3 bucket.   Example:   73 proc S3 PROFILE="sastest1"; 74 BUCKET "sasgel" ACCELERATE; 75 run; NOTE: Transfer acceleration set to on for bucket sasgel. NOTE: PROCEDURE S3 used (Total process time): real time 10.49 seconds cpu time 0.03 seconds   Note: Don’t try to use the values for aws_access_key_Id= and aws_secret_access_key= shown in this post. The listed KeyId and SecretKey in the example have already expired.   For more information: SAS PROC S3 ... View more

Azure HDInsight is a cloud distribution of Hadoop components. A SAS user may wonder how to connect an application with an HDI cluster. The SAS/ACCESS Interface to Hadoop and SAS Data connector to Hadoop technology enables SAS users to connect and access data from the HDInsight cluster.   This post is about the components required for SAS 9.4 (M6/7) and CAS to connect and access the Hive tables from the Azure HDInsight cluster. Azure HDInsight Azure HDInsight is a managed, open-source Hadoop cluster service in the cloud. Azure HDInsight enables a user to create a global and optimized cluster with open-source frameworks such as Apache Hadoop, Spark, Hive, Interactive Query, Kafka, etc. . It provides a scalable Hadoop cluster that enables users to scale up or down based on workload. HDInsight integrates seamlessly with the most popular big data solutions Data access from SAS 9.4 The SAS 9.4 (M6/7) can access hive tables from the HDInsight Hadoop cluster using SAS/ACCESS Interface to Hadoop technology. The following diagram describes the data path and components required to access the HDInsight Hadoop cluster.   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-requisites SAS/ACCESS Interface to Hadoop at SAS Compute server. HDInsight Hadoop cluster with Hive Catalog. HDI Hadoop Jars and Configuration files at SAS Compute server. JAVA_HOME path in LD_LIBRARY_PATH at SAS Compute server. The HDInsight Hadoop cluster allows ssh to login to the master node server. A valid user can log in to the HDI master node and execute the SAS-Hadoop-Tracer script to collect required Hadoop Jars and config files.   The SAS Embedded Process ( SAS EP) for Hadoop is not supported at the HDInsight Hadoop cluster, hence the in-database processing components (Code Accelerator, Data Quality Accelerator, etc.) are not supported. Sample code to read/write data into HDInsight: option set = SAS_HADOOP_CONFIG_PATH = "/opt/sas/viya/config/data/HDIHadoopData/conf"; option set = SAS_HADOOP_JAR_PATH = "/opt/sas/viya/config/data/HDIHadoopData/lib"; libname hdilib HADOOP uri='jdbc:hive2://hdiutkuma5.azurehdinsight.net:443/default;ssl=true?hive.server2.transport.mode=http;hive.server2.thrift.http.path=hive2' server='hdiutkuma5.azurehdinsight.net' user="admin" pw=“XXXXXXXXX" schema="default"; data hdilib.cars_test; set sashelp.cars; run ; Data access from CAS CAS supports the Serial mechanism to load and save data to Azure HDInsight Hive table using SAS Data Connector to Hadoop. The following diagram describes the data path and components requires to access the Azure HDInsight.   Pre-requisites SAS Data Connector to Hadoop at CAS Controller. HDInsight Hadoop cluster with Hive Catalog. HDI Hadoop Jars and Configuration files at CAS Controller JAVA_HOME path in LD_LIBRARY_PATH at CAS configuration setting. The HDInsight Hadoop cluster allows ssh to log in to the master node server. A valid user can log in to the HDI master node and execute the SAS-Hadoop-Tracer script to collect required Hadoop Jars and config files.   The SAS Embedded Process ( SAS EP) for Hadoop is not yet supported at the HDInsight Hadoop cluster. Hence, the parallel data load to CAS is not supported. There is a known bug with the Multi-Node load method, the SAS data connector for Hadoop does not work well with specific hive numeric data column to create the split query. Hence, the Multi-Node data load/save to CAS is not supported. Sample code to read/write data into HDInsight : CAS mySession SESSOPTS=(CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true); caslib cashive datasource=(srctype="hadoop", server="hdiutkuma8.azurehdinsight.net", username="admin", pwd=“XXXXXXXXXX", schema=default, uri='jdbc:hive2://hdiutkuma8.azurehdinsight.net:443/default;ssl=true?hive.server2.transport.mode=http;hive.server2.thrift.http.path=hive2', hadoopconfigdir="/opt/sas/viya/config/data/HDIHadoopData/conf", hadoopjarpath="/opt/sas/viya/config/data/HDIHadoopData/lib"); proc casutil incaslib="cashive" outcaslib="cashive" ; load data=sashelp.prdsal3 casout="prdsal3" replace; save casdata="prdsal3" casout="prdsal3" replace; quit; proc casutil incaslib="cashive" outcaslib="cashive"; load casdata="prdsal3" casout="prdsal3_new" replace; list tables; quit; CAS mySession TERMINATE;   Stay tuned for the next article on "SAS and CAS reading ADL2 files (Parquet, Avro, Jason, XML, etc.) via the HDInsight cluster". Additional Resources Azure HDInsight Hadoop Data Connector ... View more

In SAS® Viya™ the analytic engine is the Cloud Analytic Services (CAS) Server, which uses high performance, multi-threaded analytic code to rapidly process requests against data of any size. Before you can use CAS to work with a data sets, you must load the data sets into the CAS server. With SAS Viya in the 16w20 release, SAS Studio® is the integrated programming environment for CAS. By using SAS Studio, you can write SAS code to load data from the source environment to CAS.   This post explores the parallel data load method from a Hadoop Hive table to the CAS server. Data can be loaded from the Hive table to CAS using one of two methods, the serial method or the parallel method. The Data Connector facilitates the serial method and Data Connector Accelerators facilitate the parallel method. To enable parallel data loading from the Hive table to the CAS server, you will also need SAS® Embedded Process installed on Hadoop cluster.     The concept of a parallel data load to CAS is similar to a parallel data load to a SAS® LASR™ Analytic Server. The following diagram depicts the parallel data load from a Hive table to the CAS environment. The data flows directly from the Hadoop data nodes to the CAS worker nodes.         Using SAS Viya with CAS, the caslib statement enables you to define a SAS library with datasource= option, to include data source connection information. The caslib connection to the source environment uses either the serial or parallel method. The following example shows a caslib statement with a parallel connection to the Hive environment, using parameter dataTransferMode=”parallel”. The parallel data transfer mode works provided that SAS Embedded Process is installed on the Hadoop cluster.   /* Assign EP HIVE CASLIB */ caslib hiveEP datasource=(srctype="hive",server="gatekrbhdp01.gatehadoop.com",     dataTransferMode="parallel",     hadoopconfigdir="/opt/sas/hadoop/client_conf",     hadoopjarpath="/opt/sas/hadoop/client_jar");        When PROC CASUTIL is executed against the above libref, along with the list table or files statement, the Result Tab displays detail information about how the data source library is connected to the Hadoop Hive environment.     When the data load to CAS statement is executed, the data will flow from the Hadoop Hive environment to CAS using the parallel route. Data is transferred from the Hadoop data nodes to the CAS worker nodes.   /* Load HIVE tables (In memory) */ proc casutil;    load casdata="stocks" casout="stocks" outcaslib="hiveEP" incaslib="hiveEP"; quit;         To verify that the data load from Hive to CAS is using the parallel route, you have to verify within the Hadoop cluster using the MapReduce job log from the data feeder process execution. You will note references to SAS Embedded Process and DS2 in the MapReduce log as shown below.   Log Type: stdout Log Upload Time: Wed Jun 15 23:22:34 -0400 2016 Log Length: 195 20160615:23.22.25.21: 00000012:WARNING: [01S02]Current catalog set to SASEP (0x80fff8bd) 20160615:23.22.25.60: 00000018:NOTE: All Embedded Process DS2 execution instances completed with SUCCESS. Log Type: syslog Log Upload Time: Wed Jun 15 23:22:34 -0400 2016 Log Length: 9273 Showing 4096 bytes of 9273 total. Click here for the full log. ………. ……….       Related reading If you would like more information, see the Documentation Page.      ... View more

In SAS® Viya™ a caslib is an in-memory space on the CAS server to hold tables, access controls, and data source information. A caslib provides access to data from the data source environment and access to in-memory tables that are loaded to CAS from data sources. Caslibs create an association with access controls that define which user groups and individual users are authorized to access the data tables listed within a caslib.   Caslib type A caslib can be personal, pre-defined, or manually added to the CAS server. The user authorization determines the user interaction with each type of caslib.     Personal caslib A personal caslib can be configured while installing and configuring the CAS server. When a CAS session is initiated, the personal caslib is always available with global scope. This enables users to access CAS tables from any session with the same user ID. For example, in your test SAS Viya environment, you can have ‘casuser’ as the personal caslib.   Pre-defined caslib Pre-defined caslibs are defined and managed by CAS administrators and have global scope. The data access controls are managed by the CAS server administrator to grant the permission to multiple users for caslib access. Pre-defined caslibs are used for popular data sources utilized by a range of CAS users. For example, in your CAS environment, you might have pre-defined caslibs for Hadoop-Hive or Oracle data sources.     Manually added caslib Authorized users can add caslibs to the CAS server by running a caslib statement from SAS® Studio. In general, the manual caslibs are added in a program where there is need for ad hoc data access and users do not want to share the same data with all other users on the CAS server.        Caslib scope The caslib is an in-memory space in the CAS server to hold tables. When a data table is loaded to the CAS server under caslib, the caslib scope, in association with access controls, facilitates CAS table access for users. Depending on the caslib scope, the CAS table can be shared with other users. There are two scopes for caslibs: session and global.     Session-Scope caslib When a caslib is defined without including the GLOBAL option, the caslib is a session-scoped caslib. When a table is loaded to the CAS server with session-scoped caslib, the table is available to that specific CAS user session only.     The following SAS code illustrates the creation of a manual caslib (HiveCaslib) defined as a session scope and data loaded to the CAS server within the same caslib. Notice, the CASLIB statement has no GLOBAL option. The PROC CASUTIL statement to load data to the CAS server also does not have the PROMOTE option. The PROMOTE option is valid only when the caslib is defined with global scope.   CAS mySession host="gatekrbhdp01.gatehadoop.com" SESSOPTS=( CASLIB=casuser TIMEOUT=999 LOCALE="en_US");   /* Assign Std HIVE CASLIB */ caslib hivelib desc="HIVE Caslib"     datasource=(SRCTYPE="HIVE",SERVER="gatekrbhdp01.gatehadoop.com",         HADOOPCONFIGDIR="/opt/sas/hadoop/client_conf/",     HADOOPJARPATH="/opt/sas/hadoop/client_jar/",     schema="default",dfDebug=sqlinfo);   /* Load HIVE tables (In memory) */ proc casutil;    load casdata="stocks" casout="stocks" outcaslib="hivelib" incaslib="hivelib"; quit;        After loading data, when PROC CASUTIL is executed using the list table statement from the same user session, the output is displayed as a session-local caslib along with associated tables.   proc casutil;    list tables incaslib="hivelib"; run;      When the same user initiates a new session and tries to access a caslib and table loaded from the previous session, the caslib and table are not available in the new session since it was session-scoped. The following SAS code illustrates the creation of a new session by the same user who is trying to access the caslib and associated table that were loaded in the previous session. Notice the “CAS mySession2…..” statement.   CAS mySession2 host="gatekrbhdp01.gatehadoop.com" SESSOPTS=( CASLIB=casuser TIMEOUT=999 LOCALE="en_US");   proc casutil;    list tables incaslib="hivelib"; run;        SAS log with error message: 56 57 proc casutil; NOTE: The UUID 'fadb6249-2976-3146-b7ad-b79bf4809f4d' is connected using session MYSESSION2. 58 ! list tables incaslib="hivelib"; ERROR: The caslib 'hivelib' does not exist in this session. ERROR: The action stopped due to errors. 59 run;           Global-Scope caslib When a caslib is defined using a CASLIB statement with the GLOBAL option, the caslib is defined as a global-scoped caslib. The global-scoped caslib and associated table could be made available to other users in the CAS server by updating the caslib’s access controls. A group of users or individual users can be included in caslib access controls. If a table is loaded to the CAS server with the PROMOTE option within a global-scoped caslib, the table is available to all users who have access permission to the caslib.     If a table is loaded to the CAS server without the PROMOTE option, within a global-scoped caslib, the table is still personal to the owner and not yet shared with the rest of the users on the CAS server.     The CAS server administrator or authorized user can create session-scoped and global-scoped caslibs, and manage the access controls on caslibs. A table can be promoted from a session caslib to a global caslib.     The following SAS code illustrates the creation of a manual, global-scoped caslib (HiveCaslib) and data load to the CAS server in the same caslib. Notice the CASLIB statement has the GLOBAL option to make it global-scoped and the PROC CASUTIL statement has the PROMOTE option to make it a shared table.   CAS mySession host="gatekrbhdp01.gatehadoop.com" SESSOPTS=( CASLIB=casuser TIMEOUT=999 LOCALE="en_US"); /* Assign Std HIVE CASLIB */ caslib hivelib desc="HIVE Caslib"     datasource=(SRCTYPE="HIVE",SERVER="gatekrbhdp01.gatehadoop.com",         HADOOPCONFIGDIR="/opt/sas/hadoop/client_conf/",     HADOOPJARPATH="/opt/sas/hadoop/client_jar/",     schema="default",dfDebug=sqlinfo) GLOBAL ;   /* Load HIVE tables (In memory) */ proc casutil;    load casdata="stocks" casout="stocks" outcaslib="hivelib" incaslib="hivelib" PROMOTE ; quit;       After the data is loaded, when the PROC CASUTIL is executed with the list table statement from the same user session, the output displays the caslib as a global-scoped caslib along with associated promoted tables.   proc casutil;     list tables incaslib="hivelib"; run;        Once a global caslib is created, it appears under the list of caslibs on the Configuration tab on the Access Controls window. The CAS administrator can edit and update the user privileges to share the caslibs and associated tables with the rest of the users on the CAS server. A group of users or individual users can be granted access to the global-scoped caslibs on the CAS server.     Once global caslib access control privileges are updated for a user, a new CAS session with the new user (valid user) can access the global CAS table. The following code illustrates the creation of a new CAS session by user sasdemo02 and the access to the CAS table created by user sasdemo01 in the global caslib. Upon execution of the assign _all_ statement, the global caslib is available to user sasdemo02.     CAS mySession2 host="gatekrbhdp01.gatehadoop.com" SESSOPTS=( CASLIB=casuser TIMEOUT=999 LOCALE="en_US");   /* Show CASLIB in SAS Studio */ C ASLIB _ALL_ ASSIGN;     The listed CAS data table can be opened by user sasdemo02 from available global caslibs.     User privileges for creating session-scoped and global-scoped caslibs are managed by the CAS administrator using the following screen. A group of users or individual users can be granted permission to create session-scoped and global-scoped caslibs on the CAS server.       Related reading For more information and documentation about SAS Viya caslibs, see the SAS Cloud Analytics Services Fundamentals.    ... View more

In SAS® Viya™, SAS Data Connector enables you to access and load data to Cloud Analytic Services (CAS). The SAS Data Connector contains connection information and specifics to connect with such data sources as Hadoop, Oracle, or SAS data sets. While loading data to CAS, SAS Data Connector supports data filtration. You can load a subset of data to CAS from source data with execution of the data subset condition at the source environment. The source environment processes the data condition and sends only the subset of data to CAS over the network.   For simplicity, I am referring only to SAS Data Connector; the SAS Data Connect Accelerator also supports the same data filtration feature. However, SAS Data Connector is meant for serial data load from source to CAS and SAS Data Connect Accelerator is meant for parallel data load from source to CAS.     The parameter values that you supply for SAS Data Connector are specific to the data source. In general, you specify parameters for SAS Data Connector in CAS code within the datasource= parameter when adding a ‘caslib’ or in the options= / dataSourceOptions= parameter when loading a data table to CAS using PROC CASUTIL or PROC CAS table action.     The options= / dataSourceOptions= parameter is supported in PROC CASSUTIL and PROC CAS. It enables you to supply a “where” clause and a varlist= parameter value (a list of column names from a table) in order to process data filtration at the source environment.     For example, the following CAS code loads data from the “stocks” Hive table to CAS with three columns and with data satisfying “stock” ticker equal to ‘IBM’ and “close” value greater than 100. SAS Data Connector sends a query with a list of columns and a “where” clause to the Hive server to process, and returns only a subset of data to CAS. When varlist= the parameter value supplied, the SAS Data Connector SQL construct contains a list of column names instead of ‘select *’.     Example code: proc casutil;    load casdata="stocks" casout="stocks" outcaslib="hiveEP" incaslib="hiveEP"    varlist={"stock", "open", "close"}    options={where="stock='IBM' and close > 100" } ; quit;       Log Extract from code execution:     The hadoop application job list     The Hadoop application job list shows that loading the “stock” table from Hive to CAS was a two-step process. In the first step, SAS Data Connector executed the query with a list of columns and a “where” clause to create a temporary Hive table with a subset of data. In the second step, SAS Data Connector accelerator reads out of temp the Hive table (subset of data) to load into CAS.     To support data filtration at the source environment (Hadoop), you must have sufficient temp space (on HDFS) to process the query submitted by SAS Data Connector. The following Hadoop application job logs show creation of a temp table/file while working on the data load request. Notice the RECORD_IN: value and the record written value.     When using varlist= and options= or dataSourceOptions= parameters in PROC CASUTIL and PROC CAS, there are some rules to follow for how to supply the column names and the “where” clause. Column names casing must match casing of DBMS. Column names need to be quoted if they contain special characters or do not match the default case for the DBMS. A SQL statement uses the \ (backslash) as an escape character for double quotes.     If you are loading a subset of data from an external database table, such as PostgreSQL or Teradata, then use the dbmsWhere= data connector parameter. With this parameter, the “where” clause that you specify is passed directly to the external database for use while loading the data.   proc casutil;     load casdata="cars" incaslib="tdlib" casout="cars_CAS"     vars=((name="make"), (name="model"))     dataSourceOptions=(dbmswhere="cylinders=8") ; quit;     Related reading For more information on Loading a subset of Data table see, Loading a subset of data table . ... View more

The SAS DATA step can be used to manipulate and prepare data for analysis and predictive modeling. In SAS Viya, the DATA step runs in both a traditional SAS client session as well as in SAS Cloud Analytic Services (CAS). The single program and multiple data paradigm (SPDM) is used to run a DATA step in a CAS server session. When a DATA step runs in CAS, the same program executes in multiple threads. Each thread operates on only part of the data. The benefit of running a DATA step in CAS, compared to Base SAS®, is that multiple cores are used to process the DATA step and there is more input/output bandwidth available using multiple machines. The DATA step does not restrict the number of input or output tables. However, there may be practical limits imposed by memory and other operating system limitations.   The environment variable option DSACCEL=ANY enables users to run a DATA step in CAS. By default, this option is turned on to execute the DATA step in CAS, provided the input and output table references in the DATA step are associated with a CAS server libref. When the input or output table is a non CAS table, the DATA step is executed on the client side (SAS workspace server). During client-side execution, the DATA step pulls the data from CAS into the client side, does the calculations, prepares a new table, and stores/sends it to CAS to store into a new CAS table, if the output is a CAS table.     The following example demonstrates the execution of a DATA step in the CAS server session. Both the input and the output tables are in a CAS engine libref.   64 /* Convert MPG to KPL into new CAS table*/ 65 data casdata.cars_kpl ; 66 set casdata.cars; 67 KPL_City = 0.425 * MPG_City; 68 KPL_Highway = 0.425 * MPG_Highway; 69 run;   NOTE: Running DATA step in Cloud Analytic Services. NOTE: There were 428 observations read from the table CARS in caslib CASUSER(sasdemo). NOTE: The table cars_kpl in caslib CASUSER(sasdemo) has 428 observations and 17 variables. NOTE: DATA statement used (Total process time): real time 0.16 seconds cpu time 0.00 seconds         The following example demonstrates the execution of a DATA step in the SAS client (SAS workspace server). Both the input data set and the output tables are not in a CAS engine libref.   58 /* Create a new CAS table by concatenting two table, a table from CAS and a table (non CAS table) from Client library */ 59 data casdata.cars_new; 60 set casdata.cars sashelp.cars ; 61 KPL_City = 0.425 * MPG_City; 62 KPL_Highway = 0.425 * MPG_Highway; 63 run;   NOTE: To run DATA step in Cloud Analytic Services a CAS engine libref must be used with all data sets and all librefs in the program must refer to the same session. NOTE: Could not execute DATA step code in Cloud Analytic Services. Running DATA step in the SAS client.       Impact of the MaxTableMem and the “copies” parameters The MaxTableMem property has no noticeable effect on the execution of a DATA step in CAS. Whether you keep the value for MaxTableMem as low as ~16 MB or as high as ~16 GB, this parameter does not impact the execution time of a DATA step in a CAS server session.     However, the value assigned to the “copies” parameter for the CAS output table in a DATA step significantly impacts the execution time. In a multi-node CAS environment, if you keep the default value copies=1 for the CAS output table, the execution of a DATA step takes more time. In this case, most of the time is taken by the data movement between the nodes while creating the output table. If you are creating a DATA step program with many steps, it is recommended that you specify copies=0 in all the steps except the last one, assuming that you want to keep the final results as a CAS table.     The following example demonstrates the impact of the value that you assign to the “copies” parameter while executing a DATA step in CAS. We have loaded a data table that contains 100 million rows to a multi-node CAS environment in order to execute a DATA step against the same table with copies=0 and copies=1 for the CAS output table. In the log, you can notice that when a DATA step is executed with copies=0, there is no data movement between the nodes and execution takes less time. Whereas, when a DATA step is executed on the same table with the default value copies=1, there is data movement between the nodes and, hence, it takes more time to execute.     The following example demonstrates the DATA step processing in CAS with copies=0:   58 data casuser.junk2(copies=0 promote=yes ); 59 set casuser.CASJunkTable; 60 l = k*2; 61 m = l/j; 62 run;   NOTE: Running DATA step in Cloud Analytic Services. NOTE: The DATA step will run in multiple threads. NOTE: Executing action 'dataStep.runBinary'. NOTE: Executing action 'table.promote'. NOTE: Action 'table.promote' used (Total process time): NOTE: real time 0.080657 seconds NOTE: cpu time 0.188588 seconds (233.81%) NOTE: total nodes 4 (8 cores) NOTE: total memory 62.06G NOTE: memory 364.97K (0.00%) NOTE: There were 100000000 observations read from the table CASJUNKTABLE in caslib CASUSER(sasdemo). NOTE: The table junk2 in caslib CASUSER(sasdemo) has 100000000 observations and 5 variables. NOTE: Action 'datastep.runBinary' used (Total process time): NOTE: real time 7.845770 seconds NOTE: cpu time 40.669176 seconds (518.36%) NOTE: total nodes 4 (8 cores) NOTE: total memory 62.06G NOTE: memory 364.97K (0.00%) NOTE: DATA statement used (Total process time): real time 8.00 seconds cpu time 0.02 seconds           The following example demonstrates the DATA step processing in CAS with copies=1:   59 data casuser.junk3(promote=yes); 60 set casuser.CASJunkTable; 61 l = k*2; 62 m = l/j; 63 run;   NOTE: Running DATA step in Cloud Analytic Services.   NOTE: The DATA step will run in multiple threads. NOTE: Executing action 'dataStep.runBinary'. NOTE: Executing action 'table.promote'. NOTE: Action 'table.promote' used (Total process time): NOTE: real time 0.106011 seconds NOTE: cpu time 0.111325 seconds (105.01%) NOTE: total nodes 4 (8 cores) NOTE: total memory 62.06G NOTE: memory 566.66K (0.00%) NOTE: There were 100000000 observations read from the table CASJUNKTABLE in caslib CASUSER(sasdemo). NOTE: The table junk3 in caslib CASUSER(sasdemo) has 100000000 observations and 5 variables. NOTE: Action 'datastep.runBinary' used (Total process time): NOTE: real time 38.682911 seconds NOTE: cpu time 57.775873 seconds (149.36%) NOTE: data movement time 31.019510 seconds NOTE: total nodes 4 (8 cores) NOTE: total memory 62.06G NOTE: memory 566.66K (0.00%) NOTE: bytes moved 3.73G   NOTE: DATA statement used (Total process time): real time 38.85 seconds cpu time 0.01 seconds       Other noticeable limitations While running a DATA step in a CAS server session, only the double, character, and VARCHAR data types are supported. Other CAS data types in input tables are converted to one of these three supported types.     When running a DATA step in multiple threads, input table rows are divided among threads. Each thread of a DATA step sees only a part of the data, not the entire table. When dividing data among several threads of a DATA step, the results may not be the same as when only one thread is used. For example, when a RETAIN statement is used, a value is retained from one row to the next. Often, this approach is used to create a sum from all rows. Because each thread operates on only part of the data, each thread holds and stores a partial sum.     The full functionality of a Base SAS DATA step is not yet supported in CAS. For more information about executing a DATA step in CAS, a list of limitations, and unexpected discoveries, see Run a DATA step in CAS   ... View more

Cloudera Impala tables can also be used as one of the data sources to load data into Cloud Analytic Services (CAS) for analysis. This blog post discusses the steps required to configure and connect SAS Viya with Cloudera Impala.   To connect to Cloudera Impala from SAS Viya, SAS has another database engine called SAS Data Connector to Impala which is included in SAS/ACCESS® Interface to Impala (on SAS Viya). The Data Connector to Impala works using an ODBC driver as compared to the Data Connector to Hadoop, which uses Hadoop JAR files.     To connect SAS Viya with Cloudera Impala, the CAS server controller needs to configure an ODBC connection using Impala ODBC driver with UnixODBC Driver Manager. To install, configure, and validate Impala ODBC connection, follow these steps:       1. Download UnixODBC driver manager and the Impala ODBC driver.   You can refer to the Cloudera ODBC Driver Installation Guide for the compatible version of UnixODBC driver manager. UnixODBC is free software and can be downloaded from the download site. If your CAS server controller (UNIX/Linux) environment already has an ODBC driver manager, you can use the same one and append the rest of the configuration to existing config files. To configure the Impala ODBC connection, you also need the latest version of Impala ODBC driver, which can be downloaded from the Cloudera site.       2. Install UnixODBC driver manager.   On the CAS server controller, use ‘root’ user to extract and configure the UnixODBC software. You can untar in your desired location. In this example, the software is being extracted under /opt folder.      $ gunzip unixODBC-2.3.4.tar.gz    $ tar -xvf unixODBC-2.3.4.tar     Configure unixODBC using the following UNIX statements:    $ cd /opt/unixODBC-2.3.4    $ ./configure --prefix=/opt/unixODBC-2.3.4 --disable-gui --disable-drivers    $ make       3. Install the Impala ODBC driver.   Using a YUM statement, install the Impala ODBC driver at the CAS server controller. In the following example, the .rpm file is located under the /opt folder. By default, Impala ODBC is installed under /opt/cloudera/impalaodbc folder.      $ cd /opt    $ yum --nogpgcheck localinstall ClouderaImpalaODBC-2.5.35.1006-1.el7.x86       4. Configure the Impala driver with UnixODBC.   Once the UnixODBC driver and ImpalaODBC are installed in the environment, you are required to re-configure the UnixODBC driver to include Impala ODBC in the search path. The following ‘configure’ and ‘make’ statements facilitate this purpose.     $ cd /opt/unixODBC-2.3.4   $ export LD_LIBRARY_PATH=/opt/unixODBC-2.3.4/lib   $ ./configure --prefix=/opt/cloudera/impalaodbc --with-unixodbc=/opt/unixODBC-2.3.4   $ make   $ make install       5. Update ODBC.ini, ODBCINST.ini files.   When Cloudera ODBC software is installed, it provides sample setup .ini files under the cloudera/impalaodbc/Setup/ folder. You can use the same files and update, or you can copy these files to your desired location and update ODBC.ini with the server name, port, and database schema from the Hadoop Impala environment. In the following example, the config files are being updated in the same /opt/cloudera/impalaodbc/Setup/ directory location.     Most of the files remain unchanged except for the ODBC.ini file, where you need to specify the host name and port number of where the Impala Daemon is running. You can verify your Hadoop cluster for the Impala demon process; it might be running on nodes other than the name node. In the following example of a three-node Hadoop cluster (sascdh01(namenode), sascdh02, sascdh03), the Impala demon is running on all the servers.     Update the following section in the ODBC.ini file with the host name and port number:      # Values for HOST, PORT, KrbFQDN, and KrbServiceName should be set here.    # They can also be specified on the connection string.    HOST=sascdh01.race.sas.com    PORT=21050    Database=default     If you have any additional ODBC database connections defined, you can add them in the same ODBC.ini file. For example, I have a Postgres database connection defined, and I appended the same information in the “/opt/cloudera/impalaodbc/Setup/odbc.ini” file.      sasclient_dvdrental=sasclient_dvdrental      [sasclient_dvdrental]    Driver=/opt/sas/viya/home/lib64/psqlodbcw.so    ServerName=sasclient.race.sas.com    username=postgres    password=#######    database=dvdrental    port=5432       6. Edit the vars.yml file.   While preparing for SAS® Viya™ installation, update the vars.yml file and include the following lines under the file’s CAS_SETTINGS section to configure the Impala ODBC shared library path. The statement in vars.yml should be as listed, including the spaces and numerical prefixes. Depending on how you configured your Impala ODBC driver, you might need to specify the ODBC.ini file, the ODBCINST.ini file, or both files. The following example includes both files:   1: ODBCINI=/opt/cloudera/impalaodbc/Setup/odbc.ini 2: ODBCINST=/opt/cloudera/impalaodbc/Setup/odbcinst.ini 3: CLOUDERAIMPALAODBC=/opt/cloudera/impalaodbc/Setup/odbc.ini 4: LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/unixODBC-2.3.4:/opt/cloudera/impalaodbc/lib/64   Based on the above information in vars.yml, when SAS Viya software is installed, the cas.settings file reflects these parameters.       7. Update the cas.setting file.   If you are configuring Cloudera Impala ODBC driver after the CAS (Viya) installation, you need to manually update the ~/sas/viya/home/SASFoundation/cas.settings file on the CAS server controller to include the following environment variables.        export ODBCINI=/opt/cloudera/impalaodbc/Setup/odbc.ini ;    export ODBCINST=/opt/cloudera/impalaodbc/Setup/odbcinst.ini;    export CLOUDERAIMPALAODBC=/opt/cloudera/impalaodbc/Setup/odbc.ini;    export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/unixODBC-2.3.4:/opt/cloudera/impalaodbc/lib/64    export ODBCSYSINI=/opt/cloudera/impalaodbc/Setup/       Note: ODBCSYSINI is also included as it is required for the environment. ODBCSYSINI= points to a folder where ODBCINST.ini resides.         8. Validate the SAS Viya connection to Impala.   After you have set up the environment following these steps, you can execute a SAS program from SAS® Studio to create a CASLIB with source type “impala” and load a table to CAS from the same source. The following code is an example of the creation of a CASLIB with source type “impala” and data loaded from an Impala table to CAS. The SAS log shows that while loading a table from the above defined source, it was performing a serial load table action using SAS DATA Connector to Impala.   cas mySession sessopts=(messagelevel=all) ; caslib implib datasource=(srctype="impala", username="hadoop",      server="sascdh01.race.sas.com",            database="default");   proc casutil ;    load casdata="s_heart" incaslib="implib" outcaslib="implib"   casout="s_heart" replace ;   list tables incaslib="implib"; quit ;   cas mySession terminate ;         SAS log extract: 57 proc casutil ; NOTE: The UUID '55b2c571-4656-034c-8001-e8a7b6ba55bd' is connected using session MYSESSION. 58 load casdata="s_heart" incaslib="implib" outcaslib="implib" casout="s_heart" replace ; NOTE: Performing serial LoadTable action using SAS Data Connector to Impala. NOTE: Cloud Analytic Services made the external data from s_heart available as table S_HEART in caslib implib. NOTE: The Cloud Analytic Services server processed the request in 0.974435 seconds. 59 list tables incaslib="implib"; NOTE: Cloud Analytic Services processed the combined requests in 0.016342 seconds. 60 quit ;       Result of code execution:               For more information about this topic: SAS® Viya™ 3.2: Deployment Guide     ... View more

Blobfuse is an open-source project developed to provide a virtual filesystem backed by Azure Blob storage. It is a virtual filesystem driver for Azure Blob storage. Blobfuse allows you to access blob data from Azure Storage Account through Linux Filesystem.   With all excitement around SAS and Azure Cloud, Blobfuse could be a useful tool to access SAS data sets stored at Azure Blob Storage. Azure Blob Storage is a cost-effective and reliable service to store data.   As a SAS user, you may use Azure Blob Storage to store all kinds of files (type) including “.sas7bdat” and “.sashdat” files. But there are no LIBNAME engine or CASLIB connector to directly read and write “.sas7bdat” and “.sashdat” files to Azure Blob Storage. With SAS Viya 3.5 release, SAS SPRE supports the ORC LIBNAME engine for the ORC data file at Blob Storage and ADLS FILENAME statement for other file types. CAS supports ORC and CSV data file access at Azure Blob Storage using ADLS CASLIB.   The Azure Blobfuse could be a viable option for SAS users migrating SAS datasets (.sas7bdat files) to Azure Blob Storage. By using Blobfuse, a SAS user can NFS mount the Azure Blob Storage location to a Unix server as an additional filesystem. The Unix server which is hosting SAS Compute server or CAS servers. The Blobfuse NFS mount enables SAS users to use SAS LIBNAME name statement or PATH based CASLIB to access the .sas7bdat and .sashdta datafiles.   The following diagram describes the data access path from Azure Blob Storage to SAS Compute Server and CAS Servers using Blobfuse.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   How to mount Blob Storage as a filesystem at Unix server Install the Blobfuse software.   The Blobfuse is an open-source project and is free for download. With access to the internet, you can use the following statement to install Blobfuse at the Unix server.   sudo rpm -Uvh https://packages.microsoft.com/config/rhel/7/packages-microsoft-prod.rpm sudo yum install blobfuse   Prepare the Unix OS for NFS mount.   Blobfuse provides native-like performance by buffering and caching the open files at OS in a temporary path file system. You can use most performant disk or a ramdisk as a temporary path for best performance. In Azure, you may use ephemeral disk (SSD) on VMs to provide a low-latency buffer for Blobfuse.   sudo mkdir /mnt/blobfusetmp -p sudo chown utkuma:sasusers /mnt/blobfusetmp   Configure Storage Account Credentials at Unix Server.   Blobfuse requires Storage Account credentials stored in a text file in the following format. You can create a configuration file in the home directory or at a safe location with Storage Account name, key, and container name.   tee ~/fuse_connection.cfg > /dev/null << "EOF" accountName utkuma3adls2strg accountKey 3R4oxwqyqrTqb4e4v7jsI2viFPkouln9qwNAXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX containerName fsutkuma3adls2strg EOF sudo chmod 600 ~/fuse_connection.cfg   Mount an empty directory to Azure Blob Storage.   To mount a Blob Azure Storage container to the Unix server, it requires an empty folder. During the filesystem mount, you can use the “-o allow_other” option to enable access for other users.   sudo mkdir /opt/fscontainer sudo chown utkuma:sasusers /opt/fscontainer sudo blobfuse /opt/fscontainer --tmp-path=/mnt/blobfusetmp --config-file=/home/utkuma/fuse_connection.cfg -o allow_other -o attr_timeout=240 -o entry_timeout=240 -o negative_timeout=120   Once Blob storage location mounted to the Unix server using Blobfuse NFS, you can view the data files under the NFS mount folder.   [utkuma@intviya01 root]$ ls -l /opt/fscontainer total 0 -rwxrwxrwx. 1 root root 11671314432 Jul 31 14:01 dm_fact_mega_corp_10g.sas7bdat -rwxrwxrwx. 1 root root 1167204352 Aug 4 16:34 dm_fact_mega_corp_1g_1.sas7bdat -rwxrwxrwx. 1 root root 1167204352 Jul 31 11:34 dm_fact_mega_corp_1g.sas7bdat -rwxrwxrwx. 1 root root 1221188352 Aug 4 16:56 dm_fact_mega_corp_1G.sashdat -rwxrwxrwx. 1 root root 2334334976 Aug 4 16:35 dm_fact_mega_corp_2g_1.sas7bdat -rwxrwxrwx. 1 root root 2334334976 Jul 31 11:35 dm_fact_mega_corp_2g.sas7bdat -rwxrwxrwx. 1 root root 2442365904 Aug 4 16:57 dm_fact_mega_corp_2G.sashdat -rwxrwxrwx. 1 root root 5835661312 Aug 4 16:38 dm_fact_mega_corp_5g_1.sas7bdat -rwxrwxrwx. 1 root root 5835661312 Jul 31 11:36 dm_fact_mega_corp_5g.sas7bdat -rwxrwxrwx. 1 root root 6105880216 Aug 4 17:02 dm_fact_mega_corp_5G.sashdat -rwxrwxrwx. 1 root root 949092352 Jul 30 16:12 dm_fact_mega_corp.sas7bdat -rwxrwxrwx. 1 root root 131072 Jul 30 16:06 fish_sas.sas7bdat drwxrwxrwx. 2 root root 4096 Dec 31 1969 sample_data [utkuma@intviya01 root]$   The list of files in the above output is the same files located at ADLS2 Blob storage.   Azure Blob data file access from SAS and CAS SAS LIBNAME to access Blob storage data.   When a Blob Storage location is NFS mounted to SAS compute server (Unix), users can write a SAS LIBNAME statement with the NFS mounted folder to access .sas7bdat files.   libname azshrlib "/opt/fscontainer" ; Proc SQL outobs=20; select * from azshrlib.fish_sas ; run;quit;   While accessing the data files user will notice Blobfuse is buffering the data files at the temporary location used in Blobfuse mount statement. The buffer file is temporary and auto deletes.   [utkuma@intviya01 ~]$ ls -l /mnt/blobfusetmp/root/ total 128 -rwxrwxrwx. 1 root root 131072 Jul 30 16:06 fish_sas.sas7bdat [utkuma@intviya01 ~]$   PATH CASLIB to access Blob Storage data. When a Blob Storage location is NFS mounted to the CAS Controller server (Unix), users can use PATH based CASLIB to access .sas7bdat and .sashdat files.   CAS mySession SESSOPTS=( CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true); caslib azshcaslib datasource=(srctype="path") path="/opt/fscontainer" ; proc casutil outcaslib="azshcaslib" incaslib="azshcaslib" ; load casdata="dm_fact_mega_corp.sas7bdat" casout="dm_fact_mega_corp" replace; load casdata="dm_fact_mega_corp_1G.sashdat" casout="dm_fact_mega_corp_H" replace; list tables; quit; CAS mySession TERMINATE;   While accessing the data files user will notice Blobfuse is buffering the data files at the temporary location used in Blobfuse mount statement. The buffer file is temporary and auto deletes.   [root@intcas01 ~]# ls -l /mnt/blobfusetmp/root total 2250436 -rwxrwxrwx. 1 root root 1221188352 Aug 4 16:56 dm_fact_mega_corp_1G.sashdat -rwxrwxrwx. 1 root root 949092352 Jul 30 16:12 dm_fact_mega_corp.sas7bdat [root@intcas01 ~]#   You may ask this question, why not use the DNFS type of CASLIB against the Blobfuse folder for parallel access to the Azure Blob storage file. The answer is Yes and No! In a test environment, I have noticed the DNFS type CASLIB using the Blobfuse folder is not working well.   The CAS load from .sashdat file works in parallel, Blobfuse will cache the .sashdat file at each CAS nodes. However, a CAS table save to Blob Storage in .sashdat format creates a corrupted file and could not be loaded back to CAS. The PATH based CASLIB using the Serial method to save data at Blob Storage in .sashdat format works well.   The CAS load from .sas7bdat files does not work with DNFS type CASLIB using Blobfuse. Though I have noticed if Blob Storage is NFS mounted to each CAS node, it will cache the data file at each CAS node during CAS load. The CAS table data save to Blob storage as .sas7bdat file is always in serial. The CAS controller writes data to Blob Storage, even if you have CAS nodes mounted to Blob Storage using Blobfuse.   Notes: It is recommended to allows multiple CAS nodes to mount the same blob container for read-only scenarios. While a Blob container is mounted, the data in the container should not be modified by any process other than Blobfuse. This includes other instances of Blobfuse, running on this or other machines. Doing so could cause data loss or data corruption. Mounting other containers is fine. CAS load/save Performance using Blobfuse. The performance of CAS load from Blobfuse depends on the location of the CAS Unix server, and Azure Blob Storage account. For better data transfer between CAS server and Blob Storage, keep them close to each other as location-wise.   The following is the run time and data transfer speed while moving data in and out from CAS to Azure Blob Storage. Notice the data save to Blob storage is slower compare to CAS load.   CAS server (SMP) hosted at Azure VM machine (Instance Standard_D14_V2, 16 vCPUs, 112GB RAM , Max IOPS = 64x500).     Notes: For better performance user should use latest series of Azure server (e.g. E32sV3 or E32ds_V4 ). Since I/O goes through the network, it’s recommended to enable the Accelerated Networking on Azure VM (for Azure supported Instance type). CAS server hosted at SAS RACE machine (4 vCPUs, 32 GB RAM)     Many thanks to Erwan Granger for help and collaboration on this topic. Resource How to Mount Azure Blob Storage Container to a Unix Server ... View more

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 Azure SQL Database MS-SQL Data Connector ... View more

With the SAS Viya 3.5 release, the SAS Viya programming environment (SAS 9.4) can read and write data files to ADLS and ADLS2 using FILENAME statement. The ADLS FILENAME statement’s Azure access method enables you to access data from ADLS/2.   This article talks about various components involved for SAS FILENAME statement to access data files from Azure ADLS2.     Pre-requisites User access to Azure Storage Account with Storage Blob Data Contributor role. Azure application with access permission to Azure Data lake and Azure Storage.   Data Path diagram The following diagram describes the data access components and data path from SPRE/Base SAS 9.4 to ADLS2 using FILENAME statement.    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.   Azure Configurations to access ADLS2 Please refer to the article CAS accessing ADLS2 and go through the “Azure configuration to access ADSL2” section.     Azure information required to access ADLS2 data from CAS To access the Azure ADLS2 data file using FILENAME statement from SPRE/SAS, you need the following information.     azuretenantid="XXXXXXXXXXXXXXXXXXXXXX" applicationid="XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" accountname= "utkuma8adls2strg" filesystem="fsutkuma8adls2strg"   SAS FILENAME statement Authentication An ORC LIBNAME uses device code authentication with Azure. Very first-time execution of ORC LIBNAME statement, it generates a misleading error message. It could not find the Azure access token at SAS compute server for SAS user. It created a .sasadls<>.json file under SAS user’s home directory with instructions to register the device and obtain the Azure Access token. The .sasadls<>.json file created for each SAS user. As part of the instruction, you need to login to https://microsoft.com/devicelogin Microsoft page and validate the listed device.   Note: The default location for the Azure authorization key file is the home directory of the SAS user (e.g /home/viyademo01/.sasadls_.jason).     Error from very first-time execution of ORC LIBNAME statement: 90 ; ERROR: Cannot obtain connection to ADLS. Check options and tokens. ERROR: To sign in, use a web browser to open the page https://microsoft.com/devicelogin and enter the code to authenticate. 91   After you log on to listed Microsoft page a series of windows provide further instruction to authorize the device. The subsequent attempts to access the data succeed and generates an Azure authorization key file at SAS compute server under the user’s home directory.     [viyademo01@intviya01 ~]$ ls -l /home/viyademo01/.sasadls* -rw-------. 1 viyademo01 sasusers 5718 Feb 13 11:52 /home/viyademo01/.sasadls_100001.json [viyademo01@intviya01 ~]$   Data read/write to ADLS2 using SAS FILENAME statement The following code describes the data save from SPRE/SAS to ADLS2 Blob storage. In step-1, it executes the ADLS FILENAME statement. In step-2, it executes data steps to saves few lines into data file at ADLS2.     Code: /* Step-1 */ options azuretenantid="XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"; filename out adls "sasfile/example.txt" applicationid="XXXXXXXXXXXXXXXXXXXXXXX-XXXXXXXXXXX" accountname="utkuma8adls2strg" filesystem="fsutkuma8adls2strg" ; /* Step-2 */ data _null_; file out; put 'line 1'; put 'line 2'; run;   Log extract: ………… ………… 83 options azuretenantid="b1c14d5c-3625-45b3-a430-9552373a0c2f"; /* 1*/ 84 85 /** File Name Statement to ADLS2 **/ 86 filename out adls "sasfile/example.txt" 87 applicationid="XXXXXXXXXXXXXXXXXXXXXXXX-xxxxxxxxxxxx" 88 accountname="utkuma8adls2strg" 89 filesystem="fsutkuma8adls2strg" 90 ; 91 92 data _null_; 93 file out; 94 put 'line 1'; 95 put 'line 2'; 96 run; NOTE: The file OUT is: Filename=sasfile/example.txt, Account Name=utkuma8adls2strg, File system=fsutkuma8adls2strg NOTE: 2 records were written to the file OUT. The minimum record length was 6. The maximum record length was 6. NOTE: DATA statement used (Total process time): real time 0.14 seconds cpu time 0.01 seconds 97 …. ……………     The following screenshot describes the .txt data file saved to Azure ADLS2 by executing the above statement.     Important Link: FILENAME statement: Azure Access Method     Related Articles: SAS Viya 3.5 – CAS accessing Azure Data Lake files SAS Viya 3.5 – SAS ORC LIBNAME engine to ADLS2     ... View more

With the SAS Viya3.5 release, the SAS Viya programming environment (SAS 9.4) can read and write ORC data files to and from Azure Data Lake Storage 2 (ADLS2). We have a new Base SAS ORC engine to support data transfer between SAS 9.4 and ADLS2.   This blog talks about various components involved for SAS to access orc files from Azure ADLS2.     ORC data file Apache ORC (Optimized Row Columnar) is an open-source column-oriented data storage file format of the Apache Hadoop ecosystem. In column-based storage, you quickly access only the columns that you need to query, which can be highly efficient for analyzing big data.     Storage system supported by SAS ORC engine Azure Data Lake Storage 2 (ADLS2) Linux for x64 supported by SAS   Pre-requisites SAS ORC Engine at SAS Compute Server. (default install) User access to Azure Storage Account with Storage Blob Data Contributor role. Azure application with access permission to Azure Data lake and Azure Storage.   Data Path diagram The following diagram describes the data access components and data path from Base SAS 9.4 to ADLS2.   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.     Azure Configurations to access ADLS2 Before you can access the ADLS2 storage, you need to create a new or use existing Azure Application and Storage Account. The Azure application and Storage Account require appropriate configurations to access ADLS2 storage. The detailed steps with screenshots are discussed in the blog post (CAS accessing ADLS2) under “Azure configuration to access ADSL2” section.     Azure information required to access ADLS2 data from CAS To access the Azure ADLS2 data file from SAS, you need the following information to execute an ORC LIBNAME statement.   storage_account_name= “utkuma5adls2strg” storage_file_system="fsutkuma5adls2strg" storage_tenant_id="XXXXXXXXXXXXXXXXXXXXXX" storage_application_id="XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"   SAS ORC LIBNAME Authentication An ORC LIBNAME uses device code authentication with Azure. Very first-time execution of ORC LIBNAME statement, it generates a misleading error message. It could not find the Azure access token at SAS compute server for SAS user. It created a .sasadls<>.json file under SAS user’s home directory with instructions to register the device and obtain the Azure Access token. The .sasadls<>.json file created for each SAS user. As part of the instruction, you need to login to https://microsoft.com/devicelogin Microsoft page and validate the listed device. Note: The default location for the Azure authorization key file is the home directory of the SAS user (e.g /home/viyademo01/.sasadls_.jason).   Error from very first-time execution of ORC LIBNAME statement: 90 ; ERROR: Invalid physical name for library ORCLIB. ERROR: Error in the LIBNAME statement. 91   After you log on to listed Microsoft page a series of windows provide further instruction to authorize the device. The subsequent attempts to access the data succeed and generates an Azure authorization key file at SAS compute server under the user’s home directory.     [viyademo01@intviya01 ~]$ ls -l /home/viyademo01/.sasadls* -rw-------. 1 viyademo01 sasusers 5718 Feb 13 11:52 /home/viyademo01/.sasadls_100001.json [viyademo01@intviya01 ~]$   Data read/write from SAS to ADLS2 The following code describes the data save and read from ADLS2 Blob storage to SAS 9.4. In step-1, it executes the ORC LIBAME statement. In step-2, it saves SAS data sets to ADLS2 storage in ORC data file format. In step-3, it reads saved ORC file from ADLS2 to SAS.   Code:   /* Step-1 */ libname orclib ORC "/sample_data" storage_account_name = "utkuma5adls2strg" storage_file_system = "fsutkuma5adls2strg" storage_dns_suffix = "dfs.core.windows.net" storage_application_id="XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" storage_tenant_id="XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" DIRECTORIES_AS_DATA=YES FILE_NAME_EXTENSION=(orc ORC) ; /* Step-2 */ data orclib.fish_orc; set sashelp.fish; run; /* Step-3 */ Proc SQL ; select * from orclib.fish_orc ; run; quit;   Log extract:   ………… ………… 82 data orclib.fish_orc ; 83 set sashelp.fish ; 84 run; NOTE: There were 159 observations read from the data set SASHELP.FISH. NOTE: The data set ORCLIB.fish_orc has 159 observations and 7 variables. NOTE: DATA statement used (Total process time): real time 0.90 seconds cpu time 0.11 seconds 85 …. ……………   The following screenshot describes the ORC data file saved to Azure ADLS2 by executing the above statement.       The following screenshot describes the ORC data file read from Azure ADLS2 by executing the above statement.       SAS reading orc files from an ADLS2 sub-folder The SAS ORC engine supports reading n-number of orc files from an ADLS2 sub-folder. The LIBNAME definition support “DIRECTORIES_AS_DATA=YES” to enable read the sub-folder name as one table. The following screenshot describes more than one orc file in an ADLS2 sub-folder. These data files can be read at the sub-folder level and all data will end-up in a single SAS table.     The following code describes multiple orc file read from an ADLS2 sub-folder into a SAS table. The LIBNAME statement using “DIRECTORIES_AS_DATA=YES” to enable the read.   Code:   /* Step-1 */ libname orclib ORC "/sample_data" storage_account_name = "utkuma5adls2strg" storage_file_system = "fsutkuma5adls2strg" storage_dns_suffix = "dfs.core.windows.net" storage_application_id="XXXXXXXXXXXXXXXXXXXXXXXXX-XXXXXXXX" storage_tenant_id="XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" DIRECTORIES_AS_DATA=YES FILE_NAME_EXTENSION=(orc ORC) ; /* Step-2 */ Proc SQL ; select * from orclib.fish_n ; run; quit;   Data Compression The SAS ORC engine supports default ZLIB data compression while saving data to ADLS2. SAS can read compression types SNAPPY, LZO, LZ4, and ZSTD. However, these types are not supported for Write access. COMPRESS=NO | ZLIB   Restrictions There are some restrictions for SAS features and ORC file features. Few of them are listed here, for a more comprehensive list see the documentation. Data update to orc data file/s is not supported. SAS Indexes, encryption, DS2 or Fed SQL procedures are not supported. Compound data types, and Hive data partitioning are not supported.     Important Link: Base SAS ORC LIBNAME Engine Related Blog: SAS Viya 3.5 – CAS accessing Azure Data Lake files     ... View more