- users-list - monetdb.org

COPY INTO robust to empty row at end of CSV
by László Sándor 19 May '14

19 May '14

Hi all, Hannes Mühleisen (cc'd) generously shared his homepages.cwi.nl/~hannes/importer.py Python script with me last week, which automates the creation of tables from text files: Python's csv-reader helps recognize the dialect of the text file as well as grab the header for column names and identify the types for the columns, and then a single COPY %i OFFSET %i RECORDS INTO %s FROM \'%s\' USING DELIMITERS … is issued to MonetDB (see line 404 of the script). I had some issues with this over the last three days, but now I see the source of the error I get: There is nothing wrong with Hannes's Python code but when COPY INTO turns back to the text file, MonetDB still complains about the last row containing an "incomplete record" (it is empty) even if Python was smart enough not to count that row into the number of records beforehand. Is there a way to call COPY INTO that is robust to the text file ending in an extra empty row? It is common with some generated CSV or TSV files. By the way, I also have a follow-up about how to merge some of the tables together. I am happy to work on this, of course, I don't expect anyone to script this for me, but I am confused about how much I can use SQL variables for this (and how) versus how much I need to use another language to script the repeated calls to MonetDB about each year for each series of tables (I have dozens of dozens). I hope this is a reasonably well-posed question: http://dba.stackexchange.com/questions/65329/dynamic-sql-merge-partitioned-… Thanks for any help, Laszlo

2 7

type recognition, *creating* tables from CSVs
by László Sándor 15 May '14

15 May '14

Hi, I asked a question on DBA of StackExchange about MonetDB, but as there is not even a monetdb tag in use, maybe it wasn't the right forum for it. Please have a look over there, or I can repeat it below: http://dba.stackexchange.com/q/65126/38399 I am confused whether you can copy records into a *new* table in MonetDB. The official documentation seems[1] to talk only about copying into existing tables. I have quite a large universe of billions of records in dozens of tables, and I would much prefer a solution that inferred the type from the CSV itself, and the column names from the first rows. A StackOverflow answer[2] seems to work for SQL Server 2000 with `OpenRowset`, though even they are not concerned about data-type inferencing. Another answer[3] here on DBA recommends `BCP` before calling `BULK INSERT` in Server 2000, neither of which is discussed in the MonetDB documentation. Other answers[4] also suggest that this is impossible in MySQL without external scripts. The background of my issues are that I realized that managing my raw data (which came in text files from government agencies, with scarce documentation) should be separate from the front-end of my analysis still kept in Stata. This is similar to what has been achieved with MonetDB.R (e.g. hannes.muehleisen.org/SSDBM2013-databases-and-statistics.pdf) though probably limited to an odbc link (so missing some of the real benefits coming from merging and calculations done in MonetDB). That said, this is the only sign of a Stata and MonetDB link I could find: http://www.stata.com/statalist/archive/2012-08/msg01363.html Disclaimer: I am an SQL and MonetDB newbie, but thanks for not letting me miss the obvious. [1]: https://www.monetdb.org/Documentation/Manuals/SQLreference/CopyInto [2]: http://stackoverflow.com/a/10421034/938408 [3]: http://dba.stackexchange.com/a/22763/38399 [4]: http://dba.stackexchange.com/a/61969/38399

3 5

SELECT failure: "insufficient space for HEAP ..."
by Clarkson, Edward C. 14 May '14

14 May '14

Hi, We have an application (http://tmt.gtri.gatech.edu/lytic.html) based on a relatively modest, single (~1-10M row, ~20 column) MonetDB table (Win7, 64 bit, Jan14-SP1 server release, quad-core, 8GB RAM) and have been encountering sporadic SELECT query failures with the error: "Insufficient space for HEAP of 1017856 bytes". Since I've been trying in earnest to diagnose this, that byte value has been consistent, FWIW. The failures are frustratingly intermittent; if I rerun a failing query (by hand, using mclient) it sometimes fails again, and sometimes works correctly. I've been unable to figure out any sort of pattern. Unfortunately, I can't post the exact table and query, but the general form of the queries (which have ~5-10 case clauses) are: SELECT CASE WHEN Foo = 'ValueA' and Bar LIKE 'ValueB%' THEN 100*(Baz-1234)/1234 WHEN Foo = 'ValueC' and Bar LIKE 'ValueD%' THEN 100*(Baz-3456)/3456 ... END FROM table WHERE <same case statement as above> IS NOT NULL LIMIT 1 Any ideas what I might do to narrow this down or other information I might provide? I captured an explain of a failing query--it's many hundreds (1000s?) of lines, so I didn't want to include it here in full. All suggestions appreciated. Thanks, Ed P.S. MonetDB has been a fantastic complement to our application, thanks to everyone involved!

2 3

thank you for monetdb
by Anthony Damico 02 May '14

02 May '14

i love it this much: http://2.bp.blogspot.com/-ogFP6EHJ6h8/U1E225vANvI/AAAAAAAAww8/9wOEp95iekk/s…

2 1

why the same procedure (in shell or in sql) cost so different time?
by integrity 30 Apr '14

30 Apr '14

Hi All, I want to execute a batch of small sql stored procedures, for convenience, i put them totally in another big stored procedure: gwac_uniquecatalog, it cost 58 minutes: #!/bin/bash time mclient -d mydb -s "CALL gwac_uniquecatalog(99);" real 58m3.518s The time is so long that I tried to execute the small stored procedures one by one in SHELL, it only cost 6.5 minutes /usr/bin/time ./gwac_uniquecatalog.sh 99 2.82user 3.25system 6:30.39elapsed 1%CPU (0avgtext+0avgdata 10880maxresident)k 0inputs+0outputs (0major+980011minor)pagefaults 0swaps my question is why did this dramatic difference happen and how to reduce the time of big stored procedure? =========================================================== the content of the big stored procedure: gwac_uniquecatalog is CREATE PROCEDURE gwac_uniquecatalog(imgid int) BEGIN DECLARE imgid_t int; SET imgid_t =1; CALL insert_1_to_1_assoc(imgid_t); --对第一幅图 only insert into assoc is enough SET imgid_t = imgid_t +1; WHILE (imgid_t<=imgid) DO CALL insert_tempuniquecatalog(imgid_t,10.0); --10 is match radius 对第二幅图及以后的图 CALL find_n_to_m(); CALL insert_1_to_n_unique(); CALL insert_new_1_to_n_assoc(); CALL insert_1_to_n_assoc(); CALL delete_1_to_n_inactive_assoc(); CALL flag_1_to_n_inactive_uniq(); CALL flag_1_to_n_inactive_tempuniq(); CALL insert_1_to_1_assoc(imgid_t); CALL update_1_to_1_uniq(); CALL insert_new_uniq(imgid_t); CALL insert_new_assoc(imgid_t); SET imgid_t = imgid_t +1; END WHILE; END; =========================================== the SHELL code is: #!/bin/bash imgid_t=1 /usr/bin/time -f %e mclient -d mydb -s "CALL insert_1_to_1_assoc(imgid_t -le $1 ] do /usr/bin/time -f %e mclient -d mydb -s " CALL insert_tempuniquecatalog($imgid_t,10.0);" mclient -d mydb -s " CALL find_n_to_m();" mclient -d mydb -s " CALL insert_1_to_n_unique(); " mclient -d mydb -s " CALL insert_new_1_to_n_assoc();" mclient -d mydb -s " CALL insert_1_to_n_assoc();" mclient -d mydb -s " CALL delete_1_to_n_inactive_assoc();" mclient -d mydb -s " CALL flag_1_to_n_inactive_uniq();" mclient -d mydb -s " CALL flag_1_to_n_inactive_tempuniq();" mclient -d mydb -s " CALL insert_1_to_1_assoc($imgid_t);" mclient -d mydb -s " CALL update_1_to_1_uniq();" mclient -d mydb -s " CALL insert_new_uniq($imgid_t);" mclient -d mydb -s " CALL insert_new_assoc(imgid_t ((++imgid_t)) done Thanks very much! Best regards, Meng

4 8

procedure to follow when changing the sql-optimizer settings
by Mahmut Uludag 25 Apr '14

25 Apr '14

Hi, We were getting database crashes when we were using our database with read/write mode and using the default sql_optimizer settings. We decided to run our database in read-only mode and also has changed the sql_optimizer setting to sequential_pipe. With new settings we don't see any crashes but have started to see error messages in the log files similar to the following lines. 2014-04-25 08:21:52 ERR db1[14554]: #mvc_bind_schema schema1 2014-04-25 08:21:52 ERR db1[14554]: #mvc_bind_table schema1.table1 2014-04-25 08:21:52 ERR db1[14554]: #mvc_bind_column table1.col1 2014-04-25 08:21:52 ERR db1[14554]: #mvc_rollback 2014-04-25 08:21:52 ERR db1[14554]: #mvc_rollback done 2014-04-25 08:21:52 ERR db1[14554]: #mvc_commit 2014-04-25 08:21:52 ERR db1[14554]: #mvc_commit done My question is there a procedure to follow when we change sql-optimizer settings? I assume MonetDB has some history of the past and because of the change of the sql_optimizer settings the bits in the history causing these error messages but i would not be surprised if this is completely wrong. Mahmut Uludag

3 4

concurrency and use of temporary tables
by Niall Rooney 23 Apr '14

23 Apr '14

Hello, I am running a java program which connects to a MonetDB database and runs a thread where each thread creates a number of local temporary tables using the and populates these tables in certain data processing steps. On termination the temporary tables are deleted. These temporary tables are utilised in a number of select statements in combination with the tables in the database. Tables are created using a table name where a UUID is generated for each thread so the use of local tables is unique to the thread e.g. create local temporary table tempquerywords0e5a0e08c1d24cf6b9529426463b6191(word_word varchar(255)) ON COMMIT PRESERVE ROWS. All needed temporary tables are created by the thread as an initial process. The connection to the database is set with autoCommit set to true. If each thread is run sequentially , there is no problem. However if I run the process concurrently with a maximum of 2 concurrent threads, some threads terminate early with an Exception message e.g. nested exception is java.sql.SQLException: SELECT: no such table 'tempweightedclustersa7f7f47e7c184175ac8ab5ee2dc76a03'. This message is confusing as the table is populated ok before the select. Although other participants have posted messages concerning problems based on concurrent updates and temporary tables , is there an issue concerning concurrent reads and temporary tables? Best Regards Niall

1 0

TPC-H in MALs
by Qing Guo 23 Apr '14

23 Apr '14

Hi, I was trying to run “mserver” (standalone) in a simulator to do some performance study. As mserver only reads MALs, are there any ready-to-use MAL scripts for TPC-H? I tried to use “explain” in “mclient”, but the translated MALs seem to be too detailed and cannot simply copy and run. Any idea on this? Thank you! -Kevin

2 2

Build for Ubuntu 14.04 "Trusty Tahr"?
by Robert Kosara 19 Apr '14

19 Apr '14

Hi, I see Ubuntu 14.04 (which was just officially released a few days ago) listed on the Ubuntu/Debian packages list: http://dev.monetdb.org/downloads/deb/ However, the “trusty” directory doesn’t exist, and I can’t install MonetDB through apt-get. Will that build be enabled in the near future? Thanks, Robert

2 1

Overflow calculation in tpch Q1
by Tsai Li Ming 17 Apr '14

17 Apr '14

Hi, I’m getting an overflow calculation when SF=100 for Q1. The rest of the queries are fine. There’s an old thread on this from Sep 2013: https://www.monetdb.org/pipermail/users-list/2013-September/006817.html I’m using 11.17.13. Will this be fixed soon? Thanks!

1 0