What’s New in Vertica 9.0: The UUID Data Type

This blog post was authored by Gary Gray.

Vertica version 9.0 adds Universal Unique Identifier (UUID) to its collection of data types. Accompanying this new data type are updates to the client libraries and a new function to help you use UUIDs in your database.

As its name implies, computers use UUIDs to uniquely identify records, events, or anything else that needs to be tracked. When properly generated, UUIDs are virtually guaranteed to be unique without the need to consult a central registry. This feature makes them well-suited for applications where data is generated independently in multiple locations and then consolidated in a central location (like, say, in a Vertica database…).

Prior to Vertica version 9.0, your best option for storing UUID values in Vertica was to use CHAR or VARCHAR columns to store a hexadecimal representation of the value. These columns could be up to 40 characters wide (depending on the format you used for the UUID’s text representation).

The UUID data type has two main advantages over using VARCHAR representation of a UUID:

• Vertica stores UUIDs as a 16 byte value. This is less than half the size of a VARCHAR column for storing UUID values.
• Vertica can compare UUID data type values to each other faster because there is less data to compare (16 bytes vs. up to 36 bytes depending on the format of the UUID).

Creating a UUID Column

You create a UUID column in a table by using the UUID keyword as the column’s data type: => CREATE TABLE account_transactions (id UUID NOT NULL PRIMARY KEY, amount MONEY, account INTEGER, note VARCHAR(100)); CREATE TABLE

Loading UUID Values

You load UUID values from files using several different hexadecimal text representation formats. Vertica supports all of the formats described in RFC-4112. => CREATE TABLE uuid_demo (val UUID, message VARCHAR(60)); CREATE TABLE => COPY uuid_demo FROM STDIN; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 12345678-9012-3456-7890-123456789012|Message #1 >> {aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee}|Message #2 >> 12345678901234567890123456789012|Message #3 >> 0123-4567-890a-bced-ef01-2345-6789-0abc|Message #4 >> \. => SELECT * FROM uuid_demo; val | message --------------------------------------+------------ 01234567-890a-bced-ef01-234567890abc | Message #4 12345678-9012-3456-7890-123456789012 | Message #3 aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee | Message #2 12345678-9012-3456-7890-123456789012 | Message #1 (4 rows)

Generating UUIDs

In addition to loading UUID values, you can also generate them within Vertica using the uuid_generate() function. This function is based on the GNU UUID library. => SELECT uuid_generate(); uuid_generate -------------------------------------- 2e5c08aa-5ae5-455c-a715-6f90699fc478 (1 row) => INSERT INTO uuid_demo VALUES (uuid_generate(), 'Message #5'); OUTPUT -------- 1 (1 row) => SELECT * FROM uuid_demo; val | message --------------------------------------+------------ 01234567-890a-bced-ef01-234567890abc | Message #4 12345678-9012-3456-7890-123456789012 | Message #3 1dd37cae-ed8f-4aa4-b17e-9f0b90e358ba | Message #5 aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee | Message #2 12345678-9012-3456-7890-123456789012 | Message #1 (5 rows) The uuid_generate function generates completely random UUIDs which have less of a guarantee of uniqueness. If you want to generate a UUID that time-based (as discussed in RFC-4112), use the Linux system’s uuidgen command with the –time argument: => \! uuidgen --time e4a9be2e-c620-11e7-8b3f-0050568e728b You need to use a bit of command-line magic to get the UUID value enclosed in quotes so it can be inserted into a table: => \set uuid `uuidgen --time | sed -e "s/.*/'&'/"` => \echo :uuid '70c4f8e4-c624-11e7-8d18-0050568e728b' => INSERT INTO uuid_demo VALUES (:uuid, 'Message #6'); OUTPUT -------- 1 (1 row) => SELECT * FROM uuid_demo; val | message --------------------------------------+------------ 01234567-890a-bced-ef01-234567890abc | Message #4 12345678-9012-3456-7890-123456789012 | Message #3 1dd37cae-ed8f-4aa4-b17e-9f0b90e358ba | Message #5 70c4f8e4-c624-11e7-8d18-0050568e728b | Message #6 aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee | Message #2 12345678-9012-3456-7890-123456789012 | Message #1 (6 rows)

Converting an Existing Column from VARCHAR to UUID

If you have existing tables with UUIDs stored in VARCHAR columns, you can convert them to UUID columns. See the Vertica documentation topic Working with Data Column Conversions for instructions to convert a column from one data type to another.

Client Library Support

The Vertica client libraries currently do not have full compatibility with the UUID data type. They do have basic compatibility: most client library drivers treat UUID values as hexadecimal string values. When a client application inserts a UUID value in text format, Vertica translates the value into native UUID format before inserting it into a table. When a client application queries a column with a UUID value, Vertica translates the native UUID value into a string representation before sending it to the client.

The JDBC driver is currently the only driver that does not treat UUID values as strings. Instead, UUID values are handled as Java objects. Java provides the the java.util.UUID class to represent UUID values. These objects are not directly supported by the JDBC library. Instead, you use generic object methods to insert and retrieve UUID values.

To insert values into a Vertica table using JDBC, use generic object methods (such as PreparedStatement.setObject()). To retrieve a UUID value from Vertica, use a generic object method such as ResultSet.getObject() and cast the result as a member of the java.util.UUID class. See the UUID Values topic in the JDBC section of the Vertica documentation for more information and example code.

Note: You can also treat UUUD values as Strings in the JDBC client (for example, by using PreparedStatement.setString()). You may choose to use Strings if you are not using the java.util.UUID class to represent UUIDs.

Improved client driver support for the UUID data type is planned for future Vertica versions.

Where to Go From Here

For more information about UUIDs in Vertica, see the following topics in the Vertica documentation:

• The UUID Data Type.
• The UUID_GENERATE function.