ByConity 基础使用测试及反馈业务背景在实际业务中，用户会基于不同的产品分别构建实时数仓和离线数仓。其中，实时

业务背景

在实际业务中，用户会基于不同的产品分别构建实时数仓和离线数仓。其中，实时数仓强调数据能够快速入库，且在入库的第一时间就可以进行分析，低时延的返回分析结果。而离线数仓强调复杂任务能够稳定的执行完，需要更好的内存管理。

ByConity 是一款开源云原生数据仓库，可以满足用户的多种数据分析场景。ByConity 增加了 bsp 模式：可以进行 task 级别的容错；更细粒度的调度；基于资源感知的调度。希望通过 bsp 能力，把数据加工（T）的过程转移到ByConity 内部，能够一站式完成数据接入、加工和分析。

测试环境

这里准备了1t的数据测试内容，并已经建立好了测试数据。

这里要把22改成23 。

上手测试

先登录进数据库，并设置语言‘ANSI’

先从sql21 开始这个比较简单。

select *
 from(select w_warehouse_name
            ,i_item_id
            ,sum(case when (cast(d_date as date) < cast ('2000-03-11' as date))
	                then inv_quantity_on_hand 
                      else 0 end) as inv_before
            ,sum(case when (cast(d_date as date) >= cast ('2000-03-11' as date))
                      then inv_quantity_on_hand 
                      else 0 end) as inv_after
   from inventory
       ,warehouse
       ,item
       ,date_dim
   where i_current_price between 0.99 and 1.49
     and i_item_sk          = inv_item_sk
     and inv_warehouse_sk   = w_warehouse_sk
     and inv_date_sk    = d_date_sk
     and d_date between (cast ('2000-03-11' as date) - INTERVAL '30' DAY)
                    and (cast ('2000-03-11' as date) + INTERVAL '30' DAY)
   group by w_warehouse_name, i_item_id) x
 where (case when inv_before > 0 
             then inv_after / inv_before 
             else null
             end) between 2.0/3.0 and 3.0/2.0
 order by w_warehouse_name
         ,i_item_id
 limit 100;

经测试，0.2秒返回数据370000行。

100 rows in set. Elapsed: 0.237 sec. Processed 373.07 thousand rows, 10.54 MB (1.58 million rows/s., 44.55 MB/s.)

测试下复杂的sql语句，如sql 78


with ws as
        (select d_year AS ws_sold_year, ws_item_sk,
        ws_bill_customer_sk ws_customer_sk,
        sum(ws_quantity) ws_qty,
        sum(ws_wholesale_cost) ws_wc,
        sum(ws_sales_price) ws_sp
        from web_sales
        left join web_returns on wr_order_number=ws_order_number and ws_item_sk=wr_item_sk
        join date_dim on ws_sold_date_sk = d_date_sk
        where wr_order_number is null
        group by d_year, ws_item_sk, ws_bill_customer_sk
        ),
        cs as
        (select d_year AS cs_sold_year, cs_item_sk,
        cs_bill_customer_sk cs_customer_sk,
        sum(cs_quantity) cs_qty,
        sum(cs_wholesale_cost) cs_wc,
        sum(cs_sales_price) cs_sp
        from catalog_sales
        left join catalog_returns on cr_order_number=cs_order_number and cs_item_sk=cr_item_sk
        join date_dim on cs_sold_date_sk = d_date_sk
        where cr_order_number is null
        group by d_year, cs_item_sk, cs_bill_customer_sk
        ),
        ss as
        (select d_year AS ss_sold_year, ss_item_sk,
        ss_customer_sk,
        sum(ss_quantity) ss_qty,
        sum(ss_wholesale_cost) ss_wc,
        sum(ss_sales_price) ss_sp
        from store_sales
        left join store_returns on sr_ticket_number=ss_ticket_number and ss_item_sk=sr_item_sk
        join date_dim on ss_sold_date_sk = d_date_sk
        where sr_ticket_number is null
        group by d_year, ss_item_sk, ss_customer_sk
        )
        select
        ss_sold_year, ss_item_sk, ss_customer_sk,
        round(ss_qty/(coalesce(ws_qty,0)+coalesce(cs_qty,0)),2) ratio,
        ss_qty store_qty, ss_wc store_wholesale_cost, ss_sp store_sales_price,
        coalesce(ws_qty,0)+coalesce(cs_qty,0) other_chan_qty,
        coalesce(ws_wc,0)+coalesce(cs_wc,0) other_chan_wholesale_cost,
        coalesce(ws_sp,0)+coalesce(cs_sp,0) other_chan_sales_price
        from ss
        left join ws on (ws_sold_year=ss_sold_year and ws_item_sk=ss_item_sk and ws_customer_sk=ss_customer_sk)
        left join cs on (cs_sold_year=ss_sold_year and cs_item_sk=ss_item_sk and cs_customer_sk=ss_customer_sk)
        where (coalesce(ws_qty,0)>0 or coalesce(cs_qty, 0)>0) and ss_sold_year=2000
        order by
        ss_sold_year, ss_item_sk, ss_customer_sk,
        ss_qty desc, ss_wc desc, ss_sp desc,
        other_chan_qty,
        other_chan_wholesale_cost,
        other_chan_sales_price,
        ratio
        LIMIT 100 ；

设置SETTINGS

bsp_mode = 1,

distributed_max_parallel_size = 4; 这里提示内存不足。

改成24之后，

可以成功获取数据。

测试下sql 79 通过限制内容大小实现内存溢出。

select
  c_last_name,
  c_first_name,
  substr(s_city,1,30),
  ss_ticket_number,amt,profit
from
(
    select
      ss_ticket_number,
      ss_customer_sk,
      store.s_city,
      sum(ss_coupon_amt) amt,
      sum(ss_net_profit) profit
    from store_sales,date_dim,store,household_demographics
    where
        store_sales.ss_sold_date_sk = date_dim.d_date_sk
        and store_sales.ss_store_sk = store.s_store_sk
        and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
        and (household_demographics.hd_dep_count = 6 or household_demographics.hd_vehicle_count > 2)
        and date_dim.d_dow = 1
        and date_dim.d_year in (1999,1999+1,1999+2)
        and store.s_number_employees between 200 and 295
    group by ss_ticket_number,ss_customer_sk,ss_addr_sk,store.s_city) ms,customer
where ss_customer_sk = c_customer_sk
order by c_last_name,c_first_name,substr(s_city,1,30), profit
LIMIT 100

SETTINGS
max_memory_usage=40000000000;