def query(self): session = settings.Session() dbs = session.query(models.Connection).order_by( models.Connection.conn_id) db_choices = [(db.conn_id, db.conn_id) for db in dbs] conn_id_str = request.args.get('conn_id') sql = request.args.get('sql') class QueryForm(Form): conn_id = SelectField("Layout", choices=db_choices) sql = TextAreaField("SQL", widget=wwwutils.AceEditorWidget()) data = { 'conn_id': conn_id_str, 'sql': sql, } results = None has_data = False error = False if conn_id_str: db = [db for db in dbs if db.conn_id == conn_id_str][0] hook = db.get_hook() try: df = hook.get_pandas_df(wwwutils.limit_sql(sql, QUERY_LIMIT)) #df = hook.get_pandas_df(sql) has_data = len(df) > 0 df = df.fillna('') results = df.to_html( classes="table table-bordered table-striped no-wrap", index=False, na_rep='', ) if has_data else '' except Exception as e: flash(str(e), 'error') error = True if has_data and len(df) == QUERY_LIMIT: flash("Query output truncated at " + str(QUERY_LIMIT) + " rows", 'info') if not has_data and error: flash('No data', 'error') form = QueryForm(request.form, data=data) session.commit() session.close() return self.render('airflow/query.html', form=form, title="Query", results=results or '', has_data=has_data)
def query(self): session = settings.Session() dbs = session.query(models.Connection).order_by( models.Connection.conn_id) db_choices = [(db.conn_id, db.conn_id) for db in dbs] conn_id_str = request.args.get('conn_id') sql = request.args.get('sql') class QueryForm(Form): conn_id = SelectField("Layout", choices=db_choices) sql = TextAreaField("SQL", widget=wwwutils.AceEditorWidget()) data = { 'conn_id': conn_id_str, 'sql': sql, } results = None has_data = False error = False if conn_id_str: db = [db for db in dbs if db.conn_id == conn_id_str][0] hook = db.get_hook() try: df = hook.get_pandas_df(wwwutils.limit_sql(sql, QUERY_LIMIT)) #df = hook.get_pandas_df(sql) has_data = len(df) > 0 df = df.fillna('') results = df.to_html( classes="table table-bordered table-striped no-wrap", index=False, na_rep='', ) if has_data else '' except Exception as e: flash(str(e), 'error') error = True if has_data and len(df) == QUERY_LIMIT: flash( "Query output truncated at " + str(QUERY_LIMIT) + " rows", 'info') if not has_data and error: flash('No data', 'error') form = QueryForm(request.form, data=data) session.commit() session.close() return self.render( 'airflow/query.html', form=form, title="Query", results=results or '', has_data=has_data)
def chart_data(self): session = settings.Session() chart_id = request.args.get('chart_id') chart = session.query(models.Chart).filter_by(id=chart_id).all()[0] db = session.query( models.Connection).filter_by(conn_id=chart.conn_id).all()[0] session.expunge_all() payload = {} payload['state'] = 'ERROR' payload['error'] = '' # Processing templated fields try: args = eval(chart.default_params) if type(args) is not type(dict()): raise Exception('Not a dict') except: args = {} payload['error'] += ( "Default params is not valid, string has to evaluate as " "a Python dictionary. ") request_dict = {k: request.args.get(k) for k in request.args} from airflow import macros args.update(request_dict) args['macros'] = macros sql = jinja2.Template(chart.sql).render(**args) label = jinja2.Template(chart.label).render(**args) payload['sql_html'] = Markup( highlight( sql, SqlLexer(), # Lexer call HtmlFormatter(noclasses=True))) payload['label'] = label import pandas as pd pd.set_option('display.max_colwidth', 100) hook = db.get_hook() try: df = hook.get_pandas_df(wwwutils.limit_sql(sql, CHART_LIMIT)) except Exception as e: payload['error'] += "SQL execution failed. Details: " + str(e) if not payload['error'] and len(df) == CHART_LIMIT: payload['warning'] = ( "Data has been truncated to {0}" " rows. Expect incomplete results.").format(CHART_LIMIT) if not payload['error'] and len(df) == 0: payload['error'] += "Empty result set. " elif (not payload['error'] and chart.sql_layout == 'series' and len(df.columns) < 3): payload['error'] += "SQL needs to return at least 3 columns. " elif (not payload['error'] and chart.sql_layout == 'columns' and len(df.columns) < 2): payload['error'] += "SQL needs to return at least 2 columns. " elif not payload['error']: import numpy as np data = None if chart.chart_type == "datatable": chart.show_datatable = True if chart.show_datatable: data = df.to_dict(orient="split") data['columns'] = [{'title': c} for c in data['columns']] # Trying to convert time to something Highcharts likes x_col = 1 if chart.sql_layout == 'series' else 0 if chart.x_is_date: try: # From string to datetime df[df.columns[x_col]] = pd.to_datetime( df[df.columns[x_col]]) except Exception as e: raise Exception(str(e)) df[df.columns[x_col]] = df[df.columns[x_col]].apply( lambda x: int(x.strftime("%s")) * 1000) series = [] colorAxis = None if chart.chart_type == 'heatmap': color_perc_lbound = float( request.args.get('color_perc_lbound', 0)) color_perc_rbound = float( request.args.get('color_perc_rbound', 1)) color_scheme = request.args.get('color_scheme', 'blue_red') if color_scheme == 'blue_red': stops = [[color_perc_lbound, '#00D1C1'], [ color_perc_lbound + ((color_perc_rbound - color_perc_lbound) / 2), '#FFFFCC' ], [color_perc_rbound, '#FF5A5F']] elif color_scheme == 'blue_scale': stops = [[color_perc_lbound, '#FFFFFF'], [color_perc_rbound, '#2222FF']] elif color_scheme == 'fire': diff = float(color_perc_rbound - color_perc_lbound) stops = [[color_perc_lbound, '#FFFFFF'], [color_perc_lbound + 0.33 * diff, '#FFFF00'], [color_perc_lbound + 0.66 * diff, '#FF0000'], [color_perc_rbound, '#000000']] else: stops = [ [color_perc_lbound, '#FFFFFF'], [ color_perc_lbound + ((color_perc_rbound - color_perc_lbound) / 2), '#888888' ], [color_perc_rbound, '#000000'], ] xaxis_label = df.columns[1] yaxis_label = df.columns[2] data = [] for row in df.itertuples(): data.append({ 'x': row[2], 'y': row[3], 'value': row[4], }) x_format = '{point.x:%Y-%m-%d}' \ if chart.x_is_date else '{point.x}' series.append({ 'data': data, 'borderWidth': 0, 'colsize': 24 * 36e5, 'turboThreshold': sys.float_info.max, 'tooltip': { 'headerFormat': '', 'pointFormat': (df.columns[1] + ': ' + x_format + '<br/>' + df.columns[2] + ': {point.y}<br/>' + df.columns[3] + ': <b>{point.value}</b>'), }, }) colorAxis = { 'stops': stops, 'minColor': '#FFFFFF', 'maxColor': '#000000', 'min': 50, 'max': 2200, } else: if chart.sql_layout == 'series': # User provides columns (series, x, y) xaxis_label = df.columns[1] yaxis_label = df.columns[2] df[df.columns[2]] = df[df.columns[2]].astype(np.float) df = df.pivot_table(index=df.columns[1], columns=df.columns[0], values=df.columns[2], aggfunc=np.sum) else: # User provides columns (x, y, metric1, metric2, ...) xaxis_label = df.columns[0] yaxis_label = 'y' df.index = df[df.columns[0]] df = df.sort(df.columns[0]) del df[df.columns[0]] for col in df.columns: df[col] = df[col].astype(np.float) for col in df.columns: series.append({ 'name': col, 'data': [(i, v) for i, v in df[col].iteritems() if not np.isnan(v)] }) series = [ serie for serie in sorted( series, key=lambda s: s['data'][0][1], reverse=True) ] chart_type = chart.chart_type if chart.chart_type == "stacked_area": stacking = "normal" chart_type = 'area' elif chart.chart_type == "percent_area": stacking = "percent" chart_type = 'area' else: stacking = None hc = { 'chart': { 'type': chart_type }, 'plotOptions': { 'series': { 'marker': { 'enabled': False } }, 'area': { 'stacking': stacking }, }, 'title': { 'text': '' }, 'xAxis': { 'title': { 'text': xaxis_label }, 'type': 'datetime' if chart.x_is_date else None, }, 'yAxis': { 'min': 0, 'title': { 'text': yaxis_label }, }, 'colorAxis': colorAxis, 'tooltip': { 'useHTML': True, 'backgroundColor': None, 'borderWidth': 0, }, 'series': series, } if chart.y_log_scale: hc['yAxis']['type'] = 'logarithmic' hc['yAxis']['minorTickInterval'] = 0.1 del hc['yAxis']['min'] payload['state'] = 'SUCCESS' payload['hc'] = hc payload['data'] = data payload['request_dict'] = request_dict def date_handler(obj): return obj.isoformat() if hasattr(obj, 'isoformat') else obj response = Response(response=json.dumps(payload, indent=4, default=date_handler), status=200, mimetype="application/json") session.commit() session.close() return response
def chart_data(self): session = settings.Session() chart_id = request.args.get('chart_id') chart = session.query(models.Chart).filter_by(id=chart_id).all()[0] db = session.query( models.Connection).filter_by(conn_id=chart.conn_id).all()[0] session.expunge_all() payload = {} payload['state'] = 'ERROR' payload['error'] = '' # Processing templated fields try: args = eval(chart.default_params) if type(args) is not type(dict()): raise Exception('Not a dict') except: args = {} payload['error'] += ( "Default params is not valid, string has to evaluate as " "a Python dictionary. ") request_dict = {k: request.args.get(k) for k in request.args} from airflow import macros args.update(request_dict) args['macros'] = macros sql = jinja2.Template(chart.sql).render(**args) label = jinja2.Template(chart.label).render(**args) payload['sql_html'] = Markup(highlight( sql, SqlLexer(), # Lexer call HtmlFormatter(noclasses=True)) ) payload['label'] = label import pandas as pd pd.set_option('display.max_colwidth', 100) hook = db.get_hook() try: df = hook.get_pandas_df(wwwutils.limit_sql(sql, CHART_LIMIT)) except Exception as e: payload['error'] += "SQL execution failed. Details: " + str(e) if not payload['error'] and len(df) == CHART_LIMIT: payload['warning'] = ( "Data has been truncated to {0}" " rows. Expect incomplete results.").format(CHART_LIMIT) if not payload['error'] and len(df) == 0: payload['error'] += "Empty result set. " elif ( not payload['error'] and chart.sql_layout == 'series' and len(df.columns) < 3): payload['error'] += "SQL needs to return at least 3 columns. " elif ( not payload['error'] and chart.sql_layout == 'columns' and len(df.columns) < 2): payload['error'] += "SQL needs to return at least 2 columns. " elif not payload['error']: import numpy as np data = None if chart.chart_type == "datatable": chart.show_datatable = True if chart.show_datatable: data = df.to_dict(orient="split") data['columns'] = [{'title': c} for c in data['columns']] # Trying to convert time to something Highcharts likes x_col = 1 if chart.sql_layout == 'series' else 0 if chart.x_is_date: try: # From string to datetime df[df.columns[x_col]] = pd.to_datetime( df[df.columns[x_col]]) except Exception as e: raise Exception(str(e)) df[df.columns[x_col]] = df[df.columns[x_col]].apply( lambda x: int(x.strftime("%s")) * 1000) series = [] colorAxis = None if chart.chart_type == 'heatmap': color_perc_lbound = float( request.args.get('color_perc_lbound', 0)) color_perc_rbound = float( request.args.get('color_perc_rbound', 1)) color_scheme = request.args.get('color_scheme', 'blue_red') if color_scheme == 'blue_red': stops = [ [color_perc_lbound, '#00D1C1'], [ color_perc_lbound + ((color_perc_rbound - color_perc_lbound)/2), '#FFFFCC' ], [color_perc_rbound, '#FF5A5F'] ] elif color_scheme == 'blue_scale': stops = [ [color_perc_lbound, '#FFFFFF'], [color_perc_rbound, '#2222FF'] ] elif color_scheme == 'fire': diff = float(color_perc_rbound - color_perc_lbound) stops = [ [color_perc_lbound, '#FFFFFF'], [color_perc_lbound + 0.33*diff, '#FFFF00'], [color_perc_lbound + 0.66*diff, '#FF0000'], [color_perc_rbound, '#000000'] ] else: stops = [ [color_perc_lbound, '#FFFFFF'], [ color_perc_lbound + ((color_perc_rbound - color_perc_lbound)/2), '#888888' ], [color_perc_rbound, '#000000'], ] xaxis_label = df.columns[1] yaxis_label = df.columns[2] data = [] for row in df.itertuples(): data.append({ 'x': row[2], 'y': row[3], 'value': row[4], }) x_format = '{point.x:%Y-%m-%d}' \ if chart.x_is_date else '{point.x}' series.append({ 'data': data, 'borderWidth': 0, 'colsize': 24 * 36e5, 'turboThreshold': sys.float_info.max, 'tooltip': { 'headerFormat': '', 'pointFormat': ( df.columns[1] + ': ' + x_format + '<br/>' + df.columns[2] + ': {point.y}<br/>' + df.columns[3] + ': <b>{point.value}</b>' ), }, }) colorAxis = { 'stops': stops, 'minColor': '#FFFFFF', 'maxColor': '#000000', 'min': 50, 'max': 2200, } else: if chart.sql_layout == 'series': # User provides columns (series, x, y) xaxis_label = df.columns[1] yaxis_label = df.columns[2] df[df.columns[2]] = df[df.columns[2]].astype(np.float) df = df.pivot_table( index=df.columns[1], columns=df.columns[0], values=df.columns[2], aggfunc=np.sum) else: # User provides columns (x, y, metric1, metric2, ...) xaxis_label = df.columns[0] yaxis_label = 'y' df.index = df[df.columns[0]] df = df.sort(df.columns[0]) del df[df.columns[0]] for col in df.columns: df[col] = df[col].astype(np.float) for col in df.columns: series.append({ 'name': col, 'data': [ (i, v) for i, v in df[col].iteritems() if not np.isnan(v)] }) series = [serie for serie in sorted( series, key=lambda s: s['data'][0][1], reverse=True)] chart_type = chart.chart_type if chart.chart_type == "stacked_area": stacking = "normal" chart_type = 'area' elif chart.chart_type == "percent_area": stacking = "percent" chart_type = 'area' else: stacking = None hc = { 'chart': { 'type': chart_type }, 'plotOptions': { 'series': { 'marker': { 'enabled': False } }, 'area': {'stacking': stacking}, }, 'title': {'text': ''}, 'xAxis': { 'title': {'text': xaxis_label}, 'type': 'datetime' if chart.x_is_date else None, }, 'yAxis': { 'min': 0, 'title': {'text': yaxis_label}, }, 'colorAxis': colorAxis, 'tooltip': { 'useHTML': True, 'backgroundColor': None, 'borderWidth': 0, }, 'series': series, } if chart.y_log_scale: hc['yAxis']['type'] = 'logarithmic' hc['yAxis']['minorTickInterval'] = 0.1 del hc['yAxis']['min'] payload['state'] = 'SUCCESS' payload['hc'] = hc payload['data'] = data payload['request_dict'] = request_dict def date_handler(obj): return obj.isoformat() if hasattr(obj, 'isoformat') else obj response = Response( response=json.dumps(payload, indent=4, default=date_handler), status=200, mimetype="application/json") session.commit() session.close() return response
def query(self, session=None): dbs = session.query(Connection).order_by(Connection.conn_id).all() session.expunge_all() db_choices = [] for db in dbs: try: if db.get_hook(): db_choices.append((db.conn_id, db.conn_id)) except Exception: pass conn_id_str = request.form.get('conn_id') csv = request.form.get('csv') == "true" sql = request.form.get('sql') class QueryForm(Form): conn_id = SelectField("Layout", choices=db_choices) sql = TextAreaField("SQL", widget=wwwutils.AceEditorWidget()) data = { 'conn_id': conn_id_str, 'sql': sql, } results = None has_data = False error = False if conn_id_str and request.method == 'POST': db = [db for db in dbs if db.conn_id == conn_id_str][0] try: hook = db.get_hook() df = hook.get_pandas_df( wwwutils.limit_sql(sql, QUERY_LIMIT, conn_type=db.conn_type)) # df = hook.get_pandas_df(sql) has_data = len(df) > 0 df = df.fillna('') results = df.to_html( classes=[ 'table', 'table-bordered', 'table-striped', 'no-wrap' ], index=False, na_rep='', ) if has_data else '' except Exception as e: flash(str(e), 'error') error = True if has_data and len(df) == QUERY_LIMIT: flash("Query output truncated at " + str(QUERY_LIMIT) + " rows", 'info') if not has_data and error: flash('No data', 'error') if csv: return Response(response=df.to_csv(index=False), status=200, mimetype="application/text") form = QueryForm(request.form, data=data) session.commit() return self.render('airflow/query.html', form=form, title="Ad Hoc Query", results=results or '', has_data=has_data)