[ 
https://issues.apache.org/jira/browse/SPARK-61770?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18145448#comment-18145448
 ] 

Ganesha S commented on SPARK-61770:
-----------------------------------

I'm working on the design and implementation and will open a PR shortly. 

*Proposed Changes*
- Add FarmFingerprint expression, registered as farm_fingerprint, plus 
Scala/Python/Connect DataFrame APIs.
- Build it on Guava's Hashing.farmHashFingerprint64().
- Support codegen. NULL in gives NULL out. Strings are hashed as UTF-8 bytes.

*Testing*
- Golden-value tests checked against BigQuery output (empty string, ASCII, 
Unicode, binary, NULL).
- Codegen and interpreted parity, plus SQL function docs and examples.

> 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
>            Priority: Major
>
> *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