Getting Started

Getting Started giulia Mon, 02/24/2020 - 16:59

Now that we have a working setup for MonetDB, it's time to start using it!

In this tutorial we are going to cover the basics for data loading and operations with MonetDB

Create a Database

Create a Database mk Tue, 02/25/2020 - 10:22

 VOCThe 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 that 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.

The first step to use MonetDB on Linux is starting 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. The monetdbd is not available on Windows, but for all these systems a default database called demo is available by default (you can find the Windows tutorial here).

Every newly created database contains a system user named monetdb and system schemas such as "sys", "tmp", "profiler" and some more. To connect/login to the database server the first time, you must use monetdb as user name and enter its default password: monetdb. Once connected you may create new users, create new schemas, create tables etc. It is also strongly recommended to change the default password of the monetdb system user via ALTER USER SET PASSWORD command.

 
Here we demonstrate a simple session involving creation of a database, loading data, and querying.
 
________________________________________________________________________________________________
shell> monetdbd create /path/to/mydbfarm
shell> monetdbd start /path/to/mydbfarm
shell> monetdb create voc
shell> monetdb release voc
shell> mclient -u monetdb -d voc
password:<monetdb>
Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2014-SP1)
Database: MonetDB v11.19.7 (Oct2014-SP1), 'mapi:monetdb://localhost:50000/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>\q
shell>
_________________________________________________________________________________________

The command \q or end-of-file <Ctrl-d> signal terminates the connection with the server.

Create Users and Schemas

Create Users and Schemas mk Tue, 02/25/2020 - 10:47

In order to explore the wealth of functionality offered by MonetDB/SQL, the best strategy is to get 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.

First thing we need to do is download the dataset. The VOC dataset can be downloaded here voc_dump.zip (542K) gz (519K) bz2 (371K) and it 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;
auto commit mode: off

sql>CREATE TABLE test (id int, data varchar(30));
operation successful (0.722ms)

sql>\d
TABLE  voc.test

sql>\d test
CREATE TABLE "voc"."test" (
    "id"   INTEGER,
    "data" VARCHAR(30)
);
sql>CREATE TABLE "voc"."test" ("id" int, "data" varchar(30));
sql>ROLLBACK;
auto commit mode: on

sql>\d

_________________________________________________________________________________________

Load Data

Load Data mk Tue, 02/25/2020 - 10:40

It is possible to populate the database importing data from a file. The voc_dump.sql file can be imported into the database 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 < voc_dump.sql
password:<voc>
3:shell> mclient -u voc -d voc
password:<voc>
sql> \< voc_dump.sql
___________________________________________________________________________________

 

After the import procedure is complete, we can access it through the mclient interface. The VOC data set contains data for around 8000 voyages.

___________________________________________________________________________________
sql>\d
TABLE  voc.craftsmen
TABLE  voc.impotenten
TABLE  voc.invoices
TABLE  voc.passengers
TABLE  voc.seafarers
TABLE  voc.soldiers
TABLE  voc.total
TABLE  voc.voyages

_________________________________________________________________________________________

Let's check out the data we just loaded. First, count the number of rows the voyages table.

_________________________________________________________________________________________

sql>SELECT count(*) FROM voyages;
+------+
| L1   |
+======+
| 8131 |
+------+

_________________________________________________________________________________________

In the next section we are going to show examples on how to play with views and queries