Spark SQL: An Introductory Guide

1. Objective

Apache SparkSQL is a Spark module to simplify working with structured data using DataFrame and DataSet abstractions in Python, Java, and Scala. These abstractions are the distributed collection of data organized into named columns. It provides a good optimization technique. Using Spark SQL we can query data, both from inside a Spark program and from external tools that connect through standard database connectors (JDBC/ODBC) to Spark SQL.

This tutorial covers the components of Spark SQL architecture like DataSets and DataFrames, and Apache the Spark SQL Catalyst optimizer. We will also learn the use for Spark SQL in Apache Spark, Spark SQL advantage, and disadvantages.

2. Apache Spark SQL Tutorial

2.1. Spark SQL Introduction

Apache Spark SQL is a module for structured data processing in Spark. Using the interface provided by Spark SQL we get more information about the structure of the data and the computations performed. With this extra information, one can achieve extra optimization in Apache Spark. We can interact with Spark SQL in various ways like DataFrame and the Dataset API. The same execution engine is used while computing a result, irrespective of which API/language we use to express the computation. Thus, the user can easily switch back and forth between different APIs.

In Apache Spark SQL, we can use structured and semi-structured data in four ways:

• To simplify working with structured data, it provides DataFrame abstractions in Python, Java, and Scala. DataFrame is a distributed collection of data organized into named columns. It provides a good optimization technique.
• The data can be read and written in a variety of structured formats. For example, JSON, Hive Tables, and Parquet.
• Using SQL, we can query data, both from inside a Spark program and from external tools. The external tool connects through standard database connectors (JDBC/ODBC) to Spark SQL.
• The best way to use Spark SQL is inside a Spark application. This empowers us to load data and query it with SQL. At the same time, we can also combine it with “regular” program code in Python, Java, or Scala.

When SQL runs from the other programming language the result will be a Dataset/DataFrame. The interaction with SQL interface is made using the command line or over JDBC/ODBC.

2.2. Spark SQL DataFrames

There were some limitations with RDDs. When working with structured data, there was no built-in optimization engine. On the basis of attributes, the developer had to optimize each RDD. Also, there was no provision to handle structured data. The DataFrame in Spark SQL overcomes these limitations of RDD. Spark DataFrame is Spark 1.3 release. It is a distributed collection of data ordered into named columns. Concept wise it is equal to the table in a relational database or a data frame in R/Python. We can create DataFrame using:

• Structured data files
• Tables in Hive
• External databases
• Using existing RDD

2.3. Spark SQL Datasets

Spark Dataset is an interface added in version Spark 1.6. it is a distributed collection of data. Dataset provides the benefits of RDDs along with the benefits of Apache Spark SQL’s optimized execution engine. Here, an encoder is a concept that does conversions between JVM objects and tabular representations.

A dataset can be made using JVM objects and, after that, it can be manipulated using functional transformations (map, filter, etc.). The Dataset API is accessible inScala and Java. The Dataset API is not supported by Python, but, because of the dynamic nature of Python, many benefits of the Dataset API are available. The same is the case with R. Using a Dataset of rows we represent DataFrame in Scala and Java.

2.4. Spark Catalyst Optimizer

The optimizer used by Spark SQL is the Catalyst optimizer. It optimizes all the queries written in Spark SQL and the DataFrame DSL. The optimizer helps us to run queries much faster than their RDD counterpart. This increases the performance of the system.

Spark Catalyst is a library built as a rule-based system. And each rule focuses on the specific optimization. For example,ConstantFolding's focus is on eliminating a constant expression from the query.

2.5. Uses of Apache Spark SQL

• It executes SQL queries.
• We can read data from existingHive installations using SparkSQL.
• When we run SQL within another programming language we will get the result as a Dataset/DataFrame.

2.6. Functions Defined by Spark SQL

• Built-in function: Offers a built-in function to process the column value. We can access the inbuilt function by using the following command:

  • Import org.apache.spark.sql.functions 

• User Defined Functions (UDFs): UDFs allow you to create the user define functions based on the user defined functions in Scala.

• Aggregate functions: These operate on a group of rows and calculate a single return value per group.

• Windowed Aggregates (Windows): These operate on a group of rows and calculate a single return value for each row in a group.

2.7. Advantages of Spark SQL

In this section of, we will discuss various advantages of Apache Spark SQL.

a. Integrated

Apache Spark SQL mixes SQL queries with Spark programs. With the help of Spark SQL, we can query structured data as a distributed dataset (RDD). We can run SQL queries alongside complex analytic algorithms using the tight integration property of Spark SQL.   

b. Unified Data Access

Using Spark SQL, we can load and query data from different sources. The Schema-RDDs let single interfaces to productively work with structured data. For example, Apache Hive tables, parquet files, and JSON files.

c. High Compatibility

In Apache Spark SQL, we can run unmodified Hive queries on existing warehouses. It allows full compatibility with existing Hive data, queries and UDFs, by using the Hive front-end and MetaStore.

d. Standard Connectivity

It can connect through JDBC or ODBC. It includes the server mode with industry standard JDBC and ODBC connectivity.

e. Scalability

To support mid-query fault tolerance and large jobs, it takes advantage of RDD model. It uses the same engine for interactive and long queries.

f. Performance Optimization

The query optimization engine in Spark SQL converts each SQL query to a logical plan. Further, it converts to many physical execution plans. Among the entire plan, it selects the most optimal physical plan for execution.

g. For Batch Processing of Hive Tables

We can make use of Spark SQL for fast batch processing of Hive tables.

2.8. Disadvantages of Spark SQL

Apart from these features, there are also some disadvantages of Spark SQL. Some of them are listed below.

a. Unsupportive Union Type

Using Spark SQL, we cannot create or read a table containing union fields.

b. No Error for Oversize of Varchar Type

Even if the inserted value exceeds the size limit, no error will occur. The same data will truncate if read from Hive but not if read from Spark. SparkSQL will consider varchar as a string, meaning there is no size limit.

c. No Support for Transactional Table

Hive transactions are not supported by Spark SQL.

d. Unsupportive Char Type

Char type (fixed length strings) are not supported. Like the union, we cannot read or create a table with such fields.

e. No Support for Time-Stamp in Avro Table.

3. Conclusion

In conclusion to Spark SQL, it is a module of Apache Spark that analyzes structured data. It provides scalability and ensures high compatibility of the system. It has standard connectivity through JDBC or ODBC. Thus, it provides the most natural way to express structured data.