CODEWITHSUNDEEP
amazon-web-servicesamazon-redshiftdblink
Reece Mak
Reece Mak

Reputation: 101

Does Amazon Redshift support extension dblink?

CREATE EXTENSION dblink;

I tried to create an extension dblink in AWS, but I receive this error.

ERROR: syntax error at or near "EXTENSION" LINE 1: create extension dblink;

Upvotes: 5

Views: 8373

Answers (3)

Andrew Alcock
Andrew Alcock

Reputation: 19651

While AWS Redshift is based on Postgresql, it has various enhancements/alterations to integrate both AWS infrastructure and also a very different backend from standard Postgresql. In order to create a DB Link, you don't need to (in fact, you can't) load the DB Link extension.

Instead, AWS have replaced the standard Postgresql CREATE SERVER AND IMPORT FOREIGN SCHEMA statement with their unique CREATE EXTERNAL SCHEMA - this creates your DB links for you:

CREATE EXTERNAL SCHEMA [IF NOT EXISTS] local_schema_name
FROM { [ DATA CATALOG ] | HIVE METASTORE | POSTGRES | MYSQL | KINESIS }
[ DATABASE 'database_name' ]
[ REGION 'aws-region' ]
[ URI 'hive_metastore_uri' [ PORT port_number ] ]
IAM_ROLE { default | 'arn:aws:iam::<AWS account-id>:role/<role-name>' }
[ SECRET_ARN 'ssm-secret-arn' ]          
[ CATALOG_ROLE 'catalog-role-arn-string' ] 
[ CREATE EXTERNAL DATABASE IF NOT EXISTS ]

This does everything in one statement. Full documentation can be found here

Upvotes: 3

John Rotenstein
John Rotenstein

Reputation: 269360

Yes and No.

Amazon Redshift itself does not provide dblink. However, you can install dblink on a different PostgreSQL server and use it to connect to Amazon Redshift.

See: JOIN Amazon Redshift AND Amazon RDS PostgreSQL WITH dblink

Upvotes: 10

Lars Kamp
Lars Kamp

Reputation: 21

The answer is "Yes", but you have to use Amazon RDS as a ‘cache’ by leveraging the Dblink feature of RDS. This allows us to make any Amazon Redshift table available as a relation in RDS, without the need to explicitly copy that data over. Dblink handles moving the data at the block level.

We've used the approach described in the link that John has provided above. We did a detailed write up of how we did it in a blog post "Have Your Postgres Cake with Amazon Redshift and eat it, too."

https://www.intermix.io/blog/using-amazon-redshift-with-amazon-rds/

Upvotes: 2

Related Questions