Following the #sitFRA Demo Recap blog, here is the last part of the series.

This part will focus on how to connect SAP HANA and SAP Vora, and consume data from each side to the other.

To follow the steps, we assume that you have loaded the data from the GDELT project as described in the previous part: #sitFRA Demo Recap – Import the data.

First let see how to connect SAP HANA to Vora, then we will look at the other way around.

From SAP HANA to SAP Vora

As explained in:

How to setup SAP HANA, express edition and SAP Vora, developer edition to enable them to connect to each other

First, we need to create a “remote source” in SAP HANA that connects to SAP Vora.

The way you will configure your remote source depends on the type of tables you want to consume:

• To consume a “disk engine” table (created using “com.sap.spark.engines.disk”), you will connect with SAP HANA Wire protocol like this:

DROP REMOTE SOURCE VIA_HANA_WIRE CASCADE; 

CREATE REMOTE SOURCE VIA_HANA_WIRE ADAPTER "voraodbc"
CONFIGURATION  'ServerNode=linux-6rxg:30115;Driver=libodbcHDB'
WITH CREDENTIAL TYPE 'PASSWORD' USING 'user=hanaes;password=hanaes';

• to consume a “relational” table (created using “com.sap.spark.vora”), you will connect with SAP HANA Spark Controller like this:

DROP REMOTE SOURCE VIA_HANA_SPARK CASCADE; 

CREATE REMOTE SOURCE VIA_HANA_SPARK ADAPTER "sparksql"       
CONFIGURATION 'server=linux-6rxg;port=7860;ssl_mode=disabled'        
WITH CREDENTIAL TYPE 'PASSWORD' USING 'user=hanaes;password=hanaes';

So, let’s create both.

Now let’s surfact the SAP Vora tables we created inside SAP HANA (using a dedicated schema):

DROP SCHEMA DEMO_VORA CASCADE;

CREATE SCHEMA DEMO_VORA;
  
CREATE VIRTUAL TABLE "DEMO_VORA"."EVENTS_DISK"
  AT "VIA_HANA_WIRE"."NULL"."VORA"."EVENTS_DISK";

CREATE VIRTUAL TABLE "DEMO_VORA"."EVENTS_RELATIONAL"
  at "VIA_HANA_SPARK"."VORA"."SPARK_VORA"."EVENTS_RELATIONAL"; 

You can now start querying the virtual table like if they were local:

SELECT TOP 10 * FROM "DEMO_VORA"."EVENTS_DE" WHERE ACTOR1COUNTRYCODE = 'FRA';

SELECT TOP 10 * FROM "DEMO_VORA"."EVENTS_RE" WHERE ACTOR1COUNTRYCODE = 'FRA';

And off course, you can now join them to the dimension table we uploaded in SAP HANA to the virtual table:

SELECT 
	E."GLOBALEVENTID",
	E."SQLDATE",
	E."MONTHYEAR",
	E."YYEAR",
	E."FRACTIONDATE",
	E."ACTOR1CODE",
	E."ACTOR1NAME",
	E."ACTOR1COUNTRYCODE",
	C."LABEL" AS ACTOR1COUNTRYNAME,
	E."SOURCEURL"
FROM "DEMO_VORA"."EVENTS_DE" E LEFT OUTER JOIN "DEMO_HANA"."COUNTRY" C
	ON C.CODE = E.ACTOR1COUNTRYCODE
WHERE E.ACTOR1COUNTRYCODE = 'FRA';

SELECT 
	E."GLOBALEVENTID",
	E."SQLDATE",
	E."MONTHYEAR",
	E."YYEAR",
	E."FRACTIONDATE",
	E."ACTOR1CODE",
	E."ACTOR1NAME",
	E."ACTOR1COUNTRYCODE",
	C."LABEL" AS ACTOR1COUNTRYNAME,
	E."SOURCEURL"
FROM "DEMO_VORA"."EVENTS_RE" E LEFT OUTER JOIN "DEMO_HANA"."COUNTRY" C
	ON C.CODE = E.ACTOR1COUNTRYCODE
WHERE E.ACTOR1COUNTRYCODE = 'FRA';

From SAP Vora to SAP HANA

SAP Vora doesn’t require any additional software to connect to SAP HANA, and not even a “remote source”. Each object you will surface will hold its own connection details.

For example, to list all tables from SAP HANA you can run the following statement:

SHOW TABLES USING com.sap.spark.hana
OPTIONS (
    host "hxehost.localdomain.com",
    instance "90",
    user "<HANA user>",
    passwd "<HANA password>",
    tenantdatabase "SYSTEMDB",
    dbschema "DEMO_HANA"
);

In my case I’m using the SYSTEMDB for my test, but you can switch the “tenantdatabase” property if needed.

To surface a table from SAP HANA into SAP Vora, you can run the following statement:

DROP TABLE IF EXISTS COUNTRY;

CREATE TABLE COUNTRY
USING 
    com.sap.spark.hana
OPTIONS (
    host "hxehost.localdomain.com",
    instance "90",
    user "<HANA user>",
    passwd "<HANA password>",
    tenantdatabase "SYSTEMDB",
    dbschema "DEMO_HANA",
    tablepath "COUNTRY"
);

And finally join your SAP Vora table to the SAP HANA table:

SELECT 
	E.GLOBALEVENTID,
	E.SQLDATE,
	E.MONTHYEAR,
	E.YYEAR,
	E.FRACTIONDATE,
	E.ACTOR1CODE,
	E.ACTOR1NAME,
	E.ACTOR1COUNTRYCODE,
	C.LABEL AS ACTOR1COUNTRYNAME,
	E.SOURCEURL
FROM EVENTS_DE E LEFT OUTER JOIN COUNTRY C
	ON C.CODE = E.ACTOR1COUNTRYCODE
WHERE E.ACTOR1COUNTRYCODE = 'FRA';

SELECT 
	E.GLOBALEVENTID,
	E.SQLDATE,
	E.MONTHYEAR,
	E.YYEAR,
	E.FRACTIONDATE,
	E.ACTOR1CODE,
	E.ACTOR1NAME,
	E.ACTOR1COUNTRYCODE,
	C.LABEL AS ACTOR1COUNTRYNAME,
	E.SOURCEURL
FROM EVENTS_RE E LEFT OUTER JOIN COUNTRY C
	ON C.CODE = E.ACTOR1COUNTRYCODE
WHERE E.ACTOR1COUNTRYCODE = 'FRA';

Et Voilà!

Have fun and let us know how we can improve this content in the future!