def migrate(): updates = [] cur = db.cursor() cur.execute("ALTER TABLE raw_data ADD (ts DOUBLE, remote_addr VARCHAR(15))") cur.execute("SELECT id, ts, remote_addr, data FROM raw_data") for r in cur.fetchall(): jsontxt = r["data"] jsonobj = json.loads(jsontxt) objid = r["id"] ts = r["ts"] if ts is None: ts = jsonobj["ts"] remote_addr = r["remote_addr"] if remote_addr is None: remote_addr = jsonobj["remote_addr"] updates.append([ts, remote_addr, objid]) cur.executemany("UPDATE raw_data SET ts=%s, remote_addr=%s WHERE id=%s", updates) db.commit() cur.close()
def migrate(): # Fetch all the download data cur = db.cursor() cur.execute("SELECT * FROM pegasus_download") for r in cur.fetchall(): # Convert it into a download metric d = { "type": "download", "client": "pegasus-download", "version": "0.1", "filename": r["filename"], "ts": r["timestamp"], "remote_addr": None, "name": r["name"], "email": r["email"], "organization": r["organization"], "sub_announce": bool(r["announce"]), "sub_users": bool(r["users"]), "app_domain": r["domain"], "app_description": r["application"], "oldfeatures": r["oldfeatures"], "newfeatures": r["newfeatures"], "howheard": r["howheard"], "howhelp": r["howhelp"] } print r["did"],r["filename"] # Post it to the metrics server resp = requests.post(url, data=json.dumps(d), headers={'content-type': 'application/json'}) if resp.status_code != 202: print "ERROR:", resp.text break cur.close()
def migrate(): cur = db.cursor() cur.execute("alter table planner_metrics add dax_api VARCHAR(15)") db.commit() cur.close()
def migrate(conn): cur = db.cursor() cur.execute("ALTER TABLE planner_metrics ADD (application VARCHAR(256))") db.commit() cur.close()
def migrate(): cur = db.cursor() cur.execute("drop table if exists locations") cur.execute(""" create table locations ( ip VARCHAR(15) NOT NULL, country_code VARCHAR(256), country_name VARCHAR(256), region_code VARCHAR(256), region_name VARCHAR(256), city VARCHAR(256), zip_code VARCHAR(256), latitude DOUBLE, longitude DOUBLE, metro_code VARCHAR(256), PRIMARY KEY (ip) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; """) cur.execute("SELECT distinct(remote_addr) FROM raw_data WHERE remote_addr is not NULL ORDER BY ts desc") locations = [] for ip in cur: addr = ip['remote_addr'] location = None try: r = requests.get('http://gaul.isi.edu:8192/json/%s' % addr) if 200 <= r.status_code < 300: r.encoding = 'utf-8' location = json.loads(r.text) else: log.error("Error getting location for %s: Status %s" % (addr, r.status_code)) exit(1) except Exception, e: log.error("Error getting location for %s" % addr) log.exception(e) exit(1) for key in location: if type(location[key]) == unicode: location[key] = location[key].encode('utf-8') if 'latitude' not in location or \ 'longitude' not in location or \ 'country_code' not in location or \ 'region_code' not in location or \ 'region_name' not in location or \ 'city' not in location: log.error("Location for %s missing something: %s" % (addr, location)) exit(1) if 'zip_code' not in location: location['zip_code'] = None if 'metro_code' not in location: location['metro_code'] = None locations.append(location)
def migrate(): cur = db.cursor() cur.execute("alter table planner_metrics add uses_pmc BOOLEAN") cur.execute("alter table planner_metrics add planner_args TEXT") cur.execute("alter table planner_metrics add deleted_tasks INTEGER UNSIGNED") db.commit(); cur.close();
def migrate(): cur = db.cursor() cur.execute("alter table planner_metrics add dax_input_files int unsigned") cur.execute("alter table planner_metrics add dax_inter_files int unsigned") cur.execute("alter table planner_metrics add dax_output_files int unsigned") cur.execute("alter table planner_metrics add dax_total_files int unsigned") db.commit(); cur.close();
def migrate(): cur = db.cursor() cur.execute("alter table planner_metrics modify version VARCHAR(32)") cur.execute("alter table downloads modify version VARCHAR(32)") cur.execute("alter table dagman_metrics modify version VARCHAR(32)") cur.execute("alter table dagman_metrics modify planner_version VARCHAR(32)") db.commit() cur.close()
def migrate(): cur = db.cursor() def drop_index(table, idx): cur.execute("SHOW INDEX FROM %s WHERE KEY_NAME='%s'" % (table, idx)) if cur.fetchone(): cur.execute("DROP INDEX %s ON %s" % (idx, table)) drop_index("planner_metrics", "idx_planner_metrics_root_wf_uuid") cur.execute("create index idx_planner_metrics_root_wf_uuid on planner_metrics(root_wf_uuid)") drop_index("planner_metrics", "idx_planner_metrics_ts") cur.execute("create index idx_planner_metrics_ts on planner_metrics(ts)") db.commit() cur.close()
def test_top_domains(self): # load the mock data with open(relfile('addRawData.json'), 'r') as rawDataFile: rawData = ast.literal_eval(rawDataFile.read()) for dataPoint in rawData: self.app.post('/metrics', data=dataPoint['data'], headers={'Content-Type' : 'application/json'}) with db.cursor() as cur: rv = self.app.get('/planner/topdomains?start_time=1355952000&end_time=1425168000', headers={'X-Requested-With' : 'XMLHttpRequest'}) domainData = ast.literal_eval(rv.data) assert domainData['iTotalRecords'] == 1 record = domainData['aaData'][0] assert record['0'] == 'isi.edu' # Domain assert record['1'] == '95' # Workflows assert record['2'] == '295,491' # Tasks assert record['3'] == '36,375' # Jobs
def setUp(self): app.config["DBHOST"] = 'localhost' app.config["DBPORT"] = 3306 app.config["DBUSER"] = '******' app.config["DBPASS"] = '******' app.config['DBNAME'] = 'test_metrics' app.config['TESTING'] = True self.app = app.test_client() db.connect() with db.cursor() as cur: create_db = "create database if not exists %s " % (app.config["DBNAME"]) cur.execute(create_db) select_db = "use %s " % (app.config['DBNAME']) cur.execute(select_db) with open(relfile('../schema.sql'), 'r') as schemaFile: schema = schemaFile.read().split(';') for command in schema: if command: cur.execute(command)
def migrate(): cur = db.cursor() cur.execute(""" create table dagman_metrics ( id INTEGER UNSIGNED NOT NULL, ts DOUBLE, remote_addr VARCHAR(15), hostname VARCHAR(256), domain VARCHAR(256), version VARCHAR(32), wf_uuid VARCHAR(36), root_wf_uuid VARCHAR(36), start_time DOUBLE, end_time DOUBLE, duration FLOAT, exitcode SMALLINT, dagman_id VARCHAR(32), parent_dagman_id VARCHAR(32), jobs INTEGER, jobs_failed INTEGER, jobs_succeeded INTEGER, dag_jobs INTEGER, dag_jobs_failed INTEGER, dag_jobs_succeeded INTEGER, dag_status INTEGER, planner VARCHAR(1024), planner_version VARCHAR(32), rescue_dag_number INTEGER, total_job_time DOUBLE, total_jobs INTEGER, total_jobs_run INTEGER, PRIMARY KEY (id), FOREIGN KEY (id) REFERENCES raw_data(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; """) db.commit() cur.close()
def test_add_planner_metric(self): with open(relfile('metrics.json'), 'r') as rawMetric: data = rawMetric.read() rv = self.app.post('/metrics', data=data, headers={'Content-Type' : 'application/json'}) assert rv.data == '' with db.cursor() as cur: # The test data should be added to only the raw_data, planner_metrics, and locations tables cur.execute('select count(*) from raw_data') rawDataCount = int(cur.fetchone()['count(*)']) assert rawDataCount == 1 cur.execute('select * from planner_metrics') plans = cur.fetchall() assert len(plans) == 1 maxTimeError = 0.00001 plan = plans[0] assert plan['id'] == 1 assert plan['inter_tx_jobs'] == 0 assert plan['domain'] == 'socal.res.rr.com' assert plan['data_config'] == 'nonsharedfs' assert plan['duration'] == 1.425 assert plan['clustered_jobs'] == 0 assert plan['total_jobs'] == 9 assert plan['remote_addr'] == '104.32.177.85' assert plan['compute_jobs'] == 4 assert plan['hostname'] == 'cpe-104-32-177-85.socal.res.rr.com' assert plan['dag_tasks'] == 0 assert plan['cleanup_jobs'] == 0 assert plan['application'] is None assert plan['version'] == '4.2.0cvs' assert plan['create_dir_jobs'] == 1 assert plan['so_tx_jobs'] == 0 assert plan['dax_tasks'] == 0 assert plan['chmod_jobs'] == 0 assert abs(plan['start_time'] - 1356053279.2479999) < maxTimeError assert plan['dag_jobs'] == 0 assert plan['compute_tasks'] == 4 assert plan['dax_jobs'] == 0 assert plan['si_tx_jobs'] == 4 assert plan['total_tasks'] == 4 assert plan['wf_uuid'] == '620fe96a-048d-47e8-9000-e8fc347db65e' assert plan['root_wf_uuid'] == 'e8a888c1-0e49-4c24-afb1-72d7d5c0ff22' assert abs(plan['end_time'] - 1356053280.673) < maxTimeError assert plan['reg_jobs'] == 0 assert plan['exitcode'] == 0 # Check that the location for the plan was added and that the values are match up with # the proper response at the time of writing this (2/2015) # If there is an error here it could mean there's a problem calling the service that # looks up the location cur.execute('select * from locations') locations = cur.fetchall() assert len(locations) == 1 maxLatNonError = 0.00001 location = locations[0] assert location['ip'] == '104.32.177.85' assert location['country_code'] == 'US' assert location['country_name'] == 'United States' assert location['region_code'] == 'CA' assert location['region_name'] == 'California' assert location['city'] == 'Los Angeles' assert location['zip_code'] == '90007' assert abs(location['latitude'] - 34.027) < maxLatNonError assert abs(location['longitude'] + 118.284) < maxLatNonError assert location['metro_code'] == '803' cur.execute('select count(*) from downloads') dlCount = int(cur.fetchone()['count(*)']) assert dlCount == 0 cur.execute('select count(*) from dagman_metrics') dagmanCount = int(cur.fetchone()['count(*)']) assert dagmanCount == 0 cur.execute('select count(*) from invalid_data') invalidCount = int(cur.fetchone()['count(*)']) assert invalidCount == 0 cur.execute('select count(*) from planner_errors') errorCount = int(cur.fetchone()['count(*)']) assert errorCount == 0
def tearDown(self): with db.cursor() as cur: drop_db = "drop database if exists %s" % (app.config["DBNAME"]) cur.execute(drop_db) db.close()