CODEWITHSUNDEEP
amazon-athena
Dan
Dan

Reputation: 31

Hash Array Column AWS Athena

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))

Upvotes: 2

Views: 1087

Answers (1)

Nicolas Busca
Nicolas Busca

Reputation: 1305

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

select uuid(), ... from mytable

Upvotes: 2

Related Questions