CentOs安装ClickHouse及初步使用

1.安装环境

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 clickhouse-client-1.1.54236-4.el7.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没问题了。

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

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客户端

创建表语句为:

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秒。

这速度简直逆天……

先写到这里,后续继续研究再做补充。

  • 1
    点赞
  • 25
    收藏
    觉得还不错? 一键收藏
  • 6
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 6
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值