此博客文章是系列文章的一部分
这篇文章继续了“将数据导入 ClickHouse”系列。在我们之前的文章中，我们展示了加载 Hacker News 数据集的基本知识，并进行了关于 JSON 的小旁路。在这篇文章中，我们将继续探索将数据导入 ClickHouse 的方法，重点介绍如何将 Amazon S3 存储用作数据源和可查询的数据存储库本身。在这篇文章中，我们还将探索一个新的数据集：过去 20 年的汇率数据。它具有微不足道的模式，但在其属性方面很有用，让我们可以探索窗口函数。
Amazon S3 或 Amazon 简单存储服务是由 Amazon Web Services 提供的一种服务，它通过 Web 服务接口提供对象存储。在过去 10 年中，它已经成为几乎无处不在，既用作数据存储层（参见ClickHouse Cloud），也用作文件分发方式。我们越来越多地遇到使用 S3 作为数据湖的 ClickHouse 用户，他们希望将这些数据导入 ClickHouse 或直接查询这些数据。这篇文章展示了如何使用 ClickHouse 实现这两种方法。
虽然我们的示例使用的是ClickHouse Cloud，但这些示例应该适用于自管理实例，并且可以在任何普通的笔记本电脑上运行。
关于外汇的一些信息...
这篇文章不是关于外汇交易，外汇交易是一个复杂的主题，涉及一些复杂的策略！然而，为了让用户了解未来的 ClickHouse 查询和对数据的基本理解，我们简要解释了核心概念。了解外汇交易对于理解本文中的技术方面不是必需的，但可以帮助解释查询背后的动机。
外汇交易是指不同国家货币之间的交易；例如，美元兑欧元。任何货币交易都发生在一对货币之间。购买货币对涉及两方，例如交易者和经纪人。交易者可以从经纪人那里用报价货币购买基础货币，也可以出售基础货币并获得报价货币。这对货币以 X/Y 的格式报价，其中 X 是基础货币，Y 是报价货币。例如，下面的示例数据显示了 2022 年 EUR/USD 对（一个主要货币对）的买入和卖出价格。
鉴于以上内容，卖出报价（或卖出价格）代表经纪人愿意以报价货币接受的每单位基础货币的价格，即他们要求的价格。这是交易者可以开仓买入的价格。
买入报价（或买入/卖出价格）是经纪人愿意以报价货币购买或买入基础货币的价格。交易者以这个价格开仓卖出（做空）。
卖出报价将始终高于买入报价。交易者购买这对货币的价格将始终高于他们能收回的价格。买入和卖出报价之间的差额是点差，实际上是经纪人的利润，也是机构盈利的方式。
例如，考虑来自forex.com 的 2022 年 9 月 2 日的 EUR/USD 对报价。
点和跳动
交易货币对时的最小计量单位是价格点或点。对于大多数主要货币对，这通常是小数点后第四位，或者在某些情况下，是第二位，例如，涉及日元的对。反过来，货币对的价格通常用高一位小数来衡量。请注意，点差也以点来衡量。
跳动记录股票或商品的价格何时变化预定的金额或分数变化，即，当价格上涨或下降特定金额或分数变化时，就会发生跳动。在外汇中，当货币对的买入或卖出价格变化一个点时，就会发生跳动。
数据
本博文使用的数据集是从www.histdata.com 下载的。数据集包含大约 115 亿行/跳动，解压缩后近 600GB，总共 66 个货币对。它涵盖了 2000 年 5 月至 2022 年 8 月期间，但在这段时期内并不平均分布，即后几年的跳动数量明显多于 2000 年，我们将在后面进行说明。这很可能是数据可用性的因素，而不是市场活动的因素，并且事实收集仅仅是“尽力而为”。因此，任何货币的行的数量可能不是衡量交易活动的良好指标。
我们提供了一个用于 ClickHouse 测试的清理版本。希望获得更新版本的数据或持续更新的用户，请咨询www.histdata.com，它以多种格式提供以少量费用付费更新。
原始数据有几种变体，记录此处。我们特别关注提供最高粒度的 Ascii 跳动数据。在这篇文章中，我们将重点关注使用数据的清理版本，并跳过下载和准备步骤。对于感兴趣的人，我们已经记录了下载和清理此数据集的步骤此处。
我们处理后的数据包含 5 列，每行对应一个跳动，包含时间到毫秒级精度、基础货币和报价货币的指示以及卖出和买入报价。
"datetime","bid","ask","base","quote"
"2022-01-02 17:03:54.650",1.1369,1.13776,"EUR","USD"
"2022-01-02 17:04:28.267",1.13689,1.13776,"EUR","USD"
"2022-01-02 17:05:07.458",1.13692,1.13776,"EUR","USD"
"2022-01-02 17:06:51.613",1.13668,1.1369,"EUR","USD"
必须强调的是，跳动数据不代表实际的交易/交换。每秒的交易/交换数量远高于每秒几笔！它也不记录达成协议的价格或交换的货币数量（逻辑上源数据中为 0，因此被忽略）。相反，它仅仅标记了货币对的买入或卖出价格何时变化一个点。
数据本身以两种格式分发：zst 压缩的 csv 和 parquet。每个集合都提供每个月/年的文件，总共 269 个文件。这些集合位于 S3 存储桶 s3://datasets-documentation/forex/parquet/year_month 和 s3://datasets-documentation/forex/csv/year_month/ 下。
在原位查询数据
对于频繁访问的数据集，最好将它们加载到像 ClickHouse 这样的分析数据库中，以便你能够快速查询。但对于不常使用的数据集，有时将它们保留在像 S3 这样的“数据湖”中，并能够在原地对它们运行临时分析查询，会很有用。在 AWS 生态系统中，用户可能熟悉诸如 Amazon Athena之类的技术，该技术提供了使用标准 SQL 直接分析 Amazon S3 中数据的功能。但是，要查询结构化表中的数据，用户还必须使用像 Redshift这样的分析数据仓库，并在这两个工具之间切换。
ClickHouse 使用单一技术解决了这两个需求，简化了使用，并允许用户通过函数选择来选择合适的方法。需要具有无与伦比的查询速度的优化表格式？ClickHouse 提供了多种方法来结构化数据（例如， MergeTree 系列表引擎），以便快速查询。需要在原地查询数据？对于需要不频繁分析的数据集，ClickHouse 提供了使用诸如 S3 表函数之类的函数，直接在外部表 S3 中查询数据的能力，如下所示。
下面我们计算 2021 年每种货币对的滴答次数。请注意，我们能够使用通配符模式来限制文件。
SELECT base, quote, count() AS total FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/forex/csv/year_month/2011*', 'CSVWithNames') GROUP BY base, quote LIMIT 10 FORMAT PrettyMonoBlock ┏━━━━━━┳━━━━━━━┳━━━━━━━━━━┓ ┃ base ┃ quote ┃ total ┃ ┡━━━━━━╇━━━━━━━╇━━━━━━━━━━┩ │ EUR │ NOK │ 3088787 │ ├──────┼───────┼──────────┤ │ USD │ JPY │ 1920648 │ ├──────┼───────┼──────────┤ │ USD │ TRY │ 2442707 │ ├──────┼───────┼──────────┤ │ XAU │ USD │ 10529876 │ ├──────┼───────┼──────────┤ │ USD │ CAD │ 3264491 │ ├──────┼───────┼──────────┤ │ EUR │ PLN │ 1840402 │ ├──────┼───────┼──────────┤ │ EUR │ AUD │ 8072459 │ ├──────┼───────┼──────────┤ │ GRX │ EUR │ 8558052 │ ├──────┼───────┼──────────┤ │ CAD │ JPY │ 5598892 │ ├──────┼───────┼──────────┤ │ BCO │ USD │ 5620577 │ └──────┴───────┴──────────┘ 10 rows in set. Elapsed: 26.290 sec. Processed 423.26 million rows, 11.00 GB (16.10 million rows/s., 418.59 MB/s.)
滴答次数并不是衡量市场活跃度的最佳指标。然而，最常交易的货币对将具有最高的流动性 - 在正常市场条件下，这通常表示为较低的点差。提醒一下，点差是卖出价和买入价之间的差额。让我们计算整个数据集所有货币对的点差。我们依赖于模式推断来避免任何模式规范。
SELECT base, quote, avg(ask - bid) AS spread FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/forex/csv/year_month/*.csv.zst', 'CSVWithNames') GROUP BY base, quote ORDER BY spread ASC LIMIT 10 ┌─base─┬─quote─┬─────────────────spread─┐ │ EUR │ USD │ 0.00009969029669160744 │ │ EUR │ GBP │ 0.00013673935811218818 │ │ AUD │ USD │ 0.00015432083736303172 │ │ NZD │ USD │ 0.0001697723724941787 │ │ EUR │ CHF │ 0.0001715531048879742 │ │ USD │ CAD │ 0.00017623255399539916 │ │ GBP │ USD │ 0.00019109680654318212 │ │ USD │ SGD │ 0.00021710273761740704 │ │ USD │ CHF │ 0.00021764358513112766 │ │ CAD │ CHF │ 0.0002664969070414096 │ └──────┴───────┴────────────────────────┘ 10 rows in set. Elapsed: 582.913 sec. Processed 11.58 billion rows, 509.59 GB (19.87 million rows/s., 874.21 MB/s.)
这与最流行的货币对并不完全相关，主要是因为某些货币使用两位小数的点。虽然在整个数据集上进行查询可能不现实，但我们已经处理了 11.58GB 的数据，并在不到 10 分钟的时间内获得了结果，速度接近每秒 2000 万行。运行此命令的用户可能会注意到我们的吞吐量不断增加。这是数据的一个特性 - 后面的月份数据更多，文件更大，因此从并行化中获益更多。让我们看看我们是否能比这做得更好，然后再尝试一些更有趣的查询。
加快速度
为了获得最佳性能并利用所有机器资源，ClickHouse 尝试尽可能地并行化工作，以流式方式处理文件。对于 S3，这意味着在评估查询之前并行化文件的下载和解析（如果可能，也并行进行） - 这里有一个有趣的代码注释，供技术读者了解更多信息。默认情况下，大多数步骤将利用可用的核心数量。
我们的每个ClickHouse Cloud 节点都有八个核心。通过设置max_download_threads，我们增加了第一阶段的并行化，并以更高的内存消耗为代价，并行下载更多文件。
SELECT base, quote, avg(ask - bid) AS spread FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/forex/csv/year_month/*.csv.zst', 'CSVWithNames') GROUP BY base, quote ORDER BY spread ASC LIMIT 10 SETTINGS max_download_threads = 12 // result omitted for brevity 10 rows in set. Elapsed: 435.508 sec. Processed 11.58 billion rows, 509.59 GB (26.59 million rows/s., 1.17 GB/s.)
线程的任何进一步增加都不太可能等同于性能提升，因为我们会在增加的切换和较差的数据访问模式中受到影响。虽然这将我们的查询速度提高了大约 33%，但它也不是灵丹妙药，我们仍然受到接收查询的单个节点的限制。
ClickHouse Cloud 是一种无服务器产品，它不仅仅是一个负责计算的节点。理想情况下，我们会利用我们集群的所有资源来查询 S3，并在它们之间分配工作。这可以通过简单地使用 s3Cluster 函数并指定集群（在 Cloud 的情况下为默认值）来实现。
SELECT base, quote, avg(ask - bid) AS spread FROM s3Cluster('default', 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/forex/csv/year_month/*.csv.zst', 'CSVWithNames') GROUP BY base, quote ORDER BY spread ASC LIMIT 10 SETTINGS max_download_threads = 12 // result omitted for brevity 10 rows in set. Elapsed: 226.449 sec. Processed 11.58 billion rows, 509.59 GB (51.14 million rows/s., 2.25 GB/s.)
2 倍的速度提升表明我们的集群中有 2 个节点，并且至少最初是线性扩展，这通过一个简单的查询得到证实
SELECT * FROM system.clusters FORMAT Vertical Query id: 280a41fc-3d4d-4539-9947-31e41e2cc4cf Row 1: ────── cluster: default shard_num: 1 shard_weight: 1 replica_num: 1 host_name: c-orange-kq-53-server-0.c-orange-kq-53-server-headless.ns-orange-kq-53.svc.cluster.local host_address: 10.21.142.214 port: 9000 is_local: 0 user: default_database: errors_count: 0 slowdowns_count: 0 estimated_recovery_time: 0 Row 2: ────── cluster: default shard_num: 1 shard_weight: 1 replica_num: 2 host_name: c-orange-kq-53-server-1.c-orange-kq-53-server-headless.ns-orange-kq-53.svc.cluster.local host_address: 10.21.101.87 port: 9000 is_local: 1 user: default_database: errors_count: 0 slowdowns_count: 0 estimated_recovery_time: 0 2 rows in set. Elapsed: 0.001 sec.
进一步优化这超出了本文的范围，是以后的事情，但通过利用集群的全部资源，我们已经设法以每秒 5000 万行的速度查询了超过 500GB 的数据。
一些更有趣的事情
交易者在交易货币对时始终注意点差的扩大。它代表着最大的潜在成本，并且与杠杆结合使用时会导致令人恐惧的追加保证金通知。通常，点差的突然变化会导致灾难，这与世界事件导致的波动和市场流动性不足有关。下面，我们使用窗口函数，查找 EUR/USD 货币对点差的最大日变化。在本例中，我们以 parquet 文件为例。请注意，这并不总是像使用 csv 文件一样有效，因为 parquet 文件无法并行解析，这会稍微降低我们的吞吐量。
SELECT base, quote, day, spread - any(spread) OVER (PARTITION BY base, quote ORDER BY base ASC, quote ASC, day ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS change FROM ( SELECT base, quote, avg(ask - bid) AS spread, day FROM s3Cluster('default', 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/forex/parquet/year_month/*.parquet') WHERE (base = 'EUR') AND (quote = 'USD') GROUP BY base, quote, toYYYYMMDD(datetime) AS day ORDER BY base ASC, quote ASC, day ASC ) ORDER BY change DESC LIMIT 5 ┌─base─┬─quote─┬──────day─┬─────────────────change─┐ │ EUR │ USD │ 20010911 │ 0.0008654604016672505 │ │ EUR │ USD │ 20201225 │ 0.00044359838480680655 │ │ EUR │ USD │ 20161225 │ 0.00022978220019175227 │ │ EUR │ USD │ 20081026 │ 0.00019250897043647882 │ │ EUR │ USD │ 20161009 │ 0.0001777101378453994 │ └──────┴───────┴──────────┴────────────────────────┘ 5 rows in set. Elapsed: 365.092 sec. Processed 11.58 billion rows, 613.82 GB (31.72 million rows/s., 1.68 GB/s.)
2001 年 9 月 11 日可以理解的是非常不稳定，它代表着现代历史上最重大的时刻之一。圣诞节可能看起来不寻常，但市场通常在这一时期变得不稳定，因为交易量很低，导致点差扩大。2008 年的数据点可能与金融危机有关。
如果我们假设一天的最后卖出价是货币对的收盘价（一个近似值），我们可以看到所有货币对中最大变化。 argMax 函数允许我们获得这个价格。下面我们关注 2010 年以来的英镑 - 使用 LIMIT BY 为每种货币对选择一个值。
WITH daily_change AS ( SELECT base, quote, day, close, close - any(close) OVER (PARTITION BY base, quote ORDER BY base ASC, quote ASC, day ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS change FROM ( SELECT base, quote, day, argMax(ask, datetime) AS close FROM s3Cluster('default', 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/forex/csv/year_month/20{1,2}*.csv.zst', 'CSVWithNames') WHERE (quote = 'GBP') OR (base = 'GBP') GROUP BY base, quote, toStartOfDay(datetime) AS day ORDER BY base ASC, quote ASC, day ASC ) ORDER BY base ASC, quote ASC, day ASC ) SELECT base || '/' || quote as pair, day, round(close,3) as close, round(change,3) as change FROM daily_change WHERE day > '2016-01-02 00:00:00' ORDER BY abs(change) DESC LIMIT 1 BY base, quote SETTINGS max_download_threads = 12 ┌─pair────┬─────────────────day─┬────close─┬───change─┐ │ UKX/GBP │ 2020-03-15 00:00:00 │ 5218.507 │ -465.515 │ │ XAU/GBP │ 2016-06-23 00:00:00 │ 994.92 │ 139.81 │ │ GBP/JPY │ 2016-06-23 00:00:00 │ 135.654 │ -19.225 │ │ GBP/NZD │ 2016-06-23 00:00:00 │ 1.92 │ -0.141 │ │ GBP/CAD │ 2016-06-23 00:00:00 │ 1.758 │ -0.138 │ │ GBP/USD │ 2016-06-23 00:00:00 │ 1.345 │ -0.135 │ │ GBP/AUD │ 2016-06-23 00:00:00 │ 1.833 │ -0.135 │ │ GBP/CHF │ 2016-06-23 00:00:00 │ 1.312 │ -0.107 │ │ EUR/GBP │ 2016-06-23 00:00:00 │ 0.817 │ 0.05 │ └─────────┴─────────────────────┴──────────┴──────────┘ 9 rows in set. Elapsed: 236.885 sec. Processed 10.88 billion rows, 478.74 GB (45.93 million rows/s., 2.02 GB/s.)
不出所料，英国退欧公投是交易英镑的激动人心的日子！
我们的数据集还包括跟踪布伦特原油价格的 BCO/USD 货币对。石油和货币具有内在联系，其中一个的变化会对另一个产生重大影响。这种相关性在美元货币对中更为明显，其中配对货币与依赖大量原油出口的经济体相关联。像日元这样的货币，与更多样化的经济体相关联，通常具有较弱的相关性。我们可以使用 ClickHouse 中的相关系数 函数来证实这一假设。以下查询计算了挪威克朗 (NOK)、加拿大元 (CAD) 和日元 (JPY) 的价格与 BCO 价格之间的相关性。这些货币代表了各种经济体对原油的依赖程度 - 挪威的依赖程度最高，日本的依赖程度最低。请注意我们的通配符模式意味着我们只分析 2011 年之后的文件 - 这是 BCO 数据可用的最早日期。
SELECT corr(bco, cad), corr(bco, nok), corr(bco, jpy) FROM ( SELECT day, anyIf(close, base = 'BCO') AS bco, anyIf(close, quote = 'CAD') AS cad, anyIf(close, quote = 'NOK') AS nok, anyIf(close, quote = 'JPY') AS jpy FROM ( SELECT day, base, quote, argMax(ask, datetime) AS close FROM s3Cluster('default', 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/forex/csv/year_month/20{1,2}{1,2}*.csv.zst', 'CSVWithNames') WHERE (datetime > '2011-01-01 00:00:00') AND (((base = 'USD') AND ((quote = 'CAD') OR (quote = 'NOK') OR (quote = 'JPY'))) OR (base = 'BCO')) GROUP BY toStartOfDay(datetime) AS day, quote, base ORDER BY day ASC, base ASC ) GROUP BY day ORDER BY day ASC ) ┌─corr(bco, cad)─┬─corr(bco, nok)─┬─corr(bco, jpy)─┐ │ -0.82993394 │ -0.7567768 │ -0.55350846 │ └────────────────┴────────────────┴────────────────┘ 1 row in set. Elapsed: 104.829 sec. Processed 3.39 billion rows, 149.33 GB (32.37 million rows/s., 1.42 GB/s.)
正如预期的那样，挪威克朗和加拿大元与油价紧密相关，而 BCO 的变化对日元的影响较小。
虽然我们可以继续在原地查询数据，但你可能已经注意到，查询时间使得任何分析都非常耗时。更实时的分析需要我们将数据插入到我们的 ClickHouse 节点中。
使用 S3 作为源
之前的查询使用 ClickHouse 的 S3 函数在原地查询数据。这提供了灵活性，对于临时分析很有用。所有 S3 查询的一个共同主题是需要对整个数据集进行线性扫描。我们没有索引，也不能优化我们的查询，除了通过命名组织我们的文件，并使用通配符模式进行限制之外。
在某种程度上，用户愿意支付额外的存储成本，以换取将数据插入 ClickHouse 后显著提高的查询性能（以及可能的生产力）。由于 ClickHouse 能够高效地压缩数据，因此随后的存储开销通常只是原始大小的一小部分。
要实现这一点，我们首先创建一个表，然后利用INSERT SELECT 结构。
CREATE TABLE forex ( `datetime` DateTime64(3), `bid` Decimal(11, 5), `ask` Decimal(11, 5), `base` LowCardinality(String), `quote` LowCardinality(String) ) ENGINE = MergeTree ORDER BY (base, quote, datetime) INSERT INTO forex SELECT * FROM s3Cluster('default', 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/forex/csv/year_month/*.csv.zst', 'CSVWithNames') SETTINGS max_download_threads = 12, max_insert_threads = 8
请注意，我们在这里精心选择了主鍵以提高性能。买入价和卖出价表示为Decimal(11, 5)，足以表示我们所需的精度。为了加速此插入，我们使用了设置max_insert_threads - 将其增加到 8。用户应谨慎使用此设置，因为它会增加内存开销，并可能干扰集群后台操作（合并）。s3Cluster 函数再次允许我们在集群中分配工作。从 ClickHouse 22.8 版本开始，查询和插入都将在这种情况下完全分布式。我们可以通过一个简单的查询来确认 ClickHouse 实现的压缩。如所示，原始数据大小已显著减小。
SELECT table, formatReadableSize(sum(data_compressed_bytes)) AS compressed_size, formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size, sum(data_compressed_bytes) / sum(data_uncompressed_bytes) AS compression_ratio FROM system.columns WHERE (database = currentDatabase()) AND (table = 'forex') GROUP BY table ORDER BY table ASC ┌─table─┬─compressed_size─┬─uncompressed_size─┬──compression_ratio─┐ │ forex │ 102.97 GiB │ 280.52 GiB │ 0.36706997621862225│ └───────┴─────────────────┴───────────────────┴────────────────────┘✎
上述方法的真正优势在于能够选择我们插入的数据。实际上，我们可以对数据进行切片和切块，以进行更快的分析，并且只为我们感兴趣的子集承担存储成本。我们上面的SELECT 语句可以很容易地修改，以只插入过去 10 年中美元为基础货币的数据。假设我们有一个名为forex_usd 的表，其结构与上面所示相同。
INSERT INTO forex_usd SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/forex/csv/year_month/2012*.csv.zst', 'CSVWithNames') WHERE (datetime > (now() - toIntervalYear(10))) AND (base = 'USD') Ok. 0 rows in set. Elapsed: 32.980 sec. Processed 598.28 million rows, 31.71 GB (18.14 million rows/s., 961.47 MB/s.) SELECT base, quote, min(datetime) FROM forex_usd GROUP BY base, quote Query id: c9f9b88b-dbb7-41f8-8cb9-3c22cf9ced8c ┌─base─┬─quote─┬───────────min(datetime)─┐ │ USD │ HUF │ 2012-09-09 17:01:19.220 │ │ USD │ NOK │ 2012-09-09 17:01:26.783 │ │ USD │ TRY │ 2012-09-09 17:01:17.157 │ │ USD │ DKK │ 2012-09-09 17:01:05.813 │ │ USD │ PLN │ 2012-09-09 17:01:12.000 │ │ USD │ MXN │ 2012-09-09 17:01:08.563 │ │ USD │ CZK │ 2012-09-09 17:01:23.000 │ │ USD │ SEK │ 2012-09-09 17:01:06.843 │ │ USD │ ZAR │ 2012-09-09 17:01:19.907 │ │ USD │ SGD │ 2012-09-09 17:01:03.063 │ │ USD │ JPY │ 2012-09-09 17:00:13.220 │ │ USD │ CAD │ 2012-09-09 17:00:15.627 │ │ USD │ HKD │ 2012-09-09 17:01:17.500 │ │ USD │ CHF │ 2012-09-09 17:00:17.347 │ └──────┴───────┴─────────────────────────┘✎
实时分析
让我们使用加载了整个数据集的 MergeTree 表重复我们之前的查询，以说明性能提升可能是相当大的
首先，让我们重复使用我们的表而不是 s3 函数计算高点差时期的查询。
SELECT base, quote, day, spread - any(spread) OVER (PARTITION BY base, quote ORDER BY base ASC, quote ASC, day ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS change FROM ( SELECT base, quote, avg(ask - bid) AS spread, day FROM forex WHERE (base = 'EUR') AND (quote = 'USD') GROUP BY base, quote, toYYYYMMDD(datetime) AS day ORDER BY base ASC, quote ASC, day ASC ) ORDER BY change DESC LIMIT 5 SETTINGS max_threads = 24 //results hidden for brevity 5 rows in set. Elapsed: 2.257 sec. Processed 246.77 million rows, 4.44 GB (109.31 million rows/s., 1.97 GB/s.)✎
3.5 秒，比 365 秒快 100 倍！请注意，我们的主鍵避免了完全线性扫描，从而减少了处理的行数。
我们评估涉及英镑的货币对高变化时期的查询呢？
WITH daily_change AS ( SELECT base, quote, day, close, close - any(close) OVER (PARTITION BY base, quote ORDER BY base ASC, quote ASC, day ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS change FROM ( SELECT base, quote, day, argMax(ask, datetime) AS close FROM forex WHERE (quote = 'GBP') OR (base = 'GBP') GROUP BY base, quote, toStartOfDay(datetime) AS day ORDER BY base ASC, quote ASC, day ASC ) ORDER BY base ASC, quote ASC, day ASC ) SELECT * FROM daily_change WHERE day > '2016-01-02 00:00:00' ORDER BY abs(change) DESC LIMIT 1 BY base, quote SETTINGS max_threads = 24 // results hidden for brevity 9 rows in set. Elapsed: 13.143 sec. Processed 2.12 billion rows, 29.71 GB (161.43 million rows/s., 2.26 GB/s.)✎
我们的加速虽然不像约束更严格的情况那么明显，但仍然是 20 倍！
通常，我们的性能提升将取决于主鍵和模式配置的有效性。优化键和代码是一个很大的主题，超出了本文的范围，但我们鼓励读者从这个指南开始探索。敏锐的读者可能已经注意到，我们还使用了设置 max_threads 来增加查询的并行化。通常，这设置为核心数量，但增加可能会以更高的内存为代价提高某些查询的性能。在某个时刻，CPU 上下文切换会减少更高的收益，并且最佳值通常是特定于查询的。
可视化时间
识别趋势和相关性通常需要直观的表示。秒与分钟的响应时间中的性能使直观的表示更加可行。幸运的是，ClickHouse 还与许多流行的开源工具集成在一起。下面我们将使用 Grafana 将三种货币对的价格与油价进行可视化：加拿大元 (CAD)、挪威克朗 (NOK) 和日元 (JPY)。
所有查询都采用以下形式
SELECT day, argMax(ask, datetime) AS price FROM forex WHERE (datetime > '2010-01-01 00:00:00') AND (base = 'BCO') AND (quote = 'USD') GROUP BY toStartOfDay(datetime) AS day ORDER BY day ASC✎
货币对的波动市场条件通常被视为绝佳的交易机会。在外汇交易中，这被计算为每日价格变化的标准差，例如，过去 30 天的标准差。如果每日变化大于此值，则表明我们正进入波动时期，也是潜在的交易机会。我们将分析范围限制在 GBP/USD，并将其绘制在价格图上。
SELECT day, stddevPop(change) OVER (PARTITION BY base, quote ORDER BY day ASC ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) AS volatility, if(abs(change) > volatility, 'true', 'false') AS volatile FROM ( SELECT base, quote, day, close, close - any(close) OVER (PARTITION BY base, quote ORDER BY base ASC, quote ASC, day ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS change FROM ( SELECT base, quote, day, argMax(ask, datetime) AS close FROM forex WHERE (quote = 'USD') AND (base = 'GBP') AND (datetime > '2010-01-01 00:00:00') GROUP BY base, quote, toStartOfDay(datetime) AS day ORDER BY base ASC, quote ASC, day ASC ) ORDER BY base ASC, quote ASC, day ASC ) ORDER BY base ASC, quote ASC, day ASC SETTINGS max_threads = 24✎
显然，金融危机、脱欧和新冠疫情初期，市场波动非常剧烈。
烛台图 是外汇交易中常用的可视化技术。它们以带阴影的线条的形式显示开盘价、收盘价、最低价和最高价。烛台的颜色表示价格的走向。如果烛台的收盘价高于开盘价，则价格在上升，烛台将显示为绿色。如果烛台显示为红色，则价格在收盘时低于开盘价。幸运的是，Grafana 支持烛台图。下面，我们使用一个简单的查询来显示 GBP/USD 货币对的烛台图。
布林带 可以提供一种简单的方法来跟踪趋势，以便在时间较短的特定点进行更详细的分析。布林带以过去 20 天的简单移动平均线 (SMA) 为基础，并跟踪两侧偏离平均线的标准差。它们最终被可视化为一条简单移动平均线以及一个上带和一个下带。当两个带之间的距离变宽时，表示该货币市场波动性增加。相反，距离缩小则表示波动性减弱。
交易者使用布林带与烛台的交点来确定买入和卖出信号。更多详细信息，请参见 这里 和 这里。不幸的是，Grafana 不允许将这两种可视化效果叠加显示，但希望它们能提供一些灵感。
总结
在这篇博文中，我们探讨了使用 ClickHouse 在 S3 中查询外汇数据的方案，以满足临时查询需求，即用户无需频繁访问数据集，因此在 ClickHouse 中存储数据的成本难以证明。这种方法与将 S3 用作数据源插入表格相辅相成，这样可以充分利用 ClickHouse 的功能进行实时分析。最后，我们向读者展示了一些与 ClickHouse 兼容的可视化工具，我们将在以后的文章中与外汇数据集一起使用这些工具。
