Fabric notebook Py4JJavaError - underlying location does not exist

[bekkac579]

We are getting the following error on our Fabric notebook via pipeline runs -

Py4JJavaError

An error occurred while calling o6734.collectToPython. : org.apache.spark.SparkException: Job aborted due to stage failure: Task 0 in stage 18.0 failed 4 times, most recent failure: Lost task 0.3 in stage 18.0 (TID 23) (vm-4f039835 executor 1😞 com.microsoft.sqlserver.jdbc.SQLServerException: An error occurred during the current command (Done status 0). Failed to complete the command because the underlying location does not exist. Underlying data description: table '\<lakehouse table path\>', file '\<lakehouse table url\>'.

The code is below,

import com.microsoft.spark.fabric
from com.microsoft.spark.fabric.Constants import Constants

spark = SparkSession.builder.appName("create_availability").getOrCreate()


# Build SQL query to find min and max date from source view
query = "SELECT MIN(week_start_date) AS earliest_date, MAX(week_start_date) AS latest_date FROM schema.source_view"

# Load from SQL endpoint
table_load_spark = (
    spark.read.option(Constants.WorkspaceId, WS_ID)
    .option(Constants.DatabaseName, LAKEHOUSE_NAME)
    .synapsesql(query)
)

dates = table_load_spark.first()

The failure is coming from the last line. Going through the traceback it is more specifically failing when running collectToPython.

Our source view is in a Lakehouse shortcut. It is in the same schema as tables we do not have permission to view or refresh, so the Spark job must be accessing these forbidden tables while running the above.

The failure is specifically happening when the notebook is ran via pipeline on our scheduled runs managed by service principal. The SP has workspace and item access to the Lakehouse and SQL Endpoint in question, so we can't see any overt permission problems. Also this method works in another workspace with the same permissions, so it must an issue with this shortcut. 

[ati_puri · Accepted solution]

Hi @bekkac579 ,

The failure happens only when run via pipeline states that its a SQL Analytics endpoint metadata sync issue. You can add a refresh Sql endpoint activity before calling this code in your pipeline and try once, else you can refresh the query within the code itself. Write a sample warm-up query to activate your SQL endpoint before hitting the actual table and querying it.

Refer link here to understand on this issue - SQL Analytics Endpoint Metadata Sync - Microsoft Fabric | Microsoft Learn

Let me know if the above solution works.

Thanks

Ati Puri

[bekkac579]

We added a notebook to refresh sql metadata into the pipeline, seems to be working now, thank you !