PuppyGraph Amazon Implementation

Schema Diagram and Graph Statistics

Node Statistics

Node Label	Count
Product	23,643,222
Category	2,487,388
Review	230,139,802
Image	17,081,240
ReviewImage	6,538,802
Reviewer	43,249,276

Edge Statistics

Relationship	Count
(:Review)-[:REVIEWS]->(:Product)	22,531,444
(:Product)-[:HAS_PRODUCT_IMAGE]->(:Image)	22,531,444
(:Product)-[:ALSO_BUY]->(:Product)	97,123,542
(:Reviewer)-[:WROTE]->(:Review)	230,139,802
(:Review)-[:HAS_REVIEW_IMAGE]->(:Image)	6,840,462
(:Product)-[:IN_CATEGORY]->(:Category)	62,832,310
(:Product)-[:ALSO_VIEW]->(:Product)	92,658,319

Data Loading

Overview

To preprocess the amazon dataset and prepare it for ingestion into PuppyGraph, we first initialized a PySpark session configured with Delta Lake, Hive Metastore, and HDFS support. The following configuration launches Spark in cluster mode (Spark Standalone) and enables full Delta compatibility with Spark 3.4.

Delta Lake Preprocessing

So first, we need to create DataFrames from the raw JSON files, and write things into Delta Lake tables.

python

# --------------------------------------------------
# Ingesting Amazon Relationship (Edge) Data
# --------------------------------------------------

also_buy_edge_df = spark.read.json("hdfs://master:9000/tg/amazon/also_buy_edge")
also_view_edge_df = spark.read.json("hdfs://master:9000/tg/amazon/also_view_edge")
has_product_image_edge_df = spark.read.json("hdfs://master:9000/tg/amazon/has_product_image_edge")
has_review_image_edge_df = spark.read.json("hdfs://master:9000/tg/amazon/has_review_image_edge")
in_category_edge_df = spark.read.json("hdfs://master:9000/tg/amazon/in_category_edge")
reviews_edge_df = spark.read.json("hdfs://master:9000/tg/amazon/reviews_edge")
wrote_edge_df = spark.read.json("hdfs://master:9000/tg/amazon/wrote_edge")

# --------------------------------------------------
# Ingesting Amazon Entity (Node) Data
# --------------------------------------------------

category_df = spark.read.json("hdfs://master:9000/tg/amazon/category_json")
image_df = spark.read.json("hdfs://master:9000/tg/amazon/image_json")
product_df = spark.read.json("hdfs://master:9000/tg/amazon/product_json")
review_df = spark.read.json("hdfs://master:9000/tg/amazon/review_json")
reviewer_df = spark.read.json("hdfs://master:9000/tg/amazon/reviewer_json")
reviewimage_df = spark.read.json("hdfs://master:9000/tg/amazon/reviewimage_json")

Then we proceed to build Delta Lake tables for edges.

python

# --------------------------------------------------
# Edge tables (Relationships)
# --------------------------------------------------

also_buy_edge_df.write.format("delta").mode("overwrite").saveAsTable("amazon.also_buy_edge")
also_view_edge_df.write.format("delta").mode("overwrite").saveAsTable("amazon.also_view_edge")
has_product_image_edge_df.write.format("delta").mode("overwrite").saveAsTable("amazon.has_product_image_edge")
has_review_image_edge_df.write.format("delta").mode("overwrite").saveAsTable("amazon.has_review_image_edge")
in_category_edge_df.write.format("delta").mode("overwrite").saveAsTable("amazon.in_category_edge")
reviews_edge_df.write.format("delta").mode("overwrite").saveAsTable("amazon.reviews_edge")
wrote_edge_df.write.format("delta").mode("overwrite").saveAsTable("amazon.wrote_edge")

# --------------------------------------------------
# Entity tables (Nodes/Vertices)
# --------------------------------------------------

category_df.write.format("delta").mode("overwrite").saveAsTable("amazon.category")
image_df.write.format("delta").mode("overwrite").saveAsTable("amazon.image")
product_df.write.format("delta").mode("overwrite").saveAsTable("amazon.product")
review_df.write.format("delta").mode("overwrite").saveAsTable("amazon.review")
reviewer_df.write.format("delta").mode("overwrite").saveAsTable("amazon.reviewer")
reviewimage_df.write.format("delta").mode("overwrite").saveAsTable("amazon.reviewimage")

Define Nodes

First we define Product nodes. For Product, select asin as the key, and add these attributes: asin, brand.

Then we define the Image nodes.

Then we define the ReviewImage nodes.

Then we define the Category nodes.

Then we define the Reviewer nodes.

Then we define the Review nodes.

Define Edges

Then we define the HAS_PRODUCT_IMAGE edges from Product to Image.

Then we define the HAS_REVIEW_IMAGE edges from Review to ReviewImage.

Then we define the ALSO_BUY edges from Product to Product.

Then we define the ALSO_VIEW edges from Product to Product.

Then we define the IN_CATEGORY edges from Product to Category.

Then we define the REVIEWS edges from Review to Product.

Then we define the WROTE edges from Reviewer to Review.

Queries

Select a query below to view its logic and description.

1. Neighborhood

2. Path

No queries in this category.

3. Pattern

No queries in this category.

4. Aggregation

5. Analytics

6. Others

Q1. Products with the Most Reviews

Subcategory: T5.1 Degree Centrality

This query finds the 200 products with the highest number of reviews, marks them as seed products, and returns each product's identifier, display title, and review count.

cypher

MATCH (p:Product)<-[:REVIEWS]-(r:Review)
WITH p, count(r) AS ReviewCount
ORDER BY ReviewCount DESC
LIMIT 200
RETURN p.asin AS ASIN, coalesce(p.title, p.asin) AS Product, ReviewCount;

Equivalent query in GQL standard:

gql

MATCH (p:Product)<-[:REVIEWS]-(r:Review)
WITH p, count(r) AS ReviewCount
ORDER BY ReviewCount DESC
LIMIT 200
SET p:SeedProduct
RETURN p.asin AS ASIN, coalesce(p.title, p.asin) AS Product, ReviewCount;

Q2. Highly Rated Products with Many Reviews

Subcategory: T5.1 Degree Centrality

This query finds products with at least 50 reviews and an average rating of at least 4.0, returning the most-reviewed high-quality products first.

cypher

MATCH (p:Product)<-[:REVIEWS]-(r:Review)
WHERE r.overall IS NOT NULL
WITH p, avg(r.overall) AS AvgRating, count(r) AS ReviewCount
WHERE ReviewCount >= 50 AND AvgRating >= 4.0
RETURN p.asin AS ASIN,
    coalesce(p.title, p.asin) AS Product,
    round(AvgRating, 2) AS AvgRating,
    ReviewCount
ORDER BY ReviewCount DESC, AvgRating DESC
LIMIT 200;

Equivalent query in GQL standard:

gql

MATCH (p:Product)<-[:REVIEWS]-(r:Review)
WHERE r.overall IS NOT NULL
WITH p, avg(r.overall) AS AvgRating, count(r) AS ReviewCount
WHERE ReviewCount >= 50 AND AvgRating >= 4.0
RETURN p.asin AS ASIN,
    coalesce(p.title, p.asin) AS Product,
    round(AvgRating, 2) AS AvgRating,
    ReviewCount
ORDER BY ReviewCount DESC, AvgRating DESC
LIMIT 200;

Q3. Most Active Reviewers

Subcategory: T5.1 Degree Centrality

This query identifies the 200 reviewers who wrote the most reviews, marks them as top reviewers, and returns their IDs, names, and review counts.

cypher

MATCH (u:Reviewer)-[:WROTE]->(r:Review)
WITH u, count(r) AS ReviewCount
ORDER BY ReviewCount DESC
LIMIT 200
RETURN u.reviewerID AS ReviewerID,
    coalesce(u.name, u.reviewerID) AS ReviewerName,
    ReviewCount
ORDER BY ReviewCount DESC;

Equivalent query in GQL standard:

gql

MATCH (u:Reviewer)-[:WROTE]->(r:Review)
WITH u, count(r) AS ReviewCount
ORDER BY ReviewCount DESC
LIMIT 200
SET u:TopReviewer
RETURN u.reviewerID AS ReviewerID,
    coalesce(u.name, u.reviewerID) AS ReviewerName,
    ReviewCount
ORDER BY ReviewCount DESC;

Q4. Reviewers Who Write the Longest Reviews

Subcategory: Other

This query takes the top reviewers identified earlier and ranks them by the average length of their review text, returning reviewer identity, review count, and average review length.

cypher

MATCH (u:Reviewer)-[:WROTE]->(r:Review)
WHERE r.reviewText IS NOT NULL
WITH u,
    count(r) AS ReviewCount,
    avg(size(r.reviewText)) AS AvgReviewLength
ORDER BY ReviewCount DESC
LIMIT 200
RETURN u.reviewerID AS ReviewerID,
    coalesce(u.name, u.reviewerID) AS ReviewerName,
    ReviewCount,
    AvgReviewLength
ORDER BY AvgReviewLength DESC;

Equivalent query in GQL standard:

gql

MATCH (u:TopReviewer)-[:WROTE]->(r:Review)
RETURN u.reviewerID AS ReviewerID,
    coalesce(u.name, u.reviewerID) AS ReviewerName,
    count(r) AS ReviewCount,
    avg(size(r.reviewText)) AS AvgReviewLength
ORDER BY AvgReviewLength DESC
LIMIT 200;

Q5. People Who Bought This Also Bought (Seed Expansion)

Subcategory: T1.1 One-Hop Neighbors

This query expands one hop from the seed products through ALSO_BUY edges, excludes the seeds themselves, and ranks neighboring products by how many seed products link to them.

cypher

MATCH (p:Product)<-[:REVIEWS]-(r:Review)
WITH p, count(r) AS ReviewCount
ORDER BY ReviewCount DESC
LIMIT 200
WITH collect(p.asin) AS seedAsins
MATCH (seed:Product)-[:ALSO_BUY]->(other:Product)
WHERE seed.asin IN seedAsins AND NOT other.asin IN seedAsins
RETURN other.asin AS ASIN,
    coalesce(other.title, other.asin) AS CoBuyProduct,
    count(DISTINCT seed) AS LinkedSeeds
ORDER BY LinkedSeeds DESC
LIMIT 200;

Equivalent query in GQL standard:

gql

MATCH (seed:SeedProduct)-[:ALSO_BUY]->(other:Product)
WHERE NOT other:SeedProduct
RETURN other.asin AS ASIN,
    coalesce(other.title, other.asin) AS CoBuyProduct,
    count(DISTINCT seed) AS LinkedSeeds
ORDER BY LinkedSeeds DESC
LIMIT 200;

Q6. Second-Hop Co-Buy Influence (Ripple Effect)

Subcategory: T1.3 Exact kth-Hop Neighbors

This query starts from the most-reviewed seed product, follows two ALSO_BUY hops, and ranks second-hop products by how many first-hop neighbors connect back to them.

cypher

// Step 1: Pick the single most reviewed product
MATCH (seed:Product)<-[:REVIEWS]-(r:Review)
WITH seed, count(r) AS ReviewCount
ORDER BY ReviewCount DESC
LIMIT 1

// Step 2: First-hop co-buy neighbors
MATCH (seed)-[:ALSO_BUY]->(firstHop:Product)
WHERE firstHop <> seed

// Step 3: Second-hop neighbors (co-buys of firstHop)
MATCH (firstHop)-[:ALSO_BUY]->(secondHop:Product)
WHERE secondHop <> seed
RETURN secondHop.asin AS ASIN,
    coalesce(secondHop.title, secondHop.asin) AS SecondHopProduct,
    count(DISTINCT firstHop) AS SharedNeighbors
ORDER BY SharedNeighbors DESC
LIMIT 200;

Equivalent query in GQL standard:

gql

MATCH (seed:SeedProduct)<-[:REVIEWS]-(review:Review)
WITH seed, count(review) AS ReviewCount
ORDER BY ReviewCount DESC
LIMIT 1
MATCH (seed)-[:ALSO_BUY]->(firstHop:Product)
WHERE NOT firstHop:SeedProduct
MATCH (firstHop)-[:ALSO_BUY]->(secondHop:Product)
WHERE secondHop <> seed
RETURN secondHop.asin AS ASIN,
    coalesce(secondHop.title, secondHop.asin) AS SecondHopProduct,
    count(DISTINCT firstHop) AS SharedNeighbors
ORDER BY SharedNeighbors DESC
LIMIT 200;

Q7. Most Popular Categories by Reviews

Subcategory: T4.2 Grouped Node Degree Statistics by Label

This query groups products by category and ranks categories by the total number of reviews attached to products in each category.

cypher

MATCH (c:Category)<-[:IN_CATEGORY]-(p:Product)<-[:REVIEWS]-(r:Review)
RETURN c.name AS Category,
    count(r) AS TotalReviews
ORDER BY TotalReviews DESC
LIMIT 200;

Equivalent query in GQL standard:

gql

MATCH (c:Category)<-[:IN_CATEGORY]-(p:Product)<-[:REVIEWS]-(r:Review)
RETURN c.name AS Category,
    count(r) AS TotalReviews
ORDER BY TotalReviews DESC
LIMIT 200;

Q8. Top Brands by Average Rating

Subcategory: Other

This query aggregates reviews across each product brand, keeps brands with at least 100 reviews, and ranks them by average rating.

cypher

MATCH (p:Product)<-[:REVIEWS]-(r:Review)
WHERE p.brand IS NOT NULL
WITH p.brand AS Brand, avg(r.overall) AS AvgRating, count(r) AS ReviewCount
WHERE ReviewCount >= 100
RETURN Brand,
    round(AvgRating, 2) AS AvgRating,
    ReviewCount
ORDER BY AvgRating DESC
LIMIT 200;

Equivalent query in GQL standard:

gql

MATCH (p:Product)<-[:REVIEWS]-(r:Review)
WHERE p.brand IS NOT NULL
WITH p.brand AS Brand, avg(r.overall) AS AvgRating, count(r) AS ReviewCount
WHERE ReviewCount >= 100
RETURN Brand,
    round(AvgRating, 2) AS AvgRating,
    ReviewCount
ORDER BY AvgRating DESC
LIMIT 200;

Q9. Products with the Most Images

Subcategory: T5.1 Degree Centrality

This query counts product image neighbors for each product and returns the products with the largest number of attached images.

cypher

MATCH (p:Product)-[:HAS_PRODUCT_IMAGE]->(i:Image)
WITH p.asin AS ASIN, coalesce(p.title, p.asin) AS Product, count(i) AS ImageCount
RETURN ASIN, Product, ImageCount
ORDER BY ImageCount DESC
LIMIT 200;

Equivalent query in GQL standard:

gql

MATCH (p:Product)-[:HAS_PRODUCT_IMAGE]->(i:Image)
WITH p.asin AS ASIN, coalesce(p.title, p.asin) AS Product, count(i) AS ImageCount
RETURN ASIN, Product, ImageCount
ORDER BY ImageCount DESC
LIMIT 200;

Q10. Images reused by multiple products (Image-Centric View)

Subcategory: T5.1 Degree Centrality

This query switches to an image-centric view and ranks images by the number of distinct products that reuse the same image URL.

cypher

MATCH (p:Product)-[:HAS_IMAGE]->(i:Image)
RETURN i.url AS ImageURL,
    count(DISTINCT p) AS ProductCount
ORDER BY ProductCount DESC
LIMIT 200;

Equivalent query in GQL standard:

gql

MATCH (p:Product)-[:HAS_IMAGE]->(i:Image)
RETURN i.url AS ImageURL,
    count(DISTINCT p) AS ProductCount
ORDER BY ProductCount DESC
LIMIT 200;

Q11. Are Reviews with More Images More Helpful?

Subcategory: Other

This query finds highly helpful reviews, counts any images attached to each review, and returns reviewer, rating, helpful-vote, and image-count fields for comparison.

cypher

MATCH (r:Review)
WHERE r.vote IS NOT NULL
OPTIONAL MATCH (r)-[:HAS_IMAGE]->(ri:ReviewImage)
WITH r.reviewerID AS ReviewerID,
    coalesce(r.reviewerID, "Unknown") AS ReviewerName,
    r.overall AS Rating,
    r.vote AS HelpfulVotes,
    count(ri) AS ImageCount
RETURN ReviewerID, ReviewerName, Rating, HelpfulVotes, ImageCount
ORDER BY HelpfulVotes DESC
LIMIT 200;

Equivalent query in GQL standard:

gql

MATCH (r:Review)
WHERE r.vote IS NOT NULL
OPTIONAL MATCH (r)-[:HAS_IMAGE]->(ri:ReviewImage)
WITH r.reviewerID AS ReviewerID,
    coalesce(r.reviewerID, "Unknown") AS ReviewerName,
    r.overall AS Rating,
    r.vote AS HelpfulVotes,
    count(ri) AS ImageCount
RETURN ReviewerID, ReviewerName, Rating, HelpfulVotes, ImageCount
ORDER BY HelpfulVotes DESC
LIMIT 200;