September 2013 - users-list

Re: Contents of users-list Digest, Vol 13, Issue 20
by Edgar Mejia 25 Sep '13

25 Sep '13

1. I have MonetDB Database Server Toolkit v1.1 (Feb2013-SP3) (i install so: sudo apt-get install monetdb5-sql monetdb-client) shell> monetdbd create /path/to/mydbfarm shell> monetdbd start /path/to/mydbfarm shell> monetdb create voc shell> monetdb release voc 2. Ubuntu 12.04.3 LTS 3. Schema and data: Schema: CREATE TABLE timedimension ( id integer NOT NULL, yeart int DEFAULT NULL, quarter varchar(2) DEFAULT NULL, weekyear int DEFAULT NULL, dayt int DEFAULT NULL, namemonth varchar(10) DEFAULT NULL, nameday varchar(20) DEFAULT NULL, montht int DEFAULT NULL, day_of_week varchar(15) DEFAULT NULL, date date DEFAULT NULL, month2 char(2) DEFAULT NULL ) CREATE TABLE bi_con_cdr_diario ( ano INT , facmesfac VARCHAR(6) , comcodigo INT , succodigo VARCHAR(3) , procodigo INT , pronombre VARCHAR(20) , prycodigo VARCHAR(20) , fecllamada timestamp , clicodigo INT , clinombre VARCHAR(80) , vencodigo INT , vennombre VARCHAR(50) , sercodigo VARCHAR(3) , pryservicio VARCHAR(4) , sertipo VARCHAR(3) , serclase VARCHAR(4) , trdtipotelefono CHAR(10) , cfacodigo INT , cfanombre VARCHAR(30) , trddurllamada INT , minutosreales DOUBLE , minutosenteros INT , minutosfacturados DOUBLE , costo DOUBLE , costohit DOUBLE , valorbruto DOUBLE , valorneto DOUBLE , trdvalorlocalneto DOUBLE , trdvalorlocalimpu DOUBLE , valorlocal DOUBLE , codpaisori INT , nompaisori VARCHAR(80) , codpaisdes INT , nompaisdes VARCHAR(80) , comnombre2 VARCHAR(15) , linprnombre VARCHAR(40) , trdcodciuori BIGINT , trdnomciuori VARCHAR(80) , sernombre VARCHAR(50) , idtimedimension integer , linprcodigo INT , trdcodciudes INT , trdnomciudes VARCHAR(80) ); Timedimension data:20080101,2008,"1",1,1,"Ene","",1,"Martes","2008-01-01","01" bi_con_cdr_diario data:2013,"092013",4,"CTA",4,"EMBRATEL","HITBRAEMB","2013-09-04 00:00:00",3608,"Air Liquide do Brasil Ltda - Canoas",420,"Flavio Eugenio/Caroline Carneiro","E-N","E-N","ddd","PSTN","Fijo",89,"Consumo",459,7.65,10,10,0.66924,0.66924,1.43199992,1.1456000328063967,3.437,0.471,3.9080000000000004,55,"Brasil",55,"Brazil","HitBrasil","HIT VOICE",0,"0","HIT CALLSERVICE EMB NAL",20130904,1,0,"0",\N But the error is when i do group by as (select sum(minutosfacturados),succodigo,montht,comnombre2 from bi_con_cdr_diario as cdr inner join timedimension as td on cdr.idtimedimension=td.id where comcodigo=4 group by succodigo,comnombre2,montht;) and have 10.000.000 of rows. I appreciate very much your help. I believe this is one great product. Best Regards, Edgar Mejia

1 0

Re: Contents of users-list Digest, Vol 13, Issue 20
by Edgar Mejia 25 Sep '13

25 Sep '13

Thank you Jennie Zhang. 1. I have MonetDB Database Server Toolkit v1.1 (Feb2013-SP3) (i install so: sudo apt-get install monetdb5-sql monetdb-client) shell> monetdbd create /path/to/mydbfarm shell> monetdbd start /path/to/mydbfarm shell> monetdb create voc shell> monetdb release voc 2. Ubuntu 12.04.3 LTS 3. Schema view schemadatamonetdb.sql file 4. Data: timedimension view datatimediM.csv bi_con_cdr_diario: cdr.csv But the error is when i do group by as (select sum(minutosfacturados),succodigo,montht,comnombre2 from bi_con_cdr_diario as cdr inner join timedimension as td on cdr.idtimedimension=td.id where comcodigo=4 group by succodigo,comnombre2,montht;) and have 10.000.000 of rows. I appreciate very much your help. I believe this is one great product. Best Regards, Edgar Mejia

1 0

Monetdb crashed as group by
by Edgar Mejia 24 Sep '13

24 Sep '13

select sum(minutosfacturados),succodigo,montht,comnombre2 from bi_con_cdr_diario as cdr inner join timedimension as td on cdr.idtimedimension=td.id where comcodigo=4 group by succodigo,comnombre2,montht; but for example as this query work fine: select sum(minutosfacturados),succodigo,montht from bi_con_cdr_diario as cdr inner join timedimension as td on cdr.idtimedimension=td.id where comcodigo=4 group by succodigo,montht; What i need to do for to fix this? Thanks Edgar M

3 2

Whats coming in upcoming build monetdb release
by Ashish Kumar Singh 21 Sep '13

21 Sep '13

Hi , During my periodic search for "Is a new monetdb build is going to be released soon" , I found one is placed under testing recently. Is there a place where it can be seen and reviewed what kind of features/fixes are expected in upcoming release? That will be a great help for some of the folks like me to plan ahead! Regards, Ashish

1 0

Re: users-list Digest, Vol 13, Issue 16
by Rose Beck 21 Sep '13

21 Sep '13

Thanks a lot for replying :) But its really unclear to me as to how can I load RDF data into monetdb. As I have a 1000 property values and I can not manually create table for each of the property values. TSQL allows something like SELECT * INTO someTable FROM sourceTable. RDF query engines like RDF3X, BitMat (Medha Atre, et.al.) have all employed monetdb. So, if there is some way out can you please help me with it. On Sat, Sep 21, 2013 at 12:00 PM, <users-list-request(a)monetdb.org> wrote: > Send users-list mailing list submissions to > users-list(a)monetdb.org > > To subscribe or unsubscribe via the World Wide Web, visit > http://mail.monetdb.org/mailman/listinfo/users-list > or, via email, send a message with subject or body 'help' to > users-list-request(a)monetdb.org > > You can reach the person managing the list at > users-list-owner(a)monetdb.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of users-list digest..." > > > Today's Topics: > > 1. Re: How to automate creation of property tables in monetdb? > (Rose Beck) > 2. Re: How to automate creation of property tables in monetdb? > (Martin Kersten) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Sat, 21 Sep 2013 11:49:10 +0530 > From: Rose Beck <rosebeck.82(a)gmail.com> > To: users-list(a)monetdb.org > Subject: Re: How to automate creation of property tables in monetdb? > Message-ID: > <CAPx9ze9DR0bmy9joSZewwrSL_UNQGP=HefZLHax= > WZAAfDK3Cw(a)mail.gmail.com> > Content-Type: text/plain; charset="iso-8859-1" > > A gentle reminder. Can someone please help me with this? > > > > > On Fri, Sep 20, 2013 at 7:58 PM, Rose Beck <rosebeck.82(a)gmail.com> wrote: > > > I am trying to load data (from myfile, the format of myfile is explained > > below) into monetdb using itsSQL interface. mytable format: > > > > 1 2 3 > > 5 7 8 > > 8 2 3..... > > > > The commands which I am using for performing the same are given below: > > > > sql> create table mytable(subject integer, property integer, object > integer); > > > > sql> copy 10000000 records into mytable from myfile delimiter ' ','\n'; > > > > For each property value (i.e. middle column) I create a separate table: > > > > create table prop1(subject integer, object integer); > > create table prop2(subject integer, object integer); > > create table prop3(subject integer, object integer); ... > > > > and then fill these tables using the following commands: > > > > INSERT into prop1 SELECT subject, object FROM triples_table where > property=prop1 ORDER BY subject, object; > > > > Is there some way to automate this process. i.e. right now I am manually > > creating a separate table for all the distinct properties (using create > > table prop1(subject integer, object integer); ) and then filling this > > table using insert command (INSERT into prop1 SELECT subject, object FROM > > triples_table where property=prop1 ORDER BY subject, object; ). Is there > > some way by which I may automate this procedure. > > > > One of the methods which I know of is to create a C++/python program and > > then call all functions like "create table" by using shell script. But is > > there a faster method to achieve the same. > > > > Also can you please guide as to how can I create and integrate dictionary > > (RDF: id to string mapping) to sql queries? > > <users-list(a)monetdb.org> > > >

2 2

How to automate creation of property tables in monetdb?
by Rose Beck 21 Sep '13

21 Sep '13

I am trying to load data (from myfile, the format of myfile is explained below) into monetdb using itsSQL interface. mytable format: 1 2 3 5 7 8 8 2 3..... The commands which I am using for performing the same are given below: sql> create table mytable(subject integer, property integer, object integer); sql> copy 10000000 records into mytable from myfile delimiter ' ','\n'; For each property value (i.e. middle column) I create a separate table: create table prop1(subject integer, object integer); create table prop2(subject integer, object integer); create table prop3(subject integer, object integer); ... and then fill these tables using the following commands: INSERT into prop1 SELECT subject, object FROM triples_table where property=prop1 ORDER BY subject, object; Is there some way to automate this process. i.e. right now I am manually creating a separate table for all the distinct properties (using create table prop1(subject integer, object integer); ) and then filling this table using insert command (INSERT into prop1 SELECT subject, object FROM triples_table where property=prop1 ORDER BY subject, object; ). Is there some way by which I may automate this procedure. One of the methods which I know of is to create a C++/python program and then call all functions like "create table" by using shell script. But is there a faster method to achieve the same. Also can you please guide as to how can I create and integrate dictionary (RDF: id to string mapping) to sql queries? <users-list(a)monetdb.org>

2 2

Shouldn't multiple identical bind calls be impossible?
by Roberto Cornacchia 19 Sep '13

19 Sep '13

I'd like to ask a simple question before reporting what I suspect to be a bug. On a Feb2013, using default_pipe (same holds for no_mitosis and sequential pipes), no matter what the query and the data schema look like (really, that shouldn't matter here), is it for any reason possible to have a MAL plan with repeated, identical bind operations? Like this: function user.s4_1():void; ... X_40 := sql.bind(X_1,"spinque","dict","idstr",0); ... X_126 := sql.bind(X_1,"spinque","dict","idstr",0); ... X_298 := sql.bind(X_1,"spinque","dict","idstr",0); .. X_384 := sql.bind(X_1,"spinque","dict","idstr",0); ... ... and so on My understanding is: with commonTerms optimizer enabled (I'm just using the standard pipes), there should be no way to see those calls repeated: - they have no side-effects - they do not depend on any user data Why isn't the first bind reused in the rest of the plan? Of course my worries are not much about those calls per se, which are super fast, but about my suspicion that entire subqueries built on top of such binds are replicated unnecessarily. I don't want to focus on the actual query here, because if I am correct (please correct me otherwise), the situation above is already enough to submit a more complete bug report. By the way, another suspicious instruction that I see in my MAL plan is: X_25 := X_24; Shouldn't this be removed by the aliases optimizer? Best, Roberto

3 4

overflow in calculation
by Hassan, Ahmad 19 Sep '13

19 Sep '13

Hi, I am trying latest MonetDB-11.15.11 release on Ubuntu 64bit with TPCH dataset of size 120GB (scale factor 100). I am testing on a system with 512Gb RAM. Out of 22 TPCH queries, query #1 always fail and thrown the following error: overflow in calculation. Command exited with non-zero status 1 113.04 Rest of the queries 2-22 runs fine for 120GB data. If I try with 200GB of data then more queries fail. Any suggestions to work around this problem please? Thanks. Kind Regards, Ahmad

2 1

tomograph not working with remote function?
by Mengmeng Chen 18 Sep '13

18 Sep '13

Hi, We have a MAL function and we are trying to use tomograph to profile the execution of it when it is executed on a remote node. However, the tomograph fails right after the function is executed. We are just wondering if we did something wrong and if tomograph can work with a remote function call. Here's what we want to do: >From node A, we run a MAL function, which will execute a MAL function on both node A and node B using remote.exec() method. Here's what we did and what happened: 1. in A start mclient 2. in A and B, start tomograph "tomograph -d db1 -h localhost" 3. in A, start the driver function, which will call remote.exec for the MAL function on both A and B Then, in A and B, tomograpg exits promptly ubuntu@MC-MoDB1:~->tomograph -d db1 -h localhost -- opened UDP profile stream MC-MoDB1:50010 for localhost Created tomogram 'tomograph' Run: 'gnuplot tomograph.gpl' to create the 'tomograph.pdf' file The memory map is stored in 'tomograph.dat' The trace is saved in 'tomograph.trace' for use with --trace option Looking at the tomograph trace file generated on node B, we have something like: 9 1430 18111 17039 71 71 2 0 0 remote.connect "X_22 := remote.connect(\"mapi:monetdb:// 192.168.4.4:50000/db1\ <http://192.168.4.4:50000/db1%5C>",\"monetdb\",\"monetdb\",\"msql\");", ] 11 1007 10501 9496 70 71 2 0 0 remote.connect "X_24 := remote.connect(\"mapi:monetdb:// 192.168.5.4:50000/db1\ <http://192.168.5.4:50000/db1%5C>",\"monetdb\",\"monetdb\",\"msql\");", ] 69 23408 23605 198 72 72 2 0 0 remote.bintype "remote.bintype();", ] 11 10867 10867 0 71 71 5 0 0 profiler.wait 0.00 0.04 0.75 0.00 0.00 0.00 0.00 0.17 0.17 0.17 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.01 0.00 0.00 0.00 0.00 0.00 0.00 0.25 0.01 0.25 0.00 0.00 0.00 0.00 0.00 9 18497 18497 0 71 71 5 0 0 profiler.wait 0.00 0.04 0.75 0.00 0.00 0.00 0.00 0.17 0.17 0.17 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.01 0.00 0.00 0.00 0.00 0.00 0.00 0.25 0.01 0.25 0.00 0.00 0.00 0.00 0.00 69 23978 23978 0 72 72 4 0 0 profiler.ping 0.00 0.04 0.75 0.00 0.00 0.00 0.00 0.17 0.17 0.17 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.01 0.00 0.00 0.00 0.00 0.00 0.00 0.25 0.01 0.25 0.00 0.00 0.00 0.00 0.00 ================================ on node A, the tomograph trace file looks like: 4 327 453 127 66 66 2 0 0 remote.bintype "remote.bintype();", ] 4 684 684 0 66 66 4 0 0 profiler.ping 0.15 0.15 0.15 0.15 0.15 0.15 0.15 0.15 0.15 0.17 0.15 0.15 0.15 0.15 0.15 0.17 0.15 0.25 0.15 0.15 0.15 0.15 0.15 0.15 0.15 0.20 0.17 0.15 0.15 0.15 0.15 0.15 ================================ Any help would be appreciated! Thanks, Mengmeng

2 1

Re: Need information about culstering monetdb
by Hannes Mühleisen 16 Sep '13

16 Sep '13

Hello Luis, first, please direct such general questions to the mailing list. On 09/04/2013 01:01 AM, Luis Moreno wrote: > I want to know which way works a cluster system MonetDB > > This works distributing information in each node or replicated the > information in each node and distributing query processing or duplicated > information in each node and distributes the connexions as a load balancer > > I hope you can help me > > Im testing monetdb with the Pentaho business intelligence suit. To answer your questions, there are multiple ways of running a clustered MonetDB setup. There is some support for clustered operations in monetdbd, see the section on "MULTIPLEX-FUNNELS" [1]. Apart from that, some virtual duct tape is required to set things up, but this nothing to be afraid of and the load balancing scenario is certainly possible. Best, Hannes [1] http://www.monetdb.org/Documentation/monetdbd-man-page

1 0