Querying SingleStore Data as a Graph
Summary
In this tutorial, you will:
- Create a SingleStore database and load it with example data;
- Start a PuppyGraph Docker container and query the SingleStore data as a graph.
Prerequisites
Please ensure that docker compose is available. The installation can be verified by running:
See https://docs.docker.com/compose/install/ for Docker Compose installation instructions and https://www.docker.com/get-started/ for more details on Docker.
Accessing the PuppyGraph Web UI requires a browser. However, the tutorial offers alternative instructions for those who wish to exclusively use the CLI.
Deployment
Create a file docker-compose.yaml with the following content:
docker-compose.yaml
services:
puppygraph:
image: puppygraph/puppygraph:stable
pull_policy: always
container_name: puppygraph
environment:
- PUPPYGRAPH_USERNAME=puppygraph
- PUPPYGRAPH_PASSWORD=puppygraph123
networks:
puppy_net:
ports:
- "8081:8081"
- "8182:8182"
- "7687:7687"
singlestoredb:
image: ghcr.io/singlestore-labs/singlestoredb-dev:latest
container_name: singlestoredb
environment:
- ROOT_PASSWORD=puppy
networks:
puppy_net:
ports:
- "3306:3306"
- "8080:8080"
- "9000:9000"
networks:
puppy_net:
name: puppy-singlestore
Then run the following command to start SingleStore and PuppyGraph:
[+] Running 1/1
✔ puppygraph Pulled 1.6s
[+] Running 3/3
✔ Network puppy-singlestore Created 0.1s
✔ Container singlestoredb Started 0.6s
✔ Container puppygraph Started 0.6s
Data Preparation
This tutorial is designed to be comprehensive and standalone, so it includes steps to populate data in SingleStore. In practical scenarios, PuppyGraph can query data directly from your existing SingleStore databases.
Run the following command to start a SingleStore shell:
Input password puppy in the password prompt.
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 126
Server version: 5.7.32 SingleStoreDB source distribution (compatible; MySQL Enterprise & MySQL Commercial)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
singlestore>
Then execute the following SQL statements in the shell to create tables and insert data.
drop database if exists modern;
create database if not exists modern;
create table modern.person (id text, name text, age int);
insert into modern.person values
('v1', 'marko', 29),
('v2', 'vadas', 27),
('v4', 'josh', 32),
('v6', 'peter', 35);
create table modern.software (id text, name text, lang text);
insert into modern.software values
('v3', 'lop', 'java'),
('v5', 'ripple', 'java');
create table modern.created (id text, from_id text, to_id text, weight double);
insert into modern.created values
('e9', 'v1', 'v3', 0.4),
('e10', 'v4', 'v5', 1.0),
('e11', 'v4', 'v3', 0.4),
('e12', 'v6', 'v3', 0.2);
create table modern.knows (id text, from_id text, to_id text, weight double);
insert into modern.knows values
('e7', 'v1', 'v2', 0.5),
('e8', 'v1', 'v4', 1.0);
The above SQL creates the following tables:
| id | name | age |
|---|---|---|
| v1 | marko | 29 |
| v2 | vadas | 27 |
| v4 | josh | 32 |
| v6 | peter | 35 |
| id | name | lang |
|---|---|---|
| v3 | lop | java |
| v5 | ripple | java |
| id | from_id | to_id | weight |
|---|---|---|---|
| e7 | v1 | v2 | 0.5 |
| e8 | v1 | v4 | 1.0 |
| id | from_id | to_id | weight |
|---|---|---|---|
| e9 | v1 | v3 | 0.4 |
| e10 | v4 | v5 | 1.0 |
| e11 | v4 | v3 | 0.4 |
| e12 | v6 | v3 | 0.2 |
Type exit to quit the SingleStore shell.
Modeling a Graph
We then define a graph on top of the data tables we just created. Actually, this is the "Modern" graph defined by Apache Tinkerpop.
Modern Graph
A schema instructs PuppyGraph on mapping data from the SingleStore into a graph. PuppyGraph offers various methods for schema creation. For this tutorial, we've already prepared a schema to help save time.
Create a PuppyGraph schema file schema.json with the following content:
schema.json
{
"catalogs": [
{
"name": "puppygraph",
"type": "singlestore",
"jdbc": {
"username": "root",
"password": "puppy",
"jdbcUri": "jdbc:singlestore://singlestoredb:3306",
"driverClass": "com.singlestore.jdbc.Driver"
}
}
],
"graph": {
"vertices": [
{
"label": "person",
"oneToOne": {
"tableSource": {
"catalog": "puppygraph",
"schema": "modern",
"table": "person"
},
"id": {
"fields": [
{
"type": "String",
"field": "id",
"alias": "ID"
}
]
},
"attributes": [
{
"type": "String",
"field": "name",
"alias": "name"
},
{
"type": "Int",
"field": "age",
"alias": "age"
}
]
},
"cacheConfig": {}
},
{
"label": "software",
"oneToOne": {
"tableSource": {
"catalog": "puppygraph",
"schema": "modern",
"table": "software"
},
"id": {
"fields": [
{
"type": "String",
"field": "id",
"alias": "ID"
}
]
},
"attributes": [
{
"type": "String",
"field": "name",
"alias": "name"
},
{
"type": "String",
"field": "lang",
"alias": "lang"
}
]
},
"cacheConfig": {}
}
],
"edges": [
{
"label": "knows",
"fromVertex": "person",
"toVertex": "person",
"tableSource": {
"catalog": "puppygraph",
"schema": "modern",
"table": "knows"
},
"id": {
"fields": [
{
"type": "String",
"field": "id",
"alias": "ID"
}
]
},
"fromId": {
"fields": [
{
"type": "String",
"field": "from_id",
"alias": "from_id"
}
]
},
"toId": {
"fields": [
{
"type": "String",
"field": "to_id",
"alias": "to_id"
}
]
},
"attributes": [
{
"type": "Double",
"field": "weight",
"alias": "weight"
}
],
"cacheConfig": {}
},
{
"label": "created",
"fromVertex": "person",
"toVertex": "software",
"tableSource": {
"catalog": "puppygraph",
"schema": "modern",
"table": "created"
},
"id": {
"fields": [
{
"type": "String",
"field": "id",
"alias": "ID"
}
]
},
"fromId": {
"fields": [
{
"type": "String",
"field": "from_id",
"alias": "from_id"
}
]
},
"toId": {
"fields": [
{
"type": "String",
"field": "to_id",
"alias": "to_id"
}
]
},
"attributes": [
{
"type": "Double",
"field": "weight",
"alias": "weight"
}
],
"cacheConfig": {}
}
]
}
}
Log into PuppyGraph Web UI at http://localhost:8081 with username puppygraph and password puppygraph123.
PuppyGraph Login
Upload the schema by selecting the file schema.json in the Upload Graph Schema JSON block and clicking on Upload.
Schema Page
Once the schema is uploaded, the schema page shows the visualized graph schema as follows.
Visualized Schema
Alternative: Schema Uploading via CLI
Alternatively, run the following command to upload the schema file:
curl -XPOST -H "content-type: application/json" --data-binary @./schema.json --user "puppygraph:puppygraph123" localhost:8081/schema
The response shows that graph schema has been uploaded successfully:
Alternative: Build a Graph Schema Using the Web UI
The Web UI also provides a step-by-step process for building a graph schema. Begin by clicking Create graph schema on the schema page. The first step is to configure the data source. Use the same configuration as defined in schema.json.
Configure Data Source
Choose tables in the modern database to build vertices and edges.
Choose tables
Add a vertex "person"
Add a vertex "software"
Add an edge "knows"
Add an edge "created"
The graph visualization reflects each successful vertex or edge addition. After adding all those vertices and edges, you will see the visualization of schema graph below.
Prepared schema
Now submit it by clicking Submit.
Querying the Graph
In this tutorial we will use the Gremlin query language to query the Graph. Gremlin is a graph query language developed by Apache TinkerPop. Prior knowledge of Gremlin is not necessary to follow the tutorial. To learn more about it, visit https://tinkerpop.apache.org/gremlin.html.
Click on the Query panel the left side. The Gremlin Query tab offers an interactive environment for querying the graph using Gremlin.
Interactive Gremlin Query Page
Queries are entered on the left side, and the right side displays the graph visualization.
The first query retrieves the property of the person named "marko".
Copy the following query, paste it in the query input, and click on the run button.
The output is plain text like the following:
Now let's also leverage the visualization. The next query gets all the software created by people known to "marko".
Copy the following query, paste it in the query input, and click on the run button.
The output is as follows. There are two paths in the result as "marko" knows "josh" who created "lop" and "ripple".
Interactive Query with Results
Alternative: Querying the graph via CLI
Alternatively, we can query the graph via CLI.
Execute the following command to access the PuppyGraph Gremlin CLI
The welcome screen appears as follows:
____ ____ _
| _ \ _ _ _ __ _ __ _ _ / ___| _ __ __ _ _ __ | |__
| |_) | | | | | | '_ \ | '_ \ | | | | | | _ | '__| / _` | | '_ \ | '_ \
| __/ | |_| | | |_) | | |_) | | |_| | | |_| | | | | (_| | | |_) | | | | |
|_| \__,_| | .__/ | .__/ \__, | \____| |_| \__,_| | .__/ |_| |_|
|_| |_| |___/ |_|
Welcome to PuppyGraph!
version: 0.38
To Learn more about the graph schema:
- Use graph.show() to list all the vertex and edge labels.
- Use graph.show('$FOO') to list all the vertex and edge labels related to $FOO.
- Use graph.describe('$BAR') to list all the attributes of the label $BAR.
See https://tinkerpop.apache.org/gremlin.html to learn more about the Gremlin query language.
Here are some example queries for exploring the graph:
- Use g.V() to list all the vertices.
- Use g.E() to list all the edges.
- Use g.V().count() to get the total number of vertices.
- Use g.E().count() to get the total number of edges.
- Use g.V('$ID').out() to find out vertices that are reachable in 1-hop from the vertex $ID. For example, g.V('person[v1]').out() will find out 1-hop reachable vertices from 'person[v1]'.
- Use g.V('$ID').out().out() similarly to find out 2-hop reachable vertices from the vertex $ID.
puppy-gremlin>
Run the following queries in the console to query the Graph.
Properties of the person named "marko":
To exit PuppyGraph Gremlin Console, enter the command:
Cleaning up
Run the following command to shut down and remove the containers: