CREATE TABLE with MariaDB Enterprise ColumnStore

Overview

MariaDB Enterprise ColumnStore supports the CREATE TABLE statement to create tables:

Compatibility

  • MariaDB Enterprise ColumnStore 5

  • MariaDB Enterprise ColumnStore 6

CREATE TABLE .. ENGINE=ColumnStore

To create a table with MariaDB Enterprise ColumnStore, use the CREATE TABLE statement with the ENGINE=ColumnStore table option:

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED NOT NULL,
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method INT
) ENGINE=ColumnStore;

CREATE TABLE inventory.products (
   product_name varchar(11) NOT NULL DEFAULT '',
   supplier varchar(128) NOT NULL DEFAULT '',
   quantity varchar(128) NOT NULL DEFAULT '',
   unit_cost varchar(128) NOT NULL DEFAULT ''
) ENGINE=ColumnStore;

default_storage_engine System Variable

The default_storage_engine system variable configures the default storage engine for new tables when the ENGINE table option is not provided in the CREATE TABLE statement:

SET SESSION default_storage_engine='ColumnStore';

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED NOT NULL,
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method INT
);

CREATE TABLE inventory.products (
   product_name varchar(11) NOT NULL DEFAULT '',
   supplier varchar(128) NOT NULL DEFAULT '',
   quantity varchar(128) NOT NULL DEFAULT '',
   unit_cost varchar(128) NOT NULL DEFAULT ''
);