How to access data of Redshift cluster from PostgreSQL RDS

Setting up DBLink

  • First we need to enable the DBLink extensions on the PostgreSQL RDS instance.
  • Next step is the main step which creates the link to the Amazon Redshift cluster and PostgreSQL RDS instance.
  • To achieve this we need to create the server link and the user mapping
  • Please note that all the values that are enclosed within <angle brackets> need to be replaced with actual values from your environment.
  • You might have to use the private IP address of your Redshift cluster. It totally depends on your setup.
  • Now that the DBLink is created, its time to query the Redshift cluster from the PostgreSQL to check if everything is setup correctly
  • Everything enclosed within $REDSHIFT$ is the actual query that runs on Redshift and results are sent back to PostgreSQL.
  • To create the cache of the Redshift data, we need to create a materialised view.
  • Query the view just like you query any other view.
  • To refresh the view, use the following code

--

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

When you run a docker container on your laptop, what can a container do by default?

Exploring the Potential of Voice Control: Implementing Amazon Alexa Via Flask-Ask

Velocity, velocity, velocity

Your Quick Reference to Design Patterns

Install MySQL GUI, Mongodb in M1 Mac

Announcing XDV Chain

VBA Project Locked; Project is Unviewable

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Deep Shah

Deep Shah

More from Medium

Big Data : Know everything here

Dancing with data models: relational (SQL) and non-relational data (NoSQL) modeling

Slowly Changing Dimension In Hive — DataWareHouse

What should I use ? — best tools of BIG DATA.