Watch this Ask the Expert session to see an overview of SAS Enterprise Session Monitor, which is used globally to optimize the development of SAS applications.    Watch the webinar   You will learn: Identify source and target tables used by SAS. Monitor your interactive and batch SAS usage. Use capacity planning report for sizing of SAS environments. Identify usage patterns. Use Ecosystem Diagnostic. The questions from the Q&A segment held at the end of the webinar are listed below and the slides from the webinar are attached.   Q&A Do we require a separate license for ESM for existing customers? Yes, ESM is a standalone product and requires a separate license.   Is this only available for SAS Viya (Session Monitor tool)? ESM is available on SAS9.x for Unix, Linux, and Windows. ESM is available on SAS Viya 3 and SAS Viya 4.   Is there a way to map the obfuscated user in the reports back to the real user on the system? Yes, when the ESM data is extracted with the parameters that obfuscate users and hostnames it creates two files with the mappings. For hostnames, the file is called DONOTSEND_node_hash_mappings.csv. For users, the file is called DONOTSEND_user_hash_mappings.csv.   How much does this service cost on prem or SAAS? SAS Enterprise Session Monitor is a Technology offering with an annual fee and a minimum 12-month contract period (trial available). The pricing metric of SAS Enterprise Session Monitor is Authorized Users.    Regarding prerequisites, can we install ESM on a SAS 9.3 M2 environment running on AIX 7.2? ESM agents can be installed on SAS9.x for Unix, Linux, and Windows. For AIX, the ESM agent is supported on v5.2 through v7.2.   Does ESM monitor mainframe SAS jobs? No. ESM is only supported on AIX, Linux , Solaris, and Windows.   What is the difference between SAS Environment Manager and ESM? Very broadly there are two main functional differences between Environment Manager and Enterprise Session Monitor: Enterprise Session Monitor is completely independent of SAS (does not require SAS Logon for authentication/authorization) Enterprise Session Monitor has user session metrics in addition to infrastructure service metrics   ESM is going to give you much more granular detail on all interactive and batch SAS sessions. ESM collects metrics I.e., CPU, memory, and IO as well as distinct users, procedures ran and the source and target tables referenced in SAS Code. ESM can be used by individual users or administrators, supports chargeback, and can monitor other applications outside of SAS for a more complete view of what is occurring in the larger environment.   SAS Environment Manager is a web-based administration solution for a SAS environment. The application enables you to perform these tasks: administer, monitor, and manage SAS resources, including administering the SAS Web Application Server and monitoring SAS Foundation servers collect and chart data on metrics for monitored SAS resources, which creates a comprehensive view of resource health and operation monitor log events and reporting alerts incorporate the monitoring and managing of IT and SAS resources into a service management strategy by using the detailed metric information stored in the SAS Environment Manager Data Mart and the reports provided in the Report Center   SAS Environment Manager agents run on all SAS platforms except for z/OS.   How do you give multiple administrators access only to their jobs to manage? The philosophy of Enterprise Session Monitor is such that anyone can see everything that is happening on a server. Enterprise Session Monitor provides capabilities to only enable certain users to be able to either open the log files or terminate processes that are owned by particular users.   Can you drill down on ESM Capacity Planning Reports to drill down to the services that is using the most CPU or memory? The CPU Top/Bottom and Memory Top/Bottom reports will provide you information at the Server level. You do have access to the data used in these reports to drill into more details and create your own custom reports.   Do the Cluster reports separate for each Tenant on Viya environments? It depends on how an environment is configured, but in a typical Viya 3 deployment, we use the “queue” field to differentiate different application tenants.   As long as ESM_GROUP is configured you can filter the cluster reports by environment.   How granular can the chargeback model go? If you use the point-and-click interface within the tool, only a relative CPU number is available. However, if you extract the data from the system using exporting capability provided by the maintenance utility, then you can incorporate any metric captured at the session level into a chargeback model in any way you’d like.   Where is the ESM documentation on sas.com? More specifically, what are the metrics collected for chargeback? The link to the Enterprise Session Monitor documentation on sas.com can be found here. However, this just links of the Boemska documentation site currently so the links below might be more useful.   Install ESM server (once) and ESM agent (on each node): https://us.docs.boemskats.com/esm/2022.x/administration/esm-on-unix/   Enable active process discovery: https://us.docs.boemskats.com/esm/2022.x/configuration/esm-advanced-configuration/#active-process-discovery   Configure SAS for ESM: (9.4) https://us.docs.boemskats.com/esm/2022.x/configuration/esm-for-sas9/   (Viya 3) https://us.docs.boemskats.com/esm/2022.x/configuration/esm-for-viya3/   (SAS 9 and Viya 3 Compute/SPRE) Deploy sas-proc-mon: https://us.downloads.boemskats.com/esm/modules/sas-proc-mon/latest/ Username: esm Password: BoemskaESM   (Viya 3) Deploy sas-cas-mon: https://us.downloads.boemskats.com/esm/modules/sas-cas-mon/latest/ Username: esm Password: BoemskaESM   Deploy sas-data-mon: https://us.downloads.boemskats.com/esm/utilities/sas-data-mon/experimental/ Execute it at least once to get a file containing SAS Data sets information. Username: esm Password: BoemskaESM   Deploy esm-maintenance. https://us.downloads.boemskats.com/esm/utilities/esm-maintenance/latest/ Username: esm Password: BoemskaESM   SAS Viya   How do you identify source and target tables used by SAS? For SAS 9.x running on Linux or Windows and for SAS Viya 3 and SAS Viya 4 the proc-mon module needs to be configured.   When SAS uses a table logs the “mode” in which this table was opened (input, output, update, etc.). Enterprise Session Monitor captures the mode that a table was opened. A SAS procedure opens both input and output tables for a procedure at the start of the procedure. We can use the start time of a procedure (which Enterprise Session Monitor can also capture) to mark input and output tables for individual steps, but therefore, also at the program level too.   Can the ESM Agent be extended to all databases to collect data for reporting? Yes, you can install the ESM agent on any server regardless of what applications are ran on it.   How accurately can the configuration help you to set up your Azure server SKU? It’s not clear what this question is about. However, if we assume that it is about capacity planning and whether a virtual machine is over or under provisioned then my answer is as follows: Very. Enterprise Session Monitor will give to 2 second interval (aggregated to per-minute and per-hour) metrics on the core compute resources that Azure charge virtual machines on. Using this data, you can see whether you have provisioned too much, or not enough compute for your application. If you have provisioned too little, you could you the generic per-process capabilities of Enterprise Session Monitor to understand what processes are using all of the compute resources.   Is ESM going to replace Environment Manager going into the future? No.   How do you monitor your interactive and batch SAS usage? If you complete the configuration steps for either your SAS 9 or SAS Viya 3 system (SAS Viya 4 is automatic) then you will be capturing both your interactive and Batch SAS sessions.   Can we monitor the Metadata exports/imports? SAS Metadata Server exports/imports consumption of CPU, memory and IO would be captured. Out of-the-box, Enterprise Session Monitor will monitor SAS Metadata Server processes and capture the compute resources consumed. I don’t know whether or not when importing or exporting metadata there are any specific log messages that appear, but specific log file markers could be defined to highlight the start/end of import or export events.   Can we also monitor the SAS servers and service status using ESM? I am going to assume that this means SAS services like Metadata Server, Web Application Server, Object Spawner, or the SAS Microservices (in Viya). If so: Enterprise Session Monitor can be configured to capture compute metrics about “SAS servers/services”. We don’t currently show a true “status” of a service (it might have a process running, but that process may be unresponsive), though this is something that is on our roadmap.   Will it be able to get down to the services memory allocation per service on Viya? Assuming this means Microservices – Yes, Enterprise Session Monitor captures per-service memory allocation.   How do you give multiple Administrators only access to their Jobs to manage? Through the configuration of what Enterprise Session Monitor terms “privileged users”.   Recommended Resources System Tuning Using SAS® Enterprise Session Monitor Why is SAS®9 Content Assessment Key in Migrating to SAS® Viya®? Q&A, Slides, and On-Demand Recording Moving from SAS®9 to SAS® Viya® Please see additional resources in the attached slide deck.   Want more tips? Be sure to subscribe to the Ask the Expert board to receive follow up Q&A, slides and recordings from other SAS Ask the Expert webinars. ... View more

Hi SAS Administrators, I’m presenting a live “Ask the Expert” webinar on January 12 th , 11 AM – Noon ET entitled Why Do I Need SAS® Enterprise Session Monitor and Ecosystem Diagnostics? I’ll give an overview of SAS Enterprise Session Monitor. I’ll also talk about a free service offering from SAS called Ecosystem Diagnostics. Bring your questions. I’ll host a live Q&A session at the end of the webinar.   You will learn: Identify source and target tables used by SAS. Monitor your interactive and batch SAS usage. Use capacity planning report for sizing of SAS environments. Identify usage patterns. Use Ecosystem Diagnostic. Reserve your spot for the webinar.   Want more tips? Be sure to subscribe to the Ask the Expert board to receive follow up Q&A, slides and recordings from this and other SAS Ask the Expert webinars.   Can't join the live event? You can view this and other Ask the Expert sessions on-demand here.  ... View more

Did you miss the Ask the Expert session Leveraging SAS ®  Viya ®  to Improve Base SAS ® Processes? Not to worry, you can catch it on-demand at your leisure. I’ve attached the slides as well.   Watch the webinar   The SAS Platform is greatly enhanced by SAS Viya. In this session, the presenter will share his experiences in leveraging SAS Viya to improve Base SAS processes. Explore technical insights and implementation details to get your code CAS-enabled, achieving faster results. These are illustrated by real-life case studies. In this session, you’ll learn the answers to questions such as: If a process cannot run in CAS, where does it run? What coding components are most frequently encountered in code reviews with customers? What are deployment options for SAS Viya?   Here’s a transcript of the Q&A segment held at the end of the session for ease of reference.   If a process cannot run in CAS where does it run? This is a 2 part answer. If the coding compotent i.e. DATA Step, MEANS, TRANSPOSE… are CAS enabled and the source and target tables to that coding component are CAS tables then the process runs in CAS (fast). If the coding component is not CAS enabled or the source and or target tables are not in CAS then the process will run on the workspace server.   What coding components are most frequency encountered in the code review you have done with customers? Macro code represents ~30% of the SAS code I have reviewed in the last 7 years DATA Step represents ~30% BY Statement Processing in DATA Step represents ~20% Procedures FREQ, MEANS, REPORT, SUMMARY, TABULATE TRANSPOSE represents ~5% to10% The SQL procedures represents ~10% to 25% The SORT procedure represents ~10% to 25%     What are the deployment options for SAS Viya? SAS ® Cloud combines software, infrastructure, and services that are designed and managed by SAS for optimal performance and value. SAS® on Cloud Providers provides cloud-deployable options that allow our customers to run SAS on almost any private, public, or hybrid cloud infrastructure. On-Premise Is the Scalable Performance Data Engine that is delivered with Base SAS supported in Viya? Yes, it is. You can use the SPD Engine libref on the Viya workspace server to read and write SPD Engine tables. In addition, when you store your SPD Engine tables on HDFS you have 3 options in reading SPD Engine tables into CAS and written that CAS table back down to HDFS in SPD Engine format. For additional details review SAS Data Connector to SPD Engine Files and SAS Data Connect Accelerator for SPD Engine Files   Recommended Resources Course: Free SAS Viya Enablement Course: SAS Viya Learning Path Reading material: Parallel Programming with the DATA Step: Next Steps (2018) The following Base SAS procedures are CAS Enabled (2018) SAS ® Viya ™ 3: FedSQL Programming for SAS ®  Cloud Analytic Services (2018) FedSQL Implicit Pass-Through Facility in CAS (2018) What’s New in SAS® Data Connectors for SAS® Viya® (2018) How to Emulate DESCENDING BY Variables in DATA Step Code that Runs Distributed in SAS® Viya™ (2018) DATA Step in SAS® Viya™: Essential New Features (2017) How to Emulate PROC APPEND in CAS (2017) Getting your SAS 9 code to run multi-threaded in SAS Viya 3.3 (2017) Jedi SAS Tricks - FedSQL Dictionary Tables (2017) Six reasons you should stop using the RANUNI function to generate random numbers (2013) Trial: Get an early preview of SAS® Visual Analytics on SAS® Viya™    Want more tips? Be sure to subscribe to the Ask the Expert board to receive follow up Q/A, slides and recordings from other SAS Ask the Expert webinars. To subscribe, select Subscribe from the Options drop down button above the articles. ... View more

In a recent article we learned how to create an SAS Scalable Performanc e Data Engine (SPD Engine) table on HDFS. In this article we will explore the Hive SerDe for SPD Engine data, which provides read access to the SPD Engine table using Hive via non-SAS clients.   In this example, we will create two uncompressed SPD Engine tables stored on HDFS. We’ll then register those tables to the Hive metastore and join them together to create a Hive target table.   Code to create the two SPD Engine tables: 59 +LIBNAME SPDEHDFS SPDE '/user/sasss1/spde' HDFSHOST=default ACCELWHERE=YES PARALLELWRITE=YES PARALLELREAD=YES; NOTE: Libref SPDEHDFS was successfully assigned as follows: Engine: SPDE Physical Name: /user/sasss1/spde/ 66 +DATA SPDEHDFS.PLAYERS; 67 + KEEP name team; 68 + SET sashelp.baseball; 69 +RUN; NOTE: There were 322 observations read from the data set SASHELP.BASEBALL. NOTE: The data set SPDEHDFS.PLAYERS has 322 observations and 2 variables. NOTE: DATA statement used (Total process time): real time 1.75 seconds cpu time 0.16 seconds 70 + 71 +DATA SPDEHDFS.STATS; 72 + KEEP name nhits nruns; 73 + SET sashelp.baseball; 74 +RUN; NOTE: There were 322 observations read from the data set SASHELP.BASEBALL. NOTE: The data set SPDEHDFS.STATS has 322 observations and 3 variables. NOTE: DATA statement used (Total process time): real time 1.38 seconds cpu time 0.15 seconds   For SAS Administrators:   Hive SerDe for SPD Engine Data In the third maintenance release for SAS 9.4, SAS provides a custom Hive SerDe for SPD Engine Data that is stored in HDFS. The SerDe makes the data available for applications outside of SAS to query.   The following are required to access SPD Engine tables using the SPD Engine SerDe: You must deploy SAS Foundation using the SAS Deployment Wizard. Select SAS Hive SerDe for SPDE Data. You must be running a supported Hadoop distribution that includes Hive 0.13: Cloudera CDH 5.2 or later Hortonworks HDP 2.1 or later MapR 4.0.2 or later The following table features are not supported: Compressed or encrypted tables Tables with SAS informats Tables that have user-defined formats Password-protected tables Tables owned by the SAS Scalable Performance Data Server In addition, the following processing functionality is not supported by the SerDe and requires processing by the SPD Engine: Write, Update, and Append operations If preserving observation order is required.   On the Hadoop head node To register the SPD Engine tables to the Hive metastore we need to append to the HADOOP_CLASSPATH the SAS_HADOOP_CONFIG_PATH, as well as the directory paths for the Hive JARs and XMLs files:   export HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$SAS_HADOOP_CONFIG_PATH:opt/cloudera/parcels/CDH/lib/hive/lib/*:/opt/cloudera/paels/CDH/lib/hadoop-mapreduce/lib/*   In the following two examples, we are registering the SPD Engine tables “players and stats” to the Hive “sasss1” database. The following commands executes the SerDe JAR files and registers the SPD Engine tables to the Hive metastore:       hadoop jar /opt/cloudera/parcels/CDH/lib/hive/lib/sas.HiveSerdeSPDE.jar com.sas.hadoop.serde.sp.hive.MetastoreRegistration -table players -mdflocation /user/sasss1/spde -database sasss1 hadoop jar /opt/cloudera/parcels/CDH/lib/hive/lib/sas.HiveSerdeSPDE.jar com.sas.hadoop.serde.sp.hive.MetastoreRegistration -table stats  -mdflocation /user/sasss1/spde -database sasss1 When registering the tables to the Hive metastore the following options are also supported:   -database database_name to specifies a Hive metastore database if you are not using the default database. -mdflocation is the path on HDFS that we used on our SPD Engine LIBNAME. -renametable table-name to assigns a different name to the table stored in HDFS. -owner owner-name that assigns an owner name. The default owner name is Anonymous.   For SAS Programmers: In this example we will create a new Hive table by joining two SPD Engine tables in the MapReduce framework by using the two registered table in the Hive metastore: 58 +/* HIVE LIBREF */ 59 +LIBNAME HIVE HADOOP PORT=10000 TRANSCODE_FAILl=warning HOST="Hadoop.Head.Node" DATABASE=sasss1 USER=sasss1; NOTE: Libref HIVE was successfully assigned as follows: Engine: HADOOP Physical Name: jdbc:hive2://Hadoop,head.node.com:10000/sasss1 102 +PROC SQL; 103 + CREATE TABLE HIVE.PLAYER_STATS AS 104 + SELECT t1.Name, 105 + t1.Team, 106 + t2.nHits, 107 + t2.nRuns 108 + FROM HIVE.PLAYERS t1 109 + INNER JOIN HIVE.STATS t2 ON (t1.Name = t2.Name); NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables. NOTE: Table HIVE.PLAYER_STATS created, with 322 rows and 4 columns. HADOOP_23: Executed: on connection 4 DROP TABLE sasdata_17_43_43_325_00001 Summary Statistics for HADOOP are: Total SQL execution seconds were: 0.137603 Total SQL prepare seconds were: 16.867544 Total SQL describe seconds were: 0.002308 Total seconds used by the HADOOP ACCESS engine were 19.764125 110 +QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 20.61 seconds cpu time 0.17 seconds   As we can see from Figure 1, the above join is using MapReduce to accomplish the join: Figure 1. Cloudera monitor validating the join is being processed via MapReduce     Notice in Figure 2 the SAS Server that submitted this code is not consuming resource: Figure 2. Top command, notice SAS and JPROXY are not consuming resources   Non-SAS clients Once the SPD Engine tables are register to the Hive meatastore any non-SAS tool used to process Hive data can also process (read only) the SPD Engine data using HiveQL. ... View more

In my last article we learned how to create a SAS Scalable Performance Data Server (SPD Server) table. In this post, we will explore how to read that SPD Server table stored on the Hadoop Distributed File System (HDFS).   The Hadoop environment I have access to is small and not a candidate to run official benchmarks on. I am testing with Cloudera - CDH 5.4.5 on 5 Linux Servers (RHEL 6) with 4 of the servers acting as data nodes. Each server has 64GB RAM with Intel(R) Xeon(R) CPU E7-4880 v2 @ 2.50GHz (4 cores) processors. Each server has 251 gigabytes of local storage and for HDFS, we have 3.0 terabytes (NFS mount).   Suffice to say in the real world, Hadoop clusters are much bigger. The customers I have worked with tend to have 100 to 1,000 data nodes. In these environments, source and target tables are in the hundreds of gigabytes if not terabytes.   Un-compressed data When SPD Server data is not compressed, we can push our WHERE statements down to MapReduce by ensuring the SAS administrator has added the parameter HADOOPACCELWH to the SPDSERV.PARM configuration file. We can validate the location of the processing of WHERE statements by setting the SPD Server macro SPDSWDEB=YES: 105 +DATA MYSPDS.&source.3; 106 + SET MYSPDS.&source; 107 + WHERE joinvalue < 3; whinit: WHERE (joinvalue<3) whinit: wh-tree presented /-NAME = [joinvalue] --CLT----| \-LITN = [3] WHERE processing Hadoop cluster optimization candidate whinit returns: ALL EVAL6 108 +RUN; WHERE processing is optimized on the Hadoop cluster NOTE: There were 9375485 observations read from the data set SOURCE.TESTDATA. WHERE joinvalue<3; NOTE: The data set SOURCE.TESTDATA3 has 9375485 observations and 204 variables. NOTE: DATA statement used (Total process time): real time 10:55.34 cpu time 58.80 seconds 111 +PROC SQL; 112 + CREATE TABLE MYSPDS.&source._sql AS 113 + SELECT x1, x3, x5, joinvalue FROM source.&source 114 + WHERE joinvalue < 3; whinit: WHERE (joinvalue<3) whinit: wh-tree presented /-NAME = [joinvalue] --CLT----| \-LITN = [3] WHERE processing Hadoop cluster optimization candidate whinit returns: ALL EVAL6 WHERE processing is optimized on the Hadoop cluster NOTE: Table SOURCE.TESTDATA_SQL created, with 9375485 rows and 4 columns. 115 +QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 9:36.21 cpu time 2.72 seconds All SAS In-Memory technologies will “front load” the SPD Server data into the SAS High Performance Analytical Server. By “front load” we mean the data must leave the Hadoop cluster and flow through the SAS Server in to the memory of the SAS High Performance Analytics Server: 153 +PROC HPLOGISTIC DATA=MYSPDS.&source; 154 +PERFORMANCE NODES=ALL DETAILS; 155 +CLASS j / PARAM= GLM; 156 + WEIGHT c98 ; 157 +MODEL y (EVENT = "0" ) = / LINK= LOGIT; 158 +RUN; NOTE: No explanatory variables have been specified. NOTE: The HPLOGISTIC procedure is executing in the distributed computing environment with 4 worker nodes. NOTE: You are modeling the probability that y='0'. NOTE: Convergence criterion (GCONV=1E-8) satisfied. NOTE: There were 62500000 observations read from the data set MYSPDS.TESTDATA. NOTE: The PROCEDURE HPLOGISTIC printed pages 3-4. NOTE: PROCEDURE HPLOGISTIC used (Total process time): real time 12:05.23 cpu time 10.08 seconds   Compressed data When SPD Server data is compressed, we cannot push our WHERE statements down to MapReduce. For the DATA step test, the compressed real time was 04:34.05 quicker. For the PROC SQL test, the compressed real time was 04:37.70 quicker. These quicker run times are a reflection of the small Hadoop cluster I have access to, with large Hadoop clusters this may not be the case:   108 +DATA MYSPDS.&source.3(COMPRESS=BINARY); 109 + SET MYSPDS.&source; 110 + WHERE joinvalue < 3; whinit: WHERE (joinvalue<3) whinit: wh-tree presented /-NAME = [joinvalue] --CLT----| \-LITN = [3] whinitj: cannot do where pushdown; table compressed whinit returns: ALL EVAL2 111 +RUN; NOTE: There were 9375485 observations read from the data set MYSPDS.TESTDATA. WHERE joinvalue<3; NOTE: The data set MYSPDS.TESTDATA3 has 9375485 observations and 204 variables. NOTE: Compressing data set MYSPDS.TESTDATA3 decreased size by 33.37 percent. NOTE: DATA statement used (Total process time): real time 6:21.29 cpu time 1:00.19 114 +PROC SQL; 115 + CREATE TABLE MYSPDS.&source._sql(COMPRESS=BINARY) AS 116 + SELECT x1, x3, x5, joinvalue FROM MYSPDS.&source 117 + WHERE joinvalue < 3; whinit: WHERE (joinvalue<3) whinit: wh-tree presented /-NAME = [joinvalue] --CLT----| \-LITN = [3] whinitj: cannot do where pushdown; table compressed whinit returns: ALL EVAL2 NOTE: Compressing data set MYSPDS.TESTDATA_SQL decreased size by 11.12 percent. NOTE: Table MYSPDS.TESTDATA_SQL created, with 9375485 rows and 4 columns. 118 +QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 4:58.51 cpu time 2.77 seconds For our PROC HPLOGISTIC test, the compressed SPD Server table real time of 00:10.03.81 was 00:02:01.42 quicker when compared to un-compressed SPD Server real time of 00:12:05.23:  156 +PROC HPLOGISTIC DATA=MYSPDS.&source; 157 +PERFORMACE NODES=ALL DETAILS; 158 +CLASS j / PARAM= GLM; 159 + WEIGHT c98 ; 160 +MODEL y (EVENT = "0" ) = / LINK= LOGIT; 161 +RUN; NOTE: No explanatory variables have been specified. NOTE: The HPLOGISTIC procedure is executing in the distributed computing environment with 4 worker nodes. NOTE: You are modeling the probability that y='0'. NOTE: Convergence criterion (GCONV=1E-8) satisfied. NOTE: There were 62500000 observations read from the data set MYSPDS.TESTDATA. NOTE: The PROCEDURE HPLOGISTIC printed pages 3-4. NOTE: PROCEDURE HPLOGISTIC used (Total process time): real time 10:03.81 cpu time 9.76 seconds     ... View more

In my last article we learned which Hadoop distributions are supported as well as how the SAS Scalable Performance Data (SPD) Server stores data. In this post, we will explore how to create SPD Server tables on the Hadoop Distributed File System (HDFS).   Things to consider when creating SPD Server tables on Hadoop: When the data is not encrypted and un-compressed, WHERE clause pushdown to MapReduce is supported SPD Server Access Control Lists to secure the data is supported SPD Server Cluster Tables, a personal favorite format of mine, are supported SPD Server Cluster Tables WHERE clause pushdown is on the roadmap  SAS administrator In order for us to store SPD Server data on HDFS, the SAS administrator needs to add a domain to our LIBNAMES.PARM configuration file as well as a few parameters to the SPDSSERV.PARM configuration file. Let’s review figure 1. The first three domains point to a cluster file system in a SAS Grid Manager environment. The fourth domain, LIBNAME=spdshdfs, is pointing to the HDFS path we use to store our data. Like a clustered file system or SAN storage, the only Hadoop user id needing READ, WRITE, ALTER, CONTROL access to the HDFS path is the user id that started SPD Server.   Figure 1. LIBNAMES.PARM   Let’s review the parameters of the SPDSERV.PARM file in figure 2. HADOOPXML=- and HADOOPJAR= point to the XML and JAR files that were created when we ran the SAS Software Deployment wizard for SAS Access Interface to Hadoop. HADOOPWORKPATH= specifies the path to the directory in the Hadoop Distributed File System that stores the temporary results of the MapReduce output HADOOPACCELJVER= is the version of Java on the Hadoop cluster. Valid values are 1.6, 1.7, and 1.8. HADOOPACCELWH will attempt to push all SPD Server WHERE clauses to MapReduce   Figure 2. SPDSERV.PARM   SAS programmers We will start by reviewing our SPD Server LIBNAME statement:   LIBNAME source SASSPDS IP=YES DISCONNECT=YES LIBGEN=YES HOST='localhost' SERV='5400' SCHEMA='spdshdfs' USER='sasss1' PASSWORD=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX; NOTE: User sasss1(ACL Group ADMGRP) connected to SPD(LAX) 5.2 HF(1) server at 10.36.150.120. NOTE: Libref SOURCE was successfully assigned as follows: Engine: SASSPDS Physical Name: :29121/user/sas/spds/speedy/ MYSPDS is the libref we will reference in our SAS code to process the SPD Server data stored on HDFS SASSPDS is the engine SPD Server uses to process data stored on HDFS HOST='localhost' is the name of the computer where SPD Server is running SCHEMA='spdshdfs' is referencing the SPD Server domain defined to LIBNAMES.PARM, see figure 1. The path defined to this domain is the location on HDFS that we will store our SPD Server data USER= is our SPD Server user id PASSWORD= is our SPD Server password IP=YES will attempt to push all PROC SQL down to SPD Server for processing DISCONNECT=YES specifies when to close network connections between the SAS client and the SPD Server. Closure can occur after all librefs are cleared or at the end of a SAS session LIBGEN=YES option is used in explicit SQL connection statements. When you set LIBGEN= yes, SPD Server is configured to generate additional domain connections that enable you to perform SQL joins across different SPD Server domains.   Creating a compressed SPD Server Table:   DATA MYSPDS.&source (COMPRESS=BINARY);      ARRAY x{100};      ARRAY c{100};      DO i=1 TO 625000 * &Num_GB;       DO j=1 TO 100;            x{j}=RANUNI(2);            c{j}= INT(RANUNI (1)*4);         END;         y= INT(RANUNI (1)*2);         joinvalue=INT(RANUNI (1)*20);         OUTPUT;      END; RUN;   NOTE: The data set MYSPDS.TESTDATA has 62500000 observations and 204 variables. NOTE: Compressing data set MYSPDS.TESTDATA decreased size by 33.35 percent. NOTE: DATA statement used (Total process time):       real time           24:20.03           cpu time            10:26.28   Creating an un-compressed SPD Server Table using PROC APPEND:   PROC APPEND BASE=SPDS.TESTDATA data=BDAT.TESTDATA; RUN;   NOTE: Appending BDAT.TESTDATA to SPDS.TESTDATA. NOTE: BASE data set does not exist. DATA file is being copied to BASE file. INFO: Engine's block-read method cannot be used because the file is compressed   NOTE: There were 62500000 observations read from the data set BDAT.TESTDATA. NOTE: The data set SPDS.TESTDATA has 62500000 observations and 204 variables. NOTE: PROCEDURE APPEND used (Total process time):         real time           11:34.68       cpu time            9:29.99   To improve I/O operation performance, consider setting a different SPD Server I/O block size. Keep in mind that the larger the block size (i.e. 64K) the less I/O. For example, when reading a data set, the block size can significantly affect performance. When retrieving a large percentage of the data, a larger block size improves performance. However, when retrieving a subset of the data such as with WHERE processing, a smaller block size (i,e. 8K) performs better. You can specify a different block size with the IOBLOCKSIZE= LIBNAME statement option, or the IOBLOCKSIZE= data set option when you create the SPD Server table.   SPD Server uses access control lists (ACLs) and SPD Server user IDs to secure domain resources. You obtain your user ID and password from your SPD Server administrator.   SPD Server supports ACL groups, which are similar to UNIX groups. SPD Server administrators can associate an SPD Server user with as many as 32 different ACL groups.   ACL file security is turned on by default when an administrator starts an SPD Server. ACL permissions affect all SPD Server resources, including domains, tables, table columns, catalogs, catalog entries, and utility files. When ACL file security is enabled, SPD Server grants access rights only to the owner (creator) of an SPD Server resource. Resource owners can use PROC SPDO to grant ACL permissions to a specific group (ACL group) or to all SPD Server users.   The resource owner can use the following properties to grant ACL permissions to all SPD Server users:   READ Universal Read access to the resource (read or query)   WRITE Universal Write access to the resource (append to or update)   ALTER Universal Alter access to the resource (rename, delete, or replace a resource, and add or delete indexes associated with a table)   The resource owner can use the following properties to grant ACL permissions to a named ACL group:   GROUPREAD Group Read access to the resource (read or query)   GROUPWRITE Group Write access to the resource (append to or update)   GROUPALTER Group Alter access to the resource (rename, delete, or replace a resource, and add or delete indexes associated with a table)   A note about compression and encryption: both are supported using a data set option. Encryption is for data in transit as well as at rest. Note, you cannot compress and encrypt a table; you can only compress or encrypt. If you compress or encrypt, the SPD Server table WHERE process cannot be pushed down to MapReduce.   Speaking of reading SPD Server data stored on HDFS, stay tuned, in my next post we will explore how to read a SPD Server table on HDFS. ... View more

In a previous article, we learned how to create a SAS Scalable Performance Data (SPD) Engine table that is stored on HDFS. Here, as part of the SAS Data Management for Hadoop article series, we’ll explore how to read an SPD Engine table stored on HDFS.   I’ll take you through an example whereby the size of my SPD Engine table is 100GB and the WHERE statement returns 14% of the data.   Let’s start by reviewing the options in the following SPD Engine LIBNAME statement.   LIBNAME MYSPDE SPDE '/user/sasss1' HDFSHOST=DEFAULT PARALLELWRITE=YES PARALLELREAD=YES ACCELWHERE=YES; MYSPDE is the LIBREF we will reference in our SAS code to process the SPD Engine data stored on HDFS SPDE is the engine SPD Engine uses to process SPD Engine tables '/user/sasss1' is the path on HDFS that our SPD Engine data will be read from HDFSHOST=DEFAULT will default to the Hadoop system that the following SAS/ACCESS Interface to Hadoop OPTIONS point to options set=SAS_HADOOP_CONFIG_PATH= options set=SAS_HADOOP_JAR_PATH= PARALLELWRITE=YES tells SPD Engine to use parallel processing to write data to HDFS PARALLELREAD=YES tells SPD Engine to use parallel processing to read data stored in HDFS ACCELWHERE=YES tells SPD Engine to push all WHERE clauses down to Hadoop UN-COMPRESSED data When SPD Engine data is not compressed, we can push our WHERE statements down to MapReduce by ensuring the SPD Engine LIBNAME option ACCCELWHERE= is set to YES. To validate where the WHERE statements are processed, set the SPD Engine macro SPDSWDEB= to YES. With SPDSWDEB=YES, the SAS log provides information on the processing location of WHERE statements.     12 +%let SPDSWDEB=YES; **SAS Log info on where WHERE was processed; 80 +data MYSPDE.&source.3; 81 + set MYSPDE.&source; 82 + where joinvalue < 3; whinit: WHERE (joinvalue<3) whinit returns: ALL EVAL2 83 +run; WHERE processing is optimized on the Hadoop cluster Hadoop Job ID: job_1450299322397_0061 INFO: Parallel write processing is being performed using 4 threads. NOTE: There were 9375485 observations read from the data set MYSPDE.TESTDATA. WHERE joinvalue<3; NOTE: The data set MYSPDE.TESTDATA3 has 9375485 observations and 204 variables. NOTE: DATA statement used (Total process time): real time 8:55.79 cpu time 26.11 seconds 86 +proc sql; 87 + create table MYSPDE.&source._sql as 88 + select x1, x3, x5, joinvalue from MYSPDE.&source 89 + where joinvalue < 3; whinit: WHERE (joinvalue<3) whinit returns: ALL EVAL2 WHERE processing is optimized on the Hadoop cluster Hadoop Job ID: job_1450299322397_0062 INFO: Parallel write processing is being performed using 4 threads. NOTE: Table MYSPDE.TESTDATA_SQL created, with 9375485 rows and 4 columns. 90 +quit; NOTE: PROCEDURE SQL used (Total process time): real time 7:45.81 cpu time 11.79 seconds   When SPD Engine data is not compressed, we can also process that data using the SAS In-Database Code Accelerator for Hadoop. SAS In-Database Code Accelerator for Hadoop is part of the bundle of software you get when you license SAS Data Loader for Hadoop. The SAS In-Database Code Accelerator for Hadoop uses the SAS DS2 language to run DS2 code in a MapReduce framework. To accomplish this, create a DS2 THREAD program that contains your source table(s) and the business rules you need to apply to that data. To execute that THREAD program, in parallel on every data node in your Hadoop cluster, we declared it to the DS2 DATA program and then executed it using the SET statement of that DATA program.     104 +proc ds2; 105 +THREAD work.thread / overwrite=yes; 106 +dcl double count; 107 +method run (); 108 + set myspde.&source; 109 + by joinvalue; 110 + if first.joinvalue then count = 0; 111 + count + 1; 112 + if last.joinvalue; 113 +end; 114 +ENDTHREAD; 115 +DATA MYSPDE.&source.2 (overwrite=yes); 116 +DECLARE THREAD work.thread thrd; 117 +method run (); 118 + SET FROM thrd; 119 +end; 120 +ENDDATA; 121 +run; NOTE: Created thread thread in data set work.thread. NOTE: Running THREAD program in-database NOTE: Running DATA program in-database NOTE: Execution succeeded. No rows affected. 122 +quit; NOTE: PROCEDURE DS2 used (Total process time): real time 20:39.18 cpu time 2.59 seconds   When SPD Engine data is not compressed, all SAS-High Performance procedures will lift that data, in parallel, in to the memory of the SAS-High-Performance Analytics Server.       128 +PROC HPLOGISTIC DATA = MYSPDE.&source; 129 +performance nodes=all DETAILS; 130 +Class j / PARAM= GLM; 131 + WEIGHT c98 ; 132 +MODEL y (EVENT = "0" ) = / link= LOGIT; 133 +run; NOTE: No explanatory variables have been specified. INFO: Read the content of utility file /opt/sasinside/XMLS/core-site.xml. INFO: Read the content of utility file /opt/sasinside/XMLS/hdfs-site.xml. INFO: Read the content of utility file /opt/sasinside/XMLS/mapred-site.xml. INFO: Read the content of utility file /opt/sasinside/XMLS/yarn-site.xml. NOTE: The HPLOGISTIC procedure is executing in the distributed computing environment with 4 worker nodes. NOTE: You are modeling the probability that y='0'. NOTE: Convergence criterion (GCONV=1E-8) satisfied. NOTE: The PROCEDURE HPLOGISTIC printed pages 7-8. NOTE: PROCEDURE HPLOGISTIC used (Total process time): real time 1:59.46 cpu time 3.29 seconds   COMPRESSED data When the SPD Engine data is compressed, we cannot push our WHERE statements down to MapReduce. But do not fret – in my testing, the real time is faster than pushing that WHERE statement down to MapReduce. For the DATA-Step test, the compressed real time was 3:38.48 quicker. For the PROC SQL test, the compressed real time was 3:18.39 quicker.    87 +data MYSPDE.&source.3; 88 + set MYSPDE.&source; 89 + where joinvalue < 3; whinit: WHERE (joinvalue<3) The data set is compressed WHERE processing cannot be optimized on the Hadoop cluster whinit returns: ALL EVAL2 90 +run; NOTE: There were 9375485 observations read from the data set MYSPDE.TESTDATA. WHERE joinvalue<3; NOTE: The data set MYSPDE.TESTDATA3 has 9375485 observations and 204 variables. NOTE: DATA statement used (Total process time): real time 5:17.31 cpu time 5:06.40 93 +proc sql; 94 + create table MYSPDE.&source._sql as 95 + select x1, x3, x5, joinvalue from MYSPDE.&source 96 + where joinvalue < 3; whinit: WHERE (joinvalue<3) The data set is compressed WHERE processing cannot be optimized on the Hadoop cluster whinit returns: ALL EVAL2 NOTE: Table MYSPDE.TESTDATA_SQL created, with 9375485 rows and 4 columns. 97 +quit; NOTE: PROCEDURE SQL used (Total process time): real time 4:27.42 cpu time 4:45.59   When the SPD Engine data is compressed, we cannot process that data using the MapReduce framework using DS2 code and SAS In-Database Code Accelerator for Hadoop.    111 +proc ds2; 112 +thread work.thread / overwrite=yes; 113 +dcl double count; 114 +method run (); 115 + set MYSPDE.&source; 116 + by joinvalue; 117 + if first.joinvalue then count = 0; 118 + count + 1; 119 + if last.joinvalue; 120 +end; 121 +endthread; 122 +data MYSPDE.&source.2 (overwrite=yes); 123 +dcl thread work.thread thrd; 124 +method run (); 125 + set from thrd; 126 +end; 127 +enddata; 128 +run; NOTE: Created thread thread in data set work.thread. NOTE: Running THREAD program in-database NOTE: Running DATA program in-database ERROR: Failed to run DS2INDB. ERROR: The path was not found: /user/sasss1/spde//sasds2_spd_k2s8ho_es44t8bpnr. ERROR: Error returned from tkedsPubINDBDS2. NOTE: Execution succeeded. No rows affected. 129 +quit; NOTE: PROCEDURE DS2 used (Total process time): real time 7.56 seconds cpu time 1.48 seconds   When the SPD Engine data is compressed, SAS-High Performance procedures will “front load” the data in to the memory of the SAS-High-Performance Analytics Server. By “front load” we mean the data must leave the Hadoop cluster and flow through the SAS Server in to the memory of the SAS-High-Performance Analytics Server. In this case, the compressed real time was 8:56.43 slower.    135 +PROC HPLOGISTIC DATA = MYSPDE.&source; 136 +performance nodes = all DETAILS; 137 +Class j / PARAM= GLM; 138 + WEIGHT c98 ; 139 +MODEL y (EVENT = "0" ) = / link= LOGIT; 140 +run; NOTE: No explanatory variables have been specified. NOTE: Data set MYSPDE.TESTDATA cannot be processed in the Embedded Process Environment because the data set is either encrypted or compressed. NOTE: The data MYSPDE.TESTDATA are being routed through the client. NOTE: The HPLOGISTIC procedure is executing in the distributed computing environment with 4 worker nodes. NOTE: You are modeling the probability that y='0'. NOTE: Convergence criterion (GCONV=1E-8) satisfied. NOTE: There were 62500000 observations read from the data set MYSPDE.TESTDATA. NOTE: The PROCEDURE HPLOGISTIC printed pages 3-4. NOTE: PROCEDURE HPLOGISTIC used (Total process time): real time 10:15.89 cpu time 4:23.25    To compress or not to compress? That is the question. The answer depends on how you plan to process that data. If you’ve licensed SAS-High Performance procedures, SAS Visual Analytics, SAS Visual Statistics, or SAS IMSTAT, you will want to store your data in an un-compressed format. You’ll have quicker real time for your SAS-High Performance procedures. For SAS Visual Analytics, SAS Visual Statistics, and SAS IMSTAT, it will lift data in to memory quicker. Lastly, it will allow you to leverage the SAS In-Database Code Accelerator for Hadoop when staging your data for visualization, reporting, and predictive analytics.   Stay tuned, in my next article we will explore how non-SAS clients can process SPD Engine data stored on HDFS.   And be sure to follow the Data Management section of the SAS Communities Library (Click Subscribe in the pink-shaded bar of the section) for more articles on how SAS Data Management works with Hadoop. Here are links to other posts in the series for reference:   How to persist native SAS data sets to Hadoop (Hive) Give analysts 80% of their time back SAS/Access to Hadoop, not your parents' access engine How to leverage the Hadoop Distributed File System using the SAS Scalable Performance Data Engine How to leverage the Hadoop Distributed File System using the SAS Scalable Performance Data Server How to create SAS Scalable Performance Data Engine Tables on the Hadoop Distributed File System Your data is in Hadoop. Now what? What file types does the SAS In-Database Code Accelerator for Hadoop support? SAS HADOOP procedure: Managing data in Hadoop is the first order ... View more