Are you looking to find out how to use SQL expressions of PySpark DataFrame columns in Azure Databricks cloud or maybe you are looking for a solution, to use SQL expression DataFrame column in PySpark Databricks using the expr() method? If you are looking for any of these problem solutions, you have landed on the correct page. I will also show you how to use PySpark to transform dataframe using SQL expressions of DataFrames in Azure Databricks. I will explain it by taking a practical example. So please don’t waste time let’s start with a step-by-step guide to understanding how to use SQL expressions in PySpark DataFrame using the expt() function.
In this blog, I will teach you the following with practical examples:
- Syntax of expr()
- Commonly used SQL functions using expr() function
The PySpark’s expr() function is a SQL function used to execute SQL like expression of the DataFrame in PySpark Azure Databricks.
Syntax:
expr(“SQL expression”)
Contents
- 1 What is the syntax of the expr() function in PySpark Azure Databricks?
- 2 Create a simple DataFrame
- 3 How to use SQL expressions in PySpark Azure Databricks?
- 4 When should you use the expr() function in PySpark Azure Databricks?
- 5 Real World Use Case Scenarios for using expr() in PySpark Azure Databricks?
- 6 What are the alternatives to the expr() function in PySpark Azure Databricks?
- 7 Final Thoughts
What is the syntax of the expr() function in PySpark Azure Databricks?
The syntax is as follows:
expr("SQL expression")
| Parameter Name | Required | Description |
| SQL expression (str) | Yes | It represents the SQL expressions. |
Apache Spark Official Documentation Link: expr()
Create a simple DataFrame
Let’s understand the use of the expr() function with a variety of examples. Let’s start by creating a DataFrame.
Gentle reminder:
In Databricks,
- sparkSession made available as spark
- sparkContext made available as sc
In case, you want to create it manually, use the below code.
from pyspark.sql.session import SparkSession
spark = SparkSession.builder
.master("local[*]")
.appName("azurelib.com")
.getOrCreate()
sc = spark.sparkContext
a) Create manual PySpark DataFrame
data = [
("Andie","Ebbutt","Polygender","2022-03-22","$30.10"),
("Hobie","Deegan","Male","2021-12-29","$285.99"),
("Denys","Belverstone","Female","2022-01-07","$212.91"),
("Delphine","Pietersma","Female","2022-09-17","$724.81"),
("Putnem","Chasson","Male","2021-12-03","$938.47"),
("Jenilee","Hindmoor","Polygender","2022-10-10","$243.23"),
("Nicoline","Cowitz","Female","2022-08-28","$32.53"),
("Brunhilde","Vasyukhnov","Female","2022-04-10","$237.17"),
("Roxi","Leming","Female","2022-02-15","$304.06"),
("Raffaello","Cornes","Male","2022-06-30","$631.73")
]
df = spark.createDataFrame(data, schema=["first_name","last_name","gender","created","balance"])
df.printSchema()
df.show(5, truncate=False)
"""
root
|-- first_name: string (nullable = true)
|-- last_name: string (nullable = true)
|-- gender: string (nullable = true)
|-- created: string (nullable = true)
|-- balance: string (nullable = true)
+----------+-----------+----------+----------+-------+
|first_name|last_name |gender |created |balance|
+----------+-----------+----------+----------+-------+
|Andie |Ebbutt |Polygender|2022-03-22|$30.10 |
|Hobie |Deegan |Male |2021-12-29|$285.99|
|Denys |Belverstone|Female |2022-01-07|$212.91|
|Delphine |Pietersma |Female |2022-09-17|$724.81|
|Putnem |Chasson |Male |2021-12-03|$938.47|
+----------+-----------+----------+----------+-------+
"""
b) Creating a DataFrame by reading files
Download and use the below source file.
# replace the file_path with the source file location which you have downloaded.
df_2 = spark.read.format("csv").option("header", True).load(file_path)
df_2.printSchema()
"""
root
|-- first_name: string (nullable = true)
|-- last_name: string (nullable = true)
|-- gender: string (nullable = true)
|-- created: string (nullable = true)
|-- balance: string (nullable = true)
"""
Note: Here, I will be using the manually created DataFrame.
How to use SQL expressions in PySpark Azure Databricks?
To perform the SQL-like expression in PySpark DataFrame using the expr() function. The expr() function takes only one argument, a SQL-like expression in string format. In this section, I will teach you how to work with SQL expressions using expr() function with examples.
Let’s see some of the commonly used SQL expressions using expr() function.
Example 1:
# Concat columns
from pyspark.sql.functions import expr
df.select("first_name", "last_name", expr("CONCAT(first_name, last_name)")).show(3)
"""
Output:
+----------+-----------+-----------------------------+
|first_name| last_name|concat(first_name, last_name)|
+----------+-----------+-----------------------------+
| Andie| Ebbutt| AndieEbbutt|
| Hobie| Deegan| HobieDeegan|
| Denys|Belverstone| DenysBelverstone|
+----------+-----------+-----------------------------+
"""
Example 2:
# Column alias
from pyspark.sql.functions import expr
df.select(expr("first_name AS fore_name")).show(3)
"""
Output:
+---------+
|fore_name|
+---------+
| Andie|
| Hobie|
| Denys|
+---------+
"""
Example 3:
# Substring
from pyspark.sql.functions import expr
df.select(expr("SUBSTRING(balance, 2) AS balance")).show(3)
"""
Output:
+-------+
|balance|
+-------+
| 30.10|
| 285.99|
| 212.91|
+-------+
"""
Example 4:
# Cast
from pyspark.sql.functions import expr
print("a) Before casting:")
df.select(expr("SUBSTRING(balance, 2) AS balance")).printSchema()
"""
Output:
a) Before casting:
root
|-- balance: string (nullable = true)
"""
print("b) After casting:")
df.select(expr("CAST(SUBSTRING(balance, 2) AS INT) AS balance")).printSchema()
"""
Output:
b) After casting:
root
|-- balance: integer (nullable = true)
"""
Example 5:
# Arithmetic operation
from pyspark.sql.functions import expr
df.select("balance", expr("SUBSTRING(balance, 2) * 100 AS multiple_balance")).show(3)
"""
Output:
+-------+----------------+
|balance|multiple_balance|
+-------+----------------+
| $30.10| 3010.0|
|$285.99| 28599.0|
|$212.91| 21291.0|
+-------+----------------+
"""
Example 6:
# CASE WHEN
from pyspark.sql.functions import expr
df.select( "gender",
expr("""
CASE
WHEN gender = 'Male' THEN 'M'
ELSE '-'
END AS new_gender
""")).show(3)
"""
Output:
+----------+----------+
| gender|new_gender|
+----------+----------+
|Polygender| -|
| Male| M|
| Female| -|
+----------+----------+
"""
I have attached the complete code used in this blog in a notebook format in this GitHub link. You can download and import this notebook in databricks, jupyter notebook, etc.
When should you use the expr() function in PySpark Azure Databricks?
These could be the possible reasons:
- To transform data using SQL operations
- To use SQL function in DataFrame
Real World Use Case Scenarios for using expr() in PySpark Azure Databricks?
For example, if you are from a SQL background, you might know all the SQL functions already. You can use SQL functions for example CONCAT, SUBSTRING, CAST, AS, and a lot more. You can easily transform the data using these SQL functions. Som of the common SQL function has been used to transform data in the above section with practical examples.
What are the alternatives to the expr() function in PySpark Azure Databricks?
There are multiple alternatives to the expr() function, which are as follows:
- SelectExpr(): This is the combination of select() and expr() function
Final Thoughts
In this article, we have learned about the PySpark expr() method to perform conditional operation of DataFrame in Azure Databricks along with the examples explained clearly. I have also covered different scenarios with practical examples that could be possible. I hope the information that was provided helped in gaining knowledge.
Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.
- For Azure Study material Join Telegram group : Telegram group link:
- Azure Jobs and other updates Follow me on LinkedIn: Azure Updates on LinkedIn
- Azure Tutorial Videos: Videos Link
- Azure Databricks Lesson 1
- Azure Databricks Lesson 2
- Azure Databricks Lesson 3
- Azure Databricks Lesson 4
- Azure Databricks Lesson 5
- Azure Databricks Lesson 6
- Azure Databricks Lesson 7
As a big data engineer, I design and build scalable data processing systems and integrate them with various data sources and databases. I have a strong background in Python and am proficient in big data technologies such as Hadoop, Hive, Spark, Databricks, and Azure. My interest lies in working with large datasets and deriving actionable insights to support informed business decisions.
