{"id":827,"date":"2015-06-21T14:04:01","date_gmt":"2015-06-21T22:04:01","guid":{"rendered":"http:\/\/raffy.ch\/blog\/?p=827"},"modified":"2015-06-21T14:04:01","modified_gmt":"2015-06-21T22:04:01","slug":"what-you-should-know-about-monetdb","status":"publish","type":"post","link":"https:\/\/raffy.ch\/blog\/2015\/06\/21\/what-you-should-know-about-monetdb\/","title":{"rendered":"What You Should Know About MonetDB"},"content":{"rendered":"<p>I have been using <a href=\"http:\/\/monetdb.org\">MonetDB<\/a> for a month now and have to say, I really like it. MonetDB is a columnar data store and it&#8217;s freaking fast.<\/p>\n<p>However, when I started using it, I wasn&#8217;t on very good terms with my database. Here are the three things you have to know when you use MonetDB:<\/p>\n<ul>\n<li>Strings are quoted with single quotes. NOT double quotes! For example:\n<pre>SELECT * FROM table where field like '%foo';<\/pre>\n<\/li>\n<li>Field names that are special terms need to be double quoted! There are many special terms, such as: &#8220;range&#8221;, &#8220;external&#8221;, &#8220;end&#8221;, &#8230; For example:\n<pre>SELECT \"end\", other FROM table;<\/pre>\n<\/li>\n<li>The WHERE clause in a GROUP by statement is called HAVING (that&#8217;s just basic SQL knowledge, but good to remember): For example:\n<pre>SELECT * FROM table GROUP BY field HAVING field>500;<\/pre>\n<\/li>\n<li>Querying an INET datatype needs to convert a STRING to an INET in the query as well:\n<pre>SELECT * FROM table WHERE ip=inet '10.0.0.2'; \r\nor even cooler: \r\nSELECT * FROM table WHERE ip&lt;&lt;inet '10.2.0.0\/16';<\/pre>\n<\/li>\n<li>MonetDB has <b>schemas<\/b>. They are almost like different databases if you want. You can switch schema by using:\n<pre>set schema foo;<\/pre>\n<\/li>\n<li>Inserting millions of records is fastest with the COPY INTO command:\n<pre>cat file | mclient -d databse -s  \"COPY INTO table FROM STDIN using delimiters '\\t' \" -<\/pre>\n<\/li>\n<li>And here is how you create your own database and user:\n<pre>sudo monetdb create _database_ -p _password_\r\nsudo monetdb release _database_\r\nmclient -u monetdb -d _database_\r\n  alter user set password '_password_' using old password 'monetdb';\r\n  create user \"_username_\" with password '_password_' name '_name_' schema \"sys\";\r\n  create schema \"_your_schema_\" authorization \"_username_\";\r\n  alter user _username_ set schema \"_your_schema_\";<\/pre>\n<\/li>\n<\/ul>\n<p>I hope these things will help you deal with MonetDB a bit easier. The database is worth a try!!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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&#8217;s freaking fast. However, when I started using it, I wasn&#8217;t on very good terms with my database. Here are the three things you have to know when you use MonetDB: [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-827","post","type-post","status-publish","format-standard","hentry","category-programming"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/raffy.ch\/blog\/wp-json\/wp\/v2\/posts\/827","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/raffy.ch\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/raffy.ch\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/raffy.ch\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/raffy.ch\/blog\/wp-json\/wp\/v2\/comments?post=827"}],"version-history":[{"count":13,"href":"https:\/\/raffy.ch\/blog\/wp-json\/wp\/v2\/posts\/827\/revisions"}],"predecessor-version":[{"id":840,"href":"https:\/\/raffy.ch\/blog\/wp-json\/wp\/v2\/posts\/827\/revisions\/840"}],"wp:attachment":[{"href":"https:\/\/raffy.ch\/blog\/wp-json\/wp\/v2\/media?parent=827"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/raffy.ch\/blog\/wp-json\/wp\/v2\/categories?post=827"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/raffy.ch\/blog\/wp-json\/wp\/v2\/tags?post=827"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}