Hash Array Column AWS Athena

user_15888491 0
Viewed 292

I am working with data without any primary keys. I am trying to hash the unique columns in order to create a surrogate key, however I'm running into an issue as the data contains arrays. So I do want to keep the data in arrays, because if I change it to just a blob of text I lose unnesting. Ultimately, I need to move the rows into columns and in order to do that, I need the unique key to join back to.

I have tried

SELECT md5(to_utf8(array_column)) from my_table;

I have also tried to cast the column as a varchar:

SELECT CAST(array_column as VARCHAR) from my_table

I keep getting results that complain about the type: Unexpected parameters (array(row(**remaining data definitions))

amazon-athena
1 Answers

You can create a unique identifier using the uuid function. For example:

select uuid(), ... from mytable
user_67745380
Related