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]]] | named_collection[, option=value [,..]] })
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.
Serialization

PRIMARY KEY supports only one column. 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.

Arguments also can be passed using named collections. In this case host and port should be specified separately. This approach is recommended for production environment. At this moment, all parameters passed using named collections to redis are required.

Filtering

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 using Redis engine with plain arguments:

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

Or using named collections:

<named_collections>
    <redis_creds>
        <host>localhost</host>
        <port>6379</port>
        <password>****</password>
        <pool_size>16</pool_size>
        <db_index>s0</db_index>
    </redis_creds>
</named_collections>
CREATE TABLE redis_table
(
    `key` String,
    `v1` UInt32,
    `v2` String,
    `v3` Float32
)
ENGINE = Redis(redis_creds) 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.