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?
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
- 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.
