GDAL with SAP HANA driver: use it
In case you have missed the news: recently version 3.5 of GDAL has been released, which now includes an OGR database driver for SAP HANA in
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 inspiration), and then explain the steps of how I built the binaries in a separate blog post.
Examples of using GDAL with SAP HANA
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:
ogr2ogr. Please share more examples in the comments, if you get some!
Firstly, let’s check if
HANA driver is built into our GDAL binaries:
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"
Please note that while on macOS both single quotes ‘ and double quotes ” work, I found only double quotes ” working without issues on Windows.
The connection string
"DSN=DBADMIN-VITAL-TRIAL-HC-HDB;SCHEMA=TESTGEO" is the ODBC connection string. I will share its setting a bit later in the post. For now please note that
SCHEMA is the necessary 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
-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 kilometers 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 the 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.
Equivalent command on Windows would be
ogr2ogr --formats | findstr vector | find /c /v ""
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
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
Here is the preview of the data loaded into the table
I have not heard about the West Eifel Volcanic Field in Germany before.
As promised, here is an explanation about ODBC setup as GDAL is using ODBC to connect to the SAP HANA database. It means I have both installed on my laptop with macOS:
SAP HANA Client
SAP HANA Client has been installed in the default location for macOS, ie.
file /Applications/sap/hdbclient/libodbcHDB.dylib /Applications/sap/hdbclient/hdbuserstore LIST
As you can see:
- It is Intel (
x86_64) SAP HANA Client’s ODBC library.
- 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 a
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
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:
Please, do not be confused as I used the same
DBADMIN-VITAL-TRIAL-HC-HDB value for both:
- a key in the HANA user store, and
- a DSN key in the ODBC configuration.
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 describe how I have built GDAL binaries on macOS with Apple M1 (ARM-based) chip.
-Vitaliy, aka @Sygyzmundovych