SQL commands

ALTER DATABASE

Changes the definition of a database.

Synopsis
ALTER DATABASE
name
RENAME
new_name
Description

ALTER DATABASE supports only the database renaming.

RENAME

Changes the name of an existing database. Renaming does not affect stored data. The new_name parameter must contain a valid name and a database with the same name must not exist.

Parameters
name

Name of the database that is going to be altered. Must be a valid name of an existing database.

new_name

New name of a database. Must be a valid database name and a database with the same name must not exist.

Example 1. Using ALTER DATABASE
List of SQL commands
DROP DATABASE if exists testdb1;
DROP DATABASE if exists testdb2;
CREATE DATABASE testdb1;

USE testdb1;
CREATE TABLE test(x INT, y VARCHAR);
INSERT INTO test (x, y) VALUES (1, 'test1');
INSERT INTO test (x, y) VALUES (2, 'test2');

ALTER DATABASE testdb1 RENAME testdb2;

USE testdb2;
SELECT x, y FROM test;
Result
{"x":1,"y":"test1"}
{"x":2,"y":"test2"}

ALTER TABLE

Changes the definition of a table.

Synopsis
ALTER TABLE [ database . ] name
    RENAME new_name
ALTER TABLE [ database . ]  name
    ATTACH PARTITION partition_name
ALTER TABLE [ database . ]  name
    DETACH PARTITION partition_name
ALTER TABLE [ database . ] name
    DROP PARTITION partition_name
ALTER TABLE [ database . ] name
    DROP ROOT PARTITION
Description

ALTER TABLE changes the definition of an existing table.

RENAME

Change the name of an existing table. The new name must be unique and meet the criteria for table names. This statement does not modify the table data.

ATTACH PARTITION

Attach a previously detached table partition. The partition’s data is moved from the _detached folder to the table’s root directory.

DETACH PARTITION

Detaches the specified table partition, taking the partition data offline. The partition data is moved to the _detached folder in the table’s data directory.

DROP PARTITION

Remove the specified partition and delete all partition data from the disk. You must detach a partition before you can delete it. This operation is irreversible.

DROP ROOT PARTITION

Remove the table’s default partition. This operation is irreversible.

Parameters
database

Specifies the database which table should be altered. If this parameter is not specified, the current database is used.

name

Name of the table to be altered. Must be a valid name of the existing table.

new_name

New name of a table. Must be a valid table name and a table with the same name must be non-existent.

partition_name

Name of the partition the command will be executed on. Must be a valid name of the existing partition.

Example 2. Using ALTER TABLE
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE sample();
SHOW TABLES;
ALTER TABLE sample RENAME renamed;
SHOW TABLES;
Result
{"table":"sample","record_count":"0","encrypted":"no","partitioning_type":"none","size":"66 bytes"}
{"table":"renamed","record_count":"0","encrypted":"no","partitioning_type":"none","size":"66 bytes"}

COPY

Copies data across files in various formats.

Synopsis
COPY [ database . ] table
    [ FROM | TO ] file
    [ WITH ( option = value ) ]
Description

To save data to file, the user must have write permissions. Data is recorded based on either the file format or the command options. By default, all columns are copied unless otherwise specified in the options.

To read data from a file, the user must have read permissions. Data is read and appended to a table in the given format based on either the command options or the file extension.

To parse CSV data, the column names must be explicitly specified in the options. If a column does not exist in the table schema, it must also be specified in the options.

While parsing JSON data, this command automatically takes column names from the file description.

Upon successful completion, COPY returns the number of rows copied.

Parameters
database

Database name to copy data. If not specified, the current database is used.

table

Table name to copy data. Must be the name of an existing table.

file

Name of the file for reading or writing. The user must have read and write permissions.

option and value

Key-value pairs of the options and their values as described below.

Options

The following options are common for both CSV and JSON formats:

format

File format. Can be either CSV or JSON.

partition

Partition of the table to copy data. This option is ignored when copying to a file.

onerror

Error handling strategy. Can be either warn (default) or abort. This option is ignored when copying to a file.

The following options are applicable only for processing data in CSV format:

header

Boolean flag. If set to TRUE, this option enables skipping the first line of a copied file. When copying to a file, it generates a header line. Applicable only for CSV format.

quote

The quote character for a string. Applicable only for CSV format.

escape

The escape character for a string. Applicable only for CSV format.

delimiter

Column delimiter character in CSV data.

null

Value which represents NULL. If such a value is parsed, it will be replaced with NULL. Applicable only for CSV format.

force_not_null

Boolean flag. If set to TRUE, this option enables parsing a null or empty value as an empty string. Applicable only for CSV format.

force_quote

Boolean flag. If set to TRUE, this option enables quoting of all copied values. Applicable only for CSV format.

fields

List of fields which is used for mapping while reading or writing data to file. Applicable only for CSV format.

field_types

Field types. This option needs to be specified after the fields parameter. Applicable only for CSV format.

Example 3. Using COPY with JSON format
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;

CREATE TABLE IF NOT EXISTS testdb.events();

COPY testdb.events FROM 'data/logs.json';

USE testdb;
SELECT count(*) FROM events;
SELECT count("EventTime") FROM events;
Example 4. Using COPY with CSV format
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;

CREATE TABLE IF NOT EXISTS testdb.test(a INT, b FLOAT, c VARCHAR, d BOOL);

COPY testdb.test FROM 'data/data.csv' WITH(FORCE_NOT_NULL {c,b}, NULL='X', DELIMITER='#', ESCAPE='$', QUOTE='!', FORMAT=CSV);
SELECT a, b, c, d FROM testdb.test;

CREATE DATABASE

Creates a new database.

Synopsis
CREATE DATABASE [ IF NOT EXISTS ] name
Description

Creates a new database with the given name. A database name must be unique unless IF NOT EXISTS is specified. In this case, if a table with the same name exists, no new database will be created.

Example 5. Using CREATE DATABASE
List of SQL commands
DROP DATABASE IF EXISTS testdb1;

CREATE DATABASE testdb1;
CREATE DATABASE IF NOT EXISTS testdb1;
USE testdb1;

DROP DATABASE testdb1;
DROP DATABASE IF EXISTS testdb1;

CREATE TABLE

Creates a new table with or without an associated schema.

Synopsis
CREATE TABLE [ IF NOT EXISTS ] [ database . ] name
    ( [ field_name field_typ ] [, ...] )
Description

Creates a new table using the name given. Unless the IF NOT EXISTS clause is specified, the created table must be non-existent.

Parameters
database

Name of the database, in which this table will be created. If not specified, the current database is used.

name

Name of the table to be created. Table names beginning with numerals must be enclosed in double quotes.

field_name, field_type, expression
field_name

Name of a field to be created in this table. Each field name must be unique within a table.

field_type

Field type. See Data types.

Example 6. Using CREATE TABLE
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;

USE testdb;
CREATE TABLE IF NOT EXISTS events_test(
    ID INT,
    Name VARCHAR(100),
    Birthday DATETIME
);

CREATE TABLE IF NOT EXISTS events_none();

DESCRIBE

Displays information about all columns in a table.

Synopsis
DESCRIBE name
Description

Displays all columns from the name table, even the ones not present in the original CREATE TABLE statement. Note that the similar SELECT FIELDS statement displays only the fields that contain non-null data.

Example 7. Using DESCRIBE name
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;

USE testdb;
CREATE TABLE tbl (
    ID int ,
    Name varchar(100) ,
    Birthday datetime
);

INSERT into tbl(A) values(1);

DESCRIBE tbl;
Result
{"field":"id","type":"INT32"}
{"field":"name","type":"STRING"}
{"field":"birthday","type":"DATETIME"}
{"field":"a","type":"INT32"}
See also

SELECT FIELDS

DROP DATABASE

Deletes a database

Synopsis
DROP DATABASE [ IF EXISTS ] name
Description

Drops the database with the given name. The database with such name must exist unless IF EXISTS is specified. In this case, no database will be dropped.

Example 8. Using DROP DATABASE
List of SQL commands
DROP DATABASE IF EXISTS testdb1;

CREATE DATABASE testdb1;
CREATE DATABASE IF NOT EXISTS testdb1;
USE testdb1;

DROP DATABASE testdb1;
DROP DATABASE IF EXISTS testdb1;

DROP TABLE

Deletes the table from the database.

Synopsis
DROP TABLE [ IF EXISTS ] [ database . ] name
Description

Drops the table in the current database with the given name. Unless IF EXISTS is specified, the table with such name must exist. If no table exists, no table will be dropped.

If database is specified, the TABLE from that database will be used. Otherwise it uses the current database.

Example 9. Using DROP TABLE
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE tbl_one ();
CREATE TABLE tbl_two ();

SHOW TABLES;
DROP TABLE tbl_one;
SHOW TABLES;
Result
{"table":"tbl_one","record_count":"0","encrypted":"no","partitioning_type":"none","size":"66 bytes"}
{"table":"tbl_two","record_count":"0","encrypted":"no","partitioning_type":"none","size":"66 bytes"}
{"table":"tbl_two","record_count":"0","encrypted":"no","partitioning_type":"none","size":"66 bytes"}

EXPLAIN

Explains operations in the given query.

Synopsis
EXPLAIN query
Description

Returns the execution plan for the given query.

Parameters
query

Query to be explained. Must be a valid query.

Example 10. Using EXPLAIN
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE events(
    Hostname VARCHAR(20),
    EventTime TIMESTAMP,
    EventType VARCHAR(10),
    Message TEXT
);

EXPLAIN SELECT Message FROM events WHERE date(EventTime + 2) = '2016-10-25';
EXPLAIN SELECT is_even(EventTime) FROM events;

EXPLAIN INSERT INTO events VALUES('Host', '2019-07-24 00:00:00', 'Log in', 'test');
Results
{"plan":"
Result
[message]
  SequentialScan on events
  . Filter: date(eventtime + INT32(2)) = STRING(2016-10-25)
  [message]"}
{"plan":"
Result
[is_even: is_even(eventtime)]
  SequentialScan on events
  [eventtime]"}
{"plan":"
Insert on 'events'"}

FLUSH TABLE

Writes tables from memory to disk.

Synopsis
FLUSH { TABLE | TABLES } [ [ database . ] name ] [, ... ]
FLUSH TABLES WITH READ LOCK
Description

Writes either all or specified tables from memory to disk. If a named table does not exist, no error occurs. The FLUSH TABLE command is a synonym for FLUSH TABLES.

WITH READ LOCK

Prevents any further write transactions on all tables in all databases. If you execute this command from a client, e.g., the Raijin user interface, the client will wait for a response to any write transaction until the tables are unlocked with UNLOCK TABLES. You can unlock the tables from another client instance, e.g., by opening the Raijin user interface in another tab.

Example 11. Using FLUSH TABLE
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE tbl1(a INTEGER);
CREATE TABLE tbl2(b INTEGER);
CREATE TABLE tbl3(c INTEGER);

INSERT INTO tbl1(a) VALUES (1);
INSERT INTO tbl2(b) VALUES (2);
FLUSH TABLES tbl1, sampledb.tbl2;

INSERT INTO tbl3(c) VALUES (3);
FLUSH TABLES;

INSERT

Inserts data into a table.

Synopsis
INSERT INTO table [ ( field [, ...] ) ]
    [ VALUES (expression [, ...] ) [, ...] |
    SET field = expression [, ...] |
    { "field": value [, ...] } [, ...] |
    query ]
    [ WITH ( option = value ) ]
Description

Inserts data into the given table. Multiple syntaxes are supported for definition of the inserted data.

In all of these syntaxes, the data type of expression/value must be compatible with the data type of the column if being defined. Otherwise, any data type is accepted.

VALUES

While using the VALUES syntax, a list of fields must be provided. Each expression corresponds to a field in the field list. The number of values in each expression list is fixed to the number of fields in the field list.

SET

While using the SET syntax, there is no need to predefine the fields used, since every expression has its own field definition. This also means that the number and order of fields may vary.

JSON

This is the same as using SET but with the following differences:

  • Evaluated expressions are not supported.

  • A constant value must be provided.

Parameters
table

Must be a name of an existing table in the current database.

field

Name of the field that is being inserted. Inserting into undefined columns is supported.

expression

The evaluated expression.

option and value

Key-value pairs of the options and their values are described below.

Options
partition

The partition name to insert data into. If it does not exist, it will be automatically created. For more information, see Table partitioning.

Example 12. Using INSERT
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE events(
    hostname VARCHAR(20),
    eventtime TIMESTAMP,
    eventtype VARCHAR(10),
    message TEXT
);

-- Using VALUES syntax
INSERT INTO events VALUES
    ('srv-04', '2016-08-03 17:08:24Z', 'INFO', 'Server initialized successfuly.');

-- Using SET syntax
INSERT INTO events SET
    hostname = 'srv-04',
    eventtime = '2016-08-03 11:37:23Z',
    eventtype = 'WARNING',
    message = 'Invalid login credentials from user "john".';

-- Using JSON syntax
INSERT INTO events {
    "hostname":"srv-04",
    "eventtime":"2015-06-24 12:20:12Z",
    "eventtype":"ERROR",
    "message": "Database timeout!"
};

SELECT hostname, FORMAT_UTC_USEC(EventTime) as EventTime, eventtype, message FROM events;
Result
{"hostname":"srv-04","eventtime":"2016-08-03T17:08:24.000000Z","eventtype":"INFO","message":"Server initialized successfuly."}
{"hostname":"srv-04","eventtime":"2016-08-03T11:37:23.000000Z","eventtype":"WARNING","message":"Invalid login credentials from user \\"john\\"."}
{"hostname":"srv-04","eventtime":"2015-06-24T12:20:12.000000Z","eventtype":"ERROR","message":"Database timeout!"}

SELECT

Retrieves records from a table.

Synopsis
[ DISTINCT [ ON ( expression [, ...] ) ] | ALL ]
[ * | expression [ AS name ] ] [, ...]
[ FROM table [ AS alias [, ...] ] ]
[ JOIN table [ ON expression | USING(expression) ] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition ]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ LIMIT expression | FETCH FIRST [ ( number ) ] [ ROW | ROWS ] ONLY ]
[ OFFSET expression [ ROW | ROWS ] ]
Description

Retrieves rows with records from zero or more tables.

Select list

Using the asterisk character (*) will expand the select list to all fields of a record in the table or tables. At least one table must be defined in the FROM clause.

All expressions in the select list will be evaluated using the data of the current record from the table or tables specified in the FROM clause (if any).

Example 13. Using SELECT with a select list
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE events(Hostname STRING, Message STRING);
INSERT INTO events {"Hostname":"localhost"};

SELECT
    "Hostname",
    Hostname,
    2+5*2 expression1,
    2/4+1 AS expression2
FROM events;
Result
{"Hostname":"localhost","hostname":null,"expression1":12,"expression2":1.5}
DISTINCT clause

With the DISTINCT clause specified, the records or columns with duplicate values will be filtered out.

If either ALL or no clause are specified, the records will be entirely checked for duplicates.

In case ON is specified, only the given columns will be checked for duplicates.

Example 14. Using SELECT with DISTINCT
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE testtbl();
INSERT INTO testtbl {"data":1};
INSERT INTO testtbl {"data":1};
INSERT INTO testtbl {"data":1};
INSERT INTO testtbl {"data":2};

SELECT DISTINCT data FROM testtbl;
Result
{"data":1}
{"data":2}
FROM clause

Specifies the table or multiple tables to retrieve data from. If multiple tables are specified, they will be cross-joined together.

WHERE clause

Condition which all retrieved records must satisfy. Records which do not satisfy the condition will be filtered out.

Example 15. Using SELECT with WHERE clause
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE events(LogLevel INTEGER, Message STRING);
INSERT INTO events (LogLevel, Message) VALUES (1, 'It''s OK');
INSERT INTO events (LogLevel, Message) VALUES (2, 'Beware');
INSERT INTO events (LogLevel, Message) VALUES (3, 'Something went wrong');

SELECT e.LogLevel, e.message FROM events e WHERE LogLevel > 1
Result
{"loglevel":2,"message":"Beware"}
{"loglevel":3,"message":"Something went wrong"}
JOIN clause

Retrieves records from multiple tables linked by a common field. Supported JOIN types are INNER JOIN, LEFT JOIN, RIGHT JOIN, and CROSS JOIN. The first three require one of the following join conditions:

  • ON to match one or more fields or arbitrary conditions. The field names can differ from one table to another.

  • USING to match a single field. The field names must be identical in both tables.

Example 16. Using SELECT with JOIN clause
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE logs(LogLevel INTEGER, Message STRING);
INSERT INTO logs (LogLevel, Message) VALUES (6, 'It''s OK');
INSERT INTO logs (LogLevel, Message) VALUES (4, 'Beware');
INSERT INTO logs (LogLevel, Message) VALUES (2, 'This is critical');

CREATE TABLE loglevels(Num INTEGER, LevelName VARCHAR(10));
INSERT INTO loglevels (Num, LevelName) VALUES (7, 'Debug');
INSERT INTO loglevels (Num, LevelName) VALUES (6, 'Info');
INSERT INTO loglevels (Num, LevelName) VALUES (4, 'Warning');
INSERT INTO loglevels (Num, LevelName) VALUES (3, 'Error');

CREATE TABLE facilities(Num INTEGER, Facility VARCHAR(10));
INSERT INTO facilities (Num, Facility) VALUES (0, 'kernel');

SELECT 'INNER JOIN' _;
SELECT logs.Message, loglevels.LevelName FROM logs INNER JOIN loglevels ON logs.LogLevel = loglevels.num;
SELECT 'LEFT JOIN' _;
SELECT logs.Message, loglevels.LevelName FROM logs LEFT JOIN loglevels ON logs.LogLevel = loglevels.num;
SELECT 'RIGHT JOIN' _;
SELECT logs.Message, loglevels.LevelName FROM logs RIGHT JOIN loglevels ON logs.LogLevel = loglevels.num;
SELECT 'CROSS JOIN' _;
SELECT facilities.num, facilities.facility, loglevels.num, loglevels.levelname FROM facilities CROSS JOIN loglevels;
Result
{"_":"INNER JOIN"}
{"logs.message":"It's OK","loglevels.levelname":"Info"}
{"logs.message":"Beware","loglevels.levelname":"Warning"}
{"_":"LEFT JOIN"}
{"logs.message":"It's OK","loglevels.levelname":"Info"}
{"logs.message":"Beware","loglevels.levelname":"Warning"}
{"logs.message":"This is critical","loglevels.levelname":null}
{"_":"RIGHT JOIN"}
{"logs.message":null,"loglevels.levelname":"Debug"}
{"logs.message":"It's OK","loglevels.levelname":"Info"}
{"logs.message":"Beware","loglevels.levelname":"Warning"}
{"logs.message":null,"loglevels.levelname":"Error"}
{"_":"CROSS JOIN"}
{"facilities.num":0,"facilities.facility":"kernel","loglevels.num":7,"loglevels.levelname":"Debug"}
{"facilities.num":0,"facilities.facility":"kernel","loglevels.num":6,"loglevels.levelname":"Info"}
{"facilities.num":0,"facilities.facility":"kernel","loglevels.num":4,"loglevels.levelname":"Warning"}
{"facilities.num":0,"facilities.facility":"kernel","loglevels.num":3,"loglevels.levelname":"Error"}
GROUP BY clause

Merges records into groups by one or more expressions. Expressions that appear in the select list must be either aggregate functions or appear in the GROUP BY clause. Expressions which do not satisfy that requirement will be implicitly wrapped into the FIRST aggregate function.

Although it is possible to use column aliases in the GROUP BY clause:

SELECT a, mod(b, 7) AS res FROM tbl GROUP BY res;

But such usage is restricted and may give unexpected results when the alias is used as part of an expression. For this reason, it is better to use select-list ordinal position:

SELECT a, mod(b, 7) FROM tbl GROUP BY 3;
Example 17. Using SELECT with the GROUP BY clause
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE events(LogLevel INTEGER, Message STRING);
INSERT INTO events VALUES (1, 'It''s OK');
INSERT INTO events VALUES (2, 'Beware');
INSERT INTO events VALUES (2, 'Warning');
INSERT INTO events VALUES (3, 'Something went wrong');

SELECT GROUP_CONCAT(e.message) messages FROM events e WHERE LogLevel > 1 GROUP
BY LogLevel;
Result
{"messages":"Beware,Warning"}
{"messages":"Something went wrong"}
HAVING clause

This clause is similar to WHERE, but applied to each group, i.e. is used to filter out the groups which do not satisfy it.

Example 18. Using SELECT with the HAVING clause
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE events(LogLevel INTEGER, Message STRING);
INSERT INTO events VALUES (1, 'It''s OK');
INSERT INTO events VALUES (2, 'Beware');
INSERT INTO events VALUES (2, 'Warning');
INSERT INTO events VALUES (2, 'It''s not good');
INSERT INTO events VALUES (3, 'Ooops');
INSERT INTO events VALUES (3, 'Something went wrong');

SELECT GROUP_CONCAT(message) messages FROM events GROUP BY LogLevel HAVING COUNT(*) > 1;
Result
{"messages":"Beware,Warning,It's not good"}
{"messages":"Ooops,Something went wrong"}
ORDER BY clause

Sorts the resulting rows by the given columns in ascending (ASC) or descending (DESC) order.

Example 19. Using SELECT with the ORDER BY clause
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE numbers(value INTEGER);

INSERT INTO numbers (value) VALUES (63);
INSERT INTO numbers (value) VALUES (100);
INSERT INTO numbers (value) VALUES (86);
INSERT INTO numbers (value) VALUES (3);

SELECT value FROM numbers ORDER BY value DESC;
Result
{"value":100}
{"value":86}
{"value":63}
{"value":3}
LIMIT clause

Limits the number of records that can be retrieved.

If ALL is given as a clause, there will be no limit on the number of records that can be retrieved.

Example 20. Using SELECT with the LIMIT clause
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE numbers(value INTEGER);

INSERT INTO numbers (value) VALUES (1);
INSERT INTO numbers (value) VALUES (2);
INSERT INTO numbers (value) VALUES (3);
INSERT INTO numbers (value) VALUES (4);
INSERT INTO numbers (value) VALUES (5);
INSERT INTO numbers (value) VALUES (6);

SELECT 'LIMIT 1:' _;
SELECT value FROM numbers LIMIT 1;
SELECT 'FETCH FIRST 2 ROWS:' _;
SELECT value FROM numbers FETCH FIRST (1+1) ROWS ONLY;
SELECT 'FETCH FIRST ROW:' _;
SELECT value FROM numbers FETCH FIRST ROW ONLY;
SELECT 'SKIPPING FIRST 4:' _;
SELECT value FROM numbers OFFSET 4;
SELECT 'MIXED LIMITING:' _;
SELECT value FROM numbers OFFSET 3 LIMIT 2;
Result
{"_":"LIMIT 1:"}
{"value":1}
{"_":"FETCH FIRST 2 ROWS:"}
{"value":1}
{"value":2}
{"_":"FETCH FIRST ROW:"}
{"value":1}
{"_":"SKIPPING FIRST 4:"}
{"value":5}
{"value":6}
{"_":"MIXED LIMITING:"}
{"value":4}
{"value":5}
FETCH FIRST number [ROW | ROWS] ONLY

This is an alternative syntax for the LIMIT clause. If the number is concluded inside parentheses, the expression will be evaluated; otherwise, it must be a constant. See the SELECT command with the LIMIT clause example.

OFFSET clause

Skips the number of records that the given expression is evaluated to. See the SELECT command with the LIMIT clause example.

SELECT FIELDS

Displays information about the columns which contain non-null data.

Synopsis
SELECT FIELDS FROM [ database . ] name [ WHERE condition ]
Description

Displays the names and types of columns that contain non-null data. It looks at either the whole table, or the selected rows only. Note that the similar DESCRIBE statement displays the names and types of all columns in a table.

Example 21. Using SELECT FIELDS
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;

USE testdb;
CREATE TABLE tbl (
    ID int ,
    Name varchar(100) ,
    Birthday datetime
);

INSERT into tbl(ID, A) values(1, 2);
SELECT 'Whole table (two columns inserted)................................1' _;
SELECT FIELDS FROM tbl;

INSERT into tbl(Name, B) values('abc', 3);
FLUSH TABLE;
INSERT into tbl(Name, B) values('d', 3.4);
SELECT 'Whole table (all columns inserted)................................2' _;
SELECT FIELDS FROM testdb.tbl;

SELECT 'Using WHERE.......................................................3' _;
SELECT FIELDS FROM tbl WHERE A = ID + 1;

SELECT 'Using WHERE (B is INT32)..........................................4' _;
SELECT FIELDS FROM testdb.tbl WHERE Name = 'abc';

SELECT 'Using WHERE (B is DOUBLE).........................................5' _;
SELECT FIELDS FROM testdb.tbl WHERE Name = 'd';

SELECT 'Using WHERE (B is VARIANT)........................................6' _;
SELECT FIELDS FROM testdb.tbl WHERE B > 2 and B < 4;
Result
{"_":"Whole table (two columns inserted)................................1"}
{"field":"id","type":"INT32"}
{"field":"a","type":"INT32"}
{"_":"Whole table (all columns inserted)................................2"}
{"field":"id","type":"INT32"}
{"field":"name","type":"STRING"}
{"field":"a","type":"INT32"}
{"field":"b","type":"VARIANT"}
{"_":"Using WHERE.......................................................3"}
{"field":"id","type":"INT32"}
{"field":"a","type":"INT32"}
{"_":"Using WHERE (B is INT32)..........................................4"}
{"field":"name","type":"STRING"}
{"field":"b","type":"INT32"}
{"_":"Using WHERE (B is DOUBLE).........................................5"}
{"field":"name","type":"STRING"}
{"field":"b","type":"DOUBLE"}
{"_":"Using WHERE (B is VARIANT)........................................6"}
{"field":"name","type":"STRING"}
{"field":"b","type":"VARIANT"}
See also

The DESCRIBE command.

SET

Sets configuration parameters.

Synopsis
SET name TO value
Description

Sets configuration parameters with the given name and value. Changes are applied only to the current session, and neither affect other users nor survive after the session ends. See the supported parameters below.

Example 22. Using SET
List of SQL commands
CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;
DROP TABLE IF EXISTS test;

CREATE TABLE IF NOT EXISTS test();
INSERT INTO test {"a":"test"};
SELECT * from test;
SET IdColumnName TO "_another_id";
SELECT * from test;
Result
{"_id":1,"a":"test"}
{"_another_id":1,"a":"test"}

SHOW

Shows configuration parameters.

Synopsis
SHOW [ name | ALL ]
Description

Shows configuration parameters and their values for all given names only for the current user session. For more details, see the supported parameters.

Example 23. Using SHOW with config
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
USE testdb;

SELECT 'Show one option:' _;
SHOW HeapTypeConversion;
SET HeapTypeConversion TO "No";

SELECT 'Show ALL:' _;
SHOW ALL;
Result
{"_":"Show one option:"}
{"Name":"HeapTypeConversion","Value":"yes","Description":"Force data type conversion for heap columns (if schema is known in advance)."}
{"_":"Show ALL:"}
{"Name":"HeapTypeConversion","Value":"no","Description":"Force data type conversion for heap columns (if schema is known in advance)."}
{"Name":"HeapTypeConversionRaiseError","Value":"no","Description":"If the given value can not be converted to the expected data type, then throw an error when true or ignore when false."}
{"Name":"JsonAutoConvertType","Value":"yes","Description":"If set to true, Json values are being converted to the primitives."}
{"Name":"TransformNullEquals","Value":"no","Description":"If set to true, equivalency checks (=, !=, <>) work on NULL values as well."}
{"Name":"IdColumnName","Value":"_id","Description":"Name of internal row id column in Raijin's output. Session parameter."}
{"Name":"FloatPrecision","Value":"6","Description":"Sets the number of digits printed after the right of decimal point for float/double values. The default value is 6.
Maximum value for DOUBLE is 17, for FLOAT - 9 digits. At server start the value is checked for DOUBLE maximum only,
but using values greater than 9 for FLOATs will abort the query execution with run-time error."}
{"Name":"PushdownPredicates","Value":"yes","Description":"For testing and debugging purposes. Enables optimization where predicates of SQL queries can be pushed down to the datasource."}
{"Name":"PushdownAggregates","Value":"yes","Description":"Evaluates aggregates at the storage layer (as with predicates pushdown)."}
{"Name":"DetectPartitionFilters","Value":"yes","Description":"Enables partitions skipping by expression predicates."}
{"Name":"OperatorAddPolicy","Value":"","Description":"Addition operator (a + b) policy.
Acceptable values: DEFAULT, QUIET, NULLING, SIGNALING"}
{"Name":"OperatorMultiplyPolicy","Value":"","Description":"Multiply operator (a * b) policy.
\nAcceptable values: DEFAULT, QUIET, NULLING, SIGNALING"}
{"Name":"OperatorSubtractPolicy","Value":"","Description":"Subtract operator (a - b) policy.
\nAcceptable values: DEFAULT, QUIET, NULLING, SIGNALING"}
{"Name":"OperatorDividePolicy","Value":"","Description":"Division operator (a / b) policy.
Acceptable values: DEFAULT, QUIET, NULLING, SIGNALING"}
{"Name":"OperatorModulusPolicy","Value":"","Description":"Modulus operator (a % b; mod(a, b)) policy.
Acceptable values: DEFAULT, QUIET, NULLING, SIGNALING"}
{"Name":"OperatorLnPolicy","Value":"","Description":"Ln operator (Ln(a); Log(a)) policy.
Acceptable values: DEFAULT, QUIET, NULLING, SIGNALING"}
{"Name":"OperatorLogPolicy","Value":"","Description":"Log operator (Log(a, b)) policy.
Acceptable values: DEFAULT, QUIET, NULLING, SIGNALING"}
{"Name":"OperatorLog10Policy","Value":"","Description":"Log10 operator (Log10(a)) policy.
Acceptable values: DEFAULT, QUIET, NULLING, SIGNALING"}
{"Name":"OperatorLog2Policy","Value":"","Description":"Log2 operator (Log2(a)) policy.
Acceptable values: DEFAULT, QUIET, NULLING, SIGNALING"}
{"Name":"OperatorSqrtPolicy","Value":"","Description":"Sqrt operator (Sqrt(a)) policy.
Acceptable values: DEFAULT, QUIET, NULLING, SIGNALING"}
{"Name":"OperatorPowPolicy","Value":"","Description":"Power operator(pow(a, b); power(a, b)) policy.
Acceptable values: DEFAULT, QUIET, NULLING, SIGNALING"}
{"Name":"OrderbyWithLimitOptimization","Value":"yes","Description":"Enables/disables optimization of queries with ORDER BY..LIMIT statements, statements like this:
SELECT * FROM t1 ORDER BY <field list> LIMIT n [OFFSET m]
The default is TRUE.
While default algorithm performs sorting of the whole dataset first, involving using on-disc cache for data parts,
an optimized algorithm is doing the in-memory operations which involve usage of memory enough for storing LIMIT+OFFSET
rows only (no on-disc cache). This allows SIGNIFICALLY improve the speed of algorithm, even in comparance to default
algorithm in cases when no disc cache used."}
{"Name":"CrashTestConnectionId","Value":"1","Description":"For testing purposes only"}
{"Name":"ExpandCommonColumnsWithPrefix","Value":"no","Description":"Enables prefixes for common columns."}
{"Name":"CalculateNullSelectivity","Value":"yes","Description":"Enables selectivity stat estimation of NullTest Nodes"}

SHOW DATABASES

Returns a list of databases.

Synopsis
SHOW DATABASES
Description

Returns a list of databases that exist on the server.

Example 24. Using SHOW DATABASES
List of SQL commands
DROP DATABASE IF EXISTS serverlogs;
DROP DATABASE IF EXISTS userlogs;
CREATE DATABASE serverlogs;
CREATE DATABASE userlogs;

SHOW DATABASES;
Result
{"database":"serverlogs"}
{"database":"userlogs"}

SHOW PARTITIONS

Returns a list of table partions.

Synopsis
SHOW PARTITIONS [ IN | FROM ] table
Description

Returns a list of partitions in the given table. It includes the partition name, state (attached or detached), row count, and the partition data path on the disk. The path is relative to the Raijin root data folder.

Example 25. Using SHOW PARTITIONS
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
USE testdb;

CREATE TABLE tp1(a INTEGER, b INTEGER);

SELECT 'Empty table.......................................................1' _;
SHOW PARTITIONS FROM tp1;
SHOW PARTITIONS IN tp1;

INSERT INTO tp1 VALUES (1, 0), (2, -1); -- root partition
INSERT INTO tp1 VALUES (3, -2), (4, -3) WITH (partition = 'p1');
INSERT INTO tp1 VALUES (5, -4), (6, -5) WITH (partition = 'p2');
INSERT INTO tp1 VALUES (7, -6); -- root partition

SELECT 'All attached [FROM]...............................................2' _;
SHOW PARTITIONS FROM tp1;
SELECT 'All attached [IN].................................................3' _;
SHOW PARTITIONS IN tp1;
SELECT 'One attached one detached.........................................4' _;
ALTER TABLE tp1 DETACH PARTITION p1;
SHOW PARTITIONS FROM tp1;
SELECT 'All detached......................................................5' _;
ALTER TABLE tp1 DETACH PARTITION p2;
SHOW PARTITIONS IN tp1;
SELECT 'All dropped.......................................................6' _;
ALTER TABLE tp1 DROP PARTITION p1;
ALTER TABLE tp1 DROP PARTITION p2;
SHOW PARTITIONS IN tp1;
Result
{"_":"Empty table.......................................................1"}
{"_":"All attached [FROM]...............................................2"}
{"name":"<root>","state":"attached","rows":3,"path":"testdb/tp1/_root"}
{"name":"p1","state":"attached","rows":2,"path":"testdb/tp1/c985b5170824680e8b779935b16ce90f"}
{"name":"p2","state":"attached","rows":2,"path":"testdb/tp1/aaeae4b66f076c5d858a878fadd236e3"}
{"_":"All attached [IN].................................................3"}
{"name":"<root>","state":"attached","rows":3,"path":"testdb/tp1/_root"}
{"name":"p1","state":"attached","rows":2,"path":"testdb/tp1/c985b5170824680e8b779935b16ce90f"}
{"name":"p2","state":"attached","rows":2,"path":"testdb/tp1/aaeae4b66f076c5d858a878fadd236e3"}
{"_":"One attached one detached.........................................4"}
{"name":"<root>","state":"attached","rows":3,"path":"testdb/tp1/_root"}
{"name":"p2","state":"attached","rows":2,"path":"testdb/tp1/c985b5170824680e8b779935b16ce90f"}
{"name":"p1","state":"detached","rows":2,"path":"testdb/tp1/aaeae4b66f076c5d858a878fadd236e3"}
{"_":"All detached......................................................5"}
{"name":"<root>","state":"attached","rows":3,"path":"testdb/tp1/_root"}
{"name":"p1","state":"detached","rows":2,"path":"testdb/tp1/c985b5170824680e8b779935b16ce90f"}
{"name":"p2","state":"detached","rows":2,"path":"testdb/tp1/aaeae4b66f076c5d858a878fadd236e3"}
{"_":"All dropped.......................................................6"}
{"name":"<root>","state":"attached","rows":3,"path":"testdb/tp1/_root"}

SHOW TABLES

Returns a list of tables in the database.

Synopsis
SHOW TABLES
Description

Returns a list of tables in the current database.

Example 26. Using SHOW TABLES
List of SQL commands
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE eventlogs();
CREATE TABLE eventlogs_partitioned() partition by DATE(event_date);
CREATE TABLE eventlogs_partitioned_byname();

CREATE ENCRYPTED TABLE userlogs();
CREATE ENCRYPTED TABLE userlogs_partitioned() partition by DATE(log_date);
CREATE ENCRYPTED TABLE userlogs_partitioned_byname();

INSERT INTO eventlogs_partitioned(event, event_date) VALUES
 ('event1', '2020-07-11 21:46:08'),
 ('event1', '2020-07-12 22:46:08'),
 ('event2', null);

INSERT INTO eventlogs_partitioned_byname (event, event_date) VALUES('logon', '2020-07-11 21:46:08') WITH (partition = 'p1');
INSERT INTO userlogs_partitioned_byname (user, log_date) VALUES('root', '2020-07-11 21:46:08') WITH (partition = 'p_root');
FLUSH TABLES;

SHOW TABLES;

ALTER TABLE eventlogs_partitioned DETACH PARTITION "2020-07-12";

SHOW TABLES;
Result
{"table":"eventlogs","record_count":"0","encrypted":"no","partitioning_type":"none","size":"66 bytes"}
{"table":"eventlogs_partitioned","record_count":"3","encrypted":"no","partitioning_type":"expression","partitioning_expression":"DATE_FORMAT_LOCAL(event_date, <STRING>'%Y-%m-%d')","size":"2.65 KB"}
{"table":"eventlogs_partitioned_byname","record_count":"1","encrypted":"no","partitioning_type":"name","size":"1.06 KB"}
{"table":"userlogs","record_count":"0","encrypted":"yes","partitioning_type":"none","size":"98 bytes"}
{"table":"userlogs_partitioned","record_count":"0","encrypted":"yes","partitioning_type":"expression","partitioning_expression":"DATE_FORMAT_LOCAL(log_date, <STRING>'%Y-%m-%d')","size":"172 bytes"}
{"table":"userlogs_partitioned_byname","record_count":"1","encrypted":"yes","partitioning_type":"name","size":"1.22 KB"}
{"table":"eventlogs","record_count":"0","encrypted":"no","partitioning_type":"none","size":"66 bytes"}
{"table":"eventlogs_partitioned","record_count":"2","encrypted":"no","partitioning_type":"expression","partitioning_expression":"DATE_FORMAT_LOCAL(event_date, <STRING>'%Y-%m-%d')","size":"1.66 KB"}
{"table":"eventlogs_partitioned_byname","record_count":"1","encrypted":"no","partitioning_type":"name","size":"1.06 KB"}
{"table":"userlogs","record_count":"0","encrypted":"yes","partitioning_type":"none","size":"98 bytes"}
{"table":"userlogs_partitioned","record_count":"0","encrypted":"yes","partitioning_type":"expression","partitioning_expression":"DATE_FORMAT_LOCAL(log_date, <STRING>'%Y-%m-%d')","size":"172 bytes"}
{"table":"userlogs_partitioned_byname","record_count":"1","encrypted":"yes","partitioning_type":"name","size":"1.22 KB"}

UNLOCK TABLES

Synopsis
UNLOCK TABLES
Description

Releases the lock created with FLUSH TABLES WITH READ LOCK.

Example 27. Using UNLOCK TABLES
List of SQL commands
UNLOCK TABLES;

USE

Sets the current database.

Synopsis
USE name
Description

Before any data operation can be executed, you need to select an existing database with the USE command.

Parameters
name

Name of the existing database for using.

Example 28. Using USE
List of SQL commands
DROP DATABASE IF EXISTS "testdb1";
DROP DATABASE IF EXISTS "testdb2";
CREATE DATABASE "testdb1";
CREATE DATABASE "testdb2";

USE "testdb1";
CREATE TABLE tbl(value int);
INSERT INTO tbl(a) VALUES (1);

USE "testdb2";
CREATE TABLE tbl(value int);
INSERT INTO tbl(a) VALUES (2);

USE "testdb1";
SELECT a FROM tbl;

USE "testdb2";
SELECT a FROM tbl;

DROP DATABASE "testdb1";
DROP DATABASE "testdb2";
Result
{"a":1}
{"a":2}