Amazon_Reviews_ETL.ipynb

{"nbformat":4,"nbformat_minor":0,"metadata":{"colab":{"name":"Amazon_Reviews_ETL.ipynb","provenance":[],"collapsed_sections":[]},"kernelspec":{"name":"python3","display_name":"Python 3"},"nteract":{"version":"0.12.3"},"language_info":{"name":"python"}},"cells":[{"cell_type":"code","metadata":{"id":"V58rxea0HqSa","colab":{"base_uri":"https://localhost:8080/"},"outputId":"f143f679-c11d-4729-dfca-6bd1b8eec149","executionInfo":{"status":"ok","timestamp":1656247159339,"user_tz":240,"elapsed":24128,"user":{"displayName":"URVISH DESAI","userId":"15385214001091362057"}}},"source":["import os\n","# Find the latest version of spark 3.0 from http://www.apache.org/dist/spark/ and enter as the spark version\n","# For example:\n","# spark_version = 'spark-3.0.3'\n","spark_version = 'spark-3.0.3'\n","os.environ['SPARK_VERSION']=spark_version\n","\n","# Install Spark and Java\n","!apt-get update\n","!apt-get install openjdk-11-jdk-headless -qq > /dev/null\n","!wget -q http://www.apache.org/dist/spark/$SPARK_VERSION/$SPARK_VERSION-bin-hadoop2.7.tgz\n","!tar xf $SPARK_VERSION-bin-hadoop2.7.tgz\n","!pip install -q findspark\n","\n","# Set Environment Variables\n","import os\n","os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-11-openjdk-amd64\"\n","os.environ[\"SPARK_HOME\"] = f\"/content/{spark_version}-bin-hadoop2.7\"\n","\n","# Start a SparkSession\n","import findspark\n","findspark.init()"],"execution_count":1,"outputs":[{"output_type":"stream","name":"stdout","text":["\r0% [Working]\r            \rGet:1 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ InRelease [3,626 B]\n","\r0% [Connecting to archive.ubuntu.com (185.125.190.36)] [Waiting for headers] [1\r0% [Connecting to archive.ubuntu.com (185.125.190.36)] [Waiting for headers] [C\r                                                                               \rGet:2 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]\n","\r                                                                               \rHit:3 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease\n","\r0% [Connecting to archive.ubuntu.com (185.125.190.36)] [2 InRelease 14.2 kB/88.\r0% [1 InRelease gpgv 3,626 B] [Connecting to archive.ubuntu.com (185.125.190.36\r0% [1 InRelease gpgv 3,626 B] [Waiting for headers] [Waiting for headers] [Wait\r                                                                               \rHit:4 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic InRelease\n","\r                                                                               \r0% [1 InRelease gpgv 3,626 B] [Waiting for headers] [Waiting for headers]\r                                                                         \rHit:5 http://archive.ubuntu.com/ubuntu bionic InRelease\n","\r0% [1 InRelease gpgv 3,626 B] [Waiting for headers] [Connecting to ppa.launchpa\r                                                                               \rIgn:6 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease\n","Get:7 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]\n","Hit:8 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\n","Hit:9 http://ppa.launchpad.net/cran/libgit2/ubuntu bionic InRelease\n","Hit:10 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu bionic InRelease\n","Get:11 http://archive.ubuntu.com/ubuntu bionic-backports InRelease [74.6 kB]\n","Hit:12 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic InRelease\n","Get:13 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages [2,866 kB]\n","Get:14 http://security.ubuntu.com/ubuntu bionic-security/restricted amd64 Packages [1,013 kB]\n","Get:15 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 Packages [1,521 kB]\n","Get:16 http://security.ubuntu.com/ubuntu bionic-security/multiverse amd64 Packages [22.8 kB]\n","Get:18 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 Packages [3,298 kB]\n","Get:19 http://archive.ubuntu.com/ubuntu bionic-updates/universe amd64 Packages [2,297 kB]\n","Get:20 http://archive.ubuntu.com/ubuntu bionic-updates/multiverse amd64 Packages [29.8 kB]\n","Get:21 http://archive.ubuntu.com/ubuntu bionic-updates/restricted amd64 Packages [1,047 kB]\n","Get:22 http://archive.ubuntu.com/ubuntu bionic-backports/main amd64 Packages [12.2 kB]\n","Fetched 12.4 MB in 5s (2,451 kB/s)\n","Reading package lists... Done\n"]}]},{"cell_type":"code","metadata":{"id":"_xKwTpATHqSe","colab":{"base_uri":"https://localhost:8080/"},"outputId":"dca9a033-36a9-4076-9401-b92f3d00aee1","executionInfo":{"status":"ok","timestamp":1656247175345,"user_tz":240,"elapsed":678,"user":{"displayName":"URVISH DESAI","userId":"15385214001091362057"}}},"source":["# Download the Postgres driver that will allow Spark to interact with Postgres.\n","!wget https://jdbc.postgresql.org/download/postgresql-42.2.16.jar"],"execution_count":2,"outputs":[{"output_type":"stream","name":"stdout","text":["--2022-06-26 12:39:34--  https://jdbc.postgresql.org/download/postgresql-42.2.16.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: 1002883 (979K) [application/java-archive]\n","Saving to: ‘postgresql-42.2.16.jar’\n","\n","postgresql-42.2.16. 100%[===================>] 979.38K  5.68MB/s    in 0.2s    \n","\n","2022-06-26 12:39:35 (5.68 MB/s) - ‘postgresql-42.2.16.jar’ saved [1002883/1002883]\n","\n"]}]},{"cell_type":"code","metadata":{"id":"MMqDAjVS0KN9","executionInfo":{"status":"ok","timestamp":1656247201326,"user_tz":240,"elapsed":8263,"user":{"displayName":"URVISH DESAI","userId":"15385214001091362057"}}},"source":["from pyspark.sql import SparkSession\n","spark = SparkSession.builder.appName(\"M16-Amazon-Challenge\").config(\"spark.driver.extraClassPath\",\"/content/postgresql-42.2.16.jar\").getOrCreate()"],"execution_count":3,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"cyBsySGuY-9V"},"source":["### Load Amazon Data into Spark DataFrame"]},{"cell_type":"code","metadata":{"id":"CtCmBhQJY-9Z","colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"status":"ok","timestamp":1656247617921,"user_tz":240,"elapsed":36017,"user":{"displayName":"URVISH DESAI","userId":"15385214001091362057"}},"outputId":"6380f752-9298-4a46-8459-1c19e2322ca0"},"source":["from pyspark import SparkFiles\n","url = \"https://s3.amazonaws.com/amazon-reviews-pds/tsv/amazon_reviews_us_Video_Games_v1_00.tsv.gz\"\n","spark.sparkContext.addFile(url)\n","df = spark.read.option(\"encoding\", \"UTF-8\").csv(SparkFiles.get(\"amazon_reviews_us_Video_Games_v1_00.tsv.gz\"), sep=\"\\t\", header=True, inferSchema=True)\n","df.show()"],"execution_count":4,"outputs":[{"output_type":"stream","name":"stdout","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|   12039526| RTIS3L2M1F5SM|B001CXYMFS|     737716809|Thrustmaster T-Fl...|     Video Games|          5|            0|          0|   N|                Y|an amazing joysti...|Used this for Eli...| 2015-08-31|\n","|         US|    9636577| R1ZV7R40OLHKD|B00M920ND6|     569686175|Tonsee 6 buttons ...|     Video Games|          5|            0|          0|   N|                Y|Definitely a sile...|Loved it,  I didn...| 2015-08-31|\n","|         US|    2331478|R3BH071QLH8QMC|B0029CSOD2|      98937668|Hidden Mysteries:...|     Video Games|          1|            0|          1|   N|                Y|            One Star|poor quality work...| 2015-08-31|\n","|         US|   52495923|R127K9NTSXA2YH|B00GOOSV98|      23143350|GelTabz Performan...|     Video Games|          3|            0|          0|   N|                Y|good, but could b...|nice, but tend to...| 2015-08-31|\n","|         US|   14533949|R32ZWUXDJPW27Q|B00Y074JOM|     821342511|Zero Suit Samus a...|     Video Games|          4|            0|          0|   N|                Y|   Great but flawed.|Great amiibo, gre...| 2015-08-31|\n","|         US|    2377552|R3AQQ4YUKJWBA6|B002UBI6W6|     328764615|Psyclone Recharge...|     Video Games|          1|            0|          0|   N|                Y|            One Star|The remote consta...| 2015-08-31|\n","|         US|   17521011|R2F0POU5K6F73F|B008XHCLFO|      24234603|Protection for yo...|     Video Games|          5|            0|          0|   N|                Y|              A Must|I have a 2012-201...| 2015-08-31|\n","|         US|   19676307|R3VNR804HYSMR6|B00BRA9R6A|     682267517|   Nerf 3DS XL Armor|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|Perfect, kids lov...| 2015-08-31|\n","|         US|     224068| R3GZTM72WA2QH|B009EPWJLA|     435241890|One Piece: Pirate...|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|            Excelent| 2015-08-31|\n","|         US|   48467989| RNQOY62705W1K|B0000AV7GB|     256572651|Playstation 2 Dan...|     Video Games|          4|            0|          0|   N|                Y|          Four Stars|Slippery but expe...| 2015-08-31|\n","|         US|     106569|R1VTIA3JTYBY02|B00008KTNN|     384411423|Metal Arms: Glitc...|     Video Games|          5|            0|          0|   N|                N|          Five Stars|Love the game. Se...| 2015-08-31|\n","|         US|   48269642|R29DOU8791QZL8|B000A3IA0Y|     472622859|72 Pin Connector ...|     Video Games|          1|            0|          0|   N|                Y| Game will get stuck|Does not fit prop...| 2015-08-31|\n","|         US|   52738710|R15DUT1VIJ9RJZ|B0053BQN34|     577628462|uDraw Gametablet ...|     Video Games|          2|            0|          0|   N|                Y|We have tried it ...|This was way too ...| 2015-08-31|\n","|         US|   10556786|R3IMF2MQ3OU9ZM|B002I0HIMI|     988218515|NBA 2K12(Covers M...|     Video Games|          4|            0|          0|   N|                Y|          Four Stars|Works great good ...| 2015-08-31|\n","|         US|    2963837|R23H79DHOZTYAU|B0081EH12M|     770100932|New Trigger Grips...|     Video Games|          1|            1|          1|   N|                Y|Now i have to buy...|It did not fit th...| 2015-08-31|\n","|         US|   23092109| RIV24EQAIXA4O|B005FMLZQQ|      24647669|Xbox 360 Media Re...|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|perfect lightweig...| 2015-08-31|\n","|         US|   23091728|R3UCNGYDVN24YB|B002BSA388|      33706205|Super Mario Galaxy 2|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|               great| 2015-08-31|\n","|         US|   10712640| RUL4H4XTTN2DY|B00BUSLSAC|     829667834|Nintendo 3DS XL -...|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|Works beautifully...| 2015-08-31|\n","|         US|   17455376|R20JF7Z4DHTNX5|B00KWF38AW|     110680188|Captain Toad:  Tr...|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|Kids loved the ga...| 2015-08-31|\n","|         US|   14754850|R2T1AJ5MFI2260|B00BRQJYA8|     616463426|Lego Batman 2: DC...|     Video Games|          4|            0|          0|   N|                Y|          Four Stars|           Goodngame| 2015-08-31|\n","+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n","only showing top 20 rows\n","\n"]}]},{"cell_type":"markdown","metadata":{"id":"2yUSe55VY-9t"},"source":["### Create DataFrames to match tables"]},{"cell_type":"code","metadata":{"id":"C8REmY1aY-9u","colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"status":"ok","timestamp":1656247726031,"user_tz":240,"elapsed":433,"user":{"displayName":"URVISH DESAI","userId":"15385214001091362057"}},"outputId":"564bfdc8-b2b7-4dc0-c5cc-084df263b3be"},"source":["from pyspark.sql.functions import to_date\n","# Read in the Review dataset as a DataFrame\n","df.show()"],"execution_count":5,"outputs":[{"output_type":"stream","name":"stdout","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|   12039526| RTIS3L2M1F5SM|B001CXYMFS|     737716809|Thrustmaster T-Fl...|     Video Games|          5|            0|          0|   N|                Y|an amazing joysti...|Used this for Eli...| 2015-08-31|\n","|         US|    9636577| R1ZV7R40OLHKD|B00M920ND6|     569686175|Tonsee 6 buttons ...|     Video Games|          5|            0|          0|   N|                Y|Definitely a sile...|Loved it,  I didn...| 2015-08-31|\n","|         US|    2331478|R3BH071QLH8QMC|B0029CSOD2|      98937668|Hidden Mysteries:...|     Video Games|          1|            0|          1|   N|                Y|            One Star|poor quality work...| 2015-08-31|\n","|         US|   52495923|R127K9NTSXA2YH|B00GOOSV98|      23143350|GelTabz Performan...|     Video Games|          3|            0|          0|   N|                Y|good, but could b...|nice, but tend to...| 2015-08-31|\n","|         US|   14533949|R32ZWUXDJPW27Q|B00Y074JOM|     821342511|Zero Suit Samus a...|     Video Games|          4|            0|          0|   N|                Y|   Great but flawed.|Great amiibo, gre...| 2015-08-31|\n","|         US|    2377552|R3AQQ4YUKJWBA6|B002UBI6W6|     328764615|Psyclone Recharge...|     Video Games|          1|            0|          0|   N|                Y|            One Star|The remote consta...| 2015-08-31|\n","|         US|   17521011|R2F0POU5K6F73F|B008XHCLFO|      24234603|Protection for yo...|     Video Games|          5|            0|          0|   N|                Y|              A Must|I have a 2012-201...| 2015-08-31|\n","|         US|   19676307|R3VNR804HYSMR6|B00BRA9R6A|     682267517|   Nerf 3DS XL Armor|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|Perfect, kids lov...| 2015-08-31|\n","|         US|     224068| R3GZTM72WA2QH|B009EPWJLA|     435241890|One Piece: Pirate...|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|            Excelent| 2015-08-31|\n","|         US|   48467989| RNQOY62705W1K|B0000AV7GB|     256572651|Playstation 2 Dan...|     Video Games|          4|            0|          0|   N|                Y|          Four Stars|Slippery but expe...| 2015-08-31|\n","|         US|     106569|R1VTIA3JTYBY02|B00008KTNN|     384411423|Metal Arms: Glitc...|     Video Games|          5|            0|          0|   N|                N|          Five Stars|Love the game. Se...| 2015-08-31|\n","|         US|   48269642|R29DOU8791QZL8|B000A3IA0Y|     472622859|72 Pin Connector ...|     Video Games|          1|            0|          0|   N|                Y| Game will get stuck|Does not fit prop...| 2015-08-31|\n","|         US|   52738710|R15DUT1VIJ9RJZ|B0053BQN34|     577628462|uDraw Gametablet ...|     Video Games|          2|            0|          0|   N|                Y|We have tried it ...|This was way too ...| 2015-08-31|\n","|         US|   10556786|R3IMF2MQ3OU9ZM|B002I0HIMI|     988218515|NBA 2K12(Covers M...|     Video Games|          4|            0|          0|   N|                Y|          Four Stars|Works great good ...| 2015-08-31|\n","|         US|    2963837|R23H79DHOZTYAU|B0081EH12M|     770100932|New Trigger Grips...|     Video Games|          1|            1|          1|   N|                Y|Now i have to buy...|It did not fit th...| 2015-08-31|\n","|         US|   23092109| RIV24EQAIXA4O|B005FMLZQQ|      24647669|Xbox 360 Media Re...|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|perfect lightweig...| 2015-08-31|\n","|         US|   23091728|R3UCNGYDVN24YB|B002BSA388|      33706205|Super Mario Galaxy 2|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|               great| 2015-08-31|\n","|         US|   10712640| RUL4H4XTTN2DY|B00BUSLSAC|     829667834|Nintendo 3DS XL -...|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|Works beautifully...| 2015-08-31|\n","|         US|   17455376|R20JF7Z4DHTNX5|B00KWF38AW|     110680188|Captain Toad:  Tr...|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|Kids loved the ga...| 2015-08-31|\n","|         US|   14754850|R2T1AJ5MFI2260|B00BRQJYA8|     616463426|Lego Batman 2: DC...|     Video Games|          4|            0|          0|   N|                Y|          Four Stars|           Goodngame| 2015-08-31|\n","+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n","only showing top 20 rows\n","\n"]}]},{"cell_type":"code","metadata":{"id":"B0TESUDRY-90","colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"status":"ok","timestamp":1656247980010,"user_tz":240,"elapsed":16872,"user":{"displayName":"URVISH DESAI","userId":"15385214001091362057"}},"outputId":"1765bf6a-06aa-48a3-88c7-17147a7a850b"},"source":["# Create the customers_table DataFrame\n","# customers_df = df.groupby(\"\").agg({\"\"}).withColumnRenamed(\"\", \"customer_count\")\n","\n","customers_df = df.groupby(\"customer_id\").agg({\"customer_id\":\"count\"}).withColumnRenamed(\"count(customer_id)\", \"customer_count\")\n","customers_df.show()"],"execution_count":6,"outputs":[{"output_type":"stream","name":"stdout","text":["+-----------+--------------+\n","|customer_id|customer_count|\n","+-----------+--------------+\n","|   48670265|             1|\n","|   49103216|             2|\n","|    1131200|             1|\n","|   43076447|             2|\n","|   46261368|             1|\n","|    4883305|             5|\n","|   41192649|             1|\n","|   40985731|             7|\n","|   10437900|             2|\n","|   22245671|             1|\n","|    2574873|             1|\n","|    4696154|             1|\n","|    5621202|             1|\n","|    5871933|             2|\n","|   44089812|             1|\n","|    2845910|             1|\n","|    5274369|             1|\n","|   39069693|             2|\n","|     137793|             1|\n","|   31914942|             3|\n","+-----------+--------------+\n","only showing top 20 rows\n","\n"]}]},{"cell_type":"code","metadata":{"id":"4FwXA6UvY-96","colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"status":"ok","timestamp":1656248026909,"user_tz":240,"elapsed":14764,"user":{"displayName":"URVISH DESAI","userId":"15385214001091362057"}},"outputId":"e345e399-1cff-4ec9-9041-ea6e2f7086be"},"source":["# Create the products_table DataFrame and drop duplicates. \n","# products_df = df.select([]).drop_duplicates()\n","\n","products_df = df.select(['product_id','product_title']).drop_duplicates()\n","products_df.show()"],"execution_count":7,"outputs":[{"output_type":"stream","name":"stdout","text":["+----------+--------------------+\n","|product_id|       product_title|\n","+----------+--------------------+\n","|B00CJ7IUI6|The Elder Scrolls...|\n","|B00DHF39KS|Wolfenstein: The ...|\n","|B00MUTAVH6|Under Night In-Bi...|\n","|B001AZSEUW|              Peggle|\n","|B00KVOVBGM|PlayStation 4 Con...|\n","|B00O9VGH4Y|USPRO&reg; Headph...|\n","|B004OQNZY4|Phineas and Ferb:...|\n","|B00ZLN980O|Donop seablue 2.4...|\n","|B002L8W5V6|Dotop Nintendo Ga...|\n","|B007AJZ5PY|Nyko Game Case fo...|\n","|B000AOEU2K|Fire Emblem: Path...|\n","|B000H8BW7U|Tanarus (PC) (Com...|\n","|B013RADQOQ|Susenstone® 2400D...|\n","|B00KQXKUJ2|FIFA 15 (Ultimate...|\n","|B006W41X2C|Turtle Beach - Ea...|\n","|B000KCX9M4|Grand Theft Auto:...|\n","|B00YT90JWC|Red Wii Mini Cons...|\n","|B0096KG6A8|Wii U Super Mario...|\n","|B00L6AVLB0|World of Tanks-X3...|\n","|B000IMYKQ0|Wii Nunchuk Contr...|\n","+----------+--------------------+\n","only showing top 20 rows\n","\n"]}]},{"cell_type":"code","metadata":{"id":"MkqyCuNQY-9-","colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"status":"ok","timestamp":1656248057955,"user_tz":240,"elapsed":465,"user":{"displayName":"URVISH DESAI","userId":"15385214001091362057"}},"outputId":"ae36019c-ebc6-4c18-ab2d-93888c30c60f"},"source":["# Create the review_id_table DataFrame. \n","# Convert the 'review_date' column to a date datatype with to_date(\"review_date\", 'yyyy-MM-dd').alias(\"review_date\")\n","# review_id_df = df.select([, to_date(\"review_date\", 'yyyy-MM-dd').alias(\"review_date\")])\n","\n","\n","review_id_df = df.select(['review_id', 'customer_id' , 'product_id', 'product_parent', to_date(\"review_date\", 'yyyy-MM-dd').alias(\"review_date\")])\n","review_id_df.show()"],"execution_count":8,"outputs":[{"output_type":"stream","name":"stdout","text":["+--------------+-----------+----------+--------------+-----------+\n","|     review_id|customer_id|product_id|product_parent|review_date|\n","+--------------+-----------+----------+--------------+-----------+\n","| RTIS3L2M1F5SM|   12039526|B001CXYMFS|     737716809| 2015-08-31|\n","| R1ZV7R40OLHKD|    9636577|B00M920ND6|     569686175| 2015-08-31|\n","|R3BH071QLH8QMC|    2331478|B0029CSOD2|      98937668| 2015-08-31|\n","|R127K9NTSXA2YH|   52495923|B00GOOSV98|      23143350| 2015-08-31|\n","|R32ZWUXDJPW27Q|   14533949|B00Y074JOM|     821342511| 2015-08-31|\n","|R3AQQ4YUKJWBA6|    2377552|B002UBI6W6|     328764615| 2015-08-31|\n","|R2F0POU5K6F73F|   17521011|B008XHCLFO|      24234603| 2015-08-31|\n","|R3VNR804HYSMR6|   19676307|B00BRA9R6A|     682267517| 2015-08-31|\n","| R3GZTM72WA2QH|     224068|B009EPWJLA|     435241890| 2015-08-31|\n","| RNQOY62705W1K|   48467989|B0000AV7GB|     256572651| 2015-08-31|\n","|R1VTIA3JTYBY02|     106569|B00008KTNN|     384411423| 2015-08-31|\n","|R29DOU8791QZL8|   48269642|B000A3IA0Y|     472622859| 2015-08-31|\n","|R15DUT1VIJ9RJZ|   52738710|B0053BQN34|     577628462| 2015-08-31|\n","|R3IMF2MQ3OU9ZM|   10556786|B002I0HIMI|     988218515| 2015-08-31|\n","|R23H79DHOZTYAU|    2963837|B0081EH12M|     770100932| 2015-08-31|\n","| RIV24EQAIXA4O|   23092109|B005FMLZQQ|      24647669| 2015-08-31|\n","|R3UCNGYDVN24YB|   23091728|B002BSA388|      33706205| 2015-08-31|\n","| RUL4H4XTTN2DY|   10712640|B00BUSLSAC|     829667834| 2015-08-31|\n","|R20JF7Z4DHTNX5|   17455376|B00KWF38AW|     110680188| 2015-08-31|\n","|R2T1AJ5MFI2260|   14754850|B00BRQJYA8|     616463426| 2015-08-31|\n","+--------------+-----------+----------+--------------+-----------+\n","only showing top 20 rows\n","\n"]}]},{"cell_type":"code","metadata":{"id":"lzMmkdKmY--D","colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"status":"ok","timestamp":1656248084053,"user_tz":240,"elapsed":595,"user":{"displayName":"URVISH DESAI","userId":"15385214001091362057"}},"outputId":"effaecc6-c19a-40e9-856b-b01a7acc832b"},"source":["# Create the vine_table. DataFrame\n","# vine_df = df.select([])\n","\n","vine_df = df.select(['review_id', 'star_rating', 'helpful_votes', 'total_votes', 'vine', 'verified_purchase'])\n","vine_df.show()"],"execution_count":9,"outputs":[{"output_type":"stream","name":"stdout","text":["+--------------+-----------+-------------+-----------+----+-----------------+\n","|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n","+--------------+-----------+-------------+-----------+----+-----------------+\n","| RTIS3L2M1F5SM|          5|            0|          0|   N|                Y|\n","| R1ZV7R40OLHKD|          5|            0|          0|   N|                Y|\n","|R3BH071QLH8QMC|          1|            0|          1|   N|                Y|\n","|R127K9NTSXA2YH|          3|            0|          0|   N|                Y|\n","|R32ZWUXDJPW27Q|          4|            0|          0|   N|                Y|\n","|R3AQQ4YUKJWBA6|          1|            0|          0|   N|                Y|\n","|R2F0POU5K6F73F|          5|            0|          0|   N|                Y|\n","|R3VNR804HYSMR6|          5|            0|          0|   N|                Y|\n","| R3GZTM72WA2QH|          5|            0|          0|   N|                Y|\n","| RNQOY62705W1K|          4|            0|          0|   N|                Y|\n","|R1VTIA3JTYBY02|          5|            0|          0|   N|                N|\n","|R29DOU8791QZL8|          1|            0|          0|   N|                Y|\n","|R15DUT1VIJ9RJZ|          2|            0|          0|   N|                Y|\n","|R3IMF2MQ3OU9ZM|          4|            0|          0|   N|                Y|\n","|R23H79DHOZTYAU|          1|            1|          1|   N|                Y|\n","| RIV24EQAIXA4O|          5|            0|          0|   N|                Y|\n","|R3UCNGYDVN24YB|          5|            0|          0|   N|                Y|\n","| RUL4H4XTTN2DY|          5|            0|          0|   N|                Y|\n","|R20JF7Z4DHTNX5|          5|            0|          0|   N|                Y|\n","|R2T1AJ5MFI2260|          4|            0|          0|   N|                Y|\n","+--------------+-----------+-------------+-----------+----+-----------------+\n","only showing top 20 rows\n","\n"]}]},{"cell_type":"markdown","metadata":{"id":"jITZhLkmY--J"},"source":["### Connect to the AWS RDS instance and write each DataFrame to its table. "]},{"cell_type":"code","metadata":{"id":"7jiUvs1aY--L","executionInfo":{"status":"ok","timestamp":1656248472292,"user_tz":240,"elapsed":149,"user":{"displayName":"URVISH DESAI","userId":"15385214001091362057"}}},"source":["# Configure settings for RDS\n","mode = \"append\"\n","jdbc_url=\"jdbc:postgresql://amazon-analysis.cvvccrp8oiue.us-east-2.rds.amazonaws.com:5432/amazonanalysis\"\n","config = {\"user\":\"postgres\", \n","          \"password\": \"Urvish1234\", \n","          \"driver\":\"org.postgresql.Driver\"}"],"execution_count":14,"outputs":[]},{"cell_type":"code","metadata":{"id":"T2zgZ-aKY--Q","executionInfo":{"status":"ok","timestamp":1656248618169,"user_tz":240,"elapsed":144311,"user":{"displayName":"URVISH DESAI","userId":"15385214001091362057"}}},"source":["# Write review_id_df to table in RDS\n","review_id_df.write.jdbc(url=jdbc_url, table='review_id_table', mode=mode, properties=config)"],"execution_count":15,"outputs":[]},{"cell_type":"code","metadata":{"id":"1m3yzn-LY--U","executionInfo":{"status":"ok","timestamp":1656248863460,"user_tz":240,"elapsed":39006,"user":{"displayName":"URVISH DESAI","userId":"15385214001091362057"}}},"source":["# Write products_df to table in RDS\n","# about 3 min\n","products_df.write.jdbc(url=jdbc_url, table='products_table', mode=mode, properties=config)"],"execution_count":16,"outputs":[]},{"cell_type":"code","metadata":{"id":"KbXri15fY--Z","executionInfo":{"status":"ok","timestamp":1656248954350,"user_tz":240,"elapsed":72526,"user":{"displayName":"URVISH DESAI","userId":"15385214001091362057"}}},"source":["# Write customers_df to table in RDS\n","# 5 min 14 s\n","customers_df.write.jdbc(url=jdbc_url, table='customers_table', mode=mode, properties=config)"],"execution_count":17,"outputs":[]},{"cell_type":"code","metadata":{"id":"XdQknSHLY--e","executionInfo":{"status":"ok","timestamp":1656249134887,"user_tz":240,"elapsed":143206,"user":{"displayName":"URVISH DESAI","userId":"15385214001091362057"}}},"source":["# Write vine_df to table in RDS\n","# 11 minutes\n","vine_df.write.jdbc(url=jdbc_url, table='vine_table', mode=mode, properties=config)"],"execution_count":18,"outputs":[]},{"cell_type":"code","metadata":{"id":"Exuo6ebUsCqW"},"source":[""],"execution_count":null,"outputs":[]}]}