May 2016 - users-list

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