- users-list - monetdb.org

Must the file be deleted after "copy binary into table from file"？
by integrity 19 Jul '13

19 Jul '13

can i have the file reserved? Thanks Meng

4 3

Re: Must the file be deleted after "copy binary into table from file"？
by integrity 19 Jul '13

19 Jul '13

Thanks very much for explaining these to me, but is it possible to provide two options of move, one do the move and reserve, one just move? That will be very convenient for uses.^_^ Sincerely, Meng PS,google mail in China is very unstable, often got disconnected from internet for some reason, so I have to applie another account here. ------------------ Original ------------------ From: "Stefan Manegold"<Stefan.Manegold(a)cwi.nl>; Date: Fri, Jul 19, 2013 02:15 PM To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; Cc: "Niels Nes"<Niels.Nes(a)cwi.nl>; "sjoerd"<Sjoerd.Mullender(a)cwi.nl>; Subject: Re: Must the file be deleted after "copy binary into table from file"？ Hi Meng, for efficiently, i.e., to avoid a file copy, MonetDB's binary copy into simply moves(^) the user-provided and to be loaded files into its dbfarm. Thus, if you need to preserve a copy of the files you provide, you need to do so yourself before executing a binary copy into. Best, Stefan (^) 1) The actual behavior (move) is actually different than the copy that the documentation at http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/BinaryBulkLoad suggests. We need to fix the latter. 2) The actual behavior currently differs between data types; for numerical types, the user-provided files are moved as explained above; for stings, the files are read and processed, i.e., the used-provided file remains in place as-is. We need to consider unifying this behavior, e.g., by deleting also the user provided files for strings once they have been processed/loaded. 3) In case the copy into file, e.g., due to a failing sanity check after the file is moved, that data is factually lost, i.e., the files are gone, but no data is loaded into the database; we need to either document this clearly, or make the implementation behave like a transaction. ----- Original Message ----- > can i have the file reserved? > > > > Thanks > Meng > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/users-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) | _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list .

2 1

Join index
by Bouslah Ahmed 18 Jul '13

18 Jul '13

Hi, I want to optimize the runtime of this query Select sum(a.C4), b.C1, b.C2, b.C3 from v1 a, v2 b where (b.C1 = a.C1 and b.C2 = a.C2 and b.C3 = a.C3) or (b.C1 = a.C1 and b.C2 = a.C2 and b.C3 = 0) or (b.C1 = a.C1 and b.C2 = 0 and b.C3 = 0) or (b.C1 = 0 and b.C2 = 0 and b.C3 = 0) group by b.C1, b.C2, b.C3; why I thought of creating a BITMAP index join or create a bitmap index for v1 columns (c1, c2, c3) and v2 (c1, c2, c3). I can't do this with MonetDB can you help me.

2 1

failed to open file when 'copy binary into table from binaryformfile'
by integrity 17 Jul '13

17 Jul '13

hi all, when i use the command in sql: sql>copy binary into testtable from('file1'); failed to open file file1 the file1 contents : [svomhpc@localhost /home/data1]$>cat file1 0123[svomhpc@localhost /home/data1]$>hexdump file1 0000000 0000 3000 0000 3100 0000 3200 0000 3300 0000010 can anyone tell me what's wrong with file1 or how to use copy binary with? Meng

5 8

International characters in MonetDB
by aaaaaa ggggggg 15 Jul '13

15 Jul '13

Hello All, I have been trying to do some bulk uploads from CSV files into a MonetDB database. All the fields in the database are VARCHAR. The text in some of the fields contains international characters such as : ü, Ó, é etc. When I execute : copy into database from 'filename.csv' using delimiters ',','\n','"' NULL as ''; I get an error stating that the fields with words containing the international characters could not be imported, and the import process stops. How can I solve this issue? (I am running Monetdb on windows) Thanks, SG

3 5

Giving monetdb a fair chance
by Franck Routier 12 Jul '13

12 Jul '13

Hi, I am benchmarking different DB alternatives for our BI application. We are using Mondrian/JPivot on a dataset with ~300 millions row, and several parent/child dimensions. We are running some 50 real life scenario (ie 50 differents cubes using parts of this dataset) and measuring user response time on a server with several stripped Sata3 SSD, 32 GB ram and a i7-3820 CPU @ 3.60GHz (6 cores). During the tests, Monetdb performed by far the worst of Postgresql, VectorWise and (obvioulsy) MonetDB. Things that take 10 seconds with Postgresql take 450 second with monetdb... This was not expected at all, so looking at the server, I saw that MonetDB was consuming all memory (why not), including swap : bad idea I think. I was expecting MonetDB to memory mapped files when needed, real memory, but not use swap as it it was real memory... So here is my question : with a (quite) big dataset, 32GB ram, is there anything I can do to help MonetDB behave according to expectancies ? (maybe I should use ulimit ?). Thanks for your input, Franck

4 10

Problem with Mtest.py and monetdb command
by Bingyi Cao 11 Jul '13

11 Jul '13

Hey, I am new to MonetDB. I installed MonetDB yesterday. And I run Mtest.py. The results are as below: *!ERROR: Testing FAILED SIGNIFICANTLY (18 out of 1854 tests failed)* * * * 40 out of 1854 tests could not be executed* * 1 out of 1854 tests produced slightly different output* * sql/test/BugTracker-2012/predicate_select.Bug-3090* * 17 out of 1854 tests produced SIGNIFICANTLY different output* * monetdb5/optimizer/inlineFunction* * monetdb5/optimizer/ifthencst* * monetdb5/tests/BugTracker/kunion-and-nil.Bug-1667* * sql/jdbc/tests/Test_PSsomeamount* * sql/jdbc/tests/Test_PSlargebatchval* * sql/jdbc/tests/Test_PSmanycon* * sql/jdbc/tests/Test_PSmetadata* * sql/jdbc/tests/Test_PSsqldata* * sql/jdbc/tests/Test_Rsqldata* * sql/jdbc/tests/BugConcurrent_sequences* * sql/test/BugTracker-2010/incomplete-utf8-sequence.Bug-2575* * sql/test/BugTracker-2012/conditions_when_for_triggers_do_not_work.Bug-2073* * sql/test/BugTracker-2012/create_function.Bug-3172* * sql/test/BugTracker-2012/currenttime.Bug-2781* * sql/test/BugTracker-2012/null_except_null.Bug-3040* * sql/test/BugTracker-2012/day-of-month-localization.Bug-2962* * sql/test/Triggers/recursive_triggers* * * * * * First, check the testing results in /usr/local/mTests/index.html !* * * * Then, fix the problems by:* * - fixing sources and test scripts* * - fixing stable output by hand* * - approving test output by Mapprove.py (cf. Mapprove.py -?)* * * * After that, re-run Mtest.* I hasn't change any source code yet. I am not sure whether I installed it correctly. Also now I have no problem with command monetdbd, mserver5, mclient. However, I cannot use command with monetdb, the error information is always as below: monetdb: cannot find a control socket, use -h and/or -p I did a little bit research, to me it seems that it should automatically find a socket. Tell me if I am wrong. I also try to use the option -h -P, which doesn't work still. Do I actually install it correctly I tried to uninstall and reinstall it,but it hasn't solve the problem at all. Thanks very much!

2 3

Re: Fw:how are user defined function arguments passed ?
by integrity 10 Jul '13

10 Jul '13

Thanks, it's working, I am so excited, thank you all Best regards! Meng ------------------ Original ------------------ From: "Martin Kersten"<Martin.Kersten(a)cwi.nl>; Date: Wed, Jul 10, 2013 05:23 PM To: "users-list"<users-list(a)monetdb.org>; Subject: Re: Fw:how are user defined function arguments passed ? Yes, PATTERNs and COMMANDs have different interfaces. regards, Martin On 7/10/13 10:41 AM, Lefteris wrote: > try on your mal definition toinstead of the keyword pattern, to use > the keyword command. Patterns have a different signature if i remember > correctly. > > On Wed, Jul 10, 2013 at 10:28 AM, integrity <357416268(a)qq.com> wrote: >> Thanks very much for your instant reply,Lefteris! >> >> I wrote a simple test UDF, but it doesnt work. >> >> myudf.c >> #include "monetdb_config.h" >> #include "myudf.h" >> >> str >> addint(int *ret,int *one) >> { >> >> *ret=(*one)+10; >> >> return MAL_SUCCEED; >> >> } >> ------------------------------------------------------------------------------------------------------------ >> myudf.h >> /* In your own module, replace "UDF" & "udf" by your module's name */ >> >> #ifndef _SQL_MYUDF_H_ >> #define _SQL_MYUDF_H_ >> #include "sql.h" >> #include <string.h> >> >> /* This is required as-is (except from renaming "UDF" & "udf" as suggested >> * above) for all modules for correctly exporting function on Unix-like and >> * Windows systems. */ >> >> #ifdef WIN32 >> #ifndef LIBMYUDF >> #define myudf_export extern __declspec(dllimport) >> #else >> #define myudf_export extern __declspec(dllexport) >> #endif >> #else >> #define myudf_export extern >> #endif >> >> /* export MAL wrapper functions */ >> >> myudf_export str addint(int *ret, int *one); >> #endif /* _SQL_MYUDF_H_ */ >> ----------------------------------------------------------------------------------------------------- >> myudf.mal >> # scalar MAL signatures >> module myudf; >> >> pattern addint(one:int):int >> address addint >> comment "add one and two"; >> ----------------------------------------------------------------------------------------------------- >> 82_myudf.sql >> create function addint(one INT) >> returns int external name myudf.addint; >> ----------------------------------------------------------------------------------------------------- >> when i execute in sql: >> sql>select addint(76777); >> +---------------------+ >> | addint_single_value | >> +=====================+ >> | 0 | >> +---------------------+ >> 1 tuple (1.364ms) >> sql> >> sql>select addint(100); >> +---------------------+ >> | addint_single_value | >> +=====================+ >> | 0 | >> +---------------------+ >> 1 tuple (0.295ms) >> sql> >> sql> >> sql> >> sql>select addint(101); >> +---------------------+ >> | addint_single_value | >> +=====================+ >> | 0 | >> +---------------------+ >> 1 tuple (0.289ms) >> sql>select addint(1010000000000); >> overflow in conversion of 1010000000000 to int. >> sql> >> sql>select addint(1010000); >> +---------------------+ >> | addint_single_value | >> +=====================+ >> | 0 | >> +---------------------+ >> 1 tuple (1.087ms) >> sql> >> >> i checked every format of above files many times, i cannot find what's wrong >> with my program... >> it seems that the parameter didn't got passed to MAL.... >> >> Thanks again for your help! >> >> Sincerely, >> >> Meng >> ------------------ Original ------------------ >> From: "Lefteris"<lsidir(a)gmail.com>; >> Date: Wed, Jul 10, 2013 04:13 PM >> To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; >> Subject: Re: Fw:how are user defined function arguments passed ? >> >> The str *ret is you return value as described in the MAL signature >> reverse(ra1:str):str >> >> the ra1 is the name of a str variable in MAL, which is mapped to the >> str *src variable of the UDF. Naming here plays no role, just the >> types and the order of definition. Similarly, the :str end of the mal >> signature reverse(ra1:str):str means that it returns a str type, which >> in turn is mapped to the str *ret variable of the UDF signature. >> Naming plays no role again, just that the first variable of the >> signature in the udf is the return variable. Ofcourse it does not need >> to be always a str, it depends on the definition of the mal signature. >> >> On Wed, Jul 10, 2013 at 9:59 AM, integrity <357416268(a)qq.com> wrote: >>> Could anyone explain this question to me, please? >>> >>> I am still wondering on it.... >>> >>> Thanks! >>> >>> Meng >>> ------------------ Original ------------------ >>> From: "integrity"<357416268(a)qq.com>; >>> Date: Fri, Jul 5, 2013 03:42 PM >>> To: "users-list"<users-list(a)monetdb.org>; >>> Subject: how are user defined function arguments passed ? >>> >>> Hi everyone, >>> >>> In >>> >>> http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/UserDefinedFuncti… >>> In thesection about how to extend SQL with a simple scalar function to >>> reverse a string >>> >>> step 3. Extension starts with a definitin of the MAL signatures. See the >>> example given, or browse through the files in monetdb5/modules/mal/*.mal >>> to >>> get a glimpse on how to write them. The MonetDB kernel documentation >>> provides more details. The file contains the MAL snippet: >>> command reverse(ra1:str):str >>> address UDFreverse >>> comment "Reverse a string"; >>> >>> step 4. The signature says that it expects a command body implementation >>> under the name UDFreverse, shown below. The C-signature is a direct >>> mapping, >>> where arguments are passed by reference and the return value(s) >>> references >>> are the first in the arguments list. The body should return a (malloced) >>> string to denote an exception being raised or MAL_SUCCEED upon access. >>> #include "udf.h" >>> >>> static str >>> reverse(const char *src) >>> { >>> size_t len; >>> str ret, new; >>> >>> /* The scalar function returns the new space */ >>> len = strlen(src); >>> ret = new = GDKmalloc(len + 1); >>> if (new == NULL) >>> return NULL; >>> new[len] = 0; >>> while (len > 0) >>> *new++ = src[--len]; >>> return ret; >>> } >>> >>> str >>> UDFreverse(str *ret, str *src) >>> { >>> if (*src == 0 || strcmp(*src, str_nil) == 0) >>> *ret = GDKstrdup(str_nil); >>> else >>> *ret = reverse(*src); >>> return MAL_SUCCEED; >>> } >>> >>> >>> I don't know why the argument name of "command reverse(ra1:str):str" >>> different from function definition UDFreverse(str *ret, str *src)? >>> >>> how does ret come from? is it according to the naming rules that the first >>> argument is the result, must it be a string type? >>> >>> Thanks! >>> >>> Meng >>> >>> >>> _______________________________________________ >>> users-list mailing list >>> users-list(a)monetdb.org >>> http://mail.monetdb.org/mailman/listinfo/users-list >>> >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> http://mail.monetdb.org/mailman/listinfo/users-list >> . >> >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> http://mail.monetdb.org/mailman/listinfo/users-list >> > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/users-list > _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list .

1 0

Re: Fw:how are user defined function arguments passed ?
by integrity 10 Jul '13

10 Jul '13

Thanks very much for your instant reply,Lefteris! I wrote a simple test UDF, but it doesnt work. myudf.c #include "monetdb_config.h" #include "myudf.h" str addint(int *ret,int *one) { *ret=(*one)+10; return MAL_SUCCEED; } ------------------------------------------------------------------------------------------------------------ myudf.h /* In your own module, replace "UDF" & "udf" by your module's name */ #ifndef _SQL_MYUDF_H_ #define _SQL_MYUDF_H_ #include "sql.h" #include <string.h> /* This is required as-is (except from renaming "UDF" & "udf" as suggested * above) for all modules for correctly exporting function on Unix-like and * Windows systems. */ #ifdef WIN32 #ifndef LIBMYUDF #define myudf_export extern __declspec(dllimport) #else #define myudf_export extern __declspec(dllexport) #endif #else #define myudf_export extern #endif /* export MAL wrapper functions */ myudf_export str addint(int *ret, int *one); #endif /* _SQL_MYUDF_H_ */ ----------------------------------------------------------------------------------------------------- myudf.mal # scalar MAL signatures module myudf; pattern addint(one:int):int address addint comment "add one and two"; ----------------------------------------------------------------------------------------------------- 82_myudf.sql create function addint(one INT) returns int external name myudf.addint; ----------------------------------------------------------------------------------------------------- when i execute in sql: sql>select addint(76777); +---------------------+ | addint_single_value | +=====================+ | 0 | +---------------------+ 1 tuple (1.364ms) sql> sql>select addint(100); +---------------------+ | addint_single_value | +=====================+ | 0 | +---------------------+ 1 tuple (0.295ms) sql> sql> sql> sql>select addint(101); +---------------------+ | addint_single_value | +=====================+ | 0 | +---------------------+ 1 tuple (0.289ms) sql>select addint(1010000000000); overflow in conversion of 1010000000000 to int. sql> sql>select addint(1010000); +---------------------+ | addint_single_value | +=====================+ | 0 | +---------------------+ 1 tuple (1.087ms) sql> i checked every format of above files many times, i cannot find what's wrong with my program... it seems that the parameter didn't got passed to MAL.... Thanks again for your help! Sincerely, Meng ------------------ Original ------------------ From: "Lefteris"<lsidir(a)gmail.com>; Date: Wed, Jul 10, 2013 04:13 PM To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; Subject: Re: Fw:how are user defined function arguments passed ? The str *ret is you return value as described in the MAL signature reverse(ra1:str):str the ra1 is the name of a str variable in MAL, which is mapped to the str *src variable of the UDF. Naming here plays no role, just the types and the order of definition. Similarly, the :str end of the mal signature reverse(ra1:str):str means that it returns a str type, which in turn is mapped to the str *ret variable of the UDF signature. Naming plays no role again, just that the first variable of the signature in the udf is the return variable. Ofcourse it does not need to be always a str, it depends on the definition of the mal signature. On Wed, Jul 10, 2013 at 9:59 AM, integrity <357416268(a)qq.com> wrote: > Could anyone explain this question to me, please? > > I am still wondering on it.... > > Thanks! > > Meng > ------------------ Original ------------------ > From: "integrity"<357416268(a)qq.com>; > Date: Fri, Jul 5, 2013 03:42 PM > To: "users-list"<users-list(a)monetdb.org>; > Subject: how are user defined function arguments passed ? > > Hi everyone, > > In > http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/UserDefinedFuncti… > In thesection about how to extend SQL with a simple scalar function to > reverse a string > > step 3. Extension starts with a definitin of the MAL signatures. See the > example given, or browse through the files in monetdb5/modules/mal/*.mal to > get a glimpse on how to write them. The MonetDB kernel documentation > provides more details. The file contains the MAL snippet: > command reverse(ra1:str):str > address UDFreverse > comment "Reverse a string"; > > step 4. The signature says that it expects a command body implementation > under the name UDFreverse, shown below. The C-signature is a direct mapping, > where arguments are passed by reference and the return value(s) references > are the first in the arguments list. The body should return a (malloced) > string to denote an exception being raised or MAL_SUCCEED upon access. > #include "udf.h" > > static str > reverse(const char *src) > { > size_t len; > str ret, new; > > /* The scalar function returns the new space */ > len = strlen(src); > ret = new = GDKmalloc(len + 1); > if (new == NULL) > return NULL; > new[len] = 0; > while (len > 0) > *new++ = src[--len]; > return ret; > } > > str > UDFreverse(str *ret, str *src) > { > if (*src == 0 || strcmp(*src, str_nil) == 0) > *ret = GDKstrdup(str_nil); > else > *ret = reverse(*src); > return MAL_SUCCEED; > } > > > I don't know why the argument name of "command reverse(ra1:str):str" > different from function definition UDFreverse(str *ret, str *src)? > > how does ret come from? is it according to the naming rules that the first > argument is the result, must it be a string type? > > Thanks! > > Meng > > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/users-list > _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list .

3 2

Fw:how are user defined function arguments passed ?
by integrity 10 Jul '13

10 Jul '13

Could anyone explain this question to me, please? I am still wondering on it.... Thanks! Meng ------------------ Original ------------------ From: "integrity"<357416268(a)qq.com>; Date: Fri, Jul 5, 2013 03:42 PM To: "users-list"<users-list(a)monetdb.org>; Subject: how are user defined function arguments passed ? Hi everyone, In http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/UserDefinedFuncti… In thesection about how to extend SQL with a simple scalar function to reverse a string step 3. Extension starts with a definitin of the MAL signatures. See the example given, or browse through the files in monetdb5/modules/mal/*.mal to get a glimpse on how to write them. The MonetDB kernel documentation provides more details. The file contains the MAL snippet: command reverse(ra1:str):str address UDFreverse comment "Reverse a string"; step 4. The signature says that it expects a command body implementation under the name UDFreverse, shown below. The C-signature is a direct mapping, where arguments are passed by reference and the return value(s) references are the first in the arguments list. The body should return a (malloced) string to denote an exception being raised or MAL_SUCCEED upon access. #include "udf.h" static str reverse(const char *src) { size_t len; str ret, new; /* The scalar function returns the new space */ len = strlen(src); ret = new = GDKmalloc(len + 1); if (new == NULL) return NULL; new[len] = 0; while (len > 0) *new++ = src[--len]; return ret; } str UDFreverse(str *ret, str *src) { if (*src == 0 || strcmp(*src, str_nil) == 0) *ret = GDKstrdup(str_nil); else *ret = reverse(*src); return MAL_SUCCEED; } I don't know why the argument name of "command reverse(ra1:str):str" different from function definition UDFreverse(str *ret, str *src)? how does ret come from? is it according to the naming rules that the first argument is the result, must it be a string type? Thanks! Meng

2 1