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 Adapter SDK – Setup
  • Hana Adapter SDK – The first adapter
  • Hana Adapter SDK – The Open Source Adapters from Github
  • Hana Adapter SDK – Interaction via SQL
  • Hana Adapter SDK – The Adapter code
  • Hana Adapter SDK – Adapter capabilities
• 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.