Clickhouse安装及使⽤clickhouse-mysql安装
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. 下载地址：
repo.yandex.ru/clickhouse/rpm/stable/x86_64/
下载⼀下四个包：
clickhouse-client-18.arch.rpm
clickhouse-server-common-18.arch.rpm
clickhouse-server-18.arch.rpm
clickhouse-client-18.arch.rpm
2. 开始安装：
rpm -ivh clickhouse-server-common-18.arch.rpm
rpm -ivh clickhouse-server-18.arch.rpm
rpm -ivh clickhouse-common-static-18.12.17-2.x86_64.rpm
rpm -ivh clickhouse-client-18.arch.rpm
注意：在安装第⼆步会出现依赖错误，需下载依赖，yum install *ODBC*
再次安装clickhouse-server没问题了。
安装后配置路径：cd /etc/clickhouse-server/
3. 启动命令：
clickhouse-server --config-file=/etc/l
或：
systemctl stop clickhouse-server
systemctl start clickhouse-server
3.clickhouse配置
1. 放开远程访问：
vi /etc/lmysql下载完如何使用
修改服务器的配置⽂件/etc/l，第65⾏，放开注释即可，修改之后的内容如下：
<listen_host>::</listen_host>
<listen_host>127.0.0.1</listen_host>
2. 内存权限设置：
vi /etc/l
<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
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,BLI
CTYPE String,CERNO String,HJSZD String,SEX S tring,AGE Decimal (18,0),RZQX Decimal (18,0),NATDATE Date,DOM String,TEL String,LITDEG String,OFFSIGN String,ACCDSIDE String,COUNTR Y String,STUFFTYPE String,POSITION String,POSBRFORM String,APPOUNIT String,SJC Date,RJZB Decimal (18,0),TZE Decimal (18,0),CZFS Stri ng,SJZB Decimal (18,0),SJCZFS String,CZRQ Date,CZBL Decimal (18,0),CZF String,TZRLX String,SFBD Decimal (18,0),SFLDRKHYZM Decimal (1 8,0),ZXHHSWBZ String,RYLX String,FZJG String,SJQK String,BFB Decimal (18,0),UNISCID String,ZCH String,QYMC String,DJJG String,FDDBR Stri ng,CLRQ Date,QYLX String,QYSX String,JYZT String,ZCZB Decimal (18,0)) ENGINE = MergeTree(CLRQ,(PERSON_ID),10);
4. 插⼊数据：
1. 通过insert into 语句插⼊：(字符串需要单引号才能插⼊)
insert into f_sz_ryxx (PERSON_ID,NBXH,NAME,CERTYPE,BLICTYPE,CERNO,HJSZD,SEX,AGE,RZQX,NATDATE,DOM,TEL,LITDEG,OFFSI GN,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('2140 000000171339','2140000000014007','王民','10','','142701*********','','1','61','','1/5/1957 00:00:00','太原市迎泽区东安路7－3－17','13835178 783','','0','','156','02','','03','','19/2/2001 00:00:00','','','','','','','','','','','','','1','','','','','1400002002070','⼭西⽪尔复临床医药开发有限公司','140000000 0','王民','1/9/1992 00:00:00','1130','03','11','300');
2. 导⼊csv⽂件数据：
cat F_SZ_RYXX.csv | clickhouse-client --query="INSERT INTO f_sz_ryxx FORMAT CSV"
3. 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.python连接clickhouse数据库操作
1. 安装clickhouse-dricer：
pip install clickhouse-driverpip install clickhouse-driver
2. 连接clickhouse服务器：
client = client(host='192.168.3.194',database='default',user='default',password='')
3. 查询数据操作：
4. 插⼊数据操作：
注意：#a的数据类型如：[['a','b',1,3],['a','b',2,4]]
5. 案例操作（从oracle读取数据插⼊clickhouse）:
# -*- coding: utf-8 -*-
import cx_Oracle
from clickhouse_driver import Client
import re
import time,datetime
import types
class Clickhouse():
# 连接oracle服务器
dsn = cx_Oracle.makedsn("192.168.3.195","1521","topicis")
conn = t("topicis","topicis",dsn)
cur = conn.cursor()
# 连接clickhouse服务器
client = Client(host='192.168.3.194',database='default',user='default',password='')
# ute('select count(*) from DJ_ZT_HIST'))
# 查询数据
def select_data(self):
start = time.time()
print(ute('select * from F_SZ_RYXX'))
end = time.time()
print("查询时间：",end-start)
# clickhouse创建数据表
def create_table(self):
ute('DROP TABLE IF EXISTS test2')
creattable = """CREATE TABLE test2 (PERSON_ID String,NBXH String,NAME String,CERTYPE String,BLICTYPE String,CERNO String,HJSZD String,SEX String,AGE Float32,RZQX Float32,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 Float32,TZE Float32,CZFS String,SJZB Float32 ,SJCZFS String,CZRQ Date,CZBL Float32,CZF String,TZRLX String,SFBD Float32,SFLDRKHYZM Float32,ZXHHSWBZ String,RYLX String,FZJG St ring,SJQK String,BFB Float32,UNISCID String,ZCH String,QYMC String,DJJG String,FDDBR String,CLRQ Date,QYLX String,QYSX String,JYZT Strin g,ZCZB Float32) ENGINE = MergeTree(CLRQ,(PERSON_ID),10);"""
creattable1 = """CREATE TABLE test1 (ID Float32,NAME String,AGE Float32, date_time Date ) ENGINE = MergeTree(date_time,(ID),10);"""
ute(creattable)
# 插⼊数据
def insert(self):
sql = ("select * from F_SZ_RYXX")
sql1 = ("select * from yuangong")
run = ute(sql)
start = time.time()
con1 = self.cur.fetchone()
# print(con1)
# con = self.cur.fetchall()
end = time.time()
print("mysql获取时间：",end-start)
start1 = time.time()
# for i in con:
i = list(con1)
i[0] = '' if i[0]==None else i[0]
i[1] = '' if i[1]==None else i[1]
i[2] = '' if i[2]==None else i[2]
i[3] = '' if i[3]==None else i[3]
# PERSON_ID = i[0]
# NBXH = i[1]
# NAME = i[2]
# CERTYPE = i[3]
i[4] = '' if i[4]==None else i[4]
i[5] = '' if i[5]==None else i[5]
# CERNO = i[5]
i[6] = '' if i[6]==None else i[6]# HJSZD = i[6]
i[7] = '' if i[7]==None else i[7]
i[8] = 0.0 if i[8]==None else i[8]
i[9] = 0.0 if i[9]==None else i[9]
# i[10] = w().date()if i[10]==None else i[10].date() i[10] = w().date()
i[11] = '' if i[11]==None else i[11]
i[12] = '' if i[12]==None else i[12]
i[13] = '' if i[13]==None else i[13]
i[14] = '' if i[14]==None else i[14]
# DOM = i[11]
# TEL = i[12]
# LITDEG = i[13]
# OFFSIGN = i[14]
i[15] = '' if i[15]==None else i[15]
i[16] = '' if i[16]==None else i[16]
i[17] = '' if i[17]==None else i[17]
i[18] = '' if i[18]==None else i[18]
i[19] = '' if i[19]==None else i[19]
i[20] = '' if i[20]==None else i[20]
# POSITION = i[18]
# POSBRFORM = i[19]
# APPOUNIT = i[20]
i[21] = w().date()
i[22] = 0.0 if i[22]==None else i[22]
i[23] = 0.0 if i[23]==None else i[23]
# CZFS = i[24]
i[24] = '' if i[24]==None else i[24]
i[25] = 0.0 if i[25]==None else i[25]
i[26] = '' if i[26]==None else i[26]
# SJCZFS = i[26]
i[27] = w().date()
i[28] = 0.0 if i[28]==None else i[28]
i[29] = '' if i[29]==None else i[29]
i[30] = '' if i[30]==None else i[30]
# CZF = i[29]
# TZRLX = i[30]
i[31] = 0.0 if i[31]==None else i[31]
i[32] = 0.0 if i[32]==None else i[32]
i[33] = '' if i[33]==None else i[33]
i[34] = '' if i[34]==None else i[34]
i[35] = '' if i[35]==None else i[35]
i[36] = '' if i[36]==None else i[36]
# ZXHHSWBZ = i[33]
# RYLX = i[34]
# FZJG = i[35]
# SJQK = i[36]
i[37] = 0.0 if i[37]==None else i[37]
i[38] = '' if i[38]==None else i[38]
i[39] = '' if i[39]==None else i[39]
i[40] = '' if i[40]==None else i[40]
i[41] = '' if i[41]==None else i[41]
i[42] = '' if i[42]==None else i[42]
# UNISCID = i[38]
# ZCH = i[39]
# QYMC = i[40]
# DJJG = i[41]
# FDDBR = i[42]
i[43] = w().date()
i[44] = '' if i[44]==None else i[44]
i[45] = '' if i[45]==None else i[45]
i[46] = '' if i[46]==None else i[46]
# QYLX = i[44]
# QYSX = i[45]
# JYZT = i[46]
i[47] = 0.0 if i[47]==None else i[47]
# name = i[1]
# i[2] = 0.0 if i[2]==None else i[2]
# # 时间为空时数据调整
# i[3] = w().date()if i[3]==None else i[3].date()
a = [i]
print(a)
try:
ute("INSERT INTO test2 VALUES",a,types_check=True)
except Exception as e:
print(e)
end1 = time.time()
print('数据插⼊clickhouse时间为：',end1-start1)
self.cur.close()
if __name__ == '__main__':
c = Clickhouse()
# c.create_table()
# c.insert()
c.select_data()
6. clickhouse-mysql安装及数据实时插⼊
在CentOS 7上测试
curl -s 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 sql/arch.rpm
如果您的回购中没有EPEL，请直接从EPEL⽹站安装
sudo yum install -y /pub/epel/7/x86_64/Packages/arch.rpm 从packagecloud.io安装数据阅读器
sudo yum install -y clickhouse-mysql
clickhouse包也将作为依赖项安装。
准备配置⽂件 - 将⽰例⽂件复制到⽣产环境中并进⾏编辑。
sudo cp /etc/f /etc/f
sudo vim /etc/f
2.连接mysql-clickhouse数据插⼊
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论