Kyligence 分层存储：Excel 与 ClickHouse 的双剑合璧之路

 现代企业愈发需要利用海量数据的价值进行量化运营、辅助决策洞察，在线联机分析 OLAP（Online Analytical Processing）赋予业务用户对海量数据进行多维度统计分析能力，帮助企业级用户提高生产效率。OLAP 场景主要分为两类领域，一个是描述性统计报表，场景较为固定；另一个是 Ad-Hoc 即席查询，主要用于进行探索性分析。为了帮助企业更好地应对海量数据带来的挑战，Kyligence 基于 ClickHouse 技术推出了智能分层存储功能，在 Excel 中业务人员就能实现亚秒级的探索性分析响应。

概述

本文将基于零售业基准 SSB 数据集展开分析。交易表中包含 1 亿行数据，我们将在 Excel 数据透视表中对维度和度量进行分析，对比 Excel + 对象存储、Excel + 基础索引和 Excel + 分层存储的查询性能表现。

很多人对 Kylin 的理解是，需要通过预计算维度组合的方式来形成一个“Cube”，再通过空间换时间的原理，利用更为经济的分布式存储或者云存储来加速查询响应。今天我们将另辟蹊径，不做任何固定 Cube，只是建模后拉一个基础索引来“兜底”，看看ClickHouse 对于 Kylin 的自助式分析和灵活查询这部分的应用场景，有着多大的帮助和提升。

为什么是 Excel？

要问业务人员最熟悉的数据软件，大家的回答往往都是 Excel。Excel 的每次迭代升级都给用户带来了更好的数据统计体验。Excel 的数据透视表更是数据计算、汇总和分析的不二之选，它可帮您快速对比数据、查看数据中的模式与趋势。Kyligence 可以对接 Excel 使其具有分析大数据的能力，了解更多请 👉 OLAP进阶：Excel可直接分析的大数据语义层

为什么是 ClickHouse？

ClickHouse 是一款开源 OLAP（在线分析处理）数据库，最初由 Yandex 公司开发，以满足其 Metrica 解决方案（类似谷歌分析）的需求。 ClickHouse 的开发目标很简单：尽可能快、尽可能多地筛选与聚合数据。与同类方案相仿（如 Druid、Pinot），ClickHouse 也使用面向列的模型进行数据存储，同时还通过各种并行化和矢量化的机制来充分发挥多核架构的优势。更多关于 ClickHouse 与 Apache Kylin 的对比，大家可以查看👉浅淡 Apache Kylin 与 ClickHouse 的对比

从 Kyligence Cloud 开始

首先，这里用的是 Kyligence Cloud，如果您也想试用 Kyligence Cloud，可以访问 Kyligence 免费试用页面。要分析数据，首先要导入数据。本文用到了一个 1 亿行的数据集，它可以充分体现 ClickHouse 的强大查询性能。

这里我们将导入 SSB 数据集。SSB（星型架构基准）基于 TPC-H 基准，它被设计用来衡量各类支持经典数据仓库的数据库产品的性能。数据集中包含 5 张表。表采样默认开启，我可以通过采样快速了解整个数据集的统计信息。

我通过 SQL 查询查询了 SSB.LINEORDER 表的行数。如下图所示，表中有一亿多行。

自动建模

接下来，我将开启 Kyligence Cloud 的 AI 增强模式，然后通过 SQL 构建。

通过 SQL 命令自动创建模型。

下面是我用于创建模型的 SQL 命令。

SELECT
    SSB.LINEORDER.LO_ORDERKEY,
    SSB.LINEORDER.LO_LINENUMBER,
    SSB.LINEORDER.LO_CUSTKEY,
    SSB.LINEORDER.LO_PARTKEY,
    SSB.LINEORDER.LO_SUPPKEY,
    SSB.LINEORDER.LO_ORDERDATE,
    SSB.LINEORDER.LO_ORDERPRIOTITY,
    SSB.LINEORDER.LO_SHIPPRIOTITY,
    SSB.LINEORDER.LO_TAX,
    SSB.LINEORDER.LO_COMMITDATE,
    SSB.LINEORDER.LO_SHIPMODE,
    SSB.PART.P_PARTKEY,
    SSB.PART.P_NAME,
    SSB.PART.P_MFGR,
    SSB.PART.P_CATEGORY,
    SSB.PART.P_BRAND,
    SSB.PART.P_COLOR,
    SSB.PART.P_TYPE,
    SSB.PART.P_SIZE,
    SSB.PART.P_CONTAINER,
    SSB.SUPPLIER.S_SUPPKEY,
    SSB.SUPPLIER.S_NAME,
    SSB.SUPPLIER.S_ADDRESS,
    SSB.SUPPLIER.S_CITY,
    SSB.SUPPLIER.S_NATION,
    SSB.SUPPLIER.S_REGION,
    SSB.SUPPLIER.S_PHONE,
    SSB.CUSTOMER.C_CUSTKEY,
    SSB.CUSTOMER.C_NAME,
    SSB.CUSTOMER.C_ADDRESS,
    SSB.CUSTOMER.C_CITY,
    SSB.CUSTOMER.C_NATION,
    SSB.CUSTOMER.C_REGION,
    SSB.CUSTOMER.C_PHONE,
    SSB.CUSTOMER.C_MKTSEGMENT,
    SSB.CALENDAR.D_DATEKEY,
    SSB.CALENDAR.D_DATE,
    SSB.CALENDAR.D_DAYOFWEEK,
    SSB.CALENDAR.D_MONTH,
    SSB.CALENDAR.D_YEAR,
    SSB.CALENDAR.D_YEARMONTHNUM,
    SSB.CALENDAR.D_YEARMONTH,
    SSB.CALENDAR.D_DAYNUMINWEEK,
    SSB.CALENDAR.D_DAYNUMINMONTH,
    SSB.CALENDAR.D_DAYNUMINYEAR,
    SSB.CALENDAR.D_MONTHNUMINYEAR,
    SSB.CALENDAR.D_WEEKNUMINYEAR,
    SSB.CALENDAR.D_SELLINGSEASON,
    SSB.CALENDAR.D_LASTDAYINWEEKFL,
    SSB.CALENDAR.D_LASTDAYINMONTHFL,
    SSB.CALENDAR.D_HOLIDAYFL,
    SSB.CALENDAR.D_WEEKDAYFL,
    SUM(SSB.LINEORDER.LO_REVENUE) AS SUM_REVENUE,
    SUM(SSB.LINEORDER.LO_SUPPLYCOST) AS SUM_COST
FROM
    SSB.LINEORDER
    LEFT JOIN SSB.PART ON SSB.LINEORDER.LO_PARTKEY=SSB.PART.P_PARTKEY
    LEFT JOIN SSB.CUSTOMER ON SSB.LINEORDER.LO_CUSTKEY=SSB.CUSTOMER.C_CUSTKEY
    LEFT JOIN SSB.SUPPLIER ON SSB.LINEORDER.LO_SUPPKEY=SSB.SUPPLIER.S_SUPPKEY
    LEFT JOIN SSB.CALENDAR ON SSB.LINEORDER.LO_ORDERDATE=SSB.CALENDAR.D_DATEKEY
GROUP BY
    SSB.LINEORDER.LO_ORDERKEY,
    SSB.LINEORDER.LO_LINENUMBER,
    SSB.LINEORDER.LO_CUSTKEY,
    SSB.LINEORDER.LO_PARTKEY,
    SSB.LINEORDER.LO_SUPPKEY,
    SSB.LINEORDER.LO_ORDERDATE,
    SSB.LINEORDER.LO_ORDERPRIOTITY,
    SSB.LINEORDER.LO_SHIPPRIOTITY,
    SSB.LINEORDER.LO_TAX,
    SSB.LINEORDER.LO_COMMITDATE,
    SSB.LINEORDER.LO_SHIPMODE,
    SSB.PART.P_PARTKEY,
    SSB.PART.P_NAME,
    SSB.PART.P_MFGR,
    SSB.PART.P_CATEGORY,
    SSB.PART.P_BRAND,
    SSB.PART.P_COLOR,
    SSB.PART.P_TYPE,
    SSB.PART.P_SIZE,
    SSB.PART.P_CONTAINER,
    SSB.SUPPLIER.S_SUPPKEY,
    SSB.SUPPLIER.S_NAME,
    SSB.SUPPLIER.S_ADDRESS,
    SSB.SUPPLIER.S_CITY,
    SSB.SUPPLIER.S_NATION,
    SSB.SUPPLIER.S_REGION,
    SSB.SUPPLIER.S_PHONE,
    SSB.CUSTOMER.C_CUSTKEY,
    SSB.CUSTOMER.C_NAME,
    SSB.CUSTOMER.C_ADDRESS,
    SSB.CUSTOMER.C_CITY,
    SSB.CUSTOMER.C_NATION,
    SSB.CUSTOMER.C_REGION,
    SSB.CUSTOMER.C_PHONE,
    SSB.CUSTOMER.C_MKTSEGMENT,
    SSB.CALENDAR.D_DATEKEY,
    SSB.CALENDAR.D_DATE,
    SSB.CALENDAR.D_DAYOFWEEK,
    SSB.CALENDAR.D_MONTH,
    SSB.CALENDAR.D_YEAR,
    SSB.CALENDAR.D_YEARMONTHNUM,
    SSB.CALENDAR.D_YEARMONTH,
    SSB.CALENDAR.D_DAYNUMINWEEK,
    SSB.CALENDAR.D_DAYNUMINMONTH,
    SSB.CALENDAR.D_DAYNUMINYEAR,
    SSB.CALENDAR.D_MONTHNUMINYEAR,
    SSB.CALENDAR.D_WEEKNUMINYEAR,
    SSB.CALENDAR.D_SELLINGSEASON,
    SSB.CALENDAR.D_LASTDAYINWEEKFL,
    SSB.CALENDAR.D_LASTDAYINMONTHFL,
    SSB.CALENDAR.D_HOLIDAYFL,
    SSB.CALENDAR.D_WEEKDAYFL
;

保存模型前，取消增加基础索引选项。

现在，用于数据分析的星型架构模型创建好了。

MDX

模型创建完成后，我们将在 Kyligence Cloud 中体验 MDX。

输入MDX 数据集的名称。这里的数据集就是 Kyligence Cloud 模型。

您还可以在 MDX 数据集之间定义更复杂的关系。

您可以在定义语义时定义更多的对象，如维度、度量、层次结构、计算度量等。这些语义将同步到上层的 BI 工具，如 Tableau、PowerBI 或 Excel。最终用户可以通过拖放 MDX 对象来分析数据。我们将以利润度量为例：利润 = 收入 - 成本。

通过 MDX 连接 Excel

在 Windows 系统中打开 Microsoft Excel。注：文中用到的是 Windows 11。

点击数据 -> 获取数据 -> 来自数据库 -> 自 Analysis Services。 注：本功能目前只支持 Windows 操作系统。MacOS 系统暂不兼容 Analysis Services。

Kyligence Cloud 提供了非常清楚的连接指引，所以我能轻松构建 Excel 和 MDX 之间的关联。

查询性能

首先，在"行"中输入"YEAR"、"CATEGORY"，在"值"中输入"Revenue"，计算某个类别某年的收入。

查询耗时为 17s。

从查询历史看，MDX 查询被拆解为 SQL 查询中的不同部分。对象存储响应了该查询，说明查询被下压到原始数据集。

随后，我在"行"中输入"YEAR"、"CATEGORY"，在"值"中输入"Supplycost"，计算某个类别某年的成本。

查询耗时为 11s。

然后，我在"行"中输入了"YEAR"、"CATEGORY"、"REGION"、"NATION"，在"值"中输入了"Profit"。计算某个类别某年在某个国家及区域的利润。

基础索引

如果没有基础索引或缺少基础索引，可在下拉列表中选择+ 索引来增加基础索引。

因为基础索引中包含所有的维度和度量，所以我们通常将其视为安全索引（保底索引）。基础索引可以回答即席查询，避免查询被下压到对象存储中。为充分发挥基础索引的作用，我们应该基于基础索引构建Segment。

完成基础索引的构建后，我发现查询现在是由模型而非对象存储来响应。也就是说，基础索引可以避免查询被下压到原始数据集。

这里我重复执行了上述三个在 Excel 中的查询，从下表中可以看到基础索引的查询性能表现。

通过 ClickHouse 提高查询性能

Kyligence Cloud 4.5 搭载了 ClickHouse 高级功能。如希望使用此功能，您在云环境中部署工作区时将需要添加一个名为分层存储的节点。

此外，还需要在设置页面开启分层存储功能。

选择基础索引，然后选择加载到分层存储。

等待数据加载完成。

数据加载完成后，我重复执行了上述三个在 Excel 中的查询。

最后，我们比较了这三种情况下的查询性能，即"查询下压"（黄色）、"基础索引"（蓝色）和"基于 ClickHouse 的分层存储"（红色），可以看到，分层存储的表现很优异（查询速度越低越好）。我们也尝试了其他一些维度和度量的组合，即使在处理自助式分析查询或即席查询，分层存储中的 ClickHouse 也能较快做出响应。ClickHouse 对于 Kylin 的自助式分析和灵活查询这部分的查询性能，有着显著的帮助提升。

Kyligence 分层存储

Kyligence 为用户带来了分层存储这一高级功能。用户无需预计算，就可以快速冷启动查询，超多维灵活分析和明细查询分析的性能也被极大提升。同时，基于 HDFS/对象存储和 ClickHouse 的两级存储设计方案可以全面覆盖各种分析场景，使用户能在海量数据中自由探索。

此外，分层存储还支持灵活的即席查询场景，如标签分析或用户行为分析，将能极大助力企业的精细化运营和业务决策流程；此外，搭配 Kyligence 的统一模型来管理数据，可以无缝对接主流 BI 工具和 Excel，业务人员无需改变使用习惯，就能享受更好的数据分析体验。

启用分层存储后，用户无需担心数据如何分布、如何导入，只需根据业务需求建模即可，这将极大降低建模和模型优化成本，使未来的业务更敏捷。想了解更多分层存储相关信息，请 👉 ClickHouse + Kylin  融合架构技术白皮书新鲜出炉！