<jupyter_start><jupyter_text><jupyter_code>import os
# Find the latest version of spark 3.0  from http://www.apache.org/dist/spark/ and enter as the spark version
spark_version = 'spark-3.0.3'
os.environ['SPARK_VERSION']=spark_version

# Install Spark and Java
!apt-get update
!apt-get install openjdk-8-jdk-headless -qq > /dev/null
!wget -q http://www.apache.org/dist/spark/$SPARK_VERSION/$SPARK_VERSION-bin-hadoop2.7.tgz
!tar xf $SPARK_VERSION-bin-hadoop2.7.tgz
!pip install -q findspark

# Set Environment Variables
os.environ["JAVA_HOME"] = "/usr/lib/jvm/java-8-openjdk-amd64"
os.environ["SPARK_HOME"] = f"/content/{spark_version}-bin-hadoop2.7"

# Start a SparkSession
import findspark
findspark.init()

!wget https://jdbc.postgresql.org/download/postgresql-42.2.9.jar
from pyspark import SparkFiles
from google.colab import data_table
from pyspark.sql import SparkSession
from pyspark.sql.functions import *
from pyspark.sql.types import IntegerType

from pyspark.sql import SparkSession
spark = SparkSession.builder.appName("BigData-HW-1").config("spark.driver.extraClassPath","/content/postgresql-42.2.9.jar").getOrCreate()
from pyspark import SparkFiles
url ="https://s3.amazonaws.com/amazon-reviews-pds/tsv/amazon_reviews_us_Video_Games_v1_00.tsv.gz"
spark.sparkContext.addFile(url)
videogames_df = spark.read.csv(SparkFiles.get("amazon_reviews_us_Video_Games_v1_00.tsv.gz"), sep = "\t", header = True, inferSchema=True)

# Show DataFrame
videogames_df.show()
video_reviews_df= videogames_df.select(["customer_id","product_id","review_date","review_body"])
video_reviews_df.show()
video_reviews_df= videogames_df.groupBy("customer_id").agg({"customer_id": "count"}).withColumnRenamed("count(customer_id)", "customer_count")
video_reviews_df.show()
video_reviews_df.printSchema()
##RDS
#Config settings for RDS

## IMPORTANT : PLEASE ADD YOUR OWN CREDENTIALS FOR RDS ##

mode = "append"
jdbc_url="jdbc:postgresql://homework.cqekbhx09q1f.us-east-1.rds.amazonaws.com"
config = {"user":"username", 
          "password": "Password", 
          "driver":"org.postgresql.Driver"}
# Write DataFrame to review_id table in RDS

video_reviews_df.write.jdbc(url=jdbc_url, table="customer_id", mode=mode, properties= config)
video_reviews_df.write.jdbc(url=jdbc_url, table="review_id", mode=mode, properties= config)
video_reviews_df.write.jdbc(url=jdbc_url, table="music_review_table", mode=mode, properties= config)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13701431492842536, 'fraction_numerical': 0.014314928425357873, 'mean_word_length': 6.412121212121212, 'pattern_counts': {'":': 4, '<': 5, '<?xml version=': 0, '>': 6, 'https://': 2, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '349571', 'license': 'ADSL', 'n_tokens_mistral': 923, 'n_tokens_neox': 880, 'n_words': 150, 'path': 'Video_Games.ipynb', 'repo_name': 'typleasant22/big-data-challenge', 'size': '2445'}