MariaDB Enterprise ColumnStore Creating Tables

The most essential step in using Enterprise ColumnStore is to create your table using the CREATE TABLE statement.

Overview

To create a table that gets the benefits of Enterprise ColumnStore, the table must use the ColumnStore storage engine:

Create ColumnStore Tables

To create ColumnStore tables, 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;

Create ColumnStore Tables using Session Default

To create ColumnStore tables by default in the current session, set the session value of the default_storage_engine system variable to ColumnStoreand use the CREATE TABLE statement without the ENGINE table option:

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 ''
);

Configure ColumnStore as Global Default

MariaDB Enterprise Server can be configured to use ColumnStore as the global default engine for all new tables. The default engine can be configured dynamically. To ensure that the change survives server restarts, the change should also be made in a configuration file.

Perform the following procedure on each node to configure ColumnStore as the global default engine:

  1. Choose a configuration file for custom changes to system variables and options.

    It is not recommended to make custom changes to Enterprise Server's default configuration files, because your custom changes can be overwritten by other default configuration files that are loaded after.

    Ensure that your custom changes will be read last by creating a custom configuration file in one of the included directories. Configuration files in included directories are read in alphabetical order. Ensure that your custom configuration file is read last by using the z- prefix in the file name.

    Some example configuration file paths for different distributions are shown in the following table:

    Distributions

    Example configuration file path

    CentOS
    RHEL
    SLES

    /etc/my.cnf.d/z-custom-mariadb.cnf

    Debian
    Ubuntu

    /etc/mysql/mariadb.conf.d/z-custom-mariadb.cnf

  1. Set the default_storage_engine system variable in the configuration file.

    It must be set in a group that will be read by MariaDB Server, such as [mariadb] or [server].

    For example:

    [mariadb]
    ...
    default_storage_engine=ColumnStore
    
  2. Connect to the node using MariaDB Client as the root@localhost user account or another user account with the SUPER privilege:

    $ sudo mariadb
    
  3. Set the default_storage_engine system variable to ColumnStore using the SET GLOBAL statement:

    SET GLOBAL default_storage_engine='ColumnStore';
    

Table Options Supported for ColumnStore

ColumnStore tables support many different table options, which can determine various characteristics of the table.

ColumnStore storage engine supports the following table options:

Table Option

Description

COMMENT

  • Specifies a user-readable comment for the table.

DEFAULT CHARACTER SET

  • Specifies the default character set for columns which do not have a different character set explicitly specified. Note that ColumnStore does not support all character sets available to the Server.

DEFAULT COLLATE

  • Specifies the default collation for columns which do not have a different collation explicitly specified. Note that ColumnStore does not support all collations available to the Server.

Data Type Compatibility

In MariaDB Enterprise Server, data type compatibility is determined by the storage engine.

Data type compatibility details for MariaDB Enterprise ColumnStore are listed below.

Integer Data Types

Integer data types are:

Data Type

Size

Signed Range

Unsigned Range

BOOLEAN

1 byte

0 - 1

0 - 1

TINYINT

1 byte

-126 - 127

0 - 253

SMALLINT

2 bytes

-32766 - 32767

0 - 65533

MEDIUMINT

3 bytes

-8388608 - 8388607

0 - 16777215

INT

4 bytes

-2147483646 - 2147483647

0 - 4294967293

BIGINT

8 bytes

-9223372036854775806 - 9223372036854775807

0 - 18446744073709551613

  • Enterprise ColumnStore reserves 2 integer values for internal use, so the maximum integer values are 2 less than the maximum integer values for Enterprise Server.

  • Enterprise ColumnStore uses "saturation semantics" for integer values. If a value is too big for an integer data type, Enterprise ColumnStore will substitute the maximum value for the specific integer data type. If a value is too small for an integer data type, Enterprise ColumnStore will substitute the minimum value for the specific integer data type.

Fixed/Floating Point Numeric Data Types

Fixed and floating point numeric data types are:

Data Type

Aliases

Size

Format

DECIMAL[(M[,D])]

  • 2, 4, 8, or 16 bytes (ColumnStore 6)

  • 2, 4, or 8 bytes (ColumnStore 5)

Packed fixed-point number

FLOAT

4 bytes

IEEE-754 floating point number

DOUBLE

8 bytes

IEEE-754 floating point number

  • Enterprise ColumnStore stores DECIMAL, NUMERIC, and FIXED columns as a scaled integer without packing.

  • Enterprise ColumnStore supports specifying precision (M) and scale (D) for DECIMAL, NUMERIC, and FIXED:

    • In Enterprise ColumnStore 6, the maximum precision (M) is 38, and the maximum scale (D) is 38.

    • In Enterprise ColumnStore 5, the maximum precision (M) is 18, and the maximum scale (D) is 18.

    • When not specified, the default precision (M) is 10, and the default scale (D) is determined by the columnstore_decimal_scale system variable.

  • Enterprise ColumnStore does not support specifying precision and scale for FLOAT, DOUBLE, and REAL. For FLOAT, the maximum significant digits is about 6, and the range is +/- 1e38. For DOUBLE / REAL, the maximum significant digits is about 15, and the range is about +/- 1e308.

  • When executing SELECT statements bulk loads, Enterprise ColumnStore truncates, rather than rounds, numeric values that have too many decimal places.

  • When executing INSERT and UPDATE statements, Enterprise ColumnStore rounds numeric values that have too many decimal places.

String Data Types

String data types are:

Data Type

Size

Maximum Size

VARCHAR

1-4 bytes per character

8000 bytes

CHAR

1-4 bytes per character

255 bytes

  • Enterprise ColumnStore does not support the non-standard extensions VARCHAR(0) and CHAR(0), whereas other engines may support them.

  • Enterprise ColumnStore supports a maximum of 8000 bytes per VARCHAR column, which results in a maximum length for different character sets:

    • For 1-byte character sets (such as latin1), Enterprise ColumnStore supports up to VARCHAR(8000).

    • For 2-byte character sets (such as ucs2), Enterprise ColumnStore supports up to VARCHAR(4000).

    • For 3-byte character sets (such as utf8), Enterprise ColumnStore supports up to VARCHAR(2666).

    • For 4-byte character sets (such as utf8mb4), Enterprise ColumnStore supports up to VARCHAR(2000).

  • Enterprise ColumnStore supports a maximum of 255 bytes per CHAR column, which results in a maximum length for different character sets:

    • For 1-byte character sets (such as latin1), Enterprise ColumnStore supports up to CHAR(255).

    • For 2-byte character sets (such as ucs2), Enterprise ColumnStore supports up to VARCHAR(127).

    • For 3-byte character sets (such as utf8), Enterprise ColumnStore supports up to VARCHAR(85).

    • For 4-byte character sets (such as utf8mb4), Enterprise ColumnStore supports up to VARCHAR(63).

  • Enterprise ColumnStore stores large string values in dictionary extents, which do not support extent elimination, so it is recommended to avoid creating long string columns.

Large Object Data Types

Large object data types are:

Data Type

Size

TINYBLOB

255 bytes

TINYTEXT

255 bytes

BLOB

64 KB

TEXT

64 KB

MEDIUMBLOB

16 MB

MEDIUMTEXT

16 MB

LONGBLOB

16 MB

LONGTEXT

16 MB

  • Enterprise ColumnStore supports a maximum size of 16 MB for the LONGBLOB and LONGTEXT data types, whereas other engines support a maximum size of 4 GB.

  • Enterprise ColumnStore stores large object values in dictionary extents, which do not support extent elimination.

Date and Time Data Types

Date and time data types are:

Data Type

Size

DATE

4 bytes

DATETIME

8 bytes

TIME

8 bytes

TIMESTAMP

4 bytes

Resources

MariaDB Knowledge Base

Reference

SQL Statements