A Look at SparkSQL

If you’ve been reading about Apache Spark, you might be worried about whether you have to relearn all of your skills for using it to interact with databases. With Apache Spark, whether you’re a DBA or a developer, you’ll be able to interact with Apache Spark in the way you’re used to?—?while solving real problems.

What Is SparkSQL?

SparkSQL, as the name suggests, is a way to use Apache Spark using the SQL language. Apache Spark makes it easy to run complex queries over lots of nodes, something that’s rather difficult with conventional RDBMSs like MySQL.

Unlike a NoSQL database, you don’t have to learn a new query language or database model. It offers the advantage of NoSQL in scalability, and ease of running over a cluster while using the familiar SQL query model. You can import a number of different data formats in SparkSQL, such as Parquet files, JSON data, as well as RDDs (the native data format of Apache Spark).

SparkSQL allows for both interactive and batch operations. You can take advantage of Spark’s speed, running queries in real time. Spark is so fast partly because of lazy evaluation, which means that queries won’t actually be computed until you need some kind of output.

By using a REPL (i.e. interactive shell), you can explore your data using SparkSQL in real time. You can choose either Spark’s native Scala or Python.

If you haven’t noticed, Spark draws on a lot of functional programming concepts from languages like Haskell and Lisp: lazy evaluation, immutable data structures, and an interactive REPL. These concepts aren’t exactly new, as Lisp data back to the late ‘50s.

SchemaRDD

SchemaRDD is a special RDD, or Resilient Distributed Dataset. RDDs are central to understanding Apache Spark. RDDs are immutable data structures, which means that you can’t change them. Operations on RDDs simply return new RDDs. This allows for a degree of safety when dealing with RDDs.

Lineages keep track of all the changes on RDDs, which are known as transformations. In case of some kind of failure, Spark can reconstruct the data from these lineages.

RDDs are also represented in memory, or in at least as much memory as is possible. This gives Spark an extra speed boost.

SchemaRDD is a special RDD that works similarly to a SQL table. You can import your data from a text file into a SchemaRDD.

Queries

You can import your data from text files and then work on it using SQL queries such as SELECT, JOIN, and more. (see a live example)

Spark provides two contexts for queries: SQLContext and HiveContext. The former provides a simple SQL parser, while HiveContext gives you access to a HiveQL cluster for more powerful queries.

Use Case: Customers

You’re probably itching to see all this stuff in action. Let’s borrow an example from MapR’s Apache Spark reference card.

Let’s pretend we run a clothing store in the Dallas, Texas, area, and we want to know a little more about our customers. We have a plain text database showing customer name, age, gender, and address, where the values are separated by a “|”:
John Smith|38|M|201 East Heading Way #2203,Irving, TX,75063
Liana Dole|22|F|1023 West Feeder Rd, Plano,TX,75093
Craig Wolf|34|M|75942 Border Trail,Fort Worth,TX,75108
John Ledger|28|M|203 Galaxy Way,Paris, TX,75461
Joe Graham|40|M|5023 Silicon Rd,London,TX,76

Using Scala, we’ll define a schema:
case class Customer(name:String,age:Int,gender:String,address:String)

Next, we’ll import our plain text file and make a SQLContext:
val sparkConf = new SparkConf().setAppName(“Customers”)
val sc = new SparkContext(sparkConf)
val sqlContext = new SQLContext(sc)
val r = sc.textFile(“/Users/jim/temp/customers.txt”)
val records = r.map(_.split(‘|’))
val c = records.map(r=>Customer(r(0),r(1).trim.toInt,r(2),r(3)))
c.registerAsTable(“customers”)

Suppose management has decided that they’re going to start targeting millennial males as a lucrative market. We might start by looking through our database by age and gender:

sqlContext.sql(“select * from customers where gender=’M’ and age < 30”).collect().foreach(println)

Here’s the result:
[John Ledger,28,M,203 Galaxy Way,Paris, TX,75461]

It looks like we’re going to have to do a little work in attracting more of these kinds of customers.

Conclusion

For a more in-depth introduction to Spark, read Getting Started with Spark: From Inception to Production, a free interactive eBook by James A. Scott