SQL for Big Data (Spark SQL)

By the end of this lesson, you will be able to write and execute basic SQL queries on massive datasets using Spark SQL, leveraging distributed computing for performance.

What it is

Spark SQL is a module in Apache Spark that provides structured data processing. It allows you to query large datasets stored in various formats (Parquet, JSON, CSV) or databases using standard SQL syntax. Unlike traditional single-node databases, Spark SQL distributes the query execution across a cluster of machines, enabling it to process terabytes or petabytes of data efficiently. The core abstraction is the DataFrame, which represents a distributed collection of data organized into named columns.

Why it matters

• Scalability: Handles datasets far larger than memory by distributing workloads across nodes.
• Familiarity: Allows analysts and engineers to use standard SQL instead of learning complex MapReduce APIs.
• Optimization: Uses Catalyst optimizer to automatically improve query plans for better performance.
• Integration: Seamlessly combines with other Spark libraries like MLlib for machine learning tasks.

Syntax or steps

To run a Spark SQL query, you typically follow these steps: initialize a Spark session, load data into a temporary view or DataFrame, register it as a table, and then execute the SQL string. The key method is spark.sql().

Example

from pyspark.sql import SparkSession

# 1. Initialize Spark Session
spark = SparkSession.builder \
    .appName("BigDataSQL") \
    .getOrCreate()

# 2. Create sample data (in real scenarios, read from Parquet/CSV)
data = [
    ("Alice", "Engineering", 95000),
    ("Bob", "Sales", 72000),
    ("Charlie", "Engineering", 88000),
    ("David", "Marketing", 65000)
]
columns = ["name", "department", "salary"]
df = spark.createDataFrame(data, columns)

# 3. Register DataFrame as a temporary view
df.createOrReplaceTempView("employees")

# 4. Execute SQL Query
result_df = spark.sql("""
    SELECT department, AVG(salary) as avg_salary
    FROM employees
    WHERE salary > 70000
    GROUP BY department
""")

# 5. Show results
result_df.show()

# Stop session when done
spark.stop()

Explanation: First, we create a SparkSession. We then simulate a dataset using createDataFrame. Crucially, we call createOrReplaceTempView to make the DataFrame accessible via SQL syntax under the name "employees". Finally, spark.sql() runs the aggregation query, returning a new DataFrame which we display using show().

Common mistakes

• Forgetting to register views: You cannot query a DataFrame directly with SQL unless it is registered as a temporary view first.
• Using reserved keywords: Column names like order or group must be escaped with backticks (`order`) in SQL strings.
• Ignoring partitioning: Not partitioning large tables by common filter columns (like date) leads to full scans and poor performance.
• Collecting too much data: Calling .collect() on a huge result set brings all data to the driver node, causing OutOfMemory errors. Use .show() or write to storage instead.

When to use it

Compare Spark SQL with Pandas for small data and Hive for legacy Hadoop setups.

Practice

Guided Exercise: Modify the example above to find the employee with the highest salary in each department. Hint: Use ROW_NUMBER() window function or a subquery with MAX().

Challenge: Write a query to count how many employees are in each department, but only include departments with more than one employee. Expected output should show counts per department where count > 1.

Quick check

Question: Why do we need to call createOrReplaceTempView before running spark.sql()?
Answer: Because Spark SQL operates on logical tables defined in the catalog. A DataFrame exists in memory but isn't visible to the SQL engine until it is registered as a view with a specific name.

Summary

Spark SQL bridges the gap between relational database skills and big data engineering. By treating DataFrames as virtual tables, you can leverage powerful distributed processing while writing familiar SQL queries. Always remember to manage memory carefully and optimize your data layout for best results.