Cheat Sheet: SparkSQL to PySpark

SELECT * FROM table SELECT column1, column2 FROM table SELECT COUNT(*) FROM table SELECT DISTINCT column FROM table SELECT * FROM table WHERE column=value SELECT * FROM table WHERE column>value SELECT * FROM table ORDER BY column ASC SELECT * FROM table ORDER BY column DESC

df.select("*") df.select("column1", "column2") df.count() df.select("column").distinct() df.filter(df.column == value) df.filter(df.column > value) df.orderBy("column", ascending=True) df.orderBy("column", ascending=False)

SELECT COUNT(column) FROM table SELECT MAX(column) FROM table SELECT MIN(column) FROM table SELECT AVG(column) FROM table SELECT SUM(column) FROM table SELECT column, COUNT(*) FROM table GROUP BY column SELECT column, SUM(column2) FROM table GROUP BY column SELECT column, AVG(column2) FROM table GROUP BY column SELECT column, MIN(column2) FROM table GROUP BY column

df.agg(F.count("column").alias("count")) df.agg(F.max("column").alias("max")) df.agg(F.min("column").alias("min")) df.agg(F.avg("column").alias("avg")) df.agg(F.sum("column").alias("sum")) df.groupBy("column").agg(F.count("*").alias("count")) df.groupBy("column").agg(F.sum("column2").alias("sum")) df.groupBy("column").agg(F.avg("column2").alias("avg")) df.groupBy("column").agg(F.min("column2").alias("min"))

SELECT * FROM table1 JOIN table2 ON condition SELECT * FROM table1 LEFT JOIN table2 ON condition SELECT * FROM table1 RIGHT JOIN table2 ON condition SELECT * FROM table1 FULL JOIN table2 ON condition

df1.join(df2, on="condition", how="inner") df1.join(df2, on="condition", how="left") df1.join(df2, on="condition", how="right") df1.join(df2, on="condition", how="outer")

SELECT * FROM table WHERE column IS NULL SELECT * FROM table WHERE column IS NOT NULL SELECT column, COALESCE(column, 0) FROM table SELECT column, IFNULL(column, 0) FROM table

df.filter(df.column.isNull()) df.filter(df.column.isNotNull()) df.withColumn("column", F.coalesce(df.column, F.lit(0))) "df.withColumn(""column"", F.when(df.column.isNull(), 0) .otherwise(df.column))"

SELECT UPPER(column) FROM table SELECT LOWER(column) FROM table SELECT TRIM(column) FROM table SELECT SUBSTRING(column, 1, 3) FROM table SELECT LENGTH(column) FROM table

df.withColumn("column", F.upper(df.column)) df.withColumn("column", F.lower(df.column)) df.withColumn("column", F.trim(df.column)) df.withColumn("column", F.substring(df.column, 1, 3)) df.withColumn("columnLength", F.length(df.column))

SELECT CURRENT_DATE() SELECT CURRENT_TIMESTAMP() SELECT YEAR(column) FROM table SELECT MONTH(column) FROM table SELECT DAY(column) FROM table SELECT DATEDIFF(end_date, start_date) FROM table

df.withColumn("current_date", F.current_date()) df.withColumn("current_timestamp", F.current_timestamp()) df.withColumn("year", F.year(df.column)) df.withColumn("month", F.month(df.column)) df.withColumn("day", F.dayofmonth(df.column)) df.withColumn("date_diff", F.datediff(df.end_date, df.start_date))

LOAD DATA INPATH '/path' INTO TABLE table INSERT INTO table VALUES (1, 2) SELECT * FROM table SAVE AS TABLE table

df = spark.read.csv("/path") df.write.insertInto("table", overwrite=True) df = spark.table("table") df.write.saveAsTable("table", mode="overwrite")