探索 ClickHouse：用于 OLAP（联机分析处理）的高性能列式数据库管理系统

数据库简介
列式数据库基础
- 行式存储与列式存储
ClickHouse 简介
- 什么是 ClickHouse？
- ClickHouse 的关键特性与速度
  - 主键、索引与颗粒（Granule）
ClickHouse 架构
- ClickHouse 架构概述
- ClickHouse 引擎
ClickHouse 入门
- 安装与设置 - Mac 或 Linux
- 运行您的第一个查询
ClickHouse 云入门
chDB - 概述
- 概述
- 用例 - 为什么要使用 chDB
演示 - Google Colab
参考资料：

数据库简介

什么是数据库？

数据库是用于存储、组织和访问信息的系统。它允许用户高效且安全地输入、管理和检索数据。数据库通过将数据存储在易于搜索、操作和维护的表中，从而能够处理大量信息。

数据库类型

关系型 以结构化的行和列形式存储数据，允许使用 SQL 进行复杂查询。示例：人力资源（HR）和客户关系管理（CRM）数据库

非关系型 处理非结构化和半结构化数据，没有固定的表结构，使用键值对、文档和图形等格式。示例：电子商务平台、内容管理、物联网（IoT）和实时分析

数据库管理系统（DBMS）简介

允许用户定义、创建、维护和控制数据库访问的软件。它管理数据的存储、操作和检索，促进多个用户与各种数据库之间的交互。

列式数据库基础

行式存储与列式存储

行式数据库：

*结构*：按行存储数据，这对于写入操作和需要访问完整记录的事务处理非常高效。
*优势*：非常适合 OLTP（联机事务处理）系统，如客户关系管理，在这些系统中，快速更新和插入至关重要。

列式数据库：

*结构*：按列存储数据，增强了读取操作和数据分析能力，适用于需要对大型数据集进行聚合的场景。
*优势*：非常适合 OLAP（联机分析处理）系统，如数据仓库和大数据分析，为仅访问部分列的查询提供高性能。

优点：

*行式*：按完整行快速检索数据，实现更简单，对于记录的输入和更新非常高效。
*列式*：更好的压缩率，高效的存储带来更快的列数据读取速度，针对大型数据集查询进行了优化，从而减少了 I/O 操作。

列式数据库

graph TD
    subgraph "Column-Oriented Database"
        subgraph "ID Column"
            ID1[1]
            ID2[2]
            ID3[3]
        end
        subgraph "Name Column"
            Name1[John]
            Name2[Alice]
            Name3[Bob]
        end
        subgraph "Age Column"
            Age1[30]
            Age2[25]
            Age3[35]
        end
        subgraph "City Column"
            City1[New York]
            City2[Los Angeles]
            City3[San Francisco]
        end
    end

行式数据库

graph TD
    subgraph "Row-Oriented Storage"
        A["Row 1: ID | Name | Age | City"]
        B["Row 2: ID | Name | Age | City"]
        C["Row 3: ID | Name | Age | City"]
    end

ClickHouse 简介

什么是 ClickHouse？

ClickHouse 是一个开源的列式数据库管理系统（DBMS），专为联机分析处理（OLAP）而设计。它允许用户使用 SQL 查询实时生成分析报告，为处理海量数据提供高性能和可扩展性。主要特性包括：

列式存储
数据压缩
分布式查询处理
SQL 支持
分析查询的高性能
处理 PB 级数据的能力
通过复制实现容错

ClickHouse 特别适用于需要对海量数据集进行快速分析的应用程序，例如商业智能、可观测性和实时数据仓库。

ClickHouse 的关键特性与速度

*列式存储*：通常，报告只需要源数据中数百列中的几列。列式存储通过仅访问必要的列来提高效率，减少了昂贵的磁盘读取。
*索引*：ClickHouse 利用内存中的数据结构来仅读取所需的列和特定的行范围，从而优化数据检索。
*数据压缩*：与行式系统相比，对同一列的值进行分组可以增强压缩效果，因为相邻行中通常存在相似的值。ClickHouse 还采用专门的编解码器以实现更高的数据紧凑性。
*向量化查询执行*：ClickHouse 按列处理数据，提高了 CPU 缓存效率，并能够使用 SIMD CPU 指令进行更快的操作。
*可扩展性*：ClickHouse 通过利用所有可用的 CPU 核心和磁盘来最大化资源利用率，不仅是在单台服务器上，而且是在整个集群中，从而增强了复杂查询的性能。

主键、索引与颗粒（Granule）

主键：

决定了磁盘上数据的物理排序顺序
用于创建稀疏索引

索引：

每 N 行（默认 8192 行）创建一个索引，称为颗粒（Granule）
存储在 primary.idx 文件中
用于快速定位相关数据块

颗粒（Granules）：

颗粒是行的逻辑分组（默认 8192 行）
数据被拆分为多个部分，每个部分又被划分为颗粒

它们如何协同工作：

执行查询时，ClickHouse 使用主键搜索稀疏索引
索引有助于识别哪些颗粒可能包含相关数据
ClickHouse 随后仅将匹配的颗粒加载到内存中
处理颗粒内的数据以找到精确匹配的行

ClickHouse 架构

ClickHouse 架构概述

引擎 ClickHouse 引擎是一种表引擎，它决定了数据的存储方式和位置、查询的执行方式，以及数据库的并发和复制能力。每种引擎都针对特定的用例进行了优化，提供了诸如数据分区、索引和对不同查询类型的支持等各种功能。

ClickHouse 引擎

MergeTree 系列：

MergeTree：最通用且最常用的引擎，专为高性能读写操作而设计，支持数据分区、主键和数据复制。
ReplacingMergeTree：允许在合并期间根据排序键对行进行去重。
SummingMergeTree：在合并期间对具有相同排序键的行的数值列进行求和。
AggregatingMergeTree：在合并期间使用指定的聚合函数聚合数据。
CollapsingMergeTree：在合并期间根据特殊的“Sign”列折叠具有相同排序键的行。
VersionedCollapsingMergeTree：扩展了 CollapsingMergeTree，增加了行的版本控制。
GraphiteMergeTree：专门用于存储时间序列数据。

集成引擎：

ODBC：允许使用 ODBC 查询外部数据库。
JDBC：允许使用 JDBC 查询外部数据库。
MySQL：用于查询 MySQL 数据库。
MongoDB：允许查询 MongoDB 数据库。
HDFS：用于处理存储在 Hadoop 分布式文件系统中的数据。
S3：允许处理存储在 Amazon S3 中的数据。
Kafka：用于处理 Apache Kafka 流。

ClickHouse 入门

安装与设置 - Mac 或 Linux

安装库

curl https://clickhouse.com/ | sh

运行以下命令启动 ClickHouse 服务器：

./clickhouse server

打开一个新的终端并连接到服务器

clickhouse-client

查询远程文件

--检查文件
SELECT * FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/ontime/csv_by_year/*.csv.gz', CSVWithNames) limit 10;

--基本计数
SELECT count() FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/ontime/csv_by_year/*.csv.gz', CSVWithNames) limit 10;

--2007年各航空公司的延误次数
SELECT IATA_CODE_Reporting_Airline AS Carrier, count(*)
FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/ontime/csv_by_year/*.csv.gz')
WHERE DepDelay>10 AND Year=2007
GROUP BY Carrier
ORDER BY count(*) DESC limit 5;

结果
┌─Carrier─┬─count()─┐

│ WN │ 296293 │ └─────────┴─────────┘ ┌─Carrier─┬─count()─┐
│ AA │ 176203 │
│ MQ │ 145630 │
│ US │ 135987 │
│ UA │ 128174 │ └─────────┴─────────┘

5 rows in set. Elapsed: 420.586 sec. Processed 204.84 million rows, 11.66 GB (487.03 thousand rows/s., 27.72 MB/s.) Peak memory usage: 1.18 GiB.

运行您的第一个查询

创建表

CREATE TABLE `ontime`
(
    `Year`                            UInt16,
    `Quarter`                         UInt8,
    `Month`                           UInt8,
    `DayofMonth`                      UInt8,
    `DayOfWeek`                       UInt8,
    `FlightDate`                      Date,
    `Reporting_Airline`               LowCardinality(String),
    `DOT_ID_Reporting_Airline`        Int32,
    `IATA_CODE_Reporting_Airline`     LowCardinality(String),
    `Tail_Number`                     LowCardinality(String),
    `Flight_Number_Reporting_Airline` LowCardinality(String),
    `OriginAirportID`                 Int32,
    `OriginAirportSeqID`              Int32,
    `OriginCityMarketID`              Int32,
    `Origin`                          FixedString(5),
    `OriginCityName`                  LowCardinality(String),
    `OriginState`                     FixedString(2),
    `OriginStateFips`                 FixedString(2),
    `OriginStateName`                 LowCardinality(String),
    `OriginWac`                       Int32,
    `DestAirportID`                   Int32,
    `DestAirportSeqID`                Int32,
    `DestCityMarketID`                Int32,
    `Dest`                            FixedString(5),
    `DestCityName`                    LowCardinality(String),
    `DestState`                       FixedString(2),
    `DestStateFips`                   FixedString(2),
    `DestStateName`                   LowCardinality(String),
    `DestWac`                         Int32,
    `CRSDepTime`                      Int32,
    `DepTime`                         Int32,
    `DepDelay`                        Int32,
    `DepDelayMinutes`                 Int32,
    `DepDel15`                        Int32,
    `DepartureDelayGroups`            LowCardinality(String),
    `DepTimeBlk`                      LowCardinality(String),
    `TaxiOut`                         Int32,
    `WheelsOff`                       LowCardinality(String),
    `WheelsOn`                        LowCardinality(String),
    `TaxiIn`                          Int32,
    `CRSArrTime`                      Int32,
    `ArrTime`                         Int32,
    `ArrDelay`                        Int32,
    `ArrDelayMinutes`                 Int32,
    `ArrDel15`                        Int32,
    `ArrivalDelayGroups`              LowCardinality(String),
    `ArrTimeBlk`                      LowCardinality(String),
    `Cancelled`                       Int8,
    `CancellationCode`                FixedString(1),
    `Diverted`                        Int8,
    `CRSElapsedTime`                  Int32,
    `ActualElapsedTime`               Int32,
    `AirTime`                         Int32,
    `Flights`                         Int32,
    `Distance`                        Int32,
    `DistanceGroup`                   Int8,
    `CarrierDelay`                    Int32,
    `WeatherDelay`                    Int32,
    `NASDelay`                        Int32,
    `SecurityDelay`                   Int32,
    `LateAircraftDelay`               Int32,
    `FirstDepTime`                    Int16,
    `TotalAddGTime`                   Int16,
    `LongestAddGTime`                 Int16,
    `DivAirportLandings`              Int8,
    `DivReachedDest`                  Int8,
    `DivActualElapsedTime`            Int16,
    `DivArrDelay`                     Int16,
    `DivDistance`                     Int16,
    `Div1Airport`                     LowCardinality(String),
    `Div1AirportID`                   Int32,
    `Div1AirportSeqID`                Int32,
    `Div1WheelsOn`                    Int16,
    `Div1TotalGTime`                  Int16,
    `Div1LongestGTime`                Int16,
    `Div1WheelsOff`                   Int16,
    `Div1TailNum`                     LowCardinality(String),
    `Div2Airport`                     LowCardinality(String),
    `Div2AirportID`                   Int32,
    `Div2AirportSeqID`                Int32,
    `Div2WheelsOn`                    Int16,
    `Div2TotalGTime`                  Int16,
    `Div2LongestGTime`                Int16,
    `Div2WheelsOff`                   Int16,
    `Div2TailNum`                     LowCardinality(String),
    `Div3Airport`                     LowCardinality(String),
    `Div3AirportID`                   Int32,
    `Div3AirportSeqID`                Int32,
    `Div3WheelsOn`                    Int16,
    `Div3TotalGTime`                  Int16,
    `Div3LongestGTime`                Int16,
    `Div3WheelsOff`                   Int16,
    `Div3TailNum`                     LowCardinality(String),
    `Div4Airport`                     LowCardinality(String),
    `Div4AirportID`                   Int32,
    `Div4AirportSeqID`                Int32,
    `Div4WheelsOn`                    Int16,
    `Div4TotalGTime`                  Int16,
    `Div4LongestGTime`                Int16,
    `Div4WheelsOff`                   Int16,
    `Div4TailNum`                     LowCardinality(String),
    `Div5Airport`                     LowCardinality(String),
    `Div5AirportID`                   Int32,
    `Div5AirportSeqID`                Int32,
    `Div5WheelsOn`                    Int16,
    `Div5TotalGTime`                  Int16,
    `Div5LongestGTime`                Int16,
    `Div5WheelsOff`                   Int16,
    `Div5TailNum`                     LowCardinality(String)
) ENGINE = MergeTree
  ORDER BY (Year, Quarter, Month, DayofMonth, FlightDate, IATA_CODE_Reporting_Airline);

加载数据 - 加载了 100 万条记录，仅限 2002 年

INSERT INTO ontime SELECT *
FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/ontime/csv_by_year/*2001*.csv.gz', CSVWithNames)
WHERE Year=2001 limit 1000000
SETTINGS max_insert_threads = 40;

各机场的延误次数

SELECT Origin, count(*) AS c
FROM ontime
WHERE DepDelay>10
GROUP BY Origin
ORDER BY c DESC
LIMIT 10;

查询本地数据 - 文件在本地

使用纽约投诉数据集。

纽约警察局投诉数据

describe table file("/Users/[you]/user_files/nypd_complaint.tsv")

select * from file("/Users/[you]/user_files/nypd_complaint.tsv") limit 10

快速分析

按犯罪类型统计投诉数量

SELECT IFNULL(NYPD_Complaint_Data_Current_Year_To_Date.`OFNS_DESC`, 'Unknown') AS Crime_Type,
    COUNT(*) AS Complaint_Count
FROM url('https://data.cityofnewyork.us/api/views/5uac-w243/rows.csv?accessType=DOWNLOAD', CSVWithNames)
    AS NYPD_Complaint_Data_Current_Year_To_Date
GROUP BY Crime_Type ORDER BY Complaint_Count DESC limit 10

夜间最常见的犯罪

SELECT IFNULL(NYPD_Complaint.`OFNS_DESC`, 'Unknown') AS Crime_Type,
    COUNT(*) AS Complaint_Count
FROM url('https://data.cityofnewyork.us/api/views/5uac-w243/rows.csv?accessType=DOWNLOAD', CSVWithNames)
    AS NYPD_Complaint

WHERE (NYPD_Complaint.`CMPLNT_FR_TM` >= '18:00:00' OR NYPD_Complaint.`CMPLNT_FR_TM` <= '06:00:00')
GROUP BY Crime_Type ORDER BY Complaint_Count DESC limit 10

ClickHouse 云入门

ClickHouse 云

chDB - 概述

概述

chDB 是一个由 ClickHouse 提供支持的进程内 SQL OLAP（联机分析处理）引擎。它被设计为一个嵌入式数据库，无需单独安装服务器即可将 ClickHouse 的强大功能引入应用程序。chDB 由 ClickHouse, Inc. 和开源贡献者共同开发。

chDB 的主要特性包括：

无服务器操作 - 无需安装或运行 ClickHouse 服务
支持多种编程语言，包括 Python、Go、Rust、NodeJS 和 Bun
能够处理各种数据格式，如 Parquet、CSV、JSON、Arrow 和 ORC
自定义用户定义函数（UDF）

用例 - 为什么要使用 chDB

根据提供的信息，以下是 chDB 在实际应用中的一些实用用例：

Jupyter Notebook 中的数据分析：chDB 允许用户直接在 Jupyter Notebook 中运行 ClickHouse 查询，使数据科学家和分析师能够方便地处理大型数据集，而无需单独的 ClickHouse 服务器。
应用程序中的嵌入式分析：开发人员可以将 chDB 嵌入到他们的应用程序中，以提供快速的分析功能，而无需单独的数据库服务器。
在本地处理大型数据集：chDB 允许在本地机器上处理大型数据集（例如 Parquet、CSV、JSON 文件），而无需设置完整的数据库系统。
快速原型设计：数据工程师和分析师可以快速对大型数据集进行原型设计和查询测试，而无需部署完整的 ClickHouse 集群。
无服务器环境：chDB 可用于无服务器函数中，以提供分析功能，而无需管理数据库基础设施。
数据管道处理：chDB 可以集成到数据管道中，用于快速的数据转换和聚合。

演示 - Google Colab

Google Colab

Ubuntu TechHive

探索 ClickHouse：一款用于 OLAP 和实时分析的高性能列式数据库管理系统