Any real use case with UUID and Vertica

Kanako,

I found the following in one of the original requests for this feature in Jira. It's not much but possibly something to build on.

The request:
"I have had a few customers request either UUID or something similar (like GUID) data type support. Basically, the value needs to be stored and presented as hex. Under the covers it can be a numeric data type.

A typical UUID might look something like this:
00000000-0000-0000-0008-e21292830460

These are generated by MD5 and Akamai, so it is important when dealing with web hits records. Other use cases include storing Microsoft GUID. "

and a comment in the same Jira:
"Mobile data generally comes with a UUID that tracks a particular mobile device. Currently, our customers are having to work around this limitation by either storing the UUID as a varchar or using hash() on the UUID. Both of these have their problems though. Varchar join performance is not that great. Hash() is a good option, but introduces potential duplicates as the 128-bit UUID is condensed down to a 64-bit INT. Advertising companies will likely take advantage of this feature."

I hope it helps,

Hi,

I used to work at a company with .net developers and they loved to generate UUID values (i.e. GuIDs) and stick them in the Vertica database. Back then, we had to store them with the VARCHAR data type. Now we can store them as the UUID data type, where one benefit would be less storage usage.

dbadmin=> \d uuid_as_varchar;
                                        List of Fields by Tables
 Schema |      Table      | Column |    Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+-----------------+--------+-------------+------+---------+----------+-------------+-------------
 public | uuid_as_varchar | "uuid" | varchar(36) |   36 |         | f        | f           |
(1 row)

dbadmin=> select count(*) from uuid_as_varchar;
  count
---------
 1000000
(1 row)

dbadmin=> select audit('uuid_as_varchar');
  audit
----------
 36000000
(1 row)

dbadmin=> \d uuid_as_uuid;
                                   List of Fields by Tables
 Schema |    Table     | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+--------------+--------+------+------+---------+----------+-------------+-------------
 public | uuid_as_uuid | "uuid" | uuid |   16 |         | f        | f           |
(1 row)

dbadmin=> select count(*) from uuid_as_uuid;
  count
---------
 1000000
(1 row)

dbadmin=> select audit('uuid_as_uuid');
  audit
----------
 36000000
(1 row)

Although the raw license usage remains the same, note that the disk usage is significantly less:

dbadmin=> select used_bytes from projection_storage where projection_name ilike 'uuid_as_uuid%';
 used_bytes
------------
   16089831
(1 row)

dbadmin=> select used_bytes from projection_storage where projection_name ilike 'uuid_as_varchar%';
 used_bytes
------------
   31073512
(1 row)

dbadmin=> select 31073512 / 16089831 "Bigger by a Factor of...";
 Bigger by a Factor of...
--------------------------
     1.931251608547038188
(1 row)

Stephen, Jim, thanks a lot for your helps!! These are very helpful!