文章目录
- 前言
- BSP 模式简介
- 基于 TPC-DS 的 ELT 活动
- 测试环境
- 登录 ECS
- 数据查询配置
- 执行 02.sql
- sql解释:
- 1. 第一步:创建 wscs 临时表
- 2. 第二步:创建 wswscs 临时表
- 3. 第三步:对比 2001 年和 2002 年的数据
- 子查询 1:提取 2001 年数据
- 子查询 2:提取 2002 年数据
- 主查询:对比相邻年份的销售额
- 最终输出:
- 测试结果与心得
- 总结
前言
在现代数据分析场景中,实时数仓与离线数仓各有侧重:实时数仓注重低延迟的数据入库与即时分析能力,而离线数仓则强调复杂任务的稳定执行及高效的内存管理。为满足用户多样化的数据需求,ByConity 作为一款开源云原生数据仓库,提供了全新的 BSP 模式,大幅提升了数据仓库的适用场景与性能表现。
BSP 模式简介
BSP 模式(Bulk Synchronous Parallel)为 ByConity 带来了以下关键能力:
- Task 级容错:在数据处理过程中实现更高效的错误恢复机制,确保任务的稳定性。
- 更细粒度的调度:以任务为单位进行资源调度,优化计算资源的使用效率。
- 资源感知调度:根据当前资源情况动态调整任务分配,提升并行执行的效率。
通过 BSP 模式,ByConity 实现了一站式的数据接入、加工和分析流程,为用户提供了更加便捷、高效的数据分析工具。
基于 TPC-DS 的 ELT 活动
为了让用户直观感受 BSP 模式的强大性能,ByConity 提供了基于 TPC-DS 1TB 数据集 的测试活动。以下是完整的测试过程分享。
测试环境
测试环境基于火山引擎 ECS,采用以下配置:
组件 | 规格 |
---|---|
ByConity 版本 | v1.0.1 |
Worker 节点 | 4 * 16core 64G |
Server 节点 | 1 * 16core 64G |
TSO | 1 * 4core 16G |
Daemon Manager | 1 * 4core 16G |
Resource Manager | 1 * 8core 32G |
存储 | 对象存储 TOS |
FoundationDB | 3 * 4core 16G |
登录 ECS
根据活动提供的登录凭据,通过 SSH 连接到远程 ECS 服务器。以下为登录过程:
-
MacOS 用户:
- 打开终端,输入以下命令并回车:
ssh -p 23 root@14.103.145.182
- 输入
yes
确认连接,并输入提供的登录密码。
- 打开终端,输入以下命令并回车:
-
避免断连:创建 tmux 会话:
tmux new -s your_session_name
- 启动客户端:
clickhouse client --port 9010
数据查询配置
- 切换至测试数据库:
use test_elt;
- 设置 SQL 方言:
set dialect_type = 'ANSI';
执行 02.sql
执行 TPC-DS 测试集查询, source 02.sql; 如下:
with wscs as(select sold_date_sk,sales_pricefrom (select ws_sold_date_sk sold_date_sk,ws_ext_sales_price sales_pricefrom web_sales union allselect cs_sold_date_sk sold_date_sk,cs_ext_sales_price sales_pricefrom catalog_sales) x),wswscs as (select d_week_seq,sum(case when (d_day_name='Sunday') then sales_price else null end) sun_sales,sum(case when (d_day_name='Monday') then sales_price else null end) mon_sales,sum(case when (d_day_name='Tuesday') then sales_price else null end) tue_sales,sum(case when (d_day_name='Wednesday') then sales_price else null end) wed_sales,sum(case when (d_day_name='Thursday') then sales_price else null end) thu_sales,sum(case when (d_day_name='Friday') then sales_price else null end) fri_sales,sum(case when (d_day_name='Saturday') then sales_price else null end) sat_salesfrom wscs,date_dimwhere d_date_sk = sold_date_skgroup by d_week_seq)select d_week_seq1,round(sun_sales1/sun_sales2,2),round(mon_sales1/mon_sales2,2),round(tue_sales1/tue_sales2,2),round(wed_sales1/wed_sales2,2),round(thu_sales1/thu_sales2,2),round(fri_sales1/fri_sales2,2),round(sat_sales1/sat_sales2,2)from(select wswscs.d_week_seq d_week_seq1,sun_sales sun_sales1,mon_sales mon_sales1,tue_sales tue_sales1,wed_sales wed_sales1,thu_sales thu_sales1,fri_sales fri_sales1,sat_sales sat_sales1from wswscs,date_dim where date_dim.d_week_seq = wswscs.d_week_seq andd_year = 2001) y,(select wswscs.d_week_seq d_week_seq2,sun_sales sun_sales2,mon_sales mon_sales2,tue_sales tue_sales2,wed_sales wed_sales2,thu_sales thu_sales2,fri_sales fri_sales2,sat_sales sat_sales2from wswscs,date_dim where date_dim.d_week_seq = wswscs.d_week_seq andd_year = 2001+1) zwhere d_week_seq1=d_week_seq2-53order by d_week_seq1;
sql解释:
这段 SQL 语句实现了对比两个年份(2001年和2002年)的每周日到周六的销售数据的相对变化。以下是对其结构和逻辑的详细解析:
1. 第一步:创建 wscs 临时表
with wscs as(select sold_date_sk, sales_pricefrom (select ws_sold_date_sk sold_date_sk, ws_ext_sales_price sales_pricefrom web_sales union allselect cs_sold_date_sk sold_date_sk, cs_ext_sales_price sales_pricefrom catalog_sales) x)
功能:
- 将
web_sales
和catalog_sales
两个表的数据合并到一个统一的临时表wscs
中。 - 提取销售日期键 (
sold_date_sk
) 和销售价格 (sales_price
) 两列。
逻辑:
web_sales
表:- 提取
ws_sold_date_sk
(销售日期)和ws_ext_sales_price
(销售额)。
- 提取
catalog_sales
表:- 提取
cs_sold_date_sk
(销售日期)和cs_ext_sales_price
(销售额)。
- 提取
union all
:- 不去重地合并两表的记录。
2. 第二步:创建 wswscs 临时表
wswscs as (select d_week_seq,sum(case when (d_day_name='Sunday') then sales_price else null end) sun_sales,sum(case when (d_day_name='Monday') then sales_price else null end) mon_sales,sum(case when (d_day_name='Tuesday') then sales_price else null end) tue_sales,sum(case when (d_day_name='Wednesday') then sales_price else null end) wed_sales,sum(case when (d_day_name='Thursday') then sales_price else null end) thu_sales,sum(case when (d_day_name='Friday') then sales_price else null end) fri_sales,sum(case when (d_day_name='Saturday') then sales_price else null end) sat_salesfrom wscs,date_dimwhere d_date_sk = sold_date_skgroup by d_week_seq)
功能:
- 按照每周的编号(
d_week_seq
)计算每周每天(周日到周六)的总销售额。
逻辑:
- 关联
wscs
和date_dim
表:- 利用
d_date_sk = sold_date_sk
,将销售日期与日期维表匹配。
- 利用
- 按周聚合:
- 利用
d_week_seq
对每周数据分组。
- 利用
case when
语句:- 对每天的销售额分别求和,忽略不属于该天的数据。
结果:
wswscs
包含每周每天的销售额字段,例如 sun_sales
、mon_sales
等。
3. 第三步:对比 2001 年和 2002 年的数据
子查询 1:提取 2001 年数据
(select wswscs.d_week_seq d_week_seq1,sun_sales sun_sales1,mon_sales mon_sales1,tue_sales tue_sales1,wed_sales wed_sales1,thu_sales thu_sales1,fri_sales fri_sales1,sat_sales sat_sales1from wswscs, date_dim where date_dim.d_week_seq = wswscs.d_week_seq andd_year = 2001)
- 提取 2001 年的每周每天销售额,并标记为
*_sales1
。 d_week_seq1
表示 2001 年的周编号。
子查询 2:提取 2002 年数据
(select wswscs.d_week_seq d_week_seq2,sun_sales sun_sales2,mon_sales mon_sales2,tue_sales tue_sales2,wed_sales wed_sales2,thu_sales thu_sales2,fri_sales fri_sales2,sat_sales sat_sales2from wswscs, date_dim where date_dim.d_week_seq = wswscs.d_week_seq andd_year = 2002)
- 提取 2002 年的每周每天销售额,并标记为
*_sales2
。 d_week_seq2
表示 2002 年的周编号。
主查询:对比相邻年份的销售额
select d_week_seq1,round(sun_sales1/sun_sales2,2),round(mon_sales1/mon_sales2,2),round(tue_sales1/tue_sales2,2),round(wed_sales1/wed_sales2,2),round(thu_sales1/thu_sales2,2),round(fri_sales1/fri_sales2,2),round(sat_sales1/sat_sales2,2)from ...where d_week_seq1 = d_week_seq2 - 53order by d_week_seq1;
功能:
- 对比 2001 年每周与对应的 2002 年同一周(相差 53 周)的销售额。
逻辑:
- 字段匹配:
- 根据
d_week_seq1 = d_week_seq2 - 53
确保对比的是相邻年份的同一周。
- 根据
- 字段计算:
- 利用
round(sun_sales1 / sun_sales2, 2)
等公式计算每周日到周六的销售额变化比率。
- 利用
- 排序:
- 按周编号(
d_week_seq1
)从小到大排序。
- 按周编号(
最终输出:
表格展示 2001 年相较于 2002 年的每周每天销售额变化百分比。
周编号 | 周日变化 | 周一变化 | 周二变化 | 周三变化 | 周四变化 | 周五变化 | 周六变化 |
---|---|---|---|---|---|---|---|
Week1 | 1.10 | 0.95 | 1.20 | 1.05 | 0.98 | 1.15 | 1.00 |
执行后截图如下:
测试结果与心得
通过执行 TPC-DS 的模拟查询,实际验证了 ByConity 在 ELT 任务中的强大表现:
- 性能提升:BSP 模式在任务分解与调度上表现优异,查询任务耗时显著减少。
- 任务稳定性:在高并发与复杂场景下依旧保持出色的容错能力。
- 一站式体验:用户无需额外切换工具即可完成数据加工与分析,全流程高效流畅。
总结
ByConity 的 BSP 模式为实时数仓和离线数仓之间架起了一座桥梁,其灵活性与高效性让企业能够更好地应对多变的数据分析需求。本次测试验证了其在 ELT 场景中的实际效果,也展示了 ByConity 在未来数据仓库领域的潜力与前景。
如果您也想体验 ByConity 的强大功能,欢迎参与测试活动,亲自感受开源数据仓库的无限可能!