Amazon Redshift Federated Query with RDS PostgreSQL

   Go back to the Task List

  « 6. Launch Redshift Cluster    8: Clean up »

7. Federate PostgreSQL Database

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.

    Redshift Query

  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.

    Redshift Spectrum

    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.

    Redshift Spectrum

    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.

    Redshift Spectrum

    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.