Skip to content

Instantly share code, notes, and snippets.

@lemmy
Last active December 11, 2019 17:19
Show Gist options
  • Save lemmy/3194798c5b272a2e029b3297ae039997 to your computer and use it in GitHub Desktop.
Save lemmy/3194798c5b272a2e029b3297ae039997 to your computer and use it in GitHub Desktop.
TLC Execution Statistics Ingress into SQL for Analytics (Metabase)
WSGIScriptAlias / /var/www/flask/log_exec_stats.wsgi
<Directory /var/www/flask/>
Options FollowSymLinks
AllowOverride None
Require all granted
</Directory>
--
-- PostgreSQL database dump
--
-- Dumped from database version 11.5 (Debian 11.5-1+deb10u1)
-- Dumped by pg_dump version 11.5 (Debian 11.5-1+deb10u1)
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: execution_description; Type: TABLE; Schema: public; Owner: metabase
--
CREATE TABLE public.execution_description (
id integer NOT NULL,
installation_id integer NOT NULL,
execution_timestamp timestamp without time zone NOT NULL,
toolbox_launched boolean NOT NULL,
queue_type character varying(64),
fp_set_type character varying(64),
off_heap_memory integer NOT NULL,
heap_memory integer NOT NULL,
jvm_architecture character varying(8) NOT NULL,
jvm_version character varying(12) NOT NULL,
jvm_vendor character varying(64) NOT NULL,
os_architecture character varying(8) NOT NULL,
os_version character varying(48) NOT NULL,
os_name character varying(24) NOT NULL,
core_count integer NOT NULL,
worker_count integer NOT NULL,
mode character varying(16) NOT NULL,
version character varying(16) NOT NULL,
ide character varying(255) DEFAULT 'cli'::character varying NOT NULL
);
ALTER TABLE public.execution_description OWNER TO metabase;
--
-- Name: distinctenvironments; Type: VIEW; Schema: public; Owner: metabase
--
CREATE VIEW public.distinctenvironments AS
SELECT DISTINCT execution_description.heap_memory,
execution_description.jvm_vendor,
execution_description.jvm_architecture,
execution_description.jvm_version,
execution_description.os_version,
execution_description.os_name,
execution_description.os_architecture,
execution_description.core_count
FROM public.execution_description;
ALTER TABLE public.distinctenvironments OWNER TO metabase;
--
-- Name: execution_description_id_seq; Type: SEQUENCE; Schema: public; Owner: metabase
--
CREATE SEQUENCE public.execution_description_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.execution_description_id_seq OWNER TO metabase;
--
-- Name: execution_description_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: metabase
--
ALTER SEQUENCE public.execution_description_id_seq OWNED BY public.execution_description.id;
--
-- Name: installation; Type: TABLE; Schema: public; Owner: metabase
--
CREATE TABLE public.installation (
id integer NOT NULL,
unique_id character varying(32) NOT NULL
);
ALTER TABLE public.installation OWNER TO metabase;
--
-- Name: installation_id_seq; Type: SEQUENCE; Schema: public; Owner: metabase
--
CREATE SEQUENCE public.installation_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.installation_id_seq OWNER TO metabase;
--
-- Name: installation_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: metabase
--
ALTER SEQUENCE public.installation_id_seq OWNED BY public.installation.id;
--
-- Name: nonrandominstallationidentifier; Type: VIEW; Schema: public; Owner: metabase
--
CREATE VIEW public.nonrandominstallationidentifier AS
SELECT execution_description.installation_id
FROM public.execution_description
GROUP BY execution_description.installation_id
HAVING (count(*) > 1);
ALTER TABLE public.nonrandominstallationidentifier OWNER TO metabase;
--
-- Name: execution_description id; Type: DEFAULT; Schema: public; Owner: metabase
--
ALTER TABLE ONLY public.execution_description ALTER COLUMN id SET DEFAULT nextval('public.execution_description_id_seq'::regclass);
--
-- Name: installation id; Type: DEFAULT; Schema: public; Owner: metabase
--
ALTER TABLE ONLY public.installation ALTER COLUMN id SET DEFAULT nextval('public.installation_id_seq'::regclass);
--
-- Name: execution_description execution_description_pkey; Type: CONSTRAINT; Schema: public; Owner: metabase
--
ALTER TABLE ONLY public.execution_description
ADD CONSTRAINT execution_description_pkey PRIMARY KEY (id);
--
-- Name: installation installation_pkey; Type: CONSTRAINT; Schema: public; Owner: metabase
--
ALTER TABLE ONLY public.installation
ADD CONSTRAINT installation_pkey PRIMARY KEY (id);
--
-- Name: installation installation_unique_id_key; Type: CONSTRAINT; Schema: public; Owner: metabase
--
ALTER TABLE ONLY public.installation
ADD CONSTRAINT installation_unique_id_key UNIQUE (unique_id);
--
-- Name: execution_description execution_description_installation_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: metabase
--
ALTER TABLE ONLY public.execution_description
ADD CONSTRAINT execution_description_installation_id_fkey FOREIGN KEY (installation_id) REFERENCES public.installation(id);
--
-- PostgreSQL database dump complete
--
apt-get install postgresql postgresql-11 postgresql-common postgresql-client-11 postgresql-client-common
apt-get install psycopg2 libpq-dev python-dev
apt-get install libapache2-mod-wsgi-py3 python-dev
apt-get install python3-flask
apt-get install python3-psycopg2
import datetime
from flask import Flask, request
app = Flask(__name__)
## sql connection
import psycopg2
cnx = psycopg2.connect(host="localhost", database="tlaplus", user="abcdefg", password="1234567890")
@app.route("/") #, methods=['HEAD'])
def head():
## Just a HEAD request or one we want to handle?
if request.args.get('ver') is None:
return "kthxbye"
## Get the request's query part. TLC first sends "ver".
query = "ver=" + request.args.get('ver')
## Convert the query "ver=dev,mode=bfs,osName=..." into
## a dictionary (key/values).
#params = dict(e.split('=') for e in query.split(','))
## Azul VM's string gets split into 'jvmVendor=Azul Systems', ' Inc'.
## We get rid of ' Inc' with the filter.
params = dict(e.split('=') for e in filter(lambda s : '=' in s, query.split(',')))
## Check if the dict contains the pairs we need.
if 'id' not in params:
return "kthxbye2"
#print(params['id'])
##
with cnx.cursor() as cursor:
#cursor.execute("SELECT id FROM installation WHERE unique_id = '{0}';".format(params['id']))
#row = cursor.fetchone()
##print(str(row[0]))
cursor.execute("INSERT INTO installation (unique_id) VALUES ('{0}') ON CONFLICT (unique_id) DO UPDATE SET unique_id = '{0}' RETURNING id".format(params['id']))
row = cursor.fetchone()
if row is not None:
id_to_use = row[0]
timestamp = datetime.datetime.fromtimestamp(float(params['ts'])/1000.0)
toolbox_launched = params['toolbox'] == "true"
queue_type = None
if ('queue' in params) and (len(params['queue']) > 0):
queue_type = params['queue']
fp_set_type = None
if ('fpset' in params) and (len(params['fpset']) > 0):
fp_set_type = params['fpset']
ide = 'cli'
if ('ide' in params) and (len(params['ide']) > 0):
ide = params['ide'][:255]
insrt = ("INSERT INTO execution_description "
"(installation_id, execution_timestamp, toolbox_launched, queue_type, "
"fp_set_type, off_heap_memory, heap_memory, jvm_architecture, jvm_version, "
"jvm_vendor, os_architecture, os_version, os_name, core_count, worker_count, "
"mode, version, ide) "
"VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}')".format(id_to_use, timestamp, toolbox_launched, queue_type, fp_set_type,
int(params['jvmOffHeapMem']), int(params['jvmHeapMem']),
params['jvmArch'], params['jvmVersion'], params['jvmVendor'],
params['osArch'], params['osVersion'], params['osName'],
int(params['cores']), int(params['workers']), params['mode'][:16], params['ver'][:16], ide))
#print(insrt)
cursor.execute(insrt)
cnx.commit()
#cursor.close()
#cnx.close()
return "Thank You!"
if __name__ == "__main__":
app.run()
#! /usr/bin/python3
import logging
import sys
logging.basicConfig(stream=sys.stderr)
sys.path.insert(0, '/var/www/flask/')
from log_exec_stats import app as application
application.secret_key = 'cqfralkijczn8947z5'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment