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.

Still, here are a list of some common and general issues with connection:

  • Is your database running? - Double check that you can manually connect to it.
  • Is your database hosted on Amazon's RDS? - You'll need to add Chartio to your RDS Security Group

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. Visit your Settings, navigate to Data Sources, select your Data source from the list, and click Debug.

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

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

Choose your data source from the list, and switch to the Query Log tab.

You'll be able to view the Start Time, Query SQL, Errors (if any), and Query Duration. 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.

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.

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 browser will only reload with new data once per hour.

Setting the cache duration

From the top navigation, select Settings, then select Data Sources from the dropdown menu. Choose your data source from the list. In the Basics 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.

Timezone support

Chartio offers a UTC Offset feature in your data source settings which allows you to modify the time your data is displayed in.

Many databases default to UTC time. 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.

Google Analytics users: the timezone for your site can be changed from your GA Admin console. Please refer to Google's documentation here. You should always set the UTC Offset for your Google Analytics data source to match the timezone configured for your site.

Setting the UTC Offset

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

Choose your data source from the list, and you'll be redirected to the settings for that data source. In the Basics tab, find UTC Offset. Select your offset from the dropdown menu, and click Save to update.

Now all of your queries in Interactive Mode using this data source will have their date and time columns adjusted by the UTC offset value you've selected.

UTC Offset in Query Mode

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

Our example column is named Created_Date, you'll just need to change the column name to match your own.

MySQL, Google Cloud SQL CONVERTTZ( CreatedDate,'+00:00', { UTC_OFFSET } )

PostGreSQL, Heroku CreatedDate at time zone { UTCOFFSET }

SQL Server, SQL Azure, Amazon Redshift DATEADD( hour, { UTCOFFSET }, CreatedDate )

Google Big Query DATEADD( CreatedDate, { UTC_OFFSET }, "HOUR" )

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
  • Change dimension and measure groupings

Accessing your Schema

To find your schema, click Settings from the top navigation, then select Data Source from the dropdown menu. Choose your data source from the list, then switch to the Schema tab.

How to Edit

Click the column name you'd like to edit, and a form will expand with all of the editable options for that column. Click Save to update your changes.

What you can Edit

  • Column Alias: how your column name appears in the Chart Creator
  • Content Type
    • Date (MySQL DATETIME or Postgres Timestamp)
    • GIS (Postgres Geographic Information System data)
    • Number (MySQL and Postgres integers)
    • String
    • Unix timestamp
  • Grouping: change a metric's grouping to Measure or Dimension
  • Foreign Keys (see example below)

Hide/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. If you change any column or table names that are already in use in your charts, those charts will likely break. We generally recommend avoiding column and table renames whenever possible.

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

Foreign Keys Explained

In order to build joined table queries in the Chart Creator's Interactive Mode, you may have to define the foreign key relationships in the Schema Editor.

Chartio automatically detects foreign key relations if your database type supports and stores the relationships. Some databases, such as MySQL and MyISAM, do not store these relationships and therefore must be configured by you manually.

Example
We want to join the Activity and User tables. To do this, we will join the User ID column in the Users table to the User ID column in the Activity table.

First, let's select the Activity table, and click the User ID column to edit it.

You'll see a section labeled Foreign Key. From the first dropdown we'll select the Users table, and then select user_id from the second dropdown.

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

Connections

To connect a data source, click Settings in the top navigation, and select Data Sources from the dropdown menu. At the top of your data sources list, click + New Data Source, and select your data source type from the list.

Details about foreign keys and joins
Currently, all join relationships defined in the editor are explicit inner joins. Once this relationship is defined, the Chart Creator interface 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.

If you'd like to see the join path that's being used, you can switch to Query Mode and inspect the query that's being generated.

Many connections require authorizing Chartio's IP address first. Select your data source type below to view specific connection requirements.

Amazon RDS

If your database is stored on Amazon RDS there are a few extra considerations you'll have to take in connecting it to Chartio. Amazon has some unique security group settings that mean you'll need to explicitly enable access to a Chartio server. The following documentation will walk you through those steps.

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. Paste the Chartio CIDR: 173.203.96.249/32 into the form and click Add to add Chartio to your security group.

Note to VPC Users: If you're using the Amazon Virtual Private Cloud (VPC) you'll need to set up an elastic IP for your RDS instance to make it accessible to Chartio's servers outside your VPC.

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

There are a few steps you'll need to take before you can connect a Redshift instance to Chartio.

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". Paste the Chartio CIDR: 173.203.96.249/32 into the form and click Authorize to add Chartio to your security group.

Note to VPC Users: If you're using the Amazon Virtual Private Cloud (VPC) you'll need to add a rule in your VPC Security Groups in the VPC Console. Please refer to Amazon's documentation.

RDS read-only user

Creating a read-only user requires that you connect to your Amazon Redshift database via a SQL client. If you do not have one installed, please follow Amazon's documentation to setup SQL Workbench and the JDBC PostgreSQL driver.

Once you have connected to your Redshift database run the following query, using unique username and password values, to create your new read-only user. You will need superuser privileges to add a new user.

CREATE USER chartio_read_only_user PASSWORD 'yourpassword';

Next, we need to grant the user select permissions to the desired tables in our database. Run this query to generate a list of statements that we will use to grant read-only access to your newly created user. Running this query will NOT grant any permissions to the user, we will use its output to set the permissions.

SELECT 'GRANT SELECT ON '||schemaname||'."'||tablename||'" TO chartio_read_only_user;' FROM pg_tables WHERE schemaname IN ('public') ORDER BY schemaname, tablename;

Copy the statements returned by the query and paste them into the Statement window.

You can remove any tables from this list that you do not want the Chartio user to access. In the example, below I have removed the 'invoice' and 'invoiceline' tables because those tables contain sensitive information.

Run the query and confirm that everything executes successfully.

Connect to Chartio

Now you can add a data source from your Chartio settings. Choose the Amazon Redshift option and complete the form to connect to your database.

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 along with the read-only user credentials you created in the last step.

Google Analytics

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. Choose Google Analytics from the data sources list.

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.

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

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. Choose Google BigQuery from the data sources list.

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.

Once you've granted permission, you'll be directed back into the Chartio interface. You will be prompted to enter a Datasource Alias, your Project ID and Dataset ID. Click Connect when ready.

You can now create charts with your Google BigQuery data in Chartio.

Google Cloud SQL

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. Choose Google Cloud SQL from the data sources list.

You'll be redirected to Chartio's Connection Form. From here, 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 databases. Switch to the Apps tab and 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.

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. Choose MySQL from the data sources list.

You'll be redirected to the connection form where you can enter your database credentials.

MySQL read-only user

Chartio requires a read-only user for connecting to your database. If you fill out the form (without submitting) with a desired user and password you'd like to create, the form page automatically generates the MySQL GRANT statement needed to create the read-only access user. The statement is in the following format.

GRANT SELECT, SHOW VIEW
ON $database_name.*
TO $user@`rackspace1.chart.io` 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.

With a read-only user created, you can now submit the Chartio connection form and begin querying your data.

MySQL SSL Connection

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

Oracle

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. Choose Oracle from the data sources list.

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

Once you've completed the form, Chartio tests the connection, and will immediately show whether or not the connection was successful.

PostgreSQL

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

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. Choose PostgreSQL from the data sources list.

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 (173.203.96.249) you would add the following line to pg_hba.conf:

host mydatabase chartio_read_only 173.203.96.249/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.

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.

Salesforce

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. Choose Salesforce from the data sources list.

Confirm that your Salesforce edition supports API access, and click Connect. You'll be redirected to Salesforce, where you'll need to log in to authorize Chartio to connect to your account.

Segment

Log in to your Segment interface and enable Chartio as a cloud connection. You need to be on a Segment Enterprise plan to enable the cloud connection.

Once you've enabled Chartio as a connection, you should be presented with a screen that has all of the connection details. Make sure you copy all of these values.

1app-settings.png

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. Choose Segment from the data sources list.

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

You will be notified via our in-app messaging and 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 and Microsoft SQL Server 2012. 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.

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. Choose SQL Server from the data sources list.

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 information:

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

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 IP, you can follow the instructions to connect SQL Server to Chartio.

Twilio

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. Choose Twilio from the list.

You'll be prompted with a simple permissions request to allow Chartio access to your Twilio account. Click Connect when ready, and sign in to your Twilio account.

Once you've granted permission, you'll be directed back into the Chartio interface. Chartio will pull your Twilio data, and will send you an email when your data is ready to be used in Chartio.

Chartio will fetch new Twilio data every 24 hours, at the same time of day as when the connection was first made.

Note: you'll want to set the UTC offset for your Twilio data source to match the timezone that your Twilio data is in.

Connection Client

Chartio has built a lightweight set of scripts which will allow you to set up a reverse SSH tunnel connection, in the event that you are unable to edit your firewall rules.

Requirements

Environment

The Chartio connection client requires either a Linux or a Unix machine.

Dependencies

  • Python 2.5 or higher
  • OpenSSH
  • Your database listening on all interfaces
  • Ability to make an outbound connection to rackspace1.chart.io on port 22. Run telnet rackspace1.chart.io 22 from the command line to test.

Connection Client setup

For most Linux systems, the following commands should get the Chartio client installed and start the connection wizard.

$ sudo pip install chartio
$ chartio_setup --prefix=/home/username/databasename

Using the --prefix switch allows you to identify individual installations. This can help avoid confusion when running multiple instances on the same server, or if you need to move or reinstall the client.

The chartio_setup wizard will walk you through setting up a readonly user and connecting to Chartio.

Don't have pip but have easy_install?

If you don't have pip but have easy_install, you can alternatively run the following:

$ sudo easy_install chartio
$ chartio_setup --prefix=/home/username/databasename

Don't have pip or easy_install clients?

If you don't have the pip or easy_install python package managers, you can either:

A. Install pip with the following commands, then follow the standard installation procedure as described above.

$ curl -O https://raw.github.com/pypa/pip/master/contrib/get-pip.py
$ python get-pip.py

B. Download the Chartio Connection Client source here, and follow the installation instructions described there.

Moving the connection client

Follow these steps to move the connection client installation to a new server.

On your CURRENT server

1) Run crontab -l to determine the location of the chartio_connect configuration. You should see output similar to the following:

@reboot /usr/local/bin/chartio_connect -d --    prefix=/home/chartio/instance

2) Copy the command, without '@reboot', paste into your terminal window, and append 'stop' to the end of the command.

/usr/local/bin/chartio_connect -d --prefix=/home/chartio/instance stop

3) Run crontab -e to enter the crontab editor and comment out the line for chartio_connect using ###.

4) Use scp -r to recursively copy your chartio_connect configuration to your new server.

scp -r /home/chartio/instance/ chartio@new_server_name_or_ip:/home/username crontab

On your NEW server

1) Install the Chartio Connection Client.

2) Run crontab -e to enter the crontab editor and add the following line.

@reboot /usr/local/bin/chartio_connect -d --prefix=/home/username/instance

3) Set read and execute permissions on the directory containing the configuration files.

chmod -R 755 /home/username/instance

4) Run the following command to start chartio_connect as a daemon.

/usr/local/bin/chartio_connect -d --prefix=/home/username/instance