The ROLLBACK statement undoes transactions that have not been permanently saved to the database with the COMMIT statement.You cannot rollback changes to table properties including ALTER, CREATE, or DROP TABLE statements.

images here

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

The COMMIT statement makes changes to a table permanent. You should only commit changes after you have verified the integrity of the changed data. Once data is committed, it cannot be undone with the ROLLBACK statement. To return the database to its former state, you must restore the data from backups.

images here

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

The RENAME TABLE statement renames one or more ColumnStore tables.

images here

Notes:

• You cannot currently use RENAME TABLE to move a table from one database to another.

• See the ALTER TABLE syntax for an alternate to RENAME table. The following statement renames the orders table:

RENAME TABLE orders TO customer_order;

The following statement renames both the orders table and customer table:

RENAME TABLE orders TO customer_orders,customer TO customers;

You may also use RENAME TABLE to swap tables. This example swaps the customer and vendor tables (assuming the temp_table does not already exist):

RENAME TABLE customer TO temp_table, vendor TO customer,temp_table to vendor;

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

1. filter "filter"

2. String comparisons "String comparisons"

3. Pattern matching "Pattern matching"

4. OR processing "OR processing"

5. table filter "table filter"

6. join "join"

A condition is a combination of expressions and operators that return TRUE, FALSE or NULL.The following syntax shows the conditions that can be used to return a TRUE, FALSE,or NULL condition.

filter

filter:
column| literal| function [=|!=|<>|<|<=|>=|>] column| literal| function | select_statement
column| function [NOT] IN (select_statement | literal, literal,...)
column| function [NOT] BETWEEN (select_statement | literal, literal,...)
column| function  IS [NOT] NULL
string_column|string_function [NOT] LIKE pattern
EXISTS (select_statement)

NOT (filter)
(filter|function) [AND|OR] (filter|function)

String comparisons

ColumnStore, unlike the MyISAM engine, is case sensitive for string comparisons used in filters. For the most accurate results, and to avoid confusing results, make sure string filter constants are no longer than the column width itself.

Pattern matching

Pattern matching as described with the LIKE condition allows you to use “” to match any single character and “%” to match an arbitrary number of characters (including zero characters). To test for literal instances of a wildcard character, (“%” or “”),precede it by the “\” character.

OR processing

OR Processing has the following restrictions:

• Only column comparisons against a literal are allowed in conjunction with an OR. The following query would be allowed since all comparisons are against literals. SELECT count(*) from lineitem WHERE l_partkey < 100 OR l_linestatus =‘F‘;

• ColumnStore binds AND’s more tightly than OR’s, just like any other SQLparser. Therefore you must enclose OR-relations in parentheses, just like in any other SQL parser.

SELECT count(*) FROM orders, lineitem 
  WHERE (lineitem.l_orderkey < 100 OR lineitem.l_linenumber > 10) 
    AND lineitem.l_orderkey =orders.o_orderkey;

table filter

The following syntax show the conditions you can use when executing a condition against two columns. Note that the columns must be from the same table.

col_name_1 [=|!=|<>|<|<=|>=|>] col_name_2

join

The following syntax show the conditions you can use when executing a join on two tables.

join_condition [AND join_condition]
join_condition:
           [col_name_1|function_name_1] = [col_name_2|function_name_2]

Notes:

• ColumnStore tables can only be joined with non-ColumnStore tables in table mode only. See Operating Mode for information.

• ColumnStore will require a join in the WHERE clause for each set of tables in the FROM clause. No cartesian product queries will be allowed.

• ColumnStore requires that joins must be on the same datatype. In addition, numeric datatypes (INT variations, NUMERIC, DECIMAL) may be mixed in the join if they have the same scale.

• Circular joins are not supported in ColumnStore. See the Troubleshooting section

• When the join memory limit is exceeded, a disk-based join will be used for processing if this option has been enabled.

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

MariaDB ColumnStore Utility Functions are a set of simple functions that return useful information about the system, such as whether it is ready for queries.

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

The DELETE statement is used to remove rows from tables.

1. Syntax "Syntax"

Syntax

DELETE 
 [FROM] tbl_name 
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

No disk space is recovered after a DELETE. TRUNCATE and DROP PARTITION can be used to recover space, or alternatively CREATE TABLE, loading only the remaining rows, then using DROP TABLE on the original table and RENAME TABLE).

LIMIT will limit the number of rows deleted, which will perform the DELETE more quickly. The DELETE ... LIMIT statement can then be performed multiple times to achieve the same effect as DELETE with no LIMIT.

The following statement deletes customer records with a customer key identification between 1001 and 1999:

DELETE FROM customer 
  WHERE custkey > 1000 and custkey <2000

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

ColumnStore has the ability to support full MariaDB query syntax through an operating mode. This operating mode may be set as a default for the instance or set at the session level. To set the operating mode at the session level, the following command is used. Once the session has ended, any subsequent session will return to the default for the instance.

set infinidb_vtable_mode = n

where n is:

• 1. a generic, highly compatible row-by-row processing mode. Some WHERE clause components can be processed by ColumnStore, but joins are processed entirely by mysqld using a nested-loop join mechanism.

• 1. (the default) query syntax is evaluated by ColumnStore for compatibility with distributed execution and incompatible queries are rejected. Queries executed in this mode take advantage of distributed execution and typically result in higher performance.

• 1. auto-switch mode: ColumnStore will attempt to process the query internally, if it cannot, it will automatically switch the query to run in row-by-row mode.

_{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}

Introduction

Joins are performed in memory unless disk-based joins are enabled via AllowDiskBasedJoin in the columnstore.xml. When a join operation exceeds the memory allocated for query joins, the query is aborted with an error code IDB-2001. Disk-based joins enable such queries to use disk for intermediate join data in case when the memory needed for the join exceeds the memory limit. Although slower in performance as compared to a fully in-memory join and bound by the temporary space on disk, it does allow such queries to complete.

The following variables in the HashJoin element in the Columnstore.xml configuration file relate the o disk-based joins. Columnstore.xml resides in the etc directory for your installation (/usr/local/mariadb/columnstore/etc).

• AllowDiskBasedJoin—Option to use disk-based joins. Valid values are Y (enabled) or N (disabled). The default is disabled.

• TempFileCompression—Option to use compression for disk join files. Valid values are Y (use compressed files) or N (use non-compressed files).

• TempFilePath—The directory path used for the disk joins. By default, this path is the tmp directory for your installation (i.e., /tmp/columnstore_tmp_files/joins/). Files in this directory will be created and cleaned on an as-needed basis. The entire directory is removed and recreated by ExeMgr at startup.)

Per user join memory limit

In addition to the system-wide flags at the SQL global and session levels, the following system variables exist for managing per-user memory limits for joins.

• columnstore_um_mem_limit - A value for memory limit in MB per user. When this limit is exceeded by a join, it will switch to a disk-based join. By default, the limit is not set (value of 0).

For modification at the global level: In my.cnf file (example: /etc/my.cnf.d/server.cnf):

For modification at the session level, before issuing your join query from the SQL client, set the session variable as follows.

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

ColumnStore supports all MariaDB functions that can be used in a post-processing manner where data is returned by ColumnStore first and then MariaDB executes the function on the data returned. The functions are currently supported only in the projection (SELECT) and ORDER BY portions of the SQL statement.

See also

_{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 when used on ColumnStore tables.

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

MariaDB ColumnStore has the ability to change intermediate decimal mathematical results from decimal type to double. The decimal type has approximately 17-18 digits of precision but a smaller maximum range. Whereas the double type has approximately 15-16 digits of precision, but a much larger maximum range. In typical mathematical and scientific applications, the ability to avoid overflow in intermediate results with double math is likely more beneficial than the additional two digits of precision. In banking applications, however, it may be more appropriate to leave in the default decimal setting to ensure accuracy to the least significant digit.

Enable/Disable decimal to double math

The columnstore_double_for_decimal_math variable is used to control the data type for intermediate decimal results. This decimal for double math may be set as a default for the instance, set at the session level, or at the statement level by toggling this variable on and off.

To enable/disable the use of the decimal to double math at the session level, the following command is used. Once the session has ended, any subsequent session will return to the default for the instance.

where n is:

• off (disabled, default)

• on (enabled)

ColumnStore decimal scale

ColumnStore has the ability to support varied internal precision on decimal calculations. columnstore_decimal_scale is used internally by the ColumnStore engine to control how many significant digits to the right of the decimal point are carried through in suboperations on calculated columns. If, while running a query, you receive the message ‘aggregate overflow,’ try reducing columnstore_decimal_scale and running the query again. Note that, as you decrease columnstore_decimal_scale, you may see reduced accuracy in the least significant digit(s) of a returned calculated column. columnstore_decimal_scale is used internally by the ColumnStore engine to turn the use of this internal precision on and off. These two system variables may be set as a default for the instance or set at the session level.

Enable/disable decimal scale

To enable/disable the use of the decimal scale at the session level, the following command is used. Once the session has ended, any subsequent session will return to the default for the instance.

where n is off (disabled) or on (enabled).

Set decimal scale level

To set the decimal scale at the session level, the following command is used. Once the session has ended, any subsequent session will return to the default for the instance.

where n is the amount of precision desired for calculations.

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

The statement deletes a table from ColumnStore.

Syntax

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:

See also

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

MariaDB ColumnStore has the ability to compress data and this is controlled through a compression mode. This compression mode may be set as a default for the instance or set at the session level.

To set the compression mode at the session level, the following command is used. Once the session has ended, any subsequent session will return to the default for the instance.

where n is:

• 1. compression is turned off. Any subsequent table create statements run will have compression turned off for that table unless any statement overrides have been performed. Any alter statements run to add a column will have compression turned off for that column unless any statement override has been performed.

• 1. compression is turned on. Any subsequent table create statements run will have compression turned on for that table unless any statement overrides have been performed. Any alter statements run to add a column will have compression turned on for that column unless any statement override has been performed. ColumnStore uses snappy compression in this mode.

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

1. Functions "Functions"

2. Example "Example"

Functions

MariaDB ColumnStore Information Functions are selectable pseudo functions that return MariaDB ColumnStore specific “meta” information to ensure queries can be locally directed to a specific node. These functions can be specified in the projection (SELECT), WHERE, GROUP BY, HAVING and ORDER BY portions of the SQL statement and will be processed in a distributed manner.

1. Introduction "Introduction"

2. Using user defined aggregate functions "Using user defined aggregate functions"

3. Developing a new function "Developing a new function"

4. Limitations "Limitations"

Introduction

Starting with MariaDB ColumnStore 1.1, the ability to create and use user defined aggregate and window functions is supported in addition to scalar functions. With Columnstore 1.2, multiple parameters are supported. A C++ SDK is provided as well as 3 reference examples that provide additional functions that may be of general use:

• median - mathematical median, equivalent to percentile_cont(0.5)

• avg_mode - mathematical mode, i.e. the most frequent value in the set

• ssq - sum of squares, i.e. the sum of each individual number squared in the set

Similar to built-in functions, the SDK supports distributed aggregate execution where as much of the calculation is scaled out across PM nodes and then collected / finalized in the UM node. Window functions (due to the ordering requirement) are only executed at the UM level.

Using user defined aggregate functions

The reference examples above are included in the standard build of MariaDB ColumnStore and so can be used by registering them as user defined aggregate functions. The same can be done for new functions assuming the instance has the updated libraries included. From a mcsmysql prompt:

CREATE AGGREGATE FUNCTION median returns REAL soname 'libudf_mysql.so';
CREATE AGGREGATE FUNCTION avg_mode returns REAL soname 'libudf_mysql.so';
CREATE AGGREGATE FUNCTION ssq returns REAL soname 'libudf_mysql.so';

After this these may be used in the same way as any other aggregate or window function like sum:

SELECT grade, 
AVG(loan_amnt) avg, 
MEDIAN(loan_amnt) median 
FROM loanstats 
GROUP BY grade 
ORDER BY grade;

Developing a new function

This requires a MariaDB ColumnStore source tree and necessary tools to compile C/C++ code. The SDK and reference examples are available in the utils/udfsdk directory of the source tree. This contains the SDK documentation which is also available here:

• 1.2.x UDAF SDK Guide "1.2.x UDAF SDK Guide"

Limitations

• The implementation of the median and avg_mode functions will scale in memory consumption to the size of the set of unique values in the aggregation.

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

The INSERT statement allows you to add data to tables.

1. Syntax "Syntax"

2. INSERT SELECT "INSERT SELECT"

3. Autoincrement "Autoincrement"

Syntax

INSERT 
 INTO tbl_name [(col,...)]
 {VALUES | VALUE} ({expr | DEFAULT},...),(...),...

The following statement inserts a row with all column values into the customer table:

INSERT INTO customer (custno, custname, custaddress, phoneno, cardnumber, comments) 
  VALUES (12, ‘JohnSmith’, ‘100 First Street, Dallas’, ‘(214) 555-1212’,100, ‘On Time’)

The following statement inserts two rows with all column values into the customer table:

INSERT INTO customer (custno, custname, custaddress, phoneno, cardnumber, comments) VALUES 
  (12, ‘JohnSmith’, ‘100 First Street, Dallas’, ‘(214) 555-1212’,100, ‘On Time’),
  (13, ‘John Q Public’, ‘200 Second Street, Dallas’, ‘(972) 555-1234’, 200, ‘LatePayment’);

INSERT SELECT

With INSERT ... SELECT, you can quickly insert many rows into a table from one or more other tables.

• ColumnStore ignores the ON DUPLICATE KEY clause.

• Non-transactional INSERT ... SELECT is directed to ColumnStores cpimport tool by default, which significantly increases performance.

• Transactional INSERT ... SELECT statements (that is with AUTOCOMMIT off or after a START TRANSACTION) are processed through normal DML processes.

Autoincrement

Example

create table autoinc_test(
id int,
name varchar(10))
engine=columnstore comment 'autoincrement=id';

INSERT INTO autoinc_test (name) VALUES ('John');
INSERT INTO autoinc_test (name) VALUES ('Doe');

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

The UPDATE statement changes data stored in rows.

1. Syntax "Syntax"

Syntax

Single-table syntax:

UPDATE  table_reference 
  SET col1={expr1|DEFAULT} [,col2={expr2|DEFAULT}] ...
  [WHERE where_condition]
  [ORDER BY ...]
  [LIMIT row_count]

Multiple-table syntax:

UPDATE table_references
    SET col1={expr1|DEFAULT} [, col2={expr2|DEFAULT}] ...
    [WHERE where_condition]

_{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"

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}

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. The file name must be given as a literal string.

LOAD DATA [LOCAL] INFILE 'file_name' 
  INTO TABLE tbl_name
  [CHARACTER SET charset_name]
  [{FIELDS | COLUMNS}
    [TERMINATED BY 'string']
    [[OPTIONALLY] ENCLOSED BY 'char']
    [ESCAPED BY 'char']
  ]
  [LINES
    [STARTING BY 'string']
    [TERMINATED BY 'string']
]

• ColumnStore ignores the ON DUPLICATE KEY clause.

• Non-transactional LOAD DATA INFILE is directed to ColumnStores cpimport tool by default, which significantly increases performance.

• Transactional LOAD DATA INFILE statements (that is, with AUTOCOMMIT off or after a START TRANSACTION) are processed through normal DML processes.

• Use cpimport for importing UTF-8 data that contains multi-byte values

The following example loads data into a simple 5- column table: A file named /simpletable.tblhas the following data in it.

1|100|1000|10000|Test Number 1|
2|200|2000|20000|Test Number 2|
3|300|3000|30000|Test Number 3|

The data can then be loaded into the simpletable table with the following syntax:

LOAD DATA INFILE 'simpletable.tbl' INTO TABLE simpletable FIELDS TERMINATED BY '|'

If the default mode is set to use cpimport internally, any output error files will be written to /var/log/mariadb/columnstore/cpimport/ directory. It can be consulted for troubleshooting any errors reported.

See Also

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

MariaDB ColumnStore supports the following aggregate functions, these can be used in the SELECT, HAVING, and ORDER BY clauses of the SQL statement.

Note

• Regression functions (REGR_AVGX to REGR_YY), CORR, COVAR_POP and COVAR_SAMP are supported for version 1.2.0 and higher

Example

An example group by query using aggregate functions is:

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

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

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:

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

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}

The DROP PROCEDURE statement deletes a stored procedure from ColumnStore.

images here

The following statement drops the sp_complex_variable procedure:

DROP PROCEDURE sp_complex_variable;

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

This lists the different naming conventions enforced by the column store, compared to the normal .

• User names: 64 characters (MariaDB has 80)

• Table and column names are restricted to alphanumeric and underscore only, i.e "A-Z a-z 0-9 _".

• The first character of all table and column names should be an ASCII letter (a-z A-Z).

• ColumnStore reserves certain words that MariaDB does not, such as SELECT, CHAR and TABLE, so even wrapped in backticks these cannot be used.

Reserved words

In addition to MariaDB Server , ColumnStore has additional reserved words that cannot be used as table names, column names or user defined variables, functions or stored procedure names.

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

The SELECT statement is used to query the database and display table data. You can add many clauses to filter the data.

1. Syntax "Syntax"

2. Projection List (SELECT) "Projection List (SELECT)"

3. WHERE "WHERE"

4. GROUP BY "GROUP BY"

5. HAVING "HAVING"

6. ORDER BY "ORDER BY"

7. UNION "UNION"

8. LIMIT "LIMIT"

Syntax

SELECT
    [ALL | DISTINCT ]
    select_expr [, select_expr ...]
    [ FROM table_references
      [WHERE where_condition]
      [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]
      [HAVING where_condition]
      [ORDER BY {col_name | expr | position} [ASC | DESC], ...]
      [LIMIT {[offset,] row_count | row_count OFFSET offset}]
      [PROCEDURE procedure_name(argument_list)]
      [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] [export_options]
         | INTO DUMPFILE 'file_name' | INTO var_name [, var_name] ]
export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]

<<toc>>

Projection List (SELECT)

If the same column needs to be referenced more than once in the projection list, a unique name is required for each column using a column alias. The total length of the name of a column, inclusive of the length of functions, in the projection list must be 64 characters or less.

WHERE

The WHERE clause filters data retrieval based on criteria. Note that column_alias cannot be used in the WHERE clause. The following statement returns rows in the region table where the region = ‘ASIA’:

SELECT * FROM region WHERE name = ’ASIA’;

GROUP BY

GROUP BY groups data based on values in one or more specific columns. The following statement returns rows from the lineitem table where /orderkeyis less than 1 000 000 and groups them by the quantity.

SELECT quantity, count(*) FROM lineitem WHERE orderkey < 1000000 GROUP BY quantity;

HAVING

HAVING is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns.The following statement returns shipping dates, and the respective quantity where the quantity is 2500 or more.

SELECT shipdate, count(*) FROM lineitem GROUP BYshipdate HAVING count(*) >= 2500;

ORDER BY

The ORDER BY clause presents results in a specific order. Note that the ORDER BY clause represents a statement that is post-processed by MariaDB. The following statement returns an ordered quantity column from the lineitem table.

SELECT quantity FROM lineitem WHERE orderkey < 1000000 order by quantity;

The following statement returns an ordered shipmode column from the lineitem table.

Select shipmode from lineitem where orderkey < 1000000 order by 1;

NOTE: When ORDER BY is used in an inner query and LIMIT on an outer query, LIMIT is applied first and then ORDER BY is applied when returning results.

UNION

Used to combine the result from multiple SELECT statements into a single result set.The UNION or UNION DISTINCT clause returns query results from multiple queries into one display and discards duplicate results. The UNION ALL clause displays query results from multiple queries and does not discard the duplicates. The following statement returns the p_name rows in the part table and the partno table and discards the duplicate results:

SELECT p_name FROM part UNION select p_name FROM  partno;

The following statement returns all the p_name rows in the part table and the partno table:

SELECT p_name FROM part UNION ALL select p_name FROM  partno;

LIMIT

A limit is used to constrain the number of rows returned by the SELECT statement. LIMIT can have up to two arguments. LIMIT must contain a row count and may optionally contain an offset of the first row to return (the initial row is 0). The following statement returns 5 customer keys from the customer table:

SELECT custkey from customer limit 5;

The following statement returns 5 customer keys from the customer table beginning at offset 1000:

SELECT custkey from customer limit 1000,5;

NOTE: When LIMIT is applied on a nested query's results, and the inner query contains ORDER BY, LIMIT is applied first, and then ORDER BY is applied.

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

1. Introduction "Introduction"

2. Developing a user defined function "Developing a user defined function"

3. MariaDB server UDF implementation "MariaDB server UDF implementation"

4. ColumnStore distributed UDF implementation "ColumnStore distributed UDF implementation"

5. Deploying and using a UDF "Deploying and using a UDF"

Introduction

MariaDB provides extensibility support through user defined functions. For more details on the MariaDB server framework see the article. This documentation applies to MariaDB ColumnStore version 1.0.10 and above.

MariaDB ColumnStore provides scale out query processing and as such requires a separate distributed implementation of each SQL function. This allows for the function application to happen on each PM server node providing distributed scale out performance.

Thus, to fully implement a user defined function for MariaDB ColumnStore requires implementing 2 different API's:

• The MariaDB server UDF API: This allows utilization on all storage engines and is the implementation used if applied in the select list.

• The ColumnStore distributed UDF API: This enables distributed execution of where clause and group by usage of the function and will be pushed down to PM nodes for execution where possible.

MariaDB ColumnStore supports user defined function implementations in C/C++. User defined aggregate and window functions are not supported in ColumnStore 1.0.

Developing a user defined function

The development kit can be found under the utils/udfsdk directory of the mariadb-columnstore-engine source tree. To develop a user defined function requires you to set up a development environment and be comfortable with c++ development. To setup a ColumnStore development environment please follow the instructions on dependencies in the ColumnStore server fork repository.

Three main files will need to be modified in order to add a new UDF:

• udfmysql.cpp : mariadb server UDF implementation

• udfsdk.h : class headers.

• udfsdk.cpp : distributed columnstore UDF implementation.

Two reference implementations are provided to provide guidance on creating your own functions:

• MCS_IsNull : this illustrates a simple one argument function providing the ability to return a Boolean if the expression parameter is null

• MCS_Add: this illustrates a simple 2 argument function to illustrate adding 2 values and return the sum.

It is simplest to copy these and adapt to your needs. There are no system dependencies on the included reference implementations so these can be removed to simplify the class files if preferred.

MariaDB server UDF implementation

Three functions are required to be implemented (for more details see ):

• x_init : perform any parameter validation or setup such as memory allocation.

• x : perform the actual function implementation.

• x_deinit : perform any clean up tasks such as deallocating memory where 'x' is the function name.

ColumnStore distributed UDF implementation

The function name and class must be registered in order to be recognized and used by the ColumnStore primitive processor. This is done by adding a line to perform the registration in the UDFSDK::UDFMap() function in the file udfsdk.cpp:

FuncMap UDFSDK::UDFMap() const
{
	FuncMap fm;
	
	// first: function name
	// second: Function pointer
	// please use lower case for the function name. Because the names might be 
	// case-insensitive in MariaDB depending on the setting. In such case,
	// the function names passed to the interface is always in lower case.
	fm["mcs_add"] = new MCS_add();
	fm["mcs_isnull"] = new MCS_isnull();

	return fm;
}

For any new user defined functions add a new entry into the FuncMap object mapping the name to the udf class.

The UDF class should be defined in file udfsdk.h and implemented in file udfsdk.cpp. It is easiest to adapt the example classes for new instance but each class must implement the funcexp::Func C++ class definition:

• constructor: any initialization necessary

• destructor: any de-initialization.

• getOperationType: this performs parameter validation and returns the result data type.

• getVal : computes and returns the value of the user defined function for each given return datatype.

The code changes can be built using make within the directory utils/udfsdk, this will create the following libraries in the same directory:

• libudf_mysql.so.1.0.0

• libudfsdk.so.1.0.0

containing the compiled code

Deploying and using a UDF

The 2 libraries created above must be deployed to the /usr/local/mariadb/columnstore/lib directory (or equivalent lib directory in a non root install) replacing the existing files. Symbolic links in the mariadb server directory point to these but should be validated. Run the following as root from the utils/udfsdk directory in the build tree (specifying a password to restartSystem if needed for a multi server cluster):

$ cp libudf_mysql.so.1.0.0 libudfsdk.so.1.0.0 /usr/local/mariadb/columnstore/lib/
$ ls -l /usr/local/mariadb/columnstore/mysql/lib/plugin/libudf_mysql.so
lrwxrwxrwx. 1 root root 56 Jul 19 09:47 /usr/local/mariadb/columnstore/mysql/lib/plugin/libudf_mysql.so -> /usr/local/mariadb/columnstore/lib/libudf_mysql.so.1.0.0

Repeat this for each ColumnStore UM and PM node in the cluster and then restart ColumnStore to make the libraries available.

After restarting the system the UDF must be registered with the MariaDB server to be usable:

$ mcsmysql
    > create function mcs_add returns integer soname 'libudf_mysql.so';

The function mcs_add can then be used. Verify that it can be used both in the select list and where clause for correct installation:

MariaDB [test]> create function mcs_add returns integer soname 'libudf_mysql.so';
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> create table t1(i1 int, i2 int) engine=columnstore;
Query OK, 0 rows affected (0.58 sec)

MariaDB [test]> insert into t1 values (1,1), (2,2);
Query OK, 2 rows affected (0.24 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> select i1, i2, mcs_add(i1,i2) sum from t1;
+------+------+------+
| i1   | i2   | sum  |
+------+------+------+
|    1 |    1 |    2 |
|    2 |    2 |    4 |
+------+------+------+
2 rows in set (0.05 sec)

MariaDB [test]> select i1, i2 from t1 where mcs_add(i1,i2) = 4;
+------+------+
| i1   | i2   |
+------+------+
|    2 |    2 |
+------+------+
1 row in set (0.02 sec)

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

ColumnStore supports the following data types:

Numeric Data Types

String Data Types

Date and Time Data Types

Notes

• ColumnStore treats a zero-length string as a NULL value.

• As with core MariaDB, ColumnStore employs “saturation semantics” on integer values. This means that if a value is inserted into an integer field that is too big/small for it to hold (i.e. it is more negative or more positive than the values indicated above), ColumnStore will “saturate” that value to the min/max value indicated above as appropriate. For example, for a SMALLINT column, if 32800 is attempted, the actual value inserted will be 32767.

• ColumnStore largest negative and positive numbers appears to be 2 less than what MariaDB supports. ColumnStore reserves these for its internal use and they cannot be used. For example, if there is a need to store -128 in a column, the TINYINT datatype cannot be used; the SMALLINT datatype must be used instead. If the value -128 is inserted into a TINYINT column, ColumnStore will saturate it to -126 (and issue a warning).

• ColumnStore truncates rather than rounds decimal constants that have too many digits after the decimal point during bulk load and when running SELECT statements. For INSERT and UPDATE, however, the MariaDB parser will round such constants. You should verify that ETL tools used and any INSERT/UPDATEstatements only specify the correct number of decimal digits to avoid potential confusion.

• An optional display width may be added to the BIGINT, INTEGER/INT, SMALLINT & TINYINT columns. As with core MariaDB tables, this value does not affect the internal storage requirements of the column nor does it affect the valid value ranges.

• All columns in ColumnStore are nullable and the default value for any column is NULL. You may optionally specify NOT NULL for any column and/or one with a DEFAULT value.

• Unlike other MariaDB storage engines, the actual storage limit for LONGBLOB/LONGTEXT is 2,100,000,000 bytes instead of 4GB per entry. MariaDB's client API is limited to a row length of 1GB.

• Timestamp und current_timestamp still not supported. (MCOL-3694 / MCOL-1039)

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

1. COLUMNSTORE_TABLES "COLUMNSTORE_TABLES"

2. COLUMNSTORE_COLUMNS "COLUMNSTORE_COLUMNS"

3. COLUMNSTORE_EXTENTS "COLUMNSTORE_EXTENTS"

4. COLUMNSTORE_FILES "COLUMNSTORE_FILES"

5. Stored Procedures "Stored Procedures"

6. total_usage() "total_usage()"

7. table_usage() "table_usage()"

8. compression_ratio() "compression_ratio()"

MariaDB ColumnStore has four information schema tables that expose information about the table and column storage. The tables were added in version 1.0.5 of ColumnStore and were heavily modified for 1.0.6.

COLUMNSTORE_TABLES

The first table is the INFORMATION_SCHEMA.COLUMNSTORE_TABLES. It contains information about the tables inside ColumnStore.

COLUMNSTORE_COLUMNS

The INFORMATION_SCHEMA.COLUMNSTORE_COLUMNS table contains information about every single column inside ColumnStore.

COLUMNSTORE_EXTENTS

This table displays the extent map in a user-consumable form. An extent is a collection of details about a section of data related to a columnstore column. A majority of columns in ColumnStore will have multiple extents, and the columns table above can be joined to this one to filter results by table or column. The table layout is as follows:

Notes:

1. The state is "Valid" for a normal state, "Invalid" if a cpimport has completed but the table has not yet been accessed (min/max values will be invalid)or "Updating" if there is a DML statement writing to the column

2. In ColumnStore the block size is 8192 bytes

3. By default, ColumnStore will write and create an extent file of 2561024WIDTH bytes for the first partition; if this is too small, then for uncompressed data, it will create a file of the maximum size for the extent (MAX_BLOCKS * BLOCK_SIZE). Snappy always compression adds a header block.

4. Object IDs of less than 3000 are for internal tables and will not appear in any of the information schema tables

5. HWM is set to zero for the lower segments when there are multiple segments in an extent file; these can be observed when BLOCK_OFFSET > 0

6. When HWM is 0, the DATA_SIZE will show 0 instead of 8192 to avoid confusion when there is multiple segments in an extent file

COLUMNSTORE_FILES

The columnstore_files table provides information about each file associated with extensions. Each extension can reuse a file at different block offsets, so this is not a 1:1 relationship to the columnstore_extents table.

Stored Procedures

The total_usage() procedure gives a total disk usage summary for all the columns in ColumnStore except the columns used for internal maintenance. It is executed using the following query:

> call columnstore_info.total_usage();

table_usage()

The table_usage() procedure gives the total data disk usage, dictionary disk usage, and grand total disk usage per table. It can be called in several ways; the first gives a total for each table:

> call columnstore_info.table_usage(NULL, NULL);

Or for a specific table, my_table in my_schema in this example:

> call columnstore_info.table_usage('my_schema', 'my_table');

You can also request all tables for a specified schema:

> call columnstore_info.table_usage('my_schema', NULL);

compression_ratio()

The compression_ratio() procedure calculates the average compression ratio across all the compressed extents in ColumnStore. It is called using

> call columnstore_info.compression_ratio();

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

ColumnStore supports the following functions. These functions can be specified in the projection (SELECT), WHERE and ORDER BY portions of the SQL statement and will be processed in a distributed manner.

• ,

• ,

• ,

See also

• ColumnStore Non-Distributed Post-Processed Functions

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

1. Introduction "Introduction"

2. Syntax "Syntax"

3. Supported Functions "Supported Functions"

4. Note "Note"

5. Examples "Examples"

6. Example Schema "Example Schema"

7. Cumulative Sum and Running Max Example "Cumulative Sum and Running Max Example"

8. Partitioned Cumulative Sum and Running Max Example "Partitioned Cumulative Sum and Running Max Example"

9. Ranking / Top Results "Ranking / Top Results"

10. First and Last Values "First and Last Values"

11. Prior and Next Example "Prior and Next Example"

12. Quartiles Example "Quartiles Example"

13. Percentile Example "Percentile Example"

Introduction

MariaDB ColumnStore provides support for window functions broadly following the SQL 2003 specification. A window function allows for calculations relating to a window of data surrounding the current row in a result set. This capability provides for simplified queries in support of common business questions such as cumulative totals, rolling averages, and top 10 lists.

Aggregate functions are utilized for window functions however differ in behavior from a group by query because the rows remain ungrouped. This provides support for cumulative sums and rolling averages, for example.

Two key concepts for window functions are Partition and Frame:

• A Partition is a group of rows, or window, that have the same value for a specific column, for example a Partition can be created over a time period such as a quarter or lookup values.

• The Frame for each row is a subset of the row's Partition. The frame typically is dynamic allowing for a sliding frame of rows within the Partition. The Frame determines the range of rows for the windowing function. A Frame could be defined as the last X rows and next Y rows all the way up to the entire Partition.

Window functions are applied after joins, group by, and having clauses are calculated.

Syntax

A window function is applied in the select clause using the following syntax:

function_name ([expression [, expression ... ]]) OVER ( window_definition )

where window_definition is defined as:

[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]

PARTITION BY:

• Divides the window result set into groups based on one or more expressions.

• An expression may be a constant, column, and non window function expressions.

• A query is not limited to a single partition by clause. Different partition clauses can be used across different window function applications.

• The partition by columns do not need to be in the select list but do need to be available from the query result set.

• If there is no PARTITION BY clause, all rows of the result set define the group.

ORDER BY

• Defines the ordering of values within the partition.

• Can be ordered by multiple keys which may be a constant, column or non window function expression.

• The order by columns do not need to be in the select list but need to be available from the query result set.

• Use of a select column alias from the query is not supported.

• ASC (default) and DESC options allow for ordering ascending or descending.

• NULLS FIRST and NULL_LAST options specify whether null values come first or last in the ordering sequence. NULLS_FIRST is the default for ASC order, and NULLS_LAST is the default for DESC order.

and the optional frame_clause is defined as:

{ RANGE | ROWS } frame_start
{ RANGE | ROWS } BETWEEN frame_start AND frame_end

and the optional frame_start and frame_end are defined as (value being a numeric expression):

UNBOUNDED PRECEDING
value PRECEDING
CURRENT ROW
value FOLLOWING
UNBOUNDED FOLLOWING

RANGE/ROWS:

• Defines the windowing clause for calculating the set of rows that the function applies to for calculating a given rows window function result.

• Requires an ORDER BY clause to define the row order for the window.

• ROWS specify the window in physical units, i.e. result set rows and must be a constant or expression evaluating to a positive numeric value.

• RANGE specifies the window as a logical offset. If the expression evaluates to a numeric value then the ORDER BY expression must be a numeric or DATE type. If the expression evaluates to an interval value then the ORDER BY expression must be a DATE data type.

• UNBOUNDED PRECEDING indicates the window starts at the first row of the partition.

• UNBOUNDED FOLLOWING indicates the window ends at the last row of the partition.

• CURRENT ROW specifies the window start or ends at the current row or value.

• If omitted, the default is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Supported Functions

Examples

Example Schema

The examples are all based on the following simplified sales opportunity table:

create table opportunities (
id int,
accountName varchar(20),
name varchar(128),
owner varchar(7),
amount decimal(10,2),
closeDate date,
stageName varchar(11)
) engine=columnstore;

Some example values are (thanks to www.mockaroo.com for sample data generation):

The schema, sample data, and queries are available as an attachment to this article.

Cumulative Sum and Running Max Example

Window functions can be used to achieve cumulative / running calculations on a detail report. In this case a won opportunity report for a 7 day period adds columns to show the accumulated won amount as well as the current highest opportunity amount in preceding rows.

select owner, 
accountName, 
CloseDate, 
amount, 
sum(amount) over (order by CloseDate rows between unbounded preceding and current row) cumeWon, 
max(amount) over (order by CloseDate rows between unbounded preceding and current row) runningMax
from opportunities 
where stageName='ClosedWon' 
and closeDate >= '2016-10-02' and closeDate <= '2016-10-09' 
order by CloseDate;

with example results:

Partitioned Cumulative Sum and Running Max Example

The above example can be partitioned, so that the window functions are over a particular field grouping such as owner and accumulate within that grouping. This is achieved by adding the syntax "partition by " in the window function clause.

select owner,  
accountName,  
CloseDate,  
amount,  
sum(amount) over (partition by owner order by CloseDate rows between unbounded preceding and current row) cumeWon,  
max(amount) over (partition by owner order by CloseDate rows between unbounded preceding and current row) runningMax 
from opportunities  
where stageName='ClosedWon' 
and closeDate >= '2016-10-02' and closeDate <= '2016-10-09'  
order by owner, CloseDate;

with example results:

Ranking / Top Results

The rank window function allows for ranking or assigning a numeric order value based on the window function definition. Using the Rank() function will result in the same value for ties / equal values and the next rank value skipped. The Dense_Rank() function behaves similarly except the next consecutive number is used after a tie rather than skipped. The Row_Number() function will provide a unique ordering value. The example query shows the Rank() function being applied to rank sales reps by the number of opportunities for Q4 2016.

select owner, 
wonCount, 
rank() over (order by wonCount desc) rank 
from (
  select owner, 
  count(*) wonCount 
  from opportunities 
  where stageName='ClosedWon' 
  and closeDate >= '2016-10-01' and closeDate < '2016-12-31'  
  group by owner
) t
order by rank;

with example results (note the query is technically incorrect by using closeDate < '2016-12-31' however this creates a tie scenario for illustrative purposes):

If the dense_rank function is used the rank values would be 1,2,3,3,4 and for the row_number function the values would be 1,2,3,4,5.

First and Last Values

The first_value and last_value functions allow determining the first and last values of a given range. Combined with a group by this allows summarizing opening and closing values. The example shows a more complex case where detailed information is presented for first and last opportunity by quarter.

select a.year, 
a.quarter, 
f.accountName firstAccountName, 
f.owner firstOwner, 
f.amount firstAmount, 
l.accountName lastAccountName, 
l.owner lastOwner, 
l.amount lastAmount 
from (
  select year, 
  quarter, 
  min(firstId) firstId, 
  min(lastId) lastId 
  from (
    select year(closeDate) year, 
    quarter(closeDate) quarter, 
    first_value(id) over (partition by year(closeDate), quarter(closeDate) order by closeDate rows between unbounded preceding and current row) firstId, 
    last_value(id) over (partition by year(closeDate), quarter(closeDate) order by closeDate rows between current row and unbounded following) lastId 
    from opportunities  where stageName='ClosedWon'
  ) t 
  group by year, quarter order by year,quarter
) a 
join opportunities f on a.firstId = f.id 
join opportunities l on a.lastId = l.id 
order by year, quarter;

with example results:

Prior and Next Example

Sometimes it useful to understand the previous and next values in the context of a given row. The lag and lead window functions provide this capability. By default the offset is one providing the prior or next value but can also be provided to get a larger offset. The example query is a report of opportunities by account name showing the opportunity amount, and the prior and next opportunity amount for that account by close date.

select accountName, 
closeDate,  
amount currentOppAmount, 
lag(amount) over (partition by accountName order by closeDate) priorAmount, lead(amount) over (partition by accountName order by closeDate) nextAmount 
from opportunities 
order by accountName, closeDate 
limit 9;

with example results:

Quartiles Example

The NTile window function allows for breaking up a data set into portions assigned a numeric value to each portion of the range. NTile(4) breaks the data up into quartiles (4 sets). The example query produces a report of all opportunities summarizing the quartile boundaries of amount values.

select t.quartile, 
min(t.amount) min, 
max(t.amount) max 
from (
  select amount, 
  ntile(4) over (order by amount asc) quartile 
  from opportunities 
  where closeDate >= '2016-10-01' and closeDate <= '2016-12-31'
  ) t 
group by quartile 
order by quartile;

With example results:

Percentile Example

The percentile functions have a slightly different syntax from other window functions as can be seen in the example below. These functions can be only applied against numeric values. The argument to the function is the percentile to evaluate. Following 'within group' is the sort expression which indicates the sort column and optionally order. Finally after 'over' is an optional partition by clause, for no partition clause use 'over ()'. The example below utilizes the value 0.5 to calculate the median opportunity amount in the rows. The values differ sometimes because percentile_cont will return the average of the 2 middle rows for an even data set while percentile_desc returns the first encountered in the sort.

select owner,  
accountName,  
CloseDate,  
amount,
percentile_cont(0.5) within group (order by amount) over (partition by owner) pct_cont,
percentile_disc(0.5) within group (order by amount) over (partition by owner) pct_disc
from opportunities  
where stageName='ClosedWon' 
and closeDate >= '2016-10-02' and closeDate <= '2016-10-09'  
order by owner, CloseDate;

With example results:

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