<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 SparkSQL Lab
Step1:  HiveContext, a superset of SQLContext, was recommended for most use cases. Please make sure you are using HiveContext now!
Part 2
Step2:  Show time
Step3:  (2b) Read from Hive 
Don't forget the configuration of Hive should be done by placing your hive-site.xml file in conf/.
Step4:  In this class, you will use pixnet_user_log_1000 for further works
Print the schema! What do we have?
Step5:  How many rows in pixnet_user_log
Step6:  Part 3
Step7:  Part 4
Step8:  Part 5
Step9:  Don't forget to stop sc
<ASSISTANT_TASK:>
Python Code:
from pyspark.sql import SQLContext, Row
sqlContext = SQLContext(sc)
sqlContext
from pyspark.sql import HiveContext, Row
sqlContext= HiveContext(sc)
sqlContext
Explanation: SparkSQL Lab: 
From this lab, you would write code to execute SQL query in Spark. Makes your analytic life simpler and faster.
 During this lab we will cover: 
Part 1: Linking with SparkSQL
Part 2: Loading data programmatically
Part 3: User-Defined Functions
Part 4: Caching for performance
Part 5: Your show time - How many authors tagged as spam?
Reference for Spark RDD Spark's Python API
Part 1: Linking with SparkSQL
End of explanation
jsonfile = "file:///opt/spark-1.4.1-bin-hadoop2.6/examples/src/main/resources/people.json"
df = sqlContext.read.load(jsonfile, format="json")
Explanation: HiveContext, a superset of SQLContext, was recommended for most use cases. Please make sure you are using HiveContext now!
Part 2: Loading data programmatically
 (2a) Read local JSON file to DataFrame 
Now, try to read json file from Spark Example. Thank for the hashed spam data from PIXNET PIXNET HACKATHON 2015
End of explanation
# TODO: Replace <FILL IN> with appropriate code
df.<FILL IN>
#print df's schema
df.printSchema()
Explanation: Show time: Query top 2 row
End of explanation
sqlContext.sql("SHOW TABLES").show()
Explanation: (2b) Read from Hive 
Don't forget the configuration of Hive should be done by placing your hive-site.xml file in conf/.
End of explanation
sqlContext.sql("SELECT * FROM pixnet_user_log_1000").printSchema()
Explanation: In this class, you will use pixnet_user_log_1000 for further works
Print the schema! What do we have?
End of explanation
from datetime import datetime
start_time = datetime.now()
df2 = sqlContext.sql("SELECT * FROM pixnet_user_log_1000")
end_time = datetime.now()
print df2.count()
print('Duration: {}'.format(end_time - start_time))
df2.select('time').show(2)
Explanation: How many rows in pixnet_user_log
End of explanation
#registers this RDD as a temporary table using the given name.
df2.registerTempTable("people")
# Create an UDF for how long some text is
# example from user guide, length function
sqlContext.registerFunction("strLenPython", lambda x: len(x)) 
# split function for parser
sqlContext.registerFunction("strDate", lambda x: x.split("T")[0])
# put udf with expected columns
results = sqlContext.sql("SELECT author, \
                                 strDate(time) AS dt, \
                                 strLenPython(action) AS lenAct \
                            FROM people")
# print top 5 results
results.show(5)
Explanation: Part 3: User-Defined Functions
In part 3, you will create your first UDF in Spark SQL with elegant lambda
End of explanation
sqlContext.cacheTable("people")
start_time = datetime.now()
sqlContext.sql("SELECT * FROM people").count()
end_time = datetime.now()
print('Duration: {}'.format(end_time - start_time))
sqlContext.sql("SELECT   strDate(time) AS dt,\
                         count(distinct author) AS cnt \
                FROM     people \
                GROUP BY strDate(time)").show(5)
sqlContext.uncacheTable("people")
Explanation: Part 4: Caching for performance
 Saving to persistent tables
saveAsTable : Saves the contents of this DataFrame to a data source as a table.
End of explanation
# TODO: Replace <FILL IN> with appropriate code
result = <FILL IN>
Explanation: Part 5: Your show time - How many authors are tagged as spam from pixnet_user_log?
Here are two hive tables you will need:
(1) author with action pixnet_user_spam
(2) author with spam tag pixnet_user_log_1000
End of explanation
sc.stop()
Explanation: Don't forget to stop sc
End of explanation
<END_TASK>