ClickHouse, a columnar database known for high performance, provides support for various data types optimized for analytical workloads. One of these types is the Map(K, V), a flexible key-value storage format. This blog post explores how Map works in ClickHouse, its internal representation, how to query it, and important performance considerations.
What is the Map(K, V) Data Type?
In ClickHouse, the Map(K, V) data type is designed to store key-value pairs, where:
· K is the type of the key.
· V is the type of the value.
For example, you can store {'product': 3, 'views': 200} in a Map(String, UInt64) column.
Unlike traditional databases where keys in a map must be unique, ClickHouse allows duplicate keys within a map. This is because internally, a Map is stored as an Array(Tuple(K, V)).
This means:
· You can have duplicate keys in a map.
· Operations like lookup or update don’t benefit from constant-time complexity, lookups are linear in complexity due to the underlying array structure.
How to retrieve the value for a given key?
You can retrieve the value for a given key using bracket notation.
myMap['key']
Real-World Scenario: User Preferences
Let's say you’re building a system to store user preferences such as:
· Favorite color
· Preferred currency
· Delivery option
These preferences are dynamic and can vary from user to user. A Map(String, String) is a perfect fit to store them.
Step 1: Creating a database demo_db.
CREATE DATABASE IF NOT EXISTS demo_db;
Step 2: Creating a Table
CREATE TABLE demo_db.user_preferences ( user_id UInt64, preferences Map(String, String) ) ENGINE = MergeTree() ORDER BY user_id;
This creates a table where each user can have a flexible set of key-value preferences like:
{
"color": "blue",
"currency": "INR",
"delivery": "express"
}
Step 3: Let's insert some data into user_preferences table
INSERT INTO demo_db.user_preferences VALUES (1, {'color':'blue', 'currency':'USD', 'delivery':'express'}), (2, {'color':'red', 'currency':'EUR'}), (3, {'delivery':'standard', 'currency':'INR'});
krishna :) SELECT * FROM demo_db.user_preferences; SELECT * FROM demo_db.user_preferences Query id: cac03051-29ca-4875-80b3-b60bf91fa02e ┌─user_id─┬─preferences────────────────────────────────────────────┐ 1. │ 1 │ {'color':'blue','currency':'USD','delivery':'express'} │ 2. │ 2 │ {'color':'red','currency':'EUR'} │ 3. │ 3 │ {'delivery':'standard','currency':'INR'} │ └─────────┴────────────────────────────────────────────────────────┘ 3 rows in set. Elapsed: 0.009 sec.
Examples: Now let’s extract specific information from the map.
Example 1: Get all users' preferred currency
SELECT user_id, preferences['currency'] AS currency FROM demo_db.user_preferences;
krishna :) SELECT user_id, preferences['currency'] AS currency FROM demo_db.user_preferences; SELECT user_id, preferences['currency'] AS currency FROM demo_db.user_preferences Query id: d7201aa7-7cba-4581-a64d-8ea9d10a9091 ┌─user_id─┬─currency─┐ 1. │ 1 │ USD │ 2. │ 2 │ EUR │ 3. │ 3 │ INR │ └─────────┴──────────┘ 3 rows in set. Elapsed: 0.002 sec.
Example 2: Check if a user has set a preferred color
SELECT user_id, mapContains(preferences, 'color') AS has_color FROM demo_db.user_preferences;
krishna :) SELECT user_id, mapContains(preferences, 'color') AS has_color FROM demo_db.user_preferences; SELECT user_id, mapContains(preferences, 'color') AS has_color FROM demo_db.user_preferences Query id: 92019731-c433-495c-9b0a-d9368b8bba54 ┌─user_id─┬─has_color─┐ 1. │ 1 │ 1 │ 2. │ 2 │ 1 │ 3. │ 3 │ 0 │ └─────────┴───────────┘ 3 rows in set. Elapsed: 0.005 sec.
Example 3: Get a default value when the key is missing
If you directly access a key that doesn’t exist, you get a default value. It returns empty string as default.
SELECT user_id, preferences['color'] AS color FROM demo_db.user_preferences;
krishna :) SELECT user_id, preferences['color'] AS color FROM demo_db.user_preferences; SELECT user_id, preferences['color'] AS color FROM demo_db.user_preferences Query id: 56ed83dd-09ca-43a6-9940-e31e542896a5 ┌─user_id─┬─color─┐ 1. │ 1 │ blue │ 2. │ 2 │ red │ 3. │ 3 │ │ └─────────┴───────┘ 3 rows in set. Elapsed: 0.006 sec.
Converting a Tuple to a Map in ClickHouse
ClickHouse allows you to convert a pair of arrays wrapped in a tuple into a Map(K, V) using the CAST function. This is useful when you have separate arrays for keys and values and want to work with them as a single map.
Syntax
CAST((keys_array, values_array), 'Map(K, V)')
Here
keys_array: an array of keys
values_array: an array of corresponding values
K: data type of keys
V: data type of values
Example 1: Integer → String Map
SELECT CAST(([1, 2, 3], ['Ready', 'Steady', 'Go']), 'Map(UInt8, String)') AS actions;
krishna :) SELECT CAST(([1, 2, 3], ['Ready', 'Steady', 'Go']), 'Map(UInt8, String)') AS actions; SELECT CAST(([1, 2, 3], ['Ready', 'Steady', 'Go']), 'Map(UInt8, String)') AS actions Query id: 77b7cd2d-1ab6-4913-8151-cb19008a02c2 ┌─actions───────────────────────┐ 1. │ {1:'Ready',2:'Steady',3:'Go'} │ └───────────────────────────────┘ 1 row in set. Elapsed: 0.003 sec.
Example 2: String → Float Map
You can map product names to their prices.
SELECT CAST((['apple', 'banana'], [1.25, 0.75]), 'Map(String, Float32)') AS prices;
krishna :) SELECT CAST((['apple', 'banana'], [1.25, 0.75]), 'Map(String, Float32)') AS prices; SELECT CAST((['apple', 'banana'], [1.25, 0.75]), 'Map(String, Float32)') AS prices Query id: cff34c81-7768-42a1-8c16-055cdc924298 ┌─prices───────────────────────┐ 1. │ {'apple':1.25,'banana':0.75} │ └──────────────────────────────┘ 1 row in set. Elapsed: 0.004 sec.
Example 3: Date → Integer Map
Suppose you want to associate dates with user counts.
SELECT CAST((['2024-01-01', '2024-01-02'], [100, 150]), 'Map(Date, UInt32)') AS daily_users;
krishna :) SELECT CAST((['2024-01-01', '2024-01-02'], [100, 150]), 'Map(Date, UInt32)') AS daily_users; SELECT CAST((['2024-01-01', '2024-01-02'], [100, 150]), 'Map(Date, UInt32)') AS daily_users Query id: e6a104dd-e080-4141-a5f9-a4d4f430d8aa ┌─daily_users─────────────────────────┐ 1. │ {'2024-01-01':100,'2024-01-02':150} │ └─────────────────────────────────────┘ 1 row in set. Elapsed: 0.003 sec.
In summary, Map(K, V) data type in ClickHouse is a powerful way to store dynamic key-value pairs within a column. While it's flexible and easy to use, remember:
· It allows duplicate keys.
· Key lookup is not optimized for performance (linear scan).
· Use mapContains to safely check for key presence before accessing values.
This makes Map suitable for storing small sets of key-value data in a single column, like metadata, tags, or dynamic attributes per row.
Previous Next Home
No comments:
Post a Comment