Execute SQL queries seamlessly on Spark DataFrames using the Pandas API

Apache Spark has revolutionized the landscape of big data analytics, offering unparalleled scalability and performance. However, working with Spark’s native APIs might not always align with the workflows of data analysts and scientists accustomed to tools like Pandas. To bridge this gap, the Pandas API on Spark provides a familiar interface for performing various data manipulation tasks, including executing SQL queries. In this article, we will delve into how the Pandas API on Spark enables us to execute SQL queries effortlessly on Spark DataFrames, with detailed examples and outputs.

Understanding SQL Execution with Pandas API on Spark

Executing SQL queries directly on Spark DataFrames using the Pandas API offers a seamless experience for data professionals. The sql() function allows users to leverage their SQL skills within the Spark environment, obtaining query results as Pandas-on-Spark DataFrames.

Example: Executing SQL Queries on Spark DataFrames

Let’s consider an example where we have a Spark DataFrame representing employee data, and we want to execute a SQL query to filter employees with a salary greater than 50000.

# Import necessary libraries
from pyspark.sql import SparkSession
# Create SparkSession
spark = SparkSession.builder \
    .appName("Pandas API on Spark @ Learning at Freshers.in") \
    .getOrCreate()
# Sample data for DataFrame
data = [("John", 50000), ("Alice", 60000), ("Bob", 45000)]
columns = ["Name", "Salary"]
df = spark.createDataFrame(data, columns)
# Register DataFrame as a temporary view
df.createOrReplaceTempView("employees")
# Execute SQL query
query_result = spark.sql("SELECT * FROM employees WHERE Salary > 50000")
# Convert query result to Pandas-on-Spark DataFrame
result_df = query_result.toPandas()
# Display query result
print(result_df)

    Name  Salary
0  Alice   60000