SPARK SQL

Submitted By
G.Datta veerean chowdary-Vtu10096
A.Ram babu-Vtu10116
K.Venkata Ramesh Reddy -Vtu10104
SRC-7

DA38

School of Computing
Department of Computer Science and Engineering
15th MARCH 2019
ABSTRACT
In this Apache Spark SQL tutorial, we will understand various
components and ​terminologies of Spark SQL like what is
DataSet and DataFrame, what is SqlContext and HiveContext
and What are the features of Spark SQL?
After understanding ​What is Apache Spark​, in this tutorial we
will discuss about Apache Spark SQL. Spark SQL is Spark
module for structured data processing. It runs on top of Spark
Core. It offers much tighter integration between relational and
procedural processing, through declarative DataFrame and
Datasets API. These are the ways which enable users to run SQL
queries over Spark.
INTRODUCTION:
Apache Spark SQL integrates relational processing with Sparks
functional programming. It is Spark module for structured data
processing. Spark SQL blurs the line between ​RDD and
relational table. It also offers much tighter integration between
relational and procedural processing, through declarative
DataFrame APIs which integrates with Spark code. It thus
provides higher optimization. DataFrame API and Datasets API
are the ways to interact with Spark SQL. As a result, with Spark
SQL, Apache Spark is accessible to more users and improves
optimization for current ones.
Spark SQL runs on top of the Spark Core. It allows developers
to import relational data from ​Hive tables and parquet files, run
SQL queries over imported data and existing RDDs and easily
write RDDs out to Hive tables or Parquet files. As Spark SQL
provides DataFrame APIs which performs the relational
operation on both external data sources and Sparks built-in
distributed collections. Spark SQL introduces extensible
optimizer calledCatalyst as it helps in supporting a wide range of
data sources and algorithms in ​Bigdata​.
FEATURES:

a. Integrated
Integrate is simply defined as combining or merge. Here, Spark
SQL queries are integrated with Spark programs. Through Spark
SQL we are allowed to query structured data inside Spark
programs. This is possible by using SQL or a DataFrame that
can be used in Java, ​Scala​.
We can run streaming computation through it. Developers write
a batch computation against the DataFrame / Dataset API to run
it. After that to run it in a streaming fashion Spark itself
increments the computation. Developers leverage the advantage
of it that they don’t have to manage state, failures on own. Even
no need keep the application in sync with batch jobs. Despite,
the streaming job always gives the same answer as a batch job
on the same data.

b. Unified Data Access

To access a variety of data sources DataFrames and SQL
support a common way. Data Sources like Hive, Avro, Parquet,
ORC, JSON, as well as JDBC. It helps to join the data from
these sources. To accommodate all the existing users into Spark
SQL, it turns out to be very helpful.

c. High compatibility
We are allowed to run unmodified Hive queries on existing
warehouses in Spark SQL. With existing Hive data, queries and
UDFs, Spark SQL offers full compatibility. Also, rewrites the
MetaStore and Hive frontend.

d. Standard Connectivity
We can easily connect Spark SQL through JDBC or ODBC. For
connectivity for business intelligence tools, Both turned as
industry norms. Also, includes industry standard JDBC and
ODBC connectivity with server mode.

e. Scalability
It takes advantage of RDD model, to support large jobs and
mid-query ​fault tolerance​. For interactive as well as long
queries, it uses the same engine.

f. Performance Optimization
In Spark SQL, query optimization engine converts each SQL
query into a logical plan. Afterwards, it converts to many
physical execution plans. At the time of execution, it selects the
most optimal physical plan, among the entire plan. It ensures
fast execution of HIVE queries.

g. For batch processing of Hive tables

While working with Hive tables, we can use Spark SQL for
Batch Processing in them.

Conclusion
Hence, we have seen all Spark SQL features in detail. As a
result, we have learned, Spark SQL is a module of Spark that
analyses structured data. Basically, it offers scalability and
ensures high compatibility of the system. Also, allow standard
connectivity through JDBC or ODBC. Therefore, it bestows the
most natural way to express the structured data. Moreover, it
enhances its working efficiency with above-mentioned Spark
SQL features.
Spark SQL Architecture:
The following illustration explains the architecture of Spark
SQL
This architecture contains three layers namely, Language API,
Schema RDD, and Data Sources.

● Language API − Spark is compatible with different

languages and Spark SQL. It is also, supported by these

languages- API (python, scala, java, HiveQL).

● Schema RDD − Spark Core is designed with special data

structure called RDD. Generally, Spark SQL works on

schemas, tables, and records. Therefore, we can use the

Schema RDD as temporary table. We can call this Schema

RDD as Data Frame.

● Data Sources − Usually the Data source for spark-core is a

text file, Avro file, etc. However, the Data Sources for

Spark SQL is different. Those are Parquet file, JSON

document, HIVE tables, and Cassandra database.

DATA FRAME:
In this Spark SQL DataFrame tutorial, we will learn what is
DataFrame in​Apache Spark and the need of Spark Dataframe.
The tutorial covers the limitation of Spark RDD and How
DataFrame overcomes those limitations. How to create
DataFrame in Spark, Various Features of DataFrame like
Custom Memory Management, Optimized Execution plan, and
its limitations are also covers in this Spark tutorial.

What is Spark SQL DataFrame?

DataFrame appeared in Spark Release 1.3.0. We can term
DataFrame as Dataset organized into named columns.
DataFrames are similar to the table in a relational database or
data frame in ​R /Python. It can be said as a relational table with
good optimization technique.
The idea behind DataFrame is it allows processing of a large
amount of structured data. DataFrame contains rows with
Schema. The schema is the illustration of the structure of data.
DataFrame in Apache Spark prevails over ​RDD but contains the
features of RDD as well. The features common to RDD and
DataFrame are immutability, ​in-memory​, resilient, distributed
computing capability. It allows the user to impose the structure
onto a distributed collection of data. Thus provides higher level
abstraction.
We can build DataFrame from different data sources. For
Example structured data file, tables in ​Hive​, external databases
or existing RDDs. The Application Programming Interface
(APIs) of DataFrame is available in various languages.
Examples include ​Scala​, Java, Python, and R.
Both in Scala and Java, we represent DataFrame as Dataset of
rows. In the Scala API, DataFrames are type alias of
Dataset[Row]. In Java API, the user uses Dataset<Row> to
represent a DataFrame.

Why DataFrame?
DataFrame is one step ahead of RDD. Since it provides memory
management and optimized execution plan.
a. Custom Memory Management: This is also known as Project
Tungsten. A lot of memory is saved as the data is stored in
off-heap memory in binary format. Apart from this, there is no
Garbage Collection overhead. Expensive Java serialization is
also avoided. Since the data is stored in binary format and the
schema of memory is known.
b. Optimized Execution plan: This is also known as the query
optimizer. Using this, an optimized execution plan is created for
the execution of a query. Once the optimized plan is created
final execution takes place on RDDs of Spark.

Features of Apache Spark DataFrame

Some of the ​limitations of Spark RDD​ were-
● It does not have any built-in optimization engine.
● There is no provision to handle structured data.

Thus, to overcome these limitations the picture of DataFrame

came into existence. Some of the key features of DataFrame in
Spark are:
i. DataFrame is a distributed collection of data organized in
named column. It is equivalent to the table in RDBMS.
ii. It can deal with both structured and unstructured data formats.
For Example Avro, CSV, elastic search, and Cassandra. It also
deals with storage systems ​HDFS​, HIVE tables, MySQL, etc.
iii. Catalyst supports optimization. It has general libraries to
represent trees. DataFrame uses Catalyst tree transformation in
four phases:
● Analyze logical plan to solve references
● Logical plan optimization
● Physical planning
● Code generation to compile part of a query to Java
bytecode.

You can refer this guide to ​learn Spark SQL optimization phases
in detail.
iv. The DataFrame API’s are available in various programming
languages. For example Java, Scala, Python, and R.
v. It provides Hive compatibility. We can run unmodified Hive
queries on existing Hive warehouse.
vi. It can scale from kilobytes of data on the single laptop to
petabytes of data on a large cluster.
vii. DataFrame provides easy integration with Big data tools and
framework via Spark core.

Creating DataFrames in Apache Spark

To all the ​functionality of Spark​, SparkSession class is the entry
point. For the creation of basic SparkSession just use
SparkSession.builder()
Using Spark Session, an application can create DataFrame from
an existing RDD, Hive table or from Spark data sources. ​Spark
SQL can operate on the variety of data sources using DataFrame
interface. Using Spark SQL DataFrame we can create a
temporary view. In the temporary view of dataframe, we can run
the SQL query on the data.

Limitations of DataFrame in Spark

● Spark SQL DataFrame API does not have provision for
compile time type safety. So, if the structure is
unknown, we cannot manipulate the data.
● Once we convert the domain object into data frame, the
regeneration of domain object is not possible.

Conclusion
Hence, DataFrame API in Spark SQL improves the performance
and scalability of Spark. It avoids the garbage-collection cost of
constructing individual objects for each row in the dataset.
The Spark DataFrame API is different from the RDD API
because it is an API for building a relational query plan that
Spark’s Catalyst optimizer can then execute. This DataFrame
API is good for developers who are familiar with building query
plans. It is not good for the majority of developers.
DATASET:

Today, in this blog on ​Apache Spark dataset, you can read all
about what is dataset in Spark. Why the Spark DataSet needed,
what is the encoder and what is their significance in the dataset?
You will get the answer to all these questions in this blog.
Moreover, we will also cover the features of the dataset in
Apache Spark and How to create a dataset in this Spark tutorial.

What is Spark Dataset?

Dataset is a data structure in ​SparkSQL which is strongly typed
and is a map to a relational schema. It represents structured
queries with encoders. It is an extension to data frame API.
Spark Dataset provides both type safety and object-oriented
programming interface. We encounter the release of the dataset
in Spark 1.6.
The encoder is primary concept in ​serialization and
deserialization(​ SerDes) framework in Spark SQL. Encoders
translate between JVM objects and Spark’s internal binary
format. Spark has built-in encoders which are very advanced.
They generate bytecode to interact with off-heap data.
An encoder provides on-demand access to individual attributes
without having to de-serialize an entire object. To make
input-output time and space efficient, Spark SQL uses the SerDe
framework. Since encoder knows the schema of record, it can
achieve serialization and deserialization.
Spark Dataset is structured and lazy query expression that
triggers the action. Internally dataset represents a logical plan.
The logical plan tells the computational query that we need to
produce the data. the logical plan is a base catalyst query plan
for the logical operator to form a logical query plan. When we
analyze this and resolve we can form a physical query plan.
Dataset clubs the features of​ RDD​ and ​DataFrame​. It provides:
● The convenience of RDD.
● Performance optimization of DataFrame.
● Static type-safety of ​Scala​.

Thus, Datasets provides a more functional programming

interface to work with structured data.

Need of Dataset in Spark

To overcome the limitations of RDD and Dataframe, Dataset
emerged. In DataFrame, there was no provision for
compile-time type safety. Data cannot be altered without
knowing its structure. In RDD there was no automatic
optimization. So for optimization, we do it manually when
needed.

Features of Dataset in Spark

After having the introduction to dataSet, let’s now discuss
various features of Spark Dataset-
a. Optimized Query

Dataset in Spark provides Optimized query using ​Catalyst Query

Optimizer and Tungsten. Catalyst Query Optimizer is an
execution-agnostic framework. It represents and manipulates a
data-flow graph. Data flow graph is a tree of expressions and
relational operators. By optimizing the Spark job Tungsten
improves the execution. Tungsten emphasizes the hardware
architecture of the platform on which Apache Spark runs.
b. Analysis at compile time

Using Dataset we can check syntax and analysis at compile

time. It is not possible using Dataframe, RDDs or regular SQL
queries.
c. Persistent Storage

Spark Datasets are both serializable and Queryable. Thus, we

can save it to persistent storage.
d. Inter-convertible

We can convert the Type-safe dataset to an “untyped”

DataFrame. To do this task Datasetholder provide three methods
for conversion from ​Seq[T]​ or ​RDD[T]​ types to ​Dataset[T]:
● toDS(): Dataset[T]
● toDF(): DataFrame
● toDF(colNames: String*): DataFrame

e. Faster Computation

The implementation of the Dataset is much faster than the RDD

implementation. Thus increases the performance of the system.
For the same performance using the RDD, the user manually
considers how to express computation that parallelizes
optimally.
f. Less Memory Consumption

While caching, it creates a more optimal layout. Spark knows

the structure of data in the dataset.
g. Single API for Java and Scala

It provides a single interface for Java and Scala. This unification

ensures we can use Scala interface, code examples from both
languages. It also reduces the burden of libraries. As libraries
have no longer to deal with two different type of inputs.
Creating Dataset
To create a Dataset we need:
a. SparkSession
SparkSession is the entry point to the SparkSQL. It is a very first
object that we create while developing Spark SQL applications
using fully typed Dataset data abstractions. Using
SparkSession.Builder, we can create an instance of
SparkSession. And can stop SparkSession using the stop method
(​spark.stop​).
b. QueryExecution
We represent structured query execution pipeline of the dataset
using​QueryExecution​. To access QueryExecution of a Dataset
use QueryExecution attribute. By executing a logical plan in
Spark Session we get QueryExecution.
executePlan(plan: LogicalPlan): QueryExecution
executePlan executes the input LogicalPlan to produce a
QueryExecution in the current SparkSession.
c. Encoder
An encoder ​provides conversion between tabular representation
and JVM objects. With the help of the encoder, we serialize the
object. Encoder serializes objects for processing or transmitting
over the network encoders.
So, this was all in Spark Dataset Tutorial. Hope you like our
explanation.

Conclusion
Hence, in conclusion to Dataset, we can say it is a strongly typed
data structure in Apache Spark. Moreover, it represents
structured queries. Also, it fuses together the ​functionality of
RDD and DataFrame. We can generate the optimized query
using Dataset. So, Dataset lessens the memory consumption and
provides a single API for both Java and Scala.