Using Spark SQL in PySpark for Distributed Data Analysis

In our previous tutorials, you've built a solid foundation in PySpark fundamentals—from setting up your development environment to working with Resilient Distributed Datasets and DataFrames. You've seen how Spark's distributed computing power lets you process massive datasets that would overwhelm traditional tools.

But here's something that might surprise you: you don't need to choose between SQL and Python when working with Spark.

Whether you're a data analyst who thinks in SQL queries or a Python developer comfortable with DataFrame operations, Spark SQL gives you the flexibility to use whichever approach feels natural for each task. It's the same powerful distributed engine underneath, just accessed through different interfaces.

Spark SQL is Spark's module for working with structured data using SQL syntax. It sits on top of the same DataFrame API you've already learned, which means every SQL query you write gets the same automatic optimizations from Spark's Catalyst engine. The performance is identical, so you're just choosing the syntax that makes your code more readable and maintainable.

To demonstrate these concepts, we'll work with real U.S. Census data spanning four decades (links to download: 1980; 1990; 2000; 2010). These datasets provide rich demographic information that naturally leads to the kinds of analytical questions where SQL really shines: filtering populations, calculating demographic trends, and combining data across multiple time periods.

By the end of this tutorial, you'll know how to:

• Register DataFrames as temporary views that can be queried with standard SQL
• Write SQL queries using spark.sql() to filter, transform, and aggregate distributed data
• Combine multiple datasets using UNION ALL for comprehensive historical analysis
• Build complete SQL pipelines that layer filtering, grouping, and sorting operations
• Choose between SQL and DataFrame APIs based on readability and team preferences

It might not be obvious at this stage, but the reality is that these approaches work together seamlessly. You might load and clean data using DataFrame methods, then switch to SQL for complex aggregations, and finish by converting results back to a pandas DataFrame for visualization. Spark SQL gives you that flexibility.

Why Spark SQL Matters for Data Professionals

SQL is the language of data work and serves as the bridge between business questions and data answers. Whether you're collaborating with analysts, building reports for stakeholders, or exploring datasets yourself, Spark SQL provides a familiar and powerful way to query distributed data.

The Universal Data Language

Think about your typical data team. You might have analysts who live in SQL, data scientists comfortable with Python, and engineers who prefer programmatic approaches. Spark SQL lets everyone contribute using their preferred tools while working with the same underlying data and getting identical performance.

A business analyst can write:

SELECT year, AVG(total) as avg_population
FROM census_data
WHERE age < 18
GROUP BY year

While a Python developer writes:

census_df.filter(col("age") < 18)
         .groupBy("year")
         .agg(avg("total")
         .alias("avg_population"))

Both approaches compile to exactly the same optimized execution plan. The choice becomes about readability, team preferences, and the specific task at hand.

Same Performance, Different Expression

Here's what makes Spark SQL particularly valuable: there's no performance penalty for choosing SQL over DataFrames. Both interfaces use Spark's Catalyst optimizer, which means your SQL queries get the same automatic optimizations—predicate pushdown, column pruning, join optimization—that make DataFrame operations so efficient.

This performance equivalence is important to note because it means you can choose your approach based on clarity and maintainability rather than speed concerns. Complex joins might be clearer in SQL, while programmatic transformations might be easier to express with DataFrame methods.

When SQL Shines

SQL particularly excels in scenarios where you need to:

• Perform complex aggregations across multiple grouping levels
• Write queries that non-programmers can read and modify
• Combine data from multiple sources with unions and joins
• Express business logic in a declarative, readable format

For the census analysis we'll build throughout this tutorial, SQL provides an intuitive way to ask questions like "What was the average population growth across age groups between decades?" The resulting queries read almost like natural language, making them easier to review, modify, and share with stakeholders.

From DataFrames to SQL Views

The bridge between DataFrames and SQL queries is the concept of views. A view gives your DataFrame a name that SQL queries can reference, just like a table in a traditional SQL database. Once registered, you can query that view using standard SQL syntax through Spark's spark.sql() method.

Let's start by setting up our environment and loading the census data we'll use throughout this tutorial.

Setting Up and Loading Census Data

import os
import sys
from pyspark.sql import SparkSession

# Ensure PySpark uses the same Python interpreter
os.environ["PYSPARK_PYTHON"] = sys.executable

# Create SparkSession
spark = SparkSession.builder \
    .appName("CensusSQL_Analysis") \
    .master("local[*]") \
    .config("spark.driver.memory", "2g") \
    .getOrCreate()

# Load the 2010 census data
df = spark.read.json("census_2010.json")

# Take a quick look at the data structure
df.show(5)

+---+-------+-------+-------+----+
|age|females|  males|  total|year|
+---+-------+-------+-------+----+
|  0|1994141|2085528|4079669|2010|
|  1|1997991|2087350|4085341|2010|
|  2|2000746|2088549|4089295|2010|
|  3|2002756|2089465|4092221|2010|
|  4|2004366|2090436|4094802|2010|
+---+-------+-------+-------+----+
only showing top 5 rows

The data structure is straightforward: each row represents population counts for a specific age in 2010, broken down by gender. This gives us demographic information that’s ideal for demonstrating SQL operations.

Creating Your First Temporary View

Now let's register the df DataFrame as a temporary view so we can query it with SQL:

# Register the DataFrame as a temporary view
df.createOrReplaceTempView("census2010")

# Now we can query it using SQL!
result = spark.sql("SELECT * FROM census2010 LIMIT 5")
result.show()

+---+-------+-------+-------+----+
|age|females|  males|  total|year|
+---+-------+-------+-------+----+
|  0|1994141|2085528|4079669|2010|
|  1|1997991|2087350|4085341|2010|
|  2|2000746|2088549|4089295|2010|
|  3|2002756|2089465|4092221|2010|
|  4|2004366|2090436|4094802|2010|
+---+-------+-------+-------+----+

That's it! With createOrReplaceTempView("census2010"), we've made our df DataFrame available for SQL queries. The name census2010 now acts like a table name in any SQL database.

Did you notice the OrReplace part of the method name? You can create as many temporary views as you want, as long as they have different names. The "replace" only happens if you try to create a view with a name that already exists. Think of it like saving files: you can save multiple files on your computer, but saving a new file with an existing filename overwrites the old one.

Understanding the View Connection

You should know that views don't copy your data. The view census2010 is just a reference to the underlying df DataFrame. When we query the view, we're actually querying the same distributed dataset with all of Spark's optimizations intact.

Let's verify this connection by checking that our view contains the expected data:

# Count total records through SQL 
sql_count = spark.sql("SELECT COUNT(*) as record_count FROM census2010")
sql_count.show()

# Compare with DataFrame count method
df_count = df.count()
print(f"DataFrame count: {df_count}")

+------------+
|record_count|
+------------+
|         101|
+------------+

DataFrame count: 101

Both approaches return the same count (101 age groups from 0 to 100, inclusive), confirming that the view and DataFrame represent identical data. The SQL query and DataFrame method are just different ways to access the same underlying distributed dataset.

Global Temporary Views: Sharing Data Across Sessions

While regular temporary views work well within a single SparkSession, sometimes you need views that persist across multiple sessions or can be shared between different notebooks. Global temporary views solve this problem by creating views that live beyond individual session boundaries.

The key differences between temporary and global temporary views:

• Regular temporary views exist only within your current SparkSession and disappear when it ends
• Global temporary views persist across multiple SparkSession instances and can be accessed by different applications running on the same Spark cluster

When Global Temporary Views Are Useful

Global temporary views become valuable in scenarios like:

• Jupyter notebook workflows where you might restart kernels but want to preserve processed data
• Shared analysis environments where multiple team members need access to the same transformed datasets
• Multi-step ETL processes where intermediate results need to persist between different job runs
• Development and testing where you want to avoid reprocessing large datasets repeatedly

Creating and Using Global Temporary Views

Let's see how global temporary views work in practice:

# Create a global temporary view from our census data
df.createGlobalTempView("census2010_global")

# Access the global view (note the special global_temp database prefix)
global_result = spark.sql("SELECT * FROM global_temp.census2010_global LIMIT 5")
global_result.show()

# Verify both views exist
print("Regular temporary views:")
spark.sql("SHOW TABLES").show()

print("Global temporary views:")
spark.sql("SHOW TABLES IN global_temp").show()

+---+-------+-------+-------+----+
|age|females|  males|  total|year|
+---+-------+-------+-------+----+
|  0|1994141|2085528|4079669|2010|
|  1|1997991|2087350|4085341|2010|
|  2|2000746|2088549|4089295|2010|
|  3|2002756|2089465|4092221|2010|
|  4|2004366|2090436|4094802|2010|
+---+-------+-------+-------+----+

Regular temporary views:
+---------+----------+-----------+
|namespace| tableName|isTemporary|
+---------+----------+-----------+
|         |census2010|       true|
+---------+----------+-----------+

Global temporary views:
+-----------+-----------------+-----------+
|  namespace|        tableName|isTemporary|
+-----------+-----------------+-----------+
|global_temp|census2010_global|       true|
|           |       census2010|       true|
+-----------+-----------------+-----------+

Notice that both views appear in the global_temp database, but take a look at the namespace column. Only census2010_global has global_temp as its namespace, which makes it a true global view. The census2010 view (with empty namespace) is still just a regular temporary view that happens to appear in this listing. The namespace tells you which type of view you're actually looking at.

Important Considerations

Namespace Requirements: Global temporary views must be accessed through the global_temp database. You cannot query them directly by name like regular temporary views.

Cleanup: Global temporary views persist until explicitly dropped or the Spark cluster shuts down. Remember to clean up when you're finished:

# Drop the global temporary view when done
spark.sql("DROP VIEW global_temp.census2010_global")

Resource Management: Since global temporary views consume cluster resources across sessions, use them with caution in shared environments.

For most analytical work in this tutorial, regular temporary views provide the functionality you need. Global temporary views are a powerful tool to keep in mind for more complex, multi-session workflows.

Core SQL Operations in Spark

Now that you understand how views connect DataFrames to SQL, let's explore the core SQL operations you'll use for data analysis. We'll build from simple queries to more complex analytical operations.

Filtering with WHERE Clauses

The WHERE clause lets you filter rows based on conditions, just like the .filter() method in DataFrames:

# Find age groups with populations over 4.4 million
large_groups = spark.sql("""
    SELECT age, total
    FROM census2010
    WHERE total > 4400000
""")
large_groups.show()

+---+-------+
|age|  total|
+---+-------+
| 16|4410804|
| 17|4451147|
| 18|4454165|
| 19|4432260|
| 20|4411138|
| 45|4449309|
| 46|4521475|
| 47|4573855|
| 48|4596159|
| 49|4593914|
| 50|4585941|
| 51|4572070|
| 52|4529367|
| 53|4449444|
+---+-------+

The results reveal two distinct population peaks in the 2010 census data:

• Late teens/early twenties (ages 16-20): Early Millennials, likely the children of Gen X parents who were in their prime childbearing years during the 1990s
• Middle-aged adults (ages 45-53): The tail end of the Baby Boom generation, born in the late 1950s and early 1960s

Now let's explore how gender demographics vary across different life stages. We can combine multiple filtering conditions using BETWEEN, AND, and OR to compare teenage populations with younger working-age adults where gender ratios have shifted:

# Analyze gender demographics: teenagers vs younger adults with female majorities
gender_demographics = spark.sql("""
    SELECT age, males, females
    FROM census2010
    WHERE (age BETWEEN 13 AND 19)
       OR (age BETWEEN 20 AND 40 AND females > males)
""")
gender_demographics.show()

+---+-------+-------+
|age|  males|females|
+---+-------+-------+
| 13|2159943|2060100|
| 14|2195773|2089651|
| 15|2229339|2117689|
| 16|2263862|2146942|
| 17|2285295|2165852|
| 18|2285990|2168175|
| 19|2272689|2159571|
| 34|2020204|2025969|
| 35|2018080|2029981|
| 36|2018137|2036269|
| 37|2022787|2045241|
| 38|2032469|2056401|
| 39|2046398|2070132|
| 40|2061474|2085229|
+---+-------+-------+

This query demonstrates several key SQL filtering techniques:

What we're comparing:

• Teenagers (ages 13-19): Where males consistently outnumber females
• Younger adults (ages 20-40): But only those where females outnumber males

Key insight: Notice the dramatic shift around age 34; this is where we transition from male-majority to female-majority populations in the 2010 census data.

SQL techniques in action:

• BETWEEN defines our age ranges
• AND combines age and gender conditions
• OR lets us examine two distinct demographic groups in one query

To better understand these gender patterns, let's calculate the actual female-to-male ratios using SQL expressions.

Creating Calculated Fields with SQL Expressions

SQL lets you create new columns using mathematical expressions, functions, and conditional logic. Let's calculate precise gender ratios and differences to quantify the patterns we observed above:

# Calculate precise gender ratios for the transition ages we identified
gender_analysis = spark.sql("""
    SELECT
        age,
        males,
        females,
        ROUND((females * 1.0 / males), 4) AS female_male_ratio,
        (females - males) AS gender_gap
    FROM census2010
    WHERE age BETWEEN 30 AND 40
""")
gender_analysis.show()

+---+-------+-------+-----------------+----------+
|age|  males|females|female_male_ratio|gender_gap|
+---+-------+-------+-----------------+----------+
| 30|2083642|2065883|           0.9915|    -17759|
| 31|2055863|2043293|           0.9939|    -12570|
| 32|2034632|2027525|           0.9965|     -7107|
| 33|2023579|2022761|           0.9996|      -818|
| 34|2020204|2025969|           1.0029|      5765|
| 35|2018080|2029981|           1.0059|     11901|
| 36|2018137|2036269|           1.0090|     18132|
| 37|2022787|2045241|           1.0111|     22454|
| 38|2032469|2056401|           1.0118|     23932|
| 39|2046398|2070132|           1.0116|     23734|
| 40|2061474|2085229|           1.0115|     23755|
+---+-------+-------+-----------------+----------+

Key SQL techniques:

• ROUND() function makes the ratios easier to read
• 1.0 ensures floating-point division rather than integer division
• Mathematical expressions create new calculated columns

Demographic insights:

• The female-to-male ratio crosses 1.0 exactly at age 34
• Gender gap flips from negative (more males) to positive (more females) at the same age
• This confirms the demographic transition we identified in our previous query

To see where these gender ratios become most extreme across all ages, let's use ORDER BY to rank them systematically.

Sorting Results with ORDER BY

The ORDER BY clause arranges your results in meaningful ways, letting you discover patterns by ranking data from highest to lowest values:

# Find ages with the biggest gender gaps
largest_gaps = spark.sql("""
    SELECT
        age,
        total,
        (females - males) AS gender_gap,
        ROUND((females * 1.0 / males), 2) AS female_male_ratio
    FROM census2010
    ORDER BY female_male_ratio DESC
    LIMIT 15
""")
largest_gaps.show()

+---+------+----------+-----------------+
|age| total|gender_gap|female_male_ratio|
+---+------+----------+-----------------+
| 99| 30285|     21061|             5.57|
|100| 60513|     41501|             5.37|
| 98| 44099|     27457|             4.30|
| 97| 65331|     37343|             3.67|
| 96| 96077|     52035|             3.36|
| 95|135309|     69981|             3.14|
| 94|178870|     87924|             2.93|
| 93|226364|    106038|             2.76|
| 92|284857|    124465|             2.55|
| 91|357058|    142792|             2.33|
| 90|439454|    160632|             2.15|
| 89|524075|    177747|             2.03|
| 88|610415|    193881|             1.93|
| 87|702325|    207907|             1.84|
| 86|800232|    219136|             1.75|
+---+------+----------+-----------------+

The data reveals a fascinating demographic pattern: while younger age groups tend to have more males, the very oldest age groups show significantly more females. This reflects longer female life expectancy, with the gender gap becoming dramatically pronounced after age 90.

Key insights:

• At age 99, there are over 5.5 females for every male
• The female advantage increases dramatically with age
• Even at age 86, women outnumber men by 75%

SQL technique: ORDER BY female_male_ratio DESC arranges results from highest to lowest ratio, revealing the progressive impact of differential life expectancy across the very elderly population. We could use ASC instead of DESC to sort from lowest to highest ratios.

Now let's step back and use SQL aggregations to understand the overall demographic picture of the 2010 census data.

Aggregations and Grouping

SQL's grouping and aggregation capabilities let you summarize data across categories and compute statistics that reveal broader patterns in your dataset.

Basic Aggregations Across the Dataset

Let's start with simple aggregations across the entire census dataset:

# Calculate overall population statistics
population_stats = spark.sql("""
    SELECT
        SUM(total) as total_population,
        ROUND(AVG(total), 0) as avg_per_age_group,
        MIN(total) as smallest_age_group,
        MAX(total) as largest_age_group,
        COUNT(*) as age_groups_count
    FROM census2010
""")
population_stats.show()

+----------------+-----------------+------------------+-----------------+----------------+
|total_population|avg_per_age_group|smallest_age_group|largest_age_group|age_groups_count|
+----------------+-----------------+------------------+-----------------+----------------+
|       312247116|        3091556.0|             30285|          4596159|             101|
+----------------+-----------------+------------------+-----------------+----------------+

These statistics provide immediate insight into the 2010 U.S. population: approximately 308.7 million people distributed across 101 age groups (0-100 years), with significant variation in age group sizes. The ROUND(AVG(total), 0) function rounds the average to the nearest whole number, making it easier to interpret.

While overall statistics are useful, the real power of SQL aggregations emerges when we group data into meaningful categories.

More Sophisticated Analysis with GROUP BY and CASE

The GROUP BY clause lets us organize data into categories and perform calculations on each group separately, while CASE statements create those categories using conditional logic similar to if-else statements in programming.

The CASE statement evaluates conditions (in order) and assigns each row to the first matching category. Note that we have to repeat the entire CASE statement in both the SELECT and GROUP BY clauses. This is a SQL requirement when grouping by calculated fields.

Now let's create age ranges and analyze their demographic patterns:

# Analyze population distribution by detailed life stages
life_stage_analysis = spark.sql("""
    SELECT
        CASE
            WHEN age < 20 THEN 'Youth (0-19)'
            WHEN age < 40 THEN 'Young Adults (20-39)'
            WHEN age < 65 THEN 'Older Adults (40-64)'
            ELSE 'Seniors (65+)'
        END as life_stage,
        SUM(total) as total_population,
        COUNT(*) as age_groups,
        ROUND(AVG(total), 0) as avg_per_age_group,
        ROUND(AVG(females * 1.0 / males), 3) as avg_female_male_ratio
    FROM census2010
    GROUP BY
        CASE
            WHEN age < 20 THEN 'Youth (0-19)'
            WHEN age < 40 THEN 'Young Adults (20-39)'
            WHEN age < 65 THEN 'Older Adults (40-64)'
            ELSE 'Seniors (65+)'
        END
    ORDER BY avg_female_male_ratio ASC
""")
life_stage_analysis.show()

+--------------------+----------------+----------+-----------------+---------------------+
|          life_stage|total_population|age_groups|avg_per_age_group|avg_female_male_ratio|
+--------------------+----------------+----------+-----------------+---------------------+
|        Youth (0-19)|        84042596|        20|        4202130.0|                0.955|
|Young Adults (20-39)|        84045235|        20|        4202262.0|                0.987|
|Older Adults (40-64)|       103365001|        25|        4134600.0|                1.047|
|       Seniors (65+)|        40794284|        36|        1133175.0|                2.023|
+--------------------+----------------+----------+-----------------+---------------------+

This analysis reveals important demographic insights:

• Youth (0-19) and Young Adults (20-39) have similar total populations and high averages per age group, reflecting larger birth cohorts in the 1990s and 2000s
• Older Adults (40-64) represent the largest single segment while maintaining near-balanced gender ratios
• Seniors (65+) show dramatically higher female-male ratios, confirming the female longevity advantage we observed earlier

The results are sorted by gender ratio (ascending) to highlight the progression from male-majority youth to female-majority seniors.

Now let's expand our analysis by combining census data from multiple decades to examine demographic trends over time.

Combining Multiple Datasets

One of SQL's greatest strengths is combining data from multiple sources. Let's load census data from multiple decades to analyze demographic trends over time.

Loading and Registering Multiple Datasets

# Load census data from four different decades
df_1980 = spark.read.json("census_1980.json")
df_1990 = spark.read.json("census_1990.json")
df_2000 = spark.read.json("census_2000.json")
df_2010 = spark.read.json("census_2010.json")

# Register each as a temporary view
df_1980.createOrReplaceTempView("census1980")
df_1990.createOrReplaceTempView("census1990")
df_2000.createOrReplaceTempView("census2000")
df_2010.createOrReplaceTempView("census2010")

# Verify our datasets loaded correctly by checking each one
for decade, view_name in [("1980", "census1980"), ("1990", "census1990"), 
                         ("2000", "census2000"), ("2010", "census2010")]:
    result = spark.sql(f"""
        SELECT year, COUNT(*) as age_groups, SUM(total) as total_pop
        FROM {view_name}
        GROUP BY year
    """)
    print(f"Dataset {decade}:")
    result.show()

Dataset 1980:
+----+----------+---------+
|year|age_groups|total_pop|
+----+----------+---------+
|1980|       101|230176361|
+----+----------+---------+

Dataset 1990:
+----+----------+---------+
|year|age_groups|total_pop|
+----+----------+---------+
|1990|       101|254506647|
+----+----------+---------+

Dataset 2000:
+----+----------+---------+
|year|age_groups|total_pop|
+----+----------+---------+
|2000|       101|284594395|
+----+----------+---------+

Dataset 2010:
+----+----------+---------+
|year|age_groups|total_pop|
+----+----------+---------+
|2010|       101|312247116|
+----+----------+---------+

Now that we have all four decades loaded as separate views, let's combine them into a single dataset for comprehensive analysis.

Combining Datasets with UNION ALL

The UNION ALL operation stacks datasets with identical schemas on top of each other. We use UNION ALL instead of UNION because we want to keep all rows, including any duplicates (though there shouldn't be any in census data):

# Combine all four decades of census data
combined_census = spark.sql("""
    SELECT * FROM census1980
    UNION ALL
    SELECT * FROM census1990
    UNION ALL
    SELECT * FROM census2000
    UNION ALL
    SELECT * FROM census2010
""")

# Register the combined data as a new view
combined_census.createOrReplaceTempView("census_all_decades")

# Verify the combination worked
decades_summary = spark.sql("""
    SELECT
        year,
        COUNT(*) as age_groups,
        SUM(total) as total_population
    FROM census_all_decades
    GROUP BY year
    ORDER BY year
""")
decades_summary.show()

+----+----------+----------------+
|year|age_groups|total_population|
+----+----------+----------------+
|1980|       101|       230176361|
|1990|       101|       254506647|
|2000|       101|       284594395|
|2010|       101|       312247116|
+----+----------+----------------+

Perfect! Our combined dataset now contains 404 rows (101 age groups × 4 decades) representing three decades of U.S. demographic change. The population growth from 230 million in 1980 to 312 million in 2010 reflects both natural increase and immigration patterns.

With our combined dataset ready, we can now analyze how different demographic groups have evolved across these four decades.

Multi-Decade Trend Analysis

With four decades of data combined, we can now ask more sophisticated questions about demographic trends:

# Analyze how different age groups have changed over time
age_group_trends = spark.sql("""
    SELECT
        CASE
            WHEN age < 5 THEN '0-4'
            WHEN age < 18 THEN '5-17'
            WHEN age < 35 THEN '18-34'
            WHEN age < 65 THEN '35-64'
            ELSE '65+'
        END as age_category,
        year,
        SUM(total) as population,
        ROUND(AVG(females * 1.0 / males), 3) as avg_gender_ratio
    FROM census_all_decades
    GROUP BY
        CASE
            WHEN age < 5 THEN '0-4'
            WHEN age < 18 THEN '5-17'
            WHEN age < 35 THEN '18-34'
            WHEN age < 65 THEN '35-64'
            ELSE '65+'
        END,
        year
    ORDER BY age_category, year
""")
age_group_trends.show()

+------------+----+----------+----------------+
|age_category|year|population|avg_gender_ratio|
+------------+----+----------+----------------+
|         0-4|1980|  16685045|           0.955|
|         0-4|1990|  19160197|           0.954|
|         0-4|2000|  19459583|           0.955|
|         0-4|2010|  20441328|           0.958|
|       18-34|1980|  68234725|           1.009|
|       18-34|1990|  70860606|           0.986|
|       18-34|2000|  67911403|           0.970|
|       18-34|2010|  72555765|           0.976|
|       35-64|1980|  71401383|           1.080|
|       35-64|1990|  85965068|           1.062|
|       35-64|2000| 108357709|           1.046|
|       35-64|2010| 123740896|           1.041|
|        5-17|1980|  47871989|           0.958|
|        5-17|1990|  46786024|           0.951|
|        5-17|2000|  53676011|           0.950|
|        5-17|2010|  54714843|           0.955|
|         65+|1980|  25983219|           2.088|
|         65+|1990|  31734752|           2.301|
|         65+|2000|  35189689|           2.334|
|         65+|2010|  40794284|           2.023|
+------------+----+----------+----------------+

This trend analysis reveals several fascinating patterns:

• Youngest populations (0-4) grew steadily from 16.7M to 20.4M, indicating sustained birth rates across the decades
• School-age children (5-17) showed more variation, declining in the 1990s before recovering in the 2000s
• Young adults (18-34) fluctuated significantly, reflecting different generational sizes moving through this age range
• Middle-aged adults (35-64) grew dramatically from 71M to 124M, capturing the Baby Boomer generation aging into their peak years
• Seniors (65+) expanded consistently, but interestingly, their gender ratios became less extreme over time

Building Complete SQL Pipelines

Real-world data analysis often requires combining multiple operations: filtering data, calculating new metrics, grouping results, and presenting them in meaningful order. SQL excels at expressing these multi-step analytical workflows in readable, declarative queries.

Layered Analysis: Demographics and Trends

Let's build a comprehensive analysis that examines how gender balance has shifted across different life stages and decades:

# Complex pipeline analyzing gender balance trends
gender_balance_pipeline = spark.sql("""
    SELECT
        year,
        CASE
            WHEN age < 18 THEN '1: Minors'
            WHEN age < 65 THEN '2: Adults'
            ELSE '3: Senior'
        END as life_stage,
        SUM(total) as total_population,
        SUM(females) as total_females,
        SUM(males) as total_males,
        ROUND(SUM(females) * 100.0 / SUM(total), 2) as female_percentage,
        ROUND(SUM(females) * 1.0 / SUM(males), 4) as female_male_ratio
    FROM census_all_decades
    WHERE total > 100000  -- Filter out very small populations (data quality)
    GROUP BY
        year,
        CASE
            WHEN age < 18 THEN '1: Minors'
            WHEN age < 65 THEN '2: Adults'
            ELSE '3: Senior'
        END
    HAVING SUM(total) > 1000000  -- Only include substantial population groups
    ORDER BY year, life_stage
""")
gender_balance_pipeline.show()

+----+----------+----------------+-------------+-----------+-----------------+-----------------+
|year|life_stage|total_population|total_females|total_males|female_percentage|female_male_ratio|
+----+----------+----------------+-------------+-----------+-----------------+-----------------+
|1980| 1: Minors|        64557034|     31578370|   32978664|            48.92|           0.9575|
|1980| 2: Adults|       139636108|     71246317|   68389791|            51.02|           1.0418|
|1980| 3: Senior|        25716916|     15323978|   10392938|            59.59|           1.4745|
|1990| 1: Minors|        65946221|     32165885|   33780336|            48.78|           0.9522|
|1990| 2: Adults|       156825674|     79301361|   77524313|            50.57|           1.0229|
|1990| 3: Senior|        31393100|     18722713|   12670387|            59.64|           1.4777|
|2000| 1: Minors|        73135594|     35657335|   37478259|            48.76|           0.9514|
|2000| 2: Adults|       176269112|     88612115|   87656997|            50.27|           1.0109|
|2000| 3: Senior|        34966635|     20469643|   14496992|            58.54|           1.4120|
|2010| 1: Minors|        75156171|     36723522|   38432649|            48.86|           0.9555|
|2010| 2: Adults|       196296661|     98850959|   97445702|            50.36|           1.0144|
|2010| 3: Senior|        40497979|     22905157|   17592822|            56.56|           1.3020|
+----+----------+----------------+-------------+-----------+-----------------+-----------------+

This pipeline demonstrates several advanced SQL techniques:

• WHERE clause filtering removes data quality issues
• CASE statements create meaningful categories
• Multiple aggregations compute different perspectives on the same data
• HAVING clause filters grouped results based on aggregate conditions
• ROUND function makes percentages and ratios easier to read
• ORDER BY presents results in logical sequence

The results reveal important demographic trends:

• Adult gender balance has remained near 50/50 across all decades
• Minor populations consistently show fewer females (birth rate patterns)
• Senior populations maintain strong female majorities, though the ratio is gradually decreasing

Focused Analysis: Childhood Demographics Over Time

Let's build another pipeline focusing specifically on how childhood demographics have evolved:

# Analyze childhood population trends and gender patterns
childhood_trends = spark.sql("""
    SELECT
        year,
        age,
        total,
        females,
        males,
        ROUND((females - males) * 1.0 / total * 100, 2) as gender_gap_percent,
        ROUND(total * 1.0 / LAG(total) OVER (PARTITION BY age ORDER BY year) - 1, 4) as growth_rate
    FROM census_all_decades
    WHERE age <= 5  -- Focus on early childhood
    ORDER BY age, year
""")
childhood_trends.show()

+----+---+-------+-------+-------+------------------+-----------+
|year|age|  total|females|  males|gender_gap_percent|growth_rate|
+----+---+-------+-------+-------+------------------+-----------+
|1980|  0|3438584|1679209|1759375|             -2.33|       NULL|
|1990|  0|3857376|1883971|1973405|             -2.32|     0.1218|
|2000|  0|3733034|1825783|1907251|             -2.18|    -0.0322|
|2010|  0|4079669|1994141|2085528|             -2.24|     0.0929|
|1980|  1|3367035|1644767|1722268|             -2.30|       NULL|
|1990|  1|3854707|1882447|1972260|             -2.33|     0.1448|
|2000|  1|3825896|1869613|1956283|             -2.27|    -0.0075|
|2010|  1|4085341|1997991|2087350|             -2.19|     0.0678|
|1980|  2|3316902|1620583|1696319|             -2.28|       NULL|
|1990|  2|3841092|1875596|1965496|             -2.34|     0.1580|
|2000|  2|3904845|1907024|1997821|             -2.33|     0.0166|
|2010|  2|4089295|2000746|2088549|             -2.15|     0.0472|
|1980|  3|3286877|1606067|1680810|             -2.27|       NULL|
|1990|  3|3818425|1864339|1954086|             -2.35|     0.1617|
|2000|  3|3970865|1938440|2032425|             -2.37|     0.0399|
|2010|  3|4092221|2002756|2089465|             -2.12|     0.0306|
|1980|  4|3275647|1600625|1675022|             -2.27|       NULL|
|1990|  4|3788597|1849592|1939005|             -2.36|     0.1566|
|2000|  4|4024943|1964286|2060657|             -2.39|     0.0624|
|2010|  4|4094802|2004366|2090436|             -2.10|     0.0174|
+----+---+-------+-------+-------+------------------+-----------+
only showing top 20 rows

This analysis uses the window function LAG() to calculate growth rates by comparing each decade's population to the previous decade for the same age group. The results show:

• Gender gaps remain remarkably consistent (around -2.2%) across ages and decades
• Growth patterns varied significantly, with strong growth in the 1980s-90s, some decline in the 2000s, and recovery by 2010
• Population sizes for early childhood have generally increased over the 30-year period

SQL vs DataFrame API: When to Use Each

After working with both SQL queries and DataFrame operations throughout this series, you have practical experience with both approaches. The choice between them often comes down to readability, team preferences, and the specific analytical task at hand.

Performance: Truly Identical

Let's verify what we've mentioned throughout this tutorial to prove that both approaches deliver identical performance:

# Time the same analysis using both approaches
import time

# SQL approach
start_time = time.time()
sql_result = spark.sql("""
    SELECT year, AVG(total) as avg_population
    FROM census_all_decades
    WHERE age < 18
    GROUP BY year
    ORDER BY year
""").collect()
sql_time = time.time() - start_time

# DataFrame approach
from pyspark.sql.functions import avg, col

start_time = time.time()
df_result = combined_census.filter(col("age") < 18) \
                          .groupBy("year") \
                          .agg(avg("total").alias("avg_population")) \
                          .orderBy("year") \
                          .collect()
df_time = time.time() - start_time

print(f"SQL time: {sql_time:.4f} seconds")
print(f"DataFrame time: {df_time:.4f} seconds")
print(f"Results identical: {sql_result == df_result}")

SQL time: 0.8313 seconds
DataFrame time: 0.8317 seconds
Results identical: True

The execution times are nearly identical because both approaches compile to the same optimized execution plan through Spark's Catalyst optimizer. Your choice should be based on code clarity, not performance concerns.

Readability Comparison

Consider these equivalent operations and think about which feels more natural:

Complex aggregation with multiple conditions:

# SQL approach - reads like business requirements
business_summary = spark.sql("""
    SELECT
        year,
        CASE WHEN age < 18 THEN 'Youth' ELSE 'Adult' END as category,
        SUM(total) as population,
        AVG(females * 1.0 / males) as avg_gender_ratio
    FROM census_all_decades
    WHERE total > 50000
    GROUP BY year, CASE WHEN age < 18 THEN 'Youth' ELSE 'Adult' END
    HAVING SUM(total) > 1000000
    ORDER BY year, category
""")

# DataFrame approach - more programmatic
from pyspark.sql.functions import when, sum, avg, col

business_summary_df = combined_census \
    .filter(col("total") > 50000) \
    .withColumn("category", when(col("age") < 18, "Youth").otherwise("Adult")) \
    .groupBy("year", "category") \
    .agg(
        sum("total").alias("population"),
        avg(col("females") / col("males")).alias("avg_gender_ratio")
    ) \
    .filter(col("population") > 1000000) \
    .orderBy("year", "category")

Simple filtering and selection:

# SQL - concise and familiar
simple_query = spark.sql("SELECT age, total FROM census2010 WHERE age BETWEEN 20 AND 30")

# DataFrame - more explicit about operations
simple_df = df.select("age", "total").filter((col("age") >= 20) & (col("age") <= 30))

When to Choose SQL

SQL excels when you need:

• Declarative business logic that stakeholders can read and understand
• Complex joins and aggregations that map naturally to SQL constructs
• Ad-hoc analysis where you want to explore data quickly
• Team collaboration with members who have strong SQL backgrounds

SQL is particularly powerful for:

• Reporting and dashboard queries
• Data validation and quality checks
• Exploratory data analysis
• Business intelligence workflows

When to Choose DataFrames

DataFrames work better when you need:

• Programmatic transformations that integrate with Python logic
• Complex custom functions that don't translate well to SQL
• Dynamic query generation based on application logic
• Integration with machine learning pipelines and other Python libraries

DataFrames shine for:

• ETL pipeline development
• Machine learning feature engineering
• Application development
• Custom algorithm implementation

The Hybrid Approach

In practice, the most effective approach often combines both:

# Start with DataFrame operations for data loading and cleaning
clean_data = spark.read.json("census_data.json")
    .filter(col("total") > 0)
    .withColumn("decade", (col("year") / 10).cast("int") * 10)

# Register cleaned data for SQL analysis
clean_data.createOrReplaceTempView("clean_census")

# Use SQL for complex business analysis
analysis_results = spark.sql("""
    SELECT decade, age_category,
           AVG(population) as avg_pop,
           SUM(population) as total_pop
    FROM clean_census
    GROUP BY decade, age_category
""")

# Convert back to pandas for visualization
final_results = analysis_results.toPandas()

This hybrid workflow uses each tool where it's strongest: DataFrames for data preparation, SQL for analytical queries, and pandas for visualization.

Review and Next Steps

You've completed a comprehensive introduction to Spark SQL and built practical skills for querying distributed data using familiar SQL syntax. Let's recap what you've accomplished and where these skills can take you.

Key Takeaways

Spark SQL Fundamentals:

• Temporary views provide the bridge between DataFrames and SQL, giving your distributed data familiar table-like names you can query
• spark.sql() executes SQL queries against these views, returning DataFrames that integrate seamlessly with the rest of your PySpark workflow
• Performance is identical between SQL and DataFrame approaches because both use the same Catalyst optimizer underneath

Essential SQL Operations:

• Filtering with WHERE clauses works just like traditional SQL but operates across distributed datasets
• Calculated fields using expressions, functions, and CASE statements let you create new insights from existing columns
• Sorting with ORDER BY arranges results meaningfully, handling distributed data coordination automatically
• Sorting with ORDER BY arranges results meaningfully, handling distributed data coordination automatically

Advanced Analytical Patterns:

• Aggregations and grouping provide powerful summarization capabilities for large-scale data analysis
• UNION ALL operations let you combine datasets with identical schemas for historical trend analysis
• Complex SQL pipelines chain multiple operations (filtering, grouping, sorting) into readable analytical workflows

Tool Selection:

• Both SQL and DataFrames compile to identical execution plans, so choose based on readability and team preferences
• SQL excels for business reporting, ad-hoc analysis, and team collaboration
• DataFrames work better for programmatic transformations and integration with Python applications

Real-World Applications

The skills you've developed in this tutorial translate directly to production data work:

Business Intelligence and Reporting:
Your ability to write complex SQL queries against distributed datasets makes you valuable for creating dashboards, reports, and analytical insights that scale with business growth.

Data Engineering Pipelines:
Understanding both SQL and DataFrame approaches gives you flexibility in building ETL processes, letting you choose the clearest approach for each transformation step.

Exploratory Data Analysis:
SQL's declarative nature makes it excellent for investigating data patterns, validating hypotheses, and generating insights during the analytical discovery process.

Your PySpark Learning Continues

This completes our Introduction to PySpark tutorial series, but your learning journey is just beginning. You've built a strong foundation that prepares you for more specialized applications:

Advanced Analytics: Window functions, complex joins, and statistical operations that handle sophisticated analytical requirements

Machine Learning: PySpark's MLlib library for building and deploying machine learning models at scale

Production Deployment: Performance optimization, resource management, and integration with production data systems

Cloud Integration: Working with cloud storage systems and managed Spark services for enterprise-scale applications

Keep Practicing

The best way to solidify these skills is through practice with real datasets. Try applying what you've learned to:

• Public datasets that interest you personally
• Work projects where you can introduce distributed processing
• Open source contributions to data-focused projects
• Online competitions that involve large-scale data analysis

You've developed valuable skills that are in high demand in the data community. Keep building, keep experimenting, and most importantly, keep solving real problems with the power of distributed computing!