Skip to Content

This post is part of an entire series

Hana Smart Data Integration – Overview

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.

adapter sdk sql 1.png

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’, ”, ?, ?);

adapter sdk sql 2.png

But here as well, using the Catalog Editor might be simpler.

adapter sdk sql 3.png

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.

adapter sdk sql 4.png

To report this post you need to login first.

40 Comments

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

  1. Ramees Keethadath

    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!

    (0) 
    1. Werner Daehn Post author

      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.

      (0) 
      1. Mushtak AliBaig

        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

        (0) 
        1. Werner Daehn Post author

          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.

          (0) 
        1. Werner Daehn Post author

          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.

          (0) 
          1. glen spalding

            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

            (0) 
            1. Werner Daehn Post author

              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?

              (0) 
              1. glen spalding

                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

                (0) 
                1. Werner Daehn Post author

                  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.

                  (0) 
                  1. glen spalding

                    i werner,

                    requires the jdbc driver to be copied in the lib directory

                    which lib directory, we tried a couple here,”…usr/sap/dataprovagent/lib”, but is was no good

                    /wp-content/uploads/2016/06/2016_06_22_980998.png

                    thanks

                    glen

                    (0) 
                    1. Werner Daehn Post author

                      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?

                      (0) 
                      1. glen spalding

                        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

                        (0) 
                        1. Werner Daehn Post author

                          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)

                          (0) 
                          1. glen spalding

                            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

                            (0) 
                            1. Werner Daehn Post author

                              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?

                              (0) 
                                1. Werner Daehn Post author

                                  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.

                                  (0) 
                                      1. glen spalding

                                        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 “

                                        (0) 
                                        1. Werner Daehn Post author

                                          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?

                                          (0) 
                                          1. glen spalding

                                            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

                                            (0) 
                                            1. Werner Daehn Post author

                                              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).

                                              (0) 
                                                1. Werner Daehn Post author

                                                  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.

                                                  (0) 
                                                  1. glen spalding

                                                    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

                                                    🙂

                                                    (0) 
                                                      1. glen spalding

                                                        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”

                                                        Screenshot_1.png

                                                        we are thinking you may need to check it with the latest SDI Adapter v4

                                                        thanks

                                                        glen

                                                        (0) 
                                                        1. Werner Daehn Post author

                                                          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?

                                                          (0) 
                                                            1. glen spalding

                                                              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    

                                                              (0) 
                                                            2. Mushtak AliBaig

                                                              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

                                                              (0) 

Leave a Reply