當(dāng)前位置：首頁(yè) > 编程资源 > 综合教程 >内容正文

综合教程

ClickHouse快速入门

發(fā)布時(shí)間：2023/12/4 综合教程 26 生活家

生活随笔收集整理的這篇文章主要介紹了 ClickHouse快速入门小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

ClickHouse快速入門(mén)

學(xué)習(xí)目標(biāo)

1）、ClickHouse數(shù)據(jù)庫(kù)概述、應(yīng)用場(chǎng)景OLAP分析技術(shù)框架比較
2）、ClickHouse 快速入門(mén)安裝部署、服務(wù)啟動(dòng)、基本client使用
3）、ClickHouse 快讀體驗(yàn)航班飛行數(shù)據(jù)，基本分組、聚合、排序查詢，快、快、快
4）、ClickHouse SQL語(yǔ)法SQL查詢命令、INSERT語(yǔ)句、ALTER語(yǔ)句（間接實(shí)現(xiàn)更新和刪除）
5）、ClickHouse 數(shù)據(jù)類(lèi)型常見(jiàn)使用
6）、ClickHouse 表存儲(chǔ)引擎日志引擎MySQL數(shù)據(jù)庫(kù)合并樹(shù)引擎MergeTree Engine

實(shí)時(shí)OLAP分析之技術(shù)選型

本項(xiàng)目中，需要分析實(shí)時(shí)性在毫秒級(jí)別，最多不超過(guò)秒級(jí)需求，選擇OLAP分析引擎，選擇ClickHouse

? 目前市面上主流的開(kāi)源OLAP引擎包含不限于：Hive、Presto、Kylin、Impala、SparkSQL、Druid、Clickhouse、Greeplum等，可以說(shuō)目前沒(méi)有一個(gè)引擎能在數(shù)據(jù)量，靈活程度和性能上做到完美，用戶需要根據(jù)自己的需求進(jìn)行選型。

1）、Kylin 麒麟

2）、Druid

上面給出了常用的一些OLAP引擎，各自有各自的特點(diǎn)，將其分組：

沒(méi)有最好的解決方案：

實(shí)時(shí)OLAP分析之設(shè)計(jì)方案

首先，ClickHouse數(shù)據(jù)庫(kù)，到底有多火？？

ClickHouse是近年來(lái)備受關(guān)注的開(kāi)源列式數(shù)據(jù)庫(kù)，主要用于數(shù)據(jù)分析（OLAP）領(lǐng)域。目前國(guó)內(nèi)社區(qū)火熱，各個(gè)大廠紛紛跟進(jìn)大規(guī)模使用：

使用ClickHouse 實(shí)時(shí)OLAP分析數(shù)據(jù)庫(kù)，存儲(chǔ)業(yè)務(wù)數(shù)據(jù)，對(duì)外提供數(shù)據(jù)查看功能：

1）、實(shí)時(shí)大屏展示

2）、數(shù)據(jù)服務(wù)接口

實(shí)時(shí)增量將業(yè)務(wù)數(shù)據(jù)存儲(chǔ)至ClickHouse數(shù)據(jù)庫(kù)表中，以便OLAP分析查詢，比如為實(shí)時(shí)大屏提供數(shù)據(jù)和提供數(shù)據(jù)接口服務(wù)等。

當(dāng)將業(yè)務(wù)數(shù)據(jù)存儲(chǔ)到CK以后，兩個(gè)主要應(yīng)用：實(shí)時(shí)大屏展示和數(shù)據(jù)服務(wù)接口。

物流項(xiàng)目中，數(shù)據(jù)服務(wù)接口使用SpringCloud開(kāi)發(fā)的，目前使用多框架和流行框架

整個(gè)項(xiàng)目，提供實(shí)時(shí)大屏系統(tǒng)，展示每日物流業(yè)務(wù)數(shù)據(jù)實(shí)時(shí)統(tǒng)計(jì)，每隔10秒中，刷新頁(yè)面，調(diào)用服務(wù)接口，從ClickHouse數(shù)據(jù)庫(kù)查詢數(shù)據(jù)。

ClickHouse 入門(mén)之概述及案例

ClickHouse是一個(gè)面向列的數(shù)據(jù)庫(kù)管理系統(tǒng)（DBMS），用于在線分析處理查詢（OLAP）。

官網(wǎng)：https://clickhouse.tech/

文檔：https://clickhouse.tech/docs/en/、https://clickhouse.tech/docs/zh/

logo 圖標(biāo)：表示數(shù)據(jù)為列式存儲(chǔ)，紅色部分表示要查詢數(shù)據(jù)

[外鏈圖片轉(zhuǎn)存失敗,源站可能有防盜鏈機(jī)制,建議將圖片保存下來(lái)直接上傳(img-CQDqJZtx-1618997601312)(https://gitee.com/TuNan86/mapdeport2/raw/master/img/20210420163811.svg)]

ClickHouse目前在國(guó)內(nèi)發(fā)展形勢(shì)相當(dāng)好，各個(gè)大廠基本上都在使用，存儲(chǔ)海量數(shù)據(jù)，實(shí)時(shí)OLAP分析。

簡(jiǎn)單的說(shuō)ClickHouse作為分析型數(shù)據(jù)庫(kù)，三大特點(diǎn)：一是跑分快，二是功能多，三是文藝范。

2）、ClickHouse 發(fā)展歷程

從MyISAM引擎存在問(wèn)題，到Metrage引擎誕生，一直到ClickHouse開(kāi)源，歷經(jīng)6年時(shí)間。

3）、ClickHouse 特性

4）、ClickHouse 優(yōu)勢(shì)

ClickHouse存儲(chǔ)數(shù)據(jù)是面向列進(jìn)行存儲(chǔ)，類(lèi)似ORC和Parquet及Kudu數(shù)據(jù)庫(kù)存儲(chǔ)數(shù)據(jù)方式。

5）、ClickHouse 基準(zhǔn)測(cè)試

? ClickHouse提供了一個(gè)與其他列式數(shù)據(jù)庫(kù)的基準(zhǔn)測(cè)試，這個(gè)基準(zhǔn)測(cè)試大多數(shù)是在單臺(tái)服務(wù)器進(jìn)行測(cè)試，該服務(wù)器的配置是：

6）、ClickHouse 應(yīng)用場(chǎng)景

數(shù)據(jù)量大、實(shí)時(shí)OLAP查詢需求，就可以考慮使用CK。

1、絕大多數(shù)請(qǐng)求都是用于讀訪問(wèn)的，數(shù)據(jù)只是添加到數(shù)據(jù)庫(kù)，沒(méi)有必要修改

2、讀取數(shù)據(jù)時(shí)，會(huì)從數(shù)據(jù)庫(kù)中提取出大量的行，但只用到一小部分列

3、表很“寬”，即表中包含大量的列

4、每次查詢中只會(huì)查詢一個(gè)大表。除了一個(gè)大表，其余都是小表

7）、ClickHouse 使用案例

目前國(guó)內(nèi)社區(qū)火熱，各個(gè)大廠紛紛跟進(jìn)大規(guī)模使用。

目前來(lái)說(shuō)，如果使用ClickHouse存儲(chǔ)業(yè)務(wù)數(shù)據(jù)，進(jìn)行實(shí)時(shí)OALP分析，往往與Superset集成使用。

自己學(xué)習(xí)：Superset與ClickHouse整合使用。

https://superset.apache.org/docs/databases/clickhouse

ClickHouse 入門(mén)之安裝部署

目標(biāo)：ClickHouse單機(jī)版安裝部署及服務(wù)啟動(dòng)

文檔：https://clickhouse.tech/#quick-start

在線安裝ClickHouse數(shù)據(jù)庫(kù)時(shí)，可能由于網(wǎng)絡(luò)原因，很慢很慢，實(shí)際企業(yè)中，推薦離線安裝：

1）、第一種：tar包安裝

https://repo.clickhouse.tech/tgz/stable/

2）、第二種：RPM包安裝

https://repo.yandex.ru/clickhouse/rpm/stable/x86_64/

本次項(xiàng)目，采用在線安裝ClickHouse數(shù)據(jù)庫(kù)，具體步驟如下所示：

如何進(jìn)行在線安裝ClickHouse數(shù)據(jù)庫(kù)：

1）、安裝yum-utils工具包

yum install yum-utils -y

2）、添加ClickHouse的yum源

yum-config-manager --add-repo https://repo.yandex.ru/clickhouse/rpm/stable/x86_64

3）、安裝ClickHouse的服務(wù)端和客戶端

yum install -y clickhouse-server clickhouse-client --nogpgcheck

采用YUM安裝方式，ClickHouser安裝完成以后目錄存說(shuō)明：

默認(rèn)的配置文件路徑是：/etc/clickhouse-server/

默認(rèn)的日志文件路徑是：/var/log/clickhouse-server/

? clickhouse的server配置，在/etc/clickhouse-server/config.xml及user.xml，前者是clickhouse的系統(tǒng)配置，包括日志，服務(wù)部署ip，zk等配置。后者是當(dāng)前節(jié)點(diǎn)服務(wù)的配置，包括用戶名密碼，內(nèi)存大小限制等。

1）、服務(wù)配置：config.xml

ClickHouse數(shù)據(jù)庫(kù)提供2個(gè)端口號(hào)，便于訪問(wèn)：

HTTP 協(xié)議端口號(hào)：8123，使用JDBC Client 訪問(wèn)CK時(shí)，使用此端口號(hào)

TCP 協(xié)議端口號(hào)：默認(rèn)值為9000，click-client命令行使用此端口號(hào)，但是，需要修改端口號(hào)，改為9999，由于ClouderaManagerServer服務(wù)占用9000端口號(hào)

2）、用戶配置：users.xml

默認(rèn)情況下，ClickHouse數(shù)據(jù)庫(kù)用戶名為default，沒(méi)有設(shè)置密碼，但是實(shí)際項(xiàng)目中，都會(huì)配置用戶和密碼，本物流項(xiàng)目：root/123456

啟動(dòng)ClickHouse數(shù)據(jù)庫(kù)，要么前端啟動(dòng)，要么后臺(tái)啟動(dòng)（選擇此種方式）：

前端服務(wù)啟動(dòng)：clickhouse-server start

后臺(tái)服務(wù)啟動(dòng)：systemctl start clickhouse-server

[root@node2 ~]# systemctl start clickhouse-server
[root@node2 ~]# 
[root@node2 ~]# netstat -tnlp|grep clickhouse    
tcp        0      0 0.0.0.0:8123            0.0.0.0:*               LISTEN      22648/clickhouse-se 
tcp        0      0 0.0.0.0:9004            0.0.0.0:*               LISTEN      22648/clickhouse-se 
tcp        0      0 0.0.0.0:9999            0.0.0.0:*               LISTEN      22648/clickhouse-se 
tcp        0      0 0.0.0.0:9009            0.0.0.0:*               LISTEN      22648/clickhouse-se 
[root@node2 ~]#

使用ClickHouse提供客戶端命令連接服務(wù)：clickhouse-client

[root@node2 ~]# clickhouse-client -m --host node2.itcast.cn --port 9999 --user root --password 123456
ClickHouse client version 20.4.2.9 (official build).
Connecting to node2.itcast.cn:9999 as user root.
Connected to ClickHouse server version 20.4.2 revision 54434.node2.itcast.cn :) select version();SELECT version()┌─version()─┐
│ 20.4.2.9  │
└───────────┘1 rows in set. Elapsed: 0.004 sec. node2.itcast.cn :)

? ClickHouse 集群部署，與HBase、Kudu存儲(chǔ)引擎集群部署，稍微不一樣。采用多主（無(wú)中心）架構(gòu)，集群中的每個(gè)節(jié)點(diǎn)角色對(duì)等，客戶端訪問(wèn)任意一個(gè)節(jié)點(diǎn)都能得到相同的效果。

1）、將數(shù)據(jù)劃分分片Shard

2）、每個(gè)Shard分片數(shù)據(jù)放在一臺(tái)機(jī)器上

3）、支持?jǐn)?shù)據(jù)副本Replication，其副本概念與Elasticsearch類(lèi)似

ClickHouse的數(shù)據(jù)副本一般通過(guò)ReplicatedMergeTree復(fù)制表系列引擎實(shí)現(xiàn)；

副本之間借助ZooKeeper實(shí)現(xiàn)數(shù)據(jù)的一致性。

副本與分片不能在同一臺(tái)機(jī)器上

https://my.oschina.net/u/4658124/blog/4875696

? 上圖中展示如何將數(shù)據(jù)劃分為Shard分片，并且設(shè)置1個(gè)副本，分布式架構(gòu)策略，在搭建CK集群時(shí)，指定分?jǐn)?shù)、副本數(shù)，和運(yùn)行的主機(jī)名稱(chēng)和端口號(hào)等等信息。

文檔：https://clickhouse.tech/docs/zh/getting-started/tutorial/#cluster-deployment

ClickHouse 入門(mén)之命令行使用

目標(biāo)：ClickHouse 數(shù)據(jù)庫(kù)提供客戶端命令【clickhouse-client】基本使用。

1、MySQL數(shù)據(jù)庫(kù)MySQL Server 服務(wù)mysql 命令行客戶端2、ClickHouse數(shù)據(jù)庫(kù)ClickHouse Server 服務(wù)clickhouse-client 命令行客戶端

ClickHouse安裝包中提供了clickhouse-client工具，這個(gè)客戶端在運(yùn)行shell環(huán)境中，使用TCP方式連接clickhouse-server服務(wù)。

連接ClickHouse數(shù)據(jù)庫(kù)服務(wù)時(shí)，提供兩種方式：
1）、方式一、批量執(zhí)行方式，使用–query或-q指定查詢語(yǔ)句
 clickhouse-client --host node2.itcast.cn --port 9999 --user root --password 123456 -q "select count(1) as cnt from db_test.ontime"
2）、方式二：交互式查詢，通過(guò)加上-m參數(shù)，表示編寫(xiě)SQL語(yǔ)句支持多行模式
clickhouse-client -m --host node2.itcast.cn --port 9999 --user root --password 123456 

ClickHouse 入門(mén)之可視化界面

ClickHouse數(shù)據(jù)庫(kù)除了可以使用clickhouse-client客戶端連接以外，提供一些其他GUI 工具連接。

https://clickhouse.tech/docs/zh/interfaces/third-party/gui/

1）、DBeaver 具有ClickHouse支持的通用桌面數(shù)據(jù)庫(kù)客戶端。

DBeaver客戶端工具，連接CK Server服務(wù)時(shí)，采用JDBC 方式，所以需要驅(qū)動(dòng)包

連接以后，可以編寫(xiě)SQL語(yǔ)句，查詢CK數(shù)據(jù)庫(kù)表數(shù)據(jù)分析處理。

2）、IDEA 支持連接ClickHouse

DataGrip 是JetBrains的數(shù)據(jù)庫(kù)IDE，專(zhuān)門(mén)支持ClickHouse。它還嵌入到其他基于IntelliJ的工具中：PyCharm，IntelliJ IDEA，GoLand，PhpStorm等。

配置完成以后，編寫(xiě)SQL查詢數(shù)據(jù)

ClickHouse 入門(mén)之快速體驗(yàn)【導(dǎo)入數(shù)據(jù)】

目標(biāo)：在CK數(shù)據(jù)庫(kù)中，創(chuàng)建表（很多列），導(dǎo)入數(shù)據(jù)（海量，最好上千萬(wàn)），進(jìn)行基本查詢，性能狀況。

使用官方提供案例：航班飛行數(shù)據(jù)案例

https://clickhouse.tech/docs/zh/getting-started/example-datasets/ontime/

1）、編寫(xiě)下載航班數(shù)據(jù)腳本

vim clickhouse-example-data-download.shfor s in `seq 2017 2020`
do
for m in `seq 1 12`
do
wget https://transtats.bts.gov/PREZIP/On_Time_Reporting_Carrier_On_Time_Performance_1987_present_${s}_${m}.zip
done
donechmod +x clickhouse-example-data-download.sh./clickhouse-example-data-download.sh

當(dāng)數(shù)據(jù)下載完成以后，在ClickHouse數(shù)據(jù)庫(kù)中創(chuàng)建表。

2）、創(chuàng)建ontime表，可以在default數(shù)據(jù)庫(kù)中創(chuàng)建。

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
PARTITION BY Year
ORDER BY (Carrier, FlightDate)
SETTINGS index_granularity = 8192;

表創(chuàng)建完成完成以后，將下載數(shù)據(jù)文件導(dǎo)入到表中。

3）、導(dǎo)入數(shù)據(jù)到表中，導(dǎo)入數(shù)據(jù)腳本，與數(shù)據(jù)文件zip包在同一個(gè)目錄下

 vim import.shfor i in *.zip; do echo $i; unzip -cq $i '*.csv' | sed 's/\.00//g' | clickhouse-client --host node2.itcast.cn --port 9999 --user root --password 123456 --query="INSERT INTO default.ontime FORMAT CSVWithNames"; donechmod +x import.sh./import.sh

4）、查看導(dǎo)入數(shù)據(jù)量

node2.itcast.cn :) select count(1) as total from default.ontime ;SELECT count(1) AS total
FROM default.ontime┌────total─┐
│ 24998458 │
└──────────┘1 rows in set. Elapsed: 0.022 sec.

ClickHouse 入門(mén)之快速體驗(yàn)【簡(jiǎn)單查詢】

? 前面已經(jīng)將航空飛行數(shù)據(jù)導(dǎo)入至ClickHouse表：ontime，編寫(xiě)SQL語(yǔ)句進(jìn)行查詢

文檔：https://clickhouse.tech/docs/zh/getting-started/example-datasets/ontime/#cong-yuan-shi-shu-ju-dao-ru

查詢一：查詢總條數(shù)

node2.itcast.cn :) select count(1) from ontime;SELECT count(1)
FROM ontime┌─count(1)─┐
│ 24998458 │
└──────────┘1 rows in set. Elapsed: 0.130 sec.

查詢二：查詢從2019年到2020年每天的航班數(shù)

SELECT DayOfWeek, count(1) AS c
FROM ontime
WHERE (Year >= 2019) AND (Year <= 2020)
GROUP BY DayOfWeek
ORDER BY c DESC┌─DayOfWeek─┬───────c─┐
│         1 │ 1807610 │
│         5 │ 1803712 │
│         4 │ 1800290 │
│         7 │ 1747635 │
│         3 │ 1729987 │
│         2 │ 1703345 │
│         6 │ 1517812 │
└───────────┴─────────┘7 rows in set. Elapsed: 0.400 sec. Processed 12.11 million rows, 36.33 MB (30.31 million rows/s., 90.93 MB/s.)

查詢?nèi)?#xff1a;查詢從2017年到2020年每周延誤超過(guò)10分鐘的航班數(shù)

SELECT DayOfWeek, count(1) AS c
FROM ontime
WHERE (DepDelay > 10) AND (Year >= 2017) AND (Year <= 2020)
GROUP BY DayOfWeek
ORDER BY c DESC┌─DayOfWeek─┬──────c─┐
│         5 │ 773549 │
│         4 │ 754612 │
│         1 │ 732262 │
│         7 │ 670729 │
│         3 │ 641381 │
│         2 │ 625069 │
│         6 │ 527510 │
└───────────┴────────┘7 rows in set. Elapsed: 0.249 sec. Processed 25.00 million rows, 174.99 MB (100.44 million rows/s., 703.10 MB/s.)

查詢四：查詢2017年到2020年每個(gè)機(jī)場(chǎng)延誤超過(guò)10分鐘以上的次數(shù)

SELECT Origin, count(1) AS c
FROM ontime
WHERE (DepDelay > 10) AND (Year >= 2017) AND (Year <= 2020)
GROUP BY Origin
ORDER BY c DESC
LIMIT 10┌─Origin─┬──────c─┐
│ ATL    │ 260848 │
│ ORD    │ 236185 │
│ DFW    │ 209180 │
│ DEN    │ 187008 │
│ LAX    │ 163684 │
│ CLT    │ 143552 │
│ SFO    │ 136671 │
│ LAS    │ 125721 │
│ PHX    │ 119538 │
│ EWR    │ 113920 │
└────────┴────────┘10 rows in set. Elapsed: 1.242 sec. Processed 25.00 million rows, 274.99 MB (20.13 million rows/s., 221.41 MB/s.)

查詢五：查詢2020年各航空公司航班次數(shù)

SELECT Carrier, count(1)
FROM ontime
WHERE Year = 2020
GROUP BY Carrier
ORDER BY count(1) DESC┌─Carrier─┬─count(1)─┐
│ WN      │   961276 │
│ OO      │   597021 │
│ DL      │   581101 │
│ AA      │   569806 │
│ UA      │   308217 │
│ YX      │   219751 │
│ 9E      │   211398 │
│ MQ      │   211268 │
│ OH      │   192614 │
│ B6      │   144163 │
│ AS      │   138226 │
│ YV      │   136198 │
│ NK      │   135102 │
│ G4      │    98489 │
│ F9      │    91175 │
│ EV      │    52135 │
│ HA      │    40414 │
└─────────┴──────────┘17 rows in set. Elapsed: 0.232 sec. Processed 4.69 million rows, 18.75 MB (20.24 million rows/s., 80.97 MB/s.)

查詢六：查詢2020年各航空公司延誤超過(guò)10分鐘以上的百分比

SELECT Carrier, avg(DepDelay > 10) * 100 AS c3
FROM ontime
WHERE Year = 2020
GROUP BY Carrier
ORDER BY Carrier ASC┌─Carrier─┬─────────────────c3─┐
│ 9E      │ 7.7540941730763775 │
│ AA      │  11.19837277950741 │
│ AS      │ 11.176623790025031 │
│ B6      │ 13.011660412172333 │
│ DL      │  8.637396941323454 │
│ EV      │  8.786803490936991 │
│ F9      │ 12.098711269536604 │
│ G4      │ 14.588431195361917 │
│ HA      │  7.106448260503785 │
│ MQ      │ 10.312967415794157 │
│ NK      │ 12.745185119391275 │
│ OH      │ 12.992305855233784 │
│ OO      │ 10.165974061213927 │
│ UA      │  10.15420953419182 │
│ WN      │  9.014580619926015 │
│ YV      │ 11.669040661390035 │
│ YX      │  7.635460134424872 │
└─────────┴────────────────────┘17 rows in set. Elapsed: 0.258 sec. Processed 4.69 million rows, 37.51 MB (18.17 million rows/s., 145.38 MB/s.)

查詢七：查詢2017年到2020年期間各航空公司延誤超過(guò)10分鐘以上的百分比

SELECT Carrier, avg(DepDelay > 10) * 100 AS c3
FROM ontime
WHERE (Year >= 2017) AND (Year <= 2020)
GROUP BY Carrier
ORDER BY Carrier ASC┌─Carrier─┬─────────────────c3─┐
│ 9E      │ 15.354672914855824 │
│ AA      │  19.00594279206715 │
│ AS      │ 15.705546781216759 │
│ B6      │ 26.631255273918477 │
│ DL      │  15.61732246447258 │
│ EV      │ 19.836023086764605 │
│ F9      │  24.95219885277247 │
│ G4      │  20.40064663433495 │
│ HA      │  10.34594031922276 │
│ MQ      │ 17.057664370064582 │
│ NK      │ 18.594739821946288 │
│ OH      │ 19.605983889528193 │
│ OO      │ 16.764282993552108 │
│ UA      │ 18.600505660056456 │
│ VX      │ 26.373080957913615 │
│ WN      │ 22.499000442960543 │
│ YV      │  18.05329199066337 │
│ YX      │ 14.972543035179598 │
└─────────┴────────────────────┘18 rows in set. Elapsed: 0.654 sec. Processed 25.00 million rows, 199.99 MB (38.24 million rows/s., 305.93 MB/s.)

查詢八：每年航班延誤超過(guò)10分鐘的百分比

SELECT Year, avg(DepDelay > 10) AS pecent
FROM ontime
GROUP BY Year
ORDER BY Year ASC┌─Year─┬──────────────pecent─┐
│ 2017 │ 0.20725472238586506 │
│ 2018 │ 0.20937579625604738 │
│ 2019 │  0.2109795464506577 │
│ 2020 │ 0.10084584056579346 │
└──────┴─────────────────────┘4 rows in set. Elapsed: 0.255 sec. Processed 25.00 million rows, 149.99 MB (97.99 million rows/s., 587.96 MB/s.)

查詢九：每年更受人們喜歡的目的地

SELECT DestCityName, uniqExact(OriginCityName) AS u
FROM ontime
WHERE (Year >= 2019) AND (Year <= 2020)
GROUP BY DestCityName
ORDER BY u DESC
LIMIT 10┌─DestCityName──────────┬───u─┐
│ Chicago, IL           │ 190 │
│ Denver, CO            │ 186 │
│ Dallas/Fort Worth, TX │ 184 │
│ Atlanta, GA           │ 167 │
│ Charlotte, NC         │ 139 │
│ Houston, TX           │ 129 │
│ Minneapolis, MN       │ 129 │
│ Phoenix, AZ           │ 128 │
│ Detroit, MI           │ 116 │
│ Las Vegas, NV         │ 114 │
└───────────────────────┴─────┘10 rows in set. Elapsed: 1.711 sec. Processed 12.11 million rows, 561.05 MB (7.08 million rows/s., 327.91 MB/s.)

查詢十：每一年的航班總次數(shù)

SELECT Year, count(1) AS c1
FROM ontime
GROUP BY Year┌─Year─┬──────c1─┐
│ 2017 │ 5674621 │
│ 2018 │ 7213446 │
│ 2019 │ 7422037 │
│ 2020 │ 4688354 │
└──────┴─────────┘4 rows in set. Elapsed: 0.119 sec. Processed 25.00 million rows, 50.00 MB (209.68 million rows/s., 419.37 MB/s.)

CK SQL 語(yǔ)法之常用 SQL 命令】

ClickHouse數(shù)據(jù)庫(kù)中創(chuàng)建表、查詢SQL語(yǔ)句，基本上與Mysql數(shù)據(jù)庫(kù)類(lèi)似，接下來(lái)，具體常用SQL命令：

創(chuàng)建表的基本語(yǔ)法：

1）、表的類(lèi)型分為兩種：臨時(shí)表temporary和持久化表

2）、創(chuàng)建表時(shí)，建議寫(xiě)上數(shù)據(jù)庫(kù)名稱(chēng)：dbName.tableName

3）、如果使用CK集群，創(chuàng)建表時(shí)，在表的名稱(chēng)后面加上：ON CLUSTER clusterId

4）、每個(gè)數(shù)據(jù)表創(chuàng)建時(shí)，需要指定存儲(chǔ)引擎，非常關(guān)鍵屬性，決定業(yè)務(wù)查詢性能

engine = EngineName(parameters);

create [temporary] table [if not exists] tableName [ON CLUSTER cluster] (
fieldName dataType
) engine = EngineName(parameters);

? 前面演示，從航空飛行數(shù)據(jù)中查詢（過(guò)濾where、分組group by，聚合count，排序sortby）那么快原因：表的底層存儲(chǔ)引擎，比如MergeTree引擎，比較常用，決定底層如何存儲(chǔ)數(shù)據(jù)，如何查詢數(shù)據(jù)等。

CK SQL 語(yǔ)法之SELECT、INSERT和ALTER

前面講解常用SQL命令，主要就是DDL語(yǔ)句（創(chuàng)建表、刪除表），此外ClickHouse數(shù)據(jù)庫(kù)表支持DML語(yǔ)句：

1）、SELECT 語(yǔ)句，查詢數(shù)據(jù)，基本上與MySQL 查詢語(yǔ)句功能類(lèi)似

2）、INSERT語(yǔ)句，插入數(shù)據(jù)到表中

3）、ALTER語(yǔ)句，對(duì)表進(jìn)行修改，比如添加列或刪除列；此外，實(shí)現(xiàn)對(duì)表的數(shù)據(jù)進(jìn)行更新和刪除

CK中更新表的數(shù)據(jù)，采用ALTER語(yǔ)句實(shí)現(xiàn)Update功能

CK中刪除表的數(shù)據(jù)，采用ALTER語(yǔ)句實(shí)現(xiàn)Delete功能

1）、SELECT 語(yǔ)法，查詢表的數(shù)據(jù)

SELECT [DISTINCT] expr_list
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE sample_coeff]
[ARRAY JOIN ...]
[GLOBAL] ANY|ALL INNER|LEFT JOIN (subquery)|table USING columns_list
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr_list] [WITH TOTALS]
[HAVING expr]
[ORDER BY expr_list]
[LIMIT [n, ]m]
[UNION ALL ...]
[INTO OUTFILE filename]
[FORMAT format]
[LIMIT n BY columns]

SELECT語(yǔ)法中：ARRAY JOIN ...，可以幫助查詢進(jìn)行與數(shù)組和nested數(shù)據(jù)類(lèi)型的連接

CREATE TABLE tbl_test_array_join
(`str` String, `arr` Array(Int8)
)
ENGINE = Memory；insert into tbl_test_array_join(str, arr) values('a', [1,3,5]),('b', [2,4,6]);SELECT *
FROM tbl_test_array_join┌─str─┬─arr─────┐
│ a   │ [1,3,5] │
│ b   │ [2,4,6] │
└─────┴─────────┘node2.itcast.cn :) select str,arr,arrItem from tbl_test_array_join ARRAY JOIN arr as arrItem;SELECT str, arr, arrItem
FROM tbl_test_array_join
ARRAY JOIN arr AS arrItem┌─str─┬─arr─────┬─arrItem─┐
│ a   │ [1,3,5] │       1 │
│ a   │ [1,3,5] │       3 │
│ a   │ [1,3,5] │       5 │
│ b   │ [2,4,6] │       2 │
│ b   │ [2,4,6] │       4 │
│ b   │ [2,4,6] │       6 │
└─────┴─────────┴─────────┘

2）、INSERT 插入數(shù)據(jù)語(yǔ)法

ClickHouse中完整insert的主要用于向系統(tǒng)中添加數(shù)據(jù)，有如下幾種方式：

1）、語(yǔ)法一：通用插入數(shù)據(jù)

INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23)...

2）、語(yǔ)法二：

INSERT INTO [db.]table [(c1, c2, c3)] FORMAT format_name data_set

3）、語(yǔ)法三：子查詢插入數(shù)據(jù)

INSERT INTO [db.]table [(c1, c2, c3)] SELECT ...

系統(tǒng)不支持的其他用于修改數(shù)據(jù)的查詢：UPDATE、DELETE、REPLACE、MERGE、UPSERT和 INSERT UPDATE。

3）、ALTER 語(yǔ)法

ClickHouse中的ALTER只支持MergeTree系列，Merge和Distributed引擎的表

-- ALTER 語(yǔ)法
CREATE TABLE mt_table
(`date` Date, `id` UInt8, `name` String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(date)
ORDER BY id
SETTINGS index_granularity = 8192 ;insert into mt_table values ('2020-09-15', 1, 'zhangsan');
insert into mt_table values ('2020-09-15', 2, 'lisi');
insert into mt_table values ('2020-09-15', 3, 'wangwu');-- 添加列
alter table mt_table add column age UInt8 ;
-- 查看表結(jié)構(gòu)
desc mt_table ;
-- 查看數(shù)據(jù)
select * from mt_table ;-- 修改列
alter table mt_table modify column age UInt16 ;-- 刪除列
alter table mt_table drop column age ;

CK SQL 語(yǔ)法之UPDATE和DELETE

? 從使用場(chǎng)景來(lái)說(shuō)，Clickhouse是個(gè)分析型數(shù)據(jù)庫(kù)。這種場(chǎng)景下，數(shù)據(jù)一般是不變的，因此Clickhouse對(duì)update、delete的支持是比較弱的，實(shí)際上并不支持標(biāo)準(zhǔn)的update、delete操作。

-- upate和delete，基于alter語(yǔ)法實(shí)現(xiàn)CREATE TABLE tbl_test_users
(`id` UInt64, `email` String, `username` String, `gender` UInt8, `birthday` Date, `mobile` FixedString(13), `pwd` String, `regDT` DateTime, `lastLoginDT` DateTime, `lastLoginIP` String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(regDT)
ORDER BY id
SETTINGS index_granularity = 8192 ;insert into tbl_test_users(id, email, username, gender, birthday, mobile, pwd, regDT, lastLoginDT,lastLoginIP) values (1,'wcfr817e@yeah.net','督詠',2,'1992-05-31','13306834911','7f930f90eb6604e837db06908cc95149','2008-08-06 11:48:12','2015-05-08 10:51:41','106.83.54.165'),(2,'xuwcbev9y@ask.com','上磊',1,'1983-10-11','15302753472','7f930f90eb6604e837db06908cc95149','2008-08-10 05:37:32','2014-07-28 23:43:04','121.77.119.233'),(3,'mgaqfew@126.com','涂康',1,'1970-11-22','15200570030','96802a851b4a7295fb09122b9aa79c18','2008-08-10 11:37:55','2014-07-22 23:45:47','171.12.206.122'),(4,'b7zthcdg@163.net','金俊振',1,'2002-02-10','15207308903','96802a851b4a7295fb09122b9aa79c18','2008-08-10 14:47:09','2013-12-26 15:55:02','61.235.143.92'),(5,'ezrvy0p@163.net','陰福',1,'1987-09-01','13005861359','96802a851b4a7295fb09122b9aa79c18','2008-08-12 21:58:11','2013-12-26 15:52:33','182.81.200.32');ALTER TABLE tbl_test_users UPDATE username='張三' WHERE id=1;ALTER TABLE tbl_test_users DELETE WHERE id=1;

ClickHouse 入門(mén)之SQL 函數(shù)

ClickHouse數(shù)據(jù)庫(kù)，自身再帶函數(shù)：https://clickhouse.tech/docs/zh/sql-reference/functions/

其中很多函數(shù)，與MySQL數(shù)據(jù)庫(kù)和Hive中函數(shù)基本一致，可以放心使用，特殊功能，需要自己查看文檔

1）、類(lèi)型檢測(cè)函數(shù)：toTypeName

node2.itcast.cn :) select toTypeName(0);SELECT toTypeName(0)┌─toTypeName(0)─┐
│ UInt8         │
└───────────────┘1 rows in set. Elapsed: 0.002 sec. node2.itcast.cn :) select toTypeName(1000);SELECT toTypeName(1000)┌─toTypeName(1000)─┐
│ UInt16           │
└──────────────────┘1 rows in set. Elapsed: 0.003 sec.

2）、數(shù)學(xué)函數(shù)，常見(jiàn)加減乘除

node2.itcast.cn :) select divide(6, 2) ;SELECT 6 / 2┌─divide(6, 2)─┐
│            3 │
└──────────────┘1 rows in set. Elapsed: 0.130 sec. node2.itcast.cn :) select intDiv(10, 3) ;SELECT intDiv(10, 3)┌─intDiv(10, 3)─┐
│             3 │
└───────────────┘1 rows in set. Elapsed: 0.009 sec. node2.itcast.cn :) select modulo(10, 3)
:-] ;SELECT 10 % 3┌─modulo(10, 3)─┐
│             1 │
└───────────────┘1 rows in set. Elapsed: 0.048 sec.

3）、時(shí)間函數(shù)
- 當(dāng)前時(shí)間：now()
- 時(shí)間轉(zhuǎn)換：toYYYYMM(curDT) toYYYYMMDD(curDT) toYYYYMMDDhhmmss(curDT);
- 將字符串轉(zhuǎn)換為日期時(shí)間：toDateTime
- 將字符串轉(zhuǎn)換為日期：toDate

node2.itcast.cn :) select now() as curDT,toYYYYMM(curDT),toYYYYMMDD(curDT),toYYYYMMDDhhmmss(curDT);SELECT now() AS curDT, toYYYYMM(curDT), toYYYYMMDD(curDT), toYYYYMMDDhhmmss(curDT)┌───────────────curDT─┬─toYYYYMM(now())─┬─toYYYYMMDD(now())─┬─toYYYYMMDDhhmmss(now())─┐
│ 2021-03-25 15:26:58 │          202103 │          20210325 │          20210325152658 │
└─────────────────────┴─────────────────┴───────────────────┴─────────────────────────┘1 rows in set. Elapsed: 0.014 sec.

CK 數(shù)據(jù)類(lèi)型之?dāng)?shù)值類(lèi)型

ClickHouse與常用的關(guān)系型數(shù)據(jù)庫(kù)MySQL或Oracle的數(shù)據(jù)類(lèi)型類(lèi)似，提供了豐富的數(shù)據(jù)類(lèi)型支持。

文檔：https://clickhouse.tech/docs/zh/sql-reference/data-types/

1）、ClickHouse支持Int和Uint兩種固定長(zhǎng)度的整型，Int類(lèi)型是符號(hào)整型，Uint類(lèi)型是無(wú)符號(hào)整型。

2）、ClickHouse支持Float32和Float64兩種浮點(diǎn)類(lèi)型

3）、ClickHouse支持Decimal類(lèi)型的有符號(hào)定點(diǎn)數(shù)，可在加、減和乘法運(yùn)算過(guò)程中保持精度。

? 在Java語(yǔ)言中，如果對(duì)double類(lèi)型數(shù)字進(jìn)行加法操作，必須類(lèi)型轉(zhuǎn)換為BigDecimal類(lèi)型，進(jìn)行add加法操作，否則丟失精度。

CK 數(shù)據(jù)類(lèi)型之字符串及NULL類(lèi)型

ClickHouse中的String類(lèi)型沒(méi)有編碼的概念。字符串可以是任意的字節(jié)集，按它們?cè)镜姆绞?/p>
進(jìn)行存儲(chǔ)和輸出。若需存儲(chǔ)文本，建議使用UTF-8編碼。

UUID 數(shù)據(jù)類(lèi)型：

? ClickHouse支持UUID類(lèi)型（通用唯一標(biāo)識(shí)符），該類(lèi)型是一個(gè)16字節(jié)的數(shù)字，用于標(biāo)識(shí)記錄。ClickHouse內(nèi)置generateUUIDv4函數(shù)來(lái)生成UUID值，UUID數(shù)據(jù)類(lèi)型僅支持String數(shù)據(jù)類(lèi)型也支持的函數(shù)（例如，min，max和count）。

ClickHouse中沒(méi)有定義布爾類(lèi)型，可以使用UInt8類(lèi)型，取值限制為0或1。

0表示false，1表示true

ClickHouse支持Enum8和Enum16兩種枚舉類(lèi)型。Enum保存的是'string'=integer的對(duì)應(yīng)關(guān)系。

案例演示：

CREATE TABLE tbl_test_enum
(`e1` Enum8('male' = 1, 'female' = 2), `e2` Enum16('hello' = 1, 'word' = 2), `e3` Nullable(Enum8('A' = 1, 'B' = 2)), `e4` Nullable(Enum16('a' = 1, 'b' = 2))
)
ENGINE = TinyLog ;insert into tbl_test_enum values('male', 'hello', 'A', null),('male', 'word', null, 'a');insert into tbl_test_enum values(2, 1, 'A', null);

ClickHouse支持Nullable類(lèi)型，該類(lèi)型允許用NULL來(lái)表示缺失值。Nullable字段不能作為索引
列使用

-- Nullable 類(lèi)型
CREATE TABLE tbl_test_nullable
(`f1` String, `f2` Int8, `f3` Nullable(Int8)
)
ENGINE = TinyLog ;insert into tbl_test_nullable(f1,f2,f3) values('NoNull',1,1);insert into tbl_test_nullable(f1,f2,f3) values(null,2,2);
insert into tbl_test_nullable(f1,f2,f3) values('NoNull2',null,2);

CK 數(shù)據(jù)類(lèi)型之日期時(shí)間類(lèi)型

日期類(lèi)型：ClickHouse支持Date類(lèi)型，這個(gè)日期類(lèi)型用兩個(gè)字節(jié)存儲(chǔ)，表示從 1970-01-01 (無(wú)符號(hào)) 到當(dāng)
前的日期值。

日期時(shí)間類(lèi)型：ClickHouse支持DataTime類(lèi)型，這個(gè)時(shí)間戳類(lèi)型用四個(gè)字節(jié)（無(wú)符號(hào)的）存儲(chǔ)Unix時(shí)間戳。
允許存儲(chǔ)與日期類(lèi)型相同范圍內(nèi)的值，最小值為0000-00-00 00:00:00。

時(shí)間間隔interval：Interval是ClickHouse提供的一種特殊的數(shù)據(jù)類(lèi)型，此數(shù)據(jù)類(lèi)型用來(lái)對(duì)Date和Datetime進(jìn)行運(yùn)算，不能使用Interval類(lèi)型聲明表中的字段。

Interval支持的時(shí)間類(lèi)型有SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER和YEAR。

node2.itcast.cn :) select now() as cur_dt, cur_dt + interval 4 DAY plus_dt;SELECT now() AS cur_dt, cur_dt + toIntervalDay(4) AS plus_dt┌──────────────cur_dt─┬─────────────plus_dt─┐
│ 2021-03-25 16:07:01 │ 2021-03-29 16:07:01 │
└─────────────────────┴─────────────────────┘1 rows in set. Elapsed: 0.049 sec. node2.itcast.cn :) select now() as cur_dt, cur_dt + interval 4 DAY + interval 3 HOUR as plus_dt;SELECT now() AS cur_dt, (cur_dt + toIntervalDay(4)) + toIntervalHour(3) AS plus_dt┌──────────────cur_dt─┬─────────────plus_dt─┐
│ 2021-03-25 16:07:52 │ 2021-03-29 19:07:52 │
└─────────────────────┴─────────────────────┘1 rows in set. Elapsed: 0.003 sec. node2.itcast.cn :) select now() as cur_dt, cur_dt - interval 24 hour strub_dt;SELECT now() AS cur_dt, cur_dt - toIntervalHour(24) AS strub_dt┌──────────────cur_dt─┬────────────strub_dt─┐
│ 2021-03-25 16:08:42 │ 2021-03-24 16:08:42 │
└─────────────────────┴─────────────────────┘1 rows in set. Elapsed: 0.003 sec.

在ClickHouse中，對(duì)于某些類(lèi)型的列，在沒(méi)有顯示插入值時(shí)，會(huì)自動(dòng)填充默認(rèn)值處理。

CK 數(shù)據(jù)類(lèi)型之?dāng)?shù)組和元組類(lèi)型

1）、ClickHouse支持Array(T)類(lèi)型，T可以是任意類(lèi)型

node2.itcast.cn :) select array(1,3,5) as arr1,[2,4,6] as arr2, toTypeName(arr1) as arrType1, toTypeName(arr2) as arrType2;SELECT [1, 3, 5] AS arr1, [2, 4, 6] AS arr2, toTypeName(arr1) AS arrType1, toTypeName(arr2) AS arrType2┌─arr1────┬─arr2────┬─arrType1─────┬─arrType2─────┐
│ [1,3,5] │ [2,4,6] │ Array(UInt8) │ Array(UInt8) │
└─────────┴─────────┴──────────────┴──────────────┘1 rows in set. Elapsed: 0.003 sec.

2）、ClickHouse提供Tuple類(lèi)型支持，Tuple(T1,T2…)中每個(gè)元素都可以是單獨(dú)的類(lèi)型。

元組中不可以嵌套元組

CK 數(shù)據(jù)類(lèi)型之其他類(lèi)型

1)、CLickHouse支持IPv4和Ipv6兩種Domain類(lèi)型

Ipv4類(lèi)型是與UInt32類(lèi)型保持二進(jìn)制兼容的Domain類(lèi)型，其用于存儲(chǔ)IPv4地址的值；IPv6是與FixedString(16)類(lèi)型保持二進(jìn)制兼容的Domain類(lèi)型，其用于存儲(chǔ)IPv6地址的值。

node2.itcast.cn :) create table tbl_test_domain(url String, ip4 IPv4, ip6 IPv6) ENGINE = MergeTree() ORDER BY url;CREATE TABLE tbl_test_domain
(`url` String, `ip4` IPv4, `ip6` IPv6
)
ENGINE = MergeTree()
ORDER BY urlOk.0 rows in set. Elapsed: 0.005 sec. node2.itcast.cn :) insert into tbl_test_domain(url,ip4,ip6)
:-] values('http://www.itcast.cn','127.0.0.1','2a02:aa08:e000:3100::2');INSERT INTO tbl_test_domain (url, ip4, ip6) VALUESOk.1 rows in set. Elapsed: 0.005 sec. node2.itcast.cn :) select * from tbl_test_domain;SELECT *
FROM tbl_test_domain┌─url──────────────────┬───────ip4─┬─ip6────────────────────┐
│ http://www.itcast.cn │ 127.0.0.1 │ 2a02:aa08:e000:3100::2 │
└──────────────────────┴───────────┴────────────────────────┘1 rows in set. Elapsed: 0.015 sec. node2.itcast.cn :) select url,toTypeName(ip4) as ip4Type, hex(ip4) as ip4Hex,toTypeName(ip6) as ip6Type, hex(ip6) as ip6Hex
:-] from tbl_test_domain;SELECT url, toTypeName(ip4) AS ip4Type, hex(ip4) AS ip4Hex, toTypeName(ip6) AS ip6Type, hex(ip6) AS ip6Hex
FROM tbl_test_domain┌─url──────────────────┬─ip4Type─┬─ip4Hex───┬─ip6Type─┬─ip6Hex───────────────────────────┐
│ http://www.itcast.cn │ IPv4    │ 7F000001 │ IPv6    │ 2A02AA08E00031000000000000000002 │
└──────────────────────┴─────────┴──────────┴─────────┴──────────────────────────────────┘node2.itcast.cn :) select url,IPv4NumToString(ip4) as ip4Str,IPv6NumToString(ip6) as ip6Str from tbl_test_domain;SELECT url, IPv4NumToString(ip4) AS ip4Str, IPv6NumToString(ip6) AS ip6Str
FROM tbl_test_domain┌─url──────────────────┬─ip4Str────┬─ip6Str─────────────────┐
│ http://www.itcast.cn │ 127.0.0.1 │ 2a02:aa08:e000:3100::2 │
└──────────────────────┴───────────┴────────────────────────┘

2）、嵌套數(shù)據(jù)結(jié)構(gòu)

可以簡(jiǎn)單地把嵌套數(shù)據(jù)結(jié)構(gòu)當(dāng)做是所有列都是相同長(zhǎng)度的多列數(shù)組。

嵌套數(shù)據(jù)結(jié)構(gòu)的列僅支持一級(jí)嵌套

嵌套列在insert時(shí)，需要把嵌套列的每一個(gè)字段以[要插入的值]格式進(jìn)行數(shù)據(jù)插入

insert into tbl_test_nested values(1,'2019-12-25',['zhangsan'],[23],[13800138000],1);

3）、AggregateFunction 類(lèi)型

CREATE TABLE aggMT
(`whatever` Date DEFAULT '2019-12-18', `key` String, `value` String, `first` AggregateFunction(min, DateTime), `last` AggregateFunction(max, DateTime), `total` AggregateFunction(count, UInt64)
)
ENGINE = AggregatingMergeTree(whatever, (key, value), 8192)；insert into aggMT (key,value,first,last,total) select 'test','1.2.3.4',minState(toDateTime(1576654217)),maxState(toDateTime(1576654217)),countState(cast(1 as UInt64));
insert into aggMT (key,value,first,last,total) select 'test','1.2.3.5',minState(toDateTime(1576654261)),maxState(toDateTime(1576654261)),countState(cast(1 as UInt64));
insert into aggMT (key,value,first,last,total) select 'test','1.2.3.6',minState(toDateTime(1576654273)),maxState(toDateTime(1576654273)),countState(cast(1 as UInt64));insert into aggMT (key,value,first,last,total) select 'test','1.2.3.4',minState(toDateTime(1576655217)),maxState(toDateTime(1576657217)),countState(cast(1 as UInt64));
insert into aggMT (key,value,first,last,total) select 'test','1.2.3.4',minState(toDateTime(1576656217)),maxState(toDateTime(1576658217)),countState(cast(1 as UInt64));

適用場(chǎng)景：（軍訓(xùn)分小隊(duì)以小隊(duì)為單位比賽，事件跨度最短的才能贏：多個(gè)裁判開(kāi)始計(jì)時(shí)出發(fā)一個(gè)記錄一下時(shí)間，以班級(jí)+小隊(duì)編號(hào)為id）最后計(jì)算出第一個(gè)出發(fā)的時(shí)間-最后一名到達(dá)的時(shí)間為該小組的時(shí)間跨度。

ClickHouse 引擎之引擎概述

ClickHouse提供了多種不同的表引擎，表引擎可以簡(jiǎn)單理解為不同類(lèi)型的表。表引擎（即表的類(lèi)型）決定了：

文檔：https://clickhouse.tech/docs/zh/engines/table-engines/

1）、MergeTree引擎（合并樹(shù)及系列引擎）

適用于高負(fù)載任務(wù)的最通用和功能最強(qiáng)大的表引擎。

2）、日志Log引擎

最小功能的輕量級(jí)引擎

3）、集成引擎，使用其他存儲(chǔ)引擎，存儲(chǔ)表的數(shù)據(jù)

用于與其他的數(shù)據(jù)存儲(chǔ)與處理系統(tǒng)集成的引擎。

4）、用于其他特定功能的引擎

ClickHouse 引擎之TinyLog 引擎

? TinyLog 引擎，最簡(jiǎn)單的表引擎，用于將數(shù)據(jù)存儲(chǔ)在磁盤(pán)上。每列都存儲(chǔ)在單獨(dú)的壓縮文件中，寫(xiě)入時(shí)，數(shù)據(jù)將附加到文件末尾。 == 該引擎沒(méi)有并發(fā)控制 == 。

node2.itcast.cn :) create table user (id UInt16, name String) ENGINE=TinyLog;CREATE TABLE user
(`id` UInt16, `name` String
)
ENGINE = TinyLogOk.0 rows in set. Elapsed: 0.009 sec. node2.itcast.cn :) insert into user (id, name) values (1, 'zhangsan');INSERT INTO user (id, name) VALUESOk.1 rows in set. Elapsed: 0.002 sec. node2.itcast.cn :) select * from user ;SELECT *
FROM user┌─id─┬─name─────┐
│  1 │ zhangsan │
└────┴──────────┘1 rows in set. Elapsed: 0.002 sec.

查看上述創(chuàng)建表user，低層如何實(shí)現(xiàn)存儲(chǔ)

ClickHouse 引擎之MySQL 引擎

MySQL引擎用于將遠(yuǎn)程的MySQL服務(wù)器中的表映射到ClickHouse中，并允許您對(duì)表進(jìn)行INSERT和SELECT查詢，以方便您在ClickHouse與MySQL之間進(jìn)行數(shù)據(jù)交換。

此種方式，CK沒(méi)有存儲(chǔ)表的數(shù)據(jù)，MySQL才是真正存儲(chǔ)數(shù)據(jù)地方。

MySQL 數(shù)據(jù)庫(kù)引擎會(huì)將對(duì)其的查詢轉(zhuǎn)換為MySQL語(yǔ)法并發(fā)送到MySQL服務(wù)器中，因此可以執(zhí)行諸如SHOW TABLES或SHOW CREATE TABLE之類(lèi)的操作。但無(wú)法對(duì)其執(zhí)行以下操作： RENAME、CREATE TABLE、 ALTER。

案例演示：

1)、第一步、在MySQL數(shù)據(jù)庫(kù)創(chuàng)建database和table

[root@node1 ~]# docker start mysql
mysql
[root@node1 ~]# docker exec -it mysql /bin/bash
root@8b5cd2152ed9:/# 
root@8b5cd2152ed9:/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.30-log MySQL Community Server (GPL)Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| canal_tsdb         |
| crm                |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
7 rows in set (0.17 sec)mysql> create database db_test ;
Query OK, 1 row affected (0.00 sec)mysql> use db_test ;
Database changed
mysql> 
mysql> CREATE TABLE `mysql_table` (-> `int_id` INT NOT NULL AUTO_INCREMENT,-> `float` FLOAT NOT NULL,-> PRIMARY KEY (`int_id`)-> );
Query OK, 0 rows affected (0.02 sec)mysql> insert into mysql_table (`int_id`, `float`) VALUES (1,2);
Query OK, 1 row affected (0.21 sec)mysql> select * from mysql_table ;
+--------+-------+
| int_id | float |
+--------+-------+
|      1 |     2 |
+--------+-------+
1 row in set (0.00 sec)mysql>

2）、第二步、在CK中創(chuàng)建數(shù)據(jù)庫(kù)，關(guān)聯(lián)到MySQL數(shù)據(jù)庫(kù)

node2.itcast.cn :) CREATE DATABASE mysql_db ENGINE = MySQL('node1.itcast.cn:3306', 'db_test', 'root', '123456');CREATE DATABASE mysql_db
ENGINE = MySQL('node1.itcast.cn:3306', 'db_test', 'root', '123456')Ok.0 rows in set. Elapsed: 0.041 sec. node2.itcast.cn :) SHOW DATABASES;SHOW DATABASES┌─name───────────────────────────┐
│ _temporary_and_external_tables │
│ db_ck                          │
│ default                        │
│ logistics                      │
│ mysql_db                       │
│ system                         │
└────────────────────────────────┘6 rows in set. Elapsed: 0.002 sec.

注意：每次啟動(dòng)clickhouse時(shí)先要將管理的mysql服務(wù)起來(lái)，否則，clickhouse起不來(lái)。

3）、第三步、在CK中操作，查詢數(shù)據(jù)

node2.itcast.cn :) use mysql_db ;USE mysql_dbOk.0 rows in set. Elapsed: 0.001 sec. node2.itcast.cn :) show tables ;SHOW TABLES┌─name────────┐
│ mysql_table │
└─────────────┘1 rows in set. Elapsed: 0.008 sec. node2.itcast.cn :) select * from mysql_table ;SELECT *
FROM mysql_table┌─int_id─┬─float─┐
│      1 │     2 │
└────────┴───────┘1 rows in set. Elapsed: 0.025 sec.

CK 引擎之MergeTree 引擎

? MergeTree（合并樹(shù)）系列引擎是ClickHouse中最強(qiáng)大的表引擎，是官方主推的存儲(chǔ)引擎，幾乎支持ClickHouse所有的核心功能。

合并樹(shù)系列引擎：具有批量數(shù)據(jù)快速插入和后臺(tái)并發(fā)處理的優(yōu)勢(shì)

MergeTree引擎的表的允許插入主鍵重復(fù)的數(shù)據(jù)，主鍵主要作用是生成主鍵索引來(lái)提升查詢效率，而不是用來(lái)保持記錄主鍵唯一。

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
...
INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]

如果CK表中存儲(chǔ)引擎：MergeTree引擎，創(chuàng)建表時(shí)，如果沒(méi)有設(shè)置主鍵PRIMARY KEY，那么ORDER BY字段設(shè)置的話，就是主鍵字段；如果ORDER BY和PRIMARY KEY都沒(méi)有設(shè)置的話，按照數(shù)據(jù)自然順序排序。

創(chuàng)建使用MergeTree引擎的本地表： test.tbl_test_mergetree_users

CREATE TABLE tbl_test_mergetree_users
(`id` UInt64, `email` String, `username` String, `gender` UInt8, `birthday` DATE, `mobile` FixedString(13), `pwd` String, `regDT` DateTime, `lastLoginDT` DateTime, `lastLoginIP` String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(regDT)
ORDER BY id
SETTINGS index_granularity = 8192 ;

插入數(shù)據(jù)到表中：

insert into tbl_test_mergetree_users(id, email, username, gender, birthday, mobile, pwd, regDT, lastLoginDT, lastLoginIP) values (1,'wcfr817e@yeah.net','督詠',2,'1992-05-31','13306834911','7f930f90eb6604e837db06908cc95149','2008-08-06 11:48:12','2015-05-08 10:51:41','106.83.54.165'),(2,'xuwcbev9y@ask.com','上磊',1,'1983-10-11','15302753472','7f930f90eb6604e837db06908cc95149','2008-08-10 05:37:32','2014-07-28 23:43:04','121.77.119.233'),(3,'mgaqfew@126.com','涂康',1,'1970-11-22','15200570030','96802a851b4a7295fb09122b9aa79c18','2008-08-10 11:37:55','2014-07-22 23:45:47','171.12.206.122'),(4,'b7zthcdg@163.net','金俊振',1,'2002-02-10','15207308903','96802a851b4a7295fb09122b9aa79c18','2008-08-10 14:47:09','2013-12-26 15:55:02','61.235.143.92'),(5,'ezrvy0p@163.net','陰福',1,'1987-09-01','13005861359','96802a851b4a7295fb09122b9aa79c18','2008-08-12 21:58:11','2013-12-26 15:52:33','182.81.200.32'),(6,'juestiua@263.net','岑山裕',1,'1996-02-12','13008315314','96802a851b4a7295fb09122b9aa79c18','2008-08-14 05:48:16','2013-12-26 15:49:12','36.59.3.248'),(7,'oyyrzd@yahoo.com.cn','姚茗詠',2,'1977-02-06','13303203846','96e79218965eb72c92a549dd5a330112','2008-08-15 10:07:31','2013-12-26 15:55:05','106.91.23.177'),(8,'lhrwkwwf@163.com','巫紅影',2,'1996-02-21','15107523748','96802a851b4a7295fb09122b9aa79c18','2008-08-15 14:37:41','2013-12-26 15:55:05','123.234.85.27'),(9,'v2zqak8kh@0355.net','空進(jìn)',1,'1974-01-16','13903178080','96802a851b4a7295fb09122b9aa79c18','2008-08-16 03:24:44','2013-12-26 15:52:42','121.77.192.123'),(10,'mqqqmf@yahoo.com','西香',2,'1980-10-13','13108330898','96802a851b4a7295fb09122b9aa79c18','2008-08-16 04:42:08','2013-12-26 15:55:08','36.57.21.234'),(11,'sf8oubu@yahoo.com.cn','壤晶媛',2,'1976-03-05','15202615557','96802a851b4a7295fb09122b9aa79c18','2008-08-16 06:08:51','2013-12-26 15:55:03','182.83.220.201'),(12,'k6k21ce@qq.com','東平',1,'2005-04-25','13603648382','96802a851b4a7295fb09122b9aa79c18','2008-08-16 06:18:20','2013-12-26 15:55:05','210.34.111.155'),(13,'zguxgg@qq.com','夾影悅',2,'2002-08-23','15300056290','96802a851b4a7295fb09122b9aa79c18','2008-08-16 06:57:45','2013-12-26 15:55:02','61.232.211.180'),(14,'g2jqhbzrf@aol.com','生曉怡',2,'1974-06-22','13507515420','96802a851b4a7295fb09122b9aa79c18','2008-08-16 08:23:43','2013-12-26 15:55:02','182.86.5.162'),(15,'1evn3spn@126.com','咎夢(mèng)',2,'1998-04-14','15204567060','060ed80051e6384b77ddfaa26191778b','2008-08-16 08:29:57','2013-12-26 15:55:02','210.30.171.70'),(16,'tqndz6l@googlemail.com','司韻',2,'1992-08-28','15202706709','96802a851b4a7295fb09122b9aa79c18','2008-08-16 14:34:25','2013-12-26 15:55:03','171.10.115.59'),(17,'3472gs22x@live.com','李言',1,'1997-09-08','15701526600','96802a851b4a7295fb09122b9aa79c18','2008-08-16 15:04:07','2013-12-26 15:52:39','171.14.80.71'),(18,'p385ii@gmail.com','詹蕓',2,'2004-11-05','15001974846','96802a851b4a7295fb09122b9aa79c18','2008-08-16 15:26:06','2013-12-26 15:55:02','182.89.147.245'),(19,'mfbncfu@yahoo.com','蒙芬霞',2,'1990-09-10','15505788156','96802a851b4a7295fb09122b9aa79c18','2008-08-16 15:30:58','2013-12-26 15:55:05','182.91.15.89'),(20,'l24ffbn@ask.com','后冠',1,'2000-09-02','15608241150','96802a851b4a7295fb09122b9aa79c18','2008-08-17 01:15:55','2014-08-29 00:51:12','36.58.7.85'),(21,'m26lggpe@qq.com','宋美月',2,'2003-01-13','15606561425','96802a851b4a7295fb09122b9aa79c18','2008-08-17 01:24:09','2013-12-26 15:52:36','123.235.233.160'),(22,'ndmfm13qf@0355.net','鄔玲',2,'2002-08-11','13207844859','96802a851b4a7295fb09122b9aa79c18','2008-08-17 03:31:11','2013-12-26 15:55:03','36.60.8.4'),(23,'5shmvnd@sina.com','樂(lè)心有',1,'1998-05-01','13201212693','96802a851b4a7295fb09122b9aa79c18','2008-08-17 03:33:41','2013-12-26 15:55:02','123.234.184.210'),(24,'pwa0hu@3721.net','任學(xué)誠(chéng)',1,'1978-03-19','15802040152','7f930f90eb6604e837db06908cc95149','2008-08-17 07:24:01','2013-12-26 15:52:34','210.43.167.14'),(25,'1ybjhul@googlemail.com','巫紈',2,'1995-01-20','15900530105','96802a851b4a7295fb09122b9aa79c18','2008-08-17 07:48:06','2013-12-26 15:55:02','222.55.139.104'),(26,'b31me2i8b@yeah.net','石婭',2,'2000-02-25','13908735198','96802a851b4a7295fb09122b9aa79c18','2008-08-17 08:17:24','2013-12-26 15:52:45','123.235.99.123'),(27,'qgb2w4n7@163.net','柏菁',2,'1975-02-09','15306552661','96802a851b4a7295fb09122b9aa79c18','2008-08-17 08:47:39','2013-12-26 15:55:02','121.77.245.202'),(28,'cfb3ck@sohu.com','鮮夢(mèng)',2,'1974-01-26','13801751668','96802a851b4a7295fb09122b9aa79c18','2008-08-17 08:55:47','2013-12-26 15:55:02','210.26.163.24'),(29,'nfrf6mp@msn.com','鄂珍',2,'1974-04-14','13300247433','96802a851b4a7295fb09122b9aa79c18','2008-08-17 09:02:14','2013-12-26 15:55:08','210.31.214.157'),(30,'o1isumh@126.com',' 法姬',2,'1978-06-16','15607848127','96802a851b4a7295fb09122b9aa79c18','2008-08-17 09:09:59','2013-12-26 15:55:08','222.24.34.19'),(31,'y2wrclkq@msn.com','太以',1,'1998-09-07','13608585923','96802a851b4a7295fb09122b9aa79c18','2008-08-17 11:35:39','2013-12-26 15:52:35','182.89.218.177'),(32,'fv9avnuo@263.net','庚姣欣',2,'1982-09-14','13004625187','96802a851b4a7295fb09122b9aa79c18','2008-08-17 12:50:36','2013-12-26 15:55:02','106.82.225.130'),(33,'o1e96z@yahoo.com','微偉',1,'1981-07-30','13707663880','96802a851b4a7295fb09122b9aa79c18','2008-08-17 15:12:05','2013-12-26 15:49:12','171.13.152.247'),(34,'cm3oz64ja@msn.com','那竹娜',2,'1989-01-09','13607294767','96802a851b4a7295fb09122b9aa79c18','2008-08-17 15:51:08','2013-12-26 15:55:02','171.13.110.67'),(35,'g7impl@msn.com','閭和棟',1,'1994-10-12','13907368366','96802a851b4a7295fb09122b9aa79c18','2008-08-17 16:51:02','2013-12-26 15:55:01','210.28.163.83'),(36,'jz2fjtt@163.com','夏佳悅',2,'2001-03-17','15102554998','7af1b63f0d1f37c35c1274339c12b6a8','1970-01-01 08:00:00','1970-01-01 08:00:00','222.91.138.221'),(37,'klwrtomws@yahoo.com','南義梁',1,'1981-03-19','15105745902','96802a851b4a7295fb09122b9aa79c18','2008-08-18 01:49:17','2013-12-26 15:55:01','36.62.155.17'),(38,'yhzs1nnlk@3721.net','牧元',1,'2001-06-07','13501780163','96802a851b4a7295fb09122b9aa79c18','2008-08-18 04:24:52','2013-12-26 15:55:01','171.15.184.130'),(39,'hem76ot33@gmail.com','凌偉文',1,'1988-03-04','13201417535','96802a851b4a7295fb09122b9aa79c18','2008-08-18 05:34:52','2013-12-26 15:55:14','61.237.105.3'),(40,'ndp40j@sohu.com','弘枝',2,'2000-09-05','13001236425','96802a851b4a7295fb09122b9aa79c18','2008-08-18 06:23:48','2013-12-26 15:55:01','106.82.172.45'),(41,'zeyacpr@gmail.com','臺(tái)凡',2,'1998-05-26','15102913418','96802a851b4a7295fb09122b9aa79c18','2008-08-18 06:41:24','2013-12-26 15:55:07','123.233.69.218'),(42,'0ts0wiz@aol.com','任曉紅',2,'1984-05-02','13502366778','96802a851b4a7295fb09122b9aa79c18','2008-08-18 06:55:16','2013-12-26 15:55:01','210.26.44.18'),(43,'zi7dhzo@googlemail.com','蔡藝艷',2,'1990-08-07','15603954810','96802a851b4a7295fb09122b9aa79c18','2008-08-18 06:57:30','2013-12-26 15:55:01','171.12.171.179'),(44,'b0yfzilu@hotmail.com','郎誠(chéng)',1,'1994-05-18','13602127171','96802a851b4a7295fb09122b9aa79c18','2008-08-18 07:02:04','2013-12-26 15:55:02','171.8.22.92'),(45,'er9az5e9s@163.com','臺(tái)翰',1,'1994-06-22','15900953220','96802a851b4a7295fb09122b9aa79c18','2008-08-18 07:05:08','2013-12-26 15:55:14','222.31.141.156'),(46,'e34jy2@yeah.net','彭筠',2,'1983-08-12','15106915420','96802a851b4a7295fb09122b9aa79c18','2008-08-18 07:09:37','2013-12-26 15:52:34','36.60.51.67'),(47,'1u0zc56h@163.net','包華婉',2,'1998-10-03','13102518450','96802a851b4a7295fb09122b9aa79c18','2008-08-18 07:47:24','2013-12-26 15:55:02','121.76.76.105'),(48,'cs8kyk3@ask.com','淳盛',1,'2002-06-19','13203151569','96802a851b4a7295fb09122b9aa79c18','2008-08-18 08:01:58','2013-12-26 15:55:02','36.60.76.111'),(49,'ibcgi5ll@yahoo.com','車(chē)珍楓',2,'1975-07-27','15605361319','96802a851b4a7295fb09122b9aa79c18','2008-08-18 08:12:45','2013-12-26 15:55:01','106.83.110.76'),(50,'gzxcx6vz@live.com','應(yīng)冰紅',2,'2004-04-19','15104154370','96802a851b4a7295fb09122b9aa79c18','2008-08-18 09:00:09','2013-12-26 15:55:01','182.88.181.216');

進(jìn)入表存儲(chǔ)數(shù)據(jù)的目錄，查看存儲(chǔ)結(jié)構(gòu)：
cd /var/lib/clickhouse/data/db_ck/tbl_test_mergetree_users

進(jìn)入其中日期目錄，查看文件

文件：columns.txt，列信息，元數(shù)據(jù)

[root@node2 20080817_7_7_0]# more columns.txt
columns format version: 1
10 columns:
`id` UInt64
`email` String
`username` String
`gender` UInt8
`birthday` Date
`mobile` FixedString(13)
`pwd` String
`regDT` DateTime
`lastLoginDT` DateTime
`lastLoginIP` String

文件：count.txt，計(jì)數(shù)文件

[root@node2 20080817_7_7_0]# more count.txt
16

如果在CllickHouse集群上創(chuàng)建表時(shí)，語(yǔ)句如下，需要指定集群ID。

集群中查看創(chuàng)建集群表，如下截圖所示：

總結(jié)

以上是生活随笔為你收集整理的ClickHouse快速入门的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

上一篇： Xbox One 与PS4的终极PK
下一篇： C++to_string应用举例