Building Translytical Applications Using Hibernate, SAP HANA and Google
Recently, translytical data platforms have been getting a lot of attention. This blog post will illustrate how to leverage the translytical features of the SAP HANA database platform, which not only is a true translytical data platform, but according to Forrester also a leader in that area, using the Hibernate framework in a Java application deployed to the Google Cloud Platform. This post is a follow-up to the SAP HANA Expert Webinar on March 13, 2018.
Hibernate is a very popular open source object-relational mapping tool for the Java programming language. It provides a framework for mapping an object-oriented domain model to a relational database.
Recently, SAP has released an improved version of the SAP HANA Hibernate dialect which unlike the previously existing dialect is feature complete and provides much better support for SAP HANA. In addition, a Hibernate Spatial dialect for SAP HANA has been implemented which provides access to SAP HANA’s geospatial capabilities via Hibernate.
How to get started
Install Apache Maven
Download the SAP HANA JDBC driver
The SAP HANA JDBC driver can be obtained from the SAP HANA, Express Edition download page. After registration you can download the software download manager for your platform if you haven’t done so already.
Using the download manager download the SAP HANA clients package for your platform.
Once the download has completed, extract the downloaded archive and install the SAP HANA clients. You can follow the instructions from the SAP HANA Client Installation and Update Guide.
The JDBC driver (ngdbc.jar), which is installed as part of the SAP HANA client installation, is located at (unless specified otherwise during the installation):
- on Microsoft Windows platforms: C:\Program Files\SAP\hdbclient\
- on Linux and UNIX platforms /usr/sap/hdbclient/
In order to be able to reference the JDBC driver from the Maven pom.xml, it must be installed into the local Maven repository. This can be done by running the following Maven command:
NOTE: Make sure to adjust the path to the JDBC driver JAR before running the command.
> mvn install:install-file -Dfile=C:\Program Files\SAP\hdbclient\ngdbc.jar -DgroupId=com.sap.db.jdbc -DartifactId=ngdbc -Dversion=2.2.36 -Dpackaging=jar
Create a Maven project
Create a new Maven project by running the following command in a command shell:
NOTE: Make sure to adjust the values of the groupId and artifactId parameters before running the command.
> mvn -B archetype:generate -DarchetypeGroupId=org.apache.maven.archetypes -DgroupId=com.mycompany.app -DartifactId=my-app
Add the required dependencies
To use Hibernate in your application you need to add the SAP HANA JDBC driver, Hibernate, and, if required, Hibernate Spatial to your build descriptor. To do this, open the file pom.xml in the root directory of your application and add these dependencies:
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.mycompany.app</groupId> <artifactId>my-app</artifactId> <packaging>jar</packaging> <version>1.0-SNAPSHOT</version> <name>Maven Quick Start Archetype</name> <url>http://maven.apache.org</url> <dependencies> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.11</version> <scope>test</scope> </dependency> <!-- SAP HANA JDBC driver --> <dependency> <groupId>com.sap.db.jdbc</groupId> <artifactId>ngdbc</artifactId> <version>2.2.36</version> </dependency> <!-- Hibernate core --> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-core</artifactId> <version>5.2.15</version> </dependency> <!-- Hibernate Spatial --> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-spatial</artifactId> <version>5.2.15</version> </dependency> </dependencies> </project>
For Hibernate to be able to connect to the database, it needs to be configured properly. This can be done be creating a file called persistence.xml at <project root>/src/main/resources/META-INF:
<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd" version="2.1"> <persistence-unit name="MY_PERSISTENCE_UNIT"> <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider> <!-- Change this to the fully qualified names of your actual entity classes --> <class>com.mycompany.app.my-app.MyEntity</class> <properties> <property name="hibernate.dialect" value="org.hibernate.spatial.dialect.hana.HANASpatialDialect" /> <!-- If you don't need geospatial capabilities you can use the HANA column store dialect --> <!-- <property name="hibernate.dialect" value="org.hibernate.dialect.HANAColumnStoreDialect" /> --> <property name="javax.persistence.jdbc.driver" value="com.sap.db.jdbc.Driver" /> <property name="javax.persistence.jdbc.url" value="jdbc:sap://hxehost:30015/" /> <property name="javax.persistence.jdbc.user" value="MY_USER" /> <property name="javax.persistence.jdbc.password" value="SECRET" /> <property name="hibernate.show_sql" value="true" /> <property name="hibernate.hbm2ddl.auto" value="update" /> </properties> </persistence-unit> </persistence>
After that you can follow the Hibernate JPA tutorial to run the application.
To help you get started with building translytical applications using Hibernate, SAP HANA, and Google there is also a demo application available on GitHub.
About the application
The demo applications illustrates how to manipulate data using the Hibernate dialect for SAP HANA. In addition, the application illustrates how to analyze geospatial data using the Hibernate Spatial dialect for SAP HANA. What makes this a translytical application is the combination of processing transactional data with the ability to perform geospatial analytics on the same data without the need for duplicating or transferring the data. Other analytical features like, for example, SAP HANA’s full-text search capabilities can easily be added.
The applications’s base data set is the San Francisco Police Incidents data set. The application performs analysis on the data to gain insights into the distribution of police incidents around a location in the city. There are two main features:
Visualization of the data as a heat map
The visualization view allows users to get a quick overview where areas / streets with high or low incident rates are located. The view leverages SAP HANA’s geospatial engine and the HANA Hibernate Spatial dialect.
Analysis of the data showing information about individual incidents
The analysis view allows users to get detailed information about the types of incidents and occurrence rates. It also leverages SAP HANA’s geospatial engine and the HANA Hibernate Spatial dialect.
Since the geospatial analysis is based on geospatial coordinates which aren’t easily remembered, there is also an address search feature that can be used to map street addresses to geospatial coordinates.
The address search operates on the San Francisco Addresses data set.
Getting started with developing translytical applications on SAP HANA isn’t all that difficult. Using the demo application as a starting point you can build your own applications using Hibernate as a database abstraction while leveraging the advanced analytical capabilities of the SAP HANA platform.
Through Hibernate you can access the full range of SAP HANA’s transactional capabilities and also make use of SAP HANA’s advanced analytical capabilities either via a Hibernate option (like Hibernate’s geospatial dialect for SAP HANA), or via custom queries written in JPQL/HQL or native SQL.
For more in-depth information about Hibernate, SAP HANA, and deploying the application to the Google Cloud Platform you can watch the webinar again on-demand here.