Important

Helios features are now enabled during weekly update windows and are no longer directly tied to SingleStore engine releases. Refer to the release notes to view the latest features available in your Helios cluster.

SQL Commands List

Here is an alphabetical list of all the SQL commands SingleStore Helios currently supports. This list is subject to change.

Command Name	Command Description	Grouping
AGGREGATOR SYNC AUTO_INCREMENT	Sets the auto-increment counter on the aggregators (master or child).	Data Manipulation Language
ALTER DATABASE	Specifies if an existing database should begin using synchronous or asynchronous replication.	Data Definition Language
ALTER PIPELINE	Changes an existing pipeline’s configuration.	Pipeline
ALTER RESOURCE POOL	Changes the resource settings in an existing resource pool.	Resource Pool
ALTER TABLE	Modifies the structure of an existing table.	Data Definition Language
ALTER USER	Alters settings associated with the user.	Security Management
ALTER VIEW	Atomically replaces a view with a new view defined by a `SELECT` statement.	Data Definition Language
ANALYZE	Collects statistics for a table.	Operational
ARRAY	Defines an `ARRAY` data type in SingleStore Helios Procedural SQL (PSQL) that stores a collection of elements sharing the same data type.	Procedural
ATTACH DATABASE	Restores an unlimited storage database at a restore point and then brings the database online.	Operational
BACKUP DATABASE	Backs up a database.	Operational
BEGIN	Commits any existing open transaction on the current connection and starts a new transaction.	Data Manipulation Language
CALL	Runs the specified stored procedure. Does not return results.	Procedural
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	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
COLLECT	Runs a `SELECT` statement having the QUERY type and stores the result as an array of records.	Procedural
COMMIT	Commits the open transaction if one exists.	Data Manipulation Language
CREATE EXTERNAL FUNCTION	Creates an external function, which is a function that calls code that runs outside of a SingleStore database.	Procedural
CREATE TEMPORARY PROCEDURE	Creates a temporary stored procedure, which is a stored procedure that is available during a user's session.	Procedural
CREATE AGGREGATE	Creates a user-defined aggregate function (UDAF).	Procedural
CREATE DATABASE	Creates a database.	Data Definition Language
Trace Event Commands	Creates a trace event.	Trace Events
CREATE FUNCTION (TVF)	Creates a user-defined table-valued function (TVF).	Procedural
CREATE FUNCTION (UDF)	Creates a user-defined scalar-valued function (UDF).	Procedural
CREATE GROUP	Creates a group on a SingleStore Helios cluster.	Security Management
CREATE INDEX	Creates an index on the specified table.	Data Definition Language
CREATE LINK	Creates a new connection link to S3, Azure, GCS, HDFS, HTTP, Kafka, MongoDB®, or MySQL 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	Creates a new Pipeline to continuously extract, shape, and load data into a table, or to shape and load a set of files fast into a table, in parallel.	Pipeline
CREATE PROCEDURE	Creates a stored procedure.	Procedural
CREATE PROJECTION	Creates a projection.	Data Definition Language
CREATE RESOURCE POOL	Creates a resource pool.	Resource Pool
CREATE ROLE	Creates a role on a SingleStore Helios cluster.	Security Management
CREATE TABLE	Creates a new table.	Data Definition Language
CREATE USER	Creates 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	Deletes rows in a table.	Data Manipulation Language
DESCRIBE	Describes the specified table.	Data Definition Language
DETACH DATABASE	Takes an unlimited storage database offline.	Operational
DETACH PIPELINE	Detaches a pipeline from its target table, similar to the `STOP PIPELINE` command, while allowing `ALTER TABLE` operations on the table currently 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
Trace Event Commands	Drops a trace event.	Trace Events
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	Removes a group from a SingleStore Helios cluster.	Security Management
DROP INDEX	Drops the specified index on the specified table.	Data Definition Language
DROP LINK	Removes a connection link on S3, Azure, GCS, HDFS, or Kafka for a permitted user.	Security Management
DROP PIPELINE	Deletes a pipeline from your SingleStore Helios 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 PROJECTION	Drops an existing projection.	Data Definition Language
DROP RESOURCE POOL	Drops the specified resource pool.	Resource Pool
DROP ROLE	Removes a role from a SingleStore Helios cluster.	Security Management
DROP TABLE	Drops the specified table.	Data Definition Language
DROP USER	Removes one or more user accounts.	Security Management
DROP VIEW	Drops a view.	Data Definition Language
ECHO	Runs a stored procedure and returns a set of rows as a result.	Procedural
ECHO SELECT	Runs the specified `SELECT` statement within a stored procedure and returns the resulting rows to the console or to the application that called the stored procedure.	Procedural
EXCEPT and MINUS	Compare the results of two queries and return the distinct rows from the first query that do not appear in the second query.	Data Manipulation Language
EXECUTE IMMEDIATE	Runs a SQL query.	Procedural
EXISTS AND NOT EXISTS	Use a subquery to determine whether the subquery returns any rows.	Data Manipulation Language
EXPLAIN	Returns detailed information on how the query runs.	Data Manipulation Language
FULLTEXT SERVICE STOP	Stops the full-text V2 service.	Operational
GRANT	Grants the specified privileges to an existing user.	Security Management
GRANT GROUP	Adds a user to a group.	Security Management
GRANT ROLE	Adds 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	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	Imports data stored in a CSV, JSON, BSON, Avro, or Parquet 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
PIVOT	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
QUERY	SingleStore Helios Procedural SQL (PSQL) supports the `QUERY` data type, whose value represents a SQL `SELECT` statement, optionally with bound variable values.	Procedural
RECORD	SingleStore Helios Procedural SQL (PSQL) supports the `RECORD` data type, which is an encapsulated group of named fields.	Procedural
REOPTIMIZE	Reoptimizes the most recently profiled query based on previous executions of it.	Data Manipulation Language
REOPTIMIZE MARK	Manually marks a plan for Feedback Reoptimization.	Data Manipulation Language
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
RESTORE DATABASE	Restores a database from a binary backup file.	Operational
REVOKE	Revokes privileges from a SingleStore Helios user or role.	Security Management
REVOKE GROUP	Removes a user from a group.	Security Management
REVOKE ROLE	Removes a role from a group.	Security Management
ROLLBACK	Rolls back the open transaction if one exists.	Data Manipulation Language
SELECT	Retrieves data from a table.	Data Manipulation Language
SELECT GLOBAL	Inspects the setting of a global engine variable.	Operational
SET and SET SESSION	Sets a session variable. Its value remains in effect for the duration of the current database connection.	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 CDC EXTRACTOR POOL	Displays information about the CDC-in pipelines.	Show Commands
SHOW CHARACTER SET	Shows supported character sets and the default collation for each character set.	Show Commands
SHOW COLLATION	Lists available collations and character sets.	Show Commands
SHOW COLUMNS	Shows 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 LINK	Shows the `CREATE LINK` command used to create a link.	Show Commands
SHOW CREATE PIPELINE	Returns the `CREATE PIPELINE` statement that was issued to create a pipeline. The output can be used to duplicate the configuration and state of an existing pipeline.	Show Commands
SHOW CREATE PROJECTION	Shows the `CREATE PROJECTION` statement that was used to create the projection.	Show Commands
SHOW CREATE PROCEDURE	Shows the definition, procedure body, and other attributes for a stored procedure.	Procedural
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 Helios database status.	Show Commands
SHOW DATABASES	Shows the list of databases that exist on this SingleStore Helios instance.	Show Commands
SHOW DISTRIBUTED_PLANCACHE STATUS	Provides information about the status and performance of the Distributed Plancache.	Show Commands
SHOW ERRORS	Displays errors as a result of execution of an invalid statement.	Show Commands
SHOW FEEDBACK REOPTIMIZATION STATUS	Displays system-wide Feedback Reoptimization (FR) information.	Show Commands
SHOW FULLTEXT SERVICE METRICS	Displays metrics for the JLucene full-text search in JSON format.	Show Commands
SHOW FULLTEXT SERVICE STATUS	Shows the status of the full-text V2 service.	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	Shows global SingleStore Helios server status information.	Show Commands
SHOW GRANTS	Shows permissions (privileges) for the specified user (or role).	Security Management
SHOW GROUPS	Shows the list of groups on a cluster group.	Security Management
SHOW GROUPS FOR ROLE	Shows groups for a specific role.	Security Management
SHOW GROUPS FOR USER	Shows 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 LINKS	Shows all connection links on S3, Azure, GCS, HDFS, Kafka, or other data sources for a permitted user.	Security Management
SHOW LOAD ERRORS	This command is deprecated. Query the LOAD_DATA_ERRORS information schema view to see the errors that occurred after running the `LOAD DATA` command.	Show Commands
SHOW LOAD WARNINGS	This command is deprecated. Query the LOAD_DATA_ERRORS information schema view 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	Shows all existing pipelines in a database and their current state.	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 Helios 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 PROCEDURES	Lists existing stored procedures in the current or the specified database.	Procedural
SHOW PROJECTIONS	Shows projections created on the current database or specified table.	Show Commands
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 group.	Security Management
SHOW ROLES FOR GROUP	Shows roles for a specific group.	Security Management
SHOW ROLES FOR USER	Shows roles for a specific user.	Security Management
SHOW SCHEMAS	Shows the list of schemas that exist on this SingleStore Helios instance.	Show Commands
SHOW SESSION STATUS	Shows SingleStore Helios server status information for a session.	Show Commands
SHOW STATUS EXTENDED	Shows SingleStore Helios server status information with detailed SingleStore Helios-specific memory and performance metrics.	Show Commands
SHOW TABLE STATUS	Shows tables status information in a SingleStore Helios 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	Shows users on a SingleStore Helios cluster.	Security Management
SHOW USERS FOR GROUP	Shows users for a specific group.	Security Management
SHOW USERS FOR ROLE	Shows users for a specific role.	Security Management
SHOW VARIABLES	Shows a list of variable bindings.	Show Commands
SHOW WARNINGS	Displays 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	Starts an existing pipeline in a SingleStore Helios database. An error will occur if the pipeline is already running.	Pipeline
STOP PIPELINE	Stops a currently running pipeline in a SingleStore Helios database.	Pipeline
SYNC BOTTOMLESS DATABASE	Synchronizes any currently committed data in the database with the remote object storage.	Operational
_SYNC_PLANS	Synchronizes a node's local PPC with the DPC for a specific database.	Operational
TEST PIPELINE	Tests an existing pipeline and looks for any new files to import.	Pipeline
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	Modifies rows in a table.	Data Manipulation Language
USE	Specifies the database or schema to use.	Data Manipulation Language
WITH (Common Table Expressions)	Specifies a temporary named result set, referred to as a Common Table Expression (CTE).	Data Manipulation Language

SQL Commands List

Was this article helpful?

Was this article helpful?