Using duck dB to covert SQL to Velox plan and run in VeloxIn10MinDemo

[Manoj-red-hat]

HI Team,

I was wondering is it possible

than I can simply run any sql like

SELECT r_name, COUNT(1) AS nation_cnt
FROM TBL_NATION
JOIN TBL_REGION ON n_regionkey = r_regionkey
GROUP BY r_name
ORDER BY r_name
LIMIT 10;

rather than writing entire velox plan

auto planNodeIdGenerator = std::make_shared<PlanNodeIdGenerator>();
core::PlanNodeId nationScanId;
core::PlanNodeId regionScanId;
plan = PlanBuilder(planNodeIdGenerator)
           .tpchTableScan(
               tpch::Table::TBL_NATION, {"n_regionkey"}, 1 /*scaleFactor*/)
           .capturePlanNodeId(nationScanId)
           .hashJoin(
               {"n_regionkey"},
               {"r_regionkey"},
               PlanBuilder(planNodeIdGenerator)
                   .tpchTableScan(
                       tpch::Table::TBL_REGION,
                       {"r_regionkey", "r_name"},
                       1 /*scaleFactor*/)
                   .capturePlanNodeId(regionScanId)
                   .planNode(),
               "", // extra filter
               {"r_name"})
           .singleAggregation({"r_name"}, {"count(1) as nation_cnt"})
           .orderBy({"r_name"}, false)
           .planNode();

auto nationCnt = AssertQueryBuilder(plan)
                     .split(nationScanId, makeTpchSplit())
                     .split(regionScanId, makeTpchSplit())
                     .copyResults(pool());

std::cout << std::endl
          << "> number of nations per region in TPC-H: "
          << nationCnt->toString() << std::endl;
std::cout << nationCnt->toString(0, 10) << std::endl;

[Manoj-red-hat]

@aditi-pandit FYI..

[aditi-pandit]

Hi @Manoj-red-hat : We've never used anything like that because the hand-coded plans we wrote are what we consider ideal for the query. We preferred DuckDB to choose its own optimized plan for a fair comparison of the 2 systems.

Though @mbasmanova might have implemented a utility for what you want to do. Would be great to get more information from her.

[mbasmanova]

@Manoj-red-hat Velox is library for accelerating query execution. Velox doesn't provide SQL interface or query optimizer. The input to Velox is fully optimized single-node plan fragment.

[Manoj-red-hat]

Previously, it was mentioned that engineers working with Velox were reading Parquet files and conducting tests on the entire TPC-H benchmark. However, it appears that this process is more complex than initially described; they have actually written the entire set of TPC-H queries to be compatible with Velox's query execution plans.

https://github.com/facebookincubator/velox/blob/main/velox/exec/tests/utils/TpchQueryBuilder.cpp#L190

[Manoj-red-hat]

@mbasmanova @aditi-pandit on first place even why we are using DuckDB, if we need to do all by hand.

Precisely where it is helping, except match velox operator test case answers with some base sql engine

[aditi-pandit]

@Manoj-red-hat : DuckDB is considered a reasonable baseline in terms of a single node SQL engine for Velox benchmarking.

Each of test PRs are submit wtih a detailed comparison of DuckDB vs Velox plan. The plans were run with increasing number of drivers. The analysis goes deeper into Velox operators performance #9825