class SliceAsync(SliceModelView): # noqa
list_columns = ['slice_link', 'viz_type', 'creator', 'modified', 'icons']
label_columns = {
'icons': ' ',
'viz_type': _('Type'),
'slice_link': _('Slice'),
'viz_type': _('Visualization Type'),
}
class DruidColumnInlineView(CompactCRUDMixin, CaravelModelView): # noqa
datamodel = SQLAInterface(models.DruidColumn)
edit_columns = [
'column_name', 'description', 'datasource', 'groupby',
'count_distinct', 'sum', 'min', 'max'
]
list_columns = [
'column_name', 'type', 'groupby', 'filterable', 'count_distinct',
'sum', 'min', 'max'
]
can_delete = False
page_size = 500
label_columns = {
'column_name': _("Column"),
'type': _("Type"),
'datasource': _("Datasource"),
'groupby': _("Groupable"),
'filterable': _("Filterable"),
'count_distinct': _("Count Distinct"),
'sum': _("Sum"),
'min': _("Min"),
'max': _("Max"),
}
def post_update(self, col):
col.generate_metrics()
class LogModelView(CaravelModelView):
datamodel = SQLAInterface(models.Log)
list_columns = ('user', 'action', 'dttm')
edit_columns = ('user', 'action', 'dttm', 'json')
base_order = ('dttm', 'desc')
label_columns = {
'user': _("User"),
'action': _("Action"),
'dttm': _("dttm"),
'json': _("JSON"),
}
class TableModelView(CaravelModelView, DeleteMixin): # noqa
datamodel = SQLAInterface(models.SqlaTable)
list_columns = [
'table_link', 'database', 'sql_link', 'is_featured', 'changed_by_',
'changed_on_'
]
add_columns = [
'table_name', 'database', 'schema', 'default_endpoint', 'offset',
'cache_timeout'
]
edit_columns = [
'table_name', 'is_featured', 'database', 'schema', 'description',
'owner', 'main_dttm_col', 'default_endpoint', 'offset', 'cache_timeout'
]
related_views = [TableColumnInlineView, SqlMetricInlineView]
base_order = ('changed_on', 'desc')
description_columns = {
'offset':
"Timezone offset (in hours) for this datasource",
'schema':
("Schema, as used only in some databases like Postgres, Redshift "
"and DB2"),
'description':
Markup(
"Supports <a href='https://daringfireball.net/projects/markdown/'>"
"markdown</a>"),
}
label_columns = {
'table_link': _("Table"),
'changed_by_': _("Changed By"),
'database': _("Database"),
'changed_on_': _("Last Changed"),
'sql_link': _("SQL Editor"),
'is_featured': _("Is Featured"),
'schema': _("Schema"),
'default_endpoint': _("Default Endpoint"),
'offset': _("Offset"),
'cache_timeout': _("Cache Timeout"),
}
def post_add(self, table):
table_name = table.table_name
try:
table.fetch_metadata()
except Exception as e:
logging.exception(e)
flash(
"Table [{}] doesn't seem to exist, "
"couldn't fetch metadata".format(table_name), "danger")
utils.merge_perm(sm, 'datasource_access', table.perm)
def post_update(self, table):
self.post_add(table)
class BigNumberViz(BaseViz):
"""Put emphasis on a single metric with this big number viz"""
viz_type = "big_number"
verbose_name = _("Big Number with Trendline")
credits = 'a <a href="https://github.com/airbnb/caravel">Caravel</a> original'
is_timeseries = True
fieldsets = ({
'label': None,
'fields': (
'metric',
'compare_lag',
'compare_suffix',
'y_axis_format',
)
},)
form_overrides = {
'y_axis_format': {
'label': _('Number format'),
}
}
def reassignments(self):
metric = self.form_data.get('metric')
if not metric:
self.form_data['metric'] = self.orig_form_data.get('metrics')
def query_obj(self):
d = super(BigNumberViz, self).query_obj()
metric = self.form_data.get('metric')
if not metric:
raise Exception("Pick a metric!")
d['metrics'] = [self.form_data.get('metric')]
self.form_data['metric'] = metric
return d
def get_data(self):
form_data = self.form_data
df = self.get_df()
df.sort_values(by=df.columns[0], inplace=True)
compare_lag = form_data.get("compare_lag", "")
compare_lag = int(compare_lag) if compare_lag and compare_lag.isdigit() else 0
return {
'data': df.values.tolist(),
'compare_lag': compare_lag,
'compare_suffix': form_data.get('compare_suffix', ''),
}
class DistributionPieViz(NVD3Viz):
"""Annoy visualization snobs with this controversial pie chart"""
viz_type = "pie"
verbose_name = _("Distribution - NVD3 - Pie Chart")
is_timeseries = False
fieldsets = ({
'label':
None,
'fields': (
'metrics',
'groupby',
'limit',
('donut', 'show_legend'),
)
}, )
def query_obj(self):
d = super(DistributionPieViz, self).query_obj()
d['is_timeseries'] = False
return d
def get_df(self, query_obj=None):
df = super(DistributionPieViz, self).get_df(query_obj)
df = df.pivot_table(index=self.groupby, values=[self.metrics[0]])
df.sort(self.metrics[0], ascending=False, inplace=True)
return df
def get_data(self):
df = self.get_df()
df = df.reset_index()
df.columns = ['x', 'y']
return df.to_dict(orient="records")
class WordCloudViz(BaseViz):
"""Build a colorful word cloud
Uses the nice library at:
https://github.com/jasondavies/d3-cloud
"""
viz_type = "word_cloud"
verbose_name = _("Word Cloud")
is_timeseries = False
fieldsets = ({
'label': None,
'fields': (
'series', 'metric', 'limit',
('size_from', 'size_to'),
'rotation',
)
},)
def query_obj(self):
d = super(WordCloudViz, self).query_obj()
d['metrics'] = [self.form_data.get('metric')]
d['groupby'] = [self.form_data.get('series')]
return d
def get_data(self):
df = self.get_df()
# Ordering the columns
df = df[[self.form_data.get('series'), self.form_data.get('metric')]]
# Labeling the columns for uniform json schema
df.columns = ['text', 'size']
return df.to_dict(orient="records")
class SankeyViz(BaseViz):
"""A Sankey diagram that requires a parent-child dataset"""
viz_type = "sankey"
verbose_name = _("Sankey")
is_timeseries = False
credits = '<a href="https://www.npmjs.com/package/d3-sankey">d3-sankey on npm</a>'
fieldsets = ({
'label': None,
'fields': (
'groupby',
'metric',
'row_limit',
)
}, )
form_overrides = {
'groupby': {
'label': 'Source / Target',
'description': "Choose a source and a target",
},
}
def query_obj(self):
qry = super(SankeyViz, self).query_obj()
if len(qry['groupby']) != 2:
raise Exception("Pick exactly 2 columns as [Source / Target]")
qry['metrics'] = [self.form_data['metric']]
return qry
def get_data(self):
df = self.get_df()
df.columns = ['source', 'target', 'value']
recs = df.to_dict(orient='records')
hierarchy = defaultdict(set)
for row in recs:
hierarchy[row['source']].add(row['target'])
def find_cycle(g):
"""Whether there's a cycle in a directed graph"""
path = set()
def visit(vertex):
path.add(vertex)
for neighbour in g.get(vertex, ()):
if neighbour in path or visit(neighbour):
return (vertex, neighbour)
path.remove(vertex)
for v in g:
cycle = visit(v)
if cycle:
return cycle
cycle = find_cycle(hierarchy)
if cycle:
raise Exception(
"There's a loop in your Sankey, please provide a tree. "
"Here's a faulty link: {}".format(cycle))
return recs
class TableViz(BaseViz):
"""A basic html table that is sortable and searchable"""
viz_type = "table"
verbose_name = _("Table View")
credits = 'a <a href="https://github.com/airbnb/caravel">Caravel</a> original'
fieldsets = ({
'label': "GROUP BY",
'description': 'Use this section if you want a query that aggregates',
'fields': (
'groupby',
'metrics',
)
}, {
'label': "NOT GROUPED BY",
'description': 'Use this section if you want to query atomic rows',
'fields': ('all_columns', )
}, {
'label':
"Options",
'fields': (
'table_timestamp_format',
'row_limit',
('include_search', None),
)
})
form_overrides = ({
'metrics': {
'default': [],
},
})
is_timeseries = False
def query_obj(self):
d = super(TableViz, self).query_obj()
fd = self.form_data
if fd.get('all_columns') and (fd.get('groupby') or fd.get('metrics')):
raise Exception(
"Choose either fields to [Group By] and [Metrics] or "
"[Columns], not both")
if fd.get('all_columns'):
d['columns'] = fd.get('all_columns')
d['groupby'] = []
return d
def get_df(self, query_obj=None):
df = super(TableViz, self).get_df(query_obj)
if (self.form_data.get("granularity") == "all" and 'timestamp' in df):
del df['timestamp']
return df
def get_data(self):
df = self.get_df()
return dict(
records=df.to_dict(orient="records"),
columns=list(df.columns),
)
def json_dumps(self, obj):
return json.dumps(obj, default=utils.json_iso_dttm_ser)
class PivotTableViz(BaseViz):
"""A pivot table view, define your rows, columns and metrics"""
viz_type = "pivot_table"
verbose_name = _("Pivot Table")
credits = 'a <a href="https://github.com/airbnb/caravel">Caravel</a> original'
is_timeseries = False
fieldsets = ({
'label': None,
'fields': (
'groupby',
'columns',
'metrics',
'pandas_aggfunc',
)
},)
def query_obj(self):
d = super(PivotTableViz, self).query_obj()
groupby = self.form_data.get('groupby')
columns = self.form_data.get('columns')
metrics = self.form_data.get('metrics')
if not columns:
columns = []
if not groupby:
groupby = []
if not groupby:
raise Exception("Please choose at least one \"Group by\" field ")
if not metrics:
raise Exception("Please choose at least one metric")
if (
any(v in groupby for v in columns) or
any(v in columns for v in groupby)):
raise Exception("groupby and columns can't overlap")
d['groupby'] = list(set(groupby) | set(columns))
return d
def get_df(self, query_obj=None):
df = super(PivotTableViz, self).get_df(query_obj)
if (
self.form_data.get("granularity") == "all" and
'timestamp' in df):
del df['timestamp']
df = df.pivot_table(
index=self.form_data.get('groupby'),
columns=self.form_data.get('columns'),
values=self.form_data.get('metrics'),
aggfunc=self.form_data.get('pandas_aggfunc'),
margins=True,
)
return df
def get_data(self):
return self.get_df().to_html(
na_rep='',
classes=(
"dataframe table table-striped table-bordered "
"table-condensed table-hover").split(" "))
class CalHeatmapViz(BaseViz):
"""Calendar heatmap."""
viz_type = "cal_heatmap"
verbose_name = _("Calender Heatmap")
credits = ('<a href=https://github.com/wa0x6e/cal-heatmap>cal-heatmap</a>')
is_timeseries = True
fieldsets = ({
'label':
None,
'fields': (
'metric',
'domain_granularity',
'subdomain_granularity',
),
}, )
def get_df(self, query_obj=None):
df = super(CalHeatmapViz, self).get_df(query_obj)
return df
def get_data(self):
df = self.get_df()
form_data = self.form_data
df.columns = ["timestamp", "metric"]
timestamps = {
str(obj["timestamp"].value / 10**9): obj.get("metric")
for obj in df.to_dict("records")
}
start = utils.parse_human_datetime(form_data.get("since"))
end = utils.parse_human_datetime(form_data.get("until"))
domain = form_data.get("domain_granularity")
diff_delta = rdelta.relativedelta(end, start)
diff_secs = (end - start).total_seconds()
if domain == "year":
range_ = diff_delta.years + 1
elif domain == "month":
range_ = diff_delta.years * 12 + diff_delta.months + 1
elif domain == "week":
range_ = diff_delta.years * 53 + diff_delta.weeks + 1
elif domain == "day":
range_ = diff_secs // (24 * 60 * 60) + 1
else:
range_ = diff_secs // (60 * 60) + 1
return {
"timestamps": timestamps,
"start": start,
"domain": domain,
"subdomain": form_data.get("subdomain_granularity"),
"range": range_,
}
def query_obj(self):
qry = super(CalHeatmapViz, self).query_obj()
qry["metrics"] = [self.form_data["metric"]]
return qry
def runsql(self):
"""Runs arbitrary sql and returns and html table"""
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()
if (not self.appbuilder.sm.has_access('all_datasource_access',
'all_datasource_access')):
raise utils.CaravelSecurityException(
_("This view requires the `all_datasource_access` permission"))
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").split(' '))
except Exception as e:
content = ('<div class="alert alert-danger">'
"{}</div>").format(e.message)
session.commit()
return content
class HorizonViz(NVD3TimeSeriesViz):
"""Horizon chart
https://www.npmjs.com/package/d3-horizon-chart
"""
viz_type = "horizon"
verbose_name = _("Horizon Charts")
credits = (
'<a href="https://www.npmjs.com/package/d3-horizon-chart">'
'd3-horizon-chart</a>')
fieldsets = [NVD3TimeSeriesViz.fieldsets[0]] + [{
'label': _('Chart Options'),
'fields': (
('series_height', 'horizon_color_scale'),
), }]
class FilterBoxViz(BaseViz):
"""A multi filter, multi-choice filter box to make dashboards interactive"""
viz_type = "filter_box"
verbose_name = _("Filters")
is_timeseries = False
credits = 'a <a href="https://github.com/airbnb/caravel">Caravel</a> original'
fieldsets = ({
'label': None,
'fields': (
'groupby',
'metric',
)
},)
form_overrides = {
'groupby': {
'label': _('Filter fields'),
'description': _("The fields you want to filter on"),
},
}
def query_obj(self):
qry = super(FilterBoxViz, self).query_obj()
groupby = self.form_data['groupby']
if len(groupby) < 1:
raise Exception("Pick at least one filter field")
qry['metrics'] = [
self.form_data['metric']]
return qry
def get_data(self):
qry = self.query_obj()
filters = [g for g in qry['groupby']]
d = {}
for flt in filters:
qry['groupby'] = [flt]
df = super(FilterBoxViz, self).get_df(qry)
d[flt] = [{
'id': row[0],
'text': row[0],
'filter': flt,
'metric': row[1]}
for row in df.itertuples(index=False)
]
return d
class IFrameViz(BaseViz):
"""You can squeeze just about anything in this iFrame component"""
viz_type = "iframe"
verbose_name = _("iFrame")
credits = 'a <a href="https://github.com/airbnb/caravel">Caravel</a> original'
is_timeseries = False
fieldsets = ({'label': None, 'fields': ('url', )}, )
class NVD3TimeSeriesBarViz(NVD3TimeSeriesViz):
"""A bar chart where the x axis is time"""
viz_type = "bar"
sort_series = True
verbose_name = _("Time Series - Bar Chart")
fieldsets = [NVD3TimeSeriesViz.fieldsets[0]] + [{
'label': _('Chart Options'),
'fields': (
('show_brush', 'show_legend'),
('rich_tooltip', 'y_axis_zero'),
('y_log_scale', 'contribution'),
('x_axis_format', 'y_axis_format'),
('line_interpolation', 'bar_stacked'),
('x_axis_showminmax', None),
), }] + [NVD3TimeSeriesViz.fieldsets[2]]
class NVD3TimeSeriesStackedViz(NVD3TimeSeriesViz):
"""A rich stack area chart"""
viz_type = "area"
verbose_name = _("Time Series - Stacked")
sort_series = True
fieldsets = [NVD3TimeSeriesViz.fieldsets[0]] + [{
'label': _('Chart Options'),
'fields': (
('show_brush', 'show_legend'),
('rich_tooltip', 'y_axis_zero'),
('y_log_scale', 'contribution'),
('x_axis_format', 'y_axis_format'),
('x_axis_showminmax'),
('line_interpolation', 'stacked_style'),
), }] + [NVD3TimeSeriesViz.fieldsets[2]]
class SqlMetricInlineView(CompactCRUDMixin, CaravelModelView): # noqa
datamodel = SQLAInterface(models.SqlMetric)
list_columns = ['metric_name', 'verbose_name', 'metric_type',
'is_restricted']
edit_columns = [
'metric_name', 'description', 'verbose_name', 'metric_type',
'expression', 'table', 'is_restricted']
description_columns = {
'expression': utils.markdown(
"a valid SQL expression as supported by the underlying backend. "
"Example: `count(DISTINCT userid)`", True),
'is_restricted': _("Whether the access to this metric is restricted "
"to certain roles. Only roles with the permission "
"'metric access on XXX (the name of this metric)' "
"are allowed to access this metric"),
}
add_columns = edit_columns
page_size = 500
label_columns = {
'metric_name': _("Metric"),
'description': _("Description"),
'verbose_name': _("Verbose Name"),
'metric_type': _("Type"),
'expression': _("SQL Expression"),
'table': _("Table"),
}
def post_add(self, new_item):
utils.init_metrics_perm(caravel, [new_item])
def register_views(self):
self.appbuilder.add_view_no_menu(ResetPasswordView())
self.appbuilder.add_view_no_menu(ResetMyPasswordView())
if self.auth_type == AUTH_DB:
self.user_view = self.userdbmodelview
self.auth_view = self.authdbview()
if self.auth_user_registration:
self.registeruser_view = self.registeruserdbview()
self.appbuilder.add_view_no_menu(self.registeruser_view)
elif self.auth_type == AUTH_LDAP:
self.user_view = self.userldapmodelview
self.auth_view = self.authldapview()
else:
self.user_view = self.useroidmodelview
self.auth_view = self.authoidview()
if self.auth_user_registration:
self.registeruser_view = self.registeruseroidview()
self.appbuilder.add_view_no_menu(self.registeruser_view)
self.appbuilder.add_view_no_menu(self.auth_view)
self.user_view = self.appbuilder.add_view(self.user_view,
"List Users",
icon="fa-user",
label=_("List Users"),
category="Security",
category_icon="fa-cogs",
category_label=_('Security'))
role_view = self.appbuilder.add_view(RoleModelView,
"List Roles",
icon="fa-group",
label=_('List Roles'),
category="Security",
category_icon="fa-cogs")
role_view.related_views = [self.user_view.__class__]
self.appbuilder.add_view(UserStatsChartView,
"User's Statistics",
icon="fa-bar-chart-o",
label=_("User's Statistics"),
category="Security")
self.appbuilder.menu.add_separator("Security")
self.appbuilder.add_view(PermissionModelView,
"Base Permissions",
icon="fa-lock",
label=_("Base Permissions"),
category="Security")
self.appbuilder.add_view(ViewMenuModelView,
"Views/Menus",
icon="fa-list-alt",
label=_('Views/Menus'),
category="Security")
self.appbuilder.add_view(PermissionViewModelView,
"Permission on Views/Menus",
icon="fa-link",
label=_('Permission on Views/Menus'),
category="Security")
class DruidMetricInlineView(CompactCRUDMixin, CaravelModelView): # noqa
datamodel = SQLAInterface(models.DruidMetric)
list_columns = ['metric_name', 'verbose_name', 'metric_type',
'is_restricted']
edit_columns = [
'metric_name', 'description', 'verbose_name', 'metric_type', 'json',
'datasource', 'is_restricted']
add_columns = edit_columns
page_size = 500
validators_columns = {
'json': [validate_json],
}
description_columns = {
'metric_type': utils.markdown(
"use `postagg` as the metric type if you are defining a "
"[Druid Post Aggregation]"
"(http://druid.io/docs/latest/querying/post-aggregations.html)",
True),
'is_restricted': _("Whether the access to this metric is restricted "
"to certain roles. Only roles with the permission "
"'metric access on XXX (the name of this metric)' "
"are allowed to access this metric"),
}
label_columns = {
'metric_name': _("Metric"),
'description': _("Description"),
'verbose_name': _("Verbose Name"),
'metric_type': _("Type"),
'json': _("JSON"),
'datasource': _("Druid Datasource"),
}
def post_add(self, new_item):
utils.init_metrics_perm(caravel, [new_item])
class TreemapViz(BaseViz):
"""Tree map visualisation for hierarchical data."""
viz_type = "treemap"
verbose_name = _("Treemap")
credits = '<a href="https://d3js.org">d3.js</a>'
is_timeseries = False
fieldsets = ({
'label': None,
'fields': (
'metrics',
'groupby',
),
}, {
'label': _('Chart Options'),
'fields': (
'treemap_ratio',
'number_format',
)
},)
def get_df(self, query_obj=None):
df = super(TreemapViz, self).get_df(query_obj)
df = df.set_index(self.form_data.get("groupby"))
return df
def _nest(self, metric, df):
nlevels = df.index.nlevels
if nlevels == 1:
result = [{"name": n, "value": v}
for n, v in zip(df.index, df[metric])]
else:
result = [{"name": l, "children": self._nest(metric, df.loc[l])}
for l in df.index.levels[0]]
return result
def get_data(self):
df = self.get_df()
chart_data = [{"name": metric, "children": self._nest(metric, df)}
for metric in df.columns]
return chart_data
def register_views(self):
self.appbuilder.add_view_no_menu(ResetPasswordView())
self.appbuilder.add_view_no_menu(ResetMyPasswordView())
if self.auth_type == AUTH_DB:
self.user_view = UserDBModelView
self.auth_view = AuthDBView()
elif self.auth_type == AUTH_LDAP:
self.user_view = UserLDAPModelView
self.auth_view = AuthLDAPView()
else:
self.user_view = UserOIDModelView
self.auth_view = AuthOIDView()
self.oid.after_login_func = self.auth_view.after_login
self.appbuilder.add_view_no_menu(self.auth_view)
self.user_view = self.appbuilder.add_view(self.user_view,
"List Users",
icon="fa-user",
label=_("List Users"),
category="Security",
category_icon="fa-cogs",
category_label=_('Security'))
role_view = self.appbuilder.add_view(RoleModelView,
"List Roles",
icon="fa-group",
label=_('List Roles'),
category="Security",
category_icon="fa-cogs")
role_view.related_views = [self.user_view.__class__]
self.appbuilder.add_view(UserStatsChartView,
"User's Statistics",
icon="fa-bar-chart-o",
label=_("User's Statistics"),
category="Security")
self.appbuilder.menu.add_separator("Security")
self.appbuilder.add_view(PermissionModelView,
"Base Permissions",
icon="fa-lock",
label=_("Base Permissions"),
category="Security")
self.appbuilder.add_view(ViewMenuModelView,
"Views/Menus",
icon="fa-list-alt",
label=_('Views/Menus'),
category="Security")
self.appbuilder.add_view(PermissionViewModelView,
"Permission on Views/Menus",
icon="fa-link",
label=_('Permission on Views/Menus'),
category="Security")
class DruidMetricInlineView(CompactCRUDMixin, CaravelModelView): # noqa
datamodel = SQLAInterface(models.DruidMetric)
list_columns = ['metric_name', 'verbose_name', 'metric_type']
edit_columns = [
'metric_name', 'description', 'verbose_name', 'metric_type', 'json',
'datasource']
add_columns = edit_columns
page_size = 500
validators_columns = {
'json': [validate_json],
}
description_columns = {
'metric_type': utils.markdown(
"use `postagg` as the metric type if you are defining a "
"[Druid Post Aggregation]"
"(http://druid.io/docs/latest/querying/post-aggregations.html)",
True),
}
label_columns = {
'metric_name': _("Metric"),
'description': _("Description"),
'verbose_name': _("Verbose Name"),
'metric_type': _("Type"),
'json': _("JSON"),
'datasource': _("Druid Datasource"),
}
class DirectedForceViz(BaseViz):
"""An animated directed force layout graph visualization"""
viz_type = "directed_force"
verbose_name = _("Directed Force Layout")
credits = 'd3noob @<a href="http://bl.ocks.org/d3noob/5141278">bl.ocks.org</a>'
is_timeseries = False
fieldsets = ({
'label': None,
'fields': (
'groupby',
'metric',
'row_limit',
)
}, {
'label': _('Force Layout'),
'fields': (
'link_length',
'charge',
)
},)
form_overrides = {
'groupby': {
'label': _('Source / Target'),
'description': _("Choose a source and a target"),
},
}
def query_obj(self):
qry = super(DirectedForceViz, self).query_obj()
if len(self.form_data['groupby']) != 2:
raise Exception("Pick exactly 2 columns to 'Group By'")
qry['metrics'] = [self.form_data['metric']]
return qry
def get_data(self):
df = self.get_df()
df.columns = ['source', 'target', 'value']
return df.to_dict(orient='records')
class DruidDatasourceModelView(CaravelModelView, DeleteMixin): # noqa
datamodel = SQLAInterface(models.DruidDatasource)
list_columns = [
'datasource_link', 'cluster', 'changed_by_', 'modified', 'offset'
]
related_views = [DruidColumnInlineView, DruidMetricInlineView]
edit_columns = [
'datasource_name', 'cluster', 'description', 'owner', 'is_featured',
'is_hidden', 'default_endpoint', 'offset', 'cache_timeout'
]
add_columns = edit_columns
page_size = 500
base_order = ('datasource_name', 'asc')
description_columns = {
'offset':
_("Timezone offset (in hours) for this datasource"),
'description':
Markup("Supports <a href='"
"https://daringfireball.net/projects/markdown/'>markdown</a>"),
}
label_columns = {
'datasource_name': _("Data Source"),
'cluster': _("Cluster"),
'description': _("Description"),
'owner': _("Owner"),
'is_featured': _("Is Featured"),
'is_hidden': _("Is Hidden"),
'default_endpoint': _("Default Endpoint"),
'offset': _("Time Offset"),
'cache_timeout': _("Cache Timeout"),
}
def post_add(self, datasource):
datasource.generate_metrics()
utils.merge_perm(sm, 'datasource_access', datasource.perm)
def post_update(self, datasource):
self.post_add(datasource)
def register_views(self):
self.baseapp.add_view_no_menu(ResetPasswordView())
self.baseapp.add_view_no_menu(ResetMyPasswordView())
if self._get_auth_type(self.baseapp.get_app) == AUTH_DB:
self.user_view = UserDBGeneralView
self.auth_view = AuthDBView()
elif self._get_auth_type(self.baseapp.get_app) == AUTH_LDAP:
self.user_view = UserLDAPGeneralView
self.auth_view = AuthLDAPView()
else:
self.user_view = UserOIDGeneralView
self.auth_view = AuthOIDView()
self.oid.after_login_func = self.auth_view.after_login
self.baseapp.add_view_no_menu(self.auth_view)
self.user_view = self.baseapp.add_view(self.user_view, "List Users",
icon="fa-user", label=_("List Users"),
category="Security", category_icon="fa-cogs", category_label=_('Security'))
role_view = self.baseapp.add_view(RoleGeneralView, "List Roles", icon="fa-group", label=_('List Roles'),
category="Security", category_icon="fa-cogs")
role_view.related_views = [self.user_view.__class__]
self.baseapp.add_view(UserStatsChartView,
"User's Statistics", icon="fa-bar-chart-o", label=_("User's Statistics"),
category="Security")
self.baseapp.menu.add_separator("Security")
self.baseapp.add_view(PermissionGeneralView,
"Base Permissions", icon="fa-lock",
label=_("Base Permissions"), category="Security")
self.baseapp.add_view(ViewMenuGeneralView,
"Views/Menus", icon="fa-list-alt",
label=_('Views/Menus'), category="Security")
self.baseapp.add_view(PermissionViewGeneralView,
"Permission on Views/Menus", icon="fa-link",
label=_('Permission on Views/Menus'), category="Security")
def register_views(self):
self.appbuilder.add_view_no_menu(self.resetpasswordview())
self.appbuilder.add_view_no_menu(self.resetmypasswordview())
if self.auth_type == AUTH_DB:
self.user_view = self.userdbmodelview
self.auth_view = self.authdbview()
if self.auth_user_registration:
pass
#self.registeruser_view = self.registeruserdbview()
#self.appbuilder.add_view_no_menu(self.registeruser_view)
elif self.auth_type == AUTH_LDAP:
self.user_view = self.userldapmodelview
self.auth_view = self.authldapview()
elif self.auth_type == AUTH_OAUTH:
self.user_view = self.useroauthmodelview
self.auth_view = self.authoauthview()
elif self.auth_type == AUTH_REMOTE_USER:
self.user_view = self.userremoteusermodelview
self.auth_view = self.authremoteuserview()
else:
self.user_view = self.useroidmodelview
self.auth_view = self.authoidview()
if self.auth_user_registration:
pass
#self.registeruser_view = self.registeruseroidview()
#self.appbuilder.add_view_no_menu(self.registeruser_view)
self.appbuilder.add_view_no_menu(self.auth_view)
self.user_view = self.appbuilder.add_view(self.user_view, "List Users",
icon="fa-user", label=_("List Users"),
category="Security", category_icon="fa-cogs",
category_label=_('Security'))
role_view = self.appbuilder.add_view(self.rolemodelview, "List Roles", icon="fa-group", label=_('List Roles'),
category="Security", category_icon="fa-cogs")
role_view.related_views = [self.user_view.__class__]
self.appbuilder.add_view(self.userstatschartview,
"User's Statistics", icon="fa-bar-chart-o", label=_("User's Statistics"),
category="Security")
self.appbuilder.menu.add_separator("Security")
self.appbuilder.add_view(self.permissionmodelview,
"Base Permissions", icon="fa-lock",
label=_("Base Permissions"), category="Security")
self.appbuilder.add_view(self.viewmenumodelview,
"Views/Menus", icon="fa-list-alt",
label=_('Views/Menus'), category="Security")
self.appbuilder.add_view(self.permissionviewmodelview,
"Permission on Views/Menus", icon="fa-link",
label=_('Permission on Views/Menus'), category="Security")
class MarkupViz(BaseViz):
"""Use html or markdown to create a free form widget"""
viz_type = "markup"
verbose_name = _("Markup")
fieldsets = ({'label': None, 'fields': ('markup_type', 'code')}, )
is_timeseries = False
def rendered(self):
markup_type = self.form_data.get("markup_type")
code = self.form_data.get("code", '')
if markup_type == "markdown":
return markdown(code)
elif markup_type == "html":
return code
def get_data(self):
return dict(html=self.rendered())
class BigNumberTotalViz(BaseViz):
"""Put emphasis on a single metric with this big number viz"""
viz_type = "big_number_total"
verbose_name = _("Big Number")
credits = 'a <a href="https://github.com/airbnb/caravel">Caravel</a> original'
is_timeseries = False
fieldsets = ({
'label': None,
'fields': (
'metric',
'subheader',
'y_axis_format',
)
},)
form_overrides = {
'y_axis_format': {
'label': 'Number format',
}
}
def reassignments(self):
metric = self.form_data.get('metric')
if not metric:
self.form_data['metric'] = self.orig_form_data.get('metrics')
def query_obj(self):
d = super(BigNumberTotalViz, self).query_obj()
metric = self.form_data.get('metric')
if not metric:
raise Exception("Pick a metric!")
d['metrics'] = [self.form_data.get('metric')]
self.form_data['metric'] = metric
return d
def get_data(self):
form_data = self.form_data
df = self.get_df()
df = df.sort(columns=df.columns[0])
return {
'data': df.values.tolist(),
'subheader': form_data.get('subheader', ''),
}
class DruidClusterModelView(CaravelModelView, DeleteMixin): # noqa
datamodel = SQLAInterface(models.DruidCluster)
add_columns = [
'cluster_name',
'coordinator_host', 'coordinator_port', 'coordinator_endpoint',
'broker_host', 'broker_port', 'broker_endpoint',
]
edit_columns = add_columns
list_columns = ['cluster_name', 'metadata_last_refreshed']
label_columns = {
'cluster_name': _("Cluster"),
'coordinator_host': _("Coordinator Host"),
'coordinator_port': _("Coordinator Port"),
'coordinator_endpoint': _("Coordinator Endpoint"),
'broker_host': _("Broker Host"),
'broker_port': _("Broker Port"),
'broker_endpoint': _("Broker Endpoint"),
}
class DatabaseView(CaravelModelView, DeleteMixin): # noqa
datamodel = SQLAInterface(models.Database)
list_columns = ['database_name', 'sql_link', 'creator', 'changed_on_']
add_columns = [
'database_name', 'sqlalchemy_uri', 'cache_timeout', 'extra']
search_exclude_columns = ('password',)
edit_columns = add_columns
add_template = "caravel/models/database/add.html"
edit_template = "caravel/models/database/edit.html"
base_order = ('changed_on', 'desc')
description_columns = {
'sqlalchemy_uri': (
"Refer to the SqlAlchemy docs for more information on how "
"to structure your URI here: "
"http://docs.sqlalchemy.org/en/rel_1_0/core/engines.html"),
'extra': utils.markdown(
"JSON string containing extra configuration elements. "
"The ``engine_params`` object gets unpacked into the "
"[sqlalchemy.create_engine]"
"(http://docs.sqlalchemy.org/en/latest/core/engines.html#"
"sqlalchemy.create_engine) call, while the ``metadata_params`` "
"gets unpacked into the [sqlalchemy.MetaData]"
"(http://docs.sqlalchemy.org/en/rel_1_0/core/metadata.html"
"#sqlalchemy.schema.MetaData) call. ", True),
}
label_columns = {
'database_name': _("Database"),
'sql_link': _("SQL link"),
'creator': _("Creator"),
'changed_on_': _("Last Changed"),
'sqlalchemy_uri': _("SQLAlchemy URI"),
'cache_timeout': _("Cache Timeout"),
'extra': _("Extra"),
}
def pre_add(self, db):
conn = sqla.engine.url.make_url(db.sqlalchemy_uri)
db.password = conn.password
conn.password = "******" * 10 if conn.password else None
db.sqlalchemy_uri = str(conn) # hides the password
def pre_update(self, db):
self.pre_add(db)
def runsql(self):
"""Runs arbitrary sql and returns and html table"""
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()
if (
not self.appbuilder.sm.has_access(
'all_datasource_access', 'all_datasource_access')):
raise utils.CaravelSecurityException(_(
"This view requires the `all_datasource_access` permission"))
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").split(' '))
except Exception as e:
content = (
'<div class="alert alert-danger">'
"{}</div>"
).format(e.message)
session.commit()
return content
class ParallelCoordinatesViz(BaseViz):
"""Interactive parallel coordinate implementation
Uses this amazing javascript library
https://github.com/syntagmatic/parallel-coordinates
"""
viz_type = "para"
verbose_name = _("Parallel Coordinates")
credits = (
'<a href="https://syntagmatic.github.io/parallel-coordinates/">'
'Syntagmatic\'s library</a>')
is_timeseries = False
fieldsets = ({
'label': None,
'fields': (
'series',
'metrics',
'secondary_metric',
'limit',
('show_datatable', 'include_series'),
)
},)
def query_obj(self):
d = super(ParallelCoordinatesViz, self).query_obj()
fd = self.form_data
d['metrics'] = copy.copy(fd.get('metrics'))
second = fd.get('secondary_metric')
if second not in d['metrics']:
d['metrics'] += [second]
d['groupby'] = [fd.get('series')]
return d
def get_data(self):
df = self.get_df()
return df.to_dict(orient="records")
def register_views(self):
self.appbuilder.add_view_no_menu(ResetPasswordView())
self.appbuilder.add_view_no_menu(ResetMyPasswordView())
if self.auth_type == AUTH_DB:
self.user_view = self.userdbmodelview
self.auth_view = self.authdbview()
if self.auth_user_registration:
self.registeruser_view = self.registeruserdbview()
self.appbuilder.add_view_no_menu(self.registeruser_view)
elif self.auth_type == AUTH_LDAP:
self.user_view = self.userldapmodelview
self.auth_view = self.authldapview()
else:
self.user_view = self.useroidmodelview
self.auth_view = self.authoidview()
if self.auth_user_registration:
self.registeruser_view = self.registeruseroidview()
self.appbuilder.add_view_no_menu(self.registeruser_view)
self.appbuilder.add_view_no_menu(self.auth_view)
self.user_view = self.appbuilder.add_view(self.user_view, "List Users",
icon="fa-user", label=_("List Users"),
category="Security", category_icon="fa-cogs",
category_label=_('Security'))
role_view = self.appbuilder.add_view(RoleModelView, "List Roles", icon="fa-group", label=_('List Roles'),
category="Security", category_icon="fa-cogs")
role_view.related_views = [self.user_view.__class__]
self.appbuilder.add_view(UserStatsChartView,
"User's Statistics", icon="fa-bar-chart-o", label=_("User's Statistics"),
category="Security")
self.appbuilder.menu.add_separator("Security")
self.appbuilder.add_view(PermissionModelView,
"Base Permissions", icon="fa-lock",
label=_("Base Permissions"), category="Security")
self.appbuilder.add_view(ViewMenuModelView,
"Views/Menus", icon="fa-list-alt",
label=_('Views/Menus'), category="Security")
self.appbuilder.add_view(PermissionViewModelView,
"Permission on Views/Menus", icon="fa-link",
label=_('Permission on Views/Menus'), category="Security")
log = logging.getLogger(__name__)
def aggregate(label=''):
"""
Use this decorator to set a label for your aggregation functions on charts.
:param label:
The label to complement with the column
"""
def wrap(f):
f._label = label
return f
return wrap
@aggregate(_('Count of'))
def aggregate_count(items, col):
"""
Function to use on Group by Charts.
accepts a list and returns the count of the list's items
"""
return len(list(items))
@aggregate(_('Sum of'))
def aggregate_sum(items, col):
"""
Function to use on Group by Charts.
accepts a list and returns the sum of the list's items
"""
return sum(getattr(item, col) for item in items)
}
def pre_add(self, db):
conn = sqla.engine.url.make_url(db.sqlalchemy_uri)
db.password = conn.password
conn.password = "******" * 10 if conn.password else None
db.sqlalchemy_uri = str(conn) # hides the password
def pre_update(self, db):
self.pre_add(db)
appbuilder.add_view(
DatabaseView,
"Databases",
label=_("Databases"),
icon="fa-database",
category="Sources",
category_label=_("Sources"),
category_icon='fa-database',)
class TableModelView(CaravelModelView, DeleteMixin): # noqa
datamodel = SQLAInterface(models.SqlaTable)
list_columns = [
'table_link', 'database', 'sql_link', 'is_featured',
'changed_by_', 'changed_on_']
add_columns = [
'table_name', 'database', 'schema',
'default_endpoint', 'offset', 'cache_timeout']
edit_columns = [
def query( # druid
self, groupby, metrics,
granularity,
from_dttm, to_dttm,
filter=None, # noqa
is_timeseries=True,
timeseries_limit=None,
row_limit=None,
inner_from_dttm=None, inner_to_dttm=None,
extras=None, # noqa
select=None,): # noqa
"""Runs a query against Druid and returns a dataframe.
This query interface is common to SqlAlchemy and Druid
"""
# TODO refactor into using a TBD Query object
qry_start_dttm = datetime.now()
inner_from_dttm = inner_from_dttm or from_dttm
inner_to_dttm = inner_to_dttm or to_dttm
# add tzinfo to native datetime with config
from_dttm = from_dttm.replace(tzinfo=config.get("DRUID_TZ"))
to_dttm = to_dttm.replace(tzinfo=config.get("DRUID_TZ"))
query_str = ""
metrics_dict = {m.metric_name: m for m in self.metrics}
all_metrics = []
post_aggs = {}
def recursive_get_fields(_conf):
_fields = _conf.get('fields', [])
field_names = []
for _f in _fields:
_type = _f.get('type')
if _type in ['fieldAccess', 'hyperUniqueCardinality']:
field_names.append(_f.get('fieldName'))
elif _type == 'arithmetic':
field_names += recursive_get_fields(_f)
return list(set(field_names))
for metric_name in metrics:
metric = metrics_dict[metric_name]
if metric.metric_type != 'postagg':
all_metrics.append(metric_name)
else:
conf = metric.json_obj
all_metrics += recursive_get_fields(conf)
all_metrics += conf.get('fieldNames', [])
if conf.get('type') == 'javascript':
post_aggs[metric_name] = JavascriptPostAggregator(
name=conf.get('name'),
field_names=conf.get('fieldNames'),
function=conf.get('function'))
else:
post_aggs[metric_name] = Postaggregator(
conf.get('fn', "/"),
conf.get('fields', []),
conf.get('name', ''))
aggregations = {
m.metric_name: m.json_obj
for m in self.metrics
if m.metric_name in all_metrics
}
rejected_metrics = [
m.metric_name for m in self.metrics
if m.is_restricted and
m.metric_name in aggregations.keys() and
not sm.has_access('metric_access', m.perm)
]
if rejected_metrics:
raise MetricPermException(
"Access to the metrics denied: " + ', '.join(rejected_metrics)
)
granularity = granularity or "all"
if granularity != "all":
granularity = utils.parse_human_timedelta(
granularity).total_seconds() * 1000
if not isinstance(granularity, string_types):
granularity = {"type": "duration", "duration": granularity}
origin = extras.get('druid_time_origin')
if origin:
dttm = utils.parse_human_datetime(origin)
granularity['origin'] = dttm.isoformat()
qry = dict(
datasource=self.datasource_name,
dimensions=groupby,
aggregations=aggregations,
granularity=granularity,
post_aggregations=post_aggs,
intervals=from_dttm.isoformat() + '/' + to_dttm.isoformat(),
)
filters = None
for col, op, eq in filter:
cond = None
if op == '==':
cond = Dimension(col) == eq
elif op == '!=':
cond = ~(Dimension(col) == eq)
elif op in ('in', 'not in'):
fields = []
splitted = eq.split(',')
if len(splitted) > 1:
for s in eq.split(','):
s = s.strip()
fields.append(Dimension(col) == s)
cond = Filter(type="or", fields=fields)
else:
cond = Dimension(col) == eq
if op == 'not in':
cond = ~cond
elif op == 'regex':
cond = Filter(type="regex", pattern=eq, dimension=col)
if filters:
filters = Filter(type="and", fields=[
cond,
filters
])
else:
filters = cond
if filters:
qry['filter'] = filters
client = self.cluster.get_pydruid_client()
orig_filters = filters
if timeseries_limit and is_timeseries:
# Limit on the number of timeseries, doing a two-phases query
pre_qry = deepcopy(qry)
pre_qry['granularity'] = "all"
pre_qry['limit_spec'] = {
"type": "default",
"limit": timeseries_limit,
'intervals': (
inner_from_dttm.isoformat() + '/' +
inner_to_dttm.isoformat()),
"columns": [{
"dimension": metrics[0] if metrics else self.metrics[0],
"direction": "descending",
}],
}
client.groupby(**pre_qry)
query_str += "// Two phase query\n// Phase 1\n"
query_str += json.dumps(
client.query_builder.last_query.query_dict, indent=2) + "\n"
query_str += "//\nPhase 2 (built based on phase one's results)\n"
df = client.export_pandas()
if df is not None and not df.empty:
dims = qry['dimensions']
filters = []
for unused, row in df.iterrows():
fields = []
for dim in dims:
f = Dimension(dim) == row[dim]
fields.append(f)
if len(fields) > 1:
filt = Filter(type="and", fields=fields)
filters.append(filt)
elif fields:
filters.append(fields[0])
if filters:
ff = Filter(type="or", fields=filters)
if not orig_filters:
qry['filter'] = ff
else:
qry['filter'] = Filter(type="and", fields=[
ff,
orig_filters])
qry['limit_spec'] = None
if row_limit:
qry['limit_spec'] = {
"type": "default",
"limit": row_limit,
"columns": [{
"dimension": metrics[0] if metrics else self.metrics[0],
"direction": "descending",
}],
}
client.groupby(**qry)
query_str += json.dumps(
client.query_builder.last_query.query_dict, indent=2)
df = client.export_pandas()
if df is None or df.size == 0:
raise Exception(_("No data was returned."))
if (
not is_timeseries and
granularity == "all" and
'timestamp' in df.columns):
del df['timestamp']
# Reordering columns
cols = []
if 'timestamp' in df.columns:
cols += ['timestamp']
cols += [col for col in groupby if col in df.columns]
cols += [col for col in metrics if col in df.columns]
df = df[cols]
return QueryResult(
df=df,
query=query_str,
duration=datetime.now() - qry_start_dttm)
def get_form(self):
"""Returns a form object based on the viz/datasource/context"""
viz = self.viz
field_css_classes = {}
for name, obj in self.field_dict.items():
field_css_classes[name] = ['form-control']
s = self.fieltype_class.get(obj.field_class)
if s:
field_css_classes[name] += [s]
for field in ('show_brush', 'show_legend', 'rich_tooltip'):
field_css_classes[field] += ['input-sm']
class QueryForm(OmgWtForm):
"""The dynamic form object used for the explore view"""
fieldsets = copy(viz.fieldsets)
css_classes = field_css_classes
standalone = HiddenField()
async = HiddenField()
force = HiddenField()
extra_filters = HiddenField()
json = HiddenField()
slice_id = HiddenField()
slice_name = HiddenField()
previous_viz_type = HiddenField(default=viz.viz_type)
collapsed_fieldsets = HiddenField()
viz_type = self.field_dict.get('viz_type')
for field in viz.flat_form_fields():
setattr(QueryForm, field, self.field_dict[field])
def add_to_form(attrs):
for attr in attrs:
setattr(QueryForm, attr, self.field_dict[attr])
filter_choices = self.choicify(['in', 'not in'])
having_op_choices = []
filter_prefixes = ['flt']
# datasource type specific form elements
datasource_classname = viz.datasource.__class__.__name__
time_fields = None
if datasource_classname == 'SqlaTable':
QueryForm.fieldsets += ({
'label': _('SQL'),
'fields': ['where', 'having'],
'description': _(
"This section exposes ways to include snippets of "
"SQL in your query"),
},)
add_to_form(('where', 'having'))
grains = viz.datasource.database.grains()
if grains:
grains_choices = [(grain.name, grain.label) for grain in grains]
time_fields = ('granularity_sqla', 'time_grain_sqla')
self.field_dict['time_grain_sqla'] = SelectField(
_('Time Grain'),
choices=grains_choices,
default="Time Column",
description=_(
"The time granularity for the visualization. This "
"applies a date transformation to alter "
"your time column and defines a new time granularity."
"The options here are defined on a per database "
"engine basis in the Caravel source code"))
add_to_form(time_fields)
field_css_classes['time_grain_sqla'] = ['form-control', 'select2']
field_css_classes['granularity_sqla'] = ['form-control', 'select2']
else:
time_fields = 'granularity_sqla'
add_to_form((time_fields, ))
elif datasource_classname == 'DruidDatasource':
time_fields = ('granularity', 'druid_time_origin')
add_to_form(('granularity', 'druid_time_origin'))
field_css_classes['granularity'] = ['form-control', 'select2_freeform']
field_css_classes['druid_time_origin'] = ['form-control', 'select2_freeform']
filter_choices = self.choicify(['in', 'not in', 'regex'])
having_op_choices = self.choicify(['>', '<', '=='])
filter_prefixes += ['having']
add_to_form(('since', 'until'))
filter_cols = self.choicify(
viz.datasource.filterable_column_names or [''])
having_cols = filter_cols + viz.datasource.metrics_combo
for field_prefix in filter_prefixes:
is_having_filter = field_prefix == 'having'
col_choices = filter_cols if not is_having_filter else having_cols
op_choices = filter_choices if not is_having_filter else \
having_op_choices
for i in range(10):
setattr(QueryForm, field_prefix + '_col_' + str(i),
SelectField(
_('Filter 1'),
default=col_choices[0][0],
choices=col_choices))
setattr(QueryForm, field_prefix + '_op_' + str(i), SelectField(
_('Filter 1'),
default=op_choices[0][0],
choices=op_choices))
setattr(
QueryForm, field_prefix + '_eq_' + str(i),
TextField(_("Super"), default=''))
if time_fields:
QueryForm.fieldsets = ({
'label': _('Time'),
'fields': (
time_fields,
('since', 'until'),
),
'description': _("Time related form attributes"),
},) + tuple(QueryForm.fieldsets)
return QueryForm
def __init__(self, viz):
self.viz = viz
from caravel.viz import viz_types
viz = self.viz
datasource = viz.datasource
if not datasource.metrics_combo:
raise Exception("Please define at least one metric for your table")
default_metric = datasource.metrics_combo[0][0]
gb_cols = datasource.groupby_column_names
default_groupby = gb_cols[0] if gb_cols else None
group_by_choices = self.choicify(gb_cols)
order_by_choices = []
for s in sorted(datasource.num_cols):
order_by_choices.append((json.dumps([s, True]), s + ' [asc]'))
order_by_choices.append((json.dumps([s, False]), s + ' [desc]'))
# Pool of all the fields that can be used in Caravel
field_data = {
'viz_type': (SelectField, {
"label": _("Viz"),
"default": 'table',
"choices": [(k, v.verbose_name) for k, v in viz_types.items()],
"description": _("The type of visualization to display")
}),
'metrics': (SelectMultipleSortableField, {
"label": _("Metrics"),
"choices": datasource.metrics_combo,
"default": [default_metric],
"description": _("One or many metrics to display")
}),
'order_by_cols': (SelectMultipleSortableField, {
"label": _("Ordering"),
"choices": order_by_choices,
"description": _("One or many metrics to display")
}),
'metric': (SelectField, {
"label": _("Metric"),
"choices": datasource.metrics_combo,
"default": default_metric,
"description": _("Choose the metric")
}),
'stacked_style': (SelectField, {
"label": _("Chart Style"),
"choices": (
('stack', _('stack')),
('stream', _('stream')),
('expand', _('expand')),
),
"default": 'stack',
"description": ""
}),
'linear_color_scheme': (SelectField, {
"label": _("Color Scheme"),
"choices": (
('fire', _('fire')),
('blue_white_yellow', _('blue_white_yellow')),
('white_black', _('white_black')),
('black_white', _('black_white')),
),
"default": 'blue_white_yellow',
"description": ""
}),
'normalize_across': (SelectField, {
"label": _("Normalize Across"),
"choices": (
('heatmap', _('heatmap')),
('x', _('x')),
('y', _('y')),
),
"default": 'heatmap',
"description": _(
"Color will be rendered based on a ratio "
"of the cell against the sum of across this "
"criteria")
}),
'horizon_color_scale': (SelectField, {
"label": _("Color Scale"),
"choices": (
('series', _('series')),
('overall', _('overall')),
('change', _('change')),
),
"default": 'series',
"description": _("Defines how the color are attributed.")
}),
'canvas_image_rendering': (SelectField, {
"label": _("Rendering"),
"choices": (
('pixelated', _('pixelated (Sharp)')),
('auto', _('auto (Smooth)')),
),
"default": 'pixelated',
"description": _(
"image-rendering CSS attribute of the canvas object that "
"defines how the browser scales up the image")
}),
'xscale_interval': (SelectField, {
"label": _("XScale Interval"),
"choices": self.choicify(range(1, 50)),
"default": '1',
"description": _(
"Number of step to take between ticks when "
"printing the x scale")
}),
'yscale_interval': (SelectField, {
"label": _("YScale Interval"),
"choices": self.choicify(range(1, 50)),
"default": '1',
"description": _(
"Number of step to take between ticks when "
"printing the y scale")
}),
'bar_stacked': (BetterBooleanField, {
"label": _("Stacked Bars"),
"default": False,
"description": ""
}),
'show_controls': (BetterBooleanField, {
"label": _("Extra Controls"),
"default": False,
"description": (
"Whether to show extra controls or not. Extra controls "
"include things like making mulitBar charts stacked "
"or side by side.")
}),
'reduce_x_ticks': (BetterBooleanField, {
"label": _("Reduce X ticks"),
"default": False,
"description": _(
"Reduces the number of X axis ticks to be rendered. "
"If true, the x axis wont overflow and labels may be "
"missing. If false, a minimum width will be applied "
"to columns and the width may overflow into an "
"horizontal scroll."),
}),
'include_series': (BetterBooleanField, {
"label": _("Include Series"),
"default": False,
"description": _("Include series name as an axis")
}),
'secondary_metric': (SelectField, {
"label": _("Color Metric"),
"choices": datasource.metrics_combo,
"default": default_metric,
"description": _("A metric to use for color")
}),
'country_fieldtype': (SelectField, {
"label": _("Country Field Type"),
"default": 'cca2',
"choices": (
('name', _('Full name')),
('cioc', _('code International Olympic Committee (cioc)')),
('cca2', _('code ISO 3166-1 alpha-2 (cca2)')),
('cca3', _('code ISO 3166-1 alpha-3 (cca3)')),
),
"description": _(
"The country code standard that Caravel should expect "
"to find in the [country] column")
}),
'groupby': (SelectMultipleSortableField, {
"label": _("Group by"),
"choices": self.choicify(datasource.groupby_column_names),
"description": _("One or many fields to group by")
}),
'columns': (SelectMultipleSortableField, {
"label": _("Columns"),
"choices": self.choicify(datasource.groupby_column_names),
"description": _("One or many fields to pivot as columns")
}),
'all_columns': (SelectMultipleSortableField, {
"label": _("Columns"),
"choices": self.choicify(datasource.column_names),
"description": _("Columns to display")
}),
'all_columns_x': (SelectField, {
"label": _("X"),
"choices": self.choicify(datasource.column_names),
"description": _("Columns to display")
}),
'all_columns_y': (SelectField, {
"label": _("Y"),
"choices": self.choicify(datasource.column_names),
"description": _("Columns to display")
}),
'druid_time_origin': (FreeFormSelectField, {
"label": _( "Origin"),
"choices": (
('', _('default')),
('now', _('now')),
),
"default": '',
"description": _(
"Defines the origin where time buckets start, "
"accepts natural dates as in 'now', 'sunday' or '1970-01-01'")
}),
'bottom_margin': (FreeFormSelectField, {
"label": _("Bottom Margin"),
"choices": self.choicify([50, 75, 100, 125, 150, 200]),
"default": 50,
"description": _(
"Bottom marging, in pixels, allowing for more room for "
"axis labels"),
}),
'granularity': (FreeFormSelectField, {
"label": _("Time Granularity"),
"default": "one day",
"choices": (
('all', _('all')),
('5 seconds', _('5 seconds')),
('30 seconds', _('30 seconds')),
('1 minute', _('1 minute')),
('5 minutes', _('5 minutes')),
('1 hour', _('1 hour')),
('6 hour', _('6 hour')),
('1 day', _('1 day')),
('7 days', _('7 days')),
),
"description": _(
"The time granularity for the visualization. Note that you "
"can type and use simple natural language as in '10 seconds', "
"'1 day' or '56 weeks'")
}),
'domain_granularity': (SelectField, {
"label": _("Domain"),
"default": "month",
"choices": (
('hour', _('hour')),
('day', _('day')),
('week', _('week')),
('month', _('month')),
('year', _('year')),
),
"description": _(
"The time unit used for the grouping of blocks")
}),
'subdomain_granularity': (SelectField, {
"label": _("Subdomain"),
"default": "day",
"choices": (
('min', _('min')),
('hour', _('hour')),
('day', _('day')),
('week', _('week')),
('month', _('month')),
),
"description": _(
"The time unit for each block. Should be a smaller unit than "
"domain_granularity. Should be larger or equal to Time Grain")
}),
'link_length': (FreeFormSelectField, {
"label": _("Link Length"),
"default": "200",
"choices": self.choicify([
'10',
'25',
'50',
'75',
'100',
'150',
'200',
'250',
]),
"description": _("Link length in the force layout")
}),
'charge': (FreeFormSelectField, {
"label": _("Charge"),
"default": "-500",
"choices": self.choicify([
'-50',
'-75',
'-100',
'-150',
'-200',
'-250',
'-500',
'-1000',
'-2500',
'-5000',
]),
"description": _("Charge in the force layout")
}),
'granularity_sqla': (SelectField, {
"label": _("Time Column"),
"default": datasource.main_dttm_col or datasource.any_dttm_col,
"choices": self.choicify(datasource.dttm_cols),
"description": _(
"The time column for the visualization. Note that you "
"can define arbitrary expression that return a DATETIME "
"column in the table editor. Also note that the "
"filter bellow is applied against this column or "
"expression")
}),
'resample_rule': (FreeFormSelectField, {
"label": _("Resample Rule"),
"default": '',
"choices": (
('1T', _('1T')),
('1H', _('1H')),
('1D', _('1D')),
('7D', _('7D')),
('1M', _('1M')),
('1AS', _('1AS')),
),
"description": _("Pandas resample rule")
}),
'resample_how': (FreeFormSelectField, {
"label": _("Resample How"),
"default": '',
"choices": (
('', ''),
('mean', _('mean')),
('sum', _('sum')),
('median', _('median')),
),
"description": _("Pandas resample how")
}),
'resample_fillmethod': (FreeFormSelectField, {
"label": _("Resample Fill Method"),
"default": '',
"choices": (
('', ''),
('ffill', _('ffill')),
('bfill', _('bfill')),
),
"description": _("Pandas resample fill method")
}),
'since': (FreeFormSelectField, {
"label": _("Since"),
"default": "7 days ago",
"choices": (
('1 hour ago', _('1 hour ago')),
('12 hours ago', _('12 hours ago')),
('1 day ago', _('1 day ago')),
('7 days ago', _('7 days ago')),
('28 days ago', _('28 days ago')),
('90 days ago', _('90 days ago')),
('1 year ago', _('1 year ago')),
),
"description": _(
"Timestamp from filter. This supports free form typing and "
"natural language as in '1 day ago', '28 days' or '3 years'")
}),
'until': (FreeFormSelectField, {
"label": _("Until"),
"default": "now",
"choices": (
('now', _('now')),
('1 day ago', _('1 day ago')),
('7 days ago', _('7 days ago')),
('28 days ago', _('28 days ago')),
('90 days ago', _('90 days ago')),
('1 year ago', _('1 year ago')),
)
}),
'max_bubble_size': (FreeFormSelectField, {
"label": _("Max Bubble Size"),
"default": "25",
"choices": self.choicify([
'5',
'10',
'15',
'25',
'50',
'75',
'100',
])
}),
'whisker_options': (FreeFormSelectField, {
"label": _("Whisker/outlier options"),
"default": "Tukey",
"description": _(
"Determines how whiskers and outliers are calculated."),
"choices": (
('Tukey', _('Tukey')),
('Min/max (no outliers)', _('Min/max (no outliers)')),
('2/98 percentiles', _('2/98 percentiles')),
('9/91 percentiles', _('9/91 percentiles')),
)
}),
'treemap_ratio': (DecimalField, {
"label": _("Ratio"),
"default": 0.5 * (1 + math.sqrt(5)), # d3 default, golden ratio
"description": _('Target aspect ratio for treemap tiles.'),
}),
'number_format': (FreeFormSelectField, {
"label": _("Number format"),
"default": '.3s',
"choices": [
('.3s', '".3s" | 12.3k'),
('.3%', '".3%" | 1234543.210%'),
('.4r', '".4r" | 12350'),
('.3f', '".3f" | 12345.432'),
('+,', '"+," | +12,345.4321'),
('$,.2f', '"$,.2f" | $12,345.43'),
],
"description": _("D3 format syntax for numbers "
"https: //github.com/mbostock/\n"
"d3/wiki/Formatting")
}),
'row_limit': (FreeFormSelectField, {
"label": _('Row limit'),
"default": config.get("ROW_LIMIT"),
"choices": self.choicify(
[10, 50, 100, 250, 500, 1000, 5000, 10000, 50000])
}),
'limit': (FreeFormSelectField, {
"label": _('Series limit'),
"choices": self.choicify(self.series_limits),
"default": 50,
"description": _(
"Limits the number of time series that get displayed")
}),
'rolling_type': (SelectField, {
"label": _("Rolling"),
"default": 'None',
"choices": [(s, s) for s in ['None', 'mean', 'sum', 'std', 'cumsum']],
"description": _(
"Defines a rolling window function to apply, works along "
"with the [Periods] text box")
}),
'rolling_periods': (IntegerField, {
"label": _("Periods"),
"validators": [validators.optional()],
"description": _(
"Defines the size of the rolling window function, "
"relative to the time granularity selected")
}),
'series': (SelectField, {
"label": _("Series"),
"choices": group_by_choices,
"default": default_groupby,
"description": _(
"Defines the grouping of entities. "
"Each serie is shown as a specific color on the chart and "
"has a legend toggle")
}),
'entity': (SelectField, {
"label": _("Entity"),
"choices": group_by_choices,
"default": default_groupby,
"description": _("This define the element to be plotted on the chart")
}),
'x': (SelectField, {
"label": _("X Axis"),
"choices": datasource.metrics_combo,
"default": default_metric,
"description": _("Metric assigned to the [X] axis")
}),
'y': (SelectField, {
"label": _("Y Axis"),
"choices": datasource.metrics_combo,
"default": default_metric,
"description": _("Metric assigned to the [Y] axis")
}),
'size': (SelectField, {
"label": _('Bubble Size'),
"default": default_metric,
"choices": datasource.metrics_combo
}),
'url': (TextField, {
"label": _("URL"),
"description": _(
"The URL, this field is templated, so you can integrate "
"{{ width }} and/or {{ height }} in your URL string."
),
"default": 'https: //www.youtube.com/embed/JkI5rg_VcQ4',
}),
'x_axis_label': (TextField, {
"label": _("X Axis Label"),
"default": '',
}),
'y_axis_label': (TextField, {
"label": _("Y Axis Label"),
"default": '',
}),
'where': (TextField, {
"label": _("Custom WHERE clause"),
"default": '',
"description": _(
"The text in this box gets included in your query's WHERE "
"clause, as an AND to other criteria. You can include "
"complex expression, parenthesis and anything else "
"supported by the backend it is directed towards.")
}),
'having': (TextField, {
"label": _("Custom HAVING clause"),
"default": '',
"description": _(
"The text in this box gets included in your query's HAVING"
" clause, as an AND to other criteria. You can include "
"complex expression, parenthesis and anything else "
"supported by the backend it is directed towards.")
}),
'compare_lag': (TextField, {
"label": _("Comparison Period Lag"),
"description": _(
"Based on granularity, number of time periods to "
"compare against")
}),
'compare_suffix': (TextField, {
"label": _("Comparison suffix"),
"description": _("Suffix to apply after the percentage display")
}),
'table_timestamp_format': (FreeFormSelectField, {
"label": _("Table Timestamp Format"),
"default": 'smart_date',
"choices": TIMESTAMP_CHOICES,
"description": _("Timestamp Format")
}),
'series_height': (FreeFormSelectField, {
"label": _("Series Height"),
"default": 25,
"choices": self.choicify([10, 25, 40, 50, 75, 100, 150, 200]),
"description": _("Pixel height of each series")
}),
'x_axis_format': (FreeFormSelectField, {
"label": _("X axis format"),
"default": 'smart_date',
"choices": TIMESTAMP_CHOICES,
"description": _("D3 format syntax for y axis "
"https: //github.com/mbostock/\n"
"d3/wiki/Formatting")
}),
'y_axis_format': (FreeFormSelectField, {
"label": _("Y axis format"),
"default": '.3s',
"choices": [
('.3s', '".3s" | 12.3k'),
('.3%', '".3%" | 1234543.210%'),
('.4r', '".4r" | 12350'),
('.3f', '".3f" | 12345.432'),
('+,', '"+," | +12,345.4321'),
('$,.2f', '"$,.2f" | $12,345.43'),
],
"description": _("D3 format syntax for y axis "
"https: //github.com/mbostock/\n"
"d3/wiki/Formatting")
}),
'markup_type': (SelectField, {
"label": _("Markup Type"),
"choices": (
('markdown', _('markdown')),
('html', _('html'))
),
"default": "markdown",
"description": _("Pick your favorite markup language")
}),
'rotation': (SelectField, {
"label": _("Rotation"),
"choices": (
('random', _('random')),
('flat', _('flat')),
('square', _('square')),
),
"default": "random",
"description": _("Rotation to apply to words in the cloud")
}),
'line_interpolation': (SelectField, {
"label": _("Line Style"),
"choices": (
('linear', _('linear')),
('basis', _('basis')),
('cardinal', _('cardinal')),
('monotone', _('monotone')),
('step-before', _('step-before')),
('step-after', _('step-after')),
),
"default": 'linear',
"description": _("Line interpolation as defined by d3.js")
}),
'code': (TextAreaField, {
"label": _("Code"),
"description": _("Put your code here"),
"default": ''
}),
'pandas_aggfunc': (SelectField, {
"label": _("Aggregation function"),
"choices": (
('sum', _('sum')),
('mean', _('mean')),
('min', _('min')),
('max', _('max')),
('median', _('median')),
('stdev', _('stdev')),
('var', _('var')),
),
"default": 'sum',
"description": _(
"Aggregate function to apply when pivoting and "
"computing the total rows and columns")
}),
'size_from': (TextField, {
"label": _("Font Size From"),
"default": "20",
"description": _("Font size for the smallest value in the list")
}),
'size_to': (TextField, {
"label": _("Font Size To"),
"default": "150",
"description": _("Font size for the biggest value in the list")
}),
'show_brush': (BetterBooleanField, {
"label": _("Range Filter"),
"default": False,
"description": _(
"Whether to display the time range interactive selector")
}),
'show_datatable': (BetterBooleanField, {
"label": _("Data Table"),
"default": False,
"description": _("Whether to display the interactive data table")
}),
'include_search': (BetterBooleanField, {
"label": _("Search Box"),
"default": False,
"description": _(
"Whether to include a client side search box")
}),
'show_bubbles': (BetterBooleanField, {
"label": _("Show Bubbles"),
"default": False,
"description": _(
"Whether to display bubbles on top of countries")
}),
'show_legend': (BetterBooleanField, {
"label": _("Legend"),
"default": True,
"description": _("Whether to display the legend (toggles)")
}),
'x_axis_showminmax': (BetterBooleanField, {
"label": _("X bounds"),
"default": True,
"description": _(
"Whether to display the min and max values of the X axis")
}),
'rich_tooltip': (BetterBooleanField, {
"label": _("Rich Tooltip"),
"default": True,
"description": _(
"The rich tooltip shows a list of all series for that"
" point in time")
}),
'y_axis_zero': (BetterBooleanField, {
"label": _("Y Axis Zero"),
"default": False,
"description": _(
"Force the Y axis to start at 0 instead of the minimum "
"value")
}),
'y_log_scale': (BetterBooleanField, {
"label": _("Y Log"),
"default": False,
"description": _("Use a log scale for the Y axis")
}),
'x_log_scale': (BetterBooleanField, {
"label": _("X Log"),
"default": False,
"description": _("Use a log scale for the X axis")
}),
'donut': (BetterBooleanField, {
"label": _("Donut"),
"default": False,
"description": _("Do you want a donut or a pie?")
}),
'contribution': (BetterBooleanField, {
"label": _("Contribution"),
"default": False,
"description": _("Compute the contribution to the total")
}),
'num_period_compare': (IntegerField, {
"label": _("Period Ratio"),
"default": None,
"validators": [validators.optional()],
"description": _(
"[integer] Number of period to compare against, "
"this is relative to the granularity selected")
}),
'time_compare': (TextField, {
"label": _("Time Shift"),
"default": "",
"description": _(
"Overlay a timeseries from a "
"relative time period. Expects relative time delta "
"in natural language (example: 24 hours, 7 days, "
"56 weeks, 365 days")
}),
'subheader': (TextField, {
"label": _("Subheader"),
"description": _(
"Description text that shows up below your Big "
"Number")
}),
'mapbox_label': (SelectMultipleSortableField, {
"label": "Label",
"choices": self.choicify(["count"] + datasource.column_names),
"description": _(
"'count' is COUNT(*) if a group by is used. "
"Numerical columns will be aggregated with the aggregator. "
"Non-numerical columns will be used to label points. "
"Leave empty to get a count of points in each cluster."),
}),
'mapbox_style': (SelectField, {
"label": "Map Style",
"choices": [
("mapbox://styles/mapbox/streets-v9", "Streets"),
("mapbox://styles/mapbox/dark-v9", "Dark"),
("mapbox://styles/mapbox/light-v9", "Light"),
("mapbox://styles/mapbox/satellite-streets-v9", "Satellite Streets"),
("mapbox://styles/mapbox/satellite-v9", "Satellite"),
("mapbox://styles/mapbox/outdoors-v9", "Outdoors"),
],
"description": _("Base layer map style")
}),
'clustering_radius': (FreeFormSelectField, {
"label": _("Clustering Radius"),
"default": "60",
"choices": self.choicify([
'0',
'20',
'40',
'60',
'80',
'100',
'200',
'500',
'1000',
]),
"description": _(
"The radius (in pixels) the algorithm uses to define a cluster. "
"Choose 0 to turn off clustering, but beware that a large "
"number of points (>1000) will cause lag.")
}),
'point_radius': (SelectField, {
"label": _("Point Radius"),
"default": "Auto",
"choices": self.choicify(["Auto"] + datasource.column_names),
"description": _(
"The radius of individual points (ones that are not in a cluster). "
"Either a numerical column or 'Auto', which scales the point based "
"on the largest cluster")
}),
'point_radius_unit': (SelectField, {
"label": _("Point Radius Unit"),
"default": "Pixels",
"choices": self.choicify([
"Pixels",
"Miles",
"Kilometers",
]),
"description": _("The unit of measure for the specified point radius")
}),
'global_opacity': (DecimalField, {
"label": _("Opacity"),
"default": 1,
"description": _(
"Opacity of all clusters, points, and labels. "
"Between 0 and 1."),
}),
'viewport_zoom': (DecimalField, {
"label": _("Zoom"),
"default": 11,
"validators": [validators.optional()],
"description": _("Zoom level of the map"),
"places": 8,
}),
'viewport_latitude': (DecimalField, {
"label": _("Default latitude"),
"default": 37.772123,
"description": _("Latitude of default viewport"),
"places": 8,
}),
'viewport_longitude': (DecimalField, {
"label": _("Default longitude"),
"default": -122.405293,
"description": _("Longitude of default viewport"),
"places": 8,
}),
'render_while_dragging': (BetterBooleanField, {
"label": _("Live render"),
"default": True,
"description": _("Points and clusters will update as viewport "
"is being changed")
}),
'mapbox_color': (FreeFormSelectField, {
"label": _("RGB Color"),
"default": "rgb(0, 122, 135)",
"choices": [
("rgb(0, 139, 139)", "Dark Cyan"),
("rgb(128, 0, 128)", "Purple"),
("rgb(255, 215, 0)", "Gold"),
("rgb(69, 69, 69)", "Dim Gray"),
("rgb(220, 20, 60)", "Crimson"),
("rgb(34, 139, 34)", "Forest Green"),
],
"description": _("The color for points and clusters in RGB")
}),
}
# Override default arguments with form overrides
for field_name, override_map in viz.form_overrides.items():
if field_name in field_data:
field_data[field_name][1].update(override_map)
self.field_dict = {
field_name: v[0](**v[1])
for field_name, v in field_data.items()
}
}
def pre_add(self, db):
conn = sqla.engine.url.make_url(db.sqlalchemy_uri)
db.password = conn.password
conn.password = "******" * 10 if conn.password else None
db.sqlalchemy_uri = str(conn) # hides the password
def pre_update(self, db):
self.pre_add(db)
appbuilder.add_view(
DatabaseView,
"Databases",
label=_("Databases"),
icon="fa-database",
category=_("Sources"),
category_icon='fa-database',)
class TableModelView(CaravelModelView, DeleteMixin): # noqa
datamodel = SQLAInterface(models.SqlaTable)
list_columns = [
'table_link', 'database', 'sql_link', 'is_featured',
'changed_by_', 'changed_on_']
add_columns = [
'table_name', 'database', 'schema',
'default_endpoint', 'offset', 'cache_timeout']
edit_columns = [
'table_name', 'is_featured', 'database', 'schema', 'description', 'owner',
def query( # sqla
self, groupby, metrics,
granularity,
from_dttm, to_dttm,
filter=None, # noqa
is_timeseries=True,
timeseries_limit=15, row_limit=None,
inner_from_dttm=None, inner_to_dttm=None,
extras=None,
columns=None):
"""Querying any sqla table from this common interface"""
# For backward compatibility
if granularity not in self.dttm_cols:
granularity = self.main_dttm_col
cols = {col.column_name: col for col in self.columns}
qry_start_dttm = datetime.now()
if not granularity and is_timeseries:
raise Exception(_(
"Datetime column not provided as part table configuration "
"and is required by this type of chart"))
metrics_exprs = [
m.sqla_col
for m in self.metrics if m.metric_name in metrics]
if metrics:
main_metric_expr = [
m.sqla_col for m in self.metrics
if m.metric_name == metrics[0]][0]
else:
main_metric_expr = literal_column("COUNT(*)").label("ccount")
select_exprs = []
groupby_exprs = []
if groupby:
select_exprs = []
inner_select_exprs = []
inner_groupby_exprs = []
for s in groupby:
col = cols[s]
outer = col.sqla_col
inner = col.sqla_col.label(col.column_name + '__')
groupby_exprs.append(outer)
select_exprs.append(outer)
inner_groupby_exprs.append(inner)
inner_select_exprs.append(inner)
elif columns:
for s in columns:
select_exprs.append(cols[s].sqla_col)
metrics_exprs = []
if granularity:
dttm_expr = cols[granularity].sqla_col.label('timestamp')
timestamp = dttm_expr
# Transforming time grain into an expression based on configuration
time_grain_sqla = extras.get('time_grain_sqla')
if time_grain_sqla:
udf = self.database.grains_dict().get(time_grain_sqla, '{col}')
timestamp_grain = literal_column(
udf.function.format(col=dttm_expr)).label('timestamp')
else:
timestamp_grain = timestamp
if is_timeseries:
select_exprs += [timestamp_grain]
groupby_exprs += [timestamp_grain]
tf = '%Y-%m-%d %H:%M:%S.%f'
time_filter = [
timestamp >= text(self.database.dttm_converter(from_dttm)),
timestamp <= text(self.database.dttm_converter(to_dttm)),
]
inner_time_filter = copy(time_filter)
if inner_from_dttm:
inner_time_filter[0] = timestamp >= text(
self.database.dttm_converter(inner_from_dttm))
if inner_to_dttm:
inner_time_filter[1] = timestamp <= text(
self.database.dttm_converter(inner_to_dttm))
else:
inner_time_filter = []
select_exprs += metrics_exprs
qry = select(select_exprs)
tbl = table(self.table_name)
if self.schema:
tbl.schema = self.schema
if not columns:
qry = qry.group_by(*groupby_exprs)
where_clause_and = []
having_clause_and = []
for col, op, eq in filter:
col_obj = cols[col]
if op in ('in', 'not in'):
values = eq.split(",")
cond = col_obj.sqla_col.in_(values)
if op == 'not in':
cond = ~cond
where_clause_and.append(cond)
if extras and 'where' in extras:
where_clause_and += [text(extras['where'])]
if extras and 'having' in extras:
having_clause_and += [text(extras['having'])]
if granularity:
qry = qry.where(and_(*(time_filter + where_clause_and)))
else:
qry = qry.where(and_(*where_clause_and))
qry = qry.having(and_(*having_clause_and))
if groupby:
qry = qry.order_by(desc(main_metric_expr))
qry = qry.limit(row_limit)
if timeseries_limit and groupby:
subq = select(inner_select_exprs)
subq = subq.select_from(tbl)
subq = subq.where(and_(*(where_clause_and + inner_time_filter)))
subq = subq.group_by(*inner_groupby_exprs)
subq = subq.order_by(desc(main_metric_expr))
subq = subq.limit(timeseries_limit)
on_clause = []
for i, gb in enumerate(groupby):
on_clause.append(
groupby_exprs[i] == column(gb + '__'))
tbl = tbl.join(subq.alias(), and_(*on_clause))
qry = qry.select_from(tbl)
engine = self.database.get_sqla_engine()
sql = "{}".format(
qry.compile(
engine, compile_kwargs={"literal_binds": True},),
)
df = pd.read_sql_query(
sql=sql,
con=engine
)
sql = sqlparse.format(sql, reindent=True)
return QueryResult(
df=df, duration=datetime.now() - qry_start_dttm, query=sql)
from flask_babelpkg import lazy_gettext as _
from .forms import MyForm
from app import appbuilder, db
class MyFormView(SimpleFormView):
form = MyForm
form_title = 'This is my first form view'
message = 'My form was submitted'
def form_get(self, form):
form.field1.data = 'This was prefilled'
def form_post(self, form):
# post process form
flash(self.message, 'info')
appbuilder.add_view(MyFormView, "My form View", icon="fa-group", label=_('My form View'),
category="My Forms", category_icon="fa-cogs")
"""
Application wide 404 error handler
"""
@appbuilder.app.errorhandler(404)
def page_not_found(e):
return render_template('404.html', base_template=appbuilder.base_template, appbuilder=appbuilder), 404
db.create_all()
