Блог

Есть ли простой / эффективный способ создания суррогатных ключей в Snowflake?

Представьте, что этот набор данных будет выбран в таблице, во время вставки добавляется столбец battery_id, который представляет собой столбец battery_uuid, сопоставленный с суррогатным ключом. В данном случае конкретный случай, который может стать, например, [1,1,2,1,1,2,3] . Последовательности в Snowflake не имеют такой функциональности, потому что это становится, например, [1,2,3,4,5,6,7] . Предпочтительнее, если это сопоставление выполняется во время вставки, однако при необходимости это также можно сделать после вставки. Как контекст: этот столбец добавлен, потому что соединения в UUID не являются производительными.

Если ваша цель — превратить строку в число для повышения производительности соединения, я рекомендую использовать HASH функцию. В вашем случае вы могли бы просто создать новый столбец и обновить значения HASH(battery_uuid) , чтобы создать суррогатный ключ. А затем используйте этот новый столбец для своих объединений. Если ваш естественный ключ содержит более 2 столбцов, это все равно будет работать для вас, поскольку HASH функция позволяет использовать более одного столбца.

https://docs.snowflake.com/en/sql-reference/functions/hash.html

Вот как вы можете это сделать

 CREATE TABLE thetable (event_id string, batt_id string, serial_number string, batt_key number);
INSERT INTO thetable VALUES('1','bat1','1',0);
INSERT INTO thetable VALUES('2','bat1','1',0);
 

итак, с таблицей в базовой форме, которую вы хотите, с некоторыми фиктивными строками, чтобы показать «уже существующие данные, которые мы хотим использовать существующий ключ на основе battery_id. Я сократил имена столбцов по причинам ввода и использовал числовую строку вместо UUID, но точка все еще сохраняется.

 CREATE OR REPLACE SEQUENCE batt_key_seq;

WITH new_data(event_id, batt_id, serial_number) AS (
    SELECT * FROM VALUES 
        ('11','bat1', '10'),
        ('12','bat1', '10'),
        ('13','bat2', '11'),
        ('14','bat1', '10'),
        ('15','bat1', '10'),
        ('16','bat2', '11'),
        ('17','bat3', '12')
), new_data_with_keys AS (
    SELECT d.batt_id
        ,COALESCE(t.batt_key, batt_key_seq.nextval) AS batt_key
    FROM (
        SELECT DISTINCT batt_id 
        FROM new_data
    ) AS d
    LEFT JOIN thetable AS t 
        ON d.batt_id = t.batt_id
    QUALIFY row_number() OVER (PARTITION BY d.batt_id ORDER BY event_id) = 1
)
SELECT n.*, k.batt_key
FROM new_data AS n
JOIN new_data_with_keys AS k 
    ON n.batt_id = k.batt_id
;
 

таким образом, часть ROW_NUMBER() состоит в том, чтобы захватить только одну из существующих строк (таким образом, двойная вставка данных в существующие данные в начале), я чувствую, что это может быть более чистым каким-то другим способом, но он выполняет свою работу.

Но на самом деле вы получаете свои ключи, находите существующие ключи и даете новые ключи к новым данным, а затем можете вставить данные в эту обогащенную форму.

что я замечаю при тестировании, так это то, что мой ключ прыгает на 4 при каждом запуске и меняется на

 ,IFF(t.batt_key is not null, t.batt_key, batt_key_seq.nextval) AS batt_key
 

я этого не исправил.

Таким образом, разделяя ключевые части поиска, такие как

 WITH new_data(event_id, batt_id, serial_number) AS (
    SELECT * FROM VALUES 
        ('11','bat1', '10'),
        ('12','bat1', '10'),
        ('13','bat2', '11'),
        ('14','bat1', '10'),
        ('15','bat1', '10'),
        ('16','bat2', '11'),
        ('17','bat3', '12')
), missing_keys AS (
    SELECT d.batt_id
        ,batt_key_seq.nextval AS batt_key
    FROM (
        SELECT DISTINCT batt_id 
        FROM new_data
    ) AS d
    LEFT JOIN thetable AS t 
        ON d.batt_id = t.batt_id
    WHERE t.batt_id IS NULL
), old_keys AS (
    SELECT distinct d.batt_id
        ,t.batt_key
    FROM (
        SELECT DISTINCT batt_id 
        FROM new_data
    ) AS d
    LEFT JOIN thetable AS t 
        ON d.batt_id = t.batt_id
    WHERE t.batt_id IS NOT NULL
), new_data_with_keys AS (
    SELECT * FROM missing_keys
    UNION ALL
    SELECT * FROM old_keys
)
SELECT n.*, k.batt_key
FROM new_data AS n
JOIN new_data_with_keys AS k 
    ON n.batt_id = k.batt_id
;
 

теперь новые ключи не пропущены / потрачены впустую.

Разница в производительности при потере пространства клавиш стиха с двойным соединением — это то, что вам может понадобиться для тестирования и / или понимания того, сколько новых или существующих данных вы увидите и, следовательно, сколько потерь произойдет. Если производительность второго сильно отличается, вы всегда можете повторно ввести данные позже, хотя в этих ключах используются вне таблицы, в которой может быть много данных для последующего обновления.