MariaDB Platform X5: New Tools and Features for Developers

Posted on July 30, 2020 by
spacer

With the release of MariaDB Platform X5, we’ve added new tooling and features that offload development complexity so developers can focus on creating innovation solutions. MariaDB Platform X5 includes MariaDB Enterprise Server 10.5, pluggable engines (such as InnoDB, ColumnStore, MyRocks, and Spider), connectors, and the MaxScale database proxy. And because there’s so much that’s included with the MariaDB Platform X5 release I figured it might be best to start with the high points before drilling down into more details in the future.

  • Added aggregate JSON functions
  • New Temporal Tables features
  • Enhanced SQL
  • Kafka and Redis support
  • MariaDB Python Connector v. 1.0.0

Even More JSON Functionality

Many developers use JSON because of its portability, flexibility, understandability and ubiquity. In fact, JSON is seen as the de facto data interchange format for REST and microservices. In MariaDB Server 10.5 we’ve added support for the aggregate functions JSON_ARRAYAGG() for creating a JSON array from the values of a column, and JSON_OBJECTAGG() for building a JSON object from the result set of a query. These are in addition to our already extensive set of functions for storing, indexing and querying JSON documents. You can find the full list in our documentation.

Fun Fact
We use JSON ourselves to enable MaxScale to parse streamed binlogs and push them out to a Kafka broker where they can be distributed to any service that uses the Kafka format.

Streaming binlogs via JSON to Kafka broker

JSON and SQL Hybrid Data Model
If you haven’t thought about using JSON with SQL before, you might be interested in a webinar I did on using JSON and SQL in a hybrid data model. In it, I demonstrate how to use JSON for semi-structured data with SQL for structured data to develop an app. I use concrete examples of using JSON with SQL to give you an idea of the possibilities.

Structured and semi-structured data with relational and JSON

Benefits of the Hybrid Approach
While there are many reasons to use JSON within MariaDB, consider the following benefits of using semi-structured data when planning your next project.

  • Provides the ultimate flexibility of working with traditional, structured SQL data, semi-structured JSON documents or a combination of the two.
  • Query JSON documents using standard SQL functions.
  • Use transactions to insert/modify multiple JSON documents.
  • Apply integrity constraints to specific fields within JSON documents.
  • Include performance enhancing Index specific fields within JSON documents.

If you’re interested in learning more, check out this blog for more information on how to use SQL and JSON together.

Enhanced SQL

Of course, SQL is at the core of many applications, and MariaDB Platform X5 includes enhancement to SQL to give developers more options and finer-grained control. Some of these include:

  • INSERT and REPLACE support the extension RETURNING to return a result set of the inserted rows or another specified SQL statement
  • EXCEPT and INTERSECT statements now support ALL so result sets can now include duplicate rows
  • COMMENT for CREATE and ALTER DATABASE
  • Aggregate functions JSON_ARRAYAGG and JSON_OBJECTAGG
  • RENAME INDEX and ALTER TABLE … RENAME COLUMN
  • CYCLE has been added for recursive Common Table Expressions (CTE) cycle detection. This is a non SQL standard implementation of CYCLE that allows the use of a column list without requiring additional clauses. (MDEV-20632)
  • Full support of REFERENCES constraint in column definition
  • ALTER … TABLE IF EXISTS / IF NOT EXISTS

Temporal Table Functionality

MariaDB supports system-versioned, application-time and bi-temporal tables. If you’re unfamiliar with what temporal tables are or what they’re used for, I highly recommend checking out a blog series we published last year. MariaDB Platform X5 includes a variety of new features.

  • Support for WITHOUT OVERLAP for application-time period tables
  • Configurable start date/time for interval partitioned history of system versioned tables
    • PARTITION BY SYSTEM_TIME INTERVAL 1 YEAR STARTS ‘2018-10-01 00:00:00’
  • Automatic creation of history partitions when partitions are defined
    • CREATE … WITH SYSTEM VERSIONING PARTITION BY SYSTEM_TIME PARTITIONS 2;

Apache Kafka and Redis

In line with adding more support for the tools developers use, MariaDB Platform X5 integrates with Apache Kafka and Redis, publishing data to Kafka topics and caching query results in a Redis cluster. Benefits can include boosting query performance, reducing database utilization to decrease hardware costs, and replicating data to external systems to unlock new features and insights.

Recently Released Python Connector

Not technically part of the MariaDB Platform X5 release, but of interest to developers and data scientists anyway, you may have missed our announcement of the new MariaDBConnector/Python. Now MariaDB Platform can be accessed with a native MariaDB Connector for Python.

Written in C for optimal performance, MariaDB Connector/Python adheres to a Python DB API 2.0 (PEP-249) compliant API and supports all MariaDB features. Version 1.0.0 of Connector/Python is available at MariaDB Downloads and on PyPi. To learn more about how you can download, install and use the new MariaDB Python connector please check out a recent webinar I presented. This is in addition to our many other connectors that make it easy to develop on MariaDB.

But Wait, There’s More

There’s a lot more included in MariaDB Platform X5. I’ve focused this blog on what’s new for the tools developers use. My colleagues have written about some of the other new features including:

For More Information

Download MariaDB Platform X5

Flexible Data Modeling with JSON Functions

Modern SQL with MariaDB webinar discussing JSON,  temporal tables, and SQL

My Git repo with developer examples