This article explains how to configure an Table of Contents maxLevel 2
Overview
You can use the ELT Database Account to connect to a Redshift target database. It assumes that you have selected Redshift as the Database Type.
...
. The cloud location where the database is hosted is indicated in the JDBC URL for Redshift—jdbc:redshift://<redshift-cluster-name>.<company>.<region>.redshift.amazonaws.com.
Note |
---|
The ELT Snap Pack does not support mixed accounts from different types of databases in the same Pipeline. For example, a Pipeline in which some Snaps are connecting to the Redshift target database cannot have other Snaps connecting to a Snowflake or BigQuery target database. |
Prerequisites
- A valid Redshift account.
Certified JDBC JAR File: redshift-jdbc42-2.1.0.9.jar
Note title Using Alternate JDBC JAR File Versions We recommend that you let the ELT Snaps use this JAR file version. However, you may use a different JAR file version of your choice.
Limitations
None.
Known Issue
Multiexcerpt include macro | ||||
---|---|---|---|---|
|
Account Settings
Panel | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
|
| |||||||||||
|
Parameter | Field Dependency | Description | |||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
Label* | None. | Required. Unique user-provided label for the account. Default Value: N/A Example: ELT Redshift AWS Account AD ON | |||||||||
Account Properties* | Use this field set to configure the information required to establish a JDBC connection with the account. This field set consists of the following fields:
| ||||||||||
Database Type* | None. | Required. Select the target data warehouse into which the queries must be loaded, Redshift in this case. This selection activates the Source Location field. Default Value: N/A Example: Redshift | |||||||||
Download JDBC Driver Automatically | CheckboxNone. | Select this checkbox to allow the Snap account to download the certified JDBC Driver based on the Database Type you have selectedfor Redshift. The following fields are disabled when this checkbox is selected.
To use a JDBC Driver of your choice, clear this checkbox, upload (to SLDB), and choose the required JAR files in the JDBC JAR(s) and/or ZIP(s): JDBC Driver field.
Default Value: Not Selected Example: Selected | |||||||||
JDBC JAR(s) and/or ZIP(s): JDBC Driver | Required when the Download JDBC Driver Automatically checkbox is not selected. | Upload the JDBC driver and other JAR files that you want to use into SLDB. Click Downloading the JDBC Driver for more information about JDBC drivers and downloading the appropriate driver for your account. to add a new row. Add each JDBC JAR file in a separate row. See Default Value: N/A Example: redshift-jdbc42-2.0.0.2.jar | |||||||||
JDBC driver class* | Required when the Download JDBC Driver Automatically checkbox is not selected. | Specify the driver class to use for your application. We recommend that you use amazon.redshift.jdbc42.Driver for Redshift as other classes and methods may change due to future enhancements. Default Value: N/A Example: amazon.redshift.jdbc42.Driver | |||||||||
JDBC URL* | None. | Required. Enter the JDBC driver connection string that you want to use for connecting to a Redshift instance. Use the appropriate syntax provided below. Redshift:See Redshift's Configure JDBC Connection for more information.
Alternatively, you can make use of the Username, Password, and Database Name fields, along with the Advanced Properties > URL Properties field set to provide the parameters required for building your JDBC URL. See Passing your JDBC URL for more information.
Default Value: N/A Example: jdbc:redshift://redshift-cluster-1.cwunce6uocwun0q50q5.us-east-2.redshift.amazonaws.com:5439 | |||||||||
Username* | None. | Enter the username provided for your database account. Default value: N/A Example: Bigdatasnaplogic Bdsnaplogic | |||||||||
Password* | None. | Enter the password for the username specified above. Default value: N/A Example: <Encrypted> | |||||||||
Default Database Name* | StringNone. | Enter the name of the database to use by default. This database is used if you do not specify one in the ELT Select, ELT Insert-Select, or ELT Merge Into Snaps. Default value: N/A Example: EMPLOYEEDB | |||||||||
Source Location* | Database Type is Redshift. | Select the source data warehouse from which to load data into the target database. You must configure this field if you want to use the ELT Load Snap. Available options are:
Default value: None Example: S3 | |||||||||
Authentication | Source Location is S3. | Select the authentication method to use for the loaded data. Available options are:
Default value: Source Location Credentials Example: IAM Role | |||||||||
IAM Role | Source Location is S3 and Authentication is IAM Role. | Enter the IAM role associated with the target Redshift cluster. See Create an IAM Role for details on creating and associating IAM Role with your Redshift cluster. Default value: N/A Example: myiamrole | |||||||||
Access Key | Source Location is S3 and Authentication is Source Location Credentials. | Enter the access key ID associated with your AWS S3 account. Default value: N/A Example: ABCDEFGHIJKL1MNOPQRS | |||||||||
Secret Key | Source Location is S3 and Authentication is Source Location Credentials. | Enter the client secret key associated with your AWS S3 account. Default value: N/A Example: aBcdeFGhiJKLM/N1OPQRS/tUvWxyZABCDEFGHIJKLMN | |||||||||
Session Access Key | Source Location is S3 and Authentication is Source Location Session Credentials. | Enter the access key ID associated with your AWS S3 account. Default value: N/A Example: ABCDEFGHIJKL1MNOPQRS | |||||||||
Session Secret Key | Source Location is S3 and Authentication is Source Location Session Credentials. | Enter the client secret key associated with your AWS S3 account. Default value: N/A Example: aBcdeFGhiJKLM/N1OPQRS/tUvWxyZABCDEFGHIJKLMN | |||||||||
Session Token | Source Location is S3 and Authentication is Source Location Session Credentials. | Enter the session token value when the authorization to access the S3 folder is based on Security Token Service (STS) based temporary credentials. Default value: N/A Example: A%1DEFGH1Ijk2Lm3noI3OlWTjEg2tYkboXr1P9ZUXDtkk%3D | |||||||||
S3 Bucket | Source Location is S3. | Enter the name of the bucket from which to load the data. Default value: N/A Example: employeedata | |||||||||
S3 Bucket Region | Source Location is S3. | Select the region of your Redshift cluster if it is different from the region of the S3 bucket. Available options are:
Default value: Default Example: us-east-1, us-east-2 | |||||||||
S3 Folder | Source Location is S3. | Enter the name of the folder in the S3 bucket specified above where the source files are located. Default value: N/A Example: hrdepartment | |||||||||
Advanced Properties | Other parameters that you want to specify as URL properties. See the following resources for a list of parameters that can be specified in this field set. This field set consists of the following fields:
| ||||||||||
URL Properties | String/IntegerNone. | The account parameter's name and its corresponding value. Click + to add more rows. Add each URL property-value pair in a separate row. Specify the name of the parameter in the URL Property Name field and its value in the URL Property Value field. Default value: N/A Example: MAX_CONCURRENCY_LEVEL: 3 |
Click Validate after entering the required details to ensure that all fields have been filled accurately. Click Apply to save the settings.
Note | ||
---|---|---|
| ||
If you have used Pipeline parameters or expressions to define values for the account fields above, the account validation (done by clicking the Validate button) is not supported. However, the Snaps that use this account may connect to the endpoint successfully depending on the accuracy and validity of the expressions and parameters used. |
Passing your JDBC URL
Order of Precedence
...
Default Properties set internally
The following properties are property is passed in the URL internally, by default:
...
The Pipeline accesses the CSV file using the Source Location Credentials mode of authentication.
...
See Also
...