Transforming Data with the Data Pipeline
Remember, when creating a query with Visual SQL you are simply asking a database to return to you a subset of the data it holds in its tables to you in a simpler, many times smaller, table. These tables are then transformed into visualizations through Chartio’s Chart Creator. But before that takes place, you may want to manipulate that data further, and to do that you will use the Data Pipeline.
Chartio’s Data pipeline allows you to perform transformations on your query results in a series of steps. These steps include a variety of operations such as column sorting, pivoting data, and adding calculated columns. The flexibility of adding any steps in any order allows you to get your data exactly how you want it.
Actually, a good way of thinking about it is that when transforming the data in the Data Pipeline, you are just creating subsequent tables. This can be through any of the data pipeline steps that are described here.
Data Pipeline Example
We have already looked at querying two data sets to get two different columns as a line chart. In this tutorial we will show using two data pipeline steps, Add Column and Hide Columns, to show how you can further manipulate the data after the query was sent to the database and it returned a table of information.
We have pulled in the Data for the Users and the Pageviews by month for the past 12 months. So what we will do now is to compare these two columns with a simple mathematical equation. We will divide the Pageviews column by the Users column to get a metric that measures how many pageviews we must get in order to get a user, we will call this Views Per User.
Then we should hide the Pageviews column as it is no longer needed.
Now keeping in mind what we discussed earlier and that each step in the Data Pipeline as well as the Visual SQL Queries result in tables, the following diagram effectively outlines the process. Each Visual SQL Query and each Data Pipeline Step are funnels that manipulate the table that appears directly before it in the pipeline.
The final step in the process is when the Chartio Chart Creator turns your final table into a chart.
Some of the most popular and useful Data Pipeline steps include:
- Pivot Data
- Filter Rows
- Sort Rows
- Rename Columns
- Reorder Columns
- Case Statement
Head over to Chartio’s support documentation here to see a full list of the available pipeline steps and more information about them.
If you would like to use Visual SQL, you can try it out with a Chartio free trial.