- users-list - monetdb.org

MonetDB cannot find file to load
by João Victor Risso 19 May '16

19 May '16

Hi All, I am benchmarking MonetDB against other databases, in special the current version of CitusDB's Postgres, to find out which one is a good fit for our OLAP application. In order to do that, I've decided to use a standard benchmark, TPC-H. However, I did not manage to load the data generated by dbgen (one of the tools included in the benchmark) into MonetDB. Here is the SQL statement I've tried to run: > COPY INTO region FROM '/home/user/tpch/dbgen/region.tbl' USING DELIMITERS '|', '\n' LOCKED; and it generated an error which says the file could not be found: > could not open file '/home/user/tpch/dbgen/region.tbl': No such file or directory even though the file exists. I'm running the MonetDB provided in the official repository and the version reported by mclient is: > MonetDB v11.21.19 (Jul2015-SP4) Any help would be appreciated. -- João Victor Tozatti Risso C3SL - Centro de Computação Científica e Software Livre Universidade Federal do Paraná Please consider using PGP encryption to make our communications more secure. You can find my public key at https://pgp.mit.edu/ | Key ID: 395192B8 If you want to know more about PGP encryption, check the following link: https://emailselfdefense.fsf.org/en/

2 1

Monetdb Keep alive in Pentaho
by Ariel Abadi 18 May '16

18 May '16

Hi All! We are using Pentaho (BA Server) and have created a JNDI connection using Monetdb. We are facing some "errors". It seems that the connection to pentaho sometimes drops, therefore wanted to know which will be the correct sintax (if there is one) to send a "keep alive" in the JNDI connection. Thks in advance

1 0

RE: Passing mapi_open Param to mserver5 via monetdbd (Syed M Raza)
by Syed M Raza 17 May '16

17 May '16

Hi Everyone Any help or pointer on starting monetdb server to accept remote connections? Original message attached below. Regards Raza -----Original Message----- Message: 1 Date: Wed, 11 May 2016 13:28:51 +0000 From: Syed M Raza <smr337(a)cornell.edu> To: "users-list(a)monetdb.org" <users-list(a)monetdb.org> Subject: Passing mapi_open Param to mserver5 via monetdbd Message-ID: <BY1PR0401MB1429201171375B1BD9BB46258E720(a)BY1PR0401MB1429.namprd04.prod.outlook.com> Content-Type: text/plain; charset="us-ascii" Hi All Is there a way to pass config params to mserver5 via monetdbd daemon? I am trying to open database for remote connections. After started the dbfarm using monetdbd I see entries in log file that calls mserver5 are made with mapi_open=false. I couldn't figure out how to pass mapi_open=open instead, or for that matter, other params to mserver5. What I understand from documentation that mserver5 should be managed via monetdbd daemon. Or should I try to start mserver5 separately, for each database in the dbfarm? How would those instances then attach to the monetdbd daemon? For what it worth, I did try - creating a monetdb.conf file in /path/to/dbfarm directory - setting map_open using monetdbd set name=value MonetDB v11.21.19 ( Jul2015-SP4) OS - Ubuntu 14.04 LTS Thanks for your help in advance. Regards Raza

4 3

Data caching in MonetDB
by Vijay Krishna 16 May '16

16 May '16

Hi, We were studying on the parallel execution of MonetDB and memory usage of MonetDB. 1. If two queries access the same table in parallel, is the table loaded twice into memory or its loaded only once? 2. Is the hot dataset from a column of a query cached in memory, so that any further queries accessing that column may use it? 3. Are the interim results of a query cached in memory for further queries? Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

1 0

Crystal Language - MonetDB Guide
by Brian Hood 14 May '16

14 May '16

Hi All, I've created a guide for using MonetDB with the Crystal Programming Language. https://github.com/puppetpies/crystal-monetdb-libmapi/blob/master/examples/… Enjoy! Regards, Brian Hood

1 0

Error when trying to load GO.db
by Willem Ligtenberg 12 May '16

12 May '16

Hi, I am trying to run some R code in database. Most of it is going pretty well, but I seem to have stumbled on a bug. I cannot load the following package, which is a dependency for some of my code. WGCNA I have been chasing it down, and it seems to be due to an error when trying to load GO.db. I get the following error: Error in .local(conn, statement, ...) : Unable to execute statement 'SELECT * FROM tmp_test();'. Server says '!Error running R expression. Error message: Error in as.data.frame((function() { : '. Digging a bit further it seems to be due to the following statement: dbconn <- dbFileConnect(dbfile) Error can be reproduced using: functionDef <- paste( "CREATE FUNCTION tmp_test() ", "RETURNS TABLE(output STRING)", "LANGUAGE R ", "{", "library(AnnotationDbi)", "datacache <- new.env(hash=TRUE, parent=emptyenv())", "pkgname <- 'GO.db'", "libname <- .libPaths()[1]", "dbfile <- system.file('extdata', 'GO.sqlite', package=pkgname, lib.loc=libname)", "assign('dbfile', dbfile, envir=datacache)", "dbconn <- dbFileConnect(dbfile)", "};", sep = "\n") dbGetQuery(conn, functionDef) dbGetQuery(conn, "SELECT * FROM tmp_test();") By the way, installing GO.db from within MonetDB works just fine. And can be done using the following R code: source("https://bioconductor.org/biocLite.R") biocLite("GO.db") Hints on how to resolve this are greatly appreciated. Kind regards, Willem

2 1

creating a table with char[3]
by Lynn Carol Johnson 11 May '16

11 May '16

Hi All - I'm trying to create a table with specific size for text fields. In particular, I am storing a 3 byte character and an 8 byte character. I tried the following but get errors: CREATE TABLE te_LTRTest(chr int, pos int, te_gag_protein tinyint, te_ap_protein tinyint, te_integrase_protein tinyint, te_rt_protein tinyint, te_rnaseh_protein tinyint, te_chromodomain_protein tinyint, te_envelope_protein tinyint, te_ltr_age real, te_numb_ltrtes_inside integer, te_superfamily char'('3')', te_family char'('8')'); Gives the error: sql>\</workdir/lcj34/monetdbFiles/michelle_TE/create_teLTRTest.sql syntax error, unexpected STRING, expecting ')' or ',' in: "create table te_ltrtest(chr int, pos int, te_gag_protein tinyint, te_ap_protein" sql> Switching to this: CREATE TABLE te_LTRTest(chr int, pos int, te_gag_protein tinyint, te_ap_protein tinyint, te_integrase_protein tinyint, te_rt_protein tinyint, te_rnaseh_protein tinyint, te_chromodomain_protein tinyint, te_envelope_protein tinyint, te_ltr_age real, te_numb_ltrtes_inside integer, te_superfamily char[3], te_family char[8]); Gives the following error: sql>\</workdir/lcj34/monetdbFiles/michelle_TE/create_teLTRTest.sql syntax error, unexpected '[', expecting ')' or ',' in: "create table te_ltrtest(chr int, pos int, te_gag_protein tinyint, te_ap_protein" sql> I have 2.1 billion rows so am using the binary bulk loads. My non-numeric binary files were created to contain 3-byte values and 8-byte values. This is what I am trying to load, however I'm not getting past the create table stage. I love the binary loader. This is the first time I've needed to include text data in my tables. Is this supported? I'm hoping it is just my syntax. Any suggestions? Thanks - Lynn

4 6

Can not import 128 bit values
by Shmagi Kavtaradze 11 May '16

11 May '16

I create table with HUGEINT type. When I am trying to import data from text file, Monetdb fires error: Failed to import table line 508 field 3 'hugeint' expected in '170141183460469231731687303715888300032', but this value has exactly 128 bits. Any ideas how to fix the issue? Thanks.

2 2

Passing mapi_open Param to mserver5 via monetdbd
by Syed M Raza 11 May '16

11 May '16

Hi All Is there a way to pass config params to mserver5 via monetdbd daemon? I am trying to open database for remote connections. After started the dbfarm using monetdbd I see entries in log file that calls mserver5 are made with mapi_open=false. I couldn't figure out how to pass mapi_open=open instead, or for that matter, other params to mserver5. What I understand from documentation that mserver5 should be managed via monetdbd daemon. Or should I try to start mserver5 separately, for each database in the dbfarm? How would those instances then attach to the monetdbd daemon? For what it worth, I did try - creating a monetdb.conf file in /path/to/dbfarm directory - setting map_open using monetdbd set name=value MonetDB v11.21.19 ( Jul2015-SP4) OS - Ubuntu 14.04 LTS Thanks for your help in advance. Regards Raza Consultant, GOBII project Genomics & Open-source Breeding Informatics Initiative Boyce Thompson Institute # 224 Cornell University Ithaca, NY 14853

1 0

monetdb is great, thanks everyone who makes it so
by Anthony Damico 09 May '16

09 May '16

4 3

Crystal binding MonetDB / Example project
by Brian Hood 08 May '16

08 May '16

Hi Guys, If your interested in Programming Languages i've recently built the binding for MonetDB in the Crystal Language if you don't know that Crystal is its a Ruby inspired language that is statically typed and compiles in byte code via LLVM yes interesting. Here is the Driver as they used to call them. https://github.com/puppetpies/crystal-monetdb-libmapi Very small example project that i will expand upon. https://github.com/puppetpies/crystal-monetdb-examples Also yes i will write a blog as well Martin i'm not sure what want me to write but i am put together a reference guide for people that might come along in the future to use this. Regards, Brian Hood

1 0

Memory management - MonetDB on CentOS
by Vijay Krishna 07 May '16

07 May '16

Hi, We have been running tests on Memory management with the latest version of MonetDB on a CentOS installation. The machine has 1TB hard disk and 128 GB RAM space. The swap memory configured is 2GB. We were aware that MonetDB completely relies on the OS for Memory Management and is prone to OOM Killer crash and hence we wanted to simulate the crash. Hence we ran hundreds of threads executing various queries and noticed the following pattern of memory handling. 1. Initially, RAM was filled. 2. Upon RAM overflow, swap memory (2 GB) was used. 3. Upon swap overflow, the swap memory got extended and the 1TB memory that was free on the machine was used completely. 4. The OOM killer got invoked only when this 1TB memory got filled. 5. On consecutive executions, swap memory was started to be used even when RAM had 70GB of memory unused and free (not even used for cache files). 6. The /tmp partition was never used. Memory got overfilled only in the current partition. These observations led to the following questions about the memory management with CentOS. 1. Does MonetDB has no influence on the above behaviour and is it that only the OS is responsible for this? 2. Will the swap memory be expanded from the committed size by the OS if swap is running out of memory? In our case it expanded from 2GB to 1 TB. Our OS setting of overcommit is 0 (sysctl vm.overcommit_memory = 0) 3. When will the OOM Killer be invoked usually? Upon Swap fill or upon Hard disk space full? 4. Why was memory swapped even when there is 70GB free space in RAM? We tried reducing the swappiness to 10 (sysctl vm.swappiness=10), even then memory was swapped with 70 GB free space in RAM. 5. Does the OS writes intermediate results to /tmp directory? Or is it that MonetDB is bypassing this? 6. Is there any other OS configurations that we need to cross check regarding memory management? Any help much appreciated. Thanks in advance. Regards, Vijay.

1 0

index clustering
by Shmagi Kavtaradze 06 May '16

06 May '16

When searching for index clustering, I read that no user defined index clustering exists in Monetdb. Does it mean that I can not cluster table according to index? If not, how can I sort table to speed-up query? Thanks in advance.

2 1

Problem with RANK/DENSE_RANK in a subquery (dense_rank_grp undefined)
by Knezevic Nikola 04 May '16

04 May '16

Hi all, I have several tables which represent different sources (sensors) of data. I want to find out which sensors picked up the signal first, and thus I decided to join these tables together, rank on common criteria and then select the first ones. However, that fails with: TypeException:user.s22_3[110]:'calc.dense_rank_grp' undefined in: calc.dense_rank_grp(X_505:date,X_507:str,X_509:int,X_511:int,X_513:lng); program contains errors Any ideas how to fix this? Cheers, Nikola Data: CREATE TABLE taba ( aday DATE, key1 varchar(15), msid int, sid int, eid bigint, r bigint ); CREATE TABLE tabb ( aday DATE, key1 varchar(15), msid int, sid int, aid bigint, eid bigint, r bigint ); CREATE TABLE tabc ( aday DATE, key1 varchar(15), msid int, sid int, aid bigint, m bigint, r bigint ); COPY 3 RECORDS INTO taba FROM stdin USING DELIMITERS '|','\n'; 2016-05-02|inst1|675|10001|200|100 2016-05-02|inst1|675|10002|500|111 2016-05-02|inst1|789|10001|800|135 COPY 3 RECORDS INTO tabb FROM stdin USING DELIMITERS '|','\n'; 2016-05-02|inst1|675|10001|77|200|88 2016-05-02|inst1|675|10002|100|500|112 2016-05-02|inst1|789|10001|112|800|134 COPY 3 RECORDS INTO tabc FROM stdin USING DELIMITERS '|','\n'; 2016-05-02|inst1|675|10001|77|200|100 2016-05-02|inst1|675|10002|100|500|111 2016-05-02|inst1|789|10001|112|800|130 Query: SELECT aday, key1, msid, sid, aid, eid, src, r, rank FROM ( SELECT * ,sys.DENSE_RANK() OVER (PARTITION BY aday, key1, msid, sid, eid ORDER BY r) as rank FROM ( (SELECT aday, key1, msid, sid, NULL as aid, eid, 'TABA' as src, r FROM taba) UNION (SELECT aday, key1, msid, sid, aid, eid, 'TABB' as src, r FROM tabb) UNION (SELECT aday, key1, msid, sid, aid, m as eid, 'TABC' AS src, r FROM tabc) ) AS dummy1 ) AS dummy2 WHERE rank = 1 ; Results: sql>CREATE TABLE taba ( more>aday DATE, more>key1 varchar(15), more>msid int, more>sid int, more>eid bigint, more>r bigint more>); operation successful (4.102ms) sql>CREATE TABLE tabb ( more>aday DATE, more>key1 varchar(15), more>msid int, more>sid int, more>aid bigint, more>eid bigint, more>r bigint more>); operation successful (3.172ms) sql>CREATE TABLE tabc ( more>aday DATE, more>key1 varchar(15), more>msid int, more>sid int, more>aid bigint, more>m bigint, more>r bigint more>); operation successful (3.326ms) sql> sql>COPY 3 RECORDS INTO taba FROM stdin USING DELIMITERS '|','\n'; more>2016-05-02|inst1|675|10001|200|100 more>2016-05-02|inst1|675|10002|500|111 more>2016-05-02|inst1|789|10001|800|135 3 affected rows (88.249ms) sql> sql>COPY 3 RECORDS INTO tabb FROM stdin USING DELIMITERS '|','\n'; more>2016-05-02|inst1|675|10001|77|200|88 more>2016-05-02|inst1|675|10002|100|500|112 more>2016-05-02|inst1|789|10001|112|800|134 3 affected rows (59.917ms) sql> sql>COPY 3 RECORDS INTO tabc FROM stdin USING DELIMITERS '|','\n'; more>2016-05-02|inst1|675|10001|77|200|100 more>2016-05-02|inst1|675|10002|100|500|111 more>2016-05-02|inst1|789|10001|112|800|130 3 affected rows (89.804ms) sql> sql>select * from taba; +------------+-------+------+-------+------+------+ | aday | key1 | msid | sid | eid | r | +============+=======+======+=======+======+======+ | 2016-05-02 | inst1 | 675 | 10001 | 200 | 100 | | 2016-05-02 | inst1 | 675 | 10002 | 500 | 111 | | 2016-05-02 | inst1 | 789 | 10001 | 800 | 135 | +------------+-------+------+-------+------+------+ 3 tuples (4.340ms) sql>select * from tabb; +------------+-------+------+-------+------+------+------+ | aday | key1 | msid | sid | aid | eid | r | +============+=======+======+=======+======+======+======+ | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | 88 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | 112 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | 134 | +------------+-------+------+-------+------+------+------+ 3 tuples (3.484ms) sql>select * from tabc; +------------+-------+------+-------+------+------+------+ | aday | key1 | msid | sid | aid | eid | m | +============+=======+======+=======+======+======+======+ | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | 100 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | 111 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | 130 | +------------+-------+------+-------+------+------+------+ 3 tuples (3.215ms) sql>SELECT * more>FROM ( more>SELECT * more> ,sys.DENSE_RANK() OVER (PARTITION BY aday, key1, msid, sid, eid ORDER BY r) as rank more> FROM ( more> (SELECT aday, key1, msid, sid, NULL as aid, eid, 'TABA' as src, r FROM taba) more> UNION more> (SELECT aday, key1, msid, sid, aid, eid, 'TABB' as src, r FROM tabb) more> UNION more> (SELECT aday, key1, msid, sid, aid, m as eid, 'TABC' AS src, r FROM tabc) more> ) AS dummy1 more>) AS dummy2; +------------+-------+------+-------+------+------+------+------+------+ | aday | key1 | msid | sid | aid | eid | src | r | rank | +============+=======+======+=======+======+======+======+======+======+ | 2016-05-02 | inst1 | 675 | 10001 | null | 200 | TABA | 100 | 2 | | 2016-05-02 | inst1 | 675 | 10002 | null | 500 | TABA | 111 | 1 | | 2016-05-02 | inst1 | 789 | 10001 | null | 800 | TABA | 135 | 3 | | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | TABB | 88 | 1 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | TABB | 112 | 2 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | TABB | 134 | 2 | | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | TABC | 100 | 2 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | TABC | 111 | 1 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | TABC | 130 | 1 | +------------+-------+------+-------+------+------+------+------+------+ 9 tuples (13.527ms) sql>SELECT aday, key1, msid, sid, aid, eid, src, r, rank more>FROM ( more>SELECT * more> ,sys.DENSE_RANK() OVER (PARTITION BY aday, key1, msid, sid, eid ORDER BY r) as rank more> FROM ( more> (SELECT aday, key1, msid, sid, NULL as aid, eid, 'TABA' as src, r FROM taba) more> UNION more> (SELECT aday, key1, msid, sid, aid, eid, 'TABB' as src, r FROM tabb) more> UNION more> (SELECT aday, key1, msid, sid, aid, m as eid, 'TABC' AS src, r FROM tabc) more> ) AS dummy1 more>) AS dummy2 more>WHERE rank = 1 more>; TypeException:user.s22_3[110]:'calc.dense_rank_grp' undefined in: calc.dense_rank_grp(X_505:date,X_507:str,X_509:int,X_511:int,X_513:lng); program contains errors

2 2

PCRE: references to captures in replace string
by Roberto Cornacchia 04 May '16

04 May '16

After declaring a wrapper for pcre.replace: sql>create function pcre_replace(s string, pattern string, repl string, flags string) returns string external name pcre."replace"; operation successful (6.187ms) I cannot get replacements with references to captures work (but the code in pcre.c seems to support it): sql>select pcre_replace('Apple Pear', '^([a-zA-Z]+)\\s*([a-zA-Z]+)$', '\\1 \\2', ''); +---------------------------+ | pcre_replace_single_value | +===========================+ | \1 \2 | +---------------------------+ 1 tuple (0.309ms) The pattern is matched, but the back references are replaced literally. Am I not getting the syntax correct? >From http://www.pcre.org/original/doc/html/pcresyntax.html I see I could use any of \1, \g1, \g{1] for back references, but none seem to work. Roberto

3 7

Problem: 'ALTER TABLE x ADD TABLE y' does not merge the data in from y.
by Knezevic Nikola 04 May '16

04 May '16

Hi all, I'm splitting my data into per-day tables and importing them into monetdb. Before the import, I first create a per-day table and then use 'ALTER TABLE "table" ADD TABLE "table__2016-05-03";' command (passed to cursor.execute() in python. "table" is defined to be a MERGE TABLE. Now, I had only a single day that was populated on the initial run, when both MERGE TABLE and day table were created. Today, I added a new day table and ran this: sql> SELECT day, count(*) FROM "table" GROUP BY day; +------------+---------+ | day | L1 | +============+=========+ | 2016-04-26 | 2959601 | +------------+---------+ sql>select day, count(*) from "trades__2016-05-02" group by day; +------------+---------+ | day | L1 | +============+=========+ | 2016-05-02 | 2220143 | +------------+---------+ So, the output is missing the current day, and I thought my python code didn't properly tie these tables together. However, inspecting sys.* tables gives me this: sql>\d (snip...) MERGE TABLE my_namespace.trades TABLE my_namespace.trades__2016-04-26 TABLE my_namespace.trades__2016-05-02 (snip...) sql>select * from sys.tables where name like 'trades%'; +-------+--------------------+-----------+-------+------+--------+---------------+--------+-----------+ | id | name | schema_id | query | type | system | commit_action | access | temporary | +=======+====================+===========+=======+======+========+===============+========+===========+ | 13480 | trades | 6497 | null | 3 | false | 0 | 0 | 0 | | 13518 | trades__2016-04-26 | 6497 | null | 0 | false | 0 | 0 | 0 | | 16203 | trades__2016-05-02 | 6497 | null | 0 | false | 0 | 0 | 0 | +-------+--------------------+-----------+-------+------+--------+---------------+--------+-----------+ 3 tuples (3.323ms) sql>select * from sys.dependencies where depend_id=13480; +-------+-----------+-------------+ | id | depend_id | depend_type | +=======+===========+=============+ | 13518 | 13480 | 2 | | 16203 | 13480 | 2 | +-------+-----------+-------------+ Finally, if I manually add the table, it succeeds: sql>alter table "trades" add table "trades__2016-05-02"; operation successful (13.894ms) sql>select day, count(*) from "trades" group by day; +------------+---------+ | day | L1 | +============+=========+ | 2016-04-26 | 2959601 | | 2016-05-02 | 2220143 | +------------+---------+ 2 tuples (44.196ms) sql>select * from sys.tables where name like 'trades%'; +-------+--------------------+-----------+-------+------+--------+---------------+--------+-----------+ | id | name | schema_id | query | type | system | commit_action | access | temporary | +=======+====================+===========+=======+======+========+===============+========+===========+ | 13480 | trades | 6497 | null | 3 | false | 0 | 0 | 0 | | 13518 | trades__2016-04-26 | 6497 | null | 0 | false | 0 | 0 | 0 | | 16203 | trades__2016-05-02 | 6497 | null | 0 | false | 0 | 0 | 0 | +-------+--------------------+-----------+-------+------+--------+---------------+--------+-----------+ 3 tuples (7.445ms) sql>select * from sys.dependencies where depend_id=13480; +-------+-----------+-------------+ | id | depend_id | depend_type | +=======+===========+=============+ | 13518 | 13480 | 2 | | 16203 | 13480 | 2 | +-------+-----------+-------------+ 2 tuples (1.714ms) For completeness, python code looks like this (cleaned up, to present the case, keep in mind that on initial import, this worked): if args.create: if args.partition and has_day: dest_create_merge_table = re.sub('CREATE TABLE', 'CREATE MERGE TABLE', dest_create_table, count=1) print("Creating table with the following content:\n{ddl}".format(ddl=dest_create_merge_table), file=sys.stderr) try: cursor = monet_conn.cursor() cursor.execute(dest_create_merge_table) except monetdb.exceptions.OperationalError as e: if re.search('CREATE TABLE: name .* already in use', e.args[0]): print("Master table already present") else: raise(e) finally: cursor.close() if args.partition and has_day: cursor = monet_conn.cursor() for c in conditions: (text, day, sth) = c dest_create_partition = re.sub('CREATE TABLE "{table}"'.format(table=table), 'CREATE TABLE "{partition}"'.format(partition=partition_name(table, day)), dest_create_table, count=1) print("Creating partition with the following content:\n{ddl}".format(ddl=dest_create_partition), file=sys.stderr) cursor.execute(dest_create_partition) if args.debug: print('ALTER TABLE "{table}" ADD TABLE "{partition}"'.format(table=table, partition=partition_name(table, day))) cursor.execute('ALTER TABLE "{table}" ADD TABLE "{partition}"; COMMIT;'.format(table=table, partition=partition_name(table, day))) cursor.close() Can someone please help me debug this issue? Is this a bug? IIUC, metadata points that "trades__2016-05-02" belongs to "trades", but the queries do not reflect that. Thanks, Nikola

3 5

Crashed DB. cannot start DB
by Ariel Abadi 03 May '16

03 May '16

Hi! Can someone help me please with this issue? Im trying to start my db.. and Im getting the following erro from the log: 2016-05-03 15:08:38 MSG merovingian[14646]: starting database 'bm_traf', up min/avg/max: 3d/5d/1w, crash average: 1.00 0.40 0.13 (6-2=4) 2016-05-03 15:08:38 MSG bm_traf[28169]: arguments: /usr/bin/mserver5 --dbpath=/opt/monetdb/bm/bm_traf --set merovingian_uri=mapi:monetdb://monetdb:50000/bm_traf --set mapi_open=false --set mapi_port=0 --set mapi_usock=/opt/monetdb/bm/bm_traf/.mapi.sock --set monet_vault_key=/opt/monetdb/bm/bm_traf/.vaultkey --set gdk_nr_threads=4 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2016-05-03 15:08:38 MSG merovingian[14646]: database 'bm_traf' (28169) has exited with exit status 1 2016-05-03 15:08:43 ERR merovingian[14646]: client error: database 'bm_traf' appears to shut itself down after starting, check monetdbd's logfile for possible hints Does anybody has a clue? Thks in advance!

2 2

Query Histroy & query parsing / translation / compilation / optimization time
by Stefan Manegold 03 May '16

03 May '16

Hi, looking for ways to measure the query parsing / translation / compilation / optimization time in MonetDB, I found these two documentations on the MonetDB website: https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryHistory and https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryHistoryNew While the documentation is not entirely clear on whether the latter did, will, or is supposed to replace the former (and if so, when), using the latest Jul2015-SP4 release, I get with the former: sql>set history=true; operation successful (137.545ms) sql>select 1; +--------------+ | single_value | +==============+ | 1 | +--------------+ 1 tuple (155.577ms) sql>select 2; +--------------+ | single_value | +==============+ | 2 | +--------------+ 1 tuple (193.633ms) sql>select * from sys.queryHistory; SELECT: no such table 'queryhistory' sql>select * from sys.callHistory; SELECT: no such table 'callhistory' sql>select * from sys.queryLog; SELECT: no such table 'querylog' sql>call sys.resetHistory(); SELECT: no such operator 'resethistory' I.e, this does not seem to work (anymore?). The latter seems to work fine: sql>call sys.querylog_enable(); sql>select 1; +--------------+ | single_value | +==============+ | 1 | +--------------+ 1 tuple (127.056ms) sql>select 1; +--------------+ | single_value | +==============+ | 1 | +--------------+ 1 tuple (124.303ms) sql>select 2; +--------------+ | single_value | +==============+ | 2 | +--------------+ 1 tuple (148.923ms) sql>select * from sys.querylog_catalog; +------+---------+----------------------------+-------------------------------------+--------------+-----------+------+----------+ | id | owner | defined | query | pipe | plan | mal | optimize | +======+=========+============================+=====================================+==============+===========+======+==========+ | 42@0 | monetdb | 2016-05-03 00:06:44.000000 | call sys.querylog_enable(); | default_pipe | user.s6_1 | 19 | 267 | | 43@0 | monetdb | 2016-05-03 00:06:47.000000 | select 1; | default_pipe | user.s7_1 | 21 | 273 | | 44@0 | monetdb | 2016-05-03 00:06:48.000000 | select 1; | default_pipe | user.s7_1 | 21 | 273 | | 45@0 | monetdb | 2016-05-03 00:06:50.000000 | select 1; | default_pipe | user.s7_1 | 21 | 273 | | 46@0 | monetdb | 2016-05-03 00:06:56.000000 | select * from sys.querylog_catalog; | default_pipe | user.s8_1 | 68 | 665 | +------+---------+----------------------------+-------------------------------------+--------------+-----------+------+----------+ 5 tuples (132.446ms) sql>select * from sys.querylog_calls; +------+----------------------------+----------------------------+------------------------------------------+--------+-------+------+------+------+ | id | start | stop | arguments | tuples | run | ship | cpu | io | +======+============================+============================+==========================================+========+=======+======+======+======+ | 41@0 | 2016-05-03 00:06:38.000000 | 2016-05-03 00:06:39.000000 | user.s5_1{autoCommit=true}():void; | 1 | 85485 | 1 | 3 | 24 | | 42@0 | 2016-05-03 00:06:44.000000 | 2016-05-03 00:06:44.000000 | user.s6_1{autoCommit=true}():void; | 1 | 71 | 0 | 0 | 0 | | 43@0 | 2016-05-03 00:06:47.000000 | 2016-05-03 00:06:47.000000 | user.s7_1{autoCommit=true}(A0:bte):void; | 1 | 120 | 0 | 0 | 0 | | 44@0 | 2016-05-03 00:06:48.000000 | 2016-05-03 00:06:48.000000 | user.s7_1{autoCommit=true}(A0:bte):void; | 1 | 104 | 0 | 0 | 0 | | 45@0 | 2016-05-03 00:06:50.000000 | 2016-05-03 00:06:50.000000 | user.s7_1{autoCommit=true}(A0:bte):void; | 1 | 66 | 1 | 0 | 0 | | 46@0 | 2016-05-03 00:06:56.000000 | 2016-05-03 00:06:56.000000 | user.s8_1{autoCommit=true}():void; | 8 | 886 | 0 | 50 | 0 | +------+----------------------------+----------------------------+------------------------------------------+--------+-------+------+------+------+ 6 tuples (100.344ms) sql>select * from sys.querylog_history; +------+---------+----------------------------+-------------------------------------+--------------+-----------+------+----------+----------------------------+----------------------------+------------------------------------------+--------+------+------+------+------+ | id | owner | defined | query | pipe | plan | mal | optimize | start | stop | arguments | tuples | run | ship | cpu | io | +======+=========+============================+=====================================+==============+===========+======+==========+============================+============================+==========================================+========+======+======+======+======+ | 42@0 | monetdb | 2016-05-03 00:06:44.000000 | call sys.querylog_enable(); | default_pipe | user.s6_1 | 19 | 267 | 2016-05-03 00:06:44.000000 | 2016-05-03 00:06:44.000000 | user.s6_1{autoCommit=true}():void; | 1 | 71 | 0 | 0 | 0 | | 43@0 | monetdb | 2016-05-03 00:06:47.000000 | select 1; | default_pipe | user.s7_1 | 21 | 273 | 2016-05-03 00:06:47.000000 | 2016-05-03 00:06:47.000000 | user.s7_1{autoCommit=true}(A0:bte):void; | 1 | 120 | 0 | 0 | 0 | | 44@0 | monetdb | 2016-05-03 00:06:48.000000 | select 1; | default_pipe | user.s7_1 | 21 | 273 | 2016-05-03 00:06:48.000000 | 2016-05-03 00:06:48.000000 | user.s7_1{autoCommit=true}(A0:bte):void; | 1 | 104 | 0 | 0 | 0 | | 45@0 | monetdb | 2016-05-03 00:06:50.000000 | select 1; | default_pipe | user.s7_1 | 21 | 273 | 2016-05-03 00:06:50.000000 | 2016-05-03 00:06:50.000000 | user.s7_1{autoCommit=true}(A0:bte):void; | 1 | 66 | 1 | 0 | 0 | | 46@0 | monetdb | 2016-05-03 00:06:56.000000 | select * from sys.querylog_catalog; | default_pipe | user.s8_1 | 68 | 665 | 2016-05-03 00:06:56.000000 | 2016-05-03 00:06:56.000000 | user.s8_1{autoCommit=true}():void; | 8 | 886 | 0 | 50 | 0 | | 47@0 | monetdb | 2016-05-03 00:07:33.000000 | select * from sys.querylog_calls; | default_pipe | user.s9_1 | 73 | 282 | 2016-05-03 00:07:33.000000 | 2016-05-03 00:07:34.000000 | user.s9_1{autoCommit=true}():void; | 9 | 2515 | 0 | 0 | 0 | +------+---------+----------------------------+-------------------------------------+--------------+-----------+------+----------+----------------------------+----------------------------+------------------------------------------+--------+------+------+------+------+ 6 tuples (152.709ms) Hence, maybe the documentation should be updated to remove the former? Also, two questions remain unanswered by the current documentation: 1) What is the unit of "mal integer, -- size of MAL plan"? bytes? lines? statements? 2) What exactly does "optimize bigint -- time in microseconds" measure? Only the time taken by MAL optimizers (i.e., after SQL parsing, relational optimization and relational-to-MAL translation, but before actual execution)? If so, is there still a way to measure the time(s) for SQL parsing, relational optimization, and (or) relational-to-MAL translation? Thanks! Stefan -- | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |

1 0

Help with json.filters
by Alberto Ferrari 03 May '16

03 May '16

Can anyone help with json.filters? I have this example table ... CREATE TABLE tblJson ( "id" INT, "json_data" JSON, ); ... with these values... INSERT INTO tblJson (id, json_data) VALUES (1, '{"data":{"time":"2015-10-09 10:36:35","others":"xxx"}}'), (2, '{"data":{"time":"2015-10-09 12:18:20","others":"yyy"}}'), (3, '{"data":{"time":"2015-12-30 16:31:22","others":"zzz"}}'); The data is ok, I can select the rows, it's ok. Also I can select only the "time" field, like this... SELECT json.filter(json_data, 'data.time') FROM tblJson; But now I want to filter the content in "json_data" specific field, i.e. SELECT all the rows where "time like '2015-10-09%'" , or maybe "time = '2015-10-09 12:18:20'" I tried but can't do it. Any help? Thanks!

3 5

Problems with aggregate UDF (subaggregate undefined error)
by Anderson, David B 02 May '16

02 May '16

All, I have been able to create and install my own aggregate UDF (weightedpercentile), but when I try to execute it via mclient I am getting the following error: sql>select key, weightedpercentile(a,b) from t group by key; TypeException:user.s9_1[20]:'udf.subweightedpercentile' undefined in: X_34:bat[:any,:dbl] := udf.subweightedpercentile(X_26:bat[:oid,:dbl],X_33:bat[:oid,:dbl],X_16:bat[:oid,:oid],r1_16:bat[:oid,:oid],true:bit); program contains errors Does anyone know what I am doing incorrectly? I am at a total loss now. The error is almost identical to the error I get if I try to execute the corr() aggregate: sql>select key, corr(a,b) from t group by key; TypeException:user.s10_1[20]:'aggr.subcorr' undefined in: X_34:bat[:any,:dbl] := aggr.subcorr(X_26:bat[:oid,:dbl],X_33:bat[:oid,:dbl],X_16:bat[:oid,:oid],r1_16:bat[:oid,:oid],true:bit); program contains errors The corr() error is referenced in a recent bug submission (which I can't find in Bugzilla) If I look in the system function catalog, I see: sql>select * from sys.functions where name like '%weight%'; +------+--------------------+---------------------------------------------------+------+------+------+-------+-------+-------+------+ | id | name | func | mod | lang | type | side_ | varre | varar | sche | : : : : : uage : : effec : s : g : ma_i : : : : : : : : t : : : d : +======+====================+===================================================+======+======+======+=======+=======+=======+======+ | 9313 | weightedpercentile | -- weighted percentile | udf | 1 | 3 | false | false | false | 2000 | : : : create aggregate weightedpercentile(e1 real, e2 r : : : : : : : : : : : eal) returns real : : : : : : : : : : : external name udf.weightedpercentile; : : : : : : : : | 9318 | weightedpercentile | -- weighted percentile | udf | 1 | 3 | false | false | false | 2000 | : : : create aggregate weightedpercentile(e1 double, e2 : : : : : : : : : : : double) returns double : : : : : : : : : : : external name udf.weightedpercentile; : : : : : : : : +------+--------------------+---------------------------------------------------+------+------+------+-------+-------+-------+------+ 2 tuples (1.107ms) So my function appears to be installed. I have in (80_udf.sql): -- weighted percentile create aggregate weightedpercentile(e1 real, e2 real) returns real external name udf.weightedpercentile; -- weighted percentile create aggregate weightedpercentile(e1 double, e2 double) returns double external name udf.weightedpercentile; and in (udf.mal): # BAT MAL signatures module batudf; command weightedpercentile(one:bat[:oid,:bte],two:bat[:oid,:bte]):lng address UDFBATweightedpercentile comment "compute the weighted percentile (bte)" command weightedpercentile(one:bat[:oid,:int],two:bat[:oid,:int]):lng address UDFBATweightedpercentile comment "compute the weighted percentile (int)" command weightedpercentile(one:bat[:oid,:lng],two:bat[:oid,:lng]):lng address UDFBATweightedpercentile comment "compute the weighted percentile (lng)"

2 3

Problem make-install'ing MonetDB to $HOME/opt/monetdb
by Eyal Rozenberg 02 May '16

02 May '16

I'm trying to upgrade to the latest release of MonetDB (Jul2015-SP4) instead of an older one. I'm building and installing as a non-root user, into $HOME/opt/monetdb . Well, * ./bootstrap SUCCEEDED * ./configure --prefix=$HOME/opt/monetdb SUCCEEDED * ./make SUCCEEDED * ./make install FAILED Here's the error message: /usr/bin/install -c -m 644 mapi.h /home/eyalroz/opt/monetdb/include/monetdb/mapi.h /bin/sh ../../libtool --mode=install /usr/bin/install -c libmapi.la '/home/eyalroz/opt/monetdb/lib' libtool: install: error: cannot install `libmapi.la' to a directory not ending in /usr/local/lib I don't see why libtool is telling me that. What business is it of libtool what I want to call my directories? Anyway, it doesn't seem to me I tried to do something esoteric... how do you suggest I get around this? Eyal PS - It's a Fedora 22 machine in case that matters.

2 2

Explain analyze
by Shmagi Kavtaradze 02 May '16

02 May '16

In postgres I could use explain analyze to see actions performed by database and their cost. I was not able to run explain analyze. I ran EXPLAIN, but can not see any plan or cost. Any ideas how to get costs and query plan like in Postgres?

4 7

Crystal bindings for MonetDB
by Brian Hood 30 Apr '16

30 Apr '16

Hi Guys, I have got my Crystal Language bindings for MonetDB now working quite well. https://github.com/puppetpies/crystal-monetdb-libmapi I've seen options in the mapi code for a variable called mode that allow you format the response data in XML but it doesn't seem to be implemented fully in the mapi code. Are there any guide lines to processing this RAW query output. % threatmonitor.fruits, threatmonitor.fruits, threatmonitor.fruits, threatmonitor.fruits, threatmonitor.fruits # table_name % name, price, weight, comments, id # name % varchar, varchar, int, clob, int # type % 9, 4, 2, 11, 1 # length [ "Apple", "9.99", 50, NULL, 1 ] [ "Bananna", "3.99", 30, NULL, 2 ] [ "Orange", "7.99", 60, NULL, 3 ] [ "Peach", "5.00", 80, NULL, 4 ] [ "Kiwi", "9.00", 20, NULL, 5 ] [ "Tomato", "2.00", 20, "Yes a fruit", 6 ] [ "Pear", "4.00", 30, "Juicy", 7 ] [ "Nectarine", "6.00", 50, "Juicy", 8 ] Regards, Brian Hood

1 0

Case sensitive
by Ariel Abadi 30 Apr '16

30 Apr '16

Hi All! We are facing a problem, we used to work with mysql with the case insensitive. Now we have moved into MonetDB and we are facing a problem with this issue. Some of our queries are not bringing any data because of the sensitive.. SELECT * FROM Country = 'jamaica' ... 0 Records SELECT * FROM Country = 'Jamaica' ... 10 Records Some cases we solved it using the ILIKE, but we still have some code on our app that cannot be modified. Does anybody know if there is any flag or setup or config for the Database in Monet to make it insensitive ? Thanks in advance for the help!

6 6

Support for multi-column aggregate functions
by Anderson, David B 30 Apr '16

30 Apr '16

All, Does the current version of MonetDB support multi-column aggregate functions? I need to create a function which returns the "quantiles" of one column using another column as a weighting. It looks like PostgreSQL supports multi-column aggregates as of 8.2. I could write an embedded R function to compute this, but want to know if it is even possible to pass in multiple columns. Thanks, Dave

4 9