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 Direct connection from the side navigation and choose your data source type.

Manage data sources


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

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.

Ocassionally a database does not respond to a cancel query request from Chartio. MySQL, in particular, requires an open connection to send a cancellation request; if there is no open connection available (because many queries are running), the request will be unsuccessful.


Switch to the Usage tab in your data source's settings to view statistics about the queries run on your data source. An easy way to improve database performance is to take a look at the Slowest Charts and begin optimization there. You can also easily see what time of day your database processes the most queries, and which charts have the most errors.

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.


Chart caching is handled at the dashboard level. See "How does caching work?" for more details.

Clear Cache

You can manually get fresh data for all the charts on a Dashboard by selecting "Refresh all data" from your dashboard sidebar menu.

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


Admin permissions are 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
  • Delete columns and tables (from your Chartio schema, not your actual database)

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.

Column Descriptions

Add column definitions that your users can view when building charts.

Adding a column description is simple. Navigate to your data source's Schema tab, expand the table containing the column you wish to edit, then click the column to edit it. Add your definition to the Description field, and click Save.


Now your users will see this description whenever they hover over the column name in the Data Explorer.


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 Data Explorer, 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.

Delete columns and tables

Remove columns and/or tables from the schema Chartio creates for your data source. This feature is useful if your data source includes tables that you will never need to query, particularly if your data source exceeds Chartio's column and table limit for data sources.

Things to note:

  • This process is entirely reversible: to retrieve deleted tables and/or columns, simply refresh the schema and select tables/columns to add to your schema.
  • When deleting a column or table, Chartio will show you which charts are currently using it. If you remove a column or table that is in use, the charts using it will revert to SQL Mode. They will not break.

How to delete

In your schema, click the Delete button next to a table name to delete. To delete a column, click the column name to open its settings and click the delete button there.

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.

Schema update

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. In Chartio, foreign keys allow us to join tables together in Interactive Mode. 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. We also have a "Connect Tables" feature that can auto-detect possible foreign key relationships.

Connect Tables

Use the Connect Tables feature to automatically detect possible foreign keys. Review and approve the foreign key selections before they're added to your Chartio schema. Note: foreign keys are only added to Chartio's metadata, we don't make any changes to the database itself.


Adding a foreign key


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.

In our schema, we'll click Activity to expand the Activity table. Then, we'll click User Id to open the User Id column's settings. In the Foreign Key setting, we'll select the Users table, then select the Id column.

View detailed example

Foreign key joins

Once foreign key relationships are defined, the Data Explorer 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.

Time Zone support

Configure your time zone in Chartio to adjust your chart’s datetime values, as well as date variables ({TODAY}, {CURRENT_MONTH.START}, etc.) and filters.

Every dashboard has a time zone setting. By default, new dashboards will use the organization default time zone, but you can customize the time zone for individual dashboards if needed.

Time zone support can be turned on or off per-data source. If, for example, your data source is already in your desired time zone, you may disable time zone support for that data source.

To set up time zone support in your Chartio account, complete the steps below.

Set your organization default Time Zone

The organization default time zone will be used to set the default time zone for every new dashboard, Explore mode, and report dates.

From the top navigation, select the ellipsis menu and choose Settings. In the organization settings, you'll see a dropdown labeled Time Zone.

If you change the organization default Time Zone, any dashboards currently using the default Time Zone will update to use the new default.

Enable or disable Time Zone Support for your data sources

Choose whether individual data sources will have a time zone conversion applied to their data, and if so, which time zone conversion type (named time zones or UTC offset).

Named time zones are preferable, especially if you plan to use SQL Mode, because the syntax is simpler. However, not all databases support named time zones. If you aren't sure if your database supports the SET TIME ZONE syntax, see below.

From the top navigation, choose Data Sources > your data source.

There are 3 data source time zone settings:

  • Disabled: use this setting if your data source does not need its time zone adjusted.
  • Time Zone: the data source will use the SET TIME ZONE timezone_name syntax for its queries. If you don’t see Time Zone as an option, your database type does not support named time zones.
  • UTC Offset: the data source’s datetime values will be adjusted to your time zone by adding or subtracting hours from UTC. The UTC Offset is automatically populated based on the dashboard's Time Zone setting, and will account for daylight savings time as needed.

Time Zone vs. UTC Offset

Time Zone is preferable to UTC Offset, especially if you'll be using SQL mode. Check your database type below to see how time zones are supported. If your database type is not listed, it does not support either Time Zones or a UTC Offset. If you are unsure, contact support.

Database type Time Zone or UTC Offset? Supported column data types SQL Mode usage
PostgreSQL (includes Heroku, Data Stores, CSVs) Time Zone timestamptz, timestamp1 applied automatically1
MySQL Time Zone2 timestamp applied automatically
Redshift (includes Stitch) Time Zone timestamptz, timestamp1 applied automatically1
Google Analytics none3
Google BigQuery UTC Offset timestamp requires special syntax4
Google CloudSQL UTC Offset timestamp requires special syntax5
SQL Server UTC Offset datetime requires special syntax6
Time Zone SQL Mode syntax
If you are unsure what syntax is required for SQL Mode, simply add your date dimension in Interactive Mode, choose your desired time bucket (day, hour, etc.) and click Preview SQL to view an example.

1. PostgreSQL, Redshift: In SQL Mode, cast timestamp and timestamptz columns to text in order to apply the time zone conversion, using TO_CHAR or ::text. In addition, timestamp columns must be set to UTC before the time zone conversion can be applied, using syntax SELECT (date_column AT TIME ZONE 'UTC').

2. MySQL: 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. If you do not want to complete this process, you can use a UTC Offset instead.

3. Google Analytics: Set your dashboard's time zone to match your Google Analytics time zone.

4. BigQuery: For BigQuery, wrap timestamps in TIMESTAMP_ADD(timestamp_column, INTERVAL {UTC_OFFSET.RAW} HOUR)

5. Google CloudSQL: For Google CloudSQL, wrap timestamps in CONVERT_TZ(date_column,'+00:00','{UTC_OFFSET.RAW}:00')

6. SQL Server/SQL Azure: For SQL Server and SQL Azure, wrap datetimes in DATEADD(hour, {UTC_OFFSET.RAW}, date_column)

Customize Dashboard time zone

By default, any new dashboard will use the organization's default time zone. However, you may override the organization default time zone on a per-dashboard basis, via the dashboard's settings.

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 Data Explorer.

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 click Add custom table at the top of the schema page.


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

Aside from providing connection details, some data sources require a schema name to reflect properly. If you're unsure of what schema you should use, you can run the following query to find out which schema to reflect after you've connected the datasource.

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

Amazon Athena

To connect to an Amazon Athena data source, you will need the following information:

The IAM user that created the API access keys will need to have S3 access to both the S3 bucket where the data is read from and the S3 bucket where the query results are stored. The AmazonAthenaFullAccess policy only gives access to the query result S3 bucket. You will also need to add a policy to the user so it has access to the data.

Amazon Aurora

If your instance is in a VPC, see our VPC instructions.

Follow the RDS Instructions to add Chartio to your RDS Security Groups. This will allow our server to connect to your RDS instance.

Next, retrieve read-only credentials for authentication, and complete the connection form for Amazon Aurora.

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


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.


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.

Click on the VPC security group assigned to your to DB from the console and this will bring you to the security group page where you can add a new inbound rule. A form for a CIDR will be presented. Please add the following IP address to your Security Group:


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, you'll need to whitelist our inbound IP address in your Cluster's Security Group settings.

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

Log in to the Redshift Management Console and select Clusters from the left sidebar. Select the cluster you want to connect to Chartio.

The Cluster Details page will display. In the Cluster Security Groups section, click the Security Group you want to add Chartio to.

In the Security Group, click the Inbound Rules tab at the bottom of the page, then click the Edit button. Set the Type to Redshift, and adjust the Port if needed. Enter the following into the Source field:

Connection Details

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

A comma-separated values (CSV) file stores tabular data in plain text where each line of the file is a data record separated by commas. 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.
  • Remove extra headers and footers. Your CSV should be in raw table format, with all data arranged in columns and one header row as seen below.
  • Header row should contain no special characters, only numbers, letters, or underscores.
Example Table

Date City Temperature in Celsius
9/20/2017San Francisco19
9/21/2017San Francisco18

When opened in a text editor, the file should be in this format:

Date, City, Temperature in Celsius
9/20/2017, San Francisco, 19
9/21/2017, Austin, 34
9/21/2017, San Francisco, 18
9/21/2017, Austin, 33

Uploading and Processing Your Files

In the top navigation menu, select Data Sources.

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.


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
 M1..12 or 01..12
ISO WeekW1..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
  • 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]


See our basic connection instructions.

DB2 SSL support

Configure SSL support on your database, then check the Connect using SSL checkbox in the Chartio connection form.


See our basic connection instructions.

DashDB SSL support

Configure SSL support on your database, then check the Connect using SSL checkbox in the Chartio connection form.

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?

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.

Analytics 360 (Premium) Accounts

If you have a Analytis 360 (premium) GA Account, please contact to enable additional custom variables and dimensions.

Google BigQuery

To connect your BigQuery account to Chartio, you'll need to set up a Service Account and upload the generated key to Chartio.

Create Service Account

Log in to Google Cloud Platform and navigate to the project you want to use in Chartio.

In the sidebar, select IAM & admin and choose Service accounts.


Click Create Service Account.

Enter a name—you may want to name it Chartio so you can remember its purpose later—and under Role check BigQuery Data Viewer and BigQuery User in the BigQuery menu option. Ensure both roles are selected; if either are missing, Chartio will be unable to connect to your BigQuery data.1


Check the Furnish a new private key checkbox, and ensure JSON is selected under Key type. Click Create.

Save the JSON file to your computer.

1. It is possible to use only the BigQuery User role but that service account would need to be added to each dataset individually.

Upload key to Chartio

In Chartio, select Data Sources > Add a Data Source > Google BigQuery, and upload the JSON file you downloaded in the previous step. Note: it can take a few minutes for Google to accept the JSON key. If you get an error uploading the JSON file to Chartio, wait a few minutes and try again.

Access Multiple Projects

To access additional BigQuery projects with a single Service Account, you'll need to add the client ID to the additional projects from the Google Cloud Platform console.

From Google Cloud Platform, select IAM from the sidebar. Find the service account, and copy the member name.

Switch to another project you want to connect to Chartio, and click Add at the top of the page. In the Members field, paste the member name you copied earlier. Under Role, select BigQuery User and BigQuery Data Viewer.

Once this is finished, you can use the same JSON file you downloaded earlier to connect your second BigQuery project to Chartio.

Google Sheets via BigQuery

If you've added Google Sheets tables to your BigQuery project, you can query them in Chartio from your BigQuery connection after a few settings updates. Your BigQuery account must use a service account connection to enable this feature.

Enable Google Drive API in the project

Log in to your Google Cloud Console. Select your project from the top dropdown. From the left navigation, choose APIs & Services, then choose Dashboard. At the top of the page, click Enable APIs and Services. Use the searchbar to search for Google Drive API, select it, and click Enable.

Add service account client ID to the Google Sheet

From your Google Cloud Console, select IAM & admin from the left sidebar, then select Service accounts. Copy the value in the Service account ID column. If you don't have a service account yet, follow the instructions above for connecting a BigQuery account.

Open your Google Sheet, and click on the Share button. Click Advanced at the bottom, and in the Invite people text input, enter the Service account ID value you copied earlier.

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

Chartio does not currently support SSL connections to Google Cloud SQL.

Google Sheets

Chartio’s Google Sheets connection allows you to automatically import Google Sheets from your account and use them like you would any other data source: a spreadsheet corresponds to a database and worksheets correspond to tables. Any updates made to rows in your Sheets data will be immediately queryable in Chartio.

Preparing your Google Sheet

Formatting requirements:

  • Remove extra headers and footers.
  • Each worksheet should be in a tabular format, starting in the top-left cell, with all data arranged in columns and one header row with no empty header cells.
  • The header row should contain no special characters (including newlines). Headers should contain only numbers, letters, and/or underscores.
  • Use the Google Sheets Format menu to correctly specify the format of a column.

Connecting a Google Sheet

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.

Google Sheets Limitations

Renaming the spreadsheet, a worksheet or a column is currently not supported. This will be treated as a deletion and addition and will cause errors with any charts currently using a renamed object. Moving a column or worksheet is supported.

The user that initially added the data source will need to remain as authorized and maintain access to the spreadsheet. If you’d like to change the authorizing user or re-authorize, this can be done from the Connection tab within the specific Google Sheet's settings.

Any column named “Id” will be renamed to “Id1”. This is due to Google Sheets returning the row ID as “Id” which will show as a hidden column in your schema.

Google Sheets SQL Mode syntax

  • Strings must be quoted using single quotes (e.g., 'John Doe').
  • Use Oracle's syntax for date formatting using the FORMAT() function.


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 paste it into a text editor. The url will be in the following format:


Copy the individual values (username, password, hostname, etc.) from the DATABASE_URL and paste them into the connection form in Chartio.


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


If your instance is in a VPC, see our VPC instructions.

If your database is on Amazon RDS, add Chartio to your RDS Security Groups. This will allow our server to connect to your RDS instance.

Otherwise, see our basic connection instructions.


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

MoSQL does not support MongoDB 3.2 or later. If you are using a newer version of MongoDB, other options include:

  • Stitch or other ETL tools
  • Momy, a tool to replicate MongoDB to MySQL in realtime
  • rollback to MoSQL-supported version of MongoDB


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:

ON $database_name.*
TO $user@`` IDENTIFIED BY '$password';

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.


See our basic connection instructions.


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


A Presto connection can be made using either a direct connection or a reverse SSH tunnel connection.

To set up an SSH tunnel connection, please see our Tunnel Connection instructions. One the tunnel is set up, add a new Presto data source in Chartio via Data Sources > Add a Data Source.

Form requirements for both connection types:

  • Hostname or IP (Direct Connection only)
  • Database Port (Direct Connection only)
  • Database Username (Direct Connection only)
  • 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 (Tunnel Connection only) 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.



Enable Segment warehouse support

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

Obtain Segment connection details

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

Connect Segment to Chartio

In Chartio, add your Segment data source as an Amazon Redshift connection. Paste the values from the Segment connection settings into the input boxes in the Chartio connection form and click Connect.




  • Snowflake warehouse must be set to AUTO-SUSPEND

Obtain Snowflake connection details

All connection details are case-sensitive. Snowflake stores object names in uppercase unless you quote ("") the names when you create the objects. This includes Warehouse Name, Database Name, and Schema Name.

  • Account name
    The first part of your Snowflake url:
  • Account user
    The username you use to log in to your Snowflake console.
  • Warehouse name
    Click "Warehouses" from within your Snowflake console to view a list of warehouses. Any warehouse in your account will work with any database.
  • Region
    If not US West Region, your region will be listed in your Snowflake console url:
  • Database
    Click "Databases" from within your Snowflake console to view a list of databases.
  • Database password
    The password you use with your Account user to log in to your Snowflake console.
  • Schema name
    Click "Databases" from within your Snowflake console, then click the name of the database you would like to use. Switch to the "Schemas" tab to view a list of schemas for that database. Chartio will automatically default the schema to public in the connection form unless a schema name is entered.

Connect Snowflake to Chartio

In Chartio, select Data Sources from the top menu, choose Add a Data Source, then select Snowflake. Enter the connection details obtained in the previous step into the connection form and click Connect.

*Note your schema name is case sensitive.

Time Zones

Chartio sets Snowflake data sources to UTC. It is not currently possible to override this time zone setting.

Suspended warehouse

Chartio will automatically resume a suspended warehouse prior to schema refresh. Chartio will NOT resume a suspended warehouse to run a query.

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, 2014, and 2016. 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:
End IP Address:

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.


These instructions refer to Stitch accounts created via Stitch. For Stitch accounts created through Chartio, see our Automatic Cloud Data Stack documentation.

Stitch uses Amazon Redshift for data warehousing. To connect Stitch to Chartio, you’ll need to do the following:

  • Whitelist Chartio’s IP address in Redshift
  • Enter the Redshift connection details into Chartio

Whitelisting Chartio’s IP Address

See our Redshift instructions for whitelisting Chartio's IP address.

Add Stitch to Chartio

The following instructions can be used to set up a Direct Connection to your Stitch Redshift data warehouse. If you plan on using an SSH Tunnel to connect, see our Tunnel connection instructions.

Connection details can be found in your AWS management console, as described in the previous step.

In your Chartio account, select Data Sources > Add a Data Source. Select Amazon Redshift, then fill in the following:

  • Hostname or IP: Your endpoint, excluding the colon and port number.
  • Database Port
  • Connect using SSL: Leave checked to use SSL.
  • Database Username: The user you want to use to connect to Chartio; could be the master user, the Stitch user, or something else.
  • Database Password: Password associated with database user.
  • Extract Schema: Leave checked.
  • Database Name: The name of the database that you used to connect to Stitch. This should be the same database that’s listed in the Database field in your Stitch Warehouse Settings.
  • Schema Name: Your database schema name. To connect multiple schemas, add a separate data source for each.
  • Data Source Alias: A nickname for your database.

When finished, click Connect.


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.


Follow our basic connection instructions.

Querying VoltDB

VoltDB will terminate long-running queries. As such, Chartio recommends that you query materialized views only, and hide standard tables in your Chartio schema.

To determine which tables are which, navigate to your-database-IP-address:8080 in your browser, switch to the Schema tab, and select Schema from the subnavigation. Tables and Materialized Views are labeled in the schema.

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


  • Your database listening on all interfaces
  • Ability to make an outbound connection to on port 22. Run telnet 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

To accept Chartio's host key, run the following command:

echo " ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDC6wbEvCi6sz9Igw7uyQ9cIMpiRzkxO/TUEuIM+FIiE1knM9J4VlBz5CFYQZ09OW9ZpPtEDtuumPNtcHJ2xmTZ/6AYvxT5i5A7at83nzG7yM+ErpZ83lB2st9xJ/AzvCopJxvR25kt29dwjstWiDv0tepP3ywBkAWigbpWcATZPjY3vsD/T0QhNzPeP9Xy1WbsbTVS0JAEBaOzmrDQQXuNxV+xtZP2o9CQUwjNMXTI2NL1A7D9wy408sjyNADcWQstIgNf8Uxv/eSo7e7y2g/YEi8gNIApNVcPwZ9dgfg6MK4xAW1TeN4hxrFLm2KKtyRIvwRoCYckpD2IokVK+yU/" >> ~/.ssh/known_hosts

Create a read-only user

Chartio requires a read-only user to connect to your database. If you don't have one already, do so before filling out the connection form in the next step.

MySQL read-only user
SQL Server read-only user
PostgreSQL read-only user
Redshift read-only user

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.

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


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


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

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

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/

Keep your public key handy - you'll need to enter it in the connection form on 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: -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.