I have a Merge into statement that I use to update existing entries or create new entries in a dimension table based on a natural business key.
When creating new entries I would like to also create a unique uuid for that entry that I can use to crossreference with other tables.
However the merge into statement doesn't allow me to use `uuid()` .
Example code:
%sql WITH newData AS (
SELECT
MAC,
IP,
Name
FROM
bronze.ComputerLogs
) MERGE INTO silver.d_ComputerInfo AS s USING newData ON s.MAC = newData.MAC
WHEN MATCHED THEN
UPDATE
SET
s.IP = newData.IP,
s.Name = newData.Name,
WHEN NOT MATCHED THEN
INSERT
(
ComputerInfoId,
MAC,
IP,
Name
)
VALUES
(uuid(), MAC, IP, NAME)
This gives the following error
AnalysisException: nondeterministic expressions are only allowed in
Project, Filter, Aggregate, Window, or Generate, but found:
...
Is there a way to bypass this and still use randomly generated ids?