snowflake insert new UUID column with conditions

user_15632250 0
Viewed 962

I have created a new table

create or replace TABLE MARKET_SAMPLE (
    BRAND_ID NUMBER(38,0),
    DRUG_ID NUMBER(38,0),
    SERIAL_NUMBER VARCHAR(16777216),
    MARKET_ID NUMBER(38,0),
    CLASS_1 VARCHAR(16777216),
    CLASS_2 VARCHAR(16777216),
    CLASS_3 VARCHAR(16777216),
    IS_KEY_COMPETITOR BOOLEAN,
    EFFECTIVE_DATE DATE,
    END_DATE DATE,
    IS_CURRENT_FLAG BOOLEAN,
    ID VARCHAR(16777216),
    **FACTORS_ID** VARCHAR(16777216),
    CREATED_BY VARCHAR(16777216),
    UPDATED_BY VARCHAR(16777216),
    CREATED_DATE DATE,
    UPDATED_DATE DATE,
    LEO_DRUG_FLAG BOOLEAN,
    LEO_EXCLUSION_FLAG BOOLEAN
);

I will load all columns apart from FACTORS_ID from another table using insert query FACTORS_ID is a UUID column this column has to load based on conditions on the self table. conditions : ex: I have 3 records in the table, first 2 records have the same MARKET_ID, CLASS_1, CLASS_2, CLASS_3, BRAND_ID for these 2 records we must have the same FACTORS_ID value.

Appreciate any best solutions, thanks.

snowflake-cloud-data-platform uuid sql
2 Answers

Generate UUID for all rows in a dataset, then in the upper subquery use min() or max() analytic function to populate the same UUID for all records in a group. You can calculate it during insert:

select 
    BRAND_ID,
    DRUG_ID,
    SERIAL_NUMBER,
    MARKET_ID,
    CLASS_1,
    CLASS_2,
    CLASS_3,
    IS_KEY_COMPETITOR,
    EFFECTIVE_DATE,
    END_DATE,
    IS_CURRENT_FLAG,
    ID,
    max(FACTORS_ID) over (partition by MARKET_ID, CLASS_1, CLASS_2, CLASS_3, BRAND_ID) as FACTORS_ID,
    CREATED_BY,
    UPDATED_BY,
    CREATED_DATE,
    UPDATED_DATE,
    LEO_DRUG_FLAG,
    LEO_EXCLUSION_FLAG
    
from 
(
select  ---calculate all columns and UUID
    BRAND_ID,
    DRUG_ID,
    SERIAL_NUMBER,
    MARKET_ID,
    CLASS_1,
    CLASS_2,
    CLASS_3,
    IS_KEY_COMPETITOR,
    EFFECTIVE_DATE,
    END_DATE,
    IS_CURRENT_FLAG,
    ID,
    UUID_STRING() as FACTORS_ID,
    CREATED_BY,
    UPDATED_BY,
    CREATED_DATE,
    UPDATED_DATE,
    LEO_DRUG_FLAG,
    LEO_EXCLUSION_FLAG
  from ...
) s

If you are going to merge from another table and keep UUID for old records the same, just do not update it. Also you can join the this dataset with target table and keep the same UUID for existing records

user_27003440

Below are few things to note and consider-

  1. You can use the UUID_STRING() in snowflake to generate the UUID
  2. In this case where the value should be based on few columns and completely not random (maybe we can use some hashing function [not sure if there some business limitation to this])

And if we need UUID only, the UUID_STRING() has version5 variant in snowflake which accepts some input value and generate the same UUID every time[given that inputs remain same], so input value could be a hash of the columns mentioned which drive the distinctiveness of the UUID column.

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

Thanks, Lal

user_196945000
Related