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.

Solution

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)

Comments

[dfrankow]

That looks great, but doing it resulted in this for me: "_col0 [B@3bed3386"

[Jan Dörrenhaus]

@dfrankow md5 results in varbinary. You could turn it into a hex string yourself. (see edit)