Совместимость HASHBYTES SHA‑256 и BIGINT между SQL Server и PySpark

Разбираем, почему CAST(HASHBYTES('SHA2_256') AS BIGINT) в SQL Server даёт другое число, чем в PySpark, и показываем, как получить идентичный 64‑битный ключ.

При переносе ETL-логики из Synapse SQL в PySpark часто нужно воспроизвести существующий идентификатор строки, построенный с помощью HASHBYTES. Само значение SHA‑256 может совпадать побайтно между SQL и Python, но производный BIGINT расходится. В этом материале подробно показано, почему так происходит и как получить совместимое до бита 64‑битное целое.

Подготовка: как ключ формируется в SQL

В SQL идентификатор создаётся так: хешируется конкатенация значений столбцов с разделителем «|», после чего хеш приводится к BIGINT:

SELECT CAST(HASHBYTES('SHA2_256', CONCAT_WS('|', [col1], [col2], ...)) AS BIGINT) AS EtlHashKey
FROM sample_table;

Для простого примера, col1 = abc и col2 = 123, ожидается конкретное отрицательное значение BIGINT в SQL. При тех же входных данных в Python может получиться другой 64‑битный результат, даже если шестнадцатеричные дайджесты SHA‑256 совпадают. Именно это расхождение и является проблемой.

Воспроизведение проблемы в Python

Пример ниже показывает, что даже при, казалось бы, корректном подходе получается другой BIGINT по сравнению с SQL. Используются те же кодировка utf‑8 и обработка null, что и у CONCAT_WS, а шестнадцатеричный SHA‑256 совпадает с результатом HASHBYTES в SQL.

import hashlib
import struct
def calc_row_key(*parts):
    merged = '|'.join(['' if p is None else str(p) for p in parts])
    digest = hashlib.sha256(merged.encode('utf-8')).digest()
    key64 = struct.unpack('<q', digest[:8])[0]
    return key64

Код выдаёт корректное знаковое 64‑битное число, но оно не совпадает с результатом SQL CAST(HASHBYTES(...) AS BIGINT).

Почему числа расходятся

SHA‑256 возвращает 32 байта. Несоответствие возникает из‑за того, как эти 32 байта сводятся к 8 и как затем эти 8 байт интерпретируются. SQL Server берёт правые 8 байт 32‑байтового хеша и трактует их как BIGINT. А приведённый выше Python‑код берёт первые 8 байт и распаковывает их в порядке little‑endian.

Если выбрать правильные 8 байт и порядок байтов, итоговое 64‑битное число совпадёт с поведением CAST в SQL Server. Сырые значения хеша и так совпадали; отличались лишь выбранные 8 байт и способ их чтения.

Решение: повторить семантику CAST в SQL

Чтобы воспроизвести CAST(HASHBYTES('SHA2_256', ...) AS BIGINT), возьмите последние 8 байт дайджеста SHA‑256 и распакуйте их как знаковое 64‑битное целое в порядке big‑endian.

import hashlib
import struct
def build_etl_key(*fields):
    joined = '|'.join(['' if f is None else str(f) for f in fields])
    hbytes = hashlib.sha256(joined.encode('utf-8')).digest()
    etl_key = struct.unpack('>q', hbytes[-8:])[0]
    return etl_key

Эта правка выравнивает результат Python с BIGINT, полученным в SQL Server из HASHBYTES. Ключевые детали — правые 8 байт и интерпретация в формате big‑endian.

Почему это важно понимать

Сведение 32‑байтового дайджеста SHA‑256 к 8‑байтовому целому отбрасывает три четверти информации. Из‑за этого вероятность коллизий выше, чем при использовании полного бинарного значения. Такая практика сохраняется в некоторых конвейерах по историческим причинам или из‑за ограничений схемы, но важно осознавать компромисс. Если есть возможность хранить полный хеш как бинарные данные, шаг преобразования отпадает, а вероятность коллизий минимизируется относительно исходного алгоритма.

Итоги на практике

Если вам нужно получить тот же BIGINT, что и в SQL Server при CAST(HASHBYTES('SHA2_256', ...) AS BIGINT), вычислите SHA‑256, возьмите правые 8 байт и распакуйте их как знаковое 64‑битное значение в порядке big‑endian. Помните, что BIGINT хранит лишь часть сведений из хеша, поэтому, когда это возможно, предпочтительнее сохранять полный 32‑байтовый дайджест, чтобы лучше сохранять свойства уникальности.