Redis table engine

This engine allows integrating ClickHouse with 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:

Query

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>0</db_index>
    </redis_creds>
</named_collections>

Query

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

Insert:

Query

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

Query

SELECT COUNT(*) FROM redis_table;

Response

┌─count()─┐
│       2 │
└─────────┘

Query

SELECT * FROM redis_table WHERE key='1';

Response

┌─key─┬─v1─┬─v2─┬─v3─┐
│ 1   │  1 │ 1  │  1 │
└─────┴────┴────┴────┘

Query

SELECT * FROM redis_table WHERE v1=2;

Response

┌─key─┬─v1─┬─v2─┬─v3─┐
│ 2   │  2 │ 2  │  2 │
└─────┴────┴────┴────┘

Update:

Note that the primary key cannot be updated.

Query

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

Delete:

Query

ALTER TABLE redis_table DELETE WHERE key='1';

Truncate:

Flush Redis db asynchronously. Also Truncate support SYNC mode.

Query

TRUNCATE TABLE redis_table SYNC;

Join:

Join with other tables.

Query

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.