Relational Data - Functions - Uuid Functions

From FojiSoft Docs
Revision as of 18:52, 28 August 2024 by Chris.Hansen (talk | contribs) (Import ClickHouse Docs: Wed Aug 28 2024 14:52:42 GMT-0400 (Eastern Daylight Time))
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

61f0c404-5cb3-11e7-907b-a6006ad3dba0’, CAST(‘59f0c404-5cb3-11e7-907b-a6006ad3dba0’, ‘UUID’))─┐ │ 59f0c404-5cb3-11e7-907b-a6006ad3dba0 │ └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ 

## toUUIDOrNull

Takes an argument of type String and tries to parse it into UUID. If failed, returns NULL.

``` sql
toUUIDOrNull(string)

Returned value

The Nullable(UUID) type value.

Usage example 

SELECT toUUIDOrNull('61f0c404-5cb3-11e7-907b-a6006ad3dba0T') AS uuid

Result: 

┌─uuid─┐
│ ᴺᵁᴸᴸ │
└──────┘

toUUIDOrZero

It takes an argument of type String and tries to parse it into UUID. If failed, returns zero UUID. 

toUUIDOrZero(string)

Returned value

The UUID type value.

Usage example 

SELECT toUUIDOrZero('61f0c404-5cb3-11e7-907b-a6006ad3dba0T') AS uuid

Result: 

┌─────────────────────────────────uuid─┐
│ 00000000-0000-0000-0000-000000000000 │
└──────────────────────────────────────┘

UUIDStringToNum

Accepts string containing 36 characters in the format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, and returns a FixedString(16) as its binary representation, with its format optionally specified by variant (Big-endian by default).

Syntax 

UUIDStringToNum(string[, variant = 1])

Arguments

  • string — A String of 36 characters or FixedString
  • variant — Integer, representing a variant as specified by RFC4122. 1 = Big-endian (default), 2 = Microsoft.

Returned value

FixedString(16)

Usage examples 

SELECT
    '612f3c40-5d3b-217e-707b-6a546a3d7b29' AS uuid,
    UUIDStringToNum(uuid) AS bytes

Result: 

┌─uuid─────────────────────────────────┬─bytes────────────┐
│ 612f3c40-5d3b-217e-707b-6a546a3d7b29 │ a/<@];!~p{jTj={) │
└──────────────────────────────────────┴──────────────────┘
SELECT
    '612f3c40-5d3b-217e-707b-6a546a3d7b29' AS uuid,
    UUIDStringToNum(uuid, 2) AS bytes

Result: 

┌─uuid─────────────────────────────────┬─bytes────────────┐
│ 612f3c40-5d3b-217e-707b-6a546a3d7b29 │ @</a;]~!p{jTj={) │
└──────────────────────────────────────┴──────────────────┘

UUIDNumToString

Accepts binary containing a binary representation of a UUID, with its format optionally specified by variant (Big-endian by default), and returns a string containing 36 characters in text format.

Syntax 

UUIDNumToString(binary[, variant = 1])

Arguments

  • binary — FixedString(16) as a binary representation of a UUID.
  • variant — Integer, representing a variant as specified by RFC4122. 1 = Big-endian (default), 2 = Microsoft.

Returned value

String.

Usage example 

SELECT
    'a/<@];!~p{jTj={)' AS bytes,
    UUIDNumToString(toFixedString(bytes, 16)) AS uuid

Result: 

┌─bytes────────────┬─uuid─────────────────────────────────┐
│ a/<@];!~p{jTj={) │ 612f3c40-5d3b-217e-707b-6a546a3d7b29 │
└──────────────────┴──────────────────────────────────────┘
SELECT
    '@</a;]~!p{jTj={)' AS bytes,
    UUIDNumToString(toFixedString(bytes, 16), 2) AS uuid

Result: 

┌─bytes────────────┬─uuid─────────────────────────────────┐
│ @</a;]~!p{jTj={) │ 612f3c40-5d3b-217e-707b-6a546a3d7b29 │
└──────────────────┴──────────────────────────────────────┘

UUIDToNum

Accepts a UUID and returns its binary representation as a FixedString(16), with its format optionally specified by variant (Big-endian by default). This function replaces calls to two separate functions UUIDStringToNum(toString(uuid)) so no intermediate conversion from UUID to string is required to extract bytes from a UUID.

Syntax 

UUIDToNum(uuid[, variant = 1])

Arguments

  • uuid — UUID.
  • variant — Integer, representing a variant as specified by RFC4122. 1 = Big-endian (default), 2 = Microsoft.

Returned value

The binary representation of the UUID.

Usage examples 

SELECT
    toUUID('612f3c40-5d3b-217e-707b-6a546a3d7b29') AS uuid,
    UUIDToNum(uuid) AS bytes

Result: 

┌─uuid─────────────────────────────────┬─bytes────────────┐
│ 612f3c40-5d3b-217e-707b-6a546a3d7b29 │ a/<@];!~p{jTj={) │
└──────────────────────────────────────┴──────────────────┘
SELECT
    toUUID('612f3c40-5d3b-217e-707b-6a546a3d7b29') AS uuid,
    UUIDToNum(uuid, 2) AS bytes

Result: 

┌─uuid─────────────────────────────────┬─bytes────────────┐
│ 612f3c40-5d3b-217e-707b-6a546a3d7b29 │ @</a;]~!p{jTj={) │
└──────────────────────────────────────┴──────────────────┘

UUIDv7ToDateTime

Returns the timestamp component of a UUID version 7.

Syntax 

UUIDv7ToDateTime(uuid[, timezone])

Arguments

Returned value

  • Timestamp with milliseconds precision. If the UUID is not a valid version 7 UUID, it returns 1970-01-01 00:00:00.000. DateTime64(3).

Usage examples 

SELECT UUIDv7ToDateTime(toUUID('018f05c9-4ab8-7b86-b64e-c9f03fbd45d1'))

Result: 

┌─UUIDv7ToDateTime(toUUID('018f05c9-4ab8-7b86-b64e-c9f03fbd45d1'))─┐
│                                          2024-04-22 15:30:29.048 │
└──────────────────────────────────────────────────────────────────┘
SELECT UUIDv7ToDateTime(toUUID('018f05c9-4ab8-7b86-b64e-c9f03fbd45d1'), 'America/New_York')

Result: 

┌─UUIDv7ToDateTime(toUUID('018f05c9-4ab8-7b86-b64e-c9f03fbd45d1'), 'America/New_York')─┐
│                                                              2024-04-22 08:30:29.048 │
└──────────────────────────────────────────────────────────────────────────────────────┘

serverUUID

Returns the random UUID generated during the first start of the ClickHouse server. The UUID is stored in file uuid in the ClickHouse server directory (e.g. /var/lib/clickhouse/) and retained between server restarts.

Syntax 

serverUUID()

Returned value

  • The UUID of the server. UUID.

generateSnowflakeID

Generates a Snowflake ID.

The generated Snowflake ID contains the current Unix timestamp in milliseconds (41 + 1 top zero bits), followed by a machine id (10 bits), and a counter (12 bits) to distinguish IDs within a millisecond. For any given timestamp (unix_ts_ms), the counter starts at 0 and is incremented by 1 for each new Snowflake ID until the timestamp changes. In case the counter overflows, the timestamp field is incremented by 1 and the counter is reset to 0.

Function generateSnowflakeID guarantees that the counter field within a timestamp increments monotonically across all function invocations in concurrently running threads and queries.

The generated Snowflake IDs are based on the UNIX epoch 1970-01-01. While no standard or recommendation exists for the epoch of Snowflake IDs, implementations in other systems may use a different epoch, e.g. Twitter/X (2010-11-04) or Mastodon (2015-01-01). 


 0                   1                   2                   3
 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1
├─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┤
|0|                         timestamp                           |
├─┼                 ┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┼─┤
|                   |     machine_id    |    machine_seq_num    |
└─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┴─┘

Syntax 

generateSnowflakeID([expr, [machine_id]])

Arguments

  • expr — An arbitrary expression used to bypass common subexpression elimination if the function is called multiple times in a query. The value of the expression has no effect on the returned Snowflake ID. Optional.
  • machine_id — A machine ID, the lowest 10 bits are used. Int64. Optional.

Returned value

A value of type UInt64.

Example

First, create a table with a column of type UInt64, then insert a generated Snowflake ID into the table. 

CREATE TABLE tab (id UInt64) ENGINE = Memory;

INSERT INTO tab SELECT generateSnowflakeID();

SELECT * FROM tab;

Result: 

┌──────────────────id─┐
│ 7199081390080409600 │
└─────────────────────┘

Example with multiple Snowflake IDs generated per row 

SELECT generateSnowflakeID(1), generateSnowflakeID(2);

┌─generateSnowflakeID(1)─┬─generateSnowflakeID(2)─┐
│    7199081609652224000 │    7199081609652224001 │
└────────────────────────┴────────────────────────┘

Example with expression and a machine ID 

SELECT generateSnowflakeID('expr', 1);

┌─generateSnowflakeID('expr', 1)─┐
│            7201148511606784002 │
└────────────────────────────────┘

snowflakeToDateTime

This function is deprecated and can only be used if setting allow_deprecated_snowflake_conversion_functions is enabled. The function will be removed at some point in future.


Extracts the timestamp component of a Snowflake ID in DateTime format.

Syntax 

snowflakeToDateTime(value[, time_zone])

Arguments

  • value — Snowflake ID. Int64.
  • time_zone — Timezone. The function parses time_string according to the timezone. Optional. String.

Returned value

  • The timestamp component of value as a DateTime value.

Example

Query: 

SELECT snowflakeToDateTime(CAST('1426860702823350272', 'Int64'), 'UTC');

Result: 

┌─snowflakeToDateTime(CAST('1426860702823350272', 'Int64'), 'UTC')─┐
│                                              2021-08-15 10:57:56 │
└──────────────────────────────────────────────────────────────────┘

snowflakeToDateTime64

This function is deprecated and can only be used if setting allow_deprecated_snowflake_conversion_functions is enabled. The function will be removed at some point in future.


Extracts the timestamp component of a Snowflake ID in DateTime64 format.

Syntax 

snowflakeToDateTime64(value[, time_zone])

Arguments

  • value — Snowflake ID. Int64.
  • time_zone — Timezone. The function parses time_string according to the timezone. Optional. String.

Returned value

  • The timestamp component of value as a DateTime64 with scale = 3, i.e. millisecond precision.

Example

Query: 

SELECT snowflakeToDateTime64(CAST('1426860802823350272', 'Int64'), 'UTC');

Result: 

┌─snowflakeToDateTime64(CAST('1426860802823350272', 'Int64'), 'UTC')─┐
│                                            2021-08-15 10:58:19.841 │
└────────────────────────────────────────────────────────────────────┘

dateTimeToSnowflake

This function is deprecated and can only be used if setting allow_deprecated_snowflake_conversion_functions is enabled. The function will be removed at some point in future.


Converts a DateTime value to the first Snowflake ID at the giving time.

Syntax 

dateTimeToSnowflake(value)

Arguments

Returned value

  • Input value converted to the Int64 data type as the first Snowflake ID at that time.

Example

Query: 

WITH toDateTime('2021-08-15 18:57:56', 'Asia/Shanghai') AS dt SELECT dateTimeToSnowflake(dt);

Result: 

┌─dateTimeToSnowflake(dt)─┐
│     1426860702823350272 │
└─────────────────────────┘

dateTime64ToSnowflake

This function is deprecated and can only be used if setting allow_deprecated_snowflake_conversion_functions is enabled. The function will be removed at some point in future.


Convert a DateTime64 to the first Snowflake ID at the giving time.

Syntax 

dateTime64ToSnowflake(value)

Arguments

Returned value

  • Input value converted to the Int64 data type as the first Snowflake ID at that time.

Example

Query: 

WITH toDateTime64('2021-08-15 18:57:56.492', 3, 'Asia/Shanghai') AS dt64 SELECT dateTime64ToSnowflake(dt64);

Result: 

┌─dateTime64ToSnowflake(dt64)─┐
│         1426860704886947840 │
└─────────────────────────────┘

snowflakeIDToDateTime

Returns the timestamp component of a Snowflake ID as a value of type DateTime.

Syntax 

snowflakeIDToDateTime(value[, epoch[, time_zone]])

Arguments

  • value — Snowflake ID. UInt64.
  • epoch - Epoch of the Snowflake ID in milliseconds since 1970-01-01. Defaults to 0 (1970-01-01). For the Twitter/X epoch (2015-01-01), provide 1288834974657. Optional. UInt*.
  • time_zone — Timezone. The function parses time_string according to the timezone. Optional. String.

Returned value

  • The timestamp component of value as a DateTime value.

Example

Query: 

SELECT snowflakeIDToDateTime(7204436857747984384) AS res

Result: 

┌─────────────────res─┐
│ 2024-06-06 10:59:58 │
└─────────────────────┘

snowflakeIDToDateTime64

Returns the timestamp component of a Snowflake ID as a value of type DateTime64.

Syntax 

snowflakeIDToDateTime64(value[, epoch[, time_zone]])

Arguments

  • value — Snowflake ID. UInt64.
  • epoch - Epoch of the Snowflake ID in milliseconds since 1970-01-01. Defaults to 0 (1970-01-01). For the Twitter/X epoch (2015-01-01), provide 1288834974657. Optional. UInt*.
  • time_zone — Timezone. The function parses time_string according to the timezone. Optional. String.

Returned value

  • The timestamp component of value as a DateTime64 with scale = 3, i.e. millisecond precision.

Example

Query: 

SELECT snowflakeIDToDateTime64(7204436857747984384) AS res

Result: 

┌─────────────────res─┐
│ 2024-06-06 10:59:58 │
└─────────────────────┘

dateTimeToSnowflakeID

Converts a DateTime value to the first Snowflake ID at the giving time.

Syntax 

dateTimeToSnowflakeID(value[, epoch])

Arguments

  • value — Date with time. DateTime.
  • epoch - Epoch of the Snowflake ID in milliseconds since 1970-01-01. Defaults to 0 (1970-01-01). For the Twitter/X epoch (2015-01-01), provide 1288834974657. Optional. UInt*.

Returned value

  • Input value converted to UInt64 as the first Snowflake ID at that time.

Example

Query: 

SELECT toDateTime('2021-08-15 18:57:56', 'Asia/Shanghai') AS dt, dateTimeToSnowflakeID(dt) AS res;

Result: 

┌──────────────────dt─┬─────────────────res─┐
│ 2021-08-15 18:57:56 │ 6832626392367104000 │
└─────────────────────┴─────────────────────┘

dateTime64ToSnowflakeID

Convert a DateTime64 to the first Snowflake ID at the giving time.

Syntax 

dateTime64ToSnowflakeID(value[, epoch])

Arguments

  • value — Date with time. DateTime64.
  • epoch - Epoch of the Snowflake ID in milliseconds since 1970-01-01. Defaults to 0 (1970-01-01). For the Twitter/X epoch (2015-01-01), provide 1288834974657. Optional. UInt*.

Returned value

  • Input value converted to UInt64 as the first Snowflake ID at that time.

Example

Query: 

SELECT toDateTime('2021-08-15 18:57:56.493', 3, 'Asia/Shanghai') AS dt, dateTime64ToSnowflakeID(dt) AS res;

Result: 

┌──────────────────────dt─┬─────────────────res─┐
│ 2021-08-15 18:57:56.493 │ 6832626394434895872 │
└─────────────────────────┴─────────────────────┘

See also

Retrieved from "https://docs.foji.io/index.php?title=Relational_Data_-_Functions_-_Uuid_Functions&oldid=2327"