本文使用「署名 4.0 国际 (CC BY 4.0)」许可协议，欢迎转载、或重新修改使用，但需要注明来源。 [署名 4.0 国际 (CC BY 4.0)](https://creativecommons.org/licenses/by/4.0/deed.zh) 本文作者: 苏洋 创建时间: 2021年10月16日 统计字数: 34497字 阅读时间: 69分钟阅读 本文链接: https://soulteary.com/2021/10/16/get-started-quickly-with-clickhouse.html ----- # 快速上手 ClickHouse 本篇来自数月前对外分享的文稿整理，并进行了一些扩展。 希望通过简单的方式，来介绍新手如何一步一步上手 ClickHouse，如果你有潜在的数据分析的需求，但是不知道从哪里开始，那么希望本文能够帮助到你。 ## 写在前面 关于 ClickHouse 在追求性能的场景下的溢美之词，我觉得没有必要再重复了。以过往经验来看，你可以使用极其低的成本来完成以往 RDBMS（比如MySQL）做不到的准实时级别的数据分析，也可以用它来做远程多个数据库实例的数据迁移或者归档存储。 感谢两年前一位好朋友对我进行的技术选型推荐，使用 ClickHouse 可以简化非常多的不必要的基础设施搭建和维护。我们曾搭建过一台比较奢华的机器（256核心512GB内存）来进行准实时的数据分析（花费万分之几秒从海量数据中查结果），以及支持每秒落地几十万条以上数据，而丝毫不影响服务器查询性能；也曾实践过从两千块的 NUC 上跑边缘计算任务，相对快速的拿到需要分析的结果（花费千分之一到百分之一秒），以及在16核心64GB内存的普通笔记本上，跑超过十亿数据集的复杂计算的尝试（分钟级）。 ![之前使用过的“豪华”配置](https://attachment.soulteary.com/2021/10/16/baremetal.jpg) 所以，如果你有以下需求，ClickHouse 可能也会非常适合你： - 快速分析一些离线数据，做数据计算、聚合、筛选。 - 有大量读取需求，并且针对原始数据修改的需求非常少，如果存在这类需求，可以接受“追加数据”配合“版本过滤”的方式处理。 - 数据字段比较丰富，数据存在非常多“列”。 - 业务并发需求不高，查询者（消费者）只有几个或者一两百个以下。 先来聊聊硬件选择。 ### 硬件选择策略 会考虑选择 ClickHouse 的同学，一般应该是遇到了当前业务，到了需要或者不得不“考虑效率”的时刻。 一般情况，很少有需要直接把 ClickHouse 返回数据作为同步结果直接返回给调用方的场景，勤俭节约的程序员们一般都会使用异步模式，所以在极少并发的情况下，我们对于 ClickHouse 的硬件要求也就越来越低了： **亿级别以下的数据，最低只要 4核心16GB 的虚拟机也能轻松搞定；而亿级别到百亿级别的数据，只要你能搞定32～64G内存，计算出来的时间也只几乎只和你设备的核心数数量、CPU缓存大小是多少有关而已** 。 所以，在考虑使用 ClickHouse 的时候，如果你是用来做一个快速或者相对快速的“离线”数据分析，那么**优先需要考虑的是你的数据量有多大，以及需要满足快速计算的内存门槛下限是否足够，接着才是考虑你需要多快的拿到计算结果，尽量在成本预算之内，优先选择拥有更多的核心数的 CPU、以及更大的 CPU 缓存** 。至于 Cluster 模式，除非你需要提供实时接口，对于服务可用性有极高依赖和要求，有特别大的数据写入压力，不然默认情况是不需要配置的。当然，如果你有需求配置 Cluster，不推荐使用默认的分布式模式，因为数据并非完整镜像，而是均匀分布在每一个节点，如果某一个节点跪掉，你将“实时”[损失 N 分之 1 的数据](https://clickhouse.com/docs/en/sql-reference/distributed-ddl/)，导致最终计算结果不能说不准确，只能说压根可能是错的。官网为此推出了一个[“Replicated”的数据库引擎](https://clickhouse.com/docs/en/engines/database-engines/replicated/)，这个数据库引擎基于 Atomic 引擎，借助 ZooKeeper 进行完整的数据复制，虽然目前还处于实验阶段，但是总比“丢数据”强吧。 除此之外，还有一个因素会极大的影响 ClickHouse 帮助我们拿到计算结果的时间，就是**存储介质**，这里推荐使用 SSD 作为存储介质，如果你是用于小样本分析，甚至可以使用 TB 规格、便宜的民用存储。如果追求极致成本，甚至可以参考我之前的内容[《廉价的家用工作站方案：前篇》](https://soulteary.com/2021/07/02/cheap-home-workstation-solution-part-one.html)、[《NUC 折腾笔记 - 储存能力测试》](https://soulteary.com/2021/02/02/nuc-notes-storage-ability-test.html)，如果你是进行高频次、海量数据的计算，有比较大的存储量下限要求和可预期的大容量数据增长，考虑到成本和更高的数据存储可靠性，Raid 50 模式的机械磁盘会更适合你。 当然，**如果你目前啥都没有**，只是用于学习，本地起一个 Docker 容器，也能开始学习之旅，以及百万到千万级别的数据计算和分析。 ### 软件环境选择 我目前所有的机器都运行在 Ubuntu + 容器环境，为什么这么选择呢，因为“[Ubuntu 是容器世界里的一等公民](https://cn.ubuntu.com/containers)”，本文考虑到快速上手，也同样选择使用套环境。 当然，如果你选择裸机直接安装 ClickHouse，使用更稳定的 Debian 也是个不错的选择，至于 CentOS ，时至今日，真的是没有推荐的理由和必要了（企业付费购买 RHEL 是另外一个话题）。 在容器环境内跑 ClickHouse 会损失比较多的“转换”性能，在存储和网络转发上都会存在一定的体现，所以实际生产环境能够裸机安装的，请脱离容器使用。 如果你已经安装好了 Docker环境，那么我们可以继续下一个章节啦。如果你还不熟悉如何安装 Docker，可以参考本站知识地图中的[关于容器安装的内容](https://soulteary.com/subject/tech/#docker-%E5%92%8C-k8s-%E7%9B%B8%E5%85%B3%E5%B8%B8%E8%A7%81%E9%97%AE%E9%A2%98)，自行了解学习。 ## 前置准备：测试使用的数据集 为了熟悉和了解基础语法和进行 ClickHouse 高性能体验，我们可以先使用官方提供的 Yandex.Metrica Data 来进行试验。（更多的性能测试，可以从官方仓库的 [测试数据集](https://github.com/ClickHouse/ClickHouse/tree/master/tests) 中了解） 1. `https://datasets.clickhouse.tech/hits/partitions/hits_v1.tar` 2. `https://datasets.clickhouse.tech/visits/partitions/visits_v1.tar` 此外，为了演示如何在不纠结数据类型转换的情况下，快速完成数据导入，我们还需要使用一个传统类型的数据库的数据集进行操作，这里选择网友开源项目中使用的“人人影视”数据库（MySQL） `https://yyets.dmesg.app/database `。 数据下载完毕之后，我们需要先对数据进行解压缩。 ```bash mkdir data tar xvf hits_v1.tar -C data tar xvf visits_v1.tar -C data ``` 通过 `du` 命令可以看到使用的数据实际使用了 1.7GB空间，顺便提一下，这些数据如果存储在 MySQL 中，存储空间可能会膨胀 3～5倍以上。 ```bash du -hs data 1.7G data ``` 数据解压完毕，就可以开始准备对 ClickHouse 的容器运行配置了。 ### 前置准备：准备 ClickHouse 运行配置 ```yaml version: "2" services: server: image: yandex/clickhouse-server:21.9.4.35 container_name: clickhouse expose: - 9000 - 8123 - 9009 ulimits: nproc: 65535 nofile: soft: 262144 hard: 262144 environment: - TZ=Asia/Shanghai # - CLICKHOUSE_USER=root # - CLICKHOUSE_PASSWORD=xmnzdwH5 volumes: - ./data:/var/lib/clickhouse # 按需使用 # - ./config.xml:/etc/clickhouse-server/config.xml # - ./users.xml:/etc/clickhouse-server/users.xml ``` 将上面的配置保存为 `docker-compose.yml`，并使用 `docker-compose up -d` 启动 ClickHouse，以备稍后使用。 额外说一下，ClickHouse 的版本更新很快，建议升级的时候先做一些小样本测试，测试常用场景是否正常，再进行版本更新替换。 ## ClickHouse 初体验 ClickHouse 使用的 SQL 语法相比较 MySQL 等数据库会宽松许多，类比的话，就像是之前写 Java 的选手一下子步入了 Python 和 JavaScript 的世界。 因为使用容器启动 ClickHouse，所以我们可以通过 `docker exec` 命令进入 ClickHouse 的交互式终端。 ```bash docker exec -it clickhouse clickhouse-client ``` 进入终端后，先来看看有哪些“数据库”和数据表： ```bash # 查看数据库 cc1b062138da :) show databases SHOW DATABASES Query id: efaa1c51-e112-43d6-b803-1e6dd86ad43b ┌─name─────┐ │ datasets │ │ default │ │ system │ └──────────┘ 3 rows in set. Elapsed: 0.003 sec. # 切换数据库 cc1b062138da :) use datasets USE datasets Query id: b10ff8f3-0743-42f4-9ee1-663b9a2c4955 Ok. 0 rows in set. Elapsed: 0.002 sec. # 查看数据表 cc1b062138da :) show tables SHOW TABLES Query id: c6eb8203-6ea2-4576-9bb7-74ad4e1c7de9 ┌─name──────┐ │ hits_v1 │ │ visits_v1 │ └───────────┘ 2 rows in set. Elapsed: 0.005 sec. ``` 上面的结果中的 `datasets` 就是我们导入的数据集。ClickHouse 对于数据存放比较“佛系”，如果你查看本地目录可以看到上面的数据和 `data/datasets` 目录保持一致，实际操作使用的时候，只要把 data 目录打个压缩包就能完成数据备份了，是不是很简单。 ```bash tree -L 3 data/data/datasets data/data/datasets ├── hits_v1 │   ├── 201403_10_18_2 │   │   ├── AdvEngineID.bin │   │   ├── AdvEngineID.mrk │   │   ├── Age.bin │   │   ├── Age.mrk │   │   ├── UserID.bin │   │   ├── UserID.mrk ... │   │   ├── WatchID.bin │   │   ├── WatchID.mrk │   │   ├── YCLID.bin │   │   ├── YCLID.mrk │   │   ├── checksums.txt │   │   ├── columns.txt │   │   ├── count.txt │   │   ├── minmax_EventDate.idx │   │   ├── partition.dat │   │   └── primary.idx │   ├── detached │   └── format_version.txt └── visits_v1 ├── 20140317_20140323_3_4_1 │   ├── AdvEngineID.bin │   ├── AdvEngineID.mrk │   ├── Age.bin │   ├── Age.mrk │   ├── Attendance.bin │   ├── Attendance.mrk ... │   ├── ClickBannerID.bin │   ├── ClickBannerID.mrk │   ├── ClickClientIP.bin │   ├── ClickClientIP.mrk │   ├── YCLID.bin │   ├── YCLID.mrk │   ├── checksums.txt │   ├── columns.txt │   └── primary.idx ├── detached └── format_version.txt 6 directories, 675 files ``` 为了后续敲的命令能简单些，我们针对数据表先进行一个重命名操作。 ```bash # 分别去掉两张表的版本后缀 cc1b062138da :) rename table hits_v1 to hits RENAME TABLE hits_v1 TO hits Query id: dba1405a-1836-4d5a-af23-3ce0f5b31d41 Ok. 0 rows in set. Elapsed: 0.014 sec. cc1b062138da :) rename table visits_v1 to visits RENAME TABLE visits_v1 TO visits Query id: 9ffc039c-86c3-42a9-91a6-3ed165254e0b Ok. 0 rows in set. Elapsed: 0.012 sec. # 再次查看数据表 cc1b062138da :) show tables SHOW TABLES Query id: da91fb7c-5224-4c7f-9a6c-ce4cf37f9fa8 ┌─name───┐ │ hits │ │ visits │ └────────┘ 2 rows in set. Elapsed: 0.004 sec. ``` 将数据表重命名之后，接下来，来看看这两张表里到底有多少数据。 ```sql SELECT hits, visits FROM ( SELECT count() AS hits FROM hits ) AS table_hits , ( SELECT count() AS visits FROM visits ) AS table_visits ``` 可以看到两张表数据量都不大，百万到千万级别。 ```sql ┌────hits─┬──visits─┐ │ 8873898 │ 1676861 │ └─────────┴─────────┘ 1 rows in set. Elapsed: 0.005 sec. ``` 接着我们来查看一下两张表的表结构，可以看到两张表，分别有133个、181个列，是“一般意义上的”**宽表**，非常适合进行分析使用。 ```sql desc hits cc1b062138da :) desc hits :-] :-] DESCRIBE TABLE hits Query id: b8d8b650-2395-4207-b2ce-4200dc9c0fce ┌─name───────────────────────┬─type────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐ │ 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.Key1 │ Array(String) │ │ │ │ │ │ │ ParsedParams.Key2 │ Array(String) │ │ │ │ │ │ │ ParsedParams.Key3 │ Array(String) │ │ │ │ │ │ │ ParsedParams.Key4 │ Array(String) │ │ │ │ │ │ │ ParsedParams.Key5 │ Array(String) │ │ │ │ │ │ │ ParsedParams.ValueDouble │ Array(Float64) │ │ │ │ │ │ │ IslandID │ FixedString(16) │ │ │ │ │ │ │ RequestNum │ UInt32 │ │ │ │ │ │ │ RequestTry │ UInt8 │ │ │ │ │ │ └────────────────────────────┴─────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘ 133 rows in set. Elapsed: 0.003 sec. cc1b062138da :) ``` 再来看看另外一张表： ```sql cc1b062138da :) desc visits DESCRIBE TABLE visits Query id: 821e1692-b571-42ad-a38f-88bd120e478d ┌─name───────────────────────────────┬─type────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐ │ 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.ID │ Array(UInt32) │ │ │ │ │ │ │ Goals.Serial │ Array(UInt32) │ │ │ │ │ │ │ Goals.EventTime │ Array(DateTime) │ │ │ │ │ │ │ Goals.Price │ Array(Int64) │ │ │ │ │ │ │ Goals.OrderID │ Array(String) │ │ │ │ │ │ │ Goals.CurrencyID │ Array(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.ID │ Array(Int8) │ │ │ │ │ │ │ TraficSource.SearchEngineID │ Array(UInt16) │ │ │ │ │ │ │ TraficSource.AdvEngineID │ Array(UInt8) │ │ │ │ │ │ │ TraficSource.PlaceID │ Array(UInt16) │ │ │ │ │ │ │ TraficSource.SocialSourceNetworkID │ Array(UInt8) │ │ │ │ │ │ │ TraficSource.Domain │ Array(String) │ │ │ │ │ │ │ TraficSource.SearchPhrase │ Array(String) │ │ │ │ │ │ │ TraficSource.SocialSourcePage │ Array(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.Key1 │ Array(String) │ │ │ │ │ │ │ ParsedParams.Key2 │ Array(String) │ │ │ │ │ │ │ ParsedParams.Key3 │ Array(String) │ │ │ │ │ │ │ ParsedParams.Key4 │ Array(String) │ │ │ │ │ │ │ ParsedParams.Key5 │ Array(String) │ │ │ │ │ │ │ ParsedParams.ValueDouble │ Array(Float64) │ │ │ │ │ │ │ Market.Type │ Array(UInt8) │ │ │ │ │ │ │ Market.GoalID │ Array(UInt32) │ │ │ │ │ │ │ Market.OrderID │ Array(String) │ │ │ │ │ │ │ Market.OrderPrice │ Array(Int64) │ │ │ │ │ │ │ Market.PP │ Array(UInt32) │ │ │ │ │ │ │ Market.DirectPlaceID │ Array(UInt32) │ │ │ │ │ │ │ Market.DirectOrderID │ Array(UInt32) │ │ │ │ │ │ │ Market.DirectBannerID │ Array(UInt32) │ │ │ │ │ │ │ Market.GoodID │ Array(String) │ │ │ │ │ │ │ Market.GoodName │ Array(String) │ │ │ │ │ │ │ Market.GoodQuantity │ Array(Int32) │ │ │ │ │ │ │ Market.GoodPrice │ Array(Int64) │ │ │ │ │ │ │ IslandID │ FixedString(16) │ │ │ │ │ │ └────────────────────────────────────┴─────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘ 181 rows in set. Elapsed: 0.004 sec. ``` ## 数据查询操作 接下来，我们来针对这两张表里的数据进行一些基础的查询操作。 ### 分组查询取Top N记录 日常业务中比较频繁的一个操作是有一大堆数据，我们需要筛选其中比较“热门”的数据，假设我们要统计网站具备不同“计数器 ID”的子模块的访问次数，那么可以通过下面的语句来完成： ```sql cc1b062138da :) SELECT CounterID, count() AS nums FROM hits GROUP BY CounterID ORDER BY nums DESC LIMIT 10; SELECT CounterID, count() AS nums FROM hits GROUP BY CounterID ORDER BY nums DESC LIMIT 10 Query id: 4206896d-e52e-48fc-bad1-3fe60949f90a ┌─CounterID─┬───nums─┐ │ 1704509 │ 523264 │ │ 732797 │ 475698 │ │ 598875 │ 337212 │ │ 792887 │ 252197 │ │ 3807842 │ 196036 │ │ 25703952 │ 147211 │ │ 716829 │ 90109 │ │ 59183 │ 85379 │ │ 33010362 │ 77807 │ │ 800784 │ 77492 │ └───────────┴────────┘ 10 rows in set. Elapsed: 0.032 sec. Processed 8.87 million rows, 35.50 MB (277.95 million rows/s., 1.11 GB/s.) ``` 或者如果我们要实现一个诸如“新浪、知乎搜索热榜”之类的功能，可以通过下面的方式，来展示前十条热搜关键词，以及对应的搜索次数： ```sql cc1b062138da :) SELECT SearchPhrase AS keyword, count() AS nums FROM hits GROUP BY keyword ORDER BY nums DESC, keyword LIMIT 10 SELECT SearchPhrase AS keyword, count() AS nums FROM hits GROUP BY keyword ORDER BY nums DESC, keyword ASC LIMIT 10 Query id: 62952a2b-858a-463d-b5d3-7af60b9f7e92 ┌─keyword────────────────────┬────nums─┐ │ │ 8267016 │ │ ст 12.168.0.1 │ 3567 │ │ orton │ 2402 │ │ игры лица и гым чан дизайн │ 2166 │ │ imgsrc │ 1848 │ │ брызговик │ 1659 │ │ индийский афтозный │ 1549 │ │ ооооотводка и │ 1480 │ │ выступная мужчин │ 1247 │ │ юность │ 1112 │ └────────────────────────────┴─────────┘ 10 rows in set. Elapsed: 0.096 sec. Processed 8.87 million rows, 112.70 MB (92.77 million rows/s., 1.18 GB/s.) ``` ### 根据数值进行精确筛选 日常业务中，我们也很容易遇到需要看看到底有多少数值不为空的记录，查询方法类似 MySQL ，使用下面的语句可以快速统计 `AdvEngineID` 不为零的数据的总数： ```sql cc1b062138da :) SELECT count() FROM hits WHERE AdvEngineID != 0 SELECT count() FROM hits WHERE AdvEngineID != 0 Query id: e3bc420a-ef87-42a4-879e-bbf548d2e3a0 ┌─count()─┐ │ 30641 │ └─────────┘ 1 rows in set. Elapsed: 0.013 sec. Processed 8.87 million rows, 8.87 MB (668.11 million rows/s., 668.11 MB/s.) ``` ### 数据计算 除了过滤掉为空的数据之外，还容易遇到需要对总访问次数进行求和的操作，以及计算所有来源的平均值，ClickHouse 中为我们提供了大量高性能的计算函数： ```sql cc1b062138da :) SELECT sum(AdvEngineID), count(), avg(ResolutionWidth) FROM hits SELECT sum(AdvEngineID), count(), avg(ResolutionWidth) FROM hits Query id: 47aaef91-69c9-49ed-8e2d-9801ec243338 ┌─sum(AdvEngineID)─┬─count()─┬─avg(ResolutionWidth)─┐ │ 329039 │ 8873898 │ 1400.8565027454677 │ └──────────────────┴─────────┴──────────────────────┘ 1 rows in set. Elapsed: 0.042 sec. Processed 8.87 million rows, 26.62 MB (210.68 million rows/s., 632.05 MB/s.) ``` ### 数据去重 当然，有的时候，我们在应用侧会存储一些冗余的数据，为了计算的准确，我们需要以某个字段为准，进行去重操作，进行更精准的计算。 下面展示从“计数器ID”为 800784 的计数器中筛选数据，并根据 RegionID 字段进行分组，最终以降序排列展示去重后的用户ID。 ```sql cc1b062138da :) SELECT RegionID, uniq(UserID) AS UID FROM hits WHERE CounterID = 800784 GROUP BY RegionID ORDER BY UID DESC, RegionID LIMIT 10 SELECT RegionID, uniq(UserID) AS UID FROM hits WHERE CounterID = 800784 GROUP BY RegionID ORDER BY UID DESC, RegionID ASC LIMIT 10 Query id: 02049c4f-78e9-402a-93ab-1586c5d7a406 ┌─RegionID─┬─UID─┐ │ 196 │ 559 │ │ 3 │ 161 │ │ 241 │ 147 │ │ 207 │ 106 │ │ 225 │ 62 │ │ 1 │ 60 │ │ 46 │ 59 │ │ 36 │ 58 │ │ 104 │ 58 │ │ 47 │ 57 │ └──────────┴─────┘ 10 rows in set. Elapsed: 0.011 sec. Processed 81.92 thousand rows, 1.31 MB (7.61 million rows/s., 121.83 MB/s.) ``` 再来一个更复杂一些的例子，你可以使用 `uniqIf` 同时设置多个去重条件。 ```sql cc1b062138da :) SELECT uniq(UserID), uniqIf(UserID, CounterID = 800784), uniqIf(FUniqID, RegionID = 213) FROM hits SELECT uniq(UserID), uniqIf(UserID, CounterID = 800784), uniqIf(FUniqID, RegionID = 213) FROM hits Query id: 76839d79-9551-4863-a423-e0c21b2193fe ┌─uniq(UserID)─┬─uniqIf(UserID, equals(CounterID, 800784))─┬─uniqIf(FUniqID, equals(RegionID, 213))─┐ │ 120665 │ 4057 │ 106 │ └──────────────┴───────────────────────────────────────────┴────────────────────────────────────────┘ 1 rows in set. Elapsed: 0.162 sec. Processed 8.87 million rows, 212.97 MB (54.87 million rows/s., 1.32 GB/s.) ``` ### 数据排序 数据排序也是一个常见需求，比如我们根据数据时间，展示最近十条写入的数据。 ```sql cc1b062138da :) SELECT EventTime FROM hits ORDER BY EventTime DESC LIMIT 10 SELECT EventTime FROM hits ORDER BY EventTime DESC LIMIT 10 Query id: fbdc1df9-0ff1-408f-b681-3a94be1e6a86 ┌───────────EventTime─┐ │ 2014-03-24 03:59:59 │ │ 2014-03-24 03:59:59 │ │ 2014-03-24 03:59:59 │ │ 2014-03-24 03:59:59 │ │ 2014-03-24 03:59:59 │ │ 2014-03-24 03:59:59 │ │ 2014-03-24 03:59:59 │ │ 2014-03-24 03:59:59 │ │ 2014-03-24 03:59:59 │ │ 2014-03-24 03:59:59 │ └─────────────────────┘ 10 rows in set. Elapsed: 0.174 sec. Processed 8.87 million rows, 35.50 MB (51.11 million rows/s., 204.43 MB/s.) ``` ### 复合查询条件的排序 ClickHouse 对于结构化的数据的查询支持也非常好，你可以使用类似下面的语句对于数组类型的数据进行查询，以及直接对结果进行排序： ```sql cc1b062138da :) SELECT ParsedParams.Key1 FROM visits FINAL WHERE VisitID != 0 AND notEmpty(ParsedParams.Key1) ORDER BY VisitID LIMIT 10 SELECT ParsedParams.Key1 FROM visits FINAL WHERE (VisitID != 0) AND notEmpty(ParsedParams.Key1) ORDER BY VisitID ASC LIMIT 10 Query id: 05a3ae18-43a5-414a-9c8a-8b98c6cbce42 ┌─ParsedParams.Key1────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ ['Кнопка'] │ │ ['pageParams','pageParams'] │ │ ['pageParams'] │ │ ['gen_time'] │ │ ['pageParams'] │ │ ['pageParams'] │ │ ['pageParams'] │ │ ['pageParams'] │ │ ['Прав','gen_timestamp','Прав','Действи','affili','gen_timestamp','Эксперимент про отрыв счетчика у папок','Просмотр писем'] │ │ ['Марка','gen_time','Марка'] │ └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ 10 rows in set. Elapsed: 0.307 sec. Processed 1.68 million rows, 62.63 MB (5.46 million rows/s., 203.83 MB/s.) ``` ### 范围查询 限制范围查询除了使用数学的比较符号外，一般分为两类，第一类是设置“白名单”进行查询： ```sql cc1b062138da :) select sum(Sign) from visits where CounterID in (942285); SELECT sum(Sign) FROM visits WHERE CounterID IN (942285) Query id: 44b2f770-8916-46b9-b056-74b24b155e95 ┌─sum(Sign)─┐ │ 108133 │ └───────────┘ 1 rows in set. Elapsed: 0.008 sec. Processed 114.69 thousand rows, 573.44 KB (14.42 million rows/s., 72.12 MB/s.) ``` 第二类则是，根据给定的时间范围进行查询，在 ClickHouse 里，如果我们想知道一段数据的记录时间，甚至可以这样做： ```sql cc1b062138da :) SELECT min(EventDate), max(EventDate) FROM hits SELECT min(EventDate), max(EventDate) FROM hits Query id: dd1b7383-6846-4cf6-86cb-5217ffa1357f ┌─min(EventDate)─┬─max(EventDate)─┐ │ 2014-03-17 │ 2014-03-23 │ └────────────────┴────────────────┘ 1 rows in set. Elapsed: 0.015 sec. Processed 8.87 million rows, 17.75 MB (599.25 million rows/s., 1.20 GB/s.) ``` ### Join 查询 联合查询也是一个常见需求，关于 Join ，一张图胜过千言万语。 ![About SQL JOINS](https://attachment.soulteary.com/2021/10/16/sql-joins.jpg) 如果我们希望将一张百万，一张近千万的数据表进行联合查询，计算相同日期里，的用户UV和点击PV的话，可以这样操作： ```sql SELECT EventDate, hits, visits FROM ( SELECT EventDate, count() AS hits FROM hits GROUP BY EventDate ) ANY LEFT JOIN ( SELECT StartDate AS EventDate, sum(Sign) AS visits FROM visits GROUP BY EventDate ) USING (EventDate) ORDER BY hits DESC LIMIT 10 SETTINGS joined_subquery_requires_alias = 0 Query id: dbfee618-01b1-44bf-af8f-12a73c7ff300 ┌──EventDate─┬────hits─┬─visits─┐ │ 2014-03-17 │ 1406958 │ 265108 │ │ 2014-03-19 │ 1405797 │ 261624 │ │ 2014-03-18 │ 1383658 │ 258723 │ │ 2014-03-20 │ 1353623 │ 255328 │ │ 2014-03-21 │ 1245779 │ 236232 │ │ 2014-03-23 │ 1046491 │ 202212 │ │ 2014-03-22 │ 1031592 │ 197354 │ └────────────┴─────────┴────────┘ 7 rows in set. Elapsed: 0.051 sec. Processed 10.55 million rows, 22.78 MB (207.12 million rows/s., 447.17 MB/s.) ``` ### 子查询 子查询同样是一个非常常见的需求，可以帮助我们减少非常多不必要的手动步骤，比如像下面这样，先筛选一批用户，然后再计算这批用户的访问情况： ```sql cc1b062138da :) SELECT count() FROM hits PREWHERE UserID IN (SELECT UserID FROM hits WHERE CounterID = 800784); SELECT count() FROM hits PREWHERE UserID IN ( SELECT UserID FROM hits WHERE CounterID = 800784 ) Query id: 5d254281-8c9e-4593-9d8a-bdce24037d97 ┌─count()─┐ │ 1956422 │ └─────────┘ 1 rows in set. Elapsed: 0.032 sec. Processed 8.96 million rows, 71.97 MB (283.90 million rows/s., 2.28 GB/s.) cc1b062138da :) ``` 上面的例子中，我们使用了 PREWHERE 来进行数据查询优化，可以过滤大量不必要的数据读取，不过如果你的数据量比较少，或者并发压力小，一律 WHERE 也问题不大，以下是两个语句对于数据读取量的差异： ```sql # PREWHERE 1 rows in set. Elapsed: 0.106 sec. Processed 8.96 million rows, 71.97 MB (84.79 million rows/s., 681.46 MB/s.) # WHERE 1 rows in set. Elapsed: 0.026 sec. Processed 8.96 million rows, 71.97 MB (339.11 million rows/s., 2.73 GB/s.) ``` ### 字符串模糊搜索 有一些时候，我们需要进行快速的模糊搜索，ClickHouse 同样能够提供非常令人惊讶的性能： ```sql cc1b062138da :) SELECT count() FROM hits WHERE URL LIKE '%avtomobili%'; SELECT count() FROM hits WHERE URL LIKE '%avtomobili%' Query id: 93ea9afc-070e-47ae-885e-6b14c26315a5 ┌─count()─┐ │ 51354 │ └─────────┘ 1 rows in set. Elapsed: 0.152 sec. Processed 8.87 million rows, 767.95 MB (58.44 million rows/s., 5.06 GB/s.) ``` ### 彩蛋：偷懒画个图 除此之外，有的时候，我们会要求绘制一些图表来帮助我们直观的看到数据量级的差异，也可以使用 ClickHouse 自带的 `bar` 函数来处理。 ```sql cc1b062138da :) SELECT CounterID, count() AS c, bar(c, 0, 523264) FROM hits GROUP BY CounterID ORDER BY c DESC, CounterID ASC LIMIT 100; SELECT CounterID, count() AS c, bar(c, 0, 523264) FROM hits GROUP BY CounterID ORDER BY c DESC, CounterID ASC LIMIT 100 Query id: ee79be07-c93f-4aba-ad81-8d6938c99f96 ┌─CounterID─┬──────c─┬─bar(count(), 0, 523264)──────────────────────────────────────────────────────────┐ │ 1704509 │ 523264 │ ████████████████████████████████████████████████████████████████████████████████ │ │ 732797 │ 475698 │ ████████████████████████████████████████████████████████████████████████▋ │ │ 598875 │ 337212 │ ███████████████████████████████████████████████████▌ │ │ 792887 │ 252197 │ ██████████████████████████████████████▌ │ │ 3807842 │ 196036 │ █████████████████████████████▊ │ │ 25703952 │ 147211 │ ██████████████████████▌ │ │ 716829 │ 90109 │ █████████████▋ │ │ 59183 │ 85379 │ █████████████ │ │ 33010362 │ 77807 │ ███████████▊ │ │ 800784 │ 77492 │ ███████████▋ │ │ 20810645 │ 73213 │ ███████████▏ │ │ 25843850 │ 68945 │ ██████████▌ │ │ 23447120 │ 67570 │ ██████████▎ │ │ 14739804 │ 64174 │ █████████▋ │ │ 32077710 │ 60456 │ █████████▏ │ │ 22446879 │ 58389 │ ████████▊ │ │ 170282 │ 57017 │ ████████▋ │ │ 11482817 │ 52345 │ ████████ │ │ 63469 │ 52142 │ ███████▊ │ │ 29103473 │ 47758 │ ███████▎ │ │ 10136747 │ 44080 │ ██████▋ │ │ 27528801 │ 43395 │ ██████▋ │ │ 10581377 │ 43279 │ ██████▌ │ │ 9841201 │ 40581 │ ██████▏ │ │ 20310963 │ 37562 │ █████▋ │ │ 17337667 │ 34301 │ █████▏ │ │ 28600281 │ 32776 │ █████ │ │ 32046685 │ 28788 │ ████▍ │ │ 10130880 │ 26603 │ ████ │ │ 8676831 │ 25733 │ ███▊ │ │ 53230 │ 25595 │ ███▊ │ │ 20271226 │ 25585 │ ███▊ │ │ 17420663 │ 25496 │ ███▊ │ │ 631207 │ 25270 │ ███▋ │ │ 633130 │ 24744 │ ███▋ │ │ 14324015 │ 23349 │ ███▌ │ │ 8537965 │ 21270 │ ███▎ │ │ 11285298 │ 20825 │ ███▏ │ │ 14937615 │ 20788 │ ███▏ │ │ 185050 │ 20785 │ ███▏ │ │ 16368233 │ 19897 │ ███ │ │ 81602 │ 19724 │ ███ │ │ 62896 │ 19717 │ ███ │ │ 12967664 │ 19402 │ ██▊ │ │ 15996597 │ 18557 │ ██▋ │ │ 4379238 │ 18370 │ ██▋ │ │ 90982 │ 17443 │ ██▋ │ │ 18211045 │ 17390 │ ██▋ │ │ 14625884 │ 17302 │ ██▋ │ │ 12864910 │ 17279 │ ██▋ │ │ 126096 │ 16959 │ ██▌ │ │ 30296134 │ 16849 │ ██▌ │ │ 26360482 │ 16175 │ ██▍ │ │ 17788950 │ 16017 │ ██▍ │ │ 5928716 │ 15340 │ ██▎ │ │ 15469035 │ 15171 │ ██▎ │ │ 29732125 │ 15146 │ ██▎ │ │ 32946244 │ 15104 │ ██▎ │ │ 20957241 │ 14719 │ ██▎ │ │ 9495695 │ 14584 │ ██▏ │ │ 29241146 │ 14540 │ ██▏ │ │ 109805 │ 14199 │ ██▏ │ │ 26905788 │ 13972 │ ██▏ │ │ 212019 │ 13930 │ ██▏ │ │ 171509 │ 13792 │ ██ │ │ 23913162 │ 13615 │ ██ │ │ 1861993 │ 13509 │ ██ │ │ 125776 │ 13308 │ ██ │ │ 11312316 │ 13181 │ ██ │ │ 32667326 │ 13181 │ ██ │ │ 28628973 │ 12922 │ █▊ │ │ 122804 │ 12520 │ █▊ │ │ 12322758 │ 12352 │ █▊ │ │ 1301819 │ 12283 │ █▊ │ │ 10769545 │ 12183 │ █▋ │ │ 21566939 │ 12170 │ █▋ │ │ 28905364 │ 12158 │ █▋ │ │ 4250765 │ 12049 │ █▋ │ │ 15009727 │ 11818 │ █▋ │ │ 12761932 │ 11733 │ █▋ │ │ 26995888 │ 11658 │ █▋ │ │ 12759346 │ 11514 │ █▋ │ │ 1507911 │ 11452 │ █▋ │ │ 968488 │ 11444 │ █▋ │ │ 15736172 │ 11358 │ █▋ │ │ 54310 │ 11193 │ █▋ │ │ 17027391 │ 11047 │ █▋ │ │ 17439919 │ 10936 │ █▋ │ │ 4480860 │ 10747 │ █▋ │ │ 26738469 │ 10738 │ █▋ │ │ 9986231 │ 10656 │ █▋ │ │ 1539995 │ 10655 │ █▋ │ │ 214556 │ 10625 │ █▌ │ │ 219339 │ 10522 │ █▌ │ │ 3266 │ 10503 │ █▌ │ │ 30563429 │ 10128 │ █▌ │ │ 1960469 │ 10098 │ █▌ │ │ 7901143 │ 10022 │ █▌ │ │ 194599 │ 9997 │ █▌ │ │ 21052498 │ 9780 │ █▍ │ └───────────┴────────┴──────────────────────────────────────────────────────────────────────────────────┘ 100 rows in set. Elapsed: 0.034 sec. Processed 8.87 million rows, 35.50 MB (259.87 million rows/s., 1.04 GB/s.) ``` ## MySQL 数据导入 接下来，我们来了解下如何进行数据导入，分别以常见的 MySQL 、 CSV 为例。（官方目前支持几十种数据的直接导入或导出格式：[https://clickhouse.tech/docs/zh/interfaces/formats/](https://clickhouse.tech/docs/zh/interfaces/formats/) ，你可以根据实际需求进行调整，减少不必要数据转换操作。） ### 启动 MySQL 数据库 为了能够让数据库实例能够互通，我们需要先创建一个虚拟的容器网络： ```bash docker network create dbtest ``` 和上文一样，将下面的内容保存为 `docker-compose.yml`（可以存放另外一个目录，如果存放相同目录，需要调整数据目录名称）： ```yaml version: '3' services: mysql: container_name: mysql image: mysql:5.7.25 ports: - 13306:3306 environment: MYSQL_USER: test MYSQL_PASSWORD: test MYSQL_DATABASE: test MYSQL_ROOT_PASSWORD: test command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --default-storage-engine=INNODB --transaction-isolation=READ-COMMITTED --binlog_format=row --max_allowed_packet=33554432 --sql_mode="STRICT_ALL_TABLES" --local-infile=0 volumes: # - /etc/localtime:/etc/localtime:ro # - /etc/timezone:/etc/timezone:ro - ./data:/var/lib/mysql healthcheck: test: ["CMD-SHELL", "mysqladmin ping -h localhost -p$$MYSQL_ROOT_PASSWORD" ] networks: - dbtest networks: dbtest: external: true ``` 接着同样使用 `docker-compose up -d` 命令，将 MySQL 启动起来，稍后使用。 ### 初始化测试数据 将前文提到的测试数据下载完成后，先进行解压缩，解压缩之后可以看到压缩比差不多 1:6（早些时候的例子是 1:10）。 ```bash unzip yyets_mysql.zip du -hs yyets* 262M yyets.sql 41M yyets_mysql.zip ``` 将数据扔到数据库容器可以访问到的目录中（推荐使用另外一个方式，方便更直观的对比迁移前后的数据体积）： ```bash mv yyets-mysql5.7.sql data ``` 或者使用 `docker cp` 命令，将数据复制进 MySQL 容器的数据文件夹中： ```bash docker cp yyets.sql mysql:/var/lib/mysql-files/ ``` 接着使用 `docker exec` 命令登陆容器内的数据终端，先来创建一个空的数据库。 ```bash docker exec -it mysql mysql -uroot -ptest mysql> create database yyets; Query OK, 1 row affected (0.01 sec) ``` 接着，将下载好的外部数据源进行初始化，用来 ClickHouse 导入的数据源。 ```bash docker exec -it mysql bash # 到底是从 mysql-files 还是从 mysql 目录导入，取决于你将数据放在哪里了 mysql -uroot -ptest yyets < /var/lib/mysql-files/yyets.sql # 忽略下面的提示 mysql: [Warning] Using a password on the command line interface can be insecure. ``` 稍等片刻数据就都被恢复到 MySQL 实例中了。 导入完毕后，再次使用 `du` 看看磁盘占用空间，对比导入前的 SQL 文件，磁盘使用空间膨胀了270MB左右（包含索引）。 ```bash du -hs data 530M data ``` 我们再次进入数据库，来看看数据表和数据总量。 ```sql docker exec -it mysql mysql -uroot -ptest # 查看数据库列表 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | | yyets | +--------------------+ 6 rows in set (0.03 sec) # 切换数据库 mysql> use yyets; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed #查看数据表 mysql> show tables; +-----------------+ | Tables_in_yyets | +-----------------+ | yyets | +-----------------+ 1 row in set (0.00 sec) # 查看数据量 mysql> select count(*) from yyets; +----------+ | count(*) | +----------+ | 17579 | +----------+ 1 row in set (0.83 sec) ``` 可以看到数据库内，只有不到2万条的数据，虽然比较少，但是足够我们用来测试啦。 ### 调整 ClickHouse 实例 为了直观对比数据存储差异，我们可以关闭原来的 ClickHouse 实例后，换一个干净的数据目录，再启动一个新的 ClickHouse 实例。 不过，我们也需要简单调整一下 ClickHouse 配置，让它加入相同的容器网络中： ```yaml version: "2" services: server: ... networks: - dbtest networks: dbtest: external: true ``` 过程我就不多赘述了，参考前文即可，不过这里我们先记录一下空的 ClickHouse 目录占用多少磁盘空间。 ```yaml clickhouse du -hs * 2.0M data 4.0K docker-compose.yml ``` ### 导入 MySQL 数据到 ClickHouse 为了简化导入数据，减少不必要的“数据转换”操作，我们可以通过先创建一个“在线表”的方式来让 CH 自动创建 `scheme` 。 - 官方文档中的数据类型：[https://clickhouse.tech/docs/zh/sql-reference/data-types/](https://clickhouse.tech/docs/zh/sql-reference/data-types/) 先使用容器登陆终端 ： ```bash docker exec -it clickhouse clickhouse-client ``` 接着“挂载” MySQL 到 ClickHouse； ```sql 981a7a68eb35 :) CREATE DATABASE yyets_online ENGINE = MySQL('mysql:3306', 'yyets', 'root', 'test') CREATE DATABASE yyets_online ENGINE = MySQL('mysql:3306', 'yyets', 'root', 'test') Query id: 45e4570e-3536-4240-9775-fbc9d91ffdcc Ok. 0 rows in set. Elapsed: 0.026 sec. 981a7a68eb35 :) ``` 切换数据库，查看表数据量，可以看到和 MySQL 中看到的一样。 ```sql 981a7a68eb35 :) use yyets_online; USE yyets_online Query id: 9b373b74-11a5-47be-8358-989522311e3e Ok. 0 rows in set. Elapsed: 0.002 sec. 981a7a68eb35 :) show tables SHOW TABLES Query id: 04cf20ce-b656-441e-b414-a63225d498d1 ┌─name──┐ │ yyets │ └───────┘ 1 rows in set. Elapsed: 0.006 sec. ``` 继续查看表结构，会看到数据类型已经转换好了。 ```sql 981a7a68eb35 :) desc yyets DESCRIBE TABLE yyets Query id: 650a4f24-fd8b-4290-b40e-cb59b78a926e ┌─name───────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐ │ id │ Int32 │ │ │ │ │ │ │ url │ Nullable(String) │ │ │ │ │ │ │ name │ Nullable(String) │ │ │ │ │ │ │ expire │ Nullable(Int32) │ │ │ │ │ │ │ expire_cst │ Nullable(String) │ │ │ │ │ │ │ data │ Nullable(String) │ │ │ │ │ │ └────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘ 6 rows in set. Elapsed: 0.004 sec. ``` 继续“偷懒”，直接使用 `show create` 语句得到创建当前表结构的命令。 ```sql 981a7a68eb35 :) show create yyets SHOW CREATE TABLE yyets Query id: 85caee09-9bc0-4e7c-9ff2-bfab820c2c6b ┌─statement──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ CREATE TABLE yyets_online.yyets ( `id` Nullable(Int32), `cnname` Nullable(String), `enname` Nullable(String), `aliasname` Nullable(String), `views` Nullable(Int32), `data` Nullable(String) ) ENGINE = MySQL('mysql:3306', 'yyets', 'yyets', 'root', 'test') │ └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ 1 rows in set. Elapsed: 0.005 sec. ``` 然后将 `数据库引擎`部分进行修改，并添加主键即可完成我们后续用于数据计算的离线表的创建。 ```sql 981a7a68eb35 :) CREATE DATABASE yyets_offline; CREATE DATABASE yyets_offline Query id: edab3813-f340-4a48-86d7-f12c6ad3297e Ok. 0 rows in set. Elapsed: 0.019 sec. 981a7a68eb35 :) CREATE TABLE yyets_offline.yyets :-] ( :-] `id` Int32, :-] `url` Nullable(String), :-] `name` Nullable(String), :-] `expire` Nullable(Int32), :-] `expire_cst` Nullable(String), :-] `data` Nullable(String) :-] ) ENGINE = MergeTree() :-] ORDER BY `id` CREATE TABLE yyets_offline.yyets ( `id` Int32, `url` Nullable(String), `name` Nullable(String), `expire` Nullable(Int32), `expire_cst` Nullable(String), `data` Nullable(String) ) ENGINE = MergeTree ORDER BY id Query id: b8371553-683a-418f-9593-b4da49fd5517 Ok. 0 rows in set. Elapsed: 0.031 sec. 981a7a68eb35 :) ``` 最后，使用 `insert into` 语句将在线表中的数据导入离线表即可。 ```sql 981a7a68eb35 :) insert INTO yyets_offline.yyets select * from yyets_online.yyets INSERT INTO yyets_offline.resource SELECT * FROM yyets_online.resource Query id: fe5eec24-ae0d-4deb-abba-76df242ae8a9 Ok. 0 rows in set. Elapsed: 5.276 sec. Processed 17.58 thousand rows, 244.68 MB (3.33 thousand rows/s., 46.37 MB/s.) ``` 在导入完成后，我们可以先做一个简单的查询，确认数据是否传输完毕。 ```sql SELECT online, offline FROM ( SELECT count() AS online FROM yyets_online.yyets ) AS table1 , ( SELECT count() AS offline FROM yyets_offline.yyets ) AS table2 Query id: c0edda99-8491-4dd7-bcbc-0323b986553e ┌─online─┬─offline─┐ │ 17579 │ 17579 │ └────────┴─────────┘ 1 rows in set. Elapsed: 0.032 sec. Processed 17.52 thousand rows, 74.18 KB (542.94 thousand rows/s., 2.30 MB/s.) ``` 可以看到两个表的数据量一致，代表数据导入就完成了。可以随便试一条简单的关键词匹配。 ```sql 466b62ff5dae :) SELECT id, name FROM yyets_offline.`yyets` WHERE `data` LIKE '%漫威%' limit 10 SELECT id, name FROM yyets_offline.yyets WHERE data LIKE '%漫威%' LIMIT 10 Query id: 1d1a3884-b73b-4afd-b850-418e8b218e74 ┌────id─┬─name─────────────────────┐ │ 29620 │ Marvel Phase Two Preview │ └───────┴──────────────────────────┘ ┌────id─┬─name───────────────────────────────┐ │ 30574 │ Marvel One-Shot: Agent Carter │ │ 31794 │ Marvel One-Shot: All Hail The King │ │ 33101 │ Marvel 75 Years: From Pulp to Pop! │ │ 33108 │ Marvel One-Shots │ │ 34471 │ Marvel │ │ 35159 │ Iron Fist │ │ 35506 │ The Defenders │ │ 35565 │ Spider-Man │ │ 35738 │ The Punisher │ └───────┴────────────────────────────────────┘ 10 rows in set. Elapsed: 0.277 sec. Processed 17.58 thousand rows, 243.35 MB (63.52 thousand rows/s., 879.26 MB/s.) ``` 数据导入完毕后，记得“打扫卫生”，删除挂载的远程“ online ”数据库的连接即可。 ```bash 981a7a68eb35 :) drop database yyets_online DROP DATABASE yyets_online Query id: 31c82654-a15f-48ba-b27e-e7fa67896ecb Ok. 0 rows in set. Elapsed: 0.007 sec. ``` 最后，使用 du 查看一下 ClickHouse 使用的磁盘空间吧。 ```bash du -hs data 77M data ``` 相比 MySQL 的 530M 数据存储量来说， ClickHouse 的存储占用真的是非常轻量啦。 ## CSV 数据导入 一般情况下， CSV 类型数据的导入非常简单，但是 CSV 数据中**存在非常多“非标准数据”**：比如非严格转义、非严格数值类型标记，混合多系统换行符， field 数据包含“预期之外的数据”等，针对这种情况，我们需要先对数据进行“修复”，因为不同的场景数据完全不同，所以这里没有办法提供一个“万能”的通用方案，不过以过来人的经验，使用 Golang 或者 Node 编写一个清洗工具，执行效率真的蛮快的，感兴趣的同学可以试试看。 我们说回“数据导入”，根据是否还需要 pipeline 处理过程，通常我们有两种方式来进行 CSV 类型的数据导入，如果你的数据都是预先处理过的，可以采取下面的方式来直接导入： ```bash clickhouse-client --format_csv_delimiter="|" --format_csv_allow_double_quotes=0 --format_csv_allow_single_quotes=0 --query="INSERT INTO yyets_csv.resource FORMAT CSV" < /var/lib/clickhouse/dump.csv ``` 但是如果你的数据需要使用过程工具来做转换，则可以使用类似下面的方式来导入，过程中可以使用 `awk`、`sed`、`你自己的工具`： ```bash cat /var/lib/clickhouse/dump.csv | clickhouse-client --format_csv_delimiter="|" --format_csv_allow_double_quotes=0 --format_csv_allow_single_quotes=0 --query="INSERT INTO yyets_csv.resource FORMAT CSV" ``` ## 最后 不知不觉写了近万字，先聊到这里吧，后面有时间再聊聊使用过程中踩到的坑。 --EOF