Data Sources

Our data source documentation contains information regarding the available data source features and settings, as well as instructions for connecting a new data source.

To view information about a specific data source, select Connections from the side navigation and choose your data source type.

Manage data sources

Debug

New connections

Each database has its own set of potential connection issues. Instead of documenting them all into a large tree of potential issues, Chartio has built error reporting and advice for fixing the errors directly into the setup wizard and direct connection forms, where they're more directly helpful to you. If these tools aren't enough to solve your problem, please don't hesitate to email us at support@chartio.com.

Existing connections

If you've lost your connection to a previously working data source, Chartio has built a nice interface to help you debug the issue. Choose Data Sources from the top navigation, select your data source from the list, and switch to the Debug tab.

Chartio will run a series of tests on the different aspects of the connection that could go wrong. If your connection is healthy, the interface should look like the following:

If something is wrong, the interface will tell you where it is wrong and provide a description of what might be happening and the steps you can take to fix it.

Query log

There are times when it may be helpful to view the actual queries that are sent to your database. Chartio allows you to view and download the query log for each connected data source.

View query log

Select Data Sources from the top navigation and choose your data source from the list. Switch to the Query Log tab.

You'll be able to view the Start Time, Query SQL, Errors (if any), and Query Duration. Query Duration includes only the time it takes your database to run the query—it does not include chart rendering time. You can also filter by dashboard or chart in the dropdown menus if you're troubleshooting for a particular chart. The Query column also contains links back to the chart and dashboard.

You can download the query log as a CSV, and even upload it back to Chartio to analyze your query data.

Canceling running queries

Another neat feature of the query log is that it allows you to monitor the duration of any queries that are currently running, and cancel them if desired.

Maximum Query Duration

Chartio allows you to set a Maximum Query Duration for your data source. Any queries that take longer than the amount of time specified will be cancelled. Set this to a low value to prevent long-running queries from tying up resources on your database server and slowing down other queries.

Cache

Chartio allows you to set the cache duration for your data source. This, along with your Dashboard refresh rate, determines how often your charts get fresh data.

The cache duration tells Chartio how long to keep chart data before asking your database for fresh data. If you would like fresh data very often, you can set the cache duration to a lower time interval. On the other hand, if you are worried about database performance and want to limit how often new queries are sent to your database, you can set the cache duration to a higher value.

Your Dashboard refresh rate also has an effect on how often you see fresh data. For instance, if your data source cache duration is set to 30 minutes but your Dashboard refresh rate is set to 1 hour, your Dashboard will only check for new chart data every hour.

For more details, see our Knowledge Base article on how caching works.

Setting the cache duration

Select Data Sources from the top navigation and choose your data source from the list. In the General tab, you'll see a setting labeled Cache Duration. Adjust this setting as desired, and click Save to update.

Manually reloading charts

You can manually get fresh data for all the charts on a Dashboard by selecting "Refresh all data" from your Dashboard menu. To manually get fresh data for all the charts using one particular data source, click Clear Data Source Cache in your data source settings, and refresh your browser.

Read-only connections

By default, Chartio sets data sources to read-only at the connection level. This provides an additional layer of security against malicious queries.

Some databases, such as Redshift, disallow the creation of variables and temporary tables when connections are set to read-only. For this reason, we allow Redshift users to disable the enforcement of read-only in their data source settings if needed.

If you are seeing an error message like "Error: Transaction is read-only", disabling the read-only setting should resolve this issue.

Even with read-only disabled, your database is still protected from malicious queries in two ways:

  • a user-provided read-only login Chartio uses to connect to your database
  • Chartio's disallowed keywords list that blocks queries with malicious keywords

Query Mode

If you would like chart creators to use only Interactive Mode or SQL Mode, adjust this data source setting.

For existing data sources, any charts previously created in the disallowed mode will be viewable but not editable.

Moving your database connection

If you've moved your database from one server to another, follow the steps below to maintain your Chartio connection.

  1. Make sure that the Chartio user has been added to your new database with the same credentials
  2. Visit your data source settings (Data sources > your data source) and switch to the Connection tab
  3. Enter the new hostname for your database and the current password for your Chartio user. Click Save to test the connection and save the new connection information.

Schema

Admin permissions required to edit a data sources's schema.

When Chartio connects to your database, it automatically extracts your database schema. Chartio provides an editor for that schema we import, which allows you to perform the following actions:

  • Rename certain columns to make them more reader friendly
  • Hide tables and columns to simplify your data source for users
  • Add foreign keys if needed, and set custom join types
  • Change dimension and measure groupings
  • Create custom columns or tables
  • Set default aggregations for measures

Accessing your Schema

To find your schema, select Data Sources from the top navigation and choose your data source from the list. Switch to the Schema tab.

Default aggregation for measures

If you commonly use one particular aggregation type for a measure, you can set it as the default in the schema. When you use the measure in the Chart Builder, the default measure you choose will be selected.

To set a default aggregation, open the Schema tab of the data source's settings. Expand the table, click the measure you'd like to edit, and change the aggregation in the Default Aggregation dropdown.

Hide or display columns and tables

If you have a lot of tables and columns, it's often helpful to hide some of the ones you aren't using. This can make your schema easier to navigate when you're creating charts.

To hide a table or column, simply uncheck the Visible checkbox next to the table or column name.

You can also hide or display all columns or tables at once from the menu.

Refresh Schema

If you make any changes to your database schema, you'll want to refresh your schema in Chartio to make sure we have the latest version.

To refresh your schema, click the Refresh Schema button at the top of the schema editor.

You'll be redirected to a page to view the schema changes, along with a list of Interactive Mode charts that will be affected by the changes. You can choose to cancel the Schema refresh if you would like to fix the affected charts first.

How renamed or removed columns affect Interactive Mode charts

If a column or table's SQL name changes in your Schema, or a column or table is removed, any references to it in Interactive Mode will be removed when you refresh the schema. The charts will already be failing due to the missing name reference, but it may be worthwhile to hold off on refreshing the schema if you'd like to update the affected Interactive Mode charts first.

Foreign Keys

Foreign keys tell the database how to join two or more tables together in the same query. For most database types, Chartio can import any foreign keys you have set up already. If Chartio is unable to import your foreign keys, or none are set up, you may need to define the foreign key relationships manually in the Schema, located in your Data Source's settings.

Foreign key example

We want to make a chart to count the number of activities per username. Activity information is stored in the Activity table, and each activity has a User Id associated with it. However, Username is in the Users table. This means we need to link the Users and Activity tables together, through the User Id column. When we do that, we can get any related information from the Users table when we query the Activity table.

View detailed example

Foreign key joins

Once foreign key relationships are defined, the Chart Editor will automatically perform joins for you when using columns from related tables. The tables do not need to be directly related; Chartio will automatically find the closest path between a database's tables to join the two that you're using.

Setting custom join types

By default, all join relationships generated in Interactive Mode are explicit inner joins. However, it's possible to set a custom join type per foreign key.

To customize the join type between two tables, first find the column in your schema and click it to open its settings. Adjust the join type in the dropdown below the Foreign Key setting. Click Save when finished.

Now whenever the foreign key is used in Interactive Mode, Chartio will use the join type you've selected.

Timezone support

Chartio offers two types of time zone support: a Time Zone setting, and a UTC Offset. Databases that support setting a named time zone have a Time Zone setting. Databases that do not support this have a UTC Offset, which subtracts or adds hours to timestamps to adjust them.

Check your database type below to see how time zones are supported.

Time Zone setting

The Time Zone setting uses named time zones, such as America/Los Angeles. Named time zones are beneficial because they respect daylight savings time.

Setting the Time Zone

From the top navigation, select Data Sources and choose your data source from the list. In the General tab, find Time Zone. Select your time zone from the dropdown menu, and click Save to update.

PostgreSQL, Data Stores

includes Twilio, Heroku, CSVs uploaded after 3/10/2016

Once set, the time zone is applied to any timestamp column (cast as text) or timestamptz column.

To check whether a time zone is being applied to a chart, check the Executed Query tab.

Using the Time Zone setting with PostgreSQL timestamp columns

Columns with data type timestamp must be converted to text in the query in order for the time zone to be applied. This is because PostgreSQL converts any datetime column to UTC before returning it. Timestamp columns used as Dimensions in Interactive Mode are automatically converted to text. Timestamp columns used as measures will need to be cast as text in SQL Mode; for use in Interactive Mode, use a Data Store or a custom column to cast the date column as text.

PostgreSQL queries in SQL Mode

The Time Zone setting is applied automatically to both Interactive Mode and SQL Mode queries for PostgreSQL.

MySQL

The Time Zone is only applied to columns with data type timestamp.

To check whether a time zone is being applied to a chart, check the Executed Query tab.

MySQL time zone tables

In order to use the Time Zone setting with MySQL, you will need to have the time zone tables in your database. MySQL includes the tables automatically, but they may need to be manually loaded into the database.

To check whether the time zone tables are already loaded, run the following query:

select * from mysql.time_zone_name;

If there are no results, see MySQL's documentation for instructions on Populating the Time Zone tables.

MySQL queries in SQL Mode

The Time Zone setting is applied automatically to both Interactive Mode and SQL Mode queries for MySQL.

Redshift

All Redshift datetime columns are UTC timestamps, and are fully compatible with the Time Zone setting.

Redshift queries in SQL Mode

To use the timezone setting in SQL Mode, wrap each instance of a timestamp column in your query using the following syntax:

CONVERT_TIMEZONE({TIME_ZONE}, date_column)

UTC Offset

For data sources that do not support setting a named time zone, we offer a UTC Offset. Chartio's UTC offset allows you to pick a value between -12 and +14 hours that will be used to automatically adjust your date and time values as they are returned from your database for charts created in Interactive Mode.

For Google Analytics, set the UTC Offset to match the timezone of your data.

Setting the UTC Offset

From the top navigation, select Data Sources and choose your data source from the list. In the General tab, find UTC Offset. Select your offset from the dropdown menu, and click Save to update.

UTC Offset in SQL Mode

Chartio saves your UTC offset setting into a variable that you can use in SQL Mode. It will require formatting your date and time fields using the syntax below.

SQL Server, SQL Azure
DATEADD(hour, {UTC_OFFSET.RAW}, date_column)
BigQuery
DATE_ADD(date_column, {UTC_OFFSET.RAW}, "HOUR")

Custom schema

Write SQL that generates custom columns or tables in your database. Rather than storing the data results, Chartio stores the SQL and inserts it directly into your Interactive Mode query.

Custom columns and tables simply generate nested SQL statements, and therefore cannot be referenced by name in SQL Mode.

Common use cases

  • Use a CASE statement to create a column that categorizes rows
  • Flatten your database by creating a custom table that joins multiple tables together
  • Create simple calculated columns, such as converting cents to dollars

Creating a custom column

Let's start with a simple example. Let's say our Payments column is formatted in cents. Rather than having to divide it by 100 every time we want a dollar amount, we'll create a calculated column to do this for us.

We'll navigate to our data source settings, and switch to the Schema tab. We want our column to be in the Payments table, so we'll scroll down to find this table, expand it, and click the Add Custom Column button.

Custom column settings

  • Grouping
    If you're creating a category or other groupable value, choose Dimension. If your value is numeric, choose Measure.

  • Aggregated
    If you are aggregating a measure, such as taking a count or an average, select the Aggregated checkbox.

  • Type
    Select the data type of your column. Note: this does not convert your column to the specified data type, it simply tells Chartio how the column can be used. To convert your column data type, use database-specific syntax in the custom column query.

In the pop-up modal, we'll set our new column settings and add our SQL as shown below. When finished, click Add Column.

Using your custom column

We'll open the Explore tab and select our data source. Custom columns appear in the data source just as any other column would. We'll drag the custom column into the Chart Creator.

Let's switch to SQL Mode to see what the generated query looks like. The custom SQL we've written to generate this column is inserted directly into the query.

Create a custom table

Custom tables can be especially useful for flattening two tables.

Start by navigating to your database schema, and select Add custom table from the dropdown.

Name your table, and add your SQL. In our example, we're joining three tables to get a rollup of User information. Click Add table when finished.

Chartio will evaluate your SQL and will display any error messages in the schema viewer.

Using your custom table

Custom tables appear in your data source schema just as any other table would.

When you use a column from your custom table in Interactive Mode, Chartio generates a sub-select query. An example of our chart and its generated query are below.

Usage tips

  • When a custom column or table is edited and re-saved, queries are automatically updated for all of the charts using that custom column or table.
  • Setting foreign keys on custom table columns works the same way as setting foreign keys on standard database tables.

Direct connection

Public vs. Private Databases

Chartio provides two methods of connecting to your database: Direct Connect and Tunnel Connect.

Direct Connect is the easiest method as it simply requires you to whitelist Chartio's IP address (52.6.1.1) and enter your connection details on the connection page.

If your database is on a private network and you do not wish to modify any firewall rules, you can use an SSH tunnel to make an outbound encrypted request from your network to ours instead.

Direct Connections

To connect a new data source, Select Data Sources from the top navigation and click + New Data Source. Select your data source type from the list. Fill out the connection form, and click Connect.

Most direct connections are straightforward, but some require a few extra connection steps. Data sources that require special instructions are listed below.

Amazon VPC

If your database server, RDS instance or Redshift cluster is on a private subnet of an Amazon VPC, follow our instructions example here.

Amazon RDS

RDS Security Groups

In order for Chartio to connect to your database, your RDS security group needs to be modified in order to white-label Chartio's inbound IP address. To view your security groups, access your RDS Dashboard.

Navigate to DB Security Groups from the left sidebar and select one of your security groups. A form for a CIDR will be presented. Please add the following IP address to your Security Group: 52.6.1.1/32

Endpoint

You will use your Amazon RDS endpoint and port number from the Instances section of your RDS Dashboard in the Host and Port fields when connecting your data source.

Connecting your data source

Adding Chartio's external IP to your Security Group will allow our server to connect to your RDS instance. However, you will still need to provide read-only credentials for authentication. Please follow the individual instructions for your database platform.

Amazon Redshift

Redshift Security Groups

In order for Chartio to connect to your database, your Cluster security group needs to be modified in order to white-label Chartio's inbound IP address. To view your security groups, access your Redshift Management Console.

Navigate to Security Groups from the left navigation bar and select one of your security groups. At the bottom of the page you will see "CIDR/IP to Authorize". Please add the following two IP addresses to your Security Group:

52.6.1.1/32

Endpoint

You can find the information needed to connect on the Clusters page of the Amazon Redshift Management Console. Use the information in the fields indicated below.

CSV Uploads

CSV files can be easily uploaded to Chartio and queried and visualized like any other data source. Uploading multiple CSVs together allows you to use each as table within the same database.

Preparing your CSV for Upload

  • File uploads are limited to 100MB each. Larger files can be separated into multiple files and appended.
  • If your data is currently in Excel, please see converting Excel workbooks to CSV files.
  • Your CSV should be in raw table format, with all data arranged in columns. See image below for reference.

Uploading and Processing Your Files

In the top navigation menu, click Settings, then select Data Sources from the dropdown.

At the top of your data sources list, click + New Data Source.

From the Databases list, choose CSV Upload.

Select one or more (up to 100) CSV files and select Open. Each file will become a separate table in the CSV data source. Click Upload when ready.

If you'd like, edit the Table Name field. Uncheck First Row Contains Column Headers if applicable, and manually enter header names. Review the data types Chartio has auto-detected for each column, and change if necessary.

Chartio tries to guess the date format of your columns, but it isn't always correct. Verify that the date format is correct, and edit if necessary. See our formatting reference table below.

Click Upload when ready.

View the Schema Editor to rename the data source, change the names of individual tables and columns, and create foreign keys between tables.

Adding CSV Data to an Existing CSV Data Source

You can update the data in an existing CSV in the following ways:

  • replace an existing CSV table
  • append to an existing CSV table
  • add a new CSV table

From your data source settings, click Upload alternate CSV files, located in the General tab.


Choose from the list what you would like to do with the new data: Append or Replace existing data, or create a new table in the CSV data source.

If you choose Append or Replace, a dropdown will appear. Select the table from the dropdown you would like to update.

2016-05-18_15-57-06.png

The file uploader will apear. Drag your file into the window, manually select the file, or paste the data in using the Manual input option.

Uncheck First row has column headers if applicable, and confirm the encoding setting is correct.

If you've chosen Append or Replace, you'll see an interface that will allow you to choose how new columns map to existing columns. Chartio will convert data types to match an existing column's data type whenever possible.

When you're satisfied with the settings and mappings (if applicable), click Upload New Data.

Date Formatting Reference

  Token Example output
YearYYYY2000
 YY00
MonthMMMMJanuary..December
 MMMJan..Dec
 M1..12 or 01..12
ISO WeekW1..53
 WW01..53
Day of MonthD1..31 or 01..31
Day of YearDDD1..365 or 001..365
Hour (24)H1..24 or 01..24
Hour (12)h1..12 or 01..12
AM / PMAAM, PM or am, pm
Minutem1..59 or 01..59
Seconds1..59 or 01..59
Sub-secondS1 or 01 or 001
TimezoneZZ-07:00, -06:00 ... +06:00, +07:00
 Z-0700, -0600 ... +0600, +0700
Unix TimestampX1381685817
Tips
  • dates can include special characters above by using brackets. For example, Y2015-W01 has format [Y]YYYY-[W]WW
  • for 6-digit milliseconds, use SSS[000]

DB2

Requires no additional steps beyond filling out the connection form. See our basic connection instructions.

DashDB

Requires no additional steps beyond filling out the connection form. See our basic connection instructions.

Elasticsearch

Elasticsearch requirements

In order for Chartio to connect to your Elasticsearch data, you will need:

  • Elasticsearch web server
  • A reverse proxy server with HTTPS and Basic authentication, either on the same server as Elasticsearch or an intermediary server

Here's an example config for nginx as a reverse proxy using Basic authentication and HTTPS:

server {
    listen 9201 ssl;
    ssl on;
    {{ insert standard ssl settings, e.g: https://wiki.mozilla.org/Security/Server_Side_TLS#Nginx }}
    auth_basic "Restricted";
    auth_basic_user_file /etc/path/htpasswd;

    set $upstream_endpoint http://localhost:9200;
    location ~ {
        proxy_read_timeout 1260;
        proxy_pass $upstream_endpoint;
        proxy_http_version 1.1;
    }
}

Elasticsearch connection form

The Schema index refers to a single index we will use to reflect a schema. This is often "logs-*", or similar.

The Search index refers to which indexes we will issue search queries against. To list your indexes, replace your hostname in the command below:

curl host.domain.com/_cat/indices?v

See the ElasticSearch documentation for more details.

Note: Chartio supports one search index per ElasticSearch data source. If you have more than one search index you'd like to use, you'll need to add them as separate data sources in Chartio.

Google Analytics

If you're already signed into the Google account you want to associate Chartio with, you'll be prompted with a simple permissions request to allow access. If you're not, you'll need to sign in to your Google account before access is allowed.

If you plan to use Google Analytics custom segements, make sure the login you use for Chartio to connect to the database has permissions to access those segments.

Once you've granted permission, you'll be directed back into the Chartio interface. You can now create charts with your Google Analytics data.

Can't find a metric in the Chart Creator?

Chartio hides some lesser-used metrics by default to make your data easier to navigate. If you can't find a column, please see our instructions for unhiding metrics in your schema.

Google BigQuery

Permissions needed: BigQuery project Editor or Owner

First, obtain the following information from your BigQuery account:

  • Project ID
  • Dataset ID

You'll be prompted with a simple permissions request to allow Chartio access to your BigQuery data.

Once you've granted permission, you'll be directed back into the Chartio interface. Enter an Alias, your Project ID and Dataset ID. If you'd like to add a shared dataset, see instructions below.

Add a BigQuery shared dataset

If someone shares a BigQuery project with you, use the Advanced tab in the BigQuery connection form to add it to Chartio.

  • Query Project ID: the BigQuery project you have Editor/Owner permissions for
  • Dataset Project ID: the project name of the shared dataset
  • Dataset ID: the name of the shared dataset

Google Cloud SQL

In Chartio's connection form, copy Chartio's IP address, which is located under the Hostname or IP field.

Open a new browser window and go to your Project list in the Google Developers Console. Select your Project from the list. Choose Cloud SQL from the left navigation, then select your Google Cloud SQL instance. Click the Access Control tab.

Click Add new under the Authorized Networks section of your Access Control settings, and add Chartio's IP address that you copied earlier.

You'll see your Google Cloud SQL IP address on this tab as well, which you'll need to enter into the Chartio connection form.

Google Cloud SQL SSL Connection

MySQL users can also generate a certificate and configure their database to connect to Chartio with SSL encryption. Instructions on creating a certificate and SSL connection can be found in the Google Cloud SQL documentation.

Heroku

To connect a Heroku instance to Chartio, you'll need to retrieve the connection url from your Heroku account.

Log in the Heroku Postgres interface. You should see a list of your Apps. Click on the App that corresponds to your database - it will likely have the same name as your database.

Click on the Settings tab, then click Reveal config vars. Copy the value for DATABASE_URL, and enter it into the connection form in Chartio.

Security

Heroku wisely enables PostgreSQL SSL connections by default. Every data connection Chartio makes is encrypted this way.

MongoDB

Chartio supports MongoDB through a tool developed by Stripe called MoSQL. MoSQL creates and live-updates a PostgreSQL instance of your MongoDB data. As an added bonus, you'll be creating a backup copy of your data, which is something we always recommend.

We have several customers, including MongoHQ, who are successfully using MoSQL to import their MongoDB databases to Chartio.

The basic steps are:

  1. Create PostgreSQL database
  2. Follow Stripe's instructions for syncing your MongoDB data to your PostgreSQL instance
  3. Connect PostgreSQL data source to Chartio

MySQL

If your database is on Amazon RDS, please visit the RDS setup documentation to first enable Chartio in your security groups before following these steps.

MySQL read-only user

Chartio requires a read-only user for connecting to your database. Here is a sample GRANT statement for creating a read-only user:

GRANT SELECT, SHOW VIEW
ON $database_name.*
TO $user@`52.6.1.1` IDENTIFIED BY '$password';
FLUSH PRIVILEGES;

Where $database_name, $user, and $password are described in the Chartio connection form. Copy this command and paste it into a MySQL shell to create the user. This will grant the user read-only access to ALL tables in your database. If you would like to restrict access to only certain tables in your database, please see the knowledge base article.

MySQL SSL Connection

Instructions on creating a certificate and SSL connection can be found in the MySQL SSL connection documentation.

PostgreSQL

If your PostgreSQL database is hosted on Heroku or Amazon RDS, please follow those instructions for connecting your instance.

PostgreSQL read-only user

The Chartio connection form require a read-only username and password, which you'll need to create on your PostgreSQL database before you submit the connection form.

Allowing Chartio to connect to your database

By default, PostgreSQL restricts connections to hosts and networks included in the pg_hba.conf file. You may need to add Chartio's IP address to this file to allow connectivity to your database.

To allow the user 'chartio_read_only' to connect to the database 'mydatabase' from Chartio's IP address you would add the following lines to pg_hba.conf:

host mydatabase chartio_read_only 52.6.1.1/32 md5

You may need to restart your PostgreSQL server for the changes to take effect.

For more details on modifying the pg_hba.conf file consult the PostgreSQL documentation.

Presto

Presto connections are supported via SSH Tunnel only. Please see our Tunnel Connection instructions for information on setting up an SSH tunnel.

One the tunnel is set up, add a new Presto data source in Chartio via Data Sources > Add a Data Source. You'll need the following connection details:

Catalog
You can find a list of catalogs in the etc/catalog directory of your Presto installation. Catalogs will each have their own file with the extension .properties.

Schema Name
In the etc/catalog directory, find your Catalog. Open its .properties file to locate schema information.

SSH public key
Your public key is required to authorize Chartio to connect to your Presto database. Locate it on your Presto server and paste it into the Chartio connection form.

For more information, please see the Presto Documentation.

Rackspace Cloud

If your Rackspace instance is located on an ORD Rackspace server, Chartio can connect using your private IP address. Simply enter your private IP address into the MySQL connection form in Chartio.

If your Rackspace instance is located in a region other than ORD, you'll need to set up a Cloud Load Balancer, which will provide you with a public IP address. You can use this public IP address in the MySQL connection form to connect your Rackspace instance to Chartio.

Information about setting up a Cloud Load Balancer is available in Rackspace's documentation.

Segment

1app-settings.png

To use Chartio with Segment data, you will first need to enable Segment to pipe your data into your warehouse.

Log in to Segment and navigate to the Warehouse tab. If you do not have a warehouse enabled, choose the “Add Warehouse” button and enter your warehouse credentials in the setup flow. Otherwise, choose the warehouse you want to connect with Chartio and click the tile to “View Details”.

Once in the options menu for your chosen warehouse, navigate to Settings > Connection and copy those credentials.

Paste the values from the Segment connection settings into the input boxes in the Chartio connection form and click Connect.

You will be notified via our in-app messaging as well as by email when the connection is complete and your data is ready to use.

 

SQL Server

Chartio supports connecting to your SQL Server instance via a direct connection from our server to yours. For SQL Server instances hosted on Windows Azure or Amazon RDS, please refer to the instructions linked.

Currently, Chartio supports SQL authentication only.

Chartio officially supports Microsoft SQL Server 2008 R2, 2012, and 2014. Earlier versions of SQL Server may be compatible, but are not tested, and support is not guaranteed.

Before you complete the connection form in Chartio, you will need to create a read-only user that Chartio can use to connect.

Select your SQL Server version from the drop-down menu and enter the necessary information in the corresponding fields. When finished, click Connect.

SQL Server (Azure)

Before connecting SQL Server to Chartio, log in to your Windows Azure account, select SQL Databases from the navigation, then select the name of the database you would like to connect to.

Make note of the server name and port number at the bottom of the page. This is the information you will use in the Host and Port fields when creating a connection to Chartio. Select the Dashboard link at the top of the page.

Scroll down and select the link to Manage allowed IP addresses in the bottom right-hand corner of the screen.

Add a new rule using the following IP address:

Rule Name: Chartio
Start IP Address: 52.6.1.1
End IP Address: 52.6.1.1

Click the Save button at the bottom of the page.

Once you have collected your information from your Windows Azure management console and added access to Chartio's external IPs, you can follow the instructions to connect SQL Server to Chartio.

Vertica

From the top navigation, select Data Sources. Click the +Add a Data Source button, and choose Vertica from the list.

You'll see the following form:

Enter your connection details and click Connect. Once the connection is established, Chartio will retrieve your schema and the data source will be ready for use.

Tunnel connection

Chartio provides two methods of connecting to your database: Direct Connect and SSH Tunnel Connect.

Direct Connect is the easiest method as it simply requires you to whitelist Chartio's IP address (52.6.1.1) and enter your connection details on the connection page.

If your database is on a private network and you do not wish to modify any firewall rules, you can use our SSH Tunnel Connection instead. This entails setting up an SSH tunnel to make an outbound encrypted request from your network to ours.

Connection Client

Chartio's Connection Client has been discontinued for new connections. Existing connections are still supported, but any new connections will need to use another SSH tunnel manager such as autossh. If you would like to switch your existing Connection Client connections over to autossh, please see our documentation.

Dependencies

  • Your database listening on all interfaces
  • Ability to make an outbound connection to connect.chartio.com on port 22. Run telnet connect.chartio.com 22 from the command line to test.

Set-up instructions

These instructions use autossh on a Ubuntu/Debian server. For other operating systems, adjust the syntax as needed. We recommend autossh, but feel free to use your preferred SSH tunnel management tool.

SSH key

Create an SSH key if you don't have one yet.

Install autossh

On your database server, run the following to install autossh:

sudo apt-get install autossh

Add a new data source in Chartio

In the top navigation, select Data Sources. Click the + New Data Source button. Select your data source type, and switch to the SSH Tunnel Connection tab in the connection form.

Before you begin filling out the form, make sure you've created a read-only user that Chartio can use to connect to your database.

SQL Server

Postgres

Fill out your Local host, Database port, and your Private key location. Chartio will then generate the commands needed to set up the tunnel.

Before you start your autossh tunnel, test the SSH connection using the command provided in the form. Once that is successful, use the command provided to start your autossh tunnel.

Finally, click Test Connection to test the connection and finish the set-up process.

If there are any errors creating the connection, you will be redirected back to the form where you can edit as needed.

Create crontab entry

Once you've confirmed that the connection is up and running, you'll want to add a crontab entry to reconnect the SSH tunnel on reboot. It should look something like this:

@reboot autossh -M 0 -f -N -R 12345:127.0.0.1:5432 tunnel12345@connect.chartio.com -g -i ~/.ssh/id_rsa -o ServerAliveInterval=10 -o ServerAliveCountMax=1 -o ExitOnForwardFailure=yes

Additional considerations

Appending "-M 0" disables the monitoring port by default. From autossh's documentation:

If you are using a recent version of OpenSSH, you may wish to explore using the ServerAliveInterval and ServerAliveCountMax options to have the SSH client exit if it finds itself no longer connected to the server. In many ways this may be a better solution than the monitoring port.

Additional connection string settings, such as logging, are available. See autossh documentation for details: http://www.harding.motd.ca/autossh/README

Troubleshooting

Ensure you have added a crontab entry for the connection

Confirm you have added a crontab entry to restart the connection on reboot

If switching from Connection Client, ensure the SSH process is not running

Run:

ps aux | grep ssh

If you see an SSH connection string for the connection that you have switched to autossh, kill it using the instructions here.

Ensure tunnel exits when port forwarding fails

Add the following parameter to the end of your connection string to ensure the tunnel connection exits if port forwarding fails. This should help trigger an automatic tunnel restart when needed.

-o ExitOnForwardFailure=yes

Enable verbose logging

The best way to troubleshoot connection issues is by viewing the connection logs. Run the following to check whether autossh is currently generating logs:

sudo grep autossh /var/log/syslog

If there are no results for the above command, add the environment variables below to your shell (for example, in your ~/.bashrc file). They'll set the location where autossh logs will be saved, and will set the log level to return the most verbose and detailed logs.

export AUTOSSH_LOGFILE=/var/log/autossh
export AUTOSSH_LOGLEVEL=7

Start connection in debug mode

To do this, remove '-f' from your autossh connection string and add AUTOSSH_DEBUG=1 to the beginning. Our example string now looks like this:

AUTOSSH_DEBUG=1 autossh -M 0 -N -R 12345:127.0.0.1:5432 tunnel12345@connect.chartio.com -g -i ~/.ssh/id_rsa -o ServerAliveInterval=10 -o ServerAliveCountMax=1 -o ExitOnForwardFailure=yes

This will output the connection log, including any connection errors. If you do not understand the error messages, please send the output to support@chartio.com.

Generate an SSH key

Check for an existing SSH key

First, we'll want to check for existing SSH keys on your server. From the command line, enter:

ls -al ~/.ssh

If a key exists, skip ahead to the Retrieve your public key step.

Create an SSH key

Run the following command to generate an SSH key. We recommend including a comment to label the key.

ssh-keygen -t rsa -C "Your comment here"

You'll be prompted to choose your save location. To use the default location (recommended), press Enter.

Next, you'll be prompted to enter a passphrase. Do not enter a passphrase. Press Enter twice to continue.

Retrieve your public key

Navigate to your SSH folder, or run the following command to find your public key:

ls -la *.pub

Retrieve the contents of your public key file with the following command. Edit the file location as needed.

cat /Users/username/.ssh/id_rsa.pub

Keep your public key handy - you'll need to enter it in the connection form on chartio.com. Add a new data source from the website, then follow the instructions there to finish setting up the connection.

SSH permissions

Permissions should be set up correctly by default, but you can use the following commands to correct permissions if needed:

chown -R username ~/.ssh
chmod 700 ~/.ssh
chmod 600 ~/.ssh/id_dsa

Uninstalling the tunnel connection

Remove crontab entry

Run crontab -e to enter the crontab editor and comment out the autossh command using ###. Copy the autossh command - you'll need it in the next step.

Stop the tunnel

Paste the autossh command from the previous step without '@reboot', and append 'stop' to the end.

autossh -M 0 -f -N -R 12345:127.0.0.1:5432 tunnel12345@connect.chartio.com -g -i ~/.ssh/id_rsa -o ServerAliveInterval=10 -o ServerAliveCountMax=1 -o ExitOnForwardFailure=yes stop

Moving the tunnel connection

Uninstall connection on old server

Follow uninstall instructions.

Move SSH public and private key to new server

Install autossh on new server

Follow standard installation instructions, using your existing SSH key instead of creating a new one.