ScaDaMaLe Course site and book

This is an elaboration of the http://spark.apache.org/docs/latest/sql-programming-guide.html by Ivan Sadikov and Raazesh Sainudiin.

Getting Started

Spark Sql Programming Guide

• Starting Point: SparkSession
• Creating DataFrames
• Untyped Dataset Operations (aka DataFrame Operations)
• Running SQL Queries Programmatically
• Global Temporary View
• Creating Datasets
• Interoperating with RDDs
  • Inferring the Schema Using Reflection
  • Programmatically Specifying the Schema
• Scalar Functions
• Aggregate Functions

Getting Started

Starting Point: SparkSession

The entry point into all functionality in Spark is the SparkSession class and/or SQLContext/HiveContext. SparkSession is created for you as spark when you start spark-shell on command-line REPL or through a notebook server (databricks, zeppelin, jupyter, etc.). You will need to create SparkSession usually when building an application for submission to a Spark cluster. To create a basic SparkSession, 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._

Find full example code in the Spark repo at:

• https://github.com/apache/spark/blob/master/examples/src/main/scala/org/apache/spark/examples/sql/SparkSQLExample.scala

SparkSession in Spark 2.0 provides builtin support for Hive features including the ability to write queries using HiveQL, access to Hive UDFs, and the ability to read data from Hive tables. To use these features, you do not need to have an existing Hive setup.

// You could get SparkContext and SQLContext from SparkSession
val sc = spark.sparkContext
val sqlContext = spark.sqlContext

But in Databricks notebook (similar to spark-shell) SparkSession is already created for you and is available as spark (similarly, sc and sqlContext are also available).

// Evaluation of the cell by Ctrl+Enter will print spark session available in notebook
spark

After evaluation you should see something like this, i.e., a reference to the SparkSession you just created:

res0: org.apache.spark.sql.SparkSession = org.apache.spark.sql.SparkSession@5a289bf5

Creating DataFrames

With a SparkSessions, applications can create Dataset or DataFrame from an existing RDD, from a Hive table, or from various datasources.

Just to recap, a DataFrame is a distributed collection of data organized into named columns. You can think of it as an 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.

Dataset provides type-safety when working with SQL, since Row is mapped to a case class, so that each column can be referenced by property of that class.

Note that performance for Dataset/DataFrames is the same across languages Scala, Java, Python, and R. This is due to the fact that the planning phase is just language-specific, only logical plan is constructed in Python, and all the physical execution is compiled and executed as JVM bytecode.

As an example, the following creates a DataFrame based on the content of a JSON file:

val df = spark.read.json("examples/src/main/resources/people.json")

// Displays the content of the DataFrame to stdout
df.show()
// +----+-------+
// | age|   name|
// +----+-------+
// |null|Michael|
// |  30|   Andy|
// |  19| Justin|
// +----+-------+

Find full example code at - https://raw.githubusercontent.com/apache/spark/master/examples/src/main/scala/org/apache/spark/examples/sql/SparkSQLExample.scala in the Spark repo.

To be able to try this example in databricks we need to load the people.json file into dbfs. Let us do this programmatically next.

// the following lines merely fetch the file from the URL and load it into the dbfs for us to try in databricks
// getLines from the file at the URL
val peopleJsonLinesFromURL = scala.io.Source.fromURL("https://raw.githubusercontent.com/apache/spark/master/examples/src/main/resources/people.json").getLines
// remove any pre-existing file at the dbfs location
dbutils.fs.rm("dbfs:///datasets/spark-examples/people.json",recurse=true)
// convert the lines fetched from the URL to a Seq, then make it a RDD of String and finally save it as textfile to dbfs
sc.parallelize(peopleJsonLinesFromURL.toSeq).saveAsTextFile("dbfs:///datasets/spark-examples/people.json")
// read the text file we just saved and see what it has
sc.textFile("dbfs:///datasets/spark-examples/people.json").collect.mkString("\n")

val df = spark.read.json("dbfs:///datasets/spark-examples/people.json")

// you can also read into df like this
val df = spark.read.format("json").load("dbfs:///datasets/spark-examples/people.json")

df.show()

Untyped Dataset Operations (aka DataFrame Operations)

DataFrames provide a domain-specific language for structured data manipulation in Scala, Java, Python and R.

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:

// This import is needed to use the $-notation
import spark.implicits._
// Print the schema in a tree format
df.printSchema()

// Select only the "name" column
df.select("name").show()

// Select everybody, but increment the age by 1
df.select($"name", $"age" + 1).show()

// Select people older than 21
df.filter($"age" > 21).show()

// Count people by age
df.groupBy("age").count().show()

Find full example code at - https://raw.githubusercontent.com/apache/spark/master/examples/src/main/scala/org/apache/spark/examples/sql/SparkSQLExample.scala in the Spark repo.

For a complete list of the types of operations that can be performed on a Dataset, refer to the API Documentation.

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.

Running SQL Queries Programmatically

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

// Register the DataFrame as a SQL temporary view
df.createOrReplaceTempView("people")

val sqlDF = spark.sql("SELECT * FROM people")
sqlDF.show()

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.

// Register the DataFrame as a global temporary view
df.createGlobalTempView("people")

// Global temporary view is tied to a system preserved database `global_temp`
spark.sql("SELECT * FROM global_temp.people").show()

// Global temporary view is cross-session
spark.newSession().sql("SELECT * FROM global_temp.people").show()

Creating Datasets

Datasets are similar to RDDs, however, instead of using Java serialization or Kryo they use a specialized 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.

case class Person(name: String, age: Long)

// Encoders are created for case classes
val caseClassDS = Seq(Person("Andy", 32)).toDS()
caseClassDS.show()

// Encoders for most common types are automatically provided by importing spark.implicits._
val primitiveDS = Seq(1, 2, 3).toDS()
primitiveDS.map(_ + 1).collect() // Returns: Array(2, 3, 4)

// DataFrames can be converted to a Dataset by providing a class. Mapping will be done by name
val path = "dbfs:///datasets/spark-examples/people.json"
val peopleDS = spark.read.json(path).as[Person]
peopleDS.show()

Dataset is not available directly in PySpark or SparkR.

Interoperating with RDDs

Spark SQL supports two different methods for converting existing RDDs into Datasets. The first method uses reflection to infer the schema of an RDD that contains specific types of objects. This reflection-based approach leads to more concise code and works well when you already know the schema while writing your Spark application.

The second method for creating Datasets is through a programmatic interface that allows you to construct a schema and then apply it to an existing RDD. While this method is more verbose, it allows you to construct Datasets when the columns and their types are not known until runtime.

Inferring the Schema Using Reflection

The Scala interface for Spark SQL supports automatically converting an RDD containing case classes to a DataFrame. The case class defines the schema of the table. The names of the arguments to the case class are read using reflection and become the names of the columns. Case classes can also be nested or contain complex types such as Seqs or Arrays. This RDD can be implicitly converted to a DataFrame and then be registered as a table. Tables can be used in subsequent SQL statements.

// the following lines merely fetch the file from the URL and load it into the dbfs for us to try in databricks
// getLines from the file at the URL
val peopleTxtLinesFromURL = scala.io.Source.fromURL("https://raw.githubusercontent.com/apache/spark/master/examples/src/main/resources/people.txt").getLines
// remove any pre-existing file at the dbfs location
dbutils.fs.rm("dbfs:///datasets/spark-examples/people.txt",recurse=true)
// convert the lines fetched from the URL to a Seq, then make it a RDD of String and finally save it as textfile to dbfs
sc.parallelize(peopleTxtLinesFromURL.toSeq).saveAsTextFile("dbfs:///datasets/spark-examples/people.txt")
// read the text file we just saved and see what it has
sc.textFile("dbfs:///datasets/spark-examples/people.txt").collect.mkString("\n")

sc.textFile("dbfs:///datasets/spark-examples/people.txt").collect.mkString("\n")

// For implicit conversions from RDDs to DataFrames
import spark.implicits._

// make a case class
case class Person(name: String, age: Long)

// Create an RDD of Person objects from a text file, convert it to a Dataframe
val peopleDF = spark.sparkContext
  .textFile("dbfs:///datasets/spark-examples/people.txt")
  .map(_.split(","))
  .map(attributes => Person(attributes(0), attributes(1).trim.toLong))
  //.map(attributes => Person(attributes(0), attributes(1).trim.toLong))
  .toDF()

peopleDF.show

// Register the DataFrame as a temporary view
peopleDF.createOrReplaceTempView("people")

// SQL statements can be run by using the sql methods provided by Spark
val teenagersDF = spark.sql("SELECT name, age FROM people WHERE age BETWEEN 13 AND 19")

teenagersDF.show()

// The columns of a row in the result can be accessed by field index
teenagersDF.map(teenager => "Name: " + teenager(0)).show()

// or by field name
teenagersDF.map(teenager => "Name: " + teenager.getAs[String]("name")).show()

// No pre-defined encoders for Dataset[Map[K,V]], define explicitly
implicit val mapEncoder = org.apache.spark.sql.Encoders.kryo[Map[String, Any]]

// Primitive types and case classes can be also defined as
// import more classes here...
//implicit val stringIntMapEncoder: Encoder[Map[String, Any]] = ExpressionEncoder()

// row.getValuesMap[T] retrieves multiple columns at once into a Map[String, T]
teenagersDF.map(teenager => teenager.getValuesMap[Any](List("name", "age"))).collect()

Programmatically Specifying the Schema

When case classes cannot be defined ahead of time (for example, the structure of records is encoded in a string, or a text dataset will be parsed and fields will be projected differently for different users), a DataFrame can be created programmatically with three steps.

1. Create an RDD of Rows from the original RDD;
2. Create the schema represented by a StructType matching the structure of Rows in the RDD created in Step 1.
3. Apply the schema to the RDD of Rows via createDataFrame method provided by SparkSession.

For example:

import org.apache.spark.sql.Row

import org.apache.spark.sql.types._

// Create an RDD
val peopleRDD = spark.sparkContext.textFile("dbfs:///datasets/spark-examples/people.txt")

// The schema is encoded in a string
val schemaString = "name age"

// Generate the schema based on the string of schema
val fields = schemaString.split(" ")
  .map(fieldName => StructField(fieldName, StringType, nullable = true))

val schema = StructType(fields)

// Convert records of the RDD (people) to Rows
val rowRDD = peopleRDD
  .map(_.split(","))
  .map(attributes => Row(attributes(0), attributes(1).trim))

// Apply the schema to the RDD
val peopleDF = spark.createDataFrame(rowRDD, schema)

peopleDF.show

// Creates a temporary view using the DataFrame
peopleDF.createOrReplaceTempView("people")

// SQL can be run over a temporary view created using DataFrames
val results = spark.sql("SELECT name FROM people")

results.show

// The results of SQL queries are DataFrames and support all the normal RDD operations
// The columns of a row in the result can be accessed by field index or by field name
results.map(attributes => "Name: " + attributes(0)).show()

Find full example code at - https://raw.githubusercontent.com/apache/spark/master/examples/src/main/scala/org/apache/spark/examples/sql/SparkSQLExample.scala in the Spark repo.

Scalar Functions

Scalar functions are functions that return a single value per row, as opposed to aggregation functions, which return a value for a group of rows. Spark SQL supports a variety of Built-in Scalar Functions. It also supports User Defined Scalar Functions.

Aggregate Functions

Aggregate functions are functions that return a single value on a group of rows. The Built-in Aggregation Functions provide common aggregations such as count(), countDistinct(), avg(), max(), min(), etc. Users are not limited to the predefined aggregate functions and can create their own. For more details about user defined aggregate functions, please refer to the documentation of User Defined Aggregate Functions.