Most of the database systems are row major systems and they work the assumption that all values of the row are fetched at a time. What to do when we need some values of the aggregation operations on the values of columns. For this it would be better if the values of columns are stored together in the memory block.
Column based databases enable the retrieval of the necessary and related data with efficient disk access and this database is better suited for online analytical processing or OLAP (e.g. Data warehouse)workloads.
What is MonetDB
MonetDB is an open-source column-oriented database management system which was developed by MonetDB B.V. developers at the Centrum Wiskunde & Informatica (CWI) in the Netherlands.
What are the alternatives
- Apache kudu
- Apache Druid
- ClickHouse
- Metakit
MonetDB Installation
- cd /opt
- wget https://www.monetdb.org/downloads/sources/Apr2019-SP1/MonetDB-11.33.11.tar.bz2
- bzip2 -dk MonetDB-11.33.11.tar.bz2
- tar -xvf MonetDB-11.33.11.tar
- configure
- make
- make install
Database Creation
- monetdbd create /opt/Monet/database/newdata
- monetdbd start /opt/Monet/database/newdata
- ps -ef | grep monetdb
- monetdb create voc
- monetdb release voc (To move from maintenance mode)
Connect to Database
[root@hydnekqajackpot01 newdata]# mclient -u monetdb -d voc
password: <monetdb>
Welcome to mclient, the MonetDB/SQL interactive terminal (Apr2019-SP1)
Database: MonetDB v11.33.11 (Apr2019-SP1), ‘mapi:monetdb://hydnekqajackpot01:50000/voc’
Type \q to quit, \? for a list of available commands
auto commit mode: on
sql>select ‘hello world’;
+————-+
| L2 |
+=============+
| hello world |
+————-+
1 tuple
sql>
Create new user and import database
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”;
\q – To Quit
wget http://www.monetdb.org/sites/default/files/voc_dump.zip
unzip voc_dump.zip
mclient -u voc -d voc voc voc_dump.sql (Password voc)
mclient -u voc -d voc (Password voc)
sql>select count(*) from voyages;
+——+
| L3 |
+======+
| 8131 |
+——+
1 tuple
sql>
\q – To Quit
MonetDB support various Extensions
- GeoSpatial https://www.monetdb.org/Documentation/Extensions/GIS
- LifeScience https://www.monetdb.org/bam
- Data Vaults https://www.monetdb.org/Documentation/Extensions/datavaults