November 2017 - users-list

MonetDB - Data Compression, is it possible?
by Sharma, Sreejith 23 Nov '17

23 Nov '17

All, Is there any data compression methods available in MonetDB? What I understand from I read is, by default MonetDB doesn't have a compression methods to avoid the overhead of uncompression while loading the files into memory mapped files. Is that true? If there is a compression mechanism (alike QUERY HIGH Compression in Oracle) what is it in here? Regards, Sreejith

2 2

algebra.join heapextend crash
by Anthony Damico 22 Nov '17

22 Nov '17

hi, i'm using monetdblite-r 0.5.0 on a confidential dataset. my syntax worked on monetdblite 0.3.1 on the same machine ftruncate: Invalid argument Error in .local(conn, statement, ...) : Unable to execute statement 'CREATE TABLE ccaed123_b_svcmon_xwalk AS ( SELECT a.enrolid , a.yr , … [View More]

1 0

Patch for backreferences in PCRE replace
by Roberto Cornacchia 16 Nov '17

16 Nov '17

Hi, Following up on this thread ( https://www.monetdb.org/pipermail/users-list/2016-April/009107.html) from last year, the issue was that the pcre-based regex replacement did not implement back references in the replacement string. In need for this feature, I so far used the pcre implementation via R function. It works well, but using R just for this is a bit odd, especially because R-core has a pretty dumb dependency on the whole TeX distribution, and building MonetDB docker images that need … [View More]to include R and TeX just because of regex isn't a neat option. I have also tried to use both re and regex packages via python, but this is very slow compared to pcre. So I finally decided to put a few hours on the MonetDB implementation and added support for back references. They can be indicated with both \1 and $1 syntax. Back-reference 0 is the whole match. Out-of-bound back-references are empty strings. While at it: - pcre.replace replaces all matches, so I made a variant pcre.replacefirst to replace only the first match. - the bat implementation was ignored because of a missing "module batpcre" in pcre.mal, it always resorted to manifold on the string version. Fixed that. Also, I'm not sure why there don't seem to exist SQL functions declared for this. I use the following: CREATE FUNCTION pcre_replace(s string, pattern string, repl string, flags string) RETURNS string EXTERNAL NAME pcre."replace"; CREATE FUNCTION pcre_replacefirst(s string, pattern string, repl string, flags string) RETURNS string EXTERNAL NAME pcre."replace_first"; Perhaps they could come by default? Please feel free to use and modify the patch (Jul2017) in attachment if you think it's useful. I've tested it quite a bit, no issues found. Here are some examples: sql>select name, pcre_replace(name, '([a-z]+)_([a-z]+)', '[\\1]-[\\2]', '') as replaced from sys.functions limit 2; +-------------+---------------------------------------------+ | name | replaced | +=============+=============================================+ | mbr_overlap | [mbr]-[overlap] | | mbr_overlap | [mbr]-[overlap] | +-------------+---------------------------------------------+ 2 tuples (4.650ms) sql>select name, pcre_replacefirst(name, '([a-z])', '[\\1]', '') as replaced from sys.functions limit 2; +-------------+---------------------------------------+ | name | replaced | +=============+=======================================+ | mbr_overlap | [m]br_overlap | | mbr_overlap | [m]br_overlap | +-------------+---------------------------------------+ 2 tuples (4.728ms) sql>select name, pcre_replace(name, '([a-z])', '[\\1]', '') as replaced from sys.functions limit 2; +-------------+-----------------------------------------------------------------------------------------------------+ | name | replaced | +=============+=====================================================================================================+ | mbr_overlap | [m][b][r]_[o][v][e][r][l][a][p] | | mbr_overlap | [m][b][r]_[o][v][e][r][l][a][p] | +-------------+-----------------------------------------------------------------------------------------------------+ 2 tuples (10.360ms) [View Less]

1 1

UDF function with >3 parameters cannot benefit from using PYTHON_MAP
by Hanfeng Chen 15 Nov '17

15 Nov '17

Hi, I created a table with 9 columns and passed 6 columns from the table to a UDF. This UDF is fully vectorizable. However, we found only single thread was used during the execution. Then, we came to think about what the problem behind and we created the two simple functions python_min_map_3 and python_min_map_4 which takes 3 and 4 parameters separately. The MonetDB Linux version we used was July 2017. If PYTHON_MAP is enabled, it would return the results of each segment processed by … [View More]

1 0

read BLOB column on tail heap and loop over rows
by Anton Kravchenko 13 Nov '17

13 Nov '17

I wonder how one would read BLOB column and loop over its rows? BAT *b = table_funcs.full_column(m->session->tr, blob_col_name); //blob *readblobrows = (blob*) b->T.heap.base; //unsigned char *readblobrows = (unsigned char*) b->T.heap.base; p.s. For INT column it would be go like this: int * readintrows = (int *) b->T.heap.base; for (irow=0; irow < nrow; irow++) { intvalue = *readintrows++; } Thanks, Anton

1 1

Querying JSON arrays
by Brian Ploetz 09 Nov '17

09 Nov '17

Hi, Suppose I have a table with a "tags" column that is of the JSON datatype ( https://www.monetdb.org/Documentation/Manuals/SQLreference/Types/JSON) The column will contain a JSON array of Strings. For example: ["foo","bar","baz"] Is it possible to query for rows that contain a specific value in the tags array? For example, if I wanted to find all of the rows where tags contains "foo", my query would look something like: select count(*) from my_table where tags ???? 'foo'; But it's not … [View More]

2 3

large virtual memory spike on BLOB column select
by Anton Kravchenko 09 Nov '17

09 Nov '17

I am using MonetDB v11.27.9 on cent7. 1) 'select v_blob from blob_table' produces a virtual memory spike ~800Gb [using a 'limit' reproduces virtual memory spike too] >From 'select * from storage()' it looks a blob column size is ~25Gb. 2) And 'create table new_blob_table as (select v_blob from blob_table) with data;' produces only ~50Gb of virtual memory. 3) Also for a table with non-blob column both 'select * from nonblob_table' and 'create new_nonblobtable as (select * from … [View More]

2 1

Problems creating json files with more than 10^3 table entries
by Giacomo Bergami 08 Nov '17

08 Nov '17

I am using MonetDB-11.27.9 on ubuntu 17.04. The client was compiled from the source code. I am trying to run the following query on your database select distinct T.src as author, T1.src as coauthor, json.tojsonarray(T1.dst) as papers from edges1000 as T, edges1000 as T1 where T.dst = T1.dst AND T.src <> T1.src group by T.src, T1.src ; When I run this query over a table with at most 10^4 entries, the mclient client quits. If I try to execute the same qury via JDBC, I receive an … [View More]

1 0

latest version question
by Lynn Carol Johnson 02 Nov '17

02 Nov '17

Hello monetdb team – We upgraded this afternoon to the latest version of monetdb (Jul2017-SP2). When testing access for my lab, I noticed the general login could no longer perform the command: \d <table_name> The errors were related to missing group access to sys.objects, sys.keys and sys.idxs. When, as monetdb admin, I granted “select” on these tables, the “\d <table_name>” command then worked. Is this something new, or does it indicate a problem with the db ? It was not … [View More]

2 4