CentOs安装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.下载安装包
创建一个路径用来安装ClickHouse

mkdir -p /opt/zhangwq/clickhouse
下载地址:http://repo.red-soft.biz/repos/clickhouse/stable/el7/

执行命令如下命令进行下载:

cd /opt/zhangwq/clickhouse
mwget http://repo.red-soft.biz/repos/clickhouse/stable/el7/clickhouse-client-1.1.54236-4.el7.x86_64.rpm
mwget http://repo.red-soft.biz/repos/clickhouse/stable/el7/clickhouse-compressor-1.1.54236-4.el7.x86_64.rpm
mwget http://repo.red-soft.biz/repos/clickhouse/stable/el7/clickhouse-debuginfo-1.1.54236-4.el7.x86_64.rpm
mwget http://repo.red-soft.biz/repos/clickhouse/stable/el7/clickhouse-server-1.1.54236-4.el7.x86_64.rpm
mwget http://repo.red-soft.biz/repos/clickhouse/stable/el7/clickhouse-server-common-1.1.54236-4.el7.x86_64.rpm
3.开始安装
执行如下命令进行安装:

rpm -ivh clickhouse-server-common-1.1.54236-4.el7.x86_64.rpm
rpm -ivh clickhouse-server-1.1.54236-4.el7.x86_64.rpm
rpm -ivh libtool-ltdl-2.4.2-21.el7_2.x86_64.rpm
rpm -ivh clickhouse-debuginfo-1.1.54236-4.el7.x86_64.rpm
rpm -ivh clickhouse-compressor-1.1.54236-4.el7.x86_64.rpm
执行rpm -ivh clickhouse-server-1.1.54236-4.el7.x86_64.rpm时出现报错,如下图:

执行 yum install unixODBC,第一次执行没有成功。本着宁可错杀一千不可放过一个的原则,索性执行yum install *ODBC*

再次安装clickhouse-server没问题了。

至此安装完成,配置文件位于如下目录,可修改相应配置

配置
默认配置文件存放在:/etc/clickhouse-server

1. 远程访问
sudo vi /etc/clickhouse-server/config.xml
去掉::注释

2. 内存限制设置
sudo vi /etc/clickhouse-server/users.xml
修改500000000000

———————
原文:https://blog.csdn.net/baymax_007/article/details/81536520

cd /etc/clickhouse-server/
启动命令:
clickhouse-server –config-file=/etc/clickhouse-server/config.xml
4.导入数据
按照官方文档,下载一份测试数据,搞起。

执行命令:


for s in `seq 1987 2017`
do
for m in `seq 1 12`
do
mwget http://transtats.bts.gov/PREZIP/On_Time_On_Time_Performance_${s}_${m}.zip
done
done

注:mwget速度优于wget,使用mwget下载数据,mwget安装可自行百度

执行:clickhouse-client进入clickhouse客户端

ulimit: open files: cannot modify limit 解决
cat >>/etc/security/limits.conf<<eof
* soft nproc 65535
* hard nproc 65535
* soft nofile 65535
* hard nofile 65535
eof

soft nproc: 可打开的文件描述符的最大数(软限制)

hard nproc: 可打开的文件描述符的最大数(硬限制)

soft nofile:单个用户可用的最大进程数量(软限制)

hard nofile:单个用户可用的最大进程数量(硬限制)

创建表语句为:


CREATE TABLE `ontime` (
`Year` UInt16,
`Quarter` UInt8,
`Month` UInt8,
`DayofMonth` UInt8,
`DayOfWeek` UInt8,
`FlightDate` Date,
`UniqueCarrier` FixedString(7),
`AirlineID` Int32,
`Carrier` FixedString(2),
`TailNum` String,
`FlightNum` String,
`OriginAirportID` Int32,
`OriginAirportSeqID` Int32,
`OriginCityMarketID` Int32,
`Origin` FixedString(5),
`OriginCityName` String,
`OriginState` FixedString(2),
`OriginStateFips` String,
`OriginStateName` String,
`OriginWac` Int32,
`DestAirportID` Int32,
`DestAirportSeqID` Int32,
`DestCityMarketID` Int32,
`Dest` FixedString(5),
`DestCityName` String,
`DestState` FixedString(2),
`DestStateFips` String,
`DestStateName` String,
`DestWac` Int32,
`CRSDepTime` Int32,
`DepTime` Int32,
`DepDelay` Int32,
`DepDelayMinutes` Int32,
`DepDel15` Int32,
`DepartureDelayGroups` String,
`DepTimeBlk` String,
`TaxiOut` Int32,
`WheelsOff` Int32,
`WheelsOn` Int32,
`TaxiIn` Int32,
`CRSArrTime` Int32,
`ArrTime` Int32,
`ArrDelay` Int32,
`ArrDelayMinutes` Int32,
`ArrDel15` Int32,
`ArrivalDelayGroups` Int32,
`ArrTimeBlk` String,
`Cancelled` UInt8,
`CancellationCode` FixedString(1),
`Diverted` UInt8,
`CRSElapsedTime` Int32,
`ActualElapsedTime` Int32,
`AirTime` Int32,
`Flights` Int32,
`Distance` Int32,
`DistanceGroup` UInt8,
`CarrierDelay` Int32,
`WeatherDelay` Int32,
`NASDelay` Int32,
`SecurityDelay` Int32,
`LateAircraftDelay` Int32,
`FirstDepTime` String,
`TotalAddGTime` String,
`LongestAddGTime` String,
`DivAirportLandings` String,
`DivReachedDest` String,
`DivActualElapsedTime` String,
`DivArrDelay` String,
`DivDistance` String,
`Div1Airport` String,
`Div1AirportID` Int32,
`Div1AirportSeqID` Int32,
`Div1WheelsOn` String,
`Div1TotalGTime` String,
`Div1LongestGTime` String,
`Div1WheelsOff` String,
`Div1TailNum` String,
`Div2Airport` String,
`Div2AirportID` Int32,
`Div2AirportSeqID` Int32,
`Div2WheelsOn` String,
`Div2TotalGTime` String,
`Div2LongestGTime` String,
`Div2WheelsOff` String,
`Div2TailNum` String,
`Div3Airport` String,
`Div3AirportID` Int32,
`Div3AirportSeqID` Int32,
`Div3WheelsOn` String,
`Div3TotalGTime` String,
`Div3LongestGTime` String,
`Div3WheelsOff` String,
`Div3TailNum` String,
`Div4Airport` String,
`Div4AirportID` Int32,
`Div4AirportSeqID` Int32,
`Div4WheelsOn` String,
`Div4TotalGTime` String,
`Div4LongestGTime` String,
`Div4WheelsOff` String,
`Div4TailNum` String,
`Div5Airport` String,
`Div5AirportID` Int32,
`Div5AirportSeqID` Int32,
`Div5WheelsOn` String,
`Div5TotalGTime` String,
`Div5LongestGTime` String,
`Div5WheelsOff` String,
`Div5TailNum` String
) ENGINE = MergeTree(FlightDate, (Year, FlightDate), 8192)

SQL语句需要压缩后再执行。

创建表成功。

向表中导入数据:

for i in *.zip; do echo $i; unzip -cq $i ‘*.csv’ | sed ‘s/\.00//g’ | clickhouse-client –query=”INSERT INTO ontime FORMAT CSVWithNames”; done
数据导入成功,然后查询一下数据量。

一亿七千万数据,第一次执行2.479秒,第二次执行0.076秒。

这速度简直逆天……

先写到这里,后续继续研究再做补充。
———————
作者:狮子头儿
来源:CSDN
原文:https://blog.csdn.net/zwq_zwq_zwq/article/details/80791226
版权声明:本文为博主原创文章,转载请附上博文链接!

Leave a Comment

电子邮件地址不会被公开。 必填项已用*标注