Design Redis database table like SQL?

Question

Suppose my database table structure is like this

id name college address
1  xxx   nnn     xn
2  yyy   nnm     yn
3  zzz   nnz     zn

If i want to get the student details based on the name in sql like this select * from student where name = 'xxx' so how its is possible in redis database

Prism

Answer 1

Redis, like other NoSQL datastores, has different requirements based on what you are going to be doing.

Redis has several data structures that could be useful depending on your need. For example, given your desire for a select * from student where name = 'xxx' you could use a Redis hash.

redis 127.0.0.1:6379> hmset student:xxx id 1 college nnn address xn
OK
redis 127.0.0.1:6379> hgetall student:xxx
1) "id"
2) "1"
3) "college"
4) "nnn"
5) "address"
6) "xn"

If you have other queries though, like you want to do the same thing but select on where college = 'nnn' then you are going to have to denormalize your data. Denormalization is usually a bad thing in SQL, but in NoSQL it is very common.

If your primary query will be against the name, but you may need to query against the college, then you might do something like adding a set in addition to the hashes.

redis 127.0.0.1:6379> sadd college:nnn student:xxx
(integer) 1
redis 127.0.0.1:6379> smembers college:nnn
1) "student:xxx"

With your data structured like this, if you wanted to find all information for names going to college xn, you would first select the set, then select each hash based on the name returned in the set.

Your requirements will generally drive the design and the structures you use.

Madison

How would i find in college X, all students with name Y or description including word Z? – Harrod

I'd rather write hmset students:xxx id 1 college nnn address xn and then hgetall students:xxx. No? – Rockfish

@Supersharp, sure that sounds like a fine idea assuming you changed to using college:nnn instead of college.nnn like my example. Namespacing is a good thing. – Madison

Answer 2

With just 6 principles (which I collected here), it is very easy for a SQL minded person to adapt herself to Redis approach. Briefly they are:

1. The most important thing is that, don't be afraid to generate lots of key-value pairs. So feel free to store each row of the table in a different key.
2. Use Redis' hash map data type
3. Form key name from primary key values of the table by a separator (such as ":")
4. Store the remaining fields as a hash
5. When you want to query a single row, directly form the key and retrieve its results
6. When you want to query a range, use wild char "*" towards your key.

The link just gives a simple table example and how to model it in Redis. Following those 6 principles you can continue to think like you do for normal tables. (Of course without some not-so-relevant concepts as CRUD, constraints, relations, etc.)

Shavon

Answer 3

For plain, vanilla redis the other answers are completely correct, however, yesterday (02 - December - 2016) redis 4-rc1 is been released.

redis v4 provides support for modules and I just wrote a small module to embed SQLite into redis itself; rediSQL.

With that module you can actually use a fully functional SQL database inside your redis instace.

Squarerigger

I can confirm that RediSql is really really powerful! – Asyndeton

Answer 4

Hope it is not too late since the original question is long for six year of time. You may try my dbx plugin: https://github.com/cscan/dbx Which support the simple SQL to maintain the hashes in REDIS. Something like this:

127.0.0.1:6379> dbx.select name, tel from phonebook where gender = 'F' order by age desc

or calling from shell

$ redis-cli "dbx.select name, tel from phonebook where gender = 'F' order by age desc" 

Hope this help.

Geometrize

Answer 5

Redis just has some basic data structures with it, NoSQL and SQL are different worlds. But You can use Redis like some schemed SQL data store. There are funny program Redisql on github which try to play with Redis via SQL, and the idea behind Redisql is such that @sberry mentioned.

Atonsah

There is another RediSql, here: github.com/siscia/rediSQL That is really powerful, SQL support directly in Redis, and it is blazing fast, works with replication! – Asyndeton

The main reository for RediSQL is the one behind the company: github.com/redbeardlab/redisql – Squarerigger

Answer 6

You can try searchbox framework. searchbox provides easy way for querying redis data with its Criteria api.

Waterhouse

Answer 7

OnceDB is a full-text search in-memory database based on Redis. It supports data management like SQL relational databases and NoSQL schemaless databases.

OnceDB does not change the data storage structure of Redis and is fully compatible with Redis. Redis database files can be directly operated in OnceDB and then returned to Redis for use.

OnceDB automatically creates auxiliary indexes through operators:

= Ordinary field value, no index
@ Primary key
? Grouping index
* Keyword grouping index, separated by ',' between keywords
\ Sort index, the score weight of the index is the value of the field

for example, execute the following command to add the user data:

upsert user username @ dota password = 123456 title ? SDEI skills * java,go,c
> OK

you can search from the index by an operator, such as searching user data containing the c keyword, and printing the username and password fields.

find user 0 -1 username = * password = * skills * c

find user 0 -1 username = * password = * skills * c
1) (integer) 1
2) "user:dota"
3) "dota"
4) "123456"
5) "java,go,c"

Read more：OnceDB quick start

Rademacher

Answer 8

• In SQL database design, we first put everything into the database and then figure out how we will query about that

• In Redis Design, we first figure out what queries we need to answer, and then we are going to structure our data.

That is why Redis is super fast. Redis stores data as a hash in some cases. If the record has many attributes, in your case, a student might have "Age,name,class" attributes so storing "student` as the hash is useful.

In Redis, when you build your application, you have to see what you are going to store-users, sessions, products- and based on those things that your app is storing, you have to plan which data structures to use to store each thing.

Filament