网站设计论文致谢,网站制作复杂吗,php红酒网站建设,网站维护推广表官网 ClickHouse release 24.1, 2024-01-30 以毫秒为单位查询数十亿行 ClickHouse是用于实时应用和分析的最快、资源效率最高的开源数据库。
安装ClickHouse
使用ClickHouse#xff0c;你有三个选择:
ClickHouse云:官方ClickHouse作为一项服务#xff0c;-由ClickHouse的创…官网 ClickHouse release 24.1, 2024-01-30 以毫秒为单位查询数十亿行 ClickHouse是用于实时应用和分析的最快、资源效率最高的开源数据库。
安装ClickHouse
使用ClickHouse你有三个选择:
ClickHouse云:官方ClickHouse作为一项服务-由ClickHouse的创建者构建维护和支持快速安装:一个易于下载的二进制测试和开发与ClickHouse生产部署:ClickHouse可以运行在任何Linux, FreeBSD或macOS与x86-64, ARM或PowerPC64LE CPU架构Docker镜像:使用Docker Hub中的官方Docker镜像
1、什么是ClickHouse?
ClickHouse®是一个高性能面向列的SQL数据库管理系统(DBMS)用于在线分析处理(OLAP)。它既可以作为开源软件也可以作为云服务。
1.1 什么是OLAP?
OLAP场景需要在大型数据集之上的实时响应用于具有以下特征的复杂分析查询:
数据集可以是巨大的——数十亿或数万亿行数据组织在包含许多列的表中只选择几个列来回答任何特定的查询结果必须以毫秒或秒为单位返回
1.2 面向列与面向行数据库
在面向行的DBMS中数据以行为单位存储与一行相关的所有值在物理上彼此相邻存储。
在面向列的DBMS中数据存储在列中来自相同列的值存储在一起。
1.3 为什么面向列的数据库在OLAP场景中工作得更好
面向列的数据库更适合OLAP场景:它们在处理大多数查询时至少快100倍
1.4 为什么ClickHouse这么快?
ClickHouse使用所有可用的系统资源来充分发挥其潜力以尽可能快地处理每个分析查询。这是由于分析功能和对实现最快的OLAP数据库所需的底层细节的关注的独特组合而成为可能的。
深入了解这个主题的有用文章包括:
ClickHouse PerformanceDistinctive Features of ClickHouseFAQ: Why is ClickHouse so fast?
1.5 实时处理分析查询
在面向行的DBMS中数据是按以下顺序存储的: 换句话说与一行相关的所有值在物理上都是挨个存储的。
面向行的DBMS的例子有MySQL、Postgres和MS SQL Server。
在面向列的DBMS中数据是这样存储的: 这些例子只显示了数据排列的顺序。来自不同列的值被分开存储来自同一列的数据被一起存储。
面向列DBMS的例子:Vertica, Paraccel (Actian Matrix和Amazon Redshift) Sybase IQ, Exasol, Infobright, InfiniDB, MonetDB (VectorWise和Actian Vector) LucidDB, SAP HANA, Google Dremel, Google PowerDrill, Druid和kdb。
不同的数据存储顺序更适合不同的场景。数据访问场景指的是进行什么查询、查询的频率和比例;每种查询类型要读取多少数据——行、列和字节;读取和更新数据的关系;数据的工作大小以及如何在本地使用它;是否使用事务以及它们的隔离程度;数据复制和逻辑完整性要求;对每种查询类型的延迟和吞吐量的需求等等。
系统负载越高定制系统设置以匹配使用场景的需求就越重要定制的粒度也就越细。没有一个系统能同样适合于不同的场景。如果系统能够适应广泛的场景那么在高负载下系统处理所有场景的能力都很差或者只能很好地处理一个或几个可能的场景。
OLAP场景的关键属性
表是“宽”的这意味着它们包含大量的列。数据集很大查询在处理单个查询时需要高吞吐量(每台服务器每秒多达数十亿行)。列值相当小:数字和短字符串(例如每个URL 60字节)。查询提取大量行但只提取一小部分列。对于简单的查询允许大约50毫秒的延迟。每个查询都有一个大表;所有的表都很小除了一张。查询结果明显小于源数据。换句话说数据被过滤或聚合因此结果适合单个服务器的RAM。查询相对较少(通常每个服务器每秒有数百个或更少的查询)。插入以相当大的批量( 1000行)进行而不是以单行进行。事务是不必要的。
很容易看出OLAP场景与其他流行的场景(如OLTP或Key-Value访问)非常不同。因此如果您想获得良好的性能尝试使用OLTP或Key-Value DB来处理分析查询是没有意义的。例如如果您尝试使用MongoDB或Redis进行分析那么与OLAP数据库相比您将获得非常差的性能。
输入输出
1对于分析查询只需要读取少量的表列。在面向列的数据库中您可以只读取所需的数据。例如如果您需要100列中的5列那么I/O可以减少20倍。 2由于数据是以块packets的形式读取的因此更容易压缩。列中的数据也更容易压缩。这进一步减少了I/O量。 3由于减少了I/O更多的数据可以放入系统缓存。
例如查询“计数每个广告平台的记录数”需要读取一个“广告平台ID”列该列未压缩占用1字节。如果大多数流量不是来自广告平台你可以预期这个专栏的压缩至少是原来的10倍。当使用快速压缩算法时可以以每秒至少几gb的未压缩数据的速度进行数据解压缩。换句话说该查询可以在单个服务器上以大约每秒几十亿行的速度处理。这个速度实际上是在实践中达到的。
CPU
由于执行查询需要处理大量行因此可以为整个向量vectors 而不是单独的行分派所有操作或者实现查询引擎以便几乎没有分派成本。如果不这样做对于任何不太好的磁盘子系统查询解释器不可避免地会使CPU停滞。在列中存储数据和在可能的情况下按列处理数据是有意义的。
有两种方法: 1矢量引擎。所有的操作都是针对向量编写的而不是针对单独的值。这意味着您不需要经常调用操作并且调度成本可以忽略不计。操作代码包含一个优化的内部循环。
2代码生成。为查询生成的代码包含所有间接调用。
在面向行的数据库中不会这样做因为在运行简单查询时这样做没有意义。然而也有例外。例如MemSQL使用代码生成来减少处理SQL查询时的延迟。(相比之下分析型DBMS需要优化吞吐量而不是延迟。)
注意为了提高CPU效率查询语言必须是声明式的(SQL或MDX)或者至少是向量(J, K)。查询应该只包含隐式循环以便进行优化。
2、Quick Start
2.1 下载二进制文件
ClickHouse本机运行在Linux、FreeBSD和macOS上并通过WSL运行在Windows上。在本地下载ClickHouse的最简单方法是运行以下curl命令。它决定你的操作系统是否被支持然后下载一个合适的ClickHouse二进制文件:
curl https://clickhouse.com/ | sh# 安装./clickhouse installStart clickhouse-server with:sudo clickhouse startStart clickhouse-client with:clickhouse-client --password2.2 启动服务器
运行如下命令启动ClickHouse服务:
./clickhouse server2.3 启动客户端
使用ClickHouse -client连接到ClickHouse服务。打开一个新的终端更改clickhouse二进制文件的保存目录并运行以下命令:
./clickhouse client当它连接到本地主机上运行的服务时你应该看到一个笑脸:
my-host :)2.4 创建表
使用CREATE TABLE定义一个新表。典型的SQL DDL命令与一个添加可以工作在ClickHouse-表在ClickHouse需要一个ENGINE子句。使用MergeTree来利用ClickHouse的性能优势:
CREATE TABLE my_first_table
(user_id UInt32,message String,timestamp DateTime,metric Float32
)
ENGINE MergeTree
PRIMARY KEY (user_id, timestamp)2.5 插入数据
您可以在ClickHouse中使用熟悉的INSERT INTO TABLE命令但重要的是要了解每次插入到MergeTree表中都会导致在存储中创建一个part (文件夹)。为了最小化parts一次批量插入许多行(一次插入数万甚至数百万行)。
INSERT INTO my_first_table (user_id, message, timestamp, metric) VALUES(101, Hello, ClickHouse!, now(), -1.0 ),(102, Insert a lot of rows per batch, yesterday(), 1.41421 ),(102, Sort your data based on your commonly-used queries, today(), 2.718 ),(101, Granules are the smallest chunks of data read, now() 5, 3.14159 )2.6 查询新表
你可以写一个SELECT查询就像你会用任何SQL数据库: SELECT *FROM my_first_tableORDER BY timestamp注意响应以一个漂亮的表格格式返回:
2.7 插入自己的数据
下一步是将你当前的数据输入ClickHouse。我们有很多表函数和集成来获取数据。我们在下面的选项卡中有一些例子或者查看我们的集成列表了解与ClickHouse集成的一长串技术。
下一步
查看高级教程深入了解ClickHouse的关键概念和功能通过参加ClickHouse Academy的免费点播培训课程继续学习我们有一个示例数据集的列表并说明如何插入它们如果您的数据来自外部源请查看我们的集成指南集了解如何连接到消息队列、数据库、管道等如果您使用的是UI/BI可视化工具请查看连接UI到ClickHouse的-用户指南关于主键的用户指南是您需要了解的关于主键以及如何定义它们的所有内容
3、高级教程
在本教程中您将创建一个表并插入一个大型数据集(200万行纽约出租车数据)。然后您将在数据集上运行查询包括如何创建字典并使用它执行JOIN的示例。
3.1 创建一个新表
纽约市的出租车数据包含数百万次出租车出行的详细信息包括接送时间和地点、成本、小费金额、通行费、付款类型等列。让我们创建一个表来存储这些数据…
连接到SQL控制台
在default数据库中创建如下trips表:
CREATE TABLE trips
(trip_id UInt32,vendor_id Enum8(1 1, 2 2, 3 3, 4 4, CMT 5, VTS 6, DDS 7, B02512 10, B02598 11, B02617 12, B02682 13, B02764 14, 15),pickup_date Date,pickup_datetime DateTime,dropoff_date Date,dropoff_datetime DateTime,store_and_fwd_flag UInt8,rate_code_id UInt8,pickup_longitude Float64,pickup_latitude Float64,dropoff_longitude Float64,dropoff_latitude Float64,passenger_count UInt8,trip_distance Float64,fare_amount Float32,extra Float32,mta_tax Float32,tip_amount Float32,tolls_amount Float32,ehail_fee Float32,improvement_surcharge Float32,total_amount Float32,payment_type Enum8(UNK 0, CSH 1, CRE 2, NOC 3, DIS 4),trip_type UInt8,pickup FixedString(25),dropoff FixedString(25),cab_type Enum8(yellow 1, green 2, uber 3),pickup_nyct2010_gid Int8,pickup_ctlabel Float32,pickup_borocode Int8,pickup_ct2010 String,pickup_boroct2010 String,pickup_cdeligibil String,pickup_ntacode FixedString(4),pickup_ntaname String,pickup_puma UInt16,dropoff_nyct2010_gid UInt8,dropoff_ctlabel Float32,dropoff_borocode UInt8,dropoff_ct2010 String,dropoff_boroct2010 String,dropoff_cdeligibil String,dropoff_ntacode FixedString(4),dropoff_ntaname String,dropoff_puma UInt16
)
ENGINE MergeTree
PARTITION BY toYYYYMM(pickup_date)
ORDER BY pickup_datetime;3.2 插入数据集
现在已经创建了一个表让我们添加NYC出租车数据。它在S3中的CSV文件中您可以从那里加载数据。
1下面的命令将来自S3中两个不同文件的~2,000,000行插入到trips表中: trips_1.tsv.gz and trips_2.tsv.gz
INSERT INTO trips
SELECT * FROM s3(https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_{1..2}.gz,TabSeparatedWithNames, trip_id UInt32,vendor_id Enum8(1 1, 2 2, 3 3, 4 4, CMT 5, VTS 6, DDS 7, B02512 10, B02598 11, B02617 12, B02682 13, B02764 14, 15),pickup_date Date,pickup_datetime DateTime,dropoff_date Date,dropoff_datetime DateTime,store_and_fwd_flag UInt8,rate_code_id UInt8,pickup_longitude Float64,pickup_latitude Float64,dropoff_longitude Float64,dropoff_latitude Float64,passenger_count UInt8,trip_distance Float64,fare_amount Float32,extra Float32,mta_tax Float32,tip_amount Float32,tolls_amount Float32,ehail_fee Float32,improvement_surcharge Float32,total_amount Float32,payment_type Enum8(UNK 0, CSH 1, CRE 2, NOC 3, DIS 4),trip_type UInt8,pickup FixedString(25),dropoff FixedString(25),cab_type Enum8(yellow 1, green 2, uber 3),pickup_nyct2010_gid Int8,pickup_ctlabel Float32,pickup_borocode Int8,pickup_ct2010 String,pickup_boroct2010 String,pickup_cdeligibil String,pickup_ntacode FixedString(4),pickup_ntaname String,pickup_puma UInt16,dropoff_nyct2010_gid UInt8,dropoff_ctlabel Float32,dropoff_borocode UInt8,dropoff_ct2010 String,dropoff_boroct2010 String,dropoff_cdeligibil String,dropoff_ntacode FixedString(4),dropoff_ntaname String,dropoff_puma UInt16
) SETTINGS input_format_try_infer_datetimes 02等待INSERT操作完成——下载150mb的数据可能需要一些时间。 s3函数很聪明地知道如何解压缩数据TabSeparatedWithNames格式告诉ClickHouse数据是用制表符分隔的并跳过每个文件的标题行。 3当插入完成后验证它是否工作:
SELECT count() FROM trips您应该看到大约2M行(准确地说是1,999,657行)。 注意ClickHouse需要处理的行数和速度有多快?只需处理6行就可以在0.001秒内得到计数。(6恰好是trips表当前拥有的parts 数部件知道它们有多少行。)
4如果你运行一个需要访问每一行的查询你会注意到需要处理更多的行但运行时间仍然非常快:
SELECT DISTINCT(pickup_ntaname) FROM trips该查询必须处理2M行并返回190个值但请注意它在大约1秒内完成了此操作。pickup_ntaname列表示出租车在纽约市的开始地。
3.3 分析数据
让我们运行一些查询来分析这2M行数据…
1我们将从一些简单的计算开始比如计算平均小费金额:
SELECT round(avg(tip_amount), 2) FROM trips2下面的查询基于乘客数量计算平均成本:
SELECTpassenger_count,ceil(avg(total_amount),2) AS average_total_amount
FROM trips
GROUP BY passenger_count3下面是一个查询计算每个社区每天的接送次数:
SELECTpickup_date,pickup_ntaname,SUM(1) AS number_of_trips
FROM trips
GROUP BY pickup_date, pickup_ntaname
ORDER BY pickup_date ASC4该查询计算行程长度并根据该值对结果进行分组:
SELECTavg(tip_amount) AS avg_tip,avg(fare_amount) AS avg_fare,avg(passenger_count) AS avg_passenger,count() AS count,truncate(date_diff(second, pickup_datetime, dropoff_datetime)/60) as trip_minutes
FROM trips
WHERE trip_minutes 0
GROUP BY trip_minutes
ORDER BY trip_minutes DESC5这个查询显示了每个社区的接送次数按小时分列:
SELECTpickup_ntaname,toHour(pickup_datetime) as pickup_hour,SUM(1) AS pickups
FROM trips
WHERE pickup_ntaname !
GROUP BY pickup_ntaname, pickup_hour
ORDER BY pickup_ntaname, pickup_hour3.4 创建字典
如果你是ClickHouse的新手了解字典dictionaries的工作原理是很重要的。考虑字典的一种简单方法是存储在内存中的键-值对的映射。详细信息和字典的所有选项链接在本教程的末尾。
1让我们看看如何在ClickHouse服务中创建与表关联的字典。表和字典将基于包含265行的CSV文件其中一行代表纽约市的每个社区。这些社区被映射到纽约市的行政区名称(纽约市有5个行政区:布朗克斯、布鲁克林、曼哈顿、皇后区和斯塔顿岛)这个文件也将纽瓦克机场(EWR)列为一个行政区。
这是CSV文件的一部分(为清晰起见显示为表格)。文件中的LocationID列映射到trips表中的pickup_nyct2010_gid和dropff_nyct2010_gid列: 2该文件的URL为https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv。运行以下SQL语句创建一个名为taxi_zone_dictionary的字典并从S3中的CSV文件填充字典:
CREATE DICTIONARY taxi_zone_dictionary
(LocationID UInt16 DEFAULT 0,Borough String,Zone String,service_zone String
)
PRIMARY KEY LocationID
SOURCE(HTTP(URL https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv FORMAT CSVWithNames))
LIFETIME(MIN 0 MAX 0)
LAYOUT(HASHED_ARRAY())将LIFETIME设置为0意味着此字典将永远不会更新其源。这里使用它来避免向S3存储桶发送不必要的流量但通常您可以指定任何您喜欢的生命周期值。 例如: LIFETIME(MIN 1 MAX 10) 指定要在1到10秒之间的随机时间之后更新的字典。(当在大量服务器上更新时为了在字典源上分配负载随机时间是必要的。) 3验证它的工作-你应该得到265行(每个邻居一行):
SELECT * FROM taxi_zone_dictionary4使用dictGet 函数(或其变体)从字典中检索值。传入字典的名称、所需的值和键(在我们的示例中是taxxi_zone_dictionary的LocationID列)。
例如下面的查询返回LocationID为132的Borough(如上所示为JFK机场):
SELECT dictGet(taxi_zone_dictionary, Borough, 132)5使用dictHas函数查看字典中是否存在键。例如下面的查询返回1(在ClickHouse中为“true”):
SELECT dictHas(taxi_zone_dictionary, 132)6下面的查询返回0因为4567不是字典中LocationID的值:
SELECT dictHas(taxi_zone_dictionary, 4567)7使用dictGet 函数在查询中检索市镇的名称。例如:
SELECTcount(1) AS total,dictGetOrDefault(taxi_zone_dictionary,Borough, toUInt64(pickup_nyct2010_gid), Unknown) AS borough_name
FROM trips
WHERE dropoff_nyct2010_gid 132 OR dropoff_nyct2010_gid 138
GROUP BY borough_name
ORDER BY total DESC这个查询汇总了每个行政区在拉瓜迪亚机场或肯尼迪机场结束的出租车次数。结果如下所示请注意有相当多的行程的落点是未知的: 3.5 执行 Join
让我们编写一些查询将tax_zone_dictionary与trips表连接起来。
1我们可以从一个简单的JOIN开始它的作用类似于上面的机场查询:
SELECTcount(1) AS total,Borough
FROM trips
JOIN taxi_zone_dictionary ON toUInt64(trips.pickup_nyct2010_gid) taxi_zone_dictionary.LocationID
WHERE dropoff_nyct2010_gid 132 OR dropoff_nyct2010_gid 138
GROUP BY Borough
ORDER BY total DESC注意上面JOIN查询的输出与之前使用dictGetOrDefault的查询相同(只是不包括Unknown值)。在幕后ClickHouse实际上调用了taxxi_zone_dictionary字典的dictGet函数但是SQL开发人员更熟悉JOIN语法。 2我们不经常在ClickHouse中使用SELECT * -你应该只检索你实际需要的列!但是很难找到一个花费很长时间的查询所以这个查询故意选择每一列并返回每一行(除了在默认情况下响应中有一个内置的10,000行最大值)并且还使用字典对每一行进行右连接:
SELECT *
FROM trips
JOIN taxi_zone_dictionaryON trips.dropoff_nyct2010_gid taxi_zone_dictionary.LocationID
WHERE tip_amount 0
ORDER BY tip_amount DESC
LIMIT 1000恭喜! 做得好——你完成了教程希望你对如何使用ClickHouse有了更好的理解。下面是接下来该怎么做的一些选择:
阅读ClickHouse的主键是如何工作的——这些知识将使你在成为ClickHouse专家的过程中有很长的路要走集成外部数据源如文件、Kafka、PostgreSQL、数据管道或许多其他数据源连接您最喜欢的UI/BI工具到ClickHouse查看SQL参考并浏览各种函数。ClickHouse有一个惊人的功能集合用于转换处理和分析数据了解更多字典
Glossary
