Presto SQL statements

You can use a comprehensive set of SQL statements in Presto to manage schemas, tables, views, roles, and data operations efficiently.

watsonx.data Developer edition

watsonx.data on IBM Software Hub

Supported SQL statements

The following SQL statements are supported in Presto:

Data Definition Language (DDL)

  • ALTER TABLE - Change the definition of an existing table.
  • CREATE MATERIALIZED VIEW - Create a new materialized view from a SELECT query.
    Note: To improve the security and integrity of the storage table, define and lock the schema by using the storage_schema property. Configure access controls to restrict all SELECT access to the storage table, including schema metadata. This configuration helps prevent unauthorized data access and limits exposure of the storage schema design.
  • CREATE ROLE - Create the specified role in the current catalog.
  • CREATE SCHEMA - Create a new, empty schema.
  • CREATE TABLE - Create a new, empty table with the specified columns.
  • CREATE TABLE AS - Create a new table containing the results of a SELECT query.
  • CREATE VIEW - Create a new view from a SELECT query.
  • DROP MATERIALIZED VIEW - Drop an existing materialized view and delete its stored data.
  • DROP SCHEMA - Drop an existing schema.
  • DROP TABLE - Drop an existing table.
  • DROP VIEW - Drop an existing view.
  • REFRESH MATERIALIZED VIEW - Refresh the data stored in a materialized view by re-executing the view query against the base tables.
  • SHOW CREATE MATERIALIZED VIEW - Show the SQL statement that creates the specified materialized view.

Data Manipulation Language (DML)

  • DELETE - Remove rows from a table.
  • INSERT - Add new rows to a table.
  • SELECT - Retrieve rows from zero or more tables.
  • TRUNCATE - Remove all rows from a table.
  • UPDATE - Update selected column values in existing rows in a table.
  • VALUES - Define a literal inline table.

Data Query Language (DQL)

  • ANALYZE - Collect table and column statistics for a given table.
  • DESCRIBE - Display column information for a table or view.
  • DESCRIBE INPUT - List the input parameters of a prepared statement, along with the position and type of each parameter.
  • DESCRIBE OUTPUT - List the output columns of a prepared statement, including the column name (or alias), catalog, schema, table, type, type size in bytes, and a Boolean value that indicates whether the column is aliased.
  • EXPLAIN - Show the logical or distributed execution plan of a statement, or validate the statement.
  • EXPLAIN ANALYZE - Execute the statement and show its distributed execution plan along with the cost of each operation.
  • SHOW CATALOGS - List available catalogs.
  • SHOW COLUMNS - List the columns in table along with their data types and other attributes such as Extra, Comment, Precision, Scale, and Length.
  • SHOW CREATE TABLE - Show the CREATE TABLE statement for a table.
  • SHOW CREATE VIEW - Show the CREATE VIEW statement for a view.
  • SHOW FUNCTIONS - List available functions.
  • SHOW GRANTS - List the grants for the current user on the specified table in the current catalog.
  • SHOW ROLE GRANTS - List, non-recursively, the roles that have been granted to the session user in catalog, or in the current catalog if catalog is not specified.
  • SHOW ROLE - List all the roles in catalog, or in the current catalog if catalog is not specified.
  • SHOW SCHEMAS - List schemas in a catalog.
  • SHOW SESSION - Display current session properties.
  • SHOW STATS - Display table statistics.
  • SHOW TABLES - List tables in a schema.

Access Control

  • GRANT - Grant the specified privileges to the specified grantee.
  • GRANT ROLES - Grant the specified role or roles to the specified principal or principals in the current catalog.
  • REVOKE - Revoke the specified privileges from the specified grantee.
  • REVOKE ROLES - Revoke the specified role or roles from the specified principal or principals in the current catalog.

Session and Prepared Statements

  • DEALLOCATE PREPARE - Remove a statement with the name statement_name from the list of prepared statements in a session.
  • EXECUTE - Execute a prepared statement with the name statement_name.
  • PREPARE - Prepare a statement for execution at a later time.
  • RESET SESSION - Reset a session property value to the default value.
  • SET ROLE - Set the enabled role for the current session in the current catalog.
  • SET SESSION - Set a session property value.
  • USE - Update the session to use the specified catalog and schema.

Learn more

  • For detailed syntax, examples, and usage guidelines for each SQL statement, see SQL Statement Syntax
  • For more information on mixed-case feature flag behavior, supported SQL statements and supported data types matrices, see Support content.