Hana Connector

Configurations

First of all, we should finish the following steps before you start to use hana connector.

• JDBC Connection details to connect to the SAP HANA

It should be written in form of a regular openLooKeng connector config (eg. hana.properties for a openLooKeng catalog named hana). The file should contain the following contents, replacing the connection properties as appropriate for your setup.

Base property setting:

• Adding SAP HANA Driver

SAP HANA JDBC Driver is not available in common repositories, so you will need to download it from SAP HANA and install manually in your repository. The SAP HANA JDBC Driver (ngdbc.jar) may be installed as part of your SAP HANA client installation or you can download it from the SAP HANA office website. Once you have got the SAP HANA JDBC Driver, you can deploy the jdbc jar file to openLooKeng plugin folder on coordinator and worker nodes. For example, if the jdbc driver file is ngdbc.jar and openLooKeng plugin folder is /usr/lib/openlookeng/lib/plugin, use the following command to copy the library to the plugin folder. cp ngdbc.jar /usr/lib/openlookeng/lib/plugin/hana Restart the coordinator and worker processes and hana connector will work.

• Enable the query push down feature or not.

If you want to enable the connector push down feature for hana connector, you do not need to do any things for hana connector’s push down feature is turn on by default. But you can also set as below:

Multiple Hana Databases or Servers

Please configure another instance of the Hana plugin as a separate catalog if you want to connect to ultiple Hana Databases. To add another SAP HANA catalog, please add another properties file to ../conf/catalog with a different name (making sure it ends in .properties). For example, add a file named hana2.properties to ../conf/catalog to add another connector named hana2.

For there is a SAP HANA connector named hana, each SAP HANA Database’s user can get its available schemas throught the hana connector by running SHOW SCHEMAS:

If you have, you can view the tables own by a SAP HANA Database named data by running SHOW TABLES:

To see a list of the columns in a table named hello in data’s schema, use either of the following:

DESCRIBE hana.data.hello;
SHOW COLUMNS FROM hana.data.hello;

And you can access the hello table in data’s schema:

Mapping Data Types Between openLooKeng and Hana

Hana-to-openLooKeng Type Mapping

openLooKeng support selecting the following SAP HANA Detabase types. The table shows the mapping from SAP HANA data type.

Data type projection table:

Note

smalldecimal mapping: The arrange which do not cause loss of precision is the IEEE 754 double’s exactly representable numbers. Detail in: https://en.wikipedia.org/wiki/Double-precision_floating-point_format.

openLooKeng support creating tables with the following type into a SAP HANA Database. The table shows the mapping from openLooKeng to SAP HANA data types.

openLooKeng-to-Hana function Mapping

The openLooKeng functions which can be mapped to SAP HANA function is listed in the following table. Note: The “$n” is placeholder to present an argument in a function.

Note

DATE_ADD(Unit, $1, $2): When unit is ‘second’,’minute’ or ‘hour’, mapping to ADD_SECONDS. When unit is ‘day’ or ‘week’, mapping to ADD_DAYS. When unit is ‘month’ or ‘quarter’, mapping to ADD_MONTHS. When unit is ‘year’, mapping to ADD_YEARS.

SQL grammar differences between hana and openLooKeng

Such as ‘map’ function in hana sql which is used to transform the row data into column data is not support by openLooKeng sql. But you can use the ‘case’ as an alternative implementation.

For example, if you have a table named SCORES:

SELECT
  NAME,
  SUM(MAP(SUBJECT,'English',SCORE,0)) AS "English",
  SUM(MAP(SUBJECT,'Math',SCORE,0)) AS "Math",
  SUM(MAP(SUBJECT,'Science',SCORE,0)) AS "Science"

In openLookeng, you can use ‘case’ as an alternative implementation:

The other differences between hana and openLooKeng sql grammar, please refer to the official document list below:

When we use the openlk-cli to connect the openLooKeng server and handle the time and timestamp without time zone, the result return to the cli to display will depend on the openlk-cli’s start up configuration. For example, if we start up the openlk-cli with user timezone as:

java -jar -Duser.timezone=Asia/Tokyo -jar ./hetu-cli-010-executable.jar
--client-request-timeout 30m --server ip:8080 --session legacy_timestamp=false

When you handle time and timestamp dependent types, the openlk-cli will display Time dependent type with time zone:

If we start up the openlk-cli without user timezone as:

java -jar ./hetu-cli-010-executable.jar
--client-request-timeout 30m --server ip:8080
--session legacy_timestamp=false --catalog hana2

When you handle time and timestamp dependent types, the openlk-cli will display Time dependent type without time zone. Instead, it will display the UTC/GMT zone:

lk> select current_timezone();
        --------
         +08:00
         (1 row)
lk> select current_time;
          _col0
          ---------------------

But in hana, the time dependent type’s behavior is depend on the hana server. For example, we start up a hana client through jdbc directly:

Hana Connector’s Limitations

For the differences between hana datatype and openLooKeng datatype, there are some limitations when preject the hana data type to the openLooKeng datatype.

Hana’s Smalldecimal Data type

The smalldecimal in hana has a variable precision and zero scale length, but openLooKeng do not support. For the reason about, the openLooKeng translate smalldecimal in hana into double in openLooKeng, and this will cause some precision lose in a special value range.

Hana’s tiny int data type

Tiny int in hana is a 8 bits integer without sign bit and will cause some precision lose in a special value range in openLooKeng.