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