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][;][autoincrement=col_name,start_value’]
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];[autoincrement=startvalue]']

Notes:

• ColumnStore tables should not be created in the mysql, information_schema, calpontsys or test databases.
• ColumnStore stores all object names in lower case.
• CREATE TABLE LIKE is supported starting from version MariaDB CoumnStore 1.2 and higher
• CREATE TABLE AS SELECT is not supported, and will instead create the table in the default storage engine.
• A ColumnStore autoincrement column can be added by including an autoincrement comment at either the table level or the column level. Only one autoincrement can be defined per table. When creating data on a table with an autoincrement column either omit the column or specify the value 0 or null which will result in the system populating the autoincrement value. With cpimport, it requires an entry for each column so use the value 0 for the column in the import file. If a non 0 value is provided that value will be set for the row and the autoincrement counter does not increase.
• Compression level (0 for no compression, 1 for compression) can be set at the system level. If a session default exists, this will override the system default. In turn, this can be overridden by the table level compression comment, and finally a compression comment at the column level.
• A table can be created in the front end only by using a ‘schema sync only’ comment. This could be useful when the table has been created on one user module, and needs to be synced to others.
• The column DEFAULT value can be a maximum of 64 characters.
• For maximum compatibility with external tools MariaDB ColumnStore will accept the following table attributes, however these are not implemented within MariaDB ColumnStore:
  • MIN_ROWS
  • MAX_ROWS
  • AUTO_INCREMENT

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:

CREATE TABLE orders (
  orderkey INTEGER, 
  customer VARCHAR(45)
) ENGINE=ColumnStore