1 ClickHouse安裝

安裝文件清單

clickhouse-client-${version}.noarch.rpm clickhouse-common-static-dbg-${version}.x86_64.rpm clickhouse-common-static-${version}.x86_64.rpm clickhouse-server-${version}.noarch.rpm

clickhouse的安裝只需要上面4個文件

安裝命令

命令1: rpm -ivh *.rpm 命令2： yum install *.rpm

卸載
查看

rpm -qa | grep clickhouse

卸載命令：

rpm -e rpm包名

如果卸載不了，使用：

# 卸載及刪除安裝文件（需root權限） yum list installed | grep clickhouseyum remove -y clickhouse-common-staticyum remove -y clickhouse-server-commonrm -rf /var/lib/clickhouserm -rf /etc/clickhouse-*rm -rf /var/log/clickhouse-server

卸載報錯時強制刪除：

# 刪除rpm包的時候不調用卸載腳本 sudo rpm -e clickhouse-server.x86_64 --noscripts

啟動/關閉服務

systemctl start clickhouse-server systemctl status clickhouse-server systemctl restart clickhouse-server

交互式命令

clickhouse-client select 1

測試數據
下載下面文件，使用wget方式下載

wget https://clickhouse-datasets.s3.yandex.net/hits/tsv/hits_v1.tsv.xz wget https://clickhouse-datasets.s3.yandex.net/visits/tsv/visits_v1.tsv.xzyum install xz xz -d hits_v1.tsv.xz xz -d visits_v1.tsv.xz

創建數據庫

clickhouse-client --query "CREATE DATABASE IF NOT EXISTS tutorial"

建表
創建hits_v1表

clickhouse-client -m CREATE TABLE tutorial.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; CREATE TABLE tutorial.visits_v1 (`CounterID` UInt32,`StartDate` Date,`Sign` Int8,`IsNew` UInt8,`VisitID` UInt64,`UserID` UInt64,`StartTime` DateTime,`Duration` UInt32,`UTCStartTime` DateTime,`PageViews` Int32,`Hits` Int32,`IsBounce` UInt8,`Referer` String,`StartURL` String,`RefererDomain` String,`StartURLDomain` String,`EndURL` String,`LinkURL` String,`IsDownload` UInt8,`TraficSourceID` Int8,`SearchEngineID` UInt16,`SearchPhrase` String,`AdvEngineID` UInt8,`PlaceID` Int32,`RefererCategories` Array(UInt16),`URLCategories` Array(UInt16),`URLRegions` Array(UInt32),`RefererRegions` Array(UInt32),`IsYandex` UInt8,`GoalReachesDepth` Int32,`GoalReachesURL` Int32,`GoalReachesAny` Int32,`SocialSourceNetworkID` UInt8,`SocialSourcePage` String,`MobilePhoneModel` String,`ClientEventTime` DateTime,`RegionID` UInt32,`ClientIP` UInt32,`ClientIP6` FixedString(16),`RemoteIP` UInt32,`RemoteIP6` FixedString(16),`IPNetworkID` UInt32,`SilverlightVersion3` UInt32,`CodeVersion` UInt32,`ResolutionWidth` UInt16,`ResolutionHeight` UInt16,`UserAgentMajor` UInt16,`UserAgentMinor` UInt16,`WindowClientWidth` UInt16,`WindowClientHeight` UInt16,`SilverlightVersion2` UInt8,`SilverlightVersion4` UInt16,`FlashVersion3` UInt16,`FlashVersion4` UInt16,`ClientTimeZone` Int16,`OS` UInt8,`UserAgent` UInt8,`ResolutionDepth` UInt8,`FlashMajor` UInt8,`FlashMinor` UInt8,`NetMajor` UInt8,`NetMinor` UInt8,`MobilePhone` UInt8,`SilverlightVersion1` UInt8,`Age` UInt8,`Sex` UInt8,`Income` UInt8,`JavaEnable` UInt8,`CookieEnable` UInt8,`JavascriptEnable` UInt8,`IsMobile` UInt8,`BrowserLanguage` UInt16,`BrowserCountry` UInt16,`Interests` UInt16,`Robotness` UInt8,`GeneralInterests` Array(UInt16),`Params` Array(String),`Goals` Nested(ID UInt32,Serial UInt32,EventTime DateTime,Price Int64,OrderID String,CurrencyID UInt32),`WatchIDs` Array(UInt64),`ParamSumPrice` Int64,`ParamCurrency` FixedString(3),`ParamCurrencyID` UInt16,`ClickLogID` UInt64,`ClickEventID` Int32,`ClickGoodEvent` Int32,`ClickEventTime` DateTime,`ClickPriorityID` Int32,`ClickPhraseID` Int32,`ClickPageID` Int32,`ClickPlaceID` Int32,`ClickTypeID` Int32,`ClickResourceID` Int32,`ClickCost` UInt32,`ClickClientIP` UInt32,`ClickDomainID` UInt32,`ClickURL` String,`ClickAttempt` UInt8,`ClickOrderID` UInt32,`ClickBannerID` UInt32,`ClickMarketCategoryID` UInt32,`ClickMarketPP` UInt32,`ClickMarketCategoryName` String,`ClickMarketPPName` String,`ClickAWAPSCampaignName` String,`ClickPageName` String,`ClickTargetType` UInt16,`ClickTargetPhraseID` UInt64,`ClickContextType` UInt8,`ClickSelectType` Int8,`ClickOptions` String,`ClickGroupBannerID` Int32,`OpenstatServiceName` String,`OpenstatCampaignID` String,`OpenstatAdID` String,`OpenstatSourceID` String,`UTMSource` String,`UTMMedium` String,`UTMCampaign` String,`UTMContent` String,`UTMTerm` String,`FromTag` String,`HasGCLID` UInt8,`FirstVisit` DateTime,`PredLastVisit` Date,`LastVisit` Date,`TotalVisits` UInt32,`TraficSource` Nested(ID Int8,SearchEngineID UInt16,AdvEngineID UInt8,PlaceID UInt16,SocialSourceNetworkID UInt8,Domain String,SearchPhrase String,SocialSourcePage String),`Attendance` FixedString(16),`CLID` UInt32,`YCLID` UInt64,`NormalizedRefererHash` UInt64,`SearchPhraseHash` UInt64,`RefererDomainHash` UInt64,`NormalizedStartURLHash` UInt64,`StartURLDomainHash` UInt64,`NormalizedEndURLHash` UInt64,`TopLevelDomain` UInt64,`URLScheme` UInt64,`OpenstatServiceNameHash` UInt64,`OpenstatCampaignIDHash` UInt64,`OpenstatAdIDHash` UInt64,`OpenstatSourceIDHash` UInt64,`UTMSourceHash` UInt64,`UTMMediumHash` UInt64,`UTMCampaignHash` UInt64,`UTMContentHash` UInt64,`UTMTermHash` UInt64,`FromHash` UInt64,`WebVisorEnabled` UInt8,`WebVisorActivity` UInt32,`ParsedParams` Nested(Key1 String,Key2 String,Key3 String,Key4 String,Key5 String,ValueDouble Float64),`Market` Nested(Type UInt8,GoalID UInt32,OrderID String,OrderPrice Int64,PP UInt32,DirectPlaceID UInt32,DirectOrderID UInt32,DirectBannerID UInt32,GoodID String,GoodName String,GoodQuantity Int32,GoodPrice Int64),`IslandID` FixedString(16) ) ENGINE = CollapsingMergeTree(Sign) PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID) SAMPLE BY intHash32(UserID) SETTINGS index_granularity = 8192

數據導入
–max_insert_block_size=100000:表示每次批量導入100000行。

date clickhouse-client --query "INSERT INTO tutorial.hits_v1 FORMAT TSV" --max_insert_block_size=100000 < hits_v1.tsv dateclickhouse-client --query "INSERT INTO tutorial.visits_v1 FORMAT TSV" --max_insert_block_size=100000 < visits_v1.tsv

查詢一下數據（如果想按照好的格式顯示，可以在最后加上WG）：
select * from hits_v1 limit 1; \G

查詢

SELECTStartURL AS URL,AVG(Duration) AS AvgDuration FROM tutorial.visits_v1 WHERE StartDate BETWEEN '2014-03-23' AND '2014-03-30' GROUP BY URL ORDER BY AvgDuration DESC LIMIT 10;SELECTsum(Sign) AS visits,sumIf(Sign, has(Goals.ID, 1105530)) AS goal_visits,(100. * goal_visits) / visits AS goal_percent FROM tutorial.visits_v1 WHERE (CounterID = 912887) AND (toYYYYMM(StartDate) = 201403) AND (domain(StartURL) = 'yandex.ru');

---

2 clickhouse-client

交互式模式：clickhouse-client
非交互式模式： 指定了–query參數或將數據發送至標準的stdin。

測試表創建：

create database if not exists test; use test; drop table test; create table test(id UInt8, text String, created DateTime) ENGINE=TinyLog;

批模式數據導入：

echo -ne "1, 'some text', '2016-08-14 00:00:00'\n2, 'some more text', '2016-08-14 00:00:01'" | clickhouse-client --database=test --query="INSERT INTO test FORMAT CSV";cat <<_EOF | clickhouse-client --database=test --query="INSERT INTO test FORMAT CSV"; 3, 'some text', '2016-08-14 00:00:00' 4, 'some more text', '2016-08-14 00:00:01' _EOFselect * from test.test;\G

總結：
（1）、批模式下的默認數據格式為TabSeparated，可使用FORMAT指定格式。
（2）、使用–multiquery參數，可同時執行多個查詢（除了INSERT），每個查詢語句使用分號(;)分隔。
（3）、使用–multiline或-m參數，允許執行多行的查詢。
（4）、在查詢語句的分號之后指定\G或使用\G替換分號， 數據以垂直的格式展示。
（5）、歷史的執行語句保存在操作系統文件：~/.clickhouse-client-history。
（6）、退出客戶端的方式，按Ctrl + D或Ctrl + C，或執行命令：exit、quit、logout、q、Q、:q。

查詢中指定參數：

clickhouse-client --param_myid=3 --database=test --query="select * from test where id>{myid:UInt8}" clickhouse-client --database=test --query="select * from test where id>3"

查詢語句指定參數，參數值通過客戶端參數傳遞。
使用大括號占位要傳遞的參數值，格式為： {:}
name：占位標識符。通過clickhouse-client參數指定，格式為–param_ = value 。
data type：指定參數值的數據類型，例如：UInt8、String等。

clickhouse-client --param_parName="[1, 2]" -q "SELECT * FROM table WHERE a = {parName:Array(UInt16)}"clickhouse-client --query="select * from test.test FORMAT TabSeparated" > file.tsv

clickhouse-client查找配置文件的順序：
（1）、通過–config-file指定的配置文件。
（2）、./clickhouse-client.xml
（3）、~/.clickhouse-client/config.xml
（4）、/etc/clickhouse-client/config.xml

{host}
{port}
{user}
{database}
{display_name}

3 MySQL接口操作ClickHouse

安裝MySQL的客戶端

tar -xzf MySQL-client-5.6.38-1.el6.x86_64.tar.gz rpm -ivh MySQL-client-5.6.38-1.el6.x86_64.rpm

配置MySQL協議

vim /etc/clickhouse-server/config.xml <mysql_port>9004</mysql_port>

重啟clickhouse

systemctl restart clickhouse-server

命令行連接到ClickHouse：

mysql --protocol tcp -u default -P 9004

為了兼容所有MySQL客戶端，建議使用SHA1加密用戶密碼(即修改訪問密碼，下面的命令直接在服務器上執行)：

PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" \ | sha1sum | tr -d '-' | xxd -r -p | sha1sum | tr -d '-' 0FeOfptD 這個作為用戶名 e6f07fa4a54a8d81a3fd318378311d8fd5ae8e6c 密碼

修改配置文件：vim /etc/clickhouse-server/users.xml

將<password></password>注釋，然后添加如下： <password_double_sha1_hex>e6f07fa4a54a8d81a3fd318378311d8fd5ae8e6c</password_double_sha1_hex>

執行命令：

mysql --protocol tcp -u default -P 9004 --password=0FeOfptD

---

4 DBeaver可視化工具安裝與使用

安裝DBeaver （軟件下載地址：https://dbeaver.io/download/ ；安裝方式是直接下一步下一步的方式進行安裝：）

下載DBeaver的JDBC驅動

連接ClickHouse， 注意，配置CH的網絡

/etc/clickhouse-server/config.xml <listen_host>0.0.0.0</listen_host>

使用DBeaver連接ClickHouse



編輯如下內容：

點擊"編輯驅動設置"



添加阿里云的源

URL:http://maven.aliyun.com/nexus/content/groups/public/
然后點擊"應用"，“Apply and Close”，一直點擊到最后。

在"SQL編輯器”里，可以點擊"SQL編輯器"。在編輯器中可以編寫sql語句。

設置參數
在URL模板中，設置ClickHouse的參數

jdbc:clickhouse://{host}:{port}[/{database}]?socket_timeout=600000&max_bytes_before_external_group_by=20000000

配置參數：

timeout socket_timeout

外部聚合，如果用于聚合內存不足，10G

max_bytes_before_external_group_by=5G

總結

以上是生活随笔為你收集整理的02_clickhouse安装，卸载，启动/关闭服务，交互式命令（数据库创建，数据导入，查询），批模式数据导入，MySQL接口操作ClickHouse，DBeaver可视化工具安装与使用（学习笔记）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。