{"id":5325,"date":"2014-07-07T16:12:36","date_gmt":"2014-07-07T14:12:36","guid":{"rendered":"http:\/\/blog.zhaw.ch\/icclab\/?p=5325"},"modified":"2014-12-02T10:32:35","modified_gmt":"2014-12-02T08:32:35","slug":"migration-of-ceilometer-energy-consumption-data-from-havanamysql-to-icehousemongodb","status":"publish","type":"post","link":"https:\/\/blog.zhaw.ch\/icclab\/migration-of-ceilometer-energy-consumption-data-from-havanamysql-to-icehousemongodb\/","title":{"rendered":"Migration of Ceilometer Energy Consumption Data from Havana\/MySQL to Icehouse\/MongoDB"},"content":{"rendered":"

We are working on upgrading some of our servers from Havana<\/a> to Icehouse<\/a>. One of the reasons for doing this is that the performance of Ceilometer on Havana is underwhelming. Part of the move involves moving to a mongodb<\/a> backend (default in Mirantis Openstack 5.0<\/a>) but we also want to keep our energy consumption data from our Havana system. Consequently, we have a data migration issue from Havana\/mysql<\/a> to Icehouse\/mongo. Here, we describe how we did this migration. (Note that as this is just a small experimental system, we don\u2019t have important user and account data to transfer).<\/p>\n

<\/p>\n

While there is lots of information out there on moving from mysql to mongo<\/a> the Havana to Icehouse move also brings specific issues with changes in data structures which had to be taken into account here. The data structures are shown below:
\n\"mysql-mongo\"<\/a><\/p>\n

In havana\/mysql, the energy consumption data is captured in three tables: the resource, the source association and the meter. The sample data appears in the meter table. In icehouse\/mongo, the source association table has been dropped and there are only two tables – the resources and the meters; again the sample data is stored in the meter table. The resource table has been extended slightly to include more information describing the meter (the unit it uses and the meter type) and the names of some of the fields have been modified a little. The source – which was previously in a separate sourceassoc – has now been captured in both the resource and meter tables.<\/p>\n

While the differences are not large, migrating the data was not as simple as exporting a csv and importing it into mongo; we wrote a short python script to make the data transformations necessary for this import. We used the MySQLdb<\/a>, pymongo<\/a> and json<\/a> libraries.<\/p>\n

First, we had to import relevant libraries and set up the database connection.<\/p>\n

\r\n#!\/usr\/bin\/python\r\nimport MySQLdb\r\nimport MySQLdb.cursors\r\nimport pymongo\r\nfrom pymongo import MongoClient\r\nimport json\r\nimport datetime\r\nconnection = MySQLdb.connect (host = \"host.ip.address\", user = \"user\", passwd = \"passw\", db = \"ceilometer\", cursorclass = MySQLdb.cursors.DictCursor)\r\n<\/code><\/pre>\n
Then, we have to extract the resource information from the mysql database. We need to extract that subset of the resources that are connected to energy meters via the source association and then we need to change the names of the fields in each of the records – id needs to be changed to _id, source_id is changed to source, resource_metadata is changed to metadata and change the metadata from string type to dictionary.(Note that in our system, we had three energy meters named \u2018node-41.domain.tld\u2019, \u2018node-42.domain.tld\u2019 and \u2018node-43.domain.tld\u2019). Also, here we add a source field which comes from the sourceassoc directly to the resource record (in the field source).<\/p>\n
\r\ncursor = connection.cursor()\r\ncursor.execute (\"select distinct r.id, r.resource_metadata, r.project_id, r.user_id, s.source_id from resource as r inner join sourceassoc as s on s.resource_id = r.id where \u00a0r.id = 'node-41.domain.tld' \u00a0or r.id = 'node-42.domain.tld' or r.id = 'node-43.domain.tld'\")\r\nresource_data = cursor.fetchall()\r\ncursor.close()\r\n\r\nfor resource in resource_data:\r\n    resource['_id'] = resource.pop('id')\r\n    resource['source'] = resource.pop('source_id')\r\n    resource['metadata'] = resource.pop('resource_metadata')\r\n    resource['metadata'] = json.loads(resource['metadata'])\r\n<\/code><\/pre>\n
Next, we need to add basic meter information to the resource table. This had to be extracted from the havana\/mysql meter tables and inserted into the icehouse\/mongo resource tables. It involved obtaining the set of distinct counter names, units, types and resource id from havana\/mysql and matching these with resource ids in icehouse\/mongo.<\/p>\n
\r\ncursor = connection.cursor()\r\n# assumes that the counter_name, counter_unit and counter_type are the\r\n# same in all samples for a particular meter\r\ncursor.execute (\"select distinct counter_name, counter_unit, counter_type, resource.id from meter, resource where resource.id = meter.resource_id and meter.counter_name = 'energy'\")\r\nmeter_resource_data = cursor.fetchall()\r\ncursor.close()\r\n\r\ntry:\r\n    for meter_resource in meter_resource_data:\r\n        for resource in resource_data:\r\n            if resource['_id'] == meter_resource['id'] :\r\n                del meter_resource['id']\r\n                meter_list = []\r\n                meter_list.append(meter_resource)\r\n                resource['meter'] = meter_list\r\n                break\r\nexcept:\r\n    print 'Error iterating over resource samples'\u00a0\r\n<\/code><\/pre>\n
Having made the modifications to the resource table, we can now write it to the mongo database.<\/p>\n
\r\nclient = MongoClient()\r\n# the database name is usually ceilometer, so the next line usually looks like\r\n# db = client.ceilometer\r\ndb = client.databasename<\/em>\r\n# the collection name is usually resource, so the next line usually looks like\r\n# collection = db.resource\r\ncollection = db.collectioname<\/i>\r\nfor row in resource_data:\r\n    collection.insert(row)\r\n<\/code><\/pre>\n
Only a couple of small modifications are required to the meter table – we need to remove the id and modify the timestamp as the timestamp in the havana\/mysql model is represented as standard unix time, but the icehouse\/mongo timestamp is represented using an ISO format. The code below does this.<\/p>\n
\r\ncursor = connection.cursor()\r\ncursor.execute (\"select m.*, s.source_id from meter as m inner join sourceassoc as s on s.meter_id = m.id and m.counter_name = 'energy';\")\r\nmeters = cursor.fetchall()\r\nconnection.close()\r\ntry:\r\n    for meter in meters:\r\n        meter['resource_metadata'] = json.loads(meter['resource_metadata'])\r\n        meter['source'] = meter.pop('source_id')\r\n        meter['timestamp'] = datetime.datetime.fromtimestamp(meter['timestamp'])\r\n        meter.pop('id')\r\nexcept:\r\n    print 'Error iterating over meter samples'\r\n# and then we put it into the mongo db\r\n# the collection name meter is assumed below - this is default in ceilometer\r\ncollection_meter = db.meter\r\nfor row in meters:\r\n    collection_meter.insert(row)\r\n<\/code><\/pre>\n
Note: Initial tests indicate that Icehouse\/mongo is much faster than Havana\/mysql, the difference between the responses was approximately 30 sec (45 sec in mysql and 16 in mongo).<\/p>\n
The full code is below in case you\u2019d like to copy and paste.<\/p>\n
\r\n#!\/usr\/bin\/python\r\n#-*- coding: utf-8 -*-\r\nimport MySQLdb\r\nimport MySQLdb.cursors\r\nimport pymongo\r\nfrom pymongo import MongoClient\r\nimport json\r\nimport datetime\r\n\r\nconnection = MySQLdb.connect(host = \"host\", user = \"user\", passwd = \"passw\", db = \"ceilometer\", cursorclass = MySQLdb.cursors.DictCursor)\r\ncursor = connection.cursor()\r\ncursor.execute (\"select distinct r.id, r.resource_metadata, r.project_id, r.user_id, s.source_id from resource as r inner join sourceassoc as s on s.resource_id = r.id where \u00a0r.id = 'node-41.domain.tld' \u00a0or r.id = 'node-42.domain.tld' or r.id = 'node-43.domain.tld'\")\r\nresource_data = cursor.fetchall()\r\ncursor.close()\r\n\r\nfor resource in resource_data:\r\n    resource['_id'] = resource.pop('id')\r\n    resource['source'] = resource.pop('source_id')\r\n    resource['metadata'] = resource.pop('resource_metadata')\r\n    resource['metadata'] = json.loads(resource['metadata'])\r\n\r\ncursor = connection.cursor()\r\ncursor.execute (\"select distinct counter_name, counter_unit, counter_type, resource.id from meter, resource where resource.id = meter.resource_id\")\r\nmeter_resource_data = cursor.fetchall()\r\ncursor.close()\r\n\r\ntry:\r\n    for meter_resource in meter_resource_data:\r\n        for resource in resource_data:\r\n            if resource['_id'] == meter_resource['id'] :\r\n                del meter_resource['id']\r\n                meter_list = []\r\n                meter_list.append(meter_resource)\r\n                resource['meter'] = meter_list\r\n                break\r\nexcept:\r\n    print 'Error iterating over resource samples'\r\n\r\ncursor = connection.cursor()\r\ncursor.execute (\"select m.*, s.source_id from meter as m inner join sourceassoc as s on s.meter_id = m.id and m.counter_name = 'energy';\")\r\nmeters = cursor.fetchall()\r\nconnection.close()\r\n\r\ntry:\r\n    for meter in meters:\r\n        meter['resource_metadata'] = json.loads(meter['resource_metadata'])\r\n        meter['source'] = meter.pop('source_id')\r\n        meter['timestamp'] = datetime.datetime.fromtimestamp(meter['timestamp'])\r\n        meter.pop('id')\r\nexcept:\r\n    print 'Error iterating over meter samples'\r\n\r\nclient = MongoClient()\r\ndb = client.ceilometer\r\n\r\ncollection_resource = db.resource\r\nfor row in resource_data:\r\n    collection_resource.insert(row)\r\n\r\ncollection_meter = db.meter\r\nfor row in meters:\r\n    collection_meter.insert(row)<\/code><\/pre>\n
Thanks Se\u00e1n<\/a> for all the help.<\/p>\n
If you’re interested in this topic, you may be interested in some of our other blog posts:<\/p>\n