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.
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:
|allow-drop-table=true # allow hana connector to drop table or not|
- 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:
|# true to enable pushdown, false to disable it.|
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
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)
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
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-*.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-*.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.