Spark "Run SQL on files directly" fails

Spark documentation suggests I can run SQL directly on files in PySpark with syntax like this:

df = spark.sql("SELECT * FROM parquet.`examples/src/main/resources/users.parquet`")

I have some Delta format data sitting in an AWS S3 bucket (well, currently a fake S3 bucket in LocalStack). I'm trying to query it using this approach. The bucket looks like this:

$ awslocal s3 ls s3://data-lake/data/
                       PRE _delta_log/
2023-06-07 16:55:33        903 part-00000-923a2eea-e2fe-468e-b2b9-a85206858ddb-c000.snappy.parquet
2023-06-07 16:55:33        914 part-00001-a6ceac4f-d7ea-44e9-bce9-d7fe0c103e35-c000.snappy.parquet

And while the following works just fine:

df = spark.read.format("delta").load("s3://data-lake/data")

The syntax I'm trying to query it with using SQL as follows fails:

df = spark.sql("select * from delta.`s3://data-lake/data`")

(I've also tried with the path directly to a file rather than just the containing directory with not difference).

The data is written via the following simple Scala Spark code:

val ds = Seq(
  Foo("a", Bar(1, 2)),
  // etc
).toDS()

ds
  .write
  .format("delta")
  .mode(SaveMode.Overwrite)
  .save("s3://data-lake/data")

The error I am getting when I try and query this is as follows, edited a bit for brevity:

Py4JError                                 Traceback (most recent call last)
/tmp/ipykernel_178/100492152.py in <module>

---> 11 df = spark.sql("select * from delta.`s3://data-lake/data`")

<snip>

py4j.Py4JException: Method sql([class java.lang.String, class java.util.HashMap]) does not exist

Feels like a weird thing in the plumbing rather than a syntax error on my part (not least because if I try the same approach in Scala it works OK).

• Python 3.7.16
• PySpark 3.4.0
• Running from a Jupyter notebook against a LocalStack container based on the emr-serverless/spark/emr-6.10.0:latest image.

It looks like you've mistaken the documentation.

It says you can read directly from a file.

> Delta Lake is an open source file protocol that stores data in Parquet files

I suggest trying:

df = spark.sql("select * from parquet.`s3://data-lake/data/part-00000-923a2eea-e2fe-468e-b2b9-a85206858ddb-c000.snappy.parquet`")

But since delta folders are not file formats, I do not see why select * from delta would ever work.

The reason why df = spark.read.format("delta").load("s3://data-lake/data") works is because you can load from a folder.

To me, it looks like a mismatch between the versions of PySpark and the underlying Spark. The error is that py4j is not able to invoke the SparkSession::sql() method that takes both a parameterised query and a map of parameter names with their values:

py4j.Py4JException: Method sql([class java.lang.String, class java.util.HashMap]) does not exist
                               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

That particular overload was added in Spark 3.4.0 and PySpark 3.4.0 always makes use of it, no matter if the query is parameterised or not:

...
try:
   litArgs = {k: _to_java_column(lit(v)) for k, v in (args or {}).items()}
   return DataFrame(self._jsparkSession.sql(sqlQuery, litArgs), self)
...

Make sure that Spark's home directory is properly configured in the notebook environment and that PySpark is not using an older Spark version.

Indeed, checking what version is the software included in emr-6.10.0 proves my theory correct:

Downgrade your PySpark to version 3.3.1.