Fetching data from Odata or RESTful API to SAP HANA cloud

[zhiyitang]

Dear Community,

Could you please shed light on how to fetch Odata or data from RESTful API (e.g. JSON format) to SAP HANA cloud database? Is it possible without external helpers, or with little manual coding? For instance, when we create an http destination in BTP, would it be possible to use it to load data into Document Store, then convert the JSON files to tabular data?

Many thanks in advance for your insights!

Best regards,

Zhiyi Tang

[Dan_vL]

This answer is for the second part of your question and is taken from step 4 of the tutorial Try Out Multi-Model Functionality with the SAP HANA Database Explorer.

The data in a JSON document store can be returned as JSON, as tabular data, and can be joined with data from regular tables.

A JSON collection can be created as :

CREATE COLLECTION HOTEL.GUEST_NOTES;

Some JSON data is added

INSERT INTO HOTEL.GUEST_NOTES VALUES ('{"FIRST_NAME": "Jenny", "LAST_NAME": "Porter", "REQUEST": "requested a courtesy call at 7:00 am"}');

That data can then be queried

SELECT * FROM HOTEL.GUEST_NOTES; --returns JSON

SELECT FIRST_NAME, LAST_NAME, REQUEST FROM HOTEL.GUEST_NOTES; --returns tabular result

WITH myJSON AS (SELECT GUEST_NOTES FROM HOTEL.GUEST_NOTES) SELECT '[' || STRING_AGG(TO_NVARCHAR(GUEST_NOTES), ',') || ']' FROM myJSON; --returns all the results as one JSON document

WITH GN_VIEW AS (SELECT FIRST_NAME, LAST_NAME, REQUEST FROM HOTEL.GUEST_NOTES) --joins a collection with a table SELECT DISTINCT GN_VIEW.REQUEST, C.FIRSTNAME, GN_VIEW.LAST_NAME, C.ADDRESS FROM GN_VIEW INNER JOIN HOTEL.CUSTOMER AS C ON GN_VIEW.LAST_NAME = C.NAME;

For additional details see the SELECT Statement in the JSON Document Store guide.

If you did wish to copy the data into a table, I believe this could be done using

insert into table name select from statement

Hope that helps,

Dan van Leeuwen