%md
# [SDS-2.x, Scalable Data Engineering Science](https://lamastex.github.io/scalable-data-science/sds/2/x/)

%md
#Introduction to Spark SQL

* This notebook explains the motivation behind Spark SQL
* It introduces interactive SparkSQL queries and visualizations
* This notebook uses content from Databricks SparkSQL notebook and [SparkSQL programming guide](http://spark.apache.org/docs/latest/sql-programming-guide.html)

%md
### Some resources on SQL

* [https://en.wikipedia.org/wiki/SQL](https://en.wikipedia.org/wiki/SQL)
* [https://en.wikipedia.org/wiki/Apache_Hive](https://en.wikipedia.org/wiki/Apache_Hive)
* [http://www.infoq.com/articles/apache-spark-sql](http://www.infoq.com/articles/apache-spark-sql)
* [https://databricks.com/blog/2015/02/17/introducing-dataframes-in-spark-for-large-scale-data-science.html](https://databricks.com/blog/2015/02/17/introducing-dataframes-in-spark-for-large-scale-data-science.html)
* [https://databricks.com/blog/2016/07/14/a-tale-of-three-apache-spark-apis-rdds-dataframes-and-datasets.html](https://databricks.com/blog/2016/07/14/a-tale-of-three-apache-spark-apis-rdds-dataframes-and-datasets.html)
* **READ**: [https://people.csail.mit.edu/matei/papers/2015/sigmod_spark_sql.pdf](https://people.csail.mit.edu/matei/papers/2015/sigmod_spark_sql.pdf)

Some of them are embedded below in-place for your convenience.

//This allows easy embedding of publicly available information into any other notebook
//when viewing in git-book just ignore this block - you may have to manually chase the URL in frameIt("URL").
//Example usage:
// displayHTML(frameIt("https://en.wikipedia.org/wiki/Latent_Dirichlet_allocation#Topics_in_LDA",250))
def frameIt( u:String, h:Int ) : String = {
      """<iframe 
 src=""""+ u+""""
 width="95%" height="""" + h + """"
 sandbox>
  <p>
    <a href="http://spark.apache.org/docs/latest/index.html">
      Fallback link for browsers that, unlikely, don't support frames
    </a>
  </p>
</iframe>"""
   }
displayHTML(frameIt("https://en.wikipedia.org/wiki/SQL",500))

displayHTML(frameIt("https://en.wikipedia.org/wiki/Apache_Hive#HiveQL",175))

displayHTML(frameIt("https://databricks.com/blog/2015/02/17/introducing-dataframes-in-spark-for-large-scale-data-science.html",600))

displayHTML(frameIt("https://databricks.com/blog/2016/07/14/a-tale-of-three-apache-spark-apis-rdds-dataframes-and-datasets.html",600))

%md
This is an elaboration of the [Apache Spark 2.2 sql-progamming-guide](http://spark.apache.org/docs/latest/sql-programming-guide.html).

# Overview

Spark SQL is a Spark module for structured data processing. Unlike the basic Spark RDD API, the interfaces provided by Spark SQL provide Spark with more information about the structure of both the data and the computation being performed. Internally, Spark SQL uses this extra information to perform extra optimizations. There are several ways to interact with Spark SQL including SQL and the Dataset API. When computing a result the same execution engine is used, independent of which API/language you are using to express the computation. This unification means that developers can easily switch back and forth between different APIs based on which provides the most natural way to express a given transformation.

All of the examples on this page use sample data included in the Spark distribution and can be run in the spark-shell, pyspark shell, or sparkR shell.

# Datasets and DataFrames
A Dataset is a distributed collection of data. Dataset is a new interface added in Spark 1.6 that provides the benefits of RDDs (strong typing, ability to use powerful lambda functions) with the benefits of Spark SQL’s optimized execution engine. A Dataset can be [constructed](http://spark.apache.org/docs/latest/sql-programming-guide.html#creating-datasets) from JVM objects and then manipulated using functional transformations (map, flatMap, filter, etc.). The Dataset API is available in [Scala](http://spark.apache.org/docs/latest/api/scala/index.html#org.apache.spark.sql.Dataset) and [Java](http://spark.apache.org/docs/latest/api/java/index.html?org/apache/spark/sql/Dataset.html). Python does not have the support for the Dataset API. But due to Python’s dynamic nature, many of the benefits of the Dataset API are already available (i.e. you can access the field of a row by name naturally `row.columnName`). The case for R is similar.

A DataFrame is a Dataset organized into named columns. It is conceptually equivalent to a table in a relational database or a data frame in R/Python, but with richer optimizations under the hood. DataFrames can be constructed from a wide array of [sources](http://spark.apache.org/docs/latest/sql-programming-guide.html#data-sources) such as: structured data files, tables in Hive, external databases, or existing RDDs. The DataFrame API is available in Scala, Java, [Python](http://spark.apache.org/docs/latest/api/python/pyspark.sql.html#pyspark.sql.DataFrame), and [R](http://spark.apache.org/docs/latest/api/R/index.html). In Scala and Java, a DataFrame is represented by a Dataset of Rows. In the [Scala API](http://spark.apache.org/docs/latest/api/scala/index.html#org.apache.spark.sql.Dataset), DataFrame is simply a type alias of Dataset[Row]. While, in Java API, users need to use `Dataset<Row>` to represent a DataFrame.

Throughout this document, we will often refer to Scala/Java Datasets of `Rows` as DataFrames.

%md
# Getting Started in Spark 2.x

## Starting Point: SparkSession

The entry point into all functionality in Spark is the [SparkSession](http://spark.apache.org/docs/latest/api/scala/index.html#org.apache.spark.sql.SparkSession). To create a basic SparkSession in your scala Spark code, just use `SparkSession.builder()`:

```
import org.apache.spark.sql.SparkSession

val spark = SparkSession
  .builder()
  .appName("Spark SQL basic example")
  .config("spark.some.config.option", "some-value")
  .getOrCreate()

// For implicit conversions like converting RDDs to DataFrames
import spark.implicits._
```

Conveniently, in Databricks notebook (similar to `spark-shell`) `SparkSession` is already created for you and is available as `spark`.

%md
## Creating DataFrames

With a [`SparkSession`](http://spark.apache.org/docs/latest/api/scala/index.html#org.apache.spark.sql.SparkSession) or [`SQLContext`](http://spark.apache.org/docs/latest/api/scala/index.html#org.apache.spark.sql.SQLContext), applications can create [`DataFrame`](https://spark.apache.org/docs/latest/api/scala/index.html#org.apache.spark.sql.DataFrame) 

* from an existing `RDD`, 
* from a Hive table, or 
* from various other data sources.

#### Just to recap: 

* A DataFrame is a distributed collection of data organized into named columns (it is not strogly typed). 
* You can think of it as being organized into table RDD of case class `Row` (which is not exactly true). 
* DataFrames, in comparison to RDDs, are backed by rich optimizations, including:
  * tracking their own schema, 
  * adaptive query execution, 
  * code generation including whole stage codegen, 
  * extensible Catalyst optimizer, and 
  * project [Tungsten](https://databricks.com/blog/2015/04/28/project-tungsten-bringing-spark-closer-to-bare-metal.html) for optimized storage. 

> Note that performance for DataFrames is the same across languages Scala, Java, Python, and R. This is due to the fact that the only planning phase is language-specific (logical + physical SQL plan), not the actual execution of the SQL plan.

![DF speed across languages](https://databricks.com/wp-content/uploads/2015/02/Screen-Shot-2015-02-16-at-9.46.39-AM-1024x457.png)

%md
## DataFrame Basics

#### 1. An empty DataFrame
#### 2. DataFrame from a range
#### 3. DataFrame from an RDD
#### 4. DataFrame Operations (aka Untyped Dataset Operations)
#### 5. Running SQL Queries Programmatically
#### 6. Creating Datasets

%md
### 1. Making an empty DataFrame

Spark has some of the pre-built methods to create simple DataFrames

* let us make an Empty DataFrame

%md
Not really interesting, or is it?

**You Try!**

Uncomment the following cell, put your cursor after `emptyDF.` below and hit Tab to see what can be done with `emptyDF`.

%md
### 2. Making a DataFrame from a range

Let us make a DataFrame next

* from a range of numbers, as follows:

%md
Note that Spark automatically names column as `id` and casts integers to type `bigint` for big integer or Long.

In order to get a preview of data in DataFrame use `show()` as follows:

%md
### 3. Making a DataFrame from an RDD

* Make an RDD
* Conver the RDD into a DataFrame using the defualt `.toDF()` method
* Conver the RDD into a DataFrame using the non-default `.toDF(...)` method
* Do it all in one line

%md
Let's first make an RDD using the `sc.parallelize` method, transform it by a `map` and perform the `collect` action to display it, as follows:

%md
Next, let us convert the RDD into DataFrame using the `.toDF()` method, as follows:

%md
As it is clear, the DataFrame has columns named `_1` and `_2`, each of type `int`.  Let us see its content using the `.show()` method next.

%md
Note that by default, i.e. without specifying any options as in `toDF()`, the column names are given by `_1` and `_2`.

We can easily specify column names as follows:

%md
Of course, we can do all of the above steps to make the DataFrame `df1` in one line and then show it, as follows:

%md
### 4. DataFrame Operations (aka Untyped Dataset Operations)

DataFrames provide a domain-specific language for structured data manipulation in [Scala](http://spark.apache.org/docs/latest/api/scala/index.html#org.apache.spark.sql.Dataset), [Java](http://spark.apache.org/docs/latest/api/java/index.html?org/apache/spark/sql/Dataset.html), [Python](http://spark.apache.org/docs/latest/api/python/pyspark.sql.html#pyspark.sql.DataFrame) and [R](http://spark.apache.org/docs/latest/api/R/SparkDataFrame.html).

As mentioned above, in Spark 2.0, DataFrames are just Dataset of Rows in Scala and Java API. These operations are also referred as “untyped transformations” in contrast to “typed transformations” come with strongly typed Scala/Java Datasets.

Here we include some basic examples of structured data processing using Datasets:

%md
Let's make a more interesting DataFrame for `groupBy` with repeated elements so that the `count` will be more than `1`.

%md

For a complete list of the types of operations that can be performed on a Dataset refer to the [API Documentation](http://spark.apache.org/docs/latest/api/scala/index.html#org.apache.spark.sql.Dataset).

In addition to simple column references and expressions, Datasets also have a rich library of functions including string manipulation, date arithmetic, common math operations and more. The complete list is available in the [DataFrame Function Reference](http://spark.apache.org/docs/latest/api/scala/index.html#org.apache.spark.sql.functions$).

%md
**You Try!**

Uncomment the two lines in the next cell, and then fill in the `???` below to get a DataFrame `df2` whose first two columns are the same as `df1` and whose third column named triple has values that are three times the values in the first column.

%md
### 5. Running SQL Queries Programmatically

The `sql` function on a `SparkSession` enables applications to run SQL queries programmatically and returns the result as a `DataFrame`.

%md
### 5. Using SQL for interactively querying a table is very powerful!

Note `-- comments` are how you add `comments` in SQL cells beginning with `%sql`.

* You can run SQL `select *` statement to see all columns of the table, as follows:
  * This is equivalent to the above `display(diamondsDF)' with the DataFrame

%md
#### Global Temporary View
Temporary views in Spark SQL are session-scoped and will disappear if the session that creates it terminates. If you want to have a temporary view that is shared among all sessions and keep alive until the Spark application terminates, you can create a global temporary view. Global temporary view is tied to a system preserved database `global_temp`, and we must use the qualified name to refer it, e.g. `SELECT * FROM global_temp.view1`. See [http://spark.apache.org/docs/latest/sql-programming-guide.html#global-temporary-view](http://spark.apache.org/docs/latest/sql-programming-guide.html#global-temporary-view) for details.

%md
## 6. Creating Datasets
Datasets are similar to RDDs, however, instead of using Java serialization or Kryo they use a specialized [Encoder](http://spark.apache.org/docs/latest/api/scala/index.html#org.apache.spark.sql.Encoder) to serialize the objects for processing or transmitting over the network. While both encoders and standard serialization are responsible for turning an object into bytes, encoders are code generated dynamically and use a format that allows Spark to perform many operations like filtering, sorting and hashing without deserializing the bytes back into an object.

%md
We can have more complicated objects in a `DataSet` too.

%md
***
***

## Next we will play with data

The data here is **semi-structured tabular data** (Tab-delimited text file in dbfs).
Let us see what Anthony Joseph in BerkeleyX/CS100.1x had to say about such data.

### Key Data Management Concepts: Semi-Structured Tabular Data

**(watch now 1:26)**:

[![Semi-Structured Tabular Data by Anthony Joseph in BerkeleyX/CS100.1x](http://img.youtube.com/vi/G_67yUxdDbU/0.jpg)](https://www.youtube.com/watch?v=G_67yUxdDbU?rel=0&autoplay=1&modestbranding=1&start=1)

***

%md
## Go through the databricks Introductions Now

* [https://docs.databricks.com/spark/latest/dataframes-datasets/introduction-to-dataframes-scala.html](https://docs.databricks.com/spark/latest/dataframes-datasets/introduction-to-dataframes-scala.html)

* [https://docs.databricks.com/spark/latest/dataframes-datasets/introduction-to-datasets.html](https://docs.databricks.com/spark/latest/dataframes-datasets/introduction-to-datasets.html)