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	Specify if an existing database should begin using synchronous or asynchronous replication.	Data Definition Language
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	Collects statistics for a table.	Operational
ARRAY	SingleStore Helios 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
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
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	`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
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
CREATE 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 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
Trace Event Commands	Creates a trace event.	Trace Events
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 Helios workspace.	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, 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	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 PROJECTION	Creates a projection.	Data Definition Language
CREATE RESOURCE POOL	Creates a resource pool.	Resource Pool
CREATE ROLE	Create a role on a SingleStore Helios workspace.	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 workspace.	Security Management
CURRENT_SECURITY_ROLES	Returns a comma separated list in one row of the current user's security roles on a SingleStore workspace.	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 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
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	Remove a group from a SingleStore Helios workspace.	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 PIPELINE	The `DROP PIPELINE` clause deletes a pipeline from your SingleStore Helios workspace. 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	Drop the specified resource pool.	Resource Pool
DROP ROLE	Remove a role from a SingleStore Helios workspace.	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
FULLTEXT SERVICE STOP	Stops the full-text V2 service.	Operational
GRANT	Grants the specified privileges to an existing user.	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, 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	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
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	Reoptimize the most recently profiled query based on previous executions of it.	Data Manipulation Language
REOPTIMIZE MARK	Manually mark 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	Revoke privileges from a SingleStore Helios 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 a session variable. The variable’s value remains in effect during an active connection to the database.	Operational
SET AGGREGATOR	Sets a sync variable to propagate to all aggregators in the workspace.	Operational
SET CLUSTER	Sets a sync variable. Depending on the variable, the updated value is propagated to all nodes in the workspace or all aggregators in the workspace.	Operational
SET GLOBAL	Sets an engine variable globally.	Operational
SET LEAF	Sets a sync variable to propagate to all leaves in the workspace.	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	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 LINK	Shows the `CREATE LINK` command used to create a link.	Show Commands
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 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	Show global SingleStore Helios 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 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 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/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 Helios instance.	Show Commands
SHOW SESSION STATUS	Show 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	Show 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	Show users on a SingleStore Helios workspace.	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 Helios 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 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
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
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 workspace.	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

SQL Commands List

Was this article helpful?

Was this article helpful?