CODEWITHSUNDEEP
amazon-athenapresto
Rajeev A Nair
Rajeev A Nair

Reputation: 321

How to do MD5 hashing of as string in athena?

MD5 hashing function in athena is not working for string. However, athena's document shows that it does : https://docs.aws.amazon.com/redshift/latest/dg/r_MD5.html

Not sure what I am missing here. If I transform varchar to varbinary then the hash that gets generated are not correct.

Getting this error : 


SYNTAX_ERROR: line 1:8: Unexpected parameters (varchar(15)) for function md5. Expected: md5(varbinary)

This query ran against the "temp" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: dd959e8a-7fa4-4170-8895-ce7cf58be6ea.```

Upvotes: 5

Views: 18664

Answers (2)

Ken Brobst
Ken Brobst

Reputation: 1

If you would like to generate an MD5 that is consistent with other databases (which take a string parameter for its MD5 function) you can do the following:

Within Athena: select to_hex(md5(to_utf8('hello world'))), which returns 5EB63BBBE01EEED093CB22BB8F5ACDC3

Within PostgreSQL: select md5('hello world'), which returns 5eb63bbbe01eeed093cb22bb8f5acdc3

Upvotes: 0

Theo
Theo

Reputation: 132902

The md5 function in Athena/Presto takes binary input. You can convert a string to a varbinary using the to_utf8 function:

SELECT md5(to_utf8('hello world'))

Upvotes: 12

Related Questions