Skip to Content

Pattern Matching Using OpenCypher in SQL

test
0 %
Pattern Matching Using OpenCypher in SQL
Details

Pattern Matching Using OpenCypher in SQL

January 14, 2021
Created by
January 14, 2021
Execute pattern matching queries in SAP HANA Graph using OpenCypher.

You will learn

  • How to execute OpenCypher pattern matching queries
QR code

Prerequisites


Step 1: OpenCypher in SAP HANA SQL

OpenCypher is a declarative graph query language for graph pattern matching developed by the OpenCypher Implementers Group.

SAP HANA Graph allows you to use OpenCypher directly in SQL. For more please refer to online documentation for SAP HANA Cloud.

Go to the SQL Console and execute the following query.

SELECT * FROM OPENCYPHER_TABLE( GRAPH WORKSPACE "SKIING"."SKIING" QUERY
    '
    MATCH (point1)
    WHERE point1.ticket_office = ''TRUE''
    RETURN point1.node_id as station_id, point1.ticket_office as is_ticket_office
    '
) ORDER BY "station_id"

Only matched nodes with ticket_office = ‘TRUE’ have been selected.

Ticket Offices
Log on to answer question
Step 2: More examples with OpenCypher

Copy the statement and paste it into Cypher’s code field.

SELECT * FROM OPENCYPHER_TABLE( GRAPH WORKSPACE "SKIING"."SKIING" QUERY
    '
	MATCH (point1)-[conn]->(point2)
	WHERE point1.ticket_office = ''TRUE'' AND point2.restaurant = ''TRUE''
	RETURN point1.node_id as station_start, conn.edge_id as connection, conn.mode as conn_mode, point2.node_id as station_end
    '
) ORDER BY "station_start"

Now you selected only edges (or relationships in Cypher terminology), where you can go directly from a station with a ticket office to a station with a restaurant.

Restaurants

Now let’s find all runs for beginners.

SELECT * FROM OPENCYPHER_TABLE( GRAPH WORKSPACE "SKIING"."SKIING" QUERY
    '
	MATCH (sta1)-[lift]->(sta2), (sta2)-[blue]->(sta1)
	WHERE lift.mode = ''lift'' AND blue.difficulty = ''blue''
	RETURN sta1.node_id AS station_start, lift.edge_id AS lift_up, blue.edge_id AS run_down, sta2.node_id AS station_end
    '
) ORDER BY "station_start"

You selected only pairs of stations where you can go up using a lift, and then go down using a blue run (blue runs are the easiest for beginners).

Beginners
Log on to answer question
Step 3: Pattern Matching in calculation scenarios in SAP HANA 2.0

The following functionality is applicable only to SAP HANA 2.0, but not to SAP HANA Cloud!

SAP HANA 2.0 Graph provides a new graph calculation node that can be used in calculation scenarios (think objects generated from calculated views). This node allows you to execute one of the available actions on the given graph workspace and provide results as table output. The action MATCH_SUBGRAPHS allows you to execute pattern matching queries written in the supported subset of the OpenCypher query language on the given graph workspace. Calculation scenarios can be created with SQL as shown in the following section.

Go back to the SQL Console and execute the following code.

SET SCHEMA "SKIING";

--DROP CALCULATION SCENARIO "CYPHER" CASCADE;
CREATE CALCULATION SCENARIO CYPHER USING '<?xml version="1.0"?>
<cubeSchema version="2" operation="createCalculationScenario" defaultLanguage="en">
  <calculationScenario name="CYPHER">
    <calculationViews>
      <graph name="match_subgraphs_node" defaultViewFlag="true"
      workspace="SKIING" action="MATCH_SUBGRAPHS">
        <expression>
          <![CDATA[
          MATCH (sta1)-[lift]->(sta2), (sta2)-[blue]->(sta1)
          WHERE lift.mode = ''lift'' AND blue.difficulty = ''blue''
          RETURN sta1.node_id AS Lower_station, lift.edge_id AS Lift_up,
          sta2.node_id AS Upper_station, blue.edge_id AS Run_down
          ]]>
        </expression>
        <viewAttributes>
            <viewAttribute name="Lower_station" datatype="integer"/>
            <viewAttribute name="Lift_up"  datatype="integer"/>
            <viewAttribute name="Upper_station" datatype="integer"/>
            <viewAttribute name="Run_down" datatype="integer"/>
        </viewAttributes>
      </graph>
    </calculationViews>
  </calculationScenario>
</cubeSchema>
' WITH PARAMETERS ('EXPOSE_NODE'=('match_subgraphs_node', 'CYPHER'));

SELECT * from "CYPHER";

You can see Column Views now have CYPHER object created.

As well data is selected from that calculation view using the pattern matching.

calc view with OpenCypher
What are the possible ways to call OpenCypher pattern matching statement in SAP HANA Cloud?
×

Next Steps

Back to top