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.

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

Define your column using SQL

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

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 labelled “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 will evaluate your SQL and will display any error messages in the schema viewer.

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.

The executed query using a custom table

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.