Tuples in ClickHouse

  

A Tuple in ClickHouse is a data structure similar to an array, but unlike arrays (which are homogeneous), Tuples can store values of different types. This makes them useful for grouping logically related values of various types into a single column or expression.

 

Key Characteristics

·      Tuples can hold heterogeneous data types.

·      Elements can be accessed using tupleElement() or dot notation (if given names).

·      You can create tuples using the tuple() function.

 

Syntax

tuple(T1, T2, ..., Tn)

   

Each Ti can be of a different data type.

 

Example 1: Creation of tuple

 

SELECT tuple(1, 'a') AS x, toTypeName(x);

krishna :) SELECT tuple(1, 'a') AS x, toTypeName(x);

SELECT
    (1, 'a') AS x,
    toTypeName(x)

Query id: e3d19f03-5345-48ef-be73-e2dce002617f

Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 25.5.1.

   ┌─x───────┬─toTypeName(x)────────┐
1. │ (1,'a') │ Tuple(UInt8, String) │
   └─────────┴──────────────────────┘

1 row in set. Elapsed: 0.004 sec.

You can even create a tuple with single element.

 

SELECT tuple(42) AS single_tuple, toTypeName(single_tuple);

krishna :) SELECT tuple(42) AS single_tuple, toTypeName(single_tuple);

SELECT
    tuple(42) AS single_tuple,
    toTypeName(single_tuple)

Query id: 85324d4d-836c-406b-a078-1a797987e702

   ┌─single_tuple─┬─toTypeName(single_tuple)─┐
1. │ (42)         │ Tuple(UInt8)             │
   └──────────────┴──────────────────────────┘

1 row in set. Elapsed: 0.002 sec.

   

How to access tuple elements?

We can access tuple elements in two ways.

·      Using tupleElement method.

·      Using dot notation

 

Using tupleElement method

 

SELECT tupleElement(tuple(10, 'hello', now()), 2) AS second_element;

krishna :) SELECT tupleElement(tuple(10, 'hello', now()), 2) AS second_element;

SELECT (10, 'hello', now()).2 AS second_element

Query id: 0507a19c-990d-4912-a1e3-6dd8f3b930ab

   ┌─second_element─┐
1. │ hello          │
   └────────────────┘

1 row in set. Elapsed: 0.002 sec.

   

Using dot notation

 

WITH tuple(10, 'hello', now()) AS t
SELECT
    t.1 AS first,
    t.2 AS second,
    t.3 AS third;

krishna :) WITH tuple(10, 'hello', now()) AS t
SELECT
    t.1 AS first,
    t.2 AS second,
    t.3 AS third;

WITH (10, 'hello', now()) AS t
SELECT
    t.1 AS first,
    t.2 AS second,
    t.3 AS third

Query id: 2b7bde65-c744-4c8a-bc30-b7b626b974e0

   ┌─first─┬─second─┬───────────────third─┐
1. │    10 │ hello  │ 2025-05-17 18:03:51 │
   └───────┴────────┴─────────────────────┘

1 row in set. Elapsed: 0.001 sec.

   

Realworld example

 

CREATE DATABASE IF NOT EXISTS demo_db;

CREATE TABLE demo_db.user_events (
    user_info Tuple(String, UInt8),  -- name, age
    event_date Date
) ENGINE = MergeTree()
ORDER BY event_date;

INSERT INTO demo_db.user_events VALUES
(('Ram', 30), '2024-08-01'),
(('Krishna', 25), '2024-08-02');

SELECT
    user_info.1 AS name,
    user_info.2 AS age,
    event_date
FROM demo_db.user_events;

   

In this example, I demonstrated how to use Tuples in ClickHouse to store heterogeneous data types within a single column. I created a user_events table where the user_info column is a tuple containing a user's name (String) and age (UInt8). After inserting sample data, we used dot notation to extract individual elements from the tuple in the SELECT query. This approach allows for compact and structured data modeling, especially when related values are logically grouped together.

 

krishna :) SELECT
    user_info.1 AS name,
    user_info.2 AS age,
    event_date
FROM demo_db.user_events;

SELECT
    user_info.1 AS name,
    user_info.2 AS age,
    event_date
FROM demo_db.user_events

Query id: 88444db0-8874-4a53-a72f-9fa08d388569

   ┌─name────┬─age─┬─event_date─┐
1. │ Ram     │  30 │ 2024-08-01 │
2. │ Krishna │  25 │ 2024-08-02 │
   └─────────┴─────┴────────────┘

2 rows in set. Elapsed: 0.002 sec. 

   

How to detect data type of individual elements in tuple?

When you create tuples on the fly in ClickHouse (e.g., using the tuple() function in a SELECT statement), the system automatically infers the most appropriate data types for each element in the tuple. ClickHouse does this by determining the smallest compatible data type for each value:

 

·      Numeric values are assigned the smallest integer type that can represent them (e.g., 1 becomes UInt8).

·      String values are inferred as String.

·      If a NULL value is present, ClickHouse infers the type as Nullable(Nothing), indicating the type is unknown but nullable.

 

 

Example

 

SELECT tuple(1, 'Ram', 23, NULL) AS types, toTypeName(types)

krishna :) SELECT tuple(1, 'Ram', 23, NULL) AS types, toTypeName(types);

SELECT
    (1, 'Ram', 23, NULL) AS types,
    toTypeName(types)

Query id: 959db1d7-92b4-4ec5-bf6e-cc00c0f500e6

   ┌─types─────────────┬─toTypeName(types)──────────────────────────────┐
1. │ (1,'Ram',23,NULL) │ Tuple(UInt8, String, UInt8, Nullable(Nothing)) │
   └───────────────────┴────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.001 sec.

   

Here:

·      1 is inferred as UInt8 (the smallest unsigned integer type).

·      'Ram' is inferred as String.

·      23 is inferred as UInt8

·      NULL has no explicit type, so ClickHouse assigns it Nullable(Nothing).

  

Previous                                                    Next                                                    Home