[CALCITE-3510] Redis adapter

• Type: New Feature
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.22.0
• Labels: pull-request-available
• Assignee: Forward Xu
• Reporter: Forward Xu
• Created: 17/Nov/19 17:22
• Updated: 06/Mar/20 04:33
• Resolved: 07/Jan/20 07:09

Description

The Redis adapter allows querying of live data stored in Redis.Each Redis key/value pair is presented as a single row in Presto. Rows can be broken down into cells by using table definition files.
Redis `string` ,`hash`, `sets`, `zsets`, `list` value types are supported;

CSV format data

Set hello_world_1 1, james, 10
Set hello_world_2 2,bond,20
Set hello_world_3 3,lily,30
Set hello_world_4 4,lucy,20

JSON format data

Set hello_foo_1 '{"id":1,"name":"james","age":110}'
Set hello_foo_2 '{"id": 2, "name": "bond", "age": 210}'
Set hello_foo_3 '{"id": 3, "name": "lily", "age": 310}'
Set hello_foo_4 '{"id": 3, "name": "lucy", "age": 210}'

RAW format data

Set hello_raw_1 1, james, 10
Set hello_raw_2 2,bond,20
Set hello_raw_3 3,lily,30
Set hello_raw_4 4, lucy, 20

We inserted data in three formats, one in CSV format, one in JSON format, and one in RAW format. This is the three formats we currently support, which will be demonstrated separately.

Then you can define the corresponding mapping table in the JSON file:

{
  "version": "1.0",
  "defaultSchema": "foodmart",
  "schemas": [
    {
      "type": "custom",
      "name": "foodmart",
      "factory": "org.apache.calcite.adapter.redis.RedisSchemaFactory",
      "operand": {
      ```
        "host": "localhost",
        "port": 6379,
        "database": 0,
        "password": ""
        ```
      },
      "tables": [
        {
          "name": "raw_01",
          "factory": "org.apache.calcite.adapter.redis.RedisTableFactory",
          "operand": {
          ```
            "dataFormat": "csv",
            "keyDelimiter": ":",
            "fields": [
              {
                "name": "id",
                "type": "varchar",
                "mapping": "id"
              }
              ```
            ]
          }
        }
      ]
    }
  ]
}

Here are a few details about the fields:

keyDelimiter is used to split the value, the default is a colon, and the split value is used to map the field column. Only works for the CSV format.

Format is used to specify the format of the data in Redis. Currently, it supports: CSV, JSON and RAW. The raw format keeps the original redis key and value intact and only one field key is used for the query. The details are not described below.

The function of COLUMN_MAPPING is to map the columns of Redis to the underlying data. Since there is no concept of column under the Redis, the specific mapping method varies according to the format. For example, here CSV, we know that the CSV data will be formed after being parsed. A string array, the corresponding column_mapping is mapped to the index (subscript) of the underlying array. For example, here map id to subscript 2, map name to subscript 1 and so on.

You can query the data in the Redis database:

Mysql> select * from dla_person;

name	id	age
bond	20	2
lily	30	3
lucy	20	4
james	10	1

4 rows in set (0.18 sec)
Students who are familiar with SQL must feel very cool, you can go to the familiar SQL syntax to operate the Redis database.

JSON
The above demonstrates the data in CSV format. Let's try the data in JSON format. Let's create a new table:

{
  "version": "1.0",
  "defaultSchema": "foodmart",
  "schemas": [
    {
      "type": "custom",
      "name": "foodmart",
      "factory": "org.apache.calcite.adapter.redis.RedisSchemaFactory",
      "operand": {
      ```
        "host": "localhost",
        "port": 6379,
        "database": 0,
        "password": ""
        ```
      },
      "tables": [
        {
          "name": "raw_01",
          "factory": "org.apache.calcite.adapter.redis.RedisTableFactory",
          "operand": {
          ```
            "dataFormat": "json",
            "fields": [
              {
                "name": "id",
                "type": "varchar",
                "mapping": "id"
              }
              ```
            ]
          }
        }
      ]
    }
  ]
}

Note that we have specified COLUMN_MAPPING here, because the JSON data has a field name, so the column name of the Redis layer is mapped to the name of the field in the JSON data. Here, the Redis id column is deliberately mapped to Redis for demonstration purposes. The age, let's look at the results:

Mysql> select * from dla_person_json;

name	id	age
lucy	210	3
james	110	1
bond	210	2
lily	310	3

4 rows in set (0.12 sec)
As we wish, the id column shows the value of the corresponding age field in Redis.

Issue Links

• links to: GitHub Pull Request #1591