February 2012 - users-list

[MonetDB-users] select distinct ... where algorithm
by zack_pcd 13 Feb '12

13 Feb '12

I am seeing the following: select count(*) from f_sorted; +-----------+ | L7 | +===========+ | 102612097 | +-----------+ 1 tuple (0.988ms) select count(*) from f_sorted where yyyymm='201110'; +----------+ | L10 | +==========+ | 23505343 | +----------+ 1 tuple (1.0s) select count(distinct uuid) from f_sorted where yyyymm='201110'; +---------+ | L6 | +=========+ | 5941581 | +---------+ 1 tuple (23.8s) Very fast thus far! However: copy (select distinct(uuid) from f_sorted where yyyymm='201110') into '/home/user/filename' USING DELIMITERS ',','\n','"' NULL as 'null'; This has been running for hours and has yet to start writing to the file. I get this when I explain the query: +----------------------------------------------------------------------------------------+ | mal | +========================================================================================+ | function user.s5_3{autoCommit=true}():void; | | X_2 := sql.mvc(); | | X_22:bat[:oid,:str] := sql.bind(X_2,"sys","f_sorted","yyyymm",0); | | X_23 := algebra.uselect(X_22,"201110":str); | | X_20:bat[:oid,:str] := sql.bind(X_2,"sys","f_sorted","yyyymm",2); | | X_24 := algebra.kdifference(X_23,X_20); | | X_21 := algebra.uselect(X_20,"201110":str); | | X_25 := algebra.kunion(X_24,X_21); | | X_18:bat[:oid,:str] := sql.bind(X_2,"sys","f_sorted","yyyymm",1); | | X_19 := algebra.uselect(X_18,"201110":str); | | X_26 := algebra.kunion(X_25,X_19); | | X_16:bat[:oid,:oid] := sql.bind_dbat(X_2,"sys","f_sorted",1); | | X_17 := bat.reverse(X_16); | | X_27 := algebra.kdifference(X_26,X_17); | | X_28 := algebra.markT(X_27,0@0:oid); | | X_29 := bat.reverse(X_28); | | X_10:bat[:oid,:str] := sql.bind(X_2,"sys","f_sorted","uuid",0); | | X_8:bat[:oid,:str] := sql.bind(X_2,"sys","f_sorted","uuid",2); | | X_12 := algebra.kdifference(X_10,X_8); | | X_13 := algebra.kunion(X_12,X_8); | | X_5:bat[:oid,:str] := sql.bind(X_2,"sys","f_sorted","uuid",1); | | X_14 := algebra.kunion(X_13,X_5); | | X_30 := algebra.leftjoin(X_29,X_14); | | (ext44,grp42) := group.done(X_30); | | X_33 := bat.mirror(ext44); | | X_34 := algebra.leftjoin(X_33,X_30); | | X_35 := sql.resultSet(1,1,X_34); | | sql.rsColumn(X_35,"sys.f_sorted","uuid":str,"varchar",2048,0,X_34); | | X_43 := io.stdout(); | | sql.exportResult(X_43,X_35); | | end s5_3; | +----------------------------------------------------------------------------------------+ 31 tuples (1.350ms) Is it doing something wrong? I don't expect the query to take so long (hours) to produce the output, especially since the distinct count is also very fast. The copy-into *seems* to be irrelevant to the problem, but I decided to include it just in case, as that's how I am using the query. -- View this message in context: http://old.nabble.com/select-distinct-...-where-algorithm-tp33297578p332975… Sent from the monetdb-users mailing list archive at Nabble.com.

4 6

[MonetDB-users] False dependency
by Stathis Gkotsis 10 Feb '12

10 Feb '12

Hi all, trying to install: libmonetdb-client-dev_11.7.5-20120120_amd64.deb on Ubuntu 10.04, I see that there is a wrong dependency on libmonetdb-client1. Is there a way around this? Thank you.

1 0

[MonetDB-users] re cycler_pipe and cracker_pipe
by zack_pcd 08 Feb '12

08 Feb '12

Is it possible to have a mix of these pipe's optimizers so you get the benefit of both? Or are these two pipes incompatible? (recycler_pipe and cracker_pipe) -- View this message in context: http://old.nabble.com/recycler_pipe-and-cracker_pipe-tp33282420p33282420.ht… Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] LIMIT clause not recognized in nested query in FROM clause
by Viktor Rosenfeld 07 Feb '12

07 Feb '12

Hi, it appears that clauses like LIMIT or OFFSET are not allowed if the query is used inside a FROM clause. The following query throws an error: SELECT * FROM ( SELECT id FROM corpus LIMIT 10 ) AS inner_table; The error message is: syntax error, unexpected LIMIT, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * Cheers, Viktor

1 0

[MonetDB-users] How can I execute SQL via mclient directly?
by bai dongli 02 Feb '12

02 Feb '12

Hi guys, I'm pretty new for Monetdb, As per the introduce of the online document, I have to type the password on the command line window when I want to execute SQL via the mclient program. It there a way like this which can be used in Mysql: "mysql --user --password databasename -e SQL" in monetdb? I didn't found the argument that can used for the "password" in mclient. Thanks, Tony

2 4

[MonetDB-users] Can't drop "global temporary table" in SCHEMA tmp
by bai dongli 02 Feb '12

02 Feb '12

Hi guys, I got a problem when I trying to drop a global temporary table in my database, I got following errors: sql>drop table tmp.test; 42S02!DROP TABLE: no such table 'test' I got the same error as above when I trying to execute a "select" query on that table, It's so weird, cause the table "test" was been there: sql>\d TABLE tmp.test The even more weird things happened when I execute following command: sql>create global temporary table tmp.test(id int); operation successful (1.888ms) sql>\d TABLE tmp.test sql>\d test table tmp.test is not unique, corrupt catalog? sql>\d TABLE tmp.test sql>drop table tmp.test more>; operation successful (1.470ms) sql>\d TABLE tmp.test Please pay attention on this line "table tmp.test is not unique, corrupt catalog?". Do anybody can help me out of here? Thanks, Tony

1 0

[MonetDB-users] Batch sql inserts in monetdb
by Federico Dayan 01 Feb '12

01 Feb '12

Hello all, I'm working in a project and we are planning to use monetdb. Our Data: We have around 15 millon users and 6,000 columns. Our Problem We need to be able to query specific log data. We use Hadoop to massage the data and create a sql file with sql inserts (around 15 millon insert with 3000 columns) Out Question Is it efficent to run a command like. By efficent I refe:. betweent 1 to 6 hours efficent, after that, a big problem. monetdb -u ....... < my_sql_insert.sql with 15 millon inserts and between 1000 to 3000 columns? (I'm not always going to create insert for the 6000 columns) could monetdb handle multiple of those command if a split the file. monetdb -u ....... < my_sql_insert_1.sql monetdb -u ....... < my_sql_insert_2.sql monetdb -u ....... < my_sql_insert_3.sql ... Is SQL COPY more efficient than a sql scrip file with inserts? BAT files is it significant faster ? I'm trying to avoid this approach since it seems a little bit obscue. any other suggestion? I'm not able to run all this test and get the info myself becuase I will not have a monetdb ready soon. Thank you very much and I would be glad to send back an email with the results I got and contribute in that way. Federico

4 3