网站报错 自动404,忘记wordpress密码,软件开发工资一般多少大专,网站怎样做公众号前面的文章ClickHouse之初步认识已经简单的介绍了ClickHouse#xff0c;接下来进行简单的性能测试。测试数据来源于美国民用航班的数据#xff0c;从1987年到2017年#xff0c;有1.7亿条。 环境#xff1a; centos 6.3#xff0c;32G内存#xff0c;24核 下载脚本#x…前面的文章ClickHouse之初步认识已经简单的介绍了ClickHouse接下来进行简单的性能测试。测试数据来源于美国民用航班的数据从1987年到2017年有1.7亿条。 环境 centos 6.332G内存24核 下载脚本 #!/bin/bashfor s in seq 1987 2017
do
for m in seq 1 12
do
mwget -n 128 http://transtats.bts.gov/PREZIP/On_Time_On_Time_Performance_${s}_${m}.zip
done
done 这里采用的是mwget128个线程mwget是wget的多线程版本。wget太慢了。关于mwget的安装请参考https://my.oschina.net/766/blog/156807下载以后的数据是zip压缩包如下 下载完数据以后建表 客户端登录 clickhouse-client -m如果不加-m启用多行那么将会报错 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) View Code 导入数据 for i in *.zip; do echo $i; unzip -cq $i *.csv | sed s/\.00//g | clickhouse-client --queryINSERT INTO ontime FORMAT CSVWithNames; done 开始查询测试 可以看见1.7亿数据count用了0.034秒当然列存储数据库count都不快还搞毛。 继续测试其他的语句 从2000年到2016年每天的航班统计 SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Year 2000 AND Year 2016 GROUP BY DayOfWeek ORDER BY c DESC; 2000 - 2008年度机场延误数 SELECT Origin, count(*) AS c FROM ontime WHERE DepDelay10 AND Year 2000 AND Year 2008 GROUP BY Origin ORDER BY c DESC LIMIT 10 这些查询都有一个范围限制那么全部查完呢 比如 SELECT OriginCityName, DestCityName, count() AS c FROM ontime GROUP BY OriginCityName, DestCityName ORDER BY c DESC LIMIT 10; 可以看见依然快的不像话哈哈。心动了没心动了就动手安装导入数据测试一下吧。 参考资料 https://raw.githubusercontent.com/yandex/ClickHouse/master/doc/example_datasets/1_ontime.txt 转载于:https://www.cnblogs.com/gomysql/p/6655553.html
