添加自定义查询

概述

当分析模型的查询能力无法满足业务诉求，且数据分析师有一定 SQL 编辑能力时，推荐使用「自定义查询」模块，完成复杂业务逻辑的实现与构建，自主编写 SQL 语句，可满足复杂多变的业务场景，实现数据的查询与灵活分析。

操作入口

添加自定义查询的结果进业务集市的操作入口有 2 个：

入口一：业务集市中「创建业务模型」；

入口二：分析入口找到「经营分析 →自定义查询」

添加自定义查询

1.自定义查询中编写完成 SQL 语句，获取查询结果后点击「添加到业务集市」按钮，即可添加至业务集市

2.添加时会校验是否有查询结果，查询无结果时不则能添加；

3.支持设置完业务模型名称与备注后，保存 100W 行数据到报表

4.支持离线数据、实时、实时聚合三种类型存储方式，其中「实时聚合」类型受开关控制，按需开启，如下图：

业务模型配置信息

自定义查询的的配置信息如下图：

配置界面详细功能请参照下表：

	分类	名称	功能
1	基础信息	业务模型名称	业务模型名称，项目内全局唯一，不可重复。
		分组	该业务模型所在的分组
		空间类型	分为：公共空间、个人空间个人空间的业务模型支持轻量调度，帮助用户验证模型数据；公共空间的业务模型作为稳定的数据资产赋能公共报表服务业务使用
		数据调度模式	分为：离线、实时、实时聚合
		备注	对业务模型的详情描述
2	业务设置	主时间	当前sql中存在的datetime类型字段，可以指定为主时间，主时间指定后，当前sql会按照此时间字段进行增量更新主时间一旦设置保存，无法二次更改，保证数据的稳定运行当前sql中如果不存在datetime类型字段，则无法指定主时间和增量更新
2	业务设置	数据范围	数据更新时获取当前时间区间内的数据
3	更新设置	更新方式	支持两种更新方式：例行更新：系统根据配置信息按照固定频率进行数据的自动更新手动更新：手动触发执行此模型的更新任务
		更新类型	支持的更新类型：全量更新：是指将该模型对应到数据库中的所有数据全部更新一次到业务集市增量更新：每次更新按照主时间的增加增量更新，保留历史已有数据全量更新可以通过主时间字段设置变更为增量更新，增量更新不可变更为全量更新，保证数据的稳定可追溯
		更新频率	例行更新任务的执行频率
4	权限设置	数据导出	允许有数据权限的成员导出业务模型的数据，导出的数据范围与该成员的数据权限一致
		管理权限	添加账号或角色或职务，与创建人一同管理本业务模型，具体配置详情请参照：业务模型权限配置管理权限包括：编辑 / 删除业务模型、查看数据、查看配置、配置图表、更新数据
		数据权限	获得数据权限的对象，可查看指定范围的业务模型数据，并可使用此模型配置图表具体配置详情请参照：业务模型权限配置

展示与预览

自定义查询结果保存成功后，在业务集市的列表页新增一行模型信息，并提醒用户「添加成功」，如下图：
数据更新成功后，进入到业务模型详情页查看，按照用户实际的数据权限，默认加载100条样例明细数据s
同时展示当前模型的查询语句，支持可复制
根据查询结果中每个字段的数据内容，报表会判断字段的数据类型

特殊自定义查询

除常规自定义查询外，用户还可以根据实际场景需求配置实时聚合与增量更新的自定义查询

添加实时聚合模型

Impala 的实时聚合（Live Aggregation）模式可以支持对表或表的混合视图（如 events）进行增量的、实时聚合查询，受开关控制，按需开启，如下图：

实现原理上主要依赖于 Kudu 的 SetDiffScan 进行增量扫描，避免每次查询重新扫描全表，同时 SQL 会长时间运行，把聚合状态保留在内存中，并在聚合有变更的时候进行输出。

需要注意的语法规范：

只支持单表 GROUP BY
书写SQL时需要保证填写group by语句，同时指标必须配备聚合函数

本功能为高级功能，默认不开启，请联系运维开启功能并勾选实时聚合权限点

添加增量更新自定义查询

自定义查询sql通过编写日期的动态参数实现每日数据的增量更新，节省资源开销的同时提高更新效率

增量更新模版

创建增量更新的自定义查询则需要在sql的where条件中编写 '${dataset_start_date}' and '${dataset_end_date}' 时间动参，下面提供了几类常用的增量sql模版

模板1：嵌套子查询增量更新

with
  detail_1 as (
    select
      date,
      'measure1',
      'measure2'
    from
      EVENTS
    where
      date BETWEEN '${dataset_start_date}' and '${dataset_end_date}'
    group by
      1,
      2
  )
select
  date,
  count('measure2') as cnt
from
  detail_1
group by
  1

模板2：union all历史数据的增量更新

select
  date,
  'measure1',
  'measure2'
from
  EVENTS
where
  date BETWEEN '2023-10-01' and '2023-11-01' -- 查询2023-10-01到2023-11-01的数据--
union all
select
  date,
  'measure1',
  'measure2'
from
  EVENTS
where
  date BETWEEN '${dataset_start_date}' and '${dataset_end_date}' --'${dataset_start_date}' 和 '${dataset_end_date}'

增量更新配置

编写带标准时间动参的sql，'${dataset_start_date}' and '${dataset_end_date}'，同时在更多中为动态参数赋予默认值

选择保存到业务集市，进行业务模型配置，默认的动态参数值会被代入数据范围的左右时间，其中：

主时间：用以进行增量的时间字段
数据范围：有时间动参后数据范围才可选，选择后的数据范围会在sql中替换时间参数参与每日调度更新，一般增量更新数据范围为固定日期-昨天

同时选择主时间和数据范围后才触发增量，否则为全量