Client Interfaces

Client Interfaces mk Mon, 02/24/2020 - 10:49

MonetDB comes with JDBC, ODBC, PHP, Perl, Ruby and Python interface libraries. The JDBC, PHP, Perl, Ruby, Python and Node.js interfaces are native implementations and do not require installation of the MonetDB client/server code. The ODBC driver and setup library are available as a separate installer. The Mapi library is the lowest level C-interface to interacting with the server.

We rely on external documentation for the basics of the language bindings provided: PHP, Perl, Python, and the drivers JDBC and ODBC. Examples provided are used to illustrate their behavior in the context of MonetDB only.

JavaScript Node.js
Perl DBI

Notes: The programming interface is based on a client-server architecture, where the client program connects to a server using a TCP/IP connection to exchange commands and receives answers. The underlying protocol uses plain UTF-8 data for ease of use and debugging. This leads to publicly visible information exchanged over a network, which may be undesirable. Therefore, a private and secure channel can be set up with the Secure Socket Layer functionality.

In addition, a deeper integration of MonetDB with R is available on the website of the MonetDB.R connector. If you are looking for information about the embedded R support in MonetDB, follow check this blog post.

More information about using MonetDB on Node.js you can find here.


DbVisualizer mk Mon, 05/04/2020 - 17:19

DbVisualizer is a platform independent tool aimed to simplify database development and management for database administrators and developers. It's a very cool tool (can even draw dependency graphs based on the schema and foreign keys.

Free, personal use versions are available from their website. Download and install the software. The following scheme works for their versions 6.1.

After starting DbVisualizer for the first time, it will load its default welcome screen and starts the wizard to select database driver. Cancel this wizard and open the Driver Manager, using the menu Tools->Driver Manager... In the Driver Manager add a new Driver using Driver->Create Driver... Type the name of the driver in the Name field, e.g. MonetDB. Type the URL format for the driver: jdbc:monetdb://hostname/database. In the Driver File Paths box, follow the directions to load a JAR file that contains the JDBC driver. Use the MonetDB JDBC driver that came with your distribution, typically installed in ${prefix }/share/MonetDB/lib/monetdb-X.Y-jdbc.jar.

After adding, the window should list the driver class nl.cwi.monetdb.jdbc.MonetDriver and automatically fills it in in the Driver Class field. Close the Driver Manager window.

From the menu select Database->Create Database Connection. A pop-up dialog will try to pursue you to use the wizard. You know better so, click "No". Fill in the name for the connection in the Alias field, e.g. MonetDB. Select the monetdb driver from the list. Copy the default URL by clicking on the "URL Format: ..." text field and change it to reflect the right hostname (usually localhost will do). Fill in the default userid and password (monetdb). Press the "Connect" button. It will report the database being used and the JDBC driver in use. In the left pane the monetdb database now becomes available from browsing.

Explore the application and have fun!

A caveat of the free-version system is its performance on SQL scripts. They are sent as a single string to the server for execution. This is not the most optimal situation for MonetDB. Running a batch script is better started from the MonetDB SQL client.

JDBC Client

JDBC Client giulia Mon, 03/02/2020 - 16:42

Note: the jdbcclient tool is only provided for performing basic JDBC tests.  It is not supported, mclient should be used instead.

The textual client using the JDBC protocol comes with several startup options. To list them use command:

     java -jar jdbcclient.jre7.jar --help

This will show following usage help:

Usage java -jar jdbcclient.jre7.jar
		[-h host[:port]] [-p port] [-f file] [-u user]
		[-l language] [-d database] [-e] [-D [table]]
or using long option equivalents --host --port --file --user --language
--dump --echo --database.
Arguments may be written directly after the option like -p50000.

If no host and port are given, localhost and 50000 are assumed.
An .monetdb file may exist in the user's home directory.  This file can contain
preferences to use each time JdbcClient is started.  Options given on the
command line override the preferences file.  The .monetdb file syntax is
<option>=<value> where option is one of the options host, port, file, mode
debug, or password.  Note that the last one is perilous and therefore not
available as command line option.
If no input file is given using the -f flag, an interactive session is
started on the terminal.

-h --host     The hostname of the host that runs the MonetDB database.  A port
              number can be supplied by use of a colon, i.e. -h somehost:12345.
-p --port     The port number to connect to.
-f --file     A file name to use either for reading or writing.  The file will
              be used for writing when dump mode is used (-D --dump).  In read
              mode, the file can also be an URL pointing to a plain text file
              that is optionally gzip compressed.
-u --user     The username to use when connecting to the database.
-d --database Try to connect to the given database (only makes sense if
              connecting to monetdbd).
-l --language Use the given language, defaults to 'sql'.
--help        This help screen.
--version     Display driver version and exit.
-e --echo     Also outputs the contents of the input file, if any.
-q --quiet    Suppress printing the welcome header.
-D --dump     Dumps the given table(s), or the complete database if none given.
-Xoutput      The output mode when dumping.  Default is sql, xml may be used for
              an experimental XML output.
-Xhash        Use the given hash algorithm during challenge response.  Supported
              algorithm names: SHA1, MD5, plain.
-Xdebug       Writes a transmission log to disk for debugging purposes.  If a
              file name is given, it is used, otherwise a file called
              monet<timestamp>.log is created.  A given file never be
              overwritten; instead a unique variation of the file is used.
-Xbatching    Indicates that a batch should be used instead of direct
              communication with the server for each statement.  If a number is
              given, it is used as batch size.  i.e. 8000 would execute the
              contents on the batch after each 8000 statements read.  Batching
              can greatly speedup the process of restoring a database dump.

NOTE The JDBC protocol does not support the SQL DEBUG <query>, option. Use the mclient tool instead.

MAPI Client

MAPI Client mk Mon, 02/24/2020 - 10:50

The mclient program is the universal command-line tool that implements the MAPI protocol for client-server interaction with MonetDB.

On a Windows platform it can be started using start->MonetDB->MonetDB SQL Client. Alternatively, you can use the command window to start mclient.exe. Be aware that your environment variables are properly set to find the libraries of interest.

On a Linux platform it provides readline functionality, which greatly improves user interaction. A history can be maintained to ease interaction over multiple sessions.

The default setting is geared at establishing a guest connection to an SQL database at a default server running on the localhost. The -h hostname specifies on which machine the MonetDB server is running. If you communicate with a MonetDB server on the same machine, it can be omitted.

Usage: mclient [ options ] Options are: 
-h hostname | --host=hostname host or UNIX domain socket to connect to 
-p portnr | --port=portnr port to connect to 
-u user | --user=user user id 
-d database | --database=database database to connect to 
-e | --echo echo the query 
-E charset | --encoding=charset specify encoding (character set) of the terminal 
-f kind | --format=kind specify output format {csv,tab,raw,sql,xml} 
-H | --history load/save cmdline history (default off) 
-i | --interactive read stdin after command line args 
-l language | --language=lang {sql,mal} 
-L logfile | --log=logfile save client/server interaction 
-s stmt | --statement=stmt run single statement 
-X | --Xdebug trace mapi network interaction 
-| cmd | --pager=cmd for pagination 
-? | --help show this usage message SQL specific opions 
-n nullstr | --null=nullstr change NULL representation for sql, csv and tab output modes 
-r nr | --rows=nr for pagination 
-w nr | --width=nr for pagination 
-D | --dump create an SQL dump 
-N | --inserts use INSERT INTO statements when dumping 

The default mapi_port TCP port used is 50000. If this port happens to be in use on the server machine (which generally is only the case if you run two MonetDB servers on it), you will have to use the -p port do define the port to which the mserver is listening. Otherwise, it may also be omitted. If there is more than one mserver running, you must also specify the database name -d database. In this case, if your port is set to the wrong database, the connection will always be redirected to the correct one. Note that the default port (and other default options) can be set in the server configuration file.

Within the context of each query language there are more options. They can be shown using the command \? or using the commandline.

For SQL there are several knobs to tune for a better rendering of result tables (\w).

shell> mclient -d database 
Welcome to mclient, the MonetDB/SQL interactive terminal (Apr2011-SP2) 
Database: MonetDB v11.5.2, 'database' 
Type \q to quit, \? for a list of available commands 
auto commit mode: on 
\? - show this message 
\<file - read input from file 
\>file - save response in file, or stdout if no file is given 
\|cmd - pipe result to process, or stop when no command is given 
\h - show the readline history \t - toggle timer \D table- dumps the table, or the complete database if none given. 
\d[Stvsfn]+ [obj] - list database objects, or describe if obj given 
\A - enable auto commit 
\a - disable auto commit 
\e - echo the query in sql formatting mode 
\f - format using a built-in renderer {csv,tab,raw,sql,xml} 
\w# - set maximal page width (-1=unlimited, 0=terminal width, >0=limit to num) 
\r# - set maximum rows per page (-1=raw) 
\L file - save client/server interaction 
\X - trace mclient code 
\q - terminate session


Pentaho dnedev Mon, 05/04/2020 - 17:27

The Pentaho suite is comprised of several application for Data Integration, Business Analytics and Reporting. It comes in both free Community edition and Enterprise one. Written (mostly) in Java, the Pentaho suite has a great support for MonetDB. There are a few tools in the suite directly useful to MonetDB users:

  • ETL: Pentaho Data Integration (PDI), or formerly know as Kettle, is a powerful tool with numerous versatile components for data Extract, Transform and Load. PDI ships with a bulk-loader for MonetDB and JDBC driver for reading data from MonetDB
  • Business Analytics: Pentaho Interactive Reporting and Dashboard Designer are tools useful for (as their names suggest) reporting and designing interactive web dashboards. 

All of the above tools plus other are available as independently downloadable community-supported applications, as well as integrated in the Pentaho Business Analytics suite.

To read or write data from/to MonetDB in PDI, one needs to create a database connection, which is established with the MonetDB JDBC driver that ships with PDI. To create a new connection, start PDI create/open a job or transformation.

1) Select Tools -> Wizard -> Create database connection.

2) Name the connection (e.g. MonetDB), select MonetDB from the database list and Native (JDBC) from the access type list.

3) Enter hostname (e.g. localhost), port (50000 by default) and your database name.

4) Enter the username and password for your database.

You can click on the Test database connection button to verify it works and then close the dialog.

In a PDI transformation you can use the MonetDB Bulk Loader to quickly load your data processed the tool. Do use the bulk-loading component, add it to your transformation, connect it to your stream and configure it to use the pre-configured connection from the Database connection drop down menu. Next, specify in which table the data should be loaded and which fields from the PDI data stream should be written.

1) Type in the Target schema name (sys by default).

2) Type in or browse to select the Target table.

3) Open the Output Fields tab and select the Incoming Stream fields that should be loaded in MonetDB.

A few notes:

- The first column (Target Table fields) is not used for mapping the data stream fields to table columns, instead only the order in which the Incoming Stream fields are arranged is taken into account. In other words if a field called 'name' is arranged as the first incoming field, it will be mapped to the first column of the target table.

- If the target table does not exist, PDI will attempt to create it, inferring the data types from these of the type of the incoming data field. Due to some mismatch between data types we recommend creating the table beforehand. Clicking on the SQL button on dialog will display the SQL statement used for creating the table, including the data types.

Python Library

Python Library mk Tue, 03/17/2020 - 19:02

The MonetDB MAPI and SQL client python API


This is the native python client API. This API is cross-platform, and doesn't depend on any monetdb libraries. It has support for python 2.7 and 3.3+ and is Python DBAPI 2.0 compatible.


To install the MonetDB python API run the following command:

# pip install pymonetdb

That's all, now you are ready to start using the API. We recommend that you use virtual environments to avoid polluting your global python installation.


The python code is well documented, so if you need to find documentation you should have a look at the source code. Below is an interactive example on how to use the monetdb SQL API which should get you started quite fast.


There are some examples in the 'examples' folder, but here are is a line by line example of the SQL API:

> # import the SQL module
> import pymonetdb
> # set up a connection. arguments below are the defaults
> connection = pymonetdb.connect(username="monetdb", password="monetdb", hostname="localhost", database="demo")
> # create a cursor
> cursor = connection.cursor()
> # increase the rows fetched to increase performance (optional)
> cursor.arraysize = 100
> # execute a query (return the number of rows to fetch)
> cursor.execute('SELECT * FROM tables')
> # fetch only one row
> cursor.fetchone()
(1062, 'schemas', 1061, None, 0, True, 0, 0)
> # fetch the remaining rows
> cursor.fetchall()
[(1067, 'types', 1061, None, 0, True, 0, 0),
 (1076, 'functions', 1061, None, 0, True, 0, 0),
 (1085, 'args', 1061, None, 0, True, 0, 0),
 (1093, 'sequences', 1061, None, 0, True, 0, 0),
 (1103, 'dependencies', 1061, None, 0, True, 0, 0),
 (1107, 'connections', 1061, None, 0, True, 0, 0),
 (1116, '_tables', 1061, None, 0, True, 0, 0),
 (4141, 'user_role', 1061, None, 0, True, 0, 0),
 (4144, 'auths', 1061, None, 0, True, 0, 0),
 (4148, 'privileges', 1061, None, 0, True, 0, 0)]
> # Show the table meta data
> cursor.description
[('id', 'int', 4, 4, None, None, None),
 ('name', 'varchar', 12, 12, None, None, None),
 ('schema_id', 'int', 4, 4, None, None, None),
 ('query', 'varchar', 168, 168, None, None, None),
 ('type', 'smallint', 1, 1, None, None, None),
 ('system', 'boolean', 5, 5, None, None, None),
 ('commit_action', 'smallint', 1, 1, None, None, None),
 ('temporary', 'tinyint', 1, 1, None, None, None)]

If you would like to communicate with the database at a lower level you can use the MAPI library:

> from pymonetdb import mapi
> mapi_connection = mapi.Connection()
> mapi_connection.connect(hostname="localhost", port=50000, username="monetdb", password="monetdb", database="demo", language="sql", unix_socket=None, connect_timeout=-1)
> mapi_connection.cmd("sSELECT * FROM tables;")


ReportServer giulia Mon, 05/04/2020 - 17:28


ReportServer is a business intelligence platform that offers various reporting and visualisation tools. It comes both in a free Community edition and an Enterprise edition. More information can be found from their website. The tool most useful to MonetDB users is probably ReportServer's Dynamic List (its ad-hoc reporting component), which allows you to easily transform large MonetDB data tables into meaningful reports to be exported, for example, to Excel.

To set up ReportServer to talk to your MonetDB instance you will need to register the MonetDB JDBC driver. To register the JDBC driver with ReportServer you need to place the monetdb-jdbc-X.Y.jar (where X and Y are major and minor version numbers) into ReportServer's lib directory. If you installed ReportServer via one of the installers then the lib directory will be located in INSTALL_DIR/apps/reportserver/reportserver-conf/lib. If you did a manual installation then the lib directory will be /opt/reportserver/lib (on UNIX systems), or C:\Program Files\reportserver\lib on Windows. Once the driver is in the correct location, restart ReportServer.

In the following we quickly go through the basic steps to set up a Dynamic List on top of a MonetDB database table. Basically, you will need to do two things for this:

1) Create a datasource in ReportServer that points to your MonetDB installation
2) Create a Dynamic List based on that datasource.


To create a datasource for MonetDB go to the Administration Module and then to Datasources. In the datasource tree right click on a folder and select Insert > Relational Database. In the "Edit datasource" window, provide a Name for the datasource and select MonetDB as the Database (if the JDBC driver was not put into the right location, the MonetDB item will state "driver missing"). Username and Password are the username and password of the MonetDB database user with which you would like to connect to your database. Finally, the URL should be in the form jdbc:monetdb://HOST:PORT/DATABASE_NAME. For example, if your MonetDB instance is running on the same machine and on its default port, and your database is named voc, then the following URL could be used: jdbc:monetdb://localhost:50000/voc.

Save your changes by clicking the "Apply" button. To test whether the stored changes allow you to properly connect you can use the "Test Connection" button from the top toolbar (remember to always first save the changes before testing).

Now that we have a database, let us create a Dynamic List. For this we go to Administration > Reports and right click on a folder within the report tree to select Insert > Dynamic List. The basic configuration of a Dynamic List only needs a name, a database connection and a base query. That is, once you select your previously created MonetDB datasource, ReportServer will ask you to provide a query. This query forms the base of the Dynamic List. You can use any SQL statement that can be used within an inner statement. In case you simply want to base a report on a single data table, say, voc.passengers from the voc database, the query would be: SELECT * FROM passengers.

Once you've configured the Dynamic List you can start using it by simply double clicking the item in the tree. 

Here is an introduction to the Dynamic List. Documentation of ReportServer is available here.

SQL Workbench/J

SQL Workbench/J dnedev Mon, 05/04/2020 - 17:31

SQL Workbench/J is a tools for working SQL databases written in Java. You can downloaded it from the official website.

JDBC Driver

While SQL Workbench has some support for MonetDB, you will still need to download JDBC driver and quickly configure it. After starting SQL Workbench for the first time, it will load its default welcome screen and starts the wizard to select database driver. Cancel this wizard and open the Driver manager, using the menu File->Manage Drivers.

1) There add a new Driver clicking on the button in the top left corner of the dialog and type in a name for the driver, e.g. MonetDB.

2) Click on the folder-like icon and navigate to the previously downloaded JDBC .jar file.

3) The class name will be filled on it's own, but just in case it should be: nl.cwi.monetdb.jdbc.MonetDriver.

4) Type in a sample URL for database connection, e.g. jdbc:monetdb://hostname:50000/database.

Database Connection

Next, to connect to a MonetDB database instance open File->Connect window

1) Type in a name of the connection.

2) Selected the MonetDB driver from the drop-down menu.

3) Enter the details of your database connection, replacing the hostname, port and database name in the sample URL, e.g. jdbc:monetdb://localhost:50000/db for an instance running on localhost, port 50000 with database db.

4) Enter the username and password for the database

Confirm the connection and you are done.


SQuirreL mk Mon, 05/04/2020 - 17:32

SQuirreL SQL Client is a graphical Java program to view the structure of a JDBC compliant database, browse the data in tables, issue SQL commands, etc. It is a very cool tool. It can even draw dependency graphs based on the schema and foreign keys. The latest versions is available from their website. Download and install the software. The following scheme works for version 3.4.

After starting SQuirreL for the first time, it will load its default welcome screen. Locate the Drivers window and click to add a driver.


In the pop up form enter the driver name MonetDB, an example URL pointing to the database of interest jdbc:monetdb://<host>[:<port>]/<database>, the website Now goto Extra Class Path and add the MonetDB JDBC driver jar file. The latest version can be downloaded from the MonetDB repository or  came with your distribution, typically installed in ${prefix}/share/monetdb/lib/monetdb-jdbc-X.Y.jar. Next, click on the List Drivers button and it will insert nl.cwi.monetdb.jdbc.MonetDriver to the Class Name field. Click OK.

Now create a new Alias to setup a connection. You will have to edit the alias to direct it to the proper database server (change URL) and use the proper user credentials. Explore the application and have fun!


Tableau dnedev Mon, 05/04/2020 - 17:33

Tableau is a great data visualization and business intelligence software. Using the MonetDB ODBC driver, you can easily access your data. You can find more information about Tableau on their website.

Note: Currently only the Windows version of Tableau Desktop support ODBC connections. Tableau Public and all OS X version do not support ODBC connection.

To configure a connection to a MonetDB database, download and install Tableau, followed by the ODBC driver (download here). Be careful to download the appropriate version (32 or 64-bit) based on your system - 64-bit Tableau will only work with a 64-bit ODBC driver on a 64-bit Windows.

Next, (after having started the your database instance and loaded your data), open Tableau to configure the connection. From the welcome screen select Connect to data, scroll to the bottom of the On a server list and select Other Databases (ODBC). After that fill in the required details for connection to the MonetDB server:

1) Select DSN and find MonetDB from the drop down menu

2) Click connect to initialize the driver. If no system-wide DSN has been configured, you can now enter the details for database connection.

3) Type in the hostname of the server - localhost in our case.

4) Type in the port of the database server - 50000 by default.

5) Enter the database name - demo is the default database on Windows MonetDB installations.

6) Type in the username and password for the database.

Click OK and Tableau will establish a connection with the database instance.

Next, to get ready for data exploration or reporting you need to select which tables should be loaded in a Tableau worksheet.

1) Open the Schema drop down menu, click on the search button to list all schemas and select that one with your data tables - sys by default.

2) Open the Table drop down menu and click on the search button to list all to list all schemas. Selecting more than one table will usually bring up a menu to join the rows in a single query. Alternatively you can write you own Custom SQL query.

3) Select the table(s) you want to be added for visualisation. Depending on the data size, you might want to enable Automatically Update the table contents.

4) Click on the Go to Worksheet button.

That's it, you can now explore and visualise the data with Tableau.

You can also use Tableau instructions for MonetDB ODBC connection setup on Tableau connector.

You can find more about Tableau and ODBC connection on their website.