Up until now, we've spent a lot of time in the Scala-native side of Spark, using the Dataset and DataFrame APIs. But let's be honest: sometimes, writing a complex join or a nested aggregation in the DSL feels like you're fighting the language. That's where Spark SQL comes in. It allows us to write raw SQL queries against our data while still staying inside our Scala application.

Setting up the SparkSession

To use Spark SQL, everything revolves around the SparkSession. You've seen this before, but remember that the session is your gateway to the SQL engine. For this example, let's imagine we have a CSV file called web_logs.csv containing website traffic data: IP addresses, page paths, timestamps, and HTTP status codes.

val spark = SparkSession.builder()
  .appName("LogAnalyzer")
  .master("local[*]")
  .getOrCreate()

Loading the logs into a DataFrame

Before we can run SQL, we need data in the system. I'll load the CSV into a DataFrame. I'm using inferSchema here because it's faster for small prototypes, though in a production pipeline, I'd strongly recommend defining a proper case class or schema to avoid the overhead of a second pass over the data.

val logsDF = spark.read
  .option("header", "true")
  .option("inferSchema", "true")
  .csv("web_logs.csv")

The "missing view" mistake

Here is where I usually trip up when I'm switching back and forth between the DataFrame API and SQL. I'll try to run a query to find all the 404 errors on our site.

// This will fail!
val errors = spark.sql("SELECT * FROM logsDF WHERE status = 404")
errors.show()

If you run this, Spark will throw an AnalysisException telling you that the table logsDF doesn't exist. It's a common point of confusion: Spark SQL doesn't automatically know about the Scala variables in your code. The SQL engine operates on a catalog of tables and views, not your JVM variable names.

To fix this, I have to register the DataFrame as a temporary view. This creates a pointer in the Spark catalog that the SQL engine can actually see.

logsDF.createOrReplaceTempView("web_logs")

// Now this works perfectly
val errors = spark.sql("SELECT * FROM web_logs WHERE status = 404")
errors.show()

Aggregating the "broken" pages

Now that we have a view, let's do something useful. I want to find the top 5 pages that are returning 404s so the dev team knows what to fix first. I could do this with groupBy and count in the Scala API, but a SQL string is much more readable for this specific logic.

val brokenPages = spark.sql("""
  SELECT page_path, COUNT(*) as error_count
  FROM web_logs
  WHERE status = 404
  GROUP BY page_path
  ORDER BY error_count DESC
  LIMIT 5
""")

brokenPages.show()

I used a triple-quoted string (""") here. It's a Scala trick that lets me write the SQL over multiple lines without needing concatenation, which keeps the query legible. You'll notice that spark.sql() returns a DataFrame. This is the beauty of the system: you can start with a DataFrame, switch to SQL for a complex transformation, and then go back to using Scala functions for the final output.

📋 Practical Task

Analyzing Product Sales with Spark SQL

You have a dataset called sales_data.csv with the following columns: product_id (Integer), category (String), amount (Double), and sale_date (String).

Write a Scala program that:

• Initializes a SparkSession.
• Reads the sales_data.csv file into a DataFrame.
• Registers that DataFrame as a temporary view named sales.
• Uses spark.sql() to calculate the total revenue (sum of amount) for each category, but only for categories that have generated more than $1,000 in total sales.
• Orders the result by total revenue in descending order.
• Prints the resulting DataFrame to the console.