Repository navigation
Expand file tree
/
Copy pathq07_volume_shipping.py
More file actions
151 lines (126 loc) · 5.14 KB
/
Copy pathq07_volume_shipping.py
File metadata and controls
151 lines (126 loc) · 5.14 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
# Licensed to the Apache Software Foundation (ASF) under one
# or more contributor license agreements. See the NOTICE file
# distributed with this work for additional information
# regarding copyright ownership. The ASF licenses this file
# to you under the Apache License, Version 2.0 (the
# "License"); you may not use this file except in compliance
# with the License. You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing,
# software distributed under the License is distributed on an
# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
# KIND, either express or implied. See the License for the
# specific language governing permissions and limitations
# under the License.
"""
TPC-H Problem Statement Query 7:
The Volume Shipping Query finds, for two given nations, the gross discounted revenues derived from
lineitems in which parts were shipped from a supplier in either nation to a customer in the other
nation during 1995 and 1996. The query lists the supplier nation, the customer nation, the year,
and the revenue from shipments that took place in that year. The query orders the answer by
Supplier nation, Customer nation, and year (all ascending).
The above problem statement text is copyrighted by the Transaction Processing Performance Council
as part of their TPC Benchmark H Specification revision 2.18.0.
Reference SQL (from TPC-H specification, used by the benchmark suite)::
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 date '1995-01-01' and date '1996-12-31'
) as shipping
group by
supp_nation,
cust_nation,
l_year
order by
supp_nation,
cust_nation,
l_year;
"""
from datetime import date
import pyarrow as pa
from datafusion import SessionContext, col, lit
from datafusion import functions as F
from util import get_data_path
# Variables of interest to query over
nation_1 = lit("FRANCE")
nation_2 = lit("GERMANY")
START_DATE = date(1995, 1, 1)
END_DATE = date(1996, 12, 31)
# Load the dataframes we need
ctx = SessionContext()
df_supplier = ctx.read_parquet(get_data_path("supplier.parquet")).select(
"s_suppkey", "s_nationkey"
)
df_lineitem = ctx.read_parquet(get_data_path("lineitem.parquet")).select(
"l_shipdate", "l_extendedprice", "l_discount", "l_suppkey", "l_orderkey"
)
df_orders = ctx.read_parquet(get_data_path("orders.parquet")).select(
"o_orderkey", "o_custkey"
)
df_customer = ctx.read_parquet(get_data_path("customer.parquet")).select(
"c_custkey", "c_nationkey"
)
df_nation = ctx.read_parquet(get_data_path("nation.parquet")).select(
"n_nationkey", "n_name"
)
# Filter to time of interest
df_lineitem = df_lineitem.filter(
col("l_shipdate") >= lit(START_DATE), col("l_shipdate") <= lit(END_DATE)
)
# Limit the nation table to the two nations of interest.
df_nation = df_nation.filter(F.in_list(col("n_name"), [nation_1, nation_2]))
# Limit suppliers to either nation
df_supplier = df_supplier.join(
df_nation, left_on="s_nationkey", right_on="n_nationkey"
).select("s_suppkey", col("n_name").alias("supp_nation"))
# Limit customers to either nation
df_customer = df_customer.join(
df_nation, left_on="c_nationkey", right_on="n_nationkey"
).select("c_custkey", col("n_name").alias("cust_nation"))
# Join up all the data frames from line items, and make sure the supplier and customer are in
# different nations.
df = (
df_lineitem.join(df_orders, left_on="l_orderkey", right_on="o_orderkey")
.join(df_customer, left_on="o_custkey", right_on="c_custkey")
.join(df_supplier, left_on="l_suppkey", right_on="s_suppkey")
.filter(col("cust_nation") != col("supp_nation"))
)
# Extract out two values for every line item
df = df.with_columns(
l_year=F.datepart(lit("year"), col("l_shipdate")).cast(pa.int32()),
volume=col("l_extendedprice") * (lit(1.0) - col("l_discount")),
)
# Aggregate and sort per the spec.
df = df.aggregate(
["supp_nation", "cust_nation", "l_year"],
[F.sum(col("volume")).alias("revenue")],
).sort_by("supp_nation", "cust_nation", "l_year")
df.show()