topic Re: How to use uuid in SQL merge into statement in Data Engineering

How to use uuid in SQL merge into statement

Merchiv — Wed, 30 Nov 2022 10:13:17 GMT

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?

Re: How to use uuid in SQL merge into statement

-werners- — Wed, 30 Nov 2022 13:21:02 GMT

you might wanna look into an identity column, which is possible now in delta lake.

https://www.databricks.com/blog/2022/08/08/identity-columns-to-generate-surrogate-keys-are-now-available-in-a-lakehouse-near-you.html

Re: How to use uuid in SQL merge into statement

Merchiv — Thu, 01 Dec 2022 08:41:20 GMT

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.

Re: How to use uuid in SQL merge into statement

-werners- — Thu, 01 Dec 2022 08:59:42 GMT

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.