基于ClickHouse字典表的大数据查询方法、系统和介质
技术领域
本发明涉及大数据
技术领域
，更具体地说，涉及一种基于ClickHouse字典表的大数据查询方法、系统和介质。背景技术
在进行数据分析时，会需要对数据的各种维度进行查找，分类，统计等操作，用于发现和分析数据之间的差异。各种维度的数据在使用和传输中考虑到编码和安全性等问题通常使用数字编号进行命名，即我们常说的ID。在进行数据分析时，纯数字的ID结果，对于没有经过专业培训的用户而言，难以理解其中的含义，因此产生键-值映射问题。
例如，一张包含m行财务数据的表A，表中包括货币字段，财务通常处理多种货币，货币用数字代码表示，其中156代表人民币，840 代表美元，在对表A分析时，需将货币字段时关联额外的货币属性，例如货币名称和国家，用于更好的分析数据，这时会存在以下问题：
1、如果表A中没有国家和货币名称，需要再创建一张包含货币数字代码，以及对应国家和货币名称的表B，此类方法称为多表连接法。假设表B有n行，在查询时通过JOIN （连接）A表和B表来实现在返回数据结果时有国家和货币名称，这时后台的数据处理量会从m行，上升成m*n行，极大影响查询性能，几乎无法在大数据分析时使用；
2、现有技术也会通过大宽表方法实现ID和名称的匹配，但是如果表A中有国家和货币名称，用户又无法阅读系统默认显示的国家和货币名称语言，或出现国家名称改变，国家名称对应不用的缩写等情况时，需对表A的数据量进行修改，通常来说，表A具有很大的数据量，修改难度很大。
发明内容
本发明提供一种基于ClickHouse字典表的大数据查询方法，在大数据查询时可直接使用映射值进行数据查询，提高数据查询效率，提升系统整体性能。
本发明提供的基于ClickHouse字典表的大数据查询方法，包括：
（1）创建字典表：根据键-值映射要求，设置需要同步到字典表的字段；从指定数据源同步相应字段的数据至字典表；
（2）配置dataCube：针对dataCube配置中需要以键-值映射方式显示的dimension字段，在dimension字段的配置信息中添加字典表名称，以及该字典表中与dimension字段对应的字段名、需要以映射值方式显示的字段名，生成与该dimension字段关联的字典结构；
（3）大数据查询：接收查询指令，所述查询指令包括查询的字段A，读取dataCube配置信息，如果字段A匹配有dimension字段，且该dimension字段配置有字典表，构建从数据库获取字段A的SQL查询语句，该SQL查询语句还包括从字典表获取字段A所对应的以映射值方式显示的字段的语句；然后返回查询结果。
本发明通过设置数据保存在内存的字典表，在通过查询数据库获得相关数据的同时，通过字典表获得一些关联数据，实现数据查询过程中多表JOIN的效果的同时，有效解决了多表JOIN时对性能的损耗，大大提高了数据查询效率。
优选地，所述查询指令还包括对需要以映射值方式显示的字段的限缩性查询条件，所述SQL查询语句包括利用限缩性查询条件进行查询的语句。
优选地，创建字典表时，配置有端口、账号和密码，自动从指定数据源同步数据。
优选地，创建字典表时，配置有更新周期，自动根据更新周期从指定数据源同步数据。
优选地，字段A为数据型数据且由其他字段计算得到时，设置measure字段获得字段A的计算公式，所述SQL查询语句包括从数据库获取计算字段A所需要的其他字段的语句。
本发明还提供一种基于ClickHouse字典表的大数据查询系统，包括字典表创建模块、dataCube配置模块、SQL语句生成与执行模块；
所述字典表创建模块根据键-值映射要求，设置需要同步到字典表的字段；从指定数据源同步相应字段的数据至字典表；
所述dataCube配置模块针对需要以键-值映射方式显示的字段，在dataCube配置中添加dimension字段，在dimension字段的配置信息中添加字典表名称，以及该字典表中与dimension字段对应的字段名、需要以映射值方式显示的字段名，生成与该dimension字段关联的字典结构；
所述SQL语句生成与执行模块接收查询指令，读取dataCube配置信息，如果所述查询指令中包括的字段A匹配有dimension字段，且该dimension字段配置有字典表，则构建从数据库获取字段A的SQL查询语句，该SQL查询语句还包括从字典表获取字段A所对应的以映射值方式显示的字段的语句；然后执行SQL语句并返回查询结果。
优选地，所述查询指令还包括对需要以映射值方式显示的字段的限缩性查询条件，所述SQL语句生成与执行模块构建的SQL查询语句包括利用限缩性查询条件进行查询的语句。
优选地，所述字典表创建模块根据配置的端口、账号和密码，自动从指定数据源同步数据，和/或，所述字典表创建模块根据配置的更新周期，自动根据更新周期从指定数据源同步数据。
优选地，字段A由其他字段计算得到，所述dataCube配置模块添加的measure字段包括获得字段A的计算公式，所述SQL语句生成与执行模块生成的SQL查询语句包括从数据库获取计算字段A所需要的其他字段的语句。
本发明还提供一种存储介质，其上存储有计算机程序，该程序被执行时，实现本发明提供的基于ClickHouse字典表的大数据查询方法。
相比于现有技术，本发明的优点在于：
现有技术通常需要写代码实现脚本实现从不同数据库同步数据的功能，并需要单独部署以及定时启动脚本并关注脚本执行情况；本发明技术方案只需要创建字典表就能实现数据同步，并能指定数据库，同步条件和周期等，ClickHouse会根据创建字典表的语句自动执行定期更新字典表并动态加载缺失值，记录执行结果，后期维护成本低。
本发明基于ClickHouse的字典表实现，以哈希表的形式把数据保存在内存中，可以包含任意数量的带有任意标识符的元素，实现了数据查询过程中多表JOIN的效果，并有效解决多表关联时对性能的损耗，在实践中，键的数量可以达到数千万项，这是其他的OLAP数据库所不支持的特性。本发明提供了一种基于ClickHouse的外部字典，在大数据可视化分析时直接使用映射值进行数据分析的方案，不是扫描全表，有效解决了多表关联时对性能的损耗，此方案实现简单，无需维护，并且对性能几乎无损。
附图说明
图1为本发明一个实施例的更新外部字典表的流程示意图；
图2为本发明一个实施例的配置外部字典表的流程示意图；
图3为本发明一个实施例的外部字典表与数据表的UML示意图；
图4为本发明一个实施例的以某一字段查询的流程示意图。
具体实施方式
下面结合说明书附图和具体的实施例，对本发明作详细描述。
本实施例以对财务数据中的货币字段创建字典表为例，详细说明一种基于ClickHouse字典表的数据分析方法，包括字典表的创建、更新、配置和查询。本实施例基于ClickHouse字典表，创建货币字典表，并配置字典表中数据内容，设置更新周期对字典表中参数进行更新，在大数据查询时，通过调用货币字典表中字段参数，快速读取该参数匹配的映射值，实现数据的快速读取和处理，提高数据分析效率。
ClickHouse字典表是ClickHouse提供的一种简单实用的存储媒介，以键值和属性映射的形式定义数据，字典中的数据会主动或被动加载到内存之中，并支持动态更新。由于字典数据常驻内存特性，比较适合保存常量或者经常使用的维度表数据，以避免不必要的JOIN连接数据，提高数据处理效率。
创建字典表：
创建货币字典表dict_currency，由字典表创建模块负责，如图1所示，包括如下步骤：
1、使用CREATE DICTIONARY 接口创建生成货币字典表的脚本
（1）货币符号和名称的数据源在MySQL数据库的currency表上，所以设置SOURCE接口指定数据源为MySQL，并配置端口、账号、密码等信息。ClickHouse支持通过连接指定数据库、http请求API 或读取文件的方式获取数据。
（2）根据键-值要求，设置从currency表里同步所需要的字段，如id、name、country到货币字典表dict_currency。本实施例需要向数据分析人员返回货币id和名称，所以，字典表中包括字段id、name。
（3）根据键-值数据的更新频率，设置LIFETIME接口指定字典表dict_currency的更新周期。因为货币符号对应的名称和国家信息很少更新，所以设置为1-2小时更新一次；
创建字典表的完整SQL语句如下：
CREATE DICTIONARY dict_currency
(
id String,
name String,
country String
)
PRIMARY KEY id
SOURCE(MYSQL(
port 3306
user 'user'
password '1234'
db 'medsdk'
table 'currency'
))
LAYOUT(COMPLEX_KEY_HASHED())
LIFETIME(MIN 3600 MAX 7200);
以上语句创建了名称为dict_currency的字典表，包括id、name、country三个字段，数据源为medsdk数据库的currency表，端口3306、用户名为user、密码为1234，更新周期为3600秒至7200秒。
2、执行脚本在ClickHouse生成外部字典表dict_currency，此时在字典表里已同步有数据。
已创建好的字典表更新流程图如图2所示。更新字典表需先判断是否达到创建字典表时设置的更新周期，如果没有达到更新周期则等待一段时间再判断，如果达到更新周期，再判断ClickHouse服务器的负载是否满足可以更新字典表dict_currency的条件，为保证系统的稳定性，在字典表更新时，如果ClickHouse服务器的负载无法满足后续处理条件，更新流程进入等待程序，当ClickHouse服务器的负载满足更新条件执行字典表dict_currency的更新，将数据表currency中的对应字段数据同步到字典表中。
配置dataCube：
解析配置货币字典表dict_currency信息，由dataCube配置模块实现，包括如下步骤：
（1）加载dataCube的配置，循环读取并解析dimension字段，找到currency_id字段，currency_id字段是需要以键-值映射方式显示的字段。
（2）在currency_id字段的配置信息上添加外部字典表dict_currency的配置，在map结构上设置table（外部字典表名）为dict_currency，设置name（以映射值方式显示的字段名）为dict_currency的字段名name，设置id（和currency_id字段对应的字段名）为dict_currency的字段名id。
配置样例，yaml格式：
- id: currency_id
name: Currency
kind: string
map:
table: dict_currency
name: name
id: id
（3）因currency_id字段配置了外部字典表dict_currency，所以解析生成关联的字典结构，通过currency_id可以关联到相应的字典表；如果dimension字段没有配置外部字典表，不生成关联的字典结构。
dimension字段中可能还包括除currency_id之外的其他字段，如果其他字段也要像currency_id那样需要以键-值映射方式显示，那么也要像（2）中所说那样进行配置。循环读取并配置完所有dimension字段才结束。
结合图3，图3中示出的数据库表finance_table中具有currency_id字段，当数据分析人员的查询指令是对currency_id进行查询时，dimension字段中包括currency_id字段，查询指令中的currency_id可以直接关联到字典表。但为了方便数据分析人员使用系统，数据分析人员利用查询页面发出的查询指令中可能不是currency_id，而是方便数据分析人员理解的名词，如“货币ID”，而相关数据表中并没有“货币ID”字段，那么，需要在dimension字段中进行映射配置，将“货币ID”映射为currency_id，再进行（2）中的配置。
数据分析人员发出的查询指令还有可能是个结果值，需要通过数据库中的字段值进行计算得出，如需要查询的是结果值，则引入measure字段进行计算，measure字段表示数值型数据，如请求数100，点击数10，点击率0.1等，此类数据的类型为数值，不需要配置字典表，可根据查询指令需求进行计算；例如需要知道某个人的在某单位的工作年限X，但数据库中存储了这个人的入职年份X1和离职年份X2，通过measure字段可直接计算X2-X1的数值。
图4为外部字典表与数据表的UML示意图。dict_currency为财务报表finance_table的currency_id列的字典表，dict_customer为财务报表finance_table的customer_id列的字典表。在进行大数据查询currency_id列的时候，返回给前端的数据会添加上字典表dict_currency的name，如果需要，还可以同时返回country。
数据查询：
下面对利用字典表对某一字段进行数据查询进行详细介绍。
图4为流程示意图。以Currency进行数据查询，由SQL语句生成与执行模块实现，包括如下步骤：
（1）服务器端收到前端需要返回按Currency分组的收入数据，分组参数是“currency_id”，搜索参数是“人民”。
（2）服务器端读取dataCube配置信息，dimension字段中包括currency_id，并且配置了字典表dic_currency信息，currency_id字段有关联字典结构。
1）动态生成符合ClickHouse规范的SQL查询语句，语句如下：
SELECT currency_id AS id,
sum(income) AS income
FROM finance_table
GROUP BY currency_id
2）根据与currency_id字段关联的字典结构，在SELECT表达式中生成获取currency_id映射值name的语句，其中name为字典表中映射值字段名；修改后的SQL查询语句如下：
SELECT currency_id AS id,
dictGet('dict_currency', 'name', tuple(currency_id)) AS name，
sum(income) AS income
FROM finance_table
GROUP BY currency_id
3）在WHERE表达式中添加使用LIKE运算符与通配符“%”搜索匹配“人民”字段的语句，修改后的SQL查询语句如下：
SELECT currency_id AS id,
dictGet('dict_currency', 'name', tuple(currency_id)) AS name，
sum(income) AS income
WHERE name LIKE '%人民%'
FROM finance_table
GROUP BY currency_id
如果不需要前端传递的查询指令中没有搜索参数“人民”，则查询语句中就不包括“WHERE name LIKE '%人民%'”的语句。
4）执行查询语句，把获取到id，name，income数据以列表的方式返回给前端；结构如下：
[
{
"id": 156,
"name": "人民币",
"income": 1000
}
]
5）前端显示name字段的值给数据分析人员查看，数据分析人员看到就是收入人民币1000元的信息。
考虑到货币id对应的名称name有很多，全部显示给前端供数据分析人员选择将耗时很长，上述示例通过参数“人民”进行限缩性查询，利用“人民”这两个汉字进行正则匹配，数据分析人员最终看到的货币列表里只包含“人民”二字的“人民币”。
假设服务器端收到前端需要返回按Currency分组的收入数据，分组参数不是“currency_id”，而是“C_id”，搜索参数是“人民”。此时，在dataCube配置时，dimension字段需要将C_id映射currency_id，同时配置有字典表信息。在读取dataCube配置信息时，发现C_id映射currency_id，仍然可以采用前文的SQL查询语句实现。
假设服务器端收到前端分组参数是“X”，含义是工作年限，数据库表中没有X字段，只有入职年份X1和离职年份X2，X可以通过离职年份X2-入职年份X1得到。此时因为需要查询的是结果值，在dataCube配置时，设置measure字段进行X2-X1的计算，因为measure字段为的数据类型为数值，可直接进行计算，并在读取dataCube配置信息时通过查询X2、X1获得数据再相减得到最终值返回前端。本发明所述数据配置方式灵活，根据不同的需求可选择配置字典表或不配置，以获取最快的查询效率。
以上示意性地对本发明创造及其实施方式进行了描述，该描述没有限制性，在不背离本发明的精神或者基本特征的情况下，能够以其他的具体形式实现本发明。附图中所示的也只是本发明创造的实施方式之一，实际的结构并不局限于此，权利要求中的任何附图标记不应限制所涉及的权利要求。所以，如果本领域的普通技术人员受其启示，在不脱离本创造宗旨的情况下，不经创造性的设计出与该技术方案相似的结构方式及实施例，均应属于本专利的保护范围。
