def report(request):
"""返回慢SQL历史趋势"""
checksum = request.GET.get('checksum')
cnt_data = ChartDao().slow_query_review_history_by_cnt(checksum)
pct_data = ChartDao().slow_query_review_history_by_pct_95_time(checksum)
cnt_x_data = [row[1] for row in cnt_data['rows']]
cnt_y_data = [int(row[0]) for row in cnt_data['rows']]
pct_y_data = [str(row[0]) for row in pct_data['rows']]
line = Line(init_opts=opts.InitOpts(width='800', height='380px'))
line.add_xaxis(cnt_x_data)
line.add_yaxis("慢查次数",
cnt_y_data,
is_smooth=True,
markline_opts=opts.MarkLineOpts(data=[
opts.MarkLineItem(type_="max", name='最大值'),
opts.MarkLineItem(type_="average", name='平均值')
]))
line.add_yaxis("慢查时长(95%)",
pct_y_data,
is_smooth=True,
is_symbol_show=False)
line.set_series_opts(areastyle_opts=opts.AreaStyleOpts(opacity=0.5, ))
line.set_global_opts(
title_opts=opts.TitleOpts(title='SQL历史趋势'),
legend_opts=opts.LegendOpts(selected_mode='single'),
xaxis_opts=opts.AxisOpts(
axistick_opts=opts.AxisTickOpts(is_align_with_label=True),
is_scale=False,
boundary_gap=False,
),
)
result = {"status": 0, "msg": '', "data": line.render_embed()}
return HttpResponse(json.dumps(result), content_type='application/json')
def testGetDateList(self):
dao = ChartDao()
end = datetime.date.today()
begin = end - datetime.timedelta(days=3)
result = dao.get_date_list(begin, end)
self.assertEqual(len(result), 4)
self.assertEqual(result[0], begin.strftime('%Y-%m-%d'))
self.assertEqual(result[-1], end.strftime('%Y-%m-%d'))
def top_slow_sql(request):
"""TOP SLOW SQL"""
instance_name = request.GET.get('instance_name')
db_name = request.GET.get('db_name')
order = request.GET.get('order')
if order == 'count':
order = 'ts_cnt'
elif order == 'latency':
order = 'Query_time_sum'
else:
return JsonResponse({'status': 1, 'msg': '不支持的排序类型', 'data': []})
try:
instance = Instance.objects.get(instance_name=instance_name)
except Instance.DoesNotExist:
return JsonResponse({'status': 1, 'msg': '实例不存在', 'data': []})
hostname_max = f'{instance.host}:{instance.port}'
query_result = ChartDao().slow_query_top(hostname_max, db_name, order)
if not query_result['error']:
data = []
for r in query_result['rows']:
data += [dict(zip(query_result['column_list'], r))]
result = {'status': 0, 'msg': 'ok', 'rows': data}
else:
result = {'status': 1, 'msg': query_result['error']}
# 返回查询结果
return HttpResponse(json.dumps(result, cls=ExtendJSONEncoder, bigint_as_string=True),
content_type='application/json')
def testWorkflowByUser(self):
"""按用户统计测试"""
dao = ChartDao()
result = dao.workflow_by_user(30)
expected_rows = ((self.u2.display, 3), (self.u1.display, 2))
self.assertEqual(result['rows'], expected_rows)
def testWorkflowByGroup(self):
"""按组统计测试"""
dao = ChartDao()
result = dao.workflow_by_group(30)
expected_rows = (('g2', 3), ('g1', 2))
self.assertEqual(result['rows'], expected_rows)
def testWorkflowByDate(self):
"""TODO 按日分组工单数量统计测试"""
dao = ChartDao()
result = dao.workflow_by_date(30)
self.assertEqual(len(result['rows'][0]), 2)
def testSyntaxList(self):
"""工单以语法类型分组"""
dao = ChartDao()
expected_rows = (('DDL', 2), ('DML', 3))
result = dao.sql_syntax()
self.assertEqual(result['rows'], expected_rows)
def pyecharts(request):
"""dashboard view"""
# 工单数量统计
chart_dao = ChartDao()
bar1 = Bar('SQL上线工单统计(数量)', width="100%")
data = chart_dao.workflow_by_date(30)
today = date.today()
one_month_before = today - relativedelta(days=+30)
attr = chart_dao.get_date_list(one_month_before, today)
_dict = {}
for row in data['rows']:
_dict[row[0]] = row[1]
value = [_dict.get(day) if _dict.get(day) else 0 for day in attr]
bar1.add("月统计", attr, value, is_stack=False, legend_selectedmode='single')
# 工单按组统计
pie1 = Pie('SQL上线工单统计(组)', width="100%")
data = chart_dao.workflow_by_group(30)
attr = [row[0] for row in data['rows']]
value = [row[1] for row in data['rows']]
pie1.add("月统计", attr, value, is_legend_show=False, is_label_show=True)
# 工单按人统计
bar2 = Bar('SQL上线工单统计(用户)', width="100%")
data = chart_dao.workflow_by_user(30)
attr = [row[0] for row in data['rows']]
value = [row[1] for row in data['rows']]
bar2.add("月统计", attr, value, is_label_show=True)
# SQL语句类型统计
pie2 = Pie("SQL上线工单统计(类型)", width="100%")
data = chart_dao.sql_syntax()
attr = [row[0] for row in data['rows']]
value = [row[1] for row in data['rows']]
pie2.add("", attr, value, is_label_show=True)
# SQL查询统计(每日检索行数)
line1 = Line("SQL查询统计", width="100%")
attr = chart_dao.get_date_list(one_month_before, today)
effect_data = chart_dao.querylog_effect_row_by_date(30)
effect_dict = {}
for row in effect_data['rows']:
effect_dict[row[0]] = int(row[1])
effect_value = [effect_dict.get(day) if effect_dict.get(day) else 0 for day in attr]
count_data = chart_dao.querylog_count_by_date(30)
count_dict = {}
for row in count_data['rows']:
count_dict[row[0]] = int(row[1])
count_value = [count_dict.get(day) if count_dict.get(day) else 0 for day in attr]
line1.add("检索行数", attr, effect_value, is_stack=False, legend_selectedmode='single', mark_point=["average"])
line1.add("检索次数", attr, count_value, is_stack=False, legend_selectedmode='single', is_smooth=True,
mark_line=["max", "average"])
# SQL查询统计(用户检索行数)
pie4 = Pie("SQL查询统计(用户检索行数)", width="100%")
data = chart_dao.querylog_effect_row_by_user(30)
attr = [row[0] for row in data['rows']]
value = [int(row[1]) for row in data['rows']]
pie4.add("月统计", attr, value, radius=[40, 75], is_legend_show=False, is_label_show=True)
# SQL查询统计(DB检索行数)
pie5 = Pie("SQL查询统计(DB检索行数)", width="100%")
data = chart_dao.querylog_effect_row_by_db(30)
attr = [row[0] for row in data['rows']]
value = [int(row[1]) for row in data['rows']]
pie5.add("月统计", attr, value, is_legend_show=False, is_label_show=True)
# 可视化展示页面
page = Page()
page.add(bar1)
page.add(pie1)
page.add(bar2)
page.add(pie2)
page.add(line1)
page.add(pie4)
page.add(pie5)
myechart = page.render_embed() # 渲染配置
host = 'https://pyecharts.github.io/assets/js' # js文件源地址
script_list = page.get_js_dependencies() # 获取依赖的js文件名称（只获取当前视图需要的js）
return render(request, "dashboard.html", {"myechart": myechart, "host": host, "script_list": script_list})
def pyecharts(request):
"""dashboard view"""
# 工单数量统计
chart_dao = ChartDao()
data = chart_dao.workflow_by_date(30)
today = date.today()
one_month_before = today - relativedelta(days=+30)
attr = chart_dao.get_date_list(one_month_before, today)
_dict = {}
for row in data['rows']:
_dict[row[0]] = row[1]
value = [_dict.get(day) if _dict.get(day) else 0 for day in attr]
bar1 = Bar()
bar1.add_xaxis(attr)
bar1.add_yaxis("月统计", value)
bar1.set_global_opts(title_opts=opts.TitleOpts(title='SQL上线工单统计(数量)'),
datazoom_opts=opts.DataZoomOpts(is_show=True),
toolbox_opts=opts.ToolboxOpts(is_show=True))
# 工单按组统计
data = chart_dao.workflow_by_group(30)
attr = [row[0] for row in data['rows']]
value = [row[1] for row in data['rows']]
pie1 = Pie()
pie1.set_global_opts(title_opts=opts.TitleOpts(title='SQL上线工单统计(组)'),
legend_opts=opts.LegendOpts(orient="vertical",
pos_top="15%",
pos_left="2%"))
pie1.set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c}"))
pie1.add("", [list(z) for z in zip(attr, value)])
# 工单按人统计
data = chart_dao.workflow_by_user(30)
attr = [row[0] for row in data['rows']]
value = [row[1] for row in data['rows']]
bar2 = Bar()
bar2.add_xaxis(attr)
bar2.add_yaxis("月统计", value)
bar2.set_global_opts(title_opts=opts.TitleOpts(title='SQL上线工单统计(用户)'))
# SQL语句类型统计
data = chart_dao.syntax_type()
attr = [row[0] for row in data['rows']]
value = [row[1] for row in data['rows']]
pie2 = Pie()
pie2.set_global_opts(title_opts=opts.TitleOpts(title='SQL上线工单统计(类型)'),
legend_opts=opts.LegendOpts(orient="vertical",
pos_top="15%",
pos_left="2%"))
pie2.set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c}"))
pie2.add("", [list(z) for z in zip(attr, value)])
# SQL查询统计(每日检索行数)
attr = chart_dao.get_date_list(one_month_before, today)
effect_data = chart_dao.querylog_effect_row_by_date(30)
effect_dict = {}
for row in effect_data['rows']:
effect_dict[row[0]] = int(row[1])
effect_value = [
effect_dict.get(day) if effect_dict.get(day) else 0 for day in attr
]
count_data = chart_dao.querylog_count_by_date(30)
count_dict = {}
for row in count_data['rows']:
count_dict[row[0]] = int(row[1])
count_value = [
count_dict.get(day) if count_dict.get(day) else 0 for day in attr
]
line1 = Line()
line1.set_global_opts(title_opts=opts.TitleOpts(title='SQL查询统计'),
legend_opts=opts.LegendOpts(selected_mode='single'))
line1.add_xaxis(attr)
line1.add_yaxis("检索行数",
effect_value,
markpoint_opts=opts.MarkPointOpts(
data=[opts.MarkPointItem(type_="average")]))
line1.add_yaxis("检索次数",
count_value,
is_smooth=True,
markline_opts=opts.MarkLineOpts(data=[
opts.MarkLineItem(type_="max"),
opts.MarkLineItem(type_="average")
]))
# SQL查询统计(用户检索行数)
data = chart_dao.querylog_effect_row_by_user(30)
attr = [row[0] for row in data['rows']]
value = [int(row[1]) for row in data['rows']]
pie4 = Pie()
pie4.set_global_opts(title_opts=opts.TitleOpts(title='SQL查询统计(用户检索行数)'),
legend_opts=opts.LegendOpts(orient="vertical",
pos_top="15%",
pos_left="2%"))
pie4.set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c}"))
pie4.add("", [list(z) for z in zip(attr, value)])
# SQL查询统计(DB检索行数)
data = chart_dao.querylog_effect_row_by_db(30)
attr = [row[0] for row in data['rows']]
value = [int(row[1]) for row in data['rows']]
pie5 = Pie()
pie5.set_global_opts(title_opts=opts.TitleOpts(title='SQL查询统计(DB检索行数)'),
legend_opts=opts.LegendOpts(orient="vertical",
pos_top="15%",
pos_left="2%"))
pie5.set_series_opts(
label_opts=opts.LabelOpts(formatter="{b}: {c}", position="left"))
pie5.add("", [list(z) for z in zip(attr, value)])
# 可视化展示页面
page = Page(layout=Page.SimplePageLayout)
page.add(bar1, pie1, bar2, pie2, line1, pie4, pie5)
return render(request, "dashboard.html", {"chart": page.render_embed()})
def pyecharts(request):
"""dashboard view"""
# 工单数量统计
chart_dao = ChartDao()
data = chart_dao.workflow_by_date(30)
today = date.today()
one_month_before = today - relativedelta(days=+30)
attr = chart_dao.get_date_list(one_month_before, today)
_dict = {}
for row in data['rows']:
_dict[row[0]] = row[1]
value = [_dict.get(day) if _dict.get(day) else 0 for day in attr]
bar1 = Bar(init_opts=opts.InitOpts(width='600', height='380px'))
bar1.add_xaxis(attr)
bar1.add_yaxis("", value)
# 工单按组统计
data = chart_dao.workflow_by_group(30)
attr = [row[0] for row in data['rows']]
value = [row[1] for row in data['rows']]
pie1 = Pie(init_opts=opts.InitOpts(width='600', height='380px'))
pie1.set_global_opts(title_opts=opts.TitleOpts(title=''),
legend_opts=opts.LegendOpts(orient="vertical",
pos_top="15%",
pos_left="2%",
is_show=False))
pie1.set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c}"))
pie1.add("", [list(z) for z in zip(attr, value)])
# 工单按人统计
data = chart_dao.workflow_by_user(30)
attr = [row[0] for row in data['rows']]
value = [row[1] for row in data['rows']]
bar2 = Bar(init_opts=opts.InitOpts(width='600', height='380px'))
bar2.add_xaxis(attr)
bar2.add_yaxis("", value)
# SQL语句类型统计
data = chart_dao.syntax_type()
attr = [row[0] for row in data['rows']]
value = [row[1] for row in data['rows']]
pie2 = Pie()
pie2.set_global_opts(title_opts=opts.TitleOpts(title='SQL上线工单统计(类型)'),
legend_opts=opts.LegendOpts(orient="vertical",
pos_top="15%",
pos_left="2%"))
pie2.set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c}"))
pie2.add("", [list(z) for z in zip(attr, value)])
# SQL查询统计(每日检索行数)
attr = chart_dao.get_date_list(one_month_before, today)
effect_data = chart_dao.querylog_effect_row_by_date(30)
effect_dict = {}
for row in effect_data['rows']:
effect_dict[row[0]] = int(row[1])
effect_value = [
effect_dict.get(day) if effect_dict.get(day) else 0 for day in attr
]
count_data = chart_dao.querylog_count_by_date(30)
count_dict = {}
for row in count_data['rows']:
count_dict[row[0]] = int(row[1])
count_value = [
count_dict.get(day) if count_dict.get(day) else 0 for day in attr
]
line1 = Line(init_opts=opts.InitOpts(width='600', height='380px'))
line1.set_global_opts(title_opts=opts.TitleOpts(title=''),
legend_opts=opts.LegendOpts(selected_mode='single'))
line1.add_xaxis(attr)
line1.add_yaxis("检索行数",
effect_value,
is_smooth=True,
markpoint_opts=opts.MarkPointOpts(
data=[opts.MarkPointItem(type_="average")]))
line1.add_yaxis("检索次数",
count_value,
is_smooth=True,
markline_opts=opts.MarkLineOpts(data=[
opts.MarkLineItem(type_="max"),
opts.MarkLineItem(type_="average")
]))
# SQL查询统计(用户检索行数)
data = chart_dao.querylog_effect_row_by_user(30)
attr = [row[0] for row in data['rows']]
value = [int(row[1]) for row in data['rows']]
pie4 = Pie(init_opts=opts.InitOpts(width='600', height='380px'))
pie4.set_global_opts(title_opts=opts.TitleOpts(title=''),
legend_opts=opts.LegendOpts(orient="vertical",
pos_top="15%",
pos_left="2%",
is_show=False))
pie4.set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c}"))
pie4.add("", [list(z) for z in zip(attr, value)])
# SQL查询统计(DB检索行数)
data = chart_dao.querylog_effect_row_by_db(30)
attr = [row[0] for row in data['rows']]
value = [int(row[1]) for row in data['rows']]
pie5 = Pie(init_opts=opts.InitOpts(width='600', height='380px'))
pie5.set_global_opts(title_opts=opts.TitleOpts(title=''),
legend_opts=opts.LegendOpts(orient="vertical",
pos_top="15%",
pos_left="2%",
is_show=False))
pie5.set_series_opts(
label_opts=opts.LabelOpts(formatter="{b}: {c}", position="left"))
pie5.add("", [list(z) for z in zip(attr, value)])
# 可视化展示页面
chart = {
"bar1": bar1.render_embed(),
"pie1": pie1.render_embed(),
"bar2": bar2.render_embed(),
"pie2": pie2.render_embed(),
"line1": line1.render_embed(),
"pie4": pie4.render_embed(),
"pie5": pie5.render_embed(),
}
# 获取统计数据
dashboard_count_stats = {
"sql_wf_cnt": SqlWorkflow.objects.count(),
"query_wf_cnt": QueryPrivilegesApply.objects.count(),
"user_cnt": Users.objects.count(),
"ins_cnt": Instance.objects.count()
}
return render(request, "dashboard.html", {
"chart": chart,
"count_stats": dashboard_count_stats
})
# -*- coding: UTF-8 -*-
from django.contrib.auth.decorators import permission_required
from django.shortcuts import render
from pyecharts import Pie, Bar, Line
from pyecharts import Page
from sql.utils.inception import InceptionDao
from common.utils.chart_dao import ChartDao
from datetime import date
from dateutil.relativedelta import relativedelta
chart_dao = ChartDao()
@permission_required('sql.menu_dashboard', raise_exception=True)
def pyecharts(request):
# 工单数量统计
bar1 = Bar('SQL上线工单统计(数量)', width="100%")
data = chart_dao.workflow_by_date(1)
begin_date = (date.today() - relativedelta(months=+1)).strftime("%Y-%m-%d")
attr = chart_dao.get_date_list(begin_date,
date.today().strftime("%Y-%m-%d"))
dict = {}
for row in data['rows']:
dict[row[0]] = row[1]
value = [dict.get(day) if dict.get(day) else 0 for day in attr]
bar1.add("月统计", attr, value, is_stack=False, legend_selectedmode='single')
# 工单按组统计
pie1 = Pie('SQL上线工单统计(组)', width="100%")
data = chart_dao.workflow_by_group(1)
attr = [row[0] for row in data['rows']]