Radek Kotowicz

There are already a few blog posts around (e.g.
SAP Hana and r — the way of the widget), which mention that it's possible and, in fact, quite easy to connect to SAP HANA from Perl code using ODBC DBI drivers. As I'm planning to give a bit more specialized talk at this year's YAPC in Granada I thought it would be useful to describe the setup in detail and provide some references. In the follow-up posts I'll draw more on the topic of my upcoming talk: how ad-hoc OLAP DBs can be conjured with Yertl. BTW, I should probably add "yet another" to the prefix to the title of my blog post, to follow the widely accepted manner of affirming the prevalence of already existing ideas ;)

The setup described in this post has been tested on Win7 and Active Perl 5.16. You should be able to get the same working on any UN*X platform with the help of unixODBC, which comes with a number of helpful GUI tools, such as Data Source Admin

While it is possible to run HANA behind the firewall on a number of HW/SW platforms my interest is focused on the cloud option because of scalability and zero setup time.

Before we start setting up a connection, let's take a look at the cloud architecture:

Since we're connecting to the DB that is behind the firewall and we're likely behind one too we need to open a tunnel first. To do that we'll use the neo console client, which comes with the SDK. I've got a small batch file that initiates the tunnel (through a http proxy additionally):


set HTTPS_PROXY_HOST=proxy
set HTTPS_PROXY_PORT=8080
neo open-db-tunnel -h hanatrial.ondemand.com -u radoslaw.kotowicz@sap.com -a i0999999trial --id quickmove


When the tunnel opens, the client tells us what is the DB username to connect (fixed) the temporary password and the local address to connect to. Interestingly, there is just one local port open and that port is used for both JDBC and ODBC protocols. I initially suspected that the HDB ODBC driver might be a wrapper around the JDBC type 4 driver but ODBC driver is a pure C implementation, so there's apparently a socket multiplexer on the remote end of the tunnel and the ODBC/JDBC traffic is routed to the right listener.

The next steps is to install the ODBC driver. First download the driver from SAP Community Network and run the the setup tool hdbsetup -a client -- the wizard will take you through the installation. More details can be found here. You'll need an SCN account to get the drivers but you're bound to have one already if you had enrolled for a trial cloud HANA account.

Once the drivers have been installed we can set up a Data Source in ODBC Administrator.

Add a new DS with the desired name and provide the local server host name and port number (these can be read from the neo client when it boots up -- see screenshots above).

We don't enter the credentials at this point - the application will authenticate itself when connecting to the data source. Let's note the following:

• you could enter an authentication token if you wanted to skip the authentication on the app level
• data source definition can completely be skipped, in the same way as you can skip the TNS entry when connecting to an Oracle database, but I want to have it for lucidity

Now, finally some Perl:


use DBI;
use Data::Dumper;

my $dbh = DBI->connect('dbi:ODBC:DSN=your_HDB_DSN', 'your_HDB_User', 'your_Temp_Passwd');
print Dumper($dbh->selectall_arrayref(q/select * from dual/));


Coming next:

• Ad-hoc OLAP DBs with YERTL and Hana
• Ideas for presenting OLAP data