Is there any way to create a column of uniqueidentifier type in pyspark/sql databricks?

Question

We are migrating our stored procedures from Synapse to Databricks. So, in synapse there is a table which has a column of "uniqueidentifier" type. When we do MAX on this column synapse gives us different max value as compared to databricks because in databricks this column is of STRING type (values are same in both cases).

Is there any equivalent of uniqueidentifier in databricks?

Answer 1

Databricks has a function -uuid() - to create GUID values, but the return value will be a string, and Databricks has no special type for GUID and will treat these values as String.

https://learn.microsoft.com/en-us/azure/databricks/sql/language-manual/functions/uuid

Answer 2

In the Synapse column, the UNIQUEIDENTIFIER data type is assigned, while in the Databricks column, the String data type is assigned. This is the reason for getting different values for the MAX function in both environments.

When you use the MAX function on a uniqueidentifier column in Synapse, it returns the maximum value based on the underlying byte representation of the UUID, which is ordered lexicographically. However, when you use the MAX function on a string column in Databricks SQL, it returns the maximum value based on string comparison rules, which might not align with the lexicographical order of UUIDs.

In Databricks, there is no data type named UNIQUEIDENTIFIER, but you can use the code below to create table and UUID values into it:

CREATE TABLE sample (
    id STRING,
    Name STRING
);
CREATE TEMPORARY VIEW uuids AS
SELECT CAST(uuid() AS STRING) AS id;

-- Insert sample values into the table with generated UUIDs
INSERT INTO sample (id, Name)
SELECT id, Name
FROM (
    SELECT id, 'John' AS Name FROM uuids UNION ALL
    SELECT id, 'Alice' FROM uuids UNION ALL
    SELECT id, 'Bob' FROM uuids UNION ALL
    SELECT id, 'Emma' FROM uuids
) AS data;

This will insert automatic UUID values into the table, as shown below: