How do I ensure that the AWS Glue crawler I've written is using the OpenCSV SerDe instead of the LazySimpleSerDe?

user_1079354 0
Viewed 3117

For context: I skimmed this previous question but was dissatisifed with the answer for two reasons:

  • I'm not writing anything in Python; in fact, I'm not writing any custom scripts for this at all as I'm relying on a crawler and not a Glue script.
  • The answer is not as complete as I require since it's just a link to some library.

I'm looking to leverage AWS Glue to accept some CSVs into a schema, and using Athena, convert that CSV table into multiple Parquet-formatted tables for ETL purposes. The data I'm working with has quotes embedded in it, which would be okay save for the fact that one record I have has a value of:

"blablabla","1","Freeman,Morgan","bla bla bla"

It seems that Glue is tripping over itself when it encounters the "Freeman,Morgan" piece of data.

If I use the standard Glue crawler, I get a table created with the LazySimpleSerDe, which truncates the record above in its column to:

"Freeman,

...which is obviously not desirable.

How do I force the crawler to output the file with the correct SerDe?

[Unpleasant] Constraints:

  • Looking to not accomplish this with a Glue script, since for that to work I believe I have to have a table beforehand, whereas the crawler will create the table on my behalf.

If I have to do this all through Amazon Athena, I'd feel like that would largely defeat the purpose but it's a tenable solution.

amazon-athena aws-glue amazon-web-services
2 Answers

This is going to turn into a very dull answer, but apparently AWS provides its own set of rules for classifying if a file is a CSV.

To be classified as CSV, the table schema must have at least two columns and two rows of data. The CSV classifier uses a number of heuristics to determine whether a header is present in a given file. If the classifier can't determine a header from the first row of data, column headers are displayed as col1, col2, col3, and so on. The built-in CSV classifier determines whether to infer a header by evaluating the following characteristics of the file:

  • Every column in a potential header parses as a STRING data type.

  • Except for the last column, every column in a potential header has content that is fewer than 150 characters. To allow for a trailing delimiter, the last column can be empty throughout the file.

  • Every column in a potential header must meet the AWS Glue regex requirements for a column name.

  • The header row must be sufficiently different from the data rows. To determine this, one or more of the rows must parse as other than STRING type. If all columns are of type STRING, then the first row of data is not sufficiently different from subsequent rows to be used as the header.

I believed that I had met all of these requirements, given that the column names are wildly divergent from the actual data in the CSV, and ideally there shouldn't be much of an issue there.

However, in spite of my belief that it would satisfy the AWS Glue regex (which I can't find a definition for anywhere), I elected to move away from commas and to pipes instead. The data now loads as I expect it to.

user_10793540
user_76133400
Related