Forum Discussion
spark.sql is getting old data that was deleted from Lakehouse whereas spark.read.load doesn't
I have data in a Lakehouse and I have deleted some of it. I am trying to load it from a Fabric Notebook.
When I use spark.sql("SELECT * FROM parquet.`<abfs_path>/Tables/<table_name>`" then I get the old data I have deleted from the lakehouse.
When I use spark.read.load(<abfs_path>/Tables/<table_name>) I dont get this deleted data.
I have to use the abfs path as I am not setting a default lakehouse and can't set one to solve this.
Why is this old data coming up when I use spark.sql when the paths are exactly the same?
solved by changing it to delta
spark.sql("SELECT * FROM delta.`<abfs_path>/Tables/<table_name>`")
6 Replies
- wardy912
Super User
The paths are the same but you're using a different method to query them
spark.sql("SELECT * FROM parquet.`<abfs_path>/Tables/<table_name>`")
Spark SQL - may be using cached metadataspark.read.load("<abfs_path>/Tables/<table_name>")
Dataframe API - reads current state of files
You could add a cell to your notebook that clears the cache if you want to use the Spark SQL code
spark.catalog.clearCache()Please give a thumbs up if this helps, thanks
Unfortuantly clearing the cache doesn't work.
however this also gets the deleted data, so i think it's in specifying parquet.
spark.read.format("parquet").load(_table_abfs)I want to be able to use a sql query and the abfs path to the data to load the data, any ideas on how i can do this?
- wardy912df = spark.sql("""SELECT *FROM <lakehouse>.<schema>.<table>""")You can also drag the table from the left hand side in the lakehouse to a cell and it will automatically add a SQL query for that table.
