Are you looking to find out how to read JSON files into PySpark DataFrame in Azure Databricks cloud or maybe you are looking for a solution, to multiple JSON files into PySpark DataFrame in Azure Databricks using the read() 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 read JSON files into DataFrames in Azure Databricks. I will explain it by taking a practical example. So don’t waste time let’s start with a step-by-step guide to understanding how to read JSON files into PySpark DataFrame.
In this blog, I will teach you the following with practical examples:
- Different methods of reading single file
- Read from multiple files
- Read from multiple files using wild card
- Read from directory
- Common JSON options
- Write JSON file
In PySpark Azure Databricks, the read method is used to load files from an external source into a DataFrame.
Official documentation link: DataFrameReader()
Contents
- 1 Create a simple DataFrame
- 2 How to read a single JSON file in multiple ways into PySpark DataFrame in Azure Databricks?
- 3 How to read multiple JSON files into PySpark DataFrame in Azure Databricks?
- 4 How to read multiple JSON files using wildcard into PySpark DataFrame in Azure Databricks?
- 5 How to read JSON files from a directory into PySpark DataFrame in Azure Databricks?
- 6 Commonly used JSON option while reading files into PySpark DataFrame in Azure Databricks?
- 7 How to set multiple options in PySpark DataFrame in Azure Databricks?
- 8 How to write JSON files using DataFrameWriter method in Azure Databricks?
- 9 When should you use the PySpark read in Azure Databricks?
- 10 Real World Use Case Scenarios for PySpark read function in Azure Databricks?
- 11 What are the alternatives to the read function in PySpark Azure Databricks?
- 12 Final Thoughts
Create a simple DataFrame
Let’s understand the use of the fill() function with various 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
Folder Structure:
Before start learning let’s have a quick look at my folder structure and the files inside it.
The folder “read_write_json” has 4 files and 1 folder in it and the folder “read_directory” has three files in it.
File structure:
/mnt/practice/read_write_csv/
|– drivers_1.json
|– drivers_2.json
|– multi_line.json
|– single_quote.json
|– read_directory
|– drivers_1.json
|– drivers_1.json
|– drivers_info_3.json
Download the files and place them in the appropriate folder, as mentioned above. Each file has 20 records, excluding the header.
How to read a single JSON file in multiple ways into PySpark DataFrame in Azure Databricks?
To read a JSON file into a PySpark DataFrame, use the json(“path”) method provided by DataFrameReader. In this section, I will teach you how to read a single JSON file using various practical methods with examples.
Examples:
# Varioud methods of reading file
# Replace the file_path with the source file location of yours.
# Method 1:
df = spark.read.format("json").load(f"{base_path}/drivers_1.json")
# Method 2:
df = spark.read.load(format="json", path=f"{base_path}/drivers_1.json")
# Method 3:
df = spark.read.format("json").option("path", f"{base_path}/drivers_1.json").load()
# Method 4:
df = spark.read.json(f"{base_path}/drivers_1.json")
df.printSchema()
df.show(3)
#The above four example gives the following output.
"""
Output:
root
|-- code: string (nullable = true)
|-- dob: string (nullable = true)
|-- driverId: long (nullable = true)
|-- driverRef: string (nullable = true)
|-- name: struct (nullable = true)
| |-- forename: string (nullable = true)
| |-- surname: string (nullable = true)
|-- nationality: string (nullable = true)
|-- number: string (nullable = true)
|-- url: string (nullable = true)
+----+----------+--------+---------+-----------------+-----------+------+--------------------+
|code| dob|driverId|driverRef| name|nationality|number| url|
+----+----------+--------+---------+-----------------+-----------+------+--------------------+
| HAM|1985-01-07| 1| hamilton|{Lewis, Hamilton}| British| 44|http://en.wikiped...|
| HEI|1977-05-10| 2| heidfeld| {Nick, Heidfeld}| German| \N|http://en.wikiped...|
| ROS|1985-06-27| 3| rosberg| {Nico, Rosberg}| German| 6|http://en.wikiped...|
+----+----------+--------+---------+-----------------+-----------+------+--------------------+
"""
How to read multiple JSON files into PySpark DataFrame in Azure Databricks?
To read a JSON file into a PySpark DataFrame, use the json(“path”) method provided by DataFrameReader. In this section, I will teach you how to read multiple JSON files using practical methods with examples.
Examples:
df = spark.read.json(f"{base_path}/drivers_1.json")
print(f"First file count: {df.count()}")
df = spark.read.json(f"{base_path}/drivers_2.json")
print(f"Second file count: {df.count()}")
# Reading multiple files appends the second file to the first.
multiple_files = [f"{base_path}/drivers_1.json", f"{base_path}/drivers_2.json"]
df = spark.read.json(multiple_files)
print(f"Mulitple file count: {df.count()}")
"""
Output:
First file count: 20
Second file count: 20
Mulitple file count: 40
"""
As you know, we have two files each of which has 20 records, 2 * 20 = 40 records.
How to read multiple JSON files using wildcard into PySpark DataFrame in Azure Databricks?
To read a JSON file into a PySpark DataFrame, use the json(“path”) method provided by DataFrameReader. With practical examples, I will teach you how to read multiple JSON files using wildcards.
Examples:
df = spark.read.json(f"{base_path}/read_directory/drivers_*.json")
print(f"Multiple file count using wildcard(*): {df.count()}")
"""
Output:
Multiple file count using wildcard(*): 40
"""
As you know, we have two files each of which has 20 records, 2 * 20 = 40 records.
How to read JSON files from a directory into PySpark DataFrame in Azure Databricks?
To read a JSON file into a PySpark DataFrame, use the json(“path”) method provided by DataFrameReader. With examples, I will teach you how to read JSON files from a directory using various read method.
Examples:
df = spark.read.json(f"{base_path}/read_directory/")
print(f"Directory file count: {df.count()}")
"""
Output:
Directory file count: 60
"""
As you know, we have two files each of which has 50 records, 3 * 20 = 60 records excluding headers.
Commonly used JSON option while reading files into PySpark DataFrame in Azure Databricks?
There are numerous ways to work with JSON files using the PySpark JSON dataset. Some of the most significant choices are discussed with examples in the section below.
Option 1: dateFormat
The “dateFormat” parses the string date format to time format, but it needs a defined schema. Let’s see with an example.
print("a) Without schema:")
spark.read \
.option("dateFormat", "yyyy-MM-dd") \
.json(f"{base_path}/drivers_1.json") \
.select("dob").printSchema()
"""
Output:
a) Without schema:
root
|-- dob: string (nullable = true)
"""
from pyspark.sql.types import StructType, StructField, IntegerType, DateType, StringType
drivers_schema = StructType([
StructField('driverId', IntegerType()),
StructField('driverRef', StringType()),
StructField('number', IntegerType()),
StructField('code', StringType()),
StructField('name', StructType([StructField('forename', StringType()), StructField('surname', StringType())])),
StructField('dob', DateType()), # yyyy-MM-dd
StructField('nationality', StringType()),
StructField('url', StringType())
])
print("b) With schema:")
spark.read \
.schema(drivers_schema) \
.option("dateFormat", "yyyy-MM-dd") \
.json(f"{base_path}/drivers_1.json") \
.select("dob").printSchema()
"""
Output:
b) With schema:
root
|-- dob: date (nullable = true)
"""
Datetime Patterns for Formatting and Parsing: Link
Similarly, we have timestamp format and a lot of options, which you can refer it by clicking here.
Option 2: allowSingleQuotes
The “allowSingleQuotes” treats single quotes, the way you treat double quotes in JSON..
print("a) Before allowing")
spark.read \
.option("allowSingleQuotes", False) \
.json(f"{base_path}/single_quote.json") \
.printSchema()
"""
Output:
a) Before allowing
root
|-- _corrupt_record: string (nullable = true)
"""
print("b) Before allowing")
spark.read \
.option("allowSingleQuotes", True) \
.json(f"{base_path}/single_quote.json") \
.printSchema()
"""
Output:
b) Before allowing
root
|-- code: string (nullable = true)
|-- dob: string (nullable = true)
|-- driverId: long (nullable = true)
|-- driverRef: string (nullable = true)
|-- name: struct (nullable = true)
| |-- forename: string (nullable = true)
| |-- surname: string (nullable = true)
|-- nationality: string (nullable = true)
|-- number: string (nullable = true)
|-- url: string (nullable = true)
"""
Option 3: multiLine
The “multiLine” helps in reading multiline JSON files. If you are not still clear, open the “multi_line.json” and “drivers_1.json” side by side so you can find the difference between each file.
spark.read \
.option("multiLine", True) \
.json(f"{base_path}/multi_line.json") \
.show(2)
"""
Output:
+-------------+--------+------+--------+--------+--------+---+---------+------+
|constructorId|driverId|number|position| q1| q2| q3|qualifyId|raceId|
+-------------+--------+------+--------+--------+--------+---+---------+------+
| 3| 811| 19| 13|1:38.437|1:37.841| \N| 5002| 861|
| 10| 814| 11| 14|1:38.325|1:37.877| \N| 5003| 861|
+-------------+--------+------+--------+--------+--------+---+---------+------+
"""
How to set multiple options in PySpark DataFrame in Azure Databricks?
Examples:
df = spark.read \
.schema(drivers_schema) \
.options(dateFormat="yyyy-MM-dd", allowSingleQuotes=True) \
.json(f"{base_path}/drivers_1.json")
df.printSchema()
df.show(3)
"""
Output:
root
|-- driverId: integer (nullable = true)
|-- driverRef: string (nullable = true)
|-- number: integer (nullable = true)
|-- code: string (nullable = true)
|-- name: struct (nullable = true)
| |-- forename: string (nullable = true)
| |-- surname: string (nullable = true)
|-- dob: date (nullable = true)
|-- nationality: string (nullable = true)
|-- url: string (nullable = true)
+--------+---------+------+----+-----------------+----------+-----------+--------------------+
|driverId|driverRef|number|code| name| dob|nationality| url|
+--------+---------+------+----+-----------------+----------+-----------+--------------------+
| 1| hamilton| 44| HAM|{Lewis, Hamilton}|1985-01-07| British|http://en.wikiped...|
| 2| heidfeld| null| HEI| {Nick, Heidfeld}|1977-05-10| German|http://en.wikiped...|
| 3| rosberg| 6| ROS| {Nico, Rosberg}|1985-06-27| German|http://en.wikiped...|
+--------+---------+------+----+-----------------+----------+-----------+--------------------+
"""
How to write JSON files using DataFrameWriter method in Azure Databricks?
To write a JSON file into a PySpark DataFrame, use the save(“path”) method provided by DataFrameReader. In this section, I will teach you how to write JSON files using various practical methods with examples.
Examples:
df.write.save("target_location")
1. Make use of the option while writing JSON files into the target location.
df.write.options(allowSingleQuotes=True).save(“target_location”)
2. Using mode() while writing files, There are multiple modes available and they are:
- overwrite – mode is used to overwrite the existing file.
- append – To add the data to the existing file.
- ignore – Ignores write operation when the file already exists.
- error(default) – When the file already exists, it returns an error.
df.write.mode(“overwrite”).save(“target_location”)
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 PySpark read in Azure Databricks?
These could be the possible reasons:
- To read a single JSON file
- To read multiple JSON files
- To read JSON files using wild card
- To read JSON files from a directory
Real World Use Case Scenarios for PySpark read function in Azure Databricks?
Assume you were given a parquet files dataset location and asked to read files using PySpark, you can use the PySpark spark.read() to fetch and convert the parquet file into a DataFrame.
What are the alternatives to the read function in PySpark Azure Databricks?
The PySpark function read() is the only one that helps in reading files from multiple locations.
Final Thoughts
In this article, we have learned about the PySpark read and write methods to read or write JSON files into PySparks 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.