Multi Model - Document Store

Objective

After completing this lesson, you will be able to use SAP HANA Cloud’s semi-structured document store to combine JSON data with structured customer data

SAP HANA Cloud Document Store provides the ability to use complex semi-structured business data.

Some of the features include:

Native support for JSON documents
Full schema flexibility
Dedicated storage with the processing power of an in-memory database
Support for operations on JSON documents through SQL
Integration with the data structures of common programming languages

Try it out!

Switch to the Database Explorer to work through the following exercises which use the dataset from table GX_REVIEWS in the local schema.

Select the catalog object JSON Collections to display the list of Collections.
Right-click on the collection GX_REVIEWS.
Select Generate SELECT Statement.

Note

SAP HANA Cloud uses the same SQL to access collections as column and row tables.

Execute the provided statement in the resulting SQL console.

Add a filter clause to the query by running the following statement.

Code Snippet

1
SELECT * FROM "GX_REVIEWS" WHERE REVIEW_ID='R_00012';

The result is in a JSON string:

Specify each of the columns in the SQL statement to see the result in a relational format:

Code Snippet

12
SELECT REVIEW_ID,CUSTOMER_ID,PRODUCT_ID,REVIEW_RATING,REVIEW_TEXT 
FROM "GX_REVIEWS" WHERE REVIEW_ID='R_00012';

Insert a new JSON Object to the collection.

Code Snippet

12345
INSERT INTO "GX_REVIEWS" VALUES('{"REVIEW_ID":"R_100123",
"CUSTOMER_ID":"C_000000205",
"PRODUCT_ID":"P_0046",
"REVIEW_RATING":5,
"REVIEW_TEXT":"Absolutely perfect"}');

Query the collection to look for the new entry.

Code Snippet

1
SELECT * FROM "GX_REVIEWS" WHERE REVIEW_ID='R_100123';

Update the inserted entry and query the entry.

Code Snippet

12
UPDATE "GX_REVIEWS" SET REVIEW_TEXT='Hello again' WHERE REVIEW_ID='R_100123';
SELECT * FROM "GX_REVIEWS" WHERE REVIEW_ID='R_100123';

Aggregate the JSON Objects.

Code Snippet

1234
SELECT PRODUCT_ID,AVG(TO_BIGINT(REVIEW_RATING)) 
FROM "GX_REVIEWS" 
GROUP BY PRODUCT_ID 
ORDER BY PRODUCT_ID ASC;

The JSON data in the document store can easily combine with business data.

Code Snippet

12345
WITH myView AS 
(SELECT PRODUCT_ID as PID,AVG(TO_BIGINT(REVIEW_RATING)) as AVGRATING 
FROM "GX_REVIEWS" GROUP BY PRODUCT_ID )
       SELECT PID,PRODUCT_NAME,AVGRATING FROM myView INNER JOIN "GX_PRODUCTS"
       ON myView.PID = PRODUCT_ID;

Finally, join Reviews with Customer and Product data to add more context:

Code Snippet

123456
create view myView AS 
SELECT PRODUCT_ID PID, CUSTOMER_ID,PRODUCT_NAME, REVIEW_RATING,REVIEW_TEXT 
FROM GX_REVIEWS; 
(SELECT PID,P.PRODUCT_NAME, CUSTOMER_ID,REVIEW_RATING FROM myView 
INNER JOIN GX_PRODUCTS AS P
ON myView.PID = P.PRODUCT_ID);

Now add in the customer details and review text:

Code Snippet

123456
select distinct PR.PID,PR.CUSTOMER_FIRSTNAME,GP.PRODUCT_NAME, 
PR.REVIEW_RATING, PR.REVIEW_TEXT from (
select * 
from MYVIEW RE, GX_CUSTOMERS GC
where RE.CUSTOMER_ID = GC.CUSTOMER_ID) PR, GX_PRODUCTS GP
where PR.PID = GP.PRODUCT_ID;

Well done!! This completes the lesson on the SAP HANA Cloud Document Store. For further information on this topic, check out the following link:

SAP HANA Cloud, SAP HANA Database JSON Document Store Guide

SAP HANA Cloud Document Store provides the ability to use complex semi-structured business data.

Some of the features include:

Native support for JSON documents
Full schema flexibility
Dedicated storage with the processing power of an in-memory database
Support for operations on JSON documents through SQL
Integration with the data structures of common programming languages

Try it out!

Switch to the Database Explorer to work through the following exercises which use the dataset from table GX_REVIEWS in the local schema.

Select the catalog object JSON Collections to display the list of Collections.
Right-click on the collection GX_REVIEWS.
Select Generate SELECT Statement.

Note

SAP HANA Cloud uses the same SQL to access collections as column and row tables.

Execute the provided statement in the resulting SQL console.

Add a filter clause to the query by running the following statement.

Code Snippet

1
SELECT * FROM "GX_REVIEWS" WHERE REVIEW_ID='R_00012';

The result is in a JSON string:

Specify each of the columns in the SQL statement to see the result in a relational format:

Code Snippet

12
SELECT REVIEW_ID,CUSTOMER_ID,PRODUCT_ID,REVIEW_RATING,REVIEW_TEXT 
FROM "GX_REVIEWS" WHERE REVIEW_ID='R_00012';

Insert a new JSON Object to the collection.

Code Snippet

12345
INSERT INTO "GX_REVIEWS" VALUES('{"REVIEW_ID":"R_100123",
"CUSTOMER_ID":"C_000000205",
"PRODUCT_ID":"P_0046",
"REVIEW_RATING":5,
"REVIEW_TEXT":"Absolutely perfect"}');

Query the collection to look for the new entry.

Code Snippet

1
SELECT * FROM "GX_REVIEWS" WHERE REVIEW_ID='R_100123';

Update the inserted entry and query the entry.

Code Snippet

12
UPDATE "GX_REVIEWS" SET REVIEW_TEXT='Hello again' WHERE REVIEW_ID='R_100123';
SELECT * FROM "GX_REVIEWS" WHERE REVIEW_ID='R_100123';

Aggregate the JSON Objects.

Code Snippet

1234
SELECT PRODUCT_ID,AVG(TO_BIGINT(REVIEW_RATING)) 
FROM "GX_REVIEWS" 
GROUP BY PRODUCT_ID 
ORDER BY PRODUCT_ID ASC;

The JSON data in the document store can easily combine with business data.

Code Snippet

12345
WITH myView AS 
(SELECT PRODUCT_ID as PID,AVG(TO_BIGINT(REVIEW_RATING)) as AVGRATING 
FROM "GX_REVIEWS" GROUP BY PRODUCT_ID )
       SELECT PID,PRODUCT_NAME,AVGRATING FROM myView INNER JOIN "GX_PRODUCTS"
       ON myView.PID = PRODUCT_ID;

Finally, join Reviews with Customer and Product data to add more context:

Code Snippet

123456
create view myView AS 
SELECT PRODUCT_ID PID, CUSTOMER_ID,PRODUCT_NAME, REVIEW_RATING,REVIEW_TEXT 
FROM GX_REVIEWS; 
(SELECT PID,P.PRODUCT_NAME, CUSTOMER_ID,REVIEW_RATING FROM myView 
INNER JOIN GX_PRODUCTS AS P
ON myView.PID = P.PRODUCT_ID);

Now add in the customer details and review text:

Code Snippet

123456
select distinct PR.PID,PR.CUSTOMER_FIRSTNAME,GP.PRODUCT_NAME, 
PR.REVIEW_RATING, PR.REVIEW_TEXT from (
select * 
from MYVIEW RE, GX_CUSTOMERS GC
where RE.CUSTOMER_ID = GC.CUSTOMER_ID) PR, GX_PRODUCTS GP
where PR.PID = GP.PRODUCT_ID;

Well done!! This completes the lesson on the SAP HANA Cloud Document Store. For further information on this topic, check out the following link:

SAP HANA Cloud, SAP HANA Database JSON Document Store Guide

Next unit