drop dataverse tpch if exists; | |
create dataverse tpch; | |
use dataverse tpch; | |
create type LineItemType as closed { | |
l_orderkey: int32, | |
l_partkey: int32, | |
l_suppkey: int32, | |
l_linenumber: int32, | |
l_quantity: double, | |
l_extendedprice: double, | |
l_discount: double, | |
l_tax: double, | |
l_returnflag: string, | |
l_linestatus: string, | |
l_shipdate: string, | |
l_commitdate: string, | |
l_receiptdate: string, | |
l_shipinstruct: string, | |
l_shipmode: string, | |
l_comment: string | |
} | |
create type OrderType as closed { | |
o_orderkey: int32, | |
o_custkey: int32, | |
o_orderstatus: string, | |
o_totalprice: double, | |
o_orderdate: string, | |
o_orderpriority: string, | |
o_clerk: string, | |
o_shippriority: int32, | |
o_comment: string | |
} | |
create type CustomerType as closed { | |
c_custkey: int32, | |
c_name: string, | |
c_address: string, | |
c_nationkey: int32, | |
c_phone: string, | |
c_acctbal: double, | |
c_mktsegment: string, | |
c_comment: string | |
} | |
create nodegroup group1 if not exists on nc1, nc2; | |
create dataset LineItems(LineItemType) | |
primary key l_orderkey, l_linenumber on group1; | |
create dataset Orders(OrderType) | |
primary key o_orderkey on group1; | |
create dataset Customers(CustomerType) | |
primary key c_custkey on group1; | |
write output to nc1:"/tmp/nested_loj.adm"; | |
for $c in dataset('Customers') | |
let $orders := | |
for $o in dataset('Orders') | |
where $o.o_custkey = $c.c_custkey | |
let $items := | |
for $l in dataset('LineItems') | |
where $l.l_orderkey = $o.o_orderkey | |
return $l | |
return { | |
"order": $o, | |
"items": $items | |
} | |
return { | |
"cust": $c, | |
"orders": $orders | |
} |