Functions

MariaDB ColumnStore supports the following aggregate functions, these can be used in the SELECT, HAVING, and ORDER BY clauses of the SQL statement.

Example

An example group by query using aggregate functions is:

SELECT year(o_orderdate) order_year, 
AVG(o_totalprice) avg_totalprice, 
MAX(o_totalprice) max_totalprice, 
COUNT(*) order_count 
FROM orders 
GROUP BY order_year 
ORDER BY order_year;

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

ColumnStore has the ability to support full MariaDB query syntax through an operating mode. This operating mode may be set as a default for the instance or set at the session level. To set the operating mode at the session level, the following command is used. Once the session has ended, any subsequent session will return to the default for the instance.

SET infinidb_vtable_mode = n

where n is:

1. a generic, highly compatible row-by-row processing mode. Some WHERE clause components can be processed by ColumnStore, but joins are processed entirely by mysqld using a nested loop join mechanism.

2. (the default) query syntax is evaluated by ColumnStore for compatibility with distributed execution and incompatible queries are rejected. Queries executed in this mode take advantage of distributed execution and typically result in higher performance.

3. auto-switch mode: ColumnStore will attempt to process the query internally, if it cannot, it will automatically switch the query to run in row-by-row mode.

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

Introduction

MariaDB provides extensibility support through user defined functions (UDFs). For more details on the MariaDB server framework see . This documentation applies to MariaDB ColumnStore version 1.0.10 and above.

MariaDB ColumnStore provides scale-out query processing. As such, it requires a separate distributed implementation of each SQL function. This allows for the function application to happen on each PrimProc server node, providing distributed scale-out performance.

Thus, to fully implement a user defined function for MariaDB ColumnStore requires implementing 2 different APIs:

• The MariaDB server UDF API allows utilization on all storage engines, and is the implementation used if applied in the select list.

• The ColumnStore distributed UDF API enables distributed execution of WHERE clause and GROUP BY usage of the function, and is pushed down to PrimProc nodes for execution where possible.

MariaDB ColumnStore supports user-defined function implementations in C/C++. User-defined aggregate and window functions are not supported in ColumnStore 1.0.

Developing a User-Defined Function

The development kit can be found under the utils/udfsdk directory of the mariadb-columnstore-engine source tree. Developing a UDF requires setting up a development environment and familiarity with c++ development. To set up a ColumnStore development environment, follow the instructions on dependencies in the ColumnStore server fork repository.

Three main files need to be modified in order to add a new UDF:

• udfmysql.cpp: MariaDB server UDF implementation.

• udfsdk.h: Class headers.

• udfsdk.cpp: Distributed ColumnStore UDF implementation.

Two reference implementations are provided for guidance on creating your own functions:

• MCS_IsNull: Illustrates a simple single-argument function, providing the ability to return a Boolean if the expression parameter is NULL.

• MCS_Add: Illustrates a simple two-argument function that adds two values and return the sum.

It is simplest to copy these and adapt to your needs. There are no system dependencies on the included reference implementations so these can be removed to simplify the class files if preferred.

MariaDB Server UDF Implementation

Three functions are required to be implemented (for more details see ):

• x_init: Performs any parameter validation or setup such as memory allocation.

• x: Performs the actual function implementation.

• x_deinit: Performs any cleanup tasks such as deallocating memory where 'x' is the function name.

ColumnStore Distributed UDF Implementation

The function name and class must be registered in order to be recognized and used by the ColumnStore primitives processor (PrimProc). This is done by adding a line to perform the registration in the UDFSDK::UDFMap()_` function in the file udfsdk.cpp:

FuncMap UDFSDK::UDFMap() const
{
	FuncMap fm;
	
	// first: function name
	// second: Function pointer
	// please use lower case for the function name. Because the names might be 
	// case-insensitive in MariaDB depending on the setting. In such case,
	// the function names passed to the interface is always in lower case.
	fm["mcs_add"] = new MCS_add();
	fm["mcs_isnull"] = new MCS_isnull();

	return fm;
}

For a new UDF, add a new entry to the FuncMap object, mapping the name to the udf class.

The UDF class should be defined in file udfsdk.h and implemented in file udfsdk.cpp. It is easiest to adapt the example classes for new instance but each class must implement the funcexp::Func_` C++ class definition:

• constructor: any initialization necessary.

• destructor: any de-initialization.

• getOperationType performs parameter validation and returns the result data type.

• getVal computes and returns the value of the UDF for each given return datatype.

The code changes can be built using make within the directory utils/udfsdk, this creates the following libraries in the same directory:

• libudf_mysql.so.1.0.0

• libudfsdk.so.1.0.0

Those libraries contain the compiled code.

Deploying and Using a UDF

The two libraries created before must be deployed to the /usr/local/mariadb/columnstore/lib directory (or equivalent lib directory in a non-root installation), replacing the existing files. Symbolic links in the MariaDB server directory point to these, but should be validated. Run the following as root from the utils/udfsdk directory in the build tree (specifying a password to restartSystem if needed for a multi-server cluster):

$ cp libudf_mysql.so.1.0.0 libudfsdk.so.1.0.0 /usr/local/mariadb/columnstore/lib/
$ ls -l /usr/local/mariadb/columnstore/mysql/lib/plugin/libudf_mysql.so
lrwxrwxrwx. 1 root root 56 Jul 19 09:47 /usr/local/mariadb/columnstore/mysql/lib/plugin/libudf_mysql.so -> /usr/local/mariadb/columnstore/lib/libudf_mysql.so.1.0.0

Repeat this for each ColumnStore PrimProc node in the cluster, then restart ColumnStore to make the libraries available.

After restarting the system, the UDF must be registered with the MariaDB server so it can be used:

$ mcsmysql
    > create function mcs_add returns integer soname 'libudf_mysql.so';

The function mcs_add can now be used. Verify that it can be used both in the SELECT list and WHERE clause for correct installation:

MariaDB [test]> create function mcs_add returns integer soname 'libudf_mysql.so';
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> create table t1(i1 int, i2 int) engine=columnstore;
Query OK, 0 rows affected (0.58 sec)

MariaDB [test]> insert into t1 values (1,1), (2,2);
Query OK, 2 rows affected (0.24 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> select i1, i2, mcs_add(i1,i2) sum from t1;
+------+------+------+
| i1   | i2   | sum  |
+------+------+------+
|    1 |    1 |    2 |
|    2 |    2 |    4 |
+------+------+------+
2 rows in set (0.05 sec)

MariaDB [test]> select i1, i2 from t1 where mcs_add(i1,i2) = 4;
+------+------+
| i1   | i2   |
+------+------+
|    2 |    2 |
+------+------+
1 row in set (0.02 sec)

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

Introduction

MariaDB ColumnStore provides support for window functions broadly following the SQL 2003 specification. A window function allows for calculations relating to a window of data surrounding the current row in a result set. This capability provides for simplified queries in support of common business questions such as cumulative totals, rolling averages, and top 10 lists.

Aggregate functions are utilized for window functions however differ in behavior from a group by query because the rows remain ungrouped. This provides support for cumulative sums and rolling averages, for example.

Two key concepts for window functions are Partition and Frame:

• A Partition is a group of rows, or window, that have the same value for a specific column, for example a Partition can be created over a time period such as a quarter or lookup values.

• The Frame for each row is a subset of the row's Partition. The frame typically is dynamic allowing for a sliding frame of rows within the Partition. The Frame determines the range of rows for the windowing function. A Frame could be defined as the last X rows and next Y rows all the way up to the entire Partition.

Window functions are applied after joins, group by, and having clauses are calculated.

Syntax

A window function is applied in the select clause using the following syntax:

function_name ([expression [, expression ... ]]) OVER ( window_definition )

where window_definition is defined as:

[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]

PARTITION BY

• Divides the window result set into groups based on one or more expressions.

• An expression may be a constant, column, and non window function expressions.

• A query is not limited to a single partition by clause. Different partition clauses can be used across different window function applications.

• The partition by columns do not need to be in the select list but do need to be available from the query result set.

• If there is no PARTITION BY clause, all rows of the result set define the group.

ORDER BY

• Defines the ordering of values within the partition.

• Can be ordered by multiple keys which may be a constant, column or non window function expression.

• The order by columns do not need to be in the select list but need to be available from the query result set.

• Use of a select column alias from the query is not supported.

• ASC (default) and DESC options allow for ordering ascending or descending.

• NULLS FIRST and NULL_LAST options specify whether null values come first or last in the ordering sequence. NULLS_FIRST is the default for ASC order, and NULLS_LAST is the default for DESC order.

and the optional frame_clause is defined as:

{ RANGE | ROWS } frame_start
{ RANGE | ROWS } BETWEEN frame_start AND frame_end

and the optional frame_start and frame_end are defined as (value being a numeric expression):

UNBOUNDED PRECEDING
value PRECEDING
CURRENT ROW
value FOLLOWING
UNBOUNDED FOLLOWING

RANGE/ROWS

• Defines the windowing clause for calculating the set of rows that the function applies to for calculating a given rows window function result.

• Requires an ORDER BY clause to define the row order for the window.

• ROWS specify the window in physical units, i.e. result set rows and must be a constant or expression evaluating to a positive numeric value.

• RANGE specifies the window as a logical offset. If the expression evaluates to a numeric value, then the ORDER BY expression must be a numeric or DATE type. If the expression evaluates to an interval value, then the ORDER BY expression must be a DATE data type.

• UNBOUNDED PRECEDING indicates the window starts at the first row of the partition.

• UNBOUNDED FOLLOWING indicates the window ends at the last row of the partition.

• CURRENT ROW specifies the window start or ends at the current row or value.

• If omitted, the default is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Supported Window Functions

Examples

Example Schema

The examples are all based on the following simplified sales opportunity table:

CREATE TABLE opportunities (
id INT,
accountName VARCHAR(20),
name VARCHAR(128),
owner VARCHAR(7),
amount DECIMAL(10,2),
closeDate DATE,
stageName VARCHAR(11)
) ENGINE=columnstore;

Some example values are (thanks to www.mockaroo.com for sample data generation):

The schema, sample data, and queries are available as an attachment to this article.

Cumulative Sum and Running Max Example

Window functions can be used to achieve cumulative / running calculations on a detail report. In this case a won opportunity report for a 7 day period adds columns to show the accumulated won amount as well as the current highest opportunity amount in preceding rows.

SELECT owner, 
accountName, 
CloseDate, 
amount, 
SUM(amount) OVER (ORDER BY CloseDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cumeWon, 
MAX(amount) OVER (ORDER BY CloseDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) runningMax
FROM opportunities 
WHERE stageName='ClosedWon' 
AND closeDate >= '2016-10-02' AND closeDate <= '2016-10-09' 
ORDER BY CloseDate;

with example results:

Partitioned Cumulative Sum and Running Max Example

The above example can be partitioned, so that the window functions are over a particular field grouping such as owner and accumulate within that grouping. This is achieved by adding the syntax "partition by " in the window function clause.

SELECT owner,  
accountName,  
CloseDate,  
amount,  
SUM(amount) OVER (PARTITION BY owner ORDER BY CloseDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cumeWon,  
MAX(amount) OVER (PARTITION BY owner ORDER BY CloseDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) runningMax 
FROM opportunities  
WHERE stageName='ClosedWon' 
AND closeDate >= '2016-10-02' AND closeDate <= '2016-10-09'  
ORDER BY owner, CloseDate;

with example results:

Ranking / Top Results

The rank window function allows for ranking or assigning a numeric order value based on the window function definition. Using the Rank() function will result in the same value for ties / equal values and the next rank value skipped. The Dense_Rank() function behaves similarly except the next consecutive number is used after a tie rather than skipped. The Row_Number() function will provide a unique ordering value. The example query shows the Rank() function being applied to rank sales reps by the number of opportunities for Q4 2016.

SELECT owner, 
wonCount, 
rank() OVER (ORDER BY wonCount DESC) rank 
FROM (
  SELECT owner, 
  COUNT(*) wonCount 
  FROM opportunities 
  WHERE stageName='ClosedWon' 
  AND closeDate >= '2016-10-01' AND closeDate < '2016-12-31'  
  GROUP BY owner
) t
ORDER BY rank;

with example results (note the query is technically incorrect by using closeDate < '2016-12-31' however this creates a tie scenario for illustrative purposes):

If the dense_rank function is used the rank values would be 1,2,3,3,4 and for the row_number function the values would be 1,2,3,4,5.

First and Last Values

The first_value and last_value functions allow determining the first and last values of a given range. Combined with a group by this allows summarizing opening and closing values. The example shows a more complex case where detailed information is presented for first and last opportunity by quarter.

SELECT a.YEAR, 
a.quarter, 
f.accountName firstAccountName, 
f.owner firstOwner, 
f.amount firstAmount, 
l.accountName lastAccountName, 
l.owner lastOwner, 
l.amount lastAmount 
FROM (
  SELECT YEAR, 
  QUARTER, 
  MIN(firstId) firstId, 
  MIN(lastId) lastId 
  FROM (
    SELECT YEAR(closeDate) YEAR, 
    quarter(closeDate) QUARTER, 
    first_value(id) OVER (PARTITION BY YEAR(closeDate), quarter(closeDate) ORDER BY closeDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) firstId, 
    last_value(id) OVER (PARTITION BY YEAR(closeDate), quarter(closeDate) ORDER BY closeDate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) lastId 
    FROM opportunities  WHERE stageName='ClosedWon'
  ) t 
  GROUP BY YEAR, QUARTER ORDER BY YEAR,QUARTER
) a 
JOIN opportunities f ON a.firstId = f.id 
JOIN opportunities l ON a.lastId = l.id 
ORDER BY YEAR, QUARTER;

with example results:

Prior and Next Example

Sometimes it useful to understand the previous and next values in the context of a given row. The lag and lead window functions provide this capability. By default, the offset is one providing the prior or next value but can also be provided to get a larger offset. The example query is a report of opportunities by account name showing the opportunity amount, and the prior and next opportunity amount for that account by close date.

SELECT accountName, 
closeDate,  
amount currentOppAmount, 
lag(amount) OVER (PARTITION BY accountName ORDER BY closeDate) priorAmount, lead(amount) OVER (PARTITION BY accountName ORDER BY closeDate) nextAmount 
FROM opportunities 
ORDER BY accountName, closeDate 
LIMIT 9;

with example results:

Quartiles Example

The NTile window function allows for breaking up a data set into portions assigned a numeric value to each portion of the range. NTile(4) breaks the data up into quartiles (4 sets). The example query produces a report of all opportunities summarizing the quartile boundaries of amount values.

SELECT t.quartile, 
MIN(t.amount) MIN, 
MAX(t.amount) MAX 
FROM (
  SELECT amount, 
  ntile(4) OVER (ORDER BY amount ASC) quartile 
  FROM opportunities 
  WHERE closeDate >= '2016-10-01' AND closeDate <= '2016-12-31'
  ) t 
GROUP BY quartile 
ORDER BY quartile;

With example results:

Percentile Example

The percentile functions have a slightly different syntax from other window functions as can be seen in the example below. These functions can be only applied against numeric values. The argument to the function is the percentile to evaluate. Following 'within group' is the sort expression which indicates the sort column and optionally order. Finally after 'over' is an optional partition by clause, for no partition clause use 'over ()'. The example below utilizes the value 0.5 to calculate the median opportunity amount in the rows. The values differ sometimes because percentile_cont will return the average of the 2 middle rows for an even data set while percentile_desc returns the first encountered in the sort.

SELECT owner,  
accountName,  
CloseDate,  
amount,
percentile_cont(0.5) within GROUP (ORDER BY amount) OVER (PARTITION BY owner) pct_cont,
percentile_disc(0.5) within GROUP (ORDER BY amount) OVER (PARTITION BY owner) pct_disc
FROM opportunities  
WHERE stageName='ClosedWon' 
AND closeDate >= '2016-10-02' AND closeDate <= '2016-10-09'  
ORDER BY owner, CloseDate;

With example results:

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

Overview

Joins are performed in memory unless disk-based joins are enabled via AllowDiskBasedJoin in the columnstore.xml. When a join operation exceeds the memory allocated for query joins, the query is aborted with an error code IDB-2001.

Disk-based joins enable such queries to use disk for intermediate join data in case when the memory needed for the join exceeds the memory limit. Although slower in performance as compared to a fully in-memory join and bound by the temporary space on disk, it does allow such queries to complete.

The following variables in the HashJoin element in the Columnstore.xml configuration file relate the o disk-based joins. Columnstore.xml resides in the etc. directory for your installation (/usr/local/mariadb/columnstore/etc).

• AllowDiskBasedJoin: Option to use disk-based joins. Valid values are Y (enabled) or N (disabled). The default is disabled.

• TempFileCompression: Option to use compression for disk join files. Valid values are Y (use compressed files) or N (use non-compressed files).

• TempFilePath: The directory path used for the disk joins. By default, this path is the tmp directory for your installation (i.e., /tmp/columnstore_tmp_files/joins/). Files in this directory will be created and cleaned on an as-needed basis. The entire directory is removed and recreated by ExeMgr at startup.)

Per-User Join Memory Limit

In addition to the system-wide flags at the SQL global and session levels, the following system variables exist for managing per-user memory limits for joins.

• columnstore_um_mem_limit - A value for memory limit in MB per user. When this limit is exceeded by a join, it will switch to a disk-based join. By default, the limit is not set (value of 0).

For modification at the global level: In my.cnf file (example: /etc/my.cnf.d/server.cnf):

[mysqld]
...
loose-columnstore_um_mem_limit = value

where value is the value in MB for in memory limitation per user.

For modification at the session level, before issuing your join query from the SQL client, set the session variable as follows.

SET columnstore_um_mem_limit = value

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

The DROP PROCEDURE statement deletes a stored procedure from ColumnStore.

The following statement drops the sp_complex_variable procedure:

DROP PROCEDURE sp_complex_variable;

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

In most cases, a ColumnStore table works just as any other MariaDB table. There are, however, a few differences.

The following table lists the data definition statements (DDL) that differ from normal MariaDB when used on ColumnStore tables.

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

The UPDATE statement changes data stored in rows.

Syntax

Single-Table Syntax

Multiple-Table Syntax

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

Introduction

Starting with MariaDB ColumnStore 1.1, the ability to create and use user-defined aggregate and window functions is supported in addition to scalar functions. With Columnstore 1.2, multiple parameters are supported. A C++ SDK is provided, as well as 3 reference examples that provide additional functions that may be of general use:

• median—mathematical median, equivalent to percentile_cont(0.5).

MariaDB ColumnStore utility functions are a set of simple functions that return useful information about the system, such as whether it is ready for queries.

The DELETE statement is used to remove rows from tables.

Syntax

No disk space is recovered after a DELETE. TRUNCATE and DROP PARTITION can be used to recover space, or CREATE TABLE

ColumnStore supports the following data types:

Numeric Data Types

String Data Types

Date and Time Data Types

Notes

• ColumnStore treats a zero-length string as a NULL value.

• As with core MariaDB, ColumnStore employs “saturation semantics” on integer values. This means that if a value is inserted into an integer field that is too big/small for it to hold (i.e., it is more negative or more positive than the values indicated above), ColumnStore will “saturate” that value to the min/max value indicated above as appropriate. For example, for a SMALLINT column, if 32800 is attempted, the actual value inserted will be 32767.

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

CMAPI is a REST API for administering MariaDB Enterprise ColumnStore in multi-node topologies.

Reference material is available for MariaDB Enterprise ColumnStore.

MariaDB Enterprise ColumnStore is included with MariaDB Enterprise Server.

Clients

CMAPI is a REST API, so it should be compatible with most standard REST clients.

CMAPI examples throughout the documentation use curl as the REST client. The examples also pipe the JSON output to jq for enhanced readability.

Endpoint

The endpoint for CMAPI contains the hostname and port for the primary node running Enterprise ColumnStore, /cmapi/, the CMAPI API version (0.4.0), and a action-specific endpoint path.

Example: https://mcs1:8640/cmapi/0.4.0/cluster/node

Endpoint Paths

Method and required data vary by CMAPI endpoint path.

Required Headers

Authentication

Authentication is performed via an API key, which performs the role of a shared secret. The API key is passed to the API using the x-api-key header.

The API key is stored in /etc/columnstore/cmapi_server.conf.

Generate an API Key

The API key is a shared secret that can be used to add nodes to multi-node Enterprise ColumnStore. The API key can be any string, but it is recommended to use a long, random string. The API key should be stored securely and kept confidential.

For example, to create a random 256-bit API key using openssl rand:

Set the API Key

To set the API key for the first time, provide the desired API key when you add the first node using the PUT command. Since Enterprise ColumnStore does not yet have an API key, CMAPI will write the first API key it receives to /etc/columnstore/cmapi_server.conf.

For example, if the primary server's host name is mcs1 and its IP address is 192.0.2.1, the following command will add the primary server to Enterprise ColumnStore and write the provided API key to the node's CMAPI configuration file:

Change the API Key

To change the API key in multi-node Enterprise ColumnStore, change it in the CMAPI configuration file on each node located at /etc/columnstore/cmapi_server.conf. The CMAPI server must be restarted on each node for the changes to be applied.

Restart the CMAPI server by running the following command on each node:

Bash Aliases

These aliases are available if your bash shell is configured to source the columnstoreAlias shell script.

These aliases execute curl and jq, so both programs must be installed on the system.

These aliases automatically retrieve the IP address for the primary node using the command. The aliases automatically retrieve the API key by reading /etc/columnstore/cmapi_server.conf.

Available aliases:

CMAPI Service Management

The systemctl command is used to start and stop the CMAPI service.

Configuration

The CMAPI configuration file is located at /etc/columnstore/cmapi_server.conf.

To change the configuration:

• Modify the configuration file on each node

• Restart the CMAPI server on each node:

Configure Failover

Starting with CMAPI 6.4.1, the auto_failover option can be set to True or False in the [application] section:

• The default value of the auto_failover option is True.

• The auto_failover option should be set to False when is used.

Logging

Starting with Enterprise ColumnStore 5.5.2, the can be found at /var/log/mariadb/columnstore/cmapi_server.log.

In previous versions, CMAPI's log messages can be viewed in the systemd journal:

CMAPI Responses

CMAPI responds to client requests with standard HTTP response messages.

Status Line

The first part of the standard HTTP response message is the status line. To determine if your request was successful, check the status code and the reason phrase from the status line.

Headers

The second part of the standard HTTP response message is the HTTP headers. To determine what kind of message body is in the response message, check the Content-Type header field.

Body

The final part of the standard HTTP response message is the body.

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

The COMMIT statement makes changes to a table permanent. You should only commit changes after you have verified the integrity of the changed data. Once data is committed, it cannot be undone with the ROLLBACK statement. To return the database to its former state, you must restore the data from backups.

images here

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

Overview

ColumnStore supports all MariaDB functions that can be used in a post-processing manner where data is returned by ColumnStore first and then MariaDB executes the function on the data returned. The functions are currently supported only in the projection (SELECT) and ORDER BY portions of the SQL statement.

See Also

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

The RENAME TABLE statement renames one or more ColumnStore tables.

Notes:

• You cannot currently use RENAME TABLE to move a table from one database to another.

• See the ALTER TABLE syntax for an alternative to RENAME Table. The following statement renames the orders table:

The following statement renames both the orders table and customer table:

You may also use RENAME TABLE to swap tables. This example swaps the customer and vendor tables (assuming the temp_table does not already exist):

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

The ROLLBACK statement undoes transactions that have not been permanently saved to the database with the COMMIT statement. You cannot rollback changes to table properties including ALTER, CREATE, or DROP TABLE statements.

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

MariaDB ColumnStore has the ability to compress data and this is controlled through a compression mode. This compression mode may be set as a default for the instance or set at the session level.

To set the compression mode at the session level, the following command is used. Once the session has ended, any subsequent session will return to the default for the instance.

set infinidb_compression_type = n

where n is:

1. 0 = Compression is turned off. Any subsequent table create statements run will have compression turned off for that table unless any statement overrides have been performed. Any ALTER statements run to add a column will have compression turned off for that column unless any statement override has been performed.

2. 1 = Compression is turned on. Any subsequent table create statements run will have compression turned on for that table unless any statement overrides have been performed. Any ALTER statements run to add a column will have compression turned on for that column unless any statement override has been performed. ColumnStore uses snappy compression in this mode.

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

Checks the status of ColumnStore.

See CMAPI for detail on REST API endpoint, required headers, and other available actions.

Description

Upon successful status call CMAPI returns JSON payload containing detailed information on MariaDB Enterprise Cluster status.

Call made via HTTPS GET, with authentication via shared secret using the x-api-key header.

Bash alias mcsStatus is available starting with Enterprise ColumnStore 5.5.2.

Examples

Executing cURL Manually

CMAPI calls can be made from the command-line using cURL.

Replace the CMAPI_API_KEY and sample data in the following example:

In this example, jq produces human-readable output from the returned JSON response:

Executing the Bash Alias

Starting with Enterprise ColumnStore 5.5.2, if your bash shell is configured to source the columnstoreAlias shell script, this command can be executed using the mcsStatus alias. The alias executes curl and jq, so both programs must be installed on the system.

The alias automatically retrieves the IP address for the primary node using the mcsGetConfig command. The alias automatically retrieves the API key by reading /etc/columnstore/cmapi_server.conf.

In this example, jq produces human-readable output from the returned JSON response.

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

1. Enable/Disable decimal to double math "Enable/Disable decimal to double math"

2. ColumnStore decimal scale "ColumnStore decimal scale"

3. Enable/disable decimal scale "Enable/disable decimal scale"

MariaDB ColumnStore has the ability to change intermediate decimal mathematical results from decimal type to double. The decimal type has approximately 17-18 digits of precision but a smaller maximum range. Whereas the double type has approximately 15-16 digits of precision, but a much larger maximum range.

In typical mathematical and scientific applications, the ability to avoid overflow in intermediate results with double math is likely more beneficial than the additional two digits of precision. In banking applications, however, it may be more appropriate to leave in the default decimal setting to ensure accuracy to the least significant digit.

Enable/Disable decimal to double math

The columnstore_double_for_decimal_math variable is used to control the data type for intermediate decimal results. This decimal for double math may be set as a default for the instance, set at the session level, or at the statement level by toggling this variable on and off.

To enable/disable the use of the decimal to double math at the session level, the following command is used. Once the session has ended, any subsequent session will return to the default for the instance.

where n is:

• off (disabled, default)

• on (enabled)

ColumnStore Decimal Scale

ColumnStore has the ability to support varied internal precision on decimal calculations. columnstore_decimal_scale is used internally by the ColumnStore engine to control how many significant digits to the right of the decimal point are carried through in suboperations on calculated columns. If, while running a query, you receive the message ‘aggregate overflow,’ try reducing columnstore_decimal_scale and running the query again. Note that, as you decrease columnstore_decimal_scale, you may see reduced accuracy in the least significant digit(s) of a returned calculated column.

columnstore_decimal_scale is used internally by the ColumnStore engine to turn the use of this internal precision on and off. These two system variables may be set as a default for the instance or set at the session level.

Enable/Disable Decimal Scale

To enable/disable the use of the decimal scale at the session level, the following command is used. Once the session has ended, any subsequent session will return to the default for the instance.

where n is off (disabled) or on (enabled).

Set Decimal Scale Level

To set the decimal scale at the session level, the following command is used. Once the session has ended, any subsequent session will return to the default for the instance.

where n is the amount of precision desired for calculations.

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

A condition is a combination of expressions and operators that return TRUE, FALSE or NULL. The following syntax shows the conditions that can be used to return a TRUE, FALSE, or NULL condition.

Filter

String Comparisons

ColumnStore, unlike the MyISAM engine, is case sensitive for string comparisons used in filters. For the most accurate results, and to avoid confusing results, make sure string filter constants are no longer than the column width itself.

Pattern Matching

Pattern matching as described with the LIKE condition allows you to use “” to match any single character and “%” to match an arbitrary number of characters (including zero characters). To test for literal instances of a wildcard character, (“%” or “”), precede it by the “\” character.

OR Processing

OR Processing has the following restrictions:

• Only column comparisons against a literal are allowed in conjunction with an OR. The following query would be allowed since all comparisons are against literals. SELECT count(*) from lineitem WHERE l_partkey < 100 OR l_linestatus =‘F‘;

• ColumnStore binds AND’s more tightly than OR’s, just like any other SQLparser. Therefore you must enclose OR-relations in parentheses, just like in any other SQL parser.

table filter

The following syntax shows the conditions you can use when executing a condition against two columns. Note that the columns must be from the same table.

Join

The following syntax shows the conditions you can use when executing a join on two tables.

Notes:

• ColumnStore tables can only be joined with non-ColumnStore tables in table mode only. See Operating Mode for information.

• ColumnStore will require a join in the WHERE clause for each set of tables in the FROM clause. No cartesian product queries will be allowed.

• ColumnStore requires that joins must be on the same datatype. In addition, numeric datatypes (INT

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

Overview

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. The file name must be given as a literal string.

LOAD DATA [LOCAL] INFILE 'file_name' 
  INTO TABLE tbl_name
  [CHARACTER SET charset_name]
  [{FIELDS | COLUMNS}
    [TERMINATED BY 'string']
    [[OPTIONALLY] ENCLOSED BY 'char']
    [ESCAPED BY 'char']
  ]
  [LINES
    [STARTING BY 'string']
    [TERMINATED BY 'string']
]

• ColumnStore ignores the ON DUPLICATE KEY clause.

• Non-transactional LOAD DATA INFILE is directed to ColumnStores cpimport tool by default, which significantly increases performance.

• Transactional LOAD DATA INFILE statements (that is, with AUTOCOMMIT off or after a START TRANSACTION) are processed through normal DML processes.

• Use cpimport for importing UTF-8 data that contains multi-byte values

The following example loads data into a simple 5- column table: A file named /simpletable.tblhas the following data in it.

The data can then be loaded into the simpletable table with the following syntax:

If the default mode is set to use cpimport internally, any output error files will be written to /var/log/mariadb/columnstore/cpimport/ directory. It can be consulted for troubleshooting any errors reported.

See Also

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

Functions

MariaDB ColumnStore information functions are selectable pseudo functions that return MariaDB ColumnStore specific “meta” information, to ensure queries can be locally directed to a specific node. These functions can be specified in the projection (SELECT), WHERE, GROUP BY, HAVING and ORDER BY portions of the SQL statement and are processed in a distributed manner.

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

A database consists of tables that store user data. You can create multiple columns with the CREATE TABLE statement. The data type follows the column name when adding columns.

Syntax

CREATE TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)  
ENGINE=columnstore  [ DEFAULT CHARSET=character-set] 
[COMMENT '[compression=0|1][;]
CREATE TABLE [IF NOT EXISTS] tbl_name
   { LIKE old_table_name | (LIKE old_table_name) }
create_definition:
    { col_name column_definition } 
column_definition:
    data_type
      [NOT NULL | NULL]
      [DEFAULT default_value]
      [COMMENT '[compression=0|1]
      [COMMENT='schema sync only']
      [COMMENT 'autoincrement column_name'];

images here

Notes

• ColumnStore tables should not be created in the mysql, information_schema, calpontsys, or test databases.

• ColumnStore stores all object names in lowercase.

• CREATE TABLE AS SELECT is not supported and will instead create the table in the default storage engine.

All of these are ignored by ColumnStore. The following statement creates a table called "orders" with two columns: orderkey with datatype INTEGER, and customer with datatype VARCHAR:

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

Creates a stored routine in ColumnStore.

Syntax

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

type:
    Any valid MariaDB ColumnStore data type

routine_body:
    Valid SQL procedure statement

ColumnStore currently accepts definition of stored procedures with only input arguments and a single SELECT query while in Operating Mode = 1 (VTABLE mode). However, while in the Operating Mode = 0 (TABLE mode), ColumnStore will allow additional complex definition of stored procedures (i.e., OUT parameter, declare, cursors, etc.)

See Operating Mode for information on Operating Modes.

The following statements create and call the sp_complex_variable stored procedure:

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

Alters the definition of a view. CREATE OR REPLACE VIEW may also be used to alter the definition of a view.

Syntax

CREATE
    [OR REPLACE]
    VIEW view_name [(column_list)]
    AS select_statement

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

MariaDB ColumnStore has four information schema tables that expose information about the table and column storage. The tables were added in version 1.0.5 and were heavily modified for 1.0.6.

COLUMNSTORE_TABLES

The first table is the INFORMATION_SCHEMA.COLUMNSTORE_TABLES. It contains information about the tables inside ColumnStore. The table layout is as follows:

Tables created with ColumnStore 1.0.4 or lower will have the year field of the creation data set incorrectly by 1900 years.

COLUMNSTORE_COLUMNS

The INFORMATION_SCHEMA.COLUMNSTORE_COLUMNS table contains information about every single column inside ColumnStore. The table layout is as follows:

COLUMNSTORE_EXTENTS

This table displays the extent map in a user-consumable form. An extent is a collection of details about a section of data related to a columnstore column. A majority of columns in ColumnStore will have multiple extents, and the columns table above can be joined to this one to filter results by table or column. The table layout is as follows:

Notes:

1. The state is "Valid" for a normal state, "Invalid" if a cpimport has completed but the table has not yet been accessed (min/max values will be invalid), or "Updating" if there is a DML statement writing to the column.

2. In ColumnStore, the block size is 8192 bytes.

3. By default, ColumnStore will write and create an extent file of 256_1024_WIDTH bytes for the first partition; if this is too small, then for uncompressed data, it will create a file of the maximum size for the extent (MAX_BLOCKS * BLOCK_SIZE). Snappy always compresses and adds a header block.

COLUMNSTORE_FILES

The columnstore_files table provides information about each file associated with extensions. Each extension can reuse a file at different block offsets, so this is not a 1:1 relationship to the columnstore_extents table.

Stored Procedures

The total_usage() procedure gives a total disk usage summary for all the columns in ColumnStore except the columns used for internal maintenance. It is executed using the following query:

table_usage Procedure

The table_usage() procedure gives the total data disk usage, dictionary disk usage, and overall disk usage per table. It can be called in several ways; the first gives a total for each table:

Or for a specific table, my_table in my_schema in this example:

You can also request all tables for a specified schema:

compression_ratio Procedure

The compression_ratio() procedure calculates the average compression ratio across all the compressed extents in ColumnStore. It is called using

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

Removes a ColumnStore node.

See CMAPI for detail on REST API endpoint, required headers, and other available actions.

Description

Upon successful node DELETE call CMAPI configures MariaDB Enterprise ColumnStore to remove the specified node.

Call made via HTTPS DELETE, with authentication via shared secret using the x-api-key header.

JSON data required for this call:

Examples

Executing cURL Manually

CMAPI calls can be made from the command-line using cURL.

Replace the CMAPI_API_KEY and sample data in the following example:

In this example, jq produces human-readable output from the returned JSON response:

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

Functions

ColumnStore supports the following functions. These functions can be specified in the projection (SELECT), WHERE and ORDER BY portions of the SQL statement and will be processed in a distributed manner.

• ,

• ,

• ,

See Also

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}