Dremio Jekyll

Data Visualizations with Dremio, D3 and Node

Introduction

Data visualizations are one of the easiest ways to gain meaningful, valuable insight from your data. Visualizations bring patterns within data to light that weren’t readily apparent, helping turn data into a human-readable form. Many BI or data visualization tools have traditionally been available as software, but as data technologies advance it’s become possible to do a wider range of visualizations in web browsers.

Dremio works as a key component of the data visualization pipeline, bridging the gap between your various data sources and data visualization tools - in this case, your browser. With Dremio, you can avoid having to do complex manipulations of data on the client-side - Dremio handles the joining, filtering or processing of your data without the need to write hefty code or complicated SQL queries. You can easily synthesize pieces of data from any of your separate data sources - S3, elasticsearch, MongoDB, Oracle and many more.

After aggregating all of your data sources you have the freedom to structure and streamline the data as you need. This means that instead of loading large amounts of data from multiple sources into your browser, you can load only the data you need from one centralized location.

This tutorial walks through creating a basic in-browser visualization using Dremio in tandem with D3.js, a javascript library designed to manipulate and visualize data. In this specific tutorial we will use Node.js to open an ODBC connection to the Dremio server, although ODBC connections can be established with a variety of server-side languages.

Assumptions

To complete this tutorial you will need to have Dremio installed. To open an ODBC connection you will also need to install the ODBC driver for your OS. Both of these can be downloaded from the Dremio download page. For more detailed installation and setup information, refer to the Dremio documentation.

Node will also need to be installed which you can do from the Node website. To check if Node is already installed run:

1
node -v

We will use Node in conjunction with Express and EJS to open the ODBC connection and pass data from Dremio into the browser. This tutorial won’t go into the specifics of setting up the Node app but if you care to see the full project setup the files can be downloaded here.

The Data

We will be working with a couple pieces of data in this tutorial. “search_trends.csv” is the Google Trends search statistics from the state of New York for four separate search terms: “Snow”, “School Closures”, “Emergency Supplies” and “Climate Change”. The second dataset, “severe_weather.csv” is a database of several hundred thousand severe weather events taken from the National Oceanic and Atmospheric Administration’s Severe Weather Data Inventory. You can download both of the datasets here (23.8 MB).

Every entry in the severe weather database is an individually reported severe weather event. Each event has a type, a date and time, notes about the event and many pieces of data about the events location. This is a small sample of what that data looks like:

Dremio and D3 tutorial - data sample

Our goal will be to create a visualization that compares the frequency of snow and cold weather related events with any possible surges in interest for our four search terms.

Curating The Data With Dremio

Dremio will handle the majority of our data processing, instead of bringing all of our raw data over from our original data source and processing it with a language like Python or JavaScript. The first step is to start up Dremio and connect to our data sources. We are using CSVs of data for the purposes of this tutorial but if you click the “Add Source” button on the left side of the screen you can see the wide range of sources that Dremio is able to connect to.

Dremio and D3 tutorial - data sources

Select “NAS” as the type, give the source a name and put in the local path to the folder containing our two CSVs.

Dremio and D3 tutorial - NAS data source

Now you can click on the source in the left panel and you should see the contents of our folder.

Dremio and D3 tutorial - NAS data source

Click on “search_trends.csv” and a dialog will pop up allowing us to bring the data into Dremio. Dremio should automatically select “Text (delimited)” as the format for the data. From there click the option to “Extract Field Names”, which will extract the first row of our CSV into field names for each of the columns.

Dremio and D3 tutorial - NAS data source

Click “Save” and you will see this set is now saved as a physical dataset, indicated by it having the purple spreadsheet icon. Physical datasets are stored within your selected data source (stored locally in this case) and cannot be directly modified by Dremio. To shape this raw data into the version we will eventually visualize, we need to create what Dremio calls a virtual dataset.

Virtual datasets are one of the most powerful aspects of Dremio. When turning a physical dataset into a virtual dataset no copy of the original data is made, instead each time the underlying data is accessed any data manipulations (type conversions, transformations, joins, aggregations etc.) are applied dynamically using highly optimized in-memory data structures with Apache Arrow. Because of this they consume no additional space and always reflect the state of the original physical dataset that they are derived from.

To turn our dataset into a virtual dataset all we need to do is click into it and start making changes. Here we can see our data and the underlying SQL query that Dremio has dynamically built for us so far.

Dremio and D3 tutorial - search trends data

First let’s change each of our search term columns into integer data types by clicking the “Abc” icon to the left of each column header and selecting “integer” from the drop down. Make sure “Drop Source Field” is checked and click “apply”.

Dremio and D3 tutorial - search trends data

Next let’s convert the “week” field from a string into a date. Click the same “Abc” icon to the left of the “week” column header and select “date” from the dropdown.

Choose “custom” as the formatting type and use the formula “mm/dd/yy”. Dremio provides some date formatting options for quick reference but for a more in depth look you can see all of Dremio’s date formatting options here. Make sure the box to “Drop Source Field” is checked and click apply.

Dremio and D3 tutorial - date formatting

Next let’s take the date formatting a step further. Click the same dropdown next to the “week” column header again but this time select “text”. Next select “custom” as the formatting type and use the formula “MON YY”. Rename the field “month” and click apply. Now you should see our month column with each of the dates formatted as the first three letters of the month followed by the last two digits of the year.

Dremio and D3 tutorial - date formatting

Finally, we only want one row of data for each month of the year, so let’s group the fields by the “month” column. Click the “group by” button below the SQL editor and then drag month onto the “Dimensions” pane and drag each of our four terms onto the “Measures” pane. Next to each of the measures make sure that “average” is selected and click “apply”.

Dremio and D3 tutorial - grouping data

Now we have one row for each month, with the search interest for each term averaged by month as well. To finish click “save as” and name the new virtual dataset.

Next let’s open our second dataset “severe_weather.csv”. After navigating to our NAS source and clicking on the dataset the same formatting dialogue will pop up. Select the same settings as we did previously then click “save”.

Dremio and D3 tutorial - importing data

This dataset is fairly large and cumbersome, but thanks to Dremio’s use of virtual datasets we are able to process and format it without excessive waiting times. Let’s begin by properly formatting the date again - that’s the field we will eventually join our two datasets together on.

The “ZTIME” field is an unbroken text string containing the date and the time of each entry. To start, select “datetime” from the “Abc” dropdown next to the “ZTIME” column header. Next select “custom” formatting again and use the formula “yyyymmddhh24miss”.

Dremio and D3 tutorial - date formatting

Name the new field “date” and click “apply” and Dremio will replace the original “ZTIME” column with our newly formatted “date” column.

Dremio and D3 tutorial - severe weather data

Now from our “date” field we can create our joinable “month” field. Select “text” from the dropdown menu next to the “date” column header. Then use custom formatting and use the same formula we used on the previous dataset, “MON YY”. Name the new field “month” and make sure to keep the “date” field this time before clicking “apply”.

Let’s also drop every other unnecessary field from the dataset. Using the dropdown arrow menu on the right hand side of each column header drop every column except “date”, “month”, “EVENT” and “STATE”.

We also only need the event data from the state of New York, as that’s where we pulled our Google Trends data from. From the dropdown arrow menu on the right side of the “STATE” column header select “Keep Only”.

Dremio and D3 tutorial - data filtering

Next select “Values” and make sure that “NY” is the only value selected before clicking apply.

Dremio and D3 tutorial - data filtering

We also don’t need every type of weather event. We only want to look at snow or extreme cold related events, so let’s do the same process to the “EVENT” column. From the dropdown arrow menu to the right of the “EVENT” header select “Keep Only”. Next make sure that only the items related to extreme cold or snow are selected, for this dataset that would be “SNOW”, “HEAVY SNOW”, “EXTREME COLD”, “FREEZING RAIN”, “SLEET”, “BLIZZARD” and “ICE STORM”.

Dremio and D3 tutorial - data filtering

After clicking “apply” this is what our data should look like.

Dremio and D3 tutorial - curated weather data

The last step before joining our datasets is to group our data by month again. First click the dropdown arrow menu in the “date” column header and select “sort ascending”. Next click “group by” below the SQL editor and drag the “month” field onto the “Dimensions pane and drag the “EVENT” field onto the “Measures” pane. This time make sure that “Count” is selected next to the “EVENT” measure.

Dremio and D3 tutorial - grouping data

After clicking “apply” you will see that we are left with only two columns, the “month” column and our “Count_EVENT” column which is a count of the number of severe weather events per month.

Dremio and D3 tutorial - filtered data

Finally we can join our two datasets. Select the “join” button below the SQL editor and then navigate to and select our “search_interest” virtual dataset. Now drag the “month” field from each of the datasets into the joining area. Make sure the type of join is selected as “LeftOuter” before clicking “apply”.

Dremio and D3 tutorial - joining data

Now we can drop one of our “month” fields and we are left with our final curated data. You can see the complex underlying SQL query that Dremio has automatically generated within the SQL editor.

Dremio and D3 tutorial - joining data

To finish, click “save as” and name the dataset what you like.

Connecting to Dremio Via ODBC

To pull our freshly curated data from Dremio we will need to use a server-side language that can open an ODBC connection. For this tutorial we will are using Node and the Node package node-odbc. To install node-odbc run:

1
npm install node-odbc

Once you have successfully installed the package, you can write a few lines of code to establish our connection.

1
2
3
4
5
6
7
8
9
10
11
12
13
var dremio_odbc_driver = 'DRIVER LOCATION';
var dremio_host = 'localhost';
var dremio_port = 31010;
var dremio_user = 'DREMIO USERNAME';
var dremio_password = 'DREMIO PASSWORD';
var server_port = 8080;

var db = require('odbc')();
var cn = 'Driver=' + dremio_odbc_driver + 
    ';ConnectionType=Direct;HOST=' + dremio_host + 
    ';PORT=' + dremio_port + 
    ';AuthenticationType=Plain;UID=' + dremio_user + 
    ';PWD=' + dremio_password;

Now we can pass a simple SQL query to our connection to get data back from Dremio and store it in a variable.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
var dremioData;

db.open(cn, function (err) {
    if (err) {
    	return console.log(err);
    } else {
    	db.query('SELECT * from "dremio_tutorial".search_interest', [42], function (err, data) {
    	    if (err) {
    	        console.log(err)
    	    } else {
                dremioData = data;
    	    	console.log(data);
    	    }
    	});
    }
});

Setting Up The Browser

Next we will need to pass our data into our web page. An easy way to do this in Node is with a JavaScript templating language like EJS. Setting up the full framework is beyond the scope of this tutorial but you can download a copy of our final project here to see the full setup. Using EJS we are able to pass the data directly to a Javascript variable like so:

1
2
3
<script>
  	var data = <%- JSON.stringify(dremioData) %>;
</script>

Inside the head of our HTML page we will also need to include a link to the latest version of D3:

1
<script src="https://d3js.org/d3.v4.min.js"></script>

Before we start working with D3 our beginning HTML file should look like this:

1
2
3
4
5
6
7
8
9
10
11
<!DOCTYPE html>
<html>
<head>
    <title>Severe Weather &amp; Search Trends</title>
    <script src="https://d3js.org/d3.v4.min.js"></script>
    <script>
       var data = <%- JSON.stringify(dremioData) %>;
    </script>
</head>
<body>
</body>

Creating a Graph With D3

Now that we have our HTML file ready to go we can use D3 to start visualizing our data. Let’s start by creating an empty SVG element with roughly rectangular proportions inside of the page body. Follow that SVG element by opening a script tag.

1
2
3
4
5
<body>
	<svg width="960" height="500"></svg>
	<script type="text/javascript">
	</script>
</body>

Inside of the script tag we use D3 to select our SVG element and define some margins. These margins will be the empty buffer between the edge of the SVG element and our actual bar graph. We will need this space to hold the labels for each of the axes.

1
2
3
4
var svg = d3.select("svg"),
    margin = {top: 30, right: 70, bottom: 30, left: 70},
    width = +svg.attr("width") - margin.left - margin.right,
    height = +svg.attr("height") - margin.top - margin.bottom;

Next we use D3’s built in scaleBand and scaleLinear functions to set a range for both the x and the y axis. This range is set dependent on the height and width of our SVG container. In this way, we can change the sizing of our graph and the x and y range will shift accordingly.

1
2
var x = d3.scaleBand().range([0, width]).padding(0.1),
	y = d3.scaleLinear().rangeRound([height, 0]);

The ‘padding’ declaration here will make sure there is a small amount of padding horizontally between each of the bars in our graph. Now we append our actual bar graph as an element inside of the SVG. We set CSS attributes to make sure that the element is offset by the margin we specified earlier.

1
2
var g = svg.append("g")
	.attr("transform", "translate(" + margin.left + "," + margin.top + ")");

Now we can start involving our actual data. First let’s parse out the “month” string into a date format that D3 can more readily work with.

1
2
3
4
var parseTime = d3.timeParse("%b %y");
data.forEach(function(d) {
	d.month = parseTime(d.month);
});

Using D3 we can set value ticks for the x and y axis to match our data. D3 will automatically line up the highest and lowest values for each axis and evenly disperse the rest of the data points between the two extremes.

1
2
x.domain(data.map(function(d) { return d.month; }));
y.domain([0, d3.max(data, function(d) { return d.Count_EVENT; })]);

Here we are mapping the month to the x axis and the weather event count to the y axis. Now we can start rendering our axes within the SVG element.

1
2
3
4
g.append("g")
    .attr("class", "axis")
    .attr("transform", "translate(0," + height + ")")
    .call(d3.axisBottom(x));

This call appends a new element with a class of “axis”, translates it to the bottom of our graph and then renders the values along the x axis with “d3.axisBottom(x)”. To render the y axis we use similar code:

1
2
3
g.append("g")
    .attr("class", "axis")
    .call(d3.axisLeft(y));

If you load the page in a web browser now you will see something like this:

Dremio and D3 tutorial - d3 axes

The y axis looks okay, but the x axis is too packed with points of data. We could make our graph wider and D3 would automatically increase the spacing of our data points, but instead let’s selectively render the labels for the data points and change the formatting of the date. We can add a new call to our original x axis code that handles the formatting of each tick. Our new code looks like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
var monthTime = d3.timeFormat("%b");	

g.append("g")
	.attr("class", "axis")
	.attr("transform", "translate(0," + height + ")")
		.call(d3.axisBottom(x).tickFormat(function(d){
			switch (d.getMonth()) {
			    case 0:
			    case 3:
			    case 6:
			    case 9:
			        return monthTime(d);
			}
		}));

When the month is Jan, Apr, Jul or Oct we want to display it, otherwise we won’t return the date value at all. Let’s also add to our y axis code to reduce the total number of ticks, format the numbers and add horizontal gridlines across the graph.

1
2
3
4
5
g.append("g")
  	.attr("class", "y axis")
  	.call(d3.axisLeft(y).ticks(5).tickSizeInner([-width]).tickFormat(function(d){
  		if (d > 500) { return Math.round( d / 1000 ) + 'K';}
  	}));

This limits the number of ticks to 5, formats the tick numbers and sets the ticks to extend the width of the graph. We can also add labels to our y axis by appending a text element.

1
2
3
4
5
6
7
8
9
10
11
12
g.append("g")
  	.attr("class", "y axis")
  	.call(d3.axisLeft(y).ticks(5).tickSizeInner([-width]).tickFormat(function(d){
  		if (d > 500) { return Math.round( d / 1000 ) + 'K';}
  	}))
  .append("text")
    .attr("x", -200)
    .attr("y", -50)
    .attr("transform", "rotate(-90)")
    .attr("text-anchor", "start")
    .attr("fill", "#383c42")
    .text("Weather Events");

Let’s also open a style block up in the head of our html and add some styles to the axes.

1
2
3
4
5
6
7
8
9
10
.axis {
	font: 15px sans-serif;
}
.axis path,
.axis line {
	 fill: none;
	 stroke: #D4D8DA;
	 stroke-width: 1px;
	 shape-rendering: crispEdges;
}

Now when the page is refreshed you can see that our graph is starting to take shape.

Dremio and D3 tutorial - d3 axes

The last step to get our first sequence of data to render is to use D3 to iterate through each piece of our data and append another SVG element to our SVG container. We set attributes to specify the x and y location as well as the bar width, height and fill color.

1
2
3
4
5
6
7
8
g.selectAll(".bar")
  	.data(data)
  .enter().append("rect")
    .attr("x", function(d) { return x(d.month); })
    .attr("y", function(d) { return y(d.Count_EVENT); })
    .attr("width", x.bandwidth())
    .attr("height", function(d) { return height - y(d.Count_EVENT); })
    .attr("fill", "#383c42")

Now we have a complete looking bar graph of the count of weather events for each month.

Dremio and D3 tutorial - d3 bar graph

One of the best parts of D3 is that no matter how we change our underlying data, this graph will always resize and restructure itself to be the most fitting visual representation. For instance, here’s what our data looks like if we limit it to the year 2016.

Dremio and D3 tutorial - d3 alternate bar graph

D3 has taken all the guesswork out of sizing elements and scaling axes. This means that we can keep filtering or further manipulating the data in Dremio and not have to worry about changing any of our D3 settings. All we have to do is pull the updated data into our web page.

Next let’s plot the search interest for our first term as a line on top of the same graph with a separate y axis.

First let’s create our secondary y axis. We do this in the same way we created our other two axes. We begin by setting the range of the axis, then we set a domain based on our data. Finally we render the actual element as well as a text element to act as our label.

1
2
3
4
5
6
7
8
9
10
11
g.append("g")
  	.attr("class", "y2 axis")
  	.call(d3.axisRight(y2))
  	.attr("transform", "translate(" + width + " ,0)")
  .append("text")
    .attr("x", 100)
    .attr("y", -50)
    .attr("transform", "rotate(90)")
    .attr("text-anchor", "start")
    .attr("fill", "#383c42")
    .text("Search Interest");

Then we start to create our line object and specify which data maps to the x and y.

1
2
3
var line = d3.line()
    .x(function(d) { return x(d.month); })
    .y(function(d) { return y2(d.term_1); });

We follow that up by rendering the line being sure to offset it by half of a bar’s width so that each point on the line is in line with the center of each bar.

1
2
3
4
5
g.append("path")
	.data([data])
	.attr("class", "line")
	.attr("transform", "translate(" + x.bandwidth()/2 + ",0)")
	.attr("d", line);

Dremio and D3 tutorial - d3 bar and line graph

For some extra functionality let’s add a hoverable tooltip that gives us the exact data for each bar and line value. To do this, first use javascript to create a new div that has a class of “toolTip”.

1
var tooltip = d3.select("body").append("div").attr("class", "toolTip");

Next add an event listener for each of our rendered bars that changes the html of the tooltip and an event listener that hides the tooltip again when we mouseout. Our updated bar rendering code looks like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
g.selectAll(".bar")
  	.data(data)
  .enter().append("rect")
    .attr("x", function(d) { return x(d.month); })
    .attr("y", function(d) { return y(d.Count_EVENT); })
    .attr("width", x.bandwidth())
    .attr("height", function(d) { return height - y(d.Count_EVENT); })
    .attr("fill", "#383c42")
	.on("mousemove", function(d){
        tooltip
			.style("left", d3.event.pageX - 50 + "px")
			.style("top", d3.event.pageY - 90 + "px")
			.style("display", "inline-block")
			.html((fullTime(d.month)) + "<br>" + 
          		(d.Count_EVENT) + " Weather Events<br>" + 
          		(Math.round(d.term_1*100)/100) + " Search Interest"
          	);
    })
	.on("mouseout", function(d){ 
		tooltip.style("display", "none");
	});

For the last piece of functionality we are going to add the ability to switch between search terms. Let’s create a new function that does a few things. First it selects our SVG element again, then it resets the x and y values to the new term that we pass to the function. Next it rescales the domain of the Y axis and finally it rerenders both the line and the Y axis.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
function updateData(term) {
    var g = d3.select("svg").transition();

	line = d3.line()
	    .x(function(d) { return x(d.month); })
	    .y(function(d) { return y2(d[term]); });

	y2.domain([0, d3.max(data, function(d) { return d[term]; })]);

    g.select(".line")
        .duration(750)
        .attr("d", line(data));

    g.select(".y2.axis")
    	.duration(750)
    	.call(y2);
}

To run the function we will add a button for each of the terms.

1
2
3
4
5
6
7
<p>
	Search Term: &emsp;
	<button onclick="updateData('term_1')">Snow</button>
	<button onclick="updateData('term_2')">School Closures</button>
	<button onclick="updateData('term_3')">Emergency Supplies</button>
	<button onclick="updateData('term_4')">Climate Change</button>
</p>

Finally we can add a little bit of CSS to style and position our tooltip as well as style our buttons. Altogether now our final code should look like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
<!DOCTYPE html>
<html>
<head>
<title>Severe Weather &amp; Search Trends</title>
<meta charset="utf-8">
<meta name="robots" content="noindex">
<style>
	body {
		text-align:center;
		font-family: helvetica;
		color:#383c42;
	}
	button {
		background-color: #31d3db;
		color:white;
		padding:7px 15px;
		outline:none;
		font-weight:bold;
		font-size:13px;
	}
	button:hover {
		cursor:pointer;
	}
	.axis {
		font: 15px sans-serif;
	}
	.axis path,
	.axis line {
	  fill: none;
	  stroke: #D4D8DA;
	  stroke-width: 1px;
	  shape-rendering: crispEdges;
	}
	.toolTip {
		position: absolute;
		font-family:helvetica, sans-serif;
		display: none;
		height: auto;
		color:white;
		background-color: #a1576f;
		padding: 14px;
		text-align: center;
		box-shadow: 0px 0px 10px 2px rgba(0,0,0,0.1);
	}
	.line {
	  fill: none;
	  stroke: #31d3db;
	  stroke-width: 2px;
	}
	rect:hover {
		cursor:pointer;
	}
</style>
<script src="https://d3js.org/d3.v4.min.js"></script>
<script type="text/javascript">
	var data = <%- JSON.stringify(dremioData) %>;
</script>
</head>

<body>
<h1>Severe Weather vs. Search Interest</h1>
<svg width="960" height="500"></svg>
<p>
	Search Term: &emsp;
	<button onclick="updateData('term_1')">Snow</button>
	<button onclick="updateData('term_2')">School Closures</button>
	<button onclick="updateData('term_3')">Emergency Supplies</button>
	<button onclick="updateData('term_4')">Climate Change</button>
</p>
<script type="text/javascript">
	var svg = d3.select("svg"),
	    margin = {top: 30, right: 70, bottom: 30, left: 70},
	    width = +svg.attr("width") - margin.left - margin.right,
	    height = +svg.attr("height") - margin.top - margin.bottom;

	var tooltip = d3.select("body").append("div").attr("class", "toolTip");

	var parseTime = d3.timeParse("%b %y");
	var monthTime = d3.timeFormat("%b");
	var fullTime = d3.timeFormat("%b %Y");

	data.forEach(function(d) {
	    d.month = parseTime(d.month);
	});

	var x = d3.scaleBand().range([0, width]).padding(0.1),
		y = d3.scaleLinear().rangeRound([height, 0]),
		y2 = d3.scaleLinear().rangeRound([height, 0]);

	var g = svg.append("g")
		.attr("transform", "translate(" + margin.left + "," + margin.top + ")");

	x.domain(data.map(function(d){ return d.month; }));
	y.domain([0, d3.max(data, function(d) { return d.Count_EVENT; })]);
	y2.domain([0, d3.max(data, function(d) { return d.term_1; })]);

	var line = d3.line()
	    .x(function(d) { return x(d.month); })
	    .y(function(d) { return y2(d.term_1); });

	g.append("g")
        .attr("class", "axis")
        .attr("transform", "translate(0," + height + ")")
      	.call(d3.axisBottom(x).tickFormat(function(d){
      		switch (d.getMonth()) {
      		    case 0:
      		    case 3:
      		    case 6:
      		    case 9:
      		        return monthTime(d);
      		}
      	}));


    g.append("g")
      	.attr("class", "y axis")
      	.call(d3.axisLeft(y).ticks(5).tickSizeInner([-width]).tickFormat(function(d){
      		if (d > 500) { return Math.round( d / 1000 ) + 'K';}
      	}))
      .append("text")
        .attr("x", -200)
        .attr("y", -50)
        .attr("transform", "rotate(-90)")
        .attr("text-anchor", "start")
        .attr("fill", "#383c42")
        .text("Weather Events");

    g.append("g")
      	.attr("class", "y2 axis")
      	.call(d3.axisRight(y2))
      	.attr("transform", "translate(" + width + " ,0)")
      .append("text")
        .attr("x", 100)
        .attr("y", -50)
        .attr("transform", "rotate(90)")
        .attr("text-anchor", "start")
        .attr("fill", "#383c42")
        .text("Search Interest");

    g.selectAll(".bar")
      	.data(data)
      .enter().append("rect")
        .attr("x", function(d) { return x(d.month); })
        .attr("y", function(d) { return y(d.Count_EVENT); })
        .attr("width", x.bandwidth())
        .attr("height", function(d) { return height - y(d.Count_EVENT); })
        .attr("fill", "#383c42")
    	.on("mousemove", function(d){
            tooltip
				.style("left", d3.event.pageX - 50 + "px")
				.style("top", d3.event.pageY - 90 + "px")
				.style("display", "inline-block")
				.html(
				    (fullTime(d.month)) + "<br>" + 
                    (d.Count_EVENT) + " Weather Events<br>" + 
                    (Math.round(d.term_1*100)/100) + " Search Interest"
                );
        })
    	.on("mouseout", function(d){ 
    		tooltip.style("display", "none");
    	});

    g.append("path")
		.data([data])
		.attr("class", "line")
		.attr("transform", "translate(" + x.bandwidth()/2 + ",0)")
		.attr("d", line);

    function updateData(term) {
        var g = d3.select("svg").transition();

    	line = d3.line()
    	    .x(function(d) { return x(d.month); })
    	    .y(function(d) { return y2(d[term]); });

    	y2.domain([0, d3.max(data, function(d) { return d[term]; })]);

        g.select(".line")
            .duration(750)
            .attr("d", line(data));

        g.select(".y2.axis")
        	.duration(750)
        	.call(y2);
    };
		
</script>
</body>
</html>

And here is our final rendered graph with full functionality:

Search Term:  

Dremio and D3 tutorial - D3 final graph

Summary

D3 has the power to create a huge range of very complex visualizations that adapt to fit the scope and range of your data. Using Dremio, we eliminate the need to process any of our data with a server side language or to write our own massive SQL query. Instead we can be completely hands off once our initial D3 visualizations are setup.

As more data gets added to our original physical dataset, Dremio will automatically update our virtual dataset. This means that we won’t ever have to change any setting in Dremio, Node, or D3 if we want to incorporate new data into our graph. All we would need to do is pull the new data through our ODBC connection and the rest would be handled for us.

To see more examples of how you can use D3 check out their documentation.