The MonetDB/SQL tutorial is based on the material published in the book J.R. Bruijn, F.S. Gaastra and I. Schaar, Dutch-Asiatic Shipping in the 17th and 18th Centuries, which gives an account of the trips made to the East and ships returned safely (or wrecked on the way). A total of 8000 records are provided. They include information about ship name and type, captain, the arrival/departure of harbors along the route, personnel accounts, and anecdotal information.
Get going quickly on Linux involves starting the MonetDB daemon monetdbd, code name meriovingian. It runs in the background and facilitates, amongst others, management of local/remote servers. The daemon is controlled by the application monetdb. See its documentation for all details. Here we demonstrate a simple session involving creation of a database, loading data, and querying (Windows is described at the end).
shell> monetdbd create /path/to/mydbfarmshell> monetdbd start /path/to/mydbfarmshell> monetdb create vocshell> monetdb release voc
shell> mclient -u monetdb -d voc
password:<monetdb>
Welcome to mclient, the MonetDB/SQL interactive terminal (Aug2011-SP2)
Database: MonetDB v11.5.4, 'voc'
Type \q to quit, \? for a list of available commands
auto commit mode: on
sql>select 'hello world';+---------------+
| single_value |
+===============+
| hello world |
+---------------+
1 tuple (0.530ms)
sql>
The command \q or end-of-file <Ctrl-d> signal terminates the connection with the server.
Exploring the wealth of functionality offered by MonetDB/SQL is best started using a toy database. For this we use the VOC database which provides a peephole view into the administrative system of an early multi-national company, the Vereenigde geoctrooieerde Oostindische Compagnie (VOC for short - The (Dutch) East Indian Company) established on March 20, 1602. Download the VOC data set voc.sql.gz (519K) bz2 (371K) which is a compressed file with SQL statements. After the file has been extracted, load its contents into MonetDB using mclient.
Before you load the VOC data set, it is advised to introduce a user different from the omnipresent default monetdb. The new user is given his own schema for the MonetDB database. Assuming you have started MonetDB with SQL module, proceed as follows:shell> mclient -u monetdb -d voc
password:<monetdb>
sql>CREATE USER "voc" WITH PASSWORD 'voc' NAME 'VOC Explorer' SCHEMA "sys";
sql>CREATE SCHEMA "voc" AUTHORIZATION "voc";
sql>ALTER USER "voc" SET SCHEMA "voc";
sql>\q
To illustrate the use of the newly created account and schema, the following example connects and creates a table, which is dropped afterwards by aborting the transaction.
shell> mclient -u voc -d voc;
password:<voc>
sql>START TRANSACTION;
sql>CREATE TABLE test (
more> id int,
more> data varchar(30)
more> );
sql>\d
+------+
| name |
+======+
| test |
+------+
sql>\d test
CREATE TABLE "voc"."test" (
"id" int,
"data" varchar(30)
);
sql>ROLLBACK
Importing the voc_dump.sql file into the database can be done using the textual client interface. Some alternative ways are as follows:
1:
shell> mclient -u voc -d voc < voc_dump.sql
password:<voc>
2:
shell> mclient -u voc -d voc
password:<voc>
sql> \< voc_dump.sql
3:
shell> mclient -u voc -d voc
password:<voc>
sql> \< voc_dump.sql
4:
shell> mclient -u voc -d voc < voc_dump.sql
password:<voc>
The VOC data set contains data for around 8000 voyages.
sql>\d
+------------+
| name |
+============+
| craftsmen |
| impotenten |
| invoices |
| passengers |
| seafarers |
| soldiers |
| total |
| voyages |
+------------+
sql>select count(*) from voyages;
+--------+
| count_ |
+========+
| 8115 |
+--------+
The set consists of 8 tables, which are all bound to each other using FOREIGN KEY relationships. The voyages table is the main table, which all others refer to. Every table, except invoices has a PRIMARY KEY defined over the columns number and number_sup. Since the invoices table holds zero or more invoices per voyage (identified by number, number_sub) a PRIMARY KEY constraint is not possible. The tables craftsmen, impotenten, passengers, seafarers, and soldiers all share the same columns. We can define a VIEW that combines them all into one big table, to make them easier to access.sql>CREATE VIEW onboard_people AS
more>SELECT * FROM (
more>SELECT 'craftsmen' AS type, craftsmen.* FROM craftsmen
more>UNION ALL
more>SELECT 'impotenten' AS type, impotenten.* FROM impotenten
more>UNION ALL
more>SELECT 'passengers' AS type, passengers.* FROM passengers
more>UNION ALL
more>SELECT 'seafarers' AS type, seafarers.* FROM seafarers
more>UNION ALL
more>SELECT 'soldiers' AS type, soldiers.* FROM soldiers
more>UNION ALL
more> SELECT 'total' AS type, total.* FROM total
more> ) AS onboard_people_table;
sql>
The new view will show up and we can just use it as a normal table, to for instance calculate the number of records for each group of people:sql>\d
+----------------+
| name |
+================+
| craftsmen |
| impotenten |
| invoices |
| onboard_people |
| passengers |
| seafarers |
| soldiers |
| total |
| voyages |
+----------------+
sql> SELECT type, COUNT(*) AS total
more> FROM onboard_people GROUP BY type ORDER BY type;
+------------+-------+
| type | total |
+============+=======+
| craftsmen | 2349 |
| impotenten | 938 |
| passengers | 2813 |
| seafarers | 4468 |
| soldiers | 4177 |
| total | 2454 |
+------------+-------+
sql> select count(*) from impotenten;
+--------+
| count_ |
+========+
| 938 |
+--------+
It is possible to play with the set in many ways, to find out several things that took place during the voyages of the ships, or the money that was earned. A few examples are shown below:sql>SELECT COUNT(*) FROM voyages
more>WHERE particulars LIKE '%_recked%';
+--------+
| count_ |
+========+
| 358 |
+--------+
sql> SELECT chamber, CAST(AVG(invoice) AS integer) AS average
more> FROM invoices
more> WHERE invoice IS NOT NULL
more> GROUP BY chamber
more> ORDER BY average DESC;
+---------+---------+
| chamber | average |
+=========+=========+
| A | 282996 |
| Z | 259300 |
| H | 150182 |
| R | 149628 |
| D | 149522 |
| E | 149518 |
| null | 83309 |
+---------+---------+
sql>CREATE VIEW extended_onboard AS
more>SELECT number, number_sup, trip, trip_sup,
more> onboard_at_departure, death_at_cape,
more> left_at_cape, onboard_at_cape,
more> death_during_voyage, onboard_at_arrival,
more> death_during_voyage - left_at_cape AS death_at_arrival
more>FROM onboard_people;
WINDOWS
On Windows the first step is to initialize a MonetDB server by simply clicking: 'Start' -> 'Programs' -> 'MonetDB ' -> 'Start server'. Subsequently you can start the textual interface by clicking 'Start'->'Programs'->MonetDB'->'SQL client' . The commands entered there are identical to those found on other platforms. To stop the server, you can simply close the MonetDB SQL Server window. Note the server by default only accepts connections originating from the local host. If you need other machines to access your database, change the configuration file by setting mapi_open=yes.
The graphical user interfaces based on JDBC connectivity provide an alternative client interface. See Squirrel and dbvisualiser installation pages for details.
Exploring the wealth of functionality offered by MonetDB/SQL is best started using a toy database. For this we use the VOC database which provides a peephole view into the administrative system of an early multi-national company, the Vereenigde geoctrooieerde Oostindische Compagnie (VOC for short - The (Dutch) East Indian Company) established on March 20, 1602.
Download the VOC data set voc.sql gz (519K) bz2 (371K) which is a compressed file with SQL statements. After the file has been extracted, load its contents into MonetDB using the mclient.exe file, located in the C:\Program Files\CWI\MonetDB5\bin.