I have two CSV datasets that appear to use the same structure, but an AWS Glue crawler handles them differently. One table is created with OpenCSVSerde and correctly keeps a value such as "Smith, John" in one column. The other is created with ROW FORMAT DELIMITED and FIELDS TERMINATED BY ',', with table properties including classification='csv' and areColumnsQuoted='false'. As a result, quoted fields containing commas are split into separate columns.
For example, this row:
12345,"Smith, John",98765
is interpreted as separate values such as Smith and John instead of a single quoted field.
I deleted and recreated the table, removed a custom classifier, added a CSV classifier using a comma delimiter and double-quote character, and configured the crawler to recrawl all files. However, it keeps generating the simple delimited format.
Could the header or the first few sampled rows be influencing the crawler? Are there file characteristics that make Glue ignore quoted fields? Is it possible to force OpenCSVSerde during crawler creation, and is there a way to determine why Glue chooses areColumnsQuoted='false'?
3 Answers
Glue crawlers infer the schema and parsing settings from a sample of the files, so inspect the header and the first several data rows carefully. Make sure the quote usage is consistent from the beginning of the file—not just in later records. A header with inconsistent delimiters, unquoted values, malformed quoting, or files with slightly different layouts can cause the crawler to fall back to ROW FORMAT DELIMITED. Also check for mixed file formats in the same crawl path.
Rather than relying on the crawler to infer this edge case, create or edit the table with the correct OpenCSVSerde definition and prevent the crawler from changing the schema afterward. Crawlers are useful for discovering files, but CSV quoting can be unreliable when files contain inconsistent rows or multiple layouts. Managing the table definition explicitly is usually more predictable than repeatedly deleting and recreating it.
There generally isn’t a reliable crawler setting that forces OpenCSVSerde for every detected CSV. Check that every record uses valid CSV quoting, that embedded quotes are escaped correctly, and that quoted fields do not contain unexpected line breaks. Compare the problematic file byte-for-byte with a working one, especially the header, delimiter, encoding, and first sampled rows. If the source is valid but inference still fails, define the table manually with OpenCSVSerde and treat crawler schema updates as disabled.
The file does appear to have a header row, so I’ll compare that and the first few records with the files that crawl correctly.