Redshift
Amazon Redshift is a fully managed, cloud-based data warehouse service that enables fast querying and analysis of large datasets using SQL. It’s designed for high performance at scale, supports integration with popular BI tools, and can analyze data across your data warehouse, operational databases, and data lake.
Prerequisites
The following prerequisites must be met in order to create and test a successful connection.
- The Redshift server must be accessible from the iceDQ server.
- Valid credentials to access the database.
- Redshift version 2.1 or above.
Authentication Mechanism
The following authentication mechanism is supported.
- Username & Password
Connection Properties
Use the properties to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Prod_conn |
| Driver * | Driver used to establish the connection. By default, one driver is available. | Redshift Native JDBC |
| Custom JDBC URL | Standardized string used to define the connection details. Use this format supported by the driver: jdbc:redshift://[host]:[port]/[database] | jdbc:redshift://136.954.67.30:9999/prod_db |
| Host * | IP address or hostname of the database server. | redshift.acme.com or 136.954.67.30 |
| Port * | Port is the address on which the server listens. Default value is 9999. | 9999 |
| Database * | Name of the target database. | prod_db |
| Type * | The connection type – either System Connection or User Connection. Refer Connections for more details. | System or User |
| Username * | Database login username with necessary privileges. | john_doe |
| Password * | Password associated with the specified username. | Admin@123 |
Custom Properties
The following optional connection properties can be configured based on user requirements.
| Property | Default Value | Possible Values | Description |
|---|---|---|---|
| CryptoProtocolVersion | N/A | String (e.g., TLSv1.2, TLSv1.3) | Specifies the cryptographic protocol(s) to use when SSL is enabled (EncryptionMethod=SSL). |
| EncryptionMethod | noEncryption | noEncryption, SSL | Determines whether data is encrypted/decrypted over the network between driver and server. |
| HostNameInCertificate | N/A | String | Hostname for certificate validation when SSL is enabled and ValidateServerCertificate=true. Optional for MITM protection. |
| KeyPassword | N/A | String | Password to access individual keys in the keystore file when SSL client authentication is enabled. |
| KeyStore | N/A | File path | Directory of the keystore file containing client certificates for SSL client authentication. |
| KeyStorePassword | N/A | String | Password for accessing the keystore file containing client certificates. |
| TrustStore | N/A | File path | Directory of the truststore file containing trusted Certificate Authorities (CAs). |
| TrustStorePassword | N/A | String | Password to access the truststore file for server authentication. |
| ValidateServerCertificate | true | true, false | Determines whether the driver validates the server’s SSL certificate. Required for SSL server authentication. |
| ConnectionRetryCount | 0 | Integer | Number of times the driver retries connection attempts to the primary (and alternate) servers. |
| ConnectionRetryDelay | 1 | Integer (seconds) | Seconds to wait between connection retry attempts. |
| ProxyHost | N/A | String | Proxy server to use for the initial connection. |
| ProxyPassword | N/A | String | Password to authenticate to the proxy server. |
| ProxyPort | N/A | Integer | Port number where the proxy server listens for HTTP/HTTPS requests. |
| ProxyUser | N/A | String | Username to authenticate to the proxy server. |
| EnableCancelTimeout | false | true, false | If true, a cancel request for a timed-out query is subject to the same query timeout as the statement it cancels. |
| LoginTimeout | 0 | Integer (seconds) | Maximum time the driver waits for a connection to be established before timing out. |
| QueryTimeout | 0 | Integer (seconds) | Default query timeout for all statements created by the connection. |
| ConvertNull | N/A | Boolean/String | Controls how null values are handled during data conversion. |
| JavaDoubleToString | driver default | String algorithm | Algorithm used to convert double/float values to strings. Default internal algorithm improves performance. |
| MaxNumericPrecision | 38 | Integer | Maximum precision for NUMERIC columns as described in the result set metadata. |
| MaxNumericScale | 37 | Integer | Maximum scale for NUMERIC columns as described in the result set metadata. |
| MaxVarcharSize | 65535 | Integer | Maximum size for VARCHAR columns as described in the result set metadata. |
| VarcharClobThreshold | 65535 | Integer | Threshold to enable CLOB functionality. Determines if VARCHAR columns are described as VARCHAR or LONGVARCHAR (CLOB). |
Supported Datatypes
The following datatypes are supported.
- SMALLINT
- INTEGER
- BIGINT
- DECIMAL
- REAL
- DOUBLEPRECISION
- BOOLEAN
- CHAR
- VARCHAR
- DATE
- TIMESTAMP
- TIMESTAMPTZ
- SUPER
- TIME
- TIMETZ
- VARBYTE
Unsupported Datatypes
The following datatypes are not supported.
- GEOMETRY
- GEOGRAPHY
- HLLSKETCH
- SUPER
- VARBYTE