- users-list - monetdb.org

Problem: 'ALTER TABLE x ADD TABLE y' does not merge the data in from y.
by Knezevic Nikola 04 May '16

04 May '16

Hi all, I'm splitting my data into per-day tables and importing them into monetdb. Before the import, I first create a per-day table and then use 'ALTER TABLE "table" ADD TABLE "table__2016-05-03";' command (passed to cursor.execute() in python. "table" is defined to be a MERGE TABLE. Now, I had only a single day that was populated on the initial run, when both MERGE TABLE and day table were created. Today, I added a new day table and ran this: sql> SELECT day, count(*) FROM "table" GROUP BY day; +------------+---------+ | day | L1 | +============+=========+ | 2016-04-26 | 2959601 | +------------+---------+ sql>select day, count(*) from "trades__2016-05-02" group by day; +------------+---------+ | day | L1 | +============+=========+ | 2016-05-02 | 2220143 | +------------+---------+ So, the output is missing the current day, and I thought my python code didn't properly tie these tables together. However, inspecting sys.* tables gives me this: sql>\d (snip...) MERGE TABLE my_namespace.trades TABLE my_namespace.trades__2016-04-26 TABLE my_namespace.trades__2016-05-02 (snip...) sql>select * from sys.tables where name like 'trades%'; +-------+--------------------+-----------+-------+------+--------+---------------+--------+-----------+ | id | name | schema_id | query | type | system | commit_action | access | temporary | +=======+====================+===========+=======+======+========+===============+========+===========+ | 13480 | trades | 6497 | null | 3 | false | 0 | 0 | 0 | | 13518 | trades__2016-04-26 | 6497 | null | 0 | false | 0 | 0 | 0 | | 16203 | trades__2016-05-02 | 6497 | null | 0 | false | 0 | 0 | 0 | +-------+--------------------+-----------+-------+------+--------+---------------+--------+-----------+ 3 tuples (3.323ms) sql>select * from sys.dependencies where depend_id=13480; +-------+-----------+-------------+ | id | depend_id | depend_type | +=======+===========+=============+ | 13518 | 13480 | 2 | | 16203 | 13480 | 2 | +-------+-----------+-------------+ Finally, if I manually add the table, it succeeds: sql>alter table "trades" add table "trades__2016-05-02"; operation successful (13.894ms) sql>select day, count(*) from "trades" group by day; +------------+---------+ | day | L1 | +============+=========+ | 2016-04-26 | 2959601 | | 2016-05-02 | 2220143 | +------------+---------+ 2 tuples (44.196ms) sql>select * from sys.tables where name like 'trades%'; +-------+--------------------+-----------+-------+------+--------+---------------+--------+-----------+ | id | name | schema_id | query | type | system | commit_action | access | temporary | +=======+====================+===========+=======+======+========+===============+========+===========+ | 13480 | trades | 6497 | null | 3 | false | 0 | 0 | 0 | | 13518 | trades__2016-04-26 | 6497 | null | 0 | false | 0 | 0 | 0 | | 16203 | trades__2016-05-02 | 6497 | null | 0 | false | 0 | 0 | 0 | +-------+--------------------+-----------+-------+------+--------+---------------+--------+-----------+ 3 tuples (7.445ms) sql>select * from sys.dependencies where depend_id=13480; +-------+-----------+-------------+ | id | depend_id | depend_type | +=======+===========+=============+ | 13518 | 13480 | 2 | | 16203 | 13480 | 2 | +-------+-----------+-------------+ 2 tuples (1.714ms) For completeness, python code looks like this (cleaned up, to present the case, keep in mind that on initial import, this worked): if args.create: if args.partition and has_day: dest_create_merge_table = re.sub('CREATE TABLE', 'CREATE MERGE TABLE', dest_create_table, count=1) print("Creating table with the following content:\n{ddl}".format(ddl=dest_create_merge_table), file=sys.stderr) try: cursor = monet_conn.cursor() cursor.execute(dest_create_merge_table) except monetdb.exceptions.OperationalError as e: if re.search('CREATE TABLE: name .* already in use', e.args[0]): print("Master table already present") else: raise(e) finally: cursor.close() if args.partition and has_day: cursor = monet_conn.cursor() for c in conditions: (text, day, sth) = c dest_create_partition = re.sub('CREATE TABLE "{table}"'.format(table=table), 'CREATE TABLE "{partition}"'.format(partition=partition_name(table, day)), dest_create_table, count=1) print("Creating partition with the following content:\n{ddl}".format(ddl=dest_create_partition), file=sys.stderr) cursor.execute(dest_create_partition) if args.debug: print('ALTER TABLE "{table}" ADD TABLE "{partition}"'.format(table=table, partition=partition_name(table, day))) cursor.execute('ALTER TABLE "{table}" ADD TABLE "{partition}"; COMMIT;'.format(table=table, partition=partition_name(table, day))) cursor.close() Can someone please help me debug this issue? Is this a bug? IIUC, metadata points that "trades__2016-05-02" belongs to "trades", but the queries do not reflect that. Thanks, Nikola

3 5

Crashed DB. cannot start DB
by Ariel Abadi 03 May '16

03 May '16

Hi! Can someone help me please with this issue? Im trying to start my db.. and Im getting the following erro from the log: 2016-05-03 15:08:38 MSG merovingian[14646]: starting database 'bm_traf', up min/avg/max: 3d/5d/1w, crash average: 1.00 0.40 0.13 (6-2=4) 2016-05-03 15:08:38 MSG bm_traf[28169]: arguments: /usr/bin/mserver5 --dbpath=/opt/monetdb/bm/bm_traf --set merovingian_uri=mapi:monetdb://monetdb:50000/bm_traf --set mapi_open=false --set mapi_port=0 --set mapi_usock=/opt/monetdb/bm/bm_traf/.mapi.sock --set monet_vault_key=/opt/monetdb/bm/bm_traf/.vaultkey --set gdk_nr_threads=4 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2016-05-03 15:08:38 MSG merovingian[14646]: database 'bm_traf' (28169) has exited with exit status 1 2016-05-03 15:08:43 ERR merovingian[14646]: client error: database 'bm_traf' appears to shut itself down after starting, check monetdbd's logfile for possible hints Does anybody has a clue? Thks in advance!

2 2

Query Histroy & query parsing / translation / compilation / optimization time
by Stefan Manegold 03 May '16

03 May '16

Hi, looking for ways to measure the query parsing / translation / compilation / optimization time in MonetDB, I found these two documentations on the MonetDB website: https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryHistory and https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryHistoryNew While the documentation is not entirely clear on whether the latter did, will, or is supposed to replace the former (and if so, when), using the latest Jul2015-SP4 release, I get with the former: sql>set history=true; operation successful (137.545ms) sql>select 1; +--------------+ | single_value | +==============+ | 1 | +--------------+ 1 tuple (155.577ms) sql>select 2; +--------------+ | single_value | +==============+ | 2 | +--------------+ 1 tuple (193.633ms) sql>select * from sys.queryHistory; SELECT: no such table 'queryhistory' sql>select * from sys.callHistory; SELECT: no such table 'callhistory' sql>select * from sys.queryLog; SELECT: no such table 'querylog' sql>call sys.resetHistory(); SELECT: no such operator 'resethistory' I.e, this does not seem to work (anymore?). The latter seems to work fine: sql>call sys.querylog_enable(); sql>select 1; +--------------+ | single_value | +==============+ | 1 | +--------------+ 1 tuple (127.056ms) sql>select 1; +--------------+ | single_value | +==============+ | 1 | +--------------+ 1 tuple (124.303ms) sql>select 2; +--------------+ | single_value | +==============+ | 2 | +--------------+ 1 tuple (148.923ms) sql>select * from sys.querylog_catalog; +------+---------+----------------------------+-------------------------------------+--------------+-----------+------+----------+ | id | owner | defined | query | pipe | plan | mal | optimize | +======+=========+============================+=====================================+==============+===========+======+==========+ | 42@0 | monetdb | 2016-05-03 00:06:44.000000 | call sys.querylog_enable(); | default_pipe | user.s6_1 | 19 | 267 | | 43@0 | monetdb | 2016-05-03 00:06:47.000000 | select 1; | default_pipe | user.s7_1 | 21 | 273 | | 44@0 | monetdb | 2016-05-03 00:06:48.000000 | select 1; | default_pipe | user.s7_1 | 21 | 273 | | 45@0 | monetdb | 2016-05-03 00:06:50.000000 | select 1; | default_pipe | user.s7_1 | 21 | 273 | | 46@0 | monetdb | 2016-05-03 00:06:56.000000 | select * from sys.querylog_catalog; | default_pipe | user.s8_1 | 68 | 665 | +------+---------+----------------------------+-------------------------------------+--------------+-----------+------+----------+ 5 tuples (132.446ms) sql>select * from sys.querylog_calls; +------+----------------------------+----------------------------+------------------------------------------+--------+-------+------+------+------+ | id | start | stop | arguments | tuples | run | ship | cpu | io | +======+============================+============================+==========================================+========+=======+======+======+======+ | 41@0 | 2016-05-03 00:06:38.000000 | 2016-05-03 00:06:39.000000 | user.s5_1{autoCommit=true}():void; | 1 | 85485 | 1 | 3 | 24 | | 42@0 | 2016-05-03 00:06:44.000000 | 2016-05-03 00:06:44.000000 | user.s6_1{autoCommit=true}():void; | 1 | 71 | 0 | 0 | 0 | | 43@0 | 2016-05-03 00:06:47.000000 | 2016-05-03 00:06:47.000000 | user.s7_1{autoCommit=true}(A0:bte):void; | 1 | 120 | 0 | 0 | 0 | | 44@0 | 2016-05-03 00:06:48.000000 | 2016-05-03 00:06:48.000000 | user.s7_1{autoCommit=true}(A0:bte):void; | 1 | 104 | 0 | 0 | 0 | | 45@0 | 2016-05-03 00:06:50.000000 | 2016-05-03 00:06:50.000000 | user.s7_1{autoCommit=true}(A0:bte):void; | 1 | 66 | 1 | 0 | 0 | | 46@0 | 2016-05-03 00:06:56.000000 | 2016-05-03 00:06:56.000000 | user.s8_1{autoCommit=true}():void; | 8 | 886 | 0 | 50 | 0 | +------+----------------------------+----------------------------+------------------------------------------+--------+-------+------+------+------+ 6 tuples (100.344ms) sql>select * from sys.querylog_history; +------+---------+----------------------------+-------------------------------------+--------------+-----------+------+----------+----------------------------+----------------------------+------------------------------------------+--------+------+------+------+------+ | id | owner | defined | query | pipe | plan | mal | optimize | start | stop | arguments | tuples | run | ship | cpu | io | +======+=========+============================+=====================================+==============+===========+======+==========+============================+============================+==========================================+========+======+======+======+======+ | 42@0 | monetdb | 2016-05-03 00:06:44.000000 | call sys.querylog_enable(); | default_pipe | user.s6_1 | 19 | 267 | 2016-05-03 00:06:44.000000 | 2016-05-03 00:06:44.000000 | user.s6_1{autoCommit=true}():void; | 1 | 71 | 0 | 0 | 0 | | 43@0 | monetdb | 2016-05-03 00:06:47.000000 | select 1; | default_pipe | user.s7_1 | 21 | 273 | 2016-05-03 00:06:47.000000 | 2016-05-03 00:06:47.000000 | user.s7_1{autoCommit=true}(A0:bte):void; | 1 | 120 | 0 | 0 | 0 | | 44@0 | monetdb | 2016-05-03 00:06:48.000000 | select 1; | default_pipe | user.s7_1 | 21 | 273 | 2016-05-03 00:06:48.000000 | 2016-05-03 00:06:48.000000 | user.s7_1{autoCommit=true}(A0:bte):void; | 1 | 104 | 0 | 0 | 0 | | 45@0 | monetdb | 2016-05-03 00:06:50.000000 | select 1; | default_pipe | user.s7_1 | 21 | 273 | 2016-05-03 00:06:50.000000 | 2016-05-03 00:06:50.000000 | user.s7_1{autoCommit=true}(A0:bte):void; | 1 | 66 | 1 | 0 | 0 | | 46@0 | monetdb | 2016-05-03 00:06:56.000000 | select * from sys.querylog_catalog; | default_pipe | user.s8_1 | 68 | 665 | 2016-05-03 00:06:56.000000 | 2016-05-03 00:06:56.000000 | user.s8_1{autoCommit=true}():void; | 8 | 886 | 0 | 50 | 0 | | 47@0 | monetdb | 2016-05-03 00:07:33.000000 | select * from sys.querylog_calls; | default_pipe | user.s9_1 | 73 | 282 | 2016-05-03 00:07:33.000000 | 2016-05-03 00:07:34.000000 | user.s9_1{autoCommit=true}():void; | 9 | 2515 | 0 | 0 | 0 | +------+---------+----------------------------+-------------------------------------+--------------+-----------+------+----------+----------------------------+----------------------------+------------------------------------------+--------+------+------+------+------+ 6 tuples (152.709ms) Hence, maybe the documentation should be updated to remove the former? Also, two questions remain unanswered by the current documentation: 1) What is the unit of "mal integer, -- size of MAL plan"? bytes? lines? statements? 2) What exactly does "optimize bigint -- time in microseconds" measure? Only the time taken by MAL optimizers (i.e., after SQL parsing, relational optimization and relational-to-MAL translation, but before actual execution)? If so, is there still a way to measure the time(s) for SQL parsing, relational optimization, and (or) relational-to-MAL translation? Thanks! Stefan -- | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |

1 0

Help with json.filters
by Alberto Ferrari 03 May '16

03 May '16

Can anyone help with json.filters? I have this example table ... CREATE TABLE tblJson ( "id" INT, "json_data" JSON, ); ... with these values... INSERT INTO tblJson (id, json_data) VALUES (1, '{"data":{"time":"2015-10-09 10:36:35","others":"xxx"}}'), (2, '{"data":{"time":"2015-10-09 12:18:20","others":"yyy"}}'), (3, '{"data":{"time":"2015-12-30 16:31:22","others":"zzz"}}'); The data is ok, I can select the rows, it's ok. Also I can select only the "time" field, like this... SELECT json.filter(json_data, 'data.time') FROM tblJson; But now I want to filter the content in "json_data" specific field, i.e. SELECT all the rows where "time like '2015-10-09%'" , or maybe "time = '2015-10-09 12:18:20'" I tried but can't do it. Any help? Thanks!

3 5

Problems with aggregate UDF (subaggregate undefined error)
by Anderson, David B 02 May '16

02 May '16

All, I have been able to create and install my own aggregate UDF (weightedpercentile), but when I try to execute it via mclient I am getting the following error: sql>select key, weightedpercentile(a,b) from t group by key; TypeException:user.s9_1[20]:'udf.subweightedpercentile' undefined in: X_34:bat[:any,:dbl] := udf.subweightedpercentile(X_26:bat[:oid,:dbl],X_33:bat[:oid,:dbl],X_16:bat[:oid,:oid],r1_16:bat[:oid,:oid],true:bit); program contains errors Does anyone know what I am doing incorrectly? I am at a total loss now. The error is almost identical to the error I get if I try to execute the corr() aggregate: sql>select key, corr(a,b) from t group by key; TypeException:user.s10_1[20]:'aggr.subcorr' undefined in: X_34:bat[:any,:dbl] := aggr.subcorr(X_26:bat[:oid,:dbl],X_33:bat[:oid,:dbl],X_16:bat[:oid,:oid],r1_16:bat[:oid,:oid],true:bit); program contains errors The corr() error is referenced in a recent bug submission (which I can't find in Bugzilla) If I look in the system function catalog, I see: sql>select * from sys.functions where name like '%weight%'; +------+--------------------+---------------------------------------------------+------+------+------+-------+-------+-------+------+ | id | name | func | mod | lang | type | side_ | varre | varar | sche | : : : : : uage : : effec : s : g : ma_i : : : : : : : : t : : : d : +======+====================+===================================================+======+======+======+=======+=======+=======+======+ | 9313 | weightedpercentile | -- weighted percentile | udf | 1 | 3 | false | false | false | 2000 | : : : create aggregate weightedpercentile(e1 real, e2 r : : : : : : : : : : : eal) returns real : : : : : : : : : : : external name udf.weightedpercentile; : : : : : : : : | 9318 | weightedpercentile | -- weighted percentile | udf | 1 | 3 | false | false | false | 2000 | : : : create aggregate weightedpercentile(e1 double, e2 : : : : : : : : : : : double) returns double : : : : : : : : : : : external name udf.weightedpercentile; : : : : : : : : +------+--------------------+---------------------------------------------------+------+------+------+-------+-------+-------+------+ 2 tuples (1.107ms) So my function appears to be installed. I have in (80_udf.sql): -- weighted percentile create aggregate weightedpercentile(e1 real, e2 real) returns real external name udf.weightedpercentile; -- weighted percentile create aggregate weightedpercentile(e1 double, e2 double) returns double external name udf.weightedpercentile; and in (udf.mal): # BAT MAL signatures module batudf; command weightedpercentile(one:bat[:oid,:bte],two:bat[:oid,:bte]):lng address UDFBATweightedpercentile comment "compute the weighted percentile (bte)" command weightedpercentile(one:bat[:oid,:int],two:bat[:oid,:int]):lng address UDFBATweightedpercentile comment "compute the weighted percentile (int)" command weightedpercentile(one:bat[:oid,:lng],two:bat[:oid,:lng]):lng address UDFBATweightedpercentile comment "compute the weighted percentile (lng)"

2 3

Problem make-install'ing MonetDB to $HOME/opt/monetdb
by Eyal Rozenberg 02 May '16

02 May '16

I'm trying to upgrade to the latest release of MonetDB (Jul2015-SP4) instead of an older one. I'm building and installing as a non-root user, into $HOME/opt/monetdb . Well, * ./bootstrap SUCCEEDED * ./configure --prefix=$HOME/opt/monetdb SUCCEEDED * ./make SUCCEEDED * ./make install FAILED Here's the error message: /usr/bin/install -c -m 644 mapi.h /home/eyalroz/opt/monetdb/include/monetdb/mapi.h /bin/sh ../../libtool --mode=install /usr/bin/install -c libmapi.la '/home/eyalroz/opt/monetdb/lib' libtool: install: error: cannot install `libmapi.la' to a directory not ending in /usr/local/lib I don't see why libtool is telling me that. What business is it of libtool what I want to call my directories? Anyway, it doesn't seem to me I tried to do something esoteric... how do you suggest I get around this? Eyal PS - It's a Fedora 22 machine in case that matters.

2 2

Explain analyze
by Shmagi Kavtaradze 02 May '16

02 May '16

In postgres I could use explain analyze to see actions performed by database and their cost. I was not able to run explain analyze. I ran EXPLAIN, but can not see any plan or cost. Any ideas how to get costs and query plan like in Postgres?

4 7

Crystal bindings for MonetDB
by Brian Hood 30 Apr '16

30 Apr '16

Hi Guys, I have got my Crystal Language bindings for MonetDB now working quite well. https://github.com/puppetpies/crystal-monetdb-libmapi I've seen options in the mapi code for a variable called mode that allow you format the response data in XML but it doesn't seem to be implemented fully in the mapi code. Are there any guide lines to processing this RAW query output. % threatmonitor.fruits, threatmonitor.fruits, threatmonitor.fruits, threatmonitor.fruits, threatmonitor.fruits # table_name % name, price, weight, comments, id # name % varchar, varchar, int, clob, int # type % 9, 4, 2, 11, 1 # length [ "Apple", "9.99", 50, NULL, 1 ] [ "Bananna", "3.99", 30, NULL, 2 ] [ "Orange", "7.99", 60, NULL, 3 ] [ "Peach", "5.00", 80, NULL, 4 ] [ "Kiwi", "9.00", 20, NULL, 5 ] [ "Tomato", "2.00", 20, "Yes a fruit", 6 ] [ "Pear", "4.00", 30, "Juicy", 7 ] [ "Nectarine", "6.00", 50, "Juicy", 8 ] Regards, Brian Hood

1 0

Case sensitive
by Ariel Abadi 30 Apr '16

30 Apr '16

Hi All! We are facing a problem, we used to work with mysql with the case insensitive. Now we have moved into MonetDB and we are facing a problem with this issue. Some of our queries are not bringing any data because of the sensitive.. SELECT * FROM Country = 'jamaica' ... 0 Records SELECT * FROM Country = 'Jamaica' ... 10 Records Some cases we solved it using the ILIKE, but we still have some code on our app that cannot be modified. Does anybody know if there is any flag or setup or config for the Database in Monet to make it insensitive ? Thanks in advance for the help!

6 6

Support for multi-column aggregate functions
by Anderson, David B 30 Apr '16

30 Apr '16

All, Does the current version of MonetDB support multi-column aggregate functions? I need to create a function which returns the "quantiles" of one column using another column as a weighting. It looks like PostgreSQL supports multi-column aggregates as of 8.2. I could write an embedded R function to compute this, but want to know if it is even possible to pass in multiple columns. Thanks, Dave

4 9