September 2013 - users-list

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

Bulk Import
by Thomas Johann 15 Sep '13

15 Sep '13

Hi everybody, I have a problem inserting a large amount of data into a monetdb database using bulk import. I'm running the following commands in a loop: connection.execute("COPY *19000000* OFFSET 2 RECORDS INTO XXX FROM '" + csv + "' USING DELIMITERS ';','\n' ;") connection.commit() , where csv is a different csv-file in each round of the loop, but always containing 18000001 rows of data. To be sure that enough memory is allocated I chose 19000000 in the execute command. I now have two questions: 1. Should the number of records (here 19000000) represent the number of lines per .csv-file or the number of lines of the final database (number of csv-files * 18Mio.)??? 2. Can you think of any reason why monetdb would stop reading one specific variable, while continuing to read the others? Let's say my csv has 8 columns and 18000000 rows with no missing values in the raw data. Until Row 16537472 the total data is read-in, but for the following lines variable 3 is missing until line 18000000 while variable 1 as well as 3-8 are perfectly fine. Can this be due to memory or harddisk speed constraints? Why is no error message raised? It would be great if someone could help me. Thanks, Thomas

4 4

Monetdb blocked
by Edgar Mejia 14 Sep '13

14 Sep '13

Hi, please, i have on server as 3.750 mb as ubuntu 12.04, one processor and 2.000 mhz. I install here monetdb and have 12.000,000 millions of rows. I had one problem as one query. For example this query block database: select "timedimension"."namemonth" as "c0", "bi_con_cdr_diario"."comnombre2" as "c1", "bi_con_cdr_diario"."succodigo" as "c2", sum("bi_con_cdr_diario"."minutosfacturados") as "m0" from "timedimension" as "timedimension", "bi_con_cdr_diario" as "bi_con_cdr_diario" where "bi_con_cdr_diario"."idtimedimension" = "timedimension"."id" and "bi_con_cdr_diario"."comcodigo" = '4' group by "timedimension"."namemonth", "bi_con_cdr_diario"."comnombre2", "bi_con_cdr_diario"."succodigo" if i delete (and "bi_con_cdr_diario"."comcodigo" = '4' ) this work fine. Too i im testing very others querys and work fine, but why this not work and block database? Thaks for your help. Edgar M

1 0