Title: 544
Tags: lec23

Q: If df is a Spark DataFrame, what are two ways you can make it appear as a view in Hive (for the purpose of upcoming SQL queries)?
A: df.createTempView("VIEW_NAME")
df.createOrReplaceTempView("VIEW_NAME")
The former raises an exception if the view already exists.

Q: If you have a query, say SELECT * FROM data, how can you run it with Spark SQL?  Assume your Spark session is in a variable "spark".
A: spark.sql("SELECT * FROM data")

Q: What does spark.sql("QUERY") return?
A: a Spark DataFrame

Q: How do you write a Spark DataFrame (say df) as a Hive table?
A: df.write.saveAsTable("TABLE_NAME")

Q: What does spark.sql("SHOW TABLES") do?
A: Gives you a DataFrame listing all the tables AND all the views.  You'll need to do .show(), .toPandas(), or similar to actually see it.

Q: If tv is a table OR view in Hive, how can you get the corresponding Spark DataFrame?
A: spark.table("tv")

Q: What kind of Spark operation is .count()?
A: It depends.
In df.count() where df is a DataFrame, then it is an ACTION, because it actually triggers the work to get a number back.
In groups.count() where groups is a GroupedData, then it is a TRANSFORMATION, because the counts per group might only be an intermediate result that will be further processed.

Q: How are grouping and windowing similar and different?
A: In each case, we pull together related data into groups/partitions.
In grouping, there is one output row per group (e.g., an AVG stat).
In windowing, all the rows in the partitions can be part of the output, but they can have stats relative to the rest of the partition (e.g., a row_number() relative to the row's position with the partition).

Q: Say you want to filter the results of a Spark SQL query based on a value computed with a windowing function.  Why is this tricky, and what are some solutions?
A: We can't use WHERE -- that runs prior to the windowing function.  We can't use HAVING -- that is only for GROUP BY.
Options:
1. nested SQL query (the outer query can have a WHERE clause to filter)
2. add a where filter using the DataFrame API after the SQL query (this is an excellent case for mixing and matching Spark SQL with Spark DataFrames).
Example of option 2:
spark.sql("""
SELECT area, Call_Number, row_number() OVER (PARTITION BY area ORDER BY Call_Number ASC) AS num
FROM calls
""").where("num <= 3").toPandas()

Q: JOINs typically have ON clauses.  Say your ON clause is something like this:
ON tbl1.colA = tbl2.colB
What is the technical name for this kind of JOIN?
A: "equi join"

Q: If you only want to get the unique values in column C of table T, what SQL can you write?
A: SELECT DISTINCT C FROM T;