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/). # Querying data with federated queries in Amazon Redshift By using *federated queries* in Amazon Redshift, you can query and analyze data across operational databases, data warehouses, and data lakes. With the Federated Query feature, you can integrate queries from Amazon Redshift on live data in external databases with queries across your Amazon Redshift and Amazon S3 environments. Federated queries can work with external databases in Amazon RDS for PostgreSQL, Amazon Aurora PostgreSQL-Compatible Edition, Amazon RDS for MySQL, and Amazon Aurora MySQL-Compatible Edition. You can use federated queries to incorporate live data as part of your business intelligence (BI) and reporting applications. For example, to make data ingestion to Amazon Redshift easier you can use federated queries to do the following: + Query operational databases directly. + Apply transformations quickly. + Load data into the target tables without the need for complex extract, transform, load (ETL) pipelines. To reduce data movement over the network and improve performance, Amazon Redshift distributes part of the computation for federated queries directly into the remote operational databases. Amazon Redshift also uses its parallel processing capacity to support running these queries, as needed. When running federated queries, Amazon Redshift first makes a client connection to the RDS or Aurora DB cluster DB instance from the leader node to retrieve table metadata. From a compute node, Amazon Redshift issues subqueries with a predicate pushed down and retrieves the result rows. Amazon Redshift then distributes the result rows among the compute nodes for further processing. Details about queries sent to the Amazon Aurora PostgreSQL database or Amazon RDS for PostgreSQL database are logged in the system view [SVL\$1FEDERATED\$1QUERY](r_SVL_FEDERATED_QUERY.md). **Topics** + [Getting started with using federated queries to PostgreSQL](getting-started-federated.md) + [Getting started using federated queries to PostgreSQL with AWS CloudFormation](getting-started-federated-CF.md) + [Getting started with using federated queries to MySQL](getting-started-federated-mysql.md) + [Creating a secret and an IAM role to use federated queries](federated-create-secret-iam-role.md) + [Examples of using a federated query](federated_query_example.md) + [Data type differences between Amazon Redshift and supported PostgreSQL and MySQL databases](federated-data-types.md) + [Considerations when accessing federated data with Amazon Redshift](federated-limitations.md) # Getting started with using federated queries to PostgreSQL To create a federated query, you follow this general approach: 1. Set up connectivity from your Amazon Redshift cluster to your Amazon RDS or Aurora PostgreSQL DB instance. To do this, make sure that your RDS PostgreSQL or Aurora PostgreSQL DB instance can accept connections from your Amazon Redshift cluster. We recommend that your Amazon Redshift cluster and Amazon RDS or Aurora PostgreSQL instance be in the same virtual private cloud (VPC) and subnet group. This way, you can add the security group for the Amazon Redshift cluster to the inbound rules of the security group for your RDS or Aurora PostgreSQL DB instance. You can also set up VPC peering or other networking that allows Amazon Redshift to make connections to your RDS or Aurora PostgreSQL instance. For more information about VPC networking, see the following. + [What is VPC peering?](https://docs.aws.amazon.com/vpc/latest/peering/what-is-vpc-peering.html) in the *Amazon VPC Peering Guide* + [Working with a DB instance in a VPC ](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_VPC.WorkingWithRDSInstanceinaVPC.html) in the *Amazon RDS User Guide* **Note** There are cases where you must enable enhanced VPC routing: For example, if your Amazon Redshift cluster is in a different VPC than your RDS or Aurora PostgreSQL instance, or if they're in the same VPC and your routes require it. Otherwise, you might receive timeout errors when you run a federated query. 1. Set up secrets in AWS Secrets Manager for your RDS PostgreSQL and Aurora PostgreSQL databases. Then reference the secrets in AWS Identity and Access Management (IAM) access policies and roles. For more information, see [Creating a secret and an IAM role to use federated queries](federated-create-secret-iam-role.md). **Note** If your cluster uses enhanced VPC routing, you might need to configure an interface VPC endpoint for AWS Secrets Manager. This is necessary when the VPC and subnet of your Amazon Redshift cluster don’t have access to the public AWS Secrets Manager endpoint. When you use a VPC interface endpoint, communication between the Amazon Redshift cluster in your VPC and AWS Secrets Manager is routed privately from your VPC to the endpoint interface. For more information, see [Creating an interface endpoint](https://docs.aws.amazon.com/vpc/latest/userguide/vpce-interface.html#create-interface-endpoint) in the *Amazon VPC User Guide*. 1. Apply the IAM role that you previously created to the Amazon Redshift cluster. For more information, see [Creating a secret and an IAM role to use federated queries](federated-create-secret-iam-role.md). 1. Connect to your RDS PostgreSQL and Aurora PostgreSQL databases with an external schema. For more information, see [CREATE EXTERNAL SCHEMA](r_CREATE_EXTERNAL_SCHEMA.md). For examples on how to use federated query, see [Examples of using a federated query](federated_query_example.md). 1. Run your SQL queries referencing the external schema that references your RDS PostgreSQL and Aurora PostgreSQL databases. # Getting started using federated queries to PostgreSQL with AWS CloudFormation You can use federated queries to query across operational databases. In this getting-started guide, you can automate setup by using a sample AWS CloudFormation stack to enable a federated query from an Amazon Redshift cluster to an Aurora PostgreSQL serverless database. You can get up and running quickly without having to run SQL statements to provision your resources. The stack creates an external schema, referencing your Aurora PostgreSQL instance, which includes tables with sample data. You can query tables in the external schema from your Redshift cluster. If instead you want to get started with federated queries by running SQL statements to set up an external schema, without using CloudFormation, see [Getting started with using federated queries to PostgreSQL](getting-started-federated.md). Before running the CloudFormation stack for federated queries, make sure that you have an Amazon Aurora PostgreSQL-Compatible Edition serverless database with the Data API turned on. You can turn on the Data API in the database properties. If you can't find the setting, double-check that you are running a serverless instance of Aurora PostgreSQL. Also make sure that you have a Amazon Redshift cluster that uses RA3 nodes. We recommend that both the Redshift cluster and serverless Aurora PostgreSQL instance are in the same virtual private cloud (VPC) and subnet group. This way, you can add the security group for the Amazon Redshift cluster to the inbound rules of the security group for your Aurora PostgreSQL database instance. For more information about getting started setting up an Amazon Redshift cluster, see [Get started with Amazon Redshift provisioned data warehouses](https://docs.aws.amazon.com/redshift/latest/gsg/new-user.html). For more information about setting up resources with CloudFormation, see [What is AWS CloudFormation?](https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/Welcome.html). For more information about setting up an Aurora DB cluster database, see [Creating an Aurora DB cluster Serverless v1 DB cluster](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-serverless.create.html). ## Launching a CloudFormation stack for Redshift federated queries Use the following procedure to launch your CloudFormation stack for Amazon Redshift to enable federated queries. Before doing so, make sure you have your Amazon Redshift cluster and your serverless Aurora PostgreSQL instance set up. **To launch your CloudFormation stack for federated queries** 1. Click [https://console.aws.amazon.com/cloudformation/home?#/stacks/new?stackName=FederatedQuery&templateURL=https://s3.amazonaws.com/redshift-downloads/docs-downloads/FederatedQuery.yml](https://console.aws.amazon.com/cloudformation/home?#/stacks/new?stackName=FederatedQuery&templateURL=https://s3.amazonaws.com/redshift-downloads/docs-downloads/FederatedQuery.yml) here to launch the CloudFormation service in the AWS Management Console. If you are prompted, sign in. The stack creation process starts, referencing a CloudFormation template file, which is stored in Amazon S3. A CloudFormation *template* is a text file in JSON format that declares AWS resources that make up a stack. 1. Choose **Next** to enter the stack details. 1. Under **Parameters**, for the cluster, enter the following: + The Amazon Redshift cluster name, for example **ra3-consumer-cluster** + A specific database name, for example **dev** + The name of a database user, for example **consumeruser** Also enter the parameters for the Aurora DB cluster database, including the user, database name, port, and endpoint. We recommend using a test cluster and test serverless database, because the stack creates several database objects. Choose **Next**. The stack options appear. 1. Choose **Next** to accept the default settings. 1. Under **Capabilities**, choose **I acknowledge that AWS CloudFormation might create IAM resources.** 1. Choose **Create stack**. Choose **Create stack**. CloudFormation provisions the template resources, which takes about 10 minutes, and creates an external schema. If an error occurs while the stack is created, do the following: + View the CloudFormation **Events** tab for information that can help you resolve the error. + Make sure that you entered the correct name, database name, and database user name for the Redshift cluster. Also check the parameters for the Aurora PostgreSQL instance. + Make sure that your cluster has RA3 nodes. + Make sure that your database and Redshift cluster are in the same subnet and security group. ## Querying data from the external schema To use the following procedure, make sure that you have the required permissions for running queries on the cluster and the database described. **To query an external database with federated query** 1. Connect to the Redshift database that you entered when you created the stack, using a client tool such as the Redshift query editor. 1. Query for the external schema created by the stack. ``` select * from svv_external_schemas; ``` The [SVV\$1EXTERNAL\$1SCHEMAS](r_SVV_EXTERNAL_SCHEMAS.md) view returns information about available external schemas. In this case, the external schema created by the stack is returned, `myfederated_schema`. You might also have other external schemas returned, if you have any set up. The view also returns the schema's associated database. The database is the Aurora DB cluster database that you entered when you created the stack. The stack adds a table to the Aurora DB cluster database, called `category`, and another table called `sales`. 1. Run SQL queries on tables in the external schema that references your Aurora PostgreSQL database. The following example shows a query. ``` SELECT count(*) FROM myfederated_schema.category; ``` The `category` table returns several records. You can also return records from the `sales` table. ``` SELECT count(*) FROM myfederated_schema.sales; ``` For more examples, see [Examples of using a federated query](federated_query_example.md). # Getting started with using federated queries to MySQL To create a federated query to MySQL databases, you follow this general approach: 1. Set up connectivity from your Amazon Redshift cluster to your Amazon RDS or Aurora MySQL DB instance. To do this, make sure that your RDS MySQL or Aurora MySQL DB instance can accept connections from your Amazon Redshift cluster. We recommend that your Amazon Redshift cluster and Amazon RDS or Aurora MySQL instance be in the same virtual private cloud (VPC) and subnet group. This way, you can add the security group for the Amazon Redshift cluster to the inbound rules of the security group for your RDS or Aurora MySQL DB instance. You can also set up VPC peering or other networking that allows Amazon Redshift to make connections to your RDS or Aurora MySQL instance. For more information about VPC networking, see the following. + [What is VPC peering?](https://docs.aws.amazon.com/vpc/latest/peering/what-is-vpc-peering.html) in the *Amazon VPC Peering Guide* + [Working with a DB instance in a VPC ](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_VPC.WorkingWithRDSInstanceinaVPC.html) in the *Amazon RDS User Guide* **Note** If your Amazon Redshift cluster is in a different VPC than your RDS or Aurora MySQL instance, then enable enhanced VPC routing. Otherwise, you might receive timeout errors when you run a federated query. 1. Set up secrets in AWS Secrets Manager for your RDS MySQL and Aurora MySQL databases. Then reference the secrets in AWS Identity and Access Management (IAM) access policies and roles. For more information, see [Creating a secret and an IAM role to use federated queries](federated-create-secret-iam-role.md). **Note** If your cluster uses enhanced VPC routing, you might need to configure an interface VPC endpoint for AWS Secrets Manager. This is necessary when the VPC and subnet of your Amazon Redshift cluster don't have access to the public AWS Secrets Manager endpoint. When you use a VPC interface endpoint, communication between the Amazon Redshift cluster in your VPC and AWS Secrets Manager is routed privately from your VPC to the endpoint interface. For more information, see [Creating an interface endpoint](https://docs.aws.amazon.com/vpc/latest/userguide/vpce-interface.html#create-interface-endpoint) in the *Amazon VPC User Guide*. 1. Apply the IAM role that you previously created to the Amazon Redshift cluster. For more information, see [Creating a secret and an IAM role to use federated queries](federated-create-secret-iam-role.md). 1. Connect to your RDS MySQL and Aurora MySQL databases with an external schema. For more information, see [CREATE EXTERNAL SCHEMA](r_CREATE_EXTERNAL_SCHEMA.md). For examples on how to use federated queries, see [Example of using a federated query with MySQL](federated_query_example.md#federated_query_example_mysql). 1. Run your SQL queries referencing the external schema that references your RDS MySQL and Aurora MySQL databases. # Creating a secret and an IAM role to use federated queries The following steps show how to create a secret and an IAM role to use with federated queries. ## Prerequisites Make sure that you have the following prerequisites to create a secret and an IAM role to use with federated queries: + An RDS PostgreSQL, Aurora PostgreSQL DB instance, RDS MySQL, or Aurora MySQL DB instance with user name and password authentication. + An Amazon Redshift cluster with a cluster maintenance version that supports federated queries. **To create a secret (user name and password) with AWS Secrets Manager** 1. Sign in to the Secrets Manager console with the account that owns your RDS or Aurora DB cluster instance. 1. Choose **Store a new secret**. 1. Choose the **Credentials for RDS database** tile. For **User name** and **Password**, enter values for your instance. Confirm or choose a value for **Encryption key**. Then choose the RDS database that your secret will access. **Note** We recommend using the default encryption key (`DefaultEncryptionKey`). If you use a custom encryption key, the IAM role that is used to access the secret must be added as a key user. 1. Enter a name for the secret, continue with the creation steps with the default choices, and then choose **Store**. 1. View your secret and note the **Secret ARN** value that you created to identify the secret. **To create a security policy using the secret** 1. Sign in to the AWS Management Console and open the IAM console at [https://console.aws.amazon.com/iam/](https://console.aws.amazon.com/iam/). 1. Create a policy with JSON similar to the following. ------ #### [ JSON ] **** ``` { "Version":"2012-10-17", "Statement": [ { "Sid": "AccessSecret", "Effect": "Allow", "Action": [ "secretsmanager:GetResourcePolicy", "secretsmanager:GetSecretValue", "secretsmanager:DescribeSecret", "secretsmanager:ListSecretVersionIds" ], "Resource": "arn:aws:secretsmanager:us-west-2:123456789012:secret:my-rds-secret-VNenFy" }, { "Sid": "VisualEditor1", "Effect": "Allow", "Action": [ "secretsmanager:GetRandomPassword", "secretsmanager:ListSecrets" ], "Resource": "*" } ] } ``` ------ To retrieve the secret, you need list and read actions. We recommend that you restrict the resource to the specific secret that you created. To do this, use the Amazon Resource Name (ARN) of the secret to limit the resource. You can also specify the permissions and resources using the visual editor on the IAM console. 1. Give the policy a name and finish creating it. 1. Navigate to **IAM roles**. 1. Create an IAM role for **Redshift - Customizable**. 1. Either attach the IAM policy you just created to an existing IAM role, or create a new IAM role and attach the policy. 1. On the **Trust relationships** tab of your IAM role, confirm that the role contains the trust entity `redshift.amazonaws.com`. 1. Note the **Role ARN** you created. This ARN has access to the secret. **To attach the IAM role to your Amazon Redshift cluster** 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. On the navigation menu, choose **Clusters**. The clusters for your account in the current AWS Region are listed. 1. Choose the cluster name in the list to view more details about a cluster. 1. For **Actions**, choose **Manage IAM roles**. The **Manage IAM roles** page appears. 1. Add your IAM role to the cluster. # Examples of using a federated query The following examples show how to run a federated query. Run the SQL using your SQL client connected to the Amazon Redshift database. ## Example of using a federated query with PostgreSQL The following example shows how to set up a federated query that references an Amazon Redshift database, an Aurora PostgreSQL database, and Amazon S3. This example illustrates how federated queries work. To run it on your own environment, change it to fit your environment. For prerequisites for doing this, see [Getting started with using federated queries to PostgreSQL](getting-started-federated.md). Create an external schema that references an Aurora PostgreSQL database. ``` CREATE EXTERNAL SCHEMA apg FROM POSTGRES DATABASE 'database-1' SCHEMA 'myschema' URI 'endpoint to aurora hostname' IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-SecretsManager-RO' SECRET_ARN 'arn:aws:secretsmanager:us-west-2:123456789012:secret:federation/test/dataplane-apg-creds-YbVKQw'; ``` Create another external schema that references Amazon S3, which uses Amazon Redshift Spectrum. Also, grant permission to use the schema to `public`. ``` CREATE EXTERNAL SCHEMA s3 FROM DATA CATALOG DATABASE 'default' REGION 'us-west-2' IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-S3'; GRANT USAGE ON SCHEMA s3 TO public; ``` Show the count of rows in the Amazon Redshift table. ``` SELECT count(*) FROM public.lineitem; count ---------- 25075099 ``` Show the count of rows in the Aurora PostgreSQL table. ``` SELECT count(*) FROM apg.lineitem; count ------- 11760 ``` Show the count of rows in Amazon S3. ``` SELECT count(*) FROM s3.lineitem_1t_part; count ------------ 6144008876 ``` Create a view of the tables from Amazon Redshift, Aurora PostgreSQL, and Amazon S3. This view is used to run your federated query. ``` CREATE VIEW lineitem_all AS SELECT l_orderkey,l_partkey,l_suppkey,l_linenumber,l_quantity,l_extendedprice,l_discount,l_tax,l_returnflag,l_linestatus, l_shipdate::date,l_commitdate::date,l_receiptdate::date, l_shipinstruct ,l_shipmode,l_comment FROM s3.lineitem_1t_part UNION ALL SELECT * FROM public.lineitem UNION ALL SELECT * FROM apg.lineitem with no schema binding; ``` Show the count of rows in the view `lineitem_all` with a predicate to limit the results. ``` SELECT count(*) from lineitem_all WHERE l_quantity = 10; count ----------- 123373836 ``` Find out how many sales of one item there were in January of each year. ``` SELECT extract(year from l_shipdate) as year, extract(month from l_shipdate) as month, count(*) as orders FROM lineitem_all WHERE extract(month from l_shipdate) = 1 AND l_quantity < 2 GROUP BY 1,2 ORDER BY 1,2; year | month | orders ------+-------+--------- 1992 | 1 | 196019 1993 | 1 | 1582034 1994 | 1 | 1583181 1995 | 1 | 1583919 1996 | 1 | 1583622 1997 | 1 | 1586541 1998 | 1 | 1583198 2016 | 1 | 15542 2017 | 1 | 15414 2018 | 1 | 15527 2019 | 1 | 151 ``` ## Example of using a mixed-case name To query a supported PostgreSQL remote database that has a mixed-case name of a database, schema, table, or column, then set `enable_case_sensitive_identifier` to `true`. For more information about this session parameter, see [enable\$1case\$1sensitive\$1identifier](r_enable_case_sensitive_identifier.md). ``` SET enable_case_sensitive_identifier TO TRUE; ``` Typically, the database and schema names are in lowercase. The following example shows how you can connect to a supported PostgreSQL remote database that has lowercase names for database and schema and mixed-case names for table and column. Create an external schema that references an Aurora PostgreSQL database that has a lowercase database name (`dblower`) and lowercase schema name (`schemalower`). ``` CREATE EXTERNAL SCHEMA apg_lower FROM POSTGRES DATABASE 'dblower' SCHEMA 'schemalower' URI 'endpoint to aurora hostname' IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-SecretsManager-RO' SECRET_ARN 'arn:aws:secretsmanager:us-west-2:123456789012:secret:federation/test/dataplane-apg-creds-YbVKQw'; ``` In the session where the query runs, set `enable_case_sensitive_identifier` to `true`. ``` SET enable_case_sensitive_identifier TO TRUE; ``` Run a federated query to select all data from the PostgreSQL database. The table (`MixedCaseTab`) and column (`MixedCaseName`) have mixed-case names. The result is one row (`Harry`). ``` select * from apg_lower."MixedCaseTab"; ``` ``` MixedCaseName ------- Harry ``` The following example shows how you can connect to a supported PostgreSQL remote database that has a mixed-case name for the database, schema, table, and column. Set `enable_case_sensitive_identifier` to `true` before you create the external schema. If `enable_case_sensitive_identifier` is not set to `true` before creating the external schema, then a database does not exist error occurs. Create an external schema that references an Aurora PostgreSQL database that has a mixed-case database (`UpperDB`) and schema (`UpperSchema`) name. ``` CREATE EXTERNAL SCHEMA apg_upper FROM POSTGRES DATABASE 'UpperDB' SCHEMA 'UpperSchema' URI 'endpoint to aurora hostname' IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-SecretsManager-RO' SECRET_ARN 'arn:aws:secretsmanager:us-west-2:123456789012:secret:federation/test/dataplane-apg-creds-YbVKQw'; ``` Run a federated query to select all data from the PostgreSQL database. The table (`MixedCaseTab`) and column (`MixedCaseName`) have mixed-case names. The result is one row (`Harry`). ``` select * from apg_upper."MixedCaseTab"; ``` ``` MixedCaseName ------- Harry ``` ## Example of using a federated query with MySQL The following example shows how to set up a federated query that references an Aurora MySQL database. This example illustrates how federated queries works. To run it on your own environment, change it to fit your environment. For prerequisites for doing this, see [Getting started with using federated queries to MySQL](getting-started-federated-mysql.md). This example depends on the following prerequisites: + A secret that was set up in Secrets Manager for the Aurora MySQL database. This secret is referenced in IAM access policies and roles. For more information, see [Creating a secret and an IAM role to use federated queries](federated-create-secret-iam-role.md). + A security group that is set up linking Amazon Redshift and Aurora MySQL. Create an external schema that references an Aurora MySQL database. ``` CREATE EXTERNAL SCHEMA amysql FROM MYSQL DATABASE 'functional' URI 'endpoint to remote hostname' IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-SecretsManager-RO' SECRET_ARN 'arn:aws:secretsmanager:us-west-2:123456789012:secret:federation/test/dataplane-apg-creds-YbVKQw'; ``` Run an example SQL select of the Aurora MySQL table to display one row from the employees table in Aurora MySQL. ``` SELECT level FROM amysql.employees LIMIT 1; level ------- 8 ``` # Data type differences between Amazon Redshift and supported PostgreSQL and MySQL databases The following table shows the mapping of an Amazon Redshift data type to a corresponding Amazon RDS PostgreSQL or Aurora PostgreSQL data type. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/federated-data-types.html) The following RDS PostgreSQL and Aurora PostgreSQL data types are converted to VARCHAR(64K) in Amazon Redshift: + JSON, JSONB + Arrays + BIT, BIT VARYING + BYTEA + Composite types + Date and time types INTERVAL, TIME, TIME WITH TIMEZONE + Enumerated types + Monetary types + Network address types + Numeric types SERIAL, BIGSERIAL, SMALLSERIAL, and MONEY + Object identifier types + pg\$1lsn type + Pseudotypes + Range types + Text search types + TXID\$1SNAPSHOT + UUID + XML type The following table shows the mapping of an Amazon Redshift data type to a corresponding Amazon RDS MySQL or Aurora MySQL data type. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/federated-data-types.html) An error results when TIME data is out of range (00:00:00 – 24:00:00). The following RDS MySQL and Aurora MySQL data types are converted to VARCHAR(64K) in Amazon Redshift: + BIT + BINARY + VARBINARY + TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB + TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT + ENUM + SET + SPATIAL # Considerations when accessing federated data with Amazon Redshift Some Amazon Redshift features don't support access to federated data. You can find related limitations and considerations following. The following are limitations and considerations when using federated queries with Amazon Redshift: + Federated queries support read access to external data sources. You can't write or create database objects in the external data source. + In some cases, you might access an Amazon RDS or Aurora DB cluster database in a different AWS Region than Amazon Redshift. In these cases, you typically incur network latency and billing charges for transferring data across AWS Regions. We recommend using an Aurora global database with a local endpoint in the same AWS Region as your Amazon Redshift cluster. Aurora global databases use dedicated infrastructure for storage-based replication across any two AWS Regions with typical latency of less than 1 second. + Consider the cost of accessing Amazon RDS or Aurora DB cluster. For example, when using this feature to access Aurora DB cluster, Aurora DB cluster charges are based on IOPS. + Federated queries don't enable access to Amazon Redshift from RDS or Aurora DB cluster. + Federated queries are only available in AWS Regions where both Amazon Redshift and Amazon RDS or Aurora DB cluster are available. + Federated queries currently don't support `ALTER SCHEMA`. To change a schema, use `DROP` and then `CREATE EXTERNAL SCHEMA`. + Federated queries don't work with concurrency scaling. + Federated queries currently don't support access through a PostgreSQL foreign data wrapper. + Federated queries to RDS MySQL or Aurora MySQL support transaction isolation at the READ COMMITTED level. + If not specified, Amazon Redshift connects to RDS for MySQL or Aurora MySQL on port 3306. Confirm the MySQL port number before creating an external schema for MySQL. + If not specified, Amazon Redshift connects to RDS PostgreSQL or Aurora PostgreSQL on port 5432. Confirm the PostgreSQL port number before creating an external schema for PostgreSQL. + When fetching TIMESTAMP and DATE data types from MySQL, zero values are treated as NULL. + If an Aurora DB cluster database reader endpoint is used, an "invalid snapshot" error can occur. This can be avoided by one of the following methods: + Use a specific Aurora DB cluster instance endpoint (instead of using the Aurora DB cluster cluster endpoint). This method uses REPEATABLE READ transaction isolation for the results from the PostgreSQL database. + Use an Aurora DB cluster reader endpoint and set `pg_federation_repeatable_read` to false for the session. This method uses READ COMMITTED transaction isolation for the results from the PostgreSQL database. For more information about Aurora DB cluster reader endpoints, see [Types of Aurora DB cluster endpoints](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Overview.Endpoints.html#Aurora.Overview.Endpoints.Types) in the *Amazon Aurora User Guide*. For information about `pg_federation_repeatable_read`, see [pg\$1federation\$1repeatable\$1read](r_pg_federation_repeatable_read.md). The following are considerations for transactions when working with federated queries to PostgreSQL databases: + If a query consists of federated tables, the leader node starts a READ ONLY REPEATABLE READ transaction on the remote database. This transaction remains for the duration of the Amazon Redshift transaction. + The leader node creates a snapshot of the remote database by calling `pg_export_snapshot` and makes a read lock on the affected tables. + A compute node starts a transaction and uses the snapshot created at the leader node to issue queries to the remote database. ## Supported versions of federated databases An Amazon Redshift external schema can reference a database in an external RDS PostgreSQL or Aurora PostgreSQL. When it does, these limitations apply: + When creating an external schema referencing Aurora DB cluster, the Aurora PostgreSQL database must be at version 9.6, or later. + When creating an external schema referencing Amazon RDS, the Amazon RDS PostgreSQL database must be at version 9.6, or later. An Amazon Redshift external schema can reference a database in an external RDS MySQL or Aurora MySQL. When it does, these limitations apply: + When creating an external schema referencing Aurora DB cluster, the Aurora MySQL database must be at version 5.6 or later. + When creating an external schema referencing Amazon RDS, the RDS MySQL database must be at version 5.6 or later.