d013_orderby_vs_sort.py

"""
https://sparkbyexamples.com/pyspark/pyspark-orderby-and-sort-explained/

https://github.com/spark-examples/pyspark-examples/blob/master/pyspark-orderby.py

3 ways to sort

sort()

orderBy()

RawSQL


DataFrame.sort() default ascending, support multiple column

same usage provide by DataFrame.orderBy()

specifically assgin ascending/decending

df.sort(df.department.asc() / .desc() )

Also support Raw SQL

"""

import pyspark
from pyspark.sql import SparkSession
from pyspark.sql.functions import col, asc, desc

spark = SparkSession.builder.appName("SparkByExamples.com").getOrCreate()

simpleData = [
    ("James", "Sales", "NY", 90000, 34, 10000),
    ("Michael", "Sales", "NY", 86000, 56, 20000),
    ("Robert", "Sales", "CA", 81000, 30, 23000),
    ("Maria", "Finance", "CA", 90000, 24, 23000),
    ("Raman", "Finance", "CA", 99000, 40, 24000),
    ("Scott", "Finance", "NY", 83000, 36, 19000),
    ("Jen", "Finance", "NY", 79000, 53, 15000),
    ("Jeff", "Marketing", "CA", 80000, 25, 18000),
    ("Kumar", "Marketing", "NY", 91000, 50, 21000),
]
columns = ["employee_name", "department", "state", "salary", "age", "bonus"]

df = spark.createDataFrame(data=simpleData, schema=columns)

df.printSchema()
df.show(truncate=False)

df.sort("department", "state").show(truncate=False)
df.sort(col("department"), col("state")).show(truncate=False)

df.orderBy("department", "state").show(truncate=False)
df.orderBy(col("department"), col("state")).show(truncate=False)

# assign ascending or descending
df.sort(df.department.asc(), df.state.asc()).show(truncate=False)
df.sort(col("department").asc(), col("state").asc()).show(truncate=False)
df.orderBy(col("department").asc(), col("state").asc()).show(truncate=False)

df.sort(df.department.asc(), df.state.desc()).show(truncate=False)
df.sort(col("department").asc(), col("state").desc()).show(truncate=False)
df.orderBy(col("department").asc(), col("state").desc()).show(truncate=False)


df.createOrReplaceTempView("EMP")
df.select(
    "employee_name", asc("department"), desc("state"), "salary", "age", "bonus"
).show(truncate=False)

spark.sql(
    "select employee_name,department,state,salary,age,bonus from EMP ORDER BY department asc"
).show(truncate=False)