Spark SQL and PySpark cheat sheet

from pyspark.sql import SparkSession, functions as F

spark = SparkSession.builder.appName("sales").getOrCreate()
df = (spark.read.option("header", True)
      .option("inferSchema", True).csv("data/sales.csv"))
df = spark.read.parquet("data/sales/")
df = spark.table("main.sales.orders")
df.printSchema(); df.show(5)

df.select("order_id", "region", "amount")
df.filter((F.col("region") == "North") & (F.col("amount") > 100))
df.withColumn("net", F.col("amount") - F.col("discount"))
df.withColumnRenamed("amt", "amount")
df.drop("notes").dropDuplicates(["order_id"])

(df.groupBy("region")
   .agg(F.sum("amount").alias("total"),
        F.countDistinct("customer_id").alias("customers"))
   .orderBy(F.desc("total")))

orders.join(customers, on="customer_id", how="left")
orders.join(F.broadcast(products), "product_id")   # small table
orders.join(returns, "order_id", "left_anti")      # not returned

from pyspark.sql import Window

w = Window.partitionBy("customer_id").orderBy(F.desc("order_date"))
latest = (df.withColumn("rn", F.row_number().over(w))
            .filter("rn = 1").drop("rn"))

df.createOrReplaceTempView("orders")
spark.sql("""
    SELECT region, SUM(amount) AS total
    FROM orders
    GROUP BY region
    ORDER BY total DESC
""").show()

SELECT region, COUNT(*) AS orders
FROM parquet.`data/orders/`
GROUP BY region;

SELECT customer_id, order_id, order_date
FROM orders
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY customer_id ORDER BY order_date DESC
) = 1;

df.withColumn("order_date", F.to_date("order_ts"))
df.withColumn("month", F.date_trunc("month", "order_date"))
df.withColumn("age_days",
              F.datediff(F.current_date(), "order_date"))
df.fillna({"discount": 0})
df.withColumn("region", F.coalesce("region", F.lit("Unknown")))

(df.write.mode("overwrite")
   .partitionBy("region")
   .parquet("out/sales/"))
df.write.mode("append").saveAsTable("main.sales.daily")

MERGE INTO sales t
USING updates u ON t.order_id = u.order_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;