Support schema selection for Redshift source connector

[tuliren]

Tell us about the problem you're trying to solve

Allow users to specify which schemas to sync when setting up a Redshift connection.

Describe the solution you’d like

Similar to Postgres or Oracle source, add a schemas field in spec.json.

Describe the alternative you’ve considered or used

N/A

Additional context

• This is part of this epic [EPIC] Support schema selection in database and warehouse source connectors #2781
• It resolves https://github.com/airbytehq/oncall/issues/106.

[etsybaev]

Hi @tuliren .

It seems that the Redshift jdbc driver doesn't support a schema selection as we have in ex. Postgres/Oracle/ etc.
Not sure what to do with that ticket. Should I close it as "not supported"?
Thanks

https://docs.aws.amazon.com/redshift/latest/mgmt/jdbc20-obtain-url.html
https://docs.aws.amazon.com/redshift/latest/mgmt/jdbc20-build-connection-url.html
https://docs.aws.amazon.com/redshift/latest/mgmt/jdbc20-configuration-options.html

[tuliren]

Thank you for looking into this.

The doc is a bit confusing. In the second link, it does mention schema in the URL:

jdbc:redshift://[Host]:[Port]/[Schema];[Property1]=[Value];[Property2]=[Value];

Currently we specify the database name after the port. Maybe that parameter can be the schema as well, according to this doc. Can you try something like jdbc:redshift://[Host]:[Port]/[Schema];DBNAME=[Database]?

---

Also, I found this in the Redshift JDBC code:

https://github.com/aws/amazon-redshift-jdbc-driver/blob/master/src/main/java/com/amazon/redshift/RedshiftProperty.java#L137

/**
 * Specify the schema (or several schema separated by commas) to be set in the search-path. This schema will be used to resolve
 * unqualified object names used in statements over this connection.
 */
CURRENT_SCHEMA(
  "currentSchema",
  null,
  "Specify the schema (or several schema separated by commas) to be set in the search-path"),

Maybe this connection URL property is not properly documented. Can you also try that? This may require upgrading to JDBC version 2.x. Currently we are using 1.2.43.1067.

[etsybaev]

Hi @tuliren. Thanks, I will take a look.
We already use this "schema", but this is actually is a DB name. That was a bit confusing :)