Ontop

# AWS Redshift

Supported since 5.0.2.

Through the Redshift (opens new window) connector, Ontop is able to construct VKGs on AWS Redshift databases.

# Limitations & Exceptions

WARNING

AWS Redshift does not provide information about integrity constraints. Make sure to provide this information in order to avoid very inefficient queries. We recommend using lenses for this purpose.

  • The Simba Redshift JDBC does not support the use of default databases when connecting to Redshift.
  • Accessing SUPER object fields with the "dot operator" is not supported (see below).

# Database Connection

The following shows the content of a sample .properties file that can be used to connect Ontop to Redshift:

jdbc.url = jdbc:redshift:iam://default.dummyaccount.eu-central-1.redshift-serverless.amazonaws.com:5439/defaultdatabase
jdbc.property.AccessKeyID = public-access-key
jdbc.property.SecretAccessKey = private-access-key
jdbc.driver = com.amazon.redshift.jdbc42.Driver

NOTE

The AWS Redshift JDBC supports different types of authentication methods. The above .property file corresponds to authentication with access keys. However, any other authentication method can be used with Ontop as well. In these cases, the corresponding connection properties have to be supplied as jdbc.property.<property-name> instead of the AccessKeyID and SecretAccessKey properties.

# Nested Type Support

Ontop implements explicit compatibility with the Redshift type SUPER.

This type can be used with the flatten lens. However, Ontop is not able to automatically infer the type of the result column.

# Struct Access

In Redshift, individual SUPER struct objects can be accessed by SQL expressions using the "dot operator" on the SUPER column. In Ontop, this feature is not currently supported.

Should any of their elements still be required, then a workaround can be performed by first transforming the struct into a JSON object and then accessing it using JSON functions.

Example:

my_struct.my_attribute JSON_EXTRACT_PATH_TEXT(JSON_SERIALIZE(my_struct), 'my_attribute')

← Databricks →