April 2016 - users-list

回复：Re: SUM() OVER (ORDER BY..)
by chenkaijiang＠bigknow.com.cn 29 Apr '16

29 Apr '16

An alternative way is to use temp tables to get the same result: select c1, sum(c2) over(partition by c3) from t1; ==> create temp table tmp1 (sum_c2 int, c3 int); insert into tmp1 select sum(c2), c3 from t1 group by c3; select c1, sum_c2, c3 from t1 join tmp1 on t1.c3=tmp1.c3; ----- 原始邮件 ----- 发件人：Roberto Cornacchia <roberto.cornacchia(a)gmail.com> 收件人：Communication channel for MonetDB users <users-list(a)monetdb.org> 主题：Re: SUM() OVER (ORDER BY..) 日期：2016-4-29 15:46:57 No, indeed, this syntax is not supported. On 29 April 2016 at 09:41, Anthony Damico <ajdamico(a)gmail.com> wrote: > hmm, that gives me the same error.. > > SELECT mpg, wt, SUM(wt) OVER (PARTITION BY hp) AS cum_wgt, SUM(wt) over () > AS tot_wgt, hp FROM mtcars; > > > Error in .local(conn, statement, ...) : > Unable to execute statement 'SELECT > mpg, > wt, > SUM(wt) OVER (PARTITION BY hp) AS cum_wgt, > SUM(wt) over () AS tot_wgt, > hp > FROM mtc...'. > Server says 'ParseException:SQLparser:SELECT: function 'sum' not found'. > > > sorry if i'm doing something silly.. > > > > > On Fri, Apr 29, 2016 at 3:37 AM, Roberto Cornacchia < > roberto.cornacchia(a)gmail.com> wrote: > >> I think what you are looking for is SUM(..) OVER(PARTITION BY ..) >> >> Otherwise where would the groups for the sum come from? >> >> Roberto >> >> On 29 April 2016 at 09:28, Anthony Damico <ajdamico(a)gmail.com> wrote: >> >>> 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 ; >>> >>> >>> _______________________________________________ >>> users-list mailing list >>> users-list(a)monetdb.org >>> https://www.monetdb.org/mailman/listinfo/users-list >>> >>> >> >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list >> >> > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list > > _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

1 0

The Meaning of Constants in mapi.h
by Brian Hood 28 Apr '16

28 Apr '16

Hi Guys, #define MOK 0 #define MERROR (-1) #define MTIMEOUT (-2) #define MMORE (-3) #define MSERVER (-4) So far i've discovered MOK with value of 0 means connected to MServer and your query went well. MSERVER with value of -4 means that the server doesn't like the query and its bad SQL. I've not been able to discover what the others mean hope someone can help. I've also discovered that mapi_start_talking no longer seems to be implemented in the library guessing its deprecated. Regards, Brian Hood

1 0

Crystal bindings for MonetDB
by Brian Hood 26 Apr '16

26 Apr '16

Hi Guys, Well its been a long, long day putting this together but i actually have working Crystal Language binding for MonetDB. https://github.com/puppetpies/crystal-monetdb-libmapi/blob/4e55863d453be33b… This is the very first stages. I've managed to count 2 rows so far lol. Regards, Brian Hood

2 1

Merge/Remote table causes segfault
by Anderson, David B 26 Apr '16

26 Apr '16

All, I've been able to isolate the seg-fault in gdb, but am not sure how to proceed. Should I file a bug report? There is a NULL pointer access in q_enqueue_(). The q data pointer is NULL. If I look at the flow inside the flow event d, I see the error: "SQLException:sql.bind:unable to find sys.loan201508(purpose)", so it looks like the root cause is higher up/elsewhere. Dave (gdb) where #0 0x00002b39b92fbb2f in q_enqueue_ (q=0x1c0089e0, d=0x1c095b78) at mal_dataflow.c:156 #1 0x00002b39b92fbbbc in q_enqueue (q=0x1c0089e0, d=0x1c095b78) at mal_dataflow.c:164 #2 0x00002b39b92fdd88 in DFLOWscheduler (flow=0x1c094260, w=0x2b39b96a8e40) at mal_dataflow.c:709 #3 0x00002b39b92fe696 in runMALdataflow (cntxt=0x2b39be9b6328, mb=0x1bf02bd0, startpc=1, stoppc=399, stk=0x1c08b7d0) at mal_dataflow.c:892 #4 0x00002b39b93cad1c in MALstartDataflow (cntxt=0x2b39be9b6328, mb=0x1bf02bd0, stk=0x1c08b7d0, pci=0x1c056ac0) at language.c:147 #5 0x00002b39b92f9077 in runMALsequence (cntxt=0x2b39be9b6328, mb=0x1bf02bd0, startpc=1, stoppc=402, stk=0x1c08b7d0, env=0x1be28a30, pcicaller=0x1c08b710) at mal_interpreter.c:631 #6 0x00002b39b92f9418 in runMALsequence (cntxt=0x2b39be9b6328, mb=0x1bda6560, startpc=1, stoppc=0, stk=0x1be28a30, env=0x1be28a30, pcicaller=0x0) at mal_interpreter.c:743 #7 0x00002b39b92f824c in runMAL (cntxt=0x2b39be9b6328, mb=0x1bda6560, mbcaller=0x0, env=0x1be28a30) at mal_interpreter.c:365 #8 0x00002b39b9318b63 in MALengine (c=0x2b39be9b6328) at mal_session.c:637 #9 0x00002b39b9316ebd in runPhase (c=0x2b39be9b6328, phase=4) at mal_scenario.c:515 #10 0x00002b39b931707b in runScenarioBody (c=0x2b39be9b6328) at mal_scenario.c:559 #11 0x00002b39b9317164 in runScenario (c=0x2b39be9b6328) at mal_scenario.c:579 #12 0x00002b39b9317f74 in MSserveClient (dummy=0x2b39be9b6328) at mal_session.c:439 #13 0x00002b39b9317b8b in MSscheduleClient (command=0x1be20190 "p\001\342\033", challenge=0x2b39c017b0c0 "GJMKUZJ4", fin=0x1a3fdf90, fout=0x1bda6a50) at mal_session.c:319 #14 0x00002b39b93d0464 in doChallenge (data=0x1bd88880) at mal_mapi.c:184 #15 0x00002b39b9b4697b in thread_starter (arg=0x1a3fdf60) at gdk_system.c:458 #16 0x00002b39bb03683d in start_thread () from /lib64/libpthread.so.0 #17 0x000000330ded4fdd in clone () from /lib64/libc.so.6 ... (gdb) down #0 0x00002b39b92fbb2f in q_enqueue_ (q=0x1c0089e0, d=0x1c095b78) at mal_dataflow.c:156 156 q->data[q->last++] = d; (gdb) p *q $17 = { size = 0, last = 0, exitcount = 0, data = 0x0, l = { lock = 1 }, s = { sema = { __size = '\000' <repeats 31 times>, __align = 0 }, name = 0x0 } } (gdb) p d $18 = (FlowEvent) 0x1c095b78 (gdb) p *d $19 = { flow = 0x1c094260, pc = 55, blocks = 0, state = 1, clk = 0, cost = -1, hotclaim = 0, argclaim = 0 } (gdb) p *d->flow $20 = { cntxt = 0x2b39be9b6328, mb = 0x1bf02bd0, stk = 0x1c08b7d0, start = 2, stop = 399, status = 0x1c094fe0, error = 0x2b39cc00edc0 "SQLException:sql.bind:unable to find sys.loan201508(purpose)", nodes = 0x1c09a750, edges = 0x1c09c480, flowlock = { lock = 1 }, done = 0x1c0942d0 }

1 1

Fwd: JSON Failed to import
by Ariel Abadi 25 Apr '16

25 Apr '16

Now is working! Thank you guys! Rgds > > > ---------- Forwarded message ---------- > From: Sjoerd Mullender <sjoerd(a)monetdb.org> > Date: 2016-04-21 17:51 GMT-03:00 > Subject: Re: JSON Failed to import > To: users-list(a)monetdb.org > > > This works: > > 301|{"data":[{"cdr_dialnumber":"6596871820"}]} > > So the quoting of the json string doesn't help. > > > On 04/21/2016 09:26 PM, Ariel Abadi wrote: > > Hi! > > > > Can someone help me ? Im trying to upload a CSV file with json data, but > > Im receiving an error. Does anybody has an idea how to solve this? > > > > Thks in advance > > > > mclient -s"COPY 1 RECORDS INTO traf.jsontbl FROM STDIN USING > > DELIMITERS '|','\n','\"' NULL AS 'null'" - < j.csv > > > > *The data on j.csv is:* > > 301|"{\"data\":[{\"cdr_dialnumber\":\"6596871820\"}]}" > > > > *TABLE traf.xx > > * > > > sql>\d jsontbl > > CREATE TABLE "traf"."jsontbl" ( > > "a" INTEGER, > > "b" JSON > > ); > > > > > > > > _______________________________________________ > > users-list mailing list > > users-list(a)monetdb.org > > https://www.monetdb.org/mailman/listinfo/users-list > > > > -- > Sjoerd Mullender > > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list > >

1 0

Merge/Remote tables causing remote server to segfault
by Anderson, David B 25 Apr '16

25 Apr '16

All, I am seeing odd behavior with Remote/Merge tables. I have a pair of servers, one of which has a merge table that combines 12 remote & 12 local tables (24 total) into a single large table. Queries from the main server on the merge table cause the remote server to crash with a SIGSEGV. If I create a merge table which only contains the remote tables (loan2) the remote server doesn't crash, but I get different responses for the same query in the same session: /opt/flash1/gnmaloan$ mclient -d loan Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP4) Database: MonetDB v11.21.19 (Jul2015-SP4), 'mapi:monetdb://malxcs1p:50000/loan' Type \q to quit, \? for a list of available commands auto commit mode: on sql>select asofdate,sum(balance),count(loanseqnum) from loan2 group by asofdate; (mapi:monetdb://monetdb@malxcs2p/loan) unable to find sys.loan201402(loanseqnum) sql>select asofdate,sum(balance),count(loanseqnum) from loan2 group by asofdate; +------------+--------------------------+----------+ | asofdate | L1 | L2 | +============+==========================+==========+ | 2014-03-01 | 124075979043384 | 9177029 | | 2014-05-01 | 1.2634454179935e+14 | 9264814 | | 2014-07-01 | 126798740013672 | 9335352 | | 2014-09-01 | 126526301326951 | 9407070 | | 2014-11-01 | 1.2604434513281e+14 | 9478920 | | 2015-01-01 | 1.2644932715183e+14 | 9529639 | | 2015-03-01 | 127602153039423 | 9579604 | | 2015-05-01 | 126615476286119 | 9654842 | | 2015-07-01 | 125415108251273 | 9756258 | | 2015-09-01 | 124427385478338 | 9855118 | | 2015-11-01 | 126150962049943 | 9945919 | | 2016-01-01 | 129313947667802 | 10011556 | +------------+--------------------------+----------+ 12 tuples (0.9s) The only thing I see in the crashing server's log is: 2016-04-25 16:55:50 MSG merovingian[28226]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-04-25 16:55:50 MSG merovingian[28226]: database 'loan' (2706) was killed by signal SIGSEGV 2016-04-25 16:56:37 MSG merovingian[28226]: starting database 'loan', up min/avg/max: 2m/1h/2h, crash average: 1.00 0.90 0.33 (12-2=10) 2016-04-25 16:56:37 MSG loan[28022]: arguments: /opt/mr/monetdb/bin/mserver5 --dbpath=/opt/flash2/monetdb2/loan --set merovingian_uri=mapi:monetdb://malxcs2p:50000/loan --set mapi_open=false --set mapi_port=0 --set mapi_usock=/opt/flash2/monetdb2/loan/.mapi.sock --set monet_vault_key=/opt/flash2/monetdb2/loan/.vaultkey --set gdk_nr_threads=24 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes Not sure where to start debugging this. I guess I will see if this still occurs with a small number of rows in the base tables. Thanks, Dave

1 0

MonetDB Remote / Merge bug ?
by Brian Hood 25 Apr '16

25 Apr '16

Hi All, It seems when i revert back the original query the first time it breaks and gives a MAL error then the second time it works fine its also very reproduce able. Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP4) Database: MonetDB v11.21.19 (Jul2015-SP4), 'mapi:monetdb://mdb-master-01:50000/threatmonitor' Type \q to quit, \? for a list of available commands auto commit mode: on sql>select * from ippacket a JOIN tcppacket b on (a.guid = b.guid) limit 1; +-----------+------------+------+------------+------+-------+------+------+------+------+------------+------------+------+------+------+------------+------------+------+-------+------+------+------+------+------+------+------+ | guid | recv_date | ip_d | ip_dst | ip_h | ip_id | ip_l | ip_m | ip_o | ip_p | ip_src | ip_sum | ip_t | ip_t | ip_v | guid | recv_date | tcp_ | tcp_d | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ |> : : : f : : len : : en : f : ff : roto : : : os : tl : er : : : data : port : ack : fin : syn : rst : psh : urg : off :> : : : : : : : : : : : : : : : : : : _len : : : : : : : : :> +===========+============+======+============+======+=======+======+======+======+======+============+============+======+======+======+============+============+======+=======+======+======+======+======+======+======+======+ | 0413e543- | 2015-07-09 | Y | 172.17.0.1 | 5 | 28147 | 60 | N | 0 | 6 | 172.17.42. | 19108 | 0 | 64 | 4 | 0413e543-5 | 2015-07-09 | 0 | 15672 | N | N | Y | N | N | N | 10 | : 52b7-ee...> 20:50:32 : : : : : : : : : 1 : : : : : 2b7-eeda-0 : 20:50:32 : : : : : : : : : : : > +0000 : : : : : : : : : : : : : : a77-a2de...> +0000 : : : : : : : : : : +-----------+------------+------+------------+------+-------+------+------+------+------+------------+------------+------+------+------+------------+------------+------+-------+------+------+------+------+------+------+------+ 1 tuple (250.636ms) !6 columns dropped, 2 fields truncated! sql>select * from ippacket a JOIN tcppacket b on (b.guid = a.guid) limit 1; +-----------+------------+------+------------+------+-------+------+------+------+------+------------+------------+------+------+------+------------+------------+------+-------+------+------+------+------+------+------+------+ | guid | recv_date | ip_d | ip_dst | ip_h | ip_id | ip_l | ip_m | ip_o | ip_p | ip_src | ip_sum | ip_t | ip_t | ip_v | guid | recv_date | tcp_ | tcp_d | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ |> : : : f : : len : : en : f : ff : roto : : : os : tl : er : : : data : port : ack : fin : syn : rst : psh : urg : off :> : : : : : : : : : : : : : : : : : : _len : : : : : : : : :> +===========+============+======+============+======+=======+======+======+======+======+============+============+======+======+======+============+============+======+=======+======+======+======+======+======+======+======+ | 0413e543- | 2015-07-09 | Y | 172.17.0.1 | 5 | 28147 | 60 | N | 0 | 6 | 172.17.42. | 19108 | 0 | 64 | 4 | 0413e543-5 | 2015-07-09 | 0 | 15672 | N | N | Y | N | N | N | 10 | : 52b7-ee...> 20:50:32 : : : : : : : : : 1 : : : : : 2b7-eeda-0 : 20:50:32 : : : : : : : : : : : > +0000 : : : : : : : : : : : : : : a77-a2de...> +0000 : : : : : : : : : : +-----------+------------+------+------------+------+-------+------+------+------+------+------------+------------+------+------+------+------------+------------+------+-------+------+------+------+------+------+------+------+ 1 tuple (248.468ms) !6 columns dropped, 2 fields truncated! sql>select * from ippacket a JOIN tcppacket b on (a.guid = b.guid) limit 1; (mapi:monetdb://monetdb@172.17.0.6/threatmonitor) 'user.l10' undefined in: (rmt2533_X_18_bat_oid_str:bat[:oid,:str],rmt2535_X_21_bat_oid_str:bat[:oid,:str],rmt2537_X_22_bat_oid_int:bat[:oid,:int],rmt2539_X_24_bat_oid_int:bat[:oid,:int],rmt2541_X_25_bat_oid_str:bat[:oid,:str],rmt2543_X_26_bat_oid_str:bat[:oid,:str],rmt2545_X_27_bat_oid_str:bat[:oid,:str],rmt2547_X_28_bat_oid_str:bat[:oid,:str],rmt2549_X_29_bat_oid_str:bat[:oid,:str],rmt2551_X_30_bat_oid_str:bat[:oid,:str],rmt2553_X_31_bat_oid_int:bat[:oid,:int],rmt2555_X_32_bat_oid_int:bat[:oid,:int],rmt2556_X_33_bat_oid_lng:bat[:oid,:lng],rmt2557_X_35_bat_oid_str:bat[:oid,:str],rmt2558_X_36_bat_oid_int:bat[:oid,:int],rmt2559_X_37_bat_oid_str:bat[:oid,:str],rmt2560_X_38_bat_oid_int:bat[:oid,:int],rmt2562_X_39_bat_oid_oid:bat[:oid,:oid]) := user.l10(); sql>select * from ippacket a JOIN tcppacket b on (a.guid = b.guid) limit 1; +-----------+------------+------+------------+------+-------+------+------+------+------+------------+------------+------+------+------+------------+------------+------+-------+------+------+------+------+------+------+------+ | guid | recv_date | ip_d | ip_dst | ip_h | ip_id | ip_l | ip_m | ip_o | ip_p | ip_src | ip_sum | ip_t | ip_t | ip_v | guid | recv_date | tcp_ | tcp_d | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ |> : : : f : : len : : en : f : ff : roto : : : os : tl : er : : : data : port : ack : fin : syn : rst : psh : urg : off :> : : : : : : : : : : : : : : : : : : _len : : : : : : : : :> +===========+============+======+============+======+=======+======+======+======+======+============+============+======+======+======+============+============+======+=======+======+======+======+======+======+======+======+ | 0413e543- | 2015-07-09 | Y | 172.17.0.1 | 5 | 28147 | 60 | N | 0 | 6 | 172.17.42. | 19108 | 0 | 64 | 4 | 0413e543-5 | 2015-07-09 | 0 | 15672 | N | N | Y | N | N | N | 10 | : 52b7-ee...> 20:50:32 : : : : : : : : : 1 : : : : : 2b7-eeda-0 : 20:50:32 : : : : : : : : : : : > +0000 : : : : : : : : : : : : : : a77-a2de...> +0000 : : : : : : : : : : +-----------+------------+------+------------+------+-------+------+------+------+------+------------+------------+------+------+------+------------+------------+------+-------+------+------+------+------+------+------+------+ 1 tuple (213.998ms) !6 columns dropped, 2 fields truncated! sql> Regards, Brian Hood

1 0

A good strategy for loading bulk data into monetdb
by Knezevic Nikola 25 Apr '16

25 Apr '16

Hi all, I'm trying to load large amounts of data into monetdb (migrating from mysql). Since I'm using Jul2015-SP4 release, I can't create local files and I can't use external tools, my approach is as following: For each table, I partition the data based on the day, and then: 10 create one table in monetdb for each day, and a merge table that takes all these tables 20 fetch data from mysql (mysql -B -N -C ...) 30 _pipe_ data to mclient (that is invoked with mclient ... -c "COPY 2000000 RECORDS FROM STDIN INTO ..." -) 40 load data into corresponding table (the right day) 50 sleep some seconds 60 goto 20 Each daily table is around 6'000'000 rows, but I have some that have ~150'000'000 rows. However, I noticed that when I'm doing this, I quickly run out of disk space, as monetdb is creating really big logs (in /data/monetdb/some_schema/sql_logs). Size of that folder is usually 600KB, but once the process of migration starts, it goes to several GBs. Not only that, but /data/monetdb/some_schema/bat also grows a lot. The surprising part, and the motivation for this emails, is how well monetdb compresses the data. If I migrate only one day worth of data, and then restart monetdbd (once or twice) and run 'analyze some_schema', sql_logs size drops to normal levels, and I get a lot of reclaimed space (even in bat directory). I tried to put a 'sleep(120)' after each partition and running 'analyze some_schema' after each iteration, but couldn't get the same behaviour (and it is also quite slow). Is there a way to trigger the compression and reduce disk space overhead without restarting mserver, given the above constraints? Thanks, Nikola

3 4

MonetDB Driver / MAPI Implementation.
by Brian Hood 23 Apr '16

23 Apr '16

Hi All, I am have tried to convert the Ruby MonetDB driver into Crystal with little success as i want to MonetDB with the Crystal Programming Language for higher performance and to handle bigger datasets at more C level performance. I'm currently making a transition from Ruby to Crystal and want to know where the specification of MonetDB's MAPI interface lives. Any help appreciated. Regards, Brian Hood

2 1

Merovingian startup error: rapi.prelude missing
by Anderson, David B 23 Apr '16

23 Apr '16

All, I've built MonetDB from source using 11.21.19, but am having problems starting the Merovingian process. I am getting the following error even though I chose -disable-rintegration in the configure step. Does anyone know what I might have done incorrectly? Thanks, Dave 2016-04-22 23:36:11 MSG loan[7218]: # MonetDB 5 server v11.21.19 "Jul2015-SP4" 2016-04-22 23:36:11 MSG loan[7218]: # Serving database 'loan', using 24 threads 2016-04-22 23:36:11 MSG loan[7218]: # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked 2016-04-22 23:36:11 MSG loan[7218]: # Found 188.787 GiB available main-memory. 2016-04-22 23:36:11 MSG loan[7218]: # Copyright (c) 1993-July 2008 CWI. 2016-04-22 23:36:11 MSG loan[7218]: # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved 2016-04-22 23:36:11 MSG loan[7218]: # Visit http://www.monetdb.org/ for further information 2016-04-22 23:36:11 MSG loan[7218]: !TypeException:user.main[14]:object code for command rapi.prelude missing 2016-04-22 23:36:11 MSG loan[7218]: !TypeException:user.main[14]:'rapi.prelude' undefined in: rapi.prelude(); 2016-04-22 23:36:11 MSG merovingian[6082]: database 'loan' (7218) has exited with exit status 0 2016-04-22 23:36:11 MSG merovingian[6082]: database 'loan' has shut down 2016-04-22 23:36:15 ERR merovingian[6082]: client error: database 'loan' started up, but failed to open up a communication channel

3 4