--test orc foreign data wrapper.
--
-- Settings to make the result deterministic
SET datestyle = "ISO, YMD";
drop server hdfs_server cascade;
--Unit 1. test plan without distributed clause
DROP FOREIGN TABLE IF EXISTS lineitem;
CREATE SERVER hdfs_server FOREIGN DATA WRAPPER HDFS_FDW OPTIONS (address '@hdfshostname@:@hdfsport@',hdfscfgpath '@hdfscfgpath@');
create foreign table lineitem (
L_ORDERKEY INT4,
L_PARTKEY INT4,
L_SUPPKEY INT4,
L_LINENUMBER INT4,
L_QUANTITY FLOAT8,
L_EXTENDEDPRICE FLOAT8,
L_DISCOUNT FLOAT8,
L_TAX FLOAT8,
L_RETURNFLAG TEXT,
L_LINESTATUS TEXT,
L_SHIPDATE TEXT,
L_COMMITDATE TEXT,
L_RECEIPTDATE TEXT,
L_SHIPINSTRUCT TEXT,
L_SHIPMODE TEXT,
L_COMMENT TEXT)
SERVER hdfs_server
OPTIONS(format 'orc', filenames '/user/hive/warehouse/mppdb.db/lineitem_orc11_64stripe/000000_0,/user/hive/warehouse/mppdb.db/lineitem_orc11_64stripe/000001_0') distribute by roundrobin;
DROP FOREIGN TABLE IF EXISTS nation;
create foreign table nation (
N_NATIONKEY INT4,
N_NAME TEXT,
N_REGIONKEY INT4,
N_COMMENT TEXT)
SERVER hdfs_server
OPTIONS(format 'orc', filenames '/user/hive/warehouse/mppdb.db/nation_orc11_64stripe/000000_0') distribute by roundrobin;
DROP FOREIGN TABLE IF EXISTS region;
create foreign table region (
R_REGIONKEY INT4,
R_NAME TEXT,
R_COMMENT TEXT)
SERVER hdfs_server
OPTIONS(format 'orc', filenames '/user/hive/warehouse/mppdb.db/region_orc11_64stripe/000000_0') distribute by roundrobin;
DROP FOREIGN TABLE IF EXISTS part;
create foreign table part (
P_PARTKEY INT4,
P_NAME TEXT,
P_MFGR TEXT,
P_BRAND TEXT,
P_TYPE TEXT,
P_SIZE INT4,
P_CONTAINER TEXT,
P_RETAILPRICE FLOAT8,
P_COMMENT TEXT)
SERVER hdfs_server
OPTIONS(format 'orc', filenames '/user/hive/warehouse/mppdb.db/part_orc11_64stripe/000000_0') distribute by roundrobin;
DROP FOREIGN TABLE IF EXISTS supplier;
create foreign table supplier (
S_SUPPKEY INT4,
S_NAME TEXT,
S_ADDRESS TEXT,
S_NATIONKEY INT4,
S_PHONE TEXT,
S_ACCTBAL FLOAT8,
S_COMMENT TEXT)
SERVER hdfs_server
OPTIONS(format 'orc', filenames '/user/hive/warehouse/mppdb.db/supplier_orc11_64stripe/000000_0') distribute by roundrobin;
DROP FOREIGN TABLE IF EXISTS partsupp;
create foreign table partsupp (
PS_PARTKEY INT4,
PS_SUPPKEY INT4,
PS_AVAILQTY INT4,
PS_SUPPLYCOST FLOAT8,
PS_COMMENT TEXT)
SERVER hdfs_server
OPTIONS(format 'orc', filenames '/user/hive/warehouse/mppdb.db/partsupp_orc11_64stripe/000000_0') distribute by roundrobin;
explain (verbose, costs off, nodes off) SELECT L_SHIPDATE FROM lineitem WHERE L_orderkey BETWEEN 509050 AND 509070;
explain (verbose, costs off, nodes off) SELECT count(L_SHIPDATE) FROM lineitem WHERE L_orderkey BETWEEN 509050 AND 509070;
explain (verbose, costs off, nodes off) select n_name, r_name from nation join region on n_regionkey=r_regionkey;
explain (verbose, costs off, nodes off)
select
s.s_acctbal, s.s_name, n.n_name, p.p_partkey, ps.ps_supplycost, p.p_mfgr, s.s_address, s.s_phone, s.s_comment
from
nation n join region r
on
n.n_regionkey = r.r_regionkey and n.n_name = 'FRANCE'
join
supplier s
on
s.s_nationkey = n.n_nationkey
join
partsupp ps
on
s.s_suppkey = ps.ps_suppkey
join
part p
on
p.p_partkey = ps.ps_partkey and p.p_size = 15 and p.p_type like '%BRASS';
--Test Unit 2: distributed foreign table
DROP FOREIGN TABLE IF EXISTS lineitem CASCADE;
DROP FOREIGN TABLE IF EXISTS customer CASCADE;
DROP FOREIGN TABLE IF EXISTS orders CASCADE;
DROP FOREIGN TABLE IF EXISTS part CASCADE;
DROP FOREIGN TABLE IF EXISTS partsupp CASCADE;
DROP FOREIGN TABLE IF EXISTS supplier CASCADE;
DROP FOREIGN TABLE IF EXISTS nation CASCADE;
DROP FOREIGN TABLE IF EXISTS region CASCADE;
Create foreign table lineitem (
L_ORDERKEY INT4,
L_PARTKEY INT4,
L_SUPPKEY INT4,
L_LINENUMBER INT4,
L_QUANTITY FLOAT8,
L_EXTENDEDPRICE FLOAT8,
L_DISCOUNT FLOAT8,
L_TAX FLOAT8,
L_RETURNFLAG TEXT,
L_LINESTATUS TEXT,
L_SHIPDATE TEXT,
L_COMMITDATE TEXT,
L_RECEIPTDATE TEXT,
L_SHIPINSTRUCT TEXT,
L_SHIPMODE TEXT,
L_COMMENT TEXT)
SERVER hdfs_server OPTIONS(format 'orc', foldername '/user/hive/warehouse/mppdb.db/lineitem_orc11_64stripe/')
distribute by roundrobin;
CREATE FOREIGN TABLE customer (
c_custkey BIGINT,
c_name TEXT,
c_address TEXT,
c_nationkey INT4,
c_phone TEXT,
c_acctbal FLOAT4,
c_mktsegment TEXT,
c_comment TEXT)
SERVER hdfs_server
OPTIONS(format 'orc', foldername '/user/hive/warehouse/mppdb.db/customer_orc11_64stripe/')
distribute by roundrobin;
create foreign table orders (
O_ORDERKEY INT4,
O_CUSTKEY INT4,
O_ORDERSTATUS TEXT,
O_TOTALPRICE FLOAT8,
O_ORDERDATE TEXT,
O_ORDERPRIORITY TEXT,
O_CLERK TEXT,
O_SHIPPRIORITY INT4,
O_COMMENT TEXT)
SERVER hdfs_server
OPTIONS(format 'orc', foldername '/user/hive/warehouse/mppdb.db/orders_orc11_64stripe/')
distribute by roundrobin;
create foreign table part (
P_PARTKEY INT4,
P_NAME TEXT,
P_MFGR TEXT,
P_BRAND TEXT,
P_TYPE TEXT,
P_SIZE INT4,
P_CONTAINER TEXT,
P_RETAILPRICE FLOAT8,
P_COMMENT TEXT)
SERVER hdfs_server
OPTIONS(format 'orc', foldername '/user/hive/warehouse/mppdb.db/part_orc11_64stripe/')
distribute by roundrobin;
create foreign table supplier (
S_SUPPKEY INT4,
S_NAME TEXT,
S_ADDRESS TEXT,
S_NATIONKEY INT4,
S_PHONE TEXT,
S_ACCTBAL FLOAT8,
S_COMMENT TEXT)
SERVER hdfs_server
OPTIONS(format 'orc', foldername '/user/hive/warehouse/mppdb.db/supplier_orc11_64stripe/')
distribute by roundrobin;
create foreign table partsupp (
PS_PARTKEY INT4,
PS_SUPPKEY INT4,
PS_AVAILQTY INT4,
PS_SUPPLYCOST FLOAT8,
PS_COMMENT TEXT)
SERVER hdfs_server
OPTIONS(format 'orc', foldername '/user/hive/warehouse/mppdb.db/partsupp_orc11_64stripe/')
distribute by roundrobin;
create foreign table nation (
N_NATIONKEY INT4,
N_NAME TEXT,
N_REGIONKEY INT4,
N_COMMENT TEXT)
SERVER hdfs_server
OPTIONS(format 'orc', foldername '/user/hive/warehouse/mppdb.db/nation_orc11_64stripe/')
distribute by roundrobin;
create foreign table region (
R_REGIONKEY INT4,
R_NAME TEXT,
R_COMMENT TEXT)
SERVER hdfs_server
OPTIONS(format 'orc', foldername '/user/hive/warehouse/mppdb.db/region_orc11_64stripe/')
distribute by roundrobin;
-- there are bugs for the following three queries. cause EOF error from datanode
explain (verbose, costs off, nodes off) SELECT L_shipdate FROM lineitem WHERE L_orderkey between 2125190 and 2125200;
explain (verbose, costs off, nodes off) SELECT count(L_shipdate) FROM lineitem WHERE L_orderkey between 2125190 and 2125200;
-- TPCH - Q1
explain (verbose, costs off, nodes off)
SELECT
L_RETURNFLAG, L_LINESTATUS, CAST(SUM(L_QUANTITY) AS INT8), CAST(SUM(L_EXTENDEDPRICE) AS INT8) , CAST(SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS INT8), CAST(SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)) AS INT8), CAST(AVG(L_QUANTITY) AS INT8), CAST(AVG(L_EXTENDEDPRICE) as INT8), CAST(AVG(L_DISCOUNT) AS INT8), COUNT(1)
FROM
lineitem
WHERE
L_SHIPDATE<='1998-09-02'
GROUP BY L_RETURNFLAG, L_LINESTATUS
ORDER BY L_RETURNFLAG, L_LINESTATUS;
--TPCH - Q2
explain (verbose, costs off, nodes off)
select
s_acctbal,
s_name,
n_name,
p_partkey,
p_mfgr,
s_address,
s_phone,
s_comment
from
part,
supplier,
partsupp,
nation,
region,
(
select
ps_partkey as temp_ps_partkey, min(ps_supplycost) as temp_min_ps_supplycost
from
partsupp,
supplier,
nation,
region
where
s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'EUROPE'
group by ps_partkey
) as temp
where
p_partkey = temp_ps_partkey
and p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = 15
and p_type like '%BRASS'
and s_nationkey = n_nationkey
and r_name = 'EUROPE'
and ps_supplycost = temp_min_ps_supplycost
order by
s_acctbal desc,
n_name,
s_name,
p_partkey
limit 100
;
--TPCH -Q3
explain (verbose, costs off, nodes off)
select
l_orderkey,
sum(l_extendedprice * (1 - l_discount)) as revenue,
o_orderdate,
o_shippriority
from
customer,
orders,
lineitem
where
c_mktsegment = 'BUILDING'
and c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate < '1995-03-15'
and l_shipdate > '1995-03-15'
group by
l_orderkey,
o_orderdate,
o_shippriority
order by
revenue desc,
o_orderdate
limit 10
;
--TPCH -Q4
explain (verbose, costs off, nodes off)
select
o_orderpriority,
count(*) as order_count
from
orders
where
o_orderdate >= '1993-07-01'
and o_orderdate < '1993-10-01'
and exists (
select
*
from
lineitem
where
l_orderkey = o_orderkey
and l_commitdate < l_receiptdate
)
group by
o_orderpriority
order by
o_orderpriority;
--TPCH -Q5
explain (verbose, costs off, nodes off)
select
n_name,
sum(l_extendedprice * (1 - l_discount)) as revenue
from
customer,
orders,
lineitem,
supplier,
nation,
region
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and l_suppkey = s_suppkey
and c_nationkey = s_nationkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
and o_orderdate >= '1994-01-01'
and o_orderdate < '1995-01-01'
group by
n_name
order by
revenue desc;
--TPCH -Q6
explain (verbose, costs off, nodes off)
select
sum(l_extendedprice * l_discount) as revenue
from
lineitem
where
l_shipdate >= '1994-01-01'
and l_shipdate < '1995-01-01'
and l_discount between 0.06 - 0.01 and 0.06 + 0.01
and l_quantity < 24;
--TPCH -Q7
explain (verbose, costs off, nodes off)
select
supp_nation,
cust_nation,
l_year,
sum(volume) as revenue
from
(
select
n1.n_name as supp_nation,
n2.n_name as cust_nation,
extract(year from l_shipdate) as l_year,
l_extendedprice * (1 - l_discount) as volume
from
supplier,
lineitem,
orders,
customer,
nation n1,
nation n2
where
s_suppkey = l_suppkey
and o_orderkey = l_orderkey
and c_custkey = o_custkey
and s_nationkey = n1.n_nationkey
and c_nationkey = n2.n_nationkey
and (
(n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')
or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')
)
and l_shipdate between '1995-01-01' and '1996-12-31'
) as shipping
group by
supp_nation,
cust_nation,
l_year
order by
supp_nation,
cust_nation,
l_year;
--TPCH -Q8
explain (verbose, costs off, nodes off)
select
o_year,
sum(case
when nation = 'BRAZIL' then volume
else 0
end) / sum(volume) as mkt_share
from
(
select
extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) as volume,
n2.n_name as nation
from
part,
supplier,
lineitem,
orders,
customer,
nation n1,
nation n2,
region
where
p_partkey = l_partkey
and s_suppkey = l_suppkey
and l_orderkey = o_orderkey
and o_custkey = c_custkey
and c_nationkey = n1.n_nationkey
and n1.n_regionkey = r_regionkey
and r_name = 'AMERICA'
and s_nationkey = n2.n_nationkey
and o_orderdate between '1995-01-01' and '1996-12-31'
and p_type = 'ECONOMY ANODIZED STEEL'
) as all_nations
group by
o_year
order by
o_year;
--TPCH -Q9
explain (verbose, costs off, nodes off)
select
nation,
o_year,
sum(amount) as sum_profit
from
(
select
n_name as nation,
extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
from
part,
supplier,
lineitem,
partsupp,
orders,
nation
where
s_suppkey = l_suppkey
and ps_suppkey = l_suppkey
and ps_partkey = l_partkey
and p_partkey = l_partkey
and o_orderkey = l_orderkey
and s_nationkey = n_nationkey
and p_name like '%green%'
) as profit
group by
nation,
o_year
order by
nation,
o_year desc;
--TPCH -Q10
explain (verbose, costs off, nodes off)
select
c_custkey,
c_name,
sum(l_extendedprice * (1 - l_discount)) as revenue,
c_acctbal,
n_name,
c_address,
c_phone,
c_comment
from
customer,
orders,
lineitem,
nation
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate >= '1993-10-01'
and o_orderdate < '1994-01-01'
and l_returnflag = 'R'
and c_nationkey = n_nationkey
group by
c_custkey,
c_name,
c_acctbal,
c_phone,
n_name,
c_address,
c_comment
order by
revenue desc
limit 20
;
--TPCH -Q11
explain (verbose, costs off, nodes off)
select * from (
select
ps_partkey,
round(sum(ps_supplycost * ps_availqty), 1) as value
from
partsupp,
supplier,
nation
where
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'GERMANY'
group by
ps_partkey
) as inner_query
where
value > (
select
sum(ps_supplycost * ps_availqty) * 0.0001
from
partsupp,
supplier,
nation
where
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'GERMANY'
)
order by
value desc;
--TPCH -Q12
explain (verbose, costs off, nodes off)
select
l_shipmode,
sum(case
when o_orderpriority = '1-URGENT'
or o_orderpriority = '2-HIGH'
then 1
else 0
end) as high_line_count,
sum(case
when o_orderpriority <> '1-URGENT'
and o_orderpriority <> '2-HIGH'
then 1
else 0
end) as low_line_count
from
orders,
lineitem
where
o_orderkey = l_orderkey
and l_shipmode in ('MAIL', 'SHIP')
and l_commitdate < l_receiptdate
and l_shipdate < l_commitdate
and l_receiptdate >= '1994-01-01'
and l_receiptdate < '1995-01-01'
group by
l_shipmode
order by
l_shipmode;
--TPCH -Q13
explain (verbose, costs off, nodes off)
select
c_count,
count(*) as custdist
from
(
select
c_custkey,
count(*) as c_count
from
customer left outer join orders on
c_custkey = o_custkey
and o_comment not like '%special%request%'
group by
c_custkey
) as c_orders
group by
c_count
order by
custdist desc,
c_count desc;
--TPCH -Q14
explain (verbose, costs off, nodes off)
select
100.00 * sum(case
when p_type like 'PROMO%'
then l_extendedprice * (1 - l_discount)
else 0
end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
from
lineitem,
part
where
l_partkey = p_partkey
and l_shipdate >= '1995-09-01'
and l_shipdate < '1995-10-01';
--TPCH -Q15
explain (verbose, costs off, nodes off)
with revenue as
(
select
l_suppkey as supplier_no,
sum(l_extendedprice * (1 - l_discount)) total_revenue
from
lineitem
where
l_shipdate >= '1996-01-01'
and l_shipdate < '1996-04-01'
group by
l_suppkey
)
select
s_suppkey,
s_name,
s_address,
s_phone,
total_revenue
from
supplier,
revenue
where
s_suppkey = supplier_no
and total_revenue = (
select
max(total_revenue)
from
revenue
)
order by
s_suppkey;
--TPCH -Q16
explain (verbose, costs off, nodes off)
select
p_brand,
p_type,
p_size,
count(ps_suppkey) as supplier_cnt --remove distinct
from
partsupp,
part
where
p_partkey = ps_partkey
and p_brand <> 'Brand#45'
and p_type not like 'MEDIUM POLISHED%'
and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
and ps_suppkey not in (
select
s_suppkey
from
supplier
where
s_comment like '%Customer%Complaints%'
)
group by
p_brand,
p_type,
p_size
order by
supplier_cnt desc,
p_brand,
p_type,
p_size
limit 100
;
--TPCH -Q17
explain (verbose, costs off, nodes off)
select
sum(l_extendedprice) / 7.0 as avg_yearly
from
lineitem,
part,
(select
l_partkey as temp_l_partkey, 0.2 * avg(l_quantity) as temp_avg
from
lineitem
group by l_partkey
) as temp
where
p_partkey = l_partkey
and p_brand = 'Brand#23'
and p_container = 'MED BOX'
and l_quantity < temp_avg
and p_partkey = temp_l_partkey;
--TPCH -Q18
explain (verbose, costs off, nodes off)
select
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice,
sum(l_quantity)
from
customer,
orders,
lineitem
where
o_orderkey in (
select
l_orderkey
from
lineitem
group by
l_orderkey having
sum(l_quantity) > 300
)
and c_custkey = o_custkey
and o_orderkey = l_orderkey
group by
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice
order by
o_totalprice desc,
o_orderdate
limit 100;
--TPCH -Q19
explain (verbose, costs off, nodes off)
select
round(sum(l_extendedprice * (1 - l_discount)), 5) as revenue
from
lineitem,
part
where
p_partkey = l_partkey
and (
(
p_brand = 'Brand#12'
and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
and l_quantity >= 1 and l_quantity <= 11
and p_size between 1 and 5
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_brand = 'Brand#23'
and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
and l_quantity >= 10 and l_quantity <= 20
and p_size between 1 and 10
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_brand = 'Brand#34'
and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
and l_quantity >= 20 and l_quantity <= 30
and p_size between 1 and 15
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
)
;
--TPCH -Q20
explain (verbose, costs off, nodes off)
select
s_name,
s_address
from
supplier,
nation
where
s_suppkey in (
select
ps_suppkey
from
partsupp,
(
select
l_partkey as temp_l_partkey, l_suppkey as temp_l_suppkey, 0.5 * sum(l_quantity) as temp_l_quantity
from
lineitem
where
l_shipdate >= '1994-01-01'
and l_shipdate < '1995-01-01'
group by l_partkey, l_suppkey
) as temp
where
temp_l_partkey = ps_partkey
and temp_l_suppkey = ps_suppkey
and ps_partkey in (
select
p_partkey
from
part
where
p_name like 'forest%'
)
and ps_availqty > temp_l_quantity
)
and s_nationkey = n_nationkey
and n_name = 'CANADA'
order by
s_name;
--TPCH -Q21
explain (verbose, costs off, nodes off)
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 = 'SAUDI ARABIA'
group by
s_name
order by
numwait desc,
s_name
limit 100;
--TPCH -Q22
explain (verbose, costs off, nodes off)
select
cntrycode,
count(*) as numcust,
round(sum(c_acctbal), 4) as totacctbal
from (
select
substr(c_phone, 1, 2) as cntrycode, c_acctbal
from customer
where
substr(c_phone, 1, 2) in ('13', '31', '23', '29', '30', '18', '17')
and c_acctbal > (
select avg(c_acctbal)
from customer
where
c_acctbal > 0.00
and substr(c_phone, 1, 2) in ('13', '31', '23', '29', '30', '18', '17')
)
and not exists (
select *
from orders
where
o_custkey = c_custkey
)
) as custsale
group by cntrycode
order by cntrycode
;
explain (verbose, costs off, nodes off)
select
cntrycode,
count(*) as numcust,
CAST(sum(c_acctbal) AS INT8) 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
('13', '31', '23', '29', '30', '18', '17')
and c_acctbal > (
select
avg(c_acctbal)
from
customer
where
c_acctbal > 0.00
and substring(c_phone from 1 for 2) in
('13', '31', '23', '29', '30', '18', '17')
)
) as custsale
group by
cntrycode
order by
cntrycode;
explain (verbose, costs off, nodes off)
select
cntrycode,
count(*) as numcust,
CAST(sum(c_acctbal) AS INT8) 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
('13', '31', '23', '29', '30', '18', '17')
and not exists (
select
*
from
orders
where
o_custkey = c_custkey
)
) as custsale
group by
cntrycode
order by
cntrycode;
set datestyle to iso;SET IntervalStyle to postgres;
DROP FOREIGN TABLE IF EXISTS lineitem CASCADE;
DROP FOREIGN TABLE IF EXISTS customer CASCADE;
DROP FOREIGN TABLE IF EXISTS orders CASCADE;
DROP FOREIGN TABLE IF EXISTS part CASCADE;
DROP FOREIGN TABLE IF EXISTS partsupp CASCADE;
DROP FOREIGN TABLE IF EXISTS supplier CASCADE;
DROP FOREIGN TABLE IF EXISTS nation CASCADE;
DROP FOREIGN TABLE IF EXISTS region CASCADE;
DROP SERVER hdfs_server CASCADE;