A Look at SparkSQL

November 18, 2015
124 Views

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?

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 referece 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.

You may be interested

The Role Big Data Plays in Influencer Marketing
Big Data
69 shares1,300 views
Big Data
69 shares1,300 views

The Role Big Data Plays in Influencer Marketing

Ryan Kh - June 26, 2017

Influencer marketing is the leading way in which brands can get noticed. Big data is also one of the leading…

Experts Debate: Is Big Data a Boon or Risk for Actuaries?
Big Data
49 shares520 views
Big Data
49 shares520 views

Experts Debate: Is Big Data a Boon or Risk for Actuaries?

Annie Qureshi - June 26, 2017

Banks, insurance companies and other firms in the financial sector have relied extensively on big data for decades. Over the…

Big Data: A Hidden Blessing or Increased Vulnerability for the Security of IT Systems?
Big Data
40 shares671 views
Big Data
40 shares671 views

Big Data: A Hidden Blessing or Increased Vulnerability for the Security of IT Systems?

Diana Hope - June 26, 2017

For many companies that are not equipped to handle it, it seems like a real handful; yet for most, big…