SQL Commands List
Here is an alphabetical list of all the SQL commands SingleStore currently supports.
Command Name |
Command Description |
Grouping |
---|---|---|
Adds a new child aggregator to a cluster. |
Cluster Management |
|
Add a leaf node to a cluster. |
Cluster Management |
|
Promote a child aggregator to master aggregator. |
Cluster Management |
|
Sets the auto-increment counter on the aggregators (master or child). |
Data Manipulation Language |
|
Specify if an existing database should begin using synchronous or asynchronous replication. |
Data Definition Language |
|
The
|
Cluster Management |
|
The |
Pipeline |
|
Changes the resource settings in an existing resource pool. |
Resource Pool |
|
Modify the structure of an existing table. |
Data Definition Language |
|
Alters settings associated with the user. |
Security Management |
|
Atomically replace a view with a new view defined by a |
Data Definition Language |
|
Statistics for a table can be collected and updated on an ad hoc basis by running the |
Operational |
|
SingleStore Procedural SQL (PSQL) supports the |
Procedural |
|
Restores an unlimited storage database at a restore point and then brings the database online. |
Operational |
|
Attaches a detached leaf into the cluster, re-introducing its data (if possible). |
Cluster Management |
|
Attaches all leaves in a cluster that are currently in the |
Cluster Management |
|
Backs up a database. |
Operational |
|
Commits any existing open transaction on the current connection and starts a new transaction. |
Data Manipulation Language |
|
Promotes a node to Master Aggregator. |
Cluster Management |
|
Executes the specified stored procedure. |
Procedural |
|
|
Operational |
|
|
Operational |
|
Removes load errors from the |
Data Manipulation Language |
|
Removes orphaned partitions. |
Operational |
|
After |
Procedural |
|
Commits the open transaction if one exists. |
Data Manipulation Language |
|
Resume replicating the database after pausing via PAUSE REPLICATING. |
Operational |
|
Creates or replaces an external function, which is a function that calls code that is executed outside of a SingleStore database. |
Procedural |
|
When the session terminates, the temporary stored procedure is no longer available. |
Procedural |
|
Creates a user-defined aggregate function (UDAF). |
Procedural |
|
Creates a database. |
Data Definition Language |
|
Creates a user-defined table-valued function (TVF). |
Procedural |
|
Creates a user-defined scalar-valued function (UDF). |
Procedural |
|
Create a group on a SingleStore cluster. |
Security Management |
|
Create an index on the specified table. |
Data Definition Language |
|
Create a new connection link to S3, Azure, GCS, HDFS, HTTP, or Kafka for a permitted user. |
Security Management |
|
Records a milestone, which is a named point to which you can restore an unlimited storage database. |
Data Definition Language |
|
Create a new Pipeline to continuously extract, shape, and load data into a table or stored procedure. |
Pipeline |
|
Creates a stored procedure. |
Procedural |
|
Creates a resource pool. |
Resource Pool |
|
Create a role on a SingleStore cluster. |
Security Management |
|
Creates a new table. |
Data Definition Language |
|
Create a new user account. |
Security Management |
|
Creates an unmaterialized view. |
Data Definition Language |
|
Creates a new array of the specified length that is initialized with default values for the array’s inferred data type. |
Procedural |
|
Returns a comma separated list of the current user's security groups on a SingleStore cluster. |
Security Management |
|
Returns a comma separated list in one row of the current user's security roles on a SingleStore cluster. |
Security Management |
|
Returns the details (username and hostname) of the user executing the query. |
Security Management |
|
|
Data Manipulation Language |
|
Describe the specified table. |
Data Definition Language |
|
Takes an unlimited storage database offline. |
Operational |
|
Detaches a leaf. |
Cluster Management |
|
Detaches all leaves at once. |
Cluster Management |
|
The |
Pipeline |
|
Forces a plan to be optimized and generated from scratch the next time it is run. |
Data Definition Language |
|
Removes a single user-defined aggregate function (UDAF) from the specified database. |
Procedural |
|
Drops a local storage database. |
Data Definition Language |
|
Removes a single function from the specified database, including user-defined scalar-valued functions (UDFs) and user-defined table-valued functions (TVFs). |
Procedural |
|
Remove a group from a SingleStore cluster. |
Security Management |
|
Drop the specified index on the specified table. |
Data Definition Language |
|
Remove a connection link on S3, Azure, GCS, HDFS, or Kafka for a permitted user. |
Security Management |
|
This command deletes a milestone of a currently attached database. |
Data Definition Language |
|
The |
Pipeline |
|
Removes a single stored procedure from the specified database. |
Procedural |
|
Drop the specified resource pool. |
Resource Pool |
|
Drops the specified result table. |
Data Definition Language |
|
Remove a role from a SingleStore cluster. |
Security Management |
|
Drop the specified table. |
Data Definition Language |
|
Removes one or more user accounts. |
Security Management |
|
Drop a view. |
Data Definition Language |
|
Executes a stored procedure and returns a set of rows as a result. |
Procedural |
|
Within a stored procedure, executes the specified |
Procedural |
|
|
Data Manipulation Language |
|
Runs a SQL query. |
Procedural |
|
Used with a subquery to check if the subquery returns a record. |
Data Manipulation Language |
|
Returns detailed information on how the query is executed. |
Data Manipulation Language |
|
Returns the set of steps that |
Cluster Management |
|
Returns the set of steps that REBALANCE PARTITIONS would run if you executed it now. |
Cluster Management |
|
Returns the set of steps |
Cluster Management |
|
Used to open and close (reset) connections to leaf nodes. |
Cluster Management |
|
Flushes the forward and reverse caches for DNS/IP lookup. |
Cluster Management |
|
Grants the specified privileges to an existing user. |
Security Management |
|
Add a user to a group. |
Security Management |
|
Add a role to a group. |
Security Management |
|
Inserts data into a table. |
Data Manipulation Language |
|
Inserts an array of records into a table in a single operation. |
Procedural |
|
|
Data Manipulation Language |
|
Kill the connection on the specified thread. |
Operational |
|
Kills all running queries or closes all connections. |
Operational |
|
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 |
|
Performs optimizations on the storage of rows in a table. |
Data Definition Language |
|
Pause replicating a database. |
Operational |
|
The |
Data Manipulation Language |
|
Provides detailed resources usage metrics about a query. |
Data Manipulation Language |
|
Runs a single batch in the foreground to provide detailed resource metrics for each operation in your pipeline. |
Pipeline |
|
This command promotes a child aggregator node to master, while demoting the current master to child. |
Cluster Management |
|
SingleStore Procedural SQL (PSQL) supports the |
Procedural |
|
Rebalances the partitions on all databases in the cluster. |
Cluster Management |
|
Rebalances the partitions for a database. |
Cluster Management |
|
SingleStore Procedural SQL (PSQL) supports the |
Procedural |
|
Removes a child aggregator from a cluster. |
Cluster Management |
|
Removes a leaf node from a cluster without deleting its data. |
Cluster Management |
|
Removes all leaf nodes from a cluster without deleting its data. |
Cluster Management |
|
This command is deprecated. |
Operational |
|
If the table has a |
Data Manipulation Language |
|
Starts or continues replicating a database from a remote host to the local host. |
Operational |
|
Restores a database from a binary backup file. |
Operational |
|
Restore redundancy by replicating any partitions with only one instance. |
Cluster Management |
|
Revoke privileges from a SingleStore user or role. |
Security Management |
|
Remove a user from a group. |
Security Management |
|
Remove a role from a group. |
Security Management |
|
Rolls back the open transaction if one exists. |
Data Manipulation Language |
|
The |
Procedural |
|
The |
Procedural |
|
|
Security Management |
|
Retrieves data from a table. |
Data Manipulation Language |
|
Inspect the setting of a global engine variable. |
Operational |
|
Sets session variables. |
Operational |
|
Sets a sync variable to propagate to all aggregators in the cluster. |
Operational |
|
Sets a sync variable. |
Operational |
|
Sets an engine variable globally. |
Operational |
|
Sets a sync variable to propagate to all leaves in the cluster. |
Operational |
|
Sets a user’s password. |
Security Management |
|
Lists any existing user-defined aggregate functions (UDAF) in the specified database. |
Procedural |
|
Show the aggregators in a cluster. |
Cluster Management |
|
Shows information about partitions within a cluster including replication role, replication state, and replication position, across all databases. |
Cluster Management |
|
Show the list of columns within a given table. |
Show Commands |
|
Shows the function definition and other attributes for a user-defined aggregate function (UDAF). |
Procedural |
|
Outputs configuration information about an existing user-defined function, including user-defined scalar value functions (UDFs) and user-defined table-valued functions (TVFs). |
Procedural |
|
You can use the |
Show Commands |
|
Shows the |
Show Commands |
|
Shows the |
Show Commands |
|
Shows SingleStore database status. |
Show Commands |
|
Shows the list of databases that exist on this SingleStore instance. |
Show Commands |
|
Displays errors as a result of execution of an invalid statement. |
Show Commands |
|
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 SingleStore server status information. |
Show Commands |
|
Show permissions (privileges) for the specified user (or role). |
Security Management |
|
Shows the list of groups on a cluster/workspace group. |
Security Management |
|
Show groups for a specific role. |
Security Management |
|
Show groups for a specific user. |
Security Management |
|
Displays the list of indexes associated with a given table. |
Show Commands |
|
Shows the leaf status. |
Show Commands |
|
Shows the leaves in a cluster. |
Cluster Management |
|
Show all connection links on S3, Azure, GCS, HDFS, or Kafka for a permitted user. |
Security Management |
|
This command is deprecated. |
Show Commands |
|
This command is deprecated. |
Show Commands |
|
Shows partitions and relevant information about them. |
Show Commands |
|
You can see all existing pipelines in a database and their current state by using the |
Show Commands |
|
Displays the EXPLAIN plan of a query as per the plan ID. |
Show Commands |
|
Shows all query statements that SingleStore has compiled and executed, as well as cumulative query execution statistics associated with each plan. |
Show Commands |
|
Shows details about currently running threads. |
Show Commands |
|
Shows the status of the currently executing |
Cluster Management |
|
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 |
|
Shows the list of all connections between the replication master and secondary databases. |
Operational |
|
Collects troubleshooting data for query optimizer and code generation issues for queries that fail in the compilation phase. |
Show Commands |
|
Lists all resource pools and their field values. |
Resource Pool |
|
Shows the list of roles on a cluster/workspace group. |
Security Management |
|
Show roles for a specific group. |
Security Management |
|
Show roles for a specific user. |
Security Management |
|
Shows the list of schemas that exist on this SingleStore instance. |
Show Commands |
|
Show SingleStore server status information for a session. |
Show Commands |
|
Shows SingleStore server status information with detailed SingleStore-specific memory and performance metrics. |
Show Commands |
|
Show tables status information in a SingleStore database. |
Show Commands |
|
Shows the list of tables in the currently selected database, or in another database if db_ |
Show Commands |
|
Show users on a SingleStore cluster. |
Security Management |
|
Show users for a specific group. |
Security Management |
|
Show users for a specific role. |
Security Management |
|
Shows a list of variable bindings. |
Show Commands |
|
Display warnings as a result of an invalid statement execution. |
Show Commands |
|
Takes a snapshot of the given database and truncates the transaction log. |
Operational |
|
The |
Pipeline |
|
The |
Pipeline |
|
Stops replicating a database, and promotes the secondary database to the primary database. |
Operational |
|
Synchronizes any currently committed data in the database with the remote object storage. |
Operational |
|
It is a table-valued function that converts a PSQL array to a set of rows. |
Data Manipulation Language |
|
The |
Pipeline |
|
Allows you to convert a dynamic SQL statement to a query type value. |
Procedural |
|
Triggers the garbage collector on the source aggregator and propagates to all the leaves. |
Cluster Management |
|
Removes all rows from the table. |
Data Definition Language |
|
Combines results from multiple SELECT statements. |
Data Manipulation Language |
|
Releases all table locks on all databases in the cluster. |
Data Definition Language |
|
|
Data Manipulation Language |
|
Use a database or schema. |
Data Manipulation Language |
|
Specifies a temporary named result set, referred to as a Common Table Expression (CTE). |
Data Manipulation Language |
Last modified: November 7, 2024