June 21, 2015

What You Should Know About MonetDB

Filed under: Programming — @ 21st of June 2015, 14:04

I have been using MonetDB for a month now and have to say, I really like it. MonetDB is a columnar data store and it’s freaking fast.

However, when I started using it, I wasn’t on very good terms with my database. Here are the three things you have to know when you use MonetDB:

  • Strings are quoted with single quotes. NOT double quotes! For example:
    SELECT * FROM table where field like '%foo';
  • Field names that are special terms need to be double quoted! There are many special terms, such as: “range”, “external”, “end”, … For example:
    SELECT "end", other FROM table;
  • The WHERE clause in a GROUP by statement is called HAVING (that’s just basic SQL knowledge, but good to remember): For example:
    SELECT * FROM table GROUP BY field HAVING field>500;
  • Querying an INET datatype needs to convert a STRING to an INET in the query as well:
    SELECT * FROM table WHERE ip=inet '10.0.0.2'; 
    or even cooler: 
    SELECT * FROM table WHERE ip<<inet '10.2.0.0/16';
  • MonetDB has schemas. They are almost like different databases if you want. You can switch schema by using:
    set schema foo;
  • Inserting millions of records is fastest with the COPY INTO command:
    cat file | mclient -d databse -s  "COPY INTO table FROM STDIN using delimiters '\t' " -
  • And here is how you create your own database and user:
    sudo monetdb create _database_ -p _password_
    sudo monetdb release _database_
    mclient -u monetdb -d _database_
      alter user set password '_password_' using old password 'monetdb';
      create user "_username_" with password '_password_' name '_name_' schema "sys";
      create schema "_your_schema_" authorization "_username_";
      alter user _username_ set schema "_your_schema_";

I hope these things will help you deal with MonetDB a bit easier. The database is worth a try!!

No Comments »

No comments yet.

RSS feed for comments on this post. | TrackBack URI

Leave a comment

XHTML ( You can use these tags): <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> .