ColumnStore Create Table

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_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 or test databases.
• ColumnStore stores all object names in lower case.
• CREATE TABLE AS SELECT is not supported, and will instead create the table in the default storage engine (XtraDB).
• 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.
• 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 ColumnStore will accept the following table attributes:
  • 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 VAR-CHAR(45)
) ENGINE=ColumnStore