- Created by Diane Miller, last modified by Ritu Sachdeva on Apr 24, 2020
You are viewing an old version of this page. View the current version.
Compare with Current View Page History
« Previous Version 44 Next »
On this Page
You can create an account from Designer or Manager. In Designer, when working on pipelines, every Snap that needs an account prompts you to create a new account or use an existing account. The accounts can be created in or used from:
- Your private project folder: This folder contains the pipelines that will use the account.
- Your Project Space’s shared folder: This folder is accessible to all the users that belong to the Project Space.
- The global shared folder: This folder is accessible to all the users within an organization in the SnapLogic instance.
Account Configuration
In Manager, you can navigate to the required folder and create an account in it (see Accounts). To create an account for binary files:
- Click Create, then select Redshift > Redshift Account.
- Supply an account label.
Supply the necessary information.
- URL parameters to be specified to ensure SSL connectivity:
ssl=true
- The S3 Bucket, S3 Access-key ID and S3 Secret key properties are required for Redshift-Bulk Load and Redshift-Bulk Upsert Snaps. The S3 Folder property may be used for the staging file. If the S3 Folder property is left blank, the staging file will be stored in the bucket.
- URL parameters to be specified to ensure SSL connectivity:
- (Optional) Supply additional information on this account in the Notes field of the Info tab.
- Click Apply.
Avoid changing account credentials while pipelines using them are in progress. This may lead to unexpected results, including locking the account.
If the Snap fails to connect to the database, it will retry three more times.
Account Types
Redshift Account
Label | Required. User provided label for the account instance | |
---|---|---|
JDBC Driver Class | Name of the JBDC driver class to use. Default value: org.postgresql.Driver | |
JDBC jars | The list of JDBC jar files to be loaded. The user can upload the Redshift driver(s) that can override the default org.postgresql.Driver driver. Set the Batch size property to 1 with the JDBC driver version RedshiftJDBC41-1.1.10.1010.jar. Default value: [None] Recommended JDBC JAR Version for Redshift Bulk Upsert Snap Use RedshiftJDBC42-1.2.10.1009.jar as the JDBC JAR version in this property when using the Redshift Upsert Snap in the pipeline. | |
JDBC Url | The Url of the JDBC database. Example: jdbc:redshift://hostname:port/database Default value: [None] | |
Account properties | Required. Enter in the information to create a connection to the database. | |
Endpoint | Required. The server's address to connect to. Default value: [None] | |
Port number | Required. The database server's port to connect to. Default value: 5439 | |
Database name | Required. The database name to connect to. Default value: [None] | |
Username | Username that is allowed to connect to the database. Username will be used as the default username when retrieving connections. The username must be valid in order to set up the data source. Default value: [None] | |
Password | Password used to connect to the data source. Password wil be used as the default password when retrieving connections. The password must be valid in order to set up the data source. Default value: [None] | |
S3 Bucket | S3 Bucket to use for staging data onto Redshift. This field is required if the IAM role checkbox is enabled in the Bulk Load Snap. For optimal performance, this S3 bucket should be located in the same AWS region as the Redshift instance. Default value: [None] | |
S3 Folder | Relative path to a folder in S3 Bucket. This is used as a root folder for staging data onto Redshift. Default value: [None] | |
S3 Access-key ID | S3 Access key ID part of AWS authentication. This field is required if IAM role checkbox is enabled in the Bulk Load Snap. Default value: [None] | |
S3 Secret key | S3 Secret key part of AWS Authentication. This field is required if IAM role checkbox is enabled in the Bulk Load Snap. Default value: [None] | |
IAM properties | ||
AWS account ID | ID of the Amazon Web Services account to be used for performing bulk load operation. This field is required if IAM role checkbox is enabled in the Bulk Load Snap. Default value: [None] | |
IAM role name | Name of the IAM role that has been assigned to the redshift cluster to access the S3 bucket provided above. This field is required if IAM role checkbox is enabled in the Bulk Load Snap. Default value: [None] | |
Region name | Name of the region the Redshift cluster belongs to. Default value: [None] | |
Advanced properties | ||
Auto commit | When selected, each of the batches is committed immediately after it is executed. If the Snap fails, only the batch being executed at that moment is rolled back. When deselected, the Snap execution output is committed only after all the batches are executed. If the Snap fails, the entire transaction is rolled back, unless the Snap finds invalid input data before it sends the insert request to the server, and routes the error documents to the Error view. Default value: Selected | |
Batch size | Required. Number of statements to execute at a time. Set the Batch size property to 1 with the JDBC driver version RedshiftJDBC41-1.1.10.1010.jar, in the JDBC jars. Select queries are not batched. User-defined types won't be supported if a JDBC driver with a version other than 11.2.0.4.0 is set. Default value: 50 | |
Fetch size | Required. Number of rows to fetch at a time when executing a query. User-defined types won't be supported if a JDBC driver with a version other than 11.2.0.4.0 is set. Default value: 100 | |
Max pool size | Required. Maximum number of connections a pool will maintain at a time. Redshift Bulk Load/Bulk Upsert/S3 Upsert Snap requires a minimum of 2 connections per Snap in a pipeline. For example, if a pipeline has a Redshift Bulk Load Snap and an S3 Upsert Snap, then the pool size must be greater than or equal to 4 for successful execution. Default value: 50 | |
Max life time | Required. Maximum lifetime of a connection in the pool. Ensure that the value you enter is a few seconds shorter than any database or infrastructure-imposed connection time limit. A value of 0 indicates an infinite lifetime, subject to the Idle Timeout value. An in-use connection is never retired. Connections are removed only after they are closed. Default value: 30 | |
Idle Timeout | Required. The maximum amount of time a connection is allowed to sit idle in the pool. A value of 0 indicates that idle connections are never removed from the pool. Default value: 5 | |
Checkout timeout | Required. Number of milliseconds to wait for a connection to be available when the pool is exhausted. Zero waits forever. An exception will be thrown after the wait time has expired. Default value: 10000 | |
URL Properties | ||
URL property name | URL property name Default value: [None] | |
URL property value | URL property value Default value: [None] |
Account Encryption
Standard Encryption | If you are using Standard Encryption, the High sensitivity settings under Enhanced Encryption are followed. | |
---|---|---|
Enhanced Encryption | If you have the Enhanced Account Encryption feature, the following describes which fields are encrypted for each sensitivity level selected per each account. Account:
|
Redshift SSL Account
Label | Required. User provided label for the account instance | |
---|---|---|
JDBC Driver Class | Name of the JBDC driver class to use. Default value: org.postgresql.Driver | |
JDBC jars | The list of JDBC jar files to be loaded. The user can upload the Redshift driver(s) that can override the default org.postgresql.Driver driver. Set the Batch size property to 1 with the JDBC driver version RedshiftJDBC41-1.1.10.1010.jar. Default value: [None] | |
JDBC Url | The Url of the JDBC database. Example: jdbc:redshift://hostname:port/database Default value: [None] | |
Account properties | Required. Enter in the information to create a connection to the database. | |
Endpoint | Required. The server's address to connect to. Default value: [None] | |
Port number | Required. The database server's port to connect to. Default value: 5439 | |
Database name | Required. The database name to connect to. Default value: [None] | |
Username | Username that is allowed to connect to the database. Username will be used as the default username when retrieving connections. The username must be valid in order to set up the data source. Default value: [None] | |
Password | Password used to connect to the data source. Password wil be used as the default password when retrieving connections. The password must be valid in order to set up the data source. Default value: [None] | |
S3 Bucket | S3 Bucket to use for staging data onto Redshift. This field is required if the IAM role checkbox is enabled in the Bulk Load Snap. For optimal performance, this S3 bucket should be located in the same AWS region as the Redshift instance. Default value: [None] | |
S3 Folder | Relative path to a folder in S3 Bucket. This is used as a root folder for staging data onto Redshift. Default value: [None] | |
S3 Access-key ID | S3 Access key ID part of AWS authentication. This field is required if IAM role checkbox is enabled in the Bulk Load Snap. Default value: [None] | |
S3 Secret key | S3 Secret key part of AWS Authentication. This field is required if IAM role checkbox is enabled in the Bulk Load Snap. Default value: [None] | |
KeyStore | The location of the key store file, can be in SLDB or any other unauthenticated endpoint such as https://... Example: shared/client_keystore.jks Default value: [None] | |
TrustStore | The location of the trust store file, can be in SLDB or any other unauthenticated endpoint such as https://... Example: shared/client_truststore.jks Default value: [None] | |
Key/Trust store password | Password for key/trust store, it is used for both if both are defined. Default value: [None] | |
Key alias | Define the key being used via its alias Default value: [None] | |
IAM properties | ||
AWS account ID | ID of the Amazon Web Services account to be used for performing bulk load operation. This field is required if IAM role checkbox is enabled in the Bulk Load Snap. Default value: [None] | |
IAM role name | Name of the IAM role that has been assigned to the redshift cluster to access the S3 bucket provided above. This field is required if IAM role checkbox is enabled in the Bulk Load Snap. Default value: [None] | |
Region name | Name of the region the Redshift cluster belongs to. Default value: [None] | |
Advanced properties | ||
Auto commit | When selected, each of the batches is committed immediately after it is executed. If the Snap fails, only the batch being executed at that moment is rolled back. When deselected, the Snap execution output is committed only after all the batches are executed. If the Snap fails, the entire transaction is rolled back, unless the Snap finds invalid input data before it sends the insert request to the server, and routes the error documents to the Error view. Default value: Selected | |
Batch size | Required. Number of statements to execute at a time. Set the Batch size property to 1 with the JDBC driver version RedshiftJDBC41-1.1.10.1010.jar, in the JDBC jars. Select queries are not batched. User-defined types won't be supported if a JDBC driver with a version other than 11.2.0.4.0 is set. Default value: 50 | |
Fetch size | Required. Number of rows to fetch at a time when executing a query. User-defined types won't be supported if a JDBC driver with a version other than 11.2.0.4.0 is set. Default value: 100 | |
Max pool size | Required. Maximum number of connections a pool will maintain at a time. Redshift Bulk Load/Bulk Upsert/S3 Upsert Snap requires a minimum of 2 connections per Snap in a pipeline. For example, if a pipeline has a Redshift Bulk Load Snap and an S3 Upsert Snap, then the pool size must be greater than or equal to 4 for successful execution. Default value: 50 | |
Max life time | Required. Maximum lifetime of a connection in the pool. Ensure that the value you enter is a few seconds shorter than any database or infrastructure-imposed connection time limit. A value of 0 indicates an infinite lifetime, subject to the Idle Timeout value. An in-use connection is never retired. Connections are removed only after they are closed. Default value: 30 | |
Idle Timeout | Required. The maximum amount of time a connection is allowed to sit idle in the pool. A value of 0 indicates that idle connections are never removed from the pool. Default value: 5 | |
Checkout timeout | Required. Number of milliseconds to wait for a connection to be available when the pool is exhausted. Zero waits forever. An exception will be thrown after the wait time has expired. Default value: 10000 | |
URL Properties | ||
URL property name | URL property name Default value: [None] | |
URL property value | URL property value Default value: [None] |
Account Encryption
Standard Encryption | If you are using Standard Encryption, the High sensitivity settings under Enhanced Encryption are followed. | |
---|---|---|
Enhanced Encryption | If you have the Enhanced Account Encryption feature, the following describes which fields are encrypted for each sensitivity level selected per each account. Account:
|
Auto Commit with Execute Snaps
For a DB Execute Snap, assume that a stream of documents enters the input view of the Snap and the SQL statement property has JSON paths in the WHERE clause. If the number of documents are large, the Snap executes in more than one batches rather than executing one per each document. Each batch would contain a certain number of WHERE clause values. If Auto commit is turned on, a failure would only roll back the records in the current batch. If Auto commit is turned off, the entire operation would be rolled back. For a single execute statement (with no input view), the setting has no practical effect.
Snap Pack History
Release | Snap Pack Version | Date | Type | Updates |
---|---|---|---|---|
August 2024 | main27765 | | Stable |
|
May 2024 | 437patches26634 | Latest | Fixed an issue with Redshift - Execute Snap that produced logs causing node crashes. | |
May 2024 | main26341 | Stable | Updated the Delete Condition (Truncates a Table if empty) field in the Redshift - Delete Snap to Delete condition (deletes all records from a table if left blank) to indicate that all entries will be deleted from the table when this field is blank, but no truncate operation is performed. | |
February 2024 | main25112 | Stable | Updated and certified against the current SnapLogic Platform release. | |
November 2023 | main23721 |
| Stable | Updated and certified against the current SnapLogic Platform release. |
August 2023 | main22460 | Stable |
Behavior Change Starting with
| |
May 2023 | main21015 | Stable | Upgraded with the latest SnapLogic Platform release. | |
February 2023 | 432patches20500 | Latest | The Redshift Account no longer fails when a URL is entered in the JDBC URL field and no driver is specified. | |
February 2023 | 432patches20166 | Latest | Updated the description for S3 Security Token field as follows: Specify the S3 security token part of AWS Security Token Service (STS) authentication. It is not required unless a particular S3 credential is configured to require it. | |
February 2023 | 432patches20101 |
| Latest |
|
February 2023 | 432patches20035 |
| Latest | The Redshift Snaps that earlier supported only Redshift Cluster now support Redshift Serverless as well. With Redshift Serverless, you can avoid setting up and managing data warehouse infrastructure when you run or scale analytics. |
February 2023 | main19844 | Stable | Upgraded with the latest SnapLogic Platform release. | |
November 2022 | main18944 | Stable | The Redshift - Insert Snap now creates the target table only from the table metadata of the second input view when the following conditions are met:
| |
August 2022 | 430patches17189 | Latest |
| |
August 2022 | main17386 | Stable | The Redshift accounts support:
| |
4.29 Patch | 429patches16908 | Latest |
| |
4.29 Patch | 429patches15806 | Latest | Fixed an issue with Redshift Account and Redshift SSL Account where the Redshift Snaps failed when the S3 Secret key or S3 Access-key ID contained special characters, such as +. | |
4.29 | main15993 |
| Stable | Upgraded with the latest SnapLogic Platform release. |
4.28 | main14627 | Stable | Updated the label for Delete Condition to Delete Condition (Truncates Table if empty) in the Redshift Delete Snap. | |
4.27 Patch | 427patches12999 | Latest | Fixed an issue with the Redshift Bulk Load Snap, where the temporary files in S3 were not deleted for aborted or interrupted Pipelines. | |
4.27 Patch | 427patches12999 | Latest |
| |
4.27 | main12833 | Stable | Enhanced the Redshift - Execute Snap to invoke stored procedures. | |
4.26 | main11181 | Stable | Upgraded with the latest SnapLogic Platform release. | |
4.25 Patch | 425patches11008 | Latest | Updated the AWS SDK from version 1.11.688 to 1.11.1010 in the Redshift Snap Pack and added a custom SnapLogic User Agent header value. | |
4.25 | main9554 | Stable | Upgraded with the latest SnapLogic Platform release. | |
4.24 | main8556 | Stable |
| |
4.23 | main7430 | Stable | Fixed an issue with the Redshift Bulk Load Snap that fails while displaying a | |
4.22 | main6403 | Stable | Upgraded with the latest SnapLogic Platform release. | |
4.21 Patch | 421patches6144 | Latest | Fixed the following issues with DB Snaps:
| |
4.21 Patch | MULTIPLE8841 | Latest | Fixed the connection issue in Database Snaps by detecting and closing open connections after the Snap execution ends. | |
4.21 | snapsmrc542 |
| Stable | Upgraded with the latest SnapLogic Platform release. |
4.20 Patch | db/redshift8774 | Latest | Fixed the Redshift - Execute Snap that hangs if the SQL statement field contains only a comment ("-- comment"). | |
4.20 | snapsmrc535 | Stable | Upgraded with the latest SnapLogic Platform release. | |
4.19 Patch | db/redshift8410 | Latest | Fixed an issue with the Redshift - Update Snap wherein the Snap is unable to perform operations when:
| |
4.19 | snaprsmrc528 | Stable | Upgraded with the latest SnapLogic Platform release. | |
4.18 Patch | db/redshift8043 | Latest | Enhanced the Snap Pack to support AWS SDK 1.11.634 to fix the NullPointerException issue in the AWS SDK. This issue occurred in AWS-related Snaps that had HTTP or HTTPS proxy configured without a username and/or password. | |
4.18 Patch | MULTIPLE7884 | Latest | Fixed an issue with the PostgreSQL grammar to better handle the single quote characters. | |
4.18 Patch | MULTIPLE7778 | Latest | Updated the AWS SDK library version to default to Signature Version 4 Signing process for API requests across all regions. | |
4.18 | snapsmrc523 | Stable | Upgraded with the latest SnapLogic Platform release. | |
4.17 Patch | db/redshift7433 | Latest | Fixed an issue with the Redshift Bulk Load Snap wherein the Snap fails to copy the entire data from source to the Redshift table without any statements being aborted. | |
4.17 | ALL7402 | Latest | Pushed automatic rebuild of the latest version of each Snap Pack to SnapLogic UAT and Elastic servers. | |
4.17 | snapsmrc515 | Latest |
| |
4.16 Patch | db/redshift6821 | Latest | Fixed an issue with the Lookup Snap passing data simultaneously to output and error views when some values contained spaces at the end. | |
4.16 | snapsmrc508 | Stable | Upgraded with the latest SnapLogic Platform release. | |
4.15 Patch | db/redshift6286 | Latest | Fixed an issue with the Bulk Upsert Snap wherein there was no output for any input schema. | |
4.15 Patch | db/redshift6334 | Latest | Replaced Max idle time and Idle connection test period properties with Max life time and Idle Timeout properties, respectively, in the Account configuration. The new properties fix the connection release issues that were occurring due to default/restricted DB Account settings. | |
4.15 | snapsmrc500 | Stable | Upgraded with the latest SnapLogic Platform release. | |
4.14 Patch | db/redshift5786 | Latest | Fixed an issue wherein the Redshift Upload snap logged the access and secret keys without encryption in the error logs. The keys are now masked. | |
4.14 Patch | db/redshift5667 | Latest |
| |
4.14 | snapsmrc490 | Stable | Upgraded with the latest SnapLogic Platform release. | |
4.13 Patch | db/redshift/5303 | Latest | Added a new property "Validate input data" in the Redshift Bulk Load Snap to help users troubleshoot the input data schema. | |
4.13 Patch | db/redshift5186 | Latest | Fixed the Bulk Load and Unload Snaps wherein the KMS encryption type property is failing with validation error. | |
4.13 | snapsmrc486 | Stable | Added KMS encryption support to these Snaps: Redshift Unload, Redshift Bulk Load, Redshift Bulk Upsert, and Redshift S3 Upsert. | |
4.12 Patch | db/redshift5027 | Latest | Fixed an issue wherein the Redshift Snaps timeout and fail to retrieve a database connection. | |
4.12 Patch | MULTIPLE4967 | Latest | Provided an interim fix for an issue with the Redshift accounts by re-registering the driver for each account validation. The final fix is being shipped in a separate build. | |
4.12 Patch | MULTIPLE4744 | Latest | Added support for Redshift grammar to recognize window functions as being part of the query statement. | |
4.12 | snapsmrc480 | Stable | Upgraded with the latest SnapLogic Platform release. | |
4.11 Patch | db/redshift4589 | Latest | Fixed an issue when creating a Redshift table via the second/metadata input view for the Redshift Bulk Load Snap. | |
4.11 | snapsmrc465 | Stable | Added SSL support to the Configuring Redshift Accounts. | |
4.10 Patch | db/redshift4115 | Latest | The Upsert or BulkUpdate/BulkLoad shall not execute and produce output when no inputView has been provided. | |
4.10 Patch | redshift3936 | Latest | Addressed an issue in Redshift Execute with a Select that hangs after extracting 13 million in the morning or 30 million in the evening | |
4.10 | snapsmrc414 | Stable | Added Auto commit property to the Select and Execute Snaps at the Snap level to support overriding of the Auto commit property at the Account level. | |
4.9.0 Patch | redshift3229 | Latest | Addressed an issue in Redshift Multiple Execute where INSERT INTO SELECT statement generated a 'transaction, commit and rollback statements are not supported' exception. | |
4.9.0 Patch | redshift3073 | Latest | Fixed an issue regarding connection not closed after login failure; Expose autocommit for "Select into" statement in PostgreSQL Execute Snap and Redshift Execute Snap | |
4.9 | snapsmrc405 | Stable |
| |
4.8.0 Patch | redshift2852 | Latest |
| |
4.8.0 Patch | redshift2799 | Latest |
| |
4.8.0 Patch | redshift2758 | Latest | Potential fix for JDBC deadlock issue. | |
4.8.0 Patch | redshift2713 | Latest | Fixed Redshift Snap Pack rendering dates that are one hour off from the date returned by database query for non-UTC Snaplexes | |
4.8.0 Patch | redshift2697 | Latest | Addresses an issue where some changes made in the platform patch MRC294 to improve performance caused Snaps in the listed Snap Packs to fail. | |
4.8 | snapsmrc398 | Stable |
| |
4.7.0 Patch | redshift2434 | Latest | Replaced newSingleThreadExecutor() with a fixed thread pool. | |
4.7.0 Patch | redshift2387 | Latest | Addressed an issue in Redshift Bulk Load Snap where Load Empty String was setting not working after release. | |
4.7.0 Patch | redshift2223 | Latest | Auto-commit is turned off automatically for SELECT | |
4.7.0 Patch | redshift2201 |
| Latest | Fixed an issue for database Select Snaps regarding Limit rows not supporting an empty string from a pipeline parameter. |
4.7 | snapsmrc382 | Stable |
| |
4.6 | snapsmrc362 | Stable |
| |
4.5.1 | redshift1621 | Latest |
| |
4.5 | snapsmrc344 | Stable |
| |
4.4.1 | NA | Latest | Resolved an issue with numeric precision when trying to use create table if not present in Redshift Insert Snap. | |
4.4 | NA | Stable | Upgraded with the latest SnapLogic Platform release. | |
4.3.2 | NA | Stable |
| |
4.3 | NA | Stable |
| |
4.2 | NA | Latest |
| |
NA | NA | NA |
|
- No labels