在Hyper-V虚拟机中安装、部署CentOS和ClickHouse数据库

原创 Oracle6 2021-06-04

1604

作者：曹挚（iyft_hws150@sina.com）

（现任职于京弘达上海医疗技术有限公司）

【引言】：ClickHouse是“俄版Ｇ.Ｏ.Ｏ.Ｇ.Ｌ.Ｅ”—Yandex在2016年6月15日开源的一个联机分析(OLAP)的列式数据库管理系统(DBMS)，据说在国内的使用情况如下：
今日头条——内部用ClickHouse来做用户行为分析，内部一共几千个ClickHouse节点，单集群最大1200节点，总数据量几十PB，日增原始数据300TB左右，大多数查询相应时间在几秒钟。
腾讯——内部用ClickHouse做游戏数据分析，并且为之建立了一整套监控运维体系。
携程——内部从18年7月份开始接入试用，目前80%的业务都跑在ClickHouse上。每天数据增量十多亿，近百万次查询请求。
快手——内部也在使用ClickHouse，存储总量大约10PB，每天新增200TB，90%查询小于3S。
鉴于ClickHouse数据库具有支持OLAP、支持SQL、支持近似计算、适合在线查询等特性，笔者在模拟环境下对一个TB级的数据表进行关键词模糊查找性能试验，以期解决在当前环境下查询效率低下的问题。

以下是一个提纲式的安装、配置、试验步骤，仅供参考，其中配套软件的下载地址、详细操作请读者查阅相关手册。

【关键词】 ClickHouse ,DataX， Oracle , OLAP，数据迁移，列式数据库，中间件,字符集，数据类型取值范围，多字节字符

1、环境准备

参数	CPU	内存	硬盘	操作系统
物理机	4 x IntelXeon 3.0	64GB	2TB SATA	Windows Server 2012 R2
虚拟机	1 x IntelXeon 3.0	8GB	72GB	CentOS 7.6.1860(X64)

1.1 在物理机启用Hyper-V服务器角色

点击【控制面板】——【启用和关闭Windows功能】——在“选择服务器角色”页面选中“Hyper-V”，一直“下一步”，直到安装完成，重启计算机。

1.2 新建用于连接外部网络的虚拟交换机

单击【管理工具】——【Hyper-V管理器】，选中计本地计算机，右键【虚拟交换机管理器】，“创建虚拟交换机”——输入虚拟交换机名称，如：centos7.6，连接类型选择“外部网络”、选择本地计算机的当前在用网卡，选中“允许管理操作系统共享此网络适配器”。

1.3 使用Hyper-V管理器中新建虚拟机

单击【管理工具】——【Hyper-V管理器】，在左侧计算机名上右键——【新建】——【虚拟机】，输入虚拟机名称“CentOS7.6”,分配8192MB内存，在网络配置选项中选中上一步创建的虚拟交换机，分配72GB硬盘空间，选择“从可启动的CD/DVD-ROM安装操作系统”，挂载CentOS7.6的安装介质映像文件到启动光驱，完成后虚拟机自动启动。

1.4 向虚拟机DVD驱动器挂载CentOS安装盘

在虚拟机创建完成后，也可以在“设置”——“SCSI控制器”——“DVD驱动器”，在介质选项处指定“映像文件”。使用此种挂载ISO介质的方法在以后的系统维护中会经常用到。

2. 在虚拟机中安装CentOS

2.1 选择Server with GUI安装模式

在选择语言和时区后，在安装概要（Installation Summary）界面，选中SOFTWARE SELECTION按钮，在弹出窗口中，左边Base Environment中，选择Server with GUI(带图形用户界面的服务器)，右侧各项目采用默认。

2.2 选择自定义分区选项I will configure partition

在安装概要（Installation Summary）界面，选中INSTALLATION DESTINATION按钮，在弹出窗口中选择“我要配置分区”（I will configure partition），分区结构如下（仅满足本次试验环境）：

启动分区：/boot 500MB 设备名：/dev/centos/boot
交换分区：/swap 7GB 设备名：/dev/centos/swap
起点分区： /root 42GB 设备名：/dev/centos/root
用户分区： /home 20GB 设备名：/dev/centos/home

2.3 配置网络地址、关闭防火墙

安装完毕虚拟机操作系统后，首次以root用户登录centos，在GUI界面依次单击**“应用程序”——“系统工具”——“设置”——“网络**”，配置有线连接“eth0”的IPV4/V6地址（注意：此处应该与物理机在同一可访问网段）。

查看、启动、停止、禁用防火墙：
Systemctl status firewalld.service —查看
Systemctl start firewalld.service —启动
Systemctl stop firewalld.service —停止
Systemctl disable firewalld.service —禁用

3. 为物理机安装SecureCRT、ODBC驱动

3.1 新建SecureCRT连接会话

在左侧Sessions上右键【NewSession】，通讯协议【SSH2】，HostName输入上一步指定的IP地址，用户名为root。
以root用户名、密码登录CentOS虚拟机，如果登录不成功，则首先确保虚拟机与物理机连通（关闭或允许数据包通过防火墙），常见故障是IP地址不处于可访问网段，或者是双方防火墙未正确设置。
如果会话显示的字符存在乱码，则在该会话的属性中将外观（Appearance）字符集（Characterencoding）设置为UTF-8。

3.2 安装ClickHouse ODBC驱动程序

ODBC驱动程序正常安装后，Windows2012还是无法正常创建ODBC数据源是由于操作系统补丁KB2919355缺失，而该补丁需要多个先决补丁正常才能正常安装（详见参考文献11）。一系列依赖补丁安装成功后安装最新版Microsoft Visual C++ 2015-2019 Redistributable 14.28 x64/x86，至此，ClickHouse ODBC Driver才能正常配置。

4. 在虚拟机中安装ClickHouse数据库

4.1 上传ClickHouse安装介质

在CentOS中创建clickhouse用户，授予该用户为系统管理员角色，此用户作为clickhouse数据库的管理员，下文中的上传文件，安装、维护数据库等日常工作均以此用户登录。
在物理机端启动CecureCRT，以clickhouse用户登录，以ALT+P启动SFTP会话，将clickhouse安装文件上传至虚拟机，其文件包括以下4个tgz压缩包：

4.2 安装clickhouse数据库

安装包上传完毕后，以clickhouse用户登录虚拟机，依次解压并安装数据库及其支持软件。

tar -xzvf clickhouse-common-static-20.10.2.20.tgz
sudo clickhouse-common-static-20.10.2.20/install/doinst.sh
tar -xzvf clickhouse-common-static-dbg-20.10.2.20.tgz
sudo clickhouse-common-static-dbg-20.10.2.20/install/doinst.sh
tar -xzvf clickhouse-server-20.10.2.20.tgz
sudo clickhouse-server-20.10.2.20/install/doinst.sh
tar -xzvf clickhouse-client-20.10.2.20.tgz
sudo clickhouse-client-20.10.2.20/install/doinst.sh

4.3 启动、停止、查看clickhouse服务：

systemctl status clickhouse-server —查看服务状态
systemctl start clickhouse-server —启动服务
systemctl stop clickhouse-server —停止服务

4.4 修改clickhouse配置文件，允许远程访问

修改/etc/clickhouse-server/config.xml配置文件，找到<listent_host>::</listen_host>标签，去掉注释；将remote_servers标签内的内容清空，保存配置文件后，重新启动clickhouse数据库。

4.5 验证clickhouse数据库

使用命令clickhouse-client登陆数据库，缺省用户名default，密码为空，执行以下命令：show databases，返回3个数据库（default、system、_temporary_and_external_tables）表示数据库安装配置成功。

这是ClickHouse数据库缺省的数据文件位置图：

5 在物理机上安装、配置DBeaver管理工具

5.1 连接ClickHouse数据库

DBeaver是目前为数不多的提供ClickHouse专用接口的数据库维护工具，使用前需先行导入驱动程序包，需要特别注意的是，ClickHouse官网提供的JDBC驱动程序无法单独使用，需要一组互相依赖的jar包才能访问该数据库，其所依赖文件列表详见下图：

5.2 配置Druid Oracle驱动以支持中文

Druid是包装在Oracle JDBC之外的字符集转换工具，它适合于源端Oracle数据库字符集不是中文或UTF-8的情况下。尽管druid没有原生的jdbc驱动强大，但它确实解决了字符集不匹配的问题，也是一个很好的免费解决方案。
新增一个驱动程序，其类名：com.alibaba.druid.proxy.DruidDriver
URL模板:jdbc:wrap-jdbc:filters=encoding:name=dbeaver:jdbc:oracle:thin:@{host}[:{port}]/{database}
另外，在连接属性下定义2个用户属性以实现字符集的自动转换：
clientEncoding: GBK
serverEncoding: ISO-8859-1

6. 在物理机上安装DataX中间件

6.1 为什么要用DataX中间件？

说真的，将一个TB级别的表的数据从Oracle迁移到ClickHouse是一个颇有难度的问题，目前批量数据迁移大概有三种方法：

1.csv导入

这种方法面临两个麻烦：一是将TB级的表从Oracle中导出为UTF-8编码的csv需要的时间和空间开销是巨大的；二是原表中的某些不可见字符在转换后会导致csv内部紊乱，修改这个巨大的文件是一个貌似不可能完成的任务，笔者在测试中发现导出的csv文件在导入ClickHouse数据库时经常报格式转换错误，只能选择丢弃部分数据来实现数据的不完全导入，这是难以令人接受的。

2.第三方工具，如flume,datax等

datax官网并没有提供ClickHouse支持，不过GitHub有热心同仁提供了源码，需要自己打包。另外，在Windows平台上运行DataX需要用户自行编译打包。

3.自己写代码，那是猴年马月的事了。

最后笔者选择尝试 oracle -> datax（Windows2012，即物理机） ->clickhouse（CentOS，即虚拟机）的技术路线实施数据迁移，这条路一波三折、披荆斩棘，好在最后登临碣石，以观沧海。

6.2 导入ClickHouse数据写入模块插件

由于DataX的插件体系支持绝大多数主流RDBMS，其中就包括Oracle，而寻找ClickHouse的写入模块（Writer）插件就是本次旅程的最后一块拼图了。感谢CSDN的@henry.zhu哥儿们，他把ClickHouse插件编译好了，足足花了俺50积分，henry这活儿干得不赖，只是这个驱动程序（clickhouse-jdbc-0.1.48.jar）的版本有些low。

6.3 配置初始加载的Job参数文件

配置DataX数据迁移的Job参数文件是轻松愉快的，采集端是JDBC for Oracle，写入端是JDBC for ClickHouse，截图如下：

6.4 Oracle源端字符集转换的替代方案

启动DataX的Oracle_to_ClickHouse迁移任务后，字符集的问题怎么都解决不了，笔者测试环境如下：

数据源：Oracle数据库字符集为US7ASCII，NLS地区为AMERICAN_AMERICA
中间件：DataX的操作系统CHCP代码页是中文简体，CODE：936
目标端：ClickHouse的数据库字符集为UTF-8

上述场景，无论是使用5.2节的Druid，抑或是在操作系统中定义NLS_LANG环境变量，甚至尝试将JDBC for Oracle的驱动模式从thin改为OCI，均无功而返。在郁闷之余，不禁开始怀疑一切：莫非是源端已识别，中间件转码有问题？还是中间件正常，目的端字符集转码有问题？反正一头雾水。

既然没啥出路，那就换个思路：正好以前的项目中使用cast_to_raw和cast_to_varchar2函数进行不同字符集的对称转换，何不在一个中文简体的环境下不做字符集转换试试？结果正如预期的那样，中文简体字符集的Oracle能够正确转换到UTF-8字符集ClickHouse中，问题得解，胜利在向我招手，曙光就在前头。

6.5 数据类型（UInt8）取值范围不一致出现的错误

这是一个小插曲：Oracle中的数值类型number（3）取值范围是【-999，999】，而ClickHouse的UInt8的取值范围是【0，255】，正好本次测试的源表有一个字段是患者年龄（AGE），我在ClickHouse中毫不犹豫地选择了UInt8，这个“想当然”的决定把我逗乐了，数据迁移日志报错，我怎么也没想到源表中有一条记录的患者年龄是“-181”岁，听完这个故事你可以笑着酣然入梦了。

6.6 缺省JDBC驱动的短连接响应失败的问题

错误症状：正常导入若干条数据后开始大量报错，日志信息为exception code 1002，failed to response，DataX异常退出，数据迁移中断。
这个错误是因为缺省的JDBC驱动程序版本太低，致使数据库连接池参数中某几个参数的缺省值太低所致，手工替换为高版本的驱动程序就搞定啦。

6.7 ORA-29275:部分多字节字符的处理

这是本试验的最后一个小case了：源表中有一个字段是患者的“相关诊断”，这个字段几乎是自由文本，里面有标点、数字、特殊字符等等，不一而足，DataX执行过程中报出多字节字符错误，最后采用TO_SINGLE_BYTE()/TO_NCHAR()函数进行强制转换后解决。至此，回头看这一路荆棘丛生的景色，真是不虚此行。

7. 测试表及转换视图

7.1 在ClickHouse中创建测试表

 create table lab_test_master(
>   TEST_NO String(16) NOT NULL,
>   PRIORITY_INDICATOR UInt8,
>   PATIENT_ID String(16),
>   VISIT_ID UInt8,
>   WORKING_ID String(8),
>   EXECUTE_DATE DATETIME,
>   NAME String(32),
>   NAME_PHONETIC String(64),
>   CHARGE_TYPE String(16),
>   SEX String(4),
>   AGE UInt8,
>   TEST_CAUSE String(100),
>   RELEVANT_CLINIC_DIAG String(200),
>   SPECIMEN String(8),
>   NOTES_FOR_SPCM String(256),
>   SPCM_RECEIVED_DATE_TIME DATETIME,
>   SPCM_SAMPLE_DATE_TIME DATETIME,
>   REQUESTED_DATE_TIME DATETIME default now() NOT NULL,
>   ORDERING_DEPT String(12) default '0000' NOT NULL ,
>   ORDERING_PROVIDER String(32),
>   PERFORMED_BY String(12),
>   RESULT_STATUS UInt8,
>   RESULTS_RPT_DATE_TIME DATETIME,
>   TRANSCRIPTIONIST String(32),
>   VERIFIED_BY String(32),
>   COSTS float,
>   CHARGES float,
>   BILLING_INDICATOR UInt8,
>   PRINT_INDICATOR UInt8,
>   SUBJECT String(32),
>   DATE_OF_BIRTH DATE,
>   REQUEST_SOURCE String(32),
>   PATIENT_SOURCE String(32),
>   VISIT_DATE DATE,
>   VISIT_NO UInt32,
>   SPCM_COLLECTOR String(32),
>   NOTES String(512),
>   INSURANCE_ATTR UInt8,
>   AUTOMATIC_AUDIT_STATUS UInt8)
>   ENGINE = MergeTree
>   PARTITION BY toYear(REQUESTED_DATE_TIME)
>   ORDER BY (REQUESTED_DATE_TIME ,ORDERING_DEPT);

7.2 源端的字符转换视图

CREATE OR REPLACE VIEW HISVIEW.MV_HIS_LAB_TEST_MASTER_CH AS
>   SELECT
>   d.TEST_NO,
>   PRIORITY_INDICATOR,
>   PATIENT_ID,
>   VISIT_ID,
>   WORKING_ID,
>   EXECUTE_DATE,
>   to_single_byte(UTL_RAW.CAST_TO_VARCHAR2(NAME)) NAME,
>   NAME_PHONETIC,
>   to_single_byte(UTL_RAW.CAST_TO_VARCHAR2(CHARGE_TYPE)) CHARGE_TYPE,
>   to_single_byte(UTL_RAW.CAST_TO_VARCHAR2(SEX)) SEX,
>   decode(sign(AGE),1,AGE,0) AGE,
>   to_single_byte(UTL_RAW.CAST_TO_VARCHAR2(TEST_CAUSE)) TEST_CAUSE,
>   to_single_byte(UTL_RAW.CAST_TO_VARCHAR2(RELEVANT_CLINIC_DIAG))
>   RELEVANT_CLINIC_DIAG,
>   to_single_byte(UTL_RAW.CAST_TO_VARCHAR2(SPECIMEN)) SPECIMEN,
>   to_single_byte(UTL_RAW.CAST_TO_VARCHAR2(NOTES_FOR_SPCM)) NOTES_FOR_SPCM,
>   SPCM_RECEIVED_DATE_TIME,
>   SPCM_SAMPLE_DATE_TIME,
>   REQUESTED_DATE_TIME,
>   ORDERING_DEPT,
>   to_single_byte(UTL_RAW.CAST_TO_VARCHAR2(ORDERING_PROVIDER))
>   ORDERING_PROVIDER,
>   PERFORMED_BY,
>   RESULT_STATUS,
>   RESULTS_RPT_DATE_TIME,
>   to_single_byte(UTL_RAW.CAST_TO_VARCHAR2(TRANSCRIPTIONIST)) TRANSCRIPTIONIST,
>   to_single_byte(UTL_RAW.CAST_TO_VARCHAR2(VERIFIED_BY)) VERIFIED_BY,
>   COSTS,
>   CHARGES,
>   BILLING_INDICATOR,
>   PRINT_INDICATOR,
>   to_single_byte(UTL_RAW.CAST_TO_VARCHAR2(SUBJECT)) SUBJECT,
>   DATE_OF_BIRTH,
>   REQUEST_SOURCE,
>   PATIENT_SOURCE,
>   VISIT_DATE,
>   VISIT_NO,
>   to_single_byte(UTL_RAW.CAST_TO_VARCHAR2(SPCM_COLLECTOR)) SPCM_COLLECTOR,
>   NOTES,
>   INSURANCE_ATTR,
>   1 AUTOMATIC_AUDIT_STATUS
>   FROM JXKP_VTECH_LAB_TEST_MASTER@DBLINK_HIS d;

8. 试验结果

8.1 数据迁移结果：

8.2 基于正则表达式的模糊查询结果：

由上图可见，在非索引字段上执行基于正则表达式的模糊查询，从1478w记录的表中检索出符合条件的340w条，耗时1.686s。当然，这只是个案。

9. 结语

这是一个数据迁移的试验，笔者将Oracle数据库中一个假定的TB级别的数据表通过DataX中间件工具成功地迁移到ClickHouse数据库中，其中涉及到如下几个问题：

数据库字符集的转换问题；
DataX中间件在Windows平台的编译运行问题；
不同数据库之间数据类型的取值范围问题；
数据迁移的效率问题。

上述问题只触及到数据迁移的极小部分，对于大规模的数据迁移，从方案制订到数据库选型、工具选择、模拟测试等是一个系统工程，要力争做到：功夫在前期，方案来兜底。

参考文献

ClickHouse中文官方网站https://clickhouse.tech/docs/zh/
使用DataX中间件将Oracle数据迁移至ClickHouse
https://blog.csdn.net/dustzhu/article/details/105425610
ClickHouse各类函数用法详解
https://blog.csdn.net/weixin_39992480/article/details/108589249
6亿数据秒级查询，ClickHouse太快了！
https://mp.weixin.qq.com/s/Jr96fYGb3MjwLBAm-c8o_w
ClickHouse的星型模式压力测试（Star Schema Benchmark）示例https://clickhouse.tech/docs/en/getting-started/example-datasets/star-schema/
超详细的CentOS7.4下载与图文安装
https://blog.csdn.net/qq_39135287/article/details/83993574
centos7.6离线安装clickhouse20.10.2.20
http://www.clickhouse.com.cn/topic/60052e33ba8f16b55dd0fffe
CLICKHOUSE 安装和远程登录开启
https://www.freesion.com/article/96311306174/
dbeaver连接Oracle中文乱码的解决方案—druid
https://www.codercto.com/a/117514.html
ORA-29275:部分多字节字符的处理
https://blog.csdn.net/keueng/article/details/48808085?utm_medium=distribute.pc_relevant.none-task-blog-2~default~BlogCommendFromMachineLearnPai2~default-1.control&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2~default~BlogCommendFromMachineLearnPai2~default-1.control
Win2012 安装VC14(VC2015)失败,0x80240017未指定的错误，解决办法
https://blog.csdn.net/yiyihuazi/article/details/78410667?locationNum=3&fps=1
Clickhouse JDBC多次连接,发送数据到clickhouse中出现响应连接失败
https://blog.csdn.net/u011835473/article/details/98877903
oracle 迁移到clickhouse 45亿条数据
https://www.cnblogs.com/cbugs/p/13388258.html

最后修改时间：2021-06-04 14:15:52

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者