Hash function in Spark SQL - Different strings all with the same hash generated

user_9658149 0
Viewed 954

I want to generate a different hash for each email, however, I found that I'm generating the same hash for differents emails, for example:

select hash('pipohecho@hotmail.com'),
       hash('rozas_huertas@hotmail.com'),
       hash('miguelilloooooooooouu@hotmail.com'),
       hash('rjdzpmsyi@hotmail.com'),
       hash('pepe@hotmail.com')

enter image description here

These cases: hash('pipohecho@hotmail.com'), hash('rozas_huertas@hotmail.com'), hash('miguelilloooooooooouu@hotmail.com'), hash('rjdzpmsyi@hotmail.com') generate the same hash -1517714944, then I have two questions:

  1. How this is possible?
  2. How can I generate a unique hash for each email with Spark SQL?

Thanks

apache-spark-sql apache-spark hash
2 Answers

Seems there is article regarding collision in Hash probabilities here.

Try using either xxhash64(from spark-3),md5,sha2 functions to get the unique hash value.

Example:

spark.sql("""select xxhash64('pipohecho@hotmail.com'),
       xxhash64('rozas_huertas@hotmail.com'),
       xxhash64('miguelilloooooooooouu@hotmail.com'),
       xxhash64('rjdzpmsyi@hotmail.com'),
       xxhash64('pepe@hotmail.com')""").show()

#+-------------------------------+-----------------------------------+-------------------------------------------+-------------------------------+--------------------------+
#|xxhash64(pipohecho@hotmail.com)|xxhash64(rozas_huertas@hotmail.com)|xxhash64(miguelilloooooooooouu@hotmail.com)|xxhash64(rjdzpmsyi@hotmail.com)|xxhash64(pepe@hotmail.com)|
#+-------------------------------+-----------------------------------+-------------------------------------------+-------------------------------+--------------------------+
#|6332927369894443419            |-8140372026824474906               |-9124920009896762502                       |1936246589584419991            |954028670536665140        |
#+-------------------------------+-----------------------------------+-------------------------------------------+-------------------------------+--------------------------+


spark.sql("""select md5('pipohecho@hotmail.com'),
       md5('rozas_huertas@hotmail.com'),
       md5('miguelilloooooooooouu@hotmail.com'),
       md5('rjdzpmsyi@hotmail.com'),
       md5('pepe@hotmail.com')""").show()

#+------------------------------------------+----------------------------------------------+------------------------------------------------------+------------------------------------------+-------------------------------------+
#|md5(CAST(pipohecho@hotmail.com AS BINARY))|md5(CAST(rozas_huertas@hotmail.com AS BINARY))|md5(CAST(miguelilloooooooooouu@hotmail.com AS BINARY))|md5(CAST(rjdzpmsyi@hotmail.com AS BINARY))|md5(CAST(pepe@hotmail.com AS BINARY))|
#+------------------------------------------+----------------------------------------------+------------------------------------------------------+------------------------------------------+-------------------------------------+
#|7ce30aa0209335873f79e64c2eb465ff          |9d58c495ab87f2e3a4a9adc6c8fbbb76              |c283a7c6f09712fc5ba4ea30334e2c25                      |6766da691171aa5c56a70b89bd4590fa          |ab888b1a15b420b410d23b927a370013     |
#+------------------------------------------+----------------------------------------------+------------------------------------------------------+------------------------------------------+-------------------------------------+


spark.sql("""select sha2('pipohecho@hotmail.com',256),
       sha2('rozas_huertas@hotmail.com',256),
       sha2('miguelilloooooooooouu@hotmail.com',256),
       sha2('rjdzpmsyi@hotmail.com',256),
       sha2('pepe@hotmail.com',256)""").show()

#+----------------------------------------------------------------+----------------------------------------------------------------+----------------------------------------------------------------+----------------------------------------------------------------+----------------------------------------------------------------+
#|sha2(CAST(pipohecho@hotmail.com AS BINARY), 256)                |sha2(CAST(rozas_huertas@hotmail.com AS BINARY), 256)            |sha2(CAST(miguelilloooooooooouu@hotmail.com AS BINARY), 256)    |sha2(CAST(rjdzpmsyi@hotmail.com AS BINARY), 256)                |sha2(CAST(pepe@hotmail.com AS BINARY), 256)                     |
#+----------------------------------------------------------------+----------------------------------------------------------------+----------------------------------------------------------------+----------------------------------------------------------------+----------------------------------------------------------------+
#|02068bc029cd26888a4ba630ecfa91b4afc2bf72c4adeabcfcd32459529c61bb|391af34e53d82ce8f12a1396d5ae74d96f3ea583cf3fd864816b29586ed002f8|fde18d7d27497717a8a77a0eace29ad5dbcb7319637be033c3e66a068a2bd983|b07300bee7e68326143c40f75b608201f5db667a18bb73b63f9f909454521753|921efc4884d3c8a32899c079024386641564ec0d0966cc059429bbd33770e421|
#+----------------------------------------------------------------+----------------------------------------------------------------+----------------------------------------------------------------+----------------------------------------------------------------+----------------------------------------------------------------+
user_76326950

The current implementation of hash in Spark uses MurmurHash, more specifically MurmurHash3. MurmurHash, as well as the xxHash function available as xxhash64 in Spark 3.0.0+, is a non-cryptographic hash function, which means it was not specifically designed to be hard to invert or to be free of collisions. Both MurmurHash and xxHash are meant to be really quick while providing good enough spread of the hash values so that they could be useful for hash-based lookup. A typical use of such hashing functions is the implementation of a hash table where the key is mapped to a bucket and each bucked has a linked list of key/value pairs (kvp's). In that case, collisions are not fatal - they simply lead to longer kvp lists that take longer to traverse. There is an extensive cryptanalysis of MurmurHash.

As suggested by @notNull, you should instead use a cryptographic hash function instead, such as SHA-2 or MD5. I would also advise to avoid using MD5 if the hash values are ever stored somewhere and to salt the emails before hashing with a fixed but randomly chosen (e.g., during the deployment) secret:

select sha2(concat('39u!6fgs3#', 'email@domain.com'), 256)
--       fixed salt ---^^^     value ---^^^

Salting the values before hashing them makes it hard to impossible for someone who doesn't know the value of the salt to brute-force reverse the hashes if they somehow leak out.

user_13744370
Related