Updates on a column in delta table with downstream autoloader

[Wijnand]

I've got the following questions:

1. Can I pause autoloader jobs, delete cluster that was used to run these jobs, create a new cluster and run jobs with newer version cluster?

2. I have one autoloader job that ingests JSONs and transforms this to a delta table, and one autoloader job that reads the delta table and appends this data to a second delta table in another location. What to do if I want to update a column on the first delta table? I've tried this small scale, and the second job breaks because it couldn't handle the update. Threw up the following error message:

com.databricks.sql.transaction.tahoe.DeltaUnsupportedOperationException: Detected a data update (for example part-00000-6e380ba1-f9ff-4938-9152-d989ed2413ab.c000.snappy.parquet) in the source table at version 13. This is currently not supported. If you'd like to ignore updates, set the option 'skipChangeCommits' to 'true'. If you would like the data update to be reflected, please restart this query with a fresh checkpoint directory. The source table can be found at path dbfs:/user/hive/warehouse/target_table.

So I've tried restarting the query with a fresh checkpoint directory, and it reprocessed all records in delta table 1. The actual flow deals with a massive number of records, so reprocessing is very much not preferred.

[cgrant]

Hello,

1.Yes you can pause the job, delete the cluster, upgrade versions of the cluster, etc. With Auto Loader and Structured Streaming the important thing is making sure that the checkpointLocation stays in tact, so no deletions, modifications, or moves to files under checkpointLocation. Starting with a new checkpoint means you start from scratch.

2. Auto Loader is a data source in Spark Structured Streaming, which normally deals with source datasets that are append-only - this is why you see an error when running UPDATE on your source table. If you'd like to override this behavior and handle updates to your sources, there are two main options:

• skipChangeCommits - this option will ignore any commits to your source that contain deletes/updates like those resulting from the MERGE, UPDATE, and DELETE commands. You can enable this option immediately to avoid needing a fresh checkpoint.
• readChangeFeed - this option will allow you not just to skip the commits that change, but review each change at the row level and apply them to your target, given you do a bit of coding. This requires the source table to have delta.enableChangeDataFeed enabled. 

Here is some documentation that includes a couple of other options in the case that the above are not satisfactory.