November 2016 - users-list

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

COPY subquery INTO file_name - INCLUDE field name
by Ariel Abadi 10 Nov '16

10 Nov '16

Hi! We are using the COPY *subquery* INTO *file_name *[ [USING] DELIMITERS *field_separator* [',' *record_separator* [ ',' *string_quot*e ]]] [ NULL AS *null_string* ] BUT we need to have in the file the name of the fields on the first row. Im looking on the documentation, but I cannot find anything related to this, can someone help me with this? Thks in advance!

3 3

Fwd: Performance tuning
by Rajendra Bhat 09 Nov '16

09 Nov '16

Hi team, I am running monetdb on 16 core processor and 30gb ram. I have table around 54 fields. gzip compressed csv size is around 6.5 GB. query will take around 6 min. please suggest tuning options. query: Select count(distinct userid),sum(mrp)/sum(noOfItems),sum(mrp)/sum( orderCount),sum(revenue),sum(orderCount),sumnoOfItems),sum( returnCount),sum(rtoCount),sum(0),sum(0),sum(tDiscountPer),sum(cDiscountPer) from historical where gender='men' and brand='puma' Thanks and Regards Rajendra Bhat -- Thanks and Regards Rajendra Bhat

2 3

How do I force start a new monetdbd instance?
by Anderson, David B 09 Nov '16

09 Nov '16

Or what files do I need to delete from the file system? When I try to start a new monetdbd instance, I am getting the error: another monetdbd is already running but there are no monetdb processes running. . .

2 1

TimeZone conversion functions
by Ariel Abadi 03 Nov '16

03 Nov '16

Hi! Can anyone help me understand how or where I can check the included functions on MonetDB to convert timestamp from one timezone to another? Thanks in advance! Rgds Ariel

2 2

MAL optimizer result inspection
by Moritz Bruder 02 Nov '16

02 Nov '16

Hi, is it possible to look at the output of a specific optimizer? I tried using *optimizer.showFlowGraph*, which just hangs, and *optimizer.showPlan*, which does not show some variables. Is there maybe something I missed? Regards, Moritz

2 1