Kyligence Copilot - AI 数智助理,以 AI 变革企业经营与管理! 立即了解更多

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

张晓东
2021年 12月 08日

 现代企业愈发需要利用海量数据的价值进行量化运营、辅助决策洞察,在线联机分析 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 的对比

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  融合架构技术白皮书新鲜出炉!

添加企微

kyligence
关注我们

kyligence