Paper SAS3960-2016 An Insider’s Guide To SAS/ACCESS .

The PORT option specifies which port Impala is listening on. 21050 is the default, so it is notrequired. It is included just in case. USER and PASSWORD are not always required. For example, the Cloudera Quickstart VM has nosecurity enabled. That being said, some ODBC drivers require a user name and password by default.Later in this paper, you will see an example of how to override the settings of your ODBC driver.There are times when you might need to override Impala query options. For example, you might have aquery that is very resource intensive. Let’s assume that your query needs additional memory in order torun quickly (or at all). Through experimentation, you have determined that 64GB is the perfect value. Youcan change the default value using a HiveQL SET statement. The specific option you would use isMEM LIMIT . Fortunately, you can do this in SAS using the DBCONINIT LIBNAME option:libname myimp impala server "quickstart.cloudera"user cloudera password clouderadbconinit "set mem limit 1g";Note: According to the Cloudera documentation, MEM LIMIT is probably the most used option.There might be times when you would like to issue multiple SET statements for a specific library.Unfortunately, specifying multiple SET statements separated by semi-colons does not work with thesupported drivers.The following example does not work:/* This does NOT work */libname myimp impala server "quickstart.cloudera"user cloudera password clouderadbconinit "set mem limit 1g;set disable unsafe spills true";Note: We have passed this information along to the driver vendors. Hopefully, specifying multiple SETstatements using the DBCONINIT option will work in the very near future.What would you do if you needed to override an ODBC driver option and there is no corresponding SASLIBNAME statement option? It seems like it would be difficult, right? Fortunately, the CONOPTS optionenables you to override ODBC driver settings.As previously mentioned, the DataDirect driver requires both a user ID and a password by default. TheCloudera Quickstart VM does not have security enabled, so we should not have to specify a user ID andpassword. Fortunately, there is a way around it. The following SAS LIBNAME statement tells theDataDirect driver to not require authentication parameters:libname myimp impala server "quickstart.cloudera"driver vendor datadirectconopts 'AuthenticationMethod -1';There are a couple of items to notice here. First, the DRIVER VENDOR option is used to specify whichODBC the connection will use. Second, the AuthenticationMethod ODBC driver option is set to -1. Thissetting tells the driver that the connection requires no authentication. Granted, this is not a great examplebut it will serve our purposes. This is an insider trick that might serve you well in the future.You might be asking yourself, “How do I discover the available options for a specific driver?” We touchedon this during our brief discussion of the odbc.ini file (and Windows registry entry). You can find thesevalues in the odbc.ini file on UNIX or in the Windows registry. Although this paper provided a list, be sureto do some research. There could be new parameters available.Implicit Pass-ThroughSAS/ACCESS Interface to Impala generates HiveQL and sends it to Impala. Implicit pass-throughreferences the Impala table via a LIBNAME statement. Here is an example:6

libname myimp impala server "quickstart.cloudera"user cloudera password cloudera;data work.cars;set myimp.cars;run;You can use the SASTRACE option to see the SQL that is being passed to the database:options sastrace ',,,d' sastraceloc saslog nostsuffix;Tip: If you use SAS/ACCESS products, you should memorize this SASTRACE statement.This example will serve you well 99% of the time. If you would like to learn more about SASTRACE , seeSASTRACE: Your Key to RDBMS Empowerment by Andrew Howell. (See link in the References sectionof this paper.)Joins sometimes present a problem. SAS is capable of passing implicit joins to a database if certainconditions are met. We will discuss this shortly.Explicit Pass-ThroughSAS/ACCESS Interface to Impala takes the HiveQL that you write and passes it, unchecked, to Impala.This is done via PROC SQL. If there is a CONNECT statement, explicit SQL pass-through is being used.Here is an example:proc sql;connect to impala (server "quickstart.cloudera"user cloudera password cloudera);execute(create mytable(mycol varchar(20)) by impala;disconnect from impala;quit;If you want to use explicit pass-through to issue an SQL SELECT statement, you must play a trick. I amincluding it here, just for fun:proc sql;connect to impala (server "quickstart.cloudera"user cloudera password cloudera);select * from connection to impala(select * from mytable);quit;Note: If the SELECT statement is embedded in an EXECUTE statement, the code will run, but nothingwill be returned. This is because there is no “place” to put the results.Explicit pass-through is very useful. For example, suppose you want to see how your Impala table isdefined. You can use the HiveQL DESCRIBE FORMATTED statement:proc sql;connect to impala (server "quickstart.cloudera"user cloudera password cloudera);select * from connection to impala(describe formatted strcollngtest);quit;Notice the “select * from connection to” trick is required to get this to work.7

JOINSChapter 5 of SAS/ACCESS 9.4 for Relational Databases Reference discusses under what circumstancesjoin processing is passed down to the database. I highly recommend that you read this. In the meantime,let’s discuss one of the most common reasons that joins are not passed to the database for processing.Remember: In order for join processing to be eligible for push-down, the following options must beconsistent between the LIBNAME statements: SERVER PORT USER PASSWORD Notice that the SCHEMA option is not included in the list. Can this option have different values and stillproduce a join that is passed to Impala? Let’s experiment and see what happens. This code is simple butuseful. You can use it as a test for many join situations. The following code will provide us with ananswer:/* create two schemas for testing */proc sql;connect to impala (server "quickstart.cloudera" user clouderapassword cloudera);execute(create schema schema1) by impala;execute(create schema schema2) by impala;quit;/* Assign two libraries with different schemas */libname imp1 impala server "quickstart.cloudera" user clouderapassword cloudera schema schema1;libname imp2 impala server "quickstart.cloudera" user clouderapassword cloudera schema schema2;/* Create two simple tables */data imp1.table1;x 3; output;x 2; output;x 1; output;run;data imp2.table2;x 3; y 3.3; z 'three'; output;x 2; y 2.2; z 'two'; output;x 1; y 1.1; z 'one'; output;x 4; y 4.4; z 'four'; output;x 5; y 5.5; z 'five'; output;run;/* Display the generated SQL */options sastrace ',,,d' sastracelog saslog nostsuffix;proc sql;selectfrom,wherequit;i1.x, i2.y, i2.zimp1.table1 i1imp2.table2 i2i1.x i2.x;8

This code creates two Impala schemas, two SAS libraries, two tables, and joins the tables. Output 1shows that the join was passed to Impala for processing.5556575859proc sql;selectfrom,wherei1.x, i2.y, i2.zimp1.table1 i1imp2.table2 i2i1.x i2.x;IMPALA 37: Prepared: on connection 2SELECT * FROM schema1 . table1 IMPALA 38: Prepared: on connection 3SELECT * FROM schema2 . table2 IMPALA 39: Prepared: on connection 2select i1. x , i2. y , i2. z from schema1 . table1 i1, schema2.table2i2 where i1. x i2. x NOTE: Writing HTML Body file: sashtml.htmIMPALA 40: Executed: on connection 2Prepared statement IMPALA 39ACCESS ENGINE: SQL statement was passed to the DBMS for fetching data.60quit;NOTE: PROCEDURE SQL used (Total process time):real time0.78 secondscpu time0.10 secondsOutput 1. SASTRACE Output from a Join That Was Passed to ImpalaStarting in the third maintenance release for SAS 9.4 (SAS 9.4M3), a message in the log tells you if theSQL statement was passed to the database for processing. It is a good idea to review your SAS logs andmake sure that the database is doing as much processing as possible.One of the most common questions that I am asked is, “Will this join be passed to the database?” Theinformation detailed above enables you to answer that question easily.SAS PROCEDURE PUSH-DOWNSAS/ACCESS Interface to Impala supports in-database processing support for the following SASprocedures: FREQ MEANS RANK REPORT SORT SUMMARY9

TABULATEIn this example, the RANK procedure pushes processing into Impala:/* LIBNAME statement must include SCHEMA */libname myimp impala server "quickstart.cloudera" user clouderapassword cloudera schema 'default';options sastrace ',,,d' sastraceloc saslog nostsuffix;/* Create an Impala table */data myimp.class;set sashelp.class;run;/* Run rank procedure in Impala *//* LIBNAME statement must include SCHEMA option */proc rank data myimp.class out work.class rank;by descending weight;run;Output 2 shows the SAS log output when the above code is run.78libname myimp impala server "quickstart.cloudera" user clouderapassword XXXXXXXX78 ! schema 'default';NOTE: Libref MYIMP was successfully assigned as follows:Engine:IMPALAPhysical Name: quickstart.clouderaIMPALA:Called SQLTables with schema of defaultIMPALA 52: Prepared: on connection 0SELECT * FROM default . class 798081proc rank data myimp.class out work.class rank;by descending weight;run;NOTE: PROCEDURE SQL used (Total process time):real time0.01 secondscpu time0.01 secondsIMPALA 53: Prepared: on connection 5SELECT table0 . name , table0 . sex , table1 . rankalias0 AS age , table2 . rankalias1 AS height , table0 . weight FROM ( SELECT age AS age , height AS height , name AS name , sex AS sex , weight AS weight FROM class ) AS table0 LEFT JOIN (WITH subquery0 AS(SELECT weight , age , tempcol0 AS rankalias0 FROM ( SELECT weight , age , AVG( tempcol1 ) OVER ( PARTITION BY weight , age ) AS tempcol0 FROM(SELECT weight , age ,10

CAST( ROW NUMBER() OVER ( PARTITION BY weight ORDER BY age ) AS DOUBLE) AS tempcol1 FROM (SELECT age AS age , height AS height , name AS name , sex AS sex , weight AS weight FROM class ) AS subquery2 WHERE ( ( age IS NOT NULL ) ) ) AS subquery1 ) AS subquery0 ) SELECT DISTINCT weight , age , rankalias0 FROM subquery0) AS table1 ON ( ( table0 . age table1 . age ) AND ( ( table0 . weight table1 . weight ) OR ( table0 . weight IS NULL AND table1 . weight IS NULL ) ) ) LEFT JOIN (WITH subquery3 AS(SELECT weight , height , tempcol2 AS rankalias1 FROM ( SELECT weight , height , AVG( tempcol3 ) OVER ( PARTITION BY weight , height ) AS tempcol2 FROM(SELECT weight , height , CAST( ROW NUMBER() OVER ( PARTITION BY weight ORDER BY height ) AS DOUBLE ) AS tempcol3 FROM ( SELECT age AS age , height AS height , name AS name , sex AS sex , weight AS weight FROM class ) AS subquery5 WHERE ( ( height ISNOT NULL ) ) ) AS subquery4 ) AS subquery3 ) SELECT DISTINCT weight , height , rankalias1 FROM subquery3 )AS table2 ON ( ( table0 . height table2 . height ) AND ( ( table0 . weight table2 . weight ) OR ( table0 . weight IS NULL AND table2 . weight ISNULL ) ) ) ORDER BY table0 . weight DESC NULLS LASTIMPALA 54: Executed: on connection 5Prepared statement IMPALA 53NOTE: SQL generation was used to perform the ranking.NOTE: The data set WORK.CLASS RANK has 19 observations and 5 variables.NOTE: PROCEDURE RANK used (Total process time):real time2.00 secondscpu time0.12 secondsOutput 2. SAS Log Output from a Running the RANK Procedure Test CodeNotice: The generated SELECT statement pushed the processing into Impala. There is also a NOTEstatement, which tells us that the database performed the work.BULK LOADINGSAS/ACCESS Interface to Impala includes the capability to rapidly load data into Hadoop distributionswhich are fully supported by SAS. Review the systems requirements for details. To do this, SAS/ACCESSInterface to Impala by-passes the ODBC driver and writes directly to the Hadoop distributed file system(HDFS). This is an extremely important feature. If you routinely move data from SAS to Hadoop, youshould use this.Bulk loading is special, and in order to get it to work, there is special configuration. Many SAS users donot know that PROC HADOOP and the Hadoop FILENAME statement are included in Base SAS. If yoursystem has been configured for bulk loading, this functionality will be available to you. If your environmenthas been configured for PROC HADOOP and the Hadoop FILENAME statement, your bulk loading willwork.11

Unfortunately, it is easy to make a mistake that prevents this from working. We will not go into the detailsof how to configure the environment, but we will discuss it briefly.To use bulk loading (and PROC HADOOP and the Hadoop FILENAME statement) with SAS/ACCESSInterface to Impala, you must set these environment variables:SAS HADOOP JAR PATH SAS communicates with your Hadoop cluster via Java Archive files (JAR).These JAR files must be available to your SAS install. This environment variable points to a directorywhere these JAR files are saved.SAS HADOOP CONFIG PATH In order to connect to Hadoop, SAS needs to know about your cluster.This environment variable points to a directory with the XML files that contain the information SAS needsto connect, such as server names, port numbers, and settings for many Hadoop parameters. It isimportant to realize that you can override many cluster settings by changing the values in these XML files.SAS HADOOP RESTFUL I really like this environment variable. Setting SAS HADOOP RESTFUL 1tells SAS to use WebHDFS when it communicates with HDFS. WebHDFS is an HTTP REST API. UsingWebHDFS limits the reliance on JAR files. This is a useful debugging tool. If bulk loading fails, you mightwant to set this environment variable to 1. If it fixes the problem, you have a JAR error.At this point I imagine you are asking, “How do I get these JAR and XML files?” I like to point out that anormal user will not have to worry about this. Your SAS administrator will handle it for you. But what if youare the SAS administrator? Fortunately, there is a simple answer – the SAS Deployment Manager. Wecannot cover this topic here. You can read all about it in the SAS 9.4 Hadoop Configuration Guide forBase SAS and SAS/ACCESS. (See the link in the References section of this paper.)After your environment is ready, you will want to test it. Let’s run through a very simple example.This code creates an Impala/Hive table and uses bulk loading to populate it with data. I am using theCloudera Quickstart 5.5 VM. The BULKLOAD YES data set option tells SAS to use bulk loading.libname myimp impala server "quickstart.cloudera"user cloudera password cloudera;data myimp.cars (bulkload yes);set sashelp.cars;run;Here are the steps that the Impala engine will use to create the Cars table and bulk load it with data:1. SAS issues two CREATE TABLE statements. The first one creates the Impala table. The secondstatement creates a temporary table.2. SAS uploads the Cars data into a UTF-8 delimited text file that lives in the HDFS /tmp directory.It is common for this to fail the first time it is done (on the system). The cause: you don’t haveproper permissions on the /tmp directory.3. SAS issues a LOAD DATA Hive statement to move the data file from the HDFS /tmp directoryinto the temporary file.4. SAS issues an INSERT INTO statement that inserts the data into the Impala table.5. SAS deletes the temporary table.Output 3 shows the SAS log from a working bulk load session. I added comments that describe theprocessing.13data myimp.cars (bulkload yes);14set sashelp.cars;15run;IMPALA 4: Prepared: on connection12

SELECT * FROM cars WHERE 0 1NOTE: SAS variable labels, formats, and lengths are not written to DBMStables./*CREATE IMPALA TABLE */IMPALA 5: Executed: on connection 2CREATE TABLE cars ( Make VARCHAR(13), Model VARCHAR(40), Type VARCHAR(8), Origin VARCHAR(6), DriveTrain VARCHAR(5), MSRP double, Invoice double, EngineSize double, Cylinders double, Horsepower double, MPG City double, MPG Highway double, Weight double, Wheelbase double, Length double)/*CREATE TEMPORARY TABLE */IMPALA 6: Executed: on connection 2CREATE TABLE bl cars 1770654674(Make VARCHAR(13),Model rain VARCHAR(5),MSRP double,Invoice double,EngineSizedouble,Cylindersdouble,Horsepower double,MPG City double,MPG Highway double,Weightdouble,Wheelbase double,Lengthdouble) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' LINES TERMINATEDBY '\012' STORED ASTEXTFILE/* SAS MOVED DATA INTO HDFS */NOTE: There were 428 observations read from the data set SASHELP.CARS.NOTE: The data set MYIMP.cars has 428 observations and 15 variables./* SAS ISSUED LOAD DATA STATEMENT TO MOVE DATA FROM /tmp INTO TEMP TABLE */IMPALA 7: Executed: on connection 2LOAD DATA INPATH '/tmp/bl cars 1770654674.dat' INTO TABLEbl cars 1770654674/* COPY DATA FROM TEMP TABLE INTO IMPALA TABLE */IMPALA 8: Executed: on connection 2INSERT INTO cars gineSize,Cylinders,Horsepower,MPG City,MPG Highway,Weight,Wheelbase,Length) ce,EngineSize,Cylinders,Horsepower,MPG City,MPG Highway,Weight,Wheelbase,Length FROM bl cars 1770654674/* DELETE THE TEMP TABLE */IMPALA 9: Executed: on connection 2DROP TABLE bl cars 1770654674NOTE: DATA statement used (Total process time):real time12.42 secondscpu time0.10 secondsOutput 3. Output from a SAS/ACCESS Interface to Impala Bulk Load13

Output 4 shows the results of the same job without bulk loading.IMPALA: Called SQLTables with schema of NULL20data myimp.cars;21set sashelp.cars;22run;IMPALA 12: Prepared: on connection 1SELECT * FROM cars WHERE 0 1NOTE: SAS variable labels, formats, and lengths are not written to DBMStables.IMPALA 13: Executed: on connection 2CREATE TABLE cars ( Make VARCHAR(13), Model VARCHAR(40), Type VARCHAR(8), Origin VARCHAR(6), DriveTrain VARCHAR(5), MSRP double, Invoice double, EngineSize double, Cylinders double, Horsepower double, MPG City double, MPG Highway double, Weight double, Wheelbase double, Length double)/* IF YOU SEE LOTS OF QUESTION MARKS, THERE WAS NO BULK LOADING */IMPALA 14: Prepared: on connection 2INSERT INTO cars ( Make , Model , Type , Origin , DriveTrain , MSRP , Invoice , EngineSize , Cylinders , Horsepower , MPG City , MPG Highway , Weight , Wheelbase , Length ) VALUES ( ? , ? ,? , ? , ? , ? , ? , ?, ? , ? , ? , ?

using Hive. Fortunately, there is a low-latency alternative to Hive – Apache Impala. . Open Database Connectivity (ODBC) is a standard that was created by Microsoft. (ODBC 1.0 was . Windows Registry Entry for Cloudera Impala ODBC Driver Registry Entry