cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Statestore is not valid when using Spark Structured Streaming

DB1To3
Valued Contributor II

I'm using spark structured streaming and it works properly at first.  But when I try to audit the checkpointLocation I can't get it to work.  It seems like it should be a relatively simple task to query the statestore and audit the processing of a stream.  I'm attempting to follow these docs:

https://docs.databricks.com/aws/en/structured-streaming/read-state

I'm using dbx 16.4 and spark 3.5.2

The error is always something meaningless like so:

[STDS_FAILED_TO_READ_STATE_SCHEMA] Failed to read the state schema. Either the file does not exist, or the file is corrupted. options: StateSourceOptions(checkpointLocation=abfss://datalake@xxx.dfs.core.windows.net/test002/x05, batchId=1, operatorId=0, storeName=default, joinSide=none, stateVarName=None, +flattenCollectionTypes=true).

Rerun the streaming query to construct the state schema, and report to the corresponding communities or vendors if the error persists. SQLSTATE: 42K03

 

Given the importance of Spark Structured Streaming, and the popularity, I did not think there would be so many sharp edges when auditing the statestore.

My source table is just a UC managed table that is writted by zerobus.  It is like so:

CREATE TABLE a.b.streaming_c (
  MyA STRING NOT NULL,
  MyB INT NOT NULL,
  MyC STRING,
  MyDetails VARIANT,
  ZerobusRescueJson VARIANT)
USING delta
TBLPROPERTIES (
  'delta.checkpoint.writeStatsAsJson' = 'false',
  'delta.checkpoint.writeStatsAsStruct' = 'true',
  'delta.enableDeletionVectors' = 'true',
  'delta.enableRowTracking' = 'true',
  'delta.feature.appendOnly' = 'supported',
  'delta.feature.deletionVectors' = 'supported',
  'delta.feature.domainMetadata' = 'supported',
  'delta.feature.invariants' = 'supported',
  'delta.feature.rowTracking' = 'supported',
  'delta.feature.variantType' = 'supported',
  'delta.minReaderVersion' = '3',
  'delta.minWriterVersion' = '7',
  'delta.parquet.compression.codec' = 'zstd',
  'delta.parquet.format.version' = '2.12.0',
  'delta.parquet.format.version.afe.internal' = '2.12.0',
  'delta.rowTracking.materializedRowCommitVersionColumnName' = '_row-commit-version-col-777ca68b-c04b-4b5d-9a9d-5c5535e32d20',
  'delta.rowTracking.materializedRowIdColumnName' = '_row-id-col-1885c4cd-1354-4ce6-9726-ed098a251a62',
  'io.unitycatalog.tableId' = '6f07a65b-4887-4aa2-8f56-569b2922aec4')

I use readStream/writeStream to move the data to a different place. I set the checkpointLocation (ie. .option("checkpointLocation", x)) to an abfss location in azure:
"abfss://datalake@abc.dfs.core.windows.net/test002/x05"

 

DB1To3_0-1794251574660.png

Later when the streaming compute stops running, I try to audit the state of the checkpointLocation and it fails:

SELECT * FROM read_statestore('abfss://datalake@abc.dfs.core.windows.net/test002/x05/')

 

That checkpointLocation is very intimidating to me. It is doing ALL the heavy lifting when it comes to keeping track of the zerobus records that have been processed. There needs to be a way to recover an audit trail from this statestore location. Why is the statestore API giving meaningless errorrs? I tried both the pyspark equivalent of this SELECT statement and it has the same error.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

ashishshivatare
Databricks Partner

Your screenshot shows the cause. The checkpoint only has metadata, commits and offsets, with no state folder. The state store only exists for stateful operators (aggregations, dropDuplicates, stream-stream joins, windows, transformWithState). A plain readStream → writeStream copy is stateless, so read_statestore has nothing to read and throws STDS_FAILED_TO_READ_STATE_SCHEMA. The “rerun the query” hint doesn’t apply here.

Your audit trail is in the files you do have. metadata holds the query id. offsets/ has one file per batch with the source table version it read up to. commits/ marks which batches finished.

 
python
from pyspark.sql import functions as F

cp = "abfss://datalake@abc.dfs.core.windows.net/test002/x05"
query_id = spark.read.json(f"{cp}/metadata").first()["id"]

offsets = (
    spark.read.text(f"{cp}/offsets", wholetext=True)
    .select(
        F.regexp_extract("_metadata.file_path", r"/(\d+)$", 1).cast("long").alias("batch_id"),
        F.get_json_object(F.split("value", "\n").getItem(2), "$.reservoirVersion").cast("long").alias("source_version"),
    )
    .orderBy("batch_id")
)
display(offsets)

Then filter DESCRIBE HISTORY on your target table to operation = 'STREAMING UPDATE' and operationParameters.queryId = query_id. The epochId parameter is the batch ID, so you can join it to the offsets above to get batch, source version, target version and row count.

One caution: Spark cleans up old offset and commit files (it keeps about the last 100 batches by default), so don’t rely on the checkpoint as a permanent record. For a durable audit trail, write batch_id and row counts to a table from foreachBatch, or persist progress events with a StreamingQueryListener.

View solution in original post

4 REPLIES 4

ashishshivatare
Databricks Partner

Your screenshot shows the cause. The checkpoint only has metadata, commits and offsets, with no state folder. The state store only exists for stateful operators (aggregations, dropDuplicates, stream-stream joins, windows, transformWithState). A plain readStream → writeStream copy is stateless, so read_statestore has nothing to read and throws STDS_FAILED_TO_READ_STATE_SCHEMA. The “rerun the query” hint doesn’t apply here.

Your audit trail is in the files you do have. metadata holds the query id. offsets/ has one file per batch with the source table version it read up to. commits/ marks which batches finished.

 
python
from pyspark.sql import functions as F

cp = "abfss://datalake@abc.dfs.core.windows.net/test002/x05"
query_id = spark.read.json(f"{cp}/metadata").first()["id"]

offsets = (
    spark.read.text(f"{cp}/offsets", wholetext=True)
    .select(
        F.regexp_extract("_metadata.file_path", r"/(\d+)$", 1).cast("long").alias("batch_id"),
        F.get_json_object(F.split("value", "\n").getItem(2), "$.reservoirVersion").cast("long").alias("source_version"),
    )
    .orderBy("batch_id")
)
display(offsets)

Then filter DESCRIBE HISTORY on your target table to operation = 'STREAMING UPDATE' and operationParameters.queryId = query_id. The epochId parameter is the batch ID, so you can join it to the offsets above to get batch, source version, target version and row count.

One caution: Spark cleans up old offset and commit files (it keeps about the last 100 batches by default), so don’t rely on the checkpoint as a permanent record. For a durable audit trail, write batch_id and row counts to a table from foreachBatch, or persist progress events with a StreamingQueryListener.

DB1To3
Valued Contributor II

Thanks for the thorough reply.

>>  The checkpoint only has metadata, commits and offsets, with no state folder.

It is a bit counterintuitive to have a bunch of stateful information stored as metadata, commits, and offsets, yet to NOT be able to use a "statestore api" to inspect that information.

>> For a durable audit trail, write batch_id and row counts to a table from foreachBatch, or persist progress events with a StreamingQueryListener.

Thanks for the reference to "StreamingQueryListener".  That is the sort of turnkey API that I was missing.  It wasn't coming up in my searches.

I'm guessing designers of spark streaming did not intend for users to tear apart the checkpointLocation on our own (although it still seems important to have that ability in a pinch).  I was surprised NOT to find a github library or example that creates such audit trails at this level of detail.  Maybe with your additional keywords (batch_id, reservoirVersion) then I will find something....

 

FYI, I posted a similar question elsewhere.  My underlying goals are not only to be able to audit the checkpointLocation but also to have a strategy for recovering my streams in the event that my checkpointLocation became corrupted one day (for reasons outside my control, for example if I store it on a less dependable location than the place where the deltalake tables live.).  I learned about the startingVersion/startingTimestamp that can be used for recovery.  So if I am able to do some checkpointLocation auditing, and make sense of the results, then it will give me better shot at setting startingVersion/startingTimestamp for a point-in-time recovery.

 

 

 

SajeevPS
New Contributor II

I think the error is expected for this pipeline. The state reader functions (read_statestore / read_state_metadata) are for stateful Structured Streaming queries, meaning ones with a stateful operator such as aggregations, dropDuplicates, stream-stream joins or arbitrary stateful processing. A plain readStream to writeStream copy doesn't keep any state, so there's no state schema in the checkpoint to read, hence STDS_FAILED_TO_READ_STATE_SCHEMA. You can confirm with read_state_metadata('<checkpoint>'): it should show no stateful operators.

For an audit trail of what was processed, a few options that don't depend on state:

  1. Delta history on the target table. Each micro-batch write appears in DESCRIBE HISTORY with its operation metrics (rows written), so you get a per-batch log for free.
  2. Add audit columns in the stream itself, for example a processing timestamp with current_timestamp(), or use foreachBatch and write the batchId plus counts to a small audit table alongside the data.
  3. Streaming query progress. Each batch's progress (input rows, batch id, source offsets) is available from the query's progress or a StreamingQueryListener, which you can persist to a table.

Also note the state reader has compute requirements (DBR 14.3 LTS+ on dedicated or no-isolation compute, and it isn't supported on serverless), so it's worth checking those if you later add a stateful step.

Sajeev PS, Founder, ExamCrest.com

DB1To3
Valued Contributor II

Thanks for this reply.  I was about to proceed with no. 2, but didn't know about options 1 and 3.  I had not come across the StreamingQueryListener yet in my investigation.  Perhaps it is only a small fraction of streaming applications that also use that API to monitor the behavior of their solutions.  It seems pretty critical for an analysis of failures or misbehaviors.