Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/). # Cross-database queries This topic describes *cross-database queries*, which are queries that operate on multiple Amazon Redshift databases within a single Amazon Redshift cluster. By using cross-database queries in Amazon Redshift, you can query and write across databases in an Amazon Redshift cluster. With cross-database queries, you can query and write 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 from the same data warehouse. With cross-database queries, you can do the following: + **Query data across databases in your 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 have read access only to those database objects. 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. Doing this can help you query and join data that is spread across multiple databases in your Amazon Redshift cluster quickly and easily. 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 set up granular table-level read access controls for users by using standard Amazon Redshift SQL commands. By doing so, you can help ensure that users see only the relevant subsets of the data that they have permissions for. + **Write data across databases in your Amazon Redshift cluster.**. You can write from databases that you are connected to, and also write from any other database that you have permissions to. When you have write permissions on database objects on any other unconnected databases, you can use cross-database queries to write data from any database on your Amazon Redshift cluster without having to connect to that specific database. Doing this can help you with complex write operations while quickly and easily joining data that is spread across multiple databases in your Amazon Redshift cluster. You can also join datasets from multiple databases in a single query and write the data using different extract-transform-load (ETL) or analytics tools. You can continue to set up granular table-level write access controls for users by using standard Amazon Redshift SQL commands. This ensures that users see only the relevant subsets of the data that they have permissions for. + **Query objects**. You can query other database objects using fully qualified object names expressed with the three-part notation. The full path to any database object consists of three components: database name, schema, and name of the object. You can access any object from any other database using the full path notation, `database_name.schema_name.object_name`. To access a particular column, use `database_name.schema_name.object_name.column_name`. 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 can access the other database object using the external schema notation, `external_schema_name.object_name`. In the same read-only query, you can query various database objects, such as user tables, regular views, materialized views, and late-binding views from other databases. + **Manage permissions**. Users with access privileges for objects in any databases in an Amazon Redshift cluster can query and write data to those objects. You grant privileges to users and user groups using the [GRANT](r_GRANT.md) command. You can also revoke privileges using the [REVOKE](r_REVOKE.md) command when a user no longer requires the access to specific database objects. + **Work with metadata and BI tools.** 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](r_CREATE_EXTERNAL_SCHEMA.md) command. After external schema references are created, Amazon Redshift shows the tables under the schema of the other database in [SVV\$1EXTERNAL\$1TABLES](r_SVV_EXTERNAL_TABLES.md) and [SVV\$1EXTERNAL\$1COLUMNS](r_SVV_EXTERNAL_COLUMNS.md) for the tools to explore the metadata. To integrate cross-database query with BI tools, you can use the following system views. These help you view information about the metadata of objects in the connected and other databases on the Amazon Redshift cluster. Following are system views that show all Amazon Redshift objects and external objects of all databases in your Amazon Redshift cluster: + [SVV\$1ALL\$1COLUMNS](r_SVV_ALL_COLUMNS.md) + [SVV\$1ALL\$1SCHEMAS](r_SVV_ALL_SCHEMAS.md) + [SVV\$1ALL\$1TABLES](r_SVV_ALL_TABLES.md) Following are system views that show all Amazon Redshift objects of all databases in your Amazon Redshift cluster: + [SVV\$1REDSHIFT\$1COLUMNS](r_SVV_REDSHIFT_COLUMNS.md) + [SVV\$1REDSHIFT\$1DATABASES](r_SVV_REDSHIFT_DATABASES.md) + [SVV\$1REDSHIFT\$1FUNCTIONS](r_SVV_REDSHIFT_FUNCTIONS.md) + [SVV\$1REDSHIFT\$1SCHEMAS](r_SVV_REDSHIFT_SCHEMAS.md) + [SVV\$1REDSHIFT\$1TABLES](r_SVV_REDSHIFT_TABLES.md) **Topics** + [Considerations](cross-database_usage.md) + [Limitations](cross-database_limitation.md) + [Cross-database query examples](cross-database_example.md) + [Using cross-database queries with the query editor](cross-database_console.md) # Considerations This topic describes usage details for cross-database queries in Amazon Redshift. When you work with the cross-database query feature in Amazon Redshift, consider the following: + Amazon Redshift supports cross-database query on all ra3 node types and serverless namespaces. + Amazon Redshift supports joining data from tables or views across one or more databases in the same Amazon Redshift cluster. + All queries in a transaction on the connected database read data in the same state of the other database as the data was at the beginning of the transaction. This approach helps to provide query transactional consistency across databases. Amazon Redshift supports transactional consistency for cross-database queries. + To get metadata across databases, use SVV\$1ALL\$1 and SVV\$1REDSHIFT\$1 metadata views. You can't use the three-part notation or external schemas to query cross-database metadata tables or views under information\$1schema and pg\$1catalog. # Limitations This topic describes limitations for cross-database queries in Amazon Redshift. When you work with the cross-database query feature in Amazon Redshift, be aware of the limitations following: + You can't query views that are created on other databases that refer to objects of yet another database. + You can't write to external schemas to database objects on any other unconnected databases. + You can only create late-binding and materialized views on objects of other databases in the cluster. You can't create regular views on objects of other databases in the cluster. + Amazon Redshift doesn't support tables with column-level privileges for cross-database queries. + Running cross-database queries on tables with interleaved sort keys isn't supported. # Cross-database query examples This topic contains examples for how to use cross-database queries. Cross-database queries are queries that operate on multiple databases within a single Amazon Redshift cluster. Use the following examples to help learn how to set up a cross-database query that references an Amazon Redshift database. To start, create databases `db1` and `db2` and users `user1` and `user2` in your Amazon Redshift cluster. For more information, see [CREATE DATABASE](r_CREATE_DATABASE.md) and [CREATE USER](r_CREATE_USER.md). ``` --As user1 on db1 CREATE DATABASE db1; CREATE DATABASE db2; CREATE USER user1 PASSWORD 'Redshift01'; CREATE USER user2 PASSWORD 'Redshift01'; ``` As `user1` on `db1`, create a table, grant access privileges to `user2`, and insert values into `table1`. For more information, see [GRANT](r_GRANT.md) and [INSERT](r_INSERT_30.md). ``` --As user1 on db1 CREATE TABLE table1 (c1 int, c2 int, c3 int); GRANT SELECT ON table1 TO user2; INSERT INTO table1 VALUES (1,2,3),(4,5,6),(7,8,9); ``` As `user2` on `db2`, run a cross-database query in `db2` using the three-part notation. ``` --As user2 on db2 SELECT * from db1.public.table1 ORDER BY c1; c1 | c2 | c3 ---+-----+---- 1 | 2 | 3 4 | 5 | 6 7 | 8 | 9 (3 rows) ``` Now grant write privileges to `user2`, and insert values into `table1` in `db1` as `user2` . ``` --As user1 on db1 GRANT INSERT ON table1 TO user2; ``` As `user2` on `db2`, run a cross-database query in `db2` using the three-part notation to insert data into `table1` in `db1`. ``` --As user2 on db2 INSERT INTO db1.public.table1 VALUES (10,11,12); SELECT * from db1.public.table1 ORDER BY c1; c1 | c2 | c3 ----+------+---- 1 | 2 | 3 4 | 5 | 6 7 | 8 | 9 10 | 11 | 12 (4 rows) ``` As `user2` on `db2`, create an external schema and run a cross-database query in `db2` using the external schema notation. ``` --As user2 on db2 CREATE EXTERNAL SCHEMA db1_public_sch FROM REDSHIFT DATABASE 'db1' SCHEMA 'public'; SELECT * FROM db1_public_sch.table1 ORDER BY c1; c1 | c2 | c3 ----+----+---- 1 | 2 | 3 4 | 5 | 6 7 | 8 | 9 10 | 11 | 12 (4 rows) ``` To create different views and grant permissions to those views, as `user1` on `db1`, do the following. ``` --As user1 on db1 CREATE VIEW regular_view AS SELECT c1 FROM table1; GRANT SELECT ON regular_view TO user2; CREATE MATERIALIZED VIEW mat_view AS SELECT c2 FROM table1; GRANT SELECT ON mat_view TO user2; CREATE VIEW late_bind_view AS SELECT c3 FROM public.table1 WITH NO SCHEMA BINDING; GRANT SELECT ON late_bind_view TO user2; ``` As `user2` on `db2`, run the following cross-database query using the three-part notation to view the particular view. ``` --As user2 on db2 SELECT * FROM db1.public.regular_view; c1 ---- 1 4 7 10 (4 rows) SELECT * FROM db1.public.mat_view; c2 ---- 2 5 8 11 (4 rows) SELECT * FROM db1.public.late_bind_view; c3 ---- 3 6 9 12 (4 rows) ``` As `user2` on `db2`, run the following cross-database query using the external schema notation to query the late-binding view. ``` --As user2 on db2 SELECT * FROM db1_public_sch.late_bind_view; c3 ---- 3 6 9 12 (4 rows) ``` As `user2` on `db2`, run the following command using connected tables in a single query. ``` --As user2 on db2 CREATE TABLE table1 (a int, b int, c int); INSERT INTO table1 VALUES (1,2,3), (4,5,6), (7,8,9); SELECT a AS col_1, (db1.public.table1.c2 + b) AS sum_col2, (db1.public.table1.c3 + c) AS sum_col3 FROM db1.public.table1, table1 WHERE db1.public.table1.c1 = a; col_1 | sum_col2 | sum_col3 ------+----------+---------- 1 | 4 | 6 4 | 10 | 12 7 | 16 | 18 (3 rows) ``` The following example lists all databases on the cluster. ``` select database_name, database_owner, database_type from svv_redshift_databases where database_name in ('db1', 'db2'); database_name | database_owner | database_type ---------------+----------------+--------------- db1 | 100 | local db2 | 100 | local (2 rows) ``` The following example lists all Amazon Redshift schemas of all databases on the cluster. ``` select database_name, schema_name, schema_owner, schema_type from svv_redshift_schemas where database_name in ('db1', 'db2'); database_name | schema_name | schema_owner | schema_type ---------------+--------------------+--------------+------------- db1 | pg_catalog | 1 | local db1 | public | 1 | local db1 | information_schema | 1 | local db2 | pg_catalog | 1 | local db2 | public | 1 | local db2 | information_schema | 1 | local (6 rows) ``` The following example lists all Amazon Redshift tables or views of all databases on the cluster. ``` select database_name, schema_name, table_name, table_type from svv_redshift_tables where database_name in ('db1', 'db2') and schema_name in ('public'); database_name | schema_name | table_name | table_type ---------------+-------------+---------------------+------------ db1 | public | late_bind_view | VIEW db1 | public | mat_view | VIEW db1 | public | mv_tbl__mat_view__0 | TABLE db1 | public | regular_view | VIEW db1 | public | table1 | TABLE db2 | public | table2 | TABLE (6 rows) ``` The following example lists all Amazon Redshift and external schemas of all databases on the cluster. ``` select database_name, schema_name, schema_owner, schema_type from svv_all_schemas where database_name in ('db1', 'db2') ; database_name | schema_name | schema_owner | schema_type ---------------+--------------------+--------------+------------- db1 | pg_catalog | 1 | local db1 | public | 1 | local db1 | information_schema | 1 | local db2 | pg_catalog | 1 | local db2 | public | 1 | local db2 | information_schema | 1 | local db2 | db1_public_sch | 1 | external (7 rows) ``` The following example lists all Amazon Redshift and external tables of all databases on the cluster. ``` select database_name, schema_name, table_name, table_type from svv_all_tables where database_name in ('db1', 'db2') and schema_name in ('public'); database_name | schema_name | table_name | table_type ---------------+-------------+---------------------+------------ db1 | public | regular_view | VIEW db1 | public | mv_tbl__mat_view__0 | TABLE db1 | public | mat_view | VIEW db1 | public | late_bind_view | VIEW db1 | public | table1 | TABLE db2 | public | table2 | TABLE (6 rows) ``` # Using cross-database queries with the query editor This topic explains how to use cross-database queries with the query editor. Cross-database queries are queries that operate on multiple databases within a single 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. When you run cross-database queries on any other unconnected databases, you have read and write access only to those database objects. You can query and write to other database objects using fully qualified object names expressed with three-part notation. The full path to any database object consists of three components: database name, schema, and name of the object. An example is *`database_name.schema_name.object_name`*. **To use cross-database queries with the query editor v2** 1. Sign in to the AWS Management Console and open the Amazon Redshift console at [https://console.aws.amazon.com/redshiftv2/](https://console.aws.amazon.com/redshiftv2/). 1. Create a cluster to use cross-database queries in Amazon Redshift query editor v2. For more information, see [Creating a cluster](https://docs.aws.amazon.com/redshift/latest/mgmt/managing-clusters-console.html#create-cluster.html) in the *Amazon Redshift Management Guide*. 1. Enable access to the query editor with the appropriate permissions. For more information, see [Querying a database using the query editor v2](https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor-v2.html) in the *Amazon Redshift Management Guide*. 1. On the navigation menu, choose **Query editor v2**, then connect to a database in your cluster. When you connect to the query editor v2 for the first time, Amazon Redshift shows the resources for the connected database by default. 1. Choose the other databases that you have access to view database objects for these other databases. To view objects, make sure that you have the appropriate permissions. After you choose a database, Amazon Redshift shows the list of schemas from the database. Select a schema to see the list of database objects within that schema. **Note** Amazon Redshift doesn't directly support query catalog objects that are part of AWS Glue or federated databases. To query these, first create external schemas that refer to those external data sources in each database. Amazon Redshift cross-database queries with three-part notation don't support metadata tables under the schemas `information_schema` and `pg_catalog` because these metadata views are specific to a database. 1. (Optional) Filter the list of tables or views for the schema that you selected.