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
Description

ALTER TABLE changes the definition of an existing table. Currently, only the table renaming feature is supported.

RENAME

Changes the name of an existing table. There is no effect on stored data.

The new_name parameter must be a valid name and a table with the same name must not exist.

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 | STDIN | STDOUT ]
    [ WITH ( option = value ) ]
Description

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

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

To parse CSV data, 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.

After successful completion, COPY returns a 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 a valid name of an existing table.

file

Name of the file for reading or writing. User must have read and write permissions to the file.

option and value

Key-value pairs of the options and their values 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.

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. While copying to 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 INDEX

Creates a new index on a table.

Synopsis
CREATE INDEX [ IF NOT EXISTS ] ON [ database . ] table ( field )
    [ CONCURRENTLY ] [ WITH ( option = value ) ]
Description

Creates index on the given field. Unless the IF NOT EXISTS clause is specified, the created index must be non-existent on a table.

If CONCURRENTLY is specified, index creation will continue in the background. Until index creation is finished, other queries cannot use it.

Parameters
database

Database name to create the index in. If not specified, the current database is used.

table

Table name to create the index in. Must be a valid name of an existing table.

field

Field name to create the index on. Raijin allows creating indexes on non-existent fields, so a field does not have to exist.

option and value

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

Options
type

Storage data type of the index. Must be a name of a valid data type in Raijin.

CREATE TABLE

Creates a new table with or without an associated schema.

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

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

Parameters
database

Database name to create the table in. If not specified, the current database is used.

name

Name for the table to be created.

field_name, field_type, expression
field_name

Field name to be created in the table. Every field must have a unique name.

field_type

Field type. See the Data types section.

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 6. 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 7. 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 INDEX

Deletes the index on a table

This feature is currently not supported.

Synopsis
DROP INDEX [ IF EXISTS ] ON [ database . ] table ( field )
Description

Drops the index on the given field of the given table. The specified index must exist unless IF EXISTS is specified. In this case, no index will be dropped.

If database is specified, the table from that database will be used. Otherwise, it will use the current database.

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 8. 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 9. 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
);
CREATE INDEX ON events (Hostname);

EXPLAIN SELECT Hostname FROM events;
EXPLAIN SELECT 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
-> IndexScan on hostname
-------------------------------------------

FLUSH TABLE

Writes tables from memory to disk.

Synopsis
FLUSH { TABLE | TABLES } [ [ database . ] name ] [, ... ]
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.

Example 10. 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 the given data to a table.

Synopsis
INSERT INTO table [ ( field [, ...] ) ]
    [ DEFAULT VALUES |
    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, as 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, 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 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 the Partitions section.

Example 11. 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 [, ...] ]
[ 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 12. 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 13. 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 14. 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"}
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 15. 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 16. 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 17. 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 18. 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 19. 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 20. 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 21. Using SHOW with config
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
USE testdb;

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

SELECT 'Show ALL:' _;
SHOW ALL;
Result
{"_":"Show one option:"}
{"Name":"IndexTypeConversion","Value":"yes","Description":"Force data type conversion for index columns (if schema is known in advance)."}
{"_":"Show ALL:"}
{"Name":"IndexTypeConversion","Value":"no","Description":"Force data type conversion for index columns (if schema is known in advance)."}
{"Name":"IndexTypeConversionRaiseError","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":"HeapTypeConversion","Value":"yes","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":"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":"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 22. 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 INDEX

Returns a list of all indexes in a table.

Synopsis
SHOW [INDEX | INDEXES] [ IN | FROM ] [ database . ] table
Description

Returns a list of indexes in a table. The table parameter must specify a valid table name.

In case the database parameter is specified, table from the database will be used. Otherwise, it uses the current database.

The feature is currently not supported.

SHOW PARTITIONS

Returns a list of partitions in the table.

Synopsis
SHOW PARTITIONS [ IN | FROM ] table
Description

Returns a list of partitions in the given table. The first column is a partition name, the second is its state (attached/detached).

Example 23. 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":"p1","state":"attached"}
{"name":"p2","state":"attached"}
{"_":"All attached [IN].................................................3"}
{"name":"p1","state":"attached"}
{"name":"p2","state":"attached"}
{"_":"One attached one detached.........................................4"}
{"name":"p2","state":"attached"}
{"name":"p1","state":"detached"}
{"_":"All detached......................................................5"}
{"name":"p1","state":"detached"}
{"name":"p2","state":"detached"}
{"_":"All dropped.......................................................6"}

SHOW TABLES

Returns a list of tables in the database.

Synopsis
SHOW TABLES
Description

Returns a list of tables in the current database.

Example 24. 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"}

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 25. 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(nonindexed int);
INSERT INTO tbl(a) VALUES (1);

USE "testdb2";
CREATE TABLE tbl(nonindexed 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}