- users-list - monetdb.org

Default username and password: documented?
by Stefan Manegold 14 Sep '20

14 Sep '20

Hi, I just realize that, while the default (system-/admin-)username and password for a newly generated database in MonetDB are well known in the experienced MonetDB community, where do novice users find this information? I cannot find any explicit (or at least implicit?) documentation of this information, but maybe it's just that I fail to search properly? Any help is highly appreciated! Thanks! Best, 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) |

3 3

Why MonetDB uses Materialization
by Amit Pandey 11 Sep '20

11 Sep '20

Hey All, Thanks for the great open source project. So while going through some materials in a CMU DB course I saw it mentioned that MonetDB uses full materialization, and this is a bit weird since in c-store potentially scanning a billion columns a full materialization may take a lot of time and the classic volcano model might be better. Can you guys please explain the rationale ? These are the slides :- https://15445.courses.cs.cmu.edu/fall2019/slides/12-queryexecution1.pdfMone… is mentioned in Slide 11. Thanks and Regards

3 7

Bad query performance after lots of updates
by Daniel Glöckner 01 Sep '20

01 Sep '20

Hi, select count(*) from table where field = 'some value'; --> a few ms update table set field = 'value'; --> update 2 million rows out of 1 billion rows select count(*) from table where field = 'some value'; --> almost a minute :( Analyze did not help and sys.vacuum could not be used as the table has indexes (foreign key). Re-starting the DB helped ;) What's the recommended way to make MonetDB perform well after lots of updates? Kind regards, Daniel

2 1

compilers
by Roberto Cornacchia 01 Sep '20

01 Sep '20

Hi, I was wondering whether anyone has run any benchmarks with MonetDB for testing the effect of compilation options. For example, any combination of * gcc vs clang * -O1 vs -O2 vs -O3 * -march and -mtune I am actually planning to do this myself, but knowing about any previous evidence (even anecdotal) would be helpful. Cheers, Roberto

1 0

custom joins in Jun2020
by Roberto Cornacchia 27 Aug '20

27 Aug '20

This is a question about join order. In general, about how it changed from Nov2019 to Jun2020 releases. In particular, with respect to custom joins (filter functions). With a schema: CREATE TABLE t1(s string); CREATE TABLE t2(s string); Consider the following 2 queries, which only differ for having swapped conditions: Q1: SELECT t1.s, t2.s FROM t1, t2 WHERE t1.s <> t2.s AND [t1.s] maxlevenshtein [t2.s, 1]; Q2: SELECT t1.s, t2.s FROM t1, t2 WHERE [t1.s] maxlevenshtein [t2.s, 1] AND t1.s <> t2.s; [t1.s] maxlevenshtein [t2.s, 1] is equivalent to levenshtein(t1.s, t2.s) <=1 (i.e. the two strings have a levenshtein distance at most 1) This is a relatively expensive and selective function. In Nov2019, both Q1 and Q2 are translated to: - "maxlevenshtein" custom join - "!=" selection on the result In Jun2020, the two queries happen to be evaluated in the same order as they are written. Which means that Q2 is evaluated as: - "!=" join - "maxlevenshtein" selection This last evaluation plan is unfortunately not viable at all. The first join is not very selective, and the "maxlevenshtein" selection is run on way too many pairs, without the optimizations that can be exploited in a join (in a join, it is possible to skip the actual levenshtein computation for most combinations). Q2 in Jun2020 is 2 orders of magnitude slower than Q1, which quickly leads to unreasonably long query times. Of course, this is just one specific case. A very unfortunate one, due to the combination of a couple of factors: - The "!=" join is not selective enough - The custom function is an expensive one I guess my real questions are: - Is it by chance (or, as a by-product of more generic join ordering rules) that Nov2019 executes custom joins first in both Q1 and Q2, or was it an intentional choice to first execute custom joins? - What would a reasonable approach be? Is it reasonable to assume that if one writes a custom join, this is expected to use an expensive comparison function and that the join implementation can be much more efficient than the selection implementation (by skipping unnecessary comparisons)? If no assumptions can be made, can there be a way to annotate custom implementations with information on selectivity and cost? Thanks for you input, Roberto

1 1

Loading timestamps via Python loader functions
by Daniel Glöckner 26 Aug '20

26 Aug '20

Hi, there seems to be a limitation for Python-based loader functions wrt timestamps. Looking at the implementation in sql/backends/monet5/UDF/pyapi3/convert_loops.h suggests that timestamps are indeed not covered. Both of the the 2 following examples fail with "Failed conversion: MALException:pyapi3.eval:PY000!Unrecognized type. Could not convert to NPY_UNICODE.". CREATE LOADER array_loader() LANGUAGE PYTHON { from datetime import datetime _emit.emit( { 'a': [1,2,3], 'b': [datetime.utcnow(), datetime.utcnow(), datetime.utcnow()], 'c': ['1', '2', '3']}) }; CREATE LOADER array_loader() LANGUAGE PYTHON { import numpy as np from datetime import datetime dt = datetime.utcnow() _emit.emit( { 'a': [1,2,3], 'b': np.array([dt, dt, dt], dtype=np.datetime64), 'c': ['1', '2', '3']}) }; Is anyone aware of a workaround? An obvious workaround is to insert the timestamps as strings and convert them via SQL but that's much less efficient. Kind regards, Daniel

1 0

result properties in pcre.c
by Roberto Cornacchia 25 Aug '20

25 Aug '20

Hi, I was looking at pcre.c for some inspiration and found something suspicious. At the end of both pcre_likeselect() and re_likeselect(): if (bn && !msg) { BATsetcount(bn, BATcount(bn)); /* set some properties */ bn->tsorted = true; bn->trevsorted = bn->batCount <= 1; bn->tkey = true; bn->tseqbase = bn->batCount == 0 ? 0 : bn->batCount == 1 ? * (oid *) Tloc(bn, 0) : oid_nil; } Which I read as: if everything went well, then the result is sorted and key. But I miss why it should be sorted and key. Roberto

2 2

Re: Re: MonetDB on IBM Power architecture
by Daniel Glöckner 20 Aug '20

20 Aug '20

Hi, This looks very promising so far. Building on Power 8 was easy, just following the official guide from the wiki. https://www.monetdb.org/wiki/MonetDB:Building_from_sources So far I’ve only loaded larger amounts of data and the timings look good. Will perform more tests over the next days but overall this looks like absolutely usable for production. Kind regards, Daniel

1 0

JDBC Driver
by Walter Weinmann 19 Aug '20

19 Aug '20

Including the JDBC driver with gradle implementation 'monetdb:monetdb-java-lite:2.38' gives me: FAILURE: Build failed with an exception. * Where: Build file 'D:\SoftDevelopment\Projects\Konnexions\db_seeder\build.gradle' line: 66 * What went wrong: Could not determine the dependencies of task ':fatJar'. > Could not resolve all files for configuration ':runtimeClasspath'. > Could not find monetdb:monetdb-jdbc-new:2.37-SNAPSHOT. Searched in the following locations: - https://jcenter.bintray.com/monetdb/monetdb-jdbc-new/2.37-SNAPSHOT/maven-me… - https://jcenter.bintray.com/monetdb/monetdb-jdbc-new/2.37-SNAPSHOT/monetdb-… - https://repo.maven.apache.org/maven2/monetdb/monetdb-jdbc-new/2.37-SNAPSHOT… - https://repo.maven.apache.org/maven2/monetdb/monetdb-jdbc-new/2.37-SNAPSHOT… - https://jitpack.io/monetdb/monetdb-jdbc-new/2.37-SNAPSHOT/maven-metadata.xml - https://jitpack.io/monetdb/monetdb-jdbc-new/2.37-SNAPSHOT/monetdb-jdbc-new-… - http://maven.cubrid.org/monetdb/monetdb-jdbc-new/2.37-SNAPSHOT/maven-metada… - http://maven.cubrid.org/monetdb/monetdb-jdbc-new/2.37-SNAPSHOT/monetdb-jdbc… Required by: project : > monetdb:monetdb-java-lite:2.38 * Try: Run with --stacktrace option to get the stack trace. Run with --info or --debug option to get more log output. Run with --scan to get full insights.

3 2

Re: Re: MonetDB on IBM Power architecture
by Daniel Glöckner 19 Aug '20

19 Aug '20

Hi, Thanks a lot for confirming this. We’ll perform some basic tests on our Power 8 system to see if we’re able to build and run it. Kind regards, Daniel

1 0

MonetDB on IBM Power architecture
by Daniel Glöckner 19 Aug '20

19 Aug '20

Hi folks, I was wondering if one could run MonetDB on IBM Power architecture (Power 8 and later). I guess there's no official build for this? Did anyone try this out? Kind regards, Daniel

2 1

java.sql.BatchUpdateException: Error(s) occurred while executing the batch, see next SQLExceptions for details
by Walter Weinmann 18 Aug '20

18 Aug '20

*Without addBatch / executeBatch my program runs without problems:* [main] Start [main] tickerSymbolExtern='monetdb' [main] Start MonetDB [main] database table COUNTRY - 200 rows in total [main] database table TIMEZONE - 11 rows in total [main] database table COUNTRY_STATE - 500 rows so far [main] database table COUNTRY_STATE - 600 rows in total [main] database table CITY - 500 rows so far [main] database table CITY - 1000 rows so far [main] database table CITY - 1500 rows so far [main] database table CITY - 1800 rows in total [main] database table COMPANY - 500 rows so far [main] database table COMPANY - 1000 rows so far [main] database table COMPANY - 1500 rows so far [main] database table COMPANY - 2000 rows so far [main] database table COMPANY - 2500 rows so far [main] database table COMPANY - 3000 rows so far [main] database table COMPANY - 3500 rows so far [main] database table COMPANY - 4000 rows so far [main] database table COMPANY - 4500 rows so far [main] database table COMPANY - 5000 rows so far [main] database table COMPANY - 5400 rows in total [main] duration in seconds: 52 [main] End MonetDB [main] End *With addBatch / executeBatch and a batch size of 1000 I get the following error:* [main] Start [main] tickerSymbolExtern='monetdb' [main] Start MonetDB [main] database table COUNTRY - 200 rows in total [main] database table TIMEZONE - 11 rows in total [main] database table COUNTRY_STATE - 500 rows so far [main] database table COUNTRY_STATE - 600 rows in total [main] database table CITY - 500 rows so far [main] database table CITY - 1000 rows so far [main] database table CITY - 1500 rows so far java.sql.BatchUpdateException: Error(s) occurred while executing the batch, see next SQLExceptions for details at nl.cwi.monetdb.jdbc.MonetStatement.executeBatch(Unknown Source) at ch.konnexions.db_seeder.jdbc.AbstractJdbcSeeder.createDataInsert(AbstractJdbcSeeder.java:501) at ch.konnexions.db_seeder.jdbc.AbstractJdbcSeeder.createData(AbstractJdbcSeeder.java:404) at ch.konnexions.db_seeder.jdbc.AbstractJdbcSeeder.createData(AbstractJdbcSeeder.java:362) at ch.konnexions.db_seeder.DatabaseSeeder.main(DatabaseSeeder.java:141) Processing of the script was aborted, error code=1 *Both times the connection runs with autoCommit.*

3 2

java.sql.SQLException: DROP USER: 'kxn_user' owns a schema
by Walter Weinmann 18 Aug '20

18 Aug '20

*The following SQL statements are executed successfully:* SELECT count(*) FROM sys.users WHERE name = 'kxn_user' DROP SCHEMA IF EXISTS kxn_schema CASCADE CREATE USER kxn_user WITH UNENCRYPTED PASSWORD 'monetdb' NAME 'Dbseeder User' SCHEMA sys CREATE SCHEMA kxn_schema AUTHORIZATION kxn_user ALTER USER kxn_user SET SCHEMA kxn_schema CREATE TABLE COUNTRY ( PK_COUNTRY_ID BIGINT NOT NULL PRIMARY KEY, COUNTRY_MAP BLOB, CREATED TIMESTAMP NOT NULL, ISO3166 VARCHAR(50), MODIFIED TIMESTAMP, NAME VARCHAR(100) NOT NULL UNIQUE ) *The following SQL statement produces an error message:* DROP USER kxn_user java.sql.SQLException: DROP USER: 'kxn_user' owns a schema at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(Unknown Source) at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(Unknown Source) at nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(Unknown Source) at nl.cwi.monetdb.jdbc.MonetStatement.execute(Unknown Source) at ch.konnexions.db_seeder.jdbc.AbstractJdbcSeeder.dropUser(AbstractJdbcSeeder.java:814) at ch.konnexions.db_seeder.jdbc.monetdb.MonetdbSeeder.setupDatabase(MonetdbSeeder.java:103) at ch.konnexions.db_seeder.jdbc.AbstractJdbcSeeder.createData(AbstractJdbcSeeder.java:359) at ch.konnexions.db_seeder.DatabaseSeeder.main(DatabaseSeeder.java:141) Processing of the script was aborted, error code=1 *How can I delete the user in this constellation without errors ?*

2 1

New Elixir driver + some questions and feedback
by Karl Seguin 02 Aug '20

02 Aug '20

I've been working on a driver for Elixir (https://github.com/karlseguin/monet) It's immature, but in a decent enough state for our prototyping. Overall, I've been very pleased and excited about MonetDB. I wanted to share some observations from the perspective of both a newcomer and having developed a driver. First though, a couple questions: 1. I understand what a Q_BLOCK is, but I don't understand under what circumstances it happens. 2. Aside from the initial challenge response, it seems like all messages from the server are _always_ in response to a query. This is different than, say, PostgreSQL which can push data to signal some change, outside of the typical request->response flow. Correct? As for my feedback: 1. The first and, by far, single biggest issue is with the text-base protocol. I'm sure this has been discussed before, and I doubt that I can add any new insight. But it was tedious to implement and error prone. We were initially considering storing large blobs in MonetDB (for a secondary purpose), but I'm now rethinking that idea because of the performance for serializing and deserializing the data. The rest of these issues are in no particular order. 2. I'm pretty sure if you send an invalid x command, the server goes haywire. The simplest way I found to reproduce this is to change the 's' to 'x' in the official Node client (https://github.com/MonetDB/monetdb-nodejs/blob/19f24ec16db70a26d0b11e51d77b…) and connect. The CPU load spikes and the logs are filled with: "createExceptionInternal: !ERROR: SQLException:SQLparser:42000!Unrecognized X command: SET TIME ZONE INTERVAL '-480' MINUTE;"" (I'm running: 17ab9b680ec55b342c08d034f2c5659a073cc74a) 3. Maybe this is naive, but I don't understand why this works: insert into x (time_col) values ('10:30:50') but this doesn't: exec 7('10:30:50') I was hoping I could ignore the response from a "prepare" (except for getting the id from the first line) and just encode based on the argument types, but no. I need the type AND precision so that I can do "time(3) '...'" or "blob '....'" etc. 4. Point 3 is made marginally worse because: prepare select 1 - ? returns 2 rows. I look for the suffix "NULL,\tNULL,\tNULL\t]" to tell which are "real" placeholders. I'm not sure if that's correct. 5. I was surprised to see dates with 1, 2 and 3 digit years. Making it 4 digits would make it ISO8601 compliant. 6. I was caught off guard by the fact that ab error can show up as part of what seems like a healthy response. Initially, I was just looking for a response that stars with !. But while testing, a concurrent create table failed and I got "&3 X Y\n!CODE!ERR\n". Is there a rule that can help me parse these efficiently? (like they'll always be on the first or 2nd line), or can they really show up anywhere? 7. Some of the drivers appear to be in bad shape. The node driver gets confused by my point 4 above and fails to handle it. It also doesn't support time or blob values, and tries to JSON the built-in Date object. I couldn't get the go driver to work at all (nil referencse trying to read rows). The ruby driver has no support for prepared statements and I'm pretty sure it doesn't return the correct value for: db.query("select '\\\\'") 8. While I'm probably wrong, I can't help but think that merging the functionalities of the monetdbd and monetdb binaries would streamline the experience for newcomers. Please don't interpret these as unkind criticisms of (or unhappiness with) MonetDB. Again, overall, very happy.

2 1

Creation of a remote table overhead
by Ioannis Foufoulas 28 Jul '20

28 Jul '20

Hi, I am trying to develop a Python client which sends queries to different Monetdb servers and transfers the results between them using the remote tables functionalities. I’ve seen than my main overhead is execution of CREATE REMOTE TABLE statement. It requires 70-90msec so in cases with many transfers and fast and small queries this makes a difference. After looking at some older versions I’ve seen that the creation of a remote table was very fast before August 2018 (this is when authentication added). Is there any hint on this? Could the. authentication strategy change in someway so that it runs faster or is it possible to select among different authentication methods for the remote tables? Best, Yannis

4 7

MonetDB on GitHub
by Roberto Cornacchia 27 Jul '20

27 Jul '20

Hi, I was wondering whether the MonetDB repositories on GitHub (or any git-based platform) are, in your intentions, there to stay. That's because I'm planning to move our own fork from Mercurial to git. This would come very easy by using the GitHub repositories as upstreams. My second question though is: the git repositories are at this moment about 10 days behind. Is there a reason? Will that be improved? Thanks! Roberto

2 2

Announcement: New Jun2020-SP1 Bugfix release of MonetDB suite
by Sjoerd Mullender 27 Jul '20

27 Jul '20

The MonetDB team at MonetDB BV is pleased to announce the Jun2020-SP1 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <https://www.monetdb.org/>. For details on this release, please see the release notes at <https://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <https://www.monetdb.org/downloads/>. Jun 2020-SP1 bugfix release (11.37.11) MonetDB Common * Hash buckets come in variable widths. But if a BAT grows long enough so that the BAT indexes that are stored in the buckets don't fit anymore, the buckets need to be widened. This is now fixed. Bug Fixes * 6844: sys.getUser('https://me:pw@www.monetdb.org/Doc') does not return the user: me * 6845: the url sys.get...(url) functions do not allow null as a parameter * 6858: json.keyarray(json '{ "":0 }') fails with error: Could not allocate space * 6859: only first character of the separator string in json.text(js json, sep string) is used * 6873: sys.hot_snapshot() creates incomplete snapshots if the write-ahead log is very large * 6876: tar files created by sys.hot_snapshot() produce warnings on some implementations of tar * 6877: MonetDB produces malformed LZ4 files * 6880: Left fuzzy queries are much slower than other fuzzy queries. * 6882: cgroups limits no longer respected? * 6883: SQLancer crash on delete query * 6884: SQLancer generates query with unclear error message * 6885: SQLancer causes assertion error on UTF8_strlen * 6886: SQLancer alter table add unique gives strange error message * 6887: SQLancer crash on complex query * 6888: SQLancer crash on cross join on view * 6889: SQLancer crash on long query * 6892: SQLancer crash on query with HAVING * 6893: SQLancer inner join reporting GDK error * 6894: SQLancer crash on rtrim function * 6895: SQLancer causing 'algebra.select' undefined error * 6896: SQLancer algebra.select' undefined 2 * 6897: SQLancer distinct aggregate with error on group by constant * 6898: SQLancer crash on join query * 6899: SQLancer TLP query with wrong results * 6900: SQLancer generated SIGFPE * 6901: SQLancer TLP query with wrong results 2 * 6902: SQLancer query: batcalc.between undefined * 6903: SQLancer calc.abs undefined * 6904: SQLancer aggr.subavg undefined * 6905: SQLancer TLP query with wrong results 3 * 6906: SQLancer crash on complex join * 6907: SQLancer algebra.select undefined * 6908: SQLancer inputs not the same size * 6909: SQLancer query with wrong results * 6911: SQLancer query: 'calc.bit' undefined * 6917: Decimal parsing fails * 6918: SQLancer query compilation error * 6919: SQLancer insert function doesn't handle utf-8 strings * 6920: SQLancer project_bte: does not match always * 6922: Timestamp columns not migrated to new format * 6923: Imprints data files for timestamp BAT not migrated to the new format * 6924: SQLancer query copy on unique pair of columns fails and complex query with GDK error * 6925: Count string rows in union of string tables leaks (RSS) memory * 6926: SQLancer query with wrong results * 6927: SQLancer inputs not the same size * 6928: SQLancer crash on coalesce * 6929: SQLancer calc.date undefined * 6932: Syntax error while parsing JSON numbers with exponent * 6934: sys.isauuid() returns wrong answer for some invalid uuid strings

1 0

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