Skip to Content

This post is part of an entire series

Hana Smart Data Integration – Overview

When designing the Data Provisioning Solution, one key element were the Adapter Capabilities. Initially the assumption of SDA was every adapter is database-like. So joins can be pushed down, every kind of filter etc. No problem if the source is a database which supports that, then the entire exercise is to translate the Hana SQL the adapter gets into the SQL syntax of the source database understands.

If the source is not a database, that would mean the adapter has to implement it. The adapter implementing a join manually? An aggregation? A substring function?? That would have been easy for the SDA team but make adapter development very difficult.

Instead the adapter does return a list of capabilities it supports at adapter, table and column level and everything else the adapter does not support is executed as a second step inside Hana.

From a technology point of view that is quite simple to do.

Example: The user executes a SQL “select column3, column5 from virtual_table where column2 = 99”. This SQL is equivalent to a SQL in the form of “select column3, column5 from (select column2, column3, column5 from virtual table) where column2 = 99”. Some operations are put into the inner SQL – all the ones the adapter supports – and the rest is done in the outer SQL, the one executed in Hana. The capabilities control what goes into the inner SQL versus what is outside. In this concrete example, the capabilities would tell that projects (=returning individual columns) is supported, a where clause is not.

Pushdown of filters – example

Obviously this topic of adapter capabilities can get very complex quickly. Twitter is a good example of a source which is certainly not a database. According the the API documentation it supports where clauses.

see: The Search API | Twitter Developers

At a first look the logic is straight forward

Search String Description SQL equivalent
watching now containing both “watching” and “now” where Tweet like ‘%watching%’ and Tweet like ‘%now%’
“happy hour” containing the exact phrase “happy hour” where Tweet like ‘%happy hour%’
love OR hate containing either “love” or “hate” (or both). where Tweet like ‘%love%’ or Tweet like ‘%hate%’
beer -root containing “beer” but not “root” where Tweet like ‘%beer%’ and not Tweet like ‘%root%’

Filtering on dates is more of a challenge as

Search String Description SQL equivalent
since:2015-07-19 all tweets with sent date since 2015-07-19 where sent >= ‘2015.07.19’
until:2015-07-20 all tweets with sent date until 2015-07-20 where sent <= ‘2015.07.20’

Therefore a BETWEEN would work, an EQUAL also but a strict GREATER THAN cannot be pushed into the Twitter API.

Queries on long/lat are possible but only in the form of “near”. No idea how to express that in pure SQL. And those are AND always, no way to have a query similar to “All Tweets where either the text contains Boston OR it is near Boston”.

Pushdown – general guideline

The guideline for pushdowns has to be that the resulting dataset should be the same if the filter is pushed down to the source or applied in Hana. Although obvious, that could present problems. Is a Twitter search string “now” really the equivalent of “Tweet like ‘%now%'”? No, it is not. In SQL the like operator works on characters, the Twitter search on words. So the text “nowhere” would not match according to Twitter but would according to the database.

Other effects could be casing, like is ‘NOW’ = ‘now’? Or trailing blanks ‘now ‘ = ‘now’?

When performing an order-by, what is the sort order of Hana compared to the source? A classic example are Western European characters ‘Austria’ < ‘Österreich’ = ‘Oesterreich’ < ‘Russia’? In a binary order based on the UTF-8 character set it would be ‘Austria’ < ‘Oesterreich’ < ‘Russia’ < ‘Österreich’.

As a consequence when I write an adapter I tend to pushdown rather less than more and if one kind of pushdown is really required, okay, then my adapter is not 100% consistent. Better inconsistent than useless because of performance reasons.

Adapter Capabilities

On the adapter level the global capabilities are set. When creating/refreshing an adapter within Hana, the Data Provisioning Server of Hana calls the getCapabilities() method which has to return a list of Capabilities. These Capabilities are then stored in the Hana Data Dictionary and control the various aspects of the Adapter.

The majority of the capabilities deal with the kind of statements the adapter supports, e.g. AdapterCapability.CAP_SELECT tells Hana that this adapter supports select queries. A CAP_INSERT would mean the adapter supports loading as well.

Other Capabilities deal with all the kind of Hana functions the Adapter is able to understand, e.g. CAP_BI_UPPER would allow Hana to even push an upper() function in the SQL sent to the adapter.

But not all is related to pushdown, e.g. CAP_METADATA_ATTRIBUTE tells Hana this adapter does wish to store additional table properties.

As said, all these Capabilities are global for this adapter.

In case it is not sufficient to have global capabilities only, there are the adapter Capabilities CAP_TABLE_CAP and CAP_COLUMN_CAP. With these the SQL optimizer of the federation layer does the extra steps of checking the table metadata and its capabilities being set on table level or even down to every column.

Of course it would be optimal to control every single of these settings on a table level and column level. The downside of such total flexibility would be the optimizer performance, the kind of logic the optimizer has to go through to create the execution plan. Therefor the majority of the capabilities can be controlled on adapter level only, important exceptions on table/column level.

Where-clause related Capabilities

The most important capabilities revolve around the pushdown of where clauses. If the amount of data is small, the absolute time difference between reading all rows and executing the filter in Hana versus returning just the matching data, is not that big. But in the majority of cases the amount should be filtered as early as possible, meaning the adapter should get the information about the filters.

To enable that the CAP_WHERE has to be set. Without, no where-clause will ever be pushed down into the adapter. Next question is the type of where clause the adapter supports. Examples with increasing complexity:

where clause additional Capabilities to be set Comment
where value=1 CAP_WHERE, CAP_SIMPLE_EXPR_IN_WHERE
where value > 2 CAP_WHERE, CAP_SIMPLE_EXPR_IN_WHERE, CAP_NONEQUAL_COMPARISON includes: >, >=, <, <=, <>
where value between 1 and 3 CAP_WHERE, CAP_BETWEEN Note: adapter will receive “value >= 1 and value <= 3” even if CAP_AND is turned off
where value in (1,2,3) CAP_WHERE, CAP_SIMPLE_EXPR_IN_WHERE, CAP_IN

Note: adapter will receive “(value = 1 or value = 2 or value = 3)”

includes: not in

where text like ‘%abc%’ CAP_WHERE, CAP_SIMPLE_EXPR_IN_WHERE, CAP_LIKE includes: not like
where value = 1 and text = ‘abc’ CAP_WHERE, CAP_SIMPLE_EXPR_IN_WHERE, CAP_AND_DIFFERENT_COLUMNS
where (value = 1 or value = 2) and text = ‘abc’ CAP_WHERE, CAP_SIMPLE_EXPR_IN_WHERE, CAP_AND_DIFFERENT_COLUMNS, CAP_OR

Table and Column level capabilities

On table level the capabilities available to control the pushdown are

  • CAP_TABLE_SELECT, .._INSERT, .._UPDATE, .._DELETE, .._UPSERT
  • CAP_TABLE_AND, .._OR, .._AND_DIFFERENT_COLUMNS, .._OR_DIFFERENT_COLUMNS

So the idea is that some tables support reading and loading, other tables just selecting data from it. And the combinations of AND/OR one table might support can be different to another.

Remember to set the AdapterCapability.CAP_TABLE_CAP in the adapter, else these settings are not even read. And the table level capabilities are part of the table metadata, so the TableMetadata object the method importMetadata() returns should contain the table capabilities list.

If there is the requirement to have different capabilities per column, the AdapterCapability.CAP_COLUMN_CAP is set on adapter level and/or the table level capability TableCapability.CAP_TABLE_COLUMN_CAP. The list of capabilities added to each Column of a table includes:

  • CAP_COLUMN_FILTER
  • CAP_COLUMN_LIKE, CAP_COLUMN_IN, CAP_COLUMN_NONEQUAL_COMPARISON, CAP_COLUMN_BETWEEN
  • CAP_COLUMN_GROUP
  • CAP_COLUMN_INNER_JOIN, CAP_COLUMN_OUTER_JOIN
  • CAP_COLUMN_SORT
  • CAP_COLUMN_SELECT, CAP_COLUMN_UPDATE, CAP_COLUMN_UPSERT, CAP_COLUMN_INSERT

Here the idea is that one column can be selected but no filter be applied on. One column is read only, the other supports updates. And some control of the complexity of the filters supported.

Gotchas

The assumption of the capabilities is that they are static. Set once by the adapter and then never changed again. This allows to keep the capability information in the Hana data dictionary tables, e.g. in the SYS.ADAPTERS_ table the column CAPABILITIES is a BIGINT with a bitmap mask of all set adapter capabilities.

As a consequence of that, whenever the capabilities do change, the alter-adapter command has to be executed to reread that information, e.g. using this syntax

alter adapter “CapabilitiesTestAdapter” refresh at location agent “mylaptop”;

But even that is not enough, the optimizer has a few more places to cache the execution plans and the such. And if table/column capabilities are used, then the information is part of the Hana virtual table. Hence it is advised to drop and recreate the virtual tables as well.

Testing

In order to simplify playing with the adapter capabilities, a CapabilityTestAdapter has been created and is available in github.

hana-native-adapters/CapabilitiesTestAdapter

This adapter has two tables, the SQLTEXT table and the ADAPTERCAPS table. Using a statement like

update v_adaptercaps set isset = ‘TRUE’ where CAPABILITY = ‘CAP_NONEQUAL_COMPARISON’;

the individual adapter capabilities can be turned on and off.

Then the Hana dictionary is refreshed using commands similar to

alter adapter “CapabilitiesTestAdapter” refresh at location agent “mylaptop”;

drop table v_sqltable;

create virtual table v_sqltable at “captest”.”<NULL>”.”<NULL>”.”SQLTABLE”;

And finally the pushdown can be tested by either executing the query or looking at the explain plan of each statement.

select * from v_sqltable where rownumber>=1;

Note: The Capability settings are kept in static memory, meaning whenever the adapter is restarted, it starts with the default settings. Also keep in mind that ADAPTERCAPS table returns the capabilities set in the adapter at that moment, not the Hana data dictionary information and what is actively used by the Hana optimizer currently.

Finally, a set of four capabilities cannot be turned off because else the update statement would not work:

  • AdapterCapability.CAP_SELECT
  • AdapterCapability.CAP_UPDATE
  • AdapterCapability.CAP_WHERE
  • AdapterCapability.CAP_SIMPLE_EXPR_IN_WHERE
To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply