暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 干货｜以 100GB SSB 性能测试为例，通过 ByteHouse 云数仓开启你的数据分析之路

干货｜以 100GB SSB 性能测试为例，通过 ByteHouse 云数仓开启你的数据分析之路

字节跳动数据平台 2023-06-25
360


从传统数仓、湖仓一体再到云数仓，技术快速迭代，云数仓已经成为数字化基础设施中的关键“底座”。如何才能丝滑使用一款云数仓产品，本篇文章用五个步骤教你搞定！

文 | Aurora  来自火山引擎ByteHouse团队

近年来，随着数据“爆炸式”的增长，越来越多的数据被产生、收集和存储。而挖掘海量数据中的真实价值，从其中提取商机并洞见未来，则成了现代企业和组织不可忽视的命题。

随着数据量级和复杂度的增大，数据分析处理的技术架构也在不断演进。在面对海量数据分析时，传统 OLAP 技术架构中的痛点变得越来越明显，如扩容缩容耗时长，导致资源利用率偏低，成本居高不下；以及运维配置复杂，需要专业的技术人员介入等。

为了解决这类问题，云数仓的概念应运而生。和传统数仓架构不同的是，云原生数仓借助于云平台的基础资源，实现了资源的动态扩缩容，并最大化利用资源，从而达到 Pay as you go 按实际用量付费的模式。

ByteHouse 作为云原生的数据平台，从架构层面入手，通过存储和计算分离的云原生架构完美适配云上基础设施。在字节跳动内部，ByteHouse 已经支持 80% 的分析应用场景，包括用户增长业务、广告、A/B 测试等。除了极致的分析性能之外，ByteHouse 开箱即用，按实际使用付费的特性也极大地降低了企业和个人的上手门槛，能够在短短数分钟内体验到数据分析的魅力。

Talk is cheap, 接下来就让我们通过一个实战案例来体验下 ByteHouse 云数仓的强大功能。

本章节通过使用 ByteHouse 云数仓进行 SSB 基准测试，在带领读者了解产品性能的同时，也一并熟悉产品中各个模块的功能，开启你的数据分析之路，通过分析海量数据，加速数据洞察。

ByteHouse 的架构总览如下：



/ SSB 基准测试


SSB（Star Schema Benchmark）是由麻省州立大学波士顿校区的研究员定义的基于现实商业应用的数据模型。SSB 是在 TPC-H 标准的基础上改进而成，主要将 TPC-H 中的雪花模型改成了更为通用的的星型模型，将基准查询从复杂的 Ad-hoc 查询改成了结构更加固定的 OLAP 查询，从而主要用于模拟测试 OLAP 引擎和轻量数仓场景下的查询性能。由于 SSB 基准测试较为中立，并贴近现实的商业场景，因此在学界及工业界有广泛的应用。

SSB 基准测试中对应的表结构如下所示，可以看到 SSB 主要采用星型模型，其中包含了 1 个事实表 lineorder 和 4 个维度表 customer, part, dwdate 以及 supplier，每张维度表通过 Primary Key 和事实表进行关联。测试通过执行 13 条 SQL 进行查询，包含了多表关联，group by，复杂条件等多种组合。更多详细信息请参考 SSB 文献。

/ 步骤一：官网注册并开通 ByteHouse


访问 ByteHouse 云数仓火山引擎官网，注册火山引擎账户，完成实名认证后，即可登录到产品控制台。开通产品进行测试，目前 ByteHouse 支持包年包月和按量付费两种模式的实例，便于您根据业务需求进行选择。



/ 步骤二：创建计算组


登录到控制台后，可以看到数据库表管理、数据加载、SQL 工作表、计算组、查询历史和角色管理等几大模块。分别具有如下作用：

  • 数据库表管理：用于创建和管理数据库、数据表以及视图等数据对象

  • 数据加载：用于从不同的离线和实时数据源如对象存储、Kafka 等地写入数据

  • SQL 工作表：在界面上编辑、管理并运行 SQL 查询

  • 计算组：创建和管理虚拟的计算资源，用于执行数据查询等操作

  • 查询历史：用于查看 SQL 的历史执行记录、状态和查询详情等



为了方便进行后续的建库建表和查询等操作，首先在 ByteHouse 控制台创建型号为 L 的计算组，如下图所示：


计算组是 Bytehouse 中的计算资源集群，可按需进行横向扩展。计算组提供所需的资源如 CPU、内存及临时存储等，用于执行数据查询 DQL、DML 等操作。ByteHouse 计算组能够实现弹性扩缩容，读写分离、存算分离等，并且能对资源进行细粒度的权限控制。

步骤三：创建数据库表


在控制台页面中创建名为 ssb_100 的数据库。


创建完毕后，进入到 SQL 工作表模块，通过如下建表语句建立四个数据表（事实表），并保存对应的 SQL 语句。

    CREATE TABLE ssb_100.customer
    (
            C_CUSTKEY       UInt32,
            C_NAME          String,
            C_ADDRESS       String,
            C_CITY          LowCardinality(String),
            C_NATION        LowCardinality(String),
            C_REGION        LowCardinality(String),
            C_PHONE         String,
            C_MKTSEGMENT    LowCardinality(String),
            C_PLACEHOLDER   Nullable(String)
    )
    ENGINE = CnchMergeTree ORDER BY (C_CUSTKEY);


    CREATE TABLE ssb_100.lineorder
    (
        LO_ORDERKEY             UInt32,
        LO_LINENUMBER           UInt8,
        LO_CUSTKEY              UInt32,
        LO_PARTKEY              UInt32,
        LO_SUPPKEY              UInt32,
        LO_ORDERDATE            Date,
        LO_ORDERPRIORITY        LowCardinality(String),
        LO_SHIPPRIORITY         UInt8,
        LO_QUANTITY             UInt8,
        LO_EXTENDEDPRICE        UInt32,
        LO_ORDTOTALPRICE        UInt32,
        LO_DISCOUNT             UInt8,
        LO_REVENUE              UInt32,
        LO_SUPPLYCOST           UInt32,
        LO_TAX                  UInt8,
        LO_COMMITDATE           Date,
        LO_SHIPMODE             LowCardinality(String),
        LO_PLACEHOLDER          Nullable(String)
    )
    ENGINE = CnchMergeTree PARTITION BY toYear(LO_ORDERDATE) ORDER BY (LO_ORDERDATE, LO_ORDERKEY);


    CREATE TABLE ssb_100.part
    (
            P_PARTKEY       UInt32,
            P_NAME          String,
            P_MFGR          LowCardinality(String),
            P_CATEGORY      LowCardinality(String),
            P_BRAND         LowCardinality(String),
            P_COLOR         LowCardinality(String),
            P_TYPE          LowCardinality(String),
            P_SIZE          UInt8,
            P_CONTAINER     LowCardinality(String),
            P_PLACEHOLDER   Nullable(String)
    )
    ENGINE = CnchMergeTree ORDER BY P_PARTKEY;


    CREATE TABLE ssb_100.supplier
    (
            S_SUPPKEY       UInt32,
            S_NAME          String,
            S_ADDRESS       String,
            S_CITY          LowCardinality(String),
            S_NATION        LowCardinality(String),
            S_REGION        LowCardinality(String),
            S_PHONE         String,
            S_PLACEHOLDER   Nullable(String)
    )
    ENGINE = CnchMergeTree ORDER BY S_SUPPKEY;


    CREATE TABLE ssb_100.dwdate
    (
            D_DATEKEY            UInt32,
            D_DATE               String,
            D_DAYOFWEEK          String,    -- defined in Section 2.6 as Size 8, but Wednesday is 9 letters
            D_MONTH              String,
            D_YEAR               UInt32,
            D_YEARMONTHNUM       UInt32,
            D_YEARMONTH          String,
            D_DAYNUMINWEEK       UInt32,
            D_DAYNUMINMONTH      UInt32,
            D_DAYNUMINYEAR       UInt32,
            D_MONTHNUMINYEAR     UInt32,
            D_WEEKNUMINYEAR      UInt32,
            D_SELLINGSEASON      String,
            D_LASTDAYINWEEKFL    UInt32,
            D_LASTDAYINMONTHFL   UInt32,
            D_HOLIDAYFL          UInt32,
            D_WEEKDAYFL          UInt32,
            S_PLACEHOLDER        Nullable(String)
    )
    ENGINE=CnchMergeTree() ORDER BY (D_DATEKEY);

    SQL 执行完毕后，在控制台左侧对应的数据对象页面会展示出创建完成的五个工作表，分别为 customer,dwdate,lineorder以及part 和 supplier。




    / 步骤四：从对象存储中导入 SSB 数据


    通过预先生成 SSB_100 GB 的数据集并存储在对象存储（如 AWS S3 或者 火山引擎 TOS），我们可以方便且快速的将数据导入到 ByteHouse 中进行分析。本次实践中通过配置 火山引擎 TOS 的数据源对数据进行导入。

    首先在数据加载模块，新建对象存储数据源，并配置对应的秘钥连接火山引擎对象存储。


    连接新的数据源后，选择 bytehouse-shared-dataset 的储存桶和ssb_100/lineorder.csv 相应的路径



    选择之前建的数据库ssb_100和对应标表lineorder，然后按创建。重复步骤为其他四个工作表数据加载。


    数据源中存储的数据条数如下所示。用于导入完成后，对数据表的行数进行统计，进行准确性校验。


    Table 表
    Bucket Path 路径
    Rows 行
    Size 大小
    lineorder
    ssb_100/lineorder.csv
    600,038,145
    246.30 GB
    customer
    ssb_100/customer.csv
    3,000,000
    part
    ssb_100/part.csv
    1,400,000
    supplier
    ssb_100/supplier.csv
    200,000
    dwdate
    ssb_100/dwdate.csv
    2,556

    创建导入任务完成后，点击“开始”启动导入任务，任务启动后会在几秒钟内分配资源并初始化导入任务，并在导入过程中展示预估的时间和导入进度。在导入任务的执行详情中，可以查看导入状态、导入详细日志、配置信息等。




    步骤五：数据处理及分析


    1. 原始查询测试


    通过执行 SSB 的 13 条查询语句，对于多表关联和排序等场景进行性能测试。查询语句如下所示：
      -- pre-warm
      select * from ssb_100.customer order by C_CUSTKEY desc limit 100;
      select * from ssb_100.dwdate order by D_DATEKEY desc limit 100;
      select * from ssb_100.lineorder order by LO_ORDERKEY desc limit 100;
      select * from ssb_100.part order by P_PARTKEY desc limit 100;
      select * from ssb_100.supplier order by S_SUPPKEY desc limit 100;
      select * from ssb_100.lineorder_flat order by LO_ORDERKEY desc limit 100;


      -- Q1.1
      select sum(LO_EXTENDEDPRICE*LO_DISCOUNT) as revenue
      from ssb_100.lineorder
      where toYear(LO_ORDERDATE) = 1993
      and LO_DISCOUNT between 1 and 3
      and LO_QUANTITY < 25;


      -- Q1.2
      select sum(LO_EXTENDEDPRICE*LO_DISCOUNT) as revenue
      from ssb_100.lineorder
      where toYYYYMM(LO_ORDERDATE) = 199401
      and LO_DISCOUNT between 4 and 6
      and LO_QUANTITY between 26 and 35;


      -- Q1.3
      select sum(LO_EXTENDEDPRICE*LO_DISCOUNT) as revenue
      from ssb_100.lineorder
      where toISOWeek(LO_ORDERDATE) = 6
      and toYear(LO_ORDERDATE)= 1994
      and LO_DISCOUNT between 5 and 7
      and LO_QUANTITY between 26 and 35;


      -- Q2.1
      select sum(LO_REVENUE), toYear(LO_ORDERDATE) AS d_year, P_BRAND
      from ssb_100.lineorder, ssb_100.part, ssb_100.supplier
      where LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY
      and P_CATEGORY = 'MFGR#53' and S_REGION = 'AMERICA'
      GROUP BY d_year, P_BRAND;


      -- Q2.2
      SELECT sum(LO_REVENUE), toYear(LO_ORDERDATE) AS year, P_BRAND
      FROM ssb_100.lineorder, ssb_100.part, ssb_100.supplier
      WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY
      and P_BRAND >= 'MFGR#2221' and P_BRAND <= 'MFGR#2228' and S_REGION = 'ASIA'
      GROUP BY year, P_BRAND
      ORDER BY year, P_BRAND;


      -- Q2.3
      SELECT sum(LO_REVENUE), toYear(LO_ORDERDATE) AS year, P_BRAND
      FROM ssb_100.lineorder, ssb_100.part, ssb_100.supplier
      WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY 
      and P_BRAND = 'MFGR#2239'and S_REGION = 'EUROPE'
      GROUP BY year, P_BRAND
      ORDER BY year, P_BRAND;


      -- Q3.1
      SELECT C_NATION, S_NATION, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue
      FROM ssb_100.lineorder, ssb_100.part, ssb_100.supplier, ssb_100.customer
      WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY and LO_CUSTKEY = C_CUSTKEY
      and C_REGION = 'ASIA' AND S_REGION = 'ASIA' AND year >= 1992 AND year <= 1997
      GROUP BY C_NATION, S_NATION, year
      ORDER BY year ASC, revenue DESC;


      -- Q3.2 
      SELECT C_CITY, S_CITY, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue
      FROM ssb_100.lineorder, ssb_100.part, ssb_100.supplier, ssb_100.customer
      WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY and LO_CUSTKEY = C_CUSTKEY
      and C_NATION = 'UNITED STATES' AND S_NATION = 'UNITED STATES' AND year >= 1992 AND year <= 1997
      GROUP BY C_CITY, S_CITY, year
      ORDER BY year ASC, revenue DESC;


      -- Q3.3
      SELECT C_CITY, S_CITY, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue
      FROM ssb_100.lineorder, ssb_100.part, ssb_100.supplier, ssb_100.customer
      WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY and LO_CUSTKEY = C_CUSTKEY
      and (C_CITY = 'UNITED KI1' OR C_CITY = 'UNITED KI5') AND (S_CITY = 'UNITED KI1' OR S_CITY = 'UNITED KI5') AND year >= 1992 AND year <= 1997
      GROUP BY C_CITY, S_CITY, year
      ORDER BY year ASC, revenue DESC;


      -- Q3.4
      SELECT C_CITY, S_CITY, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue
      FROM ssb_100.lineorder, ssb_100.part, ssb_100.supplier, ssb_100.customer
      WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY and LO_CUSTKEY = C_CUSTKEY
      and (C_CITY = 'UNITED KI1' OR C_CITY = 'UNITED KI5') AND (S_CITY = 'UNITED KI1' OR S_CITY = 'UNITED KI5') AND toYYYYMM(LO_ORDERDATE) = 199712
      GROUP BY C_CITY, S_CITY, year
      ORDER BY year ASC, revenue DESC;


      -- Q4.1
      SELECT toYear(LO_ORDERDATE) AS year, C_NATION, sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
      FROM ssb_100.lineorder, ssb_100.part, ssb_100.supplier, ssb_100.customer
      WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY and LO_CUSTKEY = C_CUSTKEY
      and C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2')
      GROUP BY year, C_NATION
      ORDER BY year ASC, C_NATION ASC;


      -- Q4.2
      SELECT toYear(LO_ORDERDATE) AS year, S_NATION, P_CATEGORY, sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
      FROM ssb_100.lineorder, ssb_100.part, ssb_100.supplier, ssb_100.customer
      WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY and LO_CUSTKEY = C_CUSTKEY
      and C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND (year = 1997 OR year = 1998) AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2')
      GROUP BY year, S_NATION, P_CATEGORY
      ORDER BY year ASC, S_NATION ASC, P_CATEGORY ASC;


      -- Q4.3
      SELECT toYear(LO_ORDERDATE) AS year, S_CITY, P_BRAND, sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
      FROM ssb_100.lineorder, ssb_100.part, ssb_100.supplier
      WHERE LO_PARTKEY = P_PARTKEY and LO_SUPPKEY = S_SUPPKEY
      and S_NATION = 'UNITED STATES' AND (year = 1997 OR year = 1998) AND P_CATEGORY = 'MFGR#14'
      GROUP BY year, S_CITY, P_BRAND
      ORDER BY year ASC, S_CITY ASC, P_BRAND ASC;


      2. 打平表测试


      为了方便对 SSB 数据集进行测试，我们可以通过改写 SSB，将星型模型打平转换为大宽表进行分析。
      注：为了确保打平表的执行，需要配置参数 SET max_memory_usage = 20000000000; 此外需要在 ByteHouse 控制台中配置查询超时为 3600s (我的账户 > 查询配置 > 查询超时)，避免执行超时导致的失败。
        SET max_memory_usage = 20000000000;
        SET send_timeout = 3600;
        SET receive_timeout = 3600;


        CREATE TABLE IF NOT EXISTS ssb_100.lineorder_flat
        engine = CnchMergeTree
        partition by toYear(LO_ORDERDATE)
        order by (LO_ORDERDATE, LO_ORDERKEY) as
        select
            L.LO_ORDERKEY as LO_ORDERKEY,
            L.LO_LINENUMBER as LO_LINENUMBER,
            L.LO_CUSTKEY as LO_CUSTKEY,
            L.LO_PARTKEY as LO_PARTKEY,
            L.LO_SUPPKEY as LO_SUPPKEY,
            L.LO_ORDERDATE as LO_ORDERDATE,
            L.LO_ORDERPRIORITY as LO_ORDERPRIORITY,
            L.LO_SHIPPRIORITY as LO_SHIPPRIORITY,
            L.LO_QUANTITY as LO_QUANTITY,
            L.LO_EXTENDEDPRICE as LO_EXTENDEDPRICE,
            L.LO_ORDTOTALPRICE as LO_ORDTOTALPRICE,
            L.LO_DISCOUNT as LO_DISCOUNT,
            L.LO_REVENUE as LO_REVENUE,
            L.LO_SUPPLYCOST as LO_SUPPLYCOST,
            L.LO_TAX as LO_TAX,
            L.LO_COMMITDATE as LO_COMMITDATE,
            L.LO_SHIPMODE as LO_SHIPMODE,
            C.C_NAME as C_NAME,
            C.C_ADDRESS as C_ADDRESS,
            C.C_CITY as C_CITY,
            C.C_NATION as C_NATION,
            C.C_REGION as C_REGION,
            C.C_PHONE as C_PHONE,
            C.C_MKTSEGMENT as C_MKTSEGMENT,
            S.S_NAME as S_NAME,
            S.S_ADDRESS as S_ADDRESS,
            S.S_CITY as S_CITY,
            S.S_NATION as S_NATION,
            S.S_REGION as S_REGION,
            S.S_PHONE as S_PHONE,
            P.P_NAME as P_NAME,
            P.P_MFGR as P_MFGR,
            P.P_CATEGORY as P_CATEGORY,
            P.P_BRAND as P_BRAND,
            P.P_COLOR as P_COLOR,
            P.P_TYPE as P_TYPE,
            P.P_SIZE as P_SIZE,
            P.P_CONTAINER as P_CONTAINER
        from ssb_100.lineorder as L
        inner join ssb_100.customer as C on C.C_CUSTKEY = L.LO_CUSTKEY
        inner join ssb_100.supplier as S on S.S_SUPPKEY = L.LO_SUPPKEY
        inner join ssb_100.part as P on P.P_PARTKEY = L.LO_PARTKEY;

        建表完成后，通过执行查询语句进行 SSB 性能测试，如下所示：

          -- F1.1
          SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
          FROM ssb_100.lineorder_flat
          WHERE toYear(LO_ORDERDATE) = 1993
          AND LO_DISCOUNT BETWEEN 1 AND 3 
          AND LO_QUANTITY < 25;


          -- F1.2
          SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
          FROM ssb_100.lineorder_flat
          WHERE toYYYYMM(LO_ORDERDATE) = 199401
          AND LO_DISCOUNT BETWEEN 4 AND 6
          AND LO_QUANTITY BETWEEN 26 AND 35;


          -- F1.3
          SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
          FROM ssb_100.lineorder_flat
          WHERE toISOWeek(LO_ORDERDATE) = 6
          AND toYear(LO_ORDERDATE) = 1994
          AND LO_DISCOUNT BETWEEN 5 AND 7
          AND LO_QUANTITY BETWEEN 26 AND 35;
            
          -- F2.1
          SELECT sum(LO_REVENUE), toYear(LO_ORDERDATE) AS year, P_BRAND
          FROM ssb_100.lineorder_flat
          WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA'
          GROUP BY year, P_BRAND
          ORDER BY year, P_BRAND;


          -- F2.2
          SELECT sum(LO_REVENUE), toYear(LO_ORDERDATE) AS year, P_BRAND
          FROM ssb_100.lineorder_flat
          WHERE P_BRAND >= 'MFGR#2221' AND P_BRAND <= 'MFGR#2228' AND S_REGION = 'ASIA'
          GROUP BY year, P_BRAND
          ORDER BY year, P_BRAND;


          -- F2.3
          SELECT sum(LO_REVENUE), toYear(LO_ORDERDATE) AS year, P_BRAND
          FROM ssb_100.lineorder_flat
          WHERE P_BRAND = 'MFGR#2239' AND S_REGION = 'EUROPE'
          GROUP BY year, P_BRAND
          ORDER BY year, P_BRAND;


          -- F3.1
          SELECT C_NATION, S_NATION, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue
          FROM ssb_100.lineorder_flat
          WHERE C_REGION = 'ASIA' AND S_REGION = 'ASIA' AND year >= 1992 AND year <= 1997
          GROUP BY C_NATION, S_NATION, year
          ORDER BY year ASC, revenue DESC;


          -- F3.2
          SELECT C_CITY, S_CITY, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue
          FROM ssb_100.lineorder_flat
          WHERE C_NATION = 'UNITED STATES' AND S_NATION = 'UNITED STATES' AND year >= 1992 AND year <= 1997
          GROUP BY C_CITY, S_CITY, year
          ORDER BY year ASC, revenue DESC;


          -- F3.3
          SELECT C_CITY, S_CITY, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue
          FROM ssb_100.lineorder_flat
          WHERE (C_CITY = 'UNITED KI1' OR C_CITY = 'UNITED KI5') AND (S_CITY = 'UNITED KI1' OR S_CITY = 'UNITED KI5') AND year >= 1992 AND year <= 1997
          GROUP BY C_CITY, S_CITY, year
          ORDER BY year ASC, revenue DESC;


          -- F3.4
          SELECT C_CITY, S_CITY, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue
          FROM ssb_100.lineorder_flat
          WHERE (C_CITY = 'UNITED KI1' OR C_CITY = 'UNITED KI5') AND (S_CITY = 'UNITED KI1' OR S_CITY = 'UNITED KI5') AND toYYYYMM(LO_ORDERDATE) = 199712
          GROUP BY C_CITY, S_CITY, year
          ORDER BY year ASC, revenue DESC;


          -- F4.1
          SELECT toYear(LO_ORDERDATE) AS year, C_NATION, sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
          FROM ssb_100.lineorder_flat
          WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2')
          GROUP BY year, C_NATION
          ORDER BY year ASC, C_NATION ASC;


          -- F4.2
          SELECT toYear(LO_ORDERDATE) AS year, S_NATION, P_CATEGORY, sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
          FROM ssb_100.lineorder_flat
          WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND (year = 1997 OR year = 1998) AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2')
          GROUP BY year, S_NATION, P_CATEGORY
          ORDER BY year ASC, S_NATION ASC, P_CATEGORY ASC;
              
          -- F4.3
          SELECT toYear(LO_ORDERDATE) AS year, S_CITY, P_BRAND, sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
          FROM ssb_100.lineorder_flat
          WHERE S_NATION = 'UNITED STATES' AND (year = 1997 OR year = 1998) AND P_CATEGORY = 'MFGR#14'
          GROUP BY year, S_CITY, P_BRAND
          ORDER BY year ASC, S_CITY ASC, P_BRAND ASC;

          执行完毕后，统计查询结果如下所示：

          注：查询结果因配置参数和资源配置的不同，耗时也有差异，欢迎联系 ByteHouse 进行查询优化。
          Query
          Duration(ms)
          Query(flat)
          Duration(ms)
          Q1.1
          280
          F1.1
          710
          Q1.2
          130
          F1.2
          170
          Q1.3
          130
          F1.3
          200
          Q2.1
          670
          F2.1
          240
          Q2.2
          740
          F2.2
          230
          Q2.3
          520
          F2.3
          210
          Q3.1
          620
          F3.1
          290
          Q3.2
          600
          F3.2
          270
          Q3.3
          590
          F3.3
          310
          Q3.4
          320
          F3.4
          140
          Q4.1
          800
          F4.1
          270
          Q4.2
          570
          F4.2
          240
          Q4.3
          520
          F4.3
          270

          查询完成后，在 ByteHouse 计算组详情页面可以查看工作负载，包括总查询条数和CPU/Mem 利用率等，从而确认计算资源的使用情况。


          根据本次压测进行预估，消耗计算和存储资源如下表所示，由于 ByteHouse 云数仓版本按使用量计费的能力，在空闲时支持自动关闭计算组并不收取闲置费用，从而能够极大的节省资源。测试完成后，预估的总体消耗约为 31.23 元。


          配置
          用量
          预估月消耗
          计算资源
          L size:
          32核128GB，1 节点
          1 小时
          11.23 元
          存储资源
          208GB，存储一个月
          21 元





          / ByteHouse大促进行中 /



          产品介绍

          火山引擎ByteHouse
          统一的大数据分析平台。目前提供企业版和云数仓两种版本，企业版是基于开源的企业级分析型数据库，支持用户交互式分析PB级别数据，通过多种自研表引擎，灵活支持各类数据分析和应用；云数仓版作为云原生的数据分析平台，实现统一的离线和实时数据分析，并通过弹性扩展的计算层和分布式存储层，有效降低企业大数据分析。后台回复数字“6”了解产品


          asc大数据
          文章转载自字节跳动数据平台，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

          评论