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 |
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 Formation
Admin
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. TheFORCE
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 theFORCE
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*'