def get_node_rating_monthly(tenant_id: AnyStr,
node: AnyStr,
namespaces: List[AnyStr]) -> List[Dict]:
"""
Get the monthly rating for a given node.
:tenant_id (AnyStr) A string representing the tenant, only present for compatibility.
:node (AnyStr) A string representing the node.
:namespaces (List[AnyStr]) A list of namespaces accessible by the tenant.
Return the results of the query as a list of dictionary.
"""
qry = sa.text("""
SELECT frame_begin,
sum(frame_price) as frame_price
FROM frames
WHERE frame_begin >= date_trunc('month', now())
AND node = :node
AND namespace != 'unspecified'
AND pod != 'unspecified'
AND namespace IN :namespaces
GROUP BY frame_begin, node
ORDER BY frame_begin, node
""").bindparams(bindparam('namespaces', expanding=True))
params = {
'tenant_id': tenant_id,
'namespaces': namespaces,
'node': node
}
return process_query(qry, params)
def get_metric_to_date(metric: AnyStr, tenant_id: AnyStr,
namespaces: List[AnyStr]) -> List[Dict]:
"""
Get the rating of a metric, from start of the month to now.
:metric (AnyStr) A string representing the metric.
:tenant_id (AnyStr) A string representing the tenant, only used by decorators.
:namespaces (List[AnyStr]) A list of namespaces accessible by the tenant.
Return the results of the query as a list of dictionary.
"""
qry = sa.text("""
SELECT max(frame_price) *
(SELECT
(EXTRACT(EPOCH from now()) -
EXTRACT(EPOCH from date_trunc('month', now()))) / 3600)
AS frame_price
FROM frames
WHERE frame_begin >= date_trunc('month', now())
AND metric = :metric
AND namespace IN :namespaces
""").bindparams(bindparam('namespaces', expanding=True))
params = {
'tenant_id': tenant_id,
'metric': metric,
'namespaces': namespaces
}
return process_query(qry, params)
def get_pod_lifetime(pod: AnyStr,
tenant_id: AnyStr,
namespaces: List[AnyStr]) -> List[Dict]:
"""
Get the lifetime for a pod.
:pod (AnyStr) A string representing the pod.
:tenant_id (AnyStr) A string representing the tenant, only used by decorators.
:namespaces (List[AnyStr]) A list of namespaces accessible by the tenant.
Return the results of the query as a list of dictionary.
"""
qry = sa.text("""
SELECT min(frame_begin) as start,
max(frame_end) as end
FROM frames
WHERE pod = :pod
AND namespace IN :namespaces
""").bindparams(bindparam('namespaces', expanding=True))
params = {
'pod': pod,
'tenant_id': tenant_id,
'namespaces': namespaces
}
return process_query(qry, params)
def get_namespaces_rating_monthly(tenant_id: AnyStr,
namespaces: List[AnyStr]) -> List[Dict]:
"""
Get the monthly rating, by namespaces.
:tenant_id (AnyStr) A string representing the tenant, only used by decorators.
:namespaces (List[AnyStr]) A list of namespaces accessible by the tenant.
Return the results of the query as a list of dictionary.
"""
qry = sa.text("""
SELECT frame_begin,
sum(frame_price) as frame_price,
namespace
FROM frames
WHERE frame_begin >= date_trunc('month', now())
AND namespace != 'unspecified'
AND pod != 'unspecified'
AND namespace IN :namespaces
GROUP BY frame_begin, namespace
ORDER BY frame_begin, frame_price DESC
""").bindparams(bindparam('namespaces', expanding=True))
params = {'tenant_id': tenant_id, 'namespaces': namespaces}
return process_query(qry, params)
def get_namespaces_total_rating(start: AnyStr, end: AnyStr, tenant_id: AnyStr,
namespaces: List[AnyStr]) -> List[Dict]:
"""
Get the agglomerated rating, by namespace.
:start (AnyStr) A timestamp, as a string, to represent the starting time.
:end (AnyStr) A timestamp, as a string, to represent the ending time.
:tenant_id (AnyStr) A string representing the tenant, only used by decorators.
:namespaces (List[AnyStr]) A list of namespaces accessible by the tenant.
Return the results of the query as a list of dictionary.
"""
qry = sa.text("""
SELECT sum(frame_price) as frame_price,
namespace
FROM frames
WHERE frame_begin >= :start
AND frame_end <= :end
AND namespace IN :namespaces
GROUP BY namespace
ORDER BY namespace
""").bindparams(bindparam('namespaces', expanding=True))
params = {
'start': start,
'end': end,
'tenant_id': tenant_id,
'namespaces': namespaces
}
return process_query(qry, params)
def get_pod_namespace(pod: AnyStr,
start: AnyStr,
end: AnyStr,
tenant_id: AnyStr,
namespaces: List[AnyStr]) -> List[Dict]:
"""
Get the namespaces for a pod.
:pod (AnyStr) A string representing the pod.
:start (AnyStr) A timestamp, as a string, to represent the starting time.
:end (AnyStr) A timestamp, as a string, to represent the ending time.
:tenant_id (AnyStr) A string representing the tenant, only used by decorators.
:namespaces (List[AnyStr]) A list of namespaces accessible by the tenant.
Return the results of the query as a list of dictionary.
"""
qry = sa.text("""
SELECT pod,
namespace
FROM frames
WHERE pod = :pod
AND frame_begin >= :start
AND frame_end <= :end
AND namespace IN :namespaces
GROUP BY pod, namespace
""").bindparams(bindparam('namespaces', expanding=True))
params = {
'pod': pod,
'start': start,
'end': end,
'tenant_id': tenant_id,
'namespaces': namespaces
}
return process_query(qry, params)
def get_pod_rating_weekly(tenant_id: AnyStr,
pod: AnyStr,
namespaces: List[AnyStr]) -> List[Dict]:
"""
Get the weekly pods rating.
:tenant_id (AnyStr) A string representing the tenant, only present for compatibility.
:pod (AnyStr) A string representing the pod.
:namespaces (List[AnyStr]) A list of namespaces accessible by the tenant.
Return the results of the query as a list of dictionary.
"""
qry = sa.text("""
SELECT frame_begin,
sum(frame_price) as frame_price
FROM frames
WHERE frame_begin >= date_trunc('week', now())
AND pod = :pod
AND namespace != 'unspecified'
AND pod != 'unspecified'
AND namespace IN :namespaces
GROUP BY frame_begin, pod
ORDER BY frame_begin, pod
""").bindparams(bindparam('namespaces', expanding=True))
params = {
'tenant_id': tenant_id,
'namespaces': namespaces,
'pod': pod
}
return process_query(qry, params)
def get_metric_monthly_rating(metric: AnyStr, tenant_id: AnyStr,
namespaces: List[AnyStr]) -> List[Dict]:
"""
Get the monthly price for a metric.
:metric (AnyStr) A string representing the metric.
:tenant_id (AnyStr) A string representing the tenant, only used by decorators.
:namespaces (List[AnyStr]) A list of namespaces accessible by the tenant.
Return the results of the query as a list of dictionary.
"""
qry = sa.text("""
SELECT max(frame_price) * 24 *
(SELECT extract(days FROM
date_trunc('month', now()) + interval '1 month - 1 day'))
AS frame_price
FROM frames
WHERE metric = :metric
AND frame_begin >= date_trunc('month', now())
AND namespace IN :namespaces
""").bindparams(bindparam('namespaces', expanding=True))
params = {
'metric': metric,
'tenant_id': tenant_id,
'namespaces': namespaces
}
return process_query(qry, params)
def get_metric_ratio(metric: AnyStr, start: AnyStr, end: AnyStr,
tenant_id: AnyStr,
namespaces: List[AnyStr]) -> List[Dict]:
"""
Get the ratio between nodes and price for the given metric.
:metric (AnyStr) A string representing the metric.
:start (AnyStr) A timestamp, as a string, to represent the starting time.
:end (AnyStr) A timestamp, as a string, to represent the ending time.
:tenant_id (AnyStr) A string representing the tenant, only used by decorators.
:namespaces (List[AnyStr]) A list of namespaces accessible by the tenant.
Return the results of the query as a list of dictionary.
"""
qry = sa.text("""
SELECT frame_begin,
sum(frame_price) / count(node) as ratio,
metric
FROM frames
WHERE metric = :metric
AND frame_begin >= :start
AND frame_end <= :end
AND namespace IN :namespaces
GROUP BY frame_begin, metric
ORDER BY frame_begin
""").bindparams(bindparam('namespaces', expanding=True))
params = {
'metric': metric,
'start': start,
'end': end,
'tenant_id': tenant_id,
'namespaces': namespaces
}
return process_query(qry, params)
def update_rated_namespaces(namespaces: List[AnyStr],
last_insert: AnyStr) -> int:
"""
Update the namespace_status table with latest insert time.
:namespaces (List[AnyStr]) A list of namespaces
:last_insert (AnyStr) The timestamp of the latest data frame rating
Return the number of updated namespaces
"""
for namespace in namespaces:
qry = sa.text("""
INSERT INTO namespace_status (namespace, last_update)
VALUES (:namespace, :last_update)
ON CONFLICT ON CONSTRAINT namespace_status_pkey
DO UPDATE SET last_update = EXCLUDED.last_update
""")
params = {'namespace': namespace, 'last_update': last_insert}
utils.process_query(qry, params)
return len(namespaces)
def get_tenants() -> List[Dict]:
"""
Get tenants from namespaces table.
Return a list of dictionary containing the results.
"""
qry = sa.text("""
SELECT tenant_id
FROM namespaces
GROUP BY tenant_id
ORDER BY tenant_id
""")
return process_query(qry, {})
def get_rated_frames_oldest(tenant_id: AnyStr) -> List[Dict]:
"""
Get the oldest rated frame timestamp.
:tenant_id (AnyStr) The tenant name
Returns the results of the query
"""
qry = sa.text("""
SELECT MIN(frame_end)
FROM frames
""")
return utils.process_query(qry, {})
def get_tenant(tenant: AnyStr) -> List[Dict]:
"""
Get a tenant from the namespace table.
:tenant (AnyStr) A name representing the tenant to get
Return a dictionary with the table results
"""
qry = text("""
SELECT *
FROM namespaces
WHERE tenant_id = :tenant
""")
return utils.process_query(qry, {'tenant': tenant})
def get_group_tenant(tenant: AnyStr) -> List[Dict]:
"""
Get the group of the tenant.
:tenant (AnyStr) A string representing the name of the tenant
Return the user group in the group_tenant table.
"""
qry = text("""
SELECT *
FROM group_tenant
WHERE tenant_id = :tenant
""")
return utils.process_query(qry, {'tenant': tenant})
def get_tenant_namespace(tenant: AnyStr) -> List[Dict]:
"""
Get the namespace of a tenant in the namespaces table.
:tenant (AnyStr) A name reprensenting the tenant.
Return a list of dictionary containing the results.
"""
qry = sa.text("""
SELECT namespace
FROM namespaces
WHERE tenant_id = :tenant
ORDER BY namespace
""")
params = {'tenant': tenant}
return process_query(qry, params)
def get_metric_report(metric: AnyStr, tenant_id: AnyStr) -> List[Dict]:
"""
Get the report associated with a metric.
:metric (AnyStr) A name representing the metric.
:tenant_id (AnyStr) A name representing the tenant.
Return the results of the query as a list of dictionary.
"""
qry = sa.text("""
SELECT report_name
FROM frame_status
WHERE metric = :metric
""")
params = {'metric': metric, 'tenant_id': tenant_id}
return process_query(qry, params)
def get_last_rated_date(metric: AnyStr, tenant_id: AnyStr) -> List[Dict]:
"""
Get the latest rating timestamp for a metric.
:metric (AnyStr) A name representing the metric.
:tenant_id (AnyStr) A name representing the tenant.
Return the results of the query as a list of dictionary.
"""
qry = sa.text("""
SELECT last_insert
FROM frame_status
WHERE metric = :metric
""")
params = {'metric': metric, 'tenant_id': tenant_id}
return process_query(qry, params)
def get_report_metric(report: AnyStr, tenant_id: AnyStr) -> List[Dict]:
"""
Get the metric of a report.
:report (AnyStr) A name representing the report.
:tenant_id (AnyStr) A name representing the tenant.
Return the results of the query as a list of dictionary.
"""
qry = sa.text("""
SELECT metric
FROM frame_status
WHERE report_name = :report
""")
params = {'report': report}
return process_query(qry, params)
def get_last_rated_reports(report: AnyStr, tenant_id: AnyStr) -> List[Dict]:
"""
Get the time of the last update of a given report.
:report (AnyStr) A name representing the report.
:tenant_id (AnyStr) A name representing the tenant.
Return the results of the query as a list of dictionary.
"""
qry = sa.text("""
SELECT last_insert
FROM frame_status
WHERE report_name = :report
""")
params = {'report': report}
return process_query(qry, params)
def get_namespaces(tenant_id: AnyStr, namespaces: List[AnyStr]) -> List[Dict]:
"""
Get namespaces.
:tenant_id (AnyStr) A string representing the tenant, only used by decorators.
:namespaces (List[AnyStr]) A list of namespaces accessible by the tenant.
Return the results of the query as a list of dictionary.
"""
qry = sa.text("""
SELECT namespace, tenant_id
FROM namespaces
WHERE namespace IN :namespaces
ORDER BY namespace
""").bindparams(bindparam('namespaces', expanding=True))
params = {'tenant_id': tenant_id, 'namespaces': namespaces}
return process_query(qry, params)
def get_node_metric_rating(node: AnyStr,
metric: AnyStr,
start: AnyStr,
end: AnyStr,
tenant_id: AnyStr,
namespaces: List[AnyStr]) -> List[Dict]:
"""
Get the complete rating for a given node and metric.
:node (AnyStr) A string representing the node.
:metric (AnyStr) A string representing the metric.
:start (AnyStr) A timestamp, as a string, to represent the starting time.
:end (AnyStr) A timestamp, as a string, to represent the ending time.
:tenant_id (AnyStr) A string representing the tenant, only used by decorators.
:namespaces (List[AnyStr]) A list of namespaces accessible by the tenant.
Return the results of the query as a list of dictionary.
"""
qry = sa.text("""
SELECT frame_begin,
frame_end,
metric,
frame_price,
namespace,
pod
FROM frames
WHERE node = :node
and metric = :metric
AND frame_begin >= :start
AND frame_end <= :end
AND namespace IN :namespaces
ORDER BY frame_begin, namespace, pod, metric
""").bindparams(bindparam('namespaces', expanding=True))
params = {
'node': node,
'metric': metric,
'start': start,
'end': end,
'tenant_id': tenant_id,
'namespaces': namespaces
}
return process_query(qry, params)
def get_nodes(tenant_id: AnyStr, namespaces: List[AnyStr]) -> List[Dict]:
"""
Get the nodes.
:tenant_id (AnyStr) A string representing the tenant, only present for compatibility.
:namespaces (List[AnyStr]) A list of namespaces accessible by the tenant.
Return the results of the query as a list of dictionary.
"""
qry = sa.text("""
SELECT node
FROM frames
WHERE namespace IN :namespaces
GROUP BY node
ORDER BY node
""").bindparams(bindparam('namespaces', expanding=True))
params = {
'tenant_id': tenant_id,
'namespaces': namespaces
}
return process_query(qry, params)
def get_pod_metric_total_rating(pod: AnyStr,
metric: AnyStr,
start: AnyStr,
end: AnyStr,
tenant_id: AnyStr,
namespaces: List[AnyStr]) -> List[Dict]:
"""
Get the agglomerated rating for a given pod and metric, by namespace and node.
:pod (AnyStr) A string representing the pod.
:start (AnyStr) A timestamp, as a string, to represent the starting time.
:end (AnyStr) A timestamp, as a string, to represent the ending time.
:tenant_id (AnyStr) A string representing the tenant, only used by decorators.
:namespaces (List[AnyStr]) A list of namespaces accessible by the tenant.
Return the results of the query as a list of dictionary.
"""
qry = sa.text("""
SELECT sum(frame_price) as frame_price,
pod
FROM frames
WHERE pod = :pod
AND metric = :metric
AND frame_begin >= :start
AND frame_end <= :end
AND namespace IN :namespaces
GROUP BY pod
""").bindparams(bindparam('namespaces', expanding=True))
params = {
'pod': pod,
'metric': metric,
'start': start,
'end': end,
'tenant_id': tenant_id,
'namespaces': namespaces
}
return process_query(qry, params)
def get_pods_rating(start: AnyStr,
end: AnyStr,
tenant_id: AnyStr,
namespaces: List[AnyStr]) -> List[Dict]:
"""
Get the rating by pods.
:start (AnyStr) A timestamp, as a string, to represent the starting time.
:end (AnyStr) A timestamp, as a string, to represent the ending time.
:tenant_id (AnyStr) A string representing the tenant, only used by decorators.
:namespaces (List[AnyStr]) A list of namespaces accessible by the tenant.
Return the results of the query as a list of dictionary.
"""
qry = sa.text("""
SELECT frame_begin,
frame_end,
frame_price,
metric,
namespace,
node,
pod
FROM frames
WHERE frame_begin >= :start
AND frame_end <= :end
AND namespace != 'unspecified'
AND pod != 'unspecified'
AND namespace IN :namespaces
ORDER BY frame_begin, metric
""").bindparams(bindparam('namespaces', expanding=True))
params = {
'start': start,
'end': end,
'tenant_id': tenant_id,
'namespaces': namespaces
}
return process_query(qry, params)
