Composite Primary Key equivalent in Redis

Question

I'm new to nosql databases so forgive my sql mentality but I'm looking to store data that can be 'queried' by one of 2 keys. Here's the structure:

{user_id, business_id, last_seen_ts, first_seen_ts} 

where if this were a sql DB I'd use the user_id and business_id as a primary composite key. The sort of querying I'm looking for is a

1.'get all where business_id = x'

2.'get all where user_id = x'

Any tips? I don't think I can make a simple secondary index based on the 2 retrieval types above. I looked into commands like 'zadd' and 'zrange' but there isn't really any sorting involved here.

The use case for Redis for me is to alleviate writes and reads on my SQL database while this program computes (doing its storage in redis) what eventually will be written to the SQL DB.

Answer 1

Note: given the OP's self-proclaimed experience, this answer is intentionally simplified for educational purposes.

(one of) The first thing(s) you need to understand about Redis is that you design the data so every query will be what you're used to think about as access by primary key. It is convenient, in that sense, to imagine Redis' keyspace (the global dictionary) as something like this relational table:

CREATE TABLE redis (
  key    VARCHAR(512MB) NOT NULL,
  value  VARCHAR(512MB),
  PRIMARY KEY (key)
);

Note: in Redis, value can be more than just a String of course.

Keeping that in mind, and unlike other database models where normalizing data is the practice, you want to have your Redis ready to handle both of your queries efficiently. That means you'll be saving the data twice: once under a primary key that allows searching for businesses by id, and another time that allows querying by user id.

To answer the first query ("'get all where business_id = x'"), you want to have a key for each x that hold the relevant data (in Redis we use the colon, ':', as separator as a matter of convention) - so for x=1 you'd probably call your key business:1, for x=a1b2c3 business:a1b2c3 and so forth.

Each such business:x key could be a Redis Set, where each member represents the rest of the tuple. So, if the data is something like:

{user_id: foo, business_id: bar, last_seen_ts: 987, first_seen_ts: 123}

You'd be storing it with Redis with something like:

SADD business:bar foo

Note: you can use any serialization you want, Set members are just Strings.

With this in place, answering the first query is just a matter of SMEMBERS business:bar (or SSCANing it for larger Sets).

If you've followed through, you already know how to serve the second query. First, use a Set for each user (e.g. user:foo) to which you SADD user:foo bar. Then SMEMBERS/SSCAN and you're almost home.

The last thing you'll need is another set of keys, but this time you can use Hashes. Each such Hash will store the additional information of the tuple, namely the timestamps. We can use a "Primary Key" made up of the bussiness and the user ids (or vice versa) like so:

HMSET foo:bar first 123 last 987

After you've gotten the results from the 1st or 2nd query, you can fetch the contents of the relevant Hashes to complete the query (assuming that the queries return the timestamps as well).

Answer 2

The idiomatic way of doing this in Redis is to use a SET for each type of query you want to do. In your case you would create:

• a hash for each tuple (user_id, business_id, last_seen_ts, first_seen_ts)
• a set with a name like user:<user_id>:business:<business_id>, to store the keys of the hashes for this user and this business (you have to add the ID of the hashes with SADD)

Then to get all data for a given user and business, you have to get the SET content with SMEMBERS first, and then to GET every HASH whose ID is in the SET.