AWS Glue with CSV source data that changes over time

Question

We have data that is being dumped into S3 every hour, with a basic Glue crawler running that enables us to query this data in Athena.  The problem we're facing is that the source data is changing over time (columns added & removed) and the crawler doesn't seem to recognise this.  Data from newer datasets is being put into columns that appear to positionally align with the earlier datasets, rather than being placed into columns based on the column name.  (e.g. if the first dataset has columns A, C & D, and a new dataset has columns A, B, C & D, the new column B data shows up in column "C" in athena, and the new column C data shows up in column "D").  How can I fix this so that we can see all the columns with the data properly assigned to each column based on the header name of the columns?

Answer

Glue Crawler has an option to use for schema evolution scenario. To be able to add new columns whenever new columns are added into the files, you need to follow the following steps:
* Make sure you choose "Crawl all folders"![Enter image description here](/media/postImages/original/IM4W39jKRzRuKTNpzVr-A6vw)

* Choose Add new columns or Update Table definition in Catalog under advanced options in "Set output and scheduling"
![Enter image description here](/media/postImages/original/IMrwLTlNuzTqWIlMzFib5z7g)

In case your column names are updated correctly, you may also need to create a classifier and link that classifier with this crawler. Here are the options you need to choose for the classifier:
![Enter image description here](/media/postImages/original/IMFP3x8d_1Qf6Pzn2ZJUP5eA)

Answer

Thanks for bring this scenario up. Is it possible for you to try to do perform few tests using parquet files and see if that works for your use case?

AWS Glue with CSV source data that changes over time

Relevant content