TCP-H on MapD whit some errors [SOLVED with corrected import syntax]


#1

hello,
when i copy data from CSV file to table,it shows me this message “Loaded: 0 recs, Rejected: 24 recs in 0.078000 secs” all time,please give me a hand;


#2

i have worked out it,
i use “copy NATION from '/mapd-storage/2.17.3/dbgen/nation.csv’with (delimiter = ‘|’);” instead of “copy NATION from ‘/mapd-storage/2.17.3/dbgen/nation.csv’;”
then,it works;


#3

@zenghao123456 I’m glad you figured it out!


#4

Thank you,and now i have some others questions ,when I test TPC-H on MapD 4.0.2 ,the
Q2、Q4、Q17、Q20、Q21、Q22 show these errors,
Q2:explain select s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment from part, supplier, partsupp, nation, region where p_partkey = ps_partkey and s_suppkey = ps_suppkey and p_size = 25 and p_type like ‘%STEEL’ and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = ‘AFRICA’ 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 = ‘AFRICA’) order by s_acctbal desc, n_name, s_name, p_partkey;
Exception: Exception occurred: type not serializable: [$cor0] (type com.google.common.collect.SingletonImmutableSet)

Q4: explain select o_orderpriority, count(*) as order_count from orders where o_orderdate >= date ‘1993-05-01’ and o_orderdate < date ‘1999-05-01’ and exists ( select * from lineitem where l_orderkey = o_orderkey and l_commitdate < l_receiptdate ) group by o_orderpriority order by o_orderpriority;
Exception: Exception occurred: type not serializable: [$cor0] (type com.google.common.collect.SingletonImmutableSet)

Q17:select sum(l_extendedprice) / 7.0 as avg_yearly from lineitem, part where p_partkey = l_partkey and p_brand = ‘Brand#23’ and p_container = ‘LG CAN’ and l_quantity < ( select 0.2 * avg(l_quantity) from lineitem where l_partkey = p_partkey );
Exception: Exception occurred: type not serializable: [$cor0] (type com.google.common.collect.SingletonImmutableSet)

Q20:
select s_name, s_address from supplier, nation where s_suppkey in ( select ps_suppkey from partsupp where ps_partkey in ( select p_partkey from part where p_name like ‘ivory%’ ) and ps_availqty > ( select 0.5 * sum(l_quantity) from lineitem where l_partkey = ps_partkey and l_suppkey = ps_suppkey and l_shipdate >= date ‘1997-01-01’ and l_shipdate < date ‘1998-01-01’ ) ) and s_nationkey = n_nationkey and n_name = ‘UNITED STATES’ order by s_name;
Exception: Exception occurred: type not serializable: [$cor0] (type com.google.common.collect.SingletonImmutableSet)

Q21:
select s_name, count(*) as numwait from supplier, lineitem l1, orders, nation where s_suppkey = l1.l_suppkey and o_orderkey = l1.l_orderkey and o_orderstatus = ‘F’ and l1.l_receiptdate > l1.l_commitdate and exists ( select * from lineitem l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey <> l1.l_suppkey ) and not exists ( select * from lineitem l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey <> l1.l_suppkey and l3.l_receiptdate > l3.l_commitdate ) and s_nationkey = n_nationkey and n_name = ‘VIETNAM’ group by s_name order by numwait desc, s_name;
Exception: Exception occurred: type not serializable: [$cor0] (type com.google.common.collect.SingletonImmutableSet)

Q22:
select cntrycode, count(*) as numcust, sum(c_acctbal) as totacctbal from ( select substring(c_phone from 1 for 2) as cntrycode, c_acctbal from customer where substring(c_phone from 1 for 2) in (‘44’, ‘30’, ‘33’, ‘28’, ‘27’, ‘31’, ‘24’) and c_acctbal > ( select avg(c_acctbal) from customer where c_acctbal > 0.00 and substring(c_phone from 1 for 2) in (‘44’, ‘30’, ‘33’, ‘28’, ‘27’, ‘31’, ‘24’) ) and not exists ( select * from orders where o_custkey = c_custkey ) ) as custsale group by cntrycode order by cntrycode;
Exception: Exception occurred: type not serializable: [$cor0] (type com.google.common.collect.SingletonImmutableSet)

please give me some help,thanks a lot