- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-30-2022 02:13 AM
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?
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-01-2022 12:59 AM
A ok, I thought you were looking for that.
In that case:
have you tested adding the UUID in the newData DF beforehand?
Because it could be a limitiation of the merge itself.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-30-2022 05:21 AM
you might wanna look into an identity column, which is possible now in delta lake.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-01-2022 12:41 AM
We have used tested this, but these are incremental keys that require syncing if we manually insert some extra keys, which makes them not practical.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-01-2022 12:59 AM
A ok, I thought you were looking for that.
In that case:
have you tested adding the UUID in the newData DF beforehand?
Because it could be a limitiation of the merge itself.

