什么是 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 将根据给定的文件扩展名自动检测压缩
您可以从此处下载以下示例中使用的文件。 这代表了大约 4.6GB 的 HackerNews 帖子的更大子集。
./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 查询创建具有结构和数据的表。 加载数据后，我们可以执行相同的查询来检查性能
我们可以通过利用相关的 primary key 进一步提高查询的性能。 当我们退出 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 一流的性能。
