Ganesha S created SPARK-61770:
---------------------------------

             Summary: Add farm_fingerprint SQL function
                 Key: SPARK-61770
                 URL: https://issues.apache.org/jira/browse/SPARK-61770
             Project: Spark
          Issue Type: Improvement
          Components: SQL
    Affects Versions: 4.4.0
            Reporter: Ganesha S


*Summary*
Add a built-in farm_fingerprint(expr) function. It returns a stable 64-bit 
signed hash (BIGINT) of a STRING or BINARY input, using FarmHash Fingerprint64.

 
{code:java}
SELECT farm_fingerprint('hello');      -- deterministic BIGINT
SELECT farm_fingerprint(CAST('abc' AS BINARY));
SELECT farm_fingerprint(NULL);         -- NULL{code}
 

 

*Motivation*
FARM_FINGERPRINT is widely used in BigQuery and GoogleSQL for deterministic 
sampling, bucketing, surrogate keys and dedup, for example 
MOD(ABS(FARM_FINGERPRINT(id)), 10) < 1. Spark has no equivalent. Users have to 
rewrite these queries, and hash() and xxhash64() give different values, so 
results change after migration.

 

*Benefits*
- Easy migration: BigQuery and GoogleSQL queries that use FARM_FINGERPRINT run 
on Spark unchanged and give the same results.
- Same values across systems: Spark hashes match hashes from BigQuery and other 
FarmHash-based systems, so joins, dedup and sampling work across systems.
- Safe to store: Fingerprint64 is designed never to change across versions, 
platforms or languages. Hashes saved today still match years later.
- Fewer collisions: A 64-bit output collides far less often than the 32-bit 
hash(), so it works better for surrogate keys and dedup on large datasets.
- Repeatable sampling and bucketing: Train/test splits, A/B buckets and data 
samples come out the same on every run, cluster and engine.
- Fast and lightweight: It's a non-cryptographic hash built for speed, and it 
uses Guava, which Spark already depends on, so there are no new dependencies.

 

*Impact*
- New function only, so there are no behavior changes to existing queries.
- Removes a common blocker when moving BigQuery and GoogleSQL workloads to 
Spark.



--
This message was sent by Atlassian Jira
(v10.3.26#10030026)

---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to