- users-list - monetdb.org

null value from python function
by Stefano Fioravanzo 05 Dec '16

05 Dec '16

Hello, Some months ago I reported a problem when returning null values from an embedded py function (may they be inside arrays or data frames). Indeed, the null values cannot be queried using sql like: SELECT * FROM t WHERE v IS NULL the above query does not return any result, but selecting the row mclient presents a ‘null’ values in the cell. I am using a development build of a couple of months ago, I would like to know if this issue has been resolved or not. Thank you Stefano

2 1

Database dropping connections frequently with proxying.
by Joseph D'silva 01 Dec '16

01 Dec '16

Running on Ubuntu 16.04 LTS, database and clients run on the same system. I am using the Dec2016 branch, and noticed that the database connections get dropped randomly, but in high frequency. This error occurs irrespective for whether I use mclient, python package (pymonetdb) or the JDBC driver from the site. >From what I could figure if I use the forward=redirect option instead of the default proxy, the frequency reduces drastically, but is still present (if I leave the mclient connection idle for like 5 minutes it will drop - is there an idle time out somewhere ? Also is there a way to force a network connection locally instead of unix socket ? ). Sample of some messages For the default proxy option =========================== >From merovingian.log 2016-12-01 10:59:33 MSG merovingian[29445]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying Python error due to dropped connection. >>> rows=cursor.execute("SELECT 'HELLO';"); Traceback (most recent call last): File "<stdin>", line 1, in <module> File "/usr/local/lib/python3.5/dist-packages/pymonetdb/sql/cursors.py", line 164, in execute block = self.connection.execute(query) File "/usr/local/lib/python3.5/dist-packages/pymonetdb/sql/connections.py", line 133, in execute return self.command('s' + query + ';') File "/usr/local/lib/python3.5/dist-packages/pymonetdb/sql/connections.py", line 138, in command return self.mapi.cmd(command) File "/usr/local/lib/python3.5/dist-packages/pymonetdb/mapi.py", line 208, in cmd self._putblock(operation) File "/usr/local/lib/python3.5/dist-packages/pymonetdb/mapi.py", line 310, in _putblock self._putblock_inet(block) File "/usr/local/lib/python3.5/dist-packages/pymonetdb/mapi.py", line 321, in _putblock_inet self.socket.send(flag) BrokenPipeError: [Errno 32] Broken pipe JDBC Error (I am using PyCharm to execute some SQLs) [08000] Connection to server lost! (mserver still alive?) On the other hand, mclient does not show any error message, tried -X option, but no message when it dies. It just dies while executing a command (probably because the connection is dropped) A peek at netstat shows that the connections disappear from it before mclient dies. $ netstat -anp | grep 50000 tcp 0 0 127.0.0.1:50000 0.0.0.0:* LISTEN 29445/monetdbd udp 0 0 127.0.0.1:50000 0.0.0.0:* 29445/monetdbd unix 2 [ ACC ] STREAM LISTENING 3869978213 <(386)%20997-8213> 29445/monetdbd /tmp/.s.monetdb.50000 unix 2 [ ACC ] STREAM LISTENING 3869978215 <(386)%20997-8215> 29445/monetdbd /tmp/.s.merovingian.50000 unix 3 [ ] STREAM CONNECTED 3875263615 29486/mserver5 /tmp/.s.monetdb.50000 unix 2 [ ] STREAM CONNECTED 3871676942 29486/mserver5 /tmp/.s.monetdb.50000 $ netstat -anp | grep 50000 tcp 0 0 127.0.0.1:50000 0.0.0.0:* LISTEN 29445/monetdbd udp 0 0 127.0.0.1:50000 0.0.0.0:* 29445/monetdbd unix 2 [ ACC ] STREAM LISTENING 3869978213 <(386)%20997-8213> 29445/monetdbd /tmp/.s.monetdb.50000 unix 2 [ ACC ] STREAM LISTENING 3869978215 <(386)%20997-8215> 29445/monetdbd /tmp/.s.merovingian.50000 unix 2 [ ] STREAM CONNECTED 3871676942 29486/mserver5 /tmp/.s.monetdb.50000 After switching to the forward option (where this issue is less frequent to be bothersome) =========================== >From merovingian.log 2016-12-01 11:22:59 MSG merovingian[29824]: redirecting client (local) for database 'voc' to mapi:monetdb://cerberus:50001/voc Although for most part the connections are stable, if and when mclient dies, I do notice that the netstat entries are NOT being removed. (see the entries piling up at the bottom though there are no active client processes) $ netstat -anp | grep 50000 (Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.) tcp 0 0 127.0.0.1:50000 0.0.0.0:* LISTEN 29824/monetdbd tcp 1 0 127.0.0.1:50000 127.0.0.1:35870 CLOSE_WAIT 29824/monetdbd udp 0 0 127.0.0.1:50000 0.0.0.0:* 29824/monetdbd unix 2 [ ACC ] STREAM LISTENING 3876967185 29824/monetdbd /tmp/.s.monetdb.50000 unix 2 [ ACC ] STREAM LISTENING 3876967187 29824/monetdbd /tmp/.s.merovingian.50000 unix 2 [ ] STREAM CONNECTED 3877116313 29824/monetdbd /tmp/.s.monetdb.50000 unix 2 [ ] STREAM CONNECTED 3880819958 29824/monetdbd /tmp/.s.monetdb.50000 unix 2 [ ] STREAM CONNECTED 3878012589 29824/monetdbd /tmp/.s.monetdb.50000 unix 2 [ ] STREAM CONNECTED 3877885158 29824/monetdbd /tmp/.s.monetdb.50000 Thanks, Joseph

1 0

Building monetdb with Python 3
by Joseph D'silva 01 Dec '16

01 Dec '16

Hi all, Has anyone had any luck building the sources with Python 3 ? I tried building the Dec2016 branch from git, passing --with-python3=/usr/bin/python3 --with-pyversion=/usr/bin/python3 --with-pyconfig=/usr/bin/python3-config to configure, but received many errors during make under the pyapi directory, such as. /usr/include/python3.5m/object.h:86:5: error: missing braces around initializer [-Werror=missing-braces] { _PyObject_EXTRA_INIT \ ^ connection.c:159:5: note: in expansion of macro ‘PyObject_HEAD_INIT’ PyObject_HEAD_INIT(NULL) ^ /usr/include/python3.5m/object.h:86:5: note: (near initialization for ‘Py_ConnectionType.ob_base’) { _PyObject_EXTRA_INIT \ ^ connection.c:159:5: note: in expansion of macro ‘PyObject_HEAD_INIT’ PyObject_HEAD_INIT(NULL) ^ connection.c:159:5: error: missing initializer for field ‘ob_size’ of ‘PyVarObject {aka struct <anonymous>}’ [-Werror=missing-field-initializers] ................. connection.c:246:5: error: ‘return’ with a value, in function returning void [-Werror] import_array(); ^ It looks like some of the macro definitions to make python3 header files work with the pyapi source might be missing. I tried fixing some of the sources like unicode.c, formatinput.c etc. But it looks like majority of the source files have issues. Thanks, Joseph

2 2

MonetDB and IN search
by blacky_2k2＠yahoo.com 29 Nov '16

29 Nov '16

Hello, We’ve been battling for some time with what is fundamentally a simple query. We have a databases with about 15 columns which store int and float values. The query searches in each column with an IN condition , where the possible test values in the IN condition may vary from a 1 to 200 - where these 200 conditions are all the possible values of that column. So normally a query would look something like: SELECT id FROM table WHERE a IN (1,2,3,4) AND b IN (4,5,6,) AND c IN (2,5,6) ...... AND i = 5 etc. The database has about 2 mil rows but will grow to potentially 10 mil rows. A query similar to the one above takes about 80-100 ms. With about 50 test values for each IN. So far, the best results we got with MariaDB and proper indexing using Engine Memory where the above query takes about 2ms. We suspect MonetDB is not optimised for such queries, but since we don’t know much about its workings can you please confirm this? Otherwise, is there a way to improve the above query? Say using multiple JOINs instead of IN ? By the way, we are experimenting with MonetDB out of curiosity, we heard a lot of good things about it. Thank you very much, Silviu

3 4

Improved concurrency version of MonetDB
by Puthick Hok 24 Nov '16

24 Nov '16

Hi, I'm using MonetDB v11.23.13 (Jun2016-SP2). We decided to use MonetDB to store DNA marker data for our plant breeding platform (www.kddart.org) 2 years ago based on MonetDB performance on data retrieval and data update is also good. The use of MonetDB is not purely OLAP. There are lot of aspects of OLTP because people need to load marker data from their genotyping services. We finish integrating MonetDB into the platform and developing more tools for the system. As the platform is getting more mature, we started to do concurrency testing against the system including the part that is relying on MonetDB. Naively, I ran into a concurrency problem described in a master thesis by Rene Tassy (Improving Transactional Scalability in MonetDB). The work done in the thesis is very promising and I would like to thank you all for that. My question in relation to how I would deal with my problem is that what is the time frame for the improved MVCC Optimistic Concurrency Strategy in the MonetDB release? Regards, Puthick

2 1

Functions in MOnetdb
by Ariel Abadi 22 Nov '16

22 Nov '16

Hi! We are trying to create a couple of functions in Monet. Every function we create has a terrible bad performance. Can someone help me to find out what Im doing wrong ? Here is the function CREATE FUNCTION sb_glob.camelcase(input VARCHAR(500)) RETURNS VARCHAR(500) begin DECLARE len INT; DECLARE i INT; SET len = CHAR_LENGTH(input); SET input = LOWER(input); SET i = 0; WHILE (i < len) DO SET input = CASE WHEN ((SUBSTR(input,i,1) = ' ' OR SUBSTR(input,i,1) = '-' OR i = 0) AND (i < len)) THEN (LEFT(input,i) || UPPER(SUBSTR(input,i + 1,1)) || RIGHT(input,len - i - 1)) ELSE input END; SET i = i + 1; END WHILE; RETURN input; END Thks in advance!

2 1

filter based on json value of key/value pair
by Lynn Carol Johnson 21 Nov '16

21 Nov '16

Hello all - I'm having trouble pulling rows based on the value of a json key/value pair. I have created a table with a json object that looks like this: sql>\d testjson CREATE TABLE "sys"."testjson" ( "chr" INTEGER, "pos" INTEGER, "value" JSON ); sql> I have entries in the table that look as below: sql>select * from testjson; +------+-----------+----------------------------------------------------------------+ | chr | pos | value | +======+===========+================================================================+ | 1 | 209890809 | {"df":1,"pval":4.556021744872574E-6,"r2":0.0995066009518681} | | 1 | 789383847 | {"df":1,"pval":2.50962115178055E-6,"r2":0.10462833261259474} | | 2 | 127893782 | {"df":1,"pval":4.2825829011938765E-6,"r2":0.10003907080878045} | | 2 | 234533212 | {"df":2,"pval":4.556021744872574E-6,"r2":0.0995066009518681} | +------+-----------+----------------------------------------------------------------+ 4 tuples (1.027ms) sql> I would like to filter on json values. Not the json key, the json key's value. I can pull json values based on a non-json field, e.g. Grab the pval where chr>1 as below: sql>select json.filter(value,'r2') as r2Value from testjson where chr=1; +-----------------------+ | r2value | +=======================+ | [0.0995066009518681] | | [0.10462833261259474] | +-----------------------+ 2 tuples (1.179ms) sql> But I want to pull chr/pos where pval < some amount, or df > some number. For example, how would I write the query to select all rows where df > 1? This query should return the last row of the table above. I've tried the following: sql>select * from testjson where (json.filter(value,'df') > 1); types json(0,0) and tinyint(8,0) are not equal sql> Thanks for your help - Lynn

2 2

how to structure queries like SELECT a.col IN ( b.col FROM b ) FROM a ?
by Anthony Damico 21 Nov '16

21 Nov '16

hi monetdb team, i've pasted a minimal reproducible example of a few SQL queries that i had expected to work -- only about half of them do work, and the other half just error with 'ParseException:SQLparser:' my goal is to get the very last line working, so if anybody could suggest a hack or a workaround that doesn't involve a JOIN, i would appreciate it. thanks all!! CREATE TABLE table_one (this_column INTEGER) ; INSERT INTO table_one VALUES (1) ; INSERT INTO table_one VALUES (2) ; INSERT INTO table_one VALUES (3) ; INSERT INTO table_one VALUES (4) ; INSERT INTO table_one VALUES (5) ; CREATE TABLE table_two (this_column INTEGER) ; INSERT INTO table_two VALUES (1) ; INSERT INTO table_two VALUES (2) ; # works as expected SELECT SUM( ( table_one.this_column IN ( SELECT this_column FROM table_two ) ) ) FROM table_one ; SELECT MAX( ( table_one.this_column IN ( SELECT this_column FROM table_two ) ) ) FROM table_one ; # fails SELECT ( table_one.this_column IN ( SELECT this_column FROM table_two ) ) AS new_column FROM table_one ; # Server says 'ParseException:SQLparser:'. # works SELECT AVG( CASE WHEN table_one.this_column IN ( SELECT this_column FROM table_two ) THEN 1 ELSE 0 END ) AS new_column FROM table_one ; # fails SELECT ( CASE WHEN ( table_one.this_column IN ( SELECT this_column FROM table_two ) ) THEN 1 ELSE 0 END ) AS new_column FROM table_one ; # Server says 'ParseException:SQLparser:'. # fails [[this query is the one i would like to implement]] SELECT COUNT(*) , ( CASE WHEN ( table_one.this_column IN ( SELECT this_column FROM table_two ) ) THEN 1 ELSE 0 END ) AS new_column FROM table_one GROUP BY new_column ; # Server says 'ParseException:SQLparser:'.

3 8

Unable to install//access external R Packages inside of MonetDB
by Cas Saternos 10 Nov '16

10 Nov '16

Hello All, Just wanted to report a bug, or at least unexpected behavior. I am new to monetdb, and could not submit this to your bugzilla tracker. *Background* I successfully have used MonetDB with Docker ( https://www.monetdb.org/blog/docker-container-monetdb-r) and was able to create FUNCTIONs and AGGREGATEs with R (as described here: https://www.monetdb.org/content/embedded-r-monetdb) This blog post also states: "As a final note on usage, you can use any R package from CRAN within your function without explicitly installing it. We overloaded the library function in R to automatically install missing packages." *Description* However, I am not able to use external R packages available from CRAN. The session below describes both Docker commands and SQL used. This session was run on OSX using Docker Machine. Thank you, -Cas *bash-3.2$* docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES d6b944445237 monetdb/monetdb-r-docker:latest "/usr/bin/supervisord" 13 minutes ago Up 13 minutes 0.0.0.0:32768->50000/tcp monetdb-r-docker *bash-3.2$* docker exec -it monetdb-r-docker mclient db user:monetdb password: Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP4) Database: MonetDB v11.21.19 (Jul2015-SP4), 'mapi:monetdb://d6b944445237: 50000/db' Type \q to quit, \? for a list of available commands auto commit mode: on # Create and use a function to show that MonetDB with basic R expressions works: *sql>*CREATE FUNCTION test_add() RETURNS DOUBLE LANGUAGE R { 1 + 1 }; operation successful (1.254ms) *sql>*SELECT test_add(); +--------------------------+ | test_add | +==========================+ | 2 | +--------------------------+ # Add only the library function call to the function just shown (and give the function a new name). The function is created successfully, but an error occurs when the function is invoked in a query: *sql>*CREATE FUNCTION test_library() RETURNS DOUBLE LANGUAGE R { library("ggplot2"); 1 + 1 }; operation successful (1.378ms) *sql>*SELECT test_library(); Error running R expression. Error message: Error in .library.original(package, help, pos, lib.loc, character.only = T, : there is no package called 'ggplot2' Calls: as.data.frame -> <Anonymous> -> library -> .library.original *sql>*

1 0

NULL in COPY INTO FROM bulk
by Mathieu Raillard 10 Nov '16

10 Nov '16

Hi all, We are working with monetdb since 1 year and half, the performance are great. We are using in production the July 2015 release and we want to upgrade to the last release. We installed the new version and ran some tests to ensure the compatibility, unfortunatly we discovered a change in the way monetdb is handling NULL in bulk import. We are exporting datas from Mysql and by default we get a \N for every NULL value plus every field are enclosed by "" ( even number and timestamp). Exemple : 1,test,2016-01-01 00:00:00,null in database become "1","test","2016-01-01 00:00:00',"\N" in bulk. This bulk is working fine with the July 2015 realease but we cant import "\N" in number and timestamp with the june 2016 realase Have you an idea how can we make it works ? Here is some results from my tests On the July 2015 release : create schema test; create table test.test ( field1 int, field2 timestamp, field3 varchar(20) ); Bulk1 : "1","2016-01-01 00:00:00","test" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk1' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; 1 affected row (52.812ms) Bulk2 : "1","2016-01-01 00:00:00","\N" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk2' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; 1 affected row (47.852ms) Bulk3 : "1","\N","test" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk3' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; 1 affected row (58.811ms) Bulk4 : "\N","2016-01-01 00:00:00","test" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk4' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; 1 affected row (32.812ms) Bulk5 : "\N","\N","\N" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk5' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; 1 affected row (63.785ms) select * from test.test field1 field2 field3 1 2016-01-01 00:00:00.0 test 1 2016-01-01 00:00:00.0 <null> 1 <null> test <null> 2016-01-01 00:00:00.0 test <null> <null> <null> Tested on the Jun 2016 release : create schema test; create table test.test ( field1 int, field2 timestamp, field3 varchar(20) ); Bulk1 : "1","2016-01-01 00:00:00","test" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk1' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; 1 affected row (61.812ms) Bulk2 : "1","2016-01-01 00:00:00","\N" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk2' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; 1 affected row (47.314ms) Bulk3 : "1","\N","test" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk3' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; Failed to import table line 1 field 2 'timestamp(7)' expected in '\N' Bulk4 : "\N","2016-01-01 00:00:00","test" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk4' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; Failed to import table line 1 field 1 'int' expected in '\N' Bulk5 : "\N","\N","\N" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk5' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; Failed to import table line 1 field 1 'int' expected in '\N' select * from test.test +--------+----------------------------+--------+ | field1 | field2 | field3 | +========+============================+========+ | 1 | 2016-01-01 00:00:00.000000 | test | | 1 | 2016-01-01 00:00:00.000000 | N | +--------+----------------------------+--------+ Regards Mathieu

1 0