1. Syntax "Syntax"

2. ADD "ADD"

3. Online alter table add column "Online alter table add column"

4. CHANGE "CHANGE"

5. DROP "DROP"

6. RENAME "RENAME"

The ALTER TABLE statement modifies existing tables. It includes adding, deleting, and renaming columns as well as renaming tables.

Syntax

ALTER TABLE tbl_name
    alter_specification [, alter_specification] ...

alter_specification:
    table_option ...
  | ADD [COLUMN] col_name column_definition
  | ADD [COLUMN] (col_name column_definition,...)
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
  | DROP [COLUMN] col_name
  | RENAME [TO] new_tbl_name
 

column_definition:
    data_type
      [NOT NULL | NULL]
      [DEFAULT default_value]
      [COMMENT '[compression=0|1];']

table_options:
    table_option [[,] table_option] ...  (see CREATE TABLE OPTIONS)

images here

ADD

The ADD clause allows you to add columns to a table. You must specify the data type after the column name. The following statement adds a priority column with an integer datatype to the orders table:

ALTER TABLE orders ADD COLUMN priority INTEGER;

• Compression level (0 for no compression, 1 for compression) is set at the system level. If a session default exists, it will override the system default. In turn, it can be overridden by the table-level compression comment and finally, a compression comment at the column level.

Online alter table add columntable-level

The ColumnStore engine fully supports online DDL (one session can be adding columns to a table while another session is querying that table). MariaDB ColumnStore has provided it own syntax to do so for adding columns to a table, one at a time only. Do not attempt to use it for any other purpose. Follow the example below as closely as possible

We have also provided the following workaround. This workaround is intended for adding columns to a table, one at a time only. Do not attempt to use it for any other purpose. Follow the example below as closely as possible.

Scenario: Add an INT column named col7 to the existing table foo:

SELECT calonlinealter('alter table foo add column col7 int;');
ALTER TABLE foo ADD COLUMN col7 INT COMMENT 'schema sync only';

The select statement may take several tens of seconds to run, depending on how many rows are currently in the table. Regardless, other sessions can select against the table during this time (but they won’t be able to see the new column yet). The ALTER TABLE statement will take less than 1 second (depending on how busy MariaDB is), and during this brief time interval, other table reads will be held off.

CHANGE

The CHANGE clause allows you to rename a column in a table.

Notes to CHANGE COLUMN:

• You cannot currently use CHANGE COLUMN to change the definition of that column.

• You can only change a single column at a time. The following example renames the order_qty field to quantity in the orders table:

ALTER TABLE orders CHANGE COLUMN order_qty quantity
INTEGER;

DROP

The DROP clause allows you to drop columns. All associated data is removed when the column is dropped. You can DROP COLUMN (column_name). The following example alters the orders table to drop the priority column:

ALTER TABLE orders DROP COLUMN priority;

RENAME

The RENAME clause allows you to rename a table. The following example renames the orders table:

ALTER TABLE orders RENAME TO customer_orders;

_{This page is licensed: CC BY-SA / Gnu FDL}

1. Syntax "Syntax"

Alters the definition of a view. CREATE OR REPLACE VIEW may also be used to alter the definition of a view.

Syntax

CREATE
    [OR REPLACE]
    VIEW view_name [(column_list)]
    AS select_statement

_{This page is licensed: CC BY-SA / Gnu FDL}

1. Syntax "Syntax"

Creates a stored routine in ColumnStore.

Syntax

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

type:
    Any valid MariaDB ColumnStore data type

routine_body:
    Valid SQL procedure statement

ColumnStore currently accepts definition of stored procedures with only input arguments and a single SELECT query while in Operating Mode = 1 (VTABLE mode). However, while in the Operating Mode = 0 (TABLE mode), ColumnStore will allow additional complex definition of stored procedures (i.e., OUT parameter, declare, cursors,etc.)

See Operating Mode for information on Operating Modes

images here

The following statements create and call the sp_complex_variable stored procedure:

CREATE PROCEDURE sp_complex_variable(IN arg_key INT, IN arg_date DATE)
  BEGIN
    SELECT *
    FROM lineitem, orders
    WHERE o_custkey < arg_key
    AND l_partkey < 10000
    AND l_shipdate>arg_date
    AND l_orderkey = o_orderkey
    ORDER BY l_orderkey, l_linenumber;
  END

CALL sp_complex_variable(1000, '1998-10-10');

_{This page is licensed: CC BY-SA / Gnu FDL}

1. Syntax "Syntax"

2. Notes: "Notes:"

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][;]
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]
      [COMMENT='schema sync only']
      [COMMENT 'autoincrement column_name'];

images here

Notes:

• ColumnStore tables should not be created in the mysql, information_schema, calpontsys, or test databases.

• ColumnStore stores all object names in lowercase.

• CREATE TABLE AS SELECT is not supported and will instead create the table in the default storage engine.

• Compression level (0 for no compression, 1 for compression) is set at the system level. If a session default exists, it will override the system default. In turn, it can be overridden by the table-level compression comment and, finally, a compression comment at the column level.

• A table is created in the front end only by using a ‘schema sync only’ comment.

• 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

_{This page is licensed: CC BY-SA / Gnu FDL}

1. Syntax "Syntax"

Creates a stored query in the MariaDB ColumnStore

Syntax

CREATE
    [OR REPLACE]
    VIEW view_name [(column_list)]
    AS select_statement

Notes to CREATE VIEW:

• If you describe a view in MariaDB ColumnStore, the column types reported may not match the actual column types in the underlying tables. This is normal and can be ignored. The following statement creates a customer view of orders with status:

CREATE VIEW v_cust_orders (cust_name, order_number, order_status) AS
SELECT c.cust_name, o.ordernum, o.status FROM customer c, orders o
WHERE c.custnum = o.custnum;

_{This page is licensed: CC BY-SA / Gnu FDL}

1. Syntax "Syntax"

2. See also "See also"

The DROP TABLE statement deletes a table from ColumnStore.

Syntax

DROP  TABLE [IF EXISTS] 
    tbl_name 
    [RESTRICT ]

The RESTRICT clause limits the table to being dropped in the front end only. This could be useful when the table has been dropped on one user module, and needs to be synced to others.

images here

The following statement drops the orders table on the front end only:

DROP TABLE orders RESTRICT;

See also

• DROP TABLE

_{This page is licensed: CC BY-SA / Gnu FDL}

In most cases, a ColumnStore table works just as any other MariaDB table. There are however a few differences.

The following table lists the data definition statements (DDL) that differ from normal MariaDB DDL when used on ColumnStore tables.

_{This page is licensed: CC BY-SA / Gnu FDL}