From Vida Lab Team: https://vida.io
Original article from vida.io blog: http://blog.vida.io/2016/11/06/connect-d3-dot-js-visualization-with-sql-database/
Supported SQL databases: PostgreSQL, MySQL, MS SQL Server.
D3.js visualizations often use CSV data files that are loaded into browser. In this article, we walk you through how to connect to SQL database on vida.io and use D3.js to visualize the data.
The database contains data for view activities of website. We use calendar heatmap to visualize the frequency during months. The raw data table has 3 columns: views, created_at, and updated_at.
The first step we need to do is to connect to the PostgreSQL database. After login, we go to Sources section and create a new data source. We call it ACME PostgreSQL. For our example, we use the following connection settings:
- Hostname: localhost
- Port: 5432
- Username: postgres
- Database name: acme_webapp
Next, we create a dataset that reads from ACME PostgreSQL data source.
- Go to Datasets section and create a new dataset.
- We call it ACME Activities.
- Scroll to the bottom of the page and click “Add SQL Query.”
Raw data points only record the event. We need to group and count the data happening during days. We use the following SQL query:
SELECT TO_CHAR(created_at, ‘MM/DD/YYYY’) AS created, count(*) FROM activities GROUP BY created ORDER BY created;
The result dataset contains the following data:
Now, we create calendar heatmap visualization.
- Go to Documents section and click on “New Document” button.
- Select “Import Vida” under custom section. We use Calendar Heatmap template from vida.io.
- Paste in the following document ID “Fmhxf9jxed5GY993X” and click Import. The app will take us to the imported document.
- Click on Data tab and select “Link External” from Datasets menu. Note: this feature is available to subscribed user only. Please contact us if you have any question.
- Select “ACME Activities” dataset that we created earlier.
- Click on Save to refresh the document.
The final visualization looks like below.
Here’s a video demo of the entire process: