Tuples are collections of items with heterogeneous data types. ClickHouse tuples are used with IN operators and in Lambda functions. The tuples can not be empty and need at least one element in them. Tuples are also possible as a query output.
Creating a Tuple
Using the tuple() function, we can create tuples in ClickHouse.
chistadata :) SELECT tuple (1,2,'apple') as x; SELECT (1, 2, 'apple') AS x Query id: 1f14283c-a19f-44a0-b431-c63f7511725d ┌─x─────────────┐ │ (1,2,'apple') │ └───────────────┘ 1 row in set. Elapsed: 0.002 sec.
Alternatively, we can use parentheses to create tuples.
SELECT (1, 2, 'orange') AS x Query id: 6ea88928-b468-4fa0-b04e-7ac5f4072e03 ┌─x──────────────┐ │ (1,2,'orange') │ └────────────────┘ 1 row in set. Elapsed: 0.002 sec.
Creating a table with a Tuple column
chistadata :) CREATE TABLE tuple_example
(col1 Tuple(String, Int32),
col2 Int32)
Engine = Log;
CREATE TABLE tuple_example
(
`col1` Tuple(String, Int32),
`col2` Int32
)
ENGINE = Log
Query id: 23a2d229-4c98-4dec-bfb3-5620e75b6962
Ok.
0 rows in set. Elapsed: 0.003 sec.
Inserting the data
chistadata :) INSERT INTO tuple_example VALUES (('a', 32), 32);
INSERT INTO tuple_example FORMAT Values
Query id: a05fecd3-2c4d-4a05-83bc-0a3310ecf763
Ok.
1 row in set. Elapsed: 0.005 sec.
chistadata :) SELECT * FROM tuple_example;
SELECT *
FROM tuple_example
Query id: cd8120b3-cd09-4de0-bbc5-54a2e211aafb
┌─col1─────┬─col2─┐
│ ('a',32) │ 32 │
└──────────┴──────┘
1 row in set. Elapsed: 0.002 sec.
Accessing the tuple element
We can access the elements using the dot operator.
chistadata :) SELECT col1.1 FROM tuple_example; SELECT col1.1 FROM tuple_example Query id: d1a65c8f-b9a7-4593-b769-59f7f6f5cc47 ┌─tupleElement(col1, 1)─┐ │ a │ └───────────────────────┘ 1 row in set. Elapsed: 0.003 sec.
References:
https://clickhouse.com/docs/en/sql-reference/data-types/tuple/