Amazon Glue Data Catalog views - Amazon Redshift
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).

Amazon Glue Data Catalog views

This topic describes how to create views in the Amazon Glue Data Catalog. You can use views in the Data Catalog to access data in different data sources using the same schema.

By creating views in the Data Catalog, you can create a single common view schema and metadata object to use across engines such as Amazon Athena and Amazon EMR Spark. Doing so lets you use the same views across your data lakes and data warehouses to fit your use cases. Views in the Data Catalog are special in that they are categorized as definer views, where access permissions are defined by the user who created the view instead of the user querying the view. The following are some use cases and benefits of creating views in the Data Catalog:

  • Create a view that restricts data access based on the permissions the user needs. For example, you can use views in the Data Catalog to prevent employees who don’t work in the HR department from seeing personally identifiable information (PII).

  • Make sure that users can’t access incomplete records. By applying certain filters onto your view in the Data Catalog, you make sure that data records inside a view in the Data Catalog are always complete.

  • Data Catalog views have an included security benefit of making sure that the query definition used to create the view must complete to create the view. This security benefit means that views in the Data Catalog are not susceptible to SQL commands from malicious players.

  • Views in the Data Catalog support the same advantages as normal views, such as letting users access a view without making the underlying table available to users.

To create a view in the Data Catalog, you must have a Spectrum external table, an object that’s contained within a Lake Formation-managed datashare, or an Apache Iceberg table.

Definitions of Data Catalog views are stored in the Amazon Glue Data Catalog. Use Amazon Lake Formation to grant access through resource grants, column grants, or tag-based access controls. For more information about granting and revoking access in Lake Formation, see Granting and revoking permissions on Data Catalog resources.

Prerequisites

Before you can create a view in the Data Catalog, make sure that you have the following prerequisites completed:

  • Make sure that your IAM role has the following trust policy.

    { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": [ "glue.amazonaws.com", "lakeformation.amazonaws.com" ] }, "Action": "sts:AssumeRole" } ] }
  • You also need the following pass role policy.

    { "Version": "2012-10-17", "Statement": [ { "Sid": "Stmt1", "Action": [ "iam:PassRole" ], "Effect": "Allow", "Resource": "*", "Condition": { "StringEquals": { "iam:PassedToService": [ "glue.amazonaws.com", "lakeformation.amazonaws.com" ] } } } ] }
  • Finally, you also need the following permissions.

    • Glue:GetDatabase

    • Glue:GetDatabases

    • Glue:CreateTable

    • Glue:GetTable

    • Glue:UpdateTable

    • Glue:DeleteTable

    • Glue:GetTables

    • Glue:SearchTables

    • Glue:BatchGetPartition

    • Glue:GetPartitions

    • Glue:GetPartition

    • Glue:GetTableVersion

    • Glue:GetTableVersions

End-to-end example

Start by creating an external schema based on your Data Catalog database.

CREATE EXTERNAL SCHEMA IF NOT EXISTS external_schema FROM DATA CATALOG DATABASE 'external_data_catalog_db' IAM_ROLE 'arn:aws:iam::123456789012:role/sample-role';

You can now create a Data Catalog view.

CREATE EXTERNAL PROTECTED VIEW external_schema.remote_view AS SELECT * FROM external_schema.remote_table;

You can then start querying your view.

SELECT * FROM external_schema.remote_view;

For more information about the SQL commands related to views in the Data Catalog, see CREATE EXTERNAL VIEW, ALTER EXTERNAL VIEW, and DROP EXTERNAL VIEW.

Secure logging

Redshift masks metadata logged in the Redshift system logs when a query references a multi-dialect glue view. Multi-dialect means that the view supports the SQL dialects of different query engines, such as Redshift and Amazon EMR. Data in the following tables is masked for all queries with the same query id. The following table lists system views and columns with secure logging applied.

System table Sensitive columns
SYS_EXTERNAL_QUERY_DETAIL

Columns: source_type, total_partitions, qualified_partitions, scanned_files, returned_rows, returned_bytes, file_format, file_location, external_query_text, warning_message. For more information, see SYS_EXTERNAL_QUERY_DETAIL.

SYS_EXTERNAL_QUERY_ERROR

Columns: file_location, rowid, column_name, original_value, modified_value, trigger, action, action_value, error_code. For more information, see SYS_EXTERNAL_QUERY_ERROR.

SYS_QUERY_DETAIL

Columns: step_name, table_id, table_name, input_bytes, input_rows, output_bytes, output_rows, blocks_read, blocks_write, local_read_IO, remote_read_IO, spilled_block_local_disk, spilled_block_remote_disk. For more information, see SYS_QUERY_DETAIL.

SYS_QUERY_HISTORY

Columns: returned_rows, returned_bytes. For more information, see SYS_QUERY_HISTORY.

STL_AGGR

Columns: rows, bytes, tbl, type. For more information, see STL_AGGR.

STL_BCAST

Columns: rows, bytes, packets. For more information, see STL_BCAST.

STL_DDLTEXT

Columns: text. For more information, see STL_DDLTEXT.

STL_DELETE

Columns: rows, tbl. For more information, see STL_DELETE.

STL_DIST

Columns: rows, bytes, packets. For more information, see STL_DIST.

STL_EXPLAIN

Columns: plannode, info. For more information, see STL_EXPLAIN.

STL_HASH

Columns: rows, bytes, tbl, est_rows. For more information, see STL_HASH.

STL_HASHJOIN

Columns: rows, tbl, num_parts, join_type. For more information, see STL_HASHJOIN.

STL_INSERT

Columns: rows, tbl. For more information, see STL_INSERT.

STL_LIMIT

Columns: rows. For more information, see STL_LIMIT.

STL_MERGE

Columns: rows. For more information, see STL_MERGE.

STL_MERGEJOIN

Columns: rows, tbl. For more information, see STL_MERGEJOIN.

STL_NESTLOOP

Columns: rows, tbl. For more information, see STL_NESTLOOP.

STL_PARSE

Columns: rows. For more information, see STL_PARSE.

STL_PLAN_INFO

Columns: rows, bytes. For more information, see STL_PLAN_INFO.

STL_PROJECT

Columns: rows, tbl. For more information, see STL_PROJECT.

STL_QUERY

Columns: querytxt. For more information, see STL_QUERY.

STL_QUERY_METRICS

Columns: max_rows, rows, max_blocks_read, blocks_read, max_blocks_to_disk, blocks_to_disk, max_query_scan_size, query_scan_size. For more information, see STL_QUERY_METRICS.

STL_QUERYTEXT

Columns: text. For more information, see STL_QUERYTEXT.

STL_RETURN

Columns: rows, bytes. For more information, see STL_RETURN.

STL_SAVE

Columns: rows, bytes, tbl. For more information, see STL_SAVE.

STL_SCAN

Columns: rows, bytes, fetches, type, tbl, rows_pre_filter, perm_table_name, scanned_mega_value. For more information, see STL_SCAN.

STL_SORT

Columns: rows, bytes, tbl. For more information, see STL_SORT.

STL_TR_CONFLICT

Columns: table_id. For more information, see STL_TR_CONFLICT.

STL_UNDONE

Columns: table_id. For more information, see STL_UNDONE.

STL_UNIQUE

Columns: rows, type, bytes. For more information, see STL_UNIQUE.

STL_UTILITYTEXT

Columns: text. For more information, see STL_UTILITYTEXT.

STL_WINDOW

Columns: rows. For more information, see STL_WINDOW.

STV_BLOCKLIST

Columns: col, tbl, num_values, minvalue, maxvalue. For more information, see STV_BLOCKLIST.

STV_EXEC_STATE

Columns: rows, bytes, label. For more information, see STV_EXEC_STATE.

STV_LOCKS

Columns: table_id. For more information, see STV_LOCKS.

STV_QUERY_METRICS

Columns: rows, max_rows, blocks_read, max_blocks_read, max_blocks_to_disk, blocks_to_disk, max_query_scan_size, query_scan_size. For more information, see STV_QUERY_METRICS.

STV_STARTUP_RECOVERY_STATE

Columns: table_id, table_name. For more information, see STV_STARTUP_RECOVERY_STATE.

STV_TBL_PERM

Columns: id, rows, sorted_rows, temp, block_count, query_scan_size. For more information, see STV_TBL_PERM.

STV_TBL_TRANS

Columns: id, rows, size. For more information, see STV_TBL_TRANS.

SVCS_EXPLAIN

Columns: plannode, info. For more information, see SVCS_EXPLAIN.

SVCS_PLAN_INFO

Columns: rows, bytes. For more information, see SVCS_PLAN_INFO.

SVCS_QUERY_SUMMARY

Columns: step, rows, bytes, rate_row, rate_byte, label, rows_pre_filter. For more information, see SVCS_QUERY_SUMMARY.

SVCS_S3LIST

Columns: bucket, prefix, max_file_size, avg_file_size. For more information, see SVCS_QUERY_SUMMARY.

SVCS_S3LOG

Columns: message. For more information, see SVCS_QUERY_SUMMARY.

SVCS_S3PARTITION_SUMMARY

Columns: total_partitions, qualified_partitions, min_assigned_partitions, max_assigned_partitions, avg_assigned_partitions. For more information, see SVCS_S3PARTITION_SUMMARY.

SVCS_S3QUERY_SUMMARY

Columns: external_table_name, file_format, s3_scanned_rows, s3_scanned_bytes, s3query_returned_rows, s3query_returned_bytes. For more information, see SVCS_S3QUERY_SUMMARY.

SVL_QUERY_METRICS

Columns: step_label, scan_row_count, join_row_count, nested_loop_join_row_count, return_row_count, spectrum_scan_row_count, spectrum_scan_size_mb. For more information, see SVL_QUERY_METRICS.

SVL_QUERY_METRICS_SUMMARY

Columns: step_label, scan_row_count, join_row_count, nested_loop_join_row_count, return_row_count, spectrum_scan_row_count, spectrum_scan_size_mb. For more information, see SVL_QUERY_METRICS_SUMMARY.

SVL_QUERY_REPORT

Columns: rows, bytes, label, rows_pre_filter. For more information, see SVL_QUERY_REPORT.

SVL_QUERY_SUMMARY

Columns: rows, bytes, rows_pre_filter. For more information, see SVL_QUERY_SUMMARY.

SVL_S3LIST

Columns: bucket, prefix, max_file_size, avg_file_size. For more information, see SVL_S3LIST.

SVL_S3LOG

Columns: message. For more information, see SVL_S3LOG.

SVL_S3PARTITION

Columns: rows, bytes, label, rows_pre_filter. For more information, see SVL_S3PARTITION.

SVL_S3PARTITION_SUMMARY

Columns: total_partitions, qualified_partitions, min_assigned_partitions, max_assigned_partitions, avg_assigned_partitions. For more information, see SVL_S3PARTITION_SUMMARY.

SVL_S3QUERY

Columns: external_table_name, file_format, s3_scanned_rows, s3_scanned_bytes, s3query_returned_rows, s3query_returned_bytes. For more information, see SVL_S3QUERY.

SVL_S3QUERY_SUMMARY

Columns: external_table_name, file_format, s3_scanned_rows, s3_scanned_bytes, s3query_returned_rows, s3query_returned_bytes. For more information, see SVL_S3QUERY_SUMMARY.

SVL_S3RETRIES

Columns: file_size, location, message. For more information, see SVL_S3RETRIES.

SVL_SPECTRUM_SCAN_ERROR

Columns: location, rowid, colname, original_value, modified_value. For more information, see SVL_SPECTRUM_SCAN_ERROR.

SVL_STATEMENTTEXT

Columns: type, text. For more information, see SVL_STATEMENTTEXT.

SVL_STORED_PROC_CALL

Columns: querytxt. For more information, see SVL_STORED_PROC_CALL.

SVL_STORED_PROC_MESSAGES

Columns: querytext. For more information, see SVL_STORED_PROC_MESSAGES.

SVL_UDF_LOG

Columns: funcname. For more information, see SVL_UDF_LOG.

SVV_DISKUSAGE

Columns: name, col, tbl, blocknum, num_values, minvalue, maxvalue. For more information, see SVV_DISKUSAGE.

SVV_QUERY_STATE

Columns: rows, bytes, label. For more information, see SVV_QUERY_STATE.

SVV_TABLE_INFO

Columns: table_id, table. For more information, see SVV_TABLE_INFO.

SVV_TRANSACTIONS

Columns: relation. For more information, see SVV_TRANSACTIONS.

Considerations and limitations

The following are considerations and limitations that apply to views created in the Data Catalog.

  • You can’t create a Data Catalog view that is based off of another view.

  • You can only have 10 base tables in a Data Catalog view.

  • The definer of the view must have full SELECT GRANTABLE permissions on the base tables.

  • Views can only contain Lake Formation objects and built-ins. The following objects are not permitted inside of a view.

    • System tables

    • User-defined functions (UDFs)

    • Redshift tables, views, materialized views, and late binding views that aren’t in a Lake Formation managed data share.

  • Views can’t contain nested Redshift Spectrum tables.

  • Amazon Glue representations of the base objects of a view must be in the same Amazon Web Services account and Region as the view.