You may have partitioned a CAS table to improve the overall performance of Viya application. Over time, if there is a need for incremental data append to partitioned CAS table, the task is not simple. In this post, I discuss the data append process for a partitioned CAS table.   A CAS table can be partitioned on a specific data column to improve the CAS action performance. A partitioned CAS table avoids the auto partitioning and data movement among CAS worker nodes at run time for the by-group operation.   The data append to a partitioned CAS table is not supported. The data can be appended only to a standard CAS table (non-partitioned). When data is appended to a partitioned CAS table, it generates the following error message:   75 76 data pubSPRE.DM_FACT_MEGA_CORP_GOLD( append=yes ); 77 set pubSPRE.DM_FACT_MEGA_CORP_APND; NOTE: The DATA step will run in multiple threads. NOTE: Executing action 'dataStep.runBinary'. ERROR: DATA step cannot append to table DM_FACT_MEGA_CORP_GOLD in caslib DM. Append is not supported for partitioned tables. ERROR: The action stopped due to errors. How to append data to a partitioned CAS table The data can be appended to a partitioned CAS table using an intermediate staging table with the following steps. To append data to a partitioned CAS table you must have enough (thrice the size of the table) memory space (read CAS_DISK_CACHE) to accommodate two intermediate CAS table for a short period. Copy an existing in-memory partitioned CAS table to a staging CAS table without data partition. Append incremental data into the same staging CAS table. Create a new partitioned staging CAS table from appended staging CAS table. Drop the global in-memory partitioned CAS table. Promote the staging table (session scope) to global scope with the original table name. When creating a staging table, do not use the original name for the target table name. When the same original name is used for target staging table, there will be two CAS tables with the same name, one in sessions scope and another in the global scope. When drop table action is submitted for same name table, it will drop the session scope staged table rather than the global scope table. The promote action will fail as there is no session-scope table. The global table will be unaffected; no new data appended. So, avoid confusion and use a new name (e.g. _stg) for staging table and stay away from getting same name CAS table in session scope and global scope.   The following code example describes the process of data append to an in-memory partitioned global CAS table. The data step used for creating a staging table and data append runs in CAS as both source and target is a CAS table. Step 3 creates a new staging table with partitioned data since data partition CAS action does not support in-place data partition.   CAS mySession SESSOPTS=( CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=yes); /* Note the number of rows in CAS table before data append */ proc cas ; action table.tabledetails / name="DM_FACT_MEGA_CORP_GOLD" caslib="DM"; run ; /* 1. Copy existing global CAS table into a session scope staging CAS table. */ libname pubSPRE cas caslib="DM"; data pubSPRE.DM_FACT_MEGA_CORP_STAGE; set pubSPRE.DM_FACT_MEGA_CORP_GOLD; run ; /* 2. Incremental data append into staging table */ data pubSPRE.DM_FACT_MEGA_CORP_STAGE( append=yes ); set pubSPRE.DM_FACT_MEGA_CORP_APND; run ; /* 3. Create a new staging table with data partition from appended staging table */ proc cas ; table.partition / casout={name="DM_FACT_MEGA_CORP_STAGE_N", caslib="DM"}, table={name="DM_FACT_MEGA_CORP_STAGE", caslib="DM", groupby={{name="FacilityCity"}},orderby={{name="date"}}}; quit ; /* 4. Drop the existing global CAS table. */ /* 5. Promote the partitioned staging CAS table to global CAS table with same original name */ Proc casutil; droptable casdata="DM_FACT_MEGA_CORP_GOLD" incaslib="DM" quiet; promote incaslib="DM" outcaslib="DM" casdata="DM_FACT_MEGA_CORP_STAGE_N" casout="DM_FACT_MEGA_CORP_GOLD" ; quit; /* verify new CAS table have appended data with data partition. */ proc cas ; action table.tabledetails / name="DM_FACT_MEGA_CORP_GOLD" caslib="DM"; run ; proc cas; table.tabledetails result=r / level='partition' name="DM_FACT_MEGA_CORP_GOLD" caslib="DM" ; saveresult r dataout=work.r ; run ; quit ; proc print data=work.r; var node blocks rows DataSize 'Key(FacilityCity)'n; run; CAS mySession TERMINATE;   Log extract from code execution:   ………………….. ……… 75 76 /* 1. Copy existing global CAS table into a session scope staging CAS table. */ 77 libname pubSPRE cas caslib="DM"; NOTE: Libref PUBSPRE was successfully assigned as follows: Engine: CAS Physical Name: 86b07d6f-2eab-7041-812d-fa73baed385a 78 79 data pubSPRE.DM_FACT_MEGA_CORP_STAGE; 80 set pubSPRE.DM_FACT_MEGA_CORP_GOLD; 81 run ; NOTE: Running DATA step in Cloud Analytic Services. NOTE: The DATA step will run in multiple threads. NOTE: Executing action 'dataStep.runBinary'. NOTE: There were 3722071 observations read from the table DM_FACT_MEGA_CORP_GOLD in caslib DM. NOTE: The table DM_FACT_MEGA_CORP_STAGE in caslib DM has 3722071 observations and 39 variables. NOTE: DATA statement used (Total process time): real time 4.00 seconds cpu time 0.25 seconds 82 83 /* 2. Incremental data append into staging table */ 84 data pubSPRE.DM_FACT_MEGA_CORP_STAGE( append=yes ); 85 set pubSPRE.DM_FACT_MEGA_CORP_APND; 86 run ; NOTE: Running DATA step in Cloud Analytic Services. NOTE: The DATA step will run in multiple threads. NOTE: Executing action 'dataStep.runBinary'. NOTE: There were 3722071 observations read from the table DM_FACT_MEGA_CORP_APND in caslib DM. NOTE: The table DM_FACT_MEGA_CORP_STAGE in caslib DM has 7444142 observations and 39 variables. NOTE: The APPEND operation for table DM_FACT_MEGA_CORP_STAGE in caslib DM added 3722071 observations. NOTE: DATA statement used (Total process time): real time 4.28 seconds cpu time 0.21 seconds 87 88 /* 3. Create a new staging table with data partition from appended staging table */ 89 proc cas ; 90 table.partition / 91 casout={name="DM_FACT_MEGA_CORP_STAGE_N", caslib="DM"}, 92 table={name="DM_FACT_MEGA_CORP_STAGE", caslib="DM", 93 groupby={{name="FacilityCity"}},orderby={{name="date"}}}; 94 quit ; NOTE: Active Session now MYSESSION. NOTE: Executing action 'table.partition'. NOTE: Action 'table.partition' used (Total process time): {caslib=DM,tableName=DM_FACT_MEGA_CORP_STAGE_N,rowsTransferred=2790769,shuffleWaitTime=8.6317923069,minShuffleWaitTime=0, maxShuffleWaitTime=0.7270908356,averageShuffleWaitTime=0.000617748} NOTE: PROCEDURE CAS used (Total process time): real time 20.56 seconds cpu time 0.77 seconds …… ………………….   Result extract from code execution: The number of rows before data append to a partitioned table   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   The number of rows after data append to a partitioned table     CAS table partition information after data append to a partitioned table   ... View more

In a multinode CAS environment, you may have unbalanced CAS tables. CAS tables not evenly distributed across all the CAS worker nodes. When the CAS tables are not properly balanced, the SAS Viya environment underperforms.   In a multinode CAS environment, CAS tables get unbalanced due to the following reasons. CAS loaded using Multinode Data Load method.   When Multinode Data Load process selects a skewed column to split the source data or uses a lesser number of CAS Worker node than total available CAS nodes, the CAS table ends up with uneven data distribution across all CAS Worker nodes. In this scenario, when a user submits a CAS action (through reports or UI) against these tables, CAS has to temporarily move some of the data blocks amongst CAS nodes to utilize all Worker nodes and achieve parallel processing. The data movement on the fly impacts the overall performance of the SAS Viya environment.   (Note: The above statement is valid for most of the Data Connector except CDE. CDE Data connector redistributes data amongst CAS Worker nodes.)   A failed and repaired CAS Worker node came back to the environment.   With high availability feature for CAS tables, in a multinode CAS environment when one or more CAS worker nodes goes down the CAS tables are still available to users. The high availability of the CAS table depends on the number_of_copies parameter used during CAS load. With default value 1, when one of the CAS Worker nodes goes down, the CAS tables are still available. To make CAS table more node fault tolerant, you need to consider a higher number for copies parameter. The size of the CAS table is directly proportional to the number_of_copies parameter.   When a repaired CAS Worker node becomes part of the CAS environment, it does not have any data blocks from existing in-memory CAS tables. However, CAS will utilize the re-instated CAS worker node for multithread processing. CAS does not auto distribute the existing CAS table to re-instated CAS nodes. CAS will temporarily move parts of CAS table data to re-instated CAS worker node for each CAS action to execute the multi-threaded process. This on-the-fly data movement amongst the CAS nodes will cause overall performance degradation to Viya environment. To improve Viya performance, you need to manually re-write the CAS table to balance across all the CAS worker nodes. You can utilize the in-memory data to re-write the CAS table. There is no need to re-load CAS from the source environment. You can re-write a CAS table from in-memory data provided you have enough (twice the size of table) memory space (read CAS_DISK_CACHE) to accommodate the intermediate CAS table for a short period.   You can re-distribute an existing in-memory CAS table in a three-step process. Copy an existing in-memory global CAS table into a staging CAS table. Drop the unbalance in-memory global CAS table. Promote the staged CAS table to global CAS table with same original name. The three-step process is required as you cannot in-place overwrite a global CAS table. You can create a staging CAS table from a global CAS table by using data steps or table partition CAS action. The table partition CAS action without a variable name just re-writes the CAS table across all worker nodes.   When creating a staging table, do not to use the original name for the target table name. When the same original name used for target staging table, there will be two CAS tables with the same name - one in session scope and another in the global scope. When you submit a drop table action for same name table, it will drop the session scope staged table rather than the global scope table. The promote action will fail with an error stating "there is no session-scope table " to promote. Your global table will be unaffected, untouched, and not balanced. So, avoid confusion and use a new name (e.g. _stg) for staging table and stay away from getting the same name table in session scope and global scope.   The following code example describes the process of re-distributing an in-memory CAS table across all CAS worker nodes.   CAS mySession SESSOPTS=( CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=yes); /* Copy existing Global CAS table into a session bases staging CAS table. */ proc cas ; table.partition / casout={name="prdsale_stage" caslib="public"}, table={name="prdsale_org" caslib="public" }; quit ; /* Drop the existing un-balanced global CAS table. */ /* Promote the staging CAS table to global CAS table with same original name */ Proc casutil; droptable casdata="prdsale_org" incaslib="public" quiet; promote incaslib="public" outcaslib="public" casdata="prdsale_stage" casout="prdsale_org" ; quit; /* verify new CAS table is distributed across all the CAS nodes. */ proc cas ; action table.tabledetails / name="prdsale_org" caslib="public" level="node" ; run ; CAS mySession TERMINATE; ... View more

With SAS Viya 3.4 18W38 release, Viya user have a new Data Connector to access and load data from Cloud Data Exchange server to CAS. Cloud Data Exchange (CDE) is a capability to securely access customer’s on-premise data (behind a firewall) from SAS Viya 3.4 application hosted at a public or private cloud environment. This post is about data load from CDE to CAS.   Once the data source services are defined on Data Agent Server, CAS can be loaded from CDE either using Serial or Parallel data load method. CDE Serial Data Load to CAS: The serial data load method transfers data from data agent server to CAS via CAS controller server using single process thread. The CAS controller divides and distribute data blocks amongst the CAS worker nodes in the round-robin fashion.   The following animation describes the serial data load from CDE to CAS:     The following code example describes the serial data load from CDE to CAS. It opens a CAS session and defines a session CASLIB with “clouddex” source type. The CASLIB is using conopts=”DSN=hiveserv1”, a data service defined on data agent server to access Hadoop/Hive tables. The PROC CAS is loading a hive table to CAS through CDE. The Hive table is accessed through CDE using a secured connection between SAS Viya and Data Agent Server.   CAS mySession SESSOPTS=( CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true); /* Create a Session based Hive_CDE CASLIB */ proc cas; session mySession; action addCaslib / caslib="hive_cde" datasource={srcType="clouddex" username="viyademo01", password="lnxsas", dataAgentName="dagentsrv-shared01-default", schema="CASDM", conopts="dsn=hivesrv1" }; run; quit; /** Serial data Load to CAS */ proc cas; session mySession; action loadTable / casLib="hive_cde" path="cars" casout={ casLib="hive_cde" , name="cars_srl" } ; run; quit; CDE Parallel Data Load to CAS: The CDE Parallel data load method directly transfers data from data agent server to CAS Worker Nodes. It is initiated by including numReadNodes > 1 in load statement. This data load method is an improved version of Multi-Node Data Transfer method with additional parameters like data split column, data split range, dataOnReaderNodesOnly etc. The additional parameter discussed in this post is only valid for CDE Data Connector and not to other databases Data Connectors. The CDE Parallel data load to CAS is not a SAS EP based data load. Based on the value of numReadNodes=, multiple CAS worker node concurrently fetches part of data from Data Agent Server to CAS. The CAS Controller prepares the data split subquery for reader CAS worker node, formula discussed later in this post. The reader CAS Worker node further distribute the data to non-reader CAS Worker nodes using newly available option dataOnReaderNodesOnly. By default, the value for the dataOnReaderNodesOnly is FALSE. To use all available CAS Worker nodes for parallel data load use numReadNodes=0. To enable parallel data load to CAS, CDE Data Connector must be installed on each CAS nodes.   The Following animation describes the parallel data load from CDE to CAS. Two CAS worker nodes read data from Data Agent Server and further distribute the data to rest of non-reader CAS Worker nodes.   The data split for CAS Worker Nodes The CAS controller prepares the data split subquery to fetch part of data at reader CAS worker nodes. CDE Data Connector has its own formula to split the data compare to using mod() function on the first numeric column of the data table. There are two ways to split the data for reader CAS Worker nodes. Using auto Split with useMinMaxToSplit= option Using a Metadata table Using Auto Split The auto split data fetch at CAS is initiated by including numReadNodes > 1, useMinMaxToSplit=TRUE and useMetaTable=FALSE options in load statement. The load statement can also have optional parameters like data split column and data split range. When the split column name is not provided, the first numeric column from the source table is selected. When the split range is not provided, it is calculated on the split column using formula Split Range= ((Max – Min) / numReadNodes).   Each assigned CAS Worker Node will fetch the data by the value of the split range, starting from the minimum value of the split column.   Let’s look at an example with sample data sets to see how the auto split works. Let’s first look at data split with split column and split range. In the following example the source data table (car) has 3 columns make, msrp and model. The load statement includes ‘msrp’ as the split column and the split range of 20000. The 3 reader nodes are being used for data load. Based on given parameters, the CAS will create three data splits with the split range of 20000 starting from min value of ‘msrp’ column.   ….. datasourceOptions={numReadNodes=3, useMetaTable=FALSE, useMinMaxToSplit = TRUE, splitColumn="msrp", splitRange = 20000 } ….   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   The following formula is used for data split and to fetch part of data at reader worker nodes.   First Data Split: Inner bound = Min (5000) = 5000 and less Outer bound = Min (5000) + Split Range (20000) = 25000 SQL = “select * from cars where msrp <25000 or msrp is NULL”   Second Data Split: Inner bound = First data split outer bound (25000) = 25000 Outer bound = First data split outer bound (25000) + Split Range (20000) = 45000 SQL = “select * from cars where msrp >= 25000 and msrp < 45000”   Third Data Split: Inner bound = Second data split outer bound (45000) = 45000 Outer bound = Grater than Second data split outer bound (45000) and all = >45000 SQL = “select * from cars where msrp > 45000 “   Now, let’s see how the auto data split works when there is no split column and split range defined in the load statement. In the following example, the ‘msrp’ is selected for the split column since it’s the first numeric column in the data table. The split range is being calculated on msrp column using formula (max-min)/ numReadNodes. The CAS creates three data splits with the calculated split range starting from min value of ‘msrp’ column.   ….. datasourceOptions={numReadNodes=3, useMetaTable=FALSE, useMinMaxToSplit = TRUE } ….     The following formula is used for data split and to fetch part of data at reader worker nodes.   Split Range = (Max (70000) – Min (5000) / 3) = 21666   First Data Split: Inner bound = Min (5000) = 5000 and less Outer bound = Min (5000) + Split Range (21666) = 26666 SQL = “select * from cars where msrp <26666 or msrp is NULL”   Second Data Split: Inner bound = First data split outer bound (26666) = 26666 Outer bound = First data split outer bound (26666) + Split Range (21666) = 48332 SQL = “select * from cars where msrp >= 26666 and msrp < 48332”   Third Data Split: Inner bound = Second data split outer bound (48332) = 48332 Outer bound = Grater than Second data split outer bound (48332) and all = >48332 SQL = “select * from cars where msrp > 48332 “   The following code example describes the parallel data load from CDE to CAS using “Auto data split” mechanism with split column and range.   ….. proc cas; session mySession; action loadTable / casLib="hive_cde" path="cars" datasourceOptions={numReadNodes=3, useMetaTable=FALSE, useMinMaxToSplit = TRUE, splitColumn="msrp", splitRange = 20000, traceFile="/tmp/cdetrace.log", traceFlags="SQL" } casout={ casLib="hive_cde" , name="cars_splt" } ; run; quit; ….   With trace file option in data load statement, you can generate a trace log on each CAS worker nodes. You can look at the trace log to verify the SQL submitted from reader CAS worker node to the Data Agent Server. The following is the extract from the trace file generated from the data load statement.   Note: Please don’t compare the Split range listed in the following log with the earlier example. This log is based on data present in cars table.   Extract from /tmp/cdetrace.log at SASCAS01 server: [cloud-user@intcas01 tmp]$ egrep 'Sql=' /tmp/cdetrace.log In: StatementHandle=0x7f7320f0a6e0, Sql=`SELECT * FROM "CASDM"."cars" WHERE 0=1`, SqlL=38 In: StatementHandle=0x7f7320f0a6e0, Sql=`SELECT MIN(msrp), MAX(msrp ) FROM "CASDM"."cars" WHERE msrp IS NOT NULL `, SqlL=72 Extract from /tmp/cdetrace.log at SASCAS02 server: [cloud-user@intcas02 tmp]$ egrep 'Sql=' /tmp/cdetrace.log In: StatementHandle=0x7ff178144780, Sql=`SELECT * FROM "CASDM"."cars" WHERE ( "msrp" < 30280 OR "msrp" IS NULL ) `, SqlL=73 Extract from /tmp/cdetrace.log at SASCAS03 server: [cloud-user@intcas03 ~]$ egrep 'Sql=' /tmp/cdetrace.log In: StatementHandle=0x7fa210026780, Sql=`SELECT * FROM "CASDM"."cars" WHERE ( "msrp" >= 30280 AND msrp < 50280 ) `, SqlL=73 Extract from /tmp/cdetrace.log at SASCAS04 server: [cloud-user@intcas04 ~]$ egrep 'Sql=' /tmp/cdetrace.log In: StatementHandle=0x7f84ac086700, Sql=`SELECT * FROM "CASDM"."cars" WHERE ( "msrp" >= 50280 ) `, SqlL=56   Using a Metadata table To use a metadata-based parallel load, a user must create and populate a metadata table with following structure at the source database. The Metadata table stores information about how to split the data for a given data table. A metadata based parallel (multinode) load is initiated when numReadNodes > 1, metaCatalog=, metaSchema=, metaTable=, and useMetaTable= parameters included in load statement. By default, the value for useMetaTable= is TRUE. Metadata varchar column max length is 256.     The column COLUMN_NAME defines the split column from the data table. The column SPLIT defines the number of concurrent data splits to be fetched by multiple reader nodes. For a given SPLIT number, there are as many entries (HISTOGRAM_ENTRY) as (SPLIT - 1) of splits with a bound value (HISTOGRAM_BOUNDS) defined.   Let’s look at an example of metadata based parallel load. The following metadata describes the configuration to use when ‘cars’ table is parallel loaded to CAS using ‘cdemeta’ table. In this example, two ways of data splitting are configured for source table(cars).   Metadata table (cdemeta)   When numReadNodes=2 in load statement, the configuration of SPLIT=2 will be used for 2 reader nodes to fetch data simultaneously. The first node fetches data where msrp < 20000 and all the NULL value entries. The second node fetches data where msrp >= 20000.   When numReadNodes=3 in load statement, the configuration of SPLIT=3 will be used for 3 reader nodes to fetch data simultaneously. The first node fetches data where msrp < 20000 and all the NULL value entries. The second node fetches data where msrp >= 20000 and msrp < 30000. The third node fetches the data where mspr >= 30000.   The following code example describes the parallel data load from CDE to CAS using a metadata table(cdemeta).   ….. proc cas; session mySession; action loadTable / casLib="hive_cde" path="cars" datasourceOptions={numReadNodes=3, metaCatalog="hivesrv1", metaSchema="CASDM", metaTable="cdemeta traceFile="/tmp/cdetrace.log", traceFlags="SQL" } casout={ casLib="hive_cde" , name="cars_cdemeta" } ; run; quit; ….   The default value ‘cdemeta’ is considered when no value is defined for metaTable= parameter in load statement. If metadata table not found in the source environment, load statement fails.   With trace file option in data load statement, you can generate a trace log on each CAS worker nodes. You can look at the trace log to verify the SQL submitted from reader CAS worker node to the Data Agent Server. The following is the extract from the trace file generated from the data load statement.   Extract from /tmp/cdetrace.log at SASCAS01 server: [cloud-user@intcas01 tmp]$ egrep 'Sql=' /tmp/cdetrace.log In: StatementHandle=0x7fe74bddecc0, Sql=`SELECT CATALOG_NAME, SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, HISTOGRAM_ENTRY, HISTOGRAM_BOUNDS, SPLIT FROM "hivesrv1"."CASDM"."cdemeta" WHERE UPCASE(SCHEMA_NAME)=UPCASE('CASDM') AND UPCASE(TABLE_NAME)=UPCASE('cars') ORDER BY SPLIT, HISTOGRAM_ENTRY`, SqlL=245 In: StatementHandle=0x7fe74bddecc0, Sql=`SELECT * FROM "CASDM"."cars" WHERE 0=1`, SqlL=38 Extract from /tmp/cdetrace.log at SASCAS02 server: [cloud-user@intcas02 tmp]$ egrep 'Sql=' /tmp/cdetrace.log In: StatementHandle=0x7f16dc043ec0, Sql=`SELECT * FROM "CASDM"."cars" WHERE ( "msrp" < 20000 OR "msrp" IS NULL ) `, SqlL=73 Extract from /tmp/cdetrace.log at SASCAS03 server: [cloud-user@intcas03 ~]$ egrep 'Sql=' /tmp/cdetrace.log In: StatementHandle=0x7fe4180982e0, Sql=`SELECT * FROM "CASDM"."cars" WHERE ( "msrp" >= 20000 AND msrp < 30000 ) `, SqlL=73 Extract from /tmp/cdetrace.log at SASCAS04 server: [cloud-user@intcas04 ~]$ egrep 'Sql=' /tmp/cdetrace.log In: StatementHandle=0x7fa08814b700, Sql=`SELECT * FROM "CASDM"."cars" WHERE ( "msrp" >= 30000 ) `, SqlL=56   When using metadata for parallel data load make sure to populate cdemeta table with latest histogram data. Choose the split column name based on data cardinality. This is a tedious task to populate the ‘cdemeta’ table, but it’s one of the feature with CDE data connector for parallel data load.   Resources: SAS Data Agent 2.3 for Linux: Deployment Guide SAS Data Agent 2.3 for Linux: Administrator’s Guide SAS Cloud Data Exchange Preview ... View more

Cloud Data Exchange (CDE) is a capability to securely access customer’s on-premise data (behind a firewall) from SAS Viya 3.4 application hosted at a public or private cloud environment. Before a user can access the data through Cloud Data Exchange the Data Agent Administrator must create data services at the Data Agent server. This blog is about creating different types of data services at Data Agent server.   Depending on the data source (databases) the data agent offers various connection parameters for each data service. The pre-requisites and process to create a data service for BASE SAS, Hadoop, Oracle etc. are little different from each other. For understanding purpose, let’s look at the three types of data services, a BASE SAS, a JDBC (Hadoop/Hive) and an ODBC (MSSQL) data service. When adding a new data service you can always use CLI statement “sas-admin dagentsrv services create -help ” to list out all the available option for a data service. To create a supported database source service, corresponding database SAS/ACCESS product license is required at Data Agent Server. BASE SAS data service (In-built): Data Agent Server comes with a BASE SAS data service already configured. To make a set of .sas7bdat data files available under BASE data service, Data Agent Administrator need to add a new catalog and schema with folder location containing data files. The CLI statement is used to create a new catalog and schema at Data Agent Server. The admin user must have read/write access to the data folder and files to access the data from CAS through data service. While creating a schema for BASE SAS data service, the parameter “--primary-path” accept the folder location name without single or double quotes. A catalog can have multiple schemas attached with different folder location.   The following CLI statement describes the creation of a new catalog and schema under BASE SAS data service. The CLI statement also lists the available data tables from the newly created catalog and schema.   Adding a catalog to BASE SAS data service.   [sastest1@sasdagnt01 ~]$ sas-admin dagentsrv services list; Name Type Domain Version Options __SERVER__ server --- 2.3 PURGE_CACHE=30;CACHE=(NAME=AS;TIMEOUT=300) BASE base --- 2.3 --- [sastest1@sasdagnt01 ~]$ sas-admin dagentsrv catalogs create base --name BASECAT [sastest1@sasdagnt01 ~]$ [sastest1@sasdagnt01 ~]$ sas-admin dagentsrv catalogs list Name Service Native Catalog Options BASECAT BASE   Adding multiple Schemas with newly created catalog for BASE SAS data service. Note, the folder location value is not in single or double quotes   [sastest1@sasdagnt01 ~]$ sas-admin dagentsrv schemas create base --catalog BASECAT --name SCHEMA1 --primary-path /data/sasdata/ [sastest1@sasdagnt01 ~]$ [sastest1@sasdagnt01 ~]$ sas-admin dagentsrv schemas create base --catalog BASECAT --name myschema --primary-path /data/mydata/ [sastest1@sasdagnt01 ~]$ sas-admin dagentsrv schemas list Name Catalog Options SCHEMA1 BASECAT PRIMARYPATH=/data/sasdata/ myschema BASECAT PRIMARYPATH=/data/mydata/   Testing BASE SAS data service and listing tables from newly created catalog and schema.   [sastest1@sasdagnt01 ~]$ sas-admin dagentsrv ds test --name BASE Successfully connected to data source BASE. [sastest1@sasdagnt01 ~]$ [sastest1@sasdagnt01 ~]$ sas-admin dagentsrv data-sources tables list --data-source BASE --catalog BASECAT --schema SCHEMA1 Name Type Native Catalog AIRLINE_OPINION TABLE BASECAT CODINGCOMPLAINTS TABLE BASECAT CONSUMER_COMPLAINTS TABLE BASECAT COUNT_DATA TABLE BASECAT … …….. [sastest1@sasdagnt01 ~]$   If a user does not have access to the data files folder, he or she cannot access the data through data service. The CLI statement does not generate any error message when a schema is created using a non-accessible folder. But, the table list CLI statement does not show any object from schema using non-accessible folder. The following example describes that folder /data/mydata is owned by ‘root’ user and no other user have read/write permission to it, hence CLI statement does not show any table from schema pointing to this folder.   [root@sasdagnt01 data]# ls -l total 4 drwx------. 2 root root 42 Oct 26 15:47 mydata drwxr-xr-x. 2 sas sas 4096 Sep 24 23:41 sasdata [root@sasdagnt01 data]# ls -l mydata total 10752 -rwxr-xr-x. 1 root root 11010048 Oct 26 15:47 consumer_complaints.sas7bdat [root@sasdagnt01 data]# [sastest1@sasdagnt01 ~]$ sas-admin dagentsrv data-sources tables list --data-source BASE --catalog BASECAT --schema myschema Name Type Native Catalog [sastest1@sasdagnt01 ~]$ Hadoop/Hive data service (JDBC Connection): To access a Hadoop/Hive data tables through Data Agent Server, an admin user need to add a new data service. Before adding a new hive source service, the Data Agent Server must have compatible Hadoop Jars and Config files staged and /etc/sysconfig/sas/sas-viya-dagentsrv-default configuration file updated with the path of Jars and config files. The access from Data Agent Server to Hadoop/Hive server is based on JDBC connection and JAVA home must be defined with a valid path in the configuration file. The lib/amd64/server must be available under $JAVA_HOME location. The LD_LIBARARY_PATH must include the $JAVA_HOME/lib/amd64/server, $DA_HADOOP_JAR_PATH and $DA_HADOOP_CONFIG_PATH. Once config file updated, Data Agent Server must be restarted to pick up the changes.   Note: If a Hadoop/Hive service is not working it’s due to the incorrect value of $JAVA_HOME or non-compatible Hadoop Jars and Config files are staged at Data Agent Server.   An example of valid entry in /etc/sysconfig/sas/sas-viya-dagentsrv-default config file for Hadoop/Hive service.   export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.171-8.b10.el7_5.x86_64/jre export DA_HADOOP_CONFIG_PATH=/opt/hadoop/HDP/conf export DA_HADOOP_JAR_PATH=/opt/hadoop/HDP/lib:/opt/hadoop/HDP/spark export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$JAVA_HOME/lib/amd64/server:$DA_HADOOP_JAR_PATH:$DA_HADOOP_CONFIG_PATH   With the above configuration in place, an admin user can add a hive data service at Data Agent Server. For seamless access of data from SAS Viya to Hive via Data Agent Server, you must create a shadow security domain at Data Agent Server. Under the security domain, attach one or more identity user with database access credential. The credentials for Hadoop/hive environment attached with a security domain never leaves the on-premise data center and securely stored in a vault at Data Agent Server. When creating the hive data service use the shadow security domain to access hive database. As a part of hive data service creation, it also creates a catalog with the same name at Data Agent Server.   The Following CLI describe the creation of a shadow domain, attaching identity user and database credential with shadow domain, creating Hadoop/Hive data service, testing the Hive data service and listing data table from hive service.   Adding a shadow domain, attaching identity user and database credential with shadow domain.   [sastest1@sasdagnt01 ~]$ sas-admin dagentsrv security domains create --domain HIVE_DOM [sastest1@sasdagnt01 ~]$ [sastest1@sasdagnt01 ~]$ sas-admin dagentsrv security domains list ID Type Label Description HIVE_DOM shadow [sastest1@sasdagnt01 ~]$ [sastest1@sasdagnt01 ~]$ sas-admin dagentsrv security credentials create --domain HIVE_DOM --identity viyademo01 --username gatedemo100 --password xxxxx [sastest1@sasdagnt01 ~]$ [sastest1@sasdagnt01 ~]$ sas-admin dagentsrv security credentials list --domain HIVE_DOM Identity Type UserId ShadowKey viyademo01 user gatedemo100 5E783B9F-128E-3645-9C72-5C00A88461E9 [sastest1@sasdagnt01 ~]$   Adding a new Hadoop/Hive data service and listing data table from hive data service.   [sastest1@sasdagnt01 ~]$ sas-admin dagentsrv services create hive --name hivesrv1 --domain HIVE_DOM --server "sasxxx.race.sas.com" --schema casdm [sastest1@sasdagnt01 ~]$ [sastest1@sasdagnt01 ~]$ sas-admin dagentsrv services list Name Type Domain Version Options __SERVER__ server --- 2.3 PURGE_CACHE=30;CACHE=(NAME=AS;TIMEOUT=300) BASE base --- 2.3 --- hivesrv1 hive HIVE_DOM 2.3 CONOPTS=(DRIVER=HIVE;SCHEMA=casdm;SERVER=sasxxx.race.sas.com); CASE_SENSITIVITY=(OBJECT=F;COLUMN=F) [sastest1@sasdagnt01 ~]$ sas-admin dagentsrv ds test --name hivesrv1 Successfully connected to data source hivesrv1. [sastest1@sasdagnt01 ~]$ [sastest1@sasdagnt01 ~]$ sas-admin dagentsrv data-sources tables list --data-source hivesrv1 --catalog hivesrv1 --schema casdm Name Type Native Catalog cars_hv TABLE heart_hv TABLE hello_world TABLE prdsal3_hv TABLE [sastest1@sasdagnt01 ~]$ MS-SQL Server data service (ODBC Connection): To access an MS SQL Server database tables through the Data Agent Server, an admin user need to add a new data service. The access from Data Agent Server to MS SQL Server is based on ODBC connection. Before adding a new MS-SQL data service, the Data Agent Server must have Unix ODBC client installed and /etc/sysconfig/sas/sas-viya-dagentsrv-default configuration file updated with following ODBC variables. You also must have an entry in odbc.ini for DSN used for data service. Once config file updated, Data Agent Server must be restarted to pick up the changes.   An example of valid entry in /etc/sysconfig/sas/sas-viya-dagentsrv-default config file for ODBC based MSSQL service.   export ODBCHOME=/opt/sas/viya/home/lib64/accessclients export ODBCSYSINI=/opt/sas/odbc export ODBCINI=/opt/sas/odbc/odbc.ini export ODBCINST=/opt/sas/odbc/odbcinst.ini export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib64:/opt/sas/viya/home/lib64   An entry in /opt/sas/odbc/odbc.ini for DSN sqls_CASDM   … ……….. [sqls_CASDM] Driver=/opt/sas/viya/home/lib64/accessclients/lib/S0sqls27.so Description=SAS Institute, Inc 7.1 SQL Server Wire Protocol AlternateServers= AlwaysReportTriggerResults=0 AnsiNPW=1 ApplicationName= …. ………..   With the above configuration in place, an admin user can add an MSSQL data service at Data Agent Server. The process to add an MSSQL data service is same as described in Hadoop/Hive data service section. While creating an MSSQL Server data service you can use “--register-all” or “--native-catalogs” option to register all or selective native catalogs from the source database to Data Agent Server.   The Following CLI statement describes the creation of a shadow domain, attaching identity user and database credential with shadow domain, creating MSSQL data service, testing the MSSQL data service, and listing data table from MSSQL data service.   Adding a shadow domain, attaching identity user and database credential with shadow domain.   [sastest1@sasdagnt01 ~]$ sas-admin dagentsrv security domains create --domain MSSQL_DOM [sastest1@sasdagnt01 ~]$ [sastest1@sasdagnt01 ~]$ sas-admin dagentsrv security domains list ID Type Label Description MSSQL_DOM shadow [sastest1@sasdagnt01 ~]$ [sastest1@sasdagnt01 ~]$ sas-admin dagentsrv security credentials create --domain MSSQL_DOM --identity viyademo01 --username casdm --password 'xxxxxxx' [sastest1@sasdagnt01 ~]$ [sastest1@sasdagnt01 ~]$ sas-admin dagentsrv security credentials list --domain MSSQL_DOM Identity Type UserId ShadowKey viyademo01 user casdm B038225C-A05F-574B-BDD1-89451008A810 [sastest1@sasdagnt01 ~]$   Adding a new MSSQL data source service using DNS name from odbc.ini file and listing data table from MSSQL data source service.   [sastest1@sasdagnt01 ~]$ sas-admin dagentsrv services create sqlserver --name sqlsrv1 --domain MSSQL_DOM --database-name "casdm" --dsn sqls_CASDM [sastest1@sasdagnt01 ~]$ [sastest1@sasdagnt01 ~]$ sas-admin dagentsrv ds test --name sqlsrv1 Successfully connected to data source sqlsrv1. [sastest1@sasdagnt01 ~]$ [sastest1@sasdagnt01 ~]$ sas-admin dagentsrv services list Name Type Domain Version Options __SERVER__ server --- 2.3 PURGE_CACHE=30;CACHE=(NAME=AS;TIMEOUT=300) BASE base --- 2.3 --- sqlsrv1 sqlserver MSSQL_DOM 2.3 CONOPTS=(DRIVER=MSSQLSVR;DATABASE=casdm;ODBC_DSN=sqls_CASDM); CASE_SENSITIVITY=(OBJECT=F;COLUMN=F) [sastest1@sasdagnt01 ~]$ sas-admin dagentsrv data-sources tables list --data-source sqlsrv1 --catalog casdm --schema dbo Name Type Native Catalog hello_world TABLE casdm [sastest1@sasdagnt01 ~]$   Resources: SAS Data Agent 2.3 for Linux: Deployment Guide SAS Data Agent 2.3 for Linux: Administrator’s Guide SAS Cloud Data Exchange Preview ... View more

In Part 1 of this article series, I talked about mounting an S3 bucket to CAS and SAS servers as a Network File System. Mounting an AWS S3 bucket as a filesystem means you can use all your existing tools to interact with S3 bucket to perform read and write operations on files and folders. This article is about another method “Objective-FS” to mount S3 as NFS. With Objective-FS software installed on to CAS and SAS server you can load data files from S3 bucket to CAS, save a CAS table to S3 bucket, and access S3 data files in base SAS. Objective FS: Objective-FS is a shared distributed POSIX file system that provides persistent data storage among the cloud instance, local machine and on-premise servers. The Objective-FS software runs on the on-premise server and talks directly to the cloud object store using S3 API. It supports multiple writers and readers; multiple nodes can write to the same file system concurrently and see each other’s data. The speed and performance of filesystem are maintained by using workload heuristics such as write bundling and read caching. The Object-FS uses cloud secure end-to-end client crypto to read and write data to S3. The data is encrypted at rest and in motion. The data is encrypted locally with key (known to a user only) before it leaves the on-premise data server.   The software cost!! The price for the professional version software is $49/- per month. The price for the enterprise version software is $299/- per month. For more detail please visit vendor’s product price page.   The following steps describe how to install, configure and use the Objective-FS at SAS and CAS server to access the S3 bucket: Create an account at Obectivefs.com. To use the Object-FS software you need to have an active account at objectivefs.com. The user profile contains the product license and authorization key, which is required during objective-FS server configuration at SAS and CAS server. The download page contains the link to download the required software for the respective OS. The following screenshots describe the Objective-FS license and download page.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.     Install Objective-FS software on CAS and SAS servers. Once you have downloaded the required software for the respective OS, sftp it to the desired server for software installation. The software installation must be executed as user ‘root’ or user with ‘sudo’ access. The following step describes the objective-FS software installation at CAS and SAS server. You also need to verify hosting server NTP for small offset.   ## At CAS Server [root@intcas01 ~]# ls -l obj* -rw-r--r--. 1 root root 240984 Apr 3 15:48 objectivefs-6.1-1.x86_64.rpm [root@intcas01 ~]# [root@intcas01 ~]# yum install objectivefs-6.1-1.x86_64.rpm Examining objectivefs-6.1-1.x86_64.rpm: objectivefs-6.1-1.x86_64 Marking objectivefs-6.1-1.x86_64.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package objectivefs.x86_64 0:6.1-1 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================================================================================= Package Arch Version Repository Size ================================================================================================================================================= Installing: objectivefs x86_64 6.1-1 /objectivefs-6.1-1.x86_64 507 k Transaction Summary ================================================================================================================================================= Install 1 Package Total size: 507 k Installed size: 507 k Is this ok [y/d/N]: y Downloading packages: Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : objectivefs-6.1-1.x86_64 1/1 Verifying : objectivefs-6.1-1.x86_64 1/1 Installed: objectivefs.x86_64 0:6.1-1 Complete! [root@intcas01 ~]# [root@intcas01 ~]# /usr/sbin/ntpdate -q pool.ntp.org server 198.60.22.240, stratum 0, offset 0.000000, delay 0.00000 server 4.53.160.75, stratum 0, offset 0.000000, delay 0.00000 server 104.168.88.15, stratum 0, offset 0.000000, delay 0.00000 server 69.164.213.136, stratum 0, offset 0.000000, delay 0.00000 3 Apr 16:06:17 ntpdate[20764]: no server suitable for synchronization found [root@intcas01 ~]#   ## At SAS/Viya Server [root@intviya01 ~]# ls -l obj* -rw-r--r--. 1 root root 240984 Apr 3 15:57 objectivefs-6.1-1.x86_64.rpm [root@intviya01 ~]# [root@intviya01 ~]# yum install objectivefs-6.1-1.x86_64.rpm Examining objectivefs-6.1-1.x86_64.rpm: objectivefs-6.1-1.x86_64 Marking objectivefs-6.1-1.x86_64.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package objectivefs.x86_64 0:6.1-1 will be installed --> Finished Dependency Resolution ….. …………… Dependencies Resolved ================================================================================================================================================= Package Arch Version Repository Size ================================================================================================================================================= Installing: objectivefs x86_64 6.1-1 /objectivefs-6.1-1.x86_64 507 k Transaction Summary ================================================================================================================================================= Install 1 Package Total size: 507 k Installed size: 507 k Is this ok [y/d/N]: y Downloading packages: Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : objectivefs-6.1-1.x86_64 1/1 Verifying : objectivefs-6.1-1.x86_64 1/1 Installed: objectivefs.x86_64 0:6.1-1 Complete! [root@intviya01 ~]# [root@intviya01 ~]# /usr/sbin/ntpdate -q pool.ntp.org server 69.164.213.136, stratum 0, offset 0.000000, delay 0.00000 server 4.53.160.75, stratum 0, offset 0.000000, delay 0.00000 server 198.60.22.240, stratum 0, offset 0.000000, delay 0.00000 server 104.168.88.15, stratum 0, offset 0.000000, delay 0.00000 3 Apr 16:06:04 ntpdate[9945]: no server suitable for synchronization found [root@intviya01 ~]#   Configure the Objective-FS server credentials to access S3 bucket from CAS and SAS servers. To configure objective-FS server a valid AWS_ACCESS_KEY_ID and AWS_SECRET_ACECSS_KEY are required from AWS account. The ACCESS_KEY and SECRET_KEY are used for accessing S3 location. The process to get the ACCESS_KEY and SECRET_KEY is explained in a previous post. The following step describes the S3 access credential configuration for Objective-FS server on CAS and SAS server.   ## On CAS Server [root@intcas01 ~]# mount.objectivefs config Creating config in /etc/objectivefs.env Enter ObjectiveFS license: XXXXXXXXXXXXXXXXXXXXXXXX Enter Access Key Id: XXXXXXXXXXXXXXXXXXX Enter Secret Access Key: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX Enter Default Region (optional): us-east-1 Creating: /etc/objectivefs.env /etc/objectivefs.env/OBJECTIVEFS_LICENSE /etc/objectivefs.env/AWS_ACCESS_KEY_ID /etc/objectivefs.env/AWS_SECRET_ACCESS_KEY ObjectiveFS was successfully configured. [root@intcas01 ~]#   ## On SAS/Viya Server [root@intviya01 ~]# mount.objectivefs config Creating config in /etc/objectivefs.env Enter ObjectiveFS license: XXXXXXXXXXXXXXXXXXXXXXXX Enter Access Key Id: XXXXXXXXXXXXXXXXXXX Enter Secret Access Key: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX Enter Default Region (optional): us-east-1 Creating: /etc/objectivefs.env /etc/objectivefs.env/OBJECTIVEFS_LICENSE /etc/objectivefs.env/AWS_ACCESS_KEY_ID /etc/objectivefs.env/AWS_SECRET_ACCESS_KEY ObjectiveFS was successfully configured. [root@intviya01 ~]#   Create an objective-FS filesystem/bucket in the default S3 location region. The objective-FS creates its own S3 bucket to store multiple fragmented data files. You cannot use an existing bucket with Objective-FS environment. The passphrase created at this step is used when mounting a local folder to S3 location. Please keep the passphrase in a safe and secure place, without passphrase you can not access the files from S3 bucket created in this step. All files and objects at S3 are distributed and encrypted by Objective-FS. There will be multiple encrypted files for one data file.   [root@intcas01 ~]# mount.objectivefs create -l us-east-1 s3://gelsasfs Passphrase (for s3://gelsasfs): Verify passphrase (for s3://gelsasfs): NOTE: 3 PUT, 2 LIST, 0 GET, 0 DELETE, 2.491 kB IN, 2.696 kB OUT, CACHE [0.0% HIT, 0.0% META, 0.0% DATA, 704.0 B OS], DISK [0.0% HIT] NOTE: Successfully created filesystem: s3://gelsasfs in the us-east-1 region [root@intcas01 ~]#   The bucket created from the previous steps can be viewed at the S3 user interface. Notice the files created under this bucket is encrypted and can only be read and accessed by objective-FS mounting point.       Mount a local folder at CAS and SAS server to Objective-FS file system/bucket. To mount the objective-FS file system, you need an existing empty local folder. The Objective-FS process will run in background for the mounting point. When mounting the filesystem it will ask the passphrase created from previous (mount.objectivefs create) step.   ## On CAS server [root@intcas01 ~]# mkdir /opt/sas/s3objfsmnt [root@intcas01 ~]# chown sas:sas /opt/sas/s3objfsmnt [root@intcas01 ~]# chmod 777 /opt/sas/s3objfsmnt [root@intcas01 ~]# mount.objectivefs gelsasfs /opt/sas/s3objfsmnt Passphrase (for s3://gelsasfs): [ XXXXXXXXX ](Passphrase created from mount.objectivefs create step) [root@intcas01 ~]#   ## On SAS / Viya Server [root@intviya01 ~]# mkdir /opt/sas/s3objfsmnt [root@intviya01 ~]# chown sas:sas /opt/sas/s3objfsmnt [root@intviya01 ~]# chmod 777 /opt/sas/s3objfsmnt [root@intviya01 ~]# mount.objectivefs gelsasfs /opt/sas/s3objfsmnt Passphrase (for s3://gelsasfs): [ XXXXXXXXX] (Passphrase created from mount.objectivefs create step) [root@intviya01 ~]#   Verify the Objective-FS mounted filesystem at CAS and SAS/Viya server. The following step describes the verification of objective-FS mounting point, data file copy from local folder to S3 mounting point and data files accessible from both CAS and SAS servers.   ## At CAS Server ## list objective-FS mounting to S3 bucket [root@intcas01 ~]# mount.objectivefs list NAME KIND SNAP REGION LOCATION s3://gelsasfs ofs on us-east-1 US East (N. Virginia) ## list files from mounting folder [root@intcas01 ~]# ls -l /opt/sas/s3objfsmnt total 1 -rw-rw-rw-. 1 root root 605 Apr 4 11:57 README [root@intcas01 ~]# ## Copy data files to Objective-FS file system/fodler. [root@intcas01 ~]# cp /opt/sas/data/customers.sas7bdat /opt/sas/s3objfsmnt/ [root@intcas01 ~]# cp /opt/sas/data/prdsale.sas7bdat /opt/sas/s3objfsmnt/ ## list files from mounting folder [root@intcas01 ~]# ls -l /opt/sas/s3objfsmnt total 1153 -rw-r--r--. 1 root root 917504 Apr 4 12:10 customers.sas7bdat -rw-r--r--. 1 root root 262144 Apr 4 12:10 prdsale.sas7bdat -rw-rw-rw-. 1 root root 605 Apr 4 11:57 README [root@intcas01 ~]# [root@intcas01 ~]# df -k Filesystem 1K-blocks Used Available Use% Mounted on /dev/mapper/rhel-root 200671504 106605168 94066336 54% / devtmpfs 16380036 0 16380036 0% /dev tmpfs 16391040 76 16390964 1% /dev/shm tmpfs 16391040 1647520 14743520 11% /run tmpfs 16391040 0 16391040 0% s3://gelsasfs 1125899906842624 312 1125899906842312 1% /opt/sas/s3objfsmnt [root@intcas01 ~]#   ## At SAS / Viya Server ## list objective-FS mounting to S3 bucket [root@intviya01 ~]$ sudo mount.objectivefs list NAME KIND SNAP REGION LOCATION s3://gelsasfs ofs on us-east-1 US East (N. Virginia) ## list files from mounting folder [root@intviya01 ~]# ls -l /opt/sas/s3objfsmnt total 1153 -rw-r--r--. 1 root root 917504 Apr 4 12:10 customers.sas7bdat -rw-r--r--. 1 root root 262144 Apr 4 12:10 prdsale.sas7bdat -rw-rw-rw-. 1 root root 605 Apr 4 11:57 README [root@intviya01 ~]# [root@intviya01 ~]# df -k Filesystem 1K-blocks Used Available Use% Mounted on /dev/mapper/rhel-root 200671504 74814312 125857192 38% / devtmpfs 49407664 0 49407664 0% /dev tmpfs 49418664 24 49418640 1% /dev/shm tmpfs 49418664 4155988 45262676 9% /run tmpfs 49418664 0 49418664 0% /sys/fs/cgroup /dev/sda1 508588 221964 286624 44% /boot s3://gelsasfs 1125899906842624 312 1125899906842312 1% /opt/sas/s3objfsmnt [root@intviya01 ~]#   The data files at the S3 location are fragmented and encrypted. It can only be read via the objective-FS filesystem.     Objective-FS Process running on OS. There is an OS process to support the access of S3 bucket from Objective-FS. After OS reboot, if this process is down, you need to mount the filesystem again using the previous step.   [root@intcas01 ~]# ps -eaf | grep objective root 21862 1 2 12:03 ? 00:03:19 mount.objectivefs gelsasfs /opt/sas/s3objfsmnt root 27614 12143 0 14:21 pts/1 00:00:00 grep --color=auto objective [root@intcas01 ~]#   Objective-FS S3 Bucket data file access from BASE SAS. After mounting objective-FS S3 bucket to SAS compute server, the data files can be accessed from BASE SAS using LIBNAME and FILENAME statement. The following example describes the S3 bucket data file access from base SAS using a LIBNAME statement.   libname mylib "/opt/sas/s3objfsmnt " ; proc print data=mylib.customers ; run;     Objective-FS S3 Bucket data file access from CAS. After mounting objective-FS S3 bucket to CAS Controller, the data files can be accessed using path-based CASLIB. The following code example describes the data load to CAS from an S3 bucket and data save from CAS to an S3 bucket via objective-FS route. Other SAS application can also use the newly saved .sas7bdat and .csv files via objective-FS mount.   CAS mySession SESSOPTS=( CASLIB=casuser TIMEOUT=99 LOCALE="en_US"); caslib caslibs3 datasource=(srctype="path") path="/opt/sas/s3objfsmnt" ; /* load a S3 data file to CAS */ PROC CASUTIL incaslib="caslibs3" outcaslib="caslibs3"; droptable casdata="prdsale" quiet; LOAD casdata="prdsale.sas7bdat" CASOUT="prdsale" copies=0 importoptions=(filetype="basesas", dtm="auto", debug="dmsglvli"); RUN; quit; /* Save a CAS table to S3 with .sashdat extension */ proc casutil incaslib="caslibs3" outcaslib="caslibs3"; save casdata="prdsale" casout= "prdsale_new" replace ; run; quit; /* Save a CAS table to S3 with .sas7bdat extension */ proc casutil incaslib="caslibs3" outcaslib="caslibs3"; save casdata="prdsale" casout= "prdsale_new.sas7bdat" replace ; run; quit; /* Save a CAS table to S3 with .csv extension */ proc casutil incaslib="caslibs3" outcaslib="caslibs3"; save casdata="prdsale" casout= "prdsale_new.csv" replace ; run; quit; /* load a .sashdat file from S3 to CAS */ proc casutil incaslib="caslibs3" outcaslib="caslibs3"; droptable casdata="prdsale_new_hdat" quiet; load casdata="prdsale_new.sashdat" casout="prdsale_new_hdat" ; run; quit; /* load a .csv file from S3 to CAS */ proc casutil incaslib="caslibs3" outcaslib="caslibs3"; droptable casdata="prdsale_new_csv" quiet; load casdata="prdsale_new.csv" casout="prdsale_new_csv" ; run; quit; proc casutil; list tables incaslib="caslibs3 "; list files incaslib="caslibs3 "; run; /* Shutdown CAS Session */ CAS mySession TERMINATE;   Result extracts from the above code execution.       The newly saved data files from CAS to S3 are also accessible from BASE SAS.     Data load Performance from S3 bucket to CAS using objective-FS: The data load to CAS from S3 bucket using objective-FS S3 filesystem is slow compared to data load from the local filesystem. The parallel load using objective-FS S3 mounted file system is not supported so the load time is listed using serial mode. S3 Type CAS lib only support SASHDAT and CSV file format, so for better comparison following test was conducted using a .sashdat file.   Note: The SAS Viya 3.5 is coming with improved performance when loading data from S3 to CAS using S3 type CASLIB.   Summary: The Objective-FS filesystem enables you to access the S3 bucket data file from CAS and SAS servers. The data files are encrypted in transit and at rest. The data file stored at the S3 location using objective-FS can read by only objective-FS mounting. Important link: Objective-FS ... View more

In Part 1 of this article, I talked about mounting an S3 bucket to CAS and SAS servers as a Network File System. Mounting an AWS S3 bucket as a filesystem means you can use all your existing tools to interact with S3 bucket to perform read and write operations on files and folders. This article describes another method to mount S3 as NFS, offered by Amazon called “AWS Storage Gateway.” With storage gateway server mounted to CAS and SAS server you can load data files from S3 bucket to CAS, save a CAS table to S3 bucket, and access S3 data files in base SAS.   AWS Storage Gateway AWS Storage Gateway is a hybrid storage service that enables your on-premises applications to seamlessly use AWS cloud storage. You can use the service for backup, cloud data processing, storage tiering, disaster recovery etc. Your applications connect to the service through a virtual machine or hardware gateway appliance using standard storage protocols, such as NFS, SMB and iSCSI. The gateway connects to AWS storage services, such as Amazon S3, Amazon Glacier, Amazon EBS, and AWS Backup, providing storage for files, volumes, snapshots, and virtual tapes in AWS. The service includes a highly-optimized data transfer mechanism, along with a local cache for low-latency on-premises access to S3 bucket.   The AWS Storage Gateways service is a chargeable service. You can host the Storage Gateways service at the on-premise server or you can use EC2 server. If you use EC2 server to host, AWS will charge for EC2 Server, Disk space (min 170 GB) and Network traffic from S3 bucket to Gateway server to on-premise server.   The following steps describe how to start an AWS Storage Gateway, configure an AWS file share with S3 bucket, and mount to SAS and CAS server to access the S3 bucket. Create an AWS Storage Gateway.   The AWS Storage Gateway instance can be created from the AWS console with required (admin) permission. On AWS services tab under Storage section look for “Storage Gateway”. On selection of “Storage Gateway”, it will take you to “get started” page, and then to "create a Gateway type". Select “File Gateway” and move onto the next step where you an option to select a host platform. You can download the VMware for listed host platform to host the services at the on-premise server or you can choose to host on an AWS EC2 server. When you select the EC2 server, you can lunch the EC2 instance from the predefined image provided by Amazon. Once EC2 server is up and running come back to the same page with Ip address of EC2 server for the next step.   Following screenshots describe the creation of an AWS Storage Gateway. Select the AWS service Storage Gateway.     Select the gateway type as File Gateway.     Select the host platform as “Amazon EC2” and launch the instance.     Starting an EC2 Instance Type   The Storage Gateways services must be hosted at a high-end server. The minimum requirement is a general purpose M4 X Large type EC2 server with an additional 150 GB disk space (SSD gp2) for the cache. The EC2 instance must be started from the Gateway service window itself to start with a predefined Image. You can keep the default network and subnet selection or select network as per your location. Create a network security group which should allow traffic from your web browser and from your corporate domain to the Gateway EC2 server. You can configure the security as per your policy. Once the instance is up and running, note down the Public Ip and return to the create AWS Storage Gateway window.   AWS requirement for Storage Gateway   The following screen shots describe the starting of an EC2 server with required component. This is not the complete steps but the steps where you need to be pay attention.           Provide the IP address collected form EC2 instance started in the previous step at the Connect Gateway window to connect to Gateway.     Activate the gateway with a name (e.g. utkuma)     Save the Gateway with disk space used for data file cache.       Create an AWS File Share.   Once an AWS Storage Gateway service is up and running configure an AWS file share with S3 bucket having data files. An AWS File share points to an S3 bucket and object from bucket can be accessed using NFS or SMB method. The AWS File share support NFS mount statement for the different operating system (Linux, MacOS, and Win ). The following step describes the configuration of an AWS File share using an S3 bucket and AWS Gateway. Configure an AWS file share using S3 bucket and AWS Gateways.       During AWS File share review step add the IP address of client (CAS and SAS) servers from where you are going to access the data file.     Once the AWS File Share is available, use the NFS mount statement to access S3 data files at CAS and SAS server.     Mount an AWS File share to CAS and SAS server. Create a folder (e.g. /opt/sas/s3nfsmnt ) to mount an AWS File share consists of an S3 location.   $ mkdir /opt/sas/s3nfsmnt $ chown sas:sas /opt/sas/s3nfsmnt $ chmod 755 /opt/sas/s3nfsmnt   Mount the File Share S3 bucket with CAS/SAS local folder as a filesystem by using statement provided at AWS File Share window.   $ mount -t nfs -o nolock,hard 172.31.91.0:/gelsas /opt/sas/s3nfsmnt   List the files from S3 bucket at CAS and SAS Server.   [ec2-user@ip-172-31-32-37 s3nfsmnt]$ pwd /opt/sas/s3nfsmnt [ec2-user@ip-172-31-32-37 s3nfsmnt]$ ls -l total 4979 -rw-rw-rw-. 1 nfsnobody nfsnobody 968368 Feb 1 15:58 order_fact.sas5bdat -rw-rw-rw-. 1 nfsnobody nfsnobody 917504 Feb 1 15:58 customers.sas7bdat -rw-rw-rw-. 1 nfsnobody nfsnobody 262144 Feb 1 15:58 prdsale.sas7bdat [ec2-user@ip-172-31-32-37 s3nfsmnt]$   S3 Bucket data file access from BASE SAS.   After mounting S3 bucket to SAS compute server, the data files can be accessed from BASE SAS using LIBNAME and FILENAME statement. The following example describes the S3 bucket data file access from base SAS using a LIBNAME statement.   libname mylib "/opt/sas/s3nfsmnt" ; proc print data=mylib.customers ; run;     S3 Bucket data file access from CAS.   After mounting S3 bucket to CAS Controller, the data files can be accessed using path-based CASLIB. The following code example describes the data load to CAS from an S3 bucket and data save from CAS to an S3 bucket via AWS Storage Gateway server. The newly saved object/files at S3 bucket can be accessed using AWS UI and CLI. Other SAS application can also use the newly saved .sas7bdat and .csv files.   CAS mySession SESSOPTS=( CASLIB=casuser TIMEOUT=99 LOCALE="en_US"); caslib caslibs3 datasource=(srctype="path") path="/opt/sas/s3nfsmnt" ; /* load a S3 data file to CAS */ PROC CASUTIL incaslib="caslibs3" outcaslib="caslibs3"; droptable casdata="prdsale" quiet; LOAD casdata="prdsale.sas7bdat" CASOUT="prdsale" copies=0 importoptions=(filetype="basesas", dtm="auto", debug="dmsglvli"); RUN; quit; /* Save a CAS table to S3 with .sashdat extension */ proc casutil incaslib="caslibs3" outcaslib="caslibs3"; save casdata="prdsale" casout= "prdsale_new" replace ; run; quit; /* Save a CAS table to S3 with .sas7bdat extension */ proc casutil incaslib="caslibs3" outcaslib="caslibs3"; save casdata="prdsale" casout= "prdsale_new.sas7bdat" replace ; run; quit; /* Save a CAS table to S3 with .csv extension */ proc casutil incaslib="caslibs3" outcaslib="caslibs3"; save casdata="prdsale" casout= "prdsale_new.csv" replace ; run; quit; /* load a .sashdat file from S3 to CAS */ proc casutil incaslib="caslibs3" outcaslib="caslibs3"; droptable casdata="prdsale_new_hdat" quiet; load casdata="prdsale_new.sashdat" casout="prdsale_new_hdat" ; run; quit; /* load a .csv file from S3 to CAS */ proc casutil incaslib="caslibs3" outcaslib="caslibs3"; droptable casdata="prdsale_new_csv" quiet; load casdata="prdsale_new.csv" casout="prdsale_new_csv" ; run; quit; proc casutil; list tables incaslib="caslibs3 "; list files incaslib="caslibs3 "; run; /* Shutdown CAS Session */ CAS mySession TERMINATE;   Result extracts from the above code execution.         The newly saved data files from CAS to S3 are also accessible from BASE SAS.     Summary The AWS Storage Gateway enables you to access the S3 bucket data file from CAS and SAS servers. The NFS mount from CAS and SAS server to Storage Gateway server is a hard mount. The Storage Gateway server uses cache area to download the S3 data files to improve the latency. You can host the AWS Storage Gateway service at on-premise server or at high-end EC2 server. Important link: Creating an AWS Gateway and File Share ... View more

The Amazon (S3) Simple Storage Service is an object storage platform with a simple web service interface to store and retrieve any amount of data. You can store various formatted data files into Amazon S3 storage. However, CAS and SAS have limited capability to read it. With SAS Viya 3.4, CAS can read and write only SASHDAT and CSV formatted data files to S3 bucket using an S3 type CASLIB. The base SAS can manage the S3 bucket using PROC S3 with operations like creating a bucket, folder, and uploading/downloading data files. You cannot have a FILENAME or LIBNAME statement against an S3 bucket to read and write the data files.   Consider a situation where you want to store a SAS data set file (.sas7bdat) at S3 bucket and access it from both base SAS and CAS process. Currently, there is no direct method to access a .sas7bdat file stored at S3. You have to download the .sas7bdat files from S3 bucket to local servers before using with SAS or CAS. What if you had an option to mount the S3 bucket as a remote filesystem to read and write the data files from both CAS and SAS process. Mounting an AWS S3 bucket as a filesystem means you can use all your existing tools to interact with S3 bucket to perform read and write operations on files and folders.   This post is about mounting the S3 bucket as NFS filesystem to CAS and SAS (Viya client) server. A base SAS process can save a .sas7bdat data file to NFS mounted S3 bucket and a CAS session can load it without physically copying to CAS server. Similarly, a CAS session can save a .sas7bdat data file to NFS mounted S3 bucket and a base SAS process can access it without downloading to SAS server.   There are (few listed) software/technology which can enable you to mount the S3 bucket as NFS filesystem to UNIX/Win server (SAS and CAS server). Using S3FS Using AWS Storage Gateway Using ObjectiveFS This post discusses methods using S3FS to mount an S3 bucket as a remote NFS filesystem. A subsequent article will cover the other two methods.   S3FS: s3fs is a FUSE filesystem that allows you to mount an Amazon S3 bucket as a local filesystem. It stores files natively and transparently in S3 so that other programs can access the same files. The maximum size of objects that s3fs can handle depends on Amazon S3. For example, up to 5 GB when using single PUT API, and up to 5 TB is supported when Multipart Upload API is used. The s3fs is a stable software and is being used in a number of production environments, e.g., rsync backup to s3. S3FS allows Linux and MacOS to mount an S3 bucket as NFS file system via FUSE. It’s compatible with AWS S3, Google Cloud Storage and other S3 based object stores.   The following steps describe how to install and configure the s3fs software at SAS and CAS server and access the S3 bucket with data files. Install S3FS software. It’s free software and available via EPEL repository (pre-build package) at RHEL and CentOS. The YUM statement can be used at Unix server (SAS and CAS) to install the software.   [root@intviya01 ~]# yum repolist repo id repo name status !epel/x86_64 Extra Packages for Enterprise Linux 7 - x86_64 12,914 [root@intviya01 ~]# yum install -y s3fs-fuse … ….…. ……………. ================================================================================================================================== Package Arch Version Repository Size ================================================================================================================================== Installing: s3fs-fuse x86_64 1.84-3.el7 epel 240 k Transaction Summary ================================================================================================================================== Install 1 Package … ……. ………… Installed: s3fs-fuse.x86_64 0:1.84-3.el7 Complete! [root@intviya01 ~]#   Obtain an ACCESS_KEY_ID and SECRET_ACCESS_KEY. To access an S3 Bucket you need an IAM (Identity and Access Management) based user ACCESS_KEY_ID and SECRET_ACCESS_KEY. The secret key is used by s3fs to connect to the S3 bucket and mount an NFS drive. By using the standard method at AWS console you can obtain the ACCESSKEY_ID and SECRET_KEY. Following pics describe the creation of ACCESS_KEY_ID and SECRET_ACCESS_KEY.   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.   Locate and identify an S3 bucket with read/write permission. Identify (create) an S3 bucket where you can store (read/write) the data file using ACCESS_KEY.     Create an s3fs password file at Unix server (CAS/SAS) to mount the S3 bucket. The default location for the s3fs password (.passwd-s3fs) file can be ${HOME}/.passwd-s3fs or you can have password at /etc/passwd-s3fs. The password file must have chmod 600.   $ echo XXXXXXXXXDHPGXHFMDGA:XXXXXXXXXoK9mLS0BrB9roLV7oqRVkXXXXXXXXXX > ${HOME}/.passwd-s3fs $ chmod 600 ${HOME}/.passwd-s3fs   Create a folder (e.g. under /opt/sas/) to mount FS to S3 location.   $ mkdir /opt/sas/s3mnt $ chown sas:sas /opt/sas/s3mnt $ chmod 755 /opt/sas/s3mnt   Allow other users to access the mounted S3 filesystem.   $ echo user_allow_other >> /etc/fuse.conf   Mount the S3 bucket with the folder as a filesystem. The following mount statement includes password file, uid, gid, and umask which provide the metadata for S3 object at Unix machine. By default, without specific uid and gid in mount statement, the S3 files will be shown at Unix as owned by root. Note: In my test environemnt uid=2000 and gid=2000 is for user 'sas'. Note: To mount an out of AWS US region S3 bucket, it requires additional parameters as endpoint= and url=.   To mount a file system.   $ s3fs gelsas /opt/sas/s3mnt -o passwd_file=${HOME}/.passwd-s3fs -o allow_other,uid=2000,gid=2000,umask=000   To mount an out of AWS-US region S3 bucket, it require additional parameters as endpoint= and url=.   $ s3fs gelsas /opt/sas/s3mnt -o passwd_file=${HOME}/.passwd-s3fs -o endpoint=ap-southeast-2 -o url="https://s3-ap-southeast-2.amazonaws.com" -o allow_other,uid=2000,gid=2000,umask=000 -o multireq_max=5   The following statement can be used to unmount a file system.   $ fusermount -u /opt/sas/s3mnt   The following statement can be used in “/etc/fstab” file to mount the S3 bucket during OS boot.   s3fs#gelsas /opt/sas/s3mnt fuse _netdev,allow_other,umask=000,uid=2000,gid=2000 0 0   List the files at Unix from the S3 bucket. Since uid, gid, and umask option used in mount statement, the data files listed from S3 bucket is owned by a specific user (sas) with read/write permission to others.   [root@intviya01 ~]# df -k /opt/sas/s3mnt Filesystem 1K-blocks Used Available Use% Mounted on s3fs 274877906944 0 274877906944 0% /opt/sas/s3mnt [root@intviya01 ~]# ls -l /opt/sas/s3mnt total 1153 -rwxrwxrwx. 1 sas sas 917504 Feb 12 11:50 customers.sas7bdat -rwxrwxrwx. 1 sas sas 262144 Feb 12 11:49 prdsale.sas7bdat [root@intviya01 ~]#   S3FS mount process/service at OS. A process/service runs at OS to support the access and object availability from S3 bucket.   [root@intviya01 sas]# ps -eaf | grep s3fs root 22565 1 0 11:53 ? 00:00:00 s3fs gelsas /opt/sas/s3mnt -o passwd_file=/root/.passwd-s3fs -o allow_other root 29258 6826 0 11:56 pts/1 00:00:00 grep --color=auto s3fs [root@intviya01 sas]#   Copy a file to NFS mounted folder at Unix. Once you have access to the S3 bucket via a filesystem, you can copy a data file to S3 bucket using Unix command. The copied file gets stored at S3 bucket and is accessible from AWS UI and CLI.   [root@intviya01 s3mnt]# pwd /opt/sas/s3mnt [root@intviya01 s3mnt]# ls -l total 1158 -r-xr-x---. 1 sas sas 917504 Feb 12 11:50 customers.sas7bdat -r-xr-x---. 1 sas sas 262144 Feb 12 11:49 prdsale.sas7bdat [root@intviya01 s3mnt]# cp /gelcontent/demo/DM/data/order_fact.sas7bdat . [root@intviya01 s3mnt]# ls -l total 146627 -r-xr-x---. 1 sas sas 917504 Feb 12 11:50 customers.sas7bdat -r-xr-x---. 1 sas sas 148964352 Feb 12 15:34 order_fact.sas7bdat -r-xr-x---. 1 sas sas 262144 Feb 12 11:49 prdsale.sas7bdat [root@intviya01 s3mnt]#     S3 Bucket data file access from BASE SAS. After mounting S3 bucket to SAS compute server, the data files can be accessed from BASE SAS using LIBNAME and FILENAME statement. The following example describes the S3 bucket data file access from base SAS using a LIBNAME statement.   libname mylib "/opt/sas/s3mnt" ; proc print data=mylib.customers ; run;     The following example describes the S3 bucket data file access from base SAS using FILENAME statement.   filename mydata "/opt/sas/s3mnt/dept.txt"; DATA _NULL_; INFILE mydata ; INPUT; LIST; RUN;     S3 Bucket data file access from CAS. After mounting S3 bucket to CAS Controller, the data files can be accessed using path-based CASLIB. The following example describes the data load to CAS from an S3 bucket and data save from CAS to an S3 bucket. The new object/files saved at S3 bucket can be accessed using AWS UI and CLI. Other SAS application can also use the newly saved .sas7bdat file.   CAS mySession SESSOPTS=( CASLIB=casuser TIMEOUT=99 LOCALE="en_US"); caslib caslibs3 datasource=(srctype="path") path="/opt/sas/s3mnt" ; /* load a S3 data file to CAS */ PROC CASUTIL incaslib="caslibs3" outcaslib="caslibs3"; droptable casdata="customers" quiet; LOAD casdata="customers.sas7bdat" CASOUT="customers" copies=0 importoptions=(filetype="basesas", dtm="auto", debug="dmsglvli"); RUN; quit; /* Save a CAS table to S3 with .sashdat extension */ proc casutil incaslib="caslibs3" outcaslib="caslibs3"; save casdata="customers" casout= "customers_new" replace ; run; quit; /* Save a CAS table to S3 with .sas7bdat extension */ proc casutil incaslib="caslibs3" outcaslib="caslibs3"; save casdata="customers" casout= "customers_new.sas7bdat" replace ; run; quit; /* Save a CAS table to S3 with .csv extension */ proc casutil incaslib="caslibs3" outcaslib="caslibs3"; save casdata="customers" casout= "customers_new.csv" replace ; run; quit; /* load a .sashdat file from S3 to CAS */ proc casutil incaslib="caslibs3" outcaslib="caslibs3"; droptable casdata="customers_new_hdat" quiet; load casdata="customers_new.sashdat" casout="customers_new_hdat" ; run; quit; /* load a .csv file from S3 to CAS */ proc casutil incaslib="caslibs3" outcaslib="caslibs3"; droptable casdata="customers_new_csv" quiet; load casdata="customers_new.csv" casout="customers_new_csv" ; run; quit; proc casutil; list tables incaslib="caslibs3 "; list files incaslib="caslibs3 "; run; /* Shutdown CAS Session */ CAS mySession TERMINATE;         Data load Performance from S3 bucket to CAS using S3FS: The data load to CAS from S3 bucket using S3FS is a slow process. The S3FS is not a hard link and bounded by network speed between the CAS server and S3 Bucket. The S3 data access performance can improve by using S3FS CACHE at the local machine which requires additional disk space. The parallel load using S3FS mounted file system is not supported so the load time is listed using serial mode.   The following data load tests were conducted using .sas7bdat data file format.     The following data load tests were conducted using .sashdat data file format.     Note: S3 Type CAS lib only support SASHDAT and CSV file format, so the above tests were conducted using .sashdat file format for better comparison.   Note: SAS Viya 3.5 will have improved mechanism to read and write data files to S3 location. I ran the same data load test on Viya 3.5 (pre-release software) and an 8GB .sashdat file takes ~120 Sec to load into CAS using S3 Type CAS lib.   Limitations of S3FS: Generally, S3 cannot offer the same performance or semantics as a local file system. More specifically: Metadata operations such as listing directories have poor performance due to network latency. Eventual consistency can temporarily yield stale data(Amazon S3 Data Consistency Model). Random writes or appends to files require rewriting the entire file. No atomic renames of files or directories. There is no coordination between multiple clients mounting the same S3 bucket. This is not a hard links to S3 bucket. Note: Don’t try to use the accessKeyId and secretAccessKey used in this post; they are expired.   Important link: S3FS-FUSE ... View more

Cloud Data Exchange(CDE) came out with 18W38 release, let’s take a look at the software to get an idea of what it look like.   Cloud Data Exchange is a data connection capability to securely access customer’s on-premise data (behind a firewall) from a public or private cloud applications (read SAS Viya 3.4). A customer may have on-premise data in various data sources (like Oracle, Teradata, Hadoop etc.). CDE enables users to securely access these data sources from cloud-based applications (SAS Viya 3.4). CDE negotiates customer’s on-premise firewall securely and responsibly while transferring high volume data between on-premise data sources and cloud application (SAS Viya 3.4). CDE stores on-premise data source credentials (userid/password) in a secured vault. So, these never have to be stored or accessed outside the on-premise firewall.   A high-level topology for Cloud Data Exchange and the SAS Viya 3.4 environment.       CDE provides a command line Input (CLI) interface for data source connection administration and control. The SAS Data Agent Server resides on-premises (behind the firewall) where you can configure the services for data access using the CLI. There is no visual interface. The Data Agent CLI operates as a plugin to the “sas-admin” CLI and is available for Linux, Windows and the MacOS operating systems.   We'll look at some specifics of the Cloud Data Exchange and Data Agent components below.   Before you can use the CLI, you need to perform the following steps on the machine running CLI (e.g. on-premise server). Set the SSL_CERT_FILE= environment variable.   CLI uses encrypted communication for all network commination and requires a server certificate in the trust store. e.g. export SSL_CERT_FILE=/opt/sas/viya/config/etc/SASSecurityCertificateFramework/cacerts/trustedcerts.pem   You may include the /opt/sas/viya/home/bin folder into $PATH system variable for easy access to CLI statement.   Set the base URL.   Most of the CLI commands require support from a REST interface hence commands must reflect the base URL which describes where the endpoints are located. You can persist the default endpoint in your SAS profile by running “sas-admin profile init” statement. The SAS profile is stored under the ~/.sas subdirectory.   Example:   $ sas-admin profile init Enter configuration options: Service Endpoint> https://dataagent-test-fri-cloud.dmmdev.sashq-d.openstack.sas.com:443 Output type (text|json|fulljson)> text Enable ANSI colored output (y/n)?> y Saved 'Default' profile to /home/centos/.sas/config.json. $   You can also view the existing profile by issuing the following statement:   $ sas-admin profile show Current settings for the [Default] profile: Setting Current Value ssl-cert-file /opt/sas/viya/config/etc/SASSecurityCertificateFramework/cac... refresh-token access-token eyJhbGciOiJSUzI1NiIsImtpZCI6ImxlZ2FjeS10b2tlbi1rZXkiLCJ0eXAi... expiry 2018-08-21T06:54:30Z ansi-colors-enabled true oauth-client-id sas.cli output text sas-endpoint https://dataagent-test-fri-cloud.dmmdev.sashq-d.openstack.sas.com:443 $   Obtain login credentials   All commands that works with the SAS VIYA REST interface uses OAUTH2 login credentials to create a token which provides authentication and authorization for each server. This token must be acquired one time before executing additional commands. When token expires, you must acquire a new token.   Example:   $ sas-admin auth login --user dbitest --password XXXXXXXXXXX Enter credentials for https://dataagent-test-fri-cloud.dmmdev.sashq-d.openstack.sas.com:443: Login succeeded. Token saved. $   Help on CLI statements For CDE CLI newbies, you can use “-h” at each stage to get help when you don’t know what the commands, plugins, and options are available to execute.   Example:   $ sas-admin -h NAME: sas-admin - SAS Administrative Command Line Interface USAGE: sas-admin [global options] command [command options] [arguments...] …………….. ………………………   $ sas-admin dagentsrv -h NAME: sas-dagentsrv USAGE: sas-admin dagentsrv command [command options] [arguments...] COMMANDS: catalogs Operate on catalogs in the Data Agent data-agents, servers Operate on Data Agent servers data-services, services Operate on data services on the Data Agen …………………. ………………………   $ sas-admin dagentsrv servers -h NAME: sas-dagentsrv data-agents - Operate on Data Agent servers USAGE: sas-dagentsrv data-agents command [command options] [arguments...] COMMANDS: help, h Shows a list of commands or help for one command. list List available Data Agent servers set-default Set the default Data Agent server name ………….. ……………………….   $ sas-admin dagentsrv services create oracle -h NAME: sas-dagentsrv data-services create oracle - Create an ORACLE data service definition USAGE: sas-dagentsrv data-services create oracle [command options] OPTIONS: --case-sensitive-columns Specify if column names are case sensitive --case-sensitive-objects Specify if object names are case sensitive --catalog Specify the name of the logical catalog for this data service --ct-preserve Preserve result set column types, can be STRICT, SAFE, FORCE, or FORCE_COL_SIZE --data-agent, -d Specify the name of the Data Agent --database-password Specify the password used to connect to the database   Frequently used CLI statement To list available Data Agent Servers:   $ sas-admin dagentsrv servers list Name Host Port Attributes dagentsrv-shared-default dataagent-test-fri-onprem.dmmdev.sashq-d.openstack.sas.com 443 ssl=Y;contextroot=dagentsrv-shared-default-private $   To list available Data services (connections) on the Data Agent Server:   $ sas-admin dagentsrv services list Name Type Domain Version Options __SERVER__ server --- 2.3 PURGE_CACHE=111;CONNECTION_POOL_TIMEOUT=11;CACHE=(NAME=AS;TIMEOUT=300) BASE base --- 2.3 --- generic_fed generic_fed generic_fed 2.3 --- netezza netezza netezza 2.3 --- sqlserver sqlserver sqlserver 2.3 --- DSNS_PAG_Test0 hive DSNS_PAG_Test0 2.3 --- …. …………….. DSNS_PAG_Test14 fedsvr DSNS_PAG_Test14 2.3 --- orasrv1 oracle ORACLE_DOMAIN 2.3 CONOPTS=(DRIVER=ORACLE;PATH=tktsora);CASE_SENSITIVITY=(OBJECT=T;COLUMN=T) orasrv3 oracle ORACLE_DOMAIN 2.3 CONOPTS=(DRIVER=ORACLE;PATH=orcl12ep);CASE_SENSITIVITY=(OBJECT=T;COLUMN=T)   To see supported source data sources:   $ sas-admin dagentsrv services create -h NAME: sas-dagentsrv data-services create - Generate a data service definition USAGE: sas-dagentsrv data-services create command [command options] COMMANDS: db2unxpc, db2 Create a DB2 for Unix and PC data service definition federation-server, fedsvr Create a data service definition to communicate with Federation Server hive Create an Apache Hive data service definition odbc Create an ODBC data service definition oracle Create an ORACLE data service definition postgres Create a POSTGRES data service definition redshift Create a RedShift data service definition saphana, hana Create a SAP HANA data service definition sqlserver, sqlsvr Create SQL Server (via ODBC) data service definition teradata Create a Teradata data service definition   Configuring a new Oracle Data Service (connection) on the Data Agent Server As a Data Agent Administrator, execute the following steps to create a new database connection on the Data Agent. (Note: As a part of post installation steps, administrator adds user to Data Agent Administrator group) To configure and connect to a database environment you need the respective database client installed on Data Agent Server. The environment variable for all source databases are located in /opt/sas/viya/config/etc/sysconfig/dagentsrv/default/sas-dagentsrv config file.   For an Oracle database connection, the following variables are used. Other data sources use different variables but the process of exporting the required environment variables is the same.   export ORACLE_HOME=/dbi/oracle/12c export TWO_TASK=oraclev12 export ORAENV_ASK=NO export SASORA=V9 export PATH=$PATH:$ORACLE_HOME/bin export TNS_ADMIN=/~/fedadmin/tnsnames export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib   Create a security Domain(shadow) for database access.   $ sas-admin dagentsrv security domains create --domain ORACLE_DOMAIN   View the created Domain:   $ sas-admin dagentsrv security domains list ID Type Label Description DACLI_SQLSERVER_DOMAIN shadow ORACLE_DOMAIN shadow   To delete a Domain: $ sas-admin dagentsrv security domains delete --domain ORACLE_DOMAIN --cascade   Add database credentials and identity user to the newly created Domain(shadow).   $ sas-admin dagentsrv security credentials create --domain ORACLE_DOMAIN --identity dbitest --username TKTSTST1 --password XXXXXXX (Note: "--username" is for valid database user (in this case oracle user); "--password" is for valid database access password.)   View the shadow credentials in a domain:   $ sas-admin dagentsrv security credentials list --domain ORACLE_DOMAIN --identity dbitest Identity Type UserId ShadowKey dbitest user TKTSTST1 000737E0-11C9-4A4F-9361-6B9B71B7E1DD $   Create oracle database service at Data Agent using newly created Domain(shadow).   $ sas-admin dagentsrv services create oracle --name orasrv2 --path TKTSORA --domain ORACLE_DOMAIN (Note: --path - is oracle instance name from tnsnames.ora file located under TNS_ADMIN=/~/fedadmin/tnsnames folder)   View the newly created data service:   $ sas-admin dagentsrv services list Name Type Domain Version Options __SERVER__ server --- 2.3 PURGE_CACHE=111;CONNECTION_POOL_TIMEOUT=11;CACHE=(NAME=AS;TIMEOUT=300) BASE base --- 2.3 --- generic_fed generic_fed generic_fed 2.3 --- …. ……. orasrv2 oracle ORACLE_DOMAIN 2.3 CONOPTS=(DRIVER=ORACLE;PATH=TKTSORA);CASE_SENSITIVITY=(OBJECT=T;COLUMN=T)   To delete a data services: $ sas-admin dagentsrv services delete --cascade --name orasrv2   Test the newly created oracle database service at Data Agent.   $ sas-admin dagentsrv ds test --name orasrv2 Successfully connected to data source orasrv2. $   Creating a CASLib on SAS Viya to access data from newly created Data Service at Data Agent Server Once the SAS data agent server is registered, you can create a CASLib and access the data through the Data Agent. In Environment Manager, create a new CASLib using source type as CDE or use your own SAS code to create a CDE CASLib. Once you have the CDE type CASLib created, you can load data to CAS from the source environment and save it back to source environment as well.   The following screen capture shows the creation of a CDE type CASLIB.     On the advanced tab, you can use additional parameter to filter tables from specific schema.     The following screen captures shows the details of CDE type CASLib.     The following screen capture shows the data tables available from the CDE type CASLib and can be loaded into CAS (in-memory).       ... View more

Hadoop is the most commonly used data storage and processing environment in Big Data world. Apart from storing plain text data formats, users may choose to store data in serialized formats with complex data types. One of the most widely used data serialization formats is Avro, which enables users to store nested data structures with complex data types like STRUCT, ARRAY, Maps etc. An Avro data file with complex data types can be created by various applications like Hive, SPARK, Impala, Tez, Pig, etc.   Now, as a SAS user we ask the question, "Out of the box, can SAS directly read an Avro binary data file with complex data types to load into CAS?" Answer is no! SAS cannot directly read an Avro binay data file, even if you try using PROC HDMD to generate a schema for the file (without something like a custom reader).   SAS can, however, read an Avro file via Hive (when an Avro data file schema is registered in Hive-Catalog). For Avro files with complex data types, the user needs to parse the complex data into multiple separate columns at hive before reading into SAS. To do this, the user may create a Hive view with parsed columns on top of the Avro Hive table to read each data element as separate columns from nested complex data.   The following steps describe loading CAS with an Avro data file with complex data type (STRUCT) using a Hive view.   Retrieve the schema from a Binary Avro data file. The Avro data file contains both Schema and actual data values. The Avro data file header contains the schema. Most hadoop distributions come with an application called “avro-tools” which can be used to extract Avro file schemas. (If an Avro data file is too big to move around for schema extraction, try using “head " statement.)   The following Unix statement extracts the schema from the sample.avro data file and places the schema into the sample.avsc file.   $ avro-tools getschema sample.avro > sample.avsc If you don’t have ‘avro-tools’ in your hadoop environment, you can download the compatible ‘avro-tools-XXXX.jar’ file from apache site and extract schema as follows.   $ java -jar ~/avro-tools-X.X.X.jar getschema sample.avro > sample.avsc   Place the Avro data and schema files into an HDFS folder (non /user/hive/warehouse location). Use the following statement (or equivalent) to place the Avro data file and corresponding extracted schema file into an HDFS folder where the user ‘hive’ has access to register it in Hive-Catalog.   $ hadoop fs -put sample.avsc /user/cloud-user/avro_schema/ $ hadoop fs -put sample.avro /user/cloud-user/avro_data/ $ hadoop fs -ls -R /user/cloud-user drwxr-xr-x - cloud-user hive 0 2018-05-07 16:26 /user/cloud-user/avro_data -rw-r--r-- 3 cloud-user hive 754261 2018-05-07 16:26 /user/cloud-user/avro_data/sample.avro drwxr-xr-x - cloud-user hive 0 2018-05-07 16:25 /user/cloud-user/avro_schema -rw-r--r-- 3 cloud-user hive 10933 2018-05-07 16:25 /user/cloud-user/avro_schema/sample.avsc   Create a Hive table based on the Avro data and schema files. Use the "create external table" statement to register the Avro schema with Data file into Hive-Catalog. Note the SERDE, INPUTFORMAT, OUTPUTFORMAT, LOCATION, and TABLEPROPERTIES options used. The option values for these parameters will vary depending upon the values used while creating the Avro file.   CREATE EXTERNAL TABLE avro_sample_table ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat' LOCATION '/user/cloud-user/avro_data' TBLPROPERTIES('avro.schema.url'='hdfs://servername.xxx.xxx:8020/user/cloud-user/avro_schema/sample.avsc');   Verify the hive table structure and access of data at hive prompt. Notice the hive table has only two columns, Key and Value. The ‘Value’ column is a complex data type STRUCT. While SAS/ACCESS treats the STRUCT as a CHAR(32K) column, the SAS Data Connector for Hadoop does not support the STRUCT data type (without a custom reader).   hive> desc avro_sample_table ; OK key string value struct'<'siteean:string,siteplgkey:string,countrycode:string, aclgeskey:string,sitemaincode:string,sitefinkey:string,sitedesc:string,address1:string....   hive> select * from avro_sample_table limit 1; OK 3020180258104 {"siteean":"3020180258104","siteplgkey":"","countrycode":"xx"..........} Time taken: 0.144 seconds, Fetched: 1 row hive>   Create a Hive View to parse the complex data type(s). You can use the Avro schema file (XX.avsc) as a template for starting your Hive view. Copy the file and add the required statements (e.g. "create view"). Pay special attention to any nested column in the table/view. In this example, geoloctype, geox and geoy are nested columns and accordingly it’s addressed to read the actual value. There is no automated mechanism for generating the view statement. You have to manually verify that all columns are addressed properly.   Arrays and other repeating structures (e.g. an entity with multiple phone numbers) will need to be parsed into a flat structure (e.g. Phone1, Phone2, Phone3, …).   create view avro_sample_view as select key, value.siteEan, value.sitePlgKey, value.countryCode, value.aclGesKey, ... value.geoLocation.geoLocType, value.geoLocation.geoX, value.geoLocation.geoY, value.createDate, value.deleteDate, …… …. value.siteIntDesc from avro_sample_table;   Here we store our view code in a file, ‘hv_view.sql’, and invoke the hive application to create view at hive.   $ hive -f hv_view.sql   Load the Hive view using the DATA Connector and Accelerator.   75 75 ! load casdata="avro_sample_view" casout="avro_sample_view" 76 outcaslib="HiveEP" incaslib="HiveEP" ; NOTE: Executing action 'table.loadTable'. NOTE: Performing parallel LoadTable action using SAS Data Connect Accelerator for Hadoop. NOTE: Cloud Analytic Services made the external data from avro_sample_view available as table AVRO_SAMPLE_VIEW in caslib HiveEP. NOTE: Action 'table.loadTable' used (Total process time):   Table structure of the CAS table loaded from the Hive view, Avro_view: Note 1: Separate columns for each data element from the complex STRUCT data type. Note 2: All STRING data type from Avro_Hive table converted into VARCHAR by CAS.       Observations: On the Hadoop cluster, the Data load to CAS job executes in two steps. First, it stages the data in a temporary location, then SAS EP feeds the data to CAS.   ... View more

My last post was about SAS Viya “Multi Node Data Transfer” mode, mainly focused around data load from source DBMS to CAS. In this post I talk about saving data from CAS to DBMS using “Multi Node Data Transfer” mode, answering questions such as what is the NUMWRITENODES= parameter; what happens when you specify value 2 for NUMWRITENODES= even though you have 7 CAS worker nodes; and in the case of Hadoop/Hive, does it create MapReduce jobs.   By using “Multi Node Data Transfer” mode, you can save CAS data to a DBMS in addition to loading data to CAS from a source DBMS. In MultiNode Data transfer mode, each CAS Worker makes a simultaneous concurrent connection to read and write data to/from the source DBMS or Distributed data system.   Prerequisites to enable MultiNode Data Transfer include: The CAS environment is a multi-node environment (multiple CAS Worker Nodes). The SAS Data Connector for the data source is installed on each CAS Worker and Controller Node. The data source client connection components are installed on each CAS Worker and Controller Node. The CASLIB is defined with the relevant parameter (NUMREADNODES= or NUMWRITENODES=) set a value other than 1.   The CAS Controller controls the MultiNode Data transfer. It directs each CAS worker node on how to write CAS data to the DBMS. It directs each CAS worker node to submit an independent concurrent insert statement to write its slice of data. Data is transferred from the CAS worker nodes to the DBMS environment directly using a single thread connection, bypassing the CAS Controller. The CASLIB NUMWRITENODES= parameter determines the number of CAS Worker nodes that will make direct connections to the DBMS.   Let’s consider two scenarios to investigate what happens on a source CAS environment and a target Hadoop environment when CAS data is saved into a hive table.   CAS data save with NUMWRITENODES=0 (using all CAS Worker nodes) CAS data save with NUMWRITENODES= {< available CAS Worker nodes}   Scenario 1 – CAS data save with NUMWRITENODES=0 (Using all CAS Worker Nodes)   The following pics describe the data save from CAS to DBMS using MultiNode Data transfer Mode. CAS is hosted on a multi-node environment with the SAS Data Connector installed on each node (CAS Controller and Workers). A CASLIB is defined with NUMWRITENODES=0 to use all available CAS worker Nodes. With each data save request, the CAS controller directs each CAS worker node to submit individual insert statement to write its slice of data. Each CAS Worker node stages its data slice into a temporary file/table at DBMS before inserting into actual table. In case of a target hive table, CAS creates a MapReduce job for each CAS worker insert process. On Hadoop cluster, it also created a corresponding MapReduce job for each Insert statement submitted by CAS worker nodes.     The following code example describes the data save using “MultiNode” data transfer mode with all available CAS Worker nodes. It assigns a CASLIB using serial mode with NUMWRITENODES=0 to save data from an existing CAS table. The existing CAS table is evenly distributed across the CAS worker nodes under another predefined CASLIB. It executes a cross CASLIB data save operation. As the NUMWRITENODES= value is other than 1, it follows the MultiNode mechanism. The CAS controller first drops the existing target table and creates a blank table on the target DBMS. It directs each CAS worker node to insert its data slice to the DBMS table. Before inserting the data to the actual table, each thread stages its data into a temporary file/table. In case of hive, the temp data files are located in the HDFS /tmp folder.   An existing CAS table evenly distributed across CAS worker nodes, which is used for data save.     Code to save a CAS table using all available nodes (A cross CAS LIB save):   CAS mySession SESSOPTS=( CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true); ..... caslib HiveSrlN0 datasource=(srctype="hadoop", server="sascdh01.race.sas.com", username="hadoop", dataTransferMode="SERIAL", NUMREADNODES=0, NUMWRITENODES=0, hadoopconfigdir="/opt/MyHadoop/CDH/Config", hadoopjarpath="/opt/MyHadoop/CDH/Jars", schema="default"); proc casutil ; save incaslib="HiveSrl1" outcaslib="HiveSrlN0" casdata="prdsal2_1G" casout="prdsal2_1G_cas" replace; run;   SAS Log extract:   …. …. 1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 72 73 proc casutil ; NOTE: The UUID 'e9547888-e0b0-a74d-aae6-9563121f0568' is connected using session MYSESSION. 74 save incaslib="HiveSrl1" outcaslib="HiveSrlN0" 75 casdata="prdsal2_1G" casout="prdsal2_1G_cas" replace; NOTE: Executing action 'table.save'. NOTE: Performing serial SaveTable action using SAS Data Connector to Hadoop. NOTE: Cloud Analytic Services saved the file prdsal2_1G_cas in caslib HIVESRLN0. NOTE: Action 'table.save' used (Total process time): NOTE: real time 347.188016 seconds NOTE: cpu time 36.546948 seconds (10.53%) NOTE: total nodes 8 (32 cores) NOTE: total memory 250.11G NOTE: memory 34.38M (0.01%) NOTE: The Cloud Analytic Services server processed the request in 347.188016 seconds. 76 run; … .. … ..   On the Database side (Hive in this example), note the active sessions and queries submitted by the CAS Worker Nodes. The CAS environment has 7 CAS workers nodes and each one has an active session to Hive server. (The hosts file is shown below to matchup the IP addresses with the CAS worker nodes)     IP address and server name from CAS environment: 10.96.11.38 sascas01.race.sas.com sascas01 10.96.8.175 sascas02.race.sas.com sascas02 10.96.3.120 sascas03.race.sas.com sascas03 10.96.12.136 sascas04.race.sas.com sascas04 10.96.10.167 sascas05.race.sas.com sascas05 10.96.16.181 sascas06.race.sas.com sascas06 10.96.17.112 sascas07.race.sas.com sascas07 10.96.11.184 sascas08.race.sas.com sascas08   The Hive UI "Open Query" view shows the "LOAD DATA" statements that pull from the staging files.     Below we see a subset of HDFS staged temp data files, while CAS table save process was running using the Hadoop FS utility:   [hadoop@sascdh01 ~]$ hadoop fs -ls /tmp/sas_* Found 1 items -rw-r--r-- 3 hadoop supergroup 135026535 2018-04-11 16:42 /tmp/sas_tempbulk-2018-04-11-16-42-50-905/sas_tempbulk-2018-04-11-16-42-51-141_0 Found 1 items -rw-r--r-- 3 hadoop supergroup 135268559 2018-04-11 16:42 /tmp/sas_tempbulk-2018-04-11-16-42-50-907/sas_tempbulk-2018-04-11-16-42-51-144_0   Using the Hadoop Resource Manager Job History User interface, you can see the corresponding MapReduce jobs for each Insert statement submitted by the CAS worker nodes.     Scenario 2 – CAS data save with NUMWRITENODES=2 (w/ 7 worker nodes)   The following pics, code, and other artifacts describe the data save process from CAS to DBMS using the MultiNode Data transfer mode with selective Worker Nodes. CAS is hosted on a multi-node environment with SAS Data Connector installed on each node (CAS Controller and Workers). A CASLIB is defined with NUMWRITENODES=2, to make only 2 direct connection to the DBMS. When value of NUMWRIETNODES= is less than the available number of CAS worker nodes, the CAS Controller randomly select the specified number of Worker Nodes to make direct connections to the DBMS. The CAS Worker Nodes which are not chosen copy their data blocks to next available Worker Node which was chosen. The CAS controller directs each selected CAS worker node to submit individual insert statements to write its data slice (including that received from the unchosen nodes). The CAS Worker nodes stage their data slices to the DBMS in a temporary file/table before inserting into the actual table. In case of hive, CAS creates a MapReduce job for each CAS worker insert process.     Code to Save a CAS table using a subset of available nodes (cross CAS LIB save):   CAS mySession SESSOPTS=( CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true); ..... caslib HiveSrlN2 datasource=(srctype="hadoop", server="sascdh01.race.sas.com", username="hadoop", dataTransferMode="SERIAL", NUMREADNODES=2, NUMWRITENODES=2, hadoopconfigdir="/opt/MyHadoop/CDH/Config", hadoopjarpath="/opt/MyHadoop/CDH/Jars", schema="default"); proc casutil ; save incaslib="HiveSrl1" outcaslib="HiveSrlN2" casdata="prdsal2_1G" casout="prdsal2_1G_cas" replace; run;   SAS Log extract:   …. …. 72 73 proc casutil ; NOTE: The UUID 'e9547888-e0b0-a74d-aae6-9563121f0568' is connected using session MYSESSION. 74 save incaslib="HiveSrl1" outcaslib="HiveSrlN2" 75 casdata="prdsal2_1G" casout="prdsal2_1G_cas" replace; NOTE: Executing action 'table.save'. NOTE: Performing serial SaveTable action using SAS Data Connector to Hadoop. NOTE: Cloud Analytic Services saved the file prdsal2_1G_cas in caslib HIVESRLN2. NOTE: Action 'table.save' used (Total process time): NOTE: real time 97.914763 seconds NOTE: cpu time 40.201150 seconds (41.06%) NOTE: total nodes 8 (32 cores) NOTE: total memory 250.11G NOTE: memory 22.64M (0.01%) NOTE: The Cloud Analytic Services server processed the request in 97.914763 seconds. 76 run; … ..   In this case, we only see 2 workers (as well as the controller) interacting with the Hive environment.     IP address and server name from CAS environment: 10.96.11.38 sascas01.race.sas.com sascas01 10.96.8.175 sascas02.race.sas.com sascas02 10.96.3.120 sascas03.race.sas.com sascas03 10.96.12.136 sascas04.race.sas.com sascas04 10.96.10.167 sascas05.race.sas.com sascas05 10.96.16.181 sascas06.race.sas.com sascas06 10.96.17.112 sascas07.race.sas.com sascas07 10.96.11.184 sascas08.race.sas.com sascas08     Observations: When using “Multi Node Data Transfer” mode to save data to DBMS, plan for considerable amount of temporary space to accommodate the data staging by CAS save process. When saving to Hive, all CAS worker save requests end up in a MapReduce Queue. The execution of data Insert job can take time depending on Hadoop environment resources and usage.   For more information about this topic: Using Data Transfer Modes with Data Connectors ... View more

With SAS Viya 3.3, the SAS Data Connector for Hadoop and SAS Data Connect Accelerator for Hadoop can read data from SAS HDMD tables stored in HDFS, in addition to Hive tables. A SAS HDMD table is an XML-Based metadata generated by PROC HDMD that describe the contents of a file stored in HDFS.   This enables you to read data from delimited, Binary, and XML formatted files stored in HDFS (Hadoop) and load it to CAS using serial or parallel mode. You can use SAS Data connect accelerator and SAS EP to parallel load CAS table from HDMD tables.   The SAS Data Connector and Data Connect Accelerator for Hadoop are part of SAS Viya version of SAS/ACCESS interface to Hadoop, which is used for serial and parallel data read and write between CAS and various data servers.   SAS PROC HDMD The SAS PROC HDMD generates XML-Based metadata that describes the contents of a data file stored in HDFS. The file-type for an XML-based metadata generated by PROC HDMD is SASHDMD (i.e. cust_table.sashdmd). This metadata, also known as SASHDMD descriptor, is stored in HDFS. This metadata enables the SAS/ACCESS Interface to Hadoop and the SAS high-performance procedure to read Hadoop data directly without an intermediate metadata repository such as Hive.   When HDFS_METADIR= specified in LIBANME connection option, SAS/ACCESS does not connect to Hive, but instead looks for an XML-based HDMD metadata file. When SAS PROC HDMD is used with a Hadoop LIBNAME, it creates an SASHDMD file containing metadata.   The SAS PROC HDMD can be used to describe the columns in an HDFS data file or an HDFS folder containing a list of data files with same file format. When it describes one HDFS file, the SASHDMD descriptor contains the complete file path. When it describes an HDFS folder, the SASHDMD descriptor contains an HDFS directory path:   PROC HDMD can be used to describe tabular HDFS files for the following formats: Delimited text file Binary - Fixed record length data file XML- encoded text file. Examples: The following example describe the creation of a HDMD metadata for a “|” delimited HDFS file using PROC HDMD.   File location on HDFS:  /user/utkuma/hdmd/data/pipedata_dept.txt   Data file contents:   123|Accounts 124|HR 125|Sales 126|Payroll 127|Marketing 128|Purchase   PROC HDMD statement:   OPTIONS SET=SAS_HADOOP_JAR_PATH="/opt/MyHadoop/CDH/Jars"; OPTIONS SET=SAS_HADOOP_CONFIG_PATH="/opt/MyHadoop/CDH/Config"; libname HDP hadoop user=utkuma server='sascdh01.race.sas.com' hdfs_metadir = '/user/utkuma/hdmd/meta' hdfs_datadir = '/user/utkuma/hdmd/data' ; proc hdmd name=hdp.pipedata_dept file_format=delimited sep = '|' DATA_FILE='pipedata_dept.txt' ; COLUMN DeptId int; COLUMN DeptName char(15); run;   The following example describes the creation of a HDMD metadata using PROC HDMD for a HDFS folder containing list of “|” delimited data files.   Folder and file location on HDFS:   [hadoop@sascdh01 ~]$ hadoop fs -ls /user/utkuma/hdmd/data/dept Found 5 items -rw-r--r-- 3 utkuma sasusers 75 2018-02-08 09:44 /user/utkuma/hdmd/data/dept/dept0.txt -rw-r--r-- 3 utkuma sasusers 75 2018-02-08 09:44 /user/utkuma/hdmd/data/dept/dept1.txt -rw-r--r-- 3 utkuma sasusers 75 2018-02-08 09:44 /user/utkuma/hdmd/data/dept/dept2.txt -rw-r--r-- 3 utkuma sasusers 75 2018-02-08 09:44 /user/utkuma/hdmd/data/dept/dept3.txt -rw-r--r-- 3 utkuma sasusers 75 2018-02-08 09:44 /user/utkuma/hdmd/data/dept/dept4.txt   PROC HDMD statement:   … …… proc hdmd name=hdp.deptM file_format=delimited sep = '|' DATA_DIR='dept' ; COLUMN DeptId int; COLUMN DeptName char(15); run;   To view the metadata of a HDMD table, you can use ‘describe’ option with PROC HDMD.   73 proc hdmd name=hdp.deptM describe; 74 run; PROC HDMD NAME=HDP.DEPTM FILE_FORMAT=DELIMITED ENCODING=UTF8 SEP='|' BYTE_ORDER=LITTLEENDIAN FILE_TYPE=DELIMITED DATA_DIR='/user/utkuma/hdmd/data/dept' META_DIR='/user/utkuma/hdmd/meta'; COLUMN /* 1 */ DeptId INT OFFSET=0 BYTES=4 CTYPE=INT32; COLUMN /* 2 */ DeptName CHAR(15) OFFSET=0 BYTES=15 CTYPE=CHAR; NOTE: PROCEDURE HDMD used (Total process time):   For information on SAS PROC HDMD and available options with each file format, see the documentation.   Data load from a HDMD table to CAS The data from a SAS HDMD table can be loaded to CAS using Serial or Parallel method. For parallel load, you must have the SAS Data Connect Accelerator for Hadoop installed on CAS Controller and on each CAS Worker nodes along with SAS EP installed on Hadoop cluster nodes. You also need Hadoop client Jars and client Configuration files on CAS Controller node. For serial load you must have SAS Data Connector for Hadoop installed on CAS Controller node long with Hadoop client Jars and client Configuration files on CAS Controller node. You can also use the Multi-Node data transfer loading facility by installing Data Connector for Hadoop on each CAS Worker nodes along with the Hadoop client Jars and client Configuration files on each CAS worker nodes.   When defining a CASLIB with source type of Hadoop, you can specify HDFSMETADIR= and HDFSDATADIR= in connection options to enable the access of SAS HDMD metadata. When you specify HDFSMETADIR= in connection option, the Data Connector and Data Connect Accelerator does not connect to Hive, instead it looks for an XML-based HDMD metadata to access the HDFS data file.   The following code example describes the data load from a SAS HDMD table to CAS using “Parallel” data transfer mode. It assigns a CASLIB with parallel mode along with HDFSMETADIR= and HDFSDATADIR= options to enable the access of HDMD metadata. By using PROC CASUTIL and load table CAS action, it loads the data from HDMD table to CAS. You can notice the log says “Performing parallel LoadTable action using SAS Data Connect Accelerator for Hadoop”.   CAS mySession SESSOPTS=( CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true); caslib MyHdmd desc='HdmdCaslib' datasource=(srctype="hadoop", server="sascdh01.race.sas.com", username="utkuma", dataTransferMode="parallel", hadoopconfigdir="/opt/MyHadoop/CDH/Config", hadoopjarpath="/opt/MyHadoop/CDH/Jars", hdfsDataDir="/user/utkuma/hdmd/data", hdfsMetaDir="/user/utkuma/hdmd/meta"); proc casutil; load casdata="deptm" casout="deptm" incaslib="MyHdmd" outcaslib="MyHdmd" replace ; run; CAS mySession TERMINATE;   Log extract from SAS code execution:   …. 84 85 proc casutil; NOTE: The UUID '42936e10-c499-0e4a-a764-5e405a91b8e4' is connected using session MYSESSION. 86 load casdata="deptm" casout="deptm" 87 incaslib="MyHdmd" outcaslib="MyHdmd" 88 replace ; NOTE: Executing action 'table.loadTable'. NOTE: Performing parallel LoadTable action using SAS Data Connect Accelerator for Hadoop. NOTE: Cloud Analytic Services made the external data from deptm available as table DEPTM in caslib MyHdmd. NOTE: Action 'table.loadTable' used (Total process time): NOTE: real time 29.974952 seconds NOTE: cpu time 0.884905 seconds (2.95%) NOTE: total nodes 8 (32 cores) NOTE: total memory 250.11G NOTE: memory 64.41M (0.03%) NOTE: The Cloud Analytic Services server processed the request in 29.974952 seconds. 89 run; … ..   If we look at the Hadoop cluster resource manager for corresponding job execution, it shows application executed with SAS EP.     If we look at the log from executed mapper process, it also shows that SAS EP was used while running data feed to CAS.   …….. log4j:WARN No appenders could be found for logger (com.sas.hadoop.ep.TaskAgent$ShutdownHook). log4j:WARN Please initialize the log4j system properly. log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info. Log Type: stdout Log Upload Time: Tue Mar 13 16:26:31 -0400 2018 Log Length: 195 20180313:16.26.22.67: 00000010:WARNING: [01S02]Current catalog set to SASEP (0x80fff8bd) 20180313:16.26.22.79: 00000017:NOTE: All Embedded Process DS2 execution instances completed with SUCCESS. Log Type: syslog Log Upload Time: Tue Mar 13 16:26:31 -0400 2018 Log Length: 9885 Showing 4096 bytes of 9885 total. Click here for the full log. 22,666 INFO [main] com.sas.hadoop.ep.EPNativeInterface: SAS Embedded Process task is now connected. 2018-03-13 16:26:22,699 INFO [main] com.sas.hadoop.ep.EPNativeInterface: DS2 program is now prepared and ready for execution. 2018-03-13 16:26:22,702 INFO [main] com.sas.hadoop.ep.EPNativeInterface: Task ID 1 does not write output metadata. 2018-03-13 16:26:22,712 INFO [DonAlvaro] com.sas.hadoop.ep.Alvaro: Alvaro has been invoked to guide current task execution. …….   Data filter support Data filter is supported while loading SAS HDMD table to CAS using SAS Data Connector for Hadoop or SAS Data Connect Accelerator for Hadoop. The following SAS log describe the data load from a HDMD table to CAS with data filter condition.   73 proc casutil; NOTE: The UUID '418734c4-1480-d540-9a20-a001a79fc762' is connected using session MYSESSION. 74 load casdata="deptm" casout="deptm" 75 incaslib="MyHdmd" outcaslib="MyHdmd" 76 options={where="DeptId > 200 "} 77 replace ; NOTE: Executing action 'table.loadTable'. NOTE: Performing parallel LoadTable action using SAS Data Connect Accelerator for Hadoop. NOTE: Cloud Analytic Services made the external data from deptm available as table DEPTM in caslib MyHdmd. NOTE: Action 'table.loadTable' used (Total process time): NOTE: real time 29.167724 seconds   Data save from CAS to SAS HDMD table You can save a CAS table to SAS HDMD table in a text file with default “^A” column delimiter. The data append from a CAS table to an existing HDMD table is not supported.   The following SAS log describe the data save from a CAS table to SAS HDMD table using Data Connect Accelerator.   ..... 73 proc casutil; NOTE: The UUID 'bfdc4bbe-27ee-a947-864d-4dacf5ed31b2' is connected using session MYSESSION. 74 save casdata="deptm" casout="deptm_new" 75 incaslib="MyHdmd" outcaslib="MyHdmd" replace ; NOTE: Executing action 'table.save'. NOTE: Performing parallel SaveTable action using SAS Data Connect Accelerator for Hadoop. NOTE: Cloud Analytic Services saved the file deptm_new in caslib MYHDMD. NOTE: Action 'table.save' used (Total process time): NOTE: real time 30.204765 seconds ..... ......   In Hadoop HDFS, notice a new folder with list of data files for saved HDMD table.   [hadoop@sascdh01 ~]$ hadoop fs -ls /user/utkuma/hdmd/data/deptm_new Found 3 items -rw-r--r-- 3 utkuma sasusers 69 2018-03-14 15:35 /user/utkuma/hdmd/data/deptm_new/sasdata-2018-03-14-15-35-09-m-00000-SWCH-M00000-T1.dlv -rw-r--r-- 3 utkuma sasusers 138 2018-03-14 15:35 /user/utkuma/hdmd/data/deptm_new/sasdata-2018-03-14-15-35-10-m-00002-SWCH-M00002-T0.dlv -rw-r--r-- 3 utkuma sasusers 138 2018-03-14 15:35 /user/utkuma/hdmd/data/deptm_new/sasdata-2018-03-14-15-35-10-m-00005-SWCH-M00005-T1.dlv   The content from data file shows that it’s a delimited text file with default “^A” column delimiter.   [hadoop@sascdh01 ~]$ hadoop fs -cat /user/utkuma/hdmd/data/deptm_new/sasdata-2018-03-14-15-35-09-m-00000-SWCH-M00000-T1.dlv 323Accounts 324HR 325Sales 326Payroll 327MArketing 328Purchase   Note: To create an xml-based metadata for HDFS data file, you must have SAS/ACCESS interface to Hadoop on SAS Client (SAS Studio) to access Hadoop using Hadoop engine.   For more information about this topic: SAS PROC HDMD SAS Data Connector to Hadoop and SAS Data Connect Accelerator for Hadoop ... View more

With SAS Viya 3.2, what is the best approach to refresh a very large, stale CAS table (read FACT table) when the source data gets updated? The question is even more important when there is a narrow downtime window for complete refresh.   This post discusses a mechanism for swapping data into a CAS table from a staged session scoped CAS table, minimizing downtime while still doing a complete refresh. The mechanism leverages the basic concept of CAS where a user can load a table as session scoped table and later promote it to global scope for use in VA 8.1. The data transfer (PROMOTE) operation executes in CAS (memory) so it’s super-fast and efficient with minimum down time.     To refresh a stale CAS table from source data quickly, follow these steps: Load the new table version of source as a session CAS table (staging table) Drop target CAS table Promote the staged session CAS table to the target CAS table name   The following code demonstrates the data swap. For convenience purposes, I am using a .sas7bdat table to load CAS. The same data could have been in a third party database (hadoop-hive) with a view created using multiple tables to generate the flat FACT table. By using Data Accelerator and SAS EP, the source data could have parallel feed to CAS staged table. The initial step in the code loads the 10GB dataset table into a session CAS table (staging table). Then, it drops the target CAS table and promotes the staging table to the target CAS table name.   In this case, a 10GB (36M rows) table only takes ~ 8 sec to drop and ~ 0.23 sec to PROMOTE !!!       Code:     options msglevel=i ;   cas mySession sessopts=(messagelevel=all) ;   libname srcld "/opt/sas/data";     * Load new version of source table into a session scope CAS table;   proc casutil ;     load data=srcld.order_fact_10G outcaslib="CASPATH"     casout="order_fact_10G_stg" replace ;   quit ;     * Drop target CAS table (global) and PROMOTE session scoped CAS table with target CAS table name;   PROC CASUTIL ;     droptable casdata="order_fact_10G" incaslib="CASPATH" ;     promote casdata="order_fact_10G_stg" casout="order_fact_10G"     incaslib="CASPATH" outcaslib="CASPATH" ;   quit ;     cas mySession terminate ;         Log from code execution:   1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; INFO: Character variables have defaulted to a length of 200 at the places given by: (Line):(Column). Truncation can result. 23:1 RC 56 57 options msglevel=i ; 58 cas mySession sessopts=(messagelevel=all) ; NOTE: The session MYSESSION connected successfully to Cloud Analytic Services intcas01.race.sas.com using port 5570. The UUID is c8bc1412-2942-5f40-b8b8-25b8962ee5d0. The user is sastest1 and the active caslib is CASUSER(sastest1). NOTE: The SAS option SESSREF was updated with the value MYSESSION. NOTE: The SAS macro _SESSREF_ was updated with the value MYSESSION. NOTE: The session is using 3 workers. NOTE: The CAS statement request to update one or more session options for session MYSESSION completed. 59 60 libname srcld "/opt/sas/data"; NOTE: Libref SRCLD was successfully assigned as follows: Engine: V9 Physical Name: /opt/sas/data 61 62 * Load new version of source table into a session CAS table ; 63 proc casutil ; NOTE: The UUID 'c8bc1412-2942-5f40-b8b8-25b8962ee5d0' is connected using session MYSESSION. 64 load data=srcld.order_fact_10G outcaslib="CASPATH" casout="order_fact_10G_stg" replace ; NOTE: SRCLD.ORDER_FACT_10G was successfully added to the "CASPATH" caslib as "order_fact_10G_stg". 65 quit ;   NOTE: PROCEDURE CASUTIL used (Total process time): real time 4:06.37 cpu time 34.60 seconds 66 67 * Drop target live CAS table and PROMOTE the session CAS table with target CAS table name ; 68 PROC CASUTIL ; NOTE: The UUID 'c8bc1412-2942-5f40-b8b8-25b8962ee5d0' is connected using session MYSESSION. 69 droptable casdata="order_fact_10G" incaslib="CASPATH" ; NOTE: Cloud Analytic Services dropped table order_fact_10G from caslib CASPATH. NOTE: The Cloud Analytic Services server processed the request in 6.089074 seconds. 7 0 71 promote casdata="order_fact_10G_stg" casout="order_fact_10G" 72 incaslib="CASPATH" outcaslib="CASPATH" ; NOTE: The Cloud Analytic Services server processed the request in 0.239992 seconds. NOTE: Cloud Analytic Services promoted table order_fact_10G_stg in caslib CASPATH. 73 quit ;   NOTE: PROCEDURE CASUTIL used (Total process time): real time 6.35 seconds cpu time 0.01 seconds 74 75 cas mySession terminate ; NOTE: Deletion of the session MYSESSION was successful. NOTE: Request to TERMINATE completed for session MYSESSION. 76 77 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 90         A Note on Memory Requirements The session CAS table (staging table) load and swap-out process briefly requires twice the normal CAS table memory storage since there will be two copies of the table in memory, the staged one and the one being updated. Once the CAS table is dropped and Staged CAS table promoted with target CAS table name, this requirement goes away.         Reference documents CASUTIL Procedure ... View more

So you need to refresh a LASR table. What’s the best way? If you can isolate the changes since the last LASR load, you don’t have enough memory to accommodate a SWAP-OUT update, and you have mostly inserts to do, it may be preferable to perform a CDC update as opposed to reloading the entire table.   This blog post discusses a mechanism for updating a very large LASR table using a Changed Data Capture (CDC) table, a single table with all of the insert/update/delete operations required to bring the target LASR table up to date.     To apply CDC on a LASR table, the proposed mechanism uses the following steps: Load the CDC table into LASR Append the “Insert” rows from the CDC table to the target LASR table Delete “Insert” rows from the CDC table Update the target LASR table with the “Update” rows from the CDC table using a computed “_where_” column. (This step also updates the target LASR table with the ‘delete’ rows from the CDC table, identifying them for deletion). Purge the "Delete" rows from the target LASR table.     The CDC Table An example CDC table is shown below. It contains all of the fields of the target LASR table with the latest field values. The table also contains an opcode field with instructions for how to update the target table – insert (‘i’), update (‘u’), or delete (‘d’).     TRANSACTION_TABLE       The OpCode field If you have worked with CDC in the past, the ‘opcode’ on the CDC table is familiar. The operation code (opcode) indicates the type of action to be executed for the data row. In this case, however, we also include the opcode field on the target LASR table as well to facilitate deletes. When performing deletes on target table, we first identify the rows for deletion by changing the opcode field to “d” and then deleting any row with a “d”. The example target table, base_table, with the opcode field is shown below.       BASE_TABLE (The target LASR table)       The SAS Code Steps The CDC update code is described below. Prior to performing the update, the code sets options for LASR and defines LIBNAMEs for source data and LASR.   Step-1 loads the transaction_table (CDC) into LASR server from source environment.   Step-2 appends the data from transaction_table (CDC) to base_table (the target LASR table) where opcode =’i’.   Step-3 deletes and purges rows from transaction_table (CDC) where opcode =’i’. With the insert step completed, we no longer need the insert records.   Step-4 computes a new column ( _where_ ) on transaction_table (CDC) containing a WHERE clause expression. The new column is used by PROC IMTAT to update the base table. The WHERE clause expression is generated using the target table’s key columns, here ‘ID’. For example the computed _where_ field might contain the condition, “ID = 5” indicating that only the row with primary key value of 5 will be updated with the data values on the incoming row. The CDC table with the _where_ column computed is shown below.         Step-5 updates base_table from transaction_table (CDC) based on the computed _where_ column expression. At this stage, transaction_table only contains opcode=’d’ and ‘u’ rows. Only base_table rows that match the WHERE condition will be updated. The column values in transaction_table overwrite the values in base_table for all matching columns. After this step, the update is done but the delete is only half done. All matching “d” rows from transaction_table have updated the corresponding base_table rows with an opcode of “d”. Thus they are identified for deletion.   Step-6 deletes and purges rows from base_table where opcode=’d’ (those identified for deletion in the previous step.       Code: OPTION SET=GRIDINSTALLLOC="./opt/sas/TKGrid"; OPTION SET=GRIDHOST="sasserver01"; libname ld "/opt/sas/data/AppalachianChocolate_datamart"; libname lasr1 sasiola port=10010 tag='HPS';   /** Step-1 **/ /** Load the CDC table to LASR**/ %vdb_dt(lasr1.transaction_table); data lasr1.transaction_table; set ld.transaction_table; run ;   /** Step-2 **/ /**Append the CDC table ‘i’ records to the target LASR table **/ proc imstat; table lasr1.base_table ; set transaction_table ; where opcode='i' ; run;   /** Step-3 ***/ /**Remove the ‘i’ records from the CDC table**/ table lasr1.transaction_table; where opcode='i' ; deleterows / purge; run;   /** Step-4 ***/ /**Create the _where_ field used for update**/ proc imstat; table lasr1.transaction_table; compute _where_ '_where_ = strip("id = " || id)'; run;     /** Step-5***/ /**Update the target LASR table with the ‘u’ and ‘d’ records**/ table lasr1.base_table ; update data=lasr1.transaction_table; run;   /** Step-6***/ /**Delete the target LASR table records with an opcode of ‘d’ (updated in the previous step**/ where opcode='d'; deleterows / purge; run;   *Drop transaction table form LASR server; %vdb_dt(lasr1.transaction_table);         BASE_TABLE (The target LASR table) after code execution:       A Note on Performance LASR update is a very CPU-intensive process and, in cases with large update requirements (e.g. thousands of rows), it’s better to use a SWAP-OUT update than a CDC update strategy. For reference, in our tests, we saw that a 1000 row update on a 10GB (36 Million row) LASR table was orders of magnitude slower than using a swap-out technique.   So, the valid use-cases for LASR CDC update are: 1. Very few updates (10s to 100s of rows) or 2. Memory constraints that won't accommodate the swap.       Note: You can update the values of ORDERBY variables, but you cannot update the value of variables that are used for constructing partition keys.              Reference documents IMSTAT Procedure ... View more

What is the best approach to refresh a very large, stale LASR table (read FACT table) when the source data gets updated? The question is even more important when there is only a narrow downtime window for the update. The standard approach to append/update delta data from the source environment to the LASR table may not help, if you are unable to isolate the changes since the last load.   This post discusses a mechanism for swapping data into a live LASR table from a staged LASR table, minimizing downtime while still doing a complete refresh. The data transfer operation executes in LASR (memory) so it’s super-fast and efficient with minimum down time.     To refresh a stale LASR table from source data quickly, follow these steps: Load the source table into a LASR staging table Delete the records from the target live LASR table Append the staged table to the target live LASR table Drop the staged LASR table   The above steps can be executed using different SAS statements depending on your preferences and exact situation. You can use PROC IMSTAT, SAS Data Step, or PROC IMXFER to swap data into a live LASR table from staged LASR table. Generally, we recommend IMSTAT but Data Step may be preferred by legacy SAS users and IMXFER is useful if you want to use a separate LASR server for staging purposes.       Using PROC IMSTAT The following code demonstrates a data swap into a live LASR table using PROC IMSTAT. In this case, the source data is residing in a .sas7bdat table. The same data could have been in a third party database with a view created using multiple tables to generate the flat FACT table. The initial step in the code is to load the 10GB dataset table into LASR staging table. Then, the PROC IMSTAT DELETEROWS statement deletes and purges all rows from target LASR table. Finally, SET statement appends the staging table rows into target LASR table.   In this case, a 10GB (36M rows) table takes only ~ 8 sec to execute PROC IMSTAT statements to refresh the original table !!!     Note: On the SET statement, the source LASR table does not require a SAS LIBNAME reference. BY default, the SET statement looks for the source data LASR table in same LASR server.     Note 2: If you need a partial data refresh to old LASR table, delete the data rows with valid WHERE clause expression and append the new rows from staged table.       Code with PROC IMSTAT:     OPTION SET=GRIDINSTALLLOC="./opt/sas/TKGrid";   OPTION SET=GRIDHOST="sasserver01";     libname lasr1 sasiola port=10010 tag='HPS';   libname ld "/opt/sas/data/AppalachianChocolate_datamart";     * Drop staging table, if there is any ;   %vdb_dt(lasr1.ORDER_FACT_N);     * Load fresh data sets into a staging ORDER_FACT_N LASR table ;   data lasr1.ORDER_FACT_N ;   set ld.ORDER_FACT_10G;   run ;     * Swap/Refresh old LASR table data from staged LASR table ORDER_FACT_N;   proc imstat    data=lasr1.ORDER_FACT_10G;   where 1=1 ;   run;   deleterows / purge;   run;   table lasr1.ORDER_FACT_10G ;   set ORDER_FACT_N / drop ;   run;           Log extract from code execution:   9   10 * Load fresh data sets into a staging ORDER_FACT_N LASR table ;   11 data lasr1.ORDER_FACT_N ;   12 set ld.ORDER_FACT_10G;   13 run ;   NOTE: There were 36000000 observations read from the data set LD.ORDER_FACT_10G.   NOTE: The data set LASR1.ORDER_FACT_N has 36000000 observations and 24 variables.   NOTE: DATA statement used (Total process time):   real time 59.62 seconds   cpu time 17.07 seconds   14   15 * Swap/Refresh old LASR table data from staged LASR table ORDER_FACT_N;   16 proc imstat data=lasr1.ORDER_FACT_10G;   17 where 1=1 ;   18 run;   19 deleterows / purge;   20 run;   NOTE: A WHERE clause remains active from a previous RUN block: '(1)'.   NOTE: The LASR Analytic Server action request for the DELETEROWS statement   completed in 0.159001 seconds.   21   22 table lasr1.ORDER_FACT_10G ;   NOTE: The WHERE statement is cleared when you open a LASR Analytic Server table with the   TABLE statement.   23 set ORDER_FACT_N / drop ;   24 run;   NOTE: The LASR Analytic Server action request for the SET statement completed in 2.442395 seconds.   NOTE: The PROCEDURE IMSTAT printed page 1. NOTE: PROCEDURE IMSTAT used (Total process time):   real time 7.89 seconds   cpu time 2.72 seconds         Using SAS data step DATA Step can also be used to refresh LASR tables in the same manner. When running SAS data step in LASR server, be sure to include “options dsaccel=any” in code, otherwise it will pull the LASR data to SAS Workspace server to process.     Code with SAS data step:     OPTION SET=GRIDINSTALLLOC="/opt/sas/TKGrid";   OPTION SET=GRIDHOST="sasserver01";   options dsaccel=any msglevel=i;     libname lasr1 sasiola port=10010 tag='HPS';   libname ld "/opt/sas/data/AppalachianChocolate_datamart";     * Drop staging table, if there is any ;   %vdb_dt(lasr1.ORDER_FACT_N);     * Load fresh data sets into a staging ORDER_FACT_N LASR table ;   data lasr1.ORDER_FACT_N ;   set ld.ORDER_FACT_10G;   run ;     * unload original old LASR table from LASR server;    %vdb_dt(lasr1.ORDER_FACT_10G);     * Swap/Refresh old LASR table data from staged LASR table ORDER_FACT_N;   data lasr1.ORDER_FACT_10G ;   set lasr1.ORDER_FACT_N;   run ;     * Drop staging table;   %vdb_dt(lasr1.ORDER_FACT_N);         Using PROC IMXFER Finally, PROC IMXFER can perform the same logic except that it executes across two LASR servers. When using IMXFER, you write the staging table to a separate LASR server (different port). Using a separate LASR server for staging, can help minimize the impact of your back-end processing from your front-end VA users     Code with PROC IMXFER:     OPTION SET=GRIDINSTALLLOC="/opt/sas/TKGrid";   OPTION SET=GRIDHOST="sasserver01";     libname lasr1 sasiola port=10010 tag='HPS';   libname lasr2 sasiola port=10031 tag='HPS';   libname ld "/opt/sas/data/AppalachianChocolate_datamart";     * Drop staging table, if there is any ;   %vdb_dt(lasr2.ORDER_FACT_N);     * Load fresh data sets into a staging ORDER_FACT_N LASR table differnt LASR server port ;   data lasr2.ORDER_FACT_N ;   set ld.ORDER_FACT_10G;   run ;     * unload original old LASR table from LASR server;   %vdb_dt(lasr1.ORDER_FACT_10G);     * Transfer data from stagged LASR table to old LASR table, both LASR table on different server port ;   proc imxfer;   server s1 host="sasserver01.race.sas.com" port=10010;   server s2 host="sasserver01.race.sas.com" port=10031;   table s2 hps.ORDER_FACT_N s1 hps.ORDER_FACT_10G;   quit;     * Drop staging table from LASR ;   %vdb_dt(lasr2.ORDER_FACT_N);         A Note on Memory Requirements     The staging table load and swap-out process briefly requires twice the normal LASR table memory storage since there will be two copies of the table in memory, the staged one and the one being updated. Once the staging table is dropped by IMSTAT, this requirement goes away.         Reference documents IMSTAT Procedure IMXFER Procedure Data step programming for LASR ... View more

As an Impala user, we ask this question, what is the most efficient way to import tables from Impala to LASR? Can the SAS Embedded Process (EP) be used for this purpose? In this post, I talk about loading tables into LASR from Impala using Impala and a Hadoop (Hive) engine with SAS EP.   Impala is a distributed, Massively Parallel Processing (MPP) SQL query engine that enables users to issue low-latency SQL against data stored in Hive, HDFS, and Apache HBase without requiring data movement or transformation.     Impala executes SQL queries natively without translating them into MapReduce jobs.   Impala and Hive share the same metadata repository, which enables users to read Impala tables from Hive and vice versa.   Considering the shared metadata repository between Impala and Hive, the Impala tables can be read from SAS using both SAS/ACCESS to Impala and SAS/ACCESS to Hadoop (Hive) engines. So, which method is more efficient for importing Impala tables to LASR?     SAS/ACCESS to Impala is an ODBC connection to Impala (Hadoop). It does not support interactions with SAS EP on a Hadoop cluster. One of the reasons that the Impala engine cannot interact with SAS EP is because Impala executes SQL queries natively whereas SAS EP works with MapReduce. When using an Impala engine to read Impala tables in order to load them into LASR, the process is unable to request SAS EP to schedule the MapReduce task that loads into LASR in parallel mode. Hence, the process loads data into LASR using a single channel between NameNode and LASR RootNode.     As Impala shares the metadata repository with Hive, Hive can read Impala tables in the same way as it reads any other Hive table. This feature enables SAS/ACCESS to Hadoop (Hive) engine to read Impala tables and uses the SAS Embedded Process to load into LASR using parallel data feeder threads. Only, HPA PROCs (PROC LASR and HPDS2) can trigger parallel data loads into LASR using SAS EP. As a result, it seems that using the SAS/ACCESS to Hadoop (Hive) engine may be more efficient.     The example below shows a table list from both the Impala Shell and Hive prompts. Both lists show the same number of tables.   Table list from Impala shell prompt:   [sascdh01.xxxx.xxx.xxx:21000] > show tables; Query: show tables +---------------------+ | name | +---------------------+ | active_customers | | cars | | cars_impala | | cust_hv | | customers | | customers_gold | | order_fact_hive | | order_fact_impala | | order_fact_impala_s | | stocks | +---------------------+ Fetched 10 row(s) in 0.01s [sascdh01.xxx.xxx.xxx:21000] >       Table list from Hive prompt: hive> show tables; OK active_customers cars cars_impala cust_hv customers customers_gold order_fact_hive order_fact_impala order_fact_impala_s stocks Time taken: 0.704 seconds, Fetched: 10 row(s) hive>         Sample Code to load an Impala table into LASR using Impala engine.   OPTION SET=GRIDHOST="sasserver01"; OPTION SET=GRIDINSTALLLOC="/opt/sas/TKGrid_2.9/CDH520_REP/TKGrid_REP"; OPTION SET=GRIDRSHCOMMAND="/usr/bin/ssh";   libname HDFS IMPALA server="sascdh01.xxxx.xxx.com" user=hadoop port=21050 database=default ; libname lasr_ep sasiola port=54321 tag='HDFS';   * Drop existing table; %vdb_dt(lasr_ep.order_fact_impala_s); * Load to LASR ; proc lasr add port=54321 data=HDFS.order_fact_impala_s ; run;         Resource utilization history while loading an Impala table into LASR using an Impala engine     While the data load is running using an Impala engine, it has been noticed that the LASR RootNode is performing major activities with Network-In and Network-out traffic. The LASR WorkerNode is not so busy and is just engaged in network-in traffic.         Sample code to load an Impala table into LASR using a Hadoop (Hive) engine   OPTION SET=GRIDHOST="sasserver01"; OPTION SET=GRIDINSTALLLOC="/opt/sas/TKGrid_2.9/CDH520_REP/TKGrid_REP"; OPTION SET=GRIDRSHCOMMAND="/usr/bin/ssh"; OPTION SET=SAS_HADOOP_JAR_PATH="/opt/sas/thirdparty/Hadoop_Jars/CDH520"; OPTION SET=SAS_HADOOP_CONFIG_PATH="/opt/sas/thirdparty/Hadoop_Config/CDH520";   libname HDFS hadoop user=hadoop server="sascdh01.xxxx.xxx.com" port=10000 SUBPROTOCOL=hive2 ;   libname lasr_ep sasiola port=54321 tag='HDFS';   * Drop existing table; %vdb_dt(lasr_ep.order_fact_impala); * Load to LASR ; proc lasr add port=54321 data=HDFS.order_fact_impala ; run;       Resource utilization history while loading an Impala table into LASR using a Hadoop (Hive) engine     While the data load is running using a Hadoop (Hive) engine, it has been noticed that the LASR WorkerNodes are engaged in most of the activity. The LASR RootNode is minimally used as data is passing directly from DataNodes to LASR WorkerNodes. The Network-in traffic on LASR WorkerNodes indicates parallel data load from the Hadoop cluster through SAS EP.         Summary The most efficient way to load Impala table into LASR is using SAS/ACCESS to Hadoop (Hive) engine in conjunction with SAS Embedded Process configured at the Hadoop cluster. ... View more