May 2014 - users-list

COPY INTO on Windows
by László Sándor 21 May '14

21 May '14

Hi, I investigated further, and actually I cannot get any CSV file working with monetDB's COPY INTO on Windows. Hannes filed a bug report today: https://www.monetdb.org/bugzilla/show_bug.cgi?id=3484 However, I tried other things since his/our attempts, e.g. removing the final end-of-line (EOL, still CRLF on Windows) or switching EOL to LF only for the temporary file (and changing the COPY INTO call's delimiters too, of course). Neither helped. Still the same error: Incomplete record at end of file. (Which I am not sure whether it literally means the end of the source textfile or simply the last line before breaking.) Basically, the question is, is there a way to get this working on Windows in Jan14-SP1? Or could someone please verify it's worth downgrading to a previous because it definitely can do COPY INTO? Thanks! Laszlo

2 3

vacuum safe to use
by Bryan Senseman 20 May '14

20 May '14

All, Is the sys.vacuum call safe, anybody using it? I notice it is marked experimental. We have times when an entire data set is reprocessed and reloaded forcing a delete to occur, we have added this call after the delete and it seems to be working, however we still get a frequent number of database crashes. Thanx, Bryan -- ------------------------------ This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.

1 0

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