SQL Pass-Through Facility Specifics for Hadoop

Connect Using an Already Assigned Libref

When a Hadoop libref is already assigned, you can use PROC SQL’s CONNECT USING syntax to avoid re-typing the connection options.

/* libref x is already assigned */
 proc sql;
    connect using x;
     /* get the HiveQL version */
    select * from connection to x (select version () );
     /* create a table using HiveQL, not SAS SQL */
    execute (create table if not exists test1 (i integer) ) by x;
    disconnect from x;
  quit; 

Connect Using Connection Parameters

If a Hadoop libref is not available, you can connect to Hadoop using PROC SQL’s CONNECT TO syntax. You must provide the same connection parameters that are required in the LIBNAME statement in the CONNECT TO statement.

This example uses the CData JDBC driver for Apache Hive.

proc sql;
   connect to hadoop as x (
   driverClass="cdata.jdbc.hive.ApacheHiveDriver"
     url='jdbc:apachehive:Server=cdp71p1hive;QueryPassthrough=True;
Database=mydatabase;DefaultColumnSize=1024'
     user=myuser1
     password=mypwd1
);
   /* get the HiveQL version */
    select * from connection to x (select version () );
    /* create a table using HiveQL, not SAS SQL */
    execute (create table if not exists test1 (i integer) ) by x;
     disconnect from x;
 quit;