此前用过国产开源的可视化平台Davinci,实际上对那个的制作流程比较适应,这次恰好有需要用superset,被迫重新了解下怎么使用。
当前用的superset版本有点老,0.37.2,新版本没去了解。
制作报表首先是数据加载,数据库链接都一样,但是数据集需要来自一个表后自动确认模型,如果采用自定义SQL则无法自动加在出模型,这一点当前版本的superset不太好用。
直接记录下现成的sql吧,以防以后还有的用到
-- Note: Unless you save your query, these tabs will NOT persist if you clear your cookies or change browsers.
select
a.*,
b.device_name_project,
b.device_name_level_2,
concat(a.firmware_event_id,'(',ifnull(c.event_id_name_zh,''),')') as firmware_id_name_zh,
d.area,
concat(a.firmware_event_key,'(',ifnull(e.event_param_key_name_zh,''),')') as firmware_event_key_name_zh
from
(
select
*,
{% if filter_values('firmware_event_key_name_zh')|length %}
regexp_replace('{{ filter_values('firmware_event_key_name_zh')|replace("['",'')|replace("']",'') }}', '\\(.*','')
{% else %}
''
{% endif %}
as firmware_event_key,
{% if filter_values('firmware_event_key_name_zh')|length %}
get_json_string(firmware_event_params,regexp_replace("$.{{ filter_values('firmware_event_key_name_zh')|replace("['",'')|replace("']",'')|replace('(.*','') }}", '\\(.*',''))
{% else %}
''
{% endif %}
as firmware_event_value
from dw.dws_log_firmware_sum_di
where p_day >= cast(date_add(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),-90) as string)
)a
left join
(select devicesource,device_name_project,device_name_level_2 from hm_dim.dim_devicename)b
on a.device_source = b.devicesource
left join
--事件ID名称维表
(select event_id, event_id_name_zh from dw.dim_log_event_id)c
on a.firmware_event_id = c.event_id
left join
-- 国家区域维表
(select country,area from hm_dim.dim_countrycodewitharea)d
on a.active_country = d.country
left join
-- 事件key名称维表
(select event_param_key,event_param_key_name_zh from dw.dim_log_key)e
on a.firmware_event_key = e.event_param_key
参考资料:
Superset实现动态SQL查询 - 数据行者 - 博客园 (cnblogs.com)
Template Designer Documentation — Jinja Documentation (2.11.x) (palletsprojects.com)