clu2's notes: June 2013

MonetDB download

(Below I used Ubuntu 13.04 and MonetDB v11.15.7 (Feb2013-SP2)

Installation

Create file /etc/apt/sources.list.d/monetdb.list with the following content

deb http://dev.monetdb.org/downloads/deb/ quantal monetdb
deb-src http://dev.monetdb.org/downloads/deb/ quantal monetdb

Get the GPG public key by

$ wget --output-document=- http://dev.monetdb.org/downloads/MonetDB-GPG-KEY | sudo apt-key add -

Install the MonetDB

$ sudo apt-get install monetdb5-sql monetdb-client

A pseudo user monetdb will be created.

The following .deb files are downloaded and installed

libmonetdb-stream3
libmonetdb-client6
libmonetdb9
monetdb-client
monetdb5-server
monetdb5-sql

Make sure the pseudo user monetdb can write to the directory /var/lib/monetdb by
```
$ sudo chown monetdb:monetdb /var/lib/monetdb
```

MonetDB executable binaries

/usr/bin/mclient
/usr/bin/monetdb
/usr/bin/monetdbd
/usr/bin/mserver5
/usr/bin/msqldump

MonetDB concepts

MonetDB operates on "dbfarms". A dbfarm is actually just a directory where the actual database will be stored.

A dbfarm is managed by the MonetDB daemon process monetdbd, and each individual database in a dbfarm is managed by MonetDB server process mserver5.

Create/Configure/Start/Shutdown a MonetDB dbfarm

Let's say a dbfarm will be located at /data/monetdb

Create: monetdbd create /data/monetdb
Start: monetdbd start /data/monetdb
The MonetDB daemon process monetdbd by default will listen at port 50000
Show parameters: monetdbd get all /data/monetdb
Set port number to 54321: monetdbd set port=54321 /data/monetdb
Stop: monetdbd stop /data/monetdb

The dbfarm parameters are stored in /data/monetdb/.merovingian_properties

The dbfarm log file is /data/monetdb/merovingian.log

Create a MonetDB database

Suppose the MonetDB daemon process is already running and listening at port 50000. If not, see above steps to create a dbfarm and start the daemon process.

Suppose a database mydb is to be created and used. The steps are

Create: monetdb create mydb
A database named mydb will be created and be put in maintenance (locked) mode. A database in such a mode will not be started automatically by MonetDB daemon process monetdbd when a client requests connection.
The database is located under /data/monetdb/mydb/
Start: monetdb start mydb
Unlock: monetdb release mydb

Process tree

monetdbd start /data/monetdb
 \_ /usr/bin/mserver5 --dbpath=/data/monetdb/mydb --set merovingian_uri mapi:monetdb://myhost:50000/mydb --set mapi_open false --set mapi_port 0 --set mapi_usock /data/monetdb/mydb/.mapi.sock --set monet_vault_key /data/monetdb/mydb/.vaultkey --set gdk_nr_threads 2 --set max_clients 64 --set sql_optimizer default_pipe --set monet_daemon yes

Other useful MonetDB commands

List all available databases: monetdb discover
Show status of all available databases: monetdb status
The usual statuses of a database are Stopped, Running, or Locked.
Show status of mydb: monetdb status mydb
Show parameters of mydb: monetdb get all mydb
Stop mydb: monetdb stop mydb.
Alternatively, one can use monetdb kill mydb, which is equivalent to sending a SIGKILL signal to the MonetDB server process mserver5.

Create schema and user in a MonetDB database

Launch the MonetDB client: Use the default user monetdb and default password monetdb to log in
```
$ mclient -u monetdb -d mydb
```

Create a user foobar

sql> CREATE USER "foobar" WITH PASSWORD 'foobar' NAME 'Foo Bar' SCHEMA "sys";

Create a schema foobardb for user foobar

sql> CREATE SCHEMA "foobardb" AUTHORIZATION "foobar";
sql> ALTER USER "foobar" SET SCHEMA "foobardb";

Useful commands for MonetDB client

Describe table (or view) tbl: \d tbl
Describe schema sch: \dn sch
List all tables: \d
List all schemas: \dn
List all system objects: \dS
List all authorizations: SELECT * FROM AUTHS;
List database parameters: SELECT * FROM env() env;

Use DBVisualizer as a MonetDB client

Download JDBC driver here and put it under DBVisualizer's JDBC directory, say C:\Program Files (x86)\DbVisualizer\jdbc\monetdb
Launch DBVisualizer. It will find this new driver but it does not recognize it.
Select Tool -> Driver Manager menu, add a new driver called MonetDB.
In URL format, enter jdbc:monetdb://hostname/database
In Driver File Paths, point to the JAR file under C:\Program Files (x86)\DbVisualizer\jdbc\monetdb
Now the Driver Class pull-down menu should have nl.cwi.monetdb.jdbc.MonetDriver as an option. Select it.
Close Driver Manager dialog box.
Restart DBVisualizer. Create a new connection to MonetDB mydb
In JDBC Driver, select MonetDB.
In Database URL, enter jdbc:monetdb://localhost/mydb.

Migrate data from MySQL to MonetDB

Dump the table, say t1 of database mydb, into a "|" separate file:

mysql> USE mydb;
mysql> SELECT * FROM t1 INTO OUTFILE '/tmp/t1.tbl' FIELDS TERMINATED BY '|';

Create the table in MonetDB. One can show table schema in MySQL by
```
mysql> SHOW CREATE TABLE t1;
```
Note that MonetDB does not have indices, so remove all KEY clauses in the table scheme.
DATETIME in MySQL is TIMESTAMP in MonetDB, FLOAT in MySQL is REAL in MonetDB.
MonetDB uses single quotes for strings and double quotes for table or schema names.
Once the table is in MonetDB, use COPY INTO syntax:
```
$ mclient -u foobar -d foobardb -s "COPY INTO t1 FROM STDIN" - < /tmp/t1.tbl
```
Note the minus sign - in the command line.

Tips about MonetDB bulk load here. An even faster bulk load is binary mode.

Notes on MonetDB