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]