Skip to main content

Redis

This engine allows integrating ClickHouse with Redis. For Redis takes kv model, we strongly recommend you only query it in a point way, such as where k=xx or where k in (xx, xx).

Creating a Table​

CREATE TABLE [IF NOT EXISTS] [db.]table_name
(
    name1 [type1],
    name2 [type2],
    ...
) ENGINE = Redis(host:port[, db_index[, password[, pool_size]]]) PRIMARY KEY(primary_key_name);

Engine Parameters

  • host:port — Redis server address, you can ignore port and default Redis port 6379 will be used.

  • db_index — Redis db index range from 0 to 15, default is 0.

  • password — User password, default is blank string.

  • pool_size — Redis max connection pool size, default is 16.

  • primary_key_name - any column name in the column list.

  • primary must be specified, it supports only one column in the primary key. The primary key will be serialized in binary as a Redis key.

  • columns other than the primary key will be serialized in binary as Redis value in corresponding order.

  • queries with key equals or in filtering will be optimized to multi keys lookup from Redis. If queries without filtering key full table scan will happen which is a heavy operation.

Usage Example​

Create a table in ClickHouse which allows to read data from Redis:

CREATE TABLE redis_table
(
    `key` String,
    `v1` UInt32,
    `v2` String,
    `v3` Float32
)
ENGINE = Redis('redis1:6379') PRIMARY KEY(key);

Insert:

INSERT INTO redis_table Values('1', 1, '1', 1.0), ('2', 2, '2', 2.0);

Query:

SELECT COUNT(*) FROM redis_table;
┌─count()─┐
│       2 │
└─────────┘
SELECT * FROM redis_table WHERE key='1';
┌─key─┬─v1─┬─v2─┬─v3─┐
│ 1   │  1 │ 1  │  1 │
└─────┴────┴────┴────┘
SELECT * FROM redis_table WHERE v1=2;
┌─key─┬─v1─┬─v2─┬─v3─┐
│ 2   │  2 │ 2  │  2 │
└─────┴────┴────┴────┘

Update:

Note that the primary key cannot be updated.

ALTER TABLE redis_table UPDATE v1=2 WHERE key='1';

Delete:

ALTER TABLE redis_table DELETE WHERE key='1';

Truncate:

Flush Redis db asynchronously. Also Truncate support SYNC mode.

TRUNCATE TABLE redis_table SYNC;

Join:

Join with other tables.

SELECT * FROM redis_table JOIN merge_tree_table ON merge_tree_table.key=redis_table.key;

Limitations​

Redis engine also supports scanning queries, such as where k > xx, but it has some limitations:

  1. Scanning query may produce some duplicated keys in a very rare case when it is rehashing. See details in Redis Scan.
  2. During the scanning, keys could be created and deleted, so the resulting dataset can not represent a valid point in time.