Connect to Presto in SAS Access Using ODBC

SAS is an integrated system of software that enables you to do everything from accessing data across multiple sources to performing sophisticated analyses and delivering information across your organization. Presto, a powerful distributed in-memory query engine, provides a common SQL Engine for accessing data where it lives whether it be Hadoop, Cassandra, Kafka, MySQL and more.

In this blog we show you how to connect SAS Foundation to Presto using the Simba Presto ODBC driver. With Simba’s Presto ODBC and JDBC Drivers with SQL Connector, analytics applications capable of utilizing ODBC and JDBC to connect to data sources can connect to Presto and take advantage of a modern query and execution engine designed to support the SQL those applications depend on.

Prerequisites

1. Install the ODBC driver on your windows machine using the installation guide. Once installation is complete, configure an ODBC DSN and test connectivity to Presto using the DSN.
2. SAS foundation products are available as individual software modules. SAS Foundation and related software should be installed on your Windows machine. For ODBC connectivity, ensure that you have installed the SAS/Access Supplement for ODBC.

Steps

SAS offers various ways to connect using ODBC, here we connect to Presto ODBC using the CONNECT statement.

proc sql;
connect to odbc as sql1 (dsn='Simba Presto' readbuff=3000);
select * from connection to sql1 (select * from hive.dbf.employee );
quit;

Another way to import data in SAS local table using the CONNECT statement is:

proc sql;
connect to odbc as sql1 (dsn='Simba Presto' readbuff=3000);
create table prestoEmpDB as
select * from connection to sql1 (select * from hive.dbf.employee );
quit;

This will create a table named ‘Prestoempdb’ which can be viewed in the ‘Explorer’ tab.

You are now all set to explore & analyze Presto data in SAS.

Below is the output of the PROC means, which contains mean, Standard deviation, Min& max values.

proc means data=Work.Prestoempdb;
 var bonus;
run;

Download Presto ODBC Driver