← Back to Mind Maps
SQL to PySpark
A visual mental model mapping your existing SQL knowledge to PySpark DataFrame API operations.
Data Transformation
Mental Mapping
SELECT / WHERE
Filter & Select
SELECT col1, col2 FROM t WHERE col2 > 0
select() / filter()
Filter & Select
df.select('col1', 'col2').filter(df.col2 > 0)
JOIN (LEFT, INNER, etc.)
Joins
SELECT * FROM a LEFT JOIN b ON a.id = b.id
join()
Joins
dfA.join(dfB, dfA.id == dfB.id, 'left')
GROUP BY
Aggregation
SELECT id, COUNT(*), SUM(val) FROM t GROUP BY id
groupBy().agg()
Aggregation
df.groupBy('id').agg(F.count('*'), F.sum('val'))
ORDER BY
Sorting
SELECT * FROM t ORDER BY date DESC, id ASC
orderBy()
Sorting
df.orderBy(F.col('date').desc(), F.col('id').asc())
OVER(PARTITION BY)
Windowing
RANK() OVER(PARTITION BY id ORDER BY date DESC)
Window.partitionBy()
Windowing
F.rank().over(Window.partitionBy('id').orderBy(F.col('date').desc()))
WITH cte AS
CTEs / Subqueries
WITH cte AS (...) SELECT * FROM cte
Variable Assignment
CTEs / Subqueries
cte_df = df.filter(...)
cte_df.select(...)
CASE WHEN
Conditional Logic
CASE WHEN val > 10 THEN 'High' ELSE 'Low' END
when().otherwise()
Conditional Logic
F.when(df.val > 10, 'High').otherwise('Low')
UNION ALL
Combine Results
SELECT * FROM a UNION ALL SELECT * FROM b
unionAll() / union()
Combine Results
dfA.unionAll(dfB)
CAST()
Type Casting
CAST(val AS INT)
cast()
Type Casting
df.val.cast('integer')
DISTINCT
Remove Duplicates
SELECT DISTINCT id, name FROM t
distinct() / dropDuplicates()
Remove Duplicates
df.select('id', 'name').distinct()
COALESCE / IS NULL
Handling Nulls
COALESCE(val, 0)\nWHERE val IS NOT NULL
coalesce() / isNotNull()
Handling Nulls
F.coalesce('val', F.lit(0))
df.filter(df.val.isNotNull())
LIKE / ILIKE
String Matching
WHERE name LIKE '%data%'
like() / rlike()
String Matching
df.filter(df.name.like('%data%'))
SUBSTRING
Extract / Substring
SUBSTRING(name, 1, 3)
substr()
Extract / Substring
df.name.substr(1, 3)
LIMIT / TOP
Limiting Rows
SELECT * FROM t LIMIT 10
limit()
Limiting Rows
df.limit(10)