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 , a.dataty...'. Server says 'MALException:algebra.join:GDK reported error. !ERROR: GDKextendf: c !ERROR: HEAPextend: failed to extend to 2890530816 for 02\57\25766.tail: GDKmre map() failed '. it's a local drive, the hard disk isn't close to full and the machine has 700gb of ram, but the table it's trying to create might be bigger than that. dunno if related to https://www.monetdb.org/bugzilla/show_bug.cgi?id=3791 thanks

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 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)

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 parallel code. The number of results depends on how many threads are used. Otherwise, a single value should be returned (e.g. because of numpy.min). The result is - python_min_map_3 returns a couple of numbers - python_min_map_4 returns a single number Even when we increased the number of the arguments, a single number was always returned. Is it a restriction in using PYTHON_MAP when the number of arguments should be no more than 3? Here is our example code below. CREATE FUNCTION python_min_map_3(x0 FLOAT, x1 FLOAT, x2 FLOAT) RETURNS FLOAT LANGUAGE PYTHON_MAP { return numpy.min(x0) }; select python_min_map_3(x0, x1, x2) from table_0; CREATE FUNCTION python_min_map_4(x0 FLOAT, x1 FLOAT, x2 FLOAT, x3 FLOAT) RETURNS FLOAT LANGUAGE PYTHON_MAP { return numpy.min(x0) }; select python_min_map_4(x0, x1, x2,x3) from table_0; NumPy in MonetDB reference: https://www.monetdb.org/blog/embedded-pythonnumpy-monetdb Best regards, Hanfeng Chen

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 clear to me what ???? should be (or if it's even possible) based on the JSON documentation above. Thanks in advance. BP

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 nonblob_table) with data;' produce the ~same virtual memory consumption. p.s. Here are steps to reproduce this issue # to generate date by using Python hex_blob='47'*2000 hex_blob_chunk=(hex_blob+'\n')*1000 f=open('blob_data.txt','w') for irow in range(10000): f.write(hex_blob_chunk) f.close() # to load data into Monet blob_table create table blob_table(v_blob blob); COPY 10000000 RECORDS INTO blob_table FROM '/home/blob_data.txt' using delimiters ',','\n' NULL AS '' LOCKED; # to produce virtual memory spike of ~800GB select * from blob_table limit 50000; # to produce virtual memory of ~50Gb create table new_blob_table as (select * from blob_table) with data; Thanks, Anton

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 error that tells me "maybe mserver closed unexpectedly". On the other hand, the server is still alive and responsive if I afterwards run the query either oover mclient or JDBC over a table having less data (edges10). Is that a problem of memory allocation ? Is there a way to solve this issue? I'm using the default configuration, and compiled the source code using "autoreconf -if" first. Thanks in advance for your support, Bergami G

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 necessary to grant general users permission to these system tables previously. Thanks - Lynn

2 4