[
https://issues.apache.org/jira/browse/SPARK-61770?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Wenchen Fan reassigned SPARK-61770:
-----------------------------------
Assignee: Ganesha S
> 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
> Assignee: Ganesha S
> Priority: Major
> Labels: pull-request-available
>
> *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]