SQL Pass-Through Facility Specifics for Spark

Connect Using an Already Assigned Libref

When a Spark 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 Spark SQL version */
    select * from connection to x (select version () );
     /* create a table using Spark SQL, not SAS SQL */
    execute (create table if not exists test1 (i integer) ) by x;
    disconnect from x;
  quit; 

Connect Using Connection Parameters

When a Spark libref is not available, you can connect to Spark SQL 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 Databricks JDBC driver. The database connection parameters are assigned to the alias y.

proc sql;
   connect to spark as y (
   driverClass="com.databricks.client.jdbc.Driver"
   bulkload=no
   url='jdbc:spark://server:port//schema;transportMode=http;ssl=1;
   HTTPPath=myHttpPath;AuthMech=3;defaultStringColumnLength=255;useNativeQuery=1'
   user=token
   password=mytoken
);
   /* get the Spark SQL version */
   select * from connection to y (select version () );

   /* create a table using Spark SQL, not SAS SQL */
   execute (create table if not exists test1 (i integer) ) by y;

   disconnect from y;
quit;