You configure federation with PostgreSQL Database which allows the fetch PostgreSQL data in Redshift and also make join with the local tables.

1. In the Redshift Management Console, click on the EDITOR menu in the left. It will open the Connect to database popup. Select Create new connection option. Select dojoredshift as the cluster. Type in dev for the database. Type in awsuser for the user and Password1! for the password. Finally click on the Connect to database button to connect to the cluster.

   

2. On the next screen, run the following statement to create external schema dojoschema for the PostgreSQL database dojodatabase in Redshift. Replace {RDS_ENDPOINT} with PostgreSQL RDS endpoint you made note of in the earlier step. Replace {REDSHIFT_IAM_ROLE} with ARN of the IAM Role you created for Redshift Cluster. Finally, Replace {SECRET_KEY} with ARN of the Secrets Manager secret ARN you configured in the earlier steps.

   

   CREATE EXTERNAL SCHEMA IF NOT EXISTS dojoschema
   FROM POSTGRES
   DATABASE 'dojodatabase'
   URI '{RDS_ENDPOINT}'
   IAM_ROLE '{REDSHIFT_IAM_ROLE}'
   SECRET_ARN '{SECRET_KEY}' 
   

   `

3. The external schema is ready. Run the following SQL Statement to fetch data from the customers table in dojoschema. It will eventually fetch data from the customers table in dojodatabase from PostgreSQL.

   

   select * from dojoschema.customers
   

   `

4. You can make join between local tables and external schema / federated tables. Run the following SQL Statement to join payments local table with customers table in the external schema dojoschema.

   

   select * 
   from payments, dojoschema.customers
   where dojoschema.customers.customerNumber = payments.customerNumber
   

   `

5. You saw use of federated query where local tables can be joined with external PostgreSQL tables. This finishes the workshop. Follow the next step to clean-up the resources so that you don’t incur any cost post the workshop.