# Running TPC-H Queries
Currently, RisingLight supports importing TPC-H data and run a subset of TPC-H queries. You may import the TPC-H data
and run some simple queries in RisingLight.
## Generate TPC-H Data
### Use Make Recipe
You may use the make recipe to download and generate TPC-H data (about 1GB in tbl format).
```
make tpch
```
If you want to run a larger query:
```
make tpch-10gb
```
The generated data will be placed under `tpch-dbgen/tbl` folder.
### Manual Generation
First, you should use git to clone the tpch-dbgen repo:
```
git clone https://github.com/electrum/tpch-dbgen.git
```
This repo contains the program for generating TPC-H data.
Then, enter the tpch-dbgen directory and type `make all`, and it will generate some executable binaries such as `dbgen` and `qgen`. We will show you how to generate TPC-H data by using one line of command in the following sections. Meanwhile, you can read this [README](https://github.com/electrum/tpch-dbgen/blob/master/README) for more details.
Finally, type the following command and wait for several seconds:
```
./dbgen
```
This command will generate the data we want, which contains a table called `LINEITEM` with a size of 700MB.
## Create Table and Import Data
You will need to build RisingLight in release mode, so as to import data faster.
```shell
cargo build --release
```
Then, use our test scripts to create tables.
```shell
cargo run --release -- -f tests/sql/tpch/create.sql
```
We can use `\dt` to ensure that all tables have been imported.
```
cargo run --release
# Inside SQL shell
\dt
+---+----------+---+----------+---+----------+
| 0 | postgres | 0 | postgres | 0 | nation |
| 0 | postgres | 0 | postgres | 4 | partsupp |
| 0 | postgres | 0 | postgres | 7 | lineitem |
| 0 | postgres | 0 | postgres | 6 | orders |
| 0 | postgres | 0 | postgres | 2 | part |
| 0 | postgres | 0 | postgres | 5 | customer |
+---+----------+---+----------+---+----------+
```
Then, we may use the `import.sql` to import data, which calls `COPY FROM` SQL statements internally:
```shell
cargo run --release -- -f tests/sql/tpch/import.sql
```
Generally, you can finish this process within several seconds.
## Run TPC-H
Now, we can run simple queries on this table.
```shell
cargo run --release
```
```sql
select sum(L_LINENUMBER) from LINEITEM;
select count(L_ORDERKEY), sum(L_LINENUMBER) from LINEITEM where L_ORDERKEY > 2135527;
```
Or run real TPC-H queries:
```shell
cargo run --release -- -f tests/sql/tpch/q1.sql
cargo run --release -- -f tests/sql/tpch/q3.sql
cargo run --release -- -f tests/sql/tpch/q5.sql
cargo run --release -- -f tests/sql/tpch/q6.sql
cargo run --release -- -f tests/sql/tpch/q10.sql
```
## Clean Data
All data of RisingLight is stored in `risinglight.secondary.db` folder. Simply remove it
if you want to clean all data.
## Developers: Add new TPC-H tests
In `tests`, we have two kinds of TPC-H tests:
* Run TPC-H query on small dataset (used for unit tests). `tpch` folder.
* Run TPC-H query on ~1GB dataset generated by tpch-gen. `tpch-full` folder.
Everytime we add a new TPC-H query, we should add both kinds of tests, in sqllogictest format. For example,
developers should:
* create `tests/sql/tpch/_qXX.slt`
* create `tests/sql/tpch-full/_qXX.slt`
* create `tests/sql/tpch/qXX.sql`
* add `include _qXX.slt` in `tests/sql/tpch/tpch.slt`
* add `include _qXX.slt` in `tests/sql/tpch-full/tpch.slt`
By using `output-format` parameter, we can easily get the output format required by sqllogictest:
```
cargo run --release -- -f tests/sql/tpch/q5.sql --output-format text
psql -d tpch -f tests/sql/tpch/q5.sql -A -t -F " "
```
... which yields
```
ALGERIA 55756674.2813
MOZAMBIQUE 54883960.1257
MOROCCO 50463646.0237
ETHIOPIA 49934541.2268
KENYA 48858086.8222
```
Developers should check the output against Postgres in order to ensure the output is correct. The full 1GB TPC-H
test suite can be verified by running:
```
cargo run --release -- -f tests/sql/tpch-full/_tpch_full.slt --output-format text
```
And if you want to start fresh from empty database, you may run:
```
./tests/tpch-full.sh
```
