Arrays are a powerful data type in ClickHouse that allow you to store multiple values in a single column. Whether you're working with a list of tags, numeric series, or JSON-like structures, arrays can help you organize data more efficiently. In this guide, I’ll walk through the basics of arrays in ClickHouse, including how to define them, what they can contain, and how to use them effectively.
1. What is an Array in ClickHouse?
An array is an ordered collection of elements of the same type. It allows you to store multiple values in a single field.
2. How to Define Arrays
2.1 Using square brackets:
SELECT [1, 2, 3, 4] AS numbers;
krishna :) SELECT [1, 2, 3, 4] AS numbers; SELECT [1, 2, 3, 4] AS numbers Query id: ce01a46b-ae3e-444b-8ad9-bf8e0be17126 Connecting to localhost:9000 as user default. Connected to ClickHouse server version 25.5.1. ┌─numbers───┐ 1. │ [1,2,3,4] │ └───────────┘ 1 row in set. Elapsed: 0.002 sec.
2.2 Using the array() function
SELECT array(1, 2, 3, 4) AS numbers;
krishna :) SELECT array(1, 2, 3, 4) AS numbers; SELECT [1, 2, 3, 4] AS numbers Query id: d5fd5ff5-57ee-4c48-9e75-1820d3a105d4 ┌─numbers───┐ 1. │ [1,2,3,4] │ └───────────┘ 1 row in set. Elapsed: 0.001 sec.
3. Multi-dimensional Arrays
ClickHouse also supports arrays of arrays.
SELECT [[1, 2], [3, 4]] AS matrix;
krishna :) SELECT [[1, 2], [3, 4]] AS matrix; SELECT [[1, 2], [3, 4]] AS matrix Query id: ec39ab09-89c2-44c3-9ca8-05d71d5f966d ┌─matrix────────┐ 1. │ [[1,2],[3,4]] │ └───────────────┘ 1 row in set. Elapsed: 0.002 sec.
Multi Dimensional arrays are useful for representing tabular or grid-like data.
4. Type Consistency
All elements in an array must be of the compatible type. Mixing types will result in an error.
For example, following statement results in an error.
SELECT ['apple', 1] AS invalid_array;
krishna :) SELECT ['apple', 1] AS invalid_array; SELECT ['apple', 1] AS invalid_array Query id: 761150cf-622b-4557-a36c-17e97575e736 Elapsed: 0.028 sec. Received exception from server (version 25.5.1): Code: 386. DB::Exception: Received from localhost:9000. DB::Exception: There is no supertype for types String, UInt8 because some of them are String/FixedString/Enum and some of them are not. (NO_COMMON_TYPE)
5. NULL Values in Arrays
ClickHouse supports NULL values in the array.
SELECT [1, NULL, 3] AS nums;
krishna :) SELECT [1, NULL, 3] AS nums; SELECT [1, NULL, 3] AS nums Query id: 09d7aaaa-ec5a-4dbb-a5d3-7ea417196104 ┌─nums───────┐ 1. │ [1,NULL,3] │ └────────────┘ 1 row in set. Elapsed: 0.001 sec.
6. Example: Tracking User Interests in a Blog Platform
CREATE DATABASE IF NOT EXISTS demo_db; CREATE TABLE demo_db.user_profiles ( user_id UInt32, user_name String, interests Array(String) ) ENGINE = MergeTree() ORDER BY user_id; INSERT INTO demo_db.user_profiles VALUES (1, 'Ram', ['tech', 'ai', 'space']), (2, 'Krishna', ['cooking', 'travel']), (3, 'Chamu', ['tech', 'finance']), (4, 'Hari', ['art', 'travel', 'fashion']), (5, 'Ravi', ['ai', 'robotics', 'space']); SELECT * FROM demo_db.user_profiles;
krishna :) SELECT * FROM demo_db.user_profiles; SELECT * FROM demo_db.user_profiles Query id: cb4c0817-86f6-43ac-b11c-b71b642cdb0b ┌─user_id─┬─user_name─┬─interests──────────────────┐ 1. │ 1 │ Ram │ ['tech','ai','space'] │ 2. │ 2 │ Krishna │ ['cooking','travel'] │ 3. │ 3 │ Chamu │ ['tech','finance'] │ 4. │ 4 │ Hari │ ['art','travel','fashion'] │ 5. │ 5 │ Ravi │ ['ai','robotics','space'] │ └─────────┴───────────┴────────────────────────────┘ 5 rows in set. Elapsed: 0.004 sec.
6.1 Find users interested in tech.
SELECT user_name FROM demo_db.user_profiles WHERE has(interests, 'tech');
krishna :) SELECT user_name FROM demo_db.user_profiles WHERE has(interests, 'tech'); SELECT user_name FROM demo_db.user_profiles WHERE has(interests, 'tech') Query id: 09582d25-dd3b-4a3f-9fef-c92404f52989 ┌─user_name─┐ 1. │ Ram │ 2. │ Chamu │ └───────────┘ 2 rows in set. Elapsed: 0.008 sec.
6.2 Flatten interests across all users (arrayJoin)
SELECT user_name, arrayJoin(interests) AS interest FROM demo_db.user_profiles;
krishna :) SELECT user_name, arrayJoin(interests) AS interest FROM demo_db.user_profiles; SELECT user_name, arrayJoin(interests) AS interest FROM demo_db.user_profiles Query id: 54f41a7d-6474-42ea-b87f-26b23cdbdc97 ┌─user_name─┬─interest─┐ 1. │ Ram │ tech │ 2. │ Ram │ ai │ 3. │ Ram │ space │ 4. │ Krishna │ cooking │ 5. │ Krishna │ travel │ 6. │ Chamu │ tech │ 7. │ Chamu │ finance │ 8. │ Hari │ art │ 9. │ Hari │ travel │ 10. │ Hari │ fashion │ 11. │ Ravi │ ai │ 12. │ Ravi │ robotics │ 13. │ Ravi │ space │ └───────────┴──────────┘ 13 rows in set. Elapsed: 0.004 sec.
6.3 Filter interests that contain the letter 'a'
SELECT user_name, arrayFilter(x -> position(x, 'a') > 0, interests) AS filtered_interests FROM demo_db.user_profiles;
krishna :) SELECT user_name, arrayFilter(x -> position(x, 'a') > 0, interests) AS filtered_interests FROM demo_db.user_profiles; SELECT user_name, arrayFilter(x -> (position(x, 'a') > 0), interests) AS filtered_interests FROM demo_db.user_profiles Query id: 30a6a257-b886-46ca-b19b-bab0cbc5d91e ┌─user_name─┬─filtered_interests─────────┐ 1. │ Ram │ ['ai','space'] │ 2. │ Krishna │ ['travel'] │ 3. │ Chamu │ ['finance'] │ 4. │ Hari │ ['art','travel','fashion'] │ 5. │ Ravi │ ['ai','space'] │ └───────────┴────────────────────────────┘ 5 rows in set. Elapsed: 0.005 sec.
6.4 Count how many interests each user has.
SELECT user_name, length(interests) AS interest_count FROM demo_db.user_profiles;
krishna :) SELECT user_name, length(interests) AS interest_count FROM demo_db.user_profiles; SELECT user_name, length(interests) AS interest_count FROM demo_db.user_profiles Query id: 691a5e9c-cb64-47e9-a41d-0872caed076a ┌─user_name─┬─interest_count─┐ 1. │ Ram │ 3 │ 2. │ Krishna │ 2 │ 3. │ Chamu │ 2 │ 4. │ Hari │ 3 │ 5. │ Ravi │ 3 │ └───────────┴────────────────┘ 5 rows in set. Elapsed: 0.004 sec.
Previous Next Home
No comments:
Post a Comment