Querying data across databases (preview) - Amazon Redshift

Querying data across databases (preview)

This is prerelease documentation for the cross-database queries feature for Amazon Redshift, which is in preview release. The documentation and the feature are both subject to change. We recommend that you use this feature only with test clusters, and not in production environments. For preview terms and conditions, see Beta Service Participation in AWS Service Terms.

Cross-database queries in Amazon Redshift enables you to query across databases in an Amazon Redshift cluster. With cross-database queries, you can seamlessly query data from any database in the Amazon Redshift cluster, regardless of which database you are connected to. Cross-database queries eliminate data copies and simplify your data organization to support multiple business groups on the same data warehouse.

  • Querying data across databases in Amazon Redshift cluster

    Not only can you query from databases that you are connected to, you can also read from any other databases that you have permissions to. When you query database objects on any other unconnected databases, you only have read access to those database objects.

    To improve performance of common ETL staging and processing over data that is spread across multiple database in your Amazon Redshift cluster, you can use cross-database queries to access data from any of the databases on your Amazon Redshift cluster without having to connect to that specific database. You can also join datasets from multiple databases in a single query and analyze the data using business intelligence (BI) or analytics tools. You can continue to setup granular table or column level access controls for users via standard Amazon Redshift SQL commands and ensure that users see only the relevant subsets of the data they have permissions for.

  • Querying objects

    You can query other database objects using fully qualified object name expressed with the three-part notation. The full path to any database object consists of three components, such as database name, schema, and name of the object. You can access any object from any other database using the full path notation, such as database_name.schema.object. To access a particular column, use database_name.schema.object.column.

    You can also create an alias for a schema in another database using the external schema notation. This external schema references to another database and schema pair. Query and access the other database object using the external schema notation, such as schema_name.object.

    You can query various database objects, such as user tables, regular views, materialized views, and late binding views from other databases in the same read-only query.

  • Managing permissions

    • Users with access privileges on objects in any databases in the Amazon Redshift cluster can query those objects. Privileges are granted to users and user groups using GRANT. For more information, see the GRANT command.

    • You can also revoke privileges using REVOKE when a user no longer requires to be given the access to specific database objects. For more information, see the REVOKE command.

  • Working with metadata

    You can create an external schema to refer to a schema in another Amazon Redshift database within the same Amazon Redshift cluster. For information, see CREATE EXTERNAL SCHEMA command. Once external schema references are created, Amazon Redshift shows the tables under the schema of the other database in SVV_EXTERNAL_TABLES and SVV_EXTERNAL_COLUMNS for the tools to explore the metadata.

Note

When working with the preview:

  • New Amazon Redshift clusters must be created with the PREVIEW_CROSSDB maintenance track. For more information about preview tracks, see Choosing cluster maintenance tracks.

  • Note that this feature is currently available for test purposes only. Don't use the feature for production use cases.

  • You can't switch an existing Amazon Redshift cluster from the current or trailing state to this preview track, or vice versa. However, you can restore data from a cluster snapshot running on the current or trailing track.

  • The cross-database queries preview period is expected to run until November 30, 2020. Unless extended, clusters created on this track will be automatically deleted by AWS on this date.

  • You can use a cluster in any AWS Regions with Amazon Redshift RA3 instance types to preview the cross-database queries feature.

  • For any questions, issues, or feedback related to the preview features during the preview period, email redshift-crossdb@amazon.com or open a support case with Amazon Support.