Redis is not a relational database. But if you’re coming from the RDBMS world, in this post you will discover how to resolve query, search, and aggregation problems in Redis, and convert SQL queries to Redis commands.
Redis is a good fit to perform many of the operations you would do on a RDBMS. You can even execute a JOIN-like statement! I hope you will find this cheat sheet useful.
For my usual tests, I used to import the SQL database world into MySQL and make experiments. To keep consistency with the examples and tests I do, I took the time to convert the popular world database into Redis syntax. You can find the database in my repository, so you can import it and execute these examples.
Install Redis Stack
The first thing to do is to launch a Redis Stack instance. You can use Docker for that or any other installation method. If using Docker, run:
docker run -d --name redis-stack -p 6379:6379 -p 8001:8001 redis/redis-stack:latest
Create indexes
Then, connect to the server with redis-cli and create a couple of indexes.
FT.CREATE city_idx
ON HASH
PREFIX 1 city:
SCHEMA Name AS name TAG
CountryCode AS countrycode TAG SORTABLE
Population AS population NUMERIC SORTABLE
District AS district TAG SORTABLE
FT.CREATE country_idx
ON HASH
PREFIX 1 country:
SCHEMA Name AS name TAG
Code AS code TAG
Region AS region TAG
We are now ready to start testing the commands. The SQL database is a MySQL with the world dataset loaded, if you’s like to reproduce the SQL queries too.
SELECT, primary key access
SQL
Redis
SELECT Name FROM city WHERE ID=3839; +------+-------+-------------+----------+------------+ | ID | Name | CountryCode | District | Population | +------+-------+-------------+----------+------------+ | 3839 | Miami | USA | Florida | 362470 | +------+-------+-------------+----------+------------+ 1 row in set (0.00 sec)
SELECT Name FROM city WHERE ID=3839; +-------+ | Name | +-------+ | Miami | +-------+
HGET city:3839 Name "Miami"
AND
SQL
Redis
SELECT District FROM city WHERE Name = 'Newcastle' AND CountryCode = 'AUS'; +-----------------+ | District | +-----------------+ | New South Wales | +-----------------+ 1 row in set (0.00 sec)
FT.SEARCH city_idx '@name:{newcastle} @countrycode:{AUS}' RETURN 1 district 1) (integer) 1 2) "city:137" 3) 1) "district" 2) "New South Wales"
OR
SQL
Redis
SELECT Name FROM city WHERE Name = 'Madrid' OR Name = 'Roma'; +--------+ | Name | +--------+ | Madrid | | Roma | +--------+ 2 rows in set (0.00 sec)
SELECT Name FROM city WHERE Name = 'Roma' OR District = 'Valencia'; +---------------------------------+ | Name | +---------------------------------+ | Valencia | | Alicante [Alacant] | | Elche [Elx] | | Castellón de la Plana [Castell | | Roma | +---------------------------------+ 5 rows in set (0.00 sec)
SELECT Name, District FROM city WHERE Name LIKE "New%" ORDER BY District ASC LIMIT 2; +---------------------+-------------+ | Name | District | +---------------------+-------------+ | New Haven | Connecticut | | New Delhi | Delhi | +---------------------+-------------+
FT.SEARCH city_idx @name:{New*} RETURN 2 Name District LIMIT 0 2 SORTBY district ASC 1) (integer) 12 2) "city:3971" 3) 1) "Name" 2) "New Haven" 3) "District" 4) "Connecticut" 4) "city:1109" 5) 1) "Name" 2) "New Delhi" 3) "District" 4) "Delhi"
COUNT rows in a table
SQL
Redis
SELECT COUNT() FROM city; +----------+ | COUNT() | +----------+ | 4079 | +----------+ 1 row in set (0.13 sec)
FT.SEARCH city_idx * LIMIT 0 0 1) (integer) 4079
COUNT rows in the result set
SQL
Redis
SELECT COUNT(1) FROM city WHERE Name LIKE "New%"; +----------+ | COUNT(1) | +----------+ | 12 | +----------+
SELECT COUNT(1) as codes, CountryCode FROM city GROUP BY CountryCode ORDER BY codes DESC LIMIT 3; +-------+-------------+ | codes | CountryCode | +-------+-------------+ | 363 | CHN | | 341 | IND | | 274 | USA | +-------+-------------+ 3 rows in set (0.01 sec)
SELECT CountryCode, max(Population) AS mostpopulatedcity FROM city GROUP BY CountryCode ORDER BY mostpopulatedcity DESC LIMIT 3; +-------------+-------------------+ | CountryCode | mostpopulatedcity | +-------------+-------------------+ | IND | 10500000 | | KOR | 9981619 | | BRA | 9968485 | +-------------+-------------------+
SELECT MAX(Population) AS maximum FROM city; +----------+ | maximum | +----------+ | 10500000 | +----------+
FT.AGGREGATE city_idx * GROUPBY 0 REDUCE MAX 1 @population AS maximum 1) (integer) 1 2) 1) "maximum" 2) "10500000"
GROUP BY and SUM
SQL
Redis
SELECT CountryCode, SUM(Population) AS mostpopulatedcountry FROM city GROUP BY CountryCode ORDER BY mostpopulatedcountry DESC LIMIT 3; +-------------+----------------------+ | CountryCode | mostpopulatedcountry | +-------------+----------------------+ | CHN | 175953614 | | IND | 123298526 | | BRA | 85876862 | +-------------+----------------------+ 3 rows in set (0.02 sec)
SELECT CountryCode, SUM(Population) AS mostpopulatedcountry FROM city GROUP BY CountryCode HAVING mostpopulatedcountry>100000000 ORDER BY mostpopulatedcountry DESC LIMIT 3; +-------------+----------------------+ | CountryCode | mostpopulatedcountry | +-------------+----------------------+ | CHN | 175953614 | | IND | 123298526 | +-------------+----------------------+ 2 rows in set (0.00 sec)
SELECT AVG(Population) AS average, CountryCode FROM city WHERE CountryCode = 'ITA'; +-------------+-------------+ | average | CountryCode | +-------------+-------------+ | 260121.0172 | ITA | +-------------+-------------+ 1 row in set (0.01 sec)
FT.AGGREGATE city_idx @countrycode:{CHN} GROUPBY 0 REDUCE SUM 1 @population AS maximum 1) (integer) 1 2) 1) "maximum" 2) "175953614"
AVG
SQL
Redis
SELECT AVG(Population), CountryCode FROM city WHERE CountryCode = 'ITA'; +-----------------+-------------+ | AVG(Population) | CountryCode | +-----------------+-------------+ | 260121.0172 | ITA | +-----------------+-------------+ 1 row in set (0.01 sec)
FT.AGGREGATE city_idx @countrycode:{ITA} GROUPBY 0 REDUCE AVG 1 @population AS average 1) (integer) 1 2) 1) "average" 2) "260121.017241"
DISTINCT
SQL
Redis
SELECT DISTINCT CountryCode AS countrycodes FROM city; +--------------+ | countrycodes | +--------------+ | ABW | | AFG | | AGO | ...
SELECT CONCAT(name, ' - ',District) AS output, Population FROM city ORDER BY Population DESC LIMIT 3; +-------------------------------+------------+ | CONCAT(name, ' - ',District) | Population | +-------------------------------+------------+ | Mumbai (Bombay) - Maharashtra | 10500000 | | Seoul - Seoul | 9981619 | | São Paulo - São Paulo | 9968485 | +-------------------------------+------------+ 3 rows in set (0.01 sec)
SELECT Name FROM city WHERE Population BETWEEN 100 AND 500; +---------------------+ | Name | +---------------------+ | West Island | | Fakaofo | | Città del Vaticano | +---------------------+ 3 rows in set (0.00 sec)
SELECT Name FROM city WHERE Population <= 42 OR Population >= 10000000; +-----------------+ | Name | +-----------------+ | Mumbai (Bombay) | | Adamstown | +-----------------+ 2 rows in set (0.01 sec)
SELECT Name FROM city WHERE Population < 42 OR Population > 10000000; +-----------------+ | Name | +-----------------+ | Mumbai (Bombay) | +-----------------+ 1 row in set (0.00 sec)
SELECT city.Name, country.Region FROM city LEFT JOIN country ON city.CountryCode = country.Code WHERE city.Name = 'Madrid'; +--------+-----------------+ | Name | Region | +--------+-----------------+ | Madrid | Southern Europe | +--------+-----------------+ 1 row in set (0.00 sec)
Using the Lua programmability features of Redis, we can execute commands atomically. Leveraging the indexes for both city and country, we can execute a JOIN-like statement by feeding the second query with the result of the first (after all, this is what an indexed JOIN is). From Redis 7 on, you can test Redis functions, for a more structured way to write Lua scripts.
EVAL "local searchres = redis.call('FT.SEARCH','city_idx','@name:{'..ARGV[1]..'}','RETURN',2,'name','countrycode','DIALECT',2) local region = redis.call('FT.SEARCH','country_idx','@code:{'..searchres[3][4]..'}','RETURN',1,'region','DIALECT',2) return {searchres[3][2], region[3][2]}" 0 Madrid 1) "Madrid" 2) "Southern Europe"
Evaluate NULL
SQL
Redis
SELECT Name FROM country WHERE IndepYear IS NULL ORDER BY SurfaceArea DESC LIMIT 3; +----------------+ | Name | +----------------+ | Antarctica | | Greenland | | Western Sahara | +----------------+ 3 rows in set (0.00 sec)
Redis indexes are implemented as inverted indexes, so Redis keeps track of the documents containing certain terms. If you would like to have a NULL check, you will have to specify an arbitrary value to indicate the NULLness of the field (e.g. -1). The Redis dataset proposed in this post does not manage NULL values (left as empty string: “”). Download and manipulated the world.txt file as desired.
BEGIN, COMMIT and ROLLBACK
SQL
Redis
BEGIN; INSERT INTO city(Name,CountryCode,District,Population) VALUES ("Macerata","ITA","Marche",42209); INSERT INTO city(Name,CountryCode,District,Population) VALUES ("Fermo","ITA","Marche",37396); COMMIT;
BEGIN; INSERT INTO city(Name,CountryCode,District,Population) VALUES ("Fermo","ITA","Marche", 37396); ROLLBACK;
Redis Transactions provide atomic execution of commands. Read more about the usage and guarantees.
MULTI OK HSET city:4081 Name Macerata CountryCode ITA District Marche Population 42209 QUEUED HSET city:4082 Name Fermo CountryCode ITA District Marche Population 37396 QUEUED EXEC 1) (integer) 4 2) (integer) 4
MULTI OK HSET city:4082 Name Fermo CountryCode ITA District Marche Population 37396 QUEUED DISCARD OK
SELECT FOR UPDATE
SQL
Redis
BEGIN; SELECT Name,District FROM city WHERE Name="Macerata" FOR UPDATE; +----------+----------+ | Name | District | +----------+----------+ | Macerata | Marche | +----------+----------+ 1 row in set (0.01 sec)
Row is locked, do something and commit
Redis does not lock data; instead, it offers optimistic locking using the WATCH command. If at EXEC time the keys we are watching have been changed, the transaction will be aborted.
Example of a successful transaction: no change is done from another session to city:4081, the optimistic locking is successful.
WATCH city:4081 OK MULTI OK HSET city:4081 Population 42309 QUEUED EXEC 1) (integer) 0
Example of a failed transaction:
Session 1: WATCH city:4081 OK
Session 2, change the key in some way: HSET city:4081 Population 42409 (integer) 0
Session 1, go ahead with the transaction and see it fail with (nil): MULTI OK HSET city:4081 Population 42309 QUEUED EXEC (nil)
I hope you enjoyed this post! Try these examples and learn to convert SQL queries to Redis commands on a Redis Stack database or create a free Redis Cloud subscription.
Redis is a registered trademark of Redis Ltd. Any rights therein are reserved to Redis Ltd. Any use by mortensi is for referential purposes only and does not indicate any sponsorship, endorsement or affiliation between Redis and mortensi.
admin
