ClickHouse快速入门

ClickHouse快速入门

1614160921553

学习目标

1616638096156

1）、ClickHouse数据库概述、应用场景OLAP分析技术框架比较
2）、ClickHouse 快速入门安装部署、服务启动、基本client使用
3）、ClickHouse 快读体验航班飞行数据，基本分组、聚合、排序查询，快、快、快
4）、ClickHouse SQL语法SQL查询命令、INSERT语句、ALTER语句（间接实现更新和删除）
5）、ClickHouse 数据类型常见使用
6）、ClickHouse 表存储引擎日志引擎MySQL数据库合并树引擎MergeTree Engine

实时OLAP分析之技术选型

1616638447054

本项目中，需要分析实时性在毫秒级别，最多不超过秒级需求，选择OLAP分析引擎，选择ClickHouse

​ 目前市面上主流的开源OLAP引擎包含不限于：Hive、Presto、Kylin、Impala、SparkSQL、Druid、Clickhouse、Greeplum等，可以说目前没有一个引擎能在数据量，灵活程度和性能上做到完美，用户需要根据自己的需求进行选型。

1）、Kylin 麒麟

1616638609266

  • 2）、Druid

1616638814637

上面给出了常用的一些OLAP引擎，各自有各自的特点，将其分组：

1616639112886

没有最好的解决方案：

1616639157350

实时OLAP分析之设计方案

首先，ClickHouse数据库，到底有多火？？

ClickHouse是近年来备受关注的开源列式数据库，主要用于数据分析（OLAP）领域。目前国内社区火热，各个大厂纷纷跟进大规模使用：

1616640368633

使用ClickHouse 实时OLAP分析数据库，存储业务数据，对外提供数据查看功能：

  • 1）、实时大屏展示
  • 2）、数据服务接口

实时增量将业务数据存储至ClickHouse数据库表中，以便OLAP分析查询，比如为实时大屏提供数据和提供数据接口服务等。

1614305384716

当将业务数据存储到CK以后，两个主要应用：实时大屏展示和数据服务接口。

物流项目中，数据服务接口使用SpringCloud开发的，目前使用多框架和流行框架

1614305650291

整个项目，提供实时大屏系统，展示每日物流业务数据实时统计，每隔10秒中，刷新页面，调用服务接口，从ClickHouse数据库查询数据。

ClickHouse 入门之概述及案例

ClickHouse是一个面向列的数据库管理系统（DBMS），用于在线分析处理查询（OLAP）。

  • 官网：https://clickhouse.tech/

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

  • logo 图标：表示数据为列式存储，红色部分表示要查询数据

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CQDqJZtx-1618997601312)(https://gitee.com/TuNan86/mapdeport2/raw/master/img/20210420163811.svg)]

1614306056944

ClickHouse目前在国内发展形势相当好，各个大厂基本上都在使用，存储海量数据，实时OLAP分析。

1616641166533

简单的说ClickHouse作为分析型数据库，三大特点：一是跑分快， 二是功能多 ，三是文艺范。

2）、ClickHouse 发展历程

从MyISAM引擎存在问题，到Metrage引擎诞生，一直到ClickHouse开源，历经6年时间。

1616641469796

  • 3）、ClickHouse 特性

1616641590970

  • 4）、ClickHouse 优势

1616641618187

ClickHouse存储数据是面向列进行存储，类似ORC和Parquet及Kudu数据库存储数据方式。

1616641651106

  • 5）、ClickHouse 基准测试

​ ClickHouse提供了一个与其他列式数据库的基准测试，这个基准测试大多数是在单台服务器进行测试，该服务器的配置是：

1616641834679

  • 6）、ClickHouse 应用场景

数据量大、实时OLAP查询需求，就可以考虑使用CK。

  • 1、绝大多数请求都是用于读访问的，数据只是添加到数据库，没有必要修改
  • 2、读取数据时，会从数据库中提取出大量的行，但只用到一小部分列
  • 3、表很“宽”，即表中包含大量的列
  • 4、每次查询中只会查询一个大表。除了一个大表，其余都是小表
  • 7）、ClickHouse 使用案例

目前国内社区火热，各个大厂纷纷跟进大规模使用。

目前来说，如果使用ClickHouse存储业务数据，进行实时OALP分析，往往与Superset集成使用。

1616642015840

自己学习：Superset与ClickHouse整合使用。

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

ClickHouse 入门之安装部署

目标：ClickHouse单机版安装部署及服务启动

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

1616642319534

在线安装ClickHouse数据库时，可能由于网络原因，很慢很慢，实际企业中，推荐离线安装：

  • 1）、第一种：tar包安装
    • https://repo.clickhouse.tech/tgz/stable/
  • 2）、第二种：RPM包安装
    • https://repo.yandex.ru/clickhouse/rpm/stable/x86_64/

本次项目，采用在线安装ClickHouse数据库，具体步骤如下所示：

如何进行在线安装ClickHouse数据库：

  • 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的服务端和客户端

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

采用YUM安装方式，ClickHouser安装完成以后目录存说明：

  • 默认的配置文件路径是：/etc/clickhouse-server/
  • 默认的日志文件路径是：/var/log/clickhouse-server/

1616642570135

​ clickhouse的server配置，在/etc/clickhouse-server/config.xml及user.xml，前者是clickhouse的系统配置，包括日志，服务部署ip，zk等配置。后者是当前节点服务的配置，包括用户名密码，内存大小限制等。

1）、服务配置：config.xml

1616642651244

ClickHouse数据库提供2个端口号，便于访问：

  • HTTP 协议端口号：8123，使用JDBC Client 访问CK时，使用此端口号
  • TCP 协议端口号：默认值为9000，click-client命令行使用此端口号，但是，需要修改端口号，改为9999，由于ClouderaManagerServer服务占用9000端口号

1616642873713

2）、用户配置：users.xml

默认情况下，ClickHouse数据库用户名为default，没有设置密码，但是实际项目中，都会配置用户和密码，本物流项目：root/123456

1616642947353

启动ClickHouse数据库，要么前端启动，要么后台启动（选择此种方式）：

  • 前端服务启动：clickhouse-server start
  • 后台服务启动：systemctl start clickhouse-server

1616643079472

[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提供客户端命令连接服务：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存储引擎集群部署，稍微不一样。采用多主（无中心）架构，集群中的每个节点角色对等，客户端访问任意一个节点都能得到相同的效果。

  • 1）、将数据划分分片Shard
  • 2）、每个Shard分片数据放在一台机器上
  • 3）、支持数据副本Replication，其副本概念与Elasticsearch类似
    • ClickHouse的数据副本一般通过ReplicatedMergeTree复制表系列引擎实现；
    • 副本之间借助ZooKeeper实现数据的一致性。
    • 副本与分片不能在同一台机器上

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

6838ab70-c7de-40d9-a8da-b62b4ac171f5.png

​ 上图中展示如何将数据划分为Shard分片，并且设置1个副本，分布式架构策略，在搭建CK集群时，指定分数、副本数，和运行的主机名称和端口号等等信息。

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

ClickHouse 入门之命令行使用

目标：ClickHouse 数据库提供客户端命令【clickhouse-client】基本使用。

1、MySQL数据库MySQL Server 服务mysql 命令行客户端2、ClickHouse数据库ClickHouse Server 服务clickhouse-client 命令行客户端

ClickHouse安装包中提供了clickhouse-client工具，这个客户端在运行shell环境中，使用TCP方式连接clickhouse-server服务。

1616645203812

1616645237953

连接ClickHouse数据库服务时，提供两种方式：

  • 1）、方式一、批量执行方式，使用–query或-q指定查询语句

     clickhouse-client --host node2.itcast.cn --port 9999 --user root --password 123456 -q "select count(1) as cnt from db_test.ontime"

  • 2）、方式二：交互式查询，通过加上-m参数，表示编写SQL语句支持多行模式

    clickhouse-client -m --host node2.itcast.cn --port 9999 --user root --password 123456

ClickHouse 入门之可视化界面

ClickHouse数据库除了可以使用clickhouse-client客户端连接以外，提供一些其他GUI 工具连接。

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

1）、DBeaver 具有ClickHouse支持的通用桌面数据库客户端。

DBeaver客户端工具，连接CK Server服务时，采用JDBC 方式，所以需要驱动包

1614310114843

连接以后，可以编写SQL语句，查询CK数据库表数据分析处理。

1616645648405

2）、IDEA 支持连接ClickHouse

DataGrip 是JetBrains的数据库IDE，专门支持ClickHouse。 它还嵌入到其他基于IntelliJ的工具中：PyCharm，IntelliJ IDEA，GoLand，PhpStorm等。

1614310208028

配置完成以后，编写SQL查询数据

1616645817338

ClickHouse 入门之快速体验【导入数据】

目标：在CK数据库中，创建表（很多列），导入数据（海量，最好上千万），进行基本查询，性能状况。

  • 使用官方提供案例：航班飞行数据案例

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

  • 1）、编写下载航班数据脚本
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

当数据下载完成以后，在ClickHouse数据库中创建表。

  • 2）、创建ontime表，可以在default数据库中创建。
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;

表创建完成完成以后，将下载数据文件导入到表中。

  • 3）、导入数据到表中，导入数据脚本，与数据文件zip包在同一个目录下
 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

1616646208666

4）、查看导入数据量

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 入门之快速体验【简单查询】

​ 前面已经将航空飞行数据导入至ClickHouse表：ontime，编写SQL语句进行查询

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

  • 查询一：查询总条数

    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年每天的航班数

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.)
  • 查询三：查询从2017年到2020年每周延误超过10分钟的航班数
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年每个机场延误超过10分钟以上的次数
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年各航空公司航班次数
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年各航空公司延误超过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年期间各航空公司延误超过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.)
  • 查询八：每年航班延误超过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.)
  • 查询十：每一年的航班总次数
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 语法之常用 SQL 命令】

ClickHouse数据库中创建表、查询SQL语句，基本上与Mysql数据库类似，接下来，具体常用SQL命令：

1616655125264

创建表的基本语法：

  • 1）、表的类型分为两种：临时表temporary和持久化表

  • 2）、创建表时，建议写上数据库名称：dbName.tableName

  • 3）、如果使用CK集群，创建表时，在表的名称后面加上：ON CLUSTER clusterId

  • 4）、每个数据表创建时，需要指定存储引擎，非常关键属性，决定业务查询性能

engine = EngineName(parameters);

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

​ 前面演示，从航空飞行数据中查询（过滤where、分组group by，聚合count，排序sortby）那么快原因：表的底层存储引擎，比如MergeTree引擎，比较常用，决定底层如何存储数据，如何查询数据等。

CK SQL 语法之SELECT、INSERT和ALTER

前面讲解常用SQL命令，主要就是DDL语句（创建表、删除表），此外ClickHouse数据库表支持DML语句：

  • 1）、SELECT 语句，查询数据，基本上与MySQL 查询语句功能类似
  • 2）、INSERT语句，插入数据到表中
  • 3）、ALTER语句，对表进行修改，比如添加列或删除列；此外，实现对表的数据进行更新和删除
    • CK中更新表的数据，采用ALTER语句实现Update功能
    • CK中删除表的数据，采用ALTER语句实现Delete功能
  • 1）、SELECT 语法，查询表的数据
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语法中：ARRAY JOIN ...，可以帮助查询进行与数组和nested数据类型的连接

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 插入数据语法

ClickHouse中完整insert的主要用于向系统中添加数据，有如下几种方式：

  • 1）、语法一：通用插入数据

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

  • 2）、语法二：

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

  • 3）、语法三：子查询插入数据

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

系统不支持的其他用于修改数据的查询：UPDATE、DELETE、REPLACE、MERGE、UPSERT和 INSERT UPDATE。

  • 3）、ALTER 语法

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

1616656311361

-- ALTER 语法
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 ;
-- 查看表结构
desc mt_table ;
-- 查看数据
select * from mt_table ;-- 修改列
alter table mt_table modify column age UInt16 ;-- 删除列
alter table mt_table drop column age ;

CK SQL 语法之UPDATE和DELETE

​ 从使用场景来说，Clickhouse是个分析型数据库。这种场景下，数据一般是不变的，因此Clickhouse对update、delete的支持是比较弱的，实际上并不支持标准的update、delete操作。

1616656636969

-- upate和delete，基于alter语法实现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 入门之SQL 函数

ClickHouse数据库，自身再带函数：https://clickhouse.tech/docs/zh/sql-reference/functions/

其中很多函数，与MySQL数据库和Hive中函数基本一致，可以放心使用，特殊功能，需要自己查看文档

  • 1）、类型检测函数：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）、 数学函数，常见加减乘除

1616657105748

1616657118628

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）、时间函数
    • 当前时间：now()
    • 时间转换：toYYYYMM(curDT) toYYYYMMDD(curDT) toYYYYMMDDhhmmss(curDT);
    • 将字符串转换为日期时间：toDateTime
    • 将字符串转换为日期：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 数据类型之数值类型

ClickHouse与常用的关系型数据库MySQL或Oracle的数据类型类似，提供了丰富的数据类型支持。

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

  • 1）、ClickHouse支持Int和Uint两种固定长度的整型，Int类型是符号整型，Uint类型是无符号整型。

1616658567591

  • 2）、ClickHouse支持Float32和Float64两种浮点类型

1616658599634

  • 3）、ClickHouse支持Decimal类型的有符号定点数，可在加、减和乘法运算过程中保持精度。

1616658654439

​ 在Java语言中，如果对double类型数字进行加法操作，必须类型转换为BigDecimal类型，进行add加法操作，否则丢失精度。

img

CK 数据类型之字符串及NULL类型

ClickHouse中的String类型没有编码的概念。字符串可以是任意的字节集，按它们原本的方式

进行存储和输出。若需存储文本，建议使用UTF-8编码。

1616658872486

  • UUID 数据类型：

​ ClickHouse支持UUID类型（通用唯一标识符），该类型是一个16字节的数字，用于标识记录。ClickHouse内置generateUUIDv4函数来生成UUID值，UUID数据类型仅支持String数据类型也支持的函数（例如，min，max和count）。

  • ClickHouse中没有定义布尔类型，可以使用UInt8类型，取值限制为0或1。

0表示false，1表示true

  • ClickHouse支持Enum8和Enum16两种枚举类型。Enum保存的是'string'=integer的对应关系。

1616659051128

案例演示：

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类型，该类型允许用NULL来表示缺失值。Nullable字段不能作为索引
    列使用
-- Nullable 类型
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 数据类型之日期时间类型

日期类型：ClickHouse支持Date类型，这个日期类型用两个字节存储，表示从 1970-01-01 (无符号) 到当
前的日期值。

日期时间类型：ClickHouse支持DataTime类型，这个时间戳类型用四个字节（无符号的）存储Unix时间戳。
允许存储与日期类型相同范围内的值，最小值为0000-00-00 00:00:00。

时间间隔interval：Interval是ClickHouse提供的一种特殊的数据类型，此数据类型用来对Date和Datetime进行运算，不能使用Interval类型声明表中的字段。

  • Interval支持的时间类型有SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER和YEAR。

1616659580324

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中，对于某些类型的列，在没有显示插入值时，会自动填充默认值处理。

1616659777688

CK 数据类型之数组和元组类型

  • 1）、ClickHouse支持Array(T)类型，T可以是任意类型

1616659869181

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类型支持，Tuple(T1,T2…)中每个元素都可以是单独的类型。

  • 元组中不可以嵌套元组

1616660017405

CK 数据类型之其他类型

  • 1)、CLickHouse支持IPv4和Ipv6两种Domain类型

1616660256400

Ipv4类型是与UInt32类型保持二进制兼容的Domain类型，其用于存储IPv4地址的值；IPv6是与FixedString(16)类型保持二进制兼容的Domain类型，其用于存储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）、嵌套数据结构

  • 可以简单地把嵌套数据结构当做是所有列都是相同长度的多列数组。
  • 嵌套数据结构的列仅支持一级嵌套
  • 嵌套列在insert时，需要把嵌套列的每一个字段以[要插入的值]格式进行数据插入

1616660625808

insert into tbl_test_nested values(1,'2019-12-25',['zhangsan'],[23],[13800138000],1);
  • 3）、AggregateFunction 类型
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));

1616661052219

适用场景：（军训分小队以小队为单位比赛，事件跨度最短的才能赢：多个裁判开始计时出发一个记录一下时间，以班级+小队编号为id）最后计算出第一个出发的时间-最后一名到达的时间为该小组的时间跨度。

ClickHouse 引擎之引擎概述

ClickHouse提供了多种不同的表引擎，表引擎可以简单理解为不同类型的表。表引擎（即表的类型）决定了：

1616662005733

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

  • 1）、MergeTree引擎（合并树及系列引擎）
    • 适用于高负载任务的最通用和功能最强大的表引擎。
  • 2）、日志Log引擎
    • 最小功能的轻量级引擎
  • 3）、集成引擎，使用其他存储引擎，存储表的数据
    • 用于与其他的数据存储与处理系统集成的引擎。
  • 4）、用于其他特定功能的引擎

ClickHouse 引擎之TinyLog 引擎

​ TinyLog 引擎， 最简单的表引擎，用于将数据存储在磁盘上。每列都存储在单独的压缩文件中，写入时，数据将附加到文件末尾。 == 该引擎没有并发控制 == 。

1616662365509

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.

查看上述创建表user，低层如何实现存储

1616662585910

ClickHouse 引擎之MySQL 引擎

MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中，并允许您对表进行INSERT和SELECT查询，以方便您在ClickHouse与MySQL之间进行数据交换。

1614330767215

此种方式，CK没有存储表的数据，MySQL才是真正存储数据地方。

MySQL 数据库引擎会将对其的查询转换为MySQL语法并发送到MySQL服务器中，因此可以执行诸如SHOW TABLES或SHOW CREATE TABLE之类的操作。但无法对其执行以下操作： RENAME、CREATE TABLE、 ALTER。

1616664029323

案例演示：

  • 1)、第一步、在MySQL数据库创建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中创建数据库，关联到MySQL数据库
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.

注意：每次启动clickhouse时先要将管理的mysql服务起来，否则，clickhouse起不来。

  • 3）、第三步、在CK中操作，查询数据
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（合并树） 系列引擎是ClickHouse中最强大的表引擎， 是官方主推的存储引擎，几乎支持ClickHouse所有的核心功能。

1616662758483

合并树系列引擎：具有批量数据快速插入和后台并发处理的优势

MergeTree引擎的表的允许插入主键重复的数据， 主键主要作用是生成主键索引来提升查询效率，而不是用来保持记录主键唯一。

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, ...]

1616662962840

如果CK表中存储引擎：MergeTree引擎，创建表时，如果没有设置主键PRIMARY KEY，那么ORDER BY字段设置的话，就是主键字段；如果ORDER BY和PRIMARY KEY都没有设置的话，按照数据自然顺序排序。

创建使用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 ;

插入数据到表中：

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','空进',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','咎梦',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','乐心有',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','任学诚',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','鲜梦',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','台凡',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','郎诚',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','台翰',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','车珍枫',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','应冰红',2,'2004-04-19','15104154370','96802a851b4a7295fb09122b9aa79c18','2008-08-18 09:00:09','2013-12-26 15:55:01','182.88.181.216');

进入表存储数据的目录，查看存储结构：

cd /var/lib/clickhouse/data/db_ck/tbl_test_mergetree_users

1616663330982

进入其中日期目录，查看文件

1616663459477

  • 文件：columns.txt，列信息，元数据
[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，计数文件
[root@node2 20080817_7_7_0]# more count.txt
16

如果在CllickHouse集群上创建表时，语句如下，需要指定集群ID。

1616663728705

集群中查看创建集群表，如下截图所示：

1616663753247

Published by

风君子

独自遨游何稽首 揭天掀地慰生平

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注