May 2013 - developers-list

how to speed up the loading of data into a table
by Bouslah Ahmed 18 Jun '13

18 Jun '13

Good afternoon, I have 2 questions : - 1st question I have a csv file with 300000000 million lines. when I load it in a MonetDB table, it takes more than 16 hours, whereas in Oracle it only takes 15 minutes. Where is the problem? I use mclient as follows :? mclient -u user1 -d demo -ims -s " COPY 300000009 RECORDS INTO LINEORDER FROM STDIN USING DELIMITERS '|','\n'" - < D:\SSB\LINEORDER.tbl ?for information I use : MonetDB5 Windows Memory : 16 Go There are two constraints : primary key and foreign key on the table prior to loading data? 2nd question : how inner joins are made in MongoDB? cordialy.

3 4

Need help with geometry buffer function
by Casey Gum 29 May '13

29 May '13

In the docs about the geospatial extension, it mentions a buffer function: CREATE FUNCTION Buffer(a Geometry, distance FLOAT) RETURNS Geometry Does anyone know what a good value would be to use for distance if I wanted to buffer a point, line, etc. X number of meters? Or does anyone have any ideas for what would be a good approach for this problem - where you want the user to be able to specify how close two things can be to one another to be a match? Thanks a lot, Casey

2 2

Inserting geometry using JDBC PreparedStatement
by Casey Gum 27 May '13

27 May '13

Is there a way to use JDBC PreparedStatements to insert geospatial data? Here's what my prepared statement looks like: PreparedStatement statement = sqlRunner.prepareStatement(insertStatement); statement.setString(1, id); statement.setString(2, feature.getName()); statement.setString(3, feature.getDescription()); statement.setString(4, feature.getType().toString()); statement.setString(5, feature.getGeometry()); statement.executeUpdate(); In this case, feature.getGeometry() returns a string in WKT format. Here's the error I get: EXEC: wrong type for argument 5 of prepared statement: char, expected geometry Thoughts? Thanks, Casey

2 1

Is there a pre-compiled version of MonetDB with geospatial support for OSX?
by Casey Gum 22 May '13

22 May '13

If so, does anyone have a link? Thanks, Casey

1 0

Re: MonetDB: default - Use normal join code for thetajoin with == op...
by Stefan Manegold 21 May '13

21 May '13

On Fri, May 17, 2013 at 11:33:39AM +0200, Sjoerd Mullender wrote: > Changeset: f2c52e636835 for MonetDB > URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=f2c52e636835 > Modified Files: > gdk/gdk_join.c > Branch: default > Log Message: > > Use normal join code for thetajoin with == operator. > > > diffs (22 lines): > > diff --git a/gdk/gdk_join.c b/gdk/gdk_join.c > --- a/gdk/gdk_join.c > +++ b/gdk/gdk_join.c > @@ -1720,11 +1720,17 @@ BATsubthetajoin(BAT **r1p, BAT **r2p, BA > { > BAT *r1, *r2; > > + if (op[0] == '=' && ((op[1] == '=' && op[2] == 0) || op[2] == 0)) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ~~~~~~~~~~ Sjoerd, given the last predicate ("~"), the second ("^") seems obsolete to me. Should the last one rather read "op[1] == 0", i.e., both op == "==" and op == "=" indicate an equality predicate? Or should the last predicate be opmitted completely to accept only op = "==" as euality predicate? Stefan > + return BATsubjoin(r1p, r2p, l, r, sl, sr, estimate); > + > *r1p = NULL; > *r2p = NULL; > if (joinparamcheck(l, r, sl, sr, "BATsubthetajoin") == GDK_FAIL) > return GDK_FAIL; > - if (joininitresults(&r1, &r2, estimate != BUN_NONE ? estimate : sl ? BATcount(sl) : BATcount(l), "BATsubthetajoin") == GDK_FAIL) > + if (joininitresults(&r1, &r2, > + estimate != BUN_NONE ? estimate : > + (sl ? BATcount(sl) : BATcount(l)) * (sr ? BATcount(sr) : BATcount(r)), > + "BATsubthetajoin") == GDK_FAIL) > return GDK_FAIL; > *r1p = r1; > *r2p = r2; > _______________________________________________ > checkin-list mailing list > checkin-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/checkin-list -- | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |

2 1

Smart top-k querying: sorted access
by Maarten Duren 16 May '13

16 May '13

Hi all, We are two Computer Science Master students at Utrecht University. As part of our study, we are doing an experimentation project involving MonetDB. We would like to enrich MonetDB by implementing some smart techniques to handle top-k queries. So far, we have successfully implemented the Threshold Algorithm [Fagin] in MAL for a given top-k selection SQL query. It already outperforms MonetDB's strategy (a naive strategy) if the dataset is large enough :) The problem we are facing now is that almost all top-k techniques require sorted access on the attributes used in the score/ranking function. And sorting a BAT in the MAL program is the real performance bottleneck in our MAL program right now. We have had no success using indexes for sorted access because of the way MonetDB deals with these. We already thought of creating an extra table for each attribute used and insert every value in sorted order. This is a way to achieve sorted access on that attribute. But, obviously, that is not the way to go. It would be feasible for one given SQL query and given data, but not as a general solution. So the real question boils down to: (if possible) how can we get sorted access to an arbitrary attribute of a SQL table or a BAT without sorting it in MAL / MonetDB? Thanks, Maarten van Duren & Nik Schuiling

1 0

Building RPMs with geospatial module
by Casey Gum 15 May '13

15 May '13

I need to build all of the RPMs for MonetDB. I don't really have a choice in this - building from scratch on the machine it will be deployed on is (contractually) not an option in this case. I can build everything fine. For instance, from source directory: *mkdir build; cd build* *../configure strict=no assert=no debug=no optimize=yes* *make* *make install* *make rpm* On the machine I build on, I can go to mclient and execute something like: *sql>CREATE TABLE forests(id INT,name TEXT,shape MULTIPOLYGON);* *operation successful (20.620ms)* On the machine that I install RPMs on, I get this: * sql>CREATE TABLE forests(id INT,name TEXT,shape MULTIPOLYGON); 22000!type (multipolygon) unknown in: "create table forests(id int,name text,shape multipolygon)" * Anyone know how to get the RPMs to build with geospatial support?? Thanks, Casey

3 5

Re: In SQL functions, how to fetch table field value to a variable and how to execute dynamic statement?
by liyuesen 00108910 15 May '13

15 May '13

Oh, I got it, the first time there is one record, after that because more than one records are inserted, so select into can't not work! Thanks, Jason On Wed, May 15, 2013 at 06:46:29PM +0800, liyuesen 00108910 wrote: > I use select into as follow, but it does not work as I want. Can you help me fix it? > step1 create table: > CREATE TABLE table_test(f1 integer, f2 integer); > > step2 create inner function: > CREATE FUNCTION MY_Test() > RETURNS int > BEGIN > DECLARE num integer; > SET num = 100; > INSERT INTO table_test values(1, 2); > RETURN num; > END; > step3 create outer function: > CREATE FUNCTION MY_Test1() > RETURNS integer > BEGIN > DECLARE num integer; > DECLARE num1 integer; > DECLARE num2 integer; > SET num = MY_Test(); > SELECT * INTO num1, num2 from table_test; > SET num = num1 + num2; > RETURN num; > END; > step4 call outer function: > > sql>select MY_Test1(); > cardinality violation (2>1) > > I get the result, what's wrong with the statements? how many rows are already in your table_test? Niels > > Thanks, Jason > > On Wed, May 15, 2013 at 06:21:26PM +0800, liyuesen 00108910 wrote: > > What about the SELECT INTO or CURSOR in SQL functions? Can them be used? > CURSORs don't exist, ie you should select into a variable. > > Niels > > > > Jason > > On Wed, May 15, 2013 at 05:16:38PM +0800, liyuesen 00108910 wrote: > > > Hi guys: > > > I am using Moentdb 11.15.3 on Redhat linux on Inter CPU. > > > First issue, I try to write a SQL function , contains statements fetching table values into local variable, but the SELECT INTO and CURSOR statements are not supported, how can I do that? > > Select into is supported. Cursor is and will not be supported. > > > Second issue, can I execute dynamic statements in SQL function? If that is the case, can you show me how to do it? > > Dynamic sql also isn't supported and very unlikely to be supported. > > > > Niels > > > > > > Thanks, Jason ****************************************************************************************** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained here in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this email in error, please notify the sender by phone or email immediately and delete it! *****************************************************************************************

2 1

Re: In SQL functions, how to fetch table field value to a variable and how to execute dynamic statement?
by liyuesen 00108910 15 May '13

15 May '13

I use select into as follow, but it does not work as I want. Can you help me fix it? step1 create table: CREATE TABLE table_test(f1 integer, f2 integer); step2 create inner function: CREATE FUNCTION MY_Test() RETURNS int BEGIN DECLARE num integer; SET num = 100; INSERT INTO table_test values(1, 2); RETURN num; END; step3 create outer function: CREATE FUNCTION MY_Test1() RETURNS integer BEGIN DECLARE num integer; DECLARE num1 integer; DECLARE num2 integer; SET num = MY_Test(); SELECT * INTO num1, num2 from table_test; SET num = num1 + num2; RETURN num; END; step4 call outer function: sql>select MY_Test1(); cardinality violation (2>1) I get the result, what's wrong with the statements? Thanks, Jason On Wed, May 15, 2013 at 06:21:26PM +0800, liyuesen 00108910 wrote: > What about the SELECT INTO or CURSOR in SQL functions? Can them be used? CURSORs don't exist, ie you should select into a variable. Niels > > Jason > On Wed, May 15, 2013 at 05:16:38PM +0800, liyuesen 00108910 wrote: > > Hi guys: > > I am using Moentdb 11.15.3 on Redhat linux on Inter CPU. > > First issue, I try to write a SQL function , contains statements fetching table values into local variable, but the SELECT INTO and CURSOR statements are not supported, how can I do that? > Select into is supported. Cursor is and will not be supported. > > Second issue, can I execute dynamic statements in SQL function? If that is the case, can you show me how to do it? > Dynamic sql also isn't supported and very unlikely to be supported. > > Niels > > > > Thanks, Jason > > ****************************************************************************************** > > This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained here in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this email in error, please notify the sender by phone or e ma > il > > immediately and delete it! > > ***************************************************************************************** > > _______________________________________________ > > developers-list mailing list > > developers-list(a)monetdb.org > > http://mail.monetdb.org/mailman/listinfo/developers-list > > -- > Niels Nes, Centrum Wiskunde & Informatica (CWI) > Science Park 123, 1098 XG Amsterdam, The Netherlands > room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl > url: http://www.cwi.nl/~niels e-mail: Niels.Nes(a)cwi.nl > _______________________________________________ > developers-list mailing list > developers-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/developers-list > ****************************************************************************************** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained here in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this email in error, please notify the sender by phone or email immediately and delete it! *****************************************************************************************

2 1

Re: In SQL functions, how to fetch table field value to a variable and how to execute dynamic statement?
by liyuesen 00108910 15 May '13

15 May '13

What about the SELECT INTO or CURSOR in SQL functions? Can them be used? Jason On Wed, May 15, 2013 at 05:16:38PM +0800, liyuesen 00108910 wrote: > Hi guys: > I am using Moentdb 11.15.3 on Redhat linux on Inter CPU. > First issue, I try to write a SQL function , contains statements fetching table values into local variable, but the SELECT INTO and CURSOR statements are not supported, how can I do that? Select into is supported. Cursor is and will not be supported. > Second issue, can I execute dynamic statements in SQL function? If that is the case, can you show me how to do it? Dynamic sql also isn't supported and very unlikely to be supported. Niels > > Thanks, Jason > ****************************************************************************************** > This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained here in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this email in error, please notify the sender by phone or ema il > immediately and delete it! > ***************************************************************************************** > _______________________________________________ > developers-list mailing list > developers-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/developers-list -- Niels Nes, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: http://www.cwi.nl/~niels e-mail: Niels.Nes(a)cwi.nl _______________________________________________ developers-list mailing list developers-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/developers-list ****************************************************************************************** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained here in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this email in error, please notify the sender by phone or email immediately and delete it! *****************************************************************************************

2 1