def runsql(self):
session = db.session()
limit = 1000
data = json.loads(request.form.get('data'))
sql = data.get('sql')
database_id = data.get('database_id')
mydb = session.query(models.Database).filter_by(id=database_id).first()
content = ""
if mydb:
eng = mydb.get_sqla_engine()
if limit:
sql = sql.strip().strip(';')
qry = (
select('*')
.select_from(TextAsFrom(text(sql), ['*']).alias('inner_qry'))
.limit(limit)
)
sql= str(qry.compile(eng, compile_kwargs={"literal_binds": True}))
try:
df = pd.read_sql_query(sql=sql, con=eng)
content = df.to_html(
index=False,
na_rep='',
classes=(
"dataframe table table-striped table-bordered "
"table-condensed sql_results"))
except Exception as e:
content = (
'<div class="alert alert-danger">'
"{}</div>"
).format(e.message)
session.commit()
return content
def runsql(self):
session = db.session()
limit = 1000
data = json.loads(request.form.get('data'))
sql = data.get('sql')
database_id = data.get('database_id')
mydb = session.query(models.Database).filter_by(id=database_id).first()
content = ""
if mydb:
eng = mydb.get_sqla_engine()
if limit:
sql = sql.strip().strip(';')
qry = (
select('*')
.select_from(TextAsFrom(text(sql), ['*']).alias('inner_qry'))
.limit(limit)
)
sql= str(qry.compile(eng, compile_kwargs={"literal_binds": True}))
try:
df = pd.read_sql_query(sql=sql, con=eng)
content = df.to_html(
index=False,
na_rep='',
classes=(
"dataframe table table-striped table-bordered "
"table-condensed sql_results"))
except Exception as e:
content = (
'<div class="alert alert-danger">'
"{}</div>"
).format(e.message)
session.commit()
return content
def dashboard(self, dashboard_id):
session = db.session()
qry = session.query(models.Dashboard)
if dashboard_id.isdigit():
qry = qry.filter_by(id=int(dashboard_id))
else:
qry = qry.filter_by(slug=dashboard_id)
templates = session.query(models.CssTemplate).all()
dash = qry.first()
# Hack to log the dashboard_id properly, even when getting a slug
@utils.log_this
def dashboard(**kwargs):
pass
dashboard(dashboard_id=dash.id)
pos_dict = {}
if dash.position_json:
pos_dict = {
int(o['slice_id']):o
for o in json.loads(dash.position_json)}
return self.render_template(
"panoramix/dashboard.html", dashboard=dash,
templates=templates,
pos_dict=pos_dict)
def dashboard(self, dashboard_id):
session = db.session()
qry = session.query(models.Dashboard)
if dashboard_id.isdigit():
qry = qry.filter_by(id=int(dashboard_id))
else:
qry = qry.filter_by(slug=dashboard_id)
templates = session.query(models.CssTemplate).all()
dash = qry.first()
# Hack to log the dashboard_id properly, even when getting a slug
@utils.log_this
def dashboard(**kwargs):
pass
dashboard(dashboard_id=dash.id)
pos_dict = {}
if dash.position_json:
pos_dict = {
int(o['slice_id']):o
for o in json.loads(dash.position_json)}
return self.render_template(
"panoramix/dashboard.html", dashboard=dash,
templates=templates,
pos_dict=pos_dict)
def featured_datasets(self):
session = db.session()
datasets_sqla = (session.query(
models.SqlaTable).filter_by(is_featured=True).all())
datasets_druid = (session.query(
models.Datasource).filter_by(is_featured=True).all())
featured_datasets = datasets_sqla + datasets_druid
return self.render_template('panoramix/featured_datasets.html',
featured_datasets=featured_datasets,
utils=utils)
def refresh_datasources(self):
session = db.session()
for cluster in session.query(models.Cluster).all():
cluster.refresh_datasources()
cluster.metadata_last_refreshed = datetime.now()
flash(
"Refreshed metadata from cluster "
"[" + cluster.cluster_name + "]", 'info')
session.commit()
return redirect("/datasourcemodelview/list/")
def featured_datasets(self):
session = db.session()
datasets_sqla = (session.query(models.SqlaTable)
.filter_by(is_featured=True).all())
datasets_druid = (session.query(models.DruidDatasource)
.filter_by(is_featured=True).all())
featured_datasets = datasets_sqla + datasets_druid
return self.render_template(
'panoramix/featured_datasets.html',
featured_datasets=featured_datasets,
utils=utils)
def refresh_datasources(self):
session = db.session()
for cluster in session.query(models.Cluster).all():
cluster.refresh_datasources()
cluster.metadata_last_refreshed = datetime.now()
flash(
"Refreshed metadata from cluster "
"[" + cluster.cluster_name + "]",
'info')
session.commit()
return redirect("/datasourcemodelview/list/")
def refresh_datasources(self):
session = db.session()
for cluster in session.query(models.Cluster).all():
try:
cluster.refresh_datasources()
except Exception as e:
flash("Error while processing cluster '{}'".format(cluster), "alert")
return redirect("/clustermodelview/list/")
cluster.metadata_last_refreshed = datetime.now()
flash("Refreshed metadata from cluster " "[" + cluster.cluster_name + "]", "info")
session.commit()
return redirect("/datasourcemodelview/list/")
def dashboard(self, id_):
session = db.session()
dashboard = (session.query(
models.Dashboard).filter(models.Dashboard.id == id_).first())
pos_dict = {}
if dashboard.position_json:
pos_dict = {
int(o['slice_id']): o
for o in json.loads(dashboard.position_json)
}
return self.render_template("panoramix/dashboard.html",
dashboard=dashboard,
pos_dict=pos_dict)
def dashboard(self, identifier):
session = db.session()
qry = session.query(models.Dashboard)
if identifier.isdigit():
qry = qry.filter_by(id=int(identifier))
else:
qry = qry.filter_by(slug=identifier)
dashboard = qry.first()
pos_dict = {}
if dashboard.position_json:
pos_dict = {int(o["slice_id"]): o for o in json.loads(dashboard.position_json)}
return self.render_template("panoramix/dashboard.html", dashboard=dashboard, pos_dict=pos_dict)
def save_dash(self, dashboard_id):
data = json.loads(request.form.get('data'))
positions = data['positions']
slice_ids = [int(d['slice_id']) for d in positions]
session = db.session()
Dash = models.Dashboard
dash = session.query(Dash).filter_by(id=dashboard_id).first()
dash.slices = [o for o in dash.slices if o.id in slice_ids]
dash.position_json = json.dumps(data['positions'], indent=4)
dash.css = data['css']
session.merge(dash)
session.commit()
session.close()
return "SUCCESS"
def save_dash(self, dashboard_id):
data = json.loads(request.form.get('data'))
positions = data['positions']
slice_ids = [int(d['slice_id']) for d in positions]
session = db.session()
Dash = models.Dashboard
dash = session.query(Dash).filter_by(id=dashboard_id).first()
dash.slices = [o for o in dash.slices if o.id in slice_ids]
dash.position_json = json.dumps(data['positions'], indent=4)
dash.css = data['css']
session.merge(dash)
session.commit()
session.close()
return "SUCCESS"
def init():
"""
Inits the Panoramix application with security roles and such
"""
from panoramix import appbuilder
from panoramix import models
from flask_appbuilder.security.sqla import models as ab_models
sm = appbuilder.sm
alpha = sm.add_role("Alpha")
admin = sm.add_role("Admin")
merge_perm(sm, 'all_datasource_access', 'all_datasource_access')
perms = db.session.query(ab_models.PermissionView).all()
for perm in perms:
if perm.permission.name == 'datasource_access':
continue
if perm.view_menu.name not in (
'UserDBModelView', 'RoleModelView', 'ResetPasswordView',
'Security'):
sm.add_permission_role(alpha, perm)
sm.add_permission_role(admin, perm)
gamma = sm.add_role("Gamma")
for perm in perms:
s = perm.permission.name
if(
perm.view_menu.name not in (
'ResetPasswordView',
'RoleModelView',
'UserDBModelView',
'Security') and
perm.permission.name not in (
'all_datasource_access',
'can_add',
'can_download',
'can_delete',
'can_edit',
'can_save',
'datasource_access',
'muldelete',
)):
sm.add_permission_role(gamma, perm)
session = db.session()
table_perms = [
table.perm for table in session.query(models.SqlaTable).all()]
table_perms += [
table.perm for table in session.query(models.DruidDatasource).all()]
for table_perm in table_perms:
merge_perm(sm, 'datasource_access', table.perm)
def refresh_datasources(self):
session = db.session()
for cluster in session.query(models.Cluster).all():
try:
cluster.refresh_datasources()
except Exception as e:
flash("Error while processing cluster '{}'".format(cluster),
"alert")
return redirect('/clustermodelview/list/')
cluster.metadata_last_refreshed = datetime.now()
flash(
"Refreshed metadata from cluster "
"[" + cluster.cluster_name + "]", 'info')
session.commit()
return redirect("/datasourcemodelview/list/")
def dashboard(self, id_):
session = db.session()
dashboard = (
session
.query(models.Dashboard)
.filter(models.Dashboard.id == id_)
.first()
)
pos_dict = {}
if dashboard.position_json:
pos_dict = {
int(o['slice_id']):o for o in json.loads(dashboard.position_json)}
return self.render_template(
"panoramix/dashboard.html", dashboard=dashboard,
pos_dict=pos_dict)
def init():
"""
Inits the Panoramix application with security roles and such
"""
from panoramix import appbuilder
from panoramix import models
from flask_appbuilder.security.sqla import models as ab_models
sm = appbuilder.sm
alpha = sm.add_role("Alpha")
admin = sm.add_role("Admin")
merge_perm(sm, 'all_datasource_access', 'all_datasource_access')
perms = db.session.query(ab_models.PermissionView).all()
for perm in perms:
if perm.permission.name == 'datasource_access':
continue
if perm.view_menu.name not in ('UserDBModelView', 'RoleModelView',
'ResetPasswordView', 'Security'):
sm.add_permission_role(alpha, perm)
sm.add_permission_role(admin, perm)
gamma = sm.add_role("Gamma")
for perm in perms:
s = perm.permission.name
if (perm.view_menu.name not in ('ResetPasswordView', 'RoleModelView',
'UserDBModelView', 'Security')
and perm.permission.name not in (
'all_datasource_access',
'can_add',
'can_download',
'can_delete',
'can_edit',
'can_save',
'datasource_access',
'muldelete',
)):
sm.add_permission_role(gamma, perm)
session = db.session()
table_perms = [
table.perm for table in session.query(models.SqlaTable).all()
]
table_perms += [
table.perm for table in session.query(models.DruidDatasource).all()
]
for table_perm in table_perms:
merge_perm(sm, 'datasource_access', table.perm)
def dashboard(self, identifier):
session = db.session()
qry = session.query(models.Dashboard)
if identifier.isdigit():
qry = qry.filter_by(id=int(identifier))
else:
qry = qry.filter_by(slug=identifier)
dashboard = qry.first()
pos_dict = {}
if dashboard.position_json:
pos_dict = {
int(o['slice_id']): o
for o in json.loads(dashboard.position_json)
}
return self.render_template("panoramix/dashboard.html",
dashboard=dashboard,
pos_dict=pos_dict)
def refresh_datasources(self):
session = db.session()
for cluster in session.query(models.DruidCluster).all():
try:
cluster.refresh_datasources()
except Exception as e:
flash(
"Error while processing cluster '{}'\n{}".format(
cluster, str(e)),
"danger")
logging.exception(e)
return redirect('/druidclustermodelview/list/')
cluster.metadata_last_refreshed = datetime.now()
flash(
"Refreshed metadata from cluster "
"[" + cluster.cluster_name + "]",
'info')
session.commit()
return redirect("/datasourcemodelview/list/")
def datasource(self, datasource_type, datasource_id):
if datasource_type == "table":
datasource = (db.session.query(
models.SqlaTable).filter_by(id=datasource_id).first())
else:
datasource = (db.session.query(
models.Datasource).filter_by(id=datasource_id).first())
all_datasource_access = self.appbuilder.sm.has_access(
'all_datasource_access', 'all_datasource_access')
datasource_access = self.appbuilder.sm.has_access(
'datasource_access', datasource.perm)
if not (all_datasource_access or datasource_access):
flash("You don't seem to have access to this datasource",
"danger")
return redirect('/slicemodelview/list/')
action = request.args.get('action')
if action == 'save':
session = db.session()
d = request.args.to_dict(flat=False)
del d['action']
as_list = ('metrics', 'groupby')
for k in d:
v = d.get(k)
if k in as_list and not isinstance(v, list):
d[k] = [v] if v else []
if k not in as_list and isinstance(v, list):
d[k] = v[0]
table_id = druid_datasource_id = None
datasource_type = request.args.get('datasource_type')
if datasource_type in ('datasource', 'druid'):
druid_datasource_id = request.args.get('datasource_id')
elif datasource_type == 'table':
table_id = request.args.get('datasource_id')
slice_name = request.args.get('slice_name')
obj = models.Slice(
params=json.dumps(d, indent=4, sort_keys=True),
viz_type=request.args.get('viz_type'),
datasource_name=request.args.get('datasource_name'),
druid_datasource_id=druid_datasource_id,
table_id=table_id,
datasource_type=datasource_type,
slice_name=slice_name,
)
session.add(obj)
session.commit()
flash("Slice <{}> has been added to the pie".format(slice_name),
"info")
return redirect(obj.slice_url)
if not datasource:
flash("The datasource seem to have been deleted", "alert")
viz_type = request.args.get("viz_type")
if not viz_type and datasource.default_endpoint:
return redirect(datasource.default_endpoint)
if not viz_type:
viz_type = "table"
obj = viz.viz_types[viz_type](datasource, form_data=request.args)
if request.args.get("json") == "true":
if config.get("DEBUG"):
payload = obj.get_json()
try:
payload = obj.get_json()
status = 200
except Exception as e:
logging.exception(e)
payload = str(e)
status = 500
return Response(payload,
status=status,
mimetype="application/json")
else:
if config.get("DEBUG"):
resp = self.render_template("panoramix/viz.html", viz=obj)
try:
resp = self.render_template("panoramix/viz.html", viz=obj)
except Exception as e:
if config.get("DEBUG"):
raise (e)
return Response(str(e),
status=500,
mimetype="application/json")
return resp
def explore(self, datasource_type, datasource_id):
if datasource_type == "table":
datasource = (db.session.query(
models.SqlaTable).filter_by(id=datasource_id).first())
else:
datasource = (db.session.query(
models.Datasource).filter_by(id=datasource_id).first())
all_datasource_access = self.appbuilder.sm.has_access(
'all_datasource_access', 'all_datasource_access')
datasource_access = self.appbuilder.sm.has_access(
'datasource_access', datasource.perm)
if not (all_datasource_access or datasource_access):
flash("You don't seem to have access to this datasource",
"danger")
return redirect('/slicemodelview/list/')
action = request.args.get('action')
if action in ('save', 'overwrite'):
session = db.session()
# TODO use form processing form wtforms
d = request.args.to_dict(flat=False)
del d['action']
del d['previous_viz_type']
as_list = ('metrics', 'groupby', 'columns')
for k in d:
v = d.get(k)
if k in as_list and not isinstance(v, list):
d[k] = [v] if v else []
if k not in as_list and isinstance(v, list):
d[k] = v[0]
table_id = druid_datasource_id = None
datasource_type = request.args.get('datasource_type')
if datasource_type in ('datasource', 'druid'):
druid_datasource_id = request.args.get('datasource_id')
elif datasource_type == 'table':
table_id = request.args.get('datasource_id')
slice_name = request.args.get('slice_name')
if action == "save":
slc = models.Slice()
msg = "Slice [{}] has been saved".format(slice_name)
elif action == "overwrite":
slc = (session.query(models.Slice).filter_by(
id=request.args.get("slice_id")).first())
msg = "Slice [{}] has been overwritten".format(slice_name)
slc.params = json.dumps(d, indent=4, sort_keys=True)
slc.datasource_name = request.args.get('datasource_name')
slc.viz_type = request.args.get('viz_type')
slc.druid_datasource_id = druid_datasource_id
slc.table_id = table_id
slc.datasource_type = datasource_type
slc.slice_name = slice_name
session.merge(slc)
session.commit()
flash(msg, "info")
return redirect(slc.slice_url)
if not datasource:
flash("The datasource seem to have been deleted", "alert")
viz_type = request.args.get("viz_type")
if not viz_type and datasource.default_endpoint:
return redirect(datasource.default_endpoint)
if not viz_type:
viz_type = "table"
obj = viz.viz_types[viz_type](datasource, form_data=request.args)
if request.args.get("csv") == "true":
status = 200
payload = obj.get_csv()
return Response(payload, status=status, mimetype="application/csv")
if request.args.get("json") == "true":
status = 200
if config.get("DEBUG"):
payload = obj.get_json()
else:
try:
payload = obj.get_json()
except Exception as e:
logging.exception(e)
payload = str(e)
status = 500
return Response(payload,
status=status,
mimetype="application/json")
else:
if config.get("DEBUG"):
resp = self.render_template("panoramix/viz.html", viz=obj)
try:
resp = self.render_template("panoramix/viz.html", viz=obj)
except Exception as e:
if config.get("DEBUG"):
raise (e)
return Response(str(e),
status=500,
mimetype="application/json")
return resp
def datasource(self, datasource_type, datasource_id):
if datasource_type == "table":
datasource = (
db.session
.query(models.SqlaTable)
.filter_by(id=datasource_id)
.first()
)
else:
datasource = (
db.session
.query(models.Datasource)
.filter_by(id=datasource_id)
.first()
)
all_datasource_access = self.appbuilder.sm.has_access(
'all_datasource_access', 'all_datasource_access')
datasource_access = self.appbuilder.sm.has_access(
'datasource_access', datasource.perm)
if not (all_datasource_access or datasource_access):
flash(
"You don't seem to have access to this datasource",
"danger")
return redirect('/slicemodelview/list/')
action = request.args.get('action')
if action == 'save':
session = db.session()
# TODO use form processing form wtforms
d = request.args.to_dict(flat=False)
del d['action']
del d['previous_viz_type']
as_list = ('metrics', 'groupby')
for k in d:
v = d.get(k)
if k in as_list and not isinstance(v, list):
d[k] = [v] if v else []
if k not in as_list and isinstance(v, list):
d[k] = v[0]
table_id = druid_datasource_id = None
datasource_type = request.args.get('datasource_type')
if datasource_type in ('datasource', 'druid'):
druid_datasource_id = request.args.get('datasource_id')
elif datasource_type == 'table':
table_id = request.args.get('datasource_id')
slice_name = request.args.get('slice_name')
obj = models.Slice(
params=json.dumps(d, indent=4, sort_keys=True),
viz_type=request.args.get('viz_type'),
datasource_name=request.args.get('datasource_name'),
druid_datasource_id=druid_datasource_id,
table_id=table_id,
datasource_type=datasource_type,
slice_name=slice_name,
)
session.add(obj)
session.commit()
flash("Slice <{}> has been added to the pie".format(slice_name), "info")
return redirect(obj.slice_url)
if not datasource:
flash("The datasource seem to have been deleted", "alert")
viz_type = request.args.get("viz_type")
if not viz_type and datasource.default_endpoint:
return redirect(datasource.default_endpoint)
if not viz_type:
viz_type = "table"
obj = viz.viz_types[viz_type](
datasource,
form_data=request.args)
if request.args.get("json") == "true":
if config.get("DEBUG"):
payload = obj.get_json()
try:
payload = obj.get_json()
status=200
except Exception as e:
logging.exception(e)
payload = str(e)
status=500
return Response(
payload,
status=status,
mimetype="application/json")
else:
if config.get("DEBUG"):
resp = self.render_template("panoramix/viz.html", viz=obj)
try:
resp = self.render_template("panoramix/viz.html", viz=obj)
except Exception as e:
if config.get("DEBUG"):
raise(e)
return Response(
str(e),
status=500,
mimetype="application/json")
return resp
def explore(self, datasource_type, datasource_id):
if datasource_type == "table":
datasource = db.session.query(models.SqlaTable).filter_by(id=datasource_id).first()
else:
datasource = db.session.query(models.Datasource).filter_by(id=datasource_id).first()
all_datasource_access = self.appbuilder.sm.has_access("all_datasource_access", "all_datasource_access")
datasource_access = self.appbuilder.sm.has_access("datasource_access", datasource.perm)
if not (all_datasource_access or datasource_access):
flash("You don't seem to have access to this datasource", "danger")
return redirect("/slicemodelview/list/")
action = request.args.get("action")
if action in ("save", "overwrite"):
session = db.session()
# TODO use form processing form wtforms
d = request.args.to_dict(flat=False)
del d["action"]
del d["previous_viz_type"]
as_list = ("metrics", "groupby", "columns")
for k in d:
v = d.get(k)
if k in as_list and not isinstance(v, list):
d[k] = [v] if v else []
if k not in as_list and isinstance(v, list):
d[k] = v[0]
table_id = druid_datasource_id = None
datasource_type = request.args.get("datasource_type")
if datasource_type in ("datasource", "druid"):
druid_datasource_id = request.args.get("datasource_id")
elif datasource_type == "table":
table_id = request.args.get("datasource_id")
slice_name = request.args.get("slice_name")
if action == "save":
slc = models.Slice()
msg = "Slice [{}] has been saved".format(slice_name)
elif action == "overwrite":
slc = session.query(models.Slice).filter_by(id=request.args.get("slice_id")).first()
msg = "Slice [{}] has been overwritten".format(slice_name)
slc.params = json.dumps(d, indent=4, sort_keys=True)
slc.datasource_name = request.args.get("datasource_name")
slc.viz_type = request.args.get("viz_type")
slc.druid_datasource_id = druid_datasource_id
slc.table_id = table_id
slc.datasource_type = datasource_type
slc.slice_name = slice_name
session.merge(slc)
session.commit()
flash(msg, "info")
return redirect(slc.slice_url)
if not datasource:
flash("The datasource seem to have been deleted", "alert")
viz_type = request.args.get("viz_type")
if not viz_type and datasource.default_endpoint:
return redirect(datasource.default_endpoint)
if not viz_type:
viz_type = "table"
obj = viz.viz_types[viz_type](datasource, form_data=request.args)
if request.args.get("json") == "true":
status = 200
if config.get("DEBUG"):
payload = obj.get_json()
else:
try:
payload = obj.get_json()
except Exception as e:
logging.exception(e)
payload = str(e)
status = 500
return Response(payload, status=status, mimetype="application/json")
else:
if config.get("DEBUG"):
resp = self.render_template("panoramix/viz.html", viz=obj)
try:
resp = self.render_template("panoramix/viz.html", viz=obj)
except Exception as e:
if config.get("DEBUG"):
raise (e)
return Response(str(e), status=500, mimetype="application/json")
return resp