Documentation Data Sources

Custom Schemas

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

To create a custom column, go to your data source’s settings, navigate to the Schema tab, expand the table you’d like to add the custom column to then click Add Custom Column.

Here’s a simple example. Let’s say we have a Payments table, and our Amount 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 go to our data source settings, switch to the Schema tab, exapand the Payments table, then click Add Custom Column.

Click Add Custom Column under a table within your database schema

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’re aggregating a measure, such as taking a count or an average, select the Aggregated checkbox.; Note: If you use an aggregated custom column in Visual Mode, you won’t see the aggregation menu for the column in the Result Table.
Type: If your new column is a dimension, select the data type of your column. Measures is given type Number by default.; 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.
SQL: In the pop-up modal, we’ll set our new column settings and add our SQL as shown below.; Custom columns can only use columns from the table you’re creating them in, so do not include the SELECT keyword or FROM clause in the SQL you provide. If you need to create a custom column that uses more than one table in the schema, you’ll need to create a custom table instead.; When finished, click Add Column.

Define your column using SQL

Using a custom column

We’ll quickly open the chart editor by clicking Chart from the top navigation bar and select our data source. Custom columns appear in the data source just as any other column would, and you can add them as a Column or Filter in Visual SQL as usual.

Create your chart

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.

SQL for a custom query using a custom column

Editing a custom column

Edit custom columns by opening the table in your data source schema and clicking on the custom column alias.

Additional information regarding this column will appear below. You can make edits to the query directly in the section labeled “Query”. Click Save when completed and these changes will be applied to your custom column.

Edit custom columns

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

Click Add custom table to add a table to a database

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.

SQL example

Chartio evaluates your SQL and displays any error messages in the schema viewer. Avoid setting aliases in the SQL query and instead use Chartio’s schema manager to set aliases on a per column basis. If an alias in the SQL query does not match the column name there will be an error when you query the column.

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

In the Visual SQL interface

In the Data Explorer interface

Editing a custom table

Navigate to your Schema page and click on the custom table name. This will expand the custom table and list the columns contained in the table.

On the right hand-side, below the table name and before the column list, you’ll see an Edit button. Click Edit and a modal will open with the current query.

Edit custom table

From there, you can edit the name of your custom table or your custom SQL. Hit Update to save your changes.

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.
When updating a table without making changes to the query (ex. SELECT * FROM table) you must make at least one change to the query for the query to be evaluated. For instance, you can add a comment or dummy column.