- users-list - monetdb.org

issue with IS NULL
by Lynn Carol Johnson 23 May '16

23 May '16

Good morning - I am having an issue identifying columns with a null value in the July2015-SP3 monetdb version. A sample of my table with null values is below: sql>select * from kelly limit 10; +------+-------+------------+-------------+-------------+-------------+-------------+ | chr | pos | bkn22vsbkn | bkn22vsbkn3 | bkn22vsbkn4 | bkn22vstemp | bkn22vstrop |> : : : 16_weir_an : 5_weir_and_ : 0_weir_and_ : erate_cinta : ical_cinta_ :> : : : d_cockerha : cockerham_f : cockerham_f : _weir_and_c : weir_and_co :> : : : m_fst : st : st : ockerham_fs : ckerham_fst :> : : : : : : t : :> +======+=======+============+=============+=============+=============+=============+ | 1 | 10004 | null | null | null | null | null | | 1 | 10045 | 0.050576 | 0 | 0.154324 | 0.130628 | 0.0398957 | | 1 | 10077 | null | null | null | null | null | | 1 | 10084 | null | null | null | null | null | | 1 | 10097 | null | null | 0.0823631 | 0 | null | | 1 | 10112 | null | null | null | null | null | | 1 | 10128 | 0.00185251 | 0 | 0 | 0.00272515 | 0.00594128 | | 1 | 10162 | null | null | null | null | null | | 1 | 10270 | null | null | null | null | null | | 1 | 10299 | null | null | null | null | null | +------+-------+------------+-------------+-------------+-------------+-------------+ 10 tuples (18.9s) !144 columns dropped! note: to disable dropping columns and/or truncating fields use \w-1 sql> Verify the number of values in the table: sql>select count(1) from kelly; +----------+ | L1 | +==========+ | 83153144 | +----------+ 1 tuple (479.905ms) sql> I would like to pull all values that are NOT null from a specific column. My understanding is that running a “count” on a column should return the count of the non-null values. When I run a count on bkn22vsbkn40_weir_and_cockerham_fst I get the following result, which is the same value as the total number of entries above: sql>select count(bkn22vsbkn40_weir_and_cockerham_fst) from kelly; +----------+ | L1 | +==========+ | 83153144 | +----------+ 1 tuple (860.533ms) sql> The number returned is the same as the total number of entries, but it should be less. However, if I run a query to count the values that are NOT NULL, it counts them: sql>select count(1) from kelly where bkn22vsbkn40_weir_and_cockerham_fst IS NOT NULL; +----------+ | L1 | +==========+ | 41754484 | +----------+ 1 tuple (1.0s) sql> When I try to select column values based on my selected column having a null value, no results are returned. sql>select * from kelly where bkn22vsbkn40_weir_and_cockerham_fst IS NULL limit 10; +-----+-----+-------------------------------------+-------------------------------------+-------------------------------------+ | chr | pos | bkn22vsbkn16_weir_and_cockerham_fst | bkn22vsbkn35_weir_and_cockerham_fst | bkn22vsbkn40_weir_and_cockerham_fst |> +=====+=====+=====================================+=====================================+=====================================+ +-----+-----+-------------------------------------+-------------------------------------+-------------------------------------+ 0 tuples (1.1s) !146 columns dropped! sql> When I select column values based on my selected column value NOT null, I get values. sql>select * from kelly where bkn22vsbkn40_weir_and_cockerham_fst IS NOT NULL limit 10; +------+-------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+--------------+--------------+ | chr | pos | bkn22vsbkn1 | bkn22vsbkn3 | bkn22vsbkn4 | bkn22vstemp | bkn22vstrop | bkn33vsbkn1 | bkn33vsbkn2 | bkn33vsbkn35 | bkn33vsbkn40 |> : : : 6_weir_and_ : 5_weir_and_ : 0_weir_and_ : erate_cinta : ical_cinta_ : 6_weir_and_ : 2_weir_and_ : _weir_and_co : _weir_and_co :> : : : cockerham_f : cockerham_f : cockerham_f : _weir_and_c : weir_and_co : cockerham_f : cockerham_f : ckerham_fst : ckerham_fst :> : : : st : st : st : ockerham_fs : ckerham_fst : st : st : : :> : : : : : : t : : : : : :> +======+=======+=============+=============+=============+=============+=============+=============+=============+==============+==============+ | 1 | 10045 | 0.050576 | 0 | 0.154324 | 0.130628 | 0.0398957 | 0.0289548 | 0 | 0 | 0.124971 | | 1 | 10097 | null | null | 0.0823631 | 0 | null | 0.0406325 | 0.0226116 | 0.0226116 | 0 | | 1 | 10128 | 0.00185251 | 0 | 0 | 0.00272515 | 0.00594128 | null | 0 | null | null | | 1 | 10707 | 0.380632 | 0 | 0 | 0 | 0 | 0.44799 | 0.0206532 | 0.0111431 | 0.0234156 | | 1 | 10751 | 0.0462002 | 0.0439648 | 0.0084101 | 0.0505068 | 0 | 0.0475206 | 0 | 0.0453142 | 0.00522015 | | 1 | 10755 | 0.00147929 | 0 | 0.00290363 | 0.00560938 | 0 | null | 0 | 0 | null | | 1 | 10760 | 0 | 0.011568 | 0 | 0 | 0.0882033 | 0.00590868 | 0 | 0 | 0 | | 1 | 10814 | null | null | 0 | null | null | null | null | null | 0 | | 1 | 10838 | 0.133482 | 0 | 0 | 0.00616907 | 0.146222 | 0.0587818 | 0 | 0 | 0.0621118 | | 1 | 10839 | 0.133482 | 0 | 0 | 0.00616907 | 0.146222 | 0.0639509 | 0 | 0 | 0.0558049 | +------+-------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+--------------+--------------+ 10 tuples (2m 36s) !140 columns dropped! sql> My question: AM I doing something wrong, or is this a bug? I am running the july2015 sp3: [lcj34@cbsudc01 monetdbFiles]$ monetdb -v MonetDB Database Server Toolkit v1.1 (Jul2015-SP3) [lcj34@cbsudc01 monetdbFiles]$ Thanks - Lynn

2 3

Column level 3 problems
by wilco draijer 23 May '16

23 May '16

Hello, When I try to convert my monetdb sql database to rdf using d2rq I get the following error:Problem accessing /directory/genelocation_genelocation. Reason: TODO: column names of level >= 3:SELECT genelocation"."genelocation"."entrez_id" F 50 (E0) This has to do with the bug described in https://www.monetdb.org/bugzilla/show_bug.cgi?id=2526 Is there a way to get around this? Best regards, Wilco

2 1

BLOB
by Shmagi Kavtaradze 22 May '16

22 May '16

Hello community, currently I am using HUGEINT to store data and using UDF I perform bitwise AND on them. I want to store my data in BLOB and perform same actions, but in data mapping table https://www.monetdb.org/wiki/MonetDB_type_system, I was not able to find BLOB mapping for C and MAL. So: 1) What are C and MAL mappings for UDF, if they exist? 2) Is it possible to perform Bitwise AND on blob?

1 0

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