Method for converting all values of a field to string when querying with Ion SerDe?

0

I have some event logs stored as Ion data in S3, and I am trying to query it using Amazon Athena's Ion Hive SerDe. One of the datafields that is in most of our events is typically an int, but in a few events the value for the datafield is a string. I have tried specifying the datafield as a STRING when creating the external table, which works most of the time, but sometimes returns a HIVE_BAD_DATA error that says the SerDe cannot convert from IonIntLite to IonText. Since the Ion SerDe requires homogenous data, is there an official way in Athena to make all values conform to string when querying? The datafield is an ID so we often just need to check equality to an ID number. Or, is there a way to just ignore any entries that have the datafield not as the type we specify (i.e. specify the ID as BIGINT, and have Athena ignore any events with a string ID)?

wfchang
asked 2 years ago102 views
No Answers

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions