{ "cells": [ { "cell_type": "code", "execution_count": 2, "id": "2e712ce5-8a1b-4bf3-b11e-81a782abc902", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Requirement already satisfied: findspark in ./anaconda3/lib/python3.12/site-packages (2.0.1)\n" ] } ], "source": [ "!pip install findspark" ] }, { "cell_type": "code", "execution_count": 3, "id": "fb26341e-0942-4f9d-a2ec-fbe8bbbb7a7f", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'/shared/spark-3.4.4-bin-hadoop3'" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import findspark\n", "findspark.init()\n", "findspark.find()" ] }, { "cell_type": "code", "execution_count": 1, "id": "8ec93b76-c4ff-4c1d-a6ec-f27013b87188", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ ":: loading settings :: url = jar:file:/shared/spark-3.4.4-bin-hadoop3/jars/ivy-2.5.1.jar!/org/apache/ivy/core/settings/ivysettings.xml\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "Ivy Default Cache set to: /home/exouser/.ivy2/cache\n", "The jars for the packages stored in: /home/exouser/.ivy2/jars\n", "io.delta#delta-core_2.12 added as a dependency\n", "graphframes#graphframes added as a dependency\n", ":: resolving dependencies :: org.apache.spark#spark-submit-parent-d54c5ee3-113b-4f81-a3ab-361ede136be9;1.0\n", "\tconfs: [default]\n", "\tfound io.delta#delta-core_2.12;2.4.0 in central\n", "\tfound io.delta#delta-storage;2.4.0 in central\n", "\tfound org.antlr#antlr4-runtime;4.9.3 in central\n", "\tfound graphframes#graphframes;0.8.3-spark3.4-s_2.12 in spark-packages\n", "\tfound org.slf4j#slf4j-api;1.7.16 in central\n", ":: resolution report :: resolve 222ms :: artifacts dl 11ms\n", "\t:: modules in use:\n", "\tgraphframes#graphframes;0.8.3-spark3.4-s_2.12 from spark-packages in [default]\n", "\tio.delta#delta-core_2.12;2.4.0 from central in [default]\n", "\tio.delta#delta-storage;2.4.0 from central in [default]\n", "\torg.antlr#antlr4-runtime;4.9.3 from central in [default]\n", "\torg.slf4j#slf4j-api;1.7.16 from central in [default]\n", "\t---------------------------------------------------------------------\n", "\t| | modules || artifacts |\n", "\t| conf | number| search|dwnlded|evicted|| number|dwnlded|\n", "\t---------------------------------------------------------------------\n", "\t| default | 5 | 0 | 0 | 0 || 5 | 0 |\n", "\t---------------------------------------------------------------------\n", ":: retrieving :: org.apache.spark#spark-submit-parent-d54c5ee3-113b-4f81-a3ab-361ede136be9\n", "\tconfs: [default]\n", "\t0 artifacts copied, 5 already retrieved (0kB/6ms)\n", "Setting default log level to \"WARN\".\n", "To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).\n" ] } ], "source": [ "# Import PySpark\n", "from pyspark.sql import SparkSession\n", "\n", "#Create SparkSession\n", "# spark = SparkSession.builder.appName('yelp').config(\"spark.executor.memory\", \"4g\").config(\"spark.driver.memory\", \"4g\").getOrCreate()\n", "\n", "num_executors = \"4\"\n", "spark = SparkSession.builder.appName('yelp').config(\"spark.executor.memory\", \"4g\").config(\"spark.driver.memory\", \"4g\") \\\n", " .config(\"spark.executor.instances\", num_executors).getOrCreate()" ] }, { "cell_type": "markdown", "id": "d41b4f8b-1faa-4b91-9562-08ce161847e7", "metadata": {}, "source": [ "# Load JSONs from HDFS" ] }, { "cell_type": "code", "execution_count": 3, "id": "962ddb39-79a3-4079-bc4b-5fe4a92d9537", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "25/05/02 21:50:46 WARN package: Truncated the string representation of a plan since it was too large. This behavior can be adjusted by setting 'spark.sql.debug.maxToStringFields'.\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "Loaded business_df in 9.29 seconds\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "Loaded user_df in 2.58 seconds\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "Loaded review_df in 3.09 seconds\n", "Loaded tip_df in 0.42 seconds\n" ] } ], "source": [ "import time\n", "\n", "# Load business dataset\n", "start_time = time.time()\n", "business_df = spark.read.json(\"hdfs://master:9000/yelp/yelp_academic_dataset_business.json\")\n", "print(f\"Loaded business_df in {time.time() - start_time:.2f} seconds\")\n", "\n", "# Load user dataset\n", "start_time = time.time()\n", "user_df = spark.read.json(\"hdfs://master:9000/yelp/yelp_academic_dataset_user.json\")\n", "print(f\"Loaded user_df in {time.time() - start_time:.2f} seconds\")\n", "\n", "# Load review dataset\n", "start_time = time.time()\n", "review_df = spark.read.json(\"hdfs://master:9000/yelp/yelp_academic_dataset_review.json\")\n", "print(f\"Loaded review_df in {time.time() - start_time:.2f} seconds\")\n", "\n", "# Load tip dataset\n", "start_time = time.time()\n", "tip_df = spark.read.json(\"hdfs://master:9000/yelp/yelp_academic_dataset_tip.json\")\n", "print(f\"Loaded tip_df in {time.time() - start_time:.2f} seconds\")" ] }, { "cell_type": "code", "execution_count": 4, "id": "5dfb43ec-e5bc-42bd-b273-56b586c62933", "metadata": {}, "outputs": [], "source": [ "# not viable: insufficient memory for large datasets\n", "#business_df.cache()\n", "#user_df.cache()\n", "#review_df.cache()\n", "#tip_df.cache()\n", "\n", "# release memory\n", "#business_df.unpersist(blocking=True)\n", "#user_df.unpersist(blocking=True)\n", "#review_df.unpersist(blocking=True)\n", "#tip_df.unpersist(blocking=True)" ] }, { "cell_type": "markdown", "id": "5a582168-e8bd-4b28-9ac0-9a766c2e9873", "metadata": {}, "source": [ "# Create Vertices" ] }, { "cell_type": "code", "execution_count": 5, "id": "720fdf8b-ccdc-418e-9a87-1937a9519d68", "metadata": {}, "outputs": [], "source": [ "# \"id\" is required by GraphFramess for vertices\n", "# \"type\" is optional, but helpful for filtering\n", "\n", "business_vertices = business_df.selectExpr(\n", " \"business_id as id\",\n", " \"'business' as type\",\n", " \"name\",\n", " \"address\",\n", " \"city\",\n", " \"state\",\n", " \"postal_code\",\n", " \"latitude\",\n", " \"longitude\",\n", " \"is_open\",\n", " \"categories\",\n", " \"stars as rating\",\n", " \"review_count\"\n", ")" ] }, { "cell_type": "code", "execution_count": 6, "id": "de6d2765-c9b0-4dd5-a9a1-bc99766de368", "metadata": {}, "outputs": [], "source": [ "user_vertices = user_df.selectExpr(\n", " \"user_id as id\", # required by GraphFrames\n", " \"'user' as type\", # optional label for node type\n", " \"user_id\",\n", " \"name\",\n", " \"review_count\",\n", " \"yelping_since\",\n", " \"friends\",\n", " \"useful\",\n", " \"funny\",\n", " \"cool\",\n", " \"fans\",\n", " \"elite\",\n", " \"average_stars\",\n", " \"compliment_hot\",\n", " \"compliment_more\",\n", " \"compliment_profile\",\n", " \"compliment_cute\",\n", " \"compliment_list\",\n", " \"compliment_note\",\n", " \"compliment_plain\",\n", " \"compliment_cool\",\n", " \"compliment_funny\",\n", " \"compliment_writer\",\n", " \"compliment_photos\"\n", ")" ] }, { "cell_type": "code", "execution_count": 7, "id": "e713c6bd-448d-475e-84aa-6fa04e82a7b0", "metadata": {}, "outputs": [], "source": [ "review_vertices = review_df.selectExpr(\n", " \"review_id as id\", # Required by GraphFrames\n", " \"'review' as type\", # Optional, helps distinguish node type\n", " \"user_id\",\n", " \"business_id\",\n", " \"stars\",\n", " \"date\",\n", " \"text\",\n", " \"useful\",\n", " \"funny\",\n", " \"cool\"\n", ")" ] }, { "cell_type": "markdown", "id": "e7c1fd2b-bc2e-4d4b-a975-3db0c866575a", "metadata": {}, "source": [ "# Create Edges" ] }, { "cell_type": "code", "execution_count": 8, "id": "7ea5d458-67f1-43d1-bb86-e75727685215", "metadata": {}, "outputs": [], "source": [ "reviewed_edges = review_df.selectExpr(\n", " \"review_id as src\",\n", " \"business_id as dst\",\n", " \"'reviews' as relationship\"\n", ")" ] }, { "cell_type": "code", "execution_count": 9, "id": "42c3aaab-d522-41d7-8ed4-816f49b6c846", "metadata": {}, "outputs": [], "source": [ "wrote_edges = review_df.selectExpr(\n", " \"user_id as src\",\n", " \"review_id as dst\",\n", " \"'wrote' as relationship\"\n", ")" ] }, { "cell_type": "code", "execution_count": 10, "id": "51044819-7b54-47be-9a6e-2c6b3088a6d1", "metadata": {}, "outputs": [], "source": [ "tipped_edges = tip_df.selectExpr( # tip has no id field, so just treat as edge\n", " \"user_id as src\",\n", " \"business_id as dst\",\n", " \"'tipped' as relationship\",\n", " \"text\",\n", " \"date\",\n", " \"compliment_count\"\n", ")" ] }, { "cell_type": "markdown", "id": "cbb10f85-b3ed-4c08-8c87-6e1bbfb72a7c", "metadata": {}, "source": [ "# Create Categories" ] }, { "cell_type": "code", "execution_count": 11, "id": "d486c234-05e5-4cfb-9ac3-cc6c1657d9fe", "metadata": {}, "outputs": [], "source": [ "from pyspark.sql.functions import split, explode, trim, col\n", "\n", "exploded = business_df.filter(col(\"categories\").isNotNull()) \\\n", " .withColumn(\"category\", explode(split(\"categories\", \",\"))) \\\n", " .withColumn(\"category\", trim(col(\"category\")))" ] }, { "cell_type": "code", "execution_count": 12, "id": "28af2257-4892-420c-9dd2-853f77497ec6", "metadata": {}, "outputs": [], "source": [ "from pyspark.sql.functions import lit\n", "\n", "category_vertices = exploded \\\n", " .select(\"category\") \\\n", " .distinct() \\\n", " .withColumnRenamed(\"category\", \"id\") \\\n", " .withColumn(\"type\", lit(\"category\"))" ] }, { "cell_type": "code", "execution_count": 13, "id": "bc15c130-2a6b-469b-a52b-88d3cf0a7afc", "metadata": {}, "outputs": [], "source": [ "category_edges = exploded.selectExpr(\n", " \"business_id as src\",\n", " \"trim(category) as dst\",\n", " \"'in_category' as relationship\"\n", ")" ] }, { "cell_type": "markdown", "id": "2de335ce-a418-4045-9701-398e6b166171", "metadata": {}, "source": [ "# Create Friend Edges" ] }, { "cell_type": "code", "execution_count": 3, "id": "06984f08-34dd-4fcc-aa80-ee3990c2514f", "metadata": {}, "outputs": [ { "ename": "NameError", "evalue": "name 'user_df' is not defined", "output_type": "error", "traceback": [ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[0;31mNameError\u001b[0m Traceback (most recent call last)", "Cell \u001b[0;32mIn[3], line 3\u001b[0m\n\u001b[1;32m 1\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01mpyspark\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01msql\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mfunctions\u001b[39;00m \u001b[38;5;28;01mimport\u001b[39;00m split, explode, col, trim\n\u001b[0;32m----> 3\u001b[0m friend_edges \u001b[38;5;241m=\u001b[39m user_df \\\n\u001b[1;32m 4\u001b[0m \u001b[38;5;241m.\u001b[39mfilter(col(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mfriends\u001b[39m\u001b[38;5;124m\"\u001b[39m)\u001b[38;5;241m.\u001b[39misNotNull()) \\\n\u001b[1;32m 5\u001b[0m \u001b[38;5;241m.\u001b[39mwithColumn(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mfriend_id\u001b[39m\u001b[38;5;124m\"\u001b[39m, explode(split(col(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mfriends\u001b[39m\u001b[38;5;124m\"\u001b[39m), \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m, \u001b[39m\u001b[38;5;124m\"\u001b[39m))) \\\n\u001b[1;32m 6\u001b[0m \u001b[38;5;241m.\u001b[39mwithColumn(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mfriend_id\u001b[39m\u001b[38;5;124m\"\u001b[39m, trim(col(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mfriend_id\u001b[39m\u001b[38;5;124m\"\u001b[39m))) \\\n\u001b[1;32m 7\u001b[0m \u001b[38;5;241m.\u001b[39mselectExpr(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124muser_id as src\u001b[39m\u001b[38;5;124m\"\u001b[39m, \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mfriend_id as dst\u001b[39m\u001b[38;5;124m\"\u001b[39m) \\\n\u001b[1;32m 8\u001b[0m \u001b[38;5;241m.\u001b[39mwithColumn(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mrelationship\u001b[39m\u001b[38;5;124m\"\u001b[39m, lit(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mfriend\u001b[39m\u001b[38;5;124m\"\u001b[39m))\n", "\u001b[0;31mNameError\u001b[0m: name 'user_df' is not defined" ] } ], "source": [ "from pyspark.sql.functions import split, explode, col, trim\n", "\n", "friend_edges = user_df \\\n", " .filter(col(\"friends\").isNotNull()) \\\n", " .withColumn(\"friend_id\", explode(split(col(\"friends\"), \", \"))) \\\n", " .withColumn(\"friend_id\", trim(col(\"friend_id\"))) \\\n", " .selectExpr(\"user_id as src\", \"friend_id as dst\") \\\n", " .withColumn(\"relationship\", lit(\"friend\"))" ] }, { "cell_type": "code", "execution_count": 2, "id": "69f6f4ef-fb75-4d21-bc02-b6dd78f08b7b", "metadata": {}, "outputs": [ { "ename": "NameError", "evalue": "name 'friend_edges' is not defined", "output_type": "error", "traceback": [ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[0;31mNameError\u001b[0m Traceback (most recent call last)", "Cell \u001b[0;32mIn[2], line 1\u001b[0m\n\u001b[0;32m----> 1\u001b[0m friend_edges\u001b[38;5;241m.\u001b[39mcount()\n", "\u001b[0;31mNameError\u001b[0m: name 'friend_edges' is not defined" ] } ], "source": [ "friend_edges.count()" ] }, { "cell_type": "markdown", "id": "28b20071-a70f-4153-a2a5-de101bb41c2b", "metadata": {}, "source": [ "# Create Graph" ] }, { "cell_type": "code", "execution_count": 15, "id": "9433eb8f-e74b-4c60-9dba-19ff18a9587f", "metadata": {}, "outputs": [], "source": [ "# Weakness: all edges should be merged, but they can have different columns.\n", "# Total columns are the union set with null value padding.\n", "\n", "vertices = user_vertices \\\n", " .unionByName(business_vertices, allowMissingColumns=True) \\\n", " .unionByName(review_vertices, allowMissingColumns=True) \\\n", " .unionByName(category_vertices, allowMissingColumns=True)" ] }, { "cell_type": "code", "execution_count": 16, "id": "21144d5f-a4a9-4980-b25e-a94e34a4e9d3", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "root\n", " |-- id: string (nullable = true)\n", " |-- type: string (nullable = false)\n", " |-- user_id: string (nullable = true)\n", " |-- name: string (nullable = true)\n", " |-- review_count: long (nullable = true)\n", " |-- yelping_since: string (nullable = true)\n", " |-- friends: string (nullable = true)\n", " |-- useful: long (nullable = true)\n", " |-- funny: long (nullable = true)\n", " |-- cool: long (nullable = true)\n", " |-- fans: long (nullable = true)\n", " |-- elite: string (nullable = true)\n", " |-- average_stars: double (nullable = true)\n", " |-- compliment_hot: long (nullable = true)\n", " |-- compliment_more: long (nullable = true)\n", " |-- compliment_profile: long (nullable = true)\n", " |-- compliment_cute: long (nullable = true)\n", " |-- compliment_list: long (nullable = true)\n", " |-- compliment_note: long (nullable = true)\n", " |-- compliment_plain: long (nullable = true)\n", " |-- compliment_cool: long (nullable = true)\n", " |-- compliment_funny: long (nullable = true)\n", " |-- compliment_writer: long (nullable = true)\n", " |-- compliment_photos: long (nullable = true)\n", " |-- address: string (nullable = true)\n", " |-- city: string (nullable = true)\n", " |-- state: string (nullable = true)\n", " |-- postal_code: string (nullable = true)\n", " |-- latitude: double (nullable = true)\n", " |-- longitude: double (nullable = true)\n", " |-- is_open: long (nullable = true)\n", " |-- categories: string (nullable = true)\n", " |-- rating: double (nullable = true)\n", " |-- business_id: string (nullable = true)\n", " |-- stars: double (nullable = true)\n", " |-- date: string (nullable = true)\n", " |-- text: string (nullable = true)\n", "\n" ] } ], "source": [ "vertices.printSchema()" ] }, { "cell_type": "code", "execution_count": 17, "id": "e8a5dbb9-563e-4a49-8a6f-5825dc578714", "metadata": {}, "outputs": [], "source": [ "# Weakness: all edges should be merged, but they can have different columns.\n", "# Total columns are the union set with null value padding.\n", "\n", "edges = reviewed_edges \\\n", " .unionByName(wrote_edges, allowMissingColumns=True) \\\n", " .unionByName(tipped_edges, allowMissingColumns=True) \\\n", " .unionByName(category_edges, allowMissingColumns=True) \\\n", " .unionByName(friend_edges, allowMissingColumns=True)" ] }, { "cell_type": "code", "execution_count": 18, "id": "1488fc7e-6715-413a-8dc8-8ead64b0fdbc", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "root\n", " |-- src: string (nullable = true)\n", " |-- dst: string (nullable = true)\n", " |-- relationship: string (nullable = false)\n", " |-- text: string (nullable = true)\n", " |-- date: string (nullable = true)\n", " |-- compliment_count: long (nullable = true)\n", "\n" ] } ], "source": [ "edges.printSchema()" ] }, { "cell_type": "code", "execution_count": 19, "id": "1eda157c-8357-40bf-8538-78900fd6ba08", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 6:=====================================================> (98 + 4) / 102]\r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+--------+-------+\n", "| type| count|\n", "+--------+-------+\n", "| user|1987897|\n", "|business| 150346|\n", "| review|6990280|\n", "|category| 1311|\n", "+--------+-------+\n", "\n", "CPU times: user 13.7 ms, sys: 2.01 ms, total: 15.7 ms\n", "Wall time: 9.55 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] } ], "source": [ "%%time\n", "vertices.groupBy(\"type\").count().show()" ] }, { "cell_type": "code", "execution_count": 20, "id": "db3a453e-9a76-4e41-a31c-d76d1f27b1a3", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 10:=====================================================>(172 + 1) / 173]\r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+------------+---------+\n", "|relationship| count|\n", "+------------+---------+\n", "| reviews| 6990280|\n", "| wrote| 6990280|\n", "| tipped| 908915|\n", "| in_category| 668592|\n", "| friend|106104025|\n", "+------------+---------+\n", "\n", "CPU times: user 8.8 ms, sys: 5.55 ms, total: 14.3 ms\n", "Wall time: 5.56 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] } ], "source": [ "%%time\n", "edges.groupBy(\"relationship\").count().show()" ] }, { "cell_type": "code", "execution_count": 21, "id": "85db510a-a663-46cc-a228-fdd29be9b565", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "CPU times: user 201 ms, sys: 69.9 ms, total: 271 ms\n", "Wall time: 697 ms\n" ] } ], "source": [ "%%time\n", "from graphframes import GraphFrames\n", "\n", "g = GraphFrames(vertices, edges)" ] }, { "cell_type": "markdown", "id": "f0d2cc9f-b8f1-4966-b14f-52af37e0073c", "metadata": {}, "source": [ "# Query: Friend-Related" ] }, { "cell_type": "markdown", "id": "c824388d-7990-4a35-9112-5f13e4e82c6a", "metadata": {}, "source": [ "Find the most frequent names:" ] }, { "cell_type": "code", "execution_count": 23, "id": "aa3ff0e5-4a47-48d2-a7da-1de26f60455c", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 13:==========================================> (24 + 8) / 32]\r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+--------+-----------+\n", "| name|occurrences|\n", "+--------+-----------+\n", "| John| 18719|\n", "| Michael| 16645|\n", "| David| 15967|\n", "| Chris| 14591|\n", "| Mike| 13390|\n", "|Jennifer| 13275|\n", "| Sarah| 11893|\n", "| Jessica| 11688|\n", "| Lisa| 10623|\n", "|Michelle| 9804|\n", "| Brian| 9634|\n", "| Mark| 9510|\n", "| Ashley| 9031|\n", "| Amanda| 9013|\n", "| James| 8647|\n", "| Robert| 8434|\n", "| Amy| 8335|\n", "| Lauren| 8294|\n", "| Jason| 8291|\n", "| Matt| 8211|\n", "+--------+-----------+\n", "only showing top 20 rows\n", "\n", "CPU times: user 7.2 ms, sys: 1.05 ms, total: 8.25 ms\n", "Wall time: 1.69 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] } ], "source": [ "%%time\n", "from pyspark.sql.functions import col, count\n", "\n", "top_names = vertices \\\n", " .filter(col(\"type\") == \"user\") \\\n", " .groupBy(\"name\") \\\n", " .agg(count(\"*\").alias(\"occurrences\")) \\\n", " .orderBy(col(\"occurrences\").desc()) \\\n", " .limit(30)\n", "\n", "top_names.show()" ] }, { "cell_type": "markdown", "id": "ea8dc646-603e-4368-844a-17cdab50a0e2", "metadata": {}, "source": [ "Find the friends of all “John”s:" ] }, { "cell_type": "code", "execution_count": 24, "id": "4f3b03c3-009b-42f0-9005-f53cd204cc1f", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 22:====================================================> (30 + 2) / 32]\r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+--------+--------------------+\n", "| name| user_id|\n", "+--------+--------------------+\n", "| Linda|-3i9bhfvrM3F1wsC9...|\n", "| Paul|-44umGxGvaVaBINlZ...|\n", "| Mariam|-49VO6EnRppS5vehd...|\n", "| John|-55DgUo52I3zW9Rxk...|\n", "| Joshua|-7UIfkN3yoYFcUdjW...|\n", "| Justine|-8Uj3PXAO-RoGz9yF...|\n", "| Fran|-9da1xk7zgnnfO1uT...|\n", "| Erika|-DZRq9xo59Tlh6XPn...|\n", "|Michelle|-FuYIK8244zl00282...|\n", "| John|-G2PNF6OWdnmLhBhJ...|\n", "| Irene|-H0yU9OFytKr3AcIu...|\n", "| Ari|-HYCAMf2ml717YD5Y...|\n", "| Ann|-J9mS1WHKPIZ_F45o...|\n", "| Ted|-Lh_ejzYMVbSJSuCz...|\n", "| Frank|-NwtV6xvM4zSLrSio...|\n", "| Daniel|-R3JuvY4G4x-SYp0-...|\n", "| Wade|-S3ZsSIf2ms_2eQes...|\n", "| Jade|-Ww0zbqsBj70XUNU4...|\n", "| Mohsen|-ZXfHvHrOvs5WUmYJ...|\n", "| Eliot|-ZzCVD9Ge7KCy4ffh...|\n", "+--------+--------------------+\n", "\n", "CPU times: user 24.1 ms, sys: 5.54 ms, total: 29.7 ms\n", "Wall time: 14.8 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] } ], "source": [ "%%time\n", "# Spark version: filtering can happen first, should be more efficient but pattern matching (by joins) has no optimizations, not really faster\n", "\n", "from pyspark.sql.functions import col\n", "\n", "# Step 1: get IDs of users named \"John\"\n", "john_ids = vertices.filter((col(\"type\") == \"user\") & (col(\"name\") == \"John\")) \\\n", " .select(\"id\")\n", "\n", "# Step 2: join with edges to get their FRIEND relationships\n", "john_friends = edges.filter(col(\"relationship\") == \"friend\") \\\n", " .join(john_ids.withColumnRenamed(\"id\", \"src\"), on=\"src\") \\\n", " .select(\"dst\") \\\n", " .distinct()\n", "\n", "# Step 3: get friend attributes\n", "friend_info = john_friends \\\n", " .join(vertices.filter(col(\"type\") == \"user\").selectExpr(\"id as user_id\", \"name\"), john_friends.dst == col(\"user_id\")) \\\n", " .select(\"name\", \"user_id\") \\\n", " .limit(20)\n", "\n", "# Step 4: show the result\n", "friend_info.show()" ] }, { "cell_type": "code", "execution_count": 25, "id": "940c4586-9af8-49b3-85e1-bd9af0559fab", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 33:=================================================> (28 + 4) / 32]\r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+--------+--------------------+\n", "| name| id|\n", "+--------+--------------------+\n", "| Linda|-3i9bhfvrM3F1wsC9...|\n", "| Paul|-44umGxGvaVaBINlZ...|\n", "| Mariam|-49VO6EnRppS5vehd...|\n", "| John|-55DgUo52I3zW9Rxk...|\n", "| Joshua|-7UIfkN3yoYFcUdjW...|\n", "| Justine|-8Uj3PXAO-RoGz9yF...|\n", "| Fran|-9da1xk7zgnnfO1uT...|\n", "| Erika|-DZRq9xo59Tlh6XPn...|\n", "|Michelle|-FuYIK8244zl00282...|\n", "| John|-G2PNF6OWdnmLhBhJ...|\n", "| Irene|-H0yU9OFytKr3AcIu...|\n", "| Ari|-HYCAMf2ml717YD5Y...|\n", "| Ann|-J9mS1WHKPIZ_F45o...|\n", "| Ted|-Lh_ejzYMVbSJSuCz...|\n", "| Frank|-NwtV6xvM4zSLrSio...|\n", "| Daniel|-R3JuvY4G4x-SYp0-...|\n", "| Wade|-S3ZsSIf2ms_2eQes...|\n", "| Jade|-Ww0zbqsBj70XUNU4...|\n", "| Mohsen|-ZXfHvHrOvs5WUmYJ...|\n", "| Eliot|-ZzCVD9Ge7KCy4ffh...|\n", "+--------+--------------------+\n", "\n", "CPU times: user 16.4 ms, sys: 4.43 ms, total: 20.9 ms\n", "Wall time: 10.6 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] } ], "source": [ "%%time\n", "# GraphFramess version: only supports filtering after pattern matching, may not be efficient\n", "\n", "friend_pattern = g.find(\"(u)-[e]->(f)\") \\\n", " .filter(\"e.relationship = 'friend' AND u.type = 'user' AND f.type = 'user' AND u.name = 'John'\") \\\n", " .select(\"f.name\", \"f.id\") \\\n", " .distinct() \\\n", " .withColumnRenamed(\"f.id\", \"user_id\") \\\n", " .limit(20)\n", "\n", "# \"u.type = 'user' AND f.type = 'user'\" are needed since some friend ID list in the raw data links to reviews somehow\n", "\n", "friend_pattern.show()" ] }, { "cell_type": "markdown", "id": "9757a059-2a82-40c5-a047-a62c2452cf35", "metadata": {}, "source": [ "Count the friends of all “John”s:" ] }, { "cell_type": "code", "execution_count": 26, "id": "5bff08aa-0276-45b2-877d-27b184480b10", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 48:==========> (6 + 27) / 33]\r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+-------------+\n", "|total_friends|\n", "+-------------+\n", "| 76078|\n", "+-------------+\n", "\n", "CPU times: user 20.1 ms, sys: 5.28 ms, total: 25.4 ms\n", "Wall time: 14.7 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] } ], "source": [ "%%time\n", "\n", "from pyspark.sql.functions import col, countDistinct\n", "\n", "total_friends = g.find(\"(u)-[e]->(f)\") \\\n", " .filter(\"e.relationship = 'friend' AND u.type = 'user' AND f.type = 'user' AND u.name = 'John'\") \\\n", " .agg(countDistinct(\"f.id\").alias(\"total_friends\"))\n", "\n", "total_friends.show()" ] }, { "cell_type": "markdown", "id": "ba8cc783-78c7-4902-b6f4-1cb6ed28b95e", "metadata": {}, "source": [ "How many friends each user has:\n", "\n", "Note: result number is different from Neo4j since Spark DataFrame seems to remove redundant edges, really need to cleanse data for Neo4j!" ] }, { "cell_type": "raw", "id": "1355a402-1981-4a26-aab5-c82e5eab86c2", "metadata": {}, "source": [ "# Neo4j has redundant edges! Run below in Neo4j to see this\n", "\n", "MATCH (u1:User)-[r:FRIEND]->(u2:User)\n", "WITH u1.user_id AS src, u2.user_id AS dst, count(*) AS rel_count\n", "WHERE rel_count > 1\n", "RETURN src, dst, rel_count\n", "ORDER BY rel_count DESC\n", "LIMIT 20\n", "\n", "# Should've used MERGE instead of CREATE when creating FRIEND edges" ] }, { "cell_type": "code", "execution_count": 27, "id": "00343476-461c-4487-a895-b84fa59bef3f", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 58:===================> (12 + 22) / 34]\r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+---------+--------------------+-----------+\n", "| name| user_id|num_friends|\n", "+---------+--------------------+-----------+\n", "| Walker|qVc8ODYU5SZjKXVBg...| 14995|\n", "| Ruggy|iLjMdZi0Tm7DQxX1C...| 12395|\n", "| Randy|ZIOCmdFaMIF56FR-n...| 11026|\n", "| Scott|mV4lknblF-zOKSF8n...| 10366|\n", "| Steven|Oi1qbcz2m2SnwUezt...| 10072|\n", "| Katie|hizGc5W1tBHPghM5Y...| 9390|\n", "| Danny|IU86PZPgTDCFwJEuA...| 9217|\n", "| Rob|n4Y6wdh6om3QtIFlW...| 8989|\n", "| Abby|djxnI8Ux8ZYQJhiOQ...| 8858|\n", "| Rodney|F_5_UNX-wrAFCXuAk...| 8809|\n", "| Nam|MX_sGTpLIQ0EOcvK7...| 8163|\n", "|Stephanie|wEE-YMx5pmSuagLtN...| 8087|\n", "| Emi|fgwI3rYHOv1ipfVfC...| 8028|\n", "| Frank|MeDuKsZcnI3IU2g7O...| 7945|\n", "| Andy|IDVFG1pNSHIHoVuoL...| 7588|\n", "| Carl|UsXqCXRZwSCSw0AT7...| 7413|\n", "| Katy|Ve0LUwcrzxL7w0RYg...| 7403|\n", "| Jessica|VHdY6oG2JPVNjihWh...| 7342|\n", "| Matt|I2XEhv9zBeAJcwYIr...| 7317|\n", "| Richard|JjXuiru1_ONzDkYVr...| 7228|\n", "+---------+--------------------+-----------+\n", "only showing top 20 rows\n", "\n", "CPU times: user 8.12 ms, sys: 6.15 ms, total: 14.3 ms\n", "Wall time: 4.98 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] } ], "source": [ "%%time\n", "# not really using GraphFramess\n", "\n", "from pyspark.sql.functions import col, count\n", "\n", "# Step 1: filter only FRIEND edges\n", "friend_edges = edges.filter(col(\"relationship\") == \"friend\")\n", "\n", "# Step 2: count number of outgoing friends per user\n", "friend_counts = friend_edges.groupBy(\"src\").agg(count(\"*\").alias(\"num_friends\"))\n", "\n", "# Step 3: join with user vertices to get name and user_id\n", "top_friends = friend_counts \\\n", " .join(vertices.filter(col(\"type\") == \"user\").selectExpr(\"id as src\", \"name\", \"user_id\"), on=\"src\") \\\n", " .select(\"name\", \"user_id\", \"num_friends\") \\\n", " .orderBy(col(\"num_friends\").desc()) \\\n", " .limit(50)\n", "\n", "top_friends.show()" ] }, { "attachments": {}, "cell_type": "markdown", "id": "d32b0bdc-a587-4849-9975-88ae7efc25d8", "metadata": {}, "source": [ "Find mutual friends between Steven and Niki:\n", "\n", "(Very good example of inefficiency of GraphFramess for post-matching filtering)" ] }, { "cell_type": "code", "execution_count": 28, "id": "5475ead3-797c-4cbe-98c3-cd6f21371af3", "metadata": { "scrolled": true }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 78:=====================================================>(199 + 1) / 200]\r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+-----------------------------+----------------------+\n", "|name |user_id |\n", "+-----------------------------+----------------------+\n", "|null |snezm7ll4l6npatgh3IEQQ|\n", "|null |hg037J_0sVd32UjkCw24Dw|\n", "|null |5aVByErCyBwi-cnx_iEJmg|\n", "|null |3I_nSYAt6l4KTI5B_Qun6g|\n", "|null |FVw62YhyWf0B8HPDZH0f5g|\n", "|null |dvu83QWKsnnbGvNVQ3mv7A|\n", "|A |Wxd-EKF2Z4RHZUmwQ6Eeew|\n", "|Aaron |3q2cQC60mNmSHH3LYC_j_g|\n", "|Aaron |7_zsgbji6dRTota2oHa7AA|\n", "|Aimee |UlWDGR0QrBbdFsVY6ugocw|\n", "|Al |MEYWD3nf64p3INCsJ5KOaA|\n", "|Alex |ktm2YMsb65tRnePcYpVlqg|\n", "|Alex |NNL1zLTP2J_SOputgoPYeQ|\n", "|Alexander |iI0ZbwU-doAo2qyTOcWiMg|\n", "|Alice-Anne |jOustFJP5bwpp8x0NMMFJw|\n", "|Alicia |Byog9k35hMURW_O1dIteHw|\n", "|Allison |4hLkKtpQxVB8G3mnDoGjyw|\n", "|Allison |F_5E0rOSH63pLqE6Y2qeCw|\n", "|Amanda |w5_87PDaXdmbx73eCJ5J7Q|\n", "|Amy |qibGLHABNReGeJr2w4_8yQ|\n", "|Amy |68Pz3adTFyy7tgkcpfzBcA|\n", "|Andi |hdzTAN8DGJKRddkZ8279JQ|\n", "|Andie |1JTZrNfcQnO0G4YUgkzxNw|\n", "|Andrea |om5ZiponkpRqUNa3pVPiRg|\n", "|Angeleka |Zsdee1KEpMwjg5cqx-VWrQ|\n", "|Ann |YlWqbKfRinSudpLlgTxKbA|\n", "|Anna |__QLyY_W06q10ZfBQg7Dcg|\n", "|Annette |jt49xjEjQisu6wTTGn6B3A|\n", "|April |uGNvN23FS42Pch9qQwmcog|\n", "|Arispa |ubogjhBVS9C7ld4ST_StGw|\n", "|Ashley |haJT7OC0LQl4i81WWJa1Sg|\n", "|Aundra-Lyn |cnQXH0wqMce-s8P5gnUO3Q|\n", "|Azie |null |\n", "|Bill |MnYfi2DODiZkLIwJFuwB2g|\n", "|Bill |NkVtKQ4wPSuv5OjJTq2nJw|\n", "|Breanna |hj7o_BPgj-HdLAxdKqdx-w|\n", "|Brett |pou3BbKsIozfH50rxmnMew|\n", "|Brian |aP3GeJVUUqhHTPGLj_9lmg|\n", "|Brittany |kJwt2LWVaLOfzoI-GbMlvQ|\n", "|Brittany |AHRrG3T1gJpHvtpZ-K0G_g|\n", "|Brooke |OfYrax_UcxeACetqilPA2Q|\n", "|Bryant |wSByVbwME4MzgkJaFyfvNg|\n", "|Cara |NfU0zDaTMEQ4-X9dbQWd9A|\n", "|Carl |UsXqCXRZwSCSw0AT7y1uBg|\n", "|Cassie |bsMGQruRQGgQZK4KA9Q4Aw|\n", "|Catherine |YwAMC-jvZ1fvEUum6QkEkw|\n", "|Chad |0PkCxX5PeubHdkOLnvSVaA|\n", "|Chef's Market Cafe & Takeaway|null |\n", "|Chrissy |rfDqKDpd1_B-VlkPDfHsqQ|\n", "|Christi |dvg41xsMN31yfqsyfBk-Cw|\n", "|Christopher |2ogbpIu8mxB7oNgQ9-opzQ|\n", "|Cindy |GSMBpnEny1cR12yPIwO0Dg|\n", "|Claudia |LljMD_W6-3-EkWXj0sC-Zg|\n", "|Courtney |9XDFZlGs4-QwQx44YODnWQ|\n", "|Damien |rcU7ysY41qGppbw4pQgjqg|\n", "|Dan |3gRfkaVcEWri-Ju7OQX7uQ|\n", "|Dan |N--OQqLEjW24WHZrDfI_JQ|\n", "|David |C2n878JARw3oFV-_bIxGQQ|\n", "|DeAnna |-ZHlPAvlVdgtiu6DiCq7Yg|\n", "|Derek |tN2ml7LWTM2AcZz6JI7ZuQ|\n", "|Diandra |SQXsow-nuLYYQnTcsIedIA|\n", "|Don |Pf7FI0OukC_CEcCz0ZxoUw|\n", "|Dylan |kCBMksMPqNPw2dfWH_deXQ|\n", "|Elizabeth |v4YpejtzBmFnhxLdeDkqrA|\n", "|Emi |fgwI3rYHOv1ipfVfCSx7pg|\n", "|Emily |hY_0NG7JvtiXmMwR2CFnZA|\n", "|Emily |QiciohgD8N6vCty4vGL3pQ|\n", "|Emma |tVLKzWNBiMZE2kmHrhqksQ|\n", "|Erica |y_Pt8T1xZmovvrZWdlS1EQ|\n", "|Ericka |YZ7_ROsMiNy_RLokJpwKfg|\n", "|Erin |3x69a8OGyReKirigOk8Nww|\n", "|Erin |6sgdkqJ8kxFCHB0QQU3ylQ|\n", "|Erin |JEIiE2fyLeZwnmCxLB1IGw|\n", "|Evelyn |Q9mA60HnY87C1TW5kjAZ6Q|\n", "|Glenn |S7jEEZVw3EJ6Guo3IiKCUA|\n", "|Hailey |e6iCOIhtupBCiw-VwBvJ4A|\n", "|Heather |6JehEvdoCvZPJ_XIxnzIIw|\n", "|Heather |ansmmZrzHn2hAwjEWF8ozQ|\n", "|Helene |hQnjE5ACXjrquVkI_XVe4A|\n", "|Issabelle |ckG7-tdMfxiukdA4jEnE1Q|\n", "|J |WhqG_9cfE6HM9Z_ts81xBA|\n", "|J David |TrU49Ydk2ZMjlh0rtOVj7g|\n", "|Jamie |uPYyZrWOXrw4dKOV2BhV-Q|\n", "|Jando |ysCBsXWPB-LAiewVS3jZfQ|\n", "|Jayme |_NpJZ0q8KVI-d2YLL_VpCA|\n", "|Jazzy |Q3zLMeLgmBpbFzp3yOoNcg|\n", "|Jemmie |KVN1eio-4QU3zlkPmZHRFQ|\n", "|Jen |ijz-n4u6zXZfF_IdV9Aapw|\n", "|Joanna |LYHQ_anXo-4MqYipQWsezA|\n", "|Joanna |s9fb6K62gxO9ZYWn821Sbw|\n", "|Joanna |VxdBd4XPX8Y45PM9IlqIrg|\n", "|Joi |0G-QF457q_0Z_jKqh6xWiA|\n", "|Jon |1JtpcHELRrSDoWS80WtzuQ|\n", "|Jonathan |pnTiEaqM4slogpY97n9Kvg|\n", "|Julie |XiLxIJThWsE0x4d0IeSPsg|\n", "|Julie |qm6WVOID2j4qJ_M-BrrgHg|\n", "|Kara |hcJzh-FFDEfYKGpyM0TpZA|\n", "|Karley |BewDV2ISn0uAMLcsyrhiZg|\n", "|Katherine |g_Y88iSiJ39CTd_x1k6rjg|\n", "|Kathy |AQGMTHae70n3MnONotAuYQ|\n", "|Katie |uRgAL9v1PSVlmoY7VKlc4Q|\n", "|Katlyn |XItANpHKQO3MLgErEmSYsg|\n", "|Katy |DWXE3HLhlMEIcasBed-RXA|\n", "|Kelli |If42v7J7CclT-19ppoPoqg|\n", "|Kelly |V79JtgVzNUA2yrl0u02mrg|\n", "|Kelly |vca-sBvpBqX9G5ZoJONbTA|\n", "|Ken |wXdbkFZsfDR7utJvbWElyA|\n", "|Kendall |rN4RItq4xONM-cjgSfbvDQ|\n", "|Kim |N2xIrWNcgUzrhuQJHizbnQ|\n", "|Kimmi |GpZd0QV2wZrbE4SAhkUAbQ|\n", "|Kinsey |clk385ygJaFUtRIT2M7yTw|\n", "|Kristian |WeVkkF5L39888IPPlRhNpg|\n", "|Kristina |EKp_KaxLz7kUJ0xba5Wvjg|\n", "|Krysha |fbO2EDVPgRk5UQ8NE7QVHw|\n", "|Kyle |_cUtxCvCccwgNJzD5I0cDA|\n", "|Lauren |u3ZPMVVEzneq8x856WksJQ|\n", "|Lauren |lrSlArehU8XqQlRNPQ7dbQ|\n", "|Leslie |ibRrf8Kv4Oo-pUsAH9wtjw|\n", "|Libby |XF6GG8Y_XKESMfU4_vIgHQ|\n", "|Lisa |2oCR898atcv7vDbBUBtd2Q|\n", "|Lisa |il8f7LuFgCv04SoCGHHMMQ|\n", "|Lon |Kw4lH6C5wSrYVxaDP68pfA|\n", "|Madhavi |U_DL5GbEdUfvrcswuXY9ng|\n", "|Mark |eYd1sygl0Thd2JRQTVgX5A|\n", "|Martin |u4DLWfIWkYyLLkFewriykQ|\n", "|Matt |I2XEhv9zBeAJcwYIrnMf1g|\n", "|Megan |lt7bNHl-TXziny4FETu8nA|\n", "|Melissa |oXBhr-GbxjceT-g1UjBqLw|\n", "|Michael |goCRfCCpjP7ndyxuOu4WPQ|\n", "|Michael |OrfM8IXhgPvq1kAjOl-tgA|\n", "|Michael |vHc-UrI9yfL_pnnc6nJtyQ|\n", "|Michelle |ET8n-r7glWYqZhuR6GcdNw|\n", "|Michelle |Yj9TXFf1qNuIyVFT3EHzDA|\n", "|Mike |a-Ug_MFryz3utca-NaMkNQ|\n", "|Mike |Y-HpR79F_ZqRtGMjZnhwVg|\n", "|Missi |2T8cxl3Y_JhCE8o80BSp0A|\n", "|Nicole |OIDpKC2p61qVO_spPqw1qA|\n", "|Nicole |55Hphx_uynxCdtT_zyyDDw|\n", "|Nicole |v38iWCrZLFaMwUiHxcP7GA|\n", "|Nicole |qlC607Cyp0Mj91vDFvkp3Q|\n", "|Ophelia |MN6vTCeRKWU44M7xwhQc7w|\n", "|Patricia |o0emAbW2Z9MnyGxBUzH2ZA|\n", "|Patricia Abigail |ZxjOitafHu6o5crXllN8pQ|\n", "|Patrick |ohYnCSs6eTl7O6o3UySpgg|\n", "|Patrick |YaR04VV6dsiPJGQ8uqBEFw|\n", "|Patty |ZRJ3_9ShQOzdZU2YuuLFow|\n", "|Peter |2iS1vg5TYpV_iEiNC8osTg|\n", "|Peter |ITa3vh5ERI90G_WP4SmGUQ|\n", "|Phyllis |pk1HIAFsiHxpBDuagDpRuA|\n", "|Rachel |4wp4XI9AxKNqJima-xahlg|\n", "|Raechel |zDmZ3--DU1s-uvbBdgJ-hQ|\n", "|Raena |586v_Vg1IJ93qfJZd9l5gg|\n", "|Randy |ZIOCmdFaMIF56FR-nWr_2A|\n", "|Regine |GD0AnaoQO2ymuLXw0P1PRA|\n", "|Richard |A9cXP_K95FRor1qxuUEu2g|\n", "|Robyn |Nm3pTyfLdW9lauuA0lwTmQ|\n", "|Roger |5kL_t1bDR4HLDq_xNo5KZw|\n", "|Ronee |ZH3PVSJ9OFcps-MXsJu09w|\n", "|Ruby |E43QxgV87Ij6KxMCHcijKw|\n", "|Ryan |nxWrhF_hyX0wwjrEkQX8uQ|\n", "|Samantha |3rQu26a-zgGkzWCmUhcsZA|\n", "|Sara |84t6jpyclA55TMfMV0X0kA|\n", "|Sarah |1JcgOEohUMLTZ6GJBuP8QA|\n", "|Sarah |AshBB8EMCttPOObB3ez6SQ|\n", "|Sarah |MDtd2pqpOroIfwSJ4OpdKQ|\n", "|Sarah |AVyU4MC9ucdgCFRaLNpePQ|\n", "|Sarah |p-_xTXwwIEnUUO0ok7j_Sg|\n", "|Sarah |XCofXIF3n7pkjJzD1mtE4A|\n", "|Sarah |yEO1m54G1CK57US6Cfn_YA|\n", "|Scotty |4paWpLov6LjpsNNgE1fhSg|\n", "|Sean |1yuV6loIL1aWgOsJJicQWg|\n", "|Sharee |H2DNqyt2CR5a8Gv3Jisqhg|\n", "|Shelbi |eWv4QOGX1XNjLiwAMluehw|\n", "|Shelby |lr1jHyO8c0dgVBCDMpsf7Q|\n", "|Sigrid |7RlmJFtraL6HqNMlHvnmsA|\n", "|Stacy |QEpNF4Hag6_W9Stc-1Frvg|\n", "|Stacy |L1jvpCxkQu1KS_g7BhF3dg|\n", "|Stefan |1Xz0MVR_fH-aBMDRrNSYjA|\n", "|TOMMY |zAMpV0uFt9XpqRDFNLWPrQ|\n", "|Taffanee |FXnHfE2PNKnc9zvkKkwDdQ|\n", "|Tanisha |f9vxxw-LvNTw7vyKU3tIsg|\n", "|Tank |xalgcjscRLNPuyaAeKNThA|\n", "|Tara |qI3uzegMdq3q3WxAajECGw|\n", "|Tianna |iqVVmm1vcI7Hn1PUE2t_XQ|\n", "|Tiffany |4aC9eBCo3TAsP68H5CoywQ|\n", "|Timea |LXSdI3pBm5K6V1qxxEwkag|\n", "|Tisha |DBYhpb5hrAYgQjQaMhNYyQ|\n", "|Trish |9HGR8sU_zm15sI109H-SGQ|\n", "|Virginia |nr35WjC-YI0dFcsnW33Szw|\n", "|Whit |bf9TJDQefaFzVc6Sb4CqCA|\n", "|Whitney |MJWvm1GZlvEWsnt6n73vng|\n", "|Will |I9PpxV1JrGATW-u3gew3Ew|\n", "|Will |3mZrXEDBw4tZviWX0SDCoQ|\n", "|Yolanda |mAWRxbWTGtvzi773JaFgWQ|\n", "+-----------------------------+----------------------+\n", "\n", "CPU times: user 44.3 ms, sys: 17.6 ms, total: 61.9 ms\n", "Wall time: 27 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] } ], "source": [ "%%time\n", "\n", "mutual_friends = g.find(\"(a)-[e1]->(m); (b)-[e2]->(m)\") \\\n", " .filter(\n", " (col(\"e1.relationship\") == \"friend\") &\n", " (col(\"e2.relationship\") == \"friend\") &\n", " (col(\"a.user_id\") == \"Oi1qbcz2m2SnwUeztGYcnQ\") &\n", " (col(\"b.user_id\") == \"WwulXySQN8t2hwqH_yWurA\")\n", " ) \\\n", " .select(\"m.name\", \"m.user_id\") \\\n", " .distinct() \\\n", " .orderBy(\"m.name\")\n", "\n", "mutual_friends.show(200, truncate=False)\n", "\n", "# we can see that some nodes are not users, the friend list in raw data is not clean" ] }, { "cell_type": "code", "execution_count": 29, "id": "ca4a3605-16fd-420b-be5b-99741d4c8028", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 103:===================================================> (195 + 5) / 200]\r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "CPU times: user 43.3 ms, sys: 12.4 ms, total: 55.7 ms\n", "Wall time: 29.7 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] }, { "data": { "text/plain": [ "194" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%time\n", "mutual_friends.count()" ] }, { "cell_type": "code", "execution_count": 30, "id": "be8da57a-e089-407b-b86c-0a34a1e167be", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 131:====================================================>(197 + 3) / 200]\r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "CPU times: user 36.7 ms, sys: 12.3 ms, total: 49 ms\n", "Wall time: 23.5 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] }, { "data": { "text/plain": [ "186" ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%time\n", "mutual_friends = g.find(\"(a)-[e1]->(m); (b)-[e2]->(m)\") \\\n", " .filter(\n", " (col(\"e1.relationship\") == \"friend\") &\n", " (col(\"e2.relationship\") == \"friend\") &\n", " (col(\"m.type\") == \"user\") & # now, remove noise, and the result matches that from Neo4j\n", " (col(\"a.user_id\") == \"Oi1qbcz2m2SnwUeztGYcnQ\") &\n", " (col(\"b.user_id\") == \"WwulXySQN8t2hwqH_yWurA\")\n", " ) \\\n", " .select(\"m.name\", \"m.user_id\") \\\n", " .distinct() \\\n", " .orderBy(\"m.name\")\n", "\n", "mutual_friends.count()" ] }, { "cell_type": "markdown", "id": "dd29a736-74b0-4b08-a12c-eb03d5780a5d", "metadata": {}, "source": [ "Who has the most mutual friends with a given user:" ] }, { "cell_type": "code", "execution_count": 31, "id": "b71ea735-644a-45b5-8b39-5048ee03f4de", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 164:=============================================> (28 + 6) / 34]\r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+---------+----------------------+------------+\n", "|name |user_id |mutual_count|\n", "+---------+----------------------+------------+\n", "|Phil |YttDgOC9AlM4HcAlDsbB2A|1510 |\n", "|Michelle |ET8n-r7glWYqZhuR6GcdNw|1445 |\n", "|Casey |XaMwHlRaUfsSjBewVAbiQw|1298 |\n", "|Kimquyen |yLW8OrR8Ns4X1oXJmkKYgg|1271 |\n", "|Cassandra|WDpvs6U_TCx4799oxXcnag|1241 |\n", "|Mark |eYd1sygl0Thd2JRQTVgX5A|1209 |\n", "|Marianne |Kf5PVQUdDvQYf-MXoeWqZA|1180 |\n", "|Dan |vRjVhl3ONG2GfWY4NXJZbQ|1171 |\n", "|Christine|5UDpc2CMOgbf3GePS6SGVA|1156 |\n", "|Colton |xirjSwnrFjF87rYT97EG4g|1155 |\n", "|Andi |hdzTAN8DGJKRddkZ8279JQ|1089 |\n", "|Brian |sraTrYU-7q_bQ0TxQrLJvQ|1088 |\n", "|Brent |yBpajT9TpzIpLtINu3Xl0w|1084 |\n", "|Michael |AbMjnKOwg736fcIu8apuyQ|1081 |\n", "|Juliet |kPgNVSxXnhkyWBkqTcW45g|1078 |\n", "|Richard |JjXuiru1_ONzDkYVrHN0aw|1076 |\n", "|Randy |ZIOCmdFaMIF56FR-nWr_2A|1073 |\n", "|Alan |dBxOG8V7Q6efnKgd3CzJ-w|1069 |\n", "|Melissa |oXBhr-GbxjceT-g1UjBqLw|1069 |\n", "|Israel |uIjj7EIVBU4kGNgmKPO02A|1043 |\n", "+---------+----------------------+------------+\n", "only showing top 20 rows\n", "\n", "CPU times: user 44.2 ms, sys: 17.6 ms, total: 61.8 ms\n", "Wall time: 25.9 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] } ], "source": [ "%%time\n", "\n", "from pyspark.sql.functions import col, count\n", "\n", "mutual_counts = g.find(\"(me)-[r1]->(f); (other)-[r2]->(f)\") \\\n", " .filter(\n", " (col(\"r1.relationship\") == \"friend\") &\n", " (col(\"r2.relationship\") == \"friend\") &\n", " (col(\"me.user_id\") == \"Oi1qbcz2m2SnwUeztGYcnQ\") &\n", " (col(\"other.user_id\") != \"Oi1qbcz2m2SnwUeztGYcnQ\")\n", " ) \\\n", " .groupBy(\"other.name\", \"other.user_id\") \\\n", " .agg(count(\"*\").alias(\"mutual_count\")) \\\n", " .orderBy(col(\"mutual_count\").desc()) \\\n", " .limit(50)\n", "\n", "mutual_counts.show(truncate=False)" ] }, { "cell_type": "markdown", "id": "94cb2ae5-9cd3-46a6-a625-ea873295415d", "metadata": {}, "source": [ "Get 3rd-hop neighbors of a given user:\n", "\n", "(Note: this is a time-consuming one for Neo4j)" ] }, { "cell_type": "code", "execution_count": 32, "id": "49e42118-81f9-44a6-87a7-546b0ef053de", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 200:======================================================>(60 + 1) / 61]\r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "Three-hop neighbor count: 953395\n", "CPU times: user 98.5 ms, sys: 46.2 ms, total: 145 ms\n", "Wall time: 2min 3s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] } ], "source": [ "%%time\n", "\n", "from pyspark.sql.functions import col\n", "\n", "three_hop_neighbors = g.find(\"(a)-[r1]->(b); (b)-[r2]->(c); (c)-[r3]->(d)\") \\\n", " .filter(\n", " (col(\"r1.relationship\") == \"friend\") &\n", " (col(\"r2.relationship\") == \"friend\") &\n", " (col(\"r3.relationship\") == \"friend\") &\n", " (col(\"a.user_id\") == \"Oi1qbcz2m2SnwUeztGYcnQ\") &\n", " (col(\"d.user_id\") != \"Oi1qbcz2m2SnwUeztGYcnQ\") # exclude self\n", " ) \\\n", " .select(\"d.id\").distinct()\n", "\n", "three_hop_count = three_hop_neighbors.count()\n", "print(f\"Three-hop neighbor count: {three_hop_count}\")" ] }, { "cell_type": "code", "execution_count": 33, "id": "a403106e-c780-4fb5-b189-5842fd485335", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 232:=====================================================> (50 + 1) / 51]\r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "Three-hop neighbor count: 696120\n", "CPU times: user 94.4 ms, sys: 26.4 ms, total: 121 ms\n", "Wall time: 2min 14s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] } ], "source": [ "%%time\n", "\n", "# avoid noise\n", "\n", "three_hop_neighbors = g.find(\"(a)-[r1]->(b); (b)-[r2]->(c); (c)-[r3]->(d)\") \\\n", " .filter(\n", " (col(\"r1.relationship\") == \"friend\") &\n", " (col(\"r2.relationship\") == \"friend\") &\n", " (col(\"r3.relationship\") == \"friend\") &\n", " (col(\"a.type\") == \"user\") &\n", " (col(\"b.type\") == \"user\") &\n", " (col(\"c.type\") == \"user\") &\n", " (col(\"d.type\") == \"user\") &\n", " (col(\"a.user_id\") == \"Oi1qbcz2m2SnwUeztGYcnQ\") &\n", " (col(\"d.user_id\") != \"Oi1qbcz2m2SnwUeztGYcnQ\") # exclude self\n", " ) \\\n", " .select(\"d.id\").distinct()\n", "\n", "three_hop_count = three_hop_neighbors.count()\n", "print(f\"Three-hop neighbor count: {three_hop_count}\")" ] }, { "attachments": {}, "cell_type": "markdown", "id": "26049505-e447-4ff7-87b4-169cf9e5c2f3", "metadata": {}, "source": [ "# Query: FRIEND + Review, Spatial Queries and Indexing" ] }, { "cell_type": "markdown", "id": "88fa7b3f-0d3a-4ea5-9c69-97be87a6bb74", "metadata": {}, "source": [ "For a given user’s friends, find their reviewed businesses along with locations:" ] }, { "cell_type": "code", "execution_count": 34, "id": "5b24cc45-fb6a-4aff-8558-99967147c95d", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 270:=====================> (11 + 17) / 28]\r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+-----------+-------------------------------------------------------+---------------------+-------------+-----+-------------+---------------+\n", "|friend_name|business_name |address |city |state|latitude |longitude |\n", "+-----------+-------------------------------------------------------+---------------------+-------------+-----+-------------+---------------+\n", "|- J - |Dana Hot Yoga |244-246 Bala Ave |Bala Cynwyd |PA |40.006187 |-75.232523 |\n", "|- J - |Garces Trading Company |1111 Locust St |Philadelphia |PA |39.9477453 |-75.159542 |\n", "|- J - |J & J Motors, Inc. |1111 W Lancaster Ave |Bryn Mawr |PA |40.025195 |-75.324092 |\n", "|A |Amber Indian Restaurant |12510 N Meridian St |Carmel |IN |39.9710819 |-86.1601728 |\n", "|A |Atchafalaya Restaurant |901 Louisiana Ave |New Orleans |LA |29.9212715 |-90.0878562 |\n", "|A |Biscuit Love: Gulch |316 11th Ave S |Nashville |TN |36.1530503 |-86.7832308 |\n", "|A |Ca' Dario |37 E Victoria St |Santa Barbara|CA |34.4253913532|-119.7048107601|\n", "|A |Cafe Audrey at the Fort |9134 Otis Ave |Indianapolis |IN |39.8567658 |-86.0082307 |\n", "|A |Cafe Fleur De Lis |307 Chartres St |New Orleans |LA |29.9544944572|-90.0665915385 |\n", "|A |Cafe Patachou |225 W Washington St |Indianapolis |IN |39.766931766 |-86.1619852334 |\n", "|A |Commander's Palace |1403 Washington Ave |New Orleans |LA |29.9287354418|-90.0841956098 |\n", "|A |Confectioneiress Cupcakes & Sweets |80 Brendon Way |Zionsville |IN |39.9489131 |-86.2753916 |\n", "|A |Country Inn & Suites by Radisson - Indianapolis Airport|5630 Flight School Dr|Indianapolis |IN |39.69914 |-86.259658 |\n", "|A |Crema Coffee Roasters |15 Hermitage Ave |Nashville |TN |36.1568332 |-86.769701 |\n", "|A |District Donuts Sliders Brew |2209 Magazine St |New Orleans |LA |29.9294259 |-90.0770687 |\n", "|A |Five Daughters Bakery 12south |1110 Caruthers Ave |Nashville |TN |36.1258702943|-86.7887662943 |\n", "|A |Four Points by Sheraton French Quarter |541 Bourbon St |New Orleans |LA |29.9577981 |-90.0666116 |\n", "|A |Frothy Monkey |2509 12th Ave S |Nashville |TN |36.1246245774|-86.7897936337 |\n", "|A |Grand Ole Opry |2804 Opryland Dr |Nashville |TN |36.206904 |-86.692294 |\n", "|A |Granite City Food & Brewery |150 W. 96th St. |Carmel |IN |39.927189 |-86.160537 |\n", "+-----------+-------------------------------------------------------+---------------------+-------------+-----+-------------+---------------+\n", "only showing top 20 rows\n", "\n", "CPU times: user 24 ms, sys: 9.79 ms, total: 33.8 ms\n", "Wall time: 13.9 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] } ], "source": [ "%%time\n", "\n", "from pyspark.sql.functions import col\n", "\n", "# Graph pattern: user → friend → review → business\n", "friend_pattern = (\n", " g.find(\"(u)-[e1]->(friend); (friend)-[e2]->(r); (r)-[e3]->(b)\")\n", " .filter(\"\"\"\n", " e1.relationship = 'friend' AND\n", " e2.relationship = 'wrote' AND\n", " e3.relationship = 'reviews' AND\n", " u.user_id = 'Oi1qbcz2m2SnwUeztGYcnQ' AND\n", " u.type = 'user' AND\n", " friend.type = 'user' AND\n", " r.type = 'review' AND\n", " b.type = 'business'\n", " \"\"\")\n", " .select(\n", " col(\"friend.name\").alias(\"friend_name\"),\n", " col(\"b.name\").alias(\"business_name\"),\n", " col(\"b.address\"),\n", " col(\"b.city\"),\n", " col(\"b.state\"),\n", " col(\"b.latitude\"),\n", " col(\"b.longitude\")\n", " )\n", " .distinct()\n", " .orderBy(\"friend_name\", \"business_name\")\n", ")\n", "\n", "friend_pattern.show(truncate=False)" ] }, { "cell_type": "code", "execution_count": 35, "id": "c3bb34cb-c7bb-45ea-b002-130dbc53dff6", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 302:===========================================> (27 + 7) / 34]\r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "CPU times: user 18.5 ms, sys: 10.3 ms, total: 28.8 ms\n", "Wall time: 12.2 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] }, { "data": { "text/plain": [ "336124" ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%time\n", "friend_pattern.count()" ] }, { "cell_type": "markdown", "id": "6376c007-ba02-445a-8a96-24c83e339ef2", "metadata": {}, "source": [ "Find the median of the above locations:" ] }, { "cell_type": "code", "execution_count": 36, "id": "23c0de9f-9c64-422e-82d4-d04ce6225870", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ " \r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "Median Latitude: 39.861251, Median Longitude: -82.3337396979\n", "CPU times: user 39.4 ms, sys: 25.7 ms, total: 65.1 ms\n", "Wall time: 25.5 s\n" ] } ], "source": [ "%%time\n", "\n", "# We can work further on top of friend_pattern !!!\n", "\n", "lat_median = friend_pattern.approxQuantile(\"latitude\", [0.5], 0.01)[0]\n", "lon_median = friend_pattern.approxQuantile(\"longitude\", [0.5], 0.01)[0]\n", "print(f\"Median Latitude: {lat_median}, Median Longitude: {lon_median}\")\n", "\n", "# somehow result is different from that of Neo4j, not sure why......" ] }, { "cell_type": "markdown", "id": "7ee17cbc-b641-4bb2-8be6-97e7cdee438c", "metadata": {}, "source": [ "Find all businesses with in 5 miles from the specified location:" ] }, { "cell_type": "code", "execution_count": 37, "id": "3683931b-faaa-4d9c-9163-598fb5126d61", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+-----------------------------------+-----------------------+--------------+-----+-------------+--------------+------------------+\n", "|name |address |city |state|latitude |longitude |dist_meters |\n", "+-----------------------------------+-----------------------+--------------+-----+-------------+--------------+------------------+\n", "|The Bakers at Red Lion |327 East St Rd |Kennett Square|PA |39.87822 |-75.683351 |819.236369940828 |\n", "|Galer Estate Vineyard & Winery |700 Folly Hill Rd |Kennett Square|PA |39.8793787 |-75.6819928 |950.3309970402421 |\n", "|Inn at Whitewing Farm |370 Valley Rd |West Chester |PA |39.8846552356|-75.6814091148|1245.16039741095 |\n", "|Elizabeth McKinstry Veterinarian |550 School House Rd |Kennett Square|PA |39.8639447 |-75.6898743 |1561.3047730781968|\n", "|1906 Fine Dining |1001 Longwood Rd |Kennett Square|PA |39.8712977 |-75.6749004 |1699.75404292994 |\n", "|The Terrace Cafe |1001 Longwood Rd |Kennett Square|PA |39.8712977 |-75.6749004 |1699.75404292994 |\n", "|Longwood Gardens |1001 Longwood Rd |Kennett Square|PA |39.8714094093|-75.674766606 |1704.8928330929002|\n", "|PureBread Deli |825 East Baltimore Pike|Kennett Square|PA |39.863501 |-75.684556 |1742.1279530560203|\n", "|Bite of Italy |847 E Baltimore Pike |Kennett Square|PA |39.8637397 |-75.6836679 |1750.8558787879865|\n", "|Salon Secrets Spa |829 E Baltimore Pike |Kennett Square|PA |39.863416751 |-75.6844839218|1753.1958755718395|\n", "|Wasabi 3 |837 E Baltimore Pike |Kennett Square|PA |39.8635576 |-75.6839667 |1757.6948620987869|\n", "|Two Stones Pub |843 E Baltimore Pike |Kennett Square|PA |39.8636078 |-75.6837395 |1761.223175750576 |\n", "|Hearth Kitchen |847 East Baltimore Pike|Kennett Square|PA |39.8635846676|-75.6835765727|1769.7562517818096|\n", "|MyEyeDr |841 E Baltimore Pike |Kennett Square|PA |39.8636427104|-75.6831872725|1779.2845709345977|\n", "|Great Clips |144 Onix Dr |Kennett Square|PA |39.862182 |-75.688063 |1784.4141085792435|\n", "|Hand & Stone Massage and Facial Spa|126 Onix Dr |Kennett Square|PA |39.8620332594|-75.6876240747|1809.5176345908096|\n", "|Plaza Azteca - Kennett Square |186 Onix Dr |Kennett Square|PA |39.8617829829|-75.6884733096|1819.945719573414 |\n", "|Kyoto Japanese Cuisine |120 Onix Dr |Kennett Square|PA |39.8619878129|-75.6871318817|1825.3541199147764|\n", "|Dk Nails |156 Onix Dr |Kennett Square|PA |39.861843 |-75.687842 |1825.4905855109978|\n", "|Paradocx Vinyard |879B E Baltimore Pike |Kennett Square|PA |39.862966254 |-75.6832585232|1843.381074883104 |\n", "+-----------------------------------+-----------------------+--------------+-----+-------------+--------------+------------------+\n", "only showing top 20 rows\n", "\n", "CPU times: user 4.13 ms, sys: 4.56 ms, total: 8.7 ms\n", "Wall time: 325 ms\n" ] } ], "source": [ "%%time\n", "\n", "from pyspark.sql.functions import col, pow, sqrt, lit, radians, cos\n", "\n", "# Central point (latitude and longitude in decimal degrees)\n", "center_lat = 39.877788\n", "center_lon = -75.692935\n", "\n", "# Haversine-like Euclidean approximation for short distances (in meters)\n", "earth_radius = 6371000 # meters\n", "\n", "# Compute distance in meters using flat-Earth approximation\n", "business_locations = g.vertices.filter(\"type = 'business' AND latitude IS NOT NULL AND longitude IS NOT NULL\")\n", "\n", "business_with_dist = business_locations.withColumn(\n", " \"dist_meters\",\n", " earth_radius * sqrt(\n", " pow(radians(col(\"latitude\") - lit(center_lat)), 2) +\n", " pow(radians(col(\"longitude\") - lit(center_lon)) * cos(radians(lit(center_lat))), 2)\n", " )\n", ")\n", "\n", "# Filter to within 5 miles = 8046.72 meters\n", "nearby_businesses = business_with_dist.filter(col(\"dist_meters\") <= 8046.72)\n", "\n", "# Display sorted results\n", "nearby_businesses.select(\n", " \"name\", \"address\", \"city\", \"state\", \"latitude\", \"longitude\", \"dist_meters\"\n", ").orderBy(\"dist_meters\").show(truncate=False)" ] }, { "cell_type": "markdown", "id": "02f1bae0-3c4f-4f54-b3df-9a052e219c88", "metadata": {}, "source": [ "K-NN Query, find the k=10 nearest businesses to the specified location:" ] }, { "cell_type": "code", "execution_count": 38, "id": "b3134075-75cf-4f0e-8c09-000044d3276a", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+--------------------------------+-----------------------+--------------+-----+-------------+--------------+------------------+\n", "|name |address |city |state|latitude |longitude |dist_meters |\n", "+--------------------------------+-----------------------+--------------+-----+-------------+--------------+------------------+\n", "|The Bakers at Red Lion |327 East St Rd |Kennett Square|PA |39.87822 |-75.683351 |819.236369940828 |\n", "|Galer Estate Vineyard & Winery |700 Folly Hill Rd |Kennett Square|PA |39.8793787 |-75.6819928 |950.3309970402421 |\n", "|Inn at Whitewing Farm |370 Valley Rd |West Chester |PA |39.8846552356|-75.6814091148|1245.16039741095 |\n", "|Elizabeth McKinstry Veterinarian|550 School House Rd |Kennett Square|PA |39.8639447 |-75.6898743 |1561.3047730781968|\n", "|The Terrace Cafe |1001 Longwood Rd |Kennett Square|PA |39.8712977 |-75.6749004 |1699.75404292994 |\n", "|1906 Fine Dining |1001 Longwood Rd |Kennett Square|PA |39.8712977 |-75.6749004 |1699.75404292994 |\n", "|Longwood Gardens |1001 Longwood Rd |Kennett Square|PA |39.8714094093|-75.674766606 |1704.8928330929002|\n", "|PureBread Deli |825 East Baltimore Pike|Kennett Square|PA |39.863501 |-75.684556 |1742.1279530560203|\n", "|Bite of Italy |847 E Baltimore Pike |Kennett Square|PA |39.8637397 |-75.6836679 |1750.8558787879865|\n", "|Salon Secrets Spa |829 E Baltimore Pike |Kennett Square|PA |39.863416751 |-75.6844839218|1753.1958755718395|\n", "+--------------------------------+-----------------------+--------------+-----+-------------+--------------+------------------+\n", "\n", "CPU times: user 4.02 ms, sys: 1.76 ms, total: 5.78 ms\n", "Wall time: 250 ms\n" ] } ], "source": [ "%%time\n", "\n", "k = 10\n", "nearest_businesses = business_with_dist.orderBy(\"dist_meters\").limit(k)\n", "\n", "# Show selected fields\n", "nearest_businesses.select(\n", " \"name\", \"address\", \"city\", \"state\", \"latitude\", \"longitude\", \"dist_meters\"\n", ").show(truncate=False)" ] }, { "cell_type": "markdown", "id": "2d24c191-6685-42be-8bf9-dca9a913afec", "metadata": {}, "source": [ "Find all users who commented on the above businesses:" ] }, { "cell_type": "code", "execution_count": 39, "id": "2aca782b-47da-4f76-99eb-c3c5ecd159ef", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 465:=================================================> (190 + 10) / 200]\r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+----------------------+-----+\n", "|user_id |name |\n", "+----------------------+-----+\n", "|HICXwEpFvaYrKqOTjbIb8g|A |\n", "|MiZRw5TBJabsVNV8ojj05A|A |\n", "|5zGjKQvljeBhKBdJyTQKOQ|A |\n", "|ucP2NOBCT5j-q1X1NBFXvQ|A |\n", "|vYYvOR-blmdpwqbFay5ryQ|A |\n", "|gvYEDULwuT_tsgZa2PJrJA|A |\n", "|2pjtb1krvr8IRTZn4WpxXQ|A |\n", "|-q0nDJglD9hlFi1Z2ySJeg|A |\n", "|QB_7NnVCDAVgGO_PhMR44g|A |\n", "|A_TznMSVvVb6tbiGSvRQ_Q|A |\n", "|ccRLe1Jbcwc4l5Y1b93sXQ|A |\n", "|T5SWIoJnPsdZp6DtfZPn9w|A |\n", "|E3U2XH7iBqSrqY-QOfOY-Q|A |\n", "|Muid38FkHhxb93w239LGUw|A |\n", "|_7C1VHgGmozBoqPTYHY8hw|A |\n", "|r2shKPQQ5GZS4Go4FCH1NA|A |\n", "|eIYrEyNuCiiTIsyRCLMSpw|A |\n", "|wJHOnR4ZmZVJCSUZt2u2mA|A Lex|\n", "|0aBTYXlP3z5nJ7okTARj8A|A. |\n", "|37GKHIarnMKoeZbCEoaMlg|A. |\n", "+----------------------+-----+\n", "only showing top 20 rows\n", "\n", "CPU times: user 32.4 ms, sys: 17.8 ms, total: 50.2 ms\n", "Wall time: 18.6 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] } ], "source": [ "%%time\n", "\n", "from pyspark.sql.functions import col, radians, sin, cos, sqrt, atan2, lit\n", "\n", "center_lat = 39.877788\n", "center_lon = -75.69293535784999\n", "radius_km = 8.04672\n", "R = 6371.0\n", "\n", "pattern = g.find(\"(u)-[wrote]->(r); (r)-[rev]->(b)\") \\\n", " .filter(\"\"\"\n", " wrote.relationship = 'wrote' AND\n", " rev.relationship = 'reviews' AND\n", " u.type = 'user' AND\n", " r.type = 'review' AND\n", " b.type = 'business' AND\n", " b.latitude IS NOT NULL AND\n", " b.longitude IS NOT NULL\n", " \"\"\")\n", "\n", "filtered = pattern.withColumn(\"lat_rad\", radians(col(\"b.latitude\"))) \\\n", " .withColumn(\"lon_rad\", radians(col(\"b.longitude\"))) \\\n", " .withColumn(\"dlat\", radians(lit(center_lat)) - col(\"lat_rad\")) \\\n", " .withColumn(\"dlon\", radians(lit(center_lon)) - col(\"lon_rad\")) \\\n", " .withColumn(\"a\", sin(col(\"dlat\") / 2) ** 2 + cos(col(\"lat_rad\")) * cos(radians(lit(center_lat))) * sin(col(\"dlon\") / 2) ** 2) \\\n", " .withColumn(\"c\", 2 * atan2(sqrt(col(\"a\")), sqrt(1 - col(\"a\")))) \\\n", " .withColumn(\"distance_km\", R * col(\"c\")) \\\n", " .filter(col(\"distance_km\") <= radius_km)\n", "\n", "result = filtered.select(\"u.user_id\", \"u.name\").distinct().orderBy(\"u.name\")\n", "result.show(truncate=False)" ] }, { "cell_type": "markdown", "id": "62108a77-b12b-41d2-9846-def2c777a888", "metadata": {}, "source": [ "# Query: Review + Tip" ] }, { "cell_type": "markdown", "id": "d8775b10-1e54-45bb-a486-254f0738fc6d", "metadata": {}, "source": [ "Find the total number of reviews + tips for each business, and sort by descending order" ] }, { "cell_type": "code", "execution_count": 40, "id": "0c2c9914-dd58-41cf-8142-5b420fd6214e", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 479:=====================================================> (33 + 1) / 34]\r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+----------------------------------+-----------+--------+-----+\n", "|business_name |num_reviews|num_tips|total|\n", "+----------------------------------+-----------+--------+-----+\n", "|Acme Oyster House |7673 |0 |7673 |\n", "|Oceana Grill |7516 |0 |7516 |\n", "|Hattie B’s Hot Chicken - Nashville|6160 |0 |6160 |\n", "|Reading Terminal Market |5778 |0 |5778 |\n", "|Ruby Slipper - New Orleans |5264 |0 |5264 |\n", "|Mother's Restaurant |5254 |0 |5254 |\n", "|Royal House |5146 |0 |5146 |\n", "|Commander's Palace |4969 |0 |4969 |\n", "|Luke |4661 |0 |4661 |\n", "|Cochon |4480 |0 |4480 |\n", "|Pat's King of Steaks |4293 |0 |4293 |\n", "|Biscuit Love: Gulch |4247 |0 |4247 |\n", "|Pappy's Smokehouse |4093 |0 |4093 |\n", "|Felix's Restaurant & Oyster Bar |4034 |0 |4034 |\n", "|Gumbo Shop |3971 |0 |3971 |\n", "|Cochon Butcher |3917 |0 |3917 |\n", "|Los Agaves |3889 |0 |3889 |\n", "|Grand Sierra Resort and Casino |3634 |0 |3634 |\n", "|Willie Mae's Scotch House |3633 |0 |3633 |\n", "|Geno's Steaks |3428 |0 |3428 |\n", "+----------------------------------+-----------+--------+-----+\n", "only showing top 20 rows\n", "\n", "CPU times: user 17.4 ms, sys: 9.82 ms, total: 27.2 ms\n", "Wall time: 5.83 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] } ], "source": [ "%%time\n", "\n", "from pyspark.sql.functions import col, countDistinct, coalesce, lit\n", "\n", "# Step 1: Pattern match — optional edges must be outer joined manually\n", "reviews = g.find(\"(r)-[e1]->(b)\") \\\n", " .filter(\"e1.relationship = 'reviews' AND b.type = 'business' AND r.type = 'review'\") \\\n", " .selectExpr(\"b.id as business_id\", \"r.id as review_id\").distinct() \\\n", " .groupBy(\"business_id\").agg(countDistinct(\"review_id\").alias(\"num_reviews\"))\n", "\n", "tips = g.find(\"(t)-[e2]->(b)\") \\\n", " .filter(\"e2.relationship = 'tipped' AND b.type = 'business' AND t.type = 'tip'\") \\\n", " .selectExpr(\"b.id as business_id\", \"t.id as tip_id\").distinct() \\\n", " .groupBy(\"business_id\").agg(countDistinct(\"tip_id\").alias(\"num_tips\"))\n", "\n", "# Step 2: Join both counts on business ID\n", "review_tip_counts = reviews.join(tips, on=\"business_id\", how=\"outer\") \\\n", " .withColumn(\"num_reviews\", coalesce(col(\"num_reviews\"), lit(0))) \\\n", " .withColumn(\"num_tips\", coalesce(col(\"num_tips\"), lit(0))) \\\n", " .withColumn(\"total\", col(\"num_reviews\") + col(\"num_tips\"))\n", "\n", "# Step 3: Join back to business to get name\n", "result = g.vertices.filter(\"type = 'business'\") \\\n", " .select(col(\"id\").alias(\"business_id\"), col(\"name\").alias(\"business_name\")) \\\n", " .join(review_tip_counts, on=\"business_id\", how=\"inner\") \\\n", " .select(\"business_name\", \"num_reviews\", \"num_tips\", \"total\") \\\n", " .orderBy(col(\"total\").desc())\n", "\n", "result.show(truncate=False)" ] }, { "cell_type": "markdown", "id": "ee24148e-92d5-436e-8adc-d565a58b6b9b", "metadata": {}, "source": [ "Find the rating of \"Acme Oyster House\":" ] }, { "cell_type": "code", "execution_count": 41, "id": "20ee1dcb-8e63-4f00-af90-d157ed1badc7", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+-----------------+------+\n", "| name|rating|\n", "+-----------------+------+\n", "|Acme Oyster House| 4.0|\n", "|Acme Oyster House| 3.5|\n", "|Acme Oyster House| 4.0|\n", "+-----------------+------+\n", "\n", "CPU times: user 1.99 ms, sys: 1.68 ms, total: 3.67 ms\n", "Wall time: 296 ms\n" ] } ], "source": [ "%%time\n", "\n", "result = g.vertices \\\n", " .filter((col(\"type\") == \"business\") & (col(\"name\") == \"Acme Oyster House\")) \\\n", " .select(\"name\", \"rating\")\n", "\n", "result.show()" ] }, { "cell_type": "markdown", "id": "c3a88ddb-c9f9-4a46-81dc-5bfd3bd5a5be", "metadata": {}, "source": [ "Print all their text reviews and tips:" ] }, { "cell_type": "code", "execution_count": 42, "id": "f932dd61-0775-4c7b-8be3-80ee64b7465c", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 491:=====================================================> (39 + 1) / 40]\r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+\n", "|business_id |text |type |\n", "+----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+\n", "|_ab50qdWOk0DdB6XOrBitw|This restaurant was delicious!!! Best Seafood Gumbo I've had in NOLA!! I'm not an Oyster Eater but everyone recommended the Chargrilled Oysters so I tried half a dozen and they were yummy!!! The butter cheese and garlic - divine!! Yummy! |review|\n", "|Ppy-UN5RptIog4AvNnAM-g|Oh so good!!! We were just not up to fighting traffic and lines at the French Quarter and decided on the location in Metairie. It did not disappoint . The chargrilled oysters were as good or better than I remembered. We Alabama tried the candies fried oysters. Everything was amazing. Worth the long drive from Texas!!! |review|\n", "|_ab50qdWOk0DdB6XOrBitw|It's a NOLA institution. You probably gathered that by the sheer volume of reviews about this place.\\n\\nAh, the famed Acme Oyster House. Naturally, we came here and didn't get any oysters. We strolled by here a couple times during prime dinner hours, and the line goes out the door and around the corner. Ok, so there's no actual corner, but if one existed, the line would have curved around it. All to say, if you're coming during peak times, prepare to wait.\\n\\nAs for us, we came at around 10:30 at night, pre-Bourbon. Thanks to our insane timing, we went straight to the bar with nary a wait. If you're in line though, pick up a drink at nearby Bourbon to pass the time.\\n\\nOur rationale for coming, besides the famousness of it all, was to sample the crawfish. It's still pre-season, but the other French Quarter places we walked past were of comparable price and lackluster quality.\\n\\nThe restaurant itself has sort of a grungy feel that makes you feel like you're in a bar setting. True to form, I ordered an Abita, which came complete in a plastic cup. You stay classy, now. The drinks are poured ready to go.\\n\\nOur bartender/waitress was overbearingly nice and a tad bit funny to boot. She was probably the nicest waitstaff we had on the trip (sans the super service at NOLA; coming soon), so kudos to her for that.\\n\\nAs for the food, the crawfish was just decent, and pricey at $9.99 per pound. Not faulting them there, it is what it is. I went with neither the oysters nor the crawfish, instead opting for the etouffee. S-M-R-T, huh? Solid flavor, excellent portion.\\n\\nBased on the ambiance, location, service, and history, a solid 4 star joint. But I almost feel like I should grade out an incomplete, since the meal came with nary an oyster. Next trip, folks.|review|\n", "|Ppy-UN5RptIog4AvNnAM-g|Oysters where awesome what are they making those aww snap i can't think of the name of them all i know they are excellent the service was awesome the wait was not long to say it was VDay yeah the balls they where fried like donuts holes i can not think of the name to save my name i'm sleepy had to review Acme before i go to bed thank you i will be back !! HUSH PUPPIES ! |review|\n", "|Ppy-UN5RptIog4AvNnAM-g|we grabbed a bite to eat here at 9:30 pm on Fat Tuesday so maybe thats why the food and service werent that great. for starters we got the sampler platter which came with onion rings, meat pies and buffalo wings the best out of those 3 were the wings in my eyes. the onion rings were cold.\\n\\nsince tomm is ash wednesday i wanted some meat so i ordered the boo fries which read roast beef gravy and cheese it was all gravy with no bits of meat at all :( as our entree we ordered the capt mike seafood platter which came with fried oysters, shrimp,fish,crab, hush puppies and potato salad. the fried oysters, stuffed hush puppies and potato salad was yummy.the housemade tartar sauce is good but a bit too much onion for me.\\n\\nour server never really came over to check on our table she would holler to us from behind the bar? for a quick bite our bill for the 3 of us was $75. i would expect those prices downtown not out in the suburbs. |review|\n", "|_ab50qdWOk0DdB6XOrBitw|Soooo good and worthy of the hype. Don't let the line scare you, it moves fast. Service is on point. Food was amazing. We got the shrimp and crab étouffée & shrimp po boy. My husband was relentless about trying me oysters with him while on this trip but I hate oysters .. we ordered the bbq oysters and GAME CHANGER! I LOVED them. They were my favorite part of the meal. I may come back again for them! |review|\n", "|_ab50qdWOk0DdB6XOrBitw|The line was very long, but it moved very quickly. We sat at the bar.\\n\\nThe raw oysters were really good and plump! The charbroiled oysters were very decadent, but delicious. I think they were too heavy for me though, so I'll stick to the raw oysters next time.\\n\\nThe craw puppies were tasty, with a kick to them.\\n\\nThe jambalaya was excellent!\\n\\nOur server, Pam, was very friendly and helpful. |review|\n", "|_ab50qdWOk0DdB6XOrBitw|We sat at the bar with a GREAT server!!! She was sooo nice and attended us extremely well. This made our experience so much better. We ordered half a dozen chargrilled oysters. They were sooooo good we had to order another batch! |review|\n", "|_ab50qdWOk0DdB6XOrBitw|Our first meal in NOLA was the Acme Oyster House. Since it early already early afternoon and dinner reservations were coming up we decided to grab a quick and simple meal. Luckily throughout our vacation to the Big Easy by making reservations we really missed all the lines and long waits. Acme was very busy but we were seated right away. Our order consisted of a couple dozen oysters, a couple ponds of boiled crayfish, and a fried seafood platter. \\n\\nThe NOLA oyster is different from the Pacific oysters, they are bigger and meatier. The Pacific coast oysters are more briny and harder in texture. The NOLA oysters are softer. \\n\\nWe ordered the first pound of Cajun boiled crawfish, they were so good we ordered another pound. \\n\\nThe best item of the meal was the breaded fried fish of the seafood platter. I forgot to ask what kind of fish it was but it was so tender. With the platter there is fried oysters, shrimp, fries and slaw. \\n\\nAcme is a super casual restaurant some paper plates and disposable plastic cups are used but the food was excellent. The line to get in was 30 deep by the time we left. |review|\n", "|_ab50qdWOk0DdB6XOrBitw|Acme oyster House is a New Orleans must do. We queued up in front of the front door and were seated within a couple of minutes. Had a great server who recommended a couple of dishes which were outstanding. Some of the best service that we've had in New Orleans, and that's saying something. The red beans and rice is delicious as are the fried oyster and shrimp Po boys. I'm not sure that there's anything here that wouldn't be just absolutely delicious. |review|\n", "|_ab50qdWOk0DdB6XOrBitw|If you ever had oysters before like I have, this is the place to definitely check out because they know how to present, cook, and clean the oysters. If you NEVER had oysters before or if you had it before but did not like it then cancel you thought about it and try the ones that Acme is serving you. Best oysters I have had in my life!\\n\\nLines can be rough so I advise either going early or waiting in line with two drinks in your hand and get wasted before going. Once your in... YOUR IN!\\n\\nBest thing to get on the menu is the Char-grilled Oysters and get a dozen of them. If you not tired of it then get another because that's how your going to have to roll when you in the Acme Oyster House. The oysters have Parmesean cheese sprinkled on it and i don't know if there is garlic and butter that was in it too but it was effin delicious. The oyster itself was the best it was so velvety and everything they say about an afrodisiac is true because it was just as good as a sexual experience when you were in 8th grade and you just wanted to do it with you teacher.\\n\\nThe fried oysters is the second best thing to get on the menu. Holy crap was it good. Not too greasy or too over cooked. The oyster was also as velvety as ever and melted in my mouth. Was I too full? Hell yeah I was! But who care's. I was in New Orleans.\\n\\nI also ordered the Fried Soft shell crab and that was just amazing as the fried oysters.. You can literally eat all of it and no bone is to hard that will make your insides bleed.\\n\\n\\nAll in all this Acme Oyster House a must go. If your a foodie definitely you got to tap this ass. |review|\n", "|_ab50qdWOk0DdB6XOrBitw|The shrimp tasted fresh and delicious. Parents got catfish with fries, they liked it. Service was slow, and we had to ask for silverware + napkins. The decor / furniture is dated. Overall, it's ok. |review|\n", "|_ab50qdWOk0DdB6XOrBitw|I don't think my trip would have been complete without these darn good oysters. Quite frankly, I haven't had my share of oysters to say whether I like them or not. I ordered a half dozen chargrilled oysters to share with my mom. I really should have ordered a dozen because the oysters were amazingly delicious. Freshly buttered with just the right amount of seasoning... I think it's safe to say that I like oysters.\\n\\nBesides that, we shared the seafood etoufee, which was delicious. I was expecting bigger portions since everything else we've been ordering was so big. We finished with dessert - bread pudding, which was excellent!\\n\\nOof! I think I just ate a month's worth of calories in just four days here in NOLA, but it was well worth it!! |review|\n", "|_ab50qdWOk0DdB6XOrBitw|Ok. Second time I've been here. \\nFirst time I accidentally left my credit card behind. Tried calling for hours the following day to verify that either they had it, and if so, to please rip it up. I literally spent an afternoon dialing and dialing to no avail. What kind of business doesn't answer there phones. But this occurred some time ago. \\n\\nOk, went there yesterday. \\nSimply put. You can do better. You deserve better. \\n\\nThe interior is drab and dark. \\n\\nFood. Between the two of us we ordered the oysters ( charbroiled & raw ) , alligator bites, boom boom shrimp, shrimp etoufee, and the gumbo. \\nHonestly, ONLY , the oysters were worth it. \\nHence the name \"Acme Oyster\" \\nThe boom boom was bar food quality \\nThe alligator bites were dry and tasteless\\nThe etoufee was luke warm at best\\n\\nService, the server was pleasant but seriously pushing upping the bill every chance she got. Also, selected her \"desired tip\" on the portable charging machine. \\nYes I know covid has taken a bite out of your wages. But trying your best to up charge on everything isn't cool. \\n\\nCovid rules. You need to give your name and phone number upon entry. \\nMost restaurants are closed. The streets are bare. \\nRight now \"is not\" the time to plan your fun trip to MSY. \\nBut I get it. Controlling people in New Orleans is like trying to talk sense into a teenager on spring break in Ft. Lauderdale. I can see why they forced the place to close down. \\n\\nAnyways, give this place a pass unless your only going there for the oysters. |review|\n", "|_ab50qdWOk0DdB6XOrBitw|For a place that supposedly specializes in oysters, they should invest some time in teaching the kitchen how to clean them. We ordered a dozen raw. They didn't come on ice, and were completely full of sand. Worst I've ever had. Go to Oceana down the street. WAY better there. |review|\n", "|Ppy-UN5RptIog4AvNnAM-g|Always a good experience here. Best oysters in town. Much better than the competition that starts with a \"D\"! |review|\n", "|_ab50qdWOk0DdB6XOrBitw|Raw oysters ( 4.5 outta 5 ) grilled oysters were drenched in butter and Parmesan and quite delicious if you like that. Reasonable prices for a dozen. Although there seemed to be a long wait because of the line outside they were able to get us in quick. Would def come back next time I'm in NOLA |review|\n", "|_ab50qdWOk0DdB6XOrBitw|I don't even know what to say. The oysters are out of this world. This was the first place I ate in New Orleans and I was skeptical that the food was all hype but i was very very wrong. It was so flavorful it made me double take!! I will be back again tomorrow (: |review|\n", "|_ab50qdWOk0DdB6XOrBitw|fresh horribly prepared oysters. good crab chowder was decent. the oysters and the shrimp po boy was awfully SALTY! heart attack salty..the fried crab were also too salty. everything fried had a super thick breading to conceal the samll size of the sea food. the salt and the flour took away from the flavor of the crab and the oysters. \\n\\nworst of all the order came out cold. cold fried sea food.\\n\\nwe had the fried oysters at the Palace Cafe the same day and they were hot, huge and bursting in your mouth |review|\n", "|_ab50qdWOk0DdB6XOrBitw|Waited in line for 45 min, but it was worth it. Here's a tip, grab a drink while ur waiting in line. It helps pass time.\\n\\nWe had the charbroiled oysters, was seasoned & cooked just right. Didn't know Seafood etouffee could taste so good. Red beans and rice & gumbo was da bomb. Get the New Orleans medley, u will not be disappointed |review|\n", "+----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+\n", "only showing top 20 rows\n", "\n", "CPU times: user 12.9 ms, sys: 4.21 ms, total: 17.1 ms\n", "Wall time: 5.52 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] } ], "source": [ "%%time\n", "\n", "from pyspark.sql.functions import col, lit\n", "\n", "# Step 1: Get all businesses with name = \"Acme Oyster House\"\n", "acme_businesses = g.vertices.filter((col(\"type\") == \"business\") & (col(\"name\") == \"Acme Oyster House\")) \\\n", " .select(\"id\").withColumnRenamed(\"id\", \"business_id\")\n", "\n", "# Step 2: Match reviews linked to those businesses\n", "reviews = g.find(\"(r)-[e]->(b)\") \\\n", " .filter(\"e.relationship = 'reviews' AND r.type = 'review' AND b.type = 'business'\") \\\n", " .select(col(\"b.id\").alias(\"business_id\"), col(\"r.text\").alias(\"text\")) \\\n", " .join(acme_businesses, on=\"business_id\") \\\n", " .withColumn(\"type\", lit(\"review\"))\n", "\n", "# Step 3: Match tips linked to those businesses\n", "tips = g.find(\"(t)-[e]->(b)\") \\\n", " .filter(\"e.relationship = 'tipped' AND t.type = 'tip' AND b.type = 'business'\") \\\n", " .select(col(\"b.id\").alias(\"business_id\"), col(\"t.text\").alias(\"text\")) \\\n", " .join(acme_businesses, on=\"business_id\") \\\n", " .withColumn(\"type\", lit(\"tip\"))\n", "\n", "# Step 4: Combine\n", "result = reviews.unionByName(tips)\n", "result.show(truncate=False)" ] }, { "cell_type": "markdown", "id": "da05d404-2271-4a9b-a117-4c0b2b59ad04", "metadata": {}, "source": [ "Find the businesses with both tips and reviews, and return their counts:" ] }, { "cell_type": "code", "execution_count": 43, "id": "a0dcf231-f9f5-4e83-b46e-c3687e4246af", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+-------------+-----------+--------+-----+\n", "|business_name|num_reviews|num_tips|total|\n", "+-------------+-----------+--------+-----+\n", "+-------------+-----------+--------+-----+\n", "\n", "CPU times: user 7.85 ms, sys: 3.4 ms, total: 11.3 ms\n", "Wall time: 337 ms\n" ] } ], "source": [ "%%time\n", "\n", "from pyspark.sql.functions import col, countDistinct\n", "\n", "# Step 1: Find reviews\n", "reviews = g.find(\"(r)-[e1]->(b)\") \\\n", " .filter(\"e1.relationship = 'reviews' AND r.type = 'review' AND b.type = 'business'\") \\\n", " .select(col(\"b.id\").alias(\"business_id\"), col(\"r.id\").alias(\"review_id\")).distinct() \\\n", " .groupBy(\"business_id\").agg(countDistinct(\"review_id\").alias(\"num_reviews\"))\n", "\n", "# Step 2: Find tips\n", "tips = g.find(\"(t)-[e2]->(b)\") \\\n", " .filter(\"e2.relationship = 'tipped' AND t.type = 'tip' AND b.type = 'business'\") \\\n", " .select(col(\"b.id\").alias(\"business_id\"), col(\"t.id\").alias(\"tip_id\")).distinct() \\\n", " .groupBy(\"business_id\").agg(countDistinct(\"tip_id\").alias(\"num_tips\"))\n", "\n", "# Step 3: Inner join — only businesses that have both reviews and tips\n", "joined = reviews.join(tips, on=\"business_id\", how=\"inner\") \\\n", " .withColumn(\"total\", col(\"num_reviews\") + col(\"num_tips\"))\n", "\n", "# Step 4: Add business name\n", "result = g.vertices.filter(\"type = 'business'\") \\\n", " .select(col(\"id\").alias(\"business_id\"), col(\"name\").alias(\"business_name\")) \\\n", " .join(joined, on=\"business_id\") \\\n", " .select(\"business_name\", \"num_reviews\", \"num_tips\", \"total\") \\\n", " .orderBy(col(\"total\").desc())\n", "\n", "result.show(truncate=False)" ] }, { "cell_type": "code", "execution_count": 22, "id": "66dce8fb-6266-4c19-8b36-93a02c3f1d51", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 19:=================================================> (30 + 4) / 34]\r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+----------------------------------+-----------+--------+-----+\n", "|business_name |num_reviews|num_tips|total|\n", "+----------------------------------+-----------+--------+-----+\n", "|Acme Oyster House |7673 |0 |7673 |\n", "|Oceana Grill |7516 |0 |7516 |\n", "|Hattie B’s Hot Chicken - Nashville|6160 |0 |6160 |\n", "|Reading Terminal Market |5778 |0 |5778 |\n", "|Ruby Slipper - New Orleans |5264 |0 |5264 |\n", "|Mother's Restaurant |5254 |0 |5254 |\n", "|Royal House |5146 |0 |5146 |\n", "|Commander's Palace |4969 |0 |4969 |\n", "|Luke |4661 |0 |4661 |\n", "|Cochon |4480 |0 |4480 |\n", "|Pat's King of Steaks |4293 |0 |4293 |\n", "|Biscuit Love: Gulch |4247 |0 |4247 |\n", "|Pappy's Smokehouse |4093 |0 |4093 |\n", "|Felix's Restaurant & Oyster Bar |4034 |0 |4034 |\n", "|Gumbo Shop |3971 |0 |3971 |\n", "|Cochon Butcher |3917 |0 |3917 |\n", "|Los Agaves |3889 |0 |3889 |\n", "|Grand Sierra Resort and Casino |3634 |0 |3634 |\n", "|Willie Mae's Scotch House |3633 |0 |3633 |\n", "|Geno's Steaks |3428 |0 |3428 |\n", "+----------------------------------+-----------+--------+-----+\n", "only showing top 20 rows\n", "\n", "CPU times: user 21.8 ms, sys: 7.67 ms, total: 29.5 ms\n", "Wall time: 8.79 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] } ], "source": [ "%%time\n", "\n", "from pyspark.sql.functions import col, countDistinct, coalesce, lit\n", "\n", "# Step 1: Find reviews (same as before)\n", "reviews = g.find(\"(r)-[e1]->(b)\") \\\n", " .filter(\"e1.relationship = 'reviews' AND r.type = 'review' AND b.type = 'business'\") \\\n", " .select(col(\"b.id\").alias(\"business_id\"), col(\"r.id\").alias(\"review_id\")).distinct() \\\n", " .groupBy(\"business_id\").agg(countDistinct(\"review_id\").alias(\"num_reviews\"))\n", "\n", "# Step 2: Find tips (same as before)\n", "tips = g.find(\"(t)-[e2]->(b)\") \\\n", " .filter(\"e2.relationship = 'tipped' AND t.type = 'tip' AND b.type = 'business'\") \\\n", " .select(col(\"b.id\").alias(\"business_id\"), col(\"t.id\").alias(\"tip_id\")).distinct() \\\n", " .groupBy(\"business_id\").agg(countDistinct(\"tip_id\").alias(\"num_tips\"))\n", "\n", "# Step 3: Start with ALL businesses\n", "all_businesses = g.vertices.filter(\"type = 'business'\") \\\n", " .select(col(\"id\").alias(\"business_id\"), col(\"name\").alias(\"business_name\"))\n", "\n", "# Step 4: Left join reviews and tips to businesses\n", "result = all_businesses \\\n", " .join(reviews, on=\"business_id\", how=\"left\") \\\n", " .join(tips, on=\"business_id\", how=\"left\") \\\n", " .select(\n", " \"business_name\",\n", " coalesce(col(\"num_reviews\"), lit(0)).alias(\"num_reviews\"),\n", " coalesce(col(\"num_tips\"), lit(0)).alias(\"num_tips\")\n", " ) \\\n", " .withColumn(\"total\", col(\"num_reviews\") + col(\"num_tips\")) \\\n", " .orderBy(col(\"total\").desc())\n", "\n", "result.show(truncate=False)" ] }, { "cell_type": "code", "execution_count": 44, "id": "b2836836-d8f2-428c-9949-545189279bca", "metadata": { "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\n", "|id |text |\n", "+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\n", "|NUqNm6gqj-iOWo30r_iFrA|Our server was awesome. Very tentative and extremely nice! That is the only thing saving this place plus the maple wings were delicious. Now for the bad part. We paid 35 dollars for the \"piano guys\" to sing a song to the birthday girl. We had 3 options of songs that we wanted to hear. They called our birthday girl on stage and began to sing a rendition of Aladins whole new world. (Which was not one of the songs we had picked) The song quickly turned to a absolutely disgusting remake. The piano guy sang so our birthday girl about opening her thighs,tying her up, filling holes, splurging on her face, and cumming in her hair. It was even worse than that. Her husband and family including parents were in the crowd but luckily couldn't hear to well because the sound system is terrible. Our birthday girl even stopped the piano man and told him to stop and he continued and made it even dirtier. It was disgusting, offensive, and embarrassing. Trust me we are not prudes by any means but this was way too far. It completely ruined the experience and we all immediately left. We spent our time and money there only to have the birthday ruined. Also for 35 dollars we paid we only heard 1 of the songs which they only sang part of. |\n", "|hgah1g29AmdWrp3QPhEbPA|I'm not sure what changed over the last year at this restaurant, but now it's just terrible food. Before, when I first started coming, it was decent: nothing fancy, but filling and palatable. Now, though, the food is disgusting. 90% of their buffet offerings are WAY too salty. The pulled turkey was tough, the fish tasted like licking a bottle of meat tenderizer, and the vegetables cooked to a putty-like state. The only thing vaguely edible was the corn pudding and mashed potatoes. The desserts are ok, almost store-bought quality. \\n\\nThe service at least was good. Our waitress refilled our drinks promptly and removed plates often. The only good I saw all night. |\n", "|tGzobhc7MyMSej6gVdv3Pw|I had to write an order after coming here a couple of times because this place is just terrible. I tried their hoagies and they are so greasy i got nauseous after eating them... Then yesterday i accidently ordered from here instead of angelinos and i got the buffalo chicken sandwhich and it was just covered in blu cheese and greasy sauce it was so Disgusting. All their food is terribly greasy and over seasoned its too much.. Back off on it a little and use freasher ingredients it will be much better!\\n\\nIll also add that their desserts suck, taste bland and a waste of money. Their sodas are usually expired and flat. I hate everything about this place! |\n", "|jF3ZlgxpM88UjKymEfSJmQ|This was my very 1st time in New Orleans. It's been an amazing experience and so far, every single place I've eaten at has been AMAZING. So... I took a chance with The Praline Connection and walked in. Why? The ambiance and the restaurant, overall, looked nice. \\nHowever... I would NEVER return to this place again, nor would I Ever RECOMMEND it to Anyone. \\n\\nAPPETIZERS:\\nI ordered the BBQ Oysters and..wow.. fucking wow...1st of all the oysters not in their shells. Instead they placed in a soup cup filled with \"BBQ sauce\" that tasted like cheap Ketchup and Chef Boyarde! Disgusting! The oysters themselves were gooey.\\n\\nThe garlic bread was God awful! It tasted like stale frozen garlic bread. To make matters worse, when I received these appetizers, they were cold.. It tasted and looked like they had been microwaved and left out. \\n\\nI also ordered the Chicken wings. They were under cooked, cold and the sauce was nasty. \\n\\nThe other members of our party ordered entrees. They all said, \"This Sucks!\"\\n\\nPROS: The service was great. The servers were all very polite and attentive. \\nRestaurant was clean, nice tables, nice ambiance. |\n", "|cp12LEo_c7SNbpscnbYjfA|Four stars for price. \\nI drive truck, so I've eaten bbq all over America. This place is better than most, but it's certainly not 4 star worthy. I'd say the food is more like 2.5 star. \\nThe cheeseburger was quite large and the meat was tasty. (Be advised: if you don't stop them they'll automatically put that disgusting invention called mayonnaise on it.) I got it plain so I can't comment on their lettuce/tomato game. \\nThe pork bbq sammich was soggy on the bottom. Moist is good, but this was just plain wet. The meat itself was bland but not bad. \\nThe bbq sauce is pretty good but not great. \\nThe Mac & Cheese tasted ok but the noodles were way overcooked. It'd be much better if it were baked and had a little crustiness to it. \\n\\nIf I lived local I'm sure I'd come here occasionally. |\n", "|gYKwhONeiszsrzi7dhT2pg|Well, well.... Where do I start with this place? First time experience, went in with my partner.\\n\\nWalking in at about 7:30 on a Friday night (st paddys day weekend even) and the place had about 4 customers. That should have been my first clue as to what our experience was going to be like.\\n\\nWe seated ourselves and the waitress, an older gal maybe mid to late 40's who had greasy stringy unwashed hair and rotting teeth came up and took our drink order. My girlfriend got a Dr. Pepper and I got a water. She brought out Coke instead of DP. No biggie. We let it go, assuming it was an honest mistake. After about 10 minutes she returns and we order a 12 piece \"jumbo\" wing basket ($11.99), a 6 piece($7.99), and a beer batter sampler($11.99). 20 minutes pass and finally here comes our appetizer. The waitress throws the basket of appetizers on the table and says \"here's your food.\" Very flat with a big attitude and walks off. The beer battered mushrooms were doughy and undercooked. We had a single half full ranch cup to share between us for the big platter and she avoided our table when she saw we needed more.\\n***let me note that this waitress is giving the customers at the 3 tables surrounding us WAY better customer service. Even brought their food out in a timely manner with no attitude whatsoever.\\nI decide to let the mushrooms slide for the moment. I used to work in a restaurant and I understand that mistakes happen, however this far into the experience I'm being mighty generous since it's a first time experience.\\nWe finish our appetizer and we wait and wait and wait and finally she comes by and takes the platter returning 10 minutes later with our wings. She makes no inquiry about the broken open, clearly undercooked, mushrooms still sitting on the platter.\\nI got the 12 piece with a single half full ranch. Again. Food on the table, doesn't ask if we need anything else which is something she is doing for other customers. at this point we have been here for about 40 minutes.\\n\\nI got half regular and half thai flavor. The wings were NOT jumbo. They were puny. The flavor was good for the thai, and the waitress even stopped by to check up on us, though I wouldn't consider walking by quickly and saying in your flat attitude tone \"youguysdoingalright?okaycool.\" Really all that helpful since she came and went so fast it took me a second to understand her. I had to reiterate for my girlfriend. When I started on the regular I bit into my drumstick and had to fish a feather out of my mouth. DISGUSTING. It was that point that the entire experience was completely and thoroughly unacceptable. The wings were soggy because they batter them or something on top of all that. At first I had no plans other than to pay for the meal and leave the waitress 2 pennies and be on my merry way. But when the waitress came and asked if we needed a box I said no with 6 wings still on my plate, OBVIOUS SOMETHING WAS WRONG, again. She was going to take my stuff and make no inquiry to right the situation. I asked for the manager. Took a minute but finally the manager came over (mind you it is still pretty dead, what are these people doing I wonder?) I explained the food issues but blew off bringing up our treatment by the waitress. Normally I give the benefit of the doubt on this one but it was blatantly obvious that we were being treated differently with a different attitude than other patrons because we are a same-sex couple. Like our table had a contagious disease that prevented us from getting food in a timely manner, and getting drink and condiment refills too, regardless of the *empty* cups on the edge of the table. It wasn't like we were off in the distance. She had to walk by our table every time to communicate with the surrounding tables.\\n\\nAnyways. The manager experience wasn't the greatest, but I wasn't going to pay for that meal after the feather thing, and she comped everything. BUT only after slightly arguing with us about the undercooked mushrooms, which by the way come frozen which makes the fact that they weren't cooked all the way acceptable I guess? Don't know. Don't care. I WILL NOT BE GOING BACK!!!\\n\\n2 stars because they did comp the meal, the waitress got lucky. If I would have had to pay for anything she wouldn't have gotten a tip due to the attitude but we left her $4 anyway since we didn't have to pay for the meal. THAT IS THE ONLY REASON.\\n1 star though for the overall experience. Same-sex couples, steer clear. You aren't welcome there and it's obvious.|\n", "|H1mOWfIkUjKgmI8LSRMg-Q|I'm all for a dive bar but this place takes it to another level. The walls are old wood panel, the whole place smells disgusting, the drinks are tiny...I don't feel any need to ever go there again. |\n", "|INGNbsyo-MouZZzcxnCSGQ|This place gets two stars from me only because the gravy fries were good. Their sandwiches are awful. Thick slabs of badly sliced, tough, dry meat....gross. I made the mistake of ordering their pork sandwich once and it was inedible. I tried to bite into the sandwich and couldn't even bite through because of how tough and rubbery the pork was. I don't understand why they slice the meat the way they do but it makes for a disgusting sandwich. The people raving on how good this place is are just locals who have never had anything better. You're better off going to Wawa for a sandwich. Seriously. It will at least be edible. |\n", "|45jt8UwpS64ULd_TLVg5yw|Place is okay. They were the only restaurant that was able to accommodate a large party on such short notice. I ordered the black spaghetti that other patrons raved about. It was pretty disgusting. I'm not sure why he had some a weird texture and almost tasted like plastic. I've tried many different types of spaghetti and this by far was the worst plate I've tried. My co-workers said their food was okay though. Maybe it was just what I ordered that sucked. If I'm ever in Nashville again, I'd give them another go. The drinks were on point as expected. |\n", "|ilbEXGq_s0Cn8cMMUpxuMA|Updating my review from last nights recent visit. Server Drew was very good, can't complain one bit about the service. Attentive and got everything we needed. Food on other hand was not good at all. My boneless wings were not even lukewarm-cold. Either sat in window for a long time or just not fresh/ not sure. Food took a long time to come out. My sons kids meal mini corn dogs were disgusting. He didn't eat them and I wouldn't either. All you could taste was bad fryer grease as they were also cooked to death. Won't be returning too soon |\n", "|S-IwFgSGRkGEG8U89K7f4A|I've been eating here when I'm in town for about 5 years and this place used to be great. The food has severely gone downhill in not only quality of ingredients but also in ability to prepare a dish. I ate here for lunch today, saying that so hopefully a member of the staff reads this and remembers me, I ordered the dry hot pot and my gf ordered fried dumplings and the cold noodles with chili oil and we shared each other's food. The mushrooms in my dish were impossible to cut even with a knife and the rest of the vegetables had a freeze dried feel to them being the consistency of bark. The pork was a bit rubbery and only had flavor from the oil draped over everything that seemed under seasoned as well and definitely tasted old. This was labeled in the menu as a 10/10 spicy rating, it was at most a 2. I didn't bother saying anything because of how rude the waiter/host was, but I'll get to that. The rice was cold and clumped together in chunks that led me to understand that it was previously microwaved. The dumplings looked ok, but the dough was undercooked and the sauce was just soy sauce that had no care whatsoever put into it. Her noodles were very bland and whoever prepared them obviously doesn't care about their food. The noodles had the same flavor deprived oil from my dish in it, just disappointing really. This type of food is supposed to be robust and I've eaten at a ton of places across the country and internationally that do care about what the put out. This place doesn't care anymore. The man that seated us was incredibly rude the entire time. He huffed and puffed and stomped his feet when I asked for the real Chinese menu instead of the Americanized Chinese menu. And, they used to throw shade if you asked for the Americanized menu, now it's the opposite. After we were done we both had a decent amount of food left and I decided to take it to feed to the raccoons that come around my house. They look cool and I like to feed them. Anyway, the take out container was dirty from previous food in it! I was blown away, especially when I pointed it out and the rude person pretending to be a man grabbed it out of my hand and stormed, literally stormed off before I was even half way through my sentence. I was calm the whole time, not sure what this guys deal is. Then came back with the same container that he washed! He washed the dirty container that should never have been used in the first place and thought that was acceptable. I will never go here again and I hope they go out of business so no one has to be punished by this Handy Nasty food. Disgusting food and disgusting people, except for the one guy who worked there who said goodbye to us. He seemed mortified because he knew what the cuck host just did to everyone in the dining area throughout the day and I assume throughout his life. I hate this place, and I can only hate it because I used to love it. |\n", "|DYkIJIrY_dCMTY4tEqdDUw|Ordered take out from this place tonight. It was by far the worst meal I have ever had. I ordered the chicken parmesan. This is a pretty hard dish to screw up but they did a fine job of it. It was an unidentifiable piece of gray rubbery meat (I think). The breading was soggy and I needed a steak knife to cut the chicken. I literally could not chew it, it was so tough. The garlic bread it came with, was also disgusting! It was greasy and hard. Yuck! My husband had a sauted chicken campagniola with veggies which was marginally better. The chicken was so overdone, it made it hard to stomach. The food was so disgustingly greasy that the brown bag they gave us broke. Uggh! If you get food from this place, be warned. Maybe stick with the pizza. |\n", "|IzylDtyp3mDfISIl3j86DA|Over priced and over hyped. We were visiting Nashville and an internet search for restaurants showed this was one of the places to go to in Nashville. We went for brunch. I had a good feeling when we first when in and Zeppelins Over the Hills and Far Away was playing but that was probably the most memorable thing. We didn't start off with the whippped feta, which seems to be the big thing there, maybe that's why we didn't have the same experience as other people. I ordered the French toast cinnabon thing which was fine. My wife however, had the Shakshuka and did not like it at all. Too spicy in the morning, and as someone mentioned in another review, it would have been nice if that was mentioned (although it does say spicy on the menu, I think she was expecting a different spice). Also, the poached egg to me was disgusting. My wife used a spoon but could have drank it with a straw. I think there were two, I give her credit for forcing down one, I couldn't have done it. We also ordered a bloody mary and a mimosa. There wasn't much mimosa as far as volume goes and my bloody mary was a little thick, I think because of olive juice. We each had side orders of bacon, two slices each at $5, and not like it was super thick. Overall, the bill was about $80 and my wife was still hungry. The waitress did notice that my wife hardly ate anything and mentioned getting the whipped feta saying it would help her stomach which was now hurting because of the spice. She said no, but it would have been nice if the waitress had brought over a sample. Anyway, like I said, about $80; $10 each for the mimosa and bloody mary, $14 for the three cinnabon french toast, $13 for the Shakshuka, $10 for four slices of bacon and whatever it was for the coffee (which was good). I'm counting the tip so the coffee must have been up there too as well as the extra pita my wife asked for. Listen, The Olive Tree Cafe in the Village in NYC has been there forever. Check out their menu online and check out their prices. They don't do breakfast, but if you're ever in NYC and get a chance to go there, do it. It's fun, they have Charlie Chaplin movies playing, tables made of chalkboard to write on and great Middle Eastern food and great prices. If you want pseudo hoity-toity, somewhat decent Middle Eastern food at an unrealistic price, then go here. |\n", "|AzQoLK8HJC8PAQcMXQgemQ|Negative 5 stars. Worst service and slow, 15 minutes for 1 panini. They use microwaves people so its not even fresh, just crap. I'm sure they don't CLEAN them either. Disgusting, Get Real. I will go buy a frozen breakfast sandwhich & cook it my own CLEAN microwave which isn't cooked with everybody else's crap. NEVER AGAIN will go here. Btw i waited so long i got my money back. Keep your crappy sandwhich and enjoy |\n", "|77NhVtdOyHnfjKXVG7YqmA|The food is disgusting, no beef in fried rice, egg foo young gravy clumpy and nasty. Even the crabless Rangoon are way too sweet, they are not as good as some of the other local spots( China Dish) What cemented the one star was the rude employees that you have to deal with when asking for basic requests i.e. bean sprouts. When they don't have them and you sub extra white rice and they just open box and through them in on top. What a joke! |\n", "|C9xOCrlVEaWcgx_mGoXAzQ|Purchased a 2015 Jeep Wrangler Unlimited over the summer. Took it into South County Dodge to get the leather replaced on the drivers seat due to defect & they somehow managed to break the entire frame requiring me to get a rental. Enterprise gave me a Chrystler 200 which would have been fine except the fact that it is absolutely filthy and has a giant rock chip right in the drivers line of site. Yesterday I found out the part for my Jeep won't be in until 10/30 at which time I told them I would need a different rental due to the disgusting state of the current one. I received a call at 7:45am today letting me know they had a Fiord Fusion for me at which time I told them I would be by on my lunch break between 11-11:15. I got to the dealership and there was no car. I waited for 30 additional minutes before leaving. Not only is their fleet poorly maintained they have zero respect for anyone's time. It's now 4:30, I still have the disgusting 200 and no one has even bothered to follow up or apologize for once again dropping the ball. Skip the headache and go to Avis...they have never failed me. |\n", "|qjKgiQRSIBv6M9Lxz4Skbg|Horrible disgusting not sure how they are even open. Food tasted old and was served cold and had zero taste. |\n", "|VQgUlzIlXDiWUwZkI-psQQ|The nachos were seriously disgusting... Probably the grossest food I've had in a long time. The waitress was rude, unresponsive, and had a bad attitude. I will not return. |\n", "|u1zDs4Zkn4uq4MPXPH7Olw|If I could give zero stars I would. Service was slow and it wasn't busy. Order came out missing food. Place was disgustingly filthy. And the food....bland and super greasy. |\n", "|7BZI0jpioY6hroN7Tvsulg|Food was awful. We sat down and waited for everything. Our waiter left in the middle of the service, it took 20 minutes to get an orange juice.\\n\\nBathrooms were disgusting and the restaurant was not even full. A bad experience all around. |\n", "+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\n", "only showing top 20 rows\n", "\n", "CPU times: user 4.05 ms, sys: 3.11 ms, total: 7.16 ms\n", "Wall time: 203 ms\n" ] } ], "source": [ "%%time\n", "\n", "from pyspark.sql.functions import col, lower\n", "\n", "# Filter review vertices by text containing \"disgusting\" (case-insensitive)\n", "result = g.vertices \\\n", " .filter(\"type = 'review'\") \\\n", " .filter(lower(col(\"text\")).contains(\"disgusting\")) \\\n", " .select(\"id\", \"text\") \\\n", " .limit(100)\n", "\n", "result.show(truncate=False)" ] }, { "cell_type": "markdown", "id": "6a5326aa-0f35-4f41-9593-5552630ae622", "metadata": {}, "source": [ "Top-rated businesses with sufficient reviews:" ] }, { "cell_type": "code", "execution_count": 45, "id": "6e561145-32b5-4d50-9324-e8df47aee243", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+----------------------------------+--------------+------------+------+\n", "|name |city |review_count|rating|\n", "+----------------------------------+--------------+------------+------+\n", "|Blues City Deli |Saint Louis |991 |5.0 |\n", "|Carlillos Cocina |Sparks |799 |5.0 |\n", "|Free Tours By Foot |New Orleans |769 |5.0 |\n", "|Tumerico |Tucson |705 |5.0 |\n", "|Yats |Franklin |623 |5.0 |\n", "|Nelson's Green Brier Distillery |Nashville |545 |5.0 |\n", "|Smiling With Hope Pizza |Reno |526 |5.0 |\n", "|Barracuda Deli Cafe St. Pete Beach|St Pete Beach |521 |5.0 |\n", "|SUGARED + BRONZED |Philadelphia |513 |5.0 |\n", "|Cafe Soleil |St. Pete Beach|468 |5.0 |\n", "|Kaffe Crepe |Reno |454 |5.0 |\n", "|Buena Onda |Santa Barbara |414 |5.0 |\n", "|JB Plumbing and Heating |Philadelphia |398 |5.0 |\n", "|Big Al's Deli |Nashville |390 |5.0 |\n", "|AJ's Press |Tampa |376 |5.0 |\n", "|Sustainable Wine Tours |Santa Barbara |358 |5.0 |\n", "|Barista Del Barrio |Tucson |357 |5.0 |\n", "|Two Chicks Walking Tours |New Orleans |342 |5.0 |\n", "|Sulphur Springs  Sandwiches Shop |Tampa |336 |5.0 |\n", "|Break Through Reno |Reno |335 |5.0 |\n", "+----------------------------------+--------------+------------+------+\n", "\n", "CPU times: user 3.5 ms, sys: 1.18 ms, total: 4.68 ms\n", "Wall time: 251 ms\n" ] } ], "source": [ "%%time\n", "\n", "from pyspark.sql.functions import col\n", "\n", "top_businesses = g.vertices \\\n", " .filter(\"type = 'business'\") \\\n", " .filter(col(\"review_count\") > 100) \\\n", " .select(\"name\", \"city\", \"review_count\", \"rating\") \\\n", " .orderBy(col(\"rating\").desc(), col(\"review_count\").desc()) \\\n", " .limit(20)\n", "\n", "top_businesses.show(truncate=False)" ] }, { "cell_type": "markdown", "id": "8f808d20-7f8f-4fcc-a67c-194d91053055", "metadata": {}, "source": [ "Most frequently used words in reviews:" ] }, { "cell_type": "code", "execution_count": 46, "id": "4ffa9d63-29ea-439c-9e6a-4fd5ede41c28", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 497:=====================================================> (39 + 1) / 40]\r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+-----+-------+\n", "|word |freq |\n", "+-----+-------+\n", "|with |5951983|\n", "|they |5728515|\n", "|that |5704227|\n", "|this |5533102|\n", "|have |4481587|\n", "|were |4089480|\n", "|very |2860076|\n", "|food |2646645|\n", "|place|2451808|\n", "|great|2382350|\n", "|just |2218440|\n", "|there|2200938|\n", "|good |2135079|\n", "|like |2062702|\n", "|their|2050512|\n", "|from |2028907|\n", "|when |1951966|\n", "|would|1921598|\n", "|will |1693047|\n", "|it's |1672136|\n", "+-----+-------+\n", "\n", "CPU times: user 13 ms, sys: 5.48 ms, total: 18.5 ms\n", "Wall time: 11.8 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] } ], "source": [ "%%time\n", "\n", "from pyspark.sql.functions import col, lower, split, explode, length, count\n", "\n", "# Extract and explode words from review text\n", "review_words = g.vertices \\\n", " .filter(\"type = 'review'\") \\\n", " .select(explode(split(lower(col(\"text\")), \" \")).alias(\"word\"))\n", "\n", "# Filter out short words and count word frequency\n", "word_counts = review_words \\\n", " .filter(length(col(\"word\")) > 3) \\\n", " .groupBy(\"word\") \\\n", " .agg(count(\"*\").alias(\"freq\")) \\\n", " .orderBy(col(\"freq\").desc()) \\\n", " .limit(20)\n", "\n", "word_counts.show(truncate=False)" ] }, { "cell_type": "markdown", "id": "1718879b-88a3-4d3a-a1df-5bf92885dead", "metadata": {}, "source": [ "Find businesses with only negative reviews:" ] }, { "cell_type": "code", "execution_count": 47, "id": "f171a0b0-8b0e-43fa-a8ab-35130f738246", "metadata": { "scrolled": true }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 509:================================================> (30 + 4) / 34]\r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+-------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+\n", "|business_name |stars |num_reviews|\n", "+-------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+\n", "|First Advantage Corporation |[1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0]|103 |\n", "|Affordable Spa Covers |[1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 2.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 2.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0] |83 |\n", "|Assurant |[1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 2.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0] |74 |\n", "|ADT Security Services |[2.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0] |50 |\n", "|Bella Pizza |[2.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 2.0, 1.0, 1.0, 1.0] |42 |\n", "|Progress Residential |[1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 2.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 2.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 2.0] |42 |\n", "|Republic Services of Telford, PA |[1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 2.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0] |40 |\n", "|Mainstreet Equity |[1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 2.0, 2.0, 2.0, 1.0, 1.0, 1.0, 2.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0] |39 |\n", "|DIRECTV |[1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0] |38 |\n", "|Barclays North American Capital Corporation|[1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 2.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 2.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0] |36 |\n", "|Lincare |[1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0] |36 |\n", "|STL Citywide |[1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 2.0, 1.0, 1.0, 2.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0] |35 |\n", "|Lertak Building Sustainment |[1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0] |32 |\n", "|Utility Billing Services |[2.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0] |32 |\n", "|St Louis Post Dispatch |[1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0] |32 |\n", "|Squeaky Clean House Services |[1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 2.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0] |32 |\n", "|KFC |[1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 2.0, 1.0, 2.0, 1.0, 2.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 2.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 2.0, 1.0, 1.0] |31 |\n", "|Republic Services |[1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 2.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 2.0, 1.0, 1.0, 1.0] |31 |\n", "|Roaman's |[1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0] |30 |\n", "|Winterfield Funding |[1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0] |29 |\n", "+-------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+\n", "only showing top 20 rows\n", "\n", "CPU times: user 15.2 ms, sys: 5.1 ms, total: 20.3 ms\n", "Wall time: 7.24 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] } ], "source": [ "%%time\n", "\n", "from pyspark.sql.functions import col, collect_list, size, array_max\n", "\n", "# Step 1: Match reviews and businesses\n", "reviews = g.find(\"(r)-[e]->(b)\") \\\n", " .filter(\"e.relationship = 'reviews' AND r.type = 'review' AND b.type = 'business'\") \\\n", " .select(col(\"b.id\").alias(\"business_id\"), col(\"b.name\").alias(\"business_name\"), col(\"r.stars\").alias(\"star\"))\n", "\n", "# Step 2: Group by business and collect stars\n", "grouped = reviews.groupBy(\"business_id\", \"business_name\") \\\n", " .agg(collect_list(\"star\").alias(\"stars\"))\n", "\n", "# Step 3: Keep only businesses where all stars are < 3 (i.e., max(stars) < 3)\n", "filtered = grouped.filter(array_max(col(\"stars\")) < 3)\n", "\n", "# Step 4: Order by number of reviews\n", "result = filtered.withColumn(\"num_reviews\", size(col(\"stars\"))) \\\n", " .orderBy(col(\"num_reviews\").desc())\n", "\n", "result.select(\"business_name\", \"stars\", \"num_reviews\").show(truncate=False)" ] }, { "cell_type": "markdown", "id": "d163e4c1-b1b7-4e4a-9018-4de164b6755a", "metadata": {}, "source": [ "# Graph Algorithms" ] }, { "cell_type": "markdown", "id": "87f826f4-79e8-4a3b-b556-e475d303f0db", "metadata": {}, "source": [ "The friend graph should be bidirected (i.e., undirected). Check:" ] }, { "cell_type": "code", "execution_count": 48, "id": "6cbd180e-ddb1-49fa-8245-422bd8f630bf", "metadata": { "collapsed": true, "jupyter": { "outputs_hidden": true } }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 543:===================> (3 + 6) / 9]\r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+----+---+\n", "|from| to|\n", "+----+---+\n", "+----+---+\n", "\n", "CPU times: user 36.1 ms, sys: 13.3 ms, total: 49.4 ms\n", "Wall time: 25.2 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] } ], "source": [ "%%time\n", "\n", "# Step 1: Find all outgoing friend edges (u1 -> u2)\n", "outgoing = g.find(\"(u1)-[e1]->(u2)\") \\\n", " .filter(\"e1.relationship = 'friend' AND u1.type = 'user' AND u2.type = 'user'\") \\\n", " .selectExpr(\"u1.id as from\", \"u2.id as to\")\n", "\n", "# Step 2: Find all incoming (reverse) friend edges (u2 -> u1)\n", "reverse = g.find(\"(u2)-[e2]->(u1)\") \\\n", " .filter(\"e2.relationship = 'friend' AND u1.type = 'user' AND u2.type = 'user'\") \\\n", " .selectExpr(\"u1.id as from\", \"u2.id as to\")\n", "\n", "# Step 3: Left anti join to find asymmetric ones\n", "asymmetric = outgoing.join(reverse, on=[\"from\", \"to\"], how=\"left_anti\")\n", "\n", "# Step 4: Limit result\n", "asymmetric.limit(10).show()" ] }, { "attachments": {}, "cell_type": "markdown", "id": "d2271cfb-8561-40af-85cb-da385c033b19", "metadata": {}, "source": [ "PageRank" ] }, { "cell_type": "code", "execution_count": null, "id": "805d91f5-2367-499a-8149-635272c73b39", "metadata": {}, "outputs": [], "source": [ "%%time\n", "\n", "from graphframes import GraphFrames\n", "from pyspark.sql.functions import col\n", "\n", "# 1. Filter users and friend relationships\n", "user_vertices = g.vertices.filter(\"type = 'user'\").select(\"id\", \"name\")\n", "friend_edges = g.edges.filter(\"relationship = 'friend'\").select(\"src\", \"dst\")\n", "\n", "# 2. Create a new GraphFrames for just the friend network\n", "friend_graph = GraphFrames(user_vertices, friend_edges)" ] }, { "cell_type": "code", "execution_count": 49, "id": "154e1359-0c8c-4f91-b377-e8454cf6a5c1", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 1399:=======> (5 + 24) / 34]\r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+----------------------+---------+------------------+\n", "|id |name |pagerank |\n", "+----------------------+---------+------------------+\n", "|WwulXySQN8t2hwqH_yWurA|Niki |895.7808235411599 |\n", "|vHc-UrI9yfL_pnnc6nJtyQ|Michael |674.4315743857834 |\n", "|AHRrG3T1gJpHvtpZ-K0G_g|Brittany |535.3000346801945 |\n", "|Oi1qbcz2m2SnwUeztGYcnQ|Steven |488.83630949749977|\n", "|ET8n-r7glWYqZhuR6GcdNw|Michelle |445.91484343492795|\n", "|6jjHo9Lilv3kTy87pm2ycw|Kaitlyn |439.1869956836817 |\n", "|mGHP3esQQqCEicHLRV-g5Q|Cara |360.20150174189143|\n", "|djxnI8Ux8ZYQJhiOQkrRhA|Abby |359.7405454846813 |\n", "|qVc8ODYU5SZjKXVBgXdI7w|Walker |337.71393752365594|\n", "|-kLVfaJytOJY2-QdQoCcNQ|Christina|337.13782729369717|\n", "+----------------------+---------+------------------+\n", "only showing top 10 rows\n", "\n", "CPU times: user 225 ms, sys: 91 ms, total: 316 ms\n", "Wall time: 2min 45s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] } ], "source": [ "%%time\n", "\n", "# 3. Run PageRank algorithm\n", "results = friend_graph.pageRank(resetProbability=0.15, maxIter=20)\n", "\n", "# 4. Show top users by PageRank score\n", "results.vertices.select(\"id\", \"name\", \"pagerank\") \\\n", " .orderBy(col(\"pagerank\").desc()) \\\n", " .show(10, truncate=False)" ] }, { "cell_type": "markdown", "id": "2642eb1a-40c6-4787-be24-5d27a4701901", "metadata": {}, "source": [ "PageRank to Persist (Spark does not support in-place update)" ] }, { "cell_type": "code", "execution_count": 50, "id": "5ab1b12f-237d-46b8-b0f3-ba8303cf38cb", "metadata": { "scrolled": true }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 1616:================================================> (190 + 10) / 200]\r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+----------------------+---------+------------------+\n", "|id |name |pagerank |\n", "+----------------------+---------+------------------+\n", "|WwulXySQN8t2hwqH_yWurA|Niki |895.7808235411599 |\n", "|vHc-UrI9yfL_pnnc6nJtyQ|Michael |674.4315743857834 |\n", "|AHRrG3T1gJpHvtpZ-K0G_g|Brittany |535.3000346801945 |\n", "|Oi1qbcz2m2SnwUeztGYcnQ|Steven |488.83630949749977|\n", "|ET8n-r7glWYqZhuR6GcdNw|Michelle |445.91484343492795|\n", "|6jjHo9Lilv3kTy87pm2ycw|Kaitlyn |439.1869956836817 |\n", "|mGHP3esQQqCEicHLRV-g5Q|Cara |360.20150174189143|\n", "|djxnI8Ux8ZYQJhiOQkrRhA|Abby |359.7405454846813 |\n", "|qVc8ODYU5SZjKXVBgXdI7w|Walker |337.71393752365594|\n", "|-kLVfaJytOJY2-QdQoCcNQ|Christina|337.13782729369717|\n", "+----------------------+---------+------------------+\n", "only showing top 10 rows\n", "\n", "CPU times: user 19.4 ms, sys: 5.75 ms, total: 25.2 ms\n", "Wall time: 12.3 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] } ], "source": [ "%%time\n", "\n", "updated_vertices = friend_graph.vertices.join(\n", " results.vertices.select(\"id\", \"pagerank\"),\n", " on=\"id\",\n", " how=\"left\"\n", ")\n", "\n", "updated_vertices.write.mode(\"overwrite\").parquet(\"hdfs://master:9000/user/exouser/graph_output/users_with_pagerank\")\n", "\n", "# Show a preview\n", "updated_vertices.select(\"id\", \"name\", \"pagerank\").orderBy(col(\"pagerank\").desc()).show(10, truncate=False)" ] }, { "cell_type": "markdown", "id": "9c69f5a4-8037-49d8-851f-8662ab8a8b98", "metadata": {}, "source": [ "Degree Centrality" ] }, { "cell_type": "code", "execution_count": 51, "id": "9a4c7264-3a06-4a2e-ab69-c9b16b970f18", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 1729:====================================================> (33 + 1) / 34]\r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+---------+----------------------+------+\n", "|name |id |degree|\n", "+---------+----------------------+------+\n", "|Walker |qVc8ODYU5SZjKXVBgXdI7w|18530 |\n", "|Steven |Oi1qbcz2m2SnwUeztGYcnQ|16940 |\n", "|Ruggy |iLjMdZi0Tm7DQxX1C1_2dg|15808 |\n", "|Randy |ZIOCmdFaMIF56FR-nWr_2A|15419 |\n", "|Scott |mV4lknblF-zOKSF8nlGqDA|14861 |\n", "|Katie |hizGc5W1tBHPghM5YKCAtg|13281 |\n", "|Abby |djxnI8Ux8ZYQJhiOQkrRhA|13050 |\n", "|Niki |WwulXySQN8t2hwqH_yWurA|12571 |\n", "|Danny |IU86PZPgTDCFwJEuAg2j7g|12083 |\n", "|Stephanie|wEE-YMx5pmSuagLtNxMPKA|11791 |\n", "+---------+----------------------+------+\n", "\n", "CPU times: user 16.4 ms, sys: 5.47 ms, total: 21.8 ms\n", "Wall time: 13.6 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] } ], "source": [ "%%time\n", "\n", "from graphframes import GraphFrames\n", "from pyspark.sql.functions import col, desc\n", "\n", "# Step 1: Compute Degree (in + out)\n", "degree_df = friend_graph.degrees\n", "\n", "# Step 2: Join back to vertices to get user names\n", "result = friend_graph.vertices.join(degree_df, on=\"id\") \\\n", " .select(\"name\", \"id\", \"degree\") \\\n", " .orderBy(desc(\"degree\")) \\\n", " .limit(10)\n", "\n", "result.show(truncate=False)" ] }, { "cell_type": "markdown", "id": "2364f662-cf14-4697-aa5d-66a97edd9ca3", "metadata": {}, "source": [ "Weakly Connected Components (WCC)" ] }, { "cell_type": "code", "execution_count": 52, "id": "5cdefb58-77af-43db-ac31-344af1e696f5", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 1977:===============================================> (182 + 8) / 200]\r" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+------------+------+\n", "|component |size |\n", "+------------+------+\n", "|2 |892152|\n", "|51539615824 |7 |\n", "|661424966575|6 |\n", "|77309412624 |6 |\n", "|111669159367|5 |\n", "|420906803621|5 |\n", "|274877910701|5 |\n", "|257698043752|5 |\n", "|103079220109|5 |\n", "|60129546926 |5 |\n", "+------------+------+\n", "\n", "CPU times: user 116 ms, sys: 30.5 ms, total: 147 ms\n", "Wall time: 1min 21s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " \r" ] } ], "source": [ "%%time\n", "\n", "from pyspark.sql.functions import col, desc\n", "\n", "# Set checkpoint dir once before any connectedComponents call\n", "spark.sparkContext.setCheckpointDir(\"hdfs://master:9000/tmp/spark-checkpoints\")\n", "\n", "# Step 1: Run WCC (connected components)\n", "wcc_result = friend_graph.connectedComponents()\n", "\n", "# Step 2: Count the size of each component\n", "component_sizes = wcc_result.groupBy(\"component\").count() \\\n", " .orderBy(desc(\"count\")) \\\n", " .withColumnRenamed(\"count\", \"size\") \\\n", " .limit(10)\n", "\n", "component_sizes.show(truncate=False)" ] }, { "cell_type": "markdown", "id": "7e9c89a2-f941-476e-9fbc-f0de00a953d9", "metadata": {}, "source": [ "Try to cache the graph and see if the performance improves with executors:" ] }, { "cell_type": "code", "execution_count": null, "id": "e236e8fe-e9b6-470e-b4a8-a2dd3a90c8cb", "metadata": {}, "outputs": [], "source": [ "%%time\n", "\n", "friend_graph.vertices.cache()\n", "friend_graph.edges.cache()\n", "\n", "# Force caching\n", "friend_graph.vertices.count(), friend_graph.edges.count()" ] }, { "cell_type": "code", "execution_count": null, "id": "f13757bf-bb78-42a2-97de-e0511e290c1b", "metadata": {}, "outputs": [], "source": [ "%%time\n", "\n", "spark.sparkContext.setCheckpointDir(\"hdfs://master:9000/tmp/spark-checkpoints\")\n", "spark.conf.set(\"spark.sql.shuffle.partitions\", \"64\")\n", "\n", "# Run WCC\n", "wcc_result = friend_graph.connectedComponents()\n", "\n", "component_sizes = wcc_result.groupBy(\"component\").count() \\\n", " .orderBy(desc(\"count\")) \\\n", " .withColumnRenamed(\"count\", \"size\") \\\n", " .limit(10)\n", "\n", "component_sizes.show(truncate=False)" ] }, { "cell_type": "code", "execution_count": 4, "id": "395a78de-2149-4b8b-9d31-d3768aa9e523", "metadata": {}, "outputs": [], "source": [ "spark.stop()" ] }, { "cell_type": "code", "execution_count": 6, "id": "9839f5b7-c930-4936-b8e5-d5e5e8388046", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Web UI: http://master:4040\n" ] } ], "source": [ "print(\"Web UI:\", spark.sparkContext.uiWebUrl)" ] }, { "cell_type": "code", "execution_count": null, "id": "c4d70723-5a27-46c1-a4d3-7481037c7493", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Resolved master IP: 10.2.223.215\n" ] } ], "source": [ "import socket\n", "\n", "master_ip = socket.gethostbyname(\"master\")\n", "print(\"Resolved master IP:\", master_ip)\n" ] }, { "cell_type": "code", "execution_count": null, "id": "6ceb809a-a77c-4ff4-9ebf-80ee46a205dd", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.12.7" } }, "nbformat": 4, "nbformat_minor": 5 }