Try Out Multi-Model Functionality with SAP HANA Cloud Central and Database Objects App

The following steps will create a knowledge graph that provides information on additional hotel amenities, explores the created knowledge graph using the database objects app, and then will perform a query on the knowledge graph. Knowledge graphs are also known as resource description framework (RDF) named graphs and are stored in a triple store.

Before you can create a knowledge graph, please ensure your instance has the triple store activated. Here are the steps to doing this:

1. Go to Manage Configuration on your SAP HANA Cloud Database.

   

2. Under the Advanced Settings Tab, ensure the Triple Store option is enabled.

   

   The knowledge graph feature is not available for free tier instances. To view what is available in the free tier please refer to SAP HANA Database License

   To learn more about knowledge graphs see Connecting the Facts: SAP HANA Cloud’s Knowledge Graph Engine for Business Context.

3. Execute the following in the SQL Console. This query creates a knowledge graph with several hotels and amenities such as an indoor pool, hot tub, fitness center, etc.

   SQL Copy

   --CALL SPARQL_EXECUTE('DROP GRAPH <kg_hotels>', '', ?, ?);
   CALL SPARQL_EXECUTE('
       INSERT DATA { GRAPH <kg_hotels> { 
           <http://example.org/hotels/Delta> a <Hotel>;
           <name> "Delta";
           <city> "Waterloo";
           <yearBuilt> 2012 .
   
           <http://example.org/hotels/hotelamenities/IndoorPool1> a <IndoorPool>;
           <included_at> <http://example.org/hotels/Delta>;
           <length> 25;
           <width> 10.
   
           <http://example.org/hotels/hotelamenities/Hottub1> a <Hottub>;
           <included_at> <http://example.org/hotels/Delta>;
           <length> 6;
           <width> 6.
   
           <http://example.org/hotels/hotelamenities/FitnessCenter1> a <FitnessCenter>;
           <included_at> <http://example.org/hotels/Delta>.
   
           <http://example.org/hotels/hotelamenities/Keurig> a <CoffeeMaker>;
           <included_at> <http://example.org/hotels/Delta>. 
   
           <http://example.org/hotels/Sunshine> a <Hotel>;
           <name> "Sunshine";
           <city> "Clearwater";
           <yearBuilt> 1975 .
   
           <http://example.org/hotels/hotelamenities/Restaurant1> a <Restaurant>;
           <included_at> <http://example.org/hotels/Sunshine>;
           <seating> 95.
   
           <http://example.org/hotels/hotelamenities/FitnessCenter2> a <FitnessCenter>;
           <included_at> <http://example.org/hotels/Sunshine>.
   
           <http://example.org/hotels/hotelamenities/Laundry1> a <DryCleaning>;
           <included_at> <http://example.org/hotels/Sunshine>. 
   
           <http://example.org/hotels/Congress> a <Hotel>;
           <name> "Congress";
           <city> "Seattle";
           <yearBuilt> 2012 .
   
           <http://example.org/hotels/hotelamenities/IndoorPool2> a <IndoorPool>;
           <included_at> <http://example.org/hotels/Congress>;
           <length> 30;
           <width> 15.
   
           <http://example.org/hotels/hotelamenities/Restaurant2> a <Restaurant>;
           <included_at> <http://example.org/hotels/Congress>;
           <seating> 122.
   
           <http://example.org/hotels/hotelamenities/FitnessCenter3> a <FitnessCenter>;
           <included_at> <http://example.org/hotels/Congress>.
   
           <http://example.org/hotels/hotelamenities/Spa1> a <Spa>;
           <included_at> <http://example.org/hotels/Congress>. 
   
           <http://example.org/hotels/OceanStar> a <Hotel>;
           <name> "Ocean Star";
           <city> "Atlantic City";
           <yearBuilt> 2012 .
   
           <http://example.org/hotels/hotelamenities/IndoorPool3> a <IndoorPool>;
           <included_at> <http://example.org/hotels/OceanStar>;
           <length> 50;
           <width> 30.
   
           <http://example.org/hotels/hotelamenities/Restaurant2> a <Restaurant>;
           <included_at> <http://example.org/hotels/OceanStar>;
           <seating> 205.
   
           <http://example.org/hotels/hotelamenities/Cleaning1> a <CleaningServices>;
           <included_at> <http://example.org/hotels/OceanStar>.
   
           <http://example.org/hotels/hotelamenities/Spa2> a <Spa>;
           <included_at> <http://example.org/hotels/OceanStar>. 
   
           <http://example.org/hotels/LongIsland> a <Hotel>;
           <name> "LongIsland";
           <city> "Long Island";
           <yearBuilt> 2021 .
   
           <http://example.org/hotels/hotelamenities/Entertainment1> a <Entertainment>;
           <included_at> <http://example.org/hotels/LongIsland>.
   
           <http://example.org/hotels/hotelamenities/Restaurant3> a <Restaurant>;
           <included_at> <http://example.org/hotels/LongIsland>;
           <seating> 122.
   
           <http://example.org/hotels/hotelamenities/Cleaning2> a <CleaningServices>;
           <included_at> <http://example.org/hotels/LongIsland>.
   
           <http://example.org/hotels/hotelamenities/Spa3> a <Spa>;
           <included_at> <http://example.org/hotels/LongIsland>. 
   
           <http://example.org/hotels/hotelamenities/Hottub2> a <Hottub>;
           <included_at> <http://example.org/hotels/LongIsland>. 
       } 
       }
       ', '', ?, ?
   );

   Show all 103 lines Show less

   Additional examples can be found at SAP HANA Cloud, SAP HANA Database SPARQL Reference Guide

4. To visualize and examine the knowledge graph database objects, you must first enable the feature in your preferences. Click on your account

   

   Go to the Database Objects tab

   

   Enable RDF Named Graphs

   

   You will now have the RDF Named Graphs view available.

   

   You may also edit visibility by navigating to Select Object Types

   

   The DEFAULT graph is where content goes when it doesn’t have a name. For more information, review the DEFAULT_GRAPHS and Named Graphs help page.

   Click on the kg_hotels graph, and open the graph ontology to see the nodes.

   

5. Run a query to find hotels that have an indoor pool.

   SQL Copy

   SELECT *
   FROM SPARQL_TABLE('
   PREFIX ex: <http://example.org/hotels/>
   SELECT ?hotel ?hotelName ?city ?yearBuilt ?poolLength ?poolWidth
   FROM <kg_hotels>
   WHERE {
       ?hotel a <Hotel> ;
   <name> ?hotelName ;
   <city> ?city ;
   <yearBuilt> ?yearBuilt .
       ?pool a <IndoorPool> ;
   <included_at> ?hotel ;
   <length> ?poolLength ;
   <width> ?poolWidth .
   }
   ');

   

   Further examples of creating SPARQL statements can be found at SPARQL SELECT Queries Using SPARQL_TABLE.

The following steps will create a property graph workspace that can display the distance between hotels in a state. For details about the differences between a knowledge graph and a property graph see Choosing Between Knowledge Graphs and Property Graphs in SAP HANA Cloud and Why Both Matter.

In SAP HANA Cloud, a property graph is made up of a set of vertices and a set of edges. Vertices are stored in vertex tables, while edges are stored in edge tables. Vertex and edge tables are collectively denoted as graph tables.

1. Create a vertex table that represents distances between hotels by executing the following in the SQL console. Run the SQL in the HOTELS schema as USER1.

   SQL Copy

   CREATE COLUMN TABLE DISTANCES(
     DKEY INTEGER UNIQUE NOT NULL,
     HSOURCE INTEGER NOT NULL
       REFERENCES HOTEL(HNO),
     HTARGET INTEGER NOT NULL
       REFERENCES HOTEL(HNO),
     DIST_KM DOUBLE
   );

2. Populate the vertex table with distances between hotels that are in the same state.

   SQL Copy

   --Washington
   INSERT INTO DISTANCES VALUES (1, 10, 11, 11.8);
   INSERT INTO DISTANCES VALUES (2, 11, 10, 11.8);
   
   --New York
   INSERT INTO DISTANCES VALUES (3, 12, 13, 217.3);
   INSERT INTO DISTANCES VALUES (4, 13, 12, 217.3);
   INSERT INTO DISTANCES VALUES (5, 12, 14, 71.9);
   INSERT INTO DISTANCES VALUES (6, 14, 12, 71.9);
   INSERT INTO DISTANCES VALUES (7, 12, 15, 71.5);
   INSERT INTO DISTANCES VALUES (8, 15, 12, 71.5);
   INSERT INTO DISTANCES VALUES (9, 13, 14, 212.2);
   INSERT INTO DISTANCES VALUES (10, 14, 13, 212.2);
   INSERT INTO DISTANCES VALUES (11, 13, 15, 212.1);
   INSERT INTO DISTANCES VALUES (12, 15, 13, 212.1);
   INSERT INTO DISTANCES VALUES (13, 14, 15, 0.4);
   INSERT INTO DISTANCES VALUES (14, 15, 14, 0.4);
   
   --Illinois
   INSERT INTO DISTANCES VALUES (15, 16, 17, 23.5);
   INSERT INTO DISTANCES VALUES (16, 17, 16, 23.5);
   
   --Florida
   INSERT INTO DISTANCES VALUES (17, 18, 19, 219.8);
   INSERT INTO DISTANCES VALUES (18, 19, 18, 219.8);
   INSERT INTO DISTANCES VALUES (19, 18, 20, 323.4);
   INSERT INTO DISTANCES VALUES (20, 20, 18, 323.4);
   INSERT INTO DISTANCES VALUES (21, 19, 20, 333.8);
   INSERT INTO DISTANCES VALUES (22, 20, 19, 333.8);
   
   --California
   INSERT INTO DISTANCES VALUES (23, 21, 22, 149.5);
   INSERT INTO DISTANCES VALUES (24, 22, 21, 149.5);
   INSERT INTO DISTANCES VALUES (25, 21, 23, 35.7);
   INSERT INTO DISTANCES VALUES (26, 23, 21, 35.7);
   INSERT INTO DISTANCES VALUES (27, 22, 23, 163.2);
   INSERT INTO DISTANCES VALUES (28, 23, 22, 163.2);

   Show all 37 lines Show less

3. Create a graph workspace.

   SQL Copy

   CREATE GRAPH WORKSPACE DISTANCEGRAPH
   EDGE TABLE DISTANCES
       SOURCE COLUMN HSOURCE
       TARGET COLUMN HTARGET
       KEY COLUMN DKEY
   VERTEX TABLE HOTEL
       KEY COLUMN HNO;

4. Navigate to database objects, select the Graph Workspaces tab, and open the previously created graph workspace to view its properties.

   

   Ensure Graph Workspaces is enabled in the database objects settings.

For additional information, see SAP HANA Cloud, SAP HANA Database Graph Reference.

1. Open the property graph viewer.

2. Set the vertex name in Graph Viewer by clicking on the settings icon.

   Set the vertex label to NAME.

   

3. Apply a filter to vertices where STATE is NY. After specifying the filter, apply it by pressing the Apply button.

   

   Apply a filter to edges where DIST_KM is less than 100.

   

4. Highlight the Long Island vertex using the property graph viewer settings. You may do so by selecting a color.

   

5. View the details by clicking on the vertex or edge of interest.

   

In the steps above, the property graph viewer was used to visualize hotel distances, filter by state and distance, highlight specific vertices, and view details on individual vertices and edges.

Additional property graph examples include the Greek Mythology Graph Example and Open Flights. The company graph example does not currently display in the viewer as it does not currently support the display of homogeneous graphs. Property graph workspaces may also be viewed using the SAP HANA plug-in for Cytoscape.

The shortest path algorithm can be used to provide the optimal route between two vertices. The nearest neighbor algorithm can be used to show only the vertices that are connected to a specified vertex.

The following steps will walk through using the shortest path algorithm to determine the optimal route from Airport Hotel in Rosemont, IL to Regency Hotel in Seattle, WA.

1. Execute the following in SQL to add a few connections between hotels in different states.

   SQL Copy

   --Midtown New York to Lake Michigan Chicago
   INSERT INTO DISTANCES VALUES (29,14,16,1227);
   INSERT INTO DISTANCES VALUES (30,16,14,1227);
   
   --Long Island New York to Lake Michigan Chicago
   INSERT INTO DISTANCES VALUES (31,12,16,1357);
   INSERT INTO DISTANCES VALUES (32,16,12,1357);
   
   --Long Island New York to Beach Florida
   INSERT INTO DISTANCES VALUES (33,12,19,1738);
   INSERT INTO DISTANCES VALUES (34,19,12,1738);
   
   --Congress Seattle to Star California
   INSERT INTO DISTANCES VALUES (35,10,23,1817);
   INSERT INTO DISTANCES VALUES (36,23,10,1817);
   
   --Indian Horse California to Beach Florida
   INSERT INTO DISTANCES VALUES (37,22,19,3861);
   INSERT INTO DISTANCES VALUES (38,19,22,3861);
   
   --Atlantic Florida to Long Beach California
   INSERT INTO DISTANCES VALUES (39,20,21,4348);
   INSERT INTO DISTANCES VALUES (40,21,20,4348);

2. After removing the previously applied filters, navigate to the property graph viewer, and select the algorithms tab. Update the Algorithm field to “Shortest Path”, specify the values shown below, and click Apply.

   

   After applying the algorithm, the shortest path is highlighted in the graph viewer, showing the optimal route between the two specified hotels as a chain of connected vertices and edges.

   

SAP HANA provides the ability to store and query JSON data. This can be useful if the schema of the data is often changed or if you wish to join data in SQL queries that comes from both SQL tables and JSON data.

The following steps will demonstrate how to create a JSON collection that can be used to collect notes about customers staying at a hotel.

The creation of a JSON collection is not supported in the SAP HANA Cloud free tier. To view what is available in the free tier please refer to SAP HANA Database License.

1. Enable the JSON document store.

   For an SAP HANA Cloud database, in the creation wizard, or for an existing instance, the Manage Configuration dialog, enable the document store.

   

2. Create a collection named GUEST_NOTES.

   SQL Copy

   CREATE COLLECTION GUEST_NOTES;

3. Insert some data.

   SQL Copy

   INSERT INTO GUEST_NOTES VALUES ('{"FIRST_NAME": "Jenny", "LAST_NAME": "Porter", "REQUEST": "requested a courtesy call at 7:00 am"}');
   INSERT INTO GUEST_NOTES VALUES ('{"FIRST_NAME": "Jenny", "LAST_NAME": "Porter", "REQUEST": "requested an extra blanket"}');
   INSERT INTO GUEST_NOTES VALUES ('{"title": "Mr.", "FIRST_NAME": "Peter", "LAST_NAME": "Brown", "REQUEST": "requested an earl grey at 2:00"}');

   Notice that the structure of the Guest Notes does not need to be defined in advance.

4. It is also possible to use the Support for JSON Schema feature to define rules for your documents to adhere to. The example below restricts the “title” property to only accept strings.

   SQL Copy

   CREATE COLLECTION "GUEST_NOTES_WITH_SCHEMA" JSON SCHEMA '{
       "$schema": "http://json-schema.org/draft-07/schema#",
           "properties": {
               "title": {
                   "type": "string"
               }
           }
       }';
   
   INSERT INTO "GUEST_NOTES_WITH_SCHEMA" VALUES ('{"title": 1, "FIRST_NAME": "Peter", "LAST_NAME": "Brown", "REQUEST": "requested an earl grey at 2:00"}'); -- throws error 

   

5. The JSON data can be returned as a JSON document, in a tabular result, or can be joined with data from a table.

   SQL Copy

   SELECT * FROM GUEST_NOTES;  --returns JSON
   
   SELECT FIRST_NAME, LAST_NAME, REQUEST FROM GUEST_NOTES; --returns tabular result
   
   WITH myJSON AS (SELECT GUEST_NOTES FROM 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 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 CUSTOMER AS C ON GN_VIEW.LAST_NAME = C.NAME;

   

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

6. The properties of the JSON collection can also be viewed under JSON Collections in Database Objects.

   

   Click on the JSON Data tab to view the JSON text.

   

Further examples can be found in the The Small JSON Document Store Cookbook.

JSON collections can be imported using the import data wizard but are required to be in a specified format as mentioned at Import and Export. An alternative method of performing an import is shown in this step.

1. Find a JSON collection to be imported. The example below uses the Parks dataset from City of Waterloo Open Data downloaded as a GeoJSON file. Rename the file to be ParksinWaterloo.geojson to match the code below.

2. Create a Python application and update the host variable below.

   Shell Copy

   notepad loadJSON.py

   Python Copy

   from hana_ml import docstore
   from hana_ml.dataframe import ConnectionContext
   
   import json
   
   host = 'xxxxx-ee5a-4782-bc7e-297099099b59.hana.prod-ca10.hanacloud.ondemand.com'
   port = 443
   user = 'USER1'
   passwd = 'Password1'
   
   try :
       conn = ConnectionContext(address=host, port=port, user=user, password=passwd)
   except ConnectionContext.Error as er:
       print('Connect failed, exiting')
       print(er)
       exit()
   
   #If no errors, print connected
   print('connected')
   
   with open("ParksInWaterloo.geojson") as json_file:
       data = json.load(json_file)
   
   docstore.create_collection_from_elements(conn, 
       collection_name="WATERLOO_PARKS", elements=[data], drop_exist_coll=True, schema="HOTELS")

3. Add the required packages.

   Shell Copy

   pip install hana_ml
   pip install shapely

4. Run the application.

   Shell Copy

   python loadJSON.py 

   

5. Query the imported collection.

   SQL Copy

   SELECT CARDINALITY("features")  FROM "HOTELS"."WATERLOO_PARKS";
   SELECT "features" FROM "HOTELS"."WATERLOO_PARKS";
   SELECT "features" FROM "HOTELS"."WATERLOO_PARKS" UNNEST "features" as "f";
   SELECT "f"."properties"."PARK_NAME", "f"."properties"."TENNIS_CRT" FROM "HOTELS"."WATERLOO_PARKS" UNNEST "features" as "f" ORDER BY "f"."properties"."TENNIS_COURT" DESC;

   

Additional details can be found at hana_ml.docstore package.

This step will import an ESRI shapefile or optionally a GeoJSON file containing points of interest in the city of Waterloo Ontario. The ESRI shapefile import will result in a table while the JSON import will result in a JSON Collection. In the following step, a search will be performed to return the closest points of interest to the Delta hotel located in Waterloo.

1. At the ARCGIS Hub, search for Points of Interest Waterloo. Scroll through the results and choose the selection below.

   

2. Choose to download the data as a shapefile. The final sub-steps provide some details on how a GeoJSON file can be imported.

   

3. Unzip the downloaded shapefile. Upload all of the extracted files (Points_of_Interest.shp, .dbf, .prj, .shx, .cpg, .xml) to your data lake Files instance using by navigating to the Data Lake Files app, selecting the desired file, and using the upload button.

   The IMPORT AS SHAPEFILE SQL requires the individual shapefile components to be present in data lake Files — not the .zip file.

   

4. If not already done, set up the trust between your SAP HANA database and data lake Files and create the HTTPS PSE and credential. The full setup is covered in the Export and Import Data with SAP HANA Cloud Data Lake Files tutorial.

   By default, the database server adds the following spatial reference systems to a new database. Additionally, the ST_SPATIAL_REFERENCE_SYSTEMS System View can be queried for available spatial reference systems.

   Import the shapefile using SQL, replacing the hdlfs:// path with your data lake Files REST API endpoint.

   SQL Copy

   IMPORT HOTELS.POI_WATERLOO AS SHAPEFILE FROM 'hdlfs://<your-data-lake-files-endpoint>/Points_of_Interest' WITH SRID 4326 CREDENTIAL 'DL_FILES';

   The REST API endpoint can be copied by clicking the three dots in the Actions column next to your data lake Files instance in SAP HANA Cloud Central. Replace https:// with hdlfs:// and append the base filename without extension. For additional details see Import ESRI Shapefiles Using SQL Commands and Spatial Reference Systems.

   Verify the import was successful.

   SQL Copy

   SELECT * FROM HOTELS.POI_WATERLOO;

   

5. View the table in Database Objects. Notice that the points of interest locations are stored in a column of type ST_GEOMETRY.

   

6. Perform the below query.

   SQL Copy

   SELECT FACILITY, SHAPE.ST_AsWKT(), SHAPE FROM HOTELS.POI_WATERLOO;

   Notice that the location data can be formatted in a more readable format using the methods ST_AsWKT or ST_AsEWT which in addition shows the SRID.

   

   Additional details on spatial reference systems can be found at SAP HANA Spatial Reference for SAP HANA Cloud.

   For an optional example of importing a GeoJSON file as a JSON collection, see Export and Import Data and Schema with SAP HANA Database Explorer.

1. The following statement shows the list of points of interest within 3 kilometers of the Delta hotel. Ensure you are in the HOTELS schema.

   SQL Copy

   SELECT
       P.FACILITY,
       P.TYPE,
       P.ADDRESS,
       ROUND(H.LOCATION.ST_Distance(P.SHAPE, 'kilometer'), 2) as DISTANCE,
       P.SHAPE
   FROM HOTEL H, POI_WATERLOO P
   WHERE
       H.HNO=26 /*Delta*/ AND
       TO_BOOLEAN(H.LOCATION.ST_WithinDistance(NEW ST_Point(SHAPE.ST_AsWKT(), 4326), 3, 'kilometer')) = TRUE
   ORDER BY DISTANCE ASC;

   

   For additional details, see ST_Point Type, ST_Distance, ST_WithinDistance, and ST_UnionAggr Method.

   To view all access methods for spatial data, see SAP HANA Spatial Reference for SAP HANA Cloud - Accessing and Manipulating Spatial Data.

   The latitude and longitude for a location in Google Maps can be obtained for a given address via the marker’s context menu.

   

   It should also be noted that when a used in a ST_POINT, x is longitude and y is latitude so the above point would be represented as NEW ST_Point('POINT (-94.71832 32.50459)', 4326).

Congratulations! You have explored a few of the multi-model features in SAP HANA and are now familiar with graph workspaces, JSON collections, and spatial data when using the SAP HANA database explorer.