Skip to Content

Try Out Multi-Model Functionality with the SAP HANA Database Explorer

test
0 %
Try Out Multi-Model Functionality with the SAP HANA Database Explorer
Details

Try Out Multi-Model Functionality with the SAP HANA Database Explorer

October 4, 2021
Created by
October 4, 2021
Explore the graph, JSON document store, and spatial capabilities in the SAP HANA database explorer.

You will learn

  • How to create a graph, a document store, and import spatial data.
  • How the SAP HANA database explorer can be used with multi-model data.
QR code

Prerequisites

  • An SAP HANA database such as SAP HANA Cloud trial or the SAP HANA, express edition that includes the SAP HANA database explorer
  • You have completed the first 3 tutorials in this group.

This tutorial is meant to be an introduction to this topic. For a deeper dive on the topics of graph and spatial, see the tutorial groups Introduction to SAP HANA Spatial Data Types and Smart Multi-Model Data Processing with SAP HANA Cloud.


Step 1: Create a graph

The following steps will create a graph that can display the distance between hotels in a state using a graph.

  1. Create a vertex table that is to represent the distances between hotels.

    CREATE COLUMN TABLE HOTEL.DISTANCES(
      DKEY INTEGER UNIQUE NOT NULL,
      HSOURCE INTEGER NOT NULL
        REFERENCES HOTEL.HOTEL(HNO),
      HTARGET INTEGER NOT NULL
        REFERENCES HOTEL.HOTEL(HNO),
      DIST_KM DOUBLE
    );
    
  2. Populate the vertex table with distances between hotels that are in the same state.

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

    CREATE GRAPH WORKSPACE HOTEL.DISTANCEGRAPH
    EDGE TABLE HOTEL.DISTANCES
        SOURCE COLUMN HSOURCE
        TARGET COLUMN HTARGET
        KEY COLUMN DKEY
    VERTEX TABLE HOTEL.HOTEL
        KEY COLUMN HNO;
    
  4. Navigate to the graph workspaces catalog object and open it to view the properties of the graph.

    DISTANCEGRAPH properties

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

Log on to answer question
Step 2: Explore a graph using the graph viewer
  1. Open the graph viewer.

    DISTANCEGRAPH graph viewer

    The graph viewer will open in a new tab as shown below.

    DISTANCEGRAPH graph viewer
  2. Set the vertex and edge names using the graph viewer settings.

    Set the vertex label to NAME.

    DISTANCEGRAPH vertex labels

    Set the edge label to DIST_KM.

    DISTANCEGRAPH edge labels
  3. Optionally, adjust a few of the graph vertices to accommodate viewing by dragging and dropping graph vertices.

    DISTANCEGRAPH better viewing
  4. Apply a filter to vertices where STATE is NY and edges where DIST_KM is less than 100 using the graph viewer filter.

    DISTANCEGRAPH vertex filter

    Between filtering, the graph will reset.

    DISTANCEGRAPH edge filter
  5. Highlight the Long Island vertex using the graph viewer settings. The color used to highlight in the image below is #E5F5FC.

    DISTANCEGRAPH highlighted Long Island

Additional graph examples include the Greek Mythology Graph Example and Open Flights. Note that the company graph example does not currently display in the database explorer graph viewer as it does not at this time support the display of homogeneous graphs.

Log on to answer question
Step 3: Create, populate, and query a JSON collection (optional)

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

Note that the creation of a JSON collection is not supported in the SAP HANA Cloud trial.

  1. Enable the JSON document store.

    For an SAP HANA Cloud database, in the creation wizard, enable the document store. enable the document store

    For an on-premise server, add the document service as described at Enable the SAP HANA JSON Document Store.

  2. Create a collection named GUEST_NOTES.

    CREATE COLLECTION HOTEL.GUEST_NOTES;
    
  3. Insert some data.

    INSERT INTO HOTEL.GUEST_NOTES VALUES ('{"FIRST_NAME": "Jenny", "LAST_NAME": "Porter", "REQUEST": "requested  a courtesy call at 7:00 am"}');
    INSERT INTO HOTEL.GUEST_NOTES VALUES ('{"FIRST_NAME": "Jenny", "LAST_NAME": "Porter", "REQUEST": "requested  an extra blanket"}');
    INSERT INTO HOTEL.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. The JSON data can be returned as a JSON document, in a tabular result, or can be joined with data from a table.

    SELECT * FROM HOTEL.GUEST_NOTES;  --returns JSON
    
    SELECT FIRST_NAME, LAST_NAME, REQUEST FROM HOTEL.GUEST_NOTES; --returns tabluar result
    
    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;
    
    collection queries

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

  5. The properties of the collection can also be viewed.

    Collection properties
Log on to answer question
Step 4: Import and view spatial data

This step will import an ESRI shapefile containing points of interest near the Bella Ciente hotel in the city of Longview Texas. A search can then be performed to return the 3 closest golf courses to the hotel.

The import option for ESRI shapefiles is available in the SAP HANA database explorer included with SAP HANA Cloud or in the SAP HANA database explorer SP 13.

  1. At the ARCGIS Hub, search for Points of Interest in and around Longview, Texas.

    Search

    Scroll through the results and choose the selection below.

    results for Longview
  2. Choose to download the data as a shapefile.

    download shapefile
  3. Start the import data wizard.

    Open import data wizard

    Choose Import ESRI Shapefiles and select the LongViewPOI.zip file.

    Import ESRI Shapefile
  4. Choose to import the ESRI shapefile into the schema HOTEL.

    Within the downloaded ESRI shapefile, there is a file named Points_of_Interest.prj. This file mentions the spatial reference system used by this ESRI shapefile. Specify WGS 84 as the spatial reference system.

    Choose schema and reference system
  5. Rename the imported table as it was created using mixed case.

    RENAME TABLE "HOTEL"."Points_of_Interest" TO HOTEL.POI_LONGVIEW;
    
  6. View the table. Notice that the points of interest locations are stored in a column of type ST_GEOMETRY.

    view table editor
  7. View the table data.

    SELECT NAME, SHAPE.ST_ASWKT(), SHAPE FROM HOTEL.POI_LONGVIEW;
    

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

    view the spatial data

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

Log on to answer question
Step 5: Use spatial functions in a query
  1. The following statement shows the list of points of interest within 3 kilometers of the Bella Cliente hotel.
    SELECT
        P.NAME,
        P.FCODE,
        P.ADDRESS,
        ROUND(H.LOCATION.ST_Distance(P.SHAPE, 'kilometer'), 2) as DISTANCE,
        P.LINKED_URL
    FROM HOTEL.HOTEL H, HOTEL.POI_LONGVIEW P
    WHERE
        H.HNO=26 /*Bella Cliente */ AND
        TO_BOOLEAN(H.LOCATION.ST_WithinDistance(NEW ST_Point(SHAPE.ST_AsWKT(), 4326), 3, 'kilometer')) = TRUE
    ORDER BY DISTANCE ASC;
    
    Within 3 km

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

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

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

    google maps

    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).

Which of the following are true?
×

Next Steps

Back to top