GDAL with SAP HANA driver: use it

In case you have missed the news: recently the version 3.5 of GDAL has been released, which now includes an OGR database driver for SAP HANA in autoconf & cmake builds: https://gdal.org/drivers/vector/hana.html. This new functionality was contributed to the GDAL project by SAP HANA multi-model engineering team.

The Geospatial Data Abstraction Library (GDAL) is an open-source computer software library for reading and writing raster and vector geospatial data formats. It also comes with a variety of useful command line utilities for data translation and processing.

As stated above SAP HANA driver is included in cmake builds, so I have built GDAL binaries on my MacBook laptop with Apple M1 (ARM-based) chip.

I would like to show you some examples of using the software with SAP HANA first (thanks to Maksim Rylov from the engineering team of Mathias Kemeter for inspirations), and then explain steps how I built the binaries in a separate blog post.

As mentioned GDAL comes with a variety of useful command line utilities for data translation and processing. Let’s have a look at examples of using two of them: ogrinfo and ogr2ogr. Please share more examples in comments, if you get some!

Firstly, let’s check, if HANA driver is built into our GDAL binaries:

ogrinfo --formats

Secondly, let’s check spatial features loaded into the schema "TESTGEO". In my database there is a table "world-continents" loaded during the previous exercise Import zipped shapefiles into SAP HANA Cloud using Database Explorer.

ogrinfo -ro HANA:'DSN=DBADMIN-VITAL-TRIAL-HC-HDB;SCHEMA=TESTGEO'

The connection string 'DSN=DBADMIN-VITAL-TRIAL-HC-HDB;SCHEMA=TESTGEO' is ODBC connection string. I will share it’s setting a bit later in the post. For now please note that SCHEMA is the necessarily part of it.

Thirdly, let’s check summary-level (hence -so flag) details about "world-continents" table (or a “layer” accordingly to the GDAL terminology).

ogrinfo -ro HANA:'DSN=DBADMIN-VITAL-TRIAL-HC-HDB;SCHEMA=TESTGEO' \ -so world-continents

Without the -so flag the command will additionally output values of all features too, like

You can limit values outputted by using -sql parameter, eg. only continent name and size in square miles and kilometres for continents smaller than 5,000,000 sq miles.

ogrinfo -ro HANA:'DSN=DBADMIN-VITAL-TRIAL-HC-HDB;SCHEMA=TESTGEO' \ -sql 'SELECT CONTINENT, SQMI, SQKM FROM "world-continents" WHERE SQMI<5000000'

At the time of writing this post there is a number of spatial data formats that SAP HANA database can import, incl. ESRI Shapefiles or Geohash. ogr2ogr makes it possible to import/export in all spatial vector formats supported by the binary, in my case 66 formats, incl. KML:

ogr2ogr --formats | grep vector | wc -l

In an example I used before I have used ESRI Shapefile from samples shared by Esri. In the same sample you can find data in a different format: Keyhole Markup Language, or KML.

I have downloaded those Esri samples to a local ~/ProjectsLocal/arcgis-runtime-samples-data folder, and it includes a file with volcanoes in the KML format:

ogrinfo -ro ~/ProjectsLocal/arcgis-runtime-samples-data/kml/Volcanoes.kml

<img />

…and I can see there are 3 volcano areas in the Western Europe:

ogrinfo -ro ~/ProjectsLocal/arcgis-runtime-samples-data/kml/Volcanoes.kml \ -so 'Western Europe'

Let’s load them into my SAP HANA database:

ogr2ogr -f "HANA" HANA:'DSN=DBADMIN-VITAL-TRIAL-HC-HDB;SCHEMA=TESTGEO' \ -t_srs EPSG:4326 \ ~/ProjectsLocal/arcgis-runtime-samples-data/kml/Volcanoes.kml \ "Western Europe"

Please note the Spatial Reference System transformation to EPSG:4326 done using -t_srs flag.

Here is the preview of the data loaded into the table TESTGEO."WESTERN EUROPE":

I have not heard about the West Eifel Volcanic Field in Germany before.

ODBC setup

As promised above, here is an explanation about ODBC setup as GDAL is using ODBC to connect to SAP HANA database. It means I have both installed on my laptop with macOS:

  1. SAP HANA Client, and
  2. unixODBC.

SAP HANA Client

SAP HANA Client has been installed in the default location for macOS, ie. /Applications/sap/hdbclient/.

file /Applications/sap/hdbclient/libodbcHDB.dylib
/Applications/sap/hdbclient/hdbuserstore LIST

As you can see:

  1. It is Intel (x86_64) SAP HANA Client’s ODBC library.
  2. I have created a HANA User Store entry with a key DBADMIN-VITAL-TRIAL-HC-HDB, that allows me to connect to my HANA database in SAP HANA Cloud as an DBAdmin user.

unixODBC

At the time of writing this blog post SAP HANA Client is available for macOS on Intel only, so I needed both binaries in Intel versions. Otherwise, mixing Intel binary of SAP HANA Client with ARM binary of unixODBC resulted in the error like:
ERROR 1: HANA connection failed: ERROR: 0: 01000 : [unixODBC][Driver Manager]Can't open lib '/Applications/sap/hdbclient/libodbcHDB.dylib' : file not found

So, I have brew installed in an Intel-compatible architecture session, the same as I have described in Running hdbcli on an Apple M1 chip: an alternative way with using arch command.

Then I used this Intel-based brew to install Intel-compatible unixodbc on i386 emulated architecture.

arch
which brew
brew info unixodbc
file $(which odbcinst)
odbcinst -j

ODBC’s user data sources are configured in ~/.odbc.ini file. Here is mine:

cat ~/.odbc.ini

Please, do not be confused as I used the same DBADMIN-VITAL-TRIAL-HC-HDB value for both:

  • a key in HANA user store, and
  • a DSN key in the ODBC configuration.

You can find more information about ODBC driver for SAP HANA in Daniel Van Leeuwen‘s tutorial “Connect Using the SAP HANA ODBC Driver“.


That’s the end of the review using GDAL 3.5+ software with SAP HANA database to process spatial data. In a separate post I will describe how I have built GDAL binaries on macOS with Apple M1 (ARM-based) chip.

Regards,
-Vitaliy, aka @Sygyzmundovych