Hana Connector

Overview

The Hana connector allows querying and creating tables on an external Hana database. This can be used to join data between different systems like Hana and Hive, or between two different Hana instances.

Configurations

Basic configuration

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.

Querying Hana through openLooKeng

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:

SHOW SCHEMAS FROM hana;

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

SHOW TABLES FROM hana.data;

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:

SELECT * FROM hana.data.hello;

The connector’s privileges in these schemas are those of the user configured in the connection properties file. If the user does not have access to these tables, the specific connector will not be able to access them.

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:

SAP HANA Detabase type	openLooKeng type	Notes
DECIMAL(p, s)	DECIMAL(p, s)
SMALLDECIMAL	DOUBLE	See smalldecimal mapping
TINYINT	TINYINT
SMALLINT	SMALLINT
INTEGER	INTEGER
BIGINT	BIGINT
REAL	REAL
DOUBLE	DOUBLE
FLOAT(n)	n<25 -> real, 25<=n<=53 or n is not declared -> double
BOOLEAN	BOOLEAN
VARCHAR	VARCHAR
NVARCHAR	VARCHAR
ALPHANUM	CHAR
SHORTTEXT	CHAR
VARBINARY	VARBINARY
DATE	DATE
TIME	TIME
TIMESTAMP	TIMESTAMP
SECONDDATE	NA
BLOB	NA
CLOB	NA
NCLOB	NA
TEXT	NA
BINTEXT	NA

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-to-Hana Type Mapping

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 type	SAP HANA Detabase type	Notes
BOOLEAN	BOOLEAN
TINYINT	TINYINT
SMALLINT	SMALLINT
INTEGER	INTEGER
BIGINT	BIGINT
REAL	REAL
DOUBLE	DOUBLE
DECIMAL	DECIMAL
VARCHAR	VARCHAR
CHAR	CHAR
VARBINARY	VARBINARY
JSON	NA
DATE	DATE
TIME	TIME
TIME WITH TIME ZONE	NA
TIMESTAMP	TIMESTAMP
TIMESTAMP WITH TIME ZONE	NA

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.

openLooKeng function | HANA function	notes
DATE_ADD(Unit, $1, $2)	ADD_SECONDS($2, $1) or ADD_DAYS($2, $1) or ADD_MONTHS($2, $1) or ADD_YEARS($2, $1)	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.
CORR($1,$2)	CORR($1, $2)
STDDEV($1)	STDDEV($1)
VARIANCE($1)	VAR($1)
ABS($1)	ABS($1)
ACOS($1)	ACOS($1)
ASIN($1)	ASIN($1)
ATAN($1)	ATAN($1)
ATAN2($1,$2)	ATAN2($1, $2)
CEIL($1)	CEIL($1)
CEILING($1)	CEIL($1)
COS($1)	COS($1)
EXP($1)	EXP($1)
FLOOR($1)	FLOOR($1)
LN($1)	LN($1)
LOG10($1)	LOG(10, $1)
LOG2($1)	LOG(2, $1)
LOG($1,$2)	LOG($1, $2)
MOD($1,$2)	MOD($1, $2)
POW($1,$2)	POW($1, $2)
POWER($1,$2)	POWER($1, $2)
RAND()	RAND()
RANDOM()	RAND()
ROUND($1)	ROUND($1)
ROUND($1,$2)	ROUND($1, $2)
SIGN($1)	SIGN($1)
SIN($1)	SIN($1)
SQRT($1)	SQRT($1)
TAN($1)	TAN($1)
CONCAT($1,$2)	CONCAT($1, $2)
LENGTH($1)	LENGTH($1)
LOWER($1)	LOWER($1)
LPAD($1,$2,$3)	LPAD($1, $2, $3)
LTRIM($1)	LTRIM($1)
REPLACE($1,$2)	REPLACE($1, $2, ‘’)
REPLACE($1,$2,$3)	REPLACE($1, $2, $3)
RPAD($1,$2,$3)	RPAD($1, $2, $3)
RTRIM($1)	RTRIM($1)
STRPOS($1,$2)	LOCATE($1, $2)
SUBSTR($1,$2,$3)	SUBSTR($1, $2, $3)
POSITION($1,$2)	LOCATE($2, $1)
TRIM($1)	TRIM($1)
UPPER($1)	UPPER($1)
YEAR($1)	EXTRACT(YEAR FROM $1)
MONTH($1)	EXTRACT(MONTH FROM $1)
DAY($1)	EXTRACT(DAY FROM $1)
HOUR($1)	EXTRACT(HOUR FROM $1)
MINUTE($1)	EXTRACT(MINUTE FROM $1)
SECOND($1)	EXTRACT(SECOND FROM $1)
DAY_OF_WEEK($1)	WEEKDAY($1)

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.

Hana sql migrate to openLooKeng sql guide

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:

In hana you can use ‘map’ function to transform the row data into column data:

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"
FROM SCORES
GROUP BY NAME

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:

Time dependent type’s Differences

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:

lk> select current_time;
                  _col0
          -------------------------
           21:19:49.122 Asia/Tokyo
lk> select current_timezone();
              _col0
           ------------
            Asia/Tokyo
             (1 row)

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();
          _col0
        --------
         +08:00
         (1 row)
lk> select current_time;
          _col0
          ---------------------
          20:20:45.659 +08:00
          (1 row)

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:

java -jar -Duser.timezone=Asia/Tokyo ngdbc.jar -u
database,passwd -n ip:34215 -c "SELECT CURRENT_TIME FROM DUMMY"
       Connected.
       |          |
       ------------
       | 20:38:57 |

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.