September 2013 - users-list

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

Query Problem with LIKE clause
by Hancel Gonzalez 11 Sep '13

11 Sep '13

Hi, finally i installed MonetDB. Now i get the next problem =S When i execute one query, i get error and i dont know how to solve. Please, help me. =( Hancel. PD: Error is: *MAPI = (monetdb) /tmp/.s.monetdb.50000* *ACTION= read_line* *QUERY = select s0.value,s1.value* * from (* * (select f0.subject as r0,f0.object as r1* * from p1 f0,p0 f1* * where f1.subject=f0.subject and f1.object=58758 and ((select value from strings where id = f0.object) like '%ambilateral%'))* * )facts, strings s0, strings s1* * where s0.id=facts.r0 and s1.id=facts.r1;* *ERROR = !Connection terminated*

4 6

Compiling problem
by Hancel Gonzalez 11 Sep '13

11 Sep '13

Hi again, i have a problem installing MonetDB 11.9.7 from source code. When i do the * make* , get the next error in the process =S Can i help me? Many thanks. Regards, Hancel. PD: Error here: *fits.c:54:20: error: fitsio.h: No such file or directory* *fits.c:55:21: error: fitsio2.h: No such file or directory* *fits.c:56:21: error: longnam.h: No such file or directory* *fits.c: In function 'fits2mtype':* *fits.c:126: error: 'TBIT' undeclared (first use in this function)* *fits.c:126: error: (Each undeclared identifier is reported only once* *fits.c:126: error: for each function it appears in.)* *fits.c:127: error: 'TLOGICAL' undeclared (first use in this function)* *fits.c:129: error: 'TBYTE' undeclared (first use in this function)* *fits.c:130: error: 'TSBYTE' undeclared (first use in this function)* *fits.c:132: error: 'TSTRING' undeclared (first use in this function)* *fits.c:134: error: 'TUSHORT' undeclared (first use in this function)* *fits.c:135: error: 'TSHORT' undeclared (first use in this function)* *fits.c:137: error: 'TUINT' undeclared (first use in this function)* *fits.c:138: error: 'TINT' undeclared (first use in this function)* *fits.c:140: error: 'TLONG' undeclared (first use in this function)* *fits.c:141: error: 'TULONG' undeclared (first use in this function)* *fits.c:142: error: 'TLONGLONG' undeclared (first use in this function)* *fits.c:144: error: 'TFLOAT' undeclared (first use in this function)* *fits.c:146: error: 'TDOUBLE' undeclared (first use in this function)* *fits.c:149: error: 'TCOMPLEX' undeclared (first use in this function)* *fits.c:150: error: 'TDBLCOMPLEX' undeclared (first use in this function)* *fits.c: In function 'fits2subtype':* *fits.c:161: error: 'TBIT' undeclared (first use in this function)* *fits.c:162: error: 'TLOGICAL' undeclared (first use in this function)* *fits.c:165: error: 'TBYTE' undeclared (first use in this function)* *fits.c:166: error: 'TSBYTE' undeclared (first use in this function)* *fits.c:169: error: 'TSTRING' undeclared (first use in this function)* *fits.c:172: error: 'TUSHORT' undeclared (first use in this function)* *fits.c:173: error: 'TSHORT' undeclared (first use in this function)* *fits.c:176: error: 'TUINT' undeclared (first use in this function)* *fits.c:177: error: 'TINT' undeclared (first use in this function)* *fits.c:180: error: 'TULONG' undeclared (first use in this function)* *fits.c:181: error: 'TLONG' undeclared (first use in this function)* *fits.c:182: error: 'TLONGLONG' undeclared (first use in this function)* *fits.c:185: error: 'TFLOAT' undeclared (first use in this function)* *fits.c:188: error: 'TDOUBLE' undeclared (first use in this function)* *fits.c:192: error: 'TCOMPLEX' undeclared (first use in this function)* *fits.c:193: error: 'TDBLCOMPLEX' undeclared (first use in this function)* *fits.c: In function 'FITSexportTable':* *fits.c:209: error: 'fitsfile' undeclared (first use in this function)* *fits.c:209: error: 'fptr' undeclared (first use in this function)* *fits.c:303: error: 'USHORT_IMG' undeclared (first use in this function)* *fits.c:305: error: 'READWRITE' undeclared (first use in this function)* *fits.c:308: error: 'BINARY_TBL' undeclared (first use in this function)* *fits.c:332: error: 'TLOGICAL' undeclared (first use in this function)* *fits.c:361: error: 'TBYTE' undeclared (first use in this function)* *fits.c:419: error: 'TSHORT' undeclared (first use in this function)* *fits.c:448: error: 'TINT' undeclared (first use in this function)* *fits.c:477: error: 'TLONG' undeclared (first use in this function)* *fits.c:506: error: 'TFLOAT' undeclared (first use in this function)* *fits.c:535: error: 'TDOUBLE' undeclared (first use in this function)* *fits.c: In function 'FITSdir':* *fits.c:573: error: 'fitsfile' undeclared (first use in this function)* *fits.c:573: error: 'fptr' undeclared (first use in this function)* *fits.c:588: error: 'READONLY' undeclared (first use in this function)* *fits.c: In function 'FITSdirpat':* *fits.c:607: error: 'fitsfile' undeclared (first use in this function)* *fits.c:607: error: 'fptr' undeclared (first use in this function)* *fits.c:631: error: 'READONLY' undeclared (first use in this function)* *fits.c: In function 'FITStest':* *fits.c:646: error: 'fitsfile' undeclared (first use in this function)* *fits.c:646: error: 'fptr' undeclared (first use in this function)* *fits.c:651: error: 'READONLY' undeclared (first use in this function)* *fits.c: In function 'FITSattach':* *fits.c:670: error: 'fitsfile' undeclared (first use in this function)* *fits.c:670: error: 'fptr' undeclared (first use in this function)* *fits.c:684: error: 'READONLY' undeclared (first use in this function)* *fits.c:731: error: 'ASCII_TBL' undeclared (first use in this function)* *fits.c:731: error: 'BINARY_TBL' undeclared (first use in this function)* *fits.c:735: error: 'TSTRING' undeclared (first use in this function)* *fits.c: In function 'FITSloadTable':* *fits.c:834: error: 'fitsfile' undeclared (first use in this function)* *fits.c:834: error: 'fptr' undeclared (first use in this function)* *fits.c:842: error: 'FLEN_VALUE' undeclared (first use in this function)* *fits.c:878: error: 'READONLY' undeclared (first use in this function)* *fits.c:886: error: 'ASCII_TBL' undeclared (first use in this function)* *fits.c:886: error: 'BINARY_TBL' undeclared (first use in this function)* *fits.c:906: error: 'TSTRING' undeclared (first use in this function)* *fits.c:970: error: 'FLEN_ERRMSG' undeclared (first use in this function)* *make[9]: *** [lib_fits_la-fits.lo] Error 1* *make[9]: Leaving directory `/home/hancel/MonetDB-11.9.7/sql/backends/monet5/vaults'* *make[8]: *** [all] Error 2* *make[8]: Leaving directory `/home/hancel/MonetDB-11.9.7/sql/backends/monet5/vaults'* *make[7]: *** [all-recursive] Error 1* *make[7]: Leaving directory `/home/hancel/MonetDB-11.9.7/sql/backends/monet5'* *make[6]: *** [all] Error 2* *make[6]: Leaving directory `/home/hancel/MonetDB-11.9.7/sql/backends/monet5'* *make[5]: *** [all-recursive] Error 1* *make[5]: Leaving directory `/home/hancel/MonetDB-11.9.7/sql/backends'* *make[4]: *** [all] Error 2* *make[4]: Leaving directory `/home/hancel/MonetDB-11.9.7/sql/backends'* *make[3]: *** [all-recursive] Error 1* *make[3]: Leaving directory `/home/hancel/MonetDB-11.9.7/sql'* *make[2]: *** [all] Error 2* *make[2]: Leaving directory `/home/hancel/MonetDB-11.9.7/sql'* *make[1]: *** [all-recursive] Error 1* *make[1]: Leaving directory `/home/hancel/MonetDB-11.9.7'* *make: *** [all] Error 2*

4 11

Setting connection time-out?
by Dennis Pallett 10 Sep '13

10 Sep '13

Hello, Is it possible to set a time-out in MonetDB for connections? On our test instance of MonetDB we are running into connection limits because our developers are starting/stopping our application all day long without properly closing the connections. On production this is not a problem because there the connections are closed properly. Best regards, Dennis Pallett

1 0

GeoSpatial
by Lee Hachadoorian 05 Sep '13

05 Sep '13

I found out about MonetDB a few months ago and am currently giving it a whirl. I am a geographer and am interested in the GeoSpatial extension. I have found limited documentation [1][2], which (I think) has changed since I first saw it a few months ago. I remember something about a shapefile import application that was based on shp2pgsql used by PostGIS, and also something about using OpenJump to visualize the data stored in the MonetDB database, but I cannot find this information any more. I have successfully created a spatial table by copying a CREATE TABLE statement from PostGIS, then using COPY ... TO in PostGIS and COPY ... FROM in MonetDB. But I would like to know if a shp2pgsql equivalent exists for MonetDB, or if there is any work on or plans for OGR functionality. I would also like to know how users visualize MonetDB spatial data in OpenJump or any other GIS, which incidentally also leads into the question of OGR functionality since so many FOSS GISes (e.g. QGIS) use OGR for data loading. Best, --Lee [1] http://www.monetdb.org/Documentation/Extensions/GIS [2] http://www.monetdb.org/Documentation/Extensions/GIS/SpatialTypes -- Lee Hachadoorian Assistant Professor in Geography, Dartmouth College http://freecity.commons.gc.cuny.edu

2 1