Clickhouse安装及使用/clickhouse-mysql安装

本文转自：https://blog.csdn.net/xyb1206/article/details/82982688

1.安装clickhouse环境

ClickHouse对Debian/Ubuntu支持较好，但是工作当中服务器我们一般用CentOs。今天我们使用CentOs7来安装一下ClickHouse。
操作系统版本：CentOS Linux release 7.5.1804 (Core)

检查一下是否支持SSE 4.2指令集:

grep -q sse4_2 /proc/cpuinfo && echo "SSE 4.2 supported" || echo "SSE 4.2 not supported"

2.下载安装包

1. 下载地址：

https://repo.yandex.ru/clickhouse/rpm/stable/x86_64/
下载一下四个包：
clickhouse-client-18.12.17-2.noarch.rpm
clickhouse-server-common-18.12.17-2.noarch.rpm
clickhouse-server-18.12.17-2.noarch.rpm
clickhouse-client-18.12.17-2.noarch.rpm

2. 开始安装：

rpm -ivh clickhouse-server-common-18.12.17-2.noarch.rpm
rpm -ivh clickhouse-server-18.12.17-2.noarch.rpm 
rpm -ivh clickhouse-common-static-18.12.17-2.x86_64.rpm
rpm -ivh clickhouse-client-18.12.17-2.noarch.rpm 

注意：在安装第二步会出现依赖错误，需下载依赖，yum install ODBC
再次安装clickhouse-server没问题了。
安装后配置路径：cd /etc/clickhouse-server/

3. 启动命令：

clickhouse-server --config-file=/etc/clickhouse-server/config.xml
或：
systemctl stop clickhouse-server
systemctl start clickhouse-server

3.clickhouse配置

1. 放开远程访问：
   vi /etc/clickhouse-server/config.xml
   修改服务器的配置文件/etc/clickhouse-server/config.xml，第65行，放开注释即可，修改之后的内容如下：

<listen_host>::</listen_host>
<listen_host>127.0.0.1</listen_host>

2. 内存权限设置：
   vi /etc/clickhouse-server/users.xml

 <default>
    <!-- Maximum memory usage for processing single query, in bytes. -->
    <max_memory_usage>26800000000</max_memory_usage>

    <!-- Use cache of uncompressed blocks of data. Meaningfu

4.本地客户端使用clickhouse-client

1. 启动客户端命令：

#clickhouse-client

2. 创建数据库：(遵循mysql创建语句)

CREATE  DATABASE  [ IF  NOT  EXISTS ]  db_name

3. 创建数据表：(末尾需要增加表引擎)

CREATE TABLE F_SZ_RYXX 
(
    PERSON_ID String,
    NBXH String,
    NAME String,
    CERTYPE String,
    BLICTYPE String,
    CERNO String,
    HJSZD String,
    SEX String,
    AGE Decimal (18,0),
    RZQX Decimal (18,0),
    NATDATE Date,
    DOM String,
    TEL String,
    LITDEG String,
    OFFSIGN String,
    ACCDSIDE String,
    COUNTRY String,
    STUFFTYPE String,
    POSITION String,
    POSBRFORM String,
    APPOUNIT String,
    SJC Date,
    RJZB Decimal (18,0),
    TZE Decimal (18,0),
    CZFS String,
    SJZB Decimal (18,0),
    SJCZFS String,
    CZRQ Date,
    CZBL Decimal (18,0),
    CZF String,
    TZRLX String,
    SFBD Decimal (18,0),
    SFLDRKHYZM Decimal (18,0),
    ZXHHSWBZ String,
    RYLX String,
    FZJG String,
    SJQK String,
    BFB Decimal (18,0),
    UNISCID String,
    ZCH String,
    QYMC String,
    DJJG String,
    FDDBR String,
    CLRQ Date,
    QYLX String,
    QYSX String,
    JYZT String,
    ZCZB Decimal (18,0)
) ENGINE = MergeTree(CLRQ,(PERSON_ID),10);

4. 插入数据
   A: 通过insert into 语句插入：(字符串需要单引号才能插入)

 insert into f_sz_ryxx (PERSON_ID,NBXH,NAME,CERTYPE,BLICTYPE,CERNO,HJSZD,SEX,AGE,RZQX,NATDATE,DOM,TEL,LITDEG,OFFSIGN,ACCDSIDE,COUNTRY,STUFFTYPE,POSITION,POSBRFORM,APPOUNIT,SJC,RJZB,TZE,CZFS,SJZB,SJCZFS,CZRQ,CZBL,CZF,TZRLX,SFBD,SFLDRKHYZM,ZXHHSWBZ,RYLX,FZJG,SJQK,BFB,UNISCID,ZCH,QYMC,DJJG,FDDBR,CLRQ,QYLX,QYSX,JYZT,ZCZB) values('2140000000171339','2140000000014007','王民','10','','142701570501001','','1','61','','1/5/1957 00:00:00','太原市迎泽区东安路7－3－17','13835178783','','0','','156','02','','03','','19/2/2001 00:00:00','','','','','','','','','','','','','1','','','','','1400002002070','山西皮尔复临床医药开发有限公司','1400000000','王民','1/9/1992 00:00:00','1130','03','11','300');

B: 导入csv文件数据：

 cat F_SZ_RYXX.csv | clickhouse-client --query="INSERT INTO f_sz_ryxx FORMAT CSV"

C: mysql数据导入clickhouse

 #du出的表大小    5.5G    article_clientuser_sum.ibd
 #ClickHouse操作语句
 CREATE TABLE article_clientuser_sum
 ENGINE = MergeTree
 ORDER BY id AS
 SELECT *
 FROM mysql('host:port', 'db', 'article_clientuser_sum', 'user', 'password') 
   #耗时和平均速度
 0 rows in set. Elapsed: 137.251 sec. Processed 18.59 million rows, 7.34 		GB (135.43 thousand rows/s., 53.48 MB/s.)

5 clickhouse-mysql安装及数据实时插入

在CentOS 7上测试

1. 来自packagecloud.io的 Packagecloud repo 有关安装的更多详细信息，请访问https://github.com/Altinity/clickhouse-rpm-install

curl -s https://packagecloud.io/install/repositories/altinity/clickhouse/script.rpm.sh | sudo bash

安装EPEL（for python3）和MySQL（for libmysqlclient）repos

sudo yum install -y epel-release 
sudo yum install -y https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

如果您的回购中没有EPEL，请直接从EPEL网站安装

sudo yum install -y https://download.fedoraproject.org/pub/epel/7/x86_64/Packages/e/epel-release-7-11.noarch.rpm

从packagecloud.io安装数据阅读器

sudo yum install -y clickhouse-mysql

clickhouse包也将作为依赖项安装。
准备配置文件 - 将示例文件复制到生产环境中并进行编辑。

sudo cp /etc/clickhouse-mysql/clickhouse-mysql-example.conf /etc/clickhouse-mysql/clickhouse-mysql.conf 
sudo vim /etc/clickhouse-mysql/clickhouse-mysql.conf

2. 连接mysql-clickhouse数据插入

clickhouse-mysql \
--src-server-id=1 \
--src-resume \
--src-wait \
--nice-pause=1 \
--src-host=192.168.3.191 \
--src-user=root \
--src-password=abcd@1234 \
--src-tables=ZHIXIAOQIYE.zhixiao_data \
--dst-host=192.168.3.194 \
--dst-schema=default \  # 指定clickhouse数据库
--dst-table=zhixiao \   # 指定clickhouse数据表
--csvpool \
--csvpool-file-path-prefix=qwe_ \
--mempool-max-flush-interval=60 \
--mempool-max-events-num=10000 

6 clickhouse客户端连接

clickhouse-client -h 192.168.3.194 --port 9001