Introduction
When dealing with a huge amount of static data’s which are requested continuously, we need to have a fast key-value cache in place. Since we already had a huge chunk of data in SQL database. Let’s see how can we convert that to CSV and then to redis.
Step – 1 | Export data from SQL database
Here the data is exported to CSV format from the SQL data store using the favourite tools. We used “sequel pro” to dump the data to CSV format.
Step -2 | Convert the raw CSV data to Redis Commands (Generate the Redis commands from CSV)
Our CSV file is in the following format. Let’s call this as input.csv
1, key1, value1, created_at, updated_at 2, key2, value2, created_at, updated_at
Convert the CSV to Redis commands. This will print “SET, key1, key2” from the “input.csv” file and remove the double quotes. Edit the command to match your CSV file. “$1” prints the first column. (Counting start from 1 and not 0)
awk -F, 'BEGIN {OFS=","} { print "SET",$2, $3}' input.csv | sed 's/\"//g'
This will print the response on the command line as follows. Make sure that the output is in the format “SET, key1,key2“. Once you are sure that the response is in the expected format, save the file to output.txt as follows
awk -F, 'BEGIN {OFS=","} { print "SET",$2, $3}' input.csv | sed 's/\"//g' > output.txt
Note: We will be using the Redis mass insert. However, you need to convert the Redis commands to Redis protocol to do that.
Step – 3 | Convert the Redis command generated to Redis protocol. (Redis protocol generator)
Here we will be generating the redis protocol to convert the redis commands for importing to redis. We will be using the redis mass insert method for that.
Copy and save the following script to gen_redis_proto.py. Raw GitHub link
#!/usr/bin/env python -tt # -*- coding: UTF-8 -*- """ Generating Redis Protocol Generate the Redis protocol, in raw format, in order to use 'redis-cli --pipe' command to massively insert/delete.... keys in a redis server It accepts as input a pipe with redis commands formatted as "SET key value" or "DEL key"... Usage: echo "SET,mykey1,value1\nSET,mykey2,value2" > data.txt cat data.txt | python gen_redis_proto.py | redis-cli --pipe """ __author__ = "Salimane Adjao Moustapha ([email protected])" __version__ = "$Revision: 1.0 $" __date__ = "$Date: 2013/04/30 12:57:19 $" __copyleft__ = "Copyleft (c) 2013 Salimane Adjao Moustapha" __license__ = "MIT" import sys import fileinput from itertools import imap def encode(value): "Return a bytestring representation of the value" if isinstance(value, bytes): return value if not isinstance(value, unicode): value = str(value) if isinstance(value, unicode): value = value.encode('utf-8', 'strict') return value def gen_redis_proto(*cmd): proto = "" proto += "*" + str(len(cmd)) + "\r\n" for arg in imap(encode, cmd): proto += "$" + str(len(arg)) + "\r\n" proto += arg + "\r\n" return proto if __name__ == '__main__': for line in fileinput.input(): sys.stdout.write(gen_redis_proto(*line.rstrip().split(',')))
Now run the following command to generate the redis protocol command and save to redis using the mass insert method.
cat output.txt | python gen_redis_proto.py | redis-cli --pipe
The CSV will be now cached into the redis memory. You can save the data in the memory to a .rdb file using “BGSAVE” command