Hana Adapter SDK – Interaction via SQL
This post is part of an entire series
Hana Smart Data Integration – Overview
- Hana Smart Data Integration – Adapters
- Hana Smart Data Integration – Batch Dataflows
- Hana Smart Data Integration – Realtime Table Replication
- Hana Smart Data Integration – Realtime Sources with Transformations
- Hana Smart Data Integration – Realtime Sources with History Preserving
- Hana Smart Data Integration – Architecture
- Hana Smart Data Integration – Fun with Transformation Services
There are multiple UIs like the Web based FlowGraph editor and ReplicationEditor or Hana Studio available to setup and use the Adapters. But under the cover they are all executing SQL commands. Hence it might be a good idea to look at the adapters from that angle as well.
Recap – SQLs for creating the agent and adapter
In the previous chapters the agent and the adapter was registered in Hana. These applications use SQL regular commands, in particular
CREATE AGENT “mylaptop” PROTOCOL ‘TCP’ host ‘10.16.94.83’ PORT 5050; (see CREATE AGENT – SAP Library)
CREATE ADAPTER “HelloWorldAdapter” AT LOCATION AGENT “mylaptop”; (see CREATE ADAPTER – SAP Library)
If the IP address changed or one adapter is hosted at another location, there are the matching ALTER commands available as well.
To check which agents and adapters are available, the Hana data dictionary views AGENTS, ADAPTERS, ADAPTER_LOCATIONS can be queried.
Up to now, the adapter – let’s call it the middleware – was installed and Hana made aware of it.
Creating the source connection
In order to actually use an adapter, a remote-source has to be created. This provides all the login information the adapter needs to connect to a particular system using the adapter as a driver.
CREATE REMOTE SOURCE “myHelloWorldConnection” ADAPTER “HelloWorldAdapter”
AT LOCATION AGENT “mylaptop” CONFIGURATION
‘<?xml version=”1.0″ encoding=”UTF-8″?>
<ConnectionProperties name=”conn“>
<PropertyEntry name=”name“>Myself</PropertyEntry>
</ConnectionProperties>’
WITH CREDENTIAL TYPE ‘PASSWORD’ USING
‘<CredentialEntry name=”credential“>
<user>me</user>
<password>unknown</password>
</CredentialEntry>’;
The information what parameters it needs and what their names are is stored in the ADAPTERS data dictionary table, like for above adapter it reads:
<?xml version=”1.0″ encoding=”UTF-8″ standalone=”yes”?>
<RemoteSourceDescription>
<ConnectionProperties displayName=”Connection Info” isRepeatable=”false” name=”conn“>
<PropertyEntry displayName=”Hello whom?” id=”1″ isRepeatable=”false”
isRequired=”false” name=”name” type=”STRING”/>
</ConnectionProperties>
<CredentialProperties displayName=”Credentials” name=”credentials”>
<CredentialMode displayName=”Credentials Mode” id=”2″ isRepeatable=”false”
isRequired=”false” name=”credentials_mode” type=”ENUM”>
<EnumValue displayName=”Technical User” name=”technicaluser”/>
<EnumValue displayName=”None” name=”none”/>
<EnumValue displayName=”Secondary Credentials” name=”secondarycredentials”/>
</CredentialMode>
<CredentialEntry displayName=”Credentials” id=”3″ name=”credential“>
<user displayName=”Username” id=”4″ isRequired=”true” type=”STRING”/>
<password displayName=”Password” id=”5″ isRequired=”true” type=”STRING”/>
</CredentialEntry>
<Activator id=”2″ name=”credentials_mode”>
<Value>technicaluser</Value>
</Activator>
</CredentialProperties>
</RemoteSourceDescription>
But frankly, using Hana Studio -> Provisioning from the Catalog view is much easier.
Adding virtual tables
A virtual table can be seen as a database View inside Hana on top of a remote table to blend this table into the Hana dictionary seamlessly. Hence the syntax is quite simple:
CREATE VIRTUAL TABLE v_hello AT “myHelloWorldConnection”.”<NULL>”.”<NULL>”.”HELLO”;
The more interesting question is how to know the values for database, owner and table name. Since these are remote tables, the only place to get the information from is the adapter itself. Using the procedure GET_REMOTE_SOURCE_OBJECT_TREE the adapter can be queried.
call SYS.GET_REMOTE_SOURCE_OBJECT_TREE (‘myHelloWorldConnection’, ”, ?, ?);
But here as well, using the Catalog Editor might be simpler.
But the create virtual table statement allows to pass parameters to the adapter as well. This is for example used with the file adapter. A virtual table for a file needs lot of knowledge, what the delimiter is, what the first column should be named and its datatype etc. Hence a virtual table for the file adapter can be created with a SQL like this:
CREATE VIRTUAL TABLE v_fixed AT “myFileAdapter”.”<NULL>”.”<NULL>”.”fixed”
REMOTE PROPERTY ‘dataprovisioning_parameters’=
‘<?xml version=”1.0″ encoding=”UTF-8″ standalone=”yes”?>
<Parameters>
<Parameter name=”FORMAT”>FIXED</Parameter>
<Parameter name=”FORCE_FILENAME_PATTERN”>fixed%.txt</Parameter>
<Parameter name=”ROW_DELIMITER”>\r\n</Parameter>
<Parameter name=”SKIP_HEADER_LINES”>1</Parameter>
<Parameter name=”COLUMNSSTARTENDPOSITION”>0-1;2-7;8-15</Parameter>
<Parameter name=”ROWLENGTH”>16</Parameter>
<Parameter name=”COLUMN”>COL1;NVARCHAR(2)</Parameter>
<Parameter name=”COLUMN”>COL2;NVARCHAR(6)</Parameter>
<Parameter name=”COLUMN”>COL3;NVARCHAR(8)</Parameter>
<Parameter name=”CODEPAGE”>UTF-8</Parameter>
</Parameters>’;
The select command
A select command can be used to get data from a virtual table. What part of the user SQL is sent to the Adapter is controlled by the AdapterCapabilities, the remaining logic is executed in Hana. Well, that’s the Smart Data Access federation logic.The interesting part for us as adapter developers is that we can again send additional information to the adapter as part of the select statement.
File adapter is a good example for that again:
SELECT * FROM V_FILECONTENTTEXT AS “V_FILECONTENTTEXT” WHERE NAME LIKE ‘fixed1.txt’
WITH DATAPROVISIONING PARAMETERS
(‘ <PropertyGroup name=”__DP_TABLE_OPTIONS__”>
<PropertyGroup name=”V_FILECONTENTTEXT”>
<PropertyEntry name=”CODEPAGE”>UTF-32</PropertyEntry>
</PropertyGroup>
</PropertyGroup>‘);
The main difference to above is that the select statement can contain multiple tables potentially and then the SQL parser needs to know which parameter belongs to what table. Hence the SQL contains the virtual table name as a parent XML node.
The adapter parameters
The adapter itself supports setting parameters as well. These are not part of any SQL command, it is part of the startup code of the adapter process. Therefore it is set by using the configTool/dpagentconfigtool.exe shipped as part of the data provisioning agent install.
The HelloWorldAdapter does not require any parameters to be set but the FileAdapter lets you enter the root directory where all the files are located, in order to make sure nobody writes e.g. into the c:\Windows directory.
Thank you for these nice blog. It helps me a lot.
Is only SELECT is allowed in virtual tables created as part of SDI?
When I tried the INSERT, it works fine without any error. But in Remote Source data is not added also Rows Affected is zero.
Statement 'insert into "abc"."abc::northwind replication.VT_dbo.Products" ("ProductName","Discontinued") ...'
successfully executed in 31 ms 334 µs (server processing time: 6 ms 452 µs) - Rows Affected: 0
Note: I am on HCP SPS09, remote source is MS SQL Server2012 connected using MssqlLogReaderAdapter
Thanks!
In SP9 only selects do work, SP10 supports inserts as well. Both, the SDI solution in the SQL Server adapter.
Not sure why you do not get an error when executing an insert into the virtual table.
Hi Warner,
We are trying to connect Mysql server via SDA
I Created an Agent and Adapter, When I try to connect in by using connection parameters I I am getting an error JDBC 403 .Cannot get remote source objects. Can you help me on this
Hi,
How we can connect to MYSQL from Smart Data Access.
A MySQL Adapter is on my ToDo list for OpenSource. At the moment I would use the OpenSource JDBC adapter. Want to try that?
hana-native-adapters/jdbcadapter at master · SAP/hana-native-adapters · GitHub
We can work together on that if you like.
Hi Werner,
Sure i would like to work together.
Please send me an email.
We need to define the scope, timing, degree of support you expect, who is doing what.
But the JDBC driver would be a a good start.
hi werner, i'll be very interested in your mySQL adapter, if you could share.
how far have you got with this ?
The SDA part is ready, would like to put it in the OpenSource branch but need somebody to test it. And then we should implement realtime.
i would be happy to test it, as we have a simple real life use.
if you can just let me know what i need to do, thanks
glen
hello werner
what's your timeline for getting it in OpenSource, or do you want it tested before ?
as i mentioned, i'd be happy to help
thanks
I have added it to the OpenSource github. It compiles (Java 1.7 minimum) but has not been tested. Best would be to use that as a starting point for adapter development.
It is not(!) a prebuild adapter jar file, okay?
ah, was hoping it was.
let me have a chat with our java team and see what needs to be done, i'm not the java person, just the tester 🙂
thanks
As I assume you will find issues continue talking to your Java folks, please. Meanwhile I have put the MySQLAdapter*.jar file into the root directory of the hana-native-adapters aas well for you to download.
Don't forget, it requires the jdbc driver to be copied in the lib directory after the adapter had been installed, else you will get a class-not-found error.
hi werner, i have an open mind, so no problem. will catch up soon
i werner,
which lib directory, we tried a couple here,"...usr/sap/dataprovagent/lib", but is was no good
thanks
glen
That's the correct directory though, that's the directory the other JDBC drivers go as well. I just checked the help docs and they say:
Before registering your adapter with the SAP HANA system, be sure that you have downloaded and installed the correct JDBC libraries.
Place your files in <DPAgent_root>/lib,
Permissions wrong, e.g. execute rights for world?
hi werner, could you let us know, the exact driver that would work, because that's the only missing thing, we cannot verify. everything else, looks fine with the install.
thanks
It is not the jar file that cannot be loaded, it is the OSGi dependencies being not setup correctly. I have tried various ways e.g. DynamicImport-Package but could not get it to work either.
Therefore I have changed the adapter a bit, it now asks for the jar file to be specified in an additional remote source property. If the file is found in the dataprov\lib directory, then it is set as default automatically.
WATCHOUT: For testing I had to bring my environment to the SDI DPAGENT 1.00 SP03 Patch 0. This came out 4 weeks ago, please doublecheck if your dpagent is that recent.
Jar file got updated as well, old deleted, new added.
With this I had been able to connect to a MySQL database i quickly installed and did browse the remote source. Did not read from any tables as I assume this works. (Source code was provided by somebody else)
hi werner, we managed to get it working thanks
on one of our tables, we are getting the following error:
16:50:35 (Catalog) (dberror) 338 - zero-length columns are not allowed: SEED: line 1 col 282 (at pos 282)
any clues ?
others are working fine
thanks
g
Can you create me a test case, then I can debug and correct it right away. Say your create table and one row as insert statement?
did you get my email, i copied table SQL for you
Did now.
Hi Glen, the new version of the adapter is uploaded. I moved the jar file into a new sub directory, though.
It does work with all datatypes now except BIGINT. There I can't see what I am doing wrong.
And LOBs are supported as well but I have not tested large LOBs > 1M in size.
Please execute a
alter adapter "MysqlAdapter" refresh at location agent "<agent_name>";
to update the adapter capabilities. It does a projection now as well, not only select * from...;
Also drop all virtual tables and recreate them. Before the table names were all being converted to uppercase which would have issues in case you have two tables of same name but different casing, which is supported in Unix.
did you get my new email, in response ?
nope.
hi werner
i thought we were there
the virtual table that was in error, now gets created, however, all tables cannot run the SELECT statement on the virtual tables.
the definition are still viewable for all tables
here is the error we now get
14:38:31 (Catalog) Create new virtual table 'WOVERTON"."prd-nodbx02_portal_cnf_emails' successfully
14:38:41 (Data Preview) Could not open 'WOVERTON'.'prd-nodbx02_portal_cnf_emails'.
Error: (dberror) 403 - internal error: Remote execution error NullPointerException while processing request "SDA Request for MysqlAdapter for request type FEDERATION_GET_NEXT" for query "SELECT "prd-nodbx02_portal_cnf_emails"."id", "prd-nodbx02_portal_cnf_emails"."name", "prd-nodbx02_portal_cnf_emails"."langid", "prd-nodbx02_portal_cnf_emails"."subject", "prd-nodbx02_portal_cnf_emails"."headers", "prd-nodbx02_portal_cnf_emails"."content" FROM "cnf_emails" "prd-nodbx02_portal_cnf_emails" LIMIT 1000 "
Works for me and my test cases. Just created a virtual table on the mysql.user as V_USER and then executed a "select top 3 * from v_user;"
Do you have a test case for me?
hi werner, our developer is back now, so i would like to carry on this issue.
our version of mySQL is this Ver 14.14 Distrib 5.6.28
what version are you using ?
have you done any new amendments over the last week, that we could try
I have checked the code and did not find anything obvious which could cause a null value exception. MySQL was the latest version, don't know the exact one (new server image).
ok, i'll see if we revert back to the previous version, what would happen
thanks
We have two options. You provide me a simple test case. You run the adapter code in Hana Studio and debug. The latter would have the advantage that you can fix it faster. As you wish. I am interested in making this adapter more solid.
hi werner, new developments ...
we were going to revert to the previous adaptor that worked.
we then found that our system was not well, so we registered it on another instance.
then, it worked. actually, we found out that we did install the latest adaptor instead,
so in the end, we had your latest adaptor installed, registered to a new instance.
and it all works.
the data types and lengths look to be converting just fine.
thanks
lets see where we go from here
🙂
Cool. If you believe there is anything we could do better, let me know.
hi werner, some more information for you
the SDI mySQL worked for us, on HANA SPS122, however, we recently moved the SDI Adapter, AND upgraded it from SP3 to SP4, and now we get the following error, when we try to expand the SDI node in HANA Studio
(Catalog) Error reading Remote Object: InternalError: dberror(CallableStatement.execute): 403 - internal error: Cannot get remote source objects: NullPointerException while processing request "SDA Request for MysqlAdapter for request type GET_SOURCE_VERSION"
we are thinking you may need to check it with the latest SDI Adapter v4
thanks
glen
The adapter is currently compiled against the 1.3.0 or 1.3.1 version of the agent. Your agent is of version 1.2.4.
Any chance to ask your Java developer to get the source code and compile it yourself to avoid these kinds of problems?
we will upgrade the adapter to 1.3.1, and try again, thanks
hi werner, we upgraded to 1.3.2, and was successful in creating a mySQL virtual table.
we are now testing, and will let you know how it goes
thanks for all you effort and patience
Hi Glen,
We are trying to connect MYSQL db by using same link. But not able to achieve it.
Can you please explain the steps performed for the MySQL remote source by using JDBC connectors.
Thanks in Advance
Regards
Mushtak
hi werner, have you had a chance to see my response, below
thanks