def migrate(replace=False,yearsback=10,dryrun=False):
api = eeDbAPI()
devices = api.getPeriphList()
eetsdb = eeTSDB('localhost',4242)
client = RESTOpenTSDBClient("localhost",4242)
for dev in devices:
# check that the metric is in eetsdbMapping
if not dev.periph_id in eetsdbMapping:
print(("Skipping", dev.periph_id, dev.name, dev.room_name, dev.usage_name))
continue
# check if the TS is there already. If yes, look for the last point and continue from there
ts = eetsdb.mkTimeseries(dev)
if not dryrun: eetsdb.registerTS(ts)
begin = datetime.now()-relativedelta(years=yearsback)
try:
res = client.search("LOOKUP",metric=ts.metric, tags=ts.tags)
if res["totalResults"]>1 :
print("Time series:")
pprint.pprint(ts.getMap())
print("Search result:")
pprint.pprint(res)
raise RuntimeError("The timeseries is ambiguous. This should not happen.")
elif res["totalResults"]==1:
tsuid = res["results"][0]["tsuid"]
sq = OpenTSDBtsuidSubQuery("sum",[tsuid])
if replace:
query = OpenTSDBQuery([sq],"%dy-ago"%yearsback,delete=True)
if not dryrun: answer = client.query(query)
begin = datetime.now()-relativedelta(years=yearsback)
time.sleep(5)
else:
query = OpenTSDBQuery([sq],"%dy-ago"%yearsback)
answer = client.query(query)
if len(answer)>0:
last = max([ int(k) for k in list(answer[0]["dps"].keys()) ])
begin = datetime.fromtimestamp(last+1)
# migrate that dev
print(("migrating",dev.periph_id, dev.name, dev.room_name, dev.usage_name, "from", begin))
if not dryrun: eetsdb.migrate(device=dev,start_date=begin, end_date=None)
except OpenTSDBError as e:
print(("Exception while processing",dev.periph_id, dev.name, dev.room_name, dev.usage_name,"Skipping."))
raise
def migrate(replace=False,yearsback=10):
api = eeDbAPI()
devices = api.getPeriphList()
eetsdb = eeTSDB('localhost',4242)
client = RESTOpenTSDBClient("localhost",4242)
for dev in devices:
# check that the metric is in eetsdbMapping
if not dev.periph_id in eetsdbMapping:
print "Skipping", dev.periph_id, dev.name, dev.room_name, dev.usage_name
continue
# check if the TS is there already. If yes, look for the last point and continue from there
ts = eetsdb.mkTimeseries(dev)
eetsdb.registerTS(ts)
begin = datetime.now()-relativedelta(years=yearsback)
try:
res = client.search("LOOKUP",metric=ts.metric, tags=ts.tags)
if res["totalResults"]>1 :
print "Time series:"
pprint.pprint(ts.getMap())
print "Search result:"
pprint.pprint(res)
raise RuntimeError("The timeseries is ambiguous. This should not happen.")
elif res["totalResults"]==1:
tsuid = res["results"][0]["tsuid"]
sq = OpenTSDBtsuidSubQuery("sum",[tsuid])
if replace:
query = OpenTSDBQuery([sq],"%dy-ago"%yearsback,delete=True)
answer = client.query(query)
begin = datetime.now()-relativedelta(years=yearsback)
time.sleep(5)
else:
query = OpenTSDBQuery([sq],"%dy-ago"%yearsback)
answer = client.query(query)
if len(answer)>0:
last = max([ int(k) for k in answer[0]["dps"].keys() ])
begin = datetime.fromtimestamp(last+1)
# migrate that dev
print "migrating",dev.periph_id, dev.name, dev.room_name, dev.usage_name, "from", begin
eetsdb.migrate(device=dev,start_date=begin, end_date=None)
except OpenTSDBError as e:
print "Exception while processing",dev.periph_id, dev.name, dev.room_name, dev.usage_name,"Skipping."
raise
class eeTSDB:
"""Simple utility to migrate the history from eedb to eetsdb"""
def __init__(self,host,port):
self.host_ = host
self.port_ = port
self.client_ = OpenTSDBClient(self.host_, self.port_)
def migrate(self,device,start_date=None, end_date=None, history = None, lastValue=None):
"""Main method: give device and time range to migrate to openTSDB"""
self.debugId = device.periph_id
timeseries = self.mkTimeseries(device)
self.registerTS(timeseries)
timeseries.loadFrom(self.client_)
if history is None:
history = device.getHistory(start_date, end_date)
measurements = self.mkMeasurements(timeseries,history)
if len(measurements)>0:
print "Inserting %d measurements for the following timeseries:"%len(measurements)
print timeseries.getMap(full=False).__str__()
self.insertHistory(measurements)
self.addAnnotation(timeseries)
def registerTS(self, timeseries):
try:
res = self.client_.search("LOOKUP",metric=timeseries.metric, tags=timeseries.tags)
except OpenTSDBError:
timeseries.assign_uid(self.client_)
def insertHistory(self, measurements):
return self.client_.put_measurements(measurements, summary=True, compress=True)
def mkTimeseries(self,device):
try:
lookup = self.client_.search("LOOKUP",tags={"periph_id":str(device.periph_id)})
if lookup["totalResults"]==0: # create if needed
metric = eetsdbMapping[int(device.periph_id)]
tags = { "periph_id":str(device.periph_id),
"room":self.cureString(device.room_name),
"name":self.cureString(device.name)}
return OpenTSDBTimeSeries(metric,tags)
elif lookup["totalResults"]==1: # take existing one if possible
return OpenTSDBTimeSeries(tsuid=lookup["results"][0]["tsuid"]).loadFrom(self.client_)
else: # abort in case of ambiguity
raise RuntimeError("More than one time series with tsuid = %s"%str(device.periph_id),lookup)
except OpenTSDBError:
metric = eetsdbMapping[int(device.periph_id)]
tags = { "periph_id":str(device.periph_id),
"room":self.cureString(device.room_name),
"name":self.cureString(device.name)}
return OpenTSDBTimeSeries(metric,tags)
def mkMeasurements(self,timeseries,history):
# history is a vector of pairs (measurement,timestamp)
history = self.cureValues(timeseries,history)
if eedbintegration[int(timeseries.tags["periph_id"])][0]:
conversionFactor=eedbintegration[int(timeseries.tags["periph_id"])][2]
samplingPeriod=eedbintegration[int(timeseries.tags["periph_id"])][1]
last= self.getLastValue(timeseries)
return [OpenTSDBMeasurement(timeseries, int(timestamp.strftime("%s")),value) for (value,timestamp) in self.cummulative(history, conversionFactor, samplingPeriod, last)]
else:
return [OpenTSDBMeasurement(timeseries, int(timestamp.strftime("%s")),value) for (value,timestamp) in history]
def addAnnotation(self,timeseries, isGlobal=False):
timeseries.loadFrom(self.client_)
tsuid = timeseries.metadata.tsuid
description = "Migrated from eedb"
custom={"host":socket.gethostname()}
self.client_.set_annotation(int(datetime.now().strftime("%s")), tsuid=tsuid, description=description, custom=custom)
def cureString(self,thestring):
asciichars = string.ascii_letters + "0123456789-_./"
return ''.join([c for c in thestring.replace(" ","_").replace("[","_").replace("]","_") if c in asciichars or ud.category(unicode(c)) in ['Ll', 'Lu']])
def cureValues(self,timeseries,history):
recipeName = eetsdbrecipes.get(int(timeseries.tags["periph_id"]), None)
recipe = getattr(cureValues, recipeName) if recipeName is not None else lambda x:x
return [(recipe(self.translateValue(value)),timestamp) for (value,timestamp) in history]
def translateValue(self,value):
if unidecode(value).lower() in eetsdbvalues: return eetsdbvalues[unidecode(value).lower()]
try:
return float(''.join([c for c in value if c in "-0123456789."]))
except:
print value,"cannot be translated"
return 0
def cummulative(self,inputHistory, conversionFactor = 3600000., samplingPeriod = None, last = None, integrationMode="trapeze"):
""" Integrates the input to return a cummulative distribution.
By default, it uses the trapeze integration rule.
If samplingPeriod is set, each point is taken independently over that time.
The default conversion factor works for both electricity (watt*s -> kWh) and gaz (dm3/h*s -> m3)
"""
data = sorted(inputHistory, key=lambda entry:entry[1])
output = []
for i,((d0,t0),(d1,t1)) in enumerate(pairwise(data)):
if last is None:
last = 0
output.append((0,t0))
if samplingPeriod is None:
if integrationMode=="trapeze":
last += (((d0+d1)/2.)*(t1-t0).total_seconds())/conversionFactor # good approximation for continuous functions
else:
last += d0*(t1-t0).total_seconds()/conversionFactor # best when the series "updates on changes"
else:
last += d0*samplingPeriod/conversionFactor # when measurements are defined on a fixed interval and are not continuous
output.append((last,t1))
return output
def getLastValue(self,timeseries):
# issue with query last, so do it by hand with some large backsearch. Heavy...
sq = OpenTSDBtsuidSubQuery("sum",[timeseries.metadata.tsuid])
query = OpenTSDBQuery([sq],"1y-ago")
answer = self.client_.query(query)
if len(answer)>0:
if len(answer[0]["dps"].keys()) >0:
last = max([ int(k) for k in answer[0]["dps"].keys() ])
return answer[0]["dps"][str(last)]
else:
print answer
else:
return None
class eeTSDB:
"""Simple utility to migrate the history from eedb to eetsdb"""
def __init__(self, host, port):
self.host_ = host
self.port_ = port
self.client_ = OpenTSDBClient(self.host_, self.port_)
def migrate(self,
device,
start_date=None,
end_date=None,
history=None,
lastValue=None):
"""Main method: give device and time range to migrate to openTSDB"""
self.debugId = device.periph_id
timeseries = self.mkTimeseries(device)
self.registerTS(timeseries)
timeseries.loadFrom(self.client_)
if history is None:
history = device.getHistory(start_date, end_date)
measurements = self.mkMeasurements(timeseries, history)
if len(measurements) > 0:
print(("Inserting %d measurements for the following timeseries:" %
len(measurements)))
print((timeseries.getMap(full=False).__str__()))
self.insertHistory(measurements)
self.addAnnotation(timeseries)
def registerTS(self, timeseries):
try:
res = self.client_.search("LOOKUP",
metric=timeseries.metric,
tags=timeseries.tags)
except OpenTSDBError:
timeseries.assign_uid(self.client_)
def insertHistory(self, measurements):
return self.client_.put_measurements(measurements,
summary=True,
compress=True)
# TS METADATA:
# self.displayName = kwargs.get("displayName",'') # TODO: put most recent name in metadata
# self.units = kwargs.get("units",'') #TODO: put units in metadata. Should come from the yaml cfg
# self.custom = kwargs["custom"] if kwargs.get("custom",None) is not None else {} # TODO: other: creation_date,usage_name
def mkTimeseries(self, device):
try:
lookup = self.client_.search(
"LOOKUP", tags={"periph_id": str(device.periph_id)})
if lookup["totalResults"] == 0: # create if needed
metric = eetsdbMapping[int(device.periph_id)]
tags = {
"periph_id": str(device.periph_id),
"room": self.cureString(device.room_name),
"name": self.cureString(device.name)
}
return OpenTSDBTimeSeries(metric, tags)
elif lookup["totalResults"] == 1: # take existing one if possible
return OpenTSDBTimeSeries(
tsuid=lookup["results"][0]["tsuid"]).loadFrom(self.client_)
else: # abort in case of ambiguity
raise RuntimeError(
"More than one time series with tsuid = %s" %
str(device.periph_id), lookup)
except OpenTSDBError:
metric = eetsdbMapping[int(device.periph_id)]
tags = {
"periph_id": str(device.periph_id),
"room": self.cureString(device.room_name),
"name": self.cureString(device.name)
}
return OpenTSDBTimeSeries(metric, tags)
def mkMeasurements(self, timeseries, history):
# history is a vector of pairs (measurement,timestamp)
history = self.cureValues(timeseries, history)
if eedbintegration[int(timeseries.tags["periph_id"])][0]:
conversionFactor = eedbintegration[int(
timeseries.tags["periph_id"])][2]
samplingPeriod = eedbintegration[int(
timeseries.tags["periph_id"])][1]
last = self.getLastValue(timeseries)
return [
OpenTSDBMeasurement(timeseries, int(timestamp.strftime("%s")),
value)
for (value, timestamp) in self.cummulative(
history, conversionFactor, samplingPeriod, last)
]
else:
return [
OpenTSDBMeasurement(timeseries, int(timestamp.strftime("%s")),
value) for (value, timestamp) in history
]
def addAnnotation(self, timeseries, isGlobal=False):
timeseries.loadFrom(self.client_)
tsuid = timeseries.metadata.tsuid
description = "Migrated from eedb"
custom = {"host": socket.gethostname()}
self.client_.set_annotation(int(datetime.now().strftime("%s")),
tsuid=tsuid,
description=description,
custom=custom)
def cureString(self, thestring):
asciichars = string.ascii_letters + "0123456789-_./"
return ''.join([
c for c in thestring.replace(" ", "_").replace("[", "_").replace(
"]", "_")
if c in asciichars or ud.category(str(c)) in ['Ll', 'Lu']
])
def cureValues(self, timeseries, history):
recipeName = eetsdbrecipes.get(int(timeseries.tags["periph_id"]), None)
recipe = getattr(cureValues,
recipeName) if recipeName is not None else lambda x: x
return [
x for x in [(recipe(self.translateValue(value)), timestamp)
for (value, timestamp) in history] if x[0] is not None
]
def translateValue(self, value):
if unidecode(value).lower() in eetsdbvalues:
return eetsdbvalues[unidecode(value).lower()]
try:
return float(''.join([c for c in value if c in "-0123456789."]))
except:
print((value, "cannot be translated"))
return 0
def cummulative(self,
inputHistory,
conversionFactor=3600000.,
samplingPeriod=None,
last=None,
integrationMode="trapeze"):
""" Integrates the input to return a cummulative distribution.
By default, it uses the trapeze integration rule.
If samplingPeriod is set, each point is taken independently over that time.
The default conversion factor works for both electricity (watt*s -> kWh) and gaz (dm3/h*s -> m3)
"""
data = sorted(inputHistory, key=lambda entry: entry[1])
output = []
for i, ((d0, t0), (d1, t1)) in enumerate(pairwise(data)):
if last is None:
last = 0
output.append((0, t0))
else:
#TODO: fix this: have to add one entry for the first measurement.
# for that, one needs last, d0, t0, but also the time of the last measurement
pass
if samplingPeriod is None:
if integrationMode == "trapeze":
last += (
((d0 + d1) / 2.) * (t1 - t0).total_seconds()
) / conversionFactor # good approximation for continuous functions
else:
last += d0 * (t1 - t0).total_seconds(
) / conversionFactor # best when the series "updates on changes"
else:
last += d0 * samplingPeriod / conversionFactor # when measurements are defined on a fixed interval and are not continuous
output.append((last, t1))
return output
def getLastValue(self, timeseries):
# issue with query last, so do it by hand with some large backsearch. Heavy...
sq = OpenTSDBtsuidSubQuery("sum", [timeseries.metadata.tsuid])
query = OpenTSDBQuery([sq], "1y-ago")
answer = self.client_.query(query)
if len(answer) > 0:
if len(list(answer[0]["dps"].keys())) > 0:
last = max([int(k) for k in list(answer[0]["dps"].keys())])
return answer[0]["dps"][str(last)]
else:
print(answer)
else:
return None