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
If you’re creating a category or other groupable value, choose Dimension. If your value is numeric, choose Measure.
If you are aggregating a measure, such as taking a count or an average, select the Aggregated checkbox.
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.
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 a custom column
We’ll quickly open the chart editor by clicking Explore 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.
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.
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.
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.
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. 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.
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.
From there, you can edit the name of your custom table or your custom SQL. Hit Update to save your changes.
- 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.