Data Provisioning Adapter SDK - No pushdown of adapter capabilities

[marco_spyker]

Hi all,

we are developing a custom adapter using the Data Provisioning SDK for the DP Agent.

We are able to retrieve data from the remote datasource, create a virtual table etc.

But unfortunately, we are struggeling with the pushdown of adapter capabilities:

In HANA Studio, we created a new "Remote Source", added a virtual table and opened the "Data Preview" and the shown table with data is displayed as expected:

Then we tried to play around with "LIMIT", "WHERE" and "FILTER" functionalities, but realized that no pushdown of these capabilies is forwarded to our adapter.

As an example:

Changing "Max rows" to 125 and hit the "Execute" button...

... jumping to the breakpoint at "executeStatement"-Method of our custom adapter and checking the given "sql", the expected parameter "TOP" does not appear...

content of 'sql' parameter:
SELECT 
   "DEV_CON_XXXX"."YYYYY", 
   "DEV_CON_XXXXX"."fiscal-year", 
   "DEV_CON_YYYYY"."posting-period",
   [...]  
FROM 
   "ZZZZZZZ" 

...although it is generated (according to 'Show log'):

And after parsing the sql statement into variable 'query':

query = (Query) ExpressionParserUtil.buildQuery(sql, messageList);

its content for limit is just 'null' ('where', 'orderBy' etc., as well):

Query 
   [projections=[ColumnReference [column="XXXXXX", table="DEV_CON_YYYYYY", schema=null], ColumnReference [column="fiscal-year", table="DEV_CON_YYYYYY", schema=null], ColumnReference [column="posting-period", table="DEV_CON_YYYYYY", schema=null], ColumnReference [column="[ZZZZZ].[Gesamtsumme von ZZZZZ]", table="DEV_CON_YYYYYY", schema=null], ColumnReference [column="[ZZZZZ].[0000310000]", table="DEV_CON_YYYYYY", schema=null], ColumnReference [column="[ZZZZZ].[0000399000]", table="DEV_CON_YYYYYY", schema=null], ColumnReference [column="[ZZZZZ].[REV_DEV_LY_ABS]", table="DEV_CON_YYYYYY", schema=null], ColumnReference [column="[ZZZZZ].[REV_DEV_LY_REL]", table="DEV_CON_YYYYYY", schema=null], ColumnReference [column="[ZZZZZ].[REV_DEV_PL_ABS]", table="DEV_CON_YYYYYY", schema=null], ColumnReference [column="[ZZZZZ].[REV_LY]", table="DEV_CON_YYYYYY", schema=null], ColumnReference [column="[ZZZZZ].[REV_PL]", table="DEV_CON_YYYYYY", schema=null]],   where=null, orderBy=null, groupBy=null, having=null,
from=TableReference [name="YYYYYY", owner=null, database=null], limit=null, distinct=false, values=null, withParams=null]

The custom adapter extends the "Adapter" class.

Our remote datasource is an "SQL"-like datasource. Therefore our "getCapalities"-Method looks like this:
(copied from the SQLAdapter from Github-Repository[Link])

	@Override
	public Capabilities<AdapterCapability> getCapabilities(String version)
			throws AdapterException {
		Capabilities<AdapterCapability> caps = new Capabilities<AdapterCapability>();
		
		
		Capabilities<AdapterCapability> capability = new Capabilities<AdapterCapability>();
		capability.setCapability(AdapterCapability.CAP_SELECT);
		capability.setCapability(AdapterCapability.CAP_AND);
		capability.setCapability(AdapterCapability.CAP_PROJECT);
		capability.setCapability(AdapterCapability.CAP_JOINS);
		capability.setCapability(AdapterCapability.CAP_LIMIT);
		capability.setCapability(AdapterCapability.CAP_LIMIT_ARG);
		capability.setCapability(AdapterCapability.CAP_TRANSACTIONAL_CDC);
		capability.setCapability(AdapterCapability.CAP_BIGINT_BIND);
		capability.setCapability(AdapterCapability.CAP_METADATA_ATTRIBUTE);
		capability.setCapability(AdapterCapability.CAP_WHERE);
		capability.setCapability(AdapterCapability.CAP_SIMPLE_EXPR_IN_WHERE);
		capability.setCapability(AdapterCapability.CAP_AND_DIFFERENT_COLUMNS);
		capability.setCapability(AdapterCapability.CAP_LIKE);
		capability.setCapability(AdapterCapability.CAP_NONEQUAL_COMPARISON);
		capability.setCapability(AdapterCapability.CAP_AGGREGATES);
		return capability;
	}

local DPAgent-Version
dpagent.version=2.5.1.1
lcmsdk.version=2.5.28
dpagentservice.version=2.5.0
dpadapters.version=2.5.0
repagent.syb.version=15.7.1SP210
repagent.sybfilter.version=15.7.1SP214
sqlanywhere.version=16.0.0.1691
ms.vcredist.2010.version=10.0.40219
ms.vcredist.2015.version=14.0.24212
ocs.version=16.0.03.06
ci.version=1.9.0-rc-2
dataservices.engine.version=14.2.14.2681
dataservices.lib.version=14.2.8.1504
dpagent.build.version=2.5.1.1

Are we missing something, what we actually do not see at the moment?

Do we need to do some more configuration in other parts of our custom adapter?

Any inspiring idea is welcome! 🙂

Thanks a lot in advance,

Marco

[werner_daehn · Accepted solution]

No immediate idea, a couple of thoughts though:

• Please execute: SELECT * FROM ADAPTER_CAPABILITIES WHERE is_sda_supported = 'TRUE' and adapter_name = ?;
  As you have set the capabilities and then registered the adapter (I hope!) all your capabilities should be listed.
• The parsing is irrelevant as the executeStatement(sql, info) gets a sql string without any filters. Our first job is the see in that sql text more than just a select * from.
• Instead of show-log, use explain plan to see what the optimizer does.
• Don't rely on the UI. Rather execute a statement like "select top 125 * from <virtual_table>". Max rows can also execute a select statement without a top/limit and rather stop fetching the data. Post a few examples of SQLs, e.g. select * from table where "fiscal-year" = 1234; and show the explain plan result, the line that tells what has been pushed to the adapter.

When changing capabilities, always execute

ALTER adapter "name" refresh AT location agent "name";

to update Hana with the adapter caps. Same for virtual tables in case you would use table level capabilities in future.

[marco_spyker · Accepted solution]

Hi werner.daehn,

thanks for your response!

FIrst I had a look into "ADAPTER_CAPABILITIES" table and indeed, the capabilities for my custom adapter did not match with my coding.

So, executing...

ALTER adapter "name" refresh AT location agent "name";

..did the job and refreshed the capabilities and now, the pushdown works!

Best regards,

Marco

BTW: Nice that you get a description of each used capability: