foodmart-data-hsqldb

Foodmart data set in hsqldb format

This project contains the FoodMart data set as an embedded HSQLDB database.

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

Schema

Foodmart contains 37 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
• 11 aggregate tables

Its size is about 24MB uncompressed, 8MB compressed.

Here is a schema diagram:

Using the data set

The data set is packaged as a jar file that is published to Maven Central as a Maven artifact. To use the data in your Java application, add the artifact to your project's dependencies, as described below.

To connect and read data

Connect to the database using the URL, username and password constants in the FoodmartHsqldb class:

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import net.hydromatic.foodmart.data.hsqldb.FoodmartHsqldb;

Connection connection =
    DriverManager.getConnection(FoodmartHsqldb.URI,
        FoodmartHsqldb.USER, FoodmartHsqldb.PASSWORD);
Statement statement = connection.createStatement();
ResultSet resultSet =
    statement.executeQuery("select \"employee_id\", \"full_name\"\n"
        + "from \"foodmart\".\"employee\"");
while (resultSet.next()) {
  System.out.println(resultSet.getInt(1) + ":" + resultSet.getString(2));
}
resultSet.close();
statement.close();
connection.close();

Using SQLLine

You can also connect using a JDBC interface such as sqlline. Start sqlline:

$ ./sqlline
sqlline version 1.12.0
sqlline> !connect jdbc:hsqldb:res:foodmart FOODMART FOODMART
0: jdbc:hsqldb:res:foodmart> select count(*) from "sales_fact_1997";
+----------------------+
|          C1          |
+----------------------+
| 86837                |
+----------------------+
1 row selected (0.004 seconds)
0: jdbc:hsqldb:res:foodmart> !quit

You may need to edit the sqlline or sqlline.bat launcher script, adding foodmart-data-hsqldb.jar to your class path.

Get foodmart-data-hsqldb

From Maven

Get foodmart-data-hsqldb from Maven Central:

<dependency>
  <groupId>net.hydromatic</groupId>
  <artifactId>foodmart-data-hsqldb</artifactId>
  <version>0.6.1</version>
</dependency>

Download and build

Use Java version 11 or higher.

$ git clone https://github.com/julianhyde/foodmart-data-hsqldb.git
$ cd foodmart-data-hsqldb
$ ./mvnw install

On Windows, the last line is

> mvnw install

If you are using Java 8, you should add a parameter -Dhsqldb.version=2.5.1, because HSQLDB 2.6.0 or higher requires at least JDK 11.

See also

Similar data sets:

• chinook-data-hsqldb
• flight-data-hsqldb
• foodmart-data-json
• foodmart-data-mysql
• foodmart-queries
• look-data-hsqldb
• sakila-data-hsqldb
• scott-data-hsqldb
• steelwheels-data-hsqldb

More information

• License: Apache License, Version 2.0
• Author: Julian Hyde (@julianhyde)
• Blog: http://blog.hydromatic.net
• Source code: https://github.com/julianhyde/foodmart-data-hsqldb
• Developers list: [email protected] (archive, subscribe)
• Issues: https://github.com/julianhyde/foodmart-data-hsqldb/issues
• Release notes: HISTORY.md