如何使用原生ClickHouse函数和表引擎在两个数据库之间迁移数据
引言
这篇文章还是关于 clickhouse 提供 postgres 集成系列文章的一部分。在上一篇文章中，我们探讨了 postgres 函数和表引擎，并以分析工作负载为例，演示了如何将事务数据从 postgres 迁移到 clickhouse。在这篇文章中，我们将展示如何结合使用 postgres 数据与流行的 clickhouse 字典功能来加速查询——特别是连接。在文章最后，我们将展示如何使用 postgres 表引擎将分析查询的结果从 clickhouse 推回 postgres。当用户需要在终端用户应用程序中显示汇总数据，但又希望将统计数据的繁重计算工作卸载给 clickhouse 时，就可以利用这种“反向 etl”过程。
如果你想更深入地研究这些示例并重现它们，clickhouse cloud 是一个很好的起点——启动一个集群并获得 300 美元的免费额度，加载数据，处理下基础设施，然后进行查询！
对于本文的示例，我们还是只使用 clickhouse cloud 的一个开发实例。对于 postgres 实例，我们还继续使用 supabase，它提供的免费套餐已足够我们的示例使用。本文假设用户已经将英国房价数据集加载到 clickhouse，这是上一篇博文中的一个步骤。数据集加载也可以不使用 postgres，而是使用这里列出的步骤。
使用基于 postgres 的词典
正如我们在之前的博文中重点介绍的那样，字典可以用来加速 clickhouse 查询，特别是涉及连接的时候。考虑这样一个例子，我们的目标是找出英国在过去 20 年里价格变化最大的地区（根据 iso 3166-2）。请注意，iso 3166-2 编码不同于邮政编码，它代表的区域更大，但更重要的是，它在 superset 这样的工具中可视化这类数据时非常有用。
在 join 时，我们要使用一个邮政编码到区域编码的映射表，可以下载并加载到 codes 表中，如下所示。数据有 100 多万行，加载到 supabase 免费实例大约需要一分钟。假设这份数据现在只在 postgres 中，所以我们将在 postgres 中连接这个数据来响应查询。
注意：iso 3166-2 编码到邮政编码的映射表是从房价数据集生成的，并使用了 play.clickhouse.com 环境中的地区编码列表。虽然这个数据集可以满足我们的需求，但并不完整或详尽，仅涵盖房价数据集中的邮政编码。用于生成文件的查询可以从这里获取。
wget https://datasets-documentation.s3.amazonaws.com/uk-house-prices/postgres/uk_postcode_to_iso.sqlpsql -c create table uk_postcode_to_iso( id serial, postcode varchar(8) primary key, iso_code char(6));psql -c create index on uk_postcode_to_iso (iso_code);psql select postcode1, month, avg_price, quantile_prices from summary_prices where postcode1='ba5' and type='detached' and is_new=0 and duration='freehold' limit 10; postcode1 | month | avg_price | quantile_prices-----------+------------+-----------+-------------------------------------------- ba5 | 1995-01-01 | 108000 | {64000,100000,160000,160000,160000,160000} ba5 | 1995-02-01 | 95142 | {86500,100000,115000,130000,130000,130000} ba5 | 1995-03-01 | 138991 | {89487,95500,174750,354000,354000,354000} ba5 | 1995-04-01 | 91400 | {63750,69500,130000,165000,165000,165000} ba5 | 1995-05-01 | 110625 | {83500,94500,149750,170000,170000,170000} ba5 | 1995-06-01 | 124583 | {79375,118500,173750,185000,185000,185000} ba5 | 1995-07-01 | 126375 | {88250,95500,185375,272500,272500,272500} ba5 | 1995-08-01 | 104416 | {67500,95000,129750,200000,200000,200000} ba5 | 1995-09-01 | 103000 | {70000,97000,143500,146000,146000,146000} ba5 | 1995-10-01 | 90800 | {58375,72250,111250,213700,223000,223000}(10 rows)小结
在本系列文章中，我们展示了 clickhouse 和 postgres 的互补性，并通过示例演示了如何使用原生 clickhouse 函数和表引擎轻松地在两个数据库之间迁移数据。在这篇文章中，我们介绍了基于 postgres 的字典，以及如何使用它来加速涉及频繁变化数据集的查询的连接。最后，我们执行了一个“反向 etl”操作，将分析查询的结果推回 postgres，供可能面向用户的应用程序使用。
Zikko推便携式雷电3扩展坞，满足配件携带不便需求
快讯：阿里巴巴将回购250亿美元股票 苹果回应iOS15.4正式版续航翻车
投影机关机快缩短灯泡寿命
用常规的电容，电感的观点巧析传输线阻抗的概念
8个技术证明 AI不等于神经网络
如何使用原生ClickHouse函数和表引擎在两个数据库之间迁移数据
韩国被卡脖子的日本技术和材料都有哪些?
BAT都已入局，到底什么才是真正的AI？
PDF执行OCR使用指南（仅适用于万兴PDF专业版）
高通自动驾驶项目生态构建和安全仍是难点
如何解决电厂环境下的烟道腐蚀问题
STM32如何实现可调频率、 占空比的PWM波形，且可指定输出脉冲个数？
AI视觉分析技术在城和市农村电动自行车违法管理中的应用
灰尘传感器／粉尘传感器DSM501的特点及应用分析
风光互补监控供电系统—石油管道应用
Window 10操作系统的v1999和1903受追捧，占比已接近80%
分布式发电技术有何特点?
南京8所学校将照明灯改造为LED灯 观察不同光源是否对视力发展有影响
什么样的温度+湿度组合，是体感最舒适的空气环境_温湿度传感器
浅谈PLC控制器频率与伺服驱动器和负载转速
这篇文章还是关于 clickhouse 提供 postgres 集成系列文章的一部分。在上一篇文章中，我们探讨了 postgres 函数和表引擎，并以分析工作负载为例，演示了如何将事务数据从 postgres 迁移到 clickhouse。在这篇文章中，我们将展示如何结合使用 postgres 数据与流行的 clickhouse 字典功能来加速查询——特别是连接。在文章最后，我们将展示如何使用 postgres 表引擎将分析查询的结果从 clickhouse 推回 postgres。当用户需要在终端用户应用程序中显示汇总数据，但又希望将统计数据的繁重计算工作卸载给 clickhouse 时，就可以利用这种“反向 etl”过程。
如果你想更深入地研究这些示例并重现它们，clickhouse cloud 是一个很好的起点——启动一个集群并获得 300 美元的免费额度，加载数据，处理下基础设施，然后进行查询！
对于本文的示例，我们还是只使用 clickhouse cloud 的一个开发实例。对于 postgres 实例，我们还继续使用 supabase，它提供的免费套餐已足够我们的示例使用。本文假设用户已经将英国房价数据集加载到 clickhouse，这是上一篇博文中的一个步骤。数据集加载也可以不使用 postgres，而是使用这里列出的步骤。
使用基于 postgres 的词典
正如我们在之前的博文中重点介绍的那样，字典可以用来加速 clickhouse 查询，特别是涉及连接的时候。考虑这样一个例子，我们的目标是找出英国在过去 20 年里价格变化最大的地区（根据 iso 3166-2）。请注意，iso 3166-2 编码不同于邮政编码，它代表的区域更大，但更重要的是，它在 superset 这样的工具中可视化这类数据时非常有用。
在 join 时，我们要使用一个邮政编码到区域编码的映射表，可以下载并加载到 codes 表中，如下所示。数据有 100 多万行，加载到 supabase 免费实例大约需要一分钟。假设这份数据现在只在 postgres 中，所以我们将在 postgres 中连接这个数据来响应查询。
注意：iso 3166-2 编码到邮政编码的映射表是从房价数据集生成的，并使用了 play.clickhouse.com 环境中的地区编码列表。虽然这个数据集可以满足我们的需求，但并不完整或详尽，仅涵盖房价数据集中的邮政编码。用于生成文件的查询可以从这里获取。
wget https://datasets-documentation.s3.amazonaws.com/uk-house-prices/postgres/uk_postcode_to_iso.sqlpsql -c create table uk_postcode_to_iso( id serial, postcode varchar(8) primary key, iso_code char(6));psql -c create index on uk_postcode_to_iso (iso_code);psql select postcode1, month, avg_price, quantile_prices from summary_prices where postcode1='ba5' and type='detached' and is_new=0 and duration='freehold' limit 10; postcode1 | month | avg_price | quantile_prices-----------+------------+-----------+-------------------------------------------- ba5 | 1995-01-01 | 108000 | {64000,100000,160000,160000,160000,160000} ba5 | 1995-02-01 | 95142 | {86500,100000,115000,130000,130000,130000} ba5 | 1995-03-01 | 138991 | {89487,95500,174750,354000,354000,354000} ba5 | 1995-04-01 | 91400 | {63750,69500,130000,165000,165000,165000} ba5 | 1995-05-01 | 110625 | {83500,94500,149750,170000,170000,170000} ba5 | 1995-06-01 | 124583 | {79375,118500,173750,185000,185000,185000} ba5 | 1995-07-01 | 126375 | {88250,95500,185375,272500,272500,272500} ba5 | 1995-08-01 | 104416 | {67500,95000,129750,200000,200000,200000} ba5 | 1995-09-01 | 103000 | {70000,97000,143500,146000,146000,146000} ba5 | 1995-10-01 | 90800 | {58375,72250,111250,213700,223000,223000}(10 rows)小结
在本系列文章中，我们展示了 clickhouse 和 postgres 的互补性，并通过示例演示了如何使用原生 clickhouse 函数和表引擎轻松地在两个数据库之间迁移数据。在这篇文章中，我们介绍了基于 postgres 的字典，以及如何使用它来加速涉及频繁变化数据集的查询的连接。最后，我们执行了一个“反向 etl”操作，将分析查询的结果推回 postgres，供可能面向用户的应用程序使用。
Zikko推便携式雷电3扩展坞，满足配件携带不便需求
快讯：阿里巴巴将回购250亿美元股票 苹果回应iOS15.4正式版续航翻车
投影机关机快缩短灯泡寿命
用常规的电容，电感的观点巧析传输线阻抗的概念
8个技术证明 AI不等于神经网络
如何使用原生ClickHouse函数和表引擎在两个数据库之间迁移数据
韩国被卡脖子的日本技术和材料都有哪些?
BAT都已入局，到底什么才是真正的AI？
PDF执行OCR使用指南（仅适用于万兴PDF专业版）
高通自动驾驶项目生态构建和安全仍是难点
如何解决电厂环境下的烟道腐蚀问题
STM32如何实现可调频率、 占空比的PWM波形，且可指定输出脉冲个数？
AI视觉分析技术在城和市农村电动自行车违法管理中的应用
灰尘传感器／粉尘传感器DSM501的特点及应用分析
风光互补监控供电系统—石油管道应用
Window 10操作系统的v1999和1903受追捧，占比已接近80%
分布式发电技术有何特点?
南京8所学校将照明灯改造为LED灯 观察不同光源是否对视力发展有影响
什么样的温度+湿度组合，是体感最舒适的空气环境_温湿度传感器
浅谈PLC控制器频率与伺服驱动器和负载转速