- users-list - monetdb.org

enhancements question
by ALEX OSSIPOV 25 Jul '20

25 Jul '20

Hi MonetDB Team I am very interested in pushing bug 6702 which is in fact an enhancement (was enhancement request) but seems to be more of a miss these days since all other competing products have it already implemented. What is decision process on prioritizing enhancements and how can a specific request be expedited/elevated? Love this product but can't survive without recursive support in modern analytics world. Regards Alex

2 1

MonetDb crashes on a join using date between from and until date
by Groot, Frank (Sociale Verzekeringsbank) 22 Jul '20

22 Jul '20

Hi all, We are using the June 2020 release, trying to query a table with 24 mio rows and join each row from this 'left' table with one row from a right table according to a specific join clause. This clause does not result in a carthesian product. The query structure is as follows: select left.* from myschema.mytable left inner join otherschema.yourtable right on left.somedatefield >= right.fromdate and left.somedatefield <= right.untildate where left.otherdatefield is null ; This query results in following error in the merovingian.log: 2020-07-17 11:58:00 MSG merovingian[401281]: database 'datalake' (-1) has crashed with signal SIGSEGV (dumped core) However, if I change the query like below, making the left table a derived table and sorting the date field used in the join, the error doesn't occur: select left.* from (select * from myschema.mytable order by somedatefield) left inner join otherschema.yourtable right on left.somedatefield >= right.fromdate and left.somedatefield <= right.untildate where left.otherdatefield is null ; When I tested the original query with a subset from the left table there was a threshold of a certain amount of rows (in this case 26 rows) below which the query ran fine. Also, repeating the test on a different 'left' table with ~200k rows didn't result in a crash. Could it be that there's been a change in the query pipeline or join handling? Kind regards, Frank

2 1

Partition pruning for merge tables
by Daniel Glöckner 22 Jul '20

22 Jul '20

Hi, looking at merge tables and the related query plans I was expecting the planner to skip tables depending on partition information and query predicates. I'm using the official example for merge tables: https://www.monetdb.org/blog/updatable-merge-tables CREATE MERGE TABLE splitted (stamp TIMESTAMP, val INT) PARTITION BY RANGE ON (stamp); CREATE TABLE first_decade (stamp TIMESTAMP, val INT); CREATE TABLE second_decade (stamp TIMESTAMP, val INT); CREATE TABLE third_decade (stamp TIMESTAMP, val INT); ALTER TABLE splitted ADD TABLE first_decade AS PARTITION FROM TIMESTAMP '2000-01-01 00:00:00' TO TIMESTAMP '2010-01-01 00:00:00'; ALTER TABLE splitted ADD TABLE second_decade AS PARTITION FROM TIMESTAMP '2010-01-01 00:00:00' TO TIMESTAMP '2020-01-01 00:00:00'; ALTER TABLE splitted ADD TABLE third_decade AS PARTITION FROM TIMESTAMP '2020-01-01 00:00:00' TO RANGE MAXVALUE WITH NULL VALUES; INSERT INTO splitted VALUES (TIMESTAMP '2000-01-01 00:00:00', 1), (TIMESTAMP '2002-12-03 20:00:00', 2), (TIMESTAMP '2012-05-12 21:01:00', 3), (TIMESTAMP '2019-12-12 23:59:59', 4); INSERT INTO splitted VALUES (TIMESTAMP '2020-01-01 00:00:00', 5), (NULL, 6); When I trigger the following query I was expecting that only table second_decade would be used as Monet would be smart enough to check the partition information for each table. Kind regards, Daniel sql>plan select * from splitted where stamp = TIMESTAMP '2020-01-01 00:00:00'; +--------------------------------------------------------------------------------------------------------------------------------------+ | rel | +======================================================================================================================================+ | union ( | | | union ( | | | | project ( | | | | | select ( | | | | | | table(sys.first_decade) [ "first_decade"."stamp" as "splitted"."stamp", "first_decade"."val" as "splitted"."val" ] COUNT | | | | | ) [ "splitted"."stamp" = timestamp(7) "2020-01-01 00:00:00.000000" ] | | | | ) [ "splitted"."stamp", "splitted"."val" ], | | | | project ( | | | | | select ( | | | | | | table(sys.second_decade) [ "second_decade"."stamp" as "splitted"."stamp", "second_decade"."val" as "splitted"."val" ] COUNT | | | | | ) [ "splitted"."stamp" = timestamp(7) "2020-01-01 00:00:00.000000" ] | | | | ) [ "splitted"."stamp", "splitted"."val" ] | | | ) [ "splitted"."stamp", "splitted"."val" ], | | | project ( | | | | select ( | | | | | table(sys.third_decade) [ "third_decade"."stamp" as "splitted"."stamp", "third_decade"."val" as "splitted"."val" ] COUNT | | | | ) [ "splitted"."stamp" = timestamp(7) "2020-01-01 00:00:00.000000" ] | | | ) [ "splitted"."stamp", "splitted"."val" ] | | ) [ "splitted"."stamp", "splitted"."val" ] | +--------------------------------------------------------------------------------------------------------------------------------------+ 19 tuples clk: 2.428 ms

2 4

Re: Re: Implementing bulk export to Parquet
by Daniel Glöckner 20 Jul '20

20 Jul '20

Hi, thanks for the implementation hint with Arrow C++, Anton. I’ve tried a similar approach using Pyarrow () and initial tests look promising. NumPy arrays can be written to Parquet quite easily: https://www.mail-archive.com/dev@parquet.apache.org/msg04506.html <https://www.mail-archive.com/dev@parquet.apache.org/msg04506.html> Kind regards, Daniel

1 0

BUG on last version?
by Ariel Abadi 20 Jul '20

20 Jul '20

Hi! Maybe somebody can help me to understand if there is a bug on the latest version or if it is a better way to solve this issue. Linux Centos 7 Monetdb version 11.37.7 (Jun2020) I have created a new fresh database to avoid any issue on the upgrade, and imported all the data. The DB seems to be working fine. We are using creating some dynamic queries, so the final query we need to run is the following: select sum(nrm_realmin) as nrm_realmin from sb_traf.nrm_20200716 as t0 where (case when (nrm_tim_date || '@' || nrm_vep_id || '@' || nrm_rby_brn_id) in ( select distinct cast(curdate() - cast(value as interval day) as varchar(10)) || '@' || pbd_ven_id || '@' || pbd_deb_id as xx from sb_agre.agre_agr_agreement as agr join sb_agre.agre_prb_product_buy as prb on agr_id = prb_agr_id join sb_agre.agre_pbd_product_buydetail as psd on prb_id = pbd_prb_id left join sys.generate_series(0, 2) on (curdate() - cast(value as interval day)) between coalesce(prb_dstart, agr_dstart) and coalesce(prb_dend, agr_dend) and (curdate() - cast(value as interval day)) <= curdate() ) then 'BILAT' else 'AVAILABLE' end) not ilike '%BILAT%' I am using this exact query on some other versions and they are all working, but on this version the query keeps running with no answer, even worse, I tried to stop the query via CALL sys.stop(tag) ... and it never stops, I had to restart the database to stop that query. Does anybody have a clue how I can solve this issue? Thanks for the help in advance! Rgds Ariel <http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_cam…> Virus-free. www.avg.com <http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_cam…> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

2 1

Issues with None values handling in Python UDFs returning TABLE
by Daniel Glöckner 20 Jul '20

20 Jul '20

Hi, when testing TABLE-returning UDFs, I noticed that there are some issues with handling of None values from Python. Is there any magic approach on how to return NULLS / None values from such a UDF? Please see my examples below which are all not working ;) Kind regards, Daniel # 1 Does not work - Henk is just repeated in the second row drop function if exists python_table; CREATE FUNCTION python_table() RETURNS TABLE(name STRING, country STRING, age INTEGER) LANGUAGE PYTHON { result = dict() result['name'] = ['Henk', None, 'Elizabeth'] result['country'] = ['NL', 'USA', 'UK'] result['age'] = [25, 30, 33] return result }; SELECT * FROM python_table() ; # 2 Also does not work - Henk is just repeated in the second row drop function if exists python_table; CREATE FUNCTION python_table() RETURNS TABLE(name STRING, country STRING, age INTEGER) LANGUAGE PYTHON { result = dict() names = numpy.empty(3, dtype=object) names[0] = 'Henk' names[2] = 'Elizabeth' result['name'] = names result['country'] = ['NL', 'USA', 'UK'] result['age'] = [25, 30, 33] return result }; SELECT * FROM python_table() ; # 3 Also does not work - Henk is just repeated in the second row drop function if exists python_table; CREATE FUNCTION python_table() RETURNS TABLE(name STRING, country STRING, age INTEGER) LANGUAGE PYTHON { result = dict() names = numpy.full_like(numpy.arange(3, dtype=object), None) #names[0] = 'Henk' names[2] = 'Elizabeth' result['name'] = names result['country'] = ['NL', 'USA', 'UK'] result['age'] = [25, 30, 33] return result }; SELECT * FROM python_table() ; # 4 breaks if all values for a column are None # "Error converting dict return value "name": An array of size 3 was returned, yet we expect a list of 1 columns. The result is invalid.. " drop function if exists python_table; CREATE FUNCTION python_table() RETURNS TABLE(name STRING, country STRING, age INTEGER) LANGUAGE PYTHON { result = dict() names = numpy.full_like(numpy.arange(3, dtype=object), None) result['name'] = names result['country'] = ['NL', 'USA', 'UK'] result['age'] = [25, 30, 33] return result }; SELECT * FROM python_table() ;

1 0

Implementing bulk export to Parquet
by Daniel Glöckner 19 Jul '20

19 Jul '20

Hi, the project which I'm involved in would require us to integrate MonetDB with "big data technologies" so exporting data from MonetDB to Parquet for further processing in the big data tools would be an obvious choice. Can anyone guide me what it would mean to extend MonetDB with a writer for Parquet? It would be awesome if COPY INTO would be able to produce Parquet efficiently, e.g. using Arrow library: https://arrow.apache.org/docs/cpp/parquet.html Kind regards, Daniel

3 2

MonetDB/e and R
by Mark Clements 17 Jul '20

17 Jul '20

It is exciting to see the release of MonetDB/e - congratulations. I have two questions to offer. First, has anyone begun work on an R package? As a follow-up question if not, which of MonetDB.R, MonetDBLite or DuckDB packages for R would be a better starting point? Second, could someone kindly explain the potential advantages and disadvantages of using either MonetDB/e or DuckDB, please? They both seem to come from CWI, so this is a little confusing. Sincerely, Mark. När du skickar e-post till Karolinska Institutet (KI) innebär detta att KI kommer att behandla dina personuppgifter. Här finns information om hur KI behandlar personuppgifter<https://ki.se/medarbetare/integritetsskyddspolicy>. Sending email to Karolinska Institutet (KI) will result in KI processing your personal data. You can read more about KI’s processing of personal data here<https://ki.se/en/staff/data-protection-policy>.

3 2

PHP insert performance
by - - 16 Jul '20

16 Jul '20

I have run the series of INSERT tests in PHP for monetdb and SAP SQLAnywhere. The tests show: performance of the monetdb on INSERT operations is almost twice slower against SQLA with the equal conditions (DDL, index, data, autocommit, 10K records), monetdb is in in-memory mode . What can be a reason? p.s. It is not benchmark, I just used the SQLA test server already running. -- - -

4 12

News on MonetDB
by Martin Kersten 14 Jul '20

14 Jul '20

Hi, In recent weeks we have issued a few blog posts on the MonetDB site and announced them on Twitter. The latest one is a demonstration of MonetDB as a drop in library in Python. see https://monetdb.org/blog/taking-MonetDBe-for-a-taxi-ride Feedback on the embedded version of MonetDB is always welcome. regards, Martin Kersten

1 0