Redis bitmap data to Mysql - ETL strategies

Question

I really love both redis and mysql, and use them both extensively. I am interested in purging certain keys that I no longer need from my redis instance because, well, memory is expensive. I'd like to park it on disk and leave it there forever. I'm not very particular about how, but I'm exploring housing it in mysql. For most redis data types, this is cake. It's either a string, or something or something json encoding can handle easily. My problem is bitmaps which are binary representations of data.

Here is my naive psydo code/python approach:

#create an arbitrary bitmap with every third bit ticked to 1
for i in range(100):
    rediscon.setbit('thekey',i*3, 1)

#get the value as a string
thevalue = rediscon.get('thekey')#is get appropriate for a bitmap?
#this looks something like "@ \b\x02\x00\x80 "

#do the insert into mysql
mysql.query("insert into table (key, value) VALUES ('thekey', "+MySQLdb.escape_string(thevalue)+")")

#do a sanity check, restore the key back to redis
#get the value from mysql and put it back in redis with a new key
val = MySQLdb.query("select value from table where key='thekey'")
rediscon.set('thekey_new', val)

print rediscon.bitcount('thekey')#this prints correctly as 100
print rediscon.bitcount('thekey_new')#this is wrong, it prints a number much less than 100

The mysql engine type is myisam, which I don't think matters. And I've tried BLOB and LONGTEXT for the column type for values. Is doing a get on a bit map what I want to do? How do I do an insert in mysql with binary data?

How do I do this? I want to be able to put this bit map in mysql, and then have the ability to restore it later. Thanks!

Answer 1

rediscon.get() is byte-safe, so yes, that will safely get you your bitmap.

The problem lies within your SQL statement. You could base64 encode the binary data before storing it.

Your approach looks fine, but if it's a lot of data (as in 'keys', not talking 'bytes' here), always use non-transactional pipelining. For the python implementation, see here.

Hope this helps, TW

Answer 2

I was able to get this to work, my original attempt above was actually conceptually right on. There was a small logic error that caused my sanity check to fail.

I experimented with base64 per Tw Bert's suggestion. It is not necessary, but I might use it. I can throw the results straight from redis.get into my mysql insert statement and pull it out for my sanity check and it works fine. I can also base64 encode what I put in mysql, and then base64 decode it when I read it out of mysql. Preliminarily, using base64 encoding appears to cost me an additional ~20% of disk within mysql compared with the raw binary. I don't think I care about this, however, and might be willing to pay that cost for the comfort of having the data in something ascii so I can "see" it better.

I can now couple this with some basic key selection and backup to disk any subset of keys I want to archive off of redis. I hope this helps somebody in the future.

UPDATE: It turns out that I didn't do this. My solution was pretty simple and didn't warrant the complexity described above. The naming structure of my keys was such that the sharding value was at the beginning followed by the details of that key. So, I did four things:

(1) I ran KEYS id_123* to get all the keys I wanted to archive (there are few keys, so KEYS isn't a huge blocker, use SCAN if you have a ton of keys) (2) Spin up a new redis-server (it could be on the same machine, with a different port) (3) MIGRATE all of the found keys to the new redis-server. (4) Issue a SAVE to get an RDB file and do with that what you please.

This is pretty simple, and allows you to keep it in "redis" format, so re-ingesting it later is pretty simple.