How do I run md5() on a bigint in Presto?

Question

select md5(15)

returns

Query failed (#20160818_193909_00287_8zejd): line 1:8:
 Unexpected parameters (bigint) for function md5. Expected: md5(varbinary)

How do I hash 15 and get back a string? I'd like to select 1 in 16 items at random, e.g. where md5(id) like '%3'.

FYI I might be on version 0.147, don't know how to tell. FYI I found this PR. md5 would be cross-platform, which is nice, but I'd take a Presto-dependent hash function that spread ids relatively uniformly. I suppose I could implement my own linear formula. Seems awkward.

Jan Dörrenhaus · Accepted Answer · 2016-08-31T11:18:07.950

18

Best thing I could come up with was to cast the integer as a varchar, then turn it into varbinary via utf8, then apply md5 on the varbinary:

presto> select md5(to_utf8(cast(15 as varchar)));
                      _col0
-------------------------------------------------
 9b f3 1c 7f f0 62 93 6a 96 d3 c8 bd 1f 8f 2f f3
(1 row)

If this is not the result you get, you can always turn it into a hex string manually:

presto> select to_hex(md5(to_utf8(cast(15 as varchar))));
              _col0
----------------------------------
 9BF31C7FF062936A96D3C8BD1F8F2FF3
(1 row)

edited Aug 31 '16 at 11:18

answered Aug 30 '16 at 13:22

Jan Dörrenhaus

6,581
2
34
45

That looks great, but doing it resulted in this for me: "_col0 [B@3bed3386" – dfrankow Aug 30 '16 at 13:52
@dfrankow md5 results in varbinary. You could turn it into a hex string yourself. (see edit) – Jan Dörrenhaus Aug 31 '16 at 11:16

How do I run md5() on a bigint in Presto?

1 Answers1