ClickPy 数据量突破万亿行

PyPi（Python 包索引）是 Python 编程语言的软件存储库。它是 Python 生态系统的核心，各种库每天在此下载近 20 亿次。

与这些下载相关的元数据可以在 BigQuery 中访问。这对于执行临时查询来说很好，但我们希望创建一个面向用户的服务，让用户可以探索他们最喜欢的软件包。

这就是 ClickPy 的由来，它是一个基于 ClickHouse 构建的免费服务，允许您对 PyPi 软件包下载进行实时分析。代码也是开源的，并且可以在 GitHub 上获取，以便您可以在本地运行该服务。

ClickPy 已经上线大约 9 个月了，几周前，数据库中的主表超过了 **1 万亿行**，代表着 1 万亿次各种库的下载。

这篇博文将解释我们如何构建应用程序以及如何处理如此庞大的数据集。

此项目包含三个主要实体的数据

• 国家/地区 - 国家/地区名称和代码。
• 项目 - 关于每个 PyPi 项目的元数据。
• 下载 - 关于每个项目安装的元数据。

我们将为每个数据集创建一个表。它们将分别称为 countries、projects 和 pypi。

pypi 包含最多的数据，因为每个下载都对应一行，而且我们有很多行！因此，我们将为常见的查询模式创建下游表，并创建相应的物化视图来填充这些表。下图显示了我们将创建的所有表

ClickHouse 还支持字典，字典是用于引用数据的内存中键值对。我们将为国家/地区创建字典，将国家/地区代码映射到国家/地区名称，并为项目创建另一个字典，将项目名称映射到上次更新时间。

项目和下载的基础数据可在 BigQuery 中获得。但是，导出数据需要几个小时，因此我们将数据导出到 Google Cloud Storage 存储桶中，以 Parquet 文件的形式存储。您可以在 ClickPy GitHub 存储库中找到 执行此操作的查询。

然后，我们将数据导入到两个表中：projects 和 pypi。我们不会详细介绍创建这些表的查询，但您可以在 此文件中找到这些查询。

然后，我们可以运行以下查询来导入项目

INSERT INTO projects 
SELECT *
FROM s3(
'https://storage.googleapis.com/clickhouse_public_datasets/pypi/packages/packages-*.parquet'
)

以及以下查询来导入下载

INSERT INTO pypi 
SELECT timestamp::Date as date, country_code, project, file.type as type, 
       installer.name as installer, 
       arrayStringConcat(arraySlice(splitByChar('.', python), 1, 2), '.') as python_minor, 
       system.name as system, file.version as version 
FROM s3(
  'https://<bucket>/file_downloads-00000000001*.parquet', 
  'Parquet', 
  'timestamp DateTime64(6), country_code LowCardinality(String), url String, project String, `file.filename` String, `file.project` String, `file.version` String, `file.type` String, `installer.name` String, `installer.version` String, python String, `implementation.name` String, `implementation.version` String, `distro.name` String, `distro.version` String, `distro.id` String, `distro.libc.lib` String, `distro.libc.version` String, `system.name` String, `system.release` String, cpu String, openssl_version String, setuptools_version String, rustc_version String,tls_protocol String, tls_cipher String') 
WHERE python_minor != '' AND system != '' 
SETTINGS input_format_null_as_default = 1, 
         input_format_parquet_import_nested = 1

我们使用此脚本加载了前 6000 亿行。然后，我们有一个 cron 作业，它每小时运行一次，并提取自上次运行以来添加的新行，并将这些行也导出到 Parquet 文件中。然后，一些工作进程会获取这些 Parquet 文件并将它们导入到 ClickHouse 中。执行此操作的工具称为 ClickLoad，您可以在 这篇博文中了解更多信息。

最后，我们有一个包含国家/地区的 CSV 文件，我们将使用以下查询导入它

INSERT INTO pypi.countries 
SELECT name,  `alpha-2` AS code
FROM url(
'https://gist.githubusercontent.com/gingerwizard/963e2aa7b0f65a3e8761ce2d413ba02c/raw/4b09800f48d932890eedd3ec5f7de380f2067947/country_codes.csv'
)

让我们也看看填充从 pypi 下游表之一的物化视图。在 ClickHouse 中，物化视图是在将行插入上游表时执行的 SQL 片段。

CREATE MATERIALIZED VIEW pypi.pypi_downloads_per_day_by_version_by_system_mv 
TO pypi.pypi_downloads_per_day_by_version_by_system (
  `date` Date, 
  `project` String, 
  `version` String, 
  `system` String, 
  `count` Int64
) AS 
SELECT date, project, version, system, count() AS count 
FROM pypi.pypi 
GROUP BY date, project, version, system

我们在上面建模部分描述的每个下游表都有一个这样的物化视图。

ClickPy 的前端是用 Next.JS 和 React 编写的。应用程序代码也在 GitHub 存储库中提供。

主页包含所有页面的概述，显示新兴的存储库、一段时间内未更新的流行存储库、最近的版本等等。您可以点击任何链接的项目。

或者，您可以在搜索栏中搜索您最喜欢的项目。

让我们看看 openai 库，它与 OpenAI 的 API 交互。如果我们搜索 openai 并点击第一个结果，我们将看到以下页面

页面的顶部包含从 GitHub 获取的一些数据，但在其下方是下载统计信息。每个小部件都有一个箭头按钮，单击该按钮将带我们到 Play UI，并预填充小部件底层的查询。

例如，如果我们点击“热门版本”查询，我们将看到 此查询

在撰写本文时，OpenAI 库的最新版本是 1.41.0，但更多人下载了 2023 年 9 月首次发布的版本。

除了使用 Play UI 外，如果您想直接查询数据，可以使用 ClickHouse 客户端连接到使用只读 play 用户的数据库

./clickhouse client \
  -h clickpy-clickhouse.clickhouse.com \
  --user play --secure \
  --database pypi

您可以通过运行以下命令查看可查询的表列表

SHOW TABLES

┌─name─────────────────────────────────────────────────────────────────┐
│ countries                                                            │
│ countries_dict                                                       │
│ last_updated_dict                                                    │
│ projects                                                             │
│ pypi                                                                 │
│ pypi_downloads                                                       │
│ pypi_downloads_by_version                                            │
│ pypi_downloads_by_version_mv                                         │
│ pypi_downloads_max_min                                               │
│ pypi_downloads_max_min_mv                                            │
│ pypi_downloads_mv                                                    │
│ pypi_downloads_per_day                                               │
│ pypi_downloads_per_day_by_version                                    │
│ pypi_downloads_per_day_by_version_by_country                         │
│ pypi_downloads_per_day_by_version_by_country_mv                      │
│ pypi_downloads_per_day_by_version_by_file_type                       │
│ pypi_downloads_per_day_by_version_by_file_type_mv                    │
│ pypi_downloads_per_day_by_version_by_installer_by_type               │
│ pypi_downloads_per_day_by_version_by_installer_by_type_by_country    │
│ pypi_downloads_per_day_by_version_by_installer_by_type_by_country_mv │
│ pypi_downloads_per_day_by_version_by_installer_by_type_mv            │
│ pypi_downloads_per_day_by_version_by_python                          │
│ pypi_downloads_per_day_by_version_by_python_by_country               │
│ pypi_downloads_per_day_by_version_by_python_by_country_mv            │
│ pypi_downloads_per_day_by_version_by_python_mv                       │
│ pypi_downloads_per_day_by_version_by_system                          │
│ pypi_downloads_per_day_by_version_by_system_by_country               │
│ pypi_downloads_per_day_by_version_by_system_by_country_mv            │
│ pypi_downloads_per_day_by_version_by_system_mv                       │
│ pypi_downloads_per_day_by_version_mv                                 │
│ pypi_downloads_per_day_mv                                            │
│ pypi_downloads_per_month                                             │
│ pypi_downloads_per_month_mv                                          │
└──────────────────────────────────────────────────────────────────────┘

每个查询的读取行数限制为 100 亿行，因此您可能不希望查询 pypi 表，因为您可能会超过此限制。

其他表中的行数少得多，因此，例如，我们可以编写以下查询来计算过去 10 天内 pandas 的下载次数，并显示一个漂亮的小条形图

WITH downloadsPerDay AS (
   SELECT date, sum(count) AS count
   FROM pypi.pypi_downloads_per_day
   WHERE (date >= (now() - (((10 * 24) * 60) * 60))) AND (project = 'pandas')
   GROUP BY ALL
)

SELECT date, count,
    formatReadableQuantity(count) AS readableSize,
    bar(count, 0, (SELECT max(count) FROM downloadsPerDay), 10) AS bar
FROM downloadsPerDay

GROUP BY ALL
ORDER BY date ASC

┌───────date─┬───count─┬─readableSize─┬─bar────────┐
│ 2024-08-12 │ 9787106 │ 9.79 million │ █████████▉ │
│ 2024-08-13 │ 9727401 │ 9.73 million │ █████████▉ │
│ 2024-08-14 │ 9309011 │ 9.31 million │ █████████▍ │
│ 2024-08-15 │ 8825396 │ 8.83 million │ ████████▉  │
│ 2024-08-16 │ 9428220 │ 9.43 million │ █████████▌ │
│ 2024-08-17 │ 5915869 │ 5.92 million │ ██████     │
│ 2024-08-18 │ 5955829 │ 5.96 million │ ██████     │
│ 2024-08-19 │ 9118143 │ 9.12 million │ █████████▎ │
│ 2024-08-20 │ 9846985 │ 9.85 million │ ██████████ │
└────────────┴─────────┴──────────────┴────────────┘

我们可以看到周末出现了一个相当大的下降，但在其他时间，每天的下载量都保持在约 900 万左右。

将导入更多数据！自从我们注意到达到 1 万亿里程碑以来，又导入了 360 亿行数据。

我们希望您能参与 ClickPy 项目。在 项目的 issue 页面 上，如果您发现任何问题或有任何改进应用程序的想法，请告诉我们。

如果您在数据之上构建任何工具或应用程序，请在 Twitter 上标记我们 @clickhousedb，我们将帮助您推广它们。