SQL Commands List

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

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

Operational

ARRAY

SingleStoreDB Cloud (MemSQL) Procedural SQL (MPSQL) 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.

Operational

CHECK BOTTOMLESS CHECKSUM

CHECK BOTTOMLESS CHECKSUM checks the checksum of the blob files stored in the 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 [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 SingleStoreDB Cloud 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, 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 SingleStoreDB Cloud workspace.

Security Management

CREATE TABLE

Creates a new table.

Data Definition Language

CREATE USER

Create a new user account.

Security Management

CREATE VIEW

Create 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

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 SingleStoreDB Cloud 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 SingleStoreDB Cloud 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 RESOURCE POOL

Drop the specified resource pool.

Resource Pool

DROP ROLE

Remove a role from a SingleStoreDB Cloud 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

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

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 SingleStoreDB table (referred to as the destination table in this topic).

Data Manipulation Language

OPTIMIZE TABLE

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

SingleStoreDB Cloud (MemSQL) Procedural SQL (MPSQL) supports the QUERY data type, whose value represents a SQL SELECT statement, optionally with bound variable values.

Procedural

RECORD

SingleStoreDB Cloud (MemSQL) Procedural SQL (MPSQL) supports the RECORD data type, which is an encapsulated group of named fields.

Procedural

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 SingleStoreDB Cloud 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 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 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 or procedure, including user-defined scalar value functions (UDFs), user-defined table-valued functions (TVFs), and stored procedures (SPs).

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 SingleStoreDB Cloud database status.

Show Commands

SHOW DATABASES

Shows the list of databases that exist on this SingleStoreDB Cloud 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 SingleStoreDB Cloud 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 SingleStoreDB Cloud workspace

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 SingleStoreDB Cloud 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 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 SingleStoreDB Cloud workspace

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 SingleStoreDB Cloud instance.

Show Commands

SHOW SESSION STATUS

Show SingleStoreDB Cloud server status information for a session.

Show Commands

SHOW STATUS EXTENDED

Shows SingleStoreDB Cloud server status information with detailed SingleStoreDB Cloud-specific memory and performance statistics.

Show Commands

SHOW TABLE STATUS

Show tables status information in a SingleStoreDB Cloud 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 SingleStoreDB Cloud 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 SingleStoreDB Cloud 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 SingleStoreDB Cloud database.

Pipeline

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 MPSQL 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