Learn Apache Spark - Advanced SQL & Analytics

#Introduction

You've already mastered basic SQL: select, join, aggregation, and windowing. Episode 15 raises the bar: complex queries, custom functions, and integration with modern lakehouse systems. This is the skill that separates ordinary SQL analysts from analytics engineers.

In the real world, queries are never as simple as the ones in exercise books. You need CTEs to break up logic, subqueries for nested filtering, UDFs for domain-specific logic, and table formats that support transactions and time travel. All of these exist in Spark.

This episode covers four topics: CTEs and nested queries, UDFs and UDAFs, spatial and graph analytics, and integration with Delta Lake and Iceberg.

#CTEs, Subqueries, and Nested Queries

#CTEs for Layered Queries

A CTE (Common Table Expression) breaks a big query into named, reusable blocks. This makes complex logic easy to read and debug:

WITH transaksi_valid AS (
    SELECT * FROM transaksi WHERE status = 'selesai'
),
per_kota AS (
    SELECT kota, SUM(jumlah) AS total
    FROM transaksi_valid
    GROUP BY kota
)
SELECT kota, total, RANK() OVER (ORDER BY total DESC) AS peringkat
FROM per_kota;

WITH transaksi_valid AS (...) defines the first CTE used by the second CTE. Spark evaluates CTEs efficiently and can avoid re-reading data if a result is used multiple times.

#Subqueries and Nested Queries

Spark SQL supports correlated subqueries — subqueries that reference columns from the outer query:

SELECT nama, jumlah
FROM transaksi t
WHERE jumlah > (
    SELECT AVG(jumlah)
    FROM transaksi
    WHERE kota = t.kota
);

The subquery above compares each transaction with the average within its own city. Spark handles this with windows and joins automatically — however, for very large data, correlated subqueries can be expensive and are often better rewritten as a window function or self join.

#UDFs and UDAFs

#UDFs in Spark SQL

Logic not available in built-in functions can be created as a UDF (User-Defined Function). A simple example with PySpark:

Registering a UDF

from pyspark.sql import functions as F
 
def kategori_stok(stok):
    if stok == 0:
        return "habis"
    if stok < 10:
        return "menipis"
    return "cukup"
 
spark.udf.register("kategori_stok", kategori_stok)

Then use it in SQL: SELECT kategori_stok(stok) FROM produk. An important note: Python UDFs bypass Catalyst and force serialization, so for large data make sure the UDF is really needed — often the same logic can be written with F.when.

#UDAFs for Custom Aggregations

A UDAF (User-Defined Aggregate Function) combines many rows into a single value. In Scala and Java, implement an Aggregator with a buffer that can be merged in parallel:

zero → merge(b, input) → merge(b1, b2) → finish(b)

UDAFs can combine results across partitions, keeping the aggregation distributed. Implementation details are covered more deeply in episode 17.

#Spatial, Graph Processing, and Advanced Windowing

#Spatial Analytics with Sedona

Spatial analysis (distance, bounding boxes, geometry-based joins) isn't in Spark's core. The ecosystem provides Sedona (formerly GeoSpark) as a third-party library that adds geometry types and spatial functions:

ST_Distance, ST_Contains, ST_Intersects, ST_Buffer

For lightweight needs without a library, a combination of F.atan2, F.sin, and other trigonometry can compute haversine distance — however, for production, a specialized library is far more reliable.

#Graph Processing with GraphX

GraphX is Spark's graph API for network analysis: finding shortest paths, PageRank, and communities. It fits social recommendations, fraud detection, and transportation analysis. It's introduced in episode 21 as part of the Spark library ecosystem.

#Advanced Windowing

Combine windows with complex logic:

Window with a custom range

from pyspark.sql.window import Window
from pyspark.sql import functions as F
 
w = Window.partitionBy("kota") \
    .orderBy("tanggal") \
    .rowsBetween(Window.unboundedPreceding, Window.currentRow)
 
hasil = df.withColumn("total_kumulatif", F.sum("jumlah").over(w))

rowsBetween(Window.unboundedPreceding, Window.currentRow) creates a cumulative running total per city — the basic pattern for metrics like revenue to-date.

#Integration with Delta Lake and Iceberg

#Delta Lake

Delta Lake adds an ACID transaction layer on top of Parquet: schema enforcement, time travel, and merge (upsert) — capabilities plain Parquet doesn't have:

MERGE INTO transaksi_delta AS target
USING pembaruan AS source
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET jumlah = source.jumlah
WHEN NOT MATCHED THEN INSERT *;

MERGE INTO ... USING ... performs an atomic upsert — very useful for CDC pipelines and SCD (slowly changing dimensions). Time travel allows querying older versions of the data: SELECT * FROM t VERSION AS OF 10.

#Apache Iceberg

Iceberg is an alternative open table format focused on snapshot consistency, hidden partitioning, and large-scale capability:

CREATE TABLE produk_iceberg (
  id INT, nama STRING, harga DECIMAL(10,2)
) USING iceberg
PARTITIONED BY (nama);

The main difference from Delta: Iceberg stores the partition list explicitly (hidden partitioning) so queries automatically use pruning without you naming the partition column. Both Delta and Iceberg turn a data lake into a lakehouse — a data lake with warehouse properties.

#Conclusion

Episode 15 expands your analytics toolkit: CTEs and subqueries tidy up complex queries, UDFs and UDAFs add custom logic, spatial and graph libraries handle specialized domains, and Delta Lake and Iceberg bring transactions and time travel to the data lake.

Key takeaways:

• CTEs split complex queries into readable named blocks.
• Correlated subqueries can be replaced by window functions for efficiency.
• Python UDFs bypass Catalyst — use them only when necessary.
• Delta provides ACID, time travel, and merge; Iceberg provides hidden partitioning.
• Open table formats should be chosen early in the data architecture.

In the next episode, episode 16, we'll discuss save and restore, checkpointing, and fault tolerance — checkpointing in Structured Streaming, recovery behavior on failure, state management for streaming jobs, and best practices for long-running streaming applications.