Documentation
¶
Index ¶
Constants ¶
This section is empty.
Variables ¶
View Source
var AllTables = map[string]parquet.Schema{ "region": RegionSchema, "nation": NationSchema, "supplier": SupplierSchema, "part": PartSchema, "partsupp": PartSuppSchema, "customer": CustomerSchema, "orders": OrdersSchema, "lineitem": LineItemSchema, }
AllTables maps table names to their schemas.
View Source
var CustomerSchema = parquet.Schema{ Columns: []parquet.Column{ {Name: "c_custkey", Type: parquet.TypeInt32}, {Name: "c_name", Type: parquet.TypeString}, {Name: "c_address", Type: parquet.TypeString}, {Name: "c_nationkey", Type: parquet.TypeInt32}, {Name: "c_phone", Type: parquet.TypeString}, {Name: "c_acctbal", Type: parquet.TypeFloat64}, {Name: "c_mktsegment", Type: parquet.TypeString}, {Name: "c_comment", Type: parquet.TypeString, Nullable: true}, }, }
View Source
var LineItemSchema = parquet.Schema{ Columns: []parquet.Column{ {Name: "l_orderkey", Type: parquet.TypeInt32}, {Name: "l_partkey", Type: parquet.TypeInt32}, {Name: "l_suppkey", Type: parquet.TypeInt32}, {Name: "l_linenumber", Type: parquet.TypeInt32}, {Name: "l_quantity", Type: parquet.TypeFloat64}, {Name: "l_extendedprice", Type: parquet.TypeFloat64}, {Name: "l_discount", Type: parquet.TypeFloat64}, {Name: "l_tax", Type: parquet.TypeFloat64}, {Name: "l_returnflag", Type: parquet.TypeString}, {Name: "l_linestatus", Type: parquet.TypeString}, {Name: "l_shipdate", Type: parquet.TypeString}, {Name: "l_commitdate", Type: parquet.TypeString}, {Name: "l_receiptdate", Type: parquet.TypeString}, {Name: "l_shipinstruct", Type: parquet.TypeString}, {Name: "l_shipmode", Type: parquet.TypeString}, {Name: "l_comment", Type: parquet.TypeString, Nullable: true}, }, }
View Source
var NationSchema = parquet.Schema{ Columns: []parquet.Column{ {Name: "n_nationkey", Type: parquet.TypeInt32}, {Name: "n_name", Type: parquet.TypeString}, {Name: "n_regionkey", Type: parquet.TypeInt32}, {Name: "n_comment", Type: parquet.TypeString, Nullable: true}, }, }
View Source
var OrdersSchema = parquet.Schema{ Columns: []parquet.Column{ {Name: "o_orderkey", Type: parquet.TypeInt32}, {Name: "o_custkey", Type: parquet.TypeInt32}, {Name: "o_orderstatus", Type: parquet.TypeString}, {Name: "o_totalprice", Type: parquet.TypeFloat64}, {Name: "o_orderdate", Type: parquet.TypeString}, {Name: "o_orderpriority", Type: parquet.TypeString}, {Name: "o_clerk", Type: parquet.TypeString}, {Name: "o_shippriority", Type: parquet.TypeInt32}, {Name: "o_comment", Type: parquet.TypeString, Nullable: true}, }, }
View Source
var PartSchema = parquet.Schema{ Columns: []parquet.Column{ {Name: "p_partkey", Type: parquet.TypeInt32}, {Name: "p_name", Type: parquet.TypeString}, {Name: "p_mfgr", Type: parquet.TypeString}, {Name: "p_brand", Type: parquet.TypeString}, {Name: "p_type", Type: parquet.TypeString}, {Name: "p_size", Type: parquet.TypeInt32}, {Name: "p_container", Type: parquet.TypeString}, {Name: "p_retailprice", Type: parquet.TypeFloat64}, {Name: "p_comment", Type: parquet.TypeString, Nullable: true}, }, }
View Source
var PartSuppSchema = parquet.Schema{ Columns: []parquet.Column{ {Name: "ps_partkey", Type: parquet.TypeInt32}, {Name: "ps_suppkey", Type: parquet.TypeInt32}, {Name: "ps_availqty", Type: parquet.TypeInt32}, {Name: "ps_supplycost", Type: parquet.TypeFloat64}, {Name: "ps_comment", Type: parquet.TypeString, Nullable: true}, }, }
View Source
var RegionSchema = parquet.Schema{ Columns: []parquet.Column{ {Name: "r_regionkey", Type: parquet.TypeInt32}, {Name: "r_name", Type: parquet.TypeString}, {Name: "r_comment", Type: parquet.TypeString, Nullable: true}, }, }
View Source
var SupplierSchema = parquet.Schema{ Columns: []parquet.Column{ {Name: "s_suppkey", Type: parquet.TypeInt32}, {Name: "s_name", Type: parquet.TypeString}, {Name: "s_address", Type: parquet.TypeString}, {Name: "s_nationkey", Type: parquet.TypeInt32}, {Name: "s_phone", Type: parquet.TypeString}, {Name: "s_acctbal", Type: parquet.TypeFloat64}, {Name: "s_comment", Type: parquet.TypeString, Nullable: true}, }, }
View Source
var TPCHQueries = map[int]QueryDef{
1: {
Name: "Pricing Summary Report",
SQL: `SELECT
l_returnflag,
l_linestatus,
SUM(l_quantity) as sum_qty,
SUM(l_extendedprice) as sum_base_price,
SUM(l_extendedprice * (1 - l_discount)) as sum_disc_price,
SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
AVG(l_quantity) as avg_qty,
AVG(l_extendedprice) as avg_price,
AVG(l_discount) as avg_disc,
COUNT(*) as count_order
FROM lineitem
WHERE l_shipdate <= '1998-09-02'
GROUP BY l_returnflag, l_linestatus
ORDER BY l_returnflag, l_linestatus`,
},
2: {
Name: "Minimum Cost Supplier",
SQL: `SELECT
s_acctbal, s_name, n_name, p_partkey, p_mfgr,
s_address, s_phone, s_comment
FROM part
JOIN partsupp ON p_partkey = ps_partkey
JOIN supplier ON s_suppkey = ps_suppkey
JOIN nation ON s_nationkey = n_nationkey
JOIN region ON n_regionkey = r_regionkey
WHERE r_name = 'EUROPE'
AND p_size = 15
AND p_type LIKE '%BRASS'
AND ps_supplycost = (
SELECT MIN(ps_supplycost)
FROM partsupp
JOIN supplier ON s_suppkey = ps_suppkey
JOIN nation ON s_nationkey = n_nationkey
JOIN region ON n_regionkey = r_regionkey
WHERE ps_partkey = p_partkey
AND r_name = 'EUROPE'
)
ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
LIMIT 100`,
},
3: {
Name: "Shipping Priority",
SQL: `SELECT
l_orderkey,
SUM(l_extendedprice * (1 - l_discount)) as revenue,
o_orderdate,
o_shippriority
FROM customer
JOIN orders ON c_custkey = o_custkey
JOIN lineitem ON l_orderkey = o_orderkey
WHERE c_mktsegment = 'BUILDING'
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`,
},
4: {
Name: "Order Priority Checking",
SQL: `SELECT
o_orderpriority,
COUNT(*) as order_count
FROM orders
WHERE o_orderdate >= '1993-07-01'
AND o_orderdate < '1993-10-01'
AND EXISTS (
SELECT 1 FROM lineitem
WHERE l_orderkey = o_orderkey
AND l_commitdate < l_receiptdate
)
GROUP BY o_orderpriority
ORDER BY o_orderpriority`,
},
5: {
Name: "Local Supplier Volume",
SQL: `SELECT
n_name,
SUM(l_extendedprice * (1 - l_discount)) as revenue
FROM customer
JOIN orders ON c_custkey = o_custkey
JOIN lineitem ON l_orderkey = o_orderkey
JOIN supplier ON l_suppkey = s_suppkey
JOIN nation ON s_nationkey = n_nationkey
JOIN region ON n_regionkey = r_regionkey
WHERE c_nationkey = s_nationkey
AND r_name = 'ASIA'
AND o_orderdate >= '1994-01-01'
AND o_orderdate < '1995-01-01'
GROUP BY n_name
ORDER BY revenue DESC`,
},
6: {
Name: "Forecasting Revenue Change",
SQL: `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 >= 0.05
AND l_discount <= 0.07
AND l_quantity < 24`,
},
7: {
Name: "Volume Shipping",
SQL: `SELECT
n1.n_name as supp_nation,
n2.n_name as cust_nation,
SUBSTR(l_shipdate, 1, 4) as l_year,
SUM(l_extendedprice * (1 - l_discount)) as revenue
FROM supplier
JOIN lineitem ON s_suppkey = l_suppkey
JOIN orders ON o_orderkey = l_orderkey
JOIN customer ON c_custkey = o_custkey
JOIN nation n1 ON s_nationkey = n1.n_nationkey
JOIN nation n2 ON c_nationkey = n2.n_nationkey
WHERE ((n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY')
OR (n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE'))
AND l_shipdate >= '1995-01-01'
AND l_shipdate <= '1996-12-31'
GROUP BY n1.n_name, n2.n_name, SUBSTR(l_shipdate, 1, 4)
ORDER BY supp_nation, cust_nation, l_year`,
},
8: {
Name: "National Market Share",
SQL: `SELECT
SUBSTR(o_orderdate, 1, 4) as o_year,
SUM(CASE WHEN n2.n_name = 'BRAZIL' THEN l_extendedprice * (1 - l_discount) ELSE 0 END) as brazil_revenue,
SUM(l_extendedprice * (1 - l_discount)) as total_revenue
FROM part
JOIN lineitem ON p_partkey = l_partkey
JOIN supplier ON s_suppkey = l_suppkey
JOIN orders ON o_orderkey = l_orderkey
JOIN customer ON c_custkey = o_custkey
JOIN nation n1 ON c_nationkey = n1.n_nationkey
JOIN region ON n1.n_regionkey = r_regionkey
JOIN nation n2 ON s_nationkey = n2.n_nationkey
WHERE r_name = 'AMERICA'
AND o_orderdate >= '1995-01-01'
AND o_orderdate <= '1996-12-31'
AND p_type = 'ECONOMY ANODIZED STEEL'
GROUP BY SUBSTR(o_orderdate, 1, 4)
ORDER BY o_year`,
},
9: {
Name: "Product Type Profit Measure",
SQL: `SELECT
n_name as nation,
SUBSTR(o_orderdate, 1, 4) as o_year,
SUM(l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity) as sum_profit
FROM part
JOIN lineitem ON p_partkey = l_partkey
JOIN supplier ON s_suppkey = l_suppkey
JOIN partsupp ON ps_suppkey = l_suppkey AND ps_partkey = l_partkey
JOIN orders ON o_orderkey = l_orderkey
JOIN nation ON s_nationkey = n_nationkey
WHERE p_name LIKE '%green%'
GROUP BY n_name, SUBSTR(o_orderdate, 1, 4)
ORDER BY nation, o_year DESC`,
},
10: {
Name: "Returned Item Reporting",
SQL: `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
JOIN orders ON c_custkey = o_custkey
JOIN lineitem ON l_orderkey = o_orderkey
JOIN nation ON c_nationkey = n_nationkey
WHERE o_orderdate >= '1993-10-01'
AND o_orderdate < '1994-01-01'
AND l_returnflag = 'R'
GROUP BY c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment
ORDER BY revenue DESC
LIMIT 20`,
},
11: {
Name: "Important Stock Identification",
SQL: `SELECT
ps_partkey,
SUM(ps_supplycost * ps_availqty) as value
FROM partsupp
JOIN supplier ON ps_suppkey = s_suppkey
JOIN nation ON s_nationkey = n_nationkey
WHERE n_name = 'GERMANY'
GROUP BY ps_partkey
HAVING SUM(ps_supplycost * ps_availqty) > (
SELECT SUM(ps_supplycost * ps_availqty) * 0.0001
FROM partsupp
JOIN supplier ON ps_suppkey = s_suppkey
JOIN nation ON s_nationkey = n_nationkey
WHERE n_name = 'GERMANY'
)
ORDER BY value DESC`,
},
12: {
Name: "Shipping Modes and Order Priority",
SQL: `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
JOIN lineitem ON o_orderkey = l_orderkey
WHERE 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`,
},
13: {
Name: "Customer Distribution",
SQL: `SELECT
c_custkey,
COUNT(o_orderkey) as c_count
FROM customer
LEFT JOIN orders ON c_custkey = o_custkey AND o_comment NOT LIKE '%special%requests%'
GROUP BY c_custkey
ORDER BY c_count DESC, c_custkey
LIMIT 100`,
},
14: {
Name: "Promotion Effect",
SQL: `SELECT
SUM(CASE WHEN p_type LIKE 'PROMO%' THEN l_extendedprice * (1 - l_discount) ELSE 0 END) as promo_revenue,
SUM(l_extendedprice * (1 - l_discount)) as total_revenue
FROM lineitem
JOIN part ON l_partkey = p_partkey
WHERE l_shipdate >= '1995-09-01'
AND l_shipdate < '1995-10-01'`,
},
15: {
Name: "Top Supplier",
SQL: `WITH revenue AS (
SELECT
l_suppkey as supplier_no,
SUM(l_extendedprice * (1 - l_discount)) as 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
JOIN revenue ON s_suppkey = supplier_no
WHERE total_revenue = (
SELECT MAX(total_revenue) FROM revenue
)
ORDER BY s_suppkey`,
},
16: {
Name: "Parts/Supplier Relationship",
SQL: `SELECT
p_brand, p_type, p_size,
COUNT(DISTINCT ps_suppkey) as supplier_cnt
FROM partsupp
JOIN part ON p_partkey = ps_partkey
WHERE p_brand != 'Brand#45'
AND p_type NOT LIKE 'MEDIUM POLISHED%'
AND p_size IN (49, 14, 23, 45, 19, 3, 36, 9)
GROUP BY p_brand, p_type, p_size
ORDER BY supplier_cnt DESC, p_brand, p_type, p_size`,
},
17: {
Name: "Small-Quantity-Order Revenue",
SQL: `SELECT
SUM(l_extendedprice) / 7.0 as avg_yearly
FROM lineitem
JOIN part ON p_partkey = l_partkey
WHERE p_brand = 'Brand#23'
AND p_container = 'MED BOX'
AND l_quantity < (
SELECT 0.2 * AVG(l_quantity)
FROM lineitem
WHERE l_partkey = p_partkey
)`,
},
18: {
Name: "Large Volume Customer",
SQL: `SELECT
c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice,
SUM(l_quantity) as total_qty
FROM customer
JOIN orders ON c_custkey = o_custkey
JOIN lineitem ON o_orderkey = l_orderkey
WHERE o_orderkey IN (
SELECT l_orderkey
FROM lineitem
GROUP BY l_orderkey
HAVING SUM(l_quantity) > 300
)
GROUP BY c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
ORDER BY o_totalprice DESC, o_orderdate
LIMIT 100`,
},
19: {
Name: "Discounted Revenue",
SQL: `SELECT
SUM(l_extendedprice * (1 - l_discount)) as revenue
FROM lineitem
JOIN part ON p_partkey = l_partkey
WHERE (
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 >= 1 AND p_size <= 5
AND l_shipmode IN ('AIR', 'REG AIR')
AND l_shipinstruct = 'DELIVER IN PERSON'
) OR (
p_brand = 'Brand#23'
AND p_container IN ('MED BAG', 'MED BOX', 'MED PACK', 'MED PKG')
AND l_quantity >= 10 AND l_quantity <= 20
AND p_size >= 1 AND p_size <= 10
AND l_shipmode IN ('AIR', 'REG AIR')
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 >= 1 AND p_size <= 15
AND l_shipmode IN ('AIR', 'REG AIR')
AND l_shipinstruct = 'DELIVER IN PERSON'
)`,
},
20: {
Name: "Potential Part Promotion",
SQL: `SELECT s_name, s_address
FROM supplier
JOIN nation ON s_nationkey = n_nationkey
WHERE n_name = 'CANADA'
AND s_suppkey IN (
SELECT ps_suppkey
FROM partsupp
WHERE ps_partkey IN (
SELECT p_partkey FROM part WHERE p_name LIKE 'forest%'
)
AND ps_availqty > (
SELECT 0.5 * SUM(l_quantity)
FROM lineitem
WHERE l_partkey = ps_partkey
AND l_suppkey = ps_suppkey
AND l_shipdate >= '1994-01-01'
AND l_shipdate < '1995-01-01'
)
)
ORDER BY s_name`,
},
21: {
Name: "Suppliers Who Kept Orders Waiting",
SQL: `SELECT s_name, COUNT(*) as numwait
FROM supplier
JOIN lineitem l1 ON s_suppkey = l1.l_suppkey
JOIN orders ON o_orderkey = l1.l_orderkey
JOIN nation ON s_nationkey = n_nationkey
WHERE o_orderstatus = 'F'
AND l1.l_receiptdate > l1.l_commitdate
AND n_name = 'SAUDI ARABIA'
AND EXISTS (
SELECT 1 FROM lineitem l2
WHERE l2.l_orderkey = l1.l_orderkey
AND l2.l_suppkey != l1.l_suppkey
)
AND NOT EXISTS (
SELECT 1 FROM lineitem l3
WHERE l3.l_orderkey = l1.l_orderkey
AND l3.l_suppkey != l1.l_suppkey
AND l3.l_receiptdate > l3.l_commitdate
)
GROUP BY s_name
ORDER BY numwait DESC, s_name
LIMIT 100`,
},
22: {
Name: "Global Sales Opportunity",
SQL: `SELECT
SUBSTR(c_phone, 1, 2) as cntrycode,
COUNT(*) as numcust,
SUM(c_acctbal) as totacctbal
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 1 FROM orders WHERE o_custkey = c_custkey
)
GROUP BY SUBSTR(c_phone, 1, 2)
ORDER BY cntrycode`,
},
}
Functions ¶
func Generate ¶
func Generate(sf ScaleFactor) map[string][]map[string]any
Generate produces all TPC-H tables at the given scale factor. Returns a map of table name → rows. Deterministic for a given SF.
func GenerateChunked ¶
func GenerateChunked(sf ScaleFactor, chunkSize int, emit func(table string, rows []map[string]any) error) error
GenerateChunked streams TPC-H data for all tables, calling emit with chunks of rows. Memory usage is bounded to O(chunkSize) regardless of scale factor.
Types ¶
type QueryDef ¶
QueryDef holds a TPC-H query definition.
func GetQuery ¶
func GetQuery(qNum int, sf ScaleFactor) QueryDef
GetQuery returns the query definition for the given query number, adjusted for the scale factor. Q11 uses FRACTION = 0.0001/SF per the TPC-H spec.
type ScaleFactor ¶
type ScaleFactor float64
ScaleFactor controls data volume. SF=0.01 is ~10MB, SF=1 is ~1GB.
const ( SF001 ScaleFactor = 0.01 SF01 ScaleFactor = 0.1 SF1 ScaleFactor = 1.0 SF10 ScaleFactor = 10.0 SF100 ScaleFactor = 100.0 )
func (ScaleFactor) RowCounts ¶
func (sf ScaleFactor) RowCounts() TableCounts
RowCounts returns the row counts for each table at the given scale factor.
type TableCounts ¶
type TableCounts struct {
Region, Nation, Supplier, Part, PartSupp, Customer, Orders, LineItem int
}
Source Files
Click to show internal directories.
Click to hide internal directories.