def handle_hourly(hostname, feedtype, since):
"""Emit JSON for rtstats for this host"""
pgconn = util.get_dbconn()
cursor = pgconn.cursor()
flimit = ""
if feedtype != "":
flimit = " and p.feedtype_id = get_ldm_feedtype_id('%s') " % (
feedtype,
)
tlimit = ""
if since is not None:
tlimit = (" and h.valid >= '%s' ") % (
pd.to_datetime(since).strftime("%Y-%m-%d %H:%M+00"),
)
sts = datetime.datetime.utcnow()
cursor.execute(
"""
select
to_char(valid at time zone 'UTC', 'YYYY-MM-DDThh24:MI:SSZ'),
h.feedtype_path_id,
(select hostname from ldm_hostnames where id = p.origin_host_id) as origin,
(select hostname from ldm_hostnames where id = p.relay_host_id) as relay,
min_latency,
avg_latency,
max_latency,
nprods,
nbytes,
(select feedtype from ldm_feedtypes where id = p.feedtype_id) as feedtype
from ldm_rtstats_hourly h JOIN ldm_feedtype_paths p on
(h.feedtype_path_id = p.id) WHERE
p.node_host_id = get_ldm_host_id(%s) """
+ flimit
+ """
"""
+ tlimit
+ """
ORDER by h.valid ASC
""",
(hostname,),
)
utcnow = datetime.datetime.utcnow()
res = dict()
res["query_time[secs]"] = (utcnow - sts).total_seconds()
res["hostname"] = hostname
res["columns"] = [
"valid",
"feedtype_path_id",
"origin",
"relay",
"min_latency",
"avg_latency",
"max_latency",
"nprods",
"nbytes",
"feedtype",
]
res["data"] = []
for row in cursor:
res["data"].append(row)
return json.dumps(res)
def handle_topology(feedtype, reverse=False):
"""Generate topology for this feedtype"""
if feedtype == '':
return json.dumps("NO_FEEDTYPE_SET_ERROR")
pgconn = util.get_dbconn()
cursor = pgconn.cursor()
# compute all upstreams for this feedtype
upstreams = {}
sts = datetime.datetime.utcnow()
cursor.execute(
"""
WITH active as (
select distinct id from
ldm_feedtype_paths p JOIN ldm_rtstats_hourly r
on (p.id = r.feedtype_path_id)
WHERE p.feedtype_id = get_ldm_feedtype_id(%s)
and r.valid > (now() - '24 hours'::interval))
select distinct
(select hostname from ldm_hostnames where id = p.relay_host_id) as relay,
(select hostname from ldm_hostnames where id = p.node_host_id) as node
from ldm_feedtype_paths p JOIN active a on (p.id = a.id)
""", (feedtype, ))
ets = datetime.datetime.utcnow()
for row in cursor:
if reverse:
upstreams.setdefault(row[0], []).append(row[1])
else:
upstreams.setdefault(row[1], []).append(row[0])
res = dict()
res['query_time[secs]'] = (ets - sts).total_seconds()
res['generation_time'] = ets.strftime("%Y-%m-%dT%H:%M:%SZ")
res['feedtype'] = feedtype
res['upstreams' if not reverse else 'downstreams'] = upstreams
return json.dumps(res)
def handle_rtstats(hostname, feedtype):
"""Emit JSON for rtstats for this host"""
pgconn = util.get_dbconn()
cursor = pgconn.cursor()
flimit = ''
if feedtype != '':
flimit = " and p.feedtype_id = get_ldm_feedtype_id('%s') " % (
feedtype, )
cursor.execute(
"""
select
to_char(r.entry_added at time zone 'UTC', 'YYYY-MM-DDThh24:MI:SSZ'),
r.feedtype_path_id,
(select hostname from ldm_hostnames where id = p.origin_host_id) as origin,
(select hostname from ldm_hostnames where id = p.relay_host_id) as relay,
avg_latency,
(select feedtype from ldm_feedtypes where id = p.feedtype_id) as feedtype
from ldm_rtstats r JOIN ldm_feedtype_paths p on
(r.feedtype_path_id = p.id) WHERE
p.node_host_id = get_ldm_host_id(%s) and
r.entry_added > now() - '36 hours'::interval """ + flimit + """
ORDER by r.entry_added ASC
""", (hostname, ))
res = dict()
res['hostname'] = hostname
res['columns'] = [
'entry_added', 'feedtype_path_id', 'origin', 'relay', 'avg_latency',
'feedtype'
]
res['data'] = []
for row in cursor:
res['data'].append(row)
return json.dumps(res)
def stats():
"""Overview stats"""
pgconn = util.get_dbconn()
cursor = pgconn.cursor()
sts = datetime.datetime.utcnow() - datetime.timedelta(hours=1)
if sts.minute < 20: # Assume we have last hours stats by :20 after
sts -= datetime.timedelta(hours=1)
sts = sts.replace(minute=0, second=0, microsecond=0, tzinfo=pytz.utc)
cursor.execute("""
SELECT sum(nbytes)::bigint from ldm_rtstats_hourly
WHERE valid = %s
""", (sts,))
nbytes = cursor.fetchone()[0]
cursor.execute("""
select count(distinct node_host_id)::bigint from
ldm_rtstats_hourly h JOIN ldm_feedtype_paths p on
(h.feedtype_path_id = p.id) WHERE h.valid = %s
""", (sts,))
hosts = cursor.fetchone()[0]
res = dict()
res['generation_time'] = datetime.datetime.utcnow(
).strftime("%Y-%m-%dT%H:%M:%SZ")
res['data'] = [dict(valid=sts.strftime("%Y-%m-%dT%H:%M:00Z"),
nbytes=nbytes,
hosts=hosts)]
return json.dumps(res)
def run(feedtype):
"""Generate geojson for this feedtype"""
pgconn = util.get_dbconn()
cursor = pgconn.cursor()
sts = datetime.datetime.utcnow()
cursor.execute("""
with data as (
select relay_host_id, node_host_id,
avg_latency,
r.entry_added,
rank() OVER
(PARTITION by r.feedtype_path_id ORDER by r.entry_added DESC)
from ldm_rtstats r JOIN ldm_feedtype_paths p
on (r.feedtype_path_id = p.id)
WHERE r.entry_added > now() - '15 minutes'::interval
and p.feedtype_id = get_ldm_feedtype_id(%s)),
agg1 as (
SELECT relay_host_id, node_host_id, avg(avg_latency) as avg_latency,
max(entry_added) as valid
from data WHERE rank = 1 GROUP by relay_host_id, node_host_id),
geos as (
SELECT st_makeline(
(SELECT geom from ldm_hostnames where id = a.relay_host_id),
(SELECT geom from ldm_hostnames where id = a.node_host_id)) as geom,
(SELECT hostname from ldm_hostnames where id = a.relay_host_id) as relay,
(SELECT hostname from ldm_hostnames where id = a.node_host_id) as node,
avg_latency,
valid
from agg1 a)
SELECT ST_asGEoJSON(geom, 2), relay, node, avg_latency,
to_char(valid at time zone 'UTC', 'YYYY-MM-DDThh24:MI:SSZ') from geos
WHERE geom is not null and ST_Length(geom) > 0.1
""", (feedtype, ))
utcnow = datetime.datetime.utcnow()
res = {'type': 'FeatureCollection',
'crs': {'type': 'EPSG',
'properties': {'code': 4326, 'coordinate_order': [1, 0]}},
'features': [],
'generation_time': utcnow.strftime("%Y-%m-%dT%H:%M:%SZ"),
'query_time[secs]': (utcnow - sts).total_seconds(),
'count': cursor.rowcount}
for i, row in enumerate(cursor):
if row[0] is None:
continue
res['features'].append(dict(type="Feature",
id=i,
properties=dict(
latency=row[3],
relay=row[1],
node=row[2],
utc_valid=row[4]
),
geometry=json.loads(row[0])
))
return json.dumps(res)
def handle_weekly(hostname, feedtype, since):
"""Emit JSON for rtstats for this host"""
pgconn = util.get_dbconn()
cursor = pgconn.cursor()
flimit = ''
if feedtype != '':
flimit = " and p.feedtype_id = get_ldm_feedtype_id('%s') " % (
feedtype, )
tlimit = ''
if since is not None:
tlimit = (" and h.valid >= '%s' ") % (
pd.to_datetime(since).strftime("%Y-%m-%d"), )
cursor.execute(
"""
WITH weekly as (
SELECT
extract(year from valid) as yr, extract(week from valid) as week,
feedtype_path_id,
min(min_latency) as min_latency, avg(avg_latency) as avg_latency,
max(max_latency) as max_latency, sum(nprods) as nprods,
sum(nbytes) as nbytes,
max(feedtype_id) as feedtype_id from
ldm_rtstats_daily h JOIN ldm_feedtype_paths p on
(h.feedtype_path_id = p.id) WHERE
p.node_host_id = get_ldm_host_id(%s) """ + flimit + """
""" + tlimit + """ GROUP by yr, week, feedtype_path_id)
select
to_char(
(yr || '-01-01')::date + (week || ' weeks')::interval, 'YYYY-mm-dd')
as v,
h.feedtype_path_id,
(select hostname from ldm_hostnames where id = p.origin_host_id) as origin,
(select hostname from ldm_hostnames where id = p.relay_host_id) as relay,
min_latency,
avg_latency,
max_latency,
nprods::bigint,
nbytes::bigint,
(select feedtype from ldm_feedtypes where id = p.feedtype_id) as feedtype
from weekly h JOIN ldm_feedtype_paths p on
(h.feedtype_path_id = p.id)
ORDER by v ASC
""", (hostname, ))
res = dict()
res['hostname'] = hostname
res['columns'] = [
'valid', 'feedtype_path_id', 'origin', 'relay', 'min_latency',
'avg_latency', 'max_latency', 'nprods', 'nbytes', 'feedtype'
]
res['data'] = []
for row in cursor:
res['data'].append(row)
return json.dumps(res)
def run():
"""Generate geojson for this feedtype"""
pgconn = util.get_dbconn()
cursor = pgconn.cursor()
cursor.execute("""
SELECT feedtype from ldm_feedtypes ORDER by feedtype
""")
res = dict(feedtypes=[])
for row in cursor:
res["feedtypes"].append(row[0])
return json.dumps(res)
def handle_topology(hostname, feedtype):
"""Generate topology for this feedtype"""
if feedtype == "":
return json.dumps("NO_FEEDTYPE_SET_ERROR")
pgconn = util.get_dbconn()
cursor = pgconn.cursor()
# compute all upstreams for this feedtype
upstreams = {}
sts = datetime.datetime.utcnow()
cursor.execute(
"""
WITH active as (
select distinct id from
ldm_feedtype_paths p JOIN ldm_rtstats_hourly r
on (p.id = r.feedtype_path_id)
WHERE p.feedtype_id = get_ldm_feedtype_id(%s)
and r.valid > (now() - '24 hours'::interval))
select distinct
(select hostname from ldm_hostnames where id = p.relay_host_id) as relay,
(select hostname from ldm_hostnames where id = p.node_host_id) as node
from ldm_feedtype_paths p JOIN active a on (p.id = a.id)
""",
(feedtype,),
)
utcnow = datetime.datetime.utcnow()
for row in cursor:
upstreams.setdefault(row[1], []).append(row[0])
if upstreams.get(hostname) is None:
return json.dumps("NO_TOPOLOGY_ERROR")
paths = [[hostname, x] for x in upstreams[hostname]]
# print "inital upstreams are =>", paths
depth = 2
while depth < 10:
newpaths = []
for path in paths:
if len(path) == depth:
for up in upstreams.get(path[-1], []):
newpaths.append(path + [up])
if len(newpaths) == 0:
break
paths = paths + newpaths
# print("===== %s ====" % (depth,))
# for path in paths:
# print(",".join(path))
depth += 1
res = dict()
res["query_time[secs]"] = (utcnow - sts).total_seconds()
res["hostname"] = hostname
res["paths"] = paths
return json.dumps(res)
def run(feedtype):
"""Generate geojson for this feedtype"""
pgconn = util.get_dbconn()
cursor = pgconn.cursor()
sts = datetime.datetime.utcnow()
flimiter = ""
if feedtype is not None:
flimiter = (" WHERE p.feedtype_id = get_ldm_feedtype_id('%s') ") % (
feedtype,
)
cursor.execute(
"""
WITH data as (
SELECT distinct feedtype_path_id, version_id from ldm_rtstats_hourly
WHERE valid > now() - '24 hours'::interval),
agg1 as (
SELECT distinct p.node_host_id, d.version_id from
data d JOIN ldm_feedtype_paths p on (d.feedtype_path_id = p.id)
"""
+ flimiter
+ """)
SELECT ST_asGeoJson(h.geom, 2), h.hostname, v.version
from agg1 a1, ldm_versions v, ldm_hostnames h
WHERE a1.node_host_id = h.id and v.id = a1.version_id
ORDER by hostname ASC
"""
)
utcnow = datetime.datetime.utcnow()
res = {
"type": "FeatureCollection",
"crs": {
"type": "EPSG",
"properties": {"code": 4326, "coordinate_order": [1, 0]},
},
"features": [],
"query_time[secs]": (utcnow - sts).total_seconds(),
"generation_time": utcnow.strftime("%Y-%m-%dT%H:%M:%SZ"),
"count": cursor.rowcount,
}
for row in cursor:
res["features"].append(
dict(
type="Feature",
id=row[1],
properties=dict(hostname=row[1], ldmversion=row[2]),
geometry=(None if row[0] is None else json.loads(row[0])),
)
)
return json.dumps(res)
def handle_rtstats(hostname, feedtype):
"""Emit JSON for rtstats for this host"""
pgconn = util.get_dbconn()
cursor = pgconn.cursor()
flimit = ""
if feedtype != "":
flimit = " and p.feedtype_id = get_ldm_feedtype_id('%s') " % (
feedtype,
)
sts = datetime.datetime.utcnow()
cursor.execute(
"""
select
to_char(r.entry_added at time zone 'UTC', 'YYYY-MM-DDThh24:MI:SSZ'),
r.feedtype_path_id,
(select hostname from ldm_hostnames where id = p.origin_host_id) as origin,
(select hostname from ldm_hostnames where id = p.relay_host_id) as relay,
avg_latency,
(select feedtype from ldm_feedtypes where id = p.feedtype_id) as feedtype
from ldm_rtstats r JOIN ldm_feedtype_paths p on
(r.feedtype_path_id = p.id) WHERE
p.node_host_id = get_ldm_host_id(%s) and
r.entry_added > now() - '36 hours'::interval """
+ flimit
+ """
ORDER by r.entry_added ASC
""",
(hostname,),
)
utcnow = datetime.datetime.utcnow()
res = dict()
res["query_time[secs]"] = (utcnow - sts).total_seconds()
res["hostname"] = hostname
res["columns"] = [
"entry_added",
"feedtype_path_id",
"origin",
"relay",
"avg_latency",
"feedtype",
]
res["data"] = []
for row in cursor:
res["data"].append(row)
return json.dumps(res)
def handle_hourly(hostname, feedtype, since):
"""Emit JSON for rtstats for this host"""
pgconn = util.get_dbconn()
cursor = pgconn.cursor()
flimit = ''
if feedtype != '':
flimit = " and p.feedtype_id = get_ldm_feedtype_id('%s') " % (
feedtype, )
tlimit = ''
if since is not None:
tlimit = (" and h.valid >= '%s' ") % (
pd.to_datetime(since).strftime("%Y-%m-%d %H:%M+00"), )
cursor.execute(
"""
select
to_char(valid at time zone 'UTC', 'YYYY-MM-DDThh24:MI:SSZ'),
h.feedtype_path_id,
(select hostname from ldm_hostnames where id = p.origin_host_id) as origin,
(select hostname from ldm_hostnames where id = p.relay_host_id) as relay,
min_latency,
avg_latency,
max_latency,
nprods,
nbytes,
(select feedtype from ldm_feedtypes where id = p.feedtype_id) as feedtype
from ldm_rtstats_hourly h JOIN ldm_feedtype_paths p on
(h.feedtype_path_id = p.id) WHERE
p.node_host_id = get_ldm_host_id(%s) """ + flimit + """
""" + tlimit + """
ORDER by h.valid ASC
""", (hostname, ))
res = dict()
res['hostname'] = hostname
res['columns'] = [
'valid', 'feedtype_path_id', 'origin', 'relay', 'min_latency',
'avg_latency', 'max_latency', 'nprods', 'nbytes', 'feedtype'
]
res['data'] = []
for row in cursor:
res['data'].append(row)
return json.dumps(res)
def handle_feedtypes(hostname):
"""Generate geojson for this feedtype"""
pgconn = util.get_dbconn()
cursor = pgconn.cursor()
sts = datetime.datetime.utcnow()
cursor.execute(
"""
select distinct f.feedtype from ldm_feedtypes f JOIN ldm_feedtype_paths p
on (f.id = p.feedtype_id) WHERE p.node_host_id = get_ldm_host_id(%s)
ORDER by f.feedtype
""", (hostname, ))
ets = datetime.datetime.utcnow()
res = dict()
res['query_time[secs]'] = (ets - sts).total_seconds()
res['generation_time'] = ets.strftime("%Y-%m-%dT%H:%M:%SZ")
res['hostname'] = hostname
feedtypes = []
for row in cursor:
feedtypes.append(row[0])
res['feedtypes'] = feedtypes
return json.dumps(res)
def handle_weekly(hostname, feedtype, since):
"""Emit JSON for rtstats for this host"""
pgconn = util.get_dbconn()
cursor = pgconn.cursor()
flimit = ""
if feedtype != "":
flimit = " and p.feedtype_id = get_ldm_feedtype_id('%s') " % (
feedtype,
)
tlimit = ""
if since is not None:
tlimit = (" and h.valid >= '%s' ") % (
pd.to_datetime(since).strftime("%Y-%m-%d"),
)
sts = datetime.datetime.utcnow()
cursor.execute(
"""
WITH weekly as (
SELECT
extract(isoyear from valid) as yr, extract(week from valid) as week,
feedtype_path_id,
min(min_latency) as min_latency, avg(avg_latency) as avg_latency,
max(max_latency) as max_latency, sum(nprods) as nprods,
sum(nbytes) as nbytes,
max(feedtype_id) as feedtype_id from
ldm_rtstats_daily h JOIN ldm_feedtype_paths p on
(h.feedtype_path_id = p.id) WHERE
p.node_host_id = get_ldm_host_id(%s) """
+ flimit
+ """
"""
+ tlimit
+ """ GROUP by yr, week, feedtype_path_id)
select
to_char(
(yr || '-01-01')::date + (week || ' weeks')::interval, 'YYYY-mm-dd')
as v,
h.feedtype_path_id,
(select hostname from ldm_hostnames where id = p.origin_host_id) as origin,
(select hostname from ldm_hostnames where id = p.relay_host_id) as relay,
min_latency,
avg_latency,
max_latency,
nprods::bigint,
nbytes::bigint,
(select feedtype from ldm_feedtypes where id = p.feedtype_id) as feedtype
from weekly h JOIN ldm_feedtype_paths p on
(h.feedtype_path_id = p.id)
ORDER by v ASC
""",
(hostname,),
)
utcnow = datetime.datetime.utcnow()
res = dict()
res["query_time[secs]"] = (utcnow - sts).total_seconds()
res["hostname"] = hostname
res["columns"] = [
"valid",
"feedtype_path_id",
"origin",
"relay",
"min_latency",
"avg_latency",
"max_latency",
"nprods",
"nbytes",
"feedtype",
]
res["data"] = []
for row in cursor:
res["data"].append(row)
return json.dumps(res)
def main():
"""Our workflow"""
pgconn = util.get_dbconn(rw=True)
hourly(pgconn)
daily(pgconn)
cleanup(pgconn)
def main():
pgconn = util.get_dbconn(rw=True)
hourly(pgconn)
daily(pgconn)
cleanup(pgconn)
