SQL on Hadoop and Spark

Question 1

SQL-on-Hadoop

Answer 1

tools that combine familiar SQL interface with scalability and flexibility of big data processing frameworks

Question 2

Why is SQL special?

Answer 2

Users can leverage SQL knowledge to interact with large datasets, without learning new paradigms like MapReduce or Spark

Question 3

Batch SQL

Answer 3

SQL-like queries translated into MapReduce/Spark jobs

(load datasets in memory then query)

Question 4

Batch SQL query tool examples (2)

Answer 4

Apache Hive, Spark SQL

Question 5

Interactive SQL

Answer 5

Tools that enable low-latency, interactive querying (enable traditional BI and analytics)

Question 6

Interactive SQL query tool examples (2)

Answer 6

Apache Impala, Apache Drill

Question 7

Operational SQL

Answer 7

Tools supporting small, more frequent writes and queries with fast response times (OLTP)

Question 8

Examples of OLTP workloads

Answer 8

insert, update, delete operations (small, more frequent queries)

Question 9

Operational SQL query tool examples (2)

Answer 9

NoSQL, HBase

Question 10

Apache Hive

Answer 10

Provides data warehouse-like abstraction over Hadoop, enabling SQL-like queries with HiveSQL

Question 11

What specifically does HiveSQL do?

Answer 11

Translate SQL-like queries into MapReduce or Spark jobs

Question 12

Key features of Apache Hive (3) (SMO)

Answer 12

1. Schema on read (not like real DW)
2. Metastore that uses RDBMS (on single node)
3. Organize data into units

Question 13

What are the 4 data units in Hive?

Answer 13

1. Databases (Folder in HDFS)
2. Tables (set of files in HDFS folder) - Set of
   records with same schema

Optional:
3. Partitions (of table records based off column value) - faster, better for low cardinality
4. Buckets (group table records into fixed number of files) - high cardinality

Question 14

AWS version of Apache Hive

Answer 14

Athena, allows querying over S3 data

Question 15

Spark SQL

Answer 15

Allos users to run SQL queries on data stored in Spark structures

Question 16

Spark SQL Dataframe and Dataset

Answer 16

Essentially RDD’s with a schema attached to support relational (and procedural) processing

Question 17

Query optimization

Answer 17

When you use SQL with spark, your SQL queries go through several optimization steps before being executed

Question 18

Catalyst Optimizer

Answer 18

Generates optimized execution plans for SQL like queries, restructuring the query plan to make it more efficient

Question 19

3 types of Catalyst Optimizers;

Answer 19

1. Predicate Pushdown (filters/where clauses)
2. Column Pruning
3. JVM Code Generation (bytecode to reduce overhead of running queries)

Question 20

Pro and Con of using SQL over Spark

Answer 20

Pro: Language simplicity (easier to optimize SQL query than user defined function)

Con: Structure imposes limits, RDD’s typically enable any computation through user defined functions

Question 21

Logical vs Physical plans for optimizing queries

Answer 21

logical describes computations, physical outlines which algorithms are used to conduct them

Question 22

Constant Folding (Logical Optimization Rules)

Answer 22

Resolves constant expressions (non variable, 2 + 3) at compile time instead of runtime

Question 23

Predicate Pushdown (Logical Optimization Rules)

Answer 23

Push filter conditions as close to data source as possible (where department =) So that Spark only reads the where clause, not the full query

Question 24

Column Pruning (Logical Optimization Stage Rules)

Answer 24

Select only necessary columns

Question 25

Join Reordering (Logical Optimization Stage Rules)

Answer 25

Reordering joins to process smallest tables first

Question 26

Spark can apply logical optimization rules both ____________ and ____________ until plan reaches a fixed point

Answer 26

recursively and iteratively

Question 27

Physical Optimization (2)

Answer 27

Cost model, join methods

Question 28

How does spark sql decide how to physically execute the optimization?

Answer 28

A cost model is use to select the best one

Ex:
Cost = a x cost(cpu) + (1-a) x cost(IO)

Question 29

Physical Optimization Join Methods (3)

Answer 29

1. Broadcast hash (smaller table loaded into memory on each node where larger table has partitions)
2. Shuffle hash (Both tables partitioned, relevant partition groups are sent to the same node. Hash table is created locally on each node for smaller table partition, saved in memory, then scan through bigger table for matches )
3. Shuffle sort merge (partitions shuffled just like in shuffle hash, but instead of using hash table each partition is sorted by join key. Scan through both tables and merge where join keys match)

Question 30

4 Factors that determine the cost of physical optimization (NDCD)

Answer 30

Network throughput
Disk throughput
CPU cost
Data locality

Question 31

Adaptive Query Execution (AQE)

Answer 31

In Spark 3.0 dynamically optimizes execution plan based on runtime statistics (Think of as another layer on top of spark catalyst optimizer)

Question 32

Things AQE can impact

Answer 32

Adaptive number of shuffle partitions instead of fixed
Switch type of join
Optimize skewed joins?
Dynamic Partition pruning?