Repository navigation
Avro schema only file causes Redshift stl_load_errors: "Invalid AVRO file found. Unexpected end of AVRO file." #96
Description
When a part file contains no data and just the Avro schema, Redshift throws an exception:
Caused by: com.amazon.support.exceptions.ErrorException: [Amazon](500310)
Invalid operation: Load into table 'stg_device_attribute' failed. Check
'stl_load_errors' system table for details.;
Where the load error reads:
Invalid AVRO file found. Unexpected end of AVRO file.
My AVRO file just contains the schema for a number of shards where no records matched a WHERE-clause condition:
Obj...avro.codec snappy.avro.schema..{"type":"record","name":"topLevelRecord","fields":[{"name":"roster_id","type":["long","null"]},{"name":"device_id","type":["long","null"]},{"name":"push_token","type":["string","null"]},{"name":"shard_id","type":["long","null"]},{"name":"device_type","type":["string","null"]},{"name":"device_date_created","type":["string","null"]}]}
In my particular case I am running a query across 12 PostgreSQL databases which may not contain results. I can get around this problem by merging into a smaller number of partitions and hope there's at least one record in each part file by sacrificing Redshift parallelism on the load.
I am not sure what the solution is but it might be worth it to warn if a part file will have 0 records.
Activity
JoshRosen commentedon Sep 24, 2015
@TELSER1 also ran into this in #89.
I wonder whether this is a Redshift bug or a
spark-avrobug. As long as it's legal to create an Avro file containing no records, then I'd argue that Redshift should gracefully handle those files. According to the Object Container File section of the Avro 1.7.7 spec, "A file consists of a file header, followed by one or more file data blocks". I wonder whether a valid empty Avro file needs to contain a file data block which explicitly says that it contains no objects. We should figure out whether the zero-record files thatspark-avrois generated are legal.This probably merits some testing with manually-generated Avro files in order to figure out which ones Redshift considers legal (vs. Spark). If Redshift can't support Avro files which contain no records, then we might have to add an option to
spark-avroto try to avoid the creation of those files.cfeduke commentedon Sep 24, 2015
As a workaround for this issue I repartition the RDD, which I should have been doing from the beginning. I will look at the Avro spec and see if there is any mention of an empty block. (And I will check the Redshift docs too, sometimes they have notes for these sorts of edge cases.)
cfeduke commentedon Sep 24, 2015
(Since you already included the Avro spec info in your reply no reason for me to duplicate it here.)
avro-tools(v1.7.7) processes my schema-only file with no problems but my guess is that there needs to be a file data block indicating 0 records for Redshift to process the file at all.JoshRosen commentedon Sep 24, 2015
This might be another piece of evidence in favor of exploring alternative data formats for transferring data to Redshift (see #73).
If we have an easy way to identify the empty files / partitions after the write is performed, then it might be possible to use a manifest to explicitly specify only the non-empty partitions' files. We could probably use accumulators in order to record the number of records processed in each partition during the write in order to do this.
cfeduke commentedon Sep 25, 2015
👍 for manifest though without explicitly rebalancing for Redshift's # of slices parallelism on
COPYis compromised, but at this point when you have sparse/empty part files its probably not that big of a problem (failedCOPYs are worse than less parallel ones!).cfeduke commentedon Sep 25, 2015
And of course don't forgot the other state, where all part files are empty. Probably shouldn't proceed to the
COPYphase at all.JoshRosen commentedon Sep 29, 2015
Another benefit to using a manifest is the fact that it can mitigate S3 eventual consistency issues: https://docs.aws.amazon.com/redshift/latest/dg/managing-data-consistency.html\
Update: Actually, I think the AWS documentation's reference to those consistency issues may be outdated, since AFAIK the US Standard region no longer has the difference in consistency semantics that motivated that change. I've reached out to AWS documentation writers / support for clarification.
barudwale20 commentedon Jul 22, 2020
@JoshRosen It's been almost 5 years and I am still facing the same issue. Is there any solution to this apart from the manifest file?
I want to automate some of my tasks and making manifest each time does not make sense.
I couldn't find the solution hence asking here.
Will be really helpful if you could help.