How to get SQL-like Experience with Redis?

Roshan Kumar by Roshan Kumar

Recently, I was chatting with one of my friends who manages data warehousing solutions at a retail company. He posed an interesting problem: “We have a pain point with our data warehousing solutions. We have use cases where we need to record data and perform analytical operations in real time. However, sometimes it takes minutes to get the results. Can Redis help here? Keep in mind, we cannot rip and replace our SQL-based solution all at once. We can only take a baby step at a time.”

If you are in the same situation as my friends’, we have good news for you. You can introduce Redis to your architecture without disrupting your current SQL-based solution. You have a few options. The easiest ones are:

    1. Remodel your tables as Redis data structures
      It’s quite straightforward to map your table to Redis data structures. Hash, Sorted Set and Set are the most useful data structures in this effort. You could store every row as a Hash with a key that’s based on the table’s primary key, and have the key stored in a Set or a Sorted Set.Figure 1 shows an example of how you could map a table into Redis data structures. In this example, we have a table called Products. We map every row to a Hash data structure. The row with primary id, 10001 will go in as a Hash with the key, product:10001. We have two Sorted Sets in this example. One to iterate through the data set by primary key, and two to query based on price.

      Figure 1. Mapping a table to Redis data structures

      With this option, you need to make changes to your code to use Redis commands instead of SQL queries. Below are some example of SQL and Redis equivalent commands:
      A. Insert data
      insert into Products (id, name, description, price)
      values (10200, “ZXYW”,“Description for ZXYW”, 300);

      HMSET product:10200 name ZXYW desc “Description for ZXYW” price 300
      ZADD product_list 10200 product:10200
      ZADD product_price 300 product:10200

      B. Query by product id
      select * from Products where id = 10200

      HGETALL product:10200

      C. Query by price
      select * from Product where price < 300

      ZRANGEBYSCORE product_price 0 300

      This returns the keys: product:10001, product:10002, product:10003. Now run HGETALL for each key.

      HGETALL product:10001
      HGETALL product:10002
      HGETALL product:10003


  1. Use DataFrames to automatically map your tables to Redis data structures
    In case, you want to maintain SQL interface for your solutions, but just change the underlying data store to Redis to make it faster, you can do it using Apache Spark and Spark-Redis library. Spark-Redis library allows you to use the DataFrame APIs to store and access Redis data. In other words, you can insert, update and query data using SQL commands, but the data is internally mapped to Redis data structures.

    Figure 2. The stack for Spark SQL and Redis

    First, you need to download spark-redis and build the library to get the jar file. For example, with spark-redis 2.3.1, you get spark-redis-2.3.1-SNAPSHOT-jar-with- dependencies.jar. As a second step, make sure you have your Redis instance running. In our example, we will run Redis on localhost and the default port 6379.

    You can run your queries on Apache Spark engine. Here’s a sample of how you do it:

    $ spark-shell --jars spark-redis-2.3.1-SNAPSHOT-jar-with-dependencies.jar

    scala> import org.apache.spark.sql.SparkSession

    scala> val spark = SparkSession

    scala> import spark.sql

    scala> import spark.implicits._

    scala> sql(“create table if not exists products(id string, name string, description string, price int) using org.apache.spark.sql.redis options (table ‘product’)”)

    scala> sql(“insert into products values (‘10200′,’ZXYW’,’Description of ZXYW’, 300)”)

    scala> val results = sql(“select * from products”)

    | id|name| description|price|
    |10200|ZXYW|Description of ZXYW| 300|

    Now you can also use your Redis client to access this data as Redis data structures as in:> keys product*
    1) "product:2e3f8611dbe94a588706a2aaea547caa"

    The better approach is to use the scan command as it allows you to paginate as you navigate through your data.> scan 0 match product*
    1) "3"
    2) 1) "product:2e3f8611dbe94a588706a2aaea547caa"> hgetall product:2e3f8611dbe94a588706a2aaea547caa
    1) "name"
    2) "ZXYW"
    3) "price"
    4) "300"
    5) "description"
    6) "Description of ZXYW"
    7) "id"
    8) "10200"

These are just two simple solutions for my friend’s specific use case. Redis powers many more real-time and instant-experience use cases. If you have questions about how to use Redis to solve your real-time problems, do write to or send a tweet to @redislabs.

You can also get started with Redis on your own here.