Queries are the cornerstone for creating charts. This is where you’ll pull the data you want to show. All charts must start with an initial query; to help you remember this, all new charts have the first Query Action added and expanded for your convenience!
Choose a data source
Before you can start building your query, you need to decide from where to query the data. Choose one of your connected data sources by clicking the Data Source dropdown from the top-right corner.
Don’t have any data sources listed? Let’s fix that! Check out our data sources documentation for how to add some.
Choose your preferred mode
Now you can actually query data! Queries have two modes:
- Visual Mode - Quickly add columns and filters and let us generate the SQL query for you
- SQL Mode - Write out your own SQL query from scratch
Use either mode (or both modes!) as a method to pull data from your selected data source.
Execute your query
Keyboard shortcuts! You can also use
Cmd + Enter (for Mac) or
Ctrl + Enter to run your query.
The executed query depends on which mode is currently active. If you’re using Visual Mode, the auto-generated SQL query is executed; if you’re using SQL Mode, your custom query is executed.
Note: Making changes and executing the query in Visual Mode updates the query in SQL Mode, but this isn’t true for the reverse – making changes and executing the query in SQL Mode doesn’t affect what’s shown in Visual Mode. If you’re switching between Visual Mode and SQL Mode, your changes to Visual Mode don’t impact your returned results until you re-execute your query from Visual Mode. Also, be aware that re-executing the query in Visual Mode may override changes you’ve made in SQL Mode. We understand this might be a bit confusing. Maybe our FAQ on editing in both modes will shed more light on the subject.
After running your first query, you can start adding other Actions to your Pipeline to further transform your data.
If you want to know more about Visual Mode and SQL Mode, check out the content below for more in-depth information:
Chartio makes it easy for users with minimal SQL knowledge to query their data sources. With Visual Mode, you can focus on simply adding the necessary columns and filters for your chart.
Add a column
To add a column, click +Add Column in the Columns section. To find and add a column from the list, you can do any of the following options:
Search for it using the search bar
Navigate through the schema browser
Click on a column from the table preview of the schema browser when you select a table
To add multiple columns at a time without the schema browser closing between each selection, hold
Command (for Mac) or
Ctrl (for Windows) then click the columns you wish to add. This works when selecting columns from either the schema browser or the preview table.
Note: After adding a column, you might see some schemas, tables, or columns become disabled in the schema browser when you try to add another column. This means the currently selected columns aren’t connected to those disabled items. You may need to update your data source’s foreign keys to connect the tables or add another Query to query and merge additional columns.
Added columns appear in the Result Table, and each column has an Aggregation menu. The Aggregation menu is where you can choose to group by or aggregate that specific column, and the options displayed in the menu depend on the column’s data type. If you don’t want to group or aggregate a column, you can choose Unaggregated from the menu.
Remove a column
To remove a column from your Query, hover over the column’s block and click the X that appears on the right-side of the block.
Only want to show data matching certain conditions? This is where filters will come in handy. For filters in a Query, you can filter by any available columns, even if they aren’t added to the Columns section.
Add a filter
You won’t be able to add a filter until you first add a column. Once you’ve added a column, you’ll see the Filters section appear to the right of the Columns section.
To add a filter, click +Add Filter in the Filters section.
- Select a column using the same process as described in Add a column.
- After choosing a column, you’re prompted to choose a filter operator. The operator options vary depending on the aggregation type (e.g., group or aggregate) and data type of your selected column.
- After choosing the operator, you’ll need to provide values within the selected column to filter your data. Type in values manually or select your Dashboard Controls, if any, from the list. Values can be literal strings, Dashboard Controls, Variables, etc.
All added filters are connected by an implicit
AND operator. In other words, the data returned must match all the filter conditions. If you want to connect your filters with an
OR operator (i.e., you only need to match some filter conditions), you’ll need to use a Filter Rows Action.
Remove a filter
To remove a filter, click the X on the right-side of the filter’s block.
Date filters and Relative Date Variables
Chartio’s Visual Mode date filters don’t necessarily follow SQL behavior. We created standard date filter operators to ensure date filtering behavior is consistent for both dates and datetimes across all our supported data sources:
- between is exclusive, meaning the end date is not included
- between and including is inclusive, meaning the end date is included
To apply dynamic date filters, try using Chartio’s Relative Date Variables!
If you want to see the kinds of data stored in a particular table, you can get a convenient table preview with the first ten rows of data. When adding a column or a filter, an interactive table preview will appear in the schema browser when you select a table from the list. Hovering over any of the table’s columns in the dropdown will highlight the column in the table preview and the dropdown navigation.
Column data types
Each column in the schema browser has an icon corresponding to the column’s data type.
|Date or Datetime|
The Advanced Drawer is the collapsible section at the bottom of the Query editor. By default, the Drawer is collapsed. To expand the Drawer, click the down caret. To hide it again, click the up caret.
There are a few notable items in the Drawer:
Your query is automatically sorted by the order you’ve added your columns. You can change the query sort in the Advanced Drawer.
To add a sort block, click +Add Sort, select the column to sort by, then choose the sort direction (Asc for Ascending, Desc for Descending).
To delete a sort block, click the X at the right-side of the block.
To edit a sort block:
- Click the column name to choose a different column
- Click Asc/Desc to change the sort direction
Note: You can only sort by columns you’ve added to the Columns section.
Please note that Chartio queries all rows in your data source to calculate your result set, but the number of rows returned for that result set is limited to the number you enter in this field. By default, to improve performance while creating and testing your chart, the row limit for the returned results of your query is set to 1,000. When you’re finished testing, you may want to increase this limit to ensure that your chart results are not truncated by this limit. Most Chartio subscriptions allow for up to 100,000 rows to be returned from original queries.
Refer to Chart Data Limits for more details on row limits.
When you’re using Visual Mode, Chartio auto-generates the SQL query to be executed in the background. If you’re curious, you can see a read-only view of the auto-generated SQL query from the Advanced Drawer.
Tip! This is also a great way to learn some basic SQL—simply edit your Interactive Mode chart and watch how the generated query changes.
If you feel more comfortable creating SQL queries from scratch, you can do so by using SQL Mode in your Query Action. If you added columns or filters in Visual Mode then switched to SQL Mode, you’ll see the same auto-generated SQL query that also appears in the SQL preview in the Advanced Drawer. The key difference here is you can edit that SQL query to include more complex SQL functions that may not be feasible to do via Visual Mode.
If you need to reference the tables and columns in your data source, we’ve provided a sidebar version of Visual Mode’s schema browser on the left side of the SQL Mode editor.
Tip! Building queries in Visual Mode then switching to SQL Mode is a great way to generate the basic structure of return values and even joins, before editing the query with more specific needs.
The SQL syntax used for your Query in SQL Mode must be the syntax for your data source’s underlying database type. However, the SQL syntax in all other Actions is SQLite.
Note: CSVs will use PostgreSQL syntax.