How to read from SQL table in PySpark using a query instead of specifying a table
PySpark

How to read from SQL table in PySpark using a query instead of specifying a table

Fetching data using a SQL query in PySpark
Bartosz Mikulski
Bartosz Mikulski – 1 min read

When we know precisely what query we should run to get the data we want from a SQL database, we don’t need to load multiple tables in PySpark, and emulate the joins and selects in the Python code. Instead of that, we can pass the SQL query as the source of the DataFrame while retrieving it from the database.

If my code to retrieve the data looks like this:

df = spark.read \
    .format("jdbc") \
    .option("url", "jdbc:mysql://localhost:port") \
    .option("dbtable", "schema.tablename") \
    ...
    .load()

I can replace the dbtable parameter with a SQL query and use the result as the table loaded by PySpark:

.option("dbtable", "(SELECT column_A, column_B FROM some_table) AS tbl")
Older post

How to restart a stuck Airflow DAG

What to do when an Airflow DAG gets stuck and does not want to run

Newer post

Don't learn another programming language

Should you learn a new programming language this year?

Related Posts

Data engineering PySpark BDD

Testing data products: BDD for data engineers

How to use BDD to test PySpark code
Published on: 29 Jan 2021

PySpark

How to write to a SQL database using JDBC in PySpark

How to use JDBC driver in PySpark to write a DataFrame to a SQL database
Published on: 22 Dec 2020

PySpark

How to add dependencies as jar files or Python scripts to PySpark

How to add a jar file or a Python file as a Pyspark dependency
Published on: 21 Dec 2020