【ClickHouse实战】1. Clickhouse部署（单节点及集群）及实战案例验证

地亩显

已于 2022-03-29 10:22:10 修改

阅读量3.9k

点赞数

CC 4.0 BY-SA版权

分类专栏：大数据 clickhouse 文章标签：大数据

于 2022-03-27 11:36:12 首次发布

本文链接：https://blog.youkuaiyun.com/xiantim/article/details/123751405

ClickHouse实战系列目录

Clickhouse部署（单节点及集群）及实战案例验证

logo

文章目录

ClickHouse实战系列目录
前言
一、安装指引
二、使用验证
三、构造集群
总结

前言

由于工作需要，需要搭建公司级别的OLAP服务。经过前期的选型，初步确定使用Clickhouse。本文会根据官方文档和网上的资料，学习如何搭建Clickhouse单节点和集群。并且通过实例验证Clickhouse相关的功能

配置清单

名称	版本
Ubuntu	20.04
ClickHouse	22.3.x
docker	20.10.7
硬盘	SSD
CPU	Intel 11th Gen Core i9-11900KF
RAM	64G

一、安装指引

1. 验证硬件配置

通过官网的操作指引进行操作。因为需要下载数据的原因，需要较长时间。
备注1：hardware.sh中间有一步涉及某个需要【科学上网】才能下载的文件，请提前做好准备
备注2：自动测试总是有不可控步骤（这里是坑，你懂的...），建议使用人工运行
自动测试失败
备注3：人工运行的操作步骤中，由于wget经常会出现“段终断”错误且断点续传无效，建议使用迅雷下载 hits_100m_obfuscated_v1.tar.xz 再拷贝到测试文件夹中

关于数据表结构：参考如下schema，schema的设置会跟查询的延时相关，可作为参考

CREATE TABLE datasets.hits_v1
(
  WatchID UInt64,
  JavaEnable UInt8,
  Title String,
  GoodEvent Int16,
  EventTime DateTime,
  EventDate Date,
  CounterID UInt32,
  ClientIP UInt32,
  ClientIP6 FixedString(16),
  RegionID UInt32,
  UserID UInt64,
  CounterClass Int8,
  OS UInt8,
  UserAgent UInt8,
  URL String,
  Referer String,
  URLDomain String,
  RefererDomain String,
  Refresh UInt8,
  IsRobot UInt8,
  RefererCategories Array(UInt16),
  URLCategories Array(UInt16),
  URLRegions Array(UInt32),
  RefererRegions Array(UInt32),
  ResolutionWidth UInt16,
  ResolutionHeight UInt16,
  ResolutionDepth UInt8,
  FlashMajor UInt8,
  FlashMinor UInt8,
  FlashMinor2 String,
  NetMajor UInt8,
  NetMinor UInt8,
  UserAgentMajor UInt16,
  UserAgentMinor FixedString(2),
  CookieEnable UInt8,
  JavascriptEnable UInt8,
  IsMobile UInt8,
  MobilePhone UInt8,
  MobilePhoneModel String,
  Params String,
  IPNetworkID UInt32,
  TraficSourceID Int8,
  SearchEngineID UInt16,
  SearchPhrase String,
  AdvEngineID UInt8,
  IsArtifical UInt8,
  WindowClientWidth UInt16,
  WindowClientHeight UInt16,
  ClientTimeZone Int16,
  ClientEventTime DateTime,
  SilverlightVersion1 UInt8,
  SilverlightVersion2 UInt8,
  SilverlightVersion3 UInt32,
  SilverlightVersion4 UInt16,
  PageCharset String,
  CodeVersion UInt32,
  IsLink UInt8,
  IsDownload UInt8,
  IsNotBounce UInt8,
  FUniqID UInt64,
  HID UInt32,
  IsOldCounter UInt8,
  IsEvent UInt8,
  IsParameter UInt8,
  DontCountHits UInt8,
  WithHash UInt8,
  HitColor FixedString(1),
  UTCEventTime DateTime,
  Age UInt8,
  Sex UInt8,
  Income UInt8,
  Interests UInt16,
  Robotness UInt8,
  GeneralInterests Array(UInt16),
  RemoteIP UInt32,
  RemoteIP6 FixedString(16),
  WindowName Int32,
  OpenerName Int32,
  HistoryLength Int16,
  BrowserLanguage FixedString(2),
  BrowserCountry FixedString(2),
  SocialNetwork String,
  SocialAction String,
  HTTPError UInt16,
  SendTiming Int32,
  DNSTiming Int32,
  ConnectTiming Int32,
  ResponseStartTiming Int32,
  ResponseEndTiming Int32,
  FetchTiming Int32,
  RedirectTiming Int32,
  DOMInteractiveTiming Int32,
  DOMContentLoadedTiming Int32,
  DOMCompleteTiming Int32,
  LoadEventStartTiming Int32,
  LoadEventEndTiming Int32,
  NSToDOMContentLoadedTiming Int32,
  FirstPaintTiming Int32,
  RedirectCount Int8,
  SocialSourceNetworkID UInt8,
  SocialSourcePage String,
  ParamPrice Int64,
  ParamOrderID String,
  ParamCurrency FixedString(3),
  ParamCurrencyID UInt16,
  GoalsReached Array(UInt32),
  OpenstatServiceName String,
  OpenstatCampaignID String,
  OpenstatAdID String,
  OpenstatSourceID String,
  UTMSource String,
  UTMMedium String,
  UTMCampaign String,
  UTMContent String,
  UTMTerm String,
  FromTag String,
  HasGCLID UInt8,
  RefererHash UInt64,
  URLHash UInt64,
  CLID UInt32,
  YCLID UInt64,
  ShareService String,
  ShareURL String,
  ShareTitle String,
  ParsedParams Nested(
    Key1 String,
    Key2 String,
    Key3 String,
    Key4 String,
    Key5 String,
    ValueDouble Float64
  ), 
  IslandID FixedString(16),  
  RequestNum UInt32,  
  RequestTry UInt8
) ENGINE = MergeTree() 
PARTITION BY toYYYYMM(EventDate) 
ORDER BY(CounterID, EventDate, intHash32(UserID)) 
SAMPLE BY intHash32(UserID) 
SETTINGS index_granularity = 8192

关于验证结果：

在我的服务器配置下，得到以下测试数据

详细测试报告请点击测试结果文件

序	查询说明	平均耗时	首次查询	第二次	第三次
1	count	0.001	0.001	0.001	0.001
2	count+where	0.008	0.010	0.007	0.006
3	sum+avg	0.024	0.030	0.022	0.020
4	sum	0.056	0.100	0.033	0.035
5	uniq(int)	0.089	0.114	0.079	0.074
6	uniq(string)	0.225	0.241	0.209	0.225
7	min+max	0.001	0.002	0.001	0.001
8	where+groupby+orderby	0.007	0.008	0.007	0.007
9	uniq(int)+where+groupby+orderby+limit	0.532	0.565	0.519	0.511
10	sum+count+avg+uniq(int)+where+groupby+orderby+limit	0.606	0.629	0.590	0.599
11	uniq(int)+where+groupby+orderby+limit	0.141	0.159	0.130	0.134
12	uniq(int)+where+双groupby+orderby+limit	0.163	0.190	0.149	0.150
13	count+where+groupby+orderby+limit	0.939	0.976	0.927	0.915
14	uniq(int)+where+groupby+orderby+limit	1.227	1.273	1.208	1.199
15	ucount+where+双groupby+orderby+limit	1.057	1.086	1.044	1.041
16	count+where+groupby+orderby(count)+limit	1.208	1.229	1.196	1.200
17	count+where+双groupby+orderby(count)+limit	3.303	3.206	3.491	3.213
18	count+where+双groupby+limit	1.808	1.841	1.774	1.809
19	count+toMinute+where+双groupby+orderby+limit	5.872	5.919	5.897	5.799
20	where精确匹配	0.060	0.104	0.039	0.037
21	count+where模糊匹配	0.836	1.176	0.639	0.694
22	any+count+where模糊匹配+groupby+orderby+limit	1.015	1.407	0.814	0.825
23	any+count+uniq+where模糊匹配+groupby+orderby+l