Step 4: Test Enterprise ColumnStore

Step 4: Test Enterprise ColumnStore

Overview

This page details step 4 of a 5-step procedure for deploying Single-Node Enterprise ColumnStore with Object storage.

This step tests MariaDB Enterprise Server and MariaDB Enterprise ColumnStore.

The instructions were tested against ColumnStore 23.10.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Test S3 Connection

MariaDB Enterprise ColumnStore 23.10 includes a testS3Connection command to test the S3 configuration, permissions, and connectivity.

On each Enterprise ColumnStore node, test the S3 configuration:

$ sudo testS3Connection

StorageManager[26887]: Using the config file found at /etc/columnstore/storagemanager.cnf
StorageManager[26887]: S3Storage: S3 connectivity & permissions are OK
S3 Storage Manager Configuration OK

If the testS3Connection command does not return OK, investigate the S3 configuration.

Test Local Connection

Connect to the server using MariaDB Client using the root@localhost user account:

$ sudo mariadb

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 38
Server version: 11.4.5-3-MariaDB-Enterprise MariaDB Enterprise Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Test ColumnStore Plugin Status

Query information_schema.PLUGINS and confirm that the ColumnStore storage engine plugin is ACTIVE:

SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM information_schema.PLUGINS
WHERE PLUGIN_LIBRARY LIKE 'ha_columnstore%';

+---------------------+---------------+
| PLUGIN_NAME         | PLUGIN_STATUS |
+---------------------+---------------+
| Columnstore         | ACTIVE        |
| COLUMNSTORE_COLUMNS | ACTIVE        |
| COLUMNSTORE_TABLES  | ACTIVE        |
| COLUMNSTORE_FILES   | ACTIVE        |
| COLUMNSTORE_EXTENTS | ACTIVE        |
+---------------------+---------------+

Test ColumnStore Table Creation

1. Create a test database, if it does not exist:

CREATE DATABASE IF NOT EXISTS test;

1. Create a ColumnStore table:

CREATE TABLE IF NOT EXISTS test.contacts (
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   email VARCHAR(100)
) ENGINE=ColumnStore;

1. Add sample data into the table:

INSERT INTO test.contacts (first_name, last_name, email)
   VALUES
   ("Kai", "Devi", "[e-mail]"),
   ("Lee", "Wang", "[e-mail]");

1. Read data from table:

SELECT * FROM test.contacts;

+------------+-----------+----------------------+
| first_name | last_name | email                |
+------------+-----------+----------------------+
| Kai        | Devi      | [e-mail] |
| Lee        | Wang      | [e-mail] |
+------------+-----------+----------------------+

Test Cross Engine Join

1. Create an InnoDB table:

CREATE TABLE test.addresses (
   email VARCHAR(100),
   street_address VARCHAR(255),
   city VARCHAR(100),
   state_code VARCHAR(2)
) ENGINE = InnoDB;

1. Add data to the table:

INSERT INTO test.addresses (email, street_address, city, state_code)
   VALUES
   ("[e-mail]", "1660 Amphibious Blvd.", "Redwood City", "CA"),
   ("[e-mail]", "32620 Little Blvd", "Redwood City", "CA");

1. Perform a cross-engine join:

SELECT name AS "Name", addr AS "Address"
FROM (SELECT CONCAT(first_name, " ", last_name) AS name,
   email FROM test.contacts) AS contacts
INNER JOIN (SELECT CONCAT(street_address, ", ", city, ", ", state_code) AS addr,
   email FROM test.addresses) AS addr
WHERE  contacts.email = addr.email;

+----------+-----------------------------------------+
| Name     | Address                                 |
+----------+-----------------------------------------+
| Kai Devi | 1660 Amphibious Blvd., Redwood City, CA |
| Lee Wang | 32620 Little Blvd, Redwood City, CA     |
+----------+-----------------------------------------+

+-------------------+-------------------------------------+
| Name              | Address                             |
+-------------------+-------------------------------------+
| Walker Percy      | 500 Thomas More Dr., Covington, LA  |
| Flannery O'Connor | 300 Tarwater Rd., Milledgeville, GA |
+-------------------+-------------------------------------+

Next Step

Navigation in the Single-Node Enterprise ColumnStore topology with Object storage deployment procedure:

This page was step 4 of 5.

Next: Step 5: Bulk Import of Data.

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