foodmart-data

Foodmart data set as CSV files, published for Go and Rust.

This project contains the Foodmart data set as CSV files, embedded in a Go module and a Rust crate. Neither has any dependencies, and neither links a database; you can read the rows directly, or generate SQL and run it against a database of your choice.

It originated as part of the test suite of the Mondrian OLAP engine.

Schema

Foodmart contains 26 tables:

7 fact tables: sales_fact_1997, sales_fact_1998, sales_fact_dec_1998, inventory_fact_1997, inventory_fact_1998, salary, expense_fact
19 dimension tables: product, customer, time_by_day, employee and more

Together they hold 328,060 rows, about 15MB uncompressed.

The schema is defined in tools/schema.py, and is available at run time as foodmart.Tables in Go and foodmart_data::TABLES in Rust. Both can emit a CREATE TABLE statement for a table. There is a schema diagram in the foodmart-data-hsqldb project; note that it also shows the aggregate tables, which this project does not include (see below).

The files

Each table is a file csv/table.csv. The first line is a header row of column names; the remaining lines are data rows, quoted according to RFC 4180. An empty field represents SQL NULL.

$ head -3 csv/days.csv
day,week_day
1,Sunday
2,Monday

The column types are those of the foodmart-data-hsqldb project, from which these files are taken. They are defined in tools/schema.py, and are available at run time as foodmart.Tables.

Using the data set from Go

package main

import (
	"fmt"
	"log"

	foodmart "github.com/hydromatic/foodmart-data"
)

func main() {
	table, ok := foodmart.Find("employee")
	if !ok {
		log.Fatal("no such table")
	}
	rows, err := table.ReadAll()
	if err != nil {
		log.Fatal(err)
	}
	for _, row := range rows[:10] {
		fmt.Println(row[0] + ":" + row[1])
	}
}

To load the whole data set into a SQL database, use Load. This package has no driver of its own, so you supply the database:

import (
	"context"
	"database/sql"

	foodmart "github.com/hydromatic/foodmart-data"
	_ "modernc.org/sqlite"
)

db, err := sql.Open("sqlite", ":memory:")
if err != nil {
	log.Fatal(err)
}
if err := foodmart.Load(context.Background(), db); err != nil {
	log.Fatal(err)
}

var n int
err = db.QueryRow(`select count(*) from "sales_fact_1997"`).Scan(&n)

Loading all 26 tables takes about half a second.

Using the data set from Rust

let table = foodmart_data::find("employee").unwrap();
for row in table.rows().take(10) {
    println!("{}:{}", row[0], row[1]);
}

The crate embeds each table's CSV text, so there are no files to find at run time. To load the data into a database, generate SQL with create_table_sql and insert_sql:

for table in &foodmart_data::TABLES {
    connection.execute(&table.create_table_sql())?;
    for row in table.rows() {
        connection.execute(&table.insert_sql(&row))?;
    }
}

Aggregate tables

The Foodmart data set also has 11 aggregate tables, whose names start with agg_. This project does not include them: they are 60% of the data by size, and each is a GROUP BY rollup of sales_fact_1997, so you can compute any of them from the data that is here. For example, agg_l_03_sales_fact_1997 is

SELECT "time_id", "customer_id",
    SUM("store_sales"), SUM("store_cost"), SUM("unit_sales"), COUNT(*)
FROM "sales_fact_1997"
GROUP BY "time_id", "customer_id"

If you need the aggregate tables as data, use foodmart-data-hsqldb.

Get foodmart-data

From crates.io

Add the crate to your Cargo.toml:

[dependencies]
foodmart-data = "0.6.1"

From the Go module proxy

$ go get github.com/hydromatic/foodmart-data@v0.6.1

Download and build

$ git clone https://github.com/hydromatic/foodmart-data.git
$ cd foodmart-data
$ go test ./...
$ cargo test

schema.go and src/schema.rs are generated from the SCHEMA table in tools/schema.py. After editing it, regenerate them:

$ ./tools/schema.py

More information

License: Apache License, Version 2.0
Author: Julian Hyde (@julianhyde)
Blog: http://blog.hydromatic.net
Source code: https://github.com/hydromatic/foodmart-data
Issues: https://github.com/hydromatic/foodmart-data/issues
Release notes: CHANGELOG.md

foodmart-data 0.6.1