Overview
spark.sql("SELECT ...") and the DataFrame API compile to the same engine. Pick the readable spelling.
On this page7 sections
What you will do
Spark has two spellings for the same engine. The DataFrame API is Python methods: spark.table("orders").filter(...).select(...). Spark SQL is a string: spark.sql("SELECT ... FROM orders ..."). Both become a Catalyst plan. You are not choosing a second database. You are choosing syntax.
People coming from the SQL track often think in SELECT first. People coming from pandas often think in methods first. Teams mix them: SQL for a clearly relational chunk, DataFrame for loops, reuse, and column functions. Mixing in one file is normal. Mixing without a shared grain is not.
This tab can run a simple spark.sql SELECT with an optional WHERE and LIMIT. It is not full Spark SQL. There is no live cluster catalog beyond the registered sample tables. For anything beyond a simple select, use the DataFrame API.
Why this skill
A paid-orders extract is one WHERE clause. Writing it as SQL can be clearer than a chain of methods, especially if the logic already exists in a warehouse query. The reverse is also true: generating column names in a Python loop is painful in a giant SQL string. Knowing both APIs lets you pick the readable one.
The dangerous myth is that spark.sql is "the SQL engine" and DataFrames are "Python, so slower." They compile together. A bad join is expensive in either spelling.
How the code works
Two windows, one kitchen. The ticket can be written in SQL or as a Python chain. The cooks still make the same dish.
spark.sql and filter/select are different front doors to Catalyst.
Same result shape: paid orders, at most 10 rows.
| Task | DataFrame API | Spark SQL |
|---|---|---|
| Load orders | spark.table("orders") | FROM orders |
| Paid only | .filter("order_status = 'paid'") | WHERE order_status = 'paid' |
| Bound rows | .limit(10) | LIMIT 10 |
| Show | .show() | still call show() on the returned DataFrame |
WHERE order_status = 'paid' is the same predicate as filter on that column.
Worked examples
Run the example below in this tab. Read the input, follow the code, then check the output matches what you expect.
result = spark.sql(
"SELECT * FROM orders WHERE order_status = 'paid' LIMIT 10"
)
result.show()spark.sql returns a DataFrame. You still assign it to result and call show() so the grid fills. The string is ordinary SQL: SELECT, FROM, WHERE, LIMIT.
paid = (
spark.table("orders")
.filter("order_status = 'paid'")
.limit(10)
)
paid.show()
result = spark.sql(
"SELECT * FROM orders WHERE order_status = 'paid' LIMIT 10"
)
result.show()If both run, you should see the same grain: paid rows, at most 10. Pick SQL for the exercise so the check can see spark.sql and paid in your code.
This is not a second SQL editor
Complex SQL belongs in the SQL track editor. Here, keep the query to a simple SELECT from orders. If spark.sql rejects a query, simplify it or use DataFrame methods and keep spark.sql in a working SELECT for the exercise.
If you know Pandas, here is the translation
pandas has no spark.sql. The cousin is df.query("order_status == 'paid'") or boolean indexing. Spark SQL is real SQL parsed by Spark, not a pandas expression language.
Copy-paste without reading the output
Run Sample first. If the numbers or row count look wrong, stop and re-read the previous section before changing code.
Common beginner questions
Which API is faster?
Neither, if they express the same plan. Measure with explain() later. Readability and reuse decide which you type.
Can I mix SQL and methods?
Yes. spark.sql(...).filter(...) is legal. Keep column names consistent so the next reader can follow the grain.
Why did an earlier note say this tab is DataFrame-first?
Most exercises use methods because the simulator started that way. This lesson is the SQL door. Later DataFrame lessons stay on methods.
What comes next
You know why Spark exists, what a cluster is, how reads work, and that SQL and DataFrames share an engine. The next lesson is the full architecture mental model: lazy plans, actions, and keeping bulk data off the driver.
Practice
Run Sample to see spark.sql. Then complete Exercise: use spark.sql to select paid orders with a LIMIT of 10, and assign the DataFrame to result.
Your code must contain spark.sql and paid. Keep the query simple so this tab can run it.
Practicals · load into the editor
After you read the theory, run these in the pane on the right. They execute in this tab, no cluster.