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.
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.
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:
REPLACEsupport the extension
RETURNINGto return a result set of the inserted rows or another specified SQL statement
INTERSECTstatements now support
ALLso result sets can now include duplicate rows
- Aggregate functions
ALTER TABLE … RENAME COLUMN
CYCLEhas been added for recursive Common Table Expressions (CTE) cycle detection. This is a non SQL standard implementation of
CYCLEthat allows the use of a
column listwithout requiring additional clauses. (MDEV-20632)
- Full support of
REFERENCESconstraint 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:
- Significant rewrite of the InnoDB storage engine for performance and scalability
- Added new features and a GUI to MariaDB MaxScale database proxy for transparent load balancing, replication, orchestration, security
- MariaDB Enterprise Server 10.5 available on SkySQL
For More Information
Modern SQL with MariaDB webinar discussing JSON, temporal tables, and SQL