gortiz opened a new pull request, #20245:
URL: https://github.com/apache/pinot/pull/20245
## Summary
This PR makes `TPCHQueryIntegrationTest` test the multi-stage engine (MSE)
with real TPC-H data and the real TPC-H queries.
- The TPC-H example data in `pinot-tools` is now TPC-H data at scale factor
0.01 (60,175 `lineitem` rows, 3.4 MB of Avro).
- The 22 queries are in
`pinot-tools/src/main/resources/examples/batch/tpch/queries` in two forms: the
specification text, and a rewrite for the MSE.
- The test runs both forms on 2 servers and compares each result with H2.
The TPC-H quickstart runs 4 of the rewrites as sample queries.
## Background
Before this PR, the test data had 1 `orders` row and 6 `lineitem` rows. Most
queries returned no rows, so the test did not test the joins or the
aggregations. The query files also did not match the TPC-H specification:
- Q15 was split into 3 files (`CREATE VIEW`, `SELECT`, `DROP VIEW`), so
there were 24 files.
- Q1, Q6, Q7 and Q8 had parameters that are not the `qgen -d` defaults.
- Q2, Q3, Q10, Q18 and Q21 did not have the row limit of clause 2.1.2.9.
Pinot has no cost-based optimizer, so the text of a query decides its plan.
Comma joins are planned left-deep in `FROM` order, and the hash join builds on
the right input. With the specification text, some queries make a cross join
(Q2, Q8) or move large tables over the network. The rewrites change only the
join order and the join distribution hints. They return the same results as the
specification text.
## Changes
### Data and queries (`pinot-tools`)
- `examples/batch/tpch/<table>/rawdata/<table>.avro`: TPC-H data at scale
factor 0.01, made with `dbgen` 3.0.1. Dates are strings.
- `examples/batch/tpch/queries/<n>.sql`: the 22 queries of the TPC-H
specification 3.0.1 with the `qgen -d` parameters. Q15 uses a common table
expression instead of a view.
- `examples/batch/tpch/queries/rewrites/<n>.sql`: the same queries,
rewritten for the MSE. A comment at the top of each file gives the reason for
the change.
- `examples/batch/tpch/queries/README.md`: the changes to the specification
text, the techniques in the rewrites, and the table layout that the rewrites
need.
These queries are not a TPC-H benchmark. The specification does not allow
join hints or a different join order (clause 2.2.3.3).
### Table layout
The rewrites use hints that trust the user. If the table layout does not
match the hints, the results are wrong.
```
server 0 server 1
+-------------------------------+ +-------------------------------+
| customer nation part | | customer nation part |
replicated to all servers
| region supplier (full copy) | | region supplier (full copy) |
+-------------------------------+ +-------------------------------+
| orders partitions A | | orders partitions B |
Murmur on order key, 4 partitions;
| lineitem partitions A | | lineitem partitions B | A
and B split the 4 partitions
+-------------------------------+ +-------------------------------+
| partsupp (distributed) | | partsupp (distributed) |
+-------------------------------+ +-------------------------------+
```
- A join with a replicated table uses `left_distribution_type='local'` and
`right_distribution_type='local'`. It sends no rows over the network.
- A join of `orders` and `lineitem` uses `is_colocated_by_join_keys='true'`.
Each server joins its own partitions.
- An aggregation by order key uses `is_partitioned_by_group_by_keys='true'`.
The hint is necessary also with `inferPartitionHint=true`. The planner finds
colocation only when both join inputs are table scans.
### `TPCHQueryIntegrationTest`
- Starts 2 servers and makes the layout above. The instance assignment uses
replica groups by partition, with an empty `constraintConfig`. Without that
config, the controller rotates the instance list by a hash of the table name,
and `orders` and `lineitem` put a partition on different servers.
- Runs each query in its two forms (44 test cases). It compares the row
count, the column count and each value with H2.
- Adds indexes on the join keys in H2, and computes each H2 result one time.
The test runs in about 36 s on my laptop. Without these changes, it ran in
about 466 s.
- Skips the specification text of Q21. Its `EXISTS` / `NOT EXISTS` plan
builds a join of more than 10M rows, also at scale factor 0.01. The rewrite of
Q21 runs.
To make sure that the test finds a wrong layout, I ran it without
replication of the small tables. 13 rewrites failed.
### `TPCHQuickStart`
The quickstart runs the rewrites of Q1, Q3, Q5 and Q10 with the MSE after it
loads the data. On a single server, each table is complete on that server, so
the layout is always correct.
### `TblToAvro`
`TblToAvro` takes the folder of the `.tbl` files as an argument. Before, the
path was a fixed local path. It now writes deflate-compressed files. The
`README.md` next to the test gives the steps to make the data again.
## Testing
- `TPCHQueryIntegrationTest`: 43 passed, 1 skipped (specification Q21).
- `TPCHQuickStart`: started locally. The 4 sample queries returned results.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
