Accessing Data from Amazon Redshift

With Spotfire®, you can connect to, access, and analyze your Amazon Redshift data. In this article, you learn how to use Spotfire’s built-in data connector for Amazon Redshift.

With the Amazon Redshift connector, you can also access shared data. This enables you to perform cross-database queries (inter-account and cross-account). For more information about data-sharing, see Announcing Amazon Redshift data sharing.

Before you start

Here are some things you need before you can access your Amazon Redshift data in Spotfire:

Install the Amazon Redshift ODBC Driver on your computer. For help finding the right driver, see Drivers and data sources in Spotfire. Also look over the system requirements for the Amazon Redshift connector.
Gather the information that is required to connect to your Amazon Redshift cluster. It often includes server address, login credentials, and other connection settings. The person who is the administrator for Amazon Redshift in your organization can help you.

Creating a new connection to access data from Amazon Redshift

To access your Amazon Redshift data in Spotfire, create a connection to your Amazon Redshift cluster.

In Spotfire, open the Files and data flyout and select Connect to.
In the list of connectors, select Amazon Redshift. Then click New connection.
In the Amazon Redshift Connection dialog, enter the information for your Amazon Redshift cluster. Typically, the following information is required:

Server
The address to your Amazon Redshift cluster. This is sometimes referred to as the endpoint of the cluster.

Add the port number at the end of the address, preceded by colon:
my-redshift-cluster.example.com:5439
Authentication method
The way you want to log in to your Amazon Redshift cluster.
SSL mode
Select if you want to use encryption in the connection.
Username and password
Most authentication methods require that you enter your username and password (or similar credentials) to log in.
Database
The name of the Amazon Redshift database where your data is available.

Note: When connecting to an inbound shared database, you cannot specify it directly in this field. Instead, you must enter a local database (i.e. a database that is not shared) from the same cluster, and then you can see the available inbound shared databases in the Views in Connection dialog.
Refer to Data sharing concepts from the Amazon Redshift documentation for more information about inbound and outbound datashares.

Tip: There are more settings you can use to configure the connection. To learn more about the available settings, see Details on Amazon Redshift Connection.

When you have entered the required information, click Connect.
In the next step, the Views in Connection dialog, select the data you want to analyze in Spotfire. When you are happy with your selection, click OK.

Note: For shared databases, the top level in the hierarchy displays both the catalog name and the schema name as catalog_name.schema_name. Spectrum schema names are displayed in the same way (catalog_name.spectrum_name).
The final step is a summary of the data you are about to add to your analysis. Here you can select if you want the data tables to be External (for in-database analysis), Imported, or loaded On-demand.
To finish creating your data connection and add the data tables to your analysis, click OK.

Tip: When you have created your connection, you can share it in the Spotfire library. Then you and your team members can reuse the connection and easily access that Amazon Redshift data in other analyses.

Access data from Amazon S3 with Redshift Spectrum

If you have data files in Amazon S3, you can access that data in Spotfire with the connector for Amazon Redshift. To make your data from Amazon S3 accessible in Spotfire, use the Redshift Spectrum functionality in Amazon Redshift.

When you have set up Redshift Spectrum tables for your data files in Amazon S3, connect to your Amazon Redshift cluster in Spotfire. The Redshift Spectrum tables show up as data tables that you can select in the Views in Connection dialog.

What types of S3 can I access data from?

What types of data files in Amazon S3 you can access depends on Redshift Spectrum. To learn about what types of data files Redshift Spectrum supports, see the official documentation from Amazon.

Also remember that in Spotfire, the connector for Amazon Redshift might not support all data types available in your data. If there are unsupported data types in your data, those columns do not show up in Spotfire. See Amazon Redshift Data Types.

Authentication settings in settings profiles and credentials profile

Settings profiles

You can store settings from the Settings for authentication method table (see Details on Amazon Redshift Conneciton) in a settings profile that you can reuse. The purpose is to be able to store settings and their values without having to store the settings in the analysis file. Then you can use those settings automatically when you open a connection in a web client or Automation Services.

A settings profile is similar to a credentials profile. The main difference is that a settings profile does not include the username and password. That means that you can store some settings, which you do not want to store directly in the analysis file, and still have the user enter the username and password when they open the connection.

Tip: One use case for settings profile is to store the Client ID and Client Secret if you use the authentication method Azure AD.

Settings profiles are stored in the web client service configuration, in the configuration file Spotfire.Dxp.Worker.Host.exe.config.

Adding settings profiles for Amazon Redshift in a web client service configuration:

On the computer running Spotfire Server, export the service configuration you want to edit. See the instructions in Manually editing the service configuration files, in the Spotfire Server and Environment Installation and Administration manual.
In the file Spotfire.Dxp.Worker.Host.exe.config, locate or add the Redshift connector settings; Spotfire.Dxp.Data.Adapters.Redshift.Properties.Settings. Add your settings profiles within.
Example:
<Spotfire.Dxp.Data.Adapters.Redshift.Properties.Settings>
   <setting name=”SettingProfiles” serializeAs=”Xml”>
      <value>
         <profiles>
            <entry profile=”MySettingsProfile”>
               <setting name="SecretAccessKey">MySecretAccessKey</setting>
               <setting name="ClientID">MyClientID</setting>
            </entry>
            <entry profile=”MySecondSettingsProfile”>
               <setting name="SecretAccessKey">MySecretAccessKey</setting>
            </entry>
         </profiles>
      </value>
   </setting>
</Spotfire.Dxp.Data.Adapters.Redshift.Properties.Settings>
Save your changes. Then import the service configuration by following the instructions in the Spotfire Server and Environment Installation and Administration manual.

Note: If you have multiple web client services and Automation Services instances, the settings profile must be available on all web player services that you want to use the settings profile on.

Using a settings profile in a connection to Amazon Redshift:

Edit or create a new connection to Amazon Redshift (Files and data > Connect to > Amazon Redshift > New connection).
In the Amazon Redshift Connection dialog, on the Advanced tab, enter the name of the settings profile in the Settings profile field.

Credentials profiles

You can use a credentials profile to log in automatically when you open your Amazon Redshift connection in the Spotfire web client or Automation Services. If your connection has mandatory settings in the Settings for authentication method table, and you do not save the setting values in the connection, you must add those settings in your credentials profile.

To store values for settings from the Settings for authentication method table, add the setting names and their values to the password setting in your credentials profile.

Add the settings as a JSON table. The format of the table is:

{“password”:”mypassword” “firstSettingName”:”value” “secondSettingName”:”value”}

For example:

<username>MyUser<username>

<password>{“password”:”mypassword” ”SecretAccessKey”:”mysecretaccesskey”}</password>

</entry>