edit-icon download-icon

SQL statement

Last Updated: May 04, 2018

ALTER SCHEMA

Synopsis

  1. ALTER SCHEMA name RENAME TO new_name

Description

Renames SCHEMA.

Examples

  1. ALTER SCHEMA web RENAME TO traffic -- Renames Schema 'web' as 'traffic'

ALTER TABLE

Synopsis

  1. ALTER TABLE name RENAME TO new_name
  2. ALTER TABLE name ADD COLUMN column_name data_type
  3. ALTER TABLE name DROP COLUMN column_name
  4. ALTER TABLE name RENAME COLUMN column_name TO new_column_name

Description

Changes the definition of an existing table

Examples

  1. ALTER TABLE users RENAME TO people; --- Rename
  2. ALTER TABLE users ADD COLUMN zip varchar; --- Add column
  3. ALTER TABLE users DROP COLUMN zip; --- Drop column
  4. ALTER TABLE users RENAME COLUMN id TO user_id; --- Rename column

CALL

Synopsis

  1. CALL procedure_name ( [ name => ] expression [, ...] )

Description

Calls a stored procedure. Stored procedures can be provided by connectors to perform data manipulation or administrative tasks. Some connectors such as the PostgreSQL Connector, are for systems that have their own stored procedures. These systems must use the stored procedures provided by the connectors to access their own stored procedures, which are not directly callable via CALL.

Examples

  1. CALL test(123, 'apple'); --- Call a stored procedure using positional arguments
  2. CALL test(name => 'apple', id => 123); --- Call a stored procedure using named arguments
  3. CALL catalog.schema.test(); --- Call a stored procedure using a fully qualified name

COMMIT

Synopsis

  1. COMMIT [WORK]

Description

Commits the current transaction.

Examples

  1. COMMIT;
  2. COMMIT WORK;

CREATE SCHEMA

Synopsis

  1. CREATE SCHEMA [ IF NOT EXISTS ] schema_name
  2. [ WITH ( property_name = expression [, ...] ) ]

Description

Creates a new SCHEMA. Schema is a container that holds tables, views, and other database objects.

  • The optional IF NOT EXISTS clause causes the error to be suppressed if the schema already exists;
  • The optional WITH clause can be used to set properties on the newly created schema. To list all available schema properties, run the following query:
  1. SELECT * FROM system.metadata.schema_properties;

Examples

  1. CREATE SCHEMA web;
  2. CREATE SCHEMA hive.sales;
  3. CREATE SCHEMA IF NOT EXISTS traffic;

CREATE TABLE

Synopsis

  1. CREATE TABLE [ IF NOT EXISTS ]
  2. table_name (
  3. { column_name data_type [ COMMENT comment ]
  4. | LIKE existing_table_name [ { INCLUDING | EXCLUDING } PROPERTIES ] }
  5. [, ...]
  6. )
  7. [ COMMENT table_comment ]
  8. [ WITH ( property_name = expression [, ...] ) ]

Description

Creates an empty table. Use the CREATE TABLE AS to create a table from an existing data set.

  • The optional IF NOT EXISTS clause causes the error to be suppressed if the table already exists;
  • The optional WITH clause can be used to set properties on the newly created table. To list all available table properties, run the following query:

    1. SELECT * FROM system.metadata.table_properties;
  • The LIKE clause can be used to include all the column definitions from an existing table in the new table. Multiple LIKE clauses may be specified;
  • If INCLUDING PROPERTIES is specified, all of the table properties are copied to a new table. If the WITH clause specifies the same property name as one of the copied properties using INCLUDING PROPERTIES, the value from the WITH clause is used. The default behavior is EXCLUDING PROPERTIES.

Examples

  1. --- Create a new table orders:
  2. CREATE TABLE orders (
  3. orderkey bigint,
  4. orderstatus varchar,
  5. totalprice double,
  6. orderdate date
  7. )
  8. WITH (format = 'ORC')
  9. --- Create the table orders if it does not already exist, adding a table comment and a column comment:
  10. CREATE TABLE IF NOT EXISTS orders (
  11. orderkey bigint,
  12. orderstatus varchar,
  13. totalprice double COMMENT 'Price in cents.',
  14. orderdate date
  15. )
  16. COMMENT 'A table to keep track of orders.'
  17. --- Create the table bigger_orders, using some column definitions from orders:
  18. CREATE TABLE bigger_orders (
  19. another_orderkey bigint,
  20. LIKE orders,
  21. another_orderdate date
  22. )

CREATE TABLE AS

Synopsis

  1. CREATE TABLE [ IF NOT EXISTS ] table_name [ ( column_alias, ... ) ]
  2. [ COMMENT table_comment ]
  3. [ WITH ( property_name = expression [, ...] ) ]
  4. AS query
  5. [ WITH [ NO ] DATA ]

Description

Creates a new table containing the result of a SELECT query.

  • The optional IF NOT EXISTS clause causes the error to be suppressed if the table already exists;
  • The optional WITH clause can be used to set properties on the newly created table. To list all available table properties, run the following query:

    1. SELECT * FROM system.metadata.table_properties;

Examples

  1. --- Select two columns from orders to create a new table
  2. CREATE TABLE orders_column_aliased (order_date, total_price)
  3. AS
  4. SELECT orderdate, totalprice
  5. FROM orders
  6. --- Create a new table using the aggregate function
  7. CREATE TABLE orders_by_date
  8. COMMENT 'Summary of orders by date'
  9. WITH (format = 'ORC')
  10. AS
  11. SELECT orderdate, sum(totalprice) AS price
  12. FROM orders
  13. GROUP BY orderdate
  14. --- Create a new table, using the **IF NOT EXISTS** clause
  15. CREATE TABLE IF NOT EXISTS orders_by_date AS
  16. SELECT orderdate, sum(totalprice) AS price
  17. FROM orders
  18. GROUP BY orderdate
  19. --- Create a new table with the same schema as nation and no data
  20. CREATE TABLE empty_nation AS
  21. SELECT *
  22. FROM nation
  23. WITH NO DATA

CREATE VIEW

Synopsis

  1. CREATE [ OR REPLACE ] VIEW view_name AS query

Description

Creates a view. The view is a logic table that does not contain any data. It can be referenced by future queries. The query stored by the view is run every time the view is referenced by another query.

The optional OR REPLACE clause causes the view to be replaced if it already exists rather than raising an error.

Examples

  1. --- Create a simple view
  2. CREATE VIEW test AS
  3. SELECT orderkey, orderstatus, totalprice / 2 AS half
  4. FROM orders
  5. --- Create view using the aggregate function
  6. CREATE VIEW orders_by_date AS
  7. SELECT orderdate, sum(totalprice) AS price
  8. FROM orders
  9. GROUP BY orderdate
  10. --- Create a view that replaces an existing view
  11. CREATE OR REPLACE VIEW test AS
  12. SELECT orderkey, orderstatus, totalprice / 4 AS quarter
  13. FROM orders

DEALLOCATE PREPARE

Synopsis

  1. DEALLOCATE PREPARE statement_name

Description

Removes a statement with the name statement_name from the list of prepared statements in a session.

Examples

  1. --- Deallocate a statement named my_query
  2. DEALLOCATE PREPARE my_query;

DELETE

Synopsis

  1. DELETE FROM table_name [ WHERE condition ]

Description

If the WHERE clause is specified, delete the matching rows from the table. Otherwise, all rows from the table are deleted.

Examples

  1. --- Delete the matching row
  2. DELETE FROM lineitem WHERE shipmode = 'AIR';
  3. --- Delete the matching row
  4. DELETE FROM lineitem
  5. WHERE orderkey IN (SELECT orderkey FROM orders WHERE priority = 'LOW');
  6. --- Clear the table
  7. DELETE FROM orders;

Limitations

Some connectors have limited or no support for DELETE.

DESCRIBE

Synopsis

  1. DESCRIBE table_name

Description

Retrieves the table definitions, and is an alias for SHOW COLUMNS.

Examples

  1. DESCRIBE orders;

DESCRIBE INPUT

Synopsis

  1. DESCRIBE INPUT statement_name

Description

Lists the input parameters of a prepared statement along with the position and type of each parameter.

Examples

  1. --- Create a prepared statement 'my_select1'
  2. PREPARE my_select1 FROM
  3. SELECT ? FROM nation WHERE regionkey = ? AND name < ?;
  4. --- Get the descriptive information of this prepared statement
  5. DESCRIBE INPUT my_select1;

Query result:

  1. Position | Type
  2. --------------------
  3. 0 | unknown
  4. 1 | bigint
  5. 2 | varchar
  6. (3 rows)

DESCRIBE OUTPUT

Synopsis

  1. DESCRIBE OUTPUT statement_name

Description

Lists the output columns of a prepared statement, including the column name (or alias), catalog, schema, table name, type, type size in bytes, and a boolean indicating if the column is aliased.

Examples

Example 1Prepare a prepared statement:

  1. PREPARE my_select1 FROM
  2. SELECT * FROM nation;

Execute DESCRIBE OUTPUT, which outputs:

  1. DESCRIBE OUTPUT my_select1;
  2. Column Name | Catalog | Schema | Table | Type | Type Size | Aliased
  3. -------------+---------+--------+--------+---------+-----------+---------
  4. nationkey | tpch | sf1 | nation | bigint | 8 | false
  5. name | tpch | sf1 | nation | varchar | 0 | false
  6. regionkey | tpch | sf1 | nation | bigint | 8 | false
  7. comment | tpch | sf1 | nation | varchar | 0 | false
  8. (4 rows)

Example 2

  1. PREPARE my_select2 FROM
  2. SELECT count(*) as my_count, 1+2 FROM nation

Execute DESCRIBE OUTPUT, which outputs:

  1. DESCRIBE OUTPUT my_select2;
  2. Column Name | Catalog | Schema | Table | Type | Type Size | Aliased
  3. -------------+---------+--------+-------+--------+-----------+---------
  4. my_count | | | | bigint | 8 | true
  5. _col1 | | | | bigint | 8 | false
  6. (2 rows)

Example 3

  1. PREPARE my_create FROM
  2. CREATE TABLE foo AS SELECT * FROM nation;

Execute DESCRIBE OUTPUT, which outputs:

  1. DESCRIBE OUTPUT my_create;
  2. Column Name | Catalog | Schema | Table | Type | Type Size | Aliased
  3. -------------+---------+--------+-------+--------+-----------+---------
  4. rows | | | | bigint | 8 | false
  5. (1 row)

DROP SCHEMA

Synopsis

  1. DROP SCHEMA [ IF EXISTS ] schema_name

Description

Drops an existing Schema.

  • The schema must be empty.
  • The optional IF EXISTS clause causes the error to be suppressed if the schema does not exist.

Examples

  1. DROP SCHEMA web;
  2. DROP TABLE IF EXISTS sales;

DROP TABLE

Synopsis

  1. DROP TABLE [ IF EXISTS ] table_name

Description

Drops an existing table. The optional IF EXISTS clause causes the error to be suppressed if the table does not exist.

Examples

  1. DROP TABLE orders_by_date;
  2. DROP TABLE IF EXISTS orders_by_date;

DROP VIEW

Synopsis

  1. DROP VIEW [ IF EXISTS ] view_name

Description

Drops an existing view. The optional IF EXISTS clause causes the error to be suppressed if the view does not exist.

Examples

  1. DROP VIEW orders_by_date;
  2. DROP VIEW IF EXISTS orders_by_date;

EXECUTE

Synopsis

  1. EXECUTE statement_name [ USING parameter1 [ , parameter2, ... ] ]

Description

Executes a prepared statement. Parameter values are defined in the USING clause.

Examples

Example 1

  1. PREPARE my_select1 FROM
  2. SELECT name FROM nation;
  3. --- Execute a prepared statement
  4. EXECUTE my_select1;

Example 2

  1. PREPARE my_select2 FROM
  2. SELECT name FROM nation WHERE regionkey = ? and nationkey < ?;
  3. --- Execute a prepared statement
  4. EXECUTE my_select2 USING 1, 3;
  5. --- The preceding statement is equivalent to executing the following statement:
  6. SELECT name FROM nation WHERE regionkey = 1 AND nationkey < 3;

EXPLAIN

Synopsis

  1. EXPLAIN [ ( option [, ...] ) ] statement
  2. where option can be one of:
  3. FORMAT { TEXT | GRAPHVIZ }
  4. TYPE { LOGICAL | DISTRIBUTED | VALIDATE }

Description

Achieves one of the following functions based on the option used:

  • Shows the logical plan of a query statement
  • Shows the distributed execution plan of a query statement
  • Validates a query statement

Use TYPE DISTRIBUTED option to display fragmented plan. Each plan fragment is executed by a single or multiple Presto nodes. Fragments separation represent the data exchange between Presto nodes. Fragment type specifies how the fragment is executed by Presto nodes and how the data is distributed between fragments. Fragment types are as follows:

  • SINGLE: Fragment is executed on a single node;
  • HASH: Fragment is executed on a fixed number of nodes with the input data distributed using a hash function;
  • ROUND_ROBIN: Fragment is executed on a fixed number of nodes with the input data distributed in a round-robin fashion;
  • BROADCAST: Fragment is executed on a fixed number of nodes with the input data broadcasted to all nodes;
  • SOURCE: Fragment is executed on nodes where input splits are accessed.

Examples

Example 1

Logical plan:

  1. presto:tiny> EXPLAIN SELECT regionkey, count(*) FROM nation GROUP BY 1;
  2. Query Plan
  3. ----------------------------------------------------------------------------------------------------------
  4. - Output[regionkey, _col1] => [regionkey:bigint, count:bigint]
  5. _col1 := count
  6. - RemoteExchange[GATHER] => regionkey:bigint, count:bigint
  7. - Aggregate(FINAL)[regionkey] => [regionkey:bigint, count:bigint]
  8. count := "count"("count_8")
  9. - LocalExchange[HASH][$hashvalue] ("regionkey") => regionkey:bigint, count_8:bigint, $hashvalue:bigint
  10. - RemoteExchange[REPARTITION][$hashvalue_9] => regionkey:bigint, count_8:bigint, $hashvalue_9:bigint
  11. - Project[] => [regionkey:bigint, count_8:bigint, $hashvalue_10:bigint]
  12. $hashvalue_10 := "combine_hash"(BIGINT '0', COALESCE("$operator$hash_code"("regionkey"), 0))
  13. - Aggregate(PARTIAL)[regionkey] => [regionkey:bigint, count_8:bigint]
  14. count_8 := "count"(*)
  15. - TableScan[tpch:tpch:nation:sf0.1, originalConstraint = true] => [regionkey:bigint]
  16. regionkey := tpch:regionkey

Example 2

Distributed plan:

  1. presto:tiny> EXPLAIN (TYPE DISTRIBUTED) SELECT regionkey, count(*) FROM nation GROUP BY 1;
  2. Query Plan
  3. ----------------------------------------------------------------------------------------------
  4. Fragment 0 [SINGLE]
  5. Output layout: [regionkey, count]
  6. Output partitioning: SINGLE []
  7. - Output[regionkey, _col1] => [regionkey:bigint, count:bigint]
  8. _col1 := count
  9. - RemoteSource[1] => [regionkey:bigint, count:bigint]
  10. Fragment 1 [HASH]
  11. Output layout: [regionkey, count]
  12. Output partitioning: SINGLE []
  13. - Aggregate(FINAL)[regionkey] => [regionkey:bigint, count:bigint]
  14. count := "count"("count_8")
  15. - LocalExchange[HASH][$hashvalue] ("regionkey") => regionkey:bigint, count_8:bigint, $hashvalue:bigint
  16. - RemoteSource[2] => [regionkey:bigint, count_8:bigint, $hashvalue_9:bigint]
  17. Fragment 2 [SOURCE]
  18. Output layout: [regionkey, count_8, $hashvalue_10]
  19. Output partitioning: HASH [regionkey][$hashvalue_10]
  20. - Project[] => [regionkey:bigint, count_8:bigint, $hashvalue_10:bigint]
  21. $hashvalue_10 := "combine_hash"(BIGINT '0', COALESCE("$operator$hash_code"("regionkey"), 0))
  22. - Aggregate(PARTIAL)[regionkey] => [regionkey:bigint, count_8:bigint]
  23. count_8 := "count"(*)
  24. - TableScan[tpch:tpch:nation:sf0.1, originalConstraint = true] => [regionkey:bigint]
  25. regionkey := tpch:regionkey

Example 3

Validation

  1. presto:tiny> EXPLAIN (TYPE VALIDATE) SELECT regionkey, count(*) FROM nation GROUP BY 1;
  2. Valid
  3. -------
  4. true

EXPLAIN ANALYZE

Synopsis

  1. EXPLAIN ANALYZE [VERBOSE] statement

Description

Executes the statement and shows the distributed execution plan of the statement along with the cost of each operation. The VERBOSE option gives more detailed information and low-level statistics.

Examples

In the following example, you can see the CPU time spent in each stage, as well as the relative cost of each plan node in the stage. Note that the relative cost of the plan nodes is based on wall time, which may or may not be correlated to CPU time. For each plan node you can see some additional statistics, which are useful if you want to detect data anomalies for a query (skewness, abnormal hash collisions).

  1. presto:sf1> EXPLAIN ANALYZE SELECT count(*), clerk FROM orders WHERE orderdate > date '1995-01-01' GROUP BY clerk;
  2. Query Plan
  3. -----------------------------------------------------------------------------------------------
  4. Fragment 1 [HASH]
  5. Cost: CPU 88.57ms, Input: 4000 rows (148.44kB), Output: 1000 rows (28.32kB)
  6. Output layout: [count, clerk]
  7. Output partitioning: SINGLE []
  8. - Project[] => [count:bigint, clerk:varchar(15)]
  9. Cost: 26.24%, Input: 1000 rows (37.11kB), Output: 1000 rows (28.32kB), Filtered: 0.00%
  10. Input avg.: 62.50 lines, Input std.dev.: 14.77%
  11. - Aggregate(FINAL)[clerk][$hashvalue] => [clerk:varchar(15), $hashvalue:bigint, count:bigint]
  12. Cost: 16.83%, Output: 1000 rows (37.11kB)
  13. Input avg.: 250.00 lines, Input std.dev.: 14.77%
  14. count := "count"("count_8")
  15. - LocalExchange[HASH][$hashvalue] ("clerk") => clerk:varchar(15), count_8:bigint, $hashvalue:bigint
  16. Cost: 47.28%, Output: 4000 rows (148.44kB)
  17. Input avg.: 4000.00 lines, Input std.dev.: 0.00%
  18. - RemoteSource[2] => [clerk:varchar(15), count_8:bigint, $hashvalue_9:bigint]
  19. Cost: 9.65%, Output: 4000 rows (148.44kB)
  20. Input avg.: 4000.00 lines, Input std.dev.: 0.00%
  21. Fragment 2 [tpch:orders:1500000]
  22. Cost: CPU 14.00s, Input: 818058 rows (22.62MB), Output: 4000 rows (148.44kB)
  23. Output layout: [clerk, count_8, $hashvalue_10]
  24. Output partitioning: HASH [clerk][$hashvalue_10]
  25. - Aggregate(PARTIAL)[clerk][$hashvalue_10] => [clerk:varchar(15), $hashvalue_10:bigint, count_8:bigint]
  26. Cost: 4.47%, Output: 4000 rows (148.44kB)
  27. Input avg.: 204514.50 lines, Input std.dev.: 0.05%
  28. Collisions avg.: 5701.28 (17569.93% est.), Collisions std.dev.: 1.12%
  29. count_8 := "count"(*)
  30. - ScanFilterProject[table = tpch:tpch:orders:sf1.0, originalConstraint = ("orderdate" > "$literal$date"(BIGINT '9131')), filterPredicate = ("orderdate" > "$literal$date"(BIGINT '9131'))] => [cler
  31. Cost: 95.53%, Input: 1500000 rows (0B), Output: 818058 rows (22.62MB), Filtered: 45.46%
  32. Input avg.: 375000.00 lines, Input std.dev.: 0.00%
  33. $hashvalue_10 := "combine_hash"(BIGINT '0', COALESCE("$operator$hash_code"("clerk"), 0))
  34. orderdate := tpch:orderdate
  35. clerk := tpch:clerk

When the VERBOSE option is used, some operators may report additional information.

  1. EXPLAIN ANALYZE VERBOSE SELECT count(clerk) OVER() FROM orders WHERE orderdate > date '1995-01-01';
  2. Query Plan
  3. -----------------------------------------------------------------------------------------------
  4. ...
  5. - Window[] => [clerk:varchar(15), count:bigint]
  6. Cost: {rows: ?, bytes: ?}
  7. CPU fraction: 75.93%, Output: 8130 rows (230.24kB)
  8. Input avg.: 8130.00 lines, Input std.dev.: 0.00%
  9. Active Drivers: [ 1 / 1 ]
  10. Index size: std.dev.: 0.00 bytes , 0.00 rows
  11. Index count per driver: std.dev.: 0.00
  12. Rows per driver: std.dev.: 0.00
  13. Size of partition: std.dev.: 0.00
  14. count := count("clerk")
  15. ...

GRANT

Synopsis

  1. GRANT ( privilege [, ...] | ( ALL PRIVILEGES ) )
  2. ON [ TABLE ] table_name TO ( grantee | PUBLIC )
  3. [ WITH GRANT OPTION ]

Description

Grants the specified privileges to the specified grantee.

  • Specifying ALL PRIVILEGES grants DELETE, INSERT and SELECT privileges;
  • Specifying PUBLIC grants privileges to the PUBLIC role and hence to all users;
  • The optional WITH GRANT OPTION clause allows the grantee to grant these same privileges to others.

Examples

  1. GRANT INSERT, SELECT ON orders TO alice; --- Grant privileges to user alice
  2. GRANT SELECT ON nation TO alice WITH GRANT OPTION; --- Grant SELECT privilege to user alice, additionally allowing alice to grant **SELECT** privilege to others
  3. GRANT SELECT ON orders TO PUBLIC; --- Grant **SELECT** privilege on the table order to everyone

Limitations

Some connectors have no support for GRANT.

INSERT

Synopsis

  1. INSERT INTO table_name [ ( column [, ... ] ) ] query

Description

Inserts new rows into a table. If the list of column names is specified, they must exactly match the list of columns produced by the query. Each column in the table not present in the column list is filled with a null value.

Examples

  1. INSERT INTO orders SELECT * FROM new_orders; --- Insert the SELECT results into the orders table.
  2. INSERT INTO cities VALUES (1, 'San Francisco'); --- Insert a single row
  3. INSERT INTO cities VALUES (2, 'San Jose'), (3, 'Oakland'); --- Insert multiple rows
  4. INSERT INTO nation (nationkey, name, regionkey, comment) VALUES (26, 'POLAND', 3, 'no comment'); --- Insert a single row
  5. INSERT INTO nation (nationkey, name, regionkey) VALUES (26, 'POLAND', 3); --- Inserts a single row (only includes some columns)

PREPARE

Synopsis

  1. PREPARE statement_name FROM statement

Description

Prepares a statement for execution at a later time. Prepared statements are queries saved in a session with a given name. The statement can include parameters in place of literals to be replaced at execution time. Parameters are represented by ?.

Examples

  1. --- Prepare a query that does not include parameters
  2. PREPARE my_select1 FROM
  3. SELECT * FROM nation;
  4. --- Prepare a query that includes parameters
  5. PREPARE my_select2 FROM
  6. SELECT name FROM nation WHERE regionkey = ? AND nationkey < ?;
  7. --- Prepare an insert statement that does not include parameters
  8. PREPARE my_insert FROM
  9. INSERT INTO cities VALUES (1, 'San Francisco');

RESET SESSION

Synopsis

  1. RESET SESSION name
  2. RESET SESSION catalog.name

Description

Reset a session property value to the default value.

Examples

  1. RESET SESSION optimize_hash_generation;
  2. RESET SESSION hive.optimized_reader_enabled;

REVOKE

Synopsis

  1. REVOKE [ GRANT OPTION FOR ]
  2. ( privilege [, ...] | ALL PRIVILEGES )
  3. ON [ TABLE ] table_name FROM ( grantee | PUBLIC )

Description

Revokes the specified privileges from the specified grantee.

  • Specifying ALL PRIVILEGES revokes DELETE, INSERT and SELECT privileges.
  • Specifying PUBLIC revokes privileges from the PUBLIC role. Users will retain privileges assigned to them directly or via other roles.
  • The optional GRANT OPTION FOR clause also revokes the privileges to grant the specified privileges.
  • Usage of the term grantee denotes both users and roles.

Examples

  1. --- Revoke INSERT and SELECT privileges on the table orders from user alice
  2. REVOKE INSERT, SELECT ON orders FROM alice;
  3. --- Revoke SELECT privilege on the table nation from everyone,
  4. --- additionally revoking the privilege to grant SELECT privilege to others
  5. REVOKE GRANT OPTION FOR SELECT ON nation FROM PUBLIC;
  6. --- Revoke all privileges on the table test from user alice
  7. REVOKE ALL PRIVILEGES ON test FROM alice;

Limitations

Some connectors have no support for REVOKE.

ROLLBACK

Synopsis

  1. ROLLBACK [ WORK ]

Description

Rollback the current transaction.

Examples

  1. ROLLBACK;
  2. ROLLBACK WORK;

SELECT

Synopsis

  1. [ WITH with_query [, ...] ]
  2. SELECT [ ALL | DISTINCT ] select_expr [, ...]
  3. [ FROM from_item [, ...] ]
  4. [ WHERE condition ]
  5. [ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]
  6. [ HAVING condition]
  7. [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
  8. [ ORDER BY expression [ ASC | DESC ] [, ...] ]
  9. [ LIMIT [ count | ALL ] ]

where from_item is one of:

  1. table_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
  1. from_item join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

and join_type is one of:

  • [ INNER ] JOIN
  • LEFT [ OUTER ] JOIN
  • RIGHT [ OUTER ] JOIN
  • FULL [ OUTER ] JOIN
  • CROSS JOIN

and grouping_element is one of:

  • ()
  • expression
  • GROUPING SETS ( ( column [, …] ) [, …] )
  • CUBE ( column [, …] )
  • ROLLUP ( column [, …] )

Description

Retrieve rows from zero or more tables to get data sets.

WITH clause

Basic functions

The WITH clause defines named relations for use within a query. It allows flattening nested queries or simplifying subqueries. For example, the following queries are equivalent:

  1. --- The WITH clause is not used
  2. SELECT a, b
  3. FROM (
  4. SELECT a, MAX(b) AS b FROM t GROUP BY a
  5. ) AS x;
  6. --- The WITH clause is used, and the query statement looks to be much clearer
  7. WITH x AS (SELECT a, MAX(b) AS b FROM t GROUP BY a)
  8. SELECT a, b FROM x;

Define multiple subqueries

The WITH clause can be used to define multiple subqueries:

  1. WITH
  2. t1 AS (SELECT a, MAX(b) AS b FROM x GROUP BY a),
  3. t2 AS (SELECT a, AVG(d) AS d FROM y GROUP BY a)
  4. SELECT t1.*, t2.*
  5. FROM t1
  6. JOIN t2 ON t1.a = t2.a;

Form a chain structure

Additionally, the relations within a WITH clause can chain:

  1. WITH
  2. x AS (SELECT a FROM t),
  3. y AS (SELECT a AS b FROM x),
  4. z AS (SELECT b AS c FROM y)
  5. SELECT c FROM z;

GROUP BY clause

Basic functions

The GROUP BY clause divides the output of a SELECT statement into groups of rows containing matching values. A simple GROUP BY clause may contain any expression composed of input columns or it may be an ordinal number selecting an output column by position (starting at one).

The following queries are equivalent (position for the nationkey column is two).

  1. --- Using the ordinal number
  2. SELECT count(*), nationkey FROM customer GROUP BY 2;
  3. --- Using the input column name
  4. SELECT count(*), nationkey FROM customer GROUP BY nationkey;

GROUP BY clauses can group output by input column names not appearing in the output of a select statement, for example:

  1. --- The mktsegment column has not been specified in the SELECT list,
  2. --- The result set does not contain content of the mktsegment column.
  3. SELECT count(*) FROM customer GROUP BY mktsegment;
  4. _col0
  5. -------
  6. 29968
  7. 30142
  8. 30189
  9. 29949
  10. 29752
  11. (5 rows)

Note: When a GROUP BY clause is used in a SELECT statement, all output expressions must be either aggregate functions or columns present in the GROUP BY clause.

Complex grouping operations

Presto supports the following three complex aggregation syntaxes, which allows users to perform analysis that requires aggregation on multiple sets of columns in a single query:

  • GROUPING SETS
  • CUBE
  • ROLLUP

Complex grouping operations do not support grouping on expressions composed of input columns. Only column names or ordinals are allowed.

GROUPING SETS

GROUPING SETS allow users to specify multiple lists of columns to group on. The columns not part of a given list of grouping columns are set to null.

The shipping table is a data table with 5 columns, which are shown as follows:

  1. SELECT * FROM shipping;
  2. origin_state | origin_zip | destination_state | destination_zip | package_weight
  3. --------------+------------+-------------------+-----------------+----------------
  4. California | 94131 | New Jersey | 8648 | 13
  5. California | 94131 | New Jersey | 8540 | 42
  6. New Jersey | 7081 | Connecticut | 6708 | 225
  7. California | 90210 | Connecticut | 6927 | 1337
  8. California | 94131 | Colorado | 80302 | 5
  9. New York | 10002 | New Jersey | 8540 | 3
  10. (6 rows)

Now we want to retrieve the following grouping results using a single query statement:

  • Group by origin_state, and get the total package_weight;
  • Group by origin_state and origin_zip, and get the total package_weight;
  • Group by destination_state, and get the total package_weight.

GROUPING SETS allows users to retrieve the result set of the above three groups with a single query statement, as shown below:

  1. SELECT origin_state, origin_zip, destination_state, sum(package_weight)
  2. FROM shipping
  3. GROUP BY GROUPING SETS (
  4. (origin_state),
  5. (origin_state, origin_zip),
  6. (destination_state));
  7. origin_state | origin_zip | destination_state | _col0
  8. --------------+------------+-------------------+-------
  9. New Jersey | NULL | NULL | 225
  10. California | NULL | NULL | 1397
  11. New York | NULL | NULL | 3
  12. California | 90210 | NULL | 1337
  13. California | 94131 | NULL | 60
  14. New Jersey | 7081 | NULL | 225
  15. New York | 10002 | NULL | 3
  16. NULL | NULL | Colorado | 5
  17. NULL | NULL | New Jersey | 58
  18. NULL | NULL | Connecticut | 1562
  19. (10 rows)

The preceding query may be considered logically equivalent to a UNION ALL of multiple GROUP BY queries:

  1. SELECT origin_state, NULL, NULL, sum(package_weight)
  2. FROM shipping GROUP BY origin_state
  3. UNION ALL
  4. SELECT origin_state, origin_zip, NULL, sum(package_weight)
  5. FROM shipping GROUP BY origin_state, origin_zip
  6. UNION ALL
  7. SELECT NULL, NULL, destination_state, sum(package_weight)
  8. FROM shipping GROUP BY destination_state;

However, the query with the complex grouping syntax (such as GROUPING SETS) only reads from the underlying data source once, while the query with the UNION ALL reads the underlying data three times. This is why queries with a UNION ALL may produce inconsistent results when the data source is not deterministic.

CUBE

The CUBE operator generates all possible grouping sets for a given set of columns. For example, the query:

  1. SELECT origin_state, destination_state, sum(package_weight)
  2. FROM shipping
  3. GROUP BY CUBE (origin_state, destination_state);
  4. origin_state | destination_state | _col0
  5. --------------+-------------------+-------
  6. California | New Jersey | 55
  7. California | Colorado | 5
  8. New York | New Jersey | 3
  9. New Jersey | Connecticut | 225
  10. California | Connecticut | 1337
  11. California | NULL | 1397
  12. New York | NULL | 3
  13. New Jersey | NULL | 225
  14. NULL | New Jersey | 58
  15. NULL | Connecticut | 1562
  16. NULL | Colorado | 5
  17. NULL | NULL | 1625
  18. (12 rows)

is equivalent to:

  1. SELECT origin_state, destination_state, sum(package_weight)
  2. FROM shipping
  3. GROUP BY GROUPING SETS (
  4. (origin_state, destination_state),
  5. (origin_state),
  6. (destination_state),
  7. ());
ROLLUP

The ROLLUP operator generates all possible subtotals for a given set of columns. For example, the query:

  1. SELECT origin_state, origin_zip, sum(package_weight)
  2. FROM shipping
  3. GROUP BY ROLLUP (origin_state, origin_zip);
  4. origin_state | origin_zip | _col2
  5. --------------+------------+-------
  6. California | 94131 | 60
  7. California | 90210 | 1337
  8. New Jersey | 7081 | 225
  9. New York | 10002 | 3
  10. California | NULL | 1397
  11. New York | NULL | 3
  12. New Jersey | NULL | 225
  13. NULL | NULL | 1625
  14. (8 rows)

is equivalent to:

  1. SELECT origin_state, origin_zip, sum(package_weight)
  2. FROM shipping
  3. GROUP BY GROUPING SETS ((origin_state, origin_zip), (origin_state), ());
Combining multiple grouping expressions

The following 3 statements are equivalent:

  1. SELECT origin_state, destination_state, origin_zip, sum(package_weight)
  2. FROM shipping
  3. GROUP BY
  4. GROUPING SETS ((origin_state, destination_state)),
  5. ROLLUP (origin_zip);
  1. SELECT origin_state, destination_state, origin_zip, sum(package_weight)
  2. FROM shipping
  3. GROUP BY
  4. GROUPING SETS ((origin_state, destination_state)),
  5. GROUPING SETS ((origin_zip), ());
  1. SELECT origin_state, destination_state, origin_zip, sum(package_weight)
  2. FROM shipping
  3. GROUP BY GROUPING SETS (
  4. (origin_state, destination_state, origin_zip),
  5. (origin_state, destination_state));

Output results are as follows:

  1. origin_state | destination_state | origin_zip | _col3
  2. --------------+-------------------+------------+-------
  3. New York | New Jersey | 10002 | 3
  4. California | New Jersey | 94131 | 55
  5. New Jersey | Connecticut | 7081 | 225
  6. California | Connecticut | 90210 | 1337
  7. California | Colorado | 94131 | 5
  8. New York | New Jersey | NULL | 3
  9. New Jersey | Connecticut | NULL | 225
  10. California | Colorado | NULL | 5
  11. California | Connecticut | NULL | 1337
  12. California | New Jersey | NULL | 55
  13. (10 rows)

In a GROUP BY clause, the ALL and DISTINCT quantifiers determine whether duplicate grouping sets each produce distinct output rows. For example, the query:

  1. SELECT origin_state, destination_state, origin_zip, sum(package_weight)
  2. FROM shipping
  3. GROUP BY ALL
  4. CUBE (origin_state, destination_state),
  5. ROLLUP (origin_state, origin_zip);

is equivalent to

  1. SELECT origin_state, destination_state, origin_zip, sum(package_weight)
  2. FROM shipping
  3. GROUP BY GROUPING SETS (
  4. (origin_state, destination_state, origin_zip),
  5. (origin_state, origin_zip),
  6. (origin_state, destination_state, origin_zip),
  7. (origin_state, origin_zip),
  8. (origin_state, destination_state),
  9. (origin_state),
  10. (origin_state, destination_state),
  11. (origin_state),
  12. (origin_state, destination_state),
  13. (origin_state),
  14. (destination_state),
  15. ());

Multiple duplicate grouping sets are available. However, if the query uses the DISTINCT quantifier, only unique grouping sets are generated.

  1. SELECT origin_state, destination_state, origin_zip, sum(package_weight)
  2. FROM shipping
  3. GROUP BY DISTINCT
  4. CUBE (origin_state, destination_state),
  5. ROLLUP (origin_state, origin_zip);

is equivalent to

  1. SELECT origin_state, destination_state, origin_zip, sum(package_weight)
  2. FROM shipping
  3. GROUP BY GROUPING SETS (
  4. (origin_state, destination_state, origin_zip),
  5. (origin_state, origin_zip),
  6. (origin_state, destination_state),
  7. (origin_state),
  8. (destination_state),
  9. ());

The default set quantifier for GROUP BY is ALL.

GROUPING operation

Presto provides a grouping operation that returns a bit set converted to decimal, indicating which columns are present in a grouping. The semantics is demonstrated as follows:

  1. grouping(col1, ..., colN) -> bigint

grouping is used in conjunction with GROUPING SETS, ROLLUP, CUBE or GROUP BY, and its arguments must match exactly the columns referenced in the corresponding GROUPING SETS, ROLLUP, CUBE or GROUP BY clause.

  1. SELECT origin_state, origin_zip, destination_state, sum(package_weight),
  2. grouping(origin_state, origin_zip, destination_state)
  3. FROM shipping
  4. GROUP BY GROUPING SETS (
  5. (origin_state),
  6. (origin_state, origin_zip),
  7. (destination_state));
  8. origin_state | origin_zip | destination_state | _col3 | _col4
  9. --------------+------------+-------------------+-------+-------
  10. California | NULL | NULL | 1397 | 3 --- 011
  11. New Jersey | NULL | NULL | 225 | 3 --- 011
  12. New York | NULL | NULL | 3 | 3 --- 011
  13. California | 94131 | NULL | 60 | 1 --- 001
  14. New Jersey | 7081 | NULL | 225 | 1 --- 001
  15. California | 90210 | NULL | 1337 | 1 --- 001
  16. New York | 10002 | NULL | 3 | 1 --- 001
  17. NULL | NULL | New Jersey | 58 | 6 --- 100
  18. NULL | NULL | Connecticut | 1562 | 6 --- 100
  19. NULL | NULL | Colorado | 5 | 6 --- 100
  20. (10 rows)

As shown in the preceding table, bits are assigned to the argument columns with the rightmost column being the least significant bit. For a given grouping, a bit is set to 0 if the corresponding column is included in the grouping and to 1 otherwise.

HAVING clause

The HAVING clause is used in conjunction with aggregate functions and the GROUP BY clause to control which groups are selected. A HAVING clause will be executed after completion of grouping and aggregation, to eliminate groups that do not satisfy the given conditions.

The following example selects user groups with an account balance greater than 5700000:

  1. SELECT count(*), mktsegment, nationkey,
  2. CAST(sum(acctbal) AS bigint) AS totalbal
  3. FROM customer
  4. GROUP BY mktsegment, nationkey
  5. HAVING sum(acctbal) > 5700000
  6. ORDER BY totalbal DESC;

The output is as follows:

  1. _col0 | mktsegment | nationkey | totalbal
  2. -------+------------+-----------+----------
  3. 1272 | AUTOMOBILE | 19 | 5856939
  4. 1253 | FURNITURE | 14 | 5794887
  5. 1248 | FURNITURE | 9 | 5784628
  6. 1243 | FURNITURE | 12 | 5757371
  7. 1231 | HOUSEHOLD | 3 | 5753216
  8. 1251 | MACHINERY | 2 | 5719140
  9. 1247 | FURNITURE | 8 | 5701952
  10. (7 rows)

Set operations

Overview

Presto supports three set operations, namely UNION, INTERSECT, and EXCEPT. These clauses are used to combine the results of more than one query statement into a single result set. The usage is as follows:

  1. query UNION [ALL | DISTINCT] query
  2. query INTERSECT [DISTINCT] query
  3. query EXCEPT [DISTINCT] query

The argument ALL or DISTINCT controls which rows are included in the final result set, and the default is DISTINCT.

  • ALL: may return duplicated rows;
  • DISTINCT: eliminates duplicated rows.

The ALL argument is not supported for INTERSECT or EXCEPT.

The above three set operations are processed left to right, and INTERSECT has the highest priority. That means A UNION B INTERSECT C EXCEPT D is the same as A UNION (B INTERSECT C) EXCEPT D.

UNION

UNION combines two query result sets, and uses the ALL and DISTINCT arguments to control whether or not to remove duplicates.

Example 1

  1. SELECT 13
  2. UNION
  3. SELECT 42;
  4. _col0
  5. -------
  6. 13
  7. 42
  8. (2 rows)

Example 2

  1. SELECT 13
  2. UNION
  3. SELECT * FROM (VALUES 42, 13);
  4. _col0
  5. -------
  6. 13
  7. 42
  8. (2 rows)

Example 3

  1. SELECT 13
  2. UNION ALL
  3. SELECT * FROM (VALUES 42, 13);
  4. _col0
  5. -------
  6. 13
  7. 42
  8. 13
  9. (3 rows)

INTERSECT

INTERSECT returns only the rows that are in both query result sets.

Examples

  1. SELECT * FROM (VALUES 13, 42)
  2. INTERSECT
  3. SELECT 13;
  4. _col0
  5. -------
  6. 13
  7. (1 rows)

EXCEPT

EXCEPT returns the rows that are in the result set of the first query, but not the second.

  1. SELECT * FROM (VALUES 13, 42)
  2. EXCEPT
  3. SELECT 13;
  4. _col0
  5. -------
  6. 42
  7. (1 rows)

ORDER BY clause

The ORDER BY clause is used to sort a result set. The semantics is demonstrated as follows:

  1. ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...]

Where:

  • Each expression may be composed of output columns or it may be an ordinal number selecting an output column by position (starting at one);
  • The ORDER BY clause is the last step of a query after any GROUP BY or HAVING clause;
  • NULLS { FIRST | LAST } is used to control the sorting method of the NULL value (regardless of ASC/DESC), and the default null ordering is NULLS LAST.

LIMIT clause

The LIMIT clause restricts the number of rows in the result set. LIMIT ALL is the same as omitting the LIMIT clause.

Examples

  1. --- In this example, because the query lacks an ORDER BY, exactly which rows are returned is arbitrary
  2. SELECT orderdate FROM orders LIMIT 5;
  3. orderdate
  4. -------------
  5. 1996-04-14
  6. 1992-01-15
  7. 1995-02-01
  8. 1995-11-12
  9. 1992-04-26
  10. (5 rows)

TABLESAMPLE

Presto provides two sampling methods, namely BERNOULLI and SYSTEM. However, neither of the two methods allow deterministic bounds on the number of rows returned.

BERNOULLI

Each row is selected to be in the table sample with a probability of the sample percentage. When a table is sampled using the Bernoulli method, all physical blocks of the table are scanned and certain rows are skipped based on a comparison between the sample percentage and a random value calculated at runtime.

The probability of a row being included in the result is independent from any other row. This does not reduce the time required to read the sampled table from disk. It may have an impact on the total query time if the sampled output is processed further.

SYSTEM

This sampling method divides the table into logical segments of data and samples the table at this granularity. This sampling method either selects all the rows from a particular segment of data or skips it (based on a comparison between the sample percentage and a random value calculated at runtime).

The rows selected in a system sampling is dependent on which connector is used. For example, when used with Hive, it is dependent on how the data is laid out on HDFS. This method does not guarantee independent sampling probabilities.

Examples

  1. --- Using BERNOULLI sampling
  2. SELECT *
  3. FROM users TABLESAMPLE BERNOULLI (50);
  4. --- Using SYSTEM sampling
  5. SELECT *
  6. FROM users TABLESAMPLE SYSTEM (75);
  7. Using sampling with joins:
  8. --- Using sampling with JOIN
  9. SELECT o.*, i.*
  10. FROM orders o TABLESAMPLE SYSTEM (10)
  11. JOIN lineitem i TABLESAMPLE BERNOULLI (40)
  12. ON o.orderkey = i.orderkey;

UNNEST

UNNEST can be used to expand an ARRAY or MAP into a relation. Arrays are expanded into a single column, and maps are expanded into two columns (key, value). UNNEST can also be used with multiple arrays and maps, in which case they are expanded into multiple columns, with as many rows as the highest cardinality argument (the other columns are padded with nulls). UNNEST can optionally have a WITH ORDINALITY clause, in which case an additional ordinal column is added to the end. UNNEST is normally used with a JOIN and can reference columns from relations on the left side of the join.

Example 1

  1. --- Using a single column
  2. SELECT student, score
  3. FROM tests
  4. CROSS JOIN UNNEST(scores) AS t (score);

Example 2

  1. --- Using multiple columns
  2. SELECT numbers, animals, n, a
  3. FROM (
  4. VALUES
  5. (ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']),
  6. (ARRAY[7, 8, 9], ARRAY['cow', 'pig'])
  7. ) AS x (numbers, animals)
  8. CROSS JOIN UNNEST(numbers, animals) AS t (n, a);
  9. numbers | animals | n | a
  10. -----------+------------------+------+------
  11. [2, 5] | [dog, cat, bird] | 2 | dog
  12. [2, 5] | [dog, cat, bird] | 5 | cat
  13. [2, 5] | [dog, cat, bird] | NULL | bird
  14. [7, 8, 9] | [cow, pig] | 7 | cow
  15. [7, 8, 9] | [cow, pig] | 8 | pig
  16. [7, 8, 9] | [cow, pig] | 9 | NULL
  17. (6 rows)

Example 3

  1. --- Using a WITH ORDINALITY clause
  2. SELECT numbers, n, a
  3. FROM (
  4. VALUES
  5. (ARRAY[2, 5]),
  6. (ARRAY[7, 8, 9])
  7. ) AS x (numbers)
  8. CROSS JOIN UNNEST(numbers) WITH ORDINALITY AS t (n, a);
  9. numbers | n | a
  10. -----------+---+---
  11. [2, 5] | 2 | 1
  12. [2, 5] | 5 | 2
  13. [7, 8, 9] | 7 | 1
  14. [7, 8, 9] | 8 | 2
  15. [7, 8, 9] | 9 | 3
  16. (5 rows)

Joins

Joins allow you to combine data from multiple relations. A CROSS JOIN returns the Cartesian product of two relations (all combinations). Cross joins can either be specified using

  • the explicit CROSS JOIN syntax, or
  • by specifying multiple relations in the FROM clause.

Both of the following queries are equivalent:

  1. --- using the explicit **CROSS JOIN** syntax
  2. SELECT *
  3. FROM nation
  4. CROSS JOIN region;
  5. --- specifying multiple relations in the **FROM** clause
  6. SELECT *
  7. FROM nation, region;

Examples

The nation table contains 25 rows and the region table contains 5 rows, so a cross join between the two tables produces 125 rows:

  1. SELECT n.name AS nation, r.name AS region
  2. FROM nation AS n
  3. CROSS JOIN region AS r
  4. ORDER BY 1, 2;
  5. nation | region
  6. ----------------+-------------
  7. ALGERIA | AFRICA
  8. ALGERIA | AMERICA
  9. ALGERIA | ASIA
  10. ALGERIA | EUROPE
  11. ALGERIA | MIDDLE EAST
  12. ARGENTINA | AFRICA
  13. ARGENTINA | AMERICA
  14. ...
  15. (125 rows)

When two relations in a join have columns with the same name, the column references must be qualified using the relation name (or alias).

Examples

  1. --- Correct
  2. SELECT nation.name, region.name
  3. FROM nation
  4. CROSS JOIN region;
  5. --- Correct
  6. SELECT n.name, r.name
  7. FROM nation AS n
  8. CROSS JOIN region AS r;
  9. --- Correct
  10. SELECT n.name, r.name
  11. FROM nation n
  12. CROSS JOIN region r;
  13. --- Wrong, it will raise the "Column 'name' is ambiguous" error
  14. SELECT name
  15. FROM nation
  16. CROSS JOIN region;

Subquery

A subquery is an expression which is composed of a query. The subquery is correlated when it refers to columns outside of the subquery. Presto has limited support for correlated subqueries.

EXISTS

The EXISTS predicate determines if a subquery returns any rows. If subquery returns any rows, the WHERE expression is TRUE, and FALSE if otherwise.

Examples

  1. SELECT name
  2. FROM nation
  3. WHERE EXISTS (SELECT * FROM region WHERE region.regionkey = nation.regionkey);

IN

The IN predicate determines if any columns specified by WHERE are included in the result set produced by the subquery. If yes, it returns results, and does not return results if otherwise. The subquery must produce exactly one column.

Examples

  1. SELECT name
  2. FROM nation
  3. WHERE regionkey IN (SELECT regionkey FROM region);

Scalar subquery

A scalar subquery is a non-correlated subquery that returns zero or one row. The subquery cannot produce more than one row. The returned value is NULL if the subquery produces no rows.

Examples

  1. SELECT name
  2. FROM nation
  3. WHERE regionkey = (SELECT max(regionkey) FROM region)

SET SESSION

Synopsis

  1. SET SESSION name = expression
  2. SET SESSION catalog.name = expression

Description

Sets a session property value.

Examples

  1. SET SESSION optimize_hash_generation = true;
  2. SET SESSION hive.optimized_reader_enabled = true;

SHOW CATALOGS

Synopsis

  1. SHOW CATALOGS [ LIKE pattern ]

Description

Lists the available catalogs. The LIKE clause can be used to filter the catalog names.

Examples

  1. SHOW CATALOGS;

SHOW COLUMNS

Synopsis

  1. SHOW COLUMNS FROM table

Description

Lists the columns in a given table along with their data type and other attributes.

Examples

  1. SHOW COLUMNS FROM orders;

SHOW CREATE TABLE

Synopsis

  1. SHOW CREATE TABLE table_name

Description

Shows the SQL statement that creates the specified table.

Examples

  1. SHOW CREATE TABLE sf1.orders;
  2. -----------------------------------------
  3. CREATE TABLE tpch.sf1.orders (
  4. orderkey bigint,
  5. orderstatus varchar,
  6. totalprice double,
  7. orderdate varchar
  8. )
  9. WITH (
  10. format = 'ORC',
  11. partitioned_by = ARRAY['orderdate']
  12. )
  13. (1 row)

SHOW CREATE VIEW

Synopsis

  1. SHOW CREATE VIEW view_name

Description

Shows the SQL statement that creates the specified view.

Examples

  1. SHOW CREATE VIEW view1;

SHOW FUNCTIONS

Synopsis

  1. SHOW FUNCTIONS

Description

List all the functions available for use in queries.

Examples

  1. SHOW FUNCTIONS

SHOW GRANTS

Synopsis

  1. SHOW GRANTS [ ON [ TABLE ] table_name ]

Description

Lists the grants for the current user on the specified table in the current catalog.

Examples

  1. --- List the grants for the current user on table orders
  2. SHOW GRANTS ON TABLE orders;
  3. --- List the grants for the current user on all the tables in all schemas of the current catalog
  4. SHOW GRANTS;

Limitations

Some connectors have no support for SHOW GRANTS.

SHOW SCHEMAS

Synopsis

  1. SHOW SCHEMAS [ FROM catalog ] [ LIKE pattern ]

Description

Lists all schemas in the specified catalog, or in the current catalog if no catalog has been specified. The LIKE clause can be used to filter the schema names.

Examples

  1. SHOW SCHEMAS;

SHOW SESSION

Synopsis

  1. SHOW SESSION

Description

Lists the current session properties.

Examples

  1. SHOW SESSION

SHOW TABLES

Synopsis

  1. SHOW TABLES [ FROM schema ] [ LIKE pattern ]

Description

Lists all tables in the specified schema, or in the current schema if no schema has been specified. The LIKE clause can be used to filter the table name.

Examples

  1. SHOW TABLES;

START TRANSACTION

Synopsis

  1. START TRANSACTION [ mode [, ...] ]
  2. where **mode** is one of:
  3. ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
  4. READ { ONLY | WRITE }

Description

Starts a new transaction for the current session.

Examples

  1. START TRANSACTION;
  2. START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  3. START TRANSACTION READ WRITE;
  4. START TRANSACTION ISOLATION LEVEL READ COMMITTED, READ ONLY;
  5. START TRANSACTION READ WRITE, ISOLATION LEVEL SERIALIZABLE;

USE

Synopsis

  1. USE catalog.schema
  2. USE schema

Description

Updates the session to use the specified catalog and schema. If a catalog is not specified, the schema is resolved relative to the current catalog.

Examples

  1. USE hive.finance;
  2. USE information_schema;

VALUES

Synopsis

  1. VALUES row [, ...]
  2. where **row** is a single expression or
  3. ( column_expression [, ...] )

Description

Defines a literal inline table.

  • VALUES can be used anywhere a query can be used. For example, behind the FROM clause of a SELECT, in an INSERT, or even at the top level.
  • VALUES creates an anonymous table without column names by default. The table and columns can be named using an AS clause.

Examples

  1. --- Return a table with one column and three rows
  2. VALUES 1, 2, 3
  3. --- Return a table with two columns and three rows
  4. VALUES
  5. (1, 'a'),
  6. (2, 'b'),
  7. (3, 'c')
  8. --- Using in a query statement:
  9. SELECT * FROM (
  10. VALUES
  11. (1, 'a'),
  12. (2, 'b'),
  13. (3, 'c')
  14. ) AS t (id, name)
  15. --- Create a table
  16. CREATE TABLE example AS
  17. SELECT * FROM (
  18. VALUES
  19. (1, 'a'),
  20. (2, 'b'),
  21. (3, 'c')
  22. ) AS t (id, name)
Thank you! We've received your feedback.