TPC Benchmark™ H (TPC-H)

The TPC Benchmark™ H (TPC-H) is widely used to evaluate the analytic query capabilities of databases.

Database Entities, Relationships, and Characteristics

The components of TPC-H consist of eight separate and individual tables (the Base Tables). The relationships between columns in these tables are illustrated in the following ER diagram (source: TPC Benchmark H Standard Specification):

Explore the TPC-H Benchmark dataset using Spice

Step 1. Initialize and start Spice

spice init tpch-quickstart

cd tpch-quickstart

spice run

Step 2. Connect the TPC-H Benchmark pod

spice add spiceai/tpch

The following output is shown in the Spice runtime terminal:

2024-03-31T17:47:09.589058Z  INFO runtime: Loaded dataset: region

2024-03-31T17:47:09.589846Z  INFO runtime: Loaded dataset: nation

2024-03-31T17:47:09.606925Z  INFO runtime: Loaded dataset: supplier

2024-03-31T17:47:09.662131Z  INFO runtime: Loaded dataset: partsupp

2024-03-31T17:47:09.673164Z  INFO runtime: Loaded dataset: orders

2024-03-31T17:47:09.678748Z  INFO runtime: Loaded dataset: lineitem

2024-03-31T17:47:09.717499Z  INFO runtime: Loaded dataset: customer

2024-03-31T17:47:09.757605Z  INFO runtime: Loaded dataset: part

Step 3. Run queries against the dataset using the Spice SQL REPL.

In a new terminal, start the Spice SQL REPL.

spice sql

Check that TPC-H tables exist:

show tables;

+---------------+--------------------+-------------+------------+

| table_catalog | table_schema       | table_name  | table_type |

+---------------+--------------------+-------------+------------+

| datafusion    | public             | lineitem    | VIEW       |

| datafusion    | public             | part        | VIEW       |

| datafusion    | public             | region      | VIEW       |

| datafusion    | public             | partsupp    | VIEW       |

| datafusion    | public             | orders      | VIEW       |

| datafusion    | public             | nation      | VIEW       |

| datafusion    | public             | customer    | VIEW       |

| datafusion    | public             | supplier    | VIEW       |

+---------------+--------------------+-------------+------------+

| datafusion    | information_schema | tables      | VIEW       |

| datafusion    | information_schema | views       | VIEW       |

| datafusion    | information_schema | columns     | VIEW       |

| datafusion    | information_schema | df_settings | VIEW       |

+---------------+--------------------+-------------+------------+

Run Pricing Summary Report Query (Q1). More information about TPC-H and all the queries involved can be found in the official TPC Benchmark H Standard Specification.

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 <= date '1998-12-01' - interval '110' day

group by

l_returnflag,

l_linestatus

order by

l_returnflag,

l_linestatus

;

The output will show the results of the query with respective columns and their values.

Step 4 (Optional) Enable Data Acceleration for TPC-H Benchmark Sample Data

Use text editor to open ./spicepods/spiceai/tpch/spicepod.yaml file and enable acceleration flags for each table. Save.

Before:

  - from: s3://spiceai-demo-datasets/tpch/customer/
    name: customer
    acceleration:
      enabled: false

After:

  - from: s3://spiceai-demo-datasets/tpch/customer/
    name: customer
    acceleration:
      enabled: true

Run Pricing Summary Report Query using the Spice SQL REPL again.

Observe query execution time decreased from 4.178523666 to 0.108190459 seconds.