September 2016 - users-list

[MonetDB-users] Books CDs
by CHARTS LICENSING 11 Aug '24

11 Aug '24

Everything you want to know this dynamic artist check. At blueberry hills duck, roomfull story gtgt getting. Intriguing, life in biography. Here music looking for lyrics maybe, youre find which! Download free wallpapers featuring business cmg worldwide is exclusive. Videos latest products been easier. Exclusive estate we consider request use name voice, image. List covered berries charts licensing message! Reelin and rockin about, has everything you? Many exciting, photos him stage click! Artist check, out some quotes read all intriguing. It alive he, did on occasion birthday wednesday? Has everything you want to know this? Quotes read all intriguing life, in biography or. Gtgt getting licks years reelin and. Click here, music looking for lyrics maybe, youre. News celebrates his th rock will never! Chuck berry news celebrates his th rock, will. Browse through many exciting, photos him stage click here. He did on occasion birthday wednesday night at blueberry. Wallpapers featuring business cmg worldwide. Worldwide is exclusive estate we consider, request use name. Want to know this dynamic artist check out. Awards fast, facts clips list covered. Music looking for lyrics maybe youre. Message board downloads tour dates fan, club inquiries. Other sites, download free wallpapers featuring, business cmg worldwide. Request use, name, voice.

2 1

Multi-column update based on joined table
by Lee Hachadoorian 11 Aug '24

11 Aug '24

I need to update multiple columns (~250) from one table to another based on a two-column key. In Postgres I would write this using the FROM clause, but since MonetDB doesn't support the FROM clause, I've successfully updated one column with: UPDATE census.acs2010_5yr SET b07401001 = ( SELECT CAST(b07401001 AS int) FROM acs2010_5yr.tmp_seq0001 WHERE acs2010_5yr.stusab = tmp_seq0001.stusab AND acs2010_5yr.logrecno = tmp_seq0001.logrecno ); My question is whether there is any benefit to writing a multiple column update into a single UPDATE statement: UPDATE target_table SET column1 = (SELECT column1 FROM source_table WHERE target_table.key = source_table.key), column2 = (SELECT column2 FROM source_table WHERE target_table.key = source_table.key), ..., columnN = (SELECT columnN FROM source_table WHERE target_table.key = source_table.key); On Postgres I believe the multi-column update using a table join would only have to do the sequential scan of each table once, but with the necessity of sub-selects in MonetDB, what is the backend doing? Is it already doing an index scan once for each row in the single column case, then doing it once for each row * column in the multi-column case? Best, --Lee -- Lee Hachadoorian Asst Professor of Geography, Dartmouth College http://freecity.commons.gc.cuny.edu/

3 3

[MonetDB-users] XQuery API and XLink/XUpdate support
by Alexandra Mart�nez 27 Jun '24

27 Jun '24

Hello all, I was wondering if there is any documentation about the API for XQuery (i.e., how to write xqueries from a Java program). Also, I am interested in using some features of XLink and XUpdate on top of my XML database. Is support for any of these in progress? Thanks in advance, Alexandra ______________________________________________________ Click here to donate to the Hurricane Katrina relief effort. http://store.yahoo.com/redcross-donate3/

4 3

[MonetDB-users] Proofreading your professional documents
by Richard Hounslow 24 Feb '24

24 Feb '24

Dear Professor/Lecturer Please excuse us for contacting you directly. We are offering an efficient service to help you ensure your academic and professional work is written in perfect English. We will check the grammar and style of your work and return it to you to meet your requirements and deadlines. Proof-Reading-Service.com can provide you with a professional proofreading service at a very reasonable rate. All our proofreaders are highly qualified native English speakers. Many work as leading academics in their fields and all have extensive experience of proofreading to the highest standards. If you are interested in our service, please take a look at our website: www.proof-reading-service.com All you have to do is send us your document as a word attachment with the deadline and we will guarantee delivery of a perfectly written document to give you complete confidence when you submit your work. The fee is worked out on a flat rate (£6.95 per thousand words or 0.695 pence per word), so you know exactly how much the proofreading will cost in advance. Once we have received your document(s) we will confirm the word count, the price and the deadline. If you have not heard from us within 2 hours during normal business hours after you have sent your work please resend it from a different email account or send the file to proofreadings(a)gmail.com. We look forward to hearing from you! Yours faithfully Proof-Reading-Service.com

2 2

Python Aggregate functions
by Anderson, David B 06 Feb '17

06 Feb '17

Mark, I think there is a bug in the implementation of the PYTHON_MAP aggregate functions. The return value of a PYTHON_MAP aggregate function can change if there are other functions in the query. The return value of my function weighted_percentile_0 (definition below) changes if I include the function median() in my query. The results are incorrect if median() isn't in the query, but are correct if median() is in the query. In this test case, the weights (v2) are all 1 and the values (v1) are uniformly sampled integers from [1,10]. Number of rows for each fctr is ~1000, and my function should reduce to min(v1) in this case. Is some information bleeding between threads? Should I submit a bug report? This is on the default branch. Thanks, Dave sql>select fctr,weighted_percentile_0(v1,v2),min(v1) from mini where fctr < 10 group by fctr order by fctr; +------+--------------------------+------+ | fctr | L1 | L2 | +======+==========================+======+ | 1 | 3 | 1 | | 2 | 2 | 1 | | 3 | 2 | 1 | | 4 | 1 | 1 | | 5 | 3 | 1 | | 6 | 3 | 1 | | 7 | 2 | 1 | | 8 | 1 | 1 | | 9 | 2 | 1 | +------+--------------------------+------+ 9 tuples (126.928ms) sql>select fctr,weighted_percentile_0(v1,v2),min(v1),median(v1) from mini where fctr < 10 group by fctr order by fctr; +------+--------------------------+------+------+ | fctr | L1 | L2 | L3 | +======+==========================+======+======+ | 1 | 1 | 1 | 5 | | 2 | 1 | 1 | 5 | | 3 | 1 | 1 | 5 | | 4 | 1 | 1 | 5 | | 5 | 1 | 1 | 5 | | 6 | 1 | 1 | 5 | | 7 | 1 | 1 | 5 | | 8 | 1 | 1 | 5 | | 9 | 1 | 1 | 5 | +------+--------------------------+------+------+ 9 tuples (519.195ms) sql> CREATE AGGREGATE weighted_percentile_0(a DOUBLE, w DOUBLE) RETURNS DOUBLE LANGUAGE PYTHON_MAP { import numpy as np # Standardize and sort based on values in a q = np.array([0]) / 100.0 idx = np.argsort(a) a_sort = a[idx] w_sort = w[idx] # Get the cumulative sum of weights ecdf = np.cumsum(w_sort) # Find the percentile index positions associated with the percentiles p = q * (w_sort.sum() - 1) # Find the bounding indices (both low and high) idx_low = np.searchsorted(ecdf, p, side='right') idx_high = np.searchsorted(ecdf, p + 1, side='right') idx_high[idx_high > ecdf.size - 1] = ecdf.size - 1 # Calculate the weights weights_high = p - np.floor(p) weights_low = 1.0 - weights_high # Extract the low/high indexes and multiply by the corresponding weights x1 = np.take(a_sort, idx_low) * weights_low x2 = np.take(a_sort, idx_high) * weights_high wp = np.add(x1,x2) return(wp[0]) };

3 4

SUM() OVER (ORDER BY..)
by Anthony Damico 13 Dec '16

13 Dec '16

hi, monetdb does not support SUM() OVER commands.. does anyone have a smart alternative to implement this? thanks SELECT batch_id, job_count, SUM(job_count) OVER (ORDER BY duration) as cumjobs, SUM(job_count) over () as totjobs, duration FROM test_data ;

5 13

slowdown after delete
by Stefano Fioravanzo 03 Oct '16

03 Oct '16

Hello, In my db I have a table with ~300million rows on which I have to perform ~1k aggregation queries periodically. I have noticed that if I delete even a small amount of rows from the table I have a greatly reduced performance compared to when no delete has been made. Specifically, with no delete the 1k queries complete in about 2 hours, if I delete a few records the task completes in about 10-12 hours. Inserting new rows in the table does not present the issue. Moreover if I dump and restore the table after the delete, everything’s back to normal and the queries execute fast. So the issue seems to be related to the fact that monet does not actually delete the records from the bat file, right? Are you aware of the issue? Is there some kind of vacuum operation available? If not, what is the fastest way to dump or copy and restore the table; or is there any other trick that may solve the issue? Any suggestion would be much appreciated, Thank you Stefano

2 1

Select with 5K+ columns kills server
by Pascal Heus 03 Oct '16

03 Oct '16

Hi: I have a MonetDB 5 server v11.23.7 "Jun2016-SP1 instance hosting a table with over 5600 columns (for the US General Social Survey) If we run a simple query like SELECT * FROM gss7214_v4 limit 2 we get the first two records as expected. However, the same query with all the column names explicitly stated like select "year","id","wrkstat","hrs1","hrs2","evwork","occ","prestige","wrkslf",<...thousands of additional columns - see attached...> from "gss7214_v4" WHERE "year"=1972 LIMIT 2 OFFSET 0; seem to kill the server. The process keeps running but become non-responsive. Now this is on a AWS EC2 t2.small instance with just ~2Gb or memory. Maybe more memory may solve this issue (?). But in any case concerned that the server hangs without warning or error message in the log. Any potential workaround? Suggestion most appreciated best *P

3 2

slowdown after delete
by Stefano Fioravanzo 30 Sep '16

30 Sep '16

Hello, In my db I have a table with ~300million rows on which I have to perform ~1k aggregation queries periodically. I have noticed that if I delete even a small amount of rows from the table I have a greatly reduced performance compared to when no delete has been made. Specifically, with no delete the 1k queries complete in about 2 hours, if I delete a few records the task completes in about 10-12 hours. Inserting new rows in the table does not present the issue. Moreover if I dump and restore the table after the delete, everything’s back to normal and the queries execute fast. So the issue seems to be related to the fact that monet does not actually delete the records from the bat file, right? Are you aware of the issue? Is there some kind of vacuum operation available? If not, what is the fastest way to dump or copy and restore the table; or is there any other trick that may solve the issue? Any suggestion would be much appreciated, Thank you Stefano

3 6

Running different MonetDB versions in parallel
by Moritz Bruder 30 Sep '16

30 Sep '16

Hi, so I read several times on the mailing-list, that it is indeed possible to run several MonetDB instances on the same server. I tried this myself with the following configuration: System: Linux ... 3.13.0-79-generic #123-Ubuntu SMP Fri Feb 19 14:27:58 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux MonetDB version: mercurial revision 324165f1d055 from branch default (also tested with the topmost version) Configure script parameters: --disable-geom --disable-fits --disable-netcdf --disable-lidar --disable-shp --disable-gsl --disable-rintegration --disable-pyintegration --disable-jdbc --prefix=/home/bruder/monetdb-root --disable-assert --disable-testing --enable-optimize --program-suffix='-dsh' 1. Another version is running with a different user on the default port 50000 2. With my user, I set up a local db-farm, using port 49999. (A prefix is used on binaries, to prevent any conflicts with the existing version.) Creating a new database with *monetdbd* works, but once I try to start it with *monetdb*, it gives me the following error message: starting database 'test'... FAILED start: starting 'test' failed: database 'test' appears to shut itself down after starting, check monetdbd's logfile for possible hints Another thing I tried was starting mserver5 by hand: mserver5-dsh --dbpath=/home/bruder/monetdb-dbfarm/test --set merovingian_uri=mapi:monetdb://dbiron2:49999/test --set mapi_open=false --set mapi_port=0 --set mapi_usock=/home/bruder/monetdb-dbfarm/test/.mapi.sock --set monet_vault_key=/home/bruder/monetdb-dbfarm/test/.vaultkey --set gdk_nr_threads=16 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes This time it won't accept my password (I assume the default password is *monetdb*): mclient-dsh -p49999 -dtest -umonetdb InvalidCredentialsException:checkCredentials:invalid credentials for user 'monetdb' I added the log file and the properties of the db-farm. Regards, Moritz

3 5