Overview

What Is Neo4j Graph Analytics For Snowflake?

Modeling Disruptions in the MTA Subway

Prerequisites

What You Will Need

What You Will Build

What You Will Learn

Loading The Data

Setting Up

Import The Notebook

Permissions

-- Use a role with the required privileges
USE ROLE ACCOUNTADMIN;

-- Create a consumer role for users of the Graph Analytics application
CREATE ROLE IF NOT EXISTS MY_CONSUMER_ROLE;
GRANT APPLICATION ROLE Neo4j_Graph_Analytics.app_user TO ROLE MY_CONSUMER_ROLE;
SET MY_USER = (SELECT CURRENT_USER());
GRANT ROLE MY_CONSUMER_ROLE TO USER IDENTIFIER($MY_USER);

USE SCHEMA MTA.PUBLIC;
CREATE TABLE NODES (nodeId Number);
INSERT INTO NODES VALUES (1), (2), (3), (4), (5), (6);
CREATE TABLE RELATIONSHIPS (sourceNodeId Number, targetNodeId Number);
INSERT INTO RELATIONSHIPS VALUES (1, 2), (2, 3), (4, 5), (5, 6);

-- Grants needed for the app to read consumer data stored in tables and views, using a database role
USE DATABASE MTA;
CREATE DATABASE ROLE IF NOT EXISTS MY_DB_ROLE;
GRANT USAGE ON DATABASE MTA TO DATABASE ROLE MY_DB_ROLE;
GRANT USAGE ON SCHEMA MTA.PUBLIC TO DATABASE ROLE MY_DB_ROLE;
GRANT SELECT ON ALL TABLES IN SCHEMA MTA.PUBLIC TO DATABASE ROLE MY_DB_ROLE;
GRANT SELECT ON ALL VIEWS IN SCHEMA MTA.PUBLIC TO DATABASE ROLE MY_DB_ROLE;
-- Future tables also include tables that are created by the application itself.
-- This is useful as many use-cases require running algorithms in a sequence and using the output of a prior algorithm as input.
GRANT SELECT ON FUTURE TABLES IN SCHEMA MTA.PUBLIC TO DATABASE ROLE MY_DB_ROLE;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA MTA.PUBLIC TO DATABASE ROLE MY_DB_ROLE;
GRANT CREATE TABLE ON SCHEMA MTA.PUBLIC TO DATABASE ROLE MY_DB_ROLE;
GRANT DATABASE ROLE MY_DB_ROLE TO APPLICATION Neo4j_Graph_Analytics;

-- Ensure the consumer role has access to tables created by the application
GRANT USAGE ON DATABASE MTA TO ROLE MY_CONSUMER_ROLE;
GRANT USAGE ON SCHEMA MTA.PUBLIC TO ROLE MY_CONSUMER_ROLE;
GRANT SELECT ON FUTURE TABLES IN SCHEMA MTA.PUBLIC TO ROLE MY_CONSUMER_ROLE;

-- Use the consumer role to run the algorithm and inspect the output
USE ROLE MY_CONSUMER_ROLE;

Cleaning Our Data

For The Table Representing Nodes:

For The table Representing Relationships:

CREATE OR REPLACE TABLE mta.public.nodes AS
SELECT 
  id AS nodeid
FROM mta.public.stations;

CREATE OR REPLACE TABLE mta.public.relationships AS
SELECT 
    sourcenodeid,
    targetnodeid
FROM mta.public.lines;

Running Your Algorithms

CALL neo4j_graph_analytics.graph.dijkstra('CPU_X64_XS', {
'defaultTablePrefix': 'mta.public',
  'project': {
    'nodeTables': ['nodes'], 
    'relationshipTables': {
      'relationships': {
        'sourceTable': 'nodes',
        'targetTable': 'nodes'
      }
    }
  },
  'compute': { 'sourceNode': 68,
                'targetNode': 24, 
                'sourceNodeTable': 'nodes'
            },
  'write': [
    {
    'sourceLabel': 'nodes',
    'targetLabel': 'nodes',
    'outputTable':  'mta.public.paths'
    }
  ]
});

select * from mta.public.paths

WITH flattened AS (
  SELECT 
    VALUE::NUMBER AS nodeid,
    INDEX AS ordering
  FROM mta.public.paths,
       LATERAL FLATTEN(input => nodeids)
),
filtered AS (
  SELECT s.*, f.ordering
  FROM mta.public.stations s
  JOIN flattened f ON s.id = f.nodeid
)
SELECT *
FROM filtered
ORDER BY ordering;

Modeling a Disruption

CREATE OR REPLACE TABLE mta.public.nodes_f AS
SELECT *
FROM mta.public.nodes
WHERE nodeid != 230;

CREATE OR REPLACE TABLE mta.public.relationships_f AS
SELECT *
FROM mta.public.relationships
WHERE sourcenodeid != 230 and targetnodeid != 230;

CALL neo4j_graph_analytics.graph.dijkstra('CPU_X64_XS', {
'defaultTablePrefix': 'mta.public',
  'project': {
    'nodeTables': ['nodes_f'], 
    'relationshipTables': {
      'relationships_f': {
        'sourceTable': 'nodes_f',
        'targetTable': 'nodes_f'
      }
    }
  },
  'compute': { 'sourceNode': 68,
                'targetNode': 24, 
                'sourceNodeTable': 'nodes_f'
            },
  'write': [
    {
    'sourceLabel': 'nodes_f',
    'targetLabel': 'nodes_f',
    'outputTable':  'mta.public.paths_f'
    }
  ]
});

CREATE OR REPLACE VIEW mta.public.filtered_station_captions AS
WITH flattened AS (
  SELECT 
    VALUE::NUMBER AS nodeid,
    INDEX AS ordering
  FROM mta.public.paths_f,
       LATERAL FLATTEN(input => nodeids)
),
filtered AS (
  SELECT 
    s.id AS nodeid,
    s.station_name AS caption, 
    f.ordering,
  FROM mta.public.stations s
  JOIN flattened f ON s.id = f.nodeid
)
SELECT 
    nodeid,
    ordering,
    caption
FROM filtered
ORDER BY ordering;

select * from mta.public.filtered_station_captions

Visualize Your Graph

CREATE OR REPLACE VIEW mta.public.relationships_view AS
WITH ordered_nodes AS (
  SELECT
    nodeid AS sourcenodeid,
    LEAD(nodeid) OVER (ORDER BY ordering) AS targetnodeid
  FROM mta.public.filtered_station_captions
)
SELECT *
FROM ordered_nodes
WHERE targetnodeid IS NOT NULL;

select * from mta.public.relationships_view 

from neo4j_viz.snowflake import from_snowflake
from snowflake.snowpark.context import get_active_session

session = get_active_session()

viz_graph = from_snowflake(
    session,
{
    'nodeTables': ['mta.public.filtered_station_viz'],
    'relationshipTables': {
      'mta.public.relationships_view': {
        'sourceTable': 'mta.public.filtered_station_viz',
        'targetTable': 'mta.public.filtered_station_viz'
      }
    }
  }
)
 

##### specifying which column should be associated with captions
for node in viz_graph.nodes:
    node.caption = str(node.properties["STATION_NAME"])
    
###### now we render
html_object = viz_graph.render()

import streamlit.components.v1 as components

components.html(html_object.data, height=600)

Conclusions And Resources

What You Learned

Resources