Skip to main content

Blogs

SQL:2003 merge statements now supported in MonetDB

As of the Apr2019 release of MonetDB, we have implemented the merge statement feature of the SQL:2003 standard. Merge statements allow one to conditionally insert, update and/or delete records in a target table using an input relation with a single statement.

The grammar is as follows:

merge_statement:

     [ WITH with_list ] MERGE INTO table_name [ [AS] ident ] USING input_rel [ [AS] ident ] ON search_condition merge_clause [ merge_clause ]

input_rel:

Extended SQL:2011 Window Functions in MonetDB

As of the Apr2019 release of MonetDB, we have extended MonetDB’s support for the SQL window functions to cover the majority as specified by the 2011 revision of the SQL standard. In this blogpost we describe the new functionality together with some example SQL queries to show their usage.

New analytic functions

Besides the existing RANK(), DENSE_RANK() and ROW_NUMBER() functions, we have now implemented all the remaining analytic functions listed in the SQL standard:

Updatable MERGE TABLEs with partition predicates

Scaling applications horizontally

To keep up with growing data sizes and workload, applications must be scaled either vertically or horizontally. For the first case, the machine can be equipped with more hardware resources, while for the second case, more instances of the same application can be used simultaneously to serve more users, and/or process more data. The latter scenario brings liability to coordinate all the instances of an application together.

Remote Tables and Authorization

Remote table authorization has changed in the upcoming August 2018 MonetDB release. Previously any physical execution plan that was accessing data in remote tables, was using the hardcoded strings "monetdb"/"monetdb" for username and password in order to connect to the server where the actual data was residing. Besides the obvious security implications, this behavior means that the remote table feature cannot be used if the DBA has changed the password for the user monetdb.

IoT and streaming in MonetDB

Internet-of-Things Database Server

With the expansion of IoT market, database systems with support for streaming data has acquired increasing attention. In an IoT scenario, data is collected in the periphery of a  sensor network, assembled and aggregated by message brokers, and delivered at the doorstep of the database system. There, they are fed into a dataflow based processing pipeline to derive actionable knowledge or to become the basis for interactive data exploration.

Subscribe to Blogs