How to implement TPC-H queries with GreenplumPython

A quick demonstration and examples

TPCH benchmark

TPC-H is a benchmark developed to evaluate the performance of large-scale SQL and relational databases by the execution of sets of queries. It has 22 queries against a standard database under controlled conditions. These queries:

• Give answers to real-world business questions
• Are far more complex than most OLTP transactions
• Include a rich breadth of operators and selectivity constraints
• Generate intensive activity on the part of the database server component of the system under test

Data overview

TPC-H can generate data within eight tables. The data volume is defined by Scale Factor (SF). This blog uses 1 Gb of raw data, i.e. SF=1.

The TPC-H Schema

Queries execution

The 22 queries included in TPC-H demonstrate six common choke points to a database:

• Aggregation Performance
• Join Performance
• Data Access Locality
• Expression Calculation
• Correlated Subqueries
• Parallel Execution

This blog has chosen five queries among 22 to show the expressiveness of GreenplumPython.

Preparing the environment

# Database Connection
import greenplumpython as gp
db = gp.database(uri="postgresql://localhost/gpadmin")

# Create GreenplumPython DataFrame from an existing table
customer = db.create_dataframe(table_name="customer")
lineitem = db.create_dataframe(table_name="lineitem")
nation = db.create_dataframe(table_name="nation")
orders = db.create_dataframe(table_name="orders")
part = db.create_dataframe(table_name="part")
partsupp = db.create_dataframe(table_name="partsupp")
region = db.create_dataframe(table_name="region")
supplier = db.create_dataframe(table_name="supplier")

# Access to GreenplumPython builtin functions
import greenplumpython.builtins.functions as F

Q1: Pricing Summary Report Query

This query reports the amount of business billed, shipped, and returned. The date is within 90 days from the ship date in the database.

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 <= date '1998-12-01' - interval '90 days'
group by
 l_returnflag,
 l_linestatus
order by
 l_returnflag,
 l_linestatus;

GreenplumPython

It involves many aggregations; this can be easily translated GreenplumPython using its built-in functions.

q1_gp = (
    lineitem
    .where(lambda t: t["l_shipdate"] <= datetime(1998, 12, 1) - timedelta(days=90))
    .group_by("l_returnflag", "l_linestatus")
    .assign(
        sum_qty=lambda t: F.sum(t["l_quantity"]),
        sum_base_price=lambda t: F.sum(t["l_extendedprice"]),
        sum_disc_price=lambda t: F.sum(t["l_extendedprice"] * (-t["l_discount"] + 1)),
        sum_charge=lambda t: F.sum(t["l_extendedprice"] * (-t["l_discount"] + 1) * (t["l_tax"] + 1)),
        avg_qty=lambda t: F.avg(t["l_quantity"]),
        avg_price=lambda t: F.avg(t["l_extendedprice"]),
        avg_disc=lambda t: F.avg(t["l_discount"]),
        count_order=lambda _: F.count(),
    )
    .order_by("l_returnflag")
    .order_by("l_linestatus")[:]
)
q1_gp

Q2: Minimum Cost Supplier Query

This query finds which supplier should be selected to order all parts with type like “%BRASS” and size = 4 in Asia.

SQL

select
    s_acctbal,
    s_name,
    n_name,
    p_partkey,
    p_mfgr,
    s_address,
    s_phone,
    s_comment
from
    part,
    supplier,
    partsupp,
    nation,
    region
where
    p_partkey = ps_partkey
    and s_suppkey = ps_suppkey
    and p_size = 4
    and p_type like '%BRASS'
    and s_nationkey = n_nationkey
    and n_regionkey = r_regionkey
    and r_name = 'ASIA'
    and ps_supplycost = (
        select min(ps_supplycost)
        from
            partsupp,
            supplier,
            nation,
            region
        where
            p_partkey = ps_partkey
            and s_suppkey = ps_suppkey
            and s_nationkey = n_nationkey
            and n_regionkey = r_regionkey
            and r_name = 'ASIA'
    )
order by
    s_acctbal desc,
    n_name,
    s_name,
    p_partkey
LIMIT 5;

GreenplumPython

It involves many tables to be joined together; furthermore, these joins appeared not once. To simplify the query, we can break it into two parts with GreenplumPython: first calculates all supply cost, then use it to find the minimum cost for each part.

supply_cost = (
    part
    .where(lambda t: (t["p_size"] == 4) & (t["p_type"].like(r"%BRASS")))
    .join(partsupp, cond=lambda s, o: s["p_partkey"] == o["ps_partkey"])
    .join(supplier, cond=lambda s, o: s["ps_suppkey"] == o["s_suppkey"])
    .join(nation, cond=lambda s, o: s["s_nationkey"] == o["n_nationkey"])
    .join(
        region[lambda t: t["r_name"] == "ASIA"],
        cond=lambda s, o: s["n_regionkey"] == o["r_regionkey"],
    )
)
q2_gp = (
    supply_cost.join(
        supply_cost.group_by("p_partkey").assign(
            min_supplycost=lambda t: F.min(t["ps_supplycost"]),
        ),
        on="p_partkey",
        other_columns={"min_supplycost"},
    )
    .where(lambda t: t["ps_supplycost"] == t["min_supplycost"])[
        [
            "s_acctbal",
            "s_name",
            "n_name",
            "p_partkey",
            "p_mfgr",
            "s_address",
            "s_phone",
            "s_comment",
        ]
    ]
    .order_by("s_acctbal", ascending=False)
    .order_by("n_name")
    .order_by("s_name")
    .order_by("p_partkey")[:5]
)
q2_gp

Q11: Important Stock Identification

This query finds the most important subset of suppliers’ stock in Japan.

SQL

select
 ps_partkey,
 sum(ps_supplycost * ps_availqty) as value
from
 partsupp,
 supplier,
 nation
where
 ps_suppkey = s_suppkey
 and s_nationkey = n_nationkey
 and n_name = 'JAPAN'
group by
 ps_partkey having
  sum(ps_supplycost * ps_availqty) > (
   select
    sum(ps_supplycost * ps_availqty) * 0.0001000000
   from
    partsupp,
    supplier,
    nation
   where
    ps_suppkey = s_suppkey
    and s_nationkey = n_nationkey
    and n_name = 'JAPAN'
  )
order by
 value desc
LIMIT 5;

GreenplumPython

It involves complex grouping and many joins. To simplify the query, we can also break it into two parts with GreenplumPython: firstly, scan the available stock of suppliers in Japan, then find all the parts that represent 0.01% of the total value of all available parts.

supply_value = (
    partsupp
    .join(supplier, cond=lambda s, o: s["ps_suppkey"] == o["s_suppkey"])
    .join(
        nation[lambda t: t["n_name"] == "JAPAN"],
        cond=lambda s, o: s["s_nationkey"] == o["n_nationkey"],
    )
    .group_by("ps_partkey")
    .assign(value=lambda t: F.sum(t["ps_supplycost"] * t["ps_availqty"]))
)
q11_gp = (
    supply_value.join(
        supply_value.group_by().assign(value=lambda t: F.sum(t["value"])),
        cond=lambda s, o: s["value"] > o["value"] * 0.0001,
        other_columns={},
    )
    .order_by("value", ascending=False)[:5]
)
q11_gp

Q12: Shipping Modes and Order Priority Query

This query determines whether selecting less expensive modes of shipping is negatively affecting the critical-priority orders by causing more parts to be received by customers after the committed date (1994–01–01).

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,
 lineitem
where
 o_orderkey = l_orderkey
 and l_shipmode in ('TRUCK', 'MAIL')
 and l_commitdate < l_receiptdate
 and l_shipdate < l_commitdate
 and l_receiptdate >= date '1994-01-01'
 and l_receiptdate < date '1994-01-01' + interval '1 year'
group by
 l_shipmode
order by
 l_shipmode;

GreenplumPython

It involves complex functions. With GreenplumPython, we can define a class to count high and low lines and create an aggregate function to calculate the count.

# Define return type of aggregate function
class PriorityCount:
    high_line_count: int
    low_line_count: int

# Create aggregate function to count priority of high and low line
@gp.create_aggregate
def count_priority(count: PriorityCount, priority: str) -> PriorityCount:
    res = {
        "high_line_count": 0 if count is None else count["high_line_count"],
        "low_line_count": 0 if count is None else count["low_line_count"],
    }
    if priority == "1-URGENT" or priority == "2-HIGH":
        res["high_line_count"] += 1
    else:
        res["low_line_count"] += 1
    return res

After creating these elements, we can do a simple filtration and application.

text_type = gp.type_("text")

q12_gp = (
    lineitem
    .where(
        lambda t: ((t["l_shipmode"] == "TRUCK") | (t["l_shipmode"] == "MAIL"))
        & (t["l_commitdate"] < t["l_receiptdate"])
        & (t["l_shipdate"] < t["l_commitdate"])
        & (t["l_receiptdate"] >= datetime(1994, 1, 1))
        & (t["l_receiptdate"] < datetime(1994 + 1, 1, 1))
    )
    .join(orders, cond=lambda s, o: s["l_orderkey"] == o["o_orderkey"])
    .group_by("l_shipmode")
    .apply(lambda t: count_priority(text_type(t["o_orderpriority"])), expand=True)
    .order_by("l_shipmode")[:]
)

q12_gp

Q20: Potential Part Promotion Query

This query identifies suppliers in the United Kingdom having parts like “metallic%” that may be candidates for a promotional offer.

SQL

select
 s_name,
 s_address
from
 supplier,
 nation
where
 s_suppkey in (
  select
   ps_suppkey
  from
   partsupp
  where
   ps_partkey in (
    select
     p_partkey
    from
     part
    where
     p_name like 'metallic%'
   )
   and ps_availqty > (
    select
     0.5 * sum(l_quantity)
    from
     lineitem
    where
     l_partkey = ps_partkey
     and l_suppkey = ps_suppkey
     and l_shipdate >= date '1995-01-01'
     and l_shipdate < date '1995-01-01' + interval '1 year'
   )
 )
 and s_nationkey = n_nationkey
 and n_name = 'UNITED KINGDOM'
order by
 s_name
LIMIT 10;

GreenplumPython

It involves queries with nested subqueries. With GreenplumPython, we can use in_ operator to carry this query type.

q20_gp = (
    supplier.join(
        nation,
        cond=lambda s, o: (o["n_nationkey"] == s["s_nationkey"])
        & (o["n_name"] == "UNITED KINGDOM"),
    )[
        lambda t: t["s_suppkey"].in_(
            partsupp
                .join(
                    lineitem
                    .where(
                        lambda t: (t["l_shipdate"] >= datetime(1995, 1, 1))
                        & (t["l_shipdate"] < datetime(1995 + 1, 1, 1))
                    )
                    .group_by("l_partkey", "l_suppkey")
                    .assign(sum=lambda t: F.sum(t["l_quantity"])),
                    cond=lambda s, o: (s["ps_partkey"] == o["l_partkey"])
                    & (s["ps_suppkey"] == o["l_suppkey"])
                    & (s["ps_availqty"] > o["sum"] * 0.5),
                )
                [
                lambda s: s["ps_partkey"].in_(
                        part[lambda t: t["p_name"].like(r"metallic%")]["p_partkey"]
                    )
                ]["ps_suppkey"]
        )
    ]
    [["s_name", "s_address"]]
    .order_by("s_name")[:10]
)
q20_gp

Conclusion

This blog is not trying to evaluate the performance of database systems but to show the expressiveness of Greenplumpython. It is pretty easy to translate the query into Python syntax with GreenplumPython; you can break a complex SQL query down into several simple Python commands.

NOTE: This blog does a “literal” translation from SQL to GreenplumPython; there would be many changes in an actual implementation case to achieve satisfactory performance.

More resources

• Open-source Greenplum Data-warehouse
• VMware Greenplum Data-warehouse
• GreenplumPython official documentation