Redis Data Source (REDIS) v25.1+ PREVIEW

The Redis data source is a unique type of database adapter that enables interaction with specific Redis data structures.

Since Redis is not a traditional database, Kubling adapts its behavior at the adapter layer, making it compatible with the Query Engine. However, a key distinction from other database data sources is that Redis does not support schema imports. Unlike conventional databases, Redis lacks built-in schemas, requiring you to define one in Kubling using DDL.

Additionally, not all Redis data structures map easily to Kubling's relational model. As a result, only the following Redis data structures are supported:

• String – Standard Redis key-value pairs.
• Sorted Set – Ordered collections of unique elements with associated scores.
• Hash – Field-value mappings within a single key.

From the data source configuration perspective, Redis data source is like any other. All the real configuration happens in the Schema definition, via Kubling directives.

Let's start by configuring a simple Redis data source that will contain 3 tables, one of each of the supported types (defined below in each section).

dataSources:
  - name: "redis"
    dataSourceType: "REDIS"
    configObject:
      host: 127.0.0.1
    schema:
      type: "PHYSICAL"
      cacheDefaultStrategy: "NO_CACHE"
      ddl: |
        ...

string data structure

CREATE FOREIGN TABLE KV_STRING
(
    "key" string,
    "value" string,
    PRIMARY KEY(key)
)
OPTIONS(updatable 'true', redis_data_structure 'string');

The 'redis_data_structure' directive tells Kubling the backing Redis data source. On the other hand, field names have a special meaning, since "key" will map to a Redis key, and "value" will map to a Redis value.

However, you could also use different field names, in which case you need to explicitly tell Kubling which field corresponds to the key and value.

CREATE FOREIGN TABLE KV_STRING
(
    "key_" string OPTIONS (redis_key 'true'),
    "value_" string OPTIONS (redis_value 'true'),
    PRIMARY KEY(key_)
)
OPTIONS(updatable 'true', redis_data_structure 'string');

sorted_set data structure

CREATE FOREIGN TABLE KV_SORTED_SET
(
    "key" string,
    "value" string,
    score double,
    PRIMARY KEY(key)
)
OPTIONS(updatable 'true', redis_data_structure 'sorted_set');

This is similar to the string structure but adds a score. If the field name score is not present, Kubling will look for the directive, as follows:

CREATE FOREIGN TABLE KV_SORTED_SET
(
    ...
    my_score double OPTIONS (redis_score 'true'),
    ...
)

hash data structure

This is the more "natural" data structure, as it allows Kubling to work with fields instead of a single value, behaving similarly to a schema.

CREATE FOREIGN TABLE HASH_TABLE
(
    "key" string,
    name string,
    email string,
    age integer,
    additional json,
    PRIMARY KEY(key)
)
OPTIONS(updatable 'true', redis_data_structure 'hash');

As with the other data structures, "key" is a mandatory field, but the rest of the fields behave like any other regular Kubling data source.

Expiration

It is also possible to define an expiration in the string and sorted_set data structures, though it is not mandatory. However, when present, Kubling will apply it to the corresponding Redis entry.

Although Redis supports setting expiration using different units and expressions, Kubling supports only one: milliseconds. This is implemented via the psetex operation for the string data structure and pexpire for sorted_set.

Setting expiration in milliseconds using pure SQL might be confusing. That’s why we suggest following the formula shown below:

SELECT TO_MILLIS(TIMESTAMPADD(SQL_TSI_HOUR, 1, NOW())) - TO_MILLIS(NOW())

This returns 1 hour expressed in milliseconds.

Breakdown:

• NOW() returns the current system's datetime.
• TIMESTAMPADD(SQL_TSI_HOUR, 1, NOW()) adds 1 hour to the current timestamp.
• TO_MILLIS converts a timestamp into milliseconds.
• Subtracting TO_MILLIS(NOW()) gives the exact number of milliseconds in 1 hour.

You can modify the SQL_TSI_HOUR, 1 part to adjust the duration dynamically.

Example:

UPDATE redis.KV_SORTED_SET_WITH_EXP 
SET "exp" = TO_MILLIS(TIMESTAMPADD(SQL_TSI_HOUR, 5, NOW())) - TO_MILLIS(NOW()) 
WHERE "key" = 'MY_KEY';

Querying and Filtering

When querying Redis data structures, Kubling enforces filtering by key to prevent potentially blocking SCAN operations over thousands (or even millions) of keys that a production Redis instance may hold.

Supported Filtering Expressions:

• Equality (=) – For exact key matches.
• Pattern Matching (LIKE) – To match keys using wildcard patterns.

SELECT * FROM redis.KV_SORTED_SET_WITH_EXP WHERE "key" LIKE 'M%';
SELECT * FROM redis.KV_SORTED_SET_WITH_EXP WHERE "key" = 'MY_KEY';

There are no limitations on filtering other fields. You can apply any necessary filter conditions as needed.

Specific TABLE Directives

Specific FIELD Directives

Configuration example

dataSources:
  - name: "redis"
    dataSourceType: "REDIS"
    configObject:
      host: 127.0.0.1
    schema:
      type: "PHYSICAL"
      cacheDefaultStrategy: "NO_CACHE"
      ddl: |
        CREATE FOREIGN TABLE KV_STRING
        (
            "key_" string OPTIONS (redis_key 'true'),
            "value_" string OPTIONS (redis_value 'true'),
            PRIMARY KEY(key_)
        )
        OPTIONS(updatable 'true', redis_data_structure 'string');
 
        CREATE FOREIGN TABLE KV_SORTED_SET
        (
            "key" string,
            "value" string,
            score double,
            PRIMARY KEY(key)
        )
        OPTIONS(updatable 'true', redis_data_structure 'sorted_set');
 
        CREATE FOREIGN TABLE HASH_TABLE
        (
            "key" string,
            name string,
            email string,
            age integer,
            additional json,
            PRIMARY KEY(key)
        )
        OPTIONS(updatable 'true', redis_data_structure 'hash');