这篇博文是系列文章的一部分
这篇文章继续“将数据导入 ClickHouse”系列。 在我们之前的文章中，我们展示了加载 Hacker News 数据集的基础知识，并小绕弯路进入 JSON 世界。 在这篇文章中，我们将继续探索将数据导入 ClickHouse 的方法，重点是使用 Amazon S3 存储作为数据源和可查询的数据存储库。 在这篇文章中，我们还将探索一个新的数据集：过去 20 年的外汇（“forex”）数据。 它有一个简单的模式，但在其属性方面很有用，并让我们探索窗口函数。
Amazon S3 或 Amazon Simple Storage Service 是 Amazon Web Services 提供的一项服务，它通过 Web 服务接口提供对象存储。 在过去的 10 年里，它已几乎无处不在，既作为数据存储层（参见 ClickHouse Cloud），又作为文件分发的方式。 我们越来越多地遇到 ClickHouse 用户，他们使用 S3 作为数据湖，并希望将此数据导入 ClickHouse 或就地查询数据。 这篇文章展示了如何使用 ClickHouse 实现这两种方法。
虽然我们的示例使用了ClickHouse Cloud，但这些示例应该适用于自管理实例，并且可以在任何配置适中的笔记本电脑上运行。
关于外汇的一些知识...
这篇博文不是关于外汇交易，外汇交易可能是一个复杂的主题，并且涉及一些复杂的策略！ 但是，为了让用户了解未来 ClickHouse 查询的背景知识以及对数据的基本了解，我们简要解释一下核心概念。 理解外汇交易对于能够理解这篇文章中的技术方面不是强制性的，但有助于解释查询背后的动机。
外汇交易是不同国家货币之间的交易； 例如，美元兑欧元。 任何货币交易都发生在货币对之间。 购买货币对涉及交易者和经纪商等两方。 交易者可以从经纪商处以报价货币购买基础货币，或者出售基础货币并收到报价作为回报。 该货币对以 X/Y 格式报价，其中 X 是基础货币，Y 是报价货币。 例如，下面的示例数据显示了 2022 年 EUR/USD 货币对（主要货币对）的买入价和卖出价。
鉴于上述情况，卖出报价（或卖出价）表示经纪商愿意接受的报价货币价格，以换取他们出售的每单位基础货币，即他们要求的价格。 这是交易者可以开立买入头寸的价格。
买入报价（或买入/卖出价）是经纪商愿意以报价货币买入或 BID 基础货币的价格。 交易者以此价格开立卖出（空头）头寸。
卖出报价将始终高于买入报价。 交易者为该货币对支付的价格将始终高于他们可以收回的价格。 买入价和卖出价之间的差额是点差，实际上是经纪商的利润，也是机构赚钱的方式。
例如，以下是来自 forex.com 的 2022 年 2 月 9 日 EUR/USD 货币对的报价。
点和跳动
交易货币对时的最小计量单位是价格点数或 PiP。 这通常是大多数主要货币对的小数点后第 4 位，或者在某些情况下是第 2 位，例如，涉及日元的货币对。 反过来，货币对的价格通常高一位小数位。 请注意，点差也以点为单位衡量。
跳动记录股票或商品的价格何时以预定量或分数变化量变化，即，当价格向上或向下移动特定量或分数变化量时，就会发生跳动。 在外汇中，当货币对的买入价或卖出价变化一个点时，就会发生跳动。
数据
这篇博文中使用的数据集是从 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，因此被忽略）。 相反，它只是标记货币对的买入价或卖出价何时变化一个点。
数据本身以 2 种格式分发：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 年每个货币对的跳动次数。 请注意我们如何能够使用 glob 模式限制文件。
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 价格之间的相关性。 这些货币代表了各个经济体对原油的依赖程度 - 挪威的依赖程度最高，日本的依赖程度最低。 请注意，我们的 glob 模式意味着我们只分析 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 查询的共同主题是需要对整个数据集执行线性扫描。 除了通过命名组织文件和使用 glob 模式进行限制之外，我们没有索引或优化查询的能力。
在某些时候，用户准备好支付额外的存储成本，以换取通过将数据插入 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 函数计算 EUR/USD 中高点差的时期。
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✎
显然，在金融危机、英国退欧和 covid 疫情初期，市场非常动荡。
K 线图是外汇交易中常用的可视化技术。 这些图表将开盘价、收盘价、最低价和最高价显示为带有影线的条带。 K 线的颜色表示价格的方向。 如果 K 线的价格收于 K 线的开盘价之上，则价格正在向上移动，并且 K 线将为绿色。 如果 K 线为红色，则表示价格已收于开盘价之下。 幸运的是，Grafana 支持 K 线图。 下面我们使用一个简单的查询来显示货币对 GBP/USD 的 K 线图。
布林线可以提供一种简单的手段来跟踪趋势，以便在狭窄的时间点进行更详细的分析。 这些线采用过去 20 天的简单移动平均线 (SMA)，并跟踪偏离该平均线两侧的标准差。 反过来，这些线可视化为简单移动平均线以及上限和下限。 当带之间的距离扩大时，它说明了所讨论货币的市场波动性增加。 相比之下，较小的距离表示波动性较小。
交易者使用与 K 线相交的布林线来确定买入和卖出信号。 有关更多详细信息，请参阅此处和此处。 不幸的是，Grafana 不允许将这两种视觉效果叠加，但希望它们能提供一些灵感。
总结
在这篇博文中，我们探讨了使用 ClickHouse 在 S3 中查询外汇数据以满足即席查询需求，在这种情况下，用户不需要频繁访问数据集，因此存储在 ClickHouse 中的成本很难证明是合理的。 这种方法通过使用 S3 作为数据源插入到表中得到补充，在表中可以利用 ClickHouse 的全部功能进行实时分析。 最后，我们向读者介绍了与 ClickHouse 兼容的一些可视化工具，我们将在未来的文章中结合我们的外汇数据集更多地使用这些工具。
