November 2016 - users-list

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