Skip to main content

How do I create Amazon Redshift data feed metrics?

Updated

Visualize, discover, and act on your data with metric visualizations and dashboards.

Ready to get the most from the data that's stored in your Amazon Redshift database? Get started by adding some data feeds. When you're finished adding Amazon Redshift data feeds, you'll use them to power your metrics.

This article includes:

Should I create data feeds or connect directly to my Amazon Redshift warehouse?

In PowerMetrics you can connect to your Amazon Redshift warehouse and create either data warehouse metrics or data feed metrics. Which solution is better for you and your data?

  • Data warehouse metrics are the recommended solution for database and data warehouse infrastructures. They may be right for you if you have a lot of data and want scalability (the amount of data that can be handled is only limited by the capacity of your warehouse), have stringent security policies (data is stored and managed in your data warehouse) and demand higher performance (performance can be optimized by tuning your warehouse).
     
  • Data feed metrics are primarily used to connect to services using REST API to get data. They may be right for you if you process small amounts of data (the maximum data feed size is 10 MB. Each time the raw data is queried it needs to fit within this limit after the transformations are defined in the feed) and want a mixed storage model (metric history and data are stored in the metric layer in PowerMetrics and in the warehouse).

Adding an Amazon Redshift data feed

If you’re using Network Policies to restrict IP access, before adding a data feed, make sure you've added our Klipfolio IP addresses to your allow list. See the full list of PowerMetrics' Allow list IP addresses.

To add an Amazon Redshift data feed:

  1. Add a new data feed by clicking the + button beside Data Feeds in the left navigation bar. (See below.)
  2. Click Select data.
  3. On the Where is your data? page, click Amazon Redshift. (See below.)
  4. On the Configure data service page, fill in the following fields:
    • Host: Enter the URL for the server that hosts your Amazon Redshift database.
    • Port: Port 5439 is entered here by default. As per Amazon Redshift documentation, you can change to another port within the range of 5431-5455 or 8191-8215.
    • Database: Enter the name of the database you want to connect to.
    • Driver: Select PostgreSQL (Legacy) from the drop-down list.
    • Username: Enter your Amazon Redshift user name.
    • Password: Enter your Amazon Redshift password.
    • SQL Query: Enter the desired SQL query, replacing the placeholder text <databaseTable> with your own database table name.
  5. Select whether to Include column headers.
  6. At Connection security, if you select Use SSL/TLS, optionally also select to Turn off certificate validation (not recommended) or Use TLS v1.2.
  7. If the database is behind a firewall but accessible using an SSH tunnel, click Use an SSH tunnel and enter the required credentials.
    (See below for an example of configuration settings for an Amazon Redshift custom data feed).
  8. Click Get data.
  9. Ensure this is the data you're looking for, then click Continue.
  10. You're taken to the data feed editor, where you can make changes to the data feed or save it as-is. If you want to modify the data feed, go to this article for detailed editing information.
  11. Give the data feed a name and, optionally, enter a description.
  12. Click Save data feed.
    The data feed is added to your account and is ready to use for custom metrics. To see a list of all your data feeds, click Data Feeds in the left navigation sidebar.

Creating Amazon Redshift metrics

Now that you've created one or more Amazon Redshift data feeds, you can return to them and use them to make metrics. A single data feed can be used to create a single or multiple metrics.

To create Amazon Redshift metrics:

  1. In the left navigation sidebar, click Data Feeds to open your list of data feeds.
  2. Select the Amazon Redshift data feed you want to use for your metric.
  3. Click either the +Add metric button in the top right corner of the window or open the Metrics tab and click +Add metric. (See below.)
  4. Choose settings for your metric and click Save metric. If you need help, go here to learn more.
    The metric is added to your list of metrics, accessed by clicking Metrics in the left navigation sidebar.

Troubleshooting your connection

If you followed the instructions in the procedures above but still can’t connect to Klipfolio, consider the following:

  • Have you added our Klipfolio IP addresses to your allow list? See the full list of PowerMetrics' Allow list IP addresses.
  • Is your database's external IP address publicly accessible and are you able to resolve the IP address to your database server? To test internally, ping or telnet your database's IP address. If unsuccessful, check your firewall configuration.
  • Is the port open? Go to the Port forwarding tester to test your port's status. If the port is not open, it might be a bad route or the destination SQL device might not be properly configured to listen to that port.
  • If you’re using an SSH tunnel, is the domain account valid?

Troubleshooting your query

Here are a few troubleshooting ideas you can try if you're having issues with your Amazon Redshift query.

  • Confirm that the SQL connection is working by using a query of Select 1. This should return the result of 1.
  • Make sure your query isn’t trying to bring in too much data. Klipfolio supports a maximum file size of 10 MB. If you’re returning more than 10 MB of data, your query may not work. To see if this is the issue, try adding a LIMIT to your query (e.g., LIMIT 1000) to reduce the amount of data being returned.
  • Confirm that it isn't taking too long to bring in your data. Klipfolio includes a global setting of 80 seconds within which to bring in data. If you think this might be the issue, try reducing the amount of your data so you stay within this range.

Related articles