from bigquery_analytics import BigQueryAnalytics import datetime import re import os import sys import json import traceback import urllib2 if os.path.exists("/home/smbaker/projects/vicci/plstackapi/planetstack"): sys.path.append("/home/smbaker/projects/vicci/plstackapi/planetstack") else: sys.path.append("/opt/planetstack") os.environ.setdefault("DJANGO_SETTINGS_MODULE", "planetstack.settings") from django import db from django.db import connection from core.models import Slice, Sliver, ServiceClass, Reservation, Tag, Network, User, Node, Image, Deployment, Site, NetworkTemplate, NetworkSlice, Service BLUE_LOAD=5000000 RED_LOAD=15000000 class PlanetStackAnalytics(BigQueryAnalytics): def __init__(self, tableName="demoevents"): BigQueryAnalytics.__init__(self, tableName) def service_to_sliceNames(self, serviceName): service=Service.objects.get(name=serviceName) try: slices = service.slices.all() except: # BUG in data model -- Slice.service has related name 'service' and # it should be 'slices' slices = service.service.all() return [slice.name for slice in slices] def compose_query(self, slice=None, site=None, node=None, service=None, timeBucket="60", avg=[], sum=[], count=[], computed=[], groupBy=["Time"], orderBy=["Time"], tableName="demoevents"): tablePart = "%s.%s@-3600000--1" % ("vicci", tableName) fields = [] fieldNames = [] fields.append("SEC_TO_TIMESTAMP(INTEGER(TIMESTAMP_TO_SEC(time)/%s)*%s) as Time" % (str(timeBucket),str(timeBucket))) #fields.append("INTEGER(TIMESTAMP_TO_SEC(time)/%s)*%s as Time" % (str(timeBucket),str(timeBucket))) for fieldName in avg: fields.append("AVG(%s) as avg_%s" % (fieldName, fieldName.replace("%",""))) fieldNames.append("avg_%s" % fieldName.replace("%","")) for fieldName in sum: fields.append("SUM(%s) as sum_%s" % (fieldName, fieldName.replace("%",""))) fieldNames.append("sum_%s" % fieldName.replace("%","")) for fieldName in count: fields.append("COUNT(distinct %s) as count_%s" % (fieldName, fieldName.replace("%",""))) fieldNames.append("count_%s" % fieldName.replace("%","")) for fieldName in computed: operator = "/" parts = fieldName.split("/") computedFieldName = "computed_" + parts[0].replace("%","")+"_div_"+parts[1].replace("%","") if len(parts)==1: operator = "*" parts = computed.split("*") computedFieldName = "computed_" + parts[0].replace("%","")+"_mult_"+parts[1].replace("%","") fields.append("SUM(%s)%sSUM(%s) as %s" % (parts[0], operator, parts[1], computedFieldName)) fieldNames.append(computedFieldName) for fieldName in groupBy: if (fieldName not in ["Time"]): fields.append(fieldName) fieldNames.append(fieldName) fields = ", ".join(fields) where = [] if slice: where.append("%%slice='%s'" % slice) if site: where.append("%%site='%s'" % site) if node: where.append("%%hostname='%s'" % node) if service: sliceNames = self.service_to_sliceNames(service) if sliceNames: where.append("(" + " OR ".join(["%%slice='%s'" % sliceName for sliceName in sliceNames]) +")") if where: where = " WHERE " + " AND ".join(where) else: where ="" if groupBy: groupBySub = " GROUP BY " + ",".join(groupBy + ["%hostname"]) groupBy = " GROUP BY " + ",".join(groupBy) else: groupBySub = " GROUP BY %hostname" groupBy = "" if orderBy: orderBy = " ORDER BY " + ",".join(orderBy) else: orderBy = "" if computed: subQuery = "SELECT %%hostname, %s FROM [%s]" % (fields, tablePart) if where: subQuery = subQuery + where subQuery = subQuery + groupBySub #subQuery = subQuery + " GROUP BY %s,%%hostname" % timeField sumFields = [] for fieldName in fieldNames: if fieldName.startswith("avg"): sumFields.append("AVG(%s) as avg_%s"%(fieldName,fieldName)) sumFields.append("MAX(%s) as max_%s"%(fieldName,fieldName)) elif (fieldName.startswith("count")) or (fieldName.startswith("sum")) or (fieldName.startswith("computed")): sumFields.append("SUM(%s) as sum_%s"%(fieldName,fieldName)) else: sumFields.append(fieldName) sumFields = ",".join(sumFields) query = "SELECT %s, %s FROM (%s)" % ("Time", sumFields, subQuery) if groupBy: query = query + groupBy if orderBy: query = query + orderBy else: query = "SELECT %s FROM [%s]" % (fields, tablePart) if where: query = query + " " + where if groupBy: query = query + groupBy if orderBy: query = query + orderBy return query def get_list_from_req(self, req, name, default=[]): value = req.GET.get(name, None) if not value: return default value=value.replace("@","%") return value.split(",") def format_result(self, format, result, query, dataSourceUrl): if (format == "json_dicts"): result = {"query": query, "rows": result, "dataSourceUrl": dataSourceUrl} return ("application/javascript", json.dumps(result)) elif (format == "json_arrays"): new_result = [] for row in result: new_row = [] for key in sorted(row.keys()): new_row.append(row[key]) new_result.append(new_row) new_result = {"query": query, "rows": new_result} return ("application/javascript", json.dumps(new_result)) elif (format == "html_table"): new_rows = [] for row in result: new_row = [] for key in sorted(row.keys()): new_row.append("