Clickhouse字典关联外部 MySQL 表联合查询实践

前言

clickhouse 可以将源数据加载进 clickhouse 作为字典表使用，字典表可以理解为 clickhouse 中的一张特殊表，我们在查询 clickhouse 表中的数据的时候不需要 JOIN 就可以直接查询字典表中的数据，非常方便，快速。
我刚好在工作场景中遇到了一个非常适合使用 clickhouse 字典表的场景，在这里记录总结下：

  • 我们在 clickhouse 中一张原始数据表 ods_h53_file

    我们对 ods 进行了数据清洗，建模了 dwd 表 dwd_h53_trigger_type

  • 现在外部系统需要查询 ods 表中的数据，但是又需要用到清洗后的 ods 表中的数据，为了对查询速度进行优化不进行 JSON 关联查询，我们使用了 clickhouse 的字典对 dwd_h53_trigger_type进行了缓存生成了一张字典 h53_trigger_type，在查询 ods_h53_file表中的数据时可以直接关联字典 h53_trigger_type查询 dwd 表中的数据。

  • 后边外部系统又需要关联 MySQL 和 clickhouse 表的数据，关联查询 clickhouse ods_h53_file表和 MySQL t_event_package_parse表。

如果不使用 clickhouse 的字典，就需要先从 clickhouse 中查询出对应数据，然后再从 MySQL 过滤查询出对应的数据，最后再在代码内存中进行关联清洗，效率极低，而且很 LOW。

  • 这个时候可以使用 clickhouse 的字典，将外部数据源加载为字典，在查询 clickhouse 表的时候关联查询，效率高，使用简单，极力推荐。

创建库

下边的所有 clickhouse SQL 都是在 cloud_data库下执行的，需要先创建库：
create DATABASE cloud_data

创建表

这里对使用到的相关表的创建进行说明，包括 clickhouse 使用到的表： ods_h53_file,dwd_h53_trigger_type，MySQL 使用到的表 t_event_package_parse，以及 clickhouse 中的字典：h53_trigger_type（源数据为clickhouse 内部表）, dict_event_package_parse(源数据为 MySQL 外部表)。

注：
如果是在 clickhouse 集群上创建表或字典，需要制定 cluster，例如：
CREATE TABLE cloud_data.ods_h53_file on cluster 集群名称

clickhouse 表 ods_h53_file DDL

CREATE TABLE cloud_data.ods_h53_file (
  `version` String COMMENT '智驾版本,对应config文件中的software_version',
  `trigger_type` String COMMENT '触发类型',
  `vin` String COMMENT '车架号',
  `day_` Date DEFAULT toDate ( timestamp_ / 1000 ) COMMENT '数据时间-天',
  `second_` DateTime DEFAULT toDateTime ( timestamp_ / 1000 ) COMMENT '数据时间-秒',
  `timestamp_` Int64 COMMENT '秒时间戳,对应json中的timestamp',
  `packet_name` String COMMENT '包名',

) ENGINE = MergeTree()
PARTITION BY toYYYYMM(day_)
ORDER BY (vin, day_, second_)
SETTINGS index_granularity = 8192;

clickhouse 表 dwd_h53_trigger_type DDL

create table cloud_data.dwd_h53_trigger_type
(
    `trigger_type` String COMMENT '触发类型',
    `trigger_name` String COMMENT '触发名字',
    `func_type` String COMMENT '事件对应功能'
) ENGINE = MergeTree()
PRIMARY KEY (trigger_type)
ORDER BY
    (trigger_type) SETTINGS index_granularity = 8192 COMMENT 'H53触发器类型';
  • 该表后边会作为源数据创建一张字典，这里的 PRIMARY KEY 很重要，这个是源数据同步至字典的重要字段，而且后边查询字典数据的时候也是通过主键作为过滤条件的。

mysql 表 t_event_package_parse DDL

CREATE TABLE t_event_package_parse (
    id INT AUTO_INCREMENT PRIMARY KEY,
    package_name VARCHAR(255) UNIQUE,
    state ENUM('0', '1', '2'),
    create_time DATETIME,
    create_by VARCHAR(16),
    update_time DATETIME,
    update_by VARCHAR(16)
);

clickhouse 字典 DDL

注：
这里为了测试方便，在创建字典的时候 LIFETIME 设置的时间很短，更新频率较快，时间较短，这样在我们往源表插入数据的时候可以快速同步至字典中。
在实际的生产环境中需要根据具体的业务场景确定，后边会对 LAYOUT 和 LIFETIME 的配置进行说明。

使用 clickhouse 表 dwd_h53_trigger_type 作为数据源创建字典 h53_trigger_type：

create DICTIONARY cloud_data.h53_trigger_type
(
    `trigger_type` String,
    `trigger_name` String,
    `func_type` String
) 
PRIMARY KEY trigger_type 
SOURCE(CLICKHOUSE(
  HOST '127.0.0.1'
  PORT 9000
  USER 'root'
  PASSWORD '123456'
  DB 'cloud_data'
  TABLE 'dwd_h53_trigger_type'
  )) 
LAYOUT(COMPLEX_KEY_HASHED()) 
LIFETIME(10);
  • 这里需要注意主键和数据源表保持一致，使用 trigger_type
  • SOURCE配置需要改为 clickhouse 连接信息，需要注意这里的 DB 和 TABLE 不要写错了，这里指的是你需要将哪张表的数据作为源数据。

使用 MySQL 表 t_event_package_parse作为数据源创建字典dict_event_package_parse:

create DICTIONARY cloud_data.dict_event_package_parse
(
    `package_name` String,
    `state` String
) 
PRIMARY KEY package_name 
SOURCE(MYSQL(
  HOST '127.0.0.1'
  PORT 3306
  USER 'root'
  PASSWORD 'password'
  DB 'addcdata'
  TABLE 't_event_package_parse'
  )) 
LAYOUT(COMPLEX_KEY_HASHED()) 
LIFETIME(10);
  • 注意将SOURCE 改为 MYSQL

往 clickhouse 和 MySQL 表中插入测试数据
现在我们往clickhouse 表ods_h53_file,dwd_h53_trigger_type，MySQL 使用到的表 t_event_package_parse中插入几条测试数据：

-- 插入 ods_h53_file
insert into cloud_data.ods_h53_file values('H53A3632801FK', 'RPA中断', 'LDP91C968PE201357', '2024-04-19', '2024-04-19 00:00:00', 1713456000, 'LDP91C968PE201357_20240419224750_A_2');
insert into cloud_data.ods_h53_file values('H53A3632801FK', 'RPA中断2', 'LDP91C968PE201357', '2024-04-20', '2024-04-20 00:00:00', 1713542400, 'LDP91C968PE201357_20240420224750_A_2');
insert into cloud_data.ods_h53_file values('H53A3632801FK', 'RPA中断3', 'LDP91C968PE201357', '2024-04-21', '2024-04-21 00:00:00', 1713628800, 'LDP91C968PE201357_20240421224750_A_2');

-- 插入 dwd_h53_trigger_type
insert into cloud_data.dwd_h53_trigger_type values('RPA', 'RPA中断', 'RPA');

-- 插入 MYSQL 表 t_event_package_parse
INSERT INTO `addcdata`.`t_event_package_parse` (`package_name`, `state`) VALUES ('LDP91C968PE201357_20240420224750_A_2', '0');

插入完后我们验证下字典表中是否已经有数据了：
image.png

联合查询语句

当我们创建好内部字典和外部字典后，我们就可以通过一条 clickhouse SQL 联合查询 clickhouse 与 MySQL 中的数据，并且不需要 JOIN，查询速度快，简单。

SELECT 
       `vin` AS `vin`,
       version, 
       toString(parseDateTimeBestEffort(splitByChar('_', packet_name)[2])) as event_time,
       -- 这一行就是查询内部数据源字典中的数据
       dictGetStringOrDefault('cloud_data.h53_trigger_type', 'trigger_name', trigger_type, trigger_type) AS `func`,
       -- 这一行就是查询外部数据源 MySQL 字典中的数据
       dictGetStringOrDefault('cloud_data.dict_event_package_parse', 'state', packet_name, 0) AS `state`,
       `packet_name` AS `package_name`
FROM `cloud_data`.`ods_h53_file`
WHERE `day_` >= toDate('2024-04-18')
  AND `day_` <= toDate('2024-04-25')
  AND `second_` >= toDateTime('2024-04-18 00:00:00')
  AND `second_` < toDateTime('2024-04-25 00:00:00')
  AND splitByChar('_', packet_name)[3] = 'A'
  AND `vin` = 'LDP91C968PE201357'
  AND func like '%RPA%'
  AND package_name like '%LDP91C968PE201357_20240420224750_A_2%'
GROUP BY `vin`,
         `version`,
         `func`,
         `packet_name`
ORDER BY event_time DESC
LIMIT 1000;
  • 在查询字典中的数据时，不需要 JOIN， 可以直接通过 dict 函数即可，dictGetStringOrDefault 参数说明：字典表，需要查询的字段，主键key, 默认值。

还有许多其他函数可以参考官方文档。
查询结果如下:
image.png

LAYOUT 说明

该配置是说明字典在内存中存储的方式，有许多中，下图是官网的一张截图：
image.png
从上图可以看出有很多种，具体说明可以参考官方文档
关于几种常见的可以看下这篇文章说的还是比较清晰的：
大数据ClickHouse进阶（十八）：数据字典类型

LIFETIME 说明

该配置是字典刷新加载源数据的频率，主要有三种使用方式：

  • 直接写入刷新间隔时间，比如 LIFETIME(10) , 10 s 刷新一次, 如果设置为0 代表不会刷新
  • 写入 min 和 max，比如LIFETIME(MIN 300 MAX 360),表示将在300到360s 间随机选择一个时间刷新，在使用分布式 clickhouse 集群时，避免同时大量字典更新导致资源不足，建议使用这种随机的方式，打散刷新频率。
  • 第三种比较高级，在创建字典时指定 invalidate_query 参数控制字典中的数据是否刷新，主要有两种方式：
    • 我们的源表需要有一个类似 update_time 的字段，当源表数据更新时，该字段一定会更新，这样 clickhouse 就知道需要更新字典了。
    • 查询表行数。

注：
但是我在验证第三种方式的时候，不知道为什么没有生效，有验证通过的小伙伴评论或者私信我下，万分感谢。

除了上边的三种配置方式，我们在配置完字典，或者需要立即刷新字典数据时可以通过下边的命令刷新：

# SYSTEM RELOAD DICTIONARY 字典表名
SYSTEM RELOAD DICTIONARY dict_event_package_parse

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/656724.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

MySQL8报错Public Key Retrieval is not allowedz 怎么解决？

MySQL8报错Public Key Retrieval is not allowedz 怎么解决？

问题描述 当我们使用数据库管理工具连接mysql8的时候&#xff0c;可能遇到报错&#xff1a; Public Key Retrieval is not allowed 解决办法 1、在连接属性中配置allowPublicKeyRetrieval设置为true 2、在连接URL中加上配置allowPublicKeyRetrieval为true
阅读更多...
如何彻底卸载sql sever2022

如何彻底卸载sql sever2022

目录 背景过程1、关闭sql sever服务2、打开控制面板&#xff0c;卸载SQL Sever3、手动删除 SQL Server 遗留文件4、清空注册表5、重启计算机以确保所有更改生效。 总结 背景 重装了电脑&#xff0c;安装sqlServer&#xff0c;一直报错&#xff0c;不成功&#xff0c;所以每次安…
阅读更多...
从0到100，渠道码如何成为SaaS企业增长的加速器

从0到100，渠道码如何成为SaaS企业增长的加速器

在当今数字化时代&#xff0c;企业的增长策略已不再是单一和线性的。渠道码的出现&#xff0c;为SaaS&#xff08;软件即服务&#xff09;企业提供了一种全新的、多维度的增长途径&#xff0c;真正实现了从0到100的快速增长。 一、渠道码的高可定制性和灵活性&#xff0c;为Sa…
阅读更多...
MySQL 数据类型和搜索引擎

MySQL 数据类型和搜索引擎

文章目录 【 1. 数据类型 】1.1 数值类型1.1.1 整型1.1.2 小数1.1.3 数值类型的选择 1.2 日期和时间YEAR 年TIME 时间DATE 日期DATETIME 日期时间TIMESTAMP 时间戳日期和时间的选择 1.3 文本字符串CHAR 固定字符串、VARCHAR 可变字符串TEXT 文本ENUM 枚举SET 集合字符串类型的选…
阅读更多...
c++（五）

c++（五）

c&#xff08;五&#xff09; 继承基类和派生类继承的格式继承的方式三种:public、private、protected 继承的规则多层继承多重继承 类与类的关系 继承 一个新类从已有的类那里获得其已有特性(属性、行为)&#xff0c;这种现象称为类的继承 基类和派生类 <1>从已有的类…
阅读更多...
外卖小程序开发指南：从源码开始构建高效的外卖平台

外卖小程序开发指南：从源码开始构建高效的外卖平台

今天&#xff0c;笔者将为您详细讲解如何从源码开始构建一个高效的外卖小程序&#xff0c;帮助您快速进入这一蓬勃发展的市场。 一、需求分析与设计 需求分析包括&#xff1a; 1.用户需求 2.市场需求 3.技术需求 二、前端开发 以下是开发步骤&#xff1a; -使用微信开发…
阅读更多...
创新指南｜降低 TikTok CPA 的 9 项专家策略

创新指南｜降低 TikTok CPA 的 9 项专家策略

企业在 TikTok 上投放广告&#xff0c;往往最想确保获得最佳的投资回报。然而&#xff0c;这往往说起来容易做起来难。您需要了解如何利用不同的营销工具、定位策略和创意执行来实现您的业务目标并提高成本效率。本文将分享 9 个行之有效的策略&#xff0c;助您有效降低 TikTok…
阅读更多...
Java面试八股之对threadLocal是怎么理解的

Java面试八股之对threadLocal是怎么理解的

对threadLocal是怎么理解的 概念与特点&#xff1a;ThreadLocal是Java提供的一个类&#xff0c;它允许你创建线程局部变量。每个线程都拥有自己的ThreadLocal变量副本&#xff0c;彼此之间互不影响&#xff0c;实现了变量在线程间的隔离。这意味着&#xff0c;即使多个线程使用…
阅读更多...
力扣算法之1068. 产品销售分析 I

力扣算法之1068. 产品销售分析 I

力扣传送门 题解 同时输出a,b两张表上的数据 我的解 SELECT product_name,year,price FROM Sales a LEFT JOIN Product b ON a.product_id b.product_id我的解注解 考sql联表语法
阅读更多...
go语言，golang，linux，unbantu安装

go语言，golang，linux，unbantu安装

查看系统是arm还是amd uname -m 命令可以显示系统的架构类型&#xff1a; uname -m输出可能是&#xff1a; x86_64&#xff1a;表示是 AMD64 架构&#xff08;即 64 位的 x86 架构&#xff09;。aarch64&#xff1a;表示是 ARM64 架构&#xff08;即 64 位的 ARM架构&#xf…
阅读更多...
node.js —— 解读http模块

node.js —— 解读http模块

目录 http模块&#xff1a; http模块的引入&#xff1a; 创建web服务器的基本步骤&#xff1a; web服务器的一些基本属性&#xff1a; 上述知识汇总案例&#xff1a; http模块&#xff1a; http模块的引入&#xff1a; const http require (http) 创建web服务器的基本步骤…
阅读更多...
MySQL中Undo-log是什么？有什么作用？

MySQL中Undo-log是什么？有什么作用？

2.6.1. Undo-log撤销日志 Undo即撤销的意思&#xff0c;通常也称为回滚日志&#xff0c;用来给MySQL撤销SQL操作的。 当一条写入类型的SQL执行时&#xff0c;都会记录Undo-log日志&#xff0c;Undo-log并不存在单独的日志文件&#xff0c;InnoDB默认是将Undo-log存储在xx.ibd…
阅读更多...
笔记-Apriori算法介绍（Python实现）

笔记-Apriori算法介绍（Python实现）

1.Apriori算法简介 Apriori算法是经典的挖掘频繁项集和关联规则的数据挖掘算法。A priori在拉丁语中指"来自以前"。当定义问题时&#xff0c;通常会使用先验知识或者假设&#xff0c;这被称作"一个先验"&#xff08;a priori&#xff09;。Apriori算法的名…
阅读更多...
vue2-computed，vue3+watch 前端实现列表搜索，结合filter+some+indexOf

vue2-computed，vue3+watch 前端实现列表搜索，结合filter+some+indexOf

vue2 computed实现 computed: {FBAAddressListComputed () {if (!this.fbaInput) return this.FBAAddressListconst lowerCaseInput this.fbaInput.toLowerCase()return this.FBAAddressList.filter((item) > {return [item.fbaCode, item.zipCode, item.countryCode, ite…
阅读更多...
马斯克拟打造xAI“算力超级工厂”，助力聊天机器人Grok

马斯克拟打造xAI“算力超级工厂”，助力聊天机器人Grok

KlipC报道&#xff1a;马斯克计划推出xAI超级计算机&#xff0c;为下一代人工智能聊天机器人Grok提供动力&#xff0c;直言这将是一个“算力超级工厂”&#xff0c;并希望在2025年秋季之前能运行起来。 xAI是马斯克去年创立的人工智能初创公司&#xff0c;“尽可能寻求真相”、…
阅读更多...
Vue3项目练习详细步骤(第二部分：主页面搭建)

Vue3项目练习详细步骤(第二部分：主页面搭建)

主页面搭建 页面主体结构 路由 子路由 主页面搭建 页面主体结构 在vuews目录下新建Layout.vue文件 主页面内容主体代码 <script setup> import {Management,Promotion,UserFilled,User,Crop,EditPen,SwitchButton,CaretBottom } from element-plus/icons-vue imp…
阅读更多...
Selenium探险家：驾驭Web自动化的秘籍与实战

Selenium探险家：驾驭Web自动化的秘籍与实战

Hi&#xff0c;我是阿佑&#xff0c;今天将带大伙们学会如何使用Selenium进行高效的网站测试&#xff0c;如何配置Selenium Grid实现分布式测试&#xff0c;以及如何预测和拥抱自动化测试的未来&#xff01; 文章目录 1. 引言2. 背景介绍2.1 Selenium概览2.2 Python与Selenium的…
阅读更多...
SwiftUI中EnvironmentObject的使用（多界面共享数据）

SwiftUI中EnvironmentObject的使用（多界面共享数据）

SwiftUI的EnvironmentObject是一个强大的工具&#xff0c;它允许你在多个视图之间共享数据(使用一个可观察对象)。当你有一个复杂的视图层次结构&#xff0c;并且需要在没有直接连接的视图之间共享相同的可观察对象时&#xff0c;它特别有用。 我们之前传递数据主要是通过init…
阅读更多...
奥枫软件Java要个16K遇到地狱级难度，醉了。。。

奥枫软件Java要个16K遇到地狱级难度，醉了。。。

我只能说地狱难度&#xff0c;没绝对把握就别去了。我凭借前辈的经验&#xff0c;和当时天时地利人和&#xff0c;六道题答得很不错&#xff0c;但还是没通过。我有备而来都没过&#xff0c;现场写那些应该都是白忙活了。 一面 1&#xff0c;分割一个整数。如123&#xff0c;结…
阅读更多...
视图【mysql数据库】

视图【mysql数据库】

目录 一、视图的创建、查看、修改、删除 二、cascaded、local检查选项 cascaded和local的区别 三、视图的更新 四、视图的作用 一、视图的创建、查看、修改、删除 二、cascaded、local检查选项 上面的几句SQL中&#xff0c;我们虽然给视图插入了id 30的数据&#xff0c;但…
阅读更多...
最新文章

Copyright @ 2022~2023