July 2020 - users-list

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

Error with concurrent inserts via PymonetDB mapi
by Ioannis Foufoulas 13 Jul '20

13 Jul '20

Hi, I have a weird error when I am sending the following queries in parallel from 2 different python clients with MAPI: insert into table1 select numpy_count(c1) as c1 from view; insert into table2 select numpy_count(c1) as c1 from view2; where numpy_count is: CREATE or replace AGGREGATE numpy_count(val INTEGER) RETURNS INTEGER LANGUAGE PYTHON { import time time.sleep(3) return val.size }; The error is: pymonetdb.exceptions.OperationalError: GDK reported error: GDKreleasesem: semctl failed: Invalid argument However, if I run these 2 queries in parallel from 2 instances of mclient they work and AFTER doing this, they also work from python’s client with mapi. Is there any hint on this? Best, Yannis

2 2

mserver5 options
by - - 12 Jul '20

12 Jul '20

Hi, How to pass/set mserver5 options via monetdb(d) ? I’m trying to get started monetdb in in-memory mode. mserver5 has option --in-memory Run database in-memory only however all attempts to pass it were unsuccessful. -- - -

2 4