TIBCO Scribe® Online Connector For Amazon Redshift

Use the TIBCO Scribe® Online Connector for Amazon Redshift to integrate data from another datastore into Amazon Redshift for data analytics. The Connector for Amazon Redshift integrates data from JSON files extracted from third-party data sources using the Connector for Amazon S3. See TIBCO Scribe® Online Connector For Amazon S3 for more information.

Connector Specifications

  Supported

Agent Types

On Premise X
Cloud X

Replication Services

Source  
Target  

Integration Services

Source  
Target X

Migration Services

Source  
Target X

Maps

Integration X
Request-Reply  
Message  

Note:  This Connector is available from the TIBCO Scribe® Online Marketplace. See Marketplace TIBCO Scribe® Certified Connectors for more information.

Supported Entities

The Amazon Redshift Connector supports the following entities and operations. Click a linked entity name for additional information when using that entity in TIBCO Scribe® Online.

Special Operations

Entity Operations

AmazonS3Storage

-Copy

AmazonS3StorageMerge

-Merge

Selecting An Agent Type For Amazon Redshift

Refer to TIBCO Scribe® Online Agents for information on available Agent types and how to select the best Agent for your Solution.

Connecting To Amazon Redshift

Note: Best practice is to create Connections with credentials that limit permissions in the target system, following the principle of least privilege. Using Administrator level credentials in a Connection provides Administrator level access to the target system for TIBCO Scribe® Online users. Depending on the entities supported, a TIBCO Scribe® Online user could alter user accounts in the target system.

  1. Select More > Connections from the menu.
  2. From the Connections page select Add to open the Add a New Connection dialog.
  3. Select the Connector from the drop-down list in the Connection Type field, and then enter the following information for this Connection:
    • Name — This can be any meaningful name, up to 25 characters.
    • Alias — An alias for this Connection name. The alias is generated from the Connection name, and can be up to 25 characters. The Connection alias can include letters, numbers, and underscores. Spaces and special characters are not accepted. You can change the alias. For more information, see Connection Alias.
    • Host — Endpoint of the Amazon Redshift cluster.
    • Port — Port of the Amazon Redshift cluster.
    • Database — Name of the Amazon Redshift database.
    • User — Your Amazon Redshift username.
    • Password — Your Amazon Redshift Password.
    • AWS Access Key ID — Your Amazon web services access key.
    • AWS Secret Access Key — Your Amazon web services secret access key.
    • AWS Storage Region — Your Amazon storage region key.
  4. Select Test to ensure that the Agent can connect to Amazon Redshift. Be sure to test the Connection against all Agents that use this Connection. See Testing Connections.
  5. Select OK to save the Connection.

Metadata Notes

Naming

Connection metadata must have unique entity, relationship, and field names. If your Connection metadata has duplicate names, review the source system to determine if the duplicates can be renamed.

Amazon Redshift Connector As IS Target

Consider the following when using the Amazon Redshift Connector as an IS target.

Notes On Standard Entities

AmazonS3Storage

AmazonS3Storage is a virtual entity available only in the Copy Block that represents the data extracted through an Amazon S3 Connection from a third-party application, such as Salesforce. See TIBCO Scribe® Online Connector For Amazon S3 for information on configuring that Connection to extract data.

Copy Operation

Assume that you have created JSON files based on the Salesforce Contact entity using the Connector for Amazon S3. Using Amazon S3 as a Source and Amazon Redshift as a Target, you can copy the Salesforce Contact records to Amazon Redshift using the Copy Block in a Map.

When the Map runs, the Amazon Redshift Connector builds a table in Amazon Redshift based on the metadata schema associated with the selected JSON file and populates that table with the data stored in the JSON file.

For more information on generating JSON files to populate Amazon Redshift tables, see TIBCO Scribe® Online Connector For Amazon S3.

Note: Subsequent executions of the same Map using the Copy Operation, append records to the selected table in Amazon Redshift, which may generate duplicates. To eliminate duplicates, use the Merge Operation with a MergeKey for subsequent uploads.

TableName Field

The TableName field is required but does not have to match the table name in the Source data. You can modify it to anything you need and that becomes the name of the table created in Amazon Redshift.

Other Fields

Other fields shown in the target side of the Fields tab are used to normalize source data when it is written to the target. For example, TimeFormat, is used to specify the format for date time fields when stored in Amazon Redshift. If you specify "auto", Amazon Redshift recognizes and converts the inbound date or time format. For information on how each field can be used, hover over the field name.

AmazonS3StorageMerge

AmazonS3StorageMerge is a virtual entity available only in the Merge Block that represents the data extracted through an Amazon S3 Connection from a third-party application, such as Salesforce. See TIBCO Scribe® Online Connector For Amazon S3 for information on configuring that Connection to extract data.

Merge Operation

Assume that you have created JSON files based on the Salesforce Contact entity using the Connector for Amazon S3. Using Amazon S3 as a Source and Amazon Redshift as a Target, you can merge the Salesforce Contact records into an existing table in Amazon Redshift or create a new table with those Contact records using the Merge Block in a Map.

When the Map runs, the Amazon Redshift Connector either builds a new table, if the table does not exist, or merges the data into an existing table in Amazon Redshift. If the Connector builds a new table, it is based on the metadata schema associated with the selected JSON file and populated with the data stored in the JSON file.

For more information on generating JSON files to populate Amazon Redshift tables, see TIBCO Scribe® Online Connector For Amazon S3.

Note: If you are doing a one time creation of tables in Amazon Redshift, using the Copy Operation may provide faster performance.

MergeKey Field

When using the Merge Block, you can assign a field from the source that contains a unique value for each record to the MergeKey target field. For example, if you are merging Salesforce Contacts into your table, you might use name of the Salesforce field that contains the Contact ID, such as Id.

When a Map containing a Merge Block runs, the field assigned as the MergeKey is used to delete any records with matching keys. Then, the new records are appended to the table in Amazon Redshift. For example, if you are merging Contacts into a table in Amazon Redshift, the Merge operation compares the value of the Id field in each source record to the Id values in the target table and deletes any records that match before appending the source records to the target table.

Note: If you enter a field name in the MergeKey field that does not exist in the target table, no matches are found and all source records are appended to the target table.

If you enter a field name in the MergeKey field that does not have a unique value for each record, such as State, all records with matching values are deleted, and then the source records are appended to the table. For example, you could delete all Contact records for NH because you had one Contact record in your source data that had a value of NH in the State field.

TableName Field

The TableName field is required but does not have to match the table name in the Source data. You can modify it to anything you need and that becomes the name of the table created in Amazon Redshift.

Other Fields

Other fields shown in the target side of the Fields tab are used to normalize source data when it is written to the target. For example, TimeFormat, is used to specify the format for date time fields when stored in Amazon Redshift. If you specify "auto", Amazon Redshift recognizes and converts the inbound date or time format. For information on how each field can be used, hover over the field name.

TIBCO Scribe® Online API Considerations

To create Connections with the TIBCO Scribe® Online API, the Amazon Redshift Connector requires the following information:

Connector Name

Amazon Redshift

Connector ID

CBEC6FB6-DFA4-4D2C-9719-66A97736B395

TIBCO Scribe® Online Connection Properties

In addition, this Connector uses the Connection properties shown in the following table.

Note: Connection property names are case-sensitive.

Name Data Type Required Secured Usage

Host

String

Yes

No

 

Port

String

Yes

No

 

Database

String

Yes

No

 

User

String

Yes

No

 

Password

String

Yes

Yes

 

AwsAccessKeyId

String

Yes

No

 

AwsSecretAccessKey

String

Yes

Yes

 

AwsStorageRegionKey

String

Yes

No

 

More Information

For additional information on this Connector, refer to the Knowledge Base and Discussions in the TIBCO Community.