main2_level1_finalized.ipynb

{"nbformat":4,"nbformat_minor":0,"metadata":{"colab":{"name":"main2_level1_finalized.ipynb","provenance":[{"file_id":"124RLfe-TG42MzdYHbIVLnGnlcbOleHMg","timestamp":1613022345782}],"collapsed_sections":[],"authorship_tag":"ABX9TyPTIfJCogW41DoPvQ5OrwEn"},"kernelspec":{"name":"python3","display_name":"Python 3"}},"cells":[{"cell_type":"code","metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"IJcg2brHJZ0R","executionInfo":{"status":"ok","timestamp":1613010504722,"user_tz":300,"elapsed":56304,"user":{"displayName":"Erika Yi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ggz9v3ajjt637Cb6hx3Z1CLaITqBLD0pdIokrmTwQ=s64","userId":"04227724872831522525"}},"outputId":"1fe8b6bd-f87c-46d0-d31e-b7a817d4fa14"},"source":["import os\r\n","# Find the latest version of spark 2.0  from http://www-us.apache.org/dist/spark/ and enter as the spark version\r\n","# For example:\r\n","# spark_version = 'spark-3.0.1'\r\n","spark_version = 'spark-3.0.1'\r\n","os.environ['SPARK_VERSION']=spark_version\r\n","\r\n","# Install Spark and Java\r\n","!apt-get update\r\n","!apt-get install openjdk-8-jdk-headless -qq > /dev/null\r\n","!wget -q http://www-us.apache.org/dist/spark/$SPARK_VERSION/$SPARK_VERSION-bin-hadoop2.7.tgz\r\n","!tar xf $SPARK_VERSION-bin-hadoop2.7.tgz\r\n","!pip install -q findspark\r\n","\r\n","# Set Environment Variables\r\n","os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-8-openjdk-amd64\"\r\n","os.environ[\"SPARK_HOME\"] = f\"/content/{spark_version}-bin-hadoop2.7\"\r\n","\r\n","# Start a SparkSession\r\n","import findspark\r\n","findspark.init()"],"execution_count":null,"outputs":[{"output_type":"stream","text":["Get:1 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ InRelease [3,626 B]\n","Get:2 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]\n","Get:3 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic InRelease [15.9 kB]\n","Get:4 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ Packages [44.8 kB]\n","Ign:5 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease\n","Hit:6 http://archive.ubuntu.com/ubuntu bionic InRelease\n","Get:7 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]\n","Hit:8 http://ppa.launchpad.net/cran/libgit2/ubuntu bionic InRelease\n","Ign:9 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease\n","Get:10 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Release [697 B]\n","Hit:11 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\n","Get:12 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Release.gpg [836 B]\n","Get:13 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 Packages [1,391 kB]\n","Hit:14 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic InRelease\n","Get:16 http://archive.ubuntu.com/ubuntu bionic-backports InRelease [74.6 kB]\n","Get:17 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic/main Sources [1,729 kB]\n","Get:18 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 Packages [2,358 kB]\n","Ign:19 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Packages\n","Get:19 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Packages [576 kB]\n","Get:20 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages [1,927 kB]\n","Get:21 http://archive.ubuntu.com/ubuntu bionic-updates/universe amd64 Packages [2,162 kB]\n","Get:22 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic/main amd64 Packages [885 kB]\n","Fetched 11.3 MB in 6s (1,923 kB/s)\n","Reading package lists... Done\n"],"name":"stdout"}]},{"cell_type":"code","metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"rmvQvUvpvmAV","executionInfo":{"status":"ok","timestamp":1613010588796,"user_tz":300,"elapsed":2275,"user":{"displayName":"Erika Yi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ggz9v3ajjt637Cb6hx3Z1CLaITqBLD0pdIokrmTwQ=s64","userId":"04227724872831522525"}},"outputId":"e35046b6-9ed4-4b53-bdc6-5ceea577b959"},"source":["!wget https://jdbc.postgresql.org/download/postgresql-42.2.9.jar"],"execution_count":null,"outputs":[{"output_type":"stream","text":["--2021-02-11 02:29:45--  https://jdbc.postgresql.org/download/postgresql-42.2.9.jar\n","Resolving jdbc.postgresql.org (jdbc.postgresql.org)... 72.32.157.228, 2001:4800:3e1:1::228\n","Connecting to jdbc.postgresql.org (jdbc.postgresql.org)|72.32.157.228|:443... connected.\n","HTTP request sent, awaiting response... 200 OK\n","Length: 914037 (893K) [application/java-archive]\n","Saving to: ‘postgresql-42.2.9.jar’\n","\n","postgresql-42.2.9.j 100%[===================>] 892.61K  1.30MB/s    in 0.7s    \n","\n","2021-02-11 02:29:47 (1.30 MB/s) - ‘postgresql-42.2.9.jar’ saved [914037/914037]\n","\n"],"name":"stdout"}]},{"cell_type":"code","metadata":{"id":"MOkRxDgcvn8A"},"source":["from pyspark.sql import SparkSession\r\n","spark = SparkSession.builder.appName(\"KitchenAmazonReview\").config(\"spark.driver.extraClassPath\",\"/content/postgresql-42.2.9.jar\").getOrCreate()"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"tmpGCglwJmeO","colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"status":"ok","timestamp":1613010767902,"user_tz":300,"elapsed":168315,"user":{"displayName":"Erika Yi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ggz9v3ajjt637Cb6hx3Z1CLaITqBLD0pdIokrmTwQ=s64","userId":"04227724872831522525"}},"outputId":"7b2ce26b-7091-42ff-9efc-d93ca89a6232"},"source":["from pyspark import SparkFiles\r\n","# Load in user_data.csv from S3 into a DataFrame\r\n","url = \"https://s3.amazonaws.com/amazon-reviews-pds/tsv/amazon_reviews_us_Kitchen_v1_00.tsv.gz\"\r\n","spark.sparkContext.addFile(url)\r\n","\r\n","kitchen_df = spark.read.option('header', 'true').csv(SparkFiles.get(\"amazon_reviews_us_Kitchen_v1_00.tsv.gz\"), inferSchema=True, sep='\\t', timestampFormat=\"mm/dd/yy\")\r\n","kitchen_df.show(10)"],"execution_count":null,"outputs":[{"output_type":"stream","text":["+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n","|marketplace|customer_id|     review_id|product_id|product_parent|       product_title|product_category|star_rating|helpful_votes|total_votes|vine|verified_purchase|     review_headline|         review_body|review_date|\n","+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n","|         US|   37000337|R3DT59XH7HXR9K|B00303FI0G|     529320574|Arthur Court Pape...|         Kitchen|          5|            0|          0|   N|                Y|Beautiful. Looks ...|Beautiful.  Looks...| 2015-08-31|\n","|         US|   15272914|R1LFS11BNASSU8|B00JCZKZN6|     274237558|Olde Thompson Bav...|         Kitchen|          5|            0|          1|   N|                Y| Awesome & Self-ness|I personally have...| 2015-08-31|\n","|         US|   36137863|R296RT05AG0AF6|B00JLIKA5C|     544675303|Progressive Inter...|         Kitchen|          5|            0|          0|   N|                Y|Fabulous and wort...|Fabulous and wort...| 2015-08-31|\n","|         US|   43311049|R3V37XDZ7ZCI3L|B000GBNB8G|     491599489|Zyliss Jumbo Garl...|         Kitchen|          5|            0|          1|   N|                Y|          Five Stars|A must if you lov...| 2015-08-31|\n","|         US|   13763148|R14GU232NQFYX2|B00VJ5KX9S|     353790155|1 X Premier Pizza...|         Kitchen|          5|            0|          0|   N|                Y|     Better than sex|Worth every penny...| 2015-08-31|\n","|         US|   19009420| RZQH4V7L2O1PL|B00HYB5YY0|     432241873|       CHEF Aluminum|         Kitchen|          1|            1|          1|   N|                Y|Does not work on ...|The description s...| 2015-08-31|\n","|         US|   40599388|R1F8JMOSPJ3KO7|B000HEBAV2|     584680984|Presto Dual Profr...|         Kitchen|          5|            0|          0|   N|                Y|Awesome! First fr...|Awesome! First fr...| 2015-08-31|\n","|         US|   22719359|R1ZISGY2BWW4Z5|B0012DS4GG|     772637306|Rubbermaid Produc...|         Kitchen|          5|            0|          0|   N|                Y|          Five Stars|Very good item. Q...| 2015-08-31|\n","|         US|   47478640|R17PW4I3AE5WZW|B00FLQ4EE6|     755416578|Cuisinart 12-Piec...|         Kitchen|          5|            0|          0|   N|                Y|          Five Stars|sharp and look great| 2015-08-31|\n","|         US|   34195504|R3D93G1KTP6A8P|B00DBS9OTG|     648762742|Kegco 6\" Stainles...|         Kitchen|          3|            0|          0|   N|                Y|         Three Stars|Should have come ...| 2015-08-31|\n","+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n","only showing top 10 rows\n","\n"],"name":"stdout"}]},{"cell_type":"code","metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"a9xcgwfowu1z","executionInfo":{"status":"ok","timestamp":1613010788770,"user_tz":300,"elapsed":187641,"user":{"displayName":"Erika Yi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ggz9v3ajjt637Cb6hx3Z1CLaITqBLD0pdIokrmTwQ=s64","userId":"04227724872831522525"}},"outputId":"8536f5d5-2515-4a92-c4ad-1f108e6fe047"},"source":["# Count the number of the reviews \r\n","print(kitchen_df.count())"],"execution_count":null,"outputs":[{"output_type":"stream","text":["4880466\n"],"name":"stdout"}]},{"cell_type":"code","metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"4ZT_XsrYwwRT","executionInfo":{"status":"ok","timestamp":1613010835913,"user_tz":300,"elapsed":233794,"user":{"displayName":"Erika Yi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ggz9v3ajjt637Cb6hx3Z1CLaITqBLD0pdIokrmTwQ=s64","userId":"04227724872831522525"}},"outputId":"4444b0cf-bd53-453f-833c-d396c3aa76a6"},"source":["# Drop duplicates and incomplete rows \r\n","dropped_kitchen_df = kitchen_df.dropna()\r\n","print(dropped_kitchen_df.count())"],"execution_count":null,"outputs":[{"output_type":"stream","text":["4879961\n"],"name":"stdout"}]},{"cell_type":"code","metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"22KnJrvpwwmN","executionInfo":{"status":"ok","timestamp":1613010966487,"user_tz":300,"elapsed":363126,"user":{"displayName":"Erika Yi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ggz9v3ajjt637Cb6hx3Z1CLaITqBLD0pdIokrmTwQ=s64","userId":"04227724872831522525"}},"outputId":"d60daaa1-3afb-46fb-d352-21c4bb51e9ec"},"source":["# Drop duplicates and incomplete rows\r\n","clean_kitchen_df = dropped_kitchen_df.dropDuplicates()\r\n","print(clean_kitchen_df.count())"],"execution_count":null,"outputs":[{"output_type":"stream","text":["4879961\n"],"name":"stdout"}]},{"cell_type":"code","metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"x-BWhr47ww2F","executionInfo":{"status":"ok","timestamp":1613010966488,"user_tz":300,"elapsed":361989,"user":{"displayName":"Erika Yi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ggz9v3ajjt637Cb6hx3Z1CLaITqBLD0pdIokrmTwQ=s64","userId":"04227724872831522525"}},"outputId":"07e23f15-8f88-4a8a-b925-1e49cdf23cca"},"source":["# Check the schema\r\n","clean_kitchen_df.printSchema()"],"execution_count":null,"outputs":[{"output_type":"stream","text":["root\n"," |-- marketplace: string (nullable = true)\n"," |-- customer_id: integer (nullable = true)\n"," |-- review_id: string (nullable = true)\n"," |-- product_id: string (nullable = true)\n"," |-- product_parent: integer (nullable = true)\n"," |-- product_title: string (nullable = true)\n"," |-- product_category: string (nullable = true)\n"," |-- star_rating: integer (nullable = true)\n"," |-- helpful_votes: integer (nullable = true)\n"," |-- total_votes: integer (nullable = true)\n"," |-- vine: string (nullable = true)\n"," |-- verified_purchase: string (nullable = true)\n"," |-- review_headline: string (nullable = true)\n"," |-- review_body: string (nullable = true)\n"," |-- review_date: string (nullable = true)\n","\n"],"name":"stdout"}]},{"cell_type":"code","metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"B_cG1GakwxEV","executionInfo":{"status":"ok","timestamp":1613011120736,"user_tz":300,"elapsed":118237,"user":{"displayName":"Erika Yi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ggz9v3ajjt637Cb6hx3Z1CLaITqBLD0pdIokrmTwQ=s64","userId":"04227724872831522525"}},"outputId":"da9eece7-100a-4d99-c3ed-470698a16827"},"source":["# Create a new DataFrame for review info\r\n","review_id_table_kitchen = clean_kitchen_df.select([\"review_id\", \"customer_id\", \"product_id\", \"product_parent\", \"review_date\"])\r\n","review_id_table_kitchen.show(5)"],"execution_count":null,"outputs":[{"output_type":"stream","text":["+--------------+-----------+----------+--------------+-----------+\n","|     review_id|customer_id|product_id|product_parent|review_date|\n","+--------------+-----------+----------+--------------+-----------+\n","|R1002B10PD9ZFM|   24197845|B000LTOCSG|     630072149| 2009-07-20|\n","|R1007DQP1BG5WJ|   17990743|B007M2OHEY|     362955205| 2014-09-12|\n","|R100AIJ0ZFN8CZ|   33816354|B0001ZYYQU|     331822491| 2007-03-09|\n","|R100C48B994FWD|   13231777|B00IK59T1S|     744019201| 2014-11-26|\n","|R100E300CKVFYD|   53006884|B00833DQCU|     298219798| 2013-12-08|\n","+--------------+-----------+----------+--------------+-----------+\n","only showing top 5 rows\n","\n"],"name":"stdout"}]},{"cell_type":"code","metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"0cwexU4rZh44","executionInfo":{"status":"ok","timestamp":1613011240231,"user_tz":300,"elapsed":234041,"user":{"displayName":"Erika Yi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ggz9v3ajjt637Cb6hx3Z1CLaITqBLD0pdIokrmTwQ=s64","userId":"04227724872831522525"}},"outputId":"9e824ba6-6970-4b99-9892-bc6af8f76ff4"},"source":["# Create a new DataFrame for unique values\r\n","products_kitchen = clean_kitchen_df.select([\"product_id\", \"product_title\"])\r\n","products_kitchen.show(5)"],"execution_count":null,"outputs":[{"output_type":"stream","text":["+----------+--------------------+\n","|product_id|       product_title|\n","+----------+--------------------+\n","|B000LTOCSG|Replacement Filte...|\n","|B007M2OHEY|Fred & Friends E....|\n","|B0001ZYYQU|KitchenAid Artisa...|\n","|B00IK59T1S|PUR 7 Cup Pitcher...|\n","|B00833DQCU|Prepworks from Pr...|\n","+----------+--------------------+\n","only showing top 5 rows\n","\n"],"name":"stdout"}]},{"cell_type":"code","metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"_I6IrGCeZiQV","executionInfo":{"status":"ok","timestamp":1613011368215,"user_tz":300,"elapsed":360856,"user":{"displayName":"Erika Yi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ggz9v3ajjt637Cb6hx3Z1CLaITqBLD0pdIokrmTwQ=s64","userId":"04227724872831522525"}},"outputId":"b8289b9b-f8b6-4091-ec55-769ea3aff092"},"source":["# Total customers count \r\n","customer_count = clean_kitchen_df.select([\"customer_id\"]).count()\r\n","customer_count"],"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/plain":["4879961"]},"metadata":{"tags":[]},"execution_count":11}]},{"cell_type":"code","metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"yKk4YrhOZigI","executionInfo":{"status":"ok","timestamp":1613011502641,"user_tz":300,"elapsed":491358,"user":{"displayName":"Erika Yi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ggz9v3ajjt637Cb6hx3Z1CLaITqBLD0pdIokrmTwQ=s64","userId":"04227724872831522525"}},"outputId":"e45087f5-b722-4486-d3b4-65e19cfe754e"},"source":["# Create a new DataFrame for customers\r\n","customers_kitchen = clean_kitchen_df.groupBy('customer_id').count()\r\n","customers_kitchen_counts = customers_kitchen.withColumnRenamed('count', 'customers_count')\r\n","customers_kitchen_counts.show(5)"],"execution_count":null,"outputs":[{"output_type":"stream","text":["+-----------+---------------+\n","|customer_id|customers_count|\n","+-----------+---------------+\n","|   16308995|              2|\n","|    4709446|              1|\n","|   31546122|              2|\n","|   25866981|              7|\n","|   11537699|              4|\n","+-----------+---------------+\n","only showing top 5 rows\n","\n"],"name":"stdout"}]},{"cell_type":"code","metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"yAykekgklpAs","executionInfo":{"status":"ok","timestamp":1613011622287,"user_tz":300,"elapsed":609973,"user":{"displayName":"Erika Yi","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Ggz9v3ajjt637Cb6hx3Z1CLaITqBLD0pdIokrmTwQ=s64","userId":"04227724872831522525"}},"outputId":"96accdcc-d316-40ce-edc4-9dcdf98c7927"},"source":["# Vine DataFrame \r\n","vine_kitchen = clean_kitchen_df.select(['review_id', 'star_rating', 'helpful_votes', 'total_votes', 'vine'])\r\n","vine_kitchen.show(5)"],"execution_count":null,"outputs":[{"output_type":"stream","text":["+--------------+-----------+-------------+-----------+----+\n","|     review_id|star_rating|helpful_votes|total_votes|vine|\n","+--------------+-----------+-------------+-----------+----+\n","|R1002B10PD9ZFM|          5|            0|          0|   N|\n","|R1007DQP1BG5WJ|          5|            0|          0|   N|\n","|R100AIJ0ZFN8CZ|          4|            3|          3|   N|\n","|R100C48B994FWD|          5|            0|          0|   N|\n","|R100E300CKVFYD|          1|            3|          3|   N|\n","+--------------+-----------+-------------+-----------+----+\n","only showing top 5 rows\n","\n"],"name":"stdout"}]},{"cell_type":"code","metadata":{"id":"uJkrvP9LuPah"},"source":["# Write DataFrame to RDS\r\n","# Configuration for RDS instance\r\n","\r\n","mode=\"append\"\r\n","jdbc_url = \"jdbc:postgresql://:5432/\"\r\n","config = {\"user\": \"\",\r\n","          \"password\": \"\",\r\n","          \"driver\":\"org.postgresql.Driver\"}"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"tCEdgux3wxV2"},"source":["# Write DataFrame to pgAdmin table\r\n","\r\n","review_id_table_kitchen.write.jdbc(url=jdbc_url, table='review_id_table', mode=mode, properties=config)"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"FzgypvmAJmR1"},"source":["# Write DataFrame to pgAdmin table\r\n","\r\n","products_kitchen.write.jdbc(url=jdbc_url, table='products', mode=mode, properties=config)"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"zJHclX7gJmk0"},"source":["# Write DataFrame to pgAdmin table\r\n","\r\n","customers_kitchen_counts.write.jdbc(url=jdbc_url, table='customers', mode=mode, properties=config)"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"iEWK3HrYJm2M"},"source":["# Write DataFrame to pgAdmin table\r\n","\r\n","vine_kitchen.write.jdbc(url=jdbc_url, table='vine_table', mode=mode, properties=config)"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"9L0Ser8xJnFx"},"source":[""],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"W9v-j42QJnS8"},"source":[""],"execution_count":null,"outputs":[]}]}