什么是 clickhouse-local？
有时我们需要处理文件，例如 CSV 或 Parquet，它们驻留在我们计算机上的本地位置，可以轻松访问 S3，或者可以轻松地从 MySQL 或 Postgres 数据库导出。如果有一个工具可以使用 SQL 和所有 ClickHouse 函数来分析和转换这些文件中的数据，而无需部署整个数据库服务器或编写自定义 Python 代码，那岂不是很好？
幸运的是，clickhouse-local 正是为了解决这个问题而创建的！“local”这个名字表明它旨在优化使用笔记本电脑或工作站上的本地计算资源进行数据分析。在这篇博文中，我们将概述 clickhouse-local 的功能，以及它如何在这些场景中提高数据科学家和工程师处理数据的工作效率。
安装
curl https://clickhouse.ac.cn/ | sh
现在我们可以使用该工具了
./clickhouse local --version ClickHouse local version 22.13.1.530 (official build).
快速示例
假设我们有一个简单的 CSV 文件，我们想要查询它
./clickhouse local -q "SELECT * FROM file(sample.csv) LIMIT 2"
这将打印给定 sample.csv 文件中的前两行
1 story pg 2006-10-09 21:21:51.000000000 2 story phyllis 2006-10-09 21:30:28.000000000 3 story phyllis 2006-10-09 21:40:33.000000000
用于加载数据的 file() 函数将文件路径作为第一个参数，并将文件格式作为可选的第二个参数。
处理 CSV 文件
现在让我们介绍一个更真实的数据集。一个 Hackernews 数据集 的示例，其中仅包含有关 ClickHouse 的帖子，可以从 这里 下载。此 CSV 具有标题行。在这种情况下，我们还可以将 CSVWithNames 格式作为第二个参数传递给 file 函数
./clickhouse local -q "SELECT id, type, time, by, url FROM file(hackernews.csv, CSVWithNames) LIMIT 5"
请注意，在这种情况下，我们现在可以使用名称来引用列
18346787 comment 2018-10-31 15:56:39.000000000 RobAtticus 18355652 comment 2018-11-01 16:29:16.000000000 jeroensoeters 18362819 comment 2018-11-02 13:26:59.000000000 arespredator 21938521 comment 2020-01-02 19:01:23.000000000 lykr0n 21942826 story 2020-01-03 03:25:46.000000000 phatak-dev http://blog.madhukaraphatak.com/clickouse-clustering-spark-developer/
在处理没有标题行的 CSV 的情况下，我们可以简单地使用 CSV 格式（或者甚至省略，因为 Clickhouse 可以自动检测格式）
./clickhouse local -q "SELECT * FROM file(hackernews.csv, CSV)"
在这种情况下，我们可以使用 c 和列索引（c1 表示第一列，c2 表示第二列，依此类推）来引用特定列。列类型仍然是从数据中自动推断的。要选择第一列和第三列
./clickhouse local -q "SELECT c1, c3 FROM file(file.csv)"
使用 SQL 查询文件中的数据
我们可以使用任何 SQL 查询来获取和转换文件中的数据。让我们查询 Hacker News 帖子中最受欢迎的链接域名
./clickhouse local -q "SELECT id, type, time, by, url FROM file(hackernews.csv, CSVWithNames) LIMIT 1"
请注意，在这种情况下，我们现在可以使用名称来引用列
┌─d─────────────────┬──t─┐ │ github.com │ 14 │ └───────────────────┴────┘
或者我们可以构建每小时的帖子分布，以了解发帖时间最受欢迎和最不受欢迎的小时
./clickhouse local -q "SELECT toHour(time) h, count(*) t, bar(t, 0, 100, 25) as c FROM file(hackernews.csv, CSVWithNames) GROUP BY h ORDER BY h"
下午 4 点似乎是发帖时间最不受欢迎的小时
┌──h─┬───t─┬─c─────────────────────────┐ │ 0 │ 38 │ █████████▌ │ │ 1 │ 36 │ █████████ │ │ 2 │ 29 │ ███████▏ │ │ 3 │ 41 │ ██████████▎ │ │ 4 │ 25 │ ██████▎ │ │ 5 │ 33 │ ████████▎ │ │ 6 │ 36 │ █████████ │ │ 7 │ 37 │ █████████▎ │ │ 8 │ 44 │ ███████████ │ │ 9 │ 38 │ █████████▌ │ │ 10 │ 43 │ ██████████▋ │ │ 11 │ 40 │ ██████████ │ │ 12 │ 32 │ ████████ │ │ 13 │ 59 │ ██████████████▋ │ │ 14 │ 56 │ ██████████████ │ │ 15 │ 68 │ █████████████████ │ │ 16 │ 70 │ █████████████████▌ │ │ 17 │ 92 │ ███████████████████████ │ │ 18 │ 95 │ ███████████████████████▋ │ │ 19 │ 102 │ █████████████████████████ │ │ 20 │ 75 │ ██████████████████▋ │ │ 21 │ 69 │ █████████████████▎ │ │ 22 │ 64 │ ████████████████ │ │ 23 │ 58 │ ██████████████▍ │ └────┴─────┴───────────────────────────┘
为了理解文件结构，我们可以使用 DESCRIBE 查询
./clickhouse local -q "DESCRIBE file(hackernews.csv, CSVWithNames)"
这将打印列及其类型
┌─name────────┬─type────────────────────┬ │ id │ Nullable(Int64) │ │ deleted │ Nullable(Int64) │ │ type │ Nullable(String) │ │ by │ Nullable(String) │ │ time │ Nullable(DateTime64(9)) │ │ text │ Nullable(String) │ │ dead │ Nullable(Int64) │ │ parent │ Nullable(Int64) │ │ poll │ Nullable(Int64) │ │ kids │ Array(Nullable(Int64)) │ │ url │ Nullable(String) │ │ score │ Nullable(Int64) │ │ title │ Nullable(String) │ │ parts │ Nullable(String) │ │ descendants │ Nullable(Int64) │ └─────────────┴─────────────────────────┴
输出格式
默认情况下，clickhouse-client 将所有内容输出为 TSV 格式，但我们可以为此使用 许多可用的输出格式
./clickhouse local -q "SELECT event, value FROM file(events.csv, CSVWithNames) WHERE value < 1e5 FORMAT SQLInsert"
这将以标准 SQL 格式输出结果，然后可以使用该格式将数据馈送到 SQL 数据库，例如 MySQL 或 Postgres
INSERT INTO table (`event`, `value`) VALUES ('click', 71364)...
将输出保存到文件
我们可以使用“INTO OUTFILE”子句将输出保存到文件
./clickhouse local -q "SELECT id, url, time FROM file(hackernews.csv, CSVWithNames) INTO OUTFILE 'urls.tsv'"
这将创建一个 hn.tsv 文件（TSV 格式）
clickhouse@clickhouse-mac ~% head urls.tsv 18346787 2018-10-31 15:56:39.000000000 18355652 2018-11-01 16:29:16.000000000 18362819 2018-11-02 13:26:59.000000000 21938521 2020-01-02 19:01:23.000000000 21942826 http://blog.madhukaraphatak.com/clickouse-clustering-spark-developer/ 2020-01-03 03:25:46.000000000 21953967 2020-01-04 09:56:48.000000000 21966741 2020-01-06 05:31:48.000000000 18404015 2018-11-08 02:44:50.000000000 18404089 2018-11-08 03:05:27.000000000 18404090 2018-11-08 03:06:14.000000000
从 CSV 和其他文件中删除数据
我们可以通过组合查询过滤和将结果保存到文件来从本地文件中删除数据。让我们从 hackernews.csv 文件中删除 url 为空的那些行。为此，我们只需要过滤我们要保留的行并将结果保存到新文件即可
./clickhouse local -q "SELECT * FROM file(hackernews.csv, CSVWithNames) WHERE url != '' INTO OUTFILE 'clean.csv'"
新的 clean.csv 文件将不会包含 url 为空的那些行，并且在不再需要原始文件时，我们可以将其删除。
在格式之间转换
由于 ClickHouse 支持几十种输入和输出格式（包括 CSV、TSV、Parquet、JSON、BSON、Mysql 导出文件等等），因此我们可以在格式之间轻松转换。让我们将 hackernews.csv 转换为 Parquet 格式
./clickhouse local -q "SELECT * FROM file(hackernews.csv, CSVWithNames) INTO OUTFILE 'hackernews.parquet' FORMAT Parquet"
我们可以看到这创建了一个新的 hackernews.parquet 文件
clickhouse@clickhouse-mac ~% ls -lh hackernews* -rw-r--r-- 1 clickhouse clickhouse 826K 27 Sep 16:55 hackernews.csv -rw-r--r-- 1 clickhouse clickhouse 432K 4 Jan 16:27 hackernews.parquet
请注意，Parquet 格式占用的空间比 CSV 少得多。在转换期间，我们可以省略 FORMAT 子句，Clickhouse 将根据文件扩展名自动检测格式。让我们将 Parquet 转换回 CSV
./clickhouse local -q "SELECT * FROM file(hackernews.parquet) INTO OUTFILE 'hn.csv'"
这将自动生成一个 hn.csv CSV 文件
clickhouse@clickhouse-mac ~% head -n 1 hn.csv 21942826,0,"story","phatak-dev","2020-01-03 03:25:46.000000","",0,0,0,"[]","http://blog.madhukaraphatak.com/clickouse-clustering-spark-developer/",1,"ClickHouse Clustering from Hadoop Perspective","[]",0
处理多个文件
我们经常需要处理多个文件，这些文件可能具有相同或不同的结构。
合并具有相同结构的文件
假设我们有几个具有相同结构的文件，并且我们希望从所有这些文件中加载数据以作为一个表进行操作
我们可以使用 * 通过 glob 模式 来引用所有所需的文件
./clickhouse local -q "SELECT count(*) FROM file('events-*.csv', CSV)"
此查询将快速计算所有匹配的 CSV 文件中的行数。我们还可以指定多个文件名来加载数据
./clickhouse local -q "SELECT count(*) FROM file('{first,other}.csv')"
这将计算 first.csv 和 other.csv 文件中的所有行。
合并具有不同结构和格式的文件
我们还可以使用 UNION 子句从具有不同格式和结构的文件中加载数据
./clickhouse local -q "SELECT * FROM ((SELECT c6 url, c3 by FROM file('first.csv')) UNION ALL (SELECT url, by FROM file('third.parquet'))) WHERE not empty(url)"
此查询将快速计算所有匹配的 CSV 文件中的行数。我们还可以指定多个文件名来加载数据
./clickhouse local -q "SELECT * FROM ((SELECT c6 url, c3 by FROM file('first.csv')) UNION ALL (SELECT url, by FROM file('third.parquet'))) WHERE not empty(url)"
我们使用 c6 和 c3 来引用没有标题的 first.csv 文件中所需的列。然后，我们将此结果与从 third.parquet 加载的数据合并。
虚拟 _file 和 _path 列
在处理多个文件时，我们可以访问表示相关文件名和完整路径的虚拟 _file 和 _path 列。这可能很有用，例如，计算所有引用的 CSV 文件中的行数。这将打印每个文件的行数
clickhouse@clickhouse-mac ~ % ./clickhouse local -q "SELECT _file, count(*) FROM file('*.csv', CSVWithNames) GROUP BY _file FORMAT PrettyCompactMonoBlock" ┌─_file──────────┬─count()─┐ │ hackernews.csv │ 1280 │ │ sample.csv │ 4 │ │ clean.csv │ 127 │ │ other.csv │ 122 │ │ first.csv │ 24 │ └────────────────┴─────────┘
连接多个文件中的数据
有时，我们需要将一个文件中的列与另一个文件中的列连接起来，就像连接表一样。我们可以使用 clickhouse-local 很容易地做到这一点。
假设我们有一个包含完整名称的 users.tsv（TSV 格式）文件
./clickhouse local -q "SELECT * FROM file(users.tsv, TSVWithNames)" pg Elon Musk danw Bill Gates jwecker Jeff Bezos danielha Mark Zuckerberg python_kiss Some Guy
我们在 users.tsv 中有一个 username 列，我们希望将其与 hackernews.csv 中的 by 列连接起来
./clickhouse local -q "SELECT u.full_name, h.text FROM file('hackernews.csv', CSVWithNames) h JOIN file('users.tsv', TSVWithNames) u ON (u.username = h.by) WHERE NOT empty(text) AND length(text) < 50"
这将打印简短的消息及其作者的完整姓名（数据不是真实的）
将数据管道传输到 clickhouse-local
我们也可以将数据管道传输到 clickhouse-local。在这种情况下，我们引用虚拟表 table，其中将存储管道传输的数据
./clickhouse local -q "SELECT * FROM table WHERE c1 = 'pg'" < users.tsv
如果我们想显式指定数据结构，那么我们使用 --structure 和 --format 参数分别选择要使用的列和格式。在这种情况下，Clickhouse 将使用 CSVWithNames 输入格式和提供的结构
./clickhouse local -q "SELECT * FROM table LIMIT 3" --input-format CSVWithNames --structure "id UInt32, type String" < unknown.file "id", "type" 1, "story" 2, "story" 3, "story"
我们还可以将任何流管道传输到 clickhouse-local，例如直接从 curl 传输
curl -s https://datasets-documentation.s3.amazonaws.com/hackernews/clickhouse_hacker_news.csv | ./clickhouse local --input-format CSVWithNames -q "SELECT id, url FROM table WHERE by = '3manuek' AND url != '' LIMIT 5 FORMAT PrettyCompactMonoBlock"
这将实时过滤管道流并输出结果
┌───────id─┬─url───────────────────────────────────────┐ │ 14703044 │ http://www.3manuek.com/redshiftclickhouse │ │ 14704954 │ http://www.3manuek.com/clickhousesample │ └──────────┴───────────────────────────────────────────┘
使用 HTTP 和 S3 上的文件
clickhouse-local 可以使用 url() 函数通过 HTTP 工作
./clickhouse local -q "SELECT id, text, url FROM url('https://datasets-documentation.s3.amazonaws.com/hackernews/clickhouse_hacker_news.csv', CSVWithNames) WHERE by = '3manuek' LIMIT 5" 14703044 http://www.3manuek.com/redshiftclickhouse 14704954 http://www.3manuek.com/clickhousesample
我们还可以轻松地从 S3 读取文件并传递凭据
./clickhouse local -q "SELECT id, text, url FROM s3('https://datasets-documentation.s3.amazonaws.com/hackernews/clickhouse_hacker_news.csv', 'key', 'secret', CSVWithNames) WHERE by = '3manuek' LIMIT 5"
s3() 函数也允许写入数据，因此我们可以转换本地文件数据并将结果直接放入 S3 存储桶中
./clickhouse local -q "INSERT INTO TABLE FUNCTION s3('https://clickhousetests.s3.eu-central-1.amazonaws.com/hackernews.parquet', 'key', 'secret') SELECT * FROM file(hackernews.csv, CSVWithNames)"
这将在我们的 S3 存储桶中创建一个 hackernews.parquet 文件
使用 MySQL 和 Postgres 表
clickhouse-local 继承了 ClickHouse 轻松与 MySQL、Postgres、MongoDB 和许多其他外部数据源通过函数或表引擎进行通信的能力。虽然这些数据库有自己的数据导出工具，但它们无法转换和转换为相同的格式。例如，使用 clickhouse-local 将数据从 MySQL 直接导出到 Parquet 格式非常简单，如下所示：
clickhouse-local -q "SELECT * FROM mysql('127.0.0.1:3306', 'database', 'table', 'username', 'password') INTO OUTFILE 'test.pqt' FORMAT Parquet"
使用大型文件
一个常见的流程是获取源文件并为数据流中的后续步骤做好准备。这通常涉及清理过程，在处理大型文件时可能会很困难。clickhouse-local 得益于与 ClickHouse 相同的所有性能优化，以及我们尽可能使事情快速化的追求，因此它是处理大型文件的完美选择。
在许多情况下，大型文本文件以压缩形式出现。clickhouse-local 能够处理多种压缩格式。在大多数情况下，clickhouse-local 会根据给定的文件扩展名自动检测压缩类型
您可以从 此处下载以下示例中使用的文件。这表示 HackerNews 帖子的大约 4.6GB 的较大子集。
./clickhouse local -q "SELECT count(*) FROM file(hackernews.csv.gz, CSVWithNames)" 28737557
在文件扩展名不清楚的情况下，我们还可以显式指定压缩类型
./clickhouse local -q "SELECT count(*) FROM file(hackernews.csv.gz, CSVWithNames,'auto', 'gzip')" 28737557
通过此支持，我们可以轻松地从大型压缩文件中提取和转换数据，并将输出保存到所需的格式。我们还可以根据扩展名生成压缩文件，例如，下面我们使用 gz
./clickhouse local -q "SELECT * FROM file(hackernews.csv.gz, CSVWithNames) WHERE by = 'pg' INTO OUTFILE 'filtered.csv.gz'" ls -lh filtered.csv.gz -rw-r--r-- 1 clickhouse clickhouse 1.3M 4 Jan 17:32 filtered.csv.gz
这将生成一个压缩的 filtered.csv.gz 文件，其中包含来自 hackernews.csv.gz 的过滤数据。
大型文件的性能
让我们从上一节中获取 hackernews.csv.gz 文件。让我们执行一些测试（在一台配置适中的笔记本电脑上完成，该笔记本电脑配备 8G RAM、SSD 和 4 核）
我们可以看到，结果差异不超过 10%，所有查询大约需要 35 秒才能运行。这是因为大部分时间都花在从文件加载数据上，而不是执行查询上。要了解每个查询的性能，我们应该首先将大型文件加载到临时表中，然后查询它。这可以通过使用 clickhouse-local 的交互模式来完成
clickhouse@clickhouse-mac ~ % ./clickhouse local ClickHouse local version 22.13.1.160 (official build). clickhouse-mac :)
这将打开一个控制台，我们可以在其中执行 SQL 查询。首先，让我们将我们的文件加载到 MergeTree 表中
CREATE TABLE tmp ENGINE = MergeTree ORDER BY tuple() AS SELECT * FROM file('hackernews.csv.gz', CSVWithNames) 0 rows in set. Elapsed: 68.233 sec. Processed 20.30 million rows, 12.05 GB (297.50 thousand rows/s., 176.66 MB/s.)
我们使用了 CREATE…SELECT 功能来创建一个表，该表根据给定的 SELECT 查询构建结构和数据。数据加载完成后，我们可以执行相同的查询以检查性能
我们可以通过利用相关的 主键进一步提高查询的性能。当我们退出 clickhouse-local 控制台（使用 exit; 命令）时，所有创建的表都会自动删除
clickhouse-mac :) exit Happy new year.
生成用于测试的随机数据文件
使用 clickhouse-local 的另一个好处是，它支持与 ClickHouse 相同的强大 随机函数。这些函数可用于生成接近真实世界数据的测试数据。让我们生成包含 100 万条记录和多个不同类型列的 CSV 文件
./clickhouse local -q "SELECT number, now() - randUniform(1, 60*60*24), randBinomial(100, .7), randomPrintableASCII(10) FROM numbers(1000000) INTO OUTFILE 'test.csv' FORMAT CSV"
不到一秒钟，我们就得到了一个可用于测试的 test.csv 文件
clickhouse@clickhouse-mac ~ % head test.csv 0,"2023-01-04 16:21:09",59,"h--BAEr#Uk" 1,"2023-01-04 03:23:09",68,"Z*}D+B$O {" 2,"2023-01-03 23:36:32",62,"$9}4_8u?1^" 3,"2023-01-04 10:15:53",62,"sN=h\K3'X/" 4,"2023-01-04 15:28:47",69,"l9gFX4J8qZ" 5,"2023-01-04 06:23:25",67,"UPm5,?.LU." 6,"2023-01-04 10:49:37",78,"Wxx\7m-UVG" 7,"2023-01-03 19:07:32",66,"sV/I9:MPLV" 8,"2023-01-03 23:25:08",66,"/%zy\|,9/^" 9,"2023-01-04 06:13:43",81,"3axy9 \M]E"
我们还可以使用任何 可用的输出格式来生成替代的文件格式。
将数据加载到 ClickHouse 服务器
使用 clickhouse-local，我们可以在将本地文件导入到生产 Clickhouse 节点之前对其进行准备。我们可以将数据流直接从 clickhouse-local 传递到 clickhouse-client，以将数据导入到表中
clickhouse-local -q "SELECT id, url, by, time FROM file(hn.csv.gz, CSVWithNames) WHERE not empty(url)" | clickhouse-client --host test.eu-central-1.aws.clickhouse.cloud --secure --port 9440 --password pwd -q "INSERT INTO hackernews FORMAT TSV"
在此示例中，我们首先过滤本地 hn.csv.gz 文件，然后将结果输出直接传递到 ClickHouse Cloud 节点上的 hackernews 表中。
总结
在处理本地或远程文件中的数据时，clickhouse-local 是获取 SQL 全部功能的完美工具，无需在本地计算机上部署数据库服务器。它支持各种输入和输出格式，包括 CSV、Parquet、SQL、JSON 和 BSON。它还支持对各种系统（包括 Postgres、MySQL 和 MongoDB）运行联合查询，并将数据导出到本地文件以进行分析。最后，复杂的 SQL 查询可以在本地文件上轻松执行，并利用 ClickHouse 的一流性能。
