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 data type
You can view the Type and SQL Type for each column in your schema.
Change Unix Number columns to timestamps
It’s difficult for Chartio to automatically recognize Unix timestamps when pulling schemas from databases–in the schema, they are marked simply as Number columns. You can, however, specify which columns are Unix timestamps by changing the Type attribute of the relevant columns in the schema editor.
For more information, see our documentation on how you can change your column’s data type using a custom column.
Once the columns are specified, the chart editor automatically applies
FROM_UNIXTIME functions to those columns when the queries are generated.
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.
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.
You’ll be redirected to a page to view the schema changes, along with a list of Visual 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.
Once the changes look correct, click Apply Updates to complete the schema update.
How renamed or removed columns affect Visual 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 Visual 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 Visual Mode charts first.
Chartio cannot automatically detect changes and refresh your data source schema.
You will need to manually refresh the schema for any changes. Schema refreshes are mandatory to use new tables in Visual Mode, but in SQL Mode you can query the new tables directly without having to update the schema.
Check out the video below to see an example of how to refresh your schema:
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 Visual 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. The “Connect Tables” feature that can be used auto-detect possible foreign key relationships.
Note: Foreign keys set from the schema page are only added to Chartio’s metadata. Changes here are not made to the database itself.
When possible foreign key relationships in your data source are detected, you will see a Connect Tables button appear in your data source schema page. This feature can be used in Chartio to assist in setting foreign keys between tables.
Upon clicking the Connect Tables button, a modal will pop up showing foreign key suggestions. You can then review and approve foreign key selections before they are added to your schema in Chartio.
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.
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 Visual 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 Visual Mode, Chartio will use the join type you’ve selected.
Take a look at the video below which explains foreign keys and goes over the different ways of adding them to your data sources in Chartio: