Querying Snowflake Data as a Graph

Summary

In this tutorial, you will:

Create a Snowflake database and import data into it;
Start a PuppyGraph Docker container and query the data as a graph.

Prerequisites

Docker

Please ensure that docker is available.

Docker is required to run the PuppyGraph server. You can download Docker from here.
The installation can be verified by running:

docker version

Configuring Snowflake key-pair authentication

Snowflake supports using key pair authentication for enhanced authentication security as an alternative to basic authentication, such as username and password.

To use key pair authentication, we need to generate key pair and set public key to a Snowflake user.

The following content is based on Snowflake User Guide: Configuring key-pair authentication.

Generate a private key. Run following command in shell
```
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8
```
and input encryption password (passphrase) at prompt Enter Encryption Password:. After this command, we get the private key file rsa_key.p8
Generate a public key. Run following command in shell
```
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
```
and input passphrase you created for the private key in the previous step. After this command, we get the public key file rsa_key.pub The file content will look like this:
```
-----BEGIN PUBLIC KEY-----
MIIBI...
-----END PUBLIC KEY-----
```
Assign the public key to a Snowflake user. Sign in to Snowsight using your account. Open a SQL worksheet, and run the the SQL commands below:
```
ALTER USER <example_user> SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';
```
replace <example_user> with your user name and the RSA_PUBILC_KEY with the content of the public key generated in above step.

Data Preparation

In order to query data as a graph, we need to import data into Snowflake.

Generate data by using Snowflake SQL

Sign in to Snowsight using your account. You must have proper privileges to create databases, schemas and tables.
Open a SQL worksheet, run the the SQL commands below:

CREATE DATABASE PUPPYGRAPH_SAMPLE_DATA;
USE DATABASE PUPPYGRAPH_SAMPLE_DATA;

CREATE SCHEMA IF NOT EXISTS modern;

CREATE TABLE IF NOT EXISTS modern.person (
    id STRING, 
    name STRING, 
    age INTEGER
);

INSERT INTO modern.person (id, name, age) VALUES
    ('v1', 'marko', 29),
    ('v2', 'vadas', 27),
    ('v4', 'josh', 32),
    ('v6', 'peter', 35);

CREATE TABLE IF NOT EXISTS modern.software (
    id STRING, 
    name STRING, 
    lang STRING
);

INSERT INTO modern.software (id, name, lang) VALUES
    ('v3', 'lop', 'java'),
    ('v5', 'ripple', 'java');

CREATE TABLE IF NOT EXISTS modern.created (
    id STRING, 
    from_id STRING, 
    to_id STRING, 
    weight DOUBLE
);

INSERT INTO modern.created (id, from_id, to_id, weight) VALUES
    ('e9', 'v1', 'v3', 0.4),
    ('e10', 'v4', 'v5', 1.0),
    ('e11', 'v4', 'v3', 0.4),
    ('e12', 'v6', 'v3', 0.2);

CREATE TABLE IF NOT EXISTS modern.knows (
    id STRING, 
    from_id STRING, 
    to_id STRING, 
    weight DOUBLE
);

INSERT INTO modern.knows (id, from_id, to_id, weight) VALUES
    ('e7', 'v1', 'v2', 0.5),
    ('e8', 'v1', 'v4', 1.0);

Starting PuppyGraph

Start the PuppyGraph server with the following command.

docker run -p 8081:8081 -p 8182:8182 -p 7687:7687 -e PUPPYGRAPH_PASSWORD=puppygraph123 -v ./rsa_key.p8:/home/rsa_key.p8 --name puppy --rm -itd puppygraph/puppygraph:stable

Suppose your public key file rsa_key.p8 is in current directory.

Modeling the Graph

Step 1: Connecting to Snowflake

Login onto PuppyGraph with puppygraph as the username and puppygraph123 as the password.

Click on Create graph schema to create a new graph schema.

Click on Add more catalogs

Fill in the fields as follows.

Parameter	Value
Catalog type	`Snowflake`
Catalog name	Some name for the catalog as you like.
Username	User name of your snowflake account
Password	Password of your snowflake account. You can input arbitrary words because we do not use it.
JDBC Connection String	JDBC URI like `jdbc:snowflake://<account_identifier>.snowflakecomputing.com/?db=PUPPYGRAPH_SAMPLE_DATA&warehouse=<warehouse>&private_key_file=/home/rsa_key.p8&private_key_file_pwd=<password>"`. Replace the `<account_identifier>` and `<warehouse>` based on snowflake account. `<password>` is the password (passphrase) for the private key file.

Click on Save, then Click on Submit to connect to the Open Catalog.

Step 2: Building the Graph Schema

In the Schema Builder, select the MODERN database and add the first node type to the graph from the table PERSON.

After that use the Auto Suggestion to create other nodes and edges. Select PERSON as the start node (vertex) and add the auto suggested nodes and edges.

The graph schema should look like this: Submit the schema to create the graph.

Step 3: Querying the Graph

PuppyGraph provides a Dashboard that gives the summary of the graph.

Use the Interactive Query UI to further explore the graph by sending queries.

Cleaning up

Run the following command to shut down and remove the PuppyGraph

docker stop puppy