Superset 表格下钻
基于时间维度,低于维度和普通维度
1, 左侧控制栏开发
assets/src/explore/controlPanels/DrillTable.js
/**
* Licensed to the Apache Software Foundation (ASF) under one
* or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information
* regarding copyright ownership. The ASF licenses this file
* to you under the Apache License, Version 2.0 (the
* "License"); you may not use this file except in compliance
* with the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing,
* software distributed under the License is distributed on an
* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
* KIND, either express or implied. See the License for the
* specific language governing permissions and limitations
* under the License.
*/
import {t} from '@superset-ui/translation';
export default {
controlPanelSections: [
{
label: t('GROUP BY'),
description: t('Use this section if you want a query that aggregates'),
expanded: true,
controlSetRows: [
['time_by'],
['area_by'],
['groupby'],
['metrics'],
['timeseries_limit_metric', 'row_limit'],
['include_time', 'order_desc'],
],
},
// {
// label: t('NOT GROUPED BY'),
// description: t('Use this section if you want to query atomic rows'),
// expanded: true,
// controlSetRows: [
// ['all_columns'],
// ['order_by_cols'],
// ['row_limit', null],
// ],
// },
{
label: t('Query'),
expanded: true,
controlSetRows: [
['adhoc_filters'],
],
},
{
label: t('Options'),
expanded: true,
controlSetRows: [
['table_timestamp_format'],
['page_length', null],
['include_search', 'table_filter'],
['align_pn', 'color_pn'],
],
},
],
controlOverrides: {
metrics: {
validators: [],
},
},
};
相对于地图开发,里面新增了两个分组 Time By
和 Area By
,因此需要实现time_by和area_by组件的开发
assets/src/explore/controls.jsx
.......
normalized: {
type: 'CheckboxControl',
label: t('Normalized'),
renderTrigger: true,
description: t('Whether to normalize the histogram'),
default: false,
},
area_by: {
...groupByControl,
multi: true, // 多选
clearable: true, // 是否可调用, true当作sql
validators: [], // 是否可以为空
label: t('第二维度分组'),
description: t('第一维度分组'),
},
2, 左侧控制栏注册
assets/src/explore/controlPanels/index.js 参考修改即可
3, 右侧渲染层开发
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pYGbvIO0-1596185979530)(images/Superset/image-20200731164744737.png)]
1, utils/fixTableHeight.js 管理分页和排序
export default function fixTableHeight($tableDom, height) {
const headHeight = $tableDom.find('.dataTables_scrollHead').height();
const filterHeight = $tableDom.find('.dataTables_filter').height() || 0;
const pageLengthHeight = $tableDom.find('.dataTables_length').height() || 0;
const paginationHeight = $tableDom.find('.dataTables_paginate').height() || 0;
const controlsHeight = pageLengthHeight > filterHeight ? pageLengthHeight : filterHeight;
$tableDom
.find('.dataTables_scrollBody')
.css('max-height', height - headHeight - controlsHeight - paginationHeight);
}
2, DrillTable.js下钻的主要渲染逻辑
里面采用了ajax动态加载数据,动态加载数据,对于后端接口有一定要求,见接口部分
import d3 from 'd3';
import PropTypes from 'prop-types';
import dt from 'datatables.net-bs/js/dataTables.bootstrap';
import dompurify from 'dompurify';
import {getNumberFormatter, NumberFormats} from '@superset-ui/number-format';
import {getTimeFormatter} from '@superset-ui/time-format';
import fixTableHeight from './utils/fixTableHeight';
import 'datatables.net-bs/css/dataTables.bootstrap.css';
import './Table.css';
if (window.$) {
dt(window, window.$);
}
const $ = window.$ || dt.$;
const propTypes = {
// Each object is { field1: value1, field2: value2 }
data: PropTypes.arrayOf(PropTypes.object),
height: PropTypes.number,
alignPositiveNegative: PropTypes.bool,
colorPositiveNegative: PropTypes.bool,
columns: PropTypes.arrayOf(
PropTypes.shape({
key: PropTypes.string,
label: PropTypes.string,
format: PropTypes.string,
}),
),
filters: PropTypes.object,
includeSearch: PropTypes.bool,
metrics: PropTypes.arrayOf(PropTypes.oneOfType([PropTypes.string, PropTypes.object])),
onAddFilter: PropTypes.func,
onRemoveFilter: PropTypes.func,
orderDesc: PropTypes.bool,
pageLength: PropTypes.oneOfType([PropTypes.number, PropTypes.string]),
percentMetrics: PropTypes.arrayOf(PropTypes.oneOfType([PropTypes.string, PropTypes.object])),
tableFilter: PropTypes.bool,
tableTimestampFormat: PropTypes.string,
timeseriesLimitMetric: PropTypes.oneOfType([PropTypes.string, PropTypes.object]),
};
const formatValue = getNumberFormatter(NumberFormats.INTEGER);
const formatPercent = getNumberFormatter(NumberFormats.PERCENT_3_POINT);
function NOOP() {
}
function TableVis(element, props) {
const {
data,
height,
alignPositiveNegative = false,
colorPositiveNegative = false,
columns,
filters = {},
includeSearch = false,
metrics: rawMetrics,
onAddFilter = NOOP,
onRemoveFilter = NOOP,
orderDesc,
pageLength,
percentMetrics,
tableFilter,
tableTimestampFormat,
timeseriesLimitMetric,
timeBy,
areaBy,
groupby,
sliceId,
formData
} = props;
// 取消浏览器的邮件点击事件
document.oncontextmenu = function () {
return false;
};
const $container = $(element);
$container.addClass('superset-legacy-chart-table');
const metrics = (rawMetrics || [])
.map(m => m.label || m)
// Add percent metrics
.concat((percentMetrics || []).map(m => `%${m}`))
// Removing metrics (aggregates) that are strings
.filter(m => typeof data[0][m] === 'number');
function col(c) {
const arr = [];
for (let i = 0; i < data.length; i += 1) {
arr.push(data[i][c]);
}
return arr;
}
const maxes = {};
const mins = {};
for (let i = 0; i < metrics.length; i += 1) {
if (alignPositiveNegative) {
maxes[metrics[i]] = d3.max(col(metrics[i]).map(Math.abs));
} else {
maxes[metrics[i]] = d3.max(col(metrics[i]));
mins[metrics[i]] = d3.min(col(metrics[i]));
}
}
const tsFormatter = getTimeFormatter(tableTimestampFormat);
const baseUrl = window.location.protocol + "//" + window.location.host;
const apiUrl = baseUrl + '/superset/explore_json/?form_data=' + encodeURI(JSON.stringify({"slice_id": sliceId}));
let timeIndex = 0, areaIndex = -1, mainIndex = -1, newColumns = [], whereList = [], cacheData = new Map(),
check = true, token = "", requestData = formData, resultData = [];
const div = d3.select(element);
div.html('');
if (timeBy.length <= 0) {
areaIndex = 0;
if (areaBy <= 0) {
mainIndex = 0;
}
}
getToken();
flashTable();
function flashTable() {
const table = div
.append('table')
.classed(
'dataframe dataframe table table-striped ' +
'table-condensed table-hover dataTable no-footer',
true,
)
.attr('width', '100%');
// 将列置为空
newColumns = [];
columns.map(item => {
if (timeBy.length > 0) {
if (item["key"] === timeBy[timeIndex]) {
newColumns.push(item);
}
}
if ((item['key'] === areaBy[areaIndex + 1] && areaIndex === -1) || (item['key'] === areaBy[areaIndex] && areaIndex !== -1)) {
newColumns.push(item);
}
});
columns.map(item => {
if (areaIndex >= 0 || areaBy.length <= 0) {
for (let index = 0; index <= mainIndex; index++) {
if (item['key'] === groupby[index]) {
newColumns.push(item);
}
}
}
metrics.map(metric => {
if (item['key'] === metric) {
newColumns.push(item);
}
})
});
if ((areaIndex >= 0 || areaBy.length <= 0) && groupby.length > 0) {
newColumns.push({"key": "操作", "label": "操作", "format": null});
}
requestData.groupby = [];
if (timeBy.length > 0) {
requestData.groupby.push(timeBy[timeIndex]);
}
if (areaIndex !== -1 && areaBy.length > 0) {
requestData.groupby.push(areaBy[areaIndex]);
}
if (areaIndex >= 0 || areaBy.length <= 0) {
// 普通组过滤
for (let i = 0; i <= mainIndex; i++) {
requestData.groupby.push(groupby[i]);
}
}
if (whereList.length > 0) {
requestData.filters = [];
whereList.map(item => {
let filters_tmp = {"col": item['name'], "op": "==", "val": item['value']};
requestData.filters.push(filters_tmp);
});
} else {
requestData.filters = [];
}
if (check) {
$.ajax({
url: apiUrl,
method: "POST",
cache: true,
beforeSend: function (XMLHttpRequest) {
XMLHttpRequest.setRequestHeader("X-CSRFToken", token);
},
async: false,
data: "form_data=" + JSON.stringify(requestData),
success: function (result) {
resultData = result['data']['records'];
}
});
}
table
.append('thead')
.append('tr')
.selectAll('th')
.data(newColumns.map(c => c.label))
.enter()
.append('th')
.text(d => d);
table
.append('tbody')
.selectAll('tr')
.data(resultData)
.enter()
.append('tr')
.selectAll('td')
.data(row =>
newColumns.map(({key, format}) => {
let val = row[key];
let html;
const isMetric = metrics.indexOf(key) >= 0;
if (key === '__timestamp') {
html = tsFormatter(val);
}
if (typeof val === 'string') {
html = `<span class="like-pre">${dompurify.sanitize(val)}</span>`;
}
if (isMetric) {
html = getNumberFormatter(format)(val);
}
if (key[0] === '%') {
html = formatPercent(val);
}
if (typeof val === 'undefined' && key !== "操作") {
html = '全部';
val = '点击下钻';
}
if (typeof val === 'undefined' && key === "操作") {
html = `<span class="like-pre like-pre-add">下钻/上卷</span>`;
val = '左键下钻/右键上卷';
}
return {
col: key,
val,
html,
isMetric,
};
}),
)
.enter()
.append('td')
.style('background-image', d => {
if (d.isMetric) {
const r = colorPositiveNegative && d.val < 0 ? 150 : 0;
if (alignPositiveNegative) {
const perc = Math.abs(Math.round((d.val / maxes[d.col]) * 100));
// The 0.01 to 0.001 is a workaround for what appears to be a
// CSS rendering bug on flat, transparent colors
return (
`linear-gradient(to right, rgba(${r},0,0,0.2), rgba(${r},0,0,0.2) ${perc}%, ` +
`rgba(0,0,0,0.01) ${perc}%, rgba(0,0,0,0.001) 100%)`
);
}
const posExtent = Math.abs(Math.max(maxes[d.col], 0));
const negExtent = Math.abs(Math.min(mins[d.col], 0));
const tot = posExtent + negExtent;
const perc1 = Math.round((Math.min(negExtent + d.val, negExtent) / tot) * 100);
const perc2 = Math.round((Math.abs(d.val) / tot) * 100);
// The 0.01 to 0.001 is a workaround for what appears to be a
// CSS rendering bug on flat, transparent colors
return (
`linear-gradient(to right, rgba(0,0,0,0.01), rgba(0,0,0,0.001) ${perc1}%, ` +
`rgba(${r},0,0,0.2) ${perc1}%, rgba(${r},0,0,0.2) ${perc1 + perc2}%, ` +
`rgba(0,0,0,0.01) ${perc1 + perc2}%, rgba(0,0,0,0.001) 100%)`
);
}
return null;
})
.classed('text-right', d => d.isMetric)
.attr('title', d => {
if (typeof d.val === 'string') {
return d.val;
}
if (!Number.isNaN(d.val)) {
return formatValue(d.val);
}
return null;
})
.attr('data-sort', d => (d.isMetric ? d.val : null))
.classed('filtered', d => filters && filters[d.col] && filters[d.col].indexOf(d.val) >= 0)
.on('click', function (d, row, index) {
let isDown = true;
if (!d.isMetric) {
cacheData.set(timeIndex * 100 + areaIndex * 10 + mainIndex, resultData);
const line = resultData[index];
getDownWhere(line, d, false);
if (timeBy.length > 0 && areaBy.length > 0) {
if (row === 0) {
if (timeBy.length > timeIndex + 1) {
timeIndex = timeIndex + 1;
mainIndex = -1;
} else {
isDown = false;
}
}
if (row === 1) {
if (areaBy.length > areaIndex + 1) {
areaIndex = areaIndex + 1;
mainIndex = -1;
} else {
isDown = false;
}
}
} else if (timeBy.length > 0 && areaBy.length <= 0) {
if (row === 0) {
if (timeBy.length > timeIndex + 1) {
timeIndex = timeIndex + 1;
mainIndex = -1;
} else {
isDown = false;
}
}
} else if (timeBy.length <= 0 && areaBy.length > 0) {
if (row === 0) {
if (areaBy.length > areaIndex + 1) {
areaIndex = areaIndex + 1;
mainIndex = -1;
} else {
isDown = false;
}
}
}
if (d['col'] === '操作') {
if (groupby.length > mainIndex + 1) {
mainIndex = mainIndex + 1;
} else {
isDown = false;
}
}
if (isDown) {
// 移除已有的记录
$('table').remove();
$('.dataTables_wrapper').remove();
check = true;
flashTable();
}
}
})
.on('contextmenu', function (d, row, index) {
let isDown = true;
check = true;
if (!d.isMetric) {
const line = resultData[index];
if (timeBy.length > 0 && areaBy.length > 0) {
if (row === 0) {
if (timeIndex >= 1) {
timeIndex = timeIndex - 1;
mainIndex = -1;
} else {
isDown = false;
}
}
if (row === 1) {
if (areaIndex >= 0) {
areaIndex = areaIndex - 1;
mainIndex = -1;
} else {
isDown = false;
}
}
} else if (timeBy.length > 0 && areaBy.length <= 0) {
if (row === 0) {
if (timeIndex >= 1) {
timeIndex = timeIndex - 1;
mainIndex = -1;
} else {
isDown = false;
}
}
} else if (areaBy.length > 0 && timeBy.length <= 0) {
if (row === 0) {
if (areaIndex >= 0) {
areaIndex = areaIndex - 1;
mainIndex = -1;
} else {
isDown = false;
}
}
}
if (d['col'] === '操作') {
if (mainIndex > -1) {
mainIndex = mainIndex - 1;
} else {
isDown = false;
}
}
if (isDown) {
resultData = cacheData.get(timeIndex * 100 + areaIndex * 10 + mainIndex);
if (resultData === undefined) {
check = true;
} else {
check = false;
}
}
if (check && isDown) {
getUpWhere(line, d, row);
}
if (isDown) {
// 移除已有的记录
$('table').remove();
$('.dataTables_wrapper').remove();
flashTable();
}
}
}
)
.style('cursor', d => (!d.isMetric ? 'pointer' : ''))
.html(d => (d.html ? d.html : d.val));
$('.like-pre-add').parent().addClass("cssx");
const paging = pageLength && pageLength > 0;
const datatable = $container.find('.dataTable').DataTable({
paging,
pageLength,
aaSorting: [],
searching: includeSearch,
bInfo: false,
scrollY: `${height}px`,
scrollCollapse: true,
scrollX: true,
retrieve: true,
});
fixTableHeight($container.find('.dataTables_wrapper'), height);
// Sorting table by main column
let sortBy;
const limitMetric = Array.isArray(timeseriesLimitMetric)
? timeseriesLimitMetric[0]
: timeseriesLimitMetric;
if (limitMetric) {
// Sort by as specified
sortBy = limitMetric.label || limitMetric;
} else if (metrics.length > 0) {
// If not specified, use the first metric from the list
sortBy = metrics[0];
}
if (sortBy) {
const keys = newColumns.map(c => c.key);
const index = keys.indexOf(sortBy);
datatable.column(index).order(orderDesc ? 'desc' : 'asc');
if (metrics.indexOf(sortBy) < 0) {
// Hiding the sortBy column if not in the metrics list
datatable.column(index).visible(false);
}
}
datatable.draw();
}
function getDownWhere(line) {
whereList = [];
Object.keys(line).forEach(function (key) {
let flag = true;
metrics.map(metric => {
if (key === metric) {
flag = false;
}
});
if (flag) {
whereList.push({"name": key, "value": line[key]});
}
});
}
function getUpWhere(line, d, row) {
let midWhereLst = whereList;
whereList = [];
Object.keys(line).forEach(function (key) {
let flag = true;
metrics.map(metric => {
if (key === metric) {
flag = false;
}
});
if (timeBy.indexOf(key) > timeIndex) {
flag = false;
}
if (areaBy.indexOf(key) > areaIndex) {
flag = false;
}
if (groupby.indexOf(key) > mainIndex || mainIndex === -1) {
flag = false;
}
if (flag) {
whereList.push({"name": key, "value": line[key]});
}
});
if (areaIndex === -1) whereList = [];
}
function getToken() {
$.ajax({
url: baseUrl + '/superset/csrf_token/',
dataType: "json",
async: false,
success: function (res) {
token = res.csrf_token;
}
})
}
}
TableVis.displayName = 'TableVis';
TableVis.propTypes = propTypes;
export default TableVis;
3, DrillTableChartPlugin.js
import {t} from '@superset-ui/translation';
import {ChartMetadata, ChartPlugin} from '@superset-ui/chart';
import transformProps from './transformProps';
import thumbnail from './images/thumbnail.png';
const metadata = new ChartMetadata({
canBeAnnotationTypes: ['EVENT', 'INTERVAL'],
description: '',
name: t('Drill Table'),
thumbnail,
useLegacyApi: true,
});
export default class DrillTableChartPlugin extends ChartPlugin {
constructor() {
super({
loadChart: () => import('./ReactDrillTable.js'),
metadata,
transformProps,
});
}
}
4, ReactDrillTable.js
import { reactify } from '@superset-ui/chart';
import Component from './DrillTable';
export default reactify(Component);
5, Table.css css文件
.superset-legacy-chart-table {
margin: 0px !important;
background: transparent;
background-color: white;
}
.superset-legacy-chart-table thead th.sorting:after, table.table thead th.sorting_asc:after, table.table thead th.sorting_desc:after {
top: 0px;
}
.like-pre {
white-space: pre-wrap;
}
.cssx {
text-align: center;
}
.even {
background-color: #f9f9f9;
}
th {
background-color: #f9f9f9;
}
6, transformProps.js
export default function transformProps(chartProps) {
const {height, datasource, filters, formData, onAddFilter, payload} = chartProps;
const {
alignPn,
colorPn,
includeSearch,
metrics,
orderDesc,
pageLength,
percentMetrics,
tableFilter,
tableTimestampFormat,
timeseriesLimitMetric,
timeBy,
areaBy,
groupby,
sliceId,
} = formData;
const {columnFormats, verboseMap} = datasource;
const {records, columns} = payload.data;
const processedColumns = columns.map(key => {
let label = verboseMap[key];
// Handle verbose names for percents
if (!label) {
if (key[0] === '%') {
const cleanedKey = key.substring(1);
label = `% ${verboseMap[cleanedKey] || cleanedKey}`;
} else {
label = key;
}
}
return {
key,
label,
format: columnFormats && columnFormats[key],
};
});
return {
height,
data: records,
alignPositiveNegative: alignPn,
colorPositiveNegative: colorPn,
columns: processedColumns,
filters,
includeSearch,
metrics,
onAddFilter,
orderDesc,
pageLength: pageLength && parseInt(pageLength, 10),
percentMetrics,
tableFilter,
tableTimestampFormat,
timeseriesLimitMetric,
timeBy,
areaBy,
groupby,
sliceId,
formData
};
}
4, 右侧渲染层注册
assets/src/visualizations/presets/MainPreset.js 参照其它案例即可完成
5, Viz.py 视图文件(接口文件)
class DrillTableViz(BaseViz):
"""A basic html table that is sortable and searchable"""
viz_type = "drill_table"
verbose_name = _("Drill Table View")
credits = 'a <a href="https://github.com/airbnb/superset">Superset</a> original'
is_timeseries = False
enforce_numerical_metrics = False
def should_be_timeseries(self):
fd = self.form_data
# TODO handle datasource-type-specific code in datasource
conditions_met = (fd.get("granularity") and fd.get("granularity") != "all") or (
fd.get("granularity_sqla") and fd.get("time_grain_sqla")
)
if fd.get("include_time") and not conditions_met:
raise Exception(
_("Pick a granularity in the Time section or " "uncheck 'Include Time'")
)
return fd.get("include_time")
def query_obj(self):
d = super().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"
)
)
sort_by = fd.get("timeseries_limit_metric")
if fd.get("all_columns"):
d["columns"] = fd.get("all_columns")
d["groupby"] = []
order_by_cols = fd.get("order_by_cols") or []
d["orderby"] = [json.loads(t) for t in order_by_cols]
elif sort_by:
sort_by_label = utils.get_metric_name(sort_by)
if sort_by_label not in utils.get_metric_names(d["metrics"]):
d["metrics"] += [sort_by]
d["orderby"] = [(sort_by, not fd.get("order_desc", True))]
for item in fd.get("time_by") or []:
d['groupby'].append(item)
for item in fd.get("area_by") or []:
d['groupby'].append(item)
# Add all percent metrics that are not already in the list
if "percent_metrics" in fd:
d["metrics"] = d["metrics"] + list(
filter(lambda m: m not in d["metrics"], fd["percent_metrics"] or [])
)
d["is_timeseries"] = self.should_be_timeseries()
return d
def get_data(self, df):
fd = self.form_data
if not self.should_be_timeseries() and df is not None and DTTM_ALIAS in df:
del df[DTTM_ALIAS]
# Sum up and compute percentages for all percent metrics
percent_metrics = fd.get("percent_metrics") or []
percent_metrics = [utils.get_metric_name(m) for m in percent_metrics]
if len(percent_metrics):
percent_metrics = list(filter(lambda m: m in df, percent_metrics))
metric_sums = {
m: reduce(lambda a, b: a + b, df[m]) for m in percent_metrics
}
metric_percents = {
m: list(
map(
lambda a: None if metric_sums[m] == 0 else a / metric_sums[m],
df[m],
)
)
for m in percent_metrics
}
for m in percent_metrics:
m_name = "%" + m
df[m_name] = pd.Series(metric_percents[m], name=m_name)
# Remove metrics that are not in the main metrics list
metrics = fd.get("metrics") or []
metrics = [utils.get_metric_name(m) for m in metrics]
for m in filter(
lambda m: m not in metrics and m in df.columns, percent_metrics
):
del df[m]
data = self.handle_js_int_overflow(
dict(records=df.to_dict(orient="records"), columns=list(df.columns))
)
print("getData.........................................")
return data
def json_dumps(self, obj, sort_keys=False):
print("dump.........................................")
return json.dumps(
obj, default=utils.json_iso_dttm_ser, sort_keys=sort_keys, ignore_nan=True
)
说明: ajax请求数据时走不到这里,直接走的是BaseViz下面的query_obj(), 按照需要修改下即可