Amazon Redshift Connector

Overview

Used for OneTrust Data Discovery, the Amazon Redshift connector enables customers to scan and retrieve data across incredibly large analysis operations using SQL that is able to feed directly into their business intelligence applications.

Note:

This connector is currently under Public Preview. Contact OneTrust Support to request early access.

Pod Residency: rdbms-scanner
Connection Method: JDBC

Supported Scan Actions


Scan Type	Description
Catalog	This scan type will fetch only the meta data in the database like details of table, or column are scanned.
Catalog & Scan	This scans both metadata and data under the tables and returns the appropriate terms.

Credential and System Permissions

OneTrust recommends to adhere to your organization's security and infrastructure policy and include system engineer resources or other qualified subject matter experts to set up credentials on data sources. For specific instructions for how to enable these credentials, refer to your data source's implementation documentation, knowledge base and/or support resources.

This connector requires username/password credentials.

Example:

UID={username}
PWD={password}

Sample JSON

JSON

{
    "connectionUrl": "jdbc:redshift://domain.redshift.amazonaws.com:5439/db_name",
    "user": "your_user",
    "password": "your_password"
}

{
    "connectionUrl": "jdbc:redshift://domain.redshift.amazonaws.com:5439/db_name",
    "user": "your_user",
    "password": "your_password"
}

Scan Profile Support

Note:

For additional details on scan profile configuration and management, see Setting Up Scan Profiles.

Limits

Number of schemas per database
Tables per schema
Rows per table

Wildcard

Wildcards are not supported for column inclusion or exclusion.

Example: *Name* will not work to include/exclude both firstName and lastName columns
Instead, you must specify the exact column names separated by commas (e.g. firstName,lastName).

Important:

Columns Excluded in the Data Dictionary

When a column is excluded, it is still cataloged by the Data Dictionary. This means:

Basic metadata for the column is retrieved.
During extraction, the actual column content is skipped based on the configuration.

Include/Exclude Path Configuration Fields

Database
Schema
Table
Column

Collected Objects & Fields


Objects	Fields
Databases	Name
Tables	Name
Columns	Name Column Index (Order) Comment Nullable (T/F) Size Estimate Comment
Keys	Primary Key Name Primary Key Columns Foreign Key Name Foreign Key Columns

Avoiding Out-of-Memory Errors

Because of a default configuration to the RedShift JDBC driver, retrieving large data sets may result in an out-of-memory issue. OneTrust recommends fetching data in batches by setting the JDBC fetch size parameter. You will need to specify a LIMIT clause to restrict the result set. You may also use the OFFSET clause to skip to a specific starting point.

For additional guidance, see AWS documentation on JDBC fetch size parameters and defaultRowFetchSize.

Set REDSHIFT_FETCH_SIZE on scanner pod to change the default value of 10240 rows.
Set defaultRowFetchSize in JDBC URL to 10,000. If this causes out-of-memory issue, you may decrease this number. If the scan is too slow, increase this number. As the bytes per row is unknown, this makes the number experimental.
CODE
fetch_size = bytesInMemory / bytesPerRow
```
fetch_size = bytesInMemory / bytesPerRow
```
Use the query batching configuration on the scanner for rdbms to enable cursor (LIMIT, OFFSET) to pull the data. Set REDSHIFT_DATA_EXTRACTION_SPLIT_TABLE_BATCH_SIZE on scanner to large number, ideally 10,000 or 100,000.

Data Use Governance