Glue Data Catalog view syntax - Amazon Athena
ALTER VIEW DIALECTCREATE PROTECTED MULTI DIALECT VIEWDESCRIBEDROP VIEWSHOW COLUMNSSHOW CREATE VIEWSHOW VIEWS
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Glue Data Catalog view syntax

This feature is in preview release and is subject to change. For more information, see the Betas and Previews section in the Amazon Service Terms document.

This section describes the data definition language (DDL) commands for creating and managing Amazon Glue Data Catalog views.

ALTER VIEW DIALECT

You can update Data Catalog views by either adding an engine dialect or by updating or dropping an existing engine dialect. Only the Lake FormationAdmin and the Definer (the user who created the view) have permission to use the ALTER VIEW DIALECT statement on a Data Catalog view.

Syntax

ALTER VIEW name [ FORCE ] [ ADD|UPDATE ] DIALECT AS query
ALTER VIEW name [ DROP ] DIALECT
FORCE

The FORCE keyword causes conflicting engine dialect information in a view to be overwritten with the new definition. The FORCE keyword is useful when an update to a Data Catalog view results in conflicting view definitions across existing engine dialects. Suppose a Data Catalog view has both the Athena and Amazon Redshift dialects and the update results in a conflict with Amazon Redshift in the view definition. In this case, you can use the FORCE keyword to allow the update to complete and mark the Amazon Redshift dialect as stale. When engines marked as stale query the view, the query fails. The engines throw an exception to disallow stale results. To correct this, update the stale dialects in the view.

ADD

Adds a new engine dialect to the Data Catalog view. The engine specified must not already exist in the Data Catalog view.

UPDATE

Updates an engine dialect that already exists in the Data Catalog view.

DROP

Drops an existing engine dialect from a Data Catalog view. After you drop an engine from a Data Catalog view, the Data Catalog view cannot be queried by the engine that was dropped. Other engine dialects in the view can still query the view.

DIALECT AS

Introduces an engine-specific SQL query.

Examples

ALTER VIEW orders_by_date FORCE ADD DIALECT 
AS 
SELECT orderdate, sum(totalprice) AS price 
FROM orders 
GROUP BY orderdate
ALTER VIEW orders_by_date FORCE UPDATE DIALECT 
AS 
SELECT orderdate, sum(totalprice) AS price 
FROM orders 
GROUP BY orderdate
ALTER VIEW orders_by_date DROP DIALECT

CREATE PROTECTED MULTI DIALECT VIEW

Creates a Data Catalog view in the Amazon Glue Data Catalog. A Data Catalog view is a single view schema that works seamlessly across Athena and other SQL engines such as Amazon Redshift and Amazon EMR.

Syntax

CREATE [ OR REPLACE ] PROTECTED MULTI DIALECT VIEW view_name 
 [ SECURITY DEFINER ] 
AS query
PROTECTED

Required keyword. Specifies that the view is protected against data leaks. Data Catalog views can only be created as a PROTECTED view.

MULTI DIALECT

Specifies that the view supports the SQL dialects of different query engines and can therefore be read by those engines.

SECURITY DEFINER

Specifies that definer semantics are in force for this view. Definer semantics mean that the effective read permissions on the underlying tables belong to the principal or role that defined the view rather than the principal that performs the actual read.

OR REPLACE

A Data Catalog view cannot be replaced if SQL dialects from other engines are present in the view. If the calling engine has the only SQL dialect present in the view, the view can be replaced.

Example

The following example creates the orders_by_date Data Catalog view based on a query on the orders table.

CREATE PROTECTED MULTI DIALECT VIEW orders_by_date 
SECURITY DEFINER 
AS 
SELECT orderdate, sum(totalprice) AS price 
FROM orders 
WHERE order_city = 'SEATTLE' 
GROUP BY orderdate

DESCRIBE

Shows the list of columns for the specified Data Catalog view. The DESCRIBE statement is similar to the DESCRIBE statement for Athena views. Unlike Athena views, the output of the statement is controlled through Lake Formation access control. The output of this query is therefore not all columns of the view, but only the columns that the caller has access to.

Syntax

DESCRIBE [db_name.]view_name

Examples

DESCRIBE orders

DROP VIEW

Drops a Data Catalog view only if the calling engine dialect is present in the Data Catalog view. For example, if a user calls DROP VIEW from Athena, the view is dropped only if Athena's dialect exists in the view. Otherwise, the operation fails. Only the Lake Formation Admin and the view definer have permission to use the DROP VIEW statement on a Data Catalog view.

Syntax

DROP VIEW [ IF EXISTS ] view_name

Examples

DROP VIEW orders_by_date
DROP FORCE VIEW IF EXISTS orders_by_date

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

SHOW COLUMNS

Shows only the column names for a single specified Data Catalog view. The SHOW COLUMNS statement is similar to the SHOW COLUMNS statement for Athena views. Unlike Athena views, the output of the statement is controlled through Lake Formation access control. The output of this query is therefore not all columns of the view, but only the columns that the caller has access to.

Syntax

SHOW COLUMNS {FROM|IN} database_name.view_name
SHOW COLUMNS {FROM|IN} view_name [{FROM|IN} database_name]

SHOW CREATE VIEW

Shows the SQL syntax that created the Data Catalog view. The SQL returned shows the create view syntax used in Athena. Only the Lake Formation Admin and the view definer principals are authorized to call SHOW CREATE VIEW on a Data Catalog view.

Syntax

SHOW CREATE VIEW view_name

Examples

SHOW CREATE VIEW orders_by_date

SHOW VIEWS

Lists the names of all views in the database. All Data Catalog views in the database that have the Athena engine SQL dialect are listed. Other Data Catalog views that do not have the Athena engine dialect present in the view are filtered out.

Syntax

SHOW VIEWS [IN database_name] [LIKE 'regular_expression']

Examples

SHOW VIEWS
SHOW VIEWS IN marketing_analytics LIKE 'orders*'