#Create the SQLContext


"""
Explanation: Lab 2 - Spark SQL
This Lab will show you how to work with Spark SQL
Step 1
<h3>Getting started: Create a SQL Context</h3>

<b>Type:</b>
from pyspark.sql import SQLContext<br>
sqlContext = SQLContext(sc)
End of explanation
"""


#enter the commands to remove and download file here


"""
Explanation: Step 2
<h3>Dowload a JSON Recordset to work with</h3>

Let's download the data, we can run commands on the console of the server (or docker image) that the notebook enviroment is using. To do so we simply put a "!" in front of the command that we want to run. For example:
!pwd
To get the data we will download a file to the enviroment. Simple run these two commands, the first just ensures that the file is removed if it exists:
!rm world_bank.json.gz -f <br>
!wget https://raw.githubusercontent.com/bradenrc/sparksql_pot/master/world_bank.json.gz
End of explanation
"""


#create the Dataframe here:


"""
Explanation: Step 3
<h3>Create a Dataframe</h3>

Now you can create the Dataframe, note that if you wanted to see where you downloaded the file you can run !pwd or !ls
To create the Dataframe type:
example1_df = sqlContext.read.json("world_bank.json.gz")
End of explanation
"""


#print out the schema


"""
Explanation: <h3>We can look at the schema with this command:</h3>

example1_df.printSchema()
End of explanation
"""


#Use take on the dataframe to pull out 2 rows

"""
Explanation: <h3>Dataframes work like RDDs, you can map, reduce, groupby, etc. 
<br>Take a look at the first two rows of data using "take"</h3>
End of explanation
"""


#Create the table to be referenced via SQL


"""
Explanation: Step 4
<h3>Register a table</h3>

Using
DataframeObject.registerTempTable("name_of_table")
Create a table named "world_bank"
End of explanation
"""


#Use SQL to select from table limit 2 and print the output


#Extra credit, take the Dataframe you created with the two records and convert it into Pandas


#Now Calculate a Simple count based on a group, for example "regionname"


# With JSON data you can reference the nested data
# If you look at Schema above you can see that Sector.Name is a nested column
# Select that column and limit to reasonable output (like 2)


"""
Explanation: Step 5
<h3>Writing SQL Statements</h3>
Using SQL Get the first 2 records
sqlContext.sql("SQL Statement") will return a Dataframe with the records
End of explanation
"""


# we need to tell the charting library (matplotlib) to display charts inline
# just run this paragraph
%matplotlib inline 
import matplotlib.pyplot as plt, numpy as np

# first write the sql statment and look at the data, remember to add .toPandas() to have it look nice
# an even easier option is to create a variable and set it to the SQL statement
# for example: 
# query = "select count(*) as Count, countryname from world_bank group by countryname"
# chart1_df = sqlContext.sql(query).toPandas()
# print chart1_df

# now take the variable (or same sql statement) and use the method:
# .plot(kind='bar', x='countryname', y='Count', figsize=(12, 5))


"""
Explanation: Step 6
<h3>Creating simple graphs</h3>
Using Pandas we can do create some simple visualizations.
First create a SQL statement that is a resonable number if items
For example, you can count the number of projects (rows) by countryname
<br>or in anothe words: 
<br>count(*), countryname from table group by countryname
End of explanation
"""


# Default array defined below. Feel free to change as desired.
array=[[1,1,1],[2,2,2],[3,3,3],[4,4,4],[5,5,5]]
my_rdd = sc.parallelize(array)
my_rdd.collect()

"""
Explanation: Step 7
<h3>Creating a dataframe "manually" by adding a schema to an RDD</h3>

First, we need to create an RDD of pairs or triplets. This can be done using code (for loop) as
seen in the instructor's example, or more simply by assigning values to an array.
End of explanation
"""


from pyspark.sql.types import *

# The schema is encoded in a string. Complete the string below
schemaString = ""

# MissingType() should be either StringType() or IntegerType(). Please replace as required.
fields = [StructField(field_name, MissingType(), True) for field_name in schemaString.split()]
schema = StructType(fields)

# Apply the schema to the RDD.
schemaExample = sqlContext.createDataFrame(use_your_rdd_name_here, schema)

# Register the DataFrame as a table. Add table name below as parameter to registerTempTable.
schemaExample.registerTempTable("")


# Run some select statements on your newly created DataFrame and display the output

"""
Explanation: Use first the StructField method, following these steps:<br>
1- Define your schema columns as a string<br>
2- Build the schema object using StructField<br>
3- Apply the schema object to the RDD<br>
Note: The cell below is missing some code and will not run properly until the missing code has
been completed.
End of explanation
"""


