December 2007 - users-list

[MonetDB-users] SQL Script execution behaviour
by Venks 08 Dec '07

08 Dec '07

Please find attached 2 scripts namely "test1.sql" and "test2.sql". 1. First execute the script "test1.sql". You will get an error for each and every statement in the script and that's fine. 2. Execute the second script "test2.sql" which will create five simple tables and then execute "test1.sql" again. You will see that the "test1.sql" script executes fine even though it should report errors after the last DELETE statement. In short, it looks like the script stops executing after the first successful SQL statement. It should continue to report errors for all the COPY commands. Thanks,

2 1

[MonetDB-users] Enable/Disable auto-commit
by Venks 08 Dec '07

08 Dec '07

How do I enable/disable auto-commit using "mclient"? Does \A and \a still work? Thanks,

3 2

[MonetDB-users] MonetDB and fast data storage
by Djordje Djokovic 07 Dec '07

07 Dec '07

2 1

[MonetDB-users] Problem with (Tijah) search query for StreetTiVo
by Ying Zhang 07 Dec '07

07 Dec '07

Hello all, When executing the attached search query in the streettivo bobo collection, I get the following errors: $ mclient -lx -p 60000 a.xq MAPI = monetdb@localhost:60000 QUERY = import module namespace stvc = "http://monetdb.cwi.nl/streettivo/search " at "/export/scratch0/streettivo/streettivo/mxq/search.xq"; stvc:search("bobo camperen") ERROR = !ERROR: interpret_params: reverse(param 1): invalid BAT. !ERROR: interpret_params: exist(param 1): evaluation error. !ERROR: interpret_params: not(param 1): evaluation error. $ cat a.xq import module namespace stvc = "http://monetdb.cwi.nl/streettivo/search" at "/export/scratch0/streettivo/streettivo/mxq/search.xq"; stvc:search("bobo camperen") The function "search()" is implemented as the following (see also the attachment): 1. module namespace stvc = "http://monetdb.cwi.nl/streettivo/search"; 2. declare namespace tijah = 'http://dbappl.cs.utwente.nl/pftijah/'; 3. stvc:search($keywords as xs:string) as node()* { 4. let $config := doc("config.xml") 5. (: number of programs to be returned :) 6. let $numPrograms := exactly-one($config/stv-config/nr-programs/ text()) 7. (: number of fragments to be returned per program :) 8. let $numFrags := exactly-one($config/stv-config/nr-fragments/text()) 9. (: number of sentences to be ranked by TIJAH :) 10. let $numSentences := exactly-one($config/stv-config/nr-sentences/ text()) 11. let $query := tijah:tokenize($keywords) 12. let $nexi := if ($query) then 13. concat("//(asr|teletext)[about(.,", $query, ")]// sentence[about(.,", $query, ")]") 14. else "//sentence[about(., asdkjasdkj)]" (: in case query is only a stop word :) 15. let $opt := <TijahOptions ft-index="SpeechOut" returnNumber="{$numSentences}" /> 16. let $res := tijah:queryall-id($nexi,$opt) 17. let $nodes := tijah:nodes($res) 18. let $resultlist := 19. for $pid in distinct-values ($nodes/../@progID) 20. let $sentences := 21. for $s in $nodes[../@progID = $pid] 22. order by tijah:score($res, $s) descending 23. return $s 24. let $best := $sentences[1] 25. let $speech := $best/.. 26. let $pscore := tijah:score($res, exactly-one($best)) 27. order by $pscore descending 28. return element program { 29. attribute thumbnail-image {$speech/@thumbnail_image}, 30. attribute video-file {$speech/@video-file}, 31. attribute startDT {$speech/@startDT}, 32. attribute channel {$speech/@channel}, 33. attribute title {$speech/@title}, 34. attribute progID {$pid}, 35. attribute score {$pscore}, 36. subsequence( 37. for $fragment in $sentences 38. return element fragment { 39. $fragment/@*, 40. $fragment/text() }, 41. 1, $numFrags) } 42. return 43. subsequence($resultlist, 1, $numPrograms) (: first result page :) 44. }; The problem starts in lines 16 and 17. It feels like that the error will occur, as soon as the return value of the function "tijah:queryall-id" or "tijah:nodes" is used. Above error occurs, if I - query the original function, or - skip lines 17 - 43 and return <res>{$res}</res> - skip lines 18 - 43 and return $nodes I use the latest stable version, compiled using the monetdb-install.sh script as the following: $ monetdb-install.sh --prefix=$STV_HOME/monetdb --build=$STV_TEMP/ monetdb_build --enable-xquery --nightly=stable --enable-debug System info: $ uname -a Linux andorea.ins.cwi.nl 2.6.20-1.2933.fc6 #1 SMP Mon Mar 19 11:00:19 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux Would some Tijah experts please have a lot at this problem? I'm not sure if the problem is in the Tijah functions or somewhere else in MonetDB/XQuery. Thanks a lot! Regards, Jennie

1 0

[MonetDB-users] COPY with AUTO_INCREMENT column
by Venks 07 Dec '07

07 Dec '07

Hi, How can I use COPY to load data into a table with a AUTO_INCREMENT column? Just to clarify, the AUTO_INCREMENT column would be the PRIMARY KEY by default and doesn't have any corresponding data in the data file. Thanks,

3 3

[MonetDB-users] COPY - performing SQL processing while COPYing
by Venks 07 Dec '07

07 Dec '07

Hi, Is there are way I can perform some SQL processing while using COPY to load the data? For e.g. I need to load a file into a table but would like to TRIM all the spaces at the beginning and at the end of each column for all VARCHAR Columns. Below is an example in MySQL where I can load the data into a variable first and then load the variable into an actual column. LOAD DATA .... ( @col1_temp, @col2_temp, @col3_temp ) SET col1 = TRIM(@col1_temp), SET col2 = TRIM(@col2_temp), SET col3 = TRIM(@col3_temp) @<variable> stores the initial value of each field and then you can use most of the default SQL functions on the variable before inserting the data. The advantage here is the ability to perform some basic transformations on the data to adhere to the column definitions instead of loading into a temp table and then performing the transformations to load into the actual table. This really helps when loading billions of rows and 100G worth of data. Thanks,

2 1

Re: [MonetDB-users] JDBC driver, where? (sorry)
by Martin Koch 07 Dec '07

07 Dec '07

Thanks all of you for helping me along with my MonetDB testing environment. My today's build seems to contain more of the parts mentioned in the documentation. I could not test sooner due to some typical Dutch festivity involving a crazy horseman in red riding on my roof. Kind regards, Martin

2 1

[MonetDB-users] XQuery Update Last Call
by Florian Wendland 07 Dec '07

07 Dec '07

Hi, a question concerning XQuery Update Facility. The Last Call is not supported by MonetDB at this time. Are there plans to implement it in the near future or will this be skipped, until the final recommendation is published? Thx mfw -- Marc-Florian Wendland Dipl.-Inform. (FH) http://www.florianwendland.de

2 1

[MonetDB-users] Changing default monetdb password
by Venks 06 Dec '07

06 Dec '07

How can I change the default MonetDB password? ALTER USER on "monetdb" gives me the following error: !InvalidCredentialsException:setPassword:The administrator cannot set its own password, use changePassword instead Thanks, -Venks

3 17

[MonetDB-users] Profiling queries
by m h 06 Dec '07

06 Dec '07

Hello all- First of all thanks to you guys for your great help these past few weeks. I really appreciate it. I've learned a bit, and hopefully your product is improving from my bug reports. I've managed to pull a bit of my data from my proprietary db into monetdb. (Have some 60 million rows in the fact table, pulled in 5 million)! I even ran a query against it and got the same numbers (though in a different order)! So I did a very basic poor mans benchmark. My python code can hit the old db or monetdb by changing one line. So I took timestamps around the query execution portion. The old db takes 0.21 seconds when cold. Subsequent queries take 0.027-0.072 seconds Monetdb takes 0.67 seconds when cold and 0.12-0.23 otherwise. It appears that the old db is faster when cold than monetdb is after being primed. A few more caveats. (Yes, this is a poor benchmark but after reading pdfs saying that monetdb was 7 times faster than a 64 way oracle box, I thought I'd get quite a bit of speedup.) The old db is running under vmware on a Core2 1.86Ghz box. Monetdb is running on my laptop, a Pentium M 1.86 Ghz machine. Since the query is confidential I can't really put it here, but I can describe it. I'm pulling stuff out of my fact table and two other dimension tables. The select clause sums a few columns, and on one sums the product of one column and the sum of a few others. The coalesce function is used to convert NULLs to 0. The where statement joins the tables using the = operator and then has two "IN" clauses. Then there is a group by (since I'm aggregating in the select) and an order by. Any hints on how to increase performance? Do I need to tune my query or tweak other things? (Yes I know the laptop machine is not apples to apples to the vmware machine) Am open to suggestions. thanks much, -matt

3 17