June 2015 - users-list

Is minimal_pipe enforced on long MAL plans?
by Roberto Cornacchia 22 Jun '15

22 Jun '15

Hi there, Is minimal_pipe enforced under any condition? For example when the the MAL plan generated by rel2bin is longer than a given threshold? Context: I have a pretty long query which results in a MAL plan of 33K lines. Though the query is a bit long, I still would expect a 2-3K lines MAL plan. The plan indeed contains the same pieces of code re-evaluated over and over. It first, I simply blamed commonTerms not doing its job. The thing is, the MAL plans generated with minimal_pipe and default_pipe are absolutely identical for this query. Hence my question above. Best, Roberto

3 3

MonetDb Udf
by Fatma Nur Özdemir 19 Jun '15

19 Jun '15

Hello, We are using MonetDB5 server v11.19.15 “Oct2014-SP4” windows version and we want to use a function that we wrote in C programming language. However, we cannot use this user defined function within MonetDB. Maybe, this problem is about windows version because we are not sure that windows version of MonetDb supports this feature. If anybody knows something about this, please inform us. Thank you in advance. Fatma Nur [http://www.bilyoner.com/fm/mail/mailalti_Bmw-applewatch.jpg]<http://www.bilyoner.com/kampanyalar/2015haziranagustosmp.jspx> Bu elektronik posta ve onunla iletilen butun dosyalar sadece gondericisi tarafindan almasi amaclanan yetkili gercek ya da tuzel kisinin kullanimi icindir. Eger soz konusu yetkili alici degilseniz bu elektronik postanin icerigini aciklamaniz, kopyalamaniz, yonlendirmeniz ve kullanmaniz hukuka aykiri ve yasaktir, eger bu mesaji yanlislikla aldiysaniz lutfen gondereni ile temasa geciniz ve bu elektronik postayi siliniz. B�LYONER �nteraktif Hizmetleri A.S. bu mesajin icerdigi bilgilerin dogrulugu veya eksiksiz oldugu konusunda herhangi bir garanti vermemektedir. Bu nedenle bu bilgilerin ne sekilde olursa olsun iceriginden, iletilmesinden, alinmasindan ve saklanmasindan sorumlu degildir. Bu mesajdaki gorusler yalnizca gonderen kisiye aittir ve Bilyoner �nteraktif Hizmetleri A.S. nin goruslerini yansitmayabilir. Bu e-posta bilinen butun bilgisayar viruslerine karsi taranmistir.

1 0

Re: Monetdb-R integration
by imad hajj chahine 19 Jun '15

19 Jun '15

Hi Hannes, This error is due that the "2-age" column has more than 2 distinct values, there is a space after some values. can you run a trim on the "2-Age" and then try it again. As for the vector thing, I didnt get the point, shouldnt the db engine split the data into vectors and feed the aggregate function which return one data point? Thanks On Fri, Jun 19, 2015 at 11:12 AM, Hannes Mühleisen <Hannes.Muehleisen(a)cwi.nl > wrote: > I am running attached script, which I pieced together from your emails. > This does not run, it produces the following error: > > Error running R expression. Error message: Error in t.test.formula(arg1 ~ > arg2) : > grouping factor must have exactly 2 levels > Calls: as.data.frame -> <Anonymous> -> t.test -> t.test.formula > > > > > In general, for aggregations, the R code needs to return a vector with a > single entry for each group. > > > Best, > > Hannes > > > > > On 19 Jun 2015, at 09:44, imad hajj chahine <imad.hajj.chahine(a)gmail.com> > wrote: > > > > Thanks, if you need more information from my side let me know. > > > > Also can you answer me on the following when i use 2 functions one to > retrieve Pvalue and one for the stat value: > > Am I doing the correct way to retrieve the stat and p-value, is the > ttest get executed twice in this case? is there a more optimal way where i > can retrieve both information. > > > > On Fri, Jun 19, 2015 at 10:39 AM, Hannes Mühleisen < > Hannes.Muehleisen(a)cwi.nl> wrote: > > Looking into this at the moment. > > > > > On 16 Jun 2015, at 09:56, Dimitar Nedev < > dimitar.nedev(a)monetdbsolutions.com> wrote: > > > > > > Hannes, do you know if the STD_DEV is the issue there or if the > queries should actually yield different results? > > > > > > Dimitar > > > > > >> On 2015-Jun-15, at 13:28 , imad hajj chahine < > imad.hajj.chahine(a)gmail.com> wrote: > > >> > > >> Hi, > > >> > > >> Shouldn't we have the same result from running the first query and > the second query? > > >> after all its an aggregate function and should return the test for > each group. > > >> > > >> I checked that monetdb had problem with running group by with > statistics functions like STD_DEV, are these 2 issues related? > > >> > > >> Thank you. > > >> > > >> > > >> > > >> > > >> On Mon, Jun 15, 2015 at 1:52 PM, Dimitar Nedev < > dimitar.nedev(a)monetdbsolutions.com> wrote: > > >> Hi Imad, > > >> > > >> Here is the output I got from running the queries. Note, this might > not be entirely representative since I did clean-up some data before > loading it. > > >> > > >> sql>select "2-Age", "tone"."ttest2"("9-Score",6.08963) from > "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > "2-Age" is not null group by "2-Age"; > > >> +---------+--------------------------+ > > >> | 2-Age | L1 | > > >> +=========+==========================+ > > >> | [10-20] | 0.89311103869734754 | > > >> +---------+--------------------------+ > > >> 3 tuples (3.218ms) > > >> > > >> sql>select '[10-20]' as "2-Age","tone"."ttest2"("9-Score",6.08963) > from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > "2-Age" = '[10-20]' > > >> more>union all > > >> more>select '[21-30]' as "2-Age","tone"."ttest2"("9-Score",6.08963) > from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > "2-Age" = '[21-30]' > > >> more>union all > > >> more>select '[> 30]' as "2-Age","tone"."ttest2"("9-Score",6.08963) > from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > "2-Age" = '> 30'; > > >> +---------+--------------------------+ > > >> | 2-Age | L1 | > > >> +=========+==========================+ > > >> | [10-20] | 0.14150742355349472 | > > >> | [21-30] | 0.32872830865003566 | > > >> | [> 30] | 0.37127075363219841 | > > >> +---------+--------------------------+ > > >> 3 tuples (8.825ms) > > >> > > >> > > >> sql>select "tone"."ttest2samplesStatistic"("9-Score", "4-Gender") as > "stat", "tone"."ttest2samples"("9-Score", "4-Gender") as "pvalue" from > "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" group by > "2-Age"; > > >> +--------------------------+--------------------------+ > > >> | stat | pvalue | > > >> +==========================+==========================+ > > >> | -2.1588506639013736 | 0.032555033970368859 | > > >> +--------------------------+--------------------------+ > > >> 1 tuple (13.287ms) > > >> > > >> > > >> I dropped the aggregate with: > > >> sql>DROP AGGREGATE "tone"."ttest2samplesStatistic"; > > >> operation successful (2.049ms) > > >> > > >> > > >> Best regards, > > >> Dimitar > > >> > > >>> On 2015-Jun-12, at 00:46 , imad hajj chahine < > imad.hajj.chahine(a)gmail.com> wrote: > > >>> > > >>> Thank you Dimitar, > > >>> > > >>> I was using Squirrel to connect from my host machine to the VM. > > >>> > > >>> Now when I connect using SSH-client and run the short group by query > it works but the result is not similar to the union queries (PS: i also run > it in the VM direclty and had the same result) > > >>> > > >>> > > >>> +---------+--------------------------+ > > >>> | 2-Age | pval | > > >>> +=========+==========================+ > > >>> | [21-30] | 0.99996611809271041 | > > >>> +---------+--------------------------+ > > >>> 3 tuples (2.638ms) > > >>> > > >>> > > >>> What i am trying to achieve is to run a one sample ttest for all the > age categories, and it seems that the group by is ignored. > > >>> > > >>> I have also created 2 functions for the two sample ttest: > > >>> > > >>> CREATE AGGREGATE "tone"."ttest2samples"(arg1 double, arg2 > varchar(250)) RETURNS double LANGUAGE R { t.test(arg1 ~ arg2)$p.value }; > > >>> CREATE AGGREGATE "tone"."ttest2samplesStatistic"(arg1 double, arg2 > varchar(250)) RETURNS double LANGUAGE R { t.test(arg1 ~ arg2)$statistic }; > > >>> > > >>> select "tone"."ttest2samplesStatistic"("9-Score", "4-Gender") as > "stat", "tone"."ttest2samples"("9-Score", "4-Gender") as "pvalue" from > "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" group by > "2-Age" > > >>> > > >>> Am I doing the correct way to retrieve the stat and p-value, is the > ttest get executed twice in this case? is there a more optimal way where i > can retrieve both information. Please note that I also have the same > grouping issue as before. > > >>> > > >>> Do you have the same behavior, or I am missing something in the > creation of these functions. > > >>> > > >>> A final question, how do u drop a R function? i am trying to execute > the following: > > >>> drop all function "tone"."ttest2samplesStat" > > >>> > > >>> > > >>> Thank you. > > >>> > > >>> > > >>> > > >>> On Thu, Jun 11, 2015 at 11:28 PM, Dimitar Nedev < > dimitar.nedev(a)monetdbsolutions.com> wrote: > > >>> Hi Imad, > > >>> > > >>> Hannes asked me to look at your issue with the MonetDB-R VM. > > >>> > > >>> I loaded the data and ran the queries you mentioned, but I did not > encounter any issues. This holds for both the short and the union queries. > I am also not familiar with the issue you report, nor its cause. At this > time I think it is local to your instance. If you can export your db, or > re-load the data, I would recommend you to create a new instance with a > fresh image and try again. If you import the image as a new VM instance, > you can keep your old one around as well. > > >>> > > >>> Please note that during the test: > > >>> - I ran as the admin (monetdb) user, as the one created default on > the VM image cannot create new schemas. > > >>> - I have to clean the data a bit up to load it, removing dozen lines > or so. > > >>> - Renamed ttest1 to ttest2 to match the queries. > > >>> > > >>> Best regards, > > >>> Dimitar > > >>> > > >>>>>>>>> Begin forwarded message: > > >>>>>>>>> > > >>>>>>>>> From: imad hajj chahine <imad.hajj.chahine(a)gmail.com> > > >>>>>>>>> Subject: Re: Monetdb-R integration > > >>>>>>>>> Date: 10 Jun 2015 10:11:48 CEST > > >>>>>>>>> To: Hannes Mühleisen <Hannes.Muehleisen(a)cwi.nl> > > >>>>>>>>> > > >>>>>>>>> Hi, > > >>>>>>>>> > > >>>>>>>>> I am using the virtual box provided from > https://www.monetdbsolutions.com/solutions/cloud/vm: > > >>>>>>>>> OS:linux version 3.10.0-123.9.3.el7.x86_64 ( > builder(a)kbuilder.dev.centos.org) > > >>>>>>>>> Monetdb: V1.7: (Oct2014-SP1) > > >>>>>>>>> R: 3.1.2 (2014-10-31) > > >>>>>>>>> > > >>>>>>>>> > > >>>>>>>>> > > >>>>>>>>> create table > "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" ("Consumer > Number" INTEGER,"1-Disloyalty" varchar(250),"2-Age" varchar(250),"3-Amount > transferred" INTEGER,"4-Gender" varchar(250),"5-Citiz enship" > varchar(250),"6- Profession" varchar(250),"7-City" varchar(250),"8-1 NC > Billboard" varchar(250),"8-2 NC Friends" varchar(250),"8-3 NC Magazine" > varchar(250),"8-4 NC Others" varchar(250),"9-Score" decimal(18,3),"10- Type > of operation" varchar(250),"11-1 Welcoming" INTEGER,"11-2 Waiting time" > INTEGER,"11-3 Time to finish request" INTEGER,"11-4 Fees" INTEGER,"11-5 > Courtesy of employees" INTEGER,"11-6 Branch layout" INTEGER,"11-7 Attention > to special requests" INTEGER,"12- Total time for service" > decimal(18,3),"13- Distance from POS" decimal(18,3),"14- Weight" INTEGER) > > >>>>>>>>> > > >>>>>>>>> COPY offset 2 INTO > "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" FROM > '/home/App_Data/TestingDataSimple_2003_Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea.csv' > USING DELIMITERS ',','\n','"'; > > >>>>>>>>> > > >>>>>>>>> Thanks > > >>>>>>>>> > > >>>>>>>>> On Wed, Jun 10, 2015 at 10:50 AM, Hannes Mühleisen < > Hannes.Muehleisen(a)cwi.nl> wrote: > > >>>>>>>>> Hello Imad, > > >>>>>>>>> > > >>>>>>>>> which version of MonetDB and R are you using? Which operating > system? > > >>>>>>>>> > > >>>>>>>>> Can you please also include the commands you used to load your > CSV into MonetDB/ create the table? > > >>>>>>>>> > > >>>>>>>>> Thanks, > > >>>>>>>>> > > >>>>>>>>> Hannes > > >>>>>>>>> > > >>>>>>>>>> On 10 Jun 2015, at 00:25, imad hajj chahine < > imad.hajj.chahine(a)gmail.com> wrote: > > >>>>>>>>>> > > >>>>>>>>>> Hi Hannes, > > >>>>>>>>>> > > >>>>>>>>>> I am testing Monetdb-R integration to do some statistical > test, i followed the tutorial you posted on youtube and i have created the > following function: > > >>>>>>>>>> > > >>>>>>>>>> CREATE AGGREGATE "tone"."ttest1"(arg1 double, arg2 double) > RETURNS double LANGUAGE R { t.test(arg1, arg2)$p.value }; > > >>>>>>>>>> > > >>>>>>>>>> this function will basically do a one sample t-test. > > >>>>>>>>>> > > >>>>>>>>>> I have loaded the sample attached file into a demo database > and trying to run the following query: > > >>>>>>>>>> > > >>>>>>>>>> select "2-Age", "tone"."ttest2"("9-Score",6.08963) from > "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > "2-Age" is not null group by "2-Age" > > >>>>>>>>>> > > >>>>>>>>>> The following error happened > > >>>>>>>>>> Error: protocol violation: unexpected line in data block: . > > >>>>>>>>>> SQLState: M0M10 > > >>>>>>>>>> ErrorCode: 0 > > >>>>>>>>>> > > >>>>>>>>>> alternatively i managed to have the intended result by > running this query: > > >>>>>>>>>> > > >>>>>>>>>> select '[10-20]' as > "2-Age","tone"."ttest2"("9-Score",6.08963) from > "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > "2-Age" = '[10-20]' > > >>>>>>>>>> union all > > >>>>>>>>>> select '[21-30]' as > "2-Age","tone"."ttest2"("9-Score",6.08963) from > "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > "2-Age" = '[21-30]' > > >>>>>>>>>> union all > > >>>>>>>>>> select '[> 30]' as "2-Age","tone"."ttest2"("9-Score",6.08963) > from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > "2-Age" = '> 30' > > >>>>>>>>>> > > >>>>>>>>>> > > >>>>>>>>>> any help or guidance is appreciated. > > >>>>>>>>>> Thank you. > > >>>>>>>>>> > > >>>>>>>>>> > <TestingDataSimple_2003_Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea.csv> > > >>>>>>>>> > > >>>>>>>>> > > >>>>>>>> > > >>>>>>> > > >>>>>> > > >>>>> > > >>>> > > >>> > > >> > > >> > > > > > > > > > >

1 0

Fwd: Monetdb-R integration
by Hannes Mühleisen 19 Jun '15

19 Jun '15

(Forwarding to users list, free support is public) > Begin forwarded message: > > From: Hannes Mühleisen <hannes.muehleisen(a)cwi.nl> > Subject: Re: Monetdb-R integration > Date: 19 Jun 2015 10:12:48 CEST > To: imad hajj chahine <imad.hajj.chahine(a)gmail.com> > Cc: Dimitar Nedev <dimitar.nedev(a)monetdbsolutions.com> > > I am running attached script, which I pieced together from your emails. This does not run, it produces the following error: > > Error running R expression. Error message: Error in t.test.formula(arg1 ~ arg2) : > grouping factor must have exactly 2 levels > Calls: as.data.frame -> <Anonymous> -> t.test -> t.test.formula > > > > In general, for aggregations, the R code needs to return a vector with a single entry for each group. > > > Best, > > Hannes > > > >> On 19 Jun 2015, at 09:44, imad hajj chahine <imad.hajj.chahine(a)gmail.com> wrote: >> >> Thanks, if you need more information from my side let me know. >> >> Also can you answer me on the following when i use 2 functions one to retrieve Pvalue and one for the stat value: >> Am I doing the correct way to retrieve the stat and p-value, is the ttest get executed twice in this case? is there a more optimal way where i can retrieve both information. >> >> On Fri, Jun 19, 2015 at 10:39 AM, Hannes Mühleisen <Hannes.Muehleisen(a)cwi.nl> wrote: >> Looking into this at the moment. >> >>> On 16 Jun 2015, at 09:56, Dimitar Nedev <dimitar.nedev(a)monetdbsolutions.com> wrote: >>> >>> Hannes, do you know if the STD_DEV is the issue there or if the queries should actually yield different results? >>> >>> Dimitar >>> >>>> On 2015-Jun-15, at 13:28 , imad hajj chahine <imad.hajj.chahine(a)gmail.com> wrote: >>>> >>>> Hi, >>>> >>>> Shouldn't we have the same result from running the first query and the second query? >>>> after all its an aggregate function and should return the test for each group. >>>> >>>> I checked that monetdb had problem with running group by with statistics functions like STD_DEV, are these 2 issues related? >>>> >>>> Thank you. >>>> >>>> >>>> >>>> >>>> On Mon, Jun 15, 2015 at 1:52 PM, Dimitar Nedev <dimitar.nedev(a)monetdbsolutions.com> wrote: >>>> Hi Imad, >>>> >>>> Here is the output I got from running the queries. Note, this might not be entirely representative since I did clean-up some data before loading it. >>>> >>>> sql>select "2-Age", "tone"."ttest2"("9-Score",6.08963) from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where "2-Age" is not null group by "2-Age"; >>>> +---------+--------------------------+ >>>> | 2-Age | L1 | >>>> +=========+==========================+ >>>> | [10-20] | 0.89311103869734754 | >>>> +---------+--------------------------+ >>>> 3 tuples (3.218ms) >>>> >>>> sql>select '[10-20]' as "2-Age","tone"."ttest2"("9-Score",6.08963) from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where "2-Age" = '[10-20]' >>>> more>union all >>>> more>select '[21-30]' as "2-Age","tone"."ttest2"("9-Score",6.08963) from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where "2-Age" = '[21-30]' >>>> more>union all >>>> more>select '[> 30]' as "2-Age","tone"."ttest2"("9-Score",6.08963) from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where "2-Age" = '> 30'; >>>> +---------+--------------------------+ >>>> | 2-Age | L1 | >>>> +=========+==========================+ >>>> | [10-20] | 0.14150742355349472 | >>>> | [21-30] | 0.32872830865003566 | >>>> | [> 30] | 0.37127075363219841 | >>>> +---------+--------------------------+ >>>> 3 tuples (8.825ms) >>>> >>>> >>>> sql>select "tone"."ttest2samplesStatistic"("9-Score", "4-Gender") as "stat", "tone"."ttest2samples"("9-Score", "4-Gender") as "pvalue" from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" group by "2-Age"; >>>> +--------------------------+--------------------------+ >>>> | stat | pvalue | >>>> +==========================+==========================+ >>>> | -2.1588506639013736 | 0.032555033970368859 | >>>> +--------------------------+--------------------------+ >>>> 1 tuple (13.287ms) >>>> >>>> >>>> I dropped the aggregate with: >>>> sql>DROP AGGREGATE "tone"."ttest2samplesStatistic"; >>>> operation successful (2.049ms) >>>> >>>> >>>> Best regards, >>>> Dimitar >>>> >>>>> On 2015-Jun-12, at 00:46 , imad hajj chahine <imad.hajj.chahine(a)gmail.com> wrote: >>>>> >>>>> Thank you Dimitar, >>>>> >>>>> I was using Squirrel to connect from my host machine to the VM. >>>>> >>>>> Now when I connect using SSH-client and run the short group by query it works but the result is not similar to the union queries (PS: i also run it in the VM direclty and had the same result) >>>>> >>>>> >>>>> +---------+--------------------------+ >>>>> | 2-Age | pval | >>>>> +=========+==========================+ >>>>> | [21-30] | 0.99996611809271041 | >>>>> +---------+--------------------------+ >>>>> 3 tuples (2.638ms) >>>>> >>>>> >>>>> What i am trying to achieve is to run a one sample ttest for all the age categories, and it seems that the group by is ignored. >>>>> >>>>> I have also created 2 functions for the two sample ttest: >>>>> >>>>> CREATE AGGREGATE "tone"."ttest2samples"(arg1 double, arg2 varchar(250)) RETURNS double LANGUAGE R { t.test(arg1 ~ arg2)$p.value }; >>>>> CREATE AGGREGATE "tone"."ttest2samplesStatistic"(arg1 double, arg2 varchar(250)) RETURNS double LANGUAGE R { t.test(arg1 ~ arg2)$statistic }; >>>>> >>>>> select "tone"."ttest2samplesStatistic"("9-Score", "4-Gender") as "stat", "tone"."ttest2samples"("9-Score", "4-Gender") as "pvalue" from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" group by "2-Age" >>>>> >>>>> Am I doing the correct way to retrieve the stat and p-value, is the ttest get executed twice in this case? is there a more optimal way where i can retrieve both information. Please note that I also have the same grouping issue as before. >>>>> >>>>> Do you have the same behavior, or I am missing something in the creation of these functions. >>>>> >>>>> A final question, how do u drop a R function? i am trying to execute the following: >>>>> drop all function "tone"."ttest2samplesStat" >>>>> >>>>> >>>>> Thank you. >>>>> >>>>> >>>>> >>>>> On Thu, Jun 11, 2015 at 11:28 PM, Dimitar Nedev <dimitar.nedev(a)monetdbsolutions.com> wrote: >>>>> Hi Imad, >>>>> >>>>> Hannes asked me to look at your issue with the MonetDB-R VM. >>>>> >>>>> I loaded the data and ran the queries you mentioned, but I did not encounter any issues. This holds for both the short and the union queries. I am also not familiar with the issue you report, nor its cause. At this time I think it is local to your instance. If you can export your db, or re-load the data, I would recommend you to create a new instance with a fresh image and try again. If you import the image as a new VM instance, you can keep your old one around as well. >>>>> >>>>> Please note that during the test: >>>>> - I ran as the admin (monetdb) user, as the one created default on the VM image cannot create new schemas. >>>>> - I have to clean the data a bit up to load it, removing dozen lines or so. >>>>> - Renamed ttest1 to ttest2 to match the queries. >>>>> >>>>> Best regards, >>>>> Dimitar >>>>> >>>>>>>>>>> Begin forwarded message: >>>>>>>>>>> >>>>>>>>>>> From: imad hajj chahine <imad.hajj.chahine(a)gmail.com> >>>>>>>>>>> Subject: Re: Monetdb-R integration >>>>>>>>>>> Date: 10 Jun 2015 10:11:48 CEST >>>>>>>>>>> To: Hannes Mühleisen <Hannes.Muehleisen(a)cwi.nl> >>>>>>>>>>> >>>>>>>>>>> Hi, >>>>>>>>>>> >>>>>>>>>>> I am using the virtual box provided from https://www.monetdbsolutions.com/solutions/cloud/vm: >>>>>>>>>>> OS:linux version 3.10.0-123.9.3.el7.x86_64 (builder(a)kbuilder.dev.centos.org) >>>>>>>>>>> Monetdb: V1.7: (Oct2014-SP1) >>>>>>>>>>> R: 3.1.2 (2014-10-31) >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> create table "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" ("Consumer Number" INTEGER,"1-Disloyalty" varchar(250),"2-Age" varchar(250),"3-Amount transferred" INTEGER,"4-Gender" varchar(250),"5-Citiz enship" varchar(250),"6- Profession" varchar(250),"7-City" varchar(250),"8-1 NC Billboard" varchar(250),"8-2 NC Friends" varchar(250),"8-3 NC Magazine" varchar(250),"8-4 NC Others" varchar(250),"9-Score" decimal(18,3),"10- Type of operation" varchar(250),"11-1 Welcoming" INTEGER,"11-2 Waiting time" INTEGER,"11-3 Time to finish request" INTEGER,"11-4 Fees" INTEGER,"11-5 Courtesy of employees" INTEGER,"11-6 Branch layout" INTEGER,"11-7 Attention to special requests" INTEGER,"12- Total time for service" decimal(18,3),"13- Distance from POS" decimal(18,3),"14- Weight" INTEGER) >>>>>>>>>>> >>>>>>>>>>> COPY offset 2 INTO "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" FROM '/home/App_Data/TestingDataSimple_2003_Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea.csv' USING DELIMITERS ',','\n','"'; >>>>>>>>>>> >>>>>>>>>>> Thanks >>>>>>>>>>> >>>>>>>>>>> On Wed, Jun 10, 2015 at 10:50 AM, Hannes Mühleisen <Hannes.Muehleisen(a)cwi.nl> wrote: >>>>>>>>>>> Hello Imad, >>>>>>>>>>> >>>>>>>>>>> which version of MonetDB and R are you using? Which operating system? >>>>>>>>>>> >>>>>>>>>>> Can you please also include the commands you used to load your CSV into MonetDB/ create the table? >>>>>>>>>>> >>>>>>>>>>> Thanks, >>>>>>>>>>> >>>>>>>>>>> Hannes >>>>>>>>>>> >>>>>>>>>>>> On 10 Jun 2015, at 00:25, imad hajj chahine <imad.hajj.chahine(a)gmail.com> wrote: >>>>>>>>>>>> >>>>>>>>>>>> Hi Hannes, >>>>>>>>>>>> >>>>>>>>>>>> I am testing Monetdb-R integration to do some statistical test, i followed the tutorial you posted on youtube and i have created the following function: >>>>>>>>>>>> >>>>>>>>>>>> CREATE AGGREGATE "tone"."ttest1"(arg1 double, arg2 double) RETURNS double LANGUAGE R { t.test(arg1, arg2)$p.value }; >>>>>>>>>>>> >>>>>>>>>>>> this function will basically do a one sample t-test. >>>>>>>>>>>> >>>>>>>>>>>> I have loaded the sample attached file into a demo database and trying to run the following query: >>>>>>>>>>>> >>>>>>>>>>>> select "2-Age", "tone"."ttest2"("9-Score",6.08963) from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where "2-Age" is not null group by "2-Age" >>>>>>>>>>>> >>>>>>>>>>>> The following error happened >>>>>>>>>>>> Error: protocol violation: unexpected line in data block: . >>>>>>>>>>>> SQLState: M0M10 >>>>>>>>>>>> ErrorCode: 0 >>>>>>>>>>>> >>>>>>>>>>>> alternatively i managed to have the intended result by running this query: >>>>>>>>>>>> >>>>>>>>>>>> select '[10-20]' as "2-Age","tone"."ttest2"("9-Score",6.08963) from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where "2-Age" = '[10-20]' >>>>>>>>>>>> union all >>>>>>>>>>>> select '[21-30]' as "2-Age","tone"."ttest2"("9-Score",6.08963) from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where "2-Age" = '[21-30]' >>>>>>>>>>>> union all >>>>>>>>>>>> select '[> 30]' as "2-Age","tone"."ttest2"("9-Score",6.08963) from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where "2-Age" = '> 30' >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> any help or guidance is appreciated. >>>>>>>>>>>> Thank you. >>>>>>>>>>>> >>>>>>>>>>>> <TestingDataSimple_2003_Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea.csv> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>> >>>> >>>> >>> >> >> >

1 0

Enabling Spatial support?
by Thorsten Wesskallnies 17 Jun '15

17 Jun '15

Hi, I'm having some trouble with the instruction posted on the website here: https://www.monetdb.org/Documentation/Extensions/GIS I am running an OEL Server on version 6.6 and installed MonetDB using yum: Installed Packages Name : MonetDB Arch : x86_64 Version : 11.19.11 Release : 20150423.el6 ... Name : MonetDB-geom-MonetDB5 Arch : x86_64 Version : 11.19.11 Release : 20150423.el6 I also configured and installed the current version of the GEOS Library # geos-config --version >3.4.2 According to the above mentioned website I should now rebuild my DB with the '--enable-geom configure argument'. Unfortunately I'm unable to find the correct way to do it since yum installed a binary on my system and not the sourcecode. The 'MonetDB-geom' package doesn't seem to to the trick since the result of the sample Code on the Website returns an empty result. Hope somebody can point me in the right direction. MfG Thorsten Wesskallnies Avitech GmbH Principal Office: Bahnhofplatz 1 | 88045 Friedrichshafen | Germany Court Registration: Amtsgericht Ulm | HRB 728293 Geschäftsführer/Managing Director: Antonio Maria Gonzalez Gorostiza VAT No.: DE223719716 http://avitech.aero

2 1

trace disabled?
by Roberto Cornacchia 16 Jun '15

16 Jun '15

Hi there, Just to be sure: is it expected that trace is disabled / changed in Jan2015? I can see the trace by inspecting sys.tracelog() after the query, but no longer in one go as before. Roberto

1 1

Performing a quantile in R from a MonetDB.R db with 300million rows (UNCLASSIFIED)
by George, Glover E ERD-MS 15 Jun '15

15 Jun '15

Classification: UNCLASSIFIED Caveats: NONE Hi all, I'm currently trying to compare the performance of R's quantile function to that of MonetDB's. I have a table loaded with the TPC-H benchmark's data (scale factor of 50). I'm trying to return ~300 million rows of data (select l_extendedprice from lineitem), and although I have 256G of RAM, I'm getting the following: Error in paste(0)("",resp, collapse="") : Result would exceed 2^31-1 bytes The R statement is : x = dbGetQuery(conn, "select l_extendedprice from lineitem") Forgive me if this is a simplistic questions, as I am fairly new to R and don't know which avenue to take next. I took a look at bigmemory, but don't know quite how to return a result from monetdb and have it use a big.matrix. Note: the l_extendedprice is a floating (double) value. Any suggestions? Cheers. Glover George Classification: UNCLASSIFIED Caveats: NONE

2 2

1-to-N (bat) UDF
by Roberto Cornacchia 15 Jun '15

15 Jun '15

Hi there, I need a string tokenizer in MonetDB. The problem I have is not with the function itself, but with the fact that this is a 1 to N rows function. Implementing this for a single string value is easy enough, using a table function that takes a string a returns a table: create function tokenize(s string) returns table (token string) external name tokenize; select * from tokenize("one two three"); That's fine. The issue I'm having is with extending this to a column of strings. Ideally, given a string column one two three four five six seven eight I'd like to get an output along these lines (simplistic representation here): one two three | one one two three | two one two three | three four five six | four four five six | five four five six | six seven eight | seven seven eight | eight I can sure code the c function and the mal wrapper to implement this, but I can't see how to map it to SQL, given that table functions don't accept identifiers as parameters. Any idea? Any possible workaround? Thanks, Roberto

4 11

Geommodul and Linestring Query
by Dennis Wiest 15 Jun '15

15 Jun '15

Hello, I have a question for Geomodul. Is it possible to execute the following query with a LineString. This is my query with a point and the sample data set from the documentation. SELECT forests.name,buildings.name FROM forests,buildings WHERE "Intersect"(buildings.outline, Point(53.9,29.9)) = true; Now I want to replace the Point with a LineString like this in mysql. SELECT forests.name,buildings.name FROM forests,buildings WHERE "Intersect"(buildings.outline, LineString(Point(53.9,29.9), Point(53.0, 29.0))) = true; TestDatabase CREATE TABLE forests(id INT,name TEXT,shape MULTIPOLYGON); CREATE TABLE buildings(id INT,name TEXT,location POINT,outline POLYGON); INSERT INTO forests VALUES(109, 'Green Forest', 'MULTIPOLYGON( ((28 26,28 0,84 0,84 42,28 26), (52 18,66 23,73 9,48 6,52 18)), ((59 18,67 18,67 13,59 13,59 18)))'); INSERT INTO buildings VALUES(113, '123 Main Street', 'POINT( 52 30 )', 'POLYGON( ( 50 31, 54 31, 54 29, 50 29, 50 31) )'); INSERT INTO buildings VALUES(114, '215 Main Street', 'POINT( 64 33 )', 'POLYGON( ( 66 34, 62 34, 62 32, 66 32, 66 34) )'); SELECT forests.name,buildings.name FROM forests,buildings WHERE forests.name = 'Green Forest' and Overlaps(forests.shape, buildings.outline) = true; many thanks Dennis ________________________________ Avitech GmbH Principal Office: Bahnhofplatz 1 | 88045 Friedrichshafen | Germany Court Registration: Amtsgericht Ulm | HRB 728293 Gesch?ftsf?hrer/Managing Director: Antonio Maria Gonzalez Gorostiza VAT No.: DE223719716 http://avitech.aero

2 2

how to take output from DELETE and feed it to INSERT?
by integrity 12 Jun '15

12 Jun '15

Dear experts, Does anyone know how to take output from DELETE and feed it to INSERT INTO another table? Since now my pipeline ran into a certain point that 37 seconds of the total time 48 seconds spent on the following queries: INSERT INTO old_to_old SELECT uniqueid, targetid, distance_arcsec FROM associatedsource WHERE uniqueid IN (SELECT old_uniqueid FROM one_to_many) ORDER BY uniqueid, targetid; 42650 affected rows (20.0s) DELETE FROM associatedsource WHERE uniqueid IN (SELECT old_uniqueid FROM one_to_many); 42650 affected rows (16.6s) where associatedsource table is a large table with 1219234829 rows which is the size till the end of second day, while one_to_many table is quite small only 30 rows. associatedsource will accumulate processed data day by day, till the end of first day it has 603553616 rows. As associatedsource table grows, this two queries has severely degraded my pipeline, on first day the time for them were 9.1s and 7.8s. We can see monetdb server engine (randomly?) access the same data twice, for a large table and limited time allocated to a run of pipeline this is very expensive. if we can combine the queries into a single then the logic about which rows to move will be neater and improve performance twice. Thank you and best wishes, Meng

2 2