In this blog , I would like to share my thoughts around using XS destinations for internet connectivity and capturing the XML response and loading the same into HANA with the help of SAX XML Parser.
Scenario :
We shall take the familiar Google Maps API and let us see how can we connect from SAP HANA and capture the XML response. We would need to define xshttpdest for outbound connectivity.
Step 1 : setting up XS destinations for internet connectivity
google.xshttpdest
host = "maps.googleapis.com";
port = 80;
pathPrefix = "/maps/api/distancematrix/xml";
proxyType = none;
authType = none;
useSSL = false;
timeout = 30000;
Once the destination file is created, we can consume this in a .xsjs file to connect to Google Maps API as shown below :
xmlparsing.xsjs
var destination_package = "TEST.test"; //Both XSJS and XSHTTPDEST file should be kept in the same package
var destination_name = "google";
var dest = $.net.http.readDestination(destination_package, destination_name);
var req = new $.web.WebRequest($.net.http.GET, "?origins=Delhi&destinations=Visakhapatnam&mode=driving&language=en-US&sensor=false");
//hard coding the origin and destination city
var client = new $.net.http.Client();
client.request(req, dest);
var response = client.getResponse();
var xml = response.body.asString();
$.response.status = $.net.http.OK;
We can now see the XML response being captured from HANA as shown below :
Scenario :
We shall take the familiar Google Maps API and let us see how can we connect from SAP HANA and capture the XML response. We would need to define xshttpdest for outbound connectivity.
Step 1 : setting up XS destinations for internet connectivity
google.xshttpdest
host = "maps.googleapis.com";
port = 80;
pathPrefix = "/maps/api/distancematrix/xml";
proxyType = none;
authType = none;
useSSL = false;
timeout = 30000;
Once the destination file is created, we can consume this in a .xsjs file to connect to Google Maps API as shown below :
xmlparsing.xsjs
var destination_package = "TEST.test"; //Both XSJS and XSHTTPDEST file should be kept in the same package
var destination_name = "google";
var dest = $.net.http.readDestination(destination_package, destination_name);
var req = new $.web.WebRequest($.net.http.GET, "?origins=Delhi&destinations=Visakhapatnam&mode=driving&language=en-US&sensor=false");
//hard coding the origin and destination city
var client = new $.net.http.Client();
client.request(req, dest);
var response = client.getResponse();
var xml = response.body.asString();
$.response.status = $.net.http.OK;
We can now see the XML response being captured from HANA as shown below :
Step 2 : Using SAX Class for Parsing XML response :
In the same xsjs file, we will be using the methods mentioned here JSDoc: Class: SAXParser to parse the XML response we have received . While parsing we will try to format it into JSON as it will make the data load into HANA easier .
PS: There can be XML to JSON converters available in HANA, am yet to understand them so for now am doing a blunt way of formatting them into desired JSON format as shown below.
var parser = new $.util.SAXParser();
var char="";
var endtagname="";
var valuecount=0;
var textcount=0;
var data_string = '';
parser.characterDataHandler = function(s) {
char = s; // To Capture the character data into a local variable
};
parser.endElementHandler = function(name) {
endtagname = name;
// Extracting the required data from XML Response and formatting the same in JSON format
if (endtagname === 'origin_address' )
{
data_string += '\{' + '"'+ "Distance" + '"' + ':' + '\[' + '\{'+ '"' + endtagname + '"' + ':' + '"' + char +'"' + ',';
}
if (endtagname === 'destination_address' )
{
data_string += '"' + endtagname + '"' + ':' + '"' + char +'"' + ',';
}
if (endtagname === 'value' )
{
valuecount += 1;
if (valuecount === 1)
{
data_string += '"' + 'duration_value_in_secs' + '"' + ':' + '"' + char +'"' + ',';
}
else
{
data_string += '"' + 'distance_value_in_mts' + '"' + ':' + '"' + char +'"' + ',';
}
}
if (endtagname === 'text' )
{
textcount += 1;
if (textcount === 1)
{
data_string += '"' + 'duration_value_in_days' + '"' + ':' + '"' + char +'"' + ',';
}
else
{
data_string += '"' + 'distance_value_in_kms' + '"' + ':' + '"' + char +'"' + '\}';
} }
if (endtagname === 'row')
{
data_string += '\]' + '\}';
}
};
parser.parse(xml);
Once we execute the above script, you would be able to get the JSON equivalent as shown below :
Step 3 : Load the captured JSON into a table in HANA
As we now have a JSON, it is relatively easier now to load it into HANA. We need to do the below :
a) Create a table type with the structure similar to JSON format
b) Create a stored procedure with the input parameter to accept the JSON and to insert the data into a physical table in HANA
Please find the code snippets below :
Table : GOOGLE_API_DISTANCE
CREATE TABLE GOOGLE_API_DISTANCE
("origin_address" varchar(1000),"destination_address" varchar(1000),"duration_value_in_secs" varchar(1000),
"duration_value_in_days" varchar(1000),"distance_value_in_mts" varchar(1000),"distance_value_in_kms" varchar(1000))
Table Type :
CREATE TYPE TT_GOOGLE_API_DISTANCE AS TABLE
("origin_address" varchar(1000),"destination_address" varchar(1000),"duration_value_in_secs" varchar(1000),
"duration_value_in_days" varchar(1000),"distance_value_in_mts" varchar(1000),"distance_value_in_kms" varchar(1000))
Note : You could use .hdbdd file to create the table and table type . For demo purpose I have created like above
CREATE PROCEDURE SCHEMA_NAME. "GOOGLE_DISTANCE_PROC" (IN DATA CSC_CORE.TT_GOOGLE_API_DISTANCE,OUT MESSAGE VARCHAR(50) )
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
INSERT INTO GOOGLE_API_DISTANCE (SELECT * FROM :DATA);
MESSAGE := 'Data loaded Successfully into SAP HANA';
END;
Note : Here again you can use .hdbprocedure
Now let us add the relevant code required to connect and call the procedure via xsjs.
var conn = $.hdb.getConnection();
var data = data_string;
var parse_json = JSON.parse(data);
var json_object = {};
json_object = parse_json;
var google_dist_data = json_object.Distance;
//Call procedure with the correct table structure similar to JSON
var prep_procedure = conn.loadProcedure("SCHEMA_NAME","GOOGLE_DISTANCE_PROC");
//Passing the data structure and capturing the return message
var return_message = prep_procedure(google_dist_data);
//Passing output to response
$.response.status = $.net.http.OK;
$.response.contentType = "application/json";
$.response.setBody(JSON.stringify(return_message));
//Committing and closing the connection
conn.commit();
conn.close();
Now once we execute the entire .xsjs file, we can see the below response :
Now we can see the data loaded into HANA as shown below :
Source: scn.sap.com
No comments:
Post a Comment