Clickhou安装及使⽤clickhou-mysql安装
1.安装clickhou环境
ClickHou对Debian/Ubuntu⽀持较好，但是⼯作当中服务器我们⼀般⽤CentOs。今天我们使⽤CentOs7来安装⼀下ClickHou。操作系统版本：CentOS Linux relea 7.5.1804 (Core)
检查⼀下是否⽀持SSE 4.2指令集:
grep -q s4_2 /proc/cpuinfo && echo "SSE 4.2 supported" || echo "SSE 4.2 not supported"
2.下载安装包
1. 下载地址：
repo.yandex.ru/clickhou/rpm/stable/x86_64/
下载⼀下四个包：
clickhou-client-18.arch.rpm
clickhou-rver-common-18.arch.rpm
clickhou-rver-18.arch.rpm
clickhou-client-18.arch.rpm
2. 开始安装：
rpm -ivh clickhou-rver-common-18.arch.rpm
rpm -ivh clickhou-rver-18.arch.rpm
薏米是什么米rpm -ivh clickhou-common-static-18.12.17-2.x86_64.rpm
rpm -ivh clickhou-client-18.arch.rpm
注意：在安装第⼆步会出现依赖错误，需下载依赖，yum install *ODBC*
再次安装clickhou-rver没问题了。
安装后配置路径：cd /etc/clickhou-rver/
3. 启动命令：
clickhou-rver --config-file=/etc/l
或：
systemctl stop clickhou-rver
systemctl start clickhou-rver
3.clickhou配置
1. 放开远程访问：
vi /etc/l
修改服务器的配置⽂件/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>
<!-- U cache of uncompresd blocks of data. Meaningfu
1. 启动客户端命令：
#clickhou-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. 通过inrt into 语句插⼊：(字符串需要单引号才能插⼊)
inrt 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 | clickhou-client --query="INSERT INTO f_sz_ryxx FORMAT CSV"
3. mysql数据导⼊clickhou
#du出的表⼤⼩ 5.5G article_clientur_sum.ibd
#ClickHou操作语句
CREATE TABLE article_clientur_sum
ENGINE = MergeTree
ORDER BY id AS
SELECT *
FROM mysql('host:port', 'db', 'article_clientur_sum', 'ur', 'password')
#耗时和平均速度
0 rows in t. Elapd: 137.251 c. Procesd 18.59 million rows, 7.34 GB (135.43 thousand rows/s., 53.48 MB/s.)
5.python连接clickhou数据库操作
1. 安装clickhou-dricer：
pip install clickhou-driverpip install clickhou-driver
2. 连接clickhou服务器：
client = client(host='192.168.3.194',databa='default',ur='default',password='')
3. 查询数据操作：
4. 插⼊数据操作：
注意：#a的数据类型如：[['a','b',1,3],['a','b',2,4]]
5. 案例操作（从oracle读取数据插⼊clickhou）:
# -*- coding: utf-8 -*-
import cx_Oracle
from clickhou_driver import Client
import re
import time,datetime
import types
class Clickhou():
# 连接oracle服务器
dsn = cx_Oracle.makedsn("192.168.3.195","1521","topicis")
conn = t("topicis","topicis",dsn)
cur = conn.cursor()
# 连接clickhou服务器
client = Client(host='192.168.3.194',databa='default',ur='default',password='')
# ute('lect count(*) from DJ_ZT_HIST'))
# 查询数据
def lect_data(lf):
start = time.time()
print(ute('lect * from F_SZ_RYXX'))
end = time.time()
print("查询时间：",end-start)
# clickhou创建数据表
def create_table(lf):
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 inrt(lf):
sql = ("lect * from F_SZ_RYXX")
sql1 = ("lect * from yuangong")
run = ute(sql)
start = time.time()
con1 = lf.cur.fetchone()
# print(con1)
# con = lf.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 el i[0]
i[1] = '' if i[1]==None el i[1]
i[2] = '' if i[2]==None el i[2]
i[3] = '' if i[3]==None el i[3]
# PERSON_ID = i[0]
# NBXH = i[1]
# NAME = i[2]
# CERTYPE = i[3]
i[4] = '' if i[4]==None el i[4]
i[5] = '' if i[5]==None el i[5]
# CERNO = i[5]
i[6] = '' if i[6]==None el i[6]# HJSZD = i[6]
i[7] = '' if i[7]==None el i[7]
i[8] = 0.0 if i[8]==None el i[8]
i[9] = 0.0 if i[9]==None el i[9]
# i[10] = w().date()if i[10]==None el i[10].date() i[10] = w().date()
i[11] = '' if i[11]==None el i[11]
i[12] = '' if i[12]==None el i[12]
i[13] = '' if i[13]==None el i[13]
i[14] = '' if i[14]==None el i[14]
# DOM = i[11]
# TEL = i[12]
# LITDEG = i[13]
# OFFSIGN = i[14]
i[15] = '' if i[15]==None el i[15]
i[16] = '' if i[16]==None el i[16]
i[17] = '' if i[17]==None el i[17]
i[18] = '' if i[18]==None el i[18]
i[19] = '' if i[19]==None el i[19]
i[20] = '' if i[20]==None el i[20]
# POSITION = i[18]
# POSBRFORM = i[19]
# APPOUNIT = i[20]
i[21] = w().date()
i[22] = 0.0 if i[22]==None el i[22]
i[23] = 0.0 if i[23]==None el i[23]
# CZFS = i[24]
i[24] = '' if i[24]==None el i[24]
i[25] = 0.0 if i[25]==None el i[25]
i[26] = '' if i[26]==None el i[26]
# SJCZFS = i[26]忽然之间简谱
i[27] = w().date()
i[28] = 0.0 if i[28]==None el i[28]
i[29] = '' if i[29]==None el i[29]
i[30] = '' if i[30]==None el i[30]
# CZF = i[29]
# TZRLX = i[30]
i[31] = 0.0 if i[31]==None el i[31]
i[32] = 0.0 if i[32]==None el i[32]
i[33] = '' if i[33]==None el i[33]
i[34] = '' if i[34]==None el i[34]
i[35] = '' if i[35]==None el i[35]
i[36] = '' if i[36]==None el i[36]
# ZXHHSWBZ = i[33]
# RYLX = i[34]
# FZJG = i[35]
# SJQK = i[36]
i[37] = 0.0 if i[37]==None el i[37]
i[38] = '' if i[38]==None el i[38]
i[39] = '' if i[39]==None el i[39]
i[40] = '' if i[40]==None el i[40]
i[41] = '' if i[41]==None el i[41]
i[42] = '' if i[42]==None el 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 el i[44]
i[45] = '' if i[45]==None el i[45]
i[46] = '' if i[46]==None el i[46]
# QYLX = i[44]
# QYSX = i[45]
# JYZT = i[46]
i[47] = 0.0 if i[47]==None el i[47]
# name = i[1]
# i[2] = 0.0 if i[2]==None el i[2]郭海斌
# # 时间为空时数据调整
# i[3] = w().date()if i[3]==None el 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('数据插⼊clickhou时间为：',end1-start1)
lf.cur.clo()
if __name__ == '__main__':
c = Clickhou()
# c.create_table()
流行颜色# c.inrt()
c.lect_data()
6. clickhou-mysql安装及数据实时插⼊
在CentOS 7上测试
curl -s packagecloud.io/install/repositories/altinity/clickhou/script.rpm.sh | sudo bash
安装EPEL（for python3）和MySQL（for libmysqlclient）repos
sudo yum install -y epel-relea
吴浩然
sudo yum install -/arch.rpm
如果您的回购中没有EPEL，请直接从EPEL⽹站安装
sudo yum install -y download.fedoraproject/pub/epel/7/x86_64/Packages/arch.rpm 从packagecloud.io安装数据阅读器
sudo yum install -y clickhou-mysql
clickhou包也将作为依赖项安装。
云南丽江旅游必去的景点
准备配置⽂件 - 将⽰例⽂件复制到⽣产环境中并进⾏编辑。
sudo cp /etc/f /etc/f
sudo vim /etc/f
2.连接mysql-clickhou数据插⼊
