SQL Commands List

ADD AGGREGATOR

Adds a new child aggregator to a cluster.

Cluster Management

ADD LEAF

Add a leaf node to a cluster.

Cluster Management

AGGREGATOR SET AS MASTER

Promote a child aggregator to master aggregator.

Cluster Management

AGGREGATOR SYNC AUTO_INCREMENT

Sets the auto-increment counter on the aggregators (master or child).

Data Manipulation Language

ALTER DATABASE

Specify if an existing database should begin using synchronous or asynchronous replication.

Data Definition Language

ALTER NODE

The ALTER NODE command can be used to set:

• An external hostname or IP address with which to address a node.

• An external port with which to connect to a node.

Cluster Management

ALTER PIPELINE

The ALTER PIPELINE clause changes an existing pipeline’s configuration.

Pipeline

ALTER RESOURCE POOL

Changes the resource settings in an existing resource pool.

Resource Pool

ALTER TABLE

Modify the structure of an existing table.

Data Definition Language

ALTER USER

Alters settings associated with the user. To change a user password, use the SET PASSWORD command.

Security Management

ALTER VIEW

Atomically replace a view with a new view defined by a SELECT statement. ALTER VIEW is an online operation and will not cause concurrently executing queries to fail.

Data Definition Language

ANALYZE

Statistics for a table can be collected and updated on an ad hoc basis by running the ANALYZE command:

Operational

ARRAY

SingleStore Procedural SQL (PSQL) supports the ARRAY data type, which is a collection of elements that share the same data type.

Procedural

ATTACH DATABASE

Restores an unlimited storage database at a restore point and then brings the database online.

Operational

ATTACH LEAF

Attaches a detached leaf into the cluster, re-introducing its data (if possible).

Cluster Management

ATTACH LEAF ALL

Attaches all leaves in a cluster that are currently in the detached state.

Cluster Management

BACKUP DATABASE

Backs up a database. The backup files are saved on the master aggregator and on the leaves.

Operational

BEGIN

Commits any existing open transaction on the current connection and starts a new transaction.

Data Manipulation Language

BOOTSTRAP AGGREGATOR

Promotes a node to Master Aggregator.

Cluster Management

CALL

Executes the specified stored procedure. Does not return results.

Procedural

CHECK BLOB CHECKSUM

CHECK BLOB CHECKSUM checks for and displays columnstore blob file corruptions. Applies to the blobs in local disk and local blob cache.

Operational

CHECK BOTTOMLESS CHECKSUM

CHECK BOTTOMLESS CHECKSUM checks the checksum of the blob files stored in the remote blob store.

Operational

CLEAR LOAD ERRORS

Removes load errors from the information_schema.LOAD_DATA_ERRORS table. These errors are populated by LOAD DATA ... ERRORS HANDLE <string> when it runs.

Data Manipulation Language

CLEAR ORPHAN DATABASES

Removes orphaned partitions. Orphaned partitions are partition databases and tables on leaf nodes that are no longer considered part of the cluster.

Operational

COLLECT

After COLLECT executes you will typically iterate through the array to perform processing on its elements.

Procedural

COMMIT

Commits the open transaction if one exists.

Data Manipulation Language

CONTINUE REPLICATING

Resume replicating the database after pausing via PAUSE REPLICATING.

Operational

CREATE [OR REPLACE] EXTERNAL FUNCTION

Creates or replaces an external function, which is a function that calls code that is executed outside of a SingleStore database.

Procedural

CREATE [OR REPLACE] TEMPORARY PROCEDURE …

When the session terminates, the temporary stored procedure is no longer available.

Procedural

CREATE AGGREGATE

Creates a user-defined aggregate function (UDAF). A UDAF is a callable routine that accepts input parameters, executes programmatic logic in the function body, and returns a scalar-type value.

Procedural

CREATE DATABASE

Creates a database.

Data Definition Language

CREATE FUNCTION (TVF)

Creates a user-defined table-valued function (TVF). A TVF is a callable routine that accepts input parameters, executes a single SELECT statement in the function body, and returns a single table-type value (similar to a view).

Procedural

CREATE FUNCTION (UDF)

Creates a user-defined scalar-valued function (UDF). A UDF is a callable routine that accepts input parameters, executes programmatic logic, and returns a value (or values).

Procedural

CREATE GROUP

Create a group on a SingleStore cluster.

Security Management

CREATE INDEX

Create an index on the specified table.

Data Definition Language

CREATE LINK

Create a new connection link to S3, Azure, GCS, HDFS, HTTP, or Kafka for a permitted user.

Security Management

CREATE MILESTONE

Records a milestone, which is a named point to which you can restore an unlimited storage database. Restoring to the milestone will bring the database to the point in time when the milestone was created.

Data Definition Language

CREATE PIPELINE

Create a new Pipeline to continuously extract, shape, and load data into a table or stored procedure.

Pipeline

CREATE PROCEDURE

Creates a stored procedure. A stored procedure is a callable routine that accepts input parameters, executes programmatic logic, and optionally returns a single value.

Procedural

CREATE RESOURCE POOL

Creates a resource pool.

Resource Pool

CREATE ROLE

Create a role on a SingleStore cluster.

Security Management

CREATE TABLE

Creates a new table.

Data Definition Language

CREATE USER

Create a new user account.

Security Management

CREATE VIEW

Creates an unmaterialized view.

Data Definition Language

CREATE_ARRAY

Creates a new array of the specified length that is initialized with default values for the array’s inferred data type.

Procedural

CURRENT_SECURITY_GROUPS

Returns a comma separated list of the current user's security groups on a SingleStore cluster.

Security Management

CURRENT_SECURITY_ROLES

Returns a comma separated list in one row of the current user's security roles on a SingleStore cluster.

Security Management

CURRENT_USER

Returns the details (username and hostname) of the user executing the query.

Security Management

DELETE

DELETE is a DML statement that deletes rows in a table.

Data Manipulation Language

DESCRIBE

Describe the specified table.

Data Definition Language

DETACH DATABASE

Takes an unlimited storage database offline. The data remains on remote storage and should be deleted separately if you wish to completely remove it.

Operational

DETACH LEAF

Detaches a leaf.

Cluster Management

DETACH PIPELINE

The DETACH PIPELINE clause is similar to the STOP PIPELINE clause with the difference being ALTER TABLE can be performed on a detached pipeline for the table that is currently being referenced by the pipeline.

Pipeline

DROP … FROM PLANCACHE

Forces a plan to be optimized and generated from scratch the next time it is run.

Data Definition Language

DROP AGGREGATE

Removes a single user-defined aggregate function (UDAF) from the specified database.

Procedural

DROP DATABASE

Drops a local storage database.

Data Definition Language

DROP FUNCTION

Removes a single function from the specified database, including user-defined scalar-valued functions (UDFs) and user-defined table-valued functions (TVFs).

Procedural

DROP GROUP

Remove a group from a SingleStore cluster.

Security Management

DROP INDEX

Drop the specified index on the specified table.

Data Definition Language

DROP LINK

Remove a connection link on S3, Azure, GCS, HDFS, or Kafka for a permitted user.

Security Management

DROP MILESTONE

This command deletes a milestone of a currently attached database.

Data Definition Language

DROP PIPELINE

The DROP PIPELINE clause deletes a pipeline from your SingleStore cluster. If the pipeline is currently running, it will stop it before deleting it.

Pipeline

DROP PROCEDURE

Removes a single stored procedure from the specified database.

Procedural

DROP RESOURCE POOL

Drop the specified resource pool.

Resource Pool

DROP RESULT TABLE

Drops the specified result table.

Data Definition Language

DROP ROLE

Remove a role from a SingleStore cluster.

Security Management

DROP TABLE

Drop the specified table.

Data Definition Language

DROP USER

Removes one or more user accounts.

Security Management

DROP VIEW

Drop a view.

Data Definition Language

ECHO

Executes a stored procedure and returns a set of rows as a result.

Procedural

ECHO SELECT

Within a stored procedure, executes the specified SELECT statement and returns a set of rows to the console or to the application that called the stored procedure.

Procedural

EXCEPT and MINUS

EXCEPT and MINUS are synonymous operators which can be used to compare the results of two queries and return the distinct rows from the first query that are not output by the second.

Data Manipulation Language

EXECUTE IMMEDIATE

Runs a SQL query.

Procedural

EXISTS AND NOT EXISTS

Used with a subquery to check if the subquery returns a record.

Data Manipulation Language

EXPLAIN

Returns detailed information on how the query is executed.

Data Manipulation Language

EXPLAIN REBALANCE ALL DATABASES

Returns the set of steps that REBALANCE ALL DATABASES would run if you executed it now.

Cluster Management

EXPLAIN REBALANCE PARTITIONS

Returns the set of steps that REBALANCE PARTITIONS would run if you executed it now.

Cluster Management

EXPLAIN RESTORE REDUNDANCY

Returns the set of steps RESTORE REDUNDANCY would run if executed.

Cluster Management

FILL and FLUSH CONNECTION POOLS

Used to open and close (reset) connections to leaf nodes.

Cluster Management

FLUSH HOSTS

Flushes the forward and reverse caches for DNS/IP lookup.

Cluster Management

GRANT

Grants the specified privileges to an existing user. The user will be created if it does not exist.

Security Management

GRANT GROUP

Add a user to a group.

Security Management

GRANT ROLE

Add a role to a group.

Security Management

INSERT

Inserts data into a table.

Data Manipulation Language

INSERT_ALL

Inserts an array of records into a table in a single operation.

Procedural

INTERSECT

INTERSECT combines the result set of two queries and returns distinct rows that are returned by both queries.

Data Manipulation Language

KILL CONNECTION and KILL QUERY

Kill the connection on the specified thread. Rolls back any query running on the thread.

Operational

KILLALL QUERIES

Kills all running queries or closes all connections.

Operational

LOAD DATA

Import data stored in a CSV, JSON, or Avro file into a SingleStore table (referred to as the destination table in this topic).

Data Manipulation Language

OPTIMIZE TABLE

Performs optimizations on the storage of rows in a table.

Data Definition Language

PAUSE REPLICATING

Pause replicating a database.

Operational

PIVOT

The PIVOT operation lets you transform non-aggregated data into a pivot table output format.

Data Manipulation Language

PROFILE

Provides detailed resources usage metrics about a query.

Data Manipulation Language

PROFILE PIPELINE

Runs a single batch in the foreground to provide detailed resource metrics for each operation in your pipeline. The batch itself is not persisted.

Pipeline

PROMOTE AGGREGATOR … TO MASTER

This command promotes a child aggregator node to master, while demoting the current master to child.

Cluster Management

QUERY

SingleStore Procedural SQL (PSQL) supports the QUERY data type, whose value represents a SQL SELECT statement, optionally with bound variable values.

Procedural

REBALANCE ALL DATABASES

Rebalances the partitions on all databases in the cluster.

Cluster Management

REBALANCE PARTITIONS

Rebalances the partitions for a database.

Cluster Management

RECORD

SingleStore Procedural SQL (PSQL) supports the RECORD data type, which is an encapsulated group of named fields.

Procedural

REMOVE AGGREGATOR

Removes a child aggregator from a cluster.

Cluster Management

REMOVE LEAF

Removes a leaf node from a cluster without deleting its data.

Cluster Management

REPAIR DATABASE

This command is deprecated.

Operational

REPLACE

If the table has a PRIMARY KEY or UNIQUE index, REPLACE will check rows for a matching value and, if a match is found, will delete the old row and replace it with the new row.

Data Manipulation Language

REPLICATE DATABASE

Starts or continues replicating a database from a remote host to the local host.

Operational

RESTORE DATABASE

Restores a database from a binary backup file.

Operational

RESTORE REDUNDANCY

Restore redundancy by replicating any partitions with only one instance.

Cluster Management

REVOKE

Revoke privileges from a SingleStore user or role.

Security Management

REVOKE GROUP

Remove a user from a group.

Security Management

REVOKE ROLE

Remove a role from a group.

Security Management

ROLLBACK

Rolls back the open transaction if one exists.

Data Manipulation Language

ROW

The ROW() function constructs a RECORD object that may be used in an expression or assigned into a RECORD type variable.

Procedural

SCALAR

The SCALAR function executes a query that returns a single value.

Procedural

SECURITY_LISTS_INTERSECT

SECURITY_LISTS_INTERSECT() is a function that returns a list of a assigned roles for a user.

Security Management

SELECT

Retrieves data from a table.

Data Manipulation Language

SELECT GLOBAL

Inspect the setting of a global engine variable.

Operational

SET and SET SESSION

Sets session variables. The variable’s value is effective for the user’s current connection to the node, until the node is restarted.

Operational

SET AGGREGATOR

Sets a sync variable to propagate to all aggregators in the cluster.

Operational

SET CLUSTER

Sets a sync variable. Depending on the variable, the updated value is propagated to all nodes in the cluster or all aggregators in the cluster.

Operational

SET GLOBAL

Sets an engine variable globally.

Operational

SET LEAF

Sets a sync variable to propagate to all leaves in the cluster.

Operational

SET PASSWORD

Sets a user’s password.

Security Management

SHOW AGGREGATES

Lists any existing user-defined aggregate functions (UDAF) in the specified database.

Procedural

SHOW AGGREGATORS

Show the aggregators in a cluster.

Cluster Management

SHOW CLUSTER STATUS

Shows information about partitions within a cluster including replication role, replication state, and replication position, across all databases.

Cluster Management

SHOW COLUMNS

Show the list of columns within a given table.

Show Commands

SHOW CREATE AGGREGATE

Shows the function definition and other attributes for a user-defined aggregate function (UDAF).

Procedural

SHOW CREATE FUNCTION

Outputs configuration information about an existing user-defined function, including user-defined scalar value functions (UDFs) and user-defined table-valued functions (TVFs).

Procedural

SHOW CREATE PIPELINE

You can use the SHOW CREATE PIPELINE clause to output a CREATE PIPELINE statement that can be used to duplicate the configuration and state of an existing pipeline.

Show Commands

SHOW CREATE TABLE

Shows the CREATE TABLE statement that was used to create the table.

Show Commands

SHOW CREATE VIEW

Shows the CREATE VIEW statement that was used to create the view.

Show Commands

SHOW DATABASE STATUS

Shows SingleStore database status.

Show Commands

SHOW DATABASES

Shows the list of databases that exist on this SingleStore instance.

Show Commands

SHOW ERRORS

Displays errors as a result of execution of an invalid statement.

Show Commands

SHOW FUNCTIONS

Lists any existing user-defined functions in the specified database, including user-defined scalar-valued functions (UDFs) and user-defined table-valued functions (TVFs).

Procedural

SHOW GLOBAL STATUS

Show global SingleStore server status information.

Show Commands

SHOW GRANTS

Show permissions (privileges) for the specified user (or role).

Security Management

SHOW GROUPS

Shows the list of groups on a cluster/workspace group.

Security Management

SHOW GROUPS FOR ROLE

Show groups for a specific role.

Security Management

SHOW GROUPS FOR USER

Show groups for a specific user.

Security Management

SHOW INDEX, SHOW INDEXES, SHOW KEYS

Displays the list of indexes associated with a given table.

Show Commands

SHOW LEAF STATUS

Shows the leaf status.

Show Commands

SHOW LEAVES

Shows the leaves in a cluster.

Cluster Management

SHOW LINKS

Show all connection links on S3, Azure, GCS, HDFS, or Kafka for a permitted user.

Security Management

SHOW LOAD ERRORS

This command is deprecated. You can now query the information_schema.LOAD_DATA_ERRORS table to see the errors that occurred after running the LOAD DATA command.

Show Commands

SHOW LOAD WARNINGS

This command is deprecated. You can now query the information_schema.LOAD_DATA_ERRORS table to see the warnings that occurred after running the LOAD DATA command.

Show Commands

SHOW PARTITIONS

Shows partitions and relevant information about them.

Show Commands

SHOW PIPELINES

You can see all existing pipelines in a database and their current state by using the SHOW PIPELINES statement.

Show Commands

SHOW PLAN

Displays the EXPLAIN plan of a query as per the plan ID.

Show Commands

SHOW PLANCACHE

Shows all query statements that SingleStore has compiled and executed, as well as cumulative query execution statistics associated with each plan.

Show Commands

SHOW PROCESSLIST

Shows details about currently running threads.

Show Commands

SHOW REBALANCE ALL DATABASES STATUS

Shows the status of the currently executing REBALANCE ALL DATABASES operation.

Cluster Management

SHOW REBALANCE STATUS

Shows the status of the currently executing REBALANCE PARTITIONS, RESTORE REDUNDANCY, RESTORE, ATTACH LEAF, auto-failover or auto-attach operation on a database.

Cluster Management

SHOW REPLICATION STATUS

Shows the list of all connections between the replication master and secondary databases.

Operational

SHOW REPRODUCTION

Collects troubleshooting data for query optimizer and code generation issues for queries that fail in the compilation phase.

Show Commands

SHOW RESOURCE POOLS

Lists all resource pools and their field values.

Resource Pool

SHOW ROLES

Shows the list of roles on a cluster/workspace group.

Security Management

SHOW ROLES FOR GROUP

Show roles for a specific group.

Security Management

SHOW ROLES FOR USER

Show roles for a specific user.

Security Management

SHOW SCHEMAS

Shows the list of schemas that exist on this SingleStore instance.

Show Commands

SHOW SESSION STATUS

Show SingleStore server status information for a session.

Show Commands

SHOW STATUS EXTENDED

Shows SingleStore server status information with detailed SingleStore-specific memory and performance metrics.

Show Commands

SHOW TABLE STATUS

Show tables status information in a SingleStore database.

Show Commands

SHOW TABLES

Shows the list of tables in the currently selected database, or in another database if db_name is specified.

Show Commands

SHOW USERS

Show users on a SingleStore cluster.

Security Management

SHOW USERS FOR GROUP

Show users for a specific group.

Security Management

SHOW USERS FOR ROLE

Show users for a specific role.

Security Management

SHOW VARIABLES

Shows a list of variable bindings.

Show Commands

SHOW WARNINGS

Display warnings as a result of an invalid statement execution.

Show Commands

SNAPSHOT DATABASE

Takes a snapshot of the given database and truncates the transaction log.

Operational

START PIPELINE

The START PIPELINE statement starts an existing pipeline in a SingleStore database. An error will occur if the pipeline is already running.

Pipeline

STOP PIPELINE

The STOP PIPELINE statement stops a currently running pipeline in a SingleStore database.

Pipeline

STOP REPLICATING

Stops replicating a database, and promotes the secondary database to the primary database.

Operational

SYNC BOTTOMLESS DATABASE

Synchronizes any currently committed data in the database with the remote object storage.

Operational

TABLE

It is a table-valued function that converts a PSQL array to a set of rows.

Data Manipulation Language

TEST PIPELINE

The TEST PIPELINE statement tests an existing pipeline and looks for any new files to import.

Pipeline

TO_QUERY

Allows you to convert a dynamic SQL statement to a query type value.

Procedural

TRIGGER GC

Triggers the garbage collector on the source aggregator and propagates to all the leaves.

Cluster Management

TRUNCATE

Removes all rows from the table.

Data Definition Language

UNION

Combines results from multiple SELECT statements.

Data Manipulation Language

UNLOCK TABLES

Releases all table locks on all databases in the cluster.

Data Definition Language

UPDATE

UPDATE is a DML statement that modifies rows in a table.

Data Manipulation Language

USE

Use a database or schema.

Data Manipulation Language

WITH (Common Table Expressions)

Specifies a temporary named result set, referred to as a Common Table Expression (CTE).

Data Manipulation Language