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).

Working with Amazon Glue Data Catalog views in Amazon Glue

You can create and manage views in the Amazon Glue Data Catalog, commonly known as Amazon Glue Data Catalog views. These views are useful because they support multiple SQL query engines, allowing you to access the same view across different Amazon services, such as Amazon Athena, Amazon Redshift, and Amazon Glue. You can use views based on Apache Iceberg, Apache Hudi, and Delta Lake.

By creating a view in the Data Catalog, you can use resource grants and tag-based access controls in Amazon Lake Formation to grant access to it. Using this method of access control, you don't have to configure additional access to the tables referenced when creating the view. This method of granting permissions is called definer semantics, and these views are called definer views. For more information about access control in Amazon Lake Formation, see Granting and revoking permissions on Data Catalog resources in the Amazon Lake Formation Developer Guide.

Data Catalog views are useful for the following use cases:

Creating a Data Catalog view

You can create Data Catalog views using the Amazon CLI and Amazon Glue ETL scripts using Spark SQL. The syntax for creating a Data Catalog view includes specifying the view type as MULTI DIALECT and the SECURITY predicate as DEFINER, indicating a definer view.

Example SQL statement to create a Data Catalog view:

CREATE PROTECTED MULTI DIALECT VIEW database_name.catalog_view SECURITY DEFINER
AS SELECT order_date, sum(totalprice) AS price
FROM source_table
GROUP BY order_date;

After creating a Data Catalog view, you can use an IAM role with the Amazon Lake Formation `SELECT` permission on the view to query it from services like Amazon Athena, Amazon Redshift, or Amazon Glue ETL jobs. You don't need to grant access to the underlying tables referenced in the view.

For more information on creating and configuring Data Catalog views, see Building Amazon Glue Data Catalog views in the Amazon Lake Formation Developer Guide.

Supported view operations

The following command fragments show you various ways to work with Data Catalog views:

CREATE VIEW

Creates a data-catalog view. The following is a sample that shows creating a view from an existing table:

CREATE PROTECTED MULTI DIALECT VIEW catalog_view 
SECURITY DEFINER AS SELECT * FROM my_catalog.my_database.source_table

ALTER VIEW

Available syntax:

ALTER VIEW view_name [FORCE] ADD DIALECT AS query
ALTER VIEW view_name [FORCE] UPDATE DIALECT AS query
ALTER VIEW view_name DROP DIALECT

You can use the FORCE ADD DIALECT option to force update the schema and sub objects as per the new engine dialect. Note that doing this can result in query errors if you don't also use FORCE to update other engine dialects. The following shows a sample:

ALTER VIEW catalog_view FORCE ADD DIALECTAS
SELECT order_date, sum(totalprice) AS priceFROM source_tableGROUP BY orderdate;

The following shows how to alter a view in order to update the dialect:

ALTER VIEW catalog_view UPDATE DIALECT AS
SELECT count(*) FROM my_catalog.my_database.source_table;

DESCRIBE VIEW

Available syntax for describing a view:

SHOW COLUMNS {FROM|IN} view_name [{FROM|IN} database_name] – If the user has the required Amazon Glue and Amazon Lake Formation permissions to describe the view, they can list the columns. The following shows a couple sample commands for showing columns:

SHOW COLUMNS FROM my_database.source_table;    
SHOW COLUMNS IN my_database.source_table;

DESCRIBE view_name – If the user has the required Amazon Glue and Amazon Lake Formation permissions to describe the view, they can list the columns in the view along with its metadata.

DROP VIEW

Available syntax:

DROP VIEW [ IF EXISTS ] view_name

The following sample shows a DROP statement that tests if a view exists prior to dropping it:

DROP VIEW IF EXISTS catalog_view;

SHOW CREATE VIEW view_name – Shows the SQL statement that creates the specified view. The following is a sample that shows creating a data-catalog view:

SHOW CREATE TABLE my_database.catalog_view;CREATE PROTECTED MULTI DIALECT VIEW my_catalog.my_database.catalog_view (
  net_profit,
  customer_id,
  item_id,
  sold_date)
TBLPROPERTIES (
  'transient_lastDdlTime' = '1736267222')
SECURITY DEFINER AS SELECT * FROM
my_database.store_sales_partitioned_lf WHERE customer_id IN (SELECT customer_id from source_table limit 10)

SHOW VIEWS

List all views in the catalog, such as regular views, multi-dialect views (MDV), and MDV without Spark dialect. Available syntax is the following:

SHOW VIEWS [{ FROM | IN } database_name] [LIKE regex_pattern]:

The following shows a sample command to show views:

SHOW VIEWS IN marketing_analytics LIKE 'catalog_view*';

For more information about creating and configuring data-catalog views, see Building Amazon Glue Data Catalog views in the Amazon Lake Formation Developer Guide.

Querying a Data Catalog view

After creating a Data Catalog view, you can query the view. The IAM role configured in your Amazon Glue jobs must have the Lake Formation SELECT permission on the Data Catalog view. You don't need to grant access to the underlying tables referenced in the view.

Once you have everything set up, you can query your view. For example, you can run the following query to access a view.

SELECT * from my_database.catalog_view LIMIT 10;

Limitations

Consider the following limitations when you use Data Catalog views.