Saturday, 4 June 2016

SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

It is a really simple scenario we are dealing with: Accessing a public OData URL, read from and write to the tables exposed there. HANA is running on SPS12 on premise. The purpose of this blog entry is to better illustrate the different steps analogous as they are explained in the HANA EIM Administration Guide.

In this scenario we are accessing the following public OData Service URL (ODATA V4) which allows read and write operations.

The following describes the context of tables that we can access in this example:

SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

In order to realize the above mentioned points, you can consider the following steps:

1.) Create the OData Adapter in HANA studio manually via the following SQL command (this is necessary and an exception as the OData Adapter is NO system adapter! Other than the common adapters used in the SDA context):

create"ODataAdapter"'display_name=OData Adapter;description=Odata Adapter'AT;

(select * from "SYS"."ADAPTERS"):

SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

2.) Create new remote source, choosing the previously create OData Adapter:
(System priviledge "CREATE REMOTE SOURCE" required")

SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

Enter your proxy, trust, CSRF and format settings according to your requirements. For further information on each of the fields consider reading the EIM Administration Guide.

In my case I set “Support Format Query” to true which allows me to receive the dataset in JSON-format. In the credentials section (if you also use a public OData service to test e.g. the adapter capabilities you need to enter anything… I entered for instance “test” as user and “test” as password. It will allow you to connect as it doesn’t require any user).

In productive and secure environments you most probably need a user, password and/or a certificate to be on the safe side! ;-)

SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

3.) Browse remote tables and create virtual tables of your choice you want to play with

Now go to Provisioning and browse your remote tables. You should now be able to access them via your new OData remote source:

SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

4.) Working with your virtual table (1) – READ (or GET…)

You can now browse the entries of your virtual table.

SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

You can verify the entries displayed in HANA studio with the entries you get when calling the service directly from a URL


SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

5.) Working with your virtual table (2) – WRITE (or PUT…)

If your OData service allows read and write operations you can also insert new entries with e.g. using the corresponding insert statement:

insert into "SYSTEM"."MV1_RW_ODATA_Products" values(13,'Afri Cola','The Original Cola','01.10.2005 00:00:00.0','01.10.2006 00:00:00.0',3,9.9);

SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

You should now see the new entry when firing a GET request from your browser on that table:


SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

6.) Create a replication task with the table comparison transform node to capture the delta only. In order to achieve this, you need to follow the subsequent steps:

(1) In the flowgraph builder in the WebIDE drag and drop a new data source node and select the virtual table your OData source table. Remember that you use WebIde only with Google Chrome Browser.

SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

(2) Drag and drop a new Table Comparison node onto the canvas and connect your data source node with it.

SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

(3) Intermediate step (this could also be done in advance...) Create your target table. You could do it as follows:

CREATE TABLE "ODATA_Products_target" LIKE "SYSTEM"."MV1_RW_ODATA_Products";

It will create a similar table with the same table structure.

(4)  Adjust your target table and a new primary key of type integer, e.g. “SURR_ID” (this approach follows the concept as introduced in the SAP HANA Academy Video “SAP HANA Academy - Smart Data Integration/Quality: The Table Comparison Transform [SPS09]”).

In my example I need to drop the already existing primary key “ID” first in order to realize adding a new primary key “SURR_ID”.

ALTER TABLE "SYSTEM"."ODATA_PRODUCTS_TARGET" DROP CONSTRAINT ID;

You can then add a new column called “SURR_ID” of type integer to your target table (in edit mode).

SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

(5) After adding this new column you can specify this column as a new primary key with the following command:

ALTER TABLE "SYSTEM"."ODATA_PRODUCTS_TARGET" ADD CONSTRAINT PK PRIMARY KEY (SURR_ID);

You need to do this for the table comparison transform to work properly. We will see this later on in this document

SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

(6) What you also need to do is create a database sequence for your data sink in your flowgraph. You can do this either in WebIde or in HANA Studio. You need to create a new file that ends with *.hdbsequence. Just right click on your package and select “File”.

SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

Enter your sequence name with the correct file ending.

SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

Specify your sequence properties. Adjust the schema property according to your schema’s name. Save your sequence.

SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

(7)    Now you can edit your table comparison node. First and foremost choose your comparison table. This needs to be your previously created target table that has the “SURR_ID” and ideally the same structure as your source table (…or another structure, depending on your requirements).

SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

In the table comparison transform specify the generated key attribute which is your “SURR_ID” field of your target table.

SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

If you OData source allows delete operations you can select "Detect Deleted Rows From Comparison Table". This will ensure that deleted rows in the source      will also be deleted in your target table.

SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

Click on attributes in your Table Comparison node and drag and drop the fields which you want to be considered for comparison. You need to set the key column as "Key = true". The table comparison node will capture every change that occurs in one of these columns and correspondingly transfer the delta to your target table.

SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

(8) Select a data sink node. Choose your target table as your data sink.

SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

Enter the database sequence into the corresponding field of your data sink. You can find it on the "Settings" tab of your data sink node. What you should also do is specify your key generation attribute which is "SURR_ID" in our case.

SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

(9) Save your flowgraph and execute the task to conduct an initial load of your target table.

If you can´t save your flowgraph in WebIde you're probably lacking priviledges. Make sure you have object priviledges on:

- EXECUTE
on "_SYS_REPO"."TEXT_ACCESSOR"
and "_SYS_REPO"."MULTI_TEXT_ACCESSOR"

- SELECT, UPDATE, INSERT, DELETE, EXECUTE granted to _SYS_REPO

You may also refer to the EIM Administration Guide chapter "7.1 Assign Roles and Priviledges"

SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

(10) Check in system table “M_TASKS” how many records where processed with the initial load.

SELECT * FROM "SYS"."M_TASKS";

SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

(11) Insert a new record into the source table of the OData service with e.g. an insert command, re-run the task and check how many records have been processed. It is assumed that only the delta or respectively changed rows were transferred.

INSERT INTO "SYSTEM"."MV1_RW_ODATA_PRODUCTS" VALUES(14,'NEW COLA','THE ORIGINAL WOW COLA','01.10.2005 00:00:00.0','01.10.2006 00:00:00.0',3,39.9);

START TASK "SYSTEM"."SDI::ODATA_FG01";

SELECT * FROM "SYS"."M_TASKS";

SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

Alternatively you could also execute an update statement on your OData source table, re-run the task, see how many records were processed and what has changed and compare your source and target table if it really worked out for you.

(12) Finally see what is in the target table. We can see that our inserted record was appended to the table:

SELECT * FROM "SYSTEM"."ODATA_Products_target";

SDI/SDQ OData Adapter in HANA SPS12 - GET, PUT operation and REPLICATION flowgraph

On top of what we modelled we could now create a replication task to let the batch task run e.g. every ten minutes to capture the delta of our OData source.

Source: scn.sap.com

No comments:

Post a Comment