How do I create SQL data feeds for custom metrics? (NEW)

 

Our recent redesign simplifies the data transfer experience by combining raw and modelled data sources into a single object - data feeds. We’re gradually releasing this new feature to our customers.
If you see Data Feeds in the left navigation sidebar, you’ll learn how to create custom metrics for files stored in an SQL database in this article. If not, go here.

 

Structured Query Language (SQL) is used to manipulate and extract information from an SQL database.

In PowerMetrics, you can connect to data that's stored in an SQL database by executing an SQL query and retrieving information to create data feeds. Using those data feeds, you can visualize your data in custom metrics.

This article includes:

Supported drivers

The following JDBC drivers are supported:

  • MS SQL (Microsoft SQL Server)
  • MySQL
  • Oracle Thin
  • Oracle OCI
  • Sybase SQL Anywhere
  • PostgreSQL
  • Firebird
  • DB2

Note: ODBC drivers are not supported. Have you added our Klipfolio IP addresses to your allow list? See the full list of Klipfolio's Allow list IP addresses.

*If you're connecting to Amazon Aurora (AWS), make sure you have Klipfolio's IP addresses set up in your DB security group. Learn more.

Creating an SQL database data feed

To create an SQL database data feed:

  1. Add a new data feed by clicking the + button beside Data Feeds in the left navigation bar. (See below.)

  1. Click Select data.
  2. On the Where is your data? page, under Core Data Services, click SQL query. (See below.)

  1. On the Configure data service page, fill in the following fields and select whether you want to Include column headers:
    • Host
    • Port
    • Database
    • Driver
    • Username
    • Password
    • SQL Query
  2. If accessing a database behind a firewall, click Use an SSH tunnel and type the required credentials.
  3. Click Get data.

  1. 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.

  1. Give the data feed a name and, optionally, enter a description.
  2. 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.

Next steps - Creating SQL database custom metrics

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

To create custom metrics using a data feed:

  1. In the left navigation sidebar, click Data Feeds to open your list of data feeds.
  2. Select the data feed you want to use for your custom 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.)

  1. Choose settings for your custom 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 tips

There are two options for querying an SQL database:

  • Port forwarding to an external IP address
  • Using an external SSH tunnel

If you followed the instructions above but still cannot connect to Klipfolio, consider the following:

  1. Have you added our Klipfolio IP addresses to your allow list? See the full list of Klipfolio's Allow list IP addresses.
  2. 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.
  3. For multiple instances of SQL server, check your SQL Server Configuration Manager to verify you have the correct port for the database to which you want to connect. The first SQL instance uses port 1433, other instances might use a different port number.
  4. Is the port open? Go to 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.
  5. For an SSH Tunnel, is the domain account valid?
  6. In Klipfolio, at the connect to an SQL database page, are you entering the IP address of the database server as the Host Name? Note: The Host field should not point to the SQL Server instance, just to the local host. For example, local host, not localhostSQLEXPRESS2012
  7. In Klipfolio, at the connect to an SQL database page, are you entering the actual database name at the Database field?
  8. Are you using an SQL Stored Procedure? When working with Klipfolio, you cannot use temporary tables or multi-selects in your Stored Procedure.

Tip: You can use a query of Select 1 to test an SQL connection.

Learn more

Have more questions? Submit a request