ClickHouse单机 VS ClickHouse集群 VS Kyligence (单表25亿数据)

Sen Du | | 15 min read

目录

  1. 前言
  2. 结论
  3. 压测准备
  4. 压测结果
  5. SQL样本
  6. 服务器监控

1.前言

ClickHouse是一款开源的列式分析型数据库，自从2016年开源以来在全世界开源社区内的受欢迎程序逐渐上升，GitHub上Star数目已经超过了Presto、Impala、Greenplum等开源时间更久的老牌经典项目。在国内，ClickHouse也越来越火，诸多互联网大厂都纷纷在内部业务中跟进使用并且普遍反馈性能优异。

我们早先的大数据平台基于Kylin商业版，这款大数据系统帮我们减轻了业务系统的压力、提高了业务系统的响应时间、节约了不少存储费用。运行了一年多，没有出现大的生产问题。但是随着对Kylin使用程度的加深，对ClickHouse了解的增多，我逐渐认为ClickHouse可能更适合我们。主要原因：

  1. 我们现有的业务偏明细查询，而Kylin更擅长预聚合，随着数据量的增多，Kylin变慢了（Kylin的数据主要存放在云Blob，明细查询需要过多网络IO）
  2. kylin比较重，所有关于数据的变动都需要经过构建步骤。我们的数据不太多也不太少，大部分单表几十亿，微调下模型或者修改下历史数据，少者一两小时多则一天；
  3. 受限于商业软件没办法灵活使用。a). 我们的客户比较多，有的不愿意使用公有云，有的不愿意承担kylin商业版的成本（但是他们数据都不少，为了解决查询问题，现在我通常是直接在他们服务器上部署ClickHouse）；b). 商业版文档不太多、不够深入。为了更好的运维，我阅读过所有公开的文档，大部分问题都可以自我消化，但是有些问题还是需要提工单接受支持（部分情况是我的失误；部分情况是没有相关文档说明，导致我的使用和他们系统预期不一致；还有部分情况，就真的是系统缺陷了）；c). 商业版很难定制化开发。有些功能我想要，但是当前系统没有，我就只能束手无策 😂
  4. ClickHouse 查询速度快，无需构建既可以实时查询；写入数据快，能对接到flink，打造成实时大数据平台。
  5. ClickHouse 可以灵活部署，针对不同的用户，有集群版部署策略、单机版部署策略，性能都可以满足需求；可以灵活使用，支持定制化开发，为公司带来技术积淀。
  6. 基于成本与性能的考虑。a). ClickHouse 属于开源，不需要购买许可证（当然也不推荐完全白用，我写的一系列关于ClickHouse的技术文章，也是在向开源贡献绵薄之力）；b). 在我们的多数业务场景下，同等服务器成本 ClickHouse集群响应时间是 Kylin 的10倍+（详见下方Benchmark说明）
  7. 基于技术积累的考虑。Kylin 可能会比 ClickHouse 更省心，运维更容易（不行提工单，总有人会解决的），但是公司这方面的技术实力不会变强；大数据平台的核心技术不能掌握在自己手中，接受商业技术限定的条框，可能会束缚业务想象力。
  8. ...

2.结论

本次主要压测了ClickHouse单机版、ClickHouse集群版、Kylin商业版集群，数据集是我们的一个业务模块：事实表25亿数据（800GB）和 3个维度表。使用 Jmeter 调用 Golang 编写的 Restful API，完成了1、10、30、50、100 并发压测。（关于机器配置、数据集、查询条件详细内容见第三部分“压测准备“）

  1. ClickHouse单机版和集群版导入25亿条数据，用时维持在1小时左右。CPU 没有太高，30%以下，主要瓶颈在网络（200M带宽基本满载）。数据无任何丢失，准确性完整可靠。
  2. ClickHouse单机版和集群版导入800GB数据后，总磁盘占用80GB左右，接近1：10的压缩比。
  3. Kylin商业版数据存放在云上，所有数据我通过 gzip 压缩，压缩比也接近1:10。
  4. Kylin商业版全量构建模型，大约需要20小时（5台机器：8C 28GB。这个和模型复杂度相关，构建完成的模型大约300多GB）。
  5. 在低并发下，ClickHouse单机版与ClickHouse集群版性能持平在100～200ms，Kylin商业版集群需要3秒以上，大约慢了10倍+。
  6. 在高并发下，ClickHouse集群版（1秒）比ClickHouse单机版（2.5秒）快了2～3倍，比Kylin商业版集群快了100倍+。
  7. 从1、10、30、50 到 100 并发，ClickHouse单机版、ClickHouse集群版、Kylin商业版集群都能抵抗住压力。相对来说，后期Kylin的响应时间变的更长了。

3.压测准备

1. 数据集：采用真实业务数据。

  • 数据总量：800 GB(CSV)
  • 1个事实表：25 亿数据（17列）
  • 3个纬度表：
    • 纬度表A：720 万数据（9列）
    • 纬度表B：10000 数据（62列）
    • 纬度表C：1000 数据（96列）
  • 为了充分发挥数据库的特性，数据模型在不同的数据库上会有不同的设计，业务逻辑不变
    • ClickHouse 擅长单表查询，于是我会在原始表的基础上，创建一个新表(宽表)基于MergeTree引擎，通过 materialized view 实时同步数据到新表
    • Kyligence 擅长预计算，于是我会基于原始表，设计CUBE纬度，并加速查询

2. 机器配置

  • ClickHouse 单机
    • 主节点：16C、64G、40GB ESSD PL1 + 1024GB ESSD PL1
  • ClickHouse 集群
    • zk* 3: 2C、4G、100GB ESSD PL1
    • 主节点* 3：8C、32G、500GB ESSD PL1
    • 副本节点* 3：8C、32G、500GB ESSD PL1
  • Kyligence
    • 管理节点A：4C、14G、100GB HDD
    • 管理节点B：8C、28G、500GB HDD
    • 查询节点A：4C、14G、500GB HDD
    • 查询节点B：16C、56G、500GB HDD
    • 构建节点A：4C、14G、500GB HDD
    • 构建节点B* n(n>=2)：8C、28G、500GB HDD
    • Azure Blob Hot Storage * n(n>=1024GB)

3. 查询条件

  • Q1: 动态时间区间 > 1 天、< 30天；动态商铺ID、动态产品ID、动态Limit Offset；
  • Q2: 动态时间区间 > 1 天、< 30天；动态商铺ID、动态产品ID、更多动态Where条件、动态Limit Offset；
  • Q3: 动态时间区间 > 30 天、< 90天；动态商铺ID、动态产品ID、动态Limit Offset；
  • Q4: 动态时间区间 > 30 天、< 90天；动态商铺ID、动态产品ID、更多动态Where条件、动态Limit Offset；

4. 压测步骤

  • 使用 Golang 语言开发程序，生成查询SQL、请求数据库、提供统一的 API 接口
  • 使用 Jmeter 调用 Golang 提供的 Rest API 接口
  • 压测前，使用 Jmeter 单线程模式，循环调用10次 x 4组查询API ，预热系统
  • 压测中，使用 Jmeter 1并发、10并发、30并发、50并发、100并发 模式测试，每种并发，循环 3 次
  • 压测后，收集监控到的请求响应数据
    • 平均响应时间
    • 95% Line

5. 结果可视化

  • 使用 Python + plotly + pandas 生成图表

4.压测结果

1并发

10并发

30并发

50并发

100并发

5.SQL样本(已脱敏)

ClickHouse单机版

SELECT
table_x_distributed.fields1 AS fields1,
table_x_distributed.fields1 AS fields1,
table_x_distributed.fields2 AS fields2,
table_x_distributed.fields3 AS fields3,
table_x_distributed.fields4 AS fields4,
table_x_distributed.status AS status,
(
CASE
WHEN table_x_distributed.fields3 IN ('action1', 'action2') THEN table_x_distributed.fields5
ELSE - table_x_distributed.fields5
END
) AS fields5,
(
(
CASE
WHEN table_x_distributed.fields3 IN ('action1', 'action2') THEN table_x_distributed.fields5
ELSE - table_x_distributed.fields5
END
) / multiIf(
table_x_distributed.unit_order_rate == 0,
1,
table_x_distributed.unit_order_rate
)
) AS order_fields5,
table_x_distributed.num AS numerator,
table_x_distributed.deno AS denominator,
table_x_distributed.source AS source,
table_x_distributed.created_by AS created_by,
table_x_distributed.created AS created,
table_x_distributed.batch_no AS batch_no,
table_x_distributed.fields1 AS fields1,
table_x_distributed.fields1 AS fields1,
table_x_distributed.fields1 AS fields1,
table_x_distributed.store_fields2 AS store_fields2,
table_x_distributed.store_name AS store_name,
table_x_distributed.fields1 AS fields1,
table_x_distributed.product_fields2 AS product_fields2,
table_x_distributed.product_name AS product_name,
table_x_distributed.product_spec AS product_spec,
table_x_distributed.fields1 AS fields1,
table_x_distributed.category_1_name AS category_1_name,
table_x_distributed.category_1_fields2 AS category_1_fields2,
table_x_distributed.fields1 AS fields1,
table_x_distributed.unit_default_name AS unit_default_name,
table_x_distributed.unit_default_fields2 AS unit_default_fields2,
table_x_distributed.unit_default_rate AS unit_default_rate,
table_x_distributed.fields1 AS fields1,
table_x_distributed.unit_order_name AS unit_order_name,
table_x_distributed.unit_order_fields2 AS unit_order_fields2,
table_x_distributed.unit_order_rate AS unit_order_rate
FROM
xicha.table_x_distributed
WHERE
table_x_distributed.fields1 = 4219460797501276161
AND table_x_distributed.fields4 >= TIMESTAMP '2020-10-16 00:00:00'
AND table_x_distributed.fields4 <= TIMESTAMP '2020-12-17 23:59:59'
ORDER BY
fields4 DESC,
fields1,
fields1
LIMIT
87 OFFSET 0;

SELECT
COUNT(table_x_distributed.fields1) AS total,
SUM(
CASE
WHEN table_x_distributed.fields3 IN ('action1', 'action2') THEN table_x_distributed.fields5
ELSE - table_x_distributed.fields5
END
) AS amount_fields5
FROM
xicha.table_x_distributed
WHERE
table_x_distributed.fields1 = 4219460797501276161
AND table_x_distributed.fields4 >= TIMESTAMP '2020-10-16 00:00:00'
AND table_x_distributed.fields4 <= TIMESTAMP '2020-12-17 23:59:59';

SELECT
table_x_distributed.fields1 AS fields1,
table_x_distributed.fields1 AS fields1,
table_x_distributed.fields2 AS fields2,
table_x_distributed.fields3 AS fields3,
table_x_distributed.fields4 AS fields4,
table_x_distributed.status AS status,
(
CASE
WHEN table_x_distributed.fields3 IN ('action1', 'action2') THEN table_x_distributed.fields5
ELSE - table_x_distributed.fields5
END
) AS fields5,
(
(
CASE
WHEN table_x_distributed.fields3 IN ('action1', 'action2') THEN table_x_distributed.fields5
ELSE - table_x_distributed.fields5
END
) / multiIf(
table_x_distributed.unit_order_rate == 0,
1,
table_x_distributed.unit_order_rate
)
) AS order_fields5,
table_x_distributed.num AS numerator,
table_x_distributed.deno AS denominator,
table_x_distributed.source AS source,
table_x_distributed.created_by AS created_by,
table_x_distributed.created AS created,
table_x_distributed.batch_no AS batch_no,
table_x_distributed.fields1 AS fields1,
table_x_distributed.fields1 AS fields1,
table_x_distributed.fields1 AS fields1,
table_x_distributed.store_fields2 AS store_fields2,
table_x_distributed.store_name AS store_name,
table_x_distributed.fields1 AS fields1,
table_x_distributed.product_fields2 AS product_fields2,
table_x_distributed.product_name AS product_name,
table_x_distributed.product_spec AS product_spec,
table_x_distributed.fields1 AS fields1,
table_x_distributed.category_1_name AS category_1_name,
table_x_distributed.category_1_fields2 AS category_1_fields2,
table_x_distributed.fields1 AS fields1,
table_x_distributed.unit_default_name AS unit_default_name,
table_x_distributed.unit_default_fields2 AS unit_default_fields2,
table_x_distributed.unit_default_rate AS unit_default_rate,
table_x_distributed.fields1 AS fields1,
table_x_distributed.unit_order_name AS unit_order_name,
table_x_distributed.unit_order_fields2 AS unit_order_fields2,
table_x_distributed.unit_order_rate AS unit_order_rate
FROM
xicha.table_x_distributed
WHERE
table_x_distributed.fields1 = 4229398614037757953
AND table_x_distributed.fields4 >= TIMESTAMP '2021-01-13 00:00:00'
AND table_x_distributed.fields4 <= TIMESTAMP '2021-01-16 23:59:59'
AND table_x_distributed.fields1 IN (
4328489245797875712,
4219461287022690305,
4393275840849379328
)
AND table_x_distributed.fields1 IN (
4434516293426741248,
4434516293544181760,
4434516293732925440,
4434516293636456448,
4219461197910507521,
4434516293821005824,
4219461198292189185,
4434516293909086208,
0,
4219461198401241089
)
AND table_x_distributed.fields2 IN (
'action1',
'action2-W',
'action3
'action4-M',
'action5',
'action6-D',
'action7',
'action8',
'action9',
'action10'
)
ORDER BY
fields4 DESC,
fields1,
fields1
LIMIT
98 OFFSET 0;

SELECT
COUNT(table_x_distributed.fields1) AS total,
SUM(
CASE
WHEN table_x_distributed.fields3 IN ('action1', 'action2') THEN table_x_distributed.fields5
ELSE - table_x_distributed.fields5
END
) AS amount_fields5
FROM
xicha.table_x_distributed
WHERE
table_x_distributed.fields1 = 4229398614037757953
AND table_x_distributed.fields4 >= TIMESTAMP '2021-01-13 00:00:00'
AND table_x_distributed.fields4 <= TIMESTAMP '2021-01-16 23:59:59'
AND table_x_distributed.fields1 IN (
4328489245797875712,
4219461287022690305,
4393275840849379328
)
AND table_x_distributed.fields1 IN (
4434516293426741248,
4434516293544181760,
4434516293732925440,
4434516293636456448,
4219461197910507521,
4434516293821005824,
4219461198292189185,
4434516293909086208,
0,
4219461198401241089
)
AND table_x_distributed.fields2 IN (
'action1',
'action2-W',
'action3
'action4-M',
'action5',
'action6-D',
'action7',
'action8',
'action9',
'action10'
);

ClickHouse集群版

SELECT
table_x_distributed.fields1 AS fields1,
table_x_distributed.fields1 AS fields1,
table_x_distributed.fields2 AS fields2,
table_x_distributed.fields3 AS fields3,
table_x_distributed.fields4 AS fields4,
table_x_distributed.fields5 AS fields5,
(
CASE
WHEN table_x_distributed.fields3 IN ('aciotn1', 'action2') THEN table_x_distributed.fields5
ELSE - table_x_distributed.fields5
END
) AS fields5,
(
(
CASE
WHEN table_x_distributed.fields3 IN ('aciotn1', 'action2') THEN table_x_distributed.fields5
ELSE - table_x_distributed.fields5
END
) / multiIf(
table_x_distributed.unit_order_rate == 0,
1,
table_x_distributed.unit_order_rate
)
) AS fields5,
table_x_distributed.num AS numerator,
table_x_distributed.deno AS denominator,
table_x_distributed.source AS source,
table_x_distributed.created_by AS created_by,
table_x_distributed.created AS created,
table_x_distributed.batch_no AS batch_no,
table_x_distributed.fields1 AS fields1,
table_x_distributed.fields1 AS fields1,
table_x_distributed.fields1 AS fields1,
table_x_distributed.store_fields2 AS store_fields2,
table_x_distributed.store_name AS store_name,
table_x_distributed.fields1 AS fields1,
table_x_distributed.product_fields2 AS product_fields2,
table_x_distributed.product_name AS product_name,
table_x_distributed.product_spec AS product_spec,
table_x_distributed.fields1 AS fields1,
table_x_distributed.category_1_name AS category_1_name,
table_x_distributed.category_1_fields2 AS category_1_fields2,
table_x_distributed.fields1 AS fields1,
table_x_distributed.unit_default_name AS unit_default_name,
table_x_distributed.unit_default_fields2 AS unit_default_fields2,
table_x_distributed.unit_default_rate AS unit_default_rate,
table_x_distributed.fields1 AS fields1,
table_x_distributed.unit_order_name AS unit_order_name,
table_x_distributed.unit_order_fields2 AS unit_order_fields2,
table_x_distributed.unit_order_rate AS unit_order_rate
FROM
xicha.table_x_distributed
WHERE
table_x_distributed.fields1 = 4219460800332431361
AND table_x_distributed.fields4 >= TIMESTAMP '2020-11-14 00:00:00'
AND table_x_distributed.fields4 <= TIMESTAMP '2020-11-15 23:59:59'
ORDER BY
fields4 DESC,
fields1,
fields1
LIMIT
69 OFFSET 0;

SELECT
COUNT(table_x_distributed.fields1) AS total,
SUM(
CASE
WHEN table_x_distributed.fields3 IN ('action1', 'action2') THEN table_x_distributed.fields5
ELSE - table_x_distributed.fields5
END
) AS fields5
FROM
xicha.table_x_distributed
WHERE
table_x_distributed.fields1 = 4219460800332431361
AND table_x_distributed.fields4 >= TIMESTAMP '2020-11-14 00:00:00'
AND table_x_distributed.fields4 <= TIMESTAMP '2020-11-15 23:59:59';

SELECT
table_x_distributed.fields1 AS fields1,
table_x_distributed.fields1 AS fields1,
table_x_distributed.fields2 AS fields2,
table_x_distributed.fields3 AS fields3,
table_x_distributed.fields4 AS fields4,
table_x_distributed.fields5 AS fields5,
(
CASE
WHEN table_x_distributed.fields3 IN ('action1', 'action2') THEN table_x_distributed.fields5
ELSE - table_x_distributed.fields5
END
) AS fields5,
(
(
CASE
WHEN table_x_distributed.fields3 IN ('action1', 'action2') THEN table_x_distributed.fields5
ELSE - table_x_distributed.fields5
END
) / multiIf(
table_x_distributed.unit_order_rate == 0,
1,
table_x_distributed.unit_order_rate
)
) AS fields5,
table_x_distributed.num AS numerator,
table_x_distributed.deno AS denominator,
table_x_distributed.source AS source,
table_x_distributed.created_by AS created_by,
table_x_distributed.created AS created,
table_x_distributed.batch_no AS batch_no,
table_x_distributed.fields1 AS fields1,
table_x_distributed.fields1 AS fields1,
table_x_distributed.fields1 AS fields1,
table_x_distributed.store_fields2 AS store_fields2,
table_x_distributed.store_name AS store_name,
table_x_distributed.fields1 AS fields1,
table_x_distributed.product_fields2 AS product_fields2,
table_x_distributed.product_name AS product_name,
table_x_distributed.product_spec AS product_spec,
table_x_distributed.fields1 AS fields1,
table_x_distributed.category_1_name AS category_1_name,
table_x_distributed.category_1_fields2 AS category_1_fields2,
table_x_distributed.fields1 AS fields1,
table_x_distributed.unit_default_name AS unit_default_name,
table_x_distributed.unit_default_fields2 AS unit_default_fields2,
table_x_distributed.unit_default_rate AS unit_default_rate,
table_x_distributed.fields1 AS fields1,
table_x_distributed.unit_order_name AS unit_order_name,
table_x_distributed.unit_order_fields2 AS unit_order_fields2,
table_x_distributed.unit_order_rate AS unit_order_rate
FROM
xicha.table_x_distributed
WHERE
table_x_distributed.fields1 = 4219460802471526401
AND table_x_distributed.fields4 >= TIMESTAMP '2020-11-14 00:00:00'
AND table_x_distributed.fields4 <= TIMESTAMP '2020-11-15 23:59:59'
AND table_x_distributed.fields1 IN (
4287213507203596288,
4272080930383855617,
4365296557224984576
)
AND table_x_distributed.fields1 IN (
4434516293426741248,
4434516293544181760,
4434516293732925440,
4434516293636456448,
4219461197910507521,
4434516293821005824,
4219461198292189185,
4434516293909086208,
0,
4219461198401241089
)
AND table_x_distributed.fields2 IN (
'action1',
'action2-W',
'action3
'action4-M',
'action5',
'action6-D',
'action7',
'action8',
'action9',
'action10'
)
ORDER BY
fields4 DESC,
fields1,
fields1
LIMIT
69 OFFSET 0;

SELECT
COUNT(table_x_distributed.fields1) AS total,
SUM(
CASE
WHEN table_x_distributed.fields3 IN ('action1', 'action2') THEN table_x_distributed.fields5
ELSE - table_x_distributed.fields5
END
) AS fields5
FROM
xicha.table_x_distributed
WHERE
table_x_distributed.fields1 = 4219460802471526401
AND table_x_distributed.fields4 >= TIMESTAMP '2020-11-14 00:00:00'
AND table_x_distributed.fields4 <= TIMESTAMP '2020-11-15 23:59:59'
AND table_x_distributed.fields1 IN (
4287213507203596288,
4272080930383855617,
4365296557224984576
)
AND table_x_distributed.fields1 IN (
4434516293426741248,
4434516293544181760,
4434516293732925440,
4434516293636456448,
4219461197910507521,
4434516293821005824,
4219461198292189185,
4434516293909086208,
0,
4219461198401241089
)
AND table_x_distributed.fields2 IN (
'action1',
'action2-W',
'action3
'action4-M',
'action5',
'action6-D',
'action7',
'action8',
'action9',
'action10'
);

Kylin商业版集群

SELECT
table1_view.fields1 AS fields1,
table1_view.fields1 AS fields1,
table1_view.fields2 AS fields2,
table1_view.fields3 AS fields3,
table1_view.fields4 AS fields4,
table1_view.fields5 AS fields5,
table1_view.cal_qty AS qty,
table1_view.cal_order_qty AS order_qty,
table1_view.num AS numerator,
table1_view.deno AS denominator,
table1_view.source AS source,
table1_view.created_by AS created_by,
table1_view.created AS created,
table1_view.batch_no AS batch_no,
table1_view.fields1 AS fields1,
table4_view.fields1 AS fields1,
table2_view.fields1 AS fields1,
table2_view.fields2 AS fields2,
table2_view.name AS table2_name,
table2_view.fields1 AS fields1,
table2_view.fields2 AS fields2,
table2_view.name AS table2_name,
table2_view.model_name AS table2_spec,
table2_view.fields1 AS fields1,
table2_view.category_1_name AS category_1_name,
table2_view.fields2 AS fields2,
table2_view.fields1 AS fields1,
table2_view.unit_default_name AS unit_default_name,
table2_view.fields2 AS fields2,
table2_view.unit_default_rate AS unit_default_rate,
table2_view.fields1 AS fields1,
table2_view.unit_order_name AS unit_order_name,
table2_view.fields2 AS fields2,
table2_view.unit_order_rate AS unit_order_rate
FROM
database1.table1_view
join database1.table4_view ON table1_view.fields1 = table4_view.fields1
join database1.table3_view ON table4_view.fields1 =table2_view.fields1
join database1.table2_view ON table4_view.fields1 = table2_view.fields1
WHERE
table2_view.fields1 = 4219460802471526401
AND table1_view.fields4 >= TIMESTAMP '2021-01-14 00:00:00'
AND table1_view.fields4 <= TIMESTAMP '2021-01-16 23:59:59'
ORDER BY
table1_view.fields4 DESC,
table1_view.fields1 DESC
LIMIT
46 OFFSET 0;

SELECT
COUNT(table1_view.fields1) AS total,
SUM(table1_view.cal_qty) AS amount_qty
FROM
database1.table1_view
join database1.table4_view ON table1_view.fields1 = table4_view.fields1
join database1.table3_view ON table4_view.fields1 =table2_view.fields1
join database1.table2_view ON table4_view.fields1 = table2_view.fields1
WHERE
table2_view.fields1 = 4219460802471526401
AND table1_view.fields4 >= TIMESTAMP '2021-01-14 00:00:00'
AND table1_view.fields4 <= TIMESTAMP '2021-01-16 23:59:59';

SELECT
table1_view.fields1 AS fields1,
table1_view.fields1 AS fields1,
table1_view.fields2 AS fields2,
table1_view.fields3 AS fields3,
table1_view.fields4 AS fields4,
table1_view.fields5 AS fields5,
table1_view.cal_qty AS qty,
table1_view.cal_order_qty AS order_qty,
table1_view.num AS numerator,
table1_view.deno AS denominator,
table1_view.source AS source,
table1_view.created_by AS created_by,
table1_view.created AS created,
table1_view.batch_no AS batch_no,
table1_view.fields1 AS fields1,
table4_view.fields1 AS fields1,
table2_view.fields1 AS fields1,
table2_view.fields2 AS fields2,
table2_view.name AS table2_name,
table2_view.fields1 AS fields1,
table2_view.fields2 AS fields2,
table2_view.name AS table2_name,
table2_view.model_name AS table2_spec,
table2_view.fields1 AS fields1,
table2_view.category_1_name AS category_1_name,
table2_view.fields2 AS fields2,
table2_view.fields1 AS fields1,
table2_view.unit_default_name AS unit_default_name,
table2_view.fields2 AS fields2,
table2_view.unit_default_rate AS unit_default_rate,
table2_view.fields1 AS fields1,
table2_view.unit_order_name AS unit_order_name,
table2_view.fields2 AS fields2,
table2_view.unit_order_rate AS unit_order_rate
FROM
database1.table1_view
join database1.table4_view ON table1_view.fields1 = table4_view.fields1
join database1.table3_view ON table4_view.fields1 =table2_view.fields1
join database1.table2_view ON table4_view.fields1 = table2_view.fields1
WHERE
table2_view.fields1 = 4219460798721818625
AND table1_view.fields4 >= TIMESTAMP '2021-01-14 00:00:00'
AND table1_view.fields4 <= TIMESTAMP '2021-01-16 23:59:59'
AND table2_view.fields1 IN (
4263961283415506945,
4393275841348501504,
4251246369173405697
)
AND table2_view.fields1 IN (
4434516293426741248,
4434516293544181760,
4434516293732925440,
4434516293636456448,
4219461197910507521,
4434516293821005824,
4219461198292189185,
4434516293909086208,
0,
4219461198401241089
)
AND table1_view.fields2 IN (
'action1',
'action2-W',
'action3
'action4-M',
'action5',
'action6-D',
'action7',
'action8',
'action9',
'action10'
)
ORDER BY
table1_view.fields4 DESC,
table1_view.fields1 DESC
LIMIT
46 OFFSET 0;

SELECT
COUNT(table1_view.fields1) AS total,
SUM(table1_view.cal_qty) AS amount_qty
FROM
database1.table1_view
join database1.table4_view ON table1_view.fields1 = table4_view.fields1
join database1.table3_view ON table4_view.fields1 =table2_view.fields1
join database1.table2_view ON table4_view.fields1 = table2_view.fields1
WHERE
table2_view.fields1 = 4219460798721818625
AND table1_view.fields4 >= TIMESTAMP '2021-01-14 00:00:00'
AND table1_view.fields4 <= TIMESTAMP '2021-01-16 23:59:59'
AND table2_view.fields1 IN (
4263961283415506945,
4393275841348501504,
4251246369173405697
)
AND table2_view.fields1 IN (
4434516293426741248,
4434516293544181760,
4434516293732925440,
4434516293636456448,
4219461197910507521,
4434516293821005824,
4219461198292189185,
4434516293909086208,
0,
4219461198401241089
)
AND table1_view.fields2 IN (
'action1',
'action2-W',
'action3
'action4-M',
'action5',
'action6-D',
'action7',
'action8',
'action9',
'action10'
);

6.服务器监控

ClickHouse单机版

ClickHouse集群版

Kylin商业版集群

End. 👻

Share:
Show Comments