Can Byconity import tbl format from TPCH ?

[JiangXiNJU]

(you don't have to strictly follow this form)

Question

Hi, we want to test tpch on Byconity, so the first thing is to import tpch data.
Now the tpch datagen produce the data of tbl format which is separated by '|'， is there any Byconity tools to import tpch data or transform tbl to any data format that Byconity support ?

Or is there any way to test tpch on Byconity directly ?

[hustnn]

@JiangXiNJU Don't have the existing tpch tools for ByConity, we need to prepare.

Can you try TPCDS, SSB and ClickBench first?

[JiangXiNJU]

well, thanks for the answer. We will test SSB first.

By the way, when we created tpch table before , we get the following error:

I think this error is talking about that byconity don't support duplicate key now, am i right ?

[hustnn]

You can use unique key https://github.com/ByConity/byconity.github.io/blob/main/docs/%E5%9F%BA%E7%A1%80%E4%BD%BF%E7%94%A8%E6%89%8B%E5%86%8C/%E8%AE%BE%E8%AE%A1%E5%BA%93%E8%A1%A8.mdx#%E5%94%AF%E4%B8%80%E9%94%AE%E7%B4%A2%E5%BC%95unique-key

[JiangXiNJU]

OK, I have transformed the ddls of tpch into the format that Byconity support.
But when I test Q2 of the tpch on Byconity （without data import）， the clickhouse report an error ：
Code: 47. DB::Exception: Received from 127.0.0.1:18684. DB::Exception: Unknown column name 'p_partkey': While processing SELECT min(ps_supplycost) FROM partsupp ALL INNER JOIN supplier ON s_suppkey = ps_suppkey ALL INNER JOIN nation ON s_nationkey = n_nationkey ALL INNER JOIN region ON n_regionkey = r_regionkey WHERE (p_partkey = ps_partkey) AND (s_suppkey = ps_suppkey) AND (s_nationkey = n_nationkey) AND (n_regionkey = r_regionkey) AND (r_name = 'EUROPE'): While processing (SELECT min(ps_supplycost) FROM partsupp, supplier, nation, region WHERE (p_partkey = ps_partkey) AND (s_suppkey = ps_suppkey) AND (s_nationkey = n_nationkey) AND (n_regionkey = r_regionkey) AND (r_name = 'EUROPE')) AS _subquery2: While processing ps_supplycost = ((SELECT min(ps_supplycost) FROM partsupp, supplier, nation, region WHERE (p_partkey = ps_partkey) AND (s_suppkey = ps_suppkey) AND (s_nationkey = n_nationkey) AND (n_regionkey = r_regionkey) AND (r_name = 'EUROPE')) AS _subquery2): While processing (p_partkey = ps_partkey) AND (s_suppkey = ps_suppkey) AND (p_size = 15) AND (p_type LIKE '%BRASS') AND (s_nationkey = n_nationkey) AND (n_regionkey = r_regionkey) AND (r_name = 'EUROPE') AND (ps_supplycost = ((SELECT min(ps_supplycost) FROM partsupp, supplier, nation, region WHERE (p_partkey = ps_partkey) AND (s_suppkey = ps_suppkey) AND (s_nationkey = n_nationkey) AND (n_regionkey = r_regionkey) AND (r_name = 'EUROPE')) AS _subquery2)) SQLSTATE: 42703.
So I check the column of Table part：

the p_partkey exists.

Could you please help me find out the problem of this sql ?
--Q2 select count(*) from ( select s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment from part, partsupp, supplier, nation, region where p_partkey = ps_partkey and s_suppkey = ps_suppkey and p_size = 15 and p_type like '%BRASS' and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'EUROPE' and ps_supplycost = ( select min(ps_supplycost) from partsupp, supplier, nation, region where p_partkey = ps_partkey and s_suppkey = ps_suppkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'EUROPE' ) order by s_acctbal desc, n_name, s_name, p_partkey ) as t1;

[JiangXiNJU]

table creation sql:

CREATE TABLE part ( p_partkey Int32 NOT NULL, p_name String NOT NULL, p_mfgr LowCardinality(String) NOT NULL, p_brand LowCardinality(String) NOT NULL, p_type LowCardinality(String) NOT NULL, p_size Int32 NOT NULL, p_container LowCardinality(String) NOT NULL, p_retailprice Decimal(15,2) NOT NULL, p_comment String NOT NULL) ENGINE=CnchMergeTree() ORDER BY (p_partkey);

CREATE TABLE partsupp ( ps_partkey Int32 NOT NULL, ps_suppkey Int32 NOT NULL, ps_availqty Int32 NOT NULL, ps_supplycost Decimal(15,2) NOT NULL, ps_comment String NOT NULL) ENGINE=CnchMergeTree() ORDER BY (ps_partkey,ps_suppkey);

CREATE TABLE supplier ( s_suppkey Int32 NOT NULL, s_name String NOT NULL, s_address String NOT NULL, s_nationkey Int32 NOT NULL, s_phone FixedString(15) NOT NULL, s_acctbal Decimal(15,2) NOT NULL, s_comment String NOT NULL) ENGINE=CnchMergeTree() ORDER BY (s_suppkey);

CREATE TABLE nation ( n_nationkey Int32 NOT NULL, n_name FixedString(25) NOT NULL, n_regionkey Int32 NOT NULL , n_comment String NULL ) ENGINE=CnchMergeTree() ORDER BY (n_nationkey);

CREATE TABLE region ( r_regionkey Int32 NOT NULL, r_name FixedString(25) NOT NULL, r_comment String) ENGINE=CnchMergeTree() ORDER BY (r_regionkey);