体验方式

1. Docker方式

  1. docker run -d --name some-clickhouse-server --ulimit nofile=262144:262144 clickhouse/clickhouse-server
  1. docker exec -it some-clickhouse-server clickhouse-client

2. ClickHouse Playground

连接方式

  1. curl "https://play.clickhouse.com/?user=explorer" --data-binary "SELECT 'Play ClickHouse'"
  1. clickhouse client --secure --host play.clickhouse.com --user explorer

基本语法

  1. CREATE DATABASE IF NOT EXISTS tutorial
  1. CREATE TABLE tutorial.visits_v1
  2. (
  3. `CounterID` UInt32,
  4. `StartDate` Date,
  5. `Sign` Int8,
  6. `IsNew` UInt8,
  7. `VisitID` UInt64,
  8. `UserID` UInt64,
  9. `StartTime` DateTime,
  10. `Duration` UInt32,
  11. `UTCStartTime` DateTime,
  12. `PageViews` Int32,
  13. `Hits` Int32,
  14. `IsBounce` UInt8,
  15. `Referer` String,
  16. `StartURL` String,
  17. `RefererDomain` String,
  18. `StartURLDomain` String,
  19. `EndURL` String,
  20. `LinkURL` String,
  21. `IsDownload` UInt8,
  22. `TraficSourceID` Int8,
  23. `SearchEngineID` UInt16,
  24. `SearchPhrase` String,
  25. `AdvEngineID` UInt8,
  26. `PlaceID` Int32,
  27. `RefererCategories` Array(UInt16),
  28. `URLCategories` Array(UInt16),
  29. `URLRegions` Array(UInt32),
  30. `RefererRegions` Array(UInt32),
  31. `IsYandex` UInt8,
  32. `GoalReachesDepth` Int32,
  33. `GoalReachesURL` Int32,
  34. `GoalReachesAny` Int32,
  35. `SocialSourceNetworkID` UInt8,
  36. `SocialSourcePage` String,
  37. `MobilePhoneModel` String,
  38. `ClientEventTime` DateTime,
  39. `RegionID` UInt32,
  40. `ClientIP` UInt32,
  41. `ClientIP6` FixedString(16),
  42. `RemoteIP` UInt32,
  43. `RemoteIP6` FixedString(16),
  44. `IPNetworkID` UInt32,
  45. `SilverlightVersion3` UInt32,
  46. `CodeVersion` UInt32,
  47. `ResolutionWidth` UInt16,
  48. `ResolutionHeight` UInt16,
  49. `UserAgentMajor` UInt16,
  50. `UserAgentMinor` UInt16,
  51. `WindowClientWidth` UInt16,
  52. `WindowClientHeight` UInt16,
  53. `SilverlightVersion2` UInt8,
  54. `SilverlightVersion4` UInt16,
  55. `FlashVersion3` UInt16,
  56. `FlashVersion4` UInt16,
  57. `ClientTimeZone` Int16,
  58. `OS` UInt8,
  59. `UserAgent` UInt8,
  60. `ResolutionDepth` UInt8,
  61. `FlashMajor` UInt8,
  62. `FlashMinor` UInt8,
  63. `NetMajor` UInt8,
  64. `NetMinor` UInt8,
  65. `MobilePhone` UInt8,
  66. `SilverlightVersion1` UInt8,
  67. `Age` UInt8,
  68. `Sex` UInt8,
  69. `Income` UInt8,
  70. `JavaEnable` UInt8,
  71. `CookieEnable` UInt8,
  72. `JavascriptEnable` UInt8,
  73. `IsMobile` UInt8,
  74. `BrowserLanguage` UInt16,
  75. `BrowserCountry` UInt16,
  76. `Interests` UInt16,
  77. `Robotness` UInt8,
  78. `GeneralInterests` Array(UInt16),
  79. `Params` Array(String),
  80. `Goals` Nested(
  81. ID UInt32,
  82. Serial UInt32,
  83. EventTime DateTime,
  84. Price Int64,
  85. OrderID String,
  86. CurrencyID UInt32),
  87. `WatchIDs` Array(UInt64),
  88. `ParamSumPrice` Int64,
  89. `ParamCurrency` FixedString(3),
  90. `ParamCurrencyID` UInt16,
  91. `ClickLogID` UInt64,
  92. `ClickEventID` Int32,
  93. `ClickGoodEvent` Int32,
  94. `ClickEventTime` DateTime,
  95. `ClickPriorityID` Int32,
  96. `ClickPhraseID` Int32,
  97. `ClickPageID` Int32,
  98. `ClickPlaceID` Int32,
  99. `ClickTypeID` Int32,
  100. `ClickResourceID` Int32,
  101. `ClickCost` UInt32,
  102. `ClickClientIP` UInt32,
  103. `ClickDomainID` UInt32,
  104. `ClickURL` String,
  105. `ClickAttempt` UInt8,
  106. `ClickOrderID` UInt32,
  107. `ClickBannerID` UInt32,
  108. `ClickMarketCategoryID` UInt32,
  109. `ClickMarketPP` UInt32,
  110. `ClickMarketCategoryName` String,
  111. `ClickMarketPPName` String,
  112. `ClickAWAPSCampaignName` String,
  113. `ClickPageName` String,
  114. `ClickTargetType` UInt16,
  115. `ClickTargetPhraseID` UInt64,
  116. `ClickContextType` UInt8,
  117. `ClickSelectType` Int8,
  118. `ClickOptions` String,
  119. `ClickGroupBannerID` Int32,
  120. `OpenstatServiceName` String,
  121. `OpenstatCampaignID` String,
  122. `OpenstatAdID` String,
  123. `OpenstatSourceID` String,
  124. `UTMSource` String,
  125. `UTMMedium` String,
  126. `UTMCampaign` String,
  127. `UTMContent` String,
  128. `UTMTerm` String,
  129. `FromTag` String,
  130. `HasGCLID` UInt8,
  131. `FirstVisit` DateTime,
  132. `PredLastVisit` Date,
  133. `LastVisit` Date,
  134. `TotalVisits` UInt32,
  135. `TraficSource` Nested(
  136. ID Int8,
  137. SearchEngineID UInt16,
  138. AdvEngineID UInt8,
  139. PlaceID UInt16,
  140. SocialSourceNetworkID UInt8,
  141. Domain String,
  142. SearchPhrase String,
  143. SocialSourcePage String),
  144. `Attendance` FixedString(16),
  145. `CLID` UInt32,
  146. `YCLID` UInt64,
  147. `NormalizedRefererHash` UInt64,
  148. `SearchPhraseHash` UInt64,
  149. `RefererDomainHash` UInt64,
  150. `NormalizedStartURLHash` UInt64,
  151. `StartURLDomainHash` UInt64,
  152. `NormalizedEndURLHash` UInt64,
  153. `TopLevelDomain` UInt64,
  154. `URLScheme` UInt64,
  155. `OpenstatServiceNameHash` UInt64,
  156. `OpenstatCampaignIDHash` UInt64,
  157. `OpenstatAdIDHash` UInt64,
  158. `OpenstatSourceIDHash` UInt64,
  159. `UTMSourceHash` UInt64,
  160. `UTMMediumHash` UInt64,
  161. `UTMCampaignHash` UInt64,
  162. `UTMContentHash` UInt64,
  163. `UTMTermHash` UInt64,
  164. `FromHash` UInt64,
  165. `WebVisorEnabled` UInt8,
  166. `WebVisorActivity` UInt32,
  167. `ParsedParams` Nested(
  168. Key1 String,
  169. Key2 String,
  170. Key3 String,
  171. Key4 String,
  172. Key5 String,
  173. ValueDouble Float64),
  174. `Market` Nested(
  175. Type UInt8,
  176. GoalID UInt32,
  177. OrderID String,
  178. OrderPrice Int64,
  179. PP UInt32,
  180. DirectPlaceID UInt32,
  181. DirectOrderID UInt32,
  182. DirectBannerID UInt32,
  183. GoodID String,
  184. GoodName String,
  185. GoodQuantity Int32,
  186. GoodPrice Int64),
  187. `IslandID` FixedString(16)
  188. )
  189. ENGINE = CollapsingMergeTree(Sign)
  190. PARTITION BY toYYYYMM(StartDate)
  191. ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID)
  192. SAMPLE BY intHash32(UserID)

3. 查询表

  1. SELECT
  2. sum(Sign) AS visits,
  3. sumIf(Sign, has(Goals.ID, 1105530)) AS goal_visits,
  4. (100. * goal_visits) / visits AS goal_percent
  5. FROM tutorial.visits_v1
  6. WHERE (CounterID = 912887) AND (toYYYYMM(StartDate) = 201403) AND (domain(StartURL) = 'yandex.ru')

基本概念

1. 表引擎

MergeTree 允许重复数据
ReplacingMergeTree 删除排序键值相同的重复项

2. 表字段类型

Date
DateTime
Int8
UInt8
UInt32
UInt64
Array(String)
Array(UInt16)
String
Nested

官方案例

参考资料