How do you build a real-time dashboard on top of BigQuery? That’s the question that this blog post answers via a step-by-step tutorial for building a real-time React dashboard app over BigQuery using Tinybird, Next.js, and Tremor components. This is what the final product looks like:
If you’d like to skip ahead to the tutorial, click here.
But first, let’s answer two questions:
Google BigQuery is a serverless data warehouse, offering powerful online analytical processing (OLAP) computations over large data sets with a familiar SQL interface. Since its launch in 2010, it’s been widely adopted by Google Cloud users to handle long-running analytics queries to support strategic decision-making through business intelligence (BI) visualizations.
Sometimes, however, you want to extend the functionality of your BigQuery data beyond business intelligence. One such extension is user-facing analytics: real-time data visualizations that can be integrated into user-facing applications. A growing trend emphasizes the importance of including real-time data visualizations in user-facing applications, so if your data is in BigQuery, you’ve come to the right place.
A real-time dashboard has three important qualities:
Real-time dashboards are increasing in popularity as product teams seek to embed analytics into their applications. To support new data-driven features, these embedded analytics must be high-speed for many concurrent users.
For Google Cloud Platform (GCP) users, the de facto method for building dashboards over BigQuery is with Looker and Looker Studio (formerly known as “Google Data Studio”).
Looker is a data modeling and business intelligence tool that can connect with data sources like BigQuery. With Looker, you can filter, aggregate, and enrich data sources to build new data models and tables. You can embed dashboards built with Looker into user-facing applications using iframes.
Looker Studio is a simple report-building tool that can connect to many different Google data sources, including BigQuery and Looker. With Looker Studio, you can import data sources and build reports to filter and aggregate those data sources in a visual manner. As with Looker, you can embed those reports into a web application or product using a simple iframe.
Looker and Looker Studio are good business intelligence tools. If you use the two together, you can build comprehensive data models and consistent business metrics, and then construct dashboards to visualize them.
Unfortunately, these dashboards are often slow. The main culprits, according to Google:
Even if you follow all of Google’s recommendations to optimize Looker performance, you still won’t be able to build real-time dashboards over BigQuery.
Here’s why:
If you want to build fast dashboards over BigQuery for user-facing applications, you have some options:
Now for the fun part, let’s build a real-time dashboard over BigQuery. To do so, we’re going to use Tinybird and its native BigQuery Connector. Tinybird will replicate BigQuery tables into its real-time database, enable us to build metrics for our dashboards with SQL, and expose those metrics as APIs that we can integrate into our frontend code.
To build an interactive dashboard, we’ll be creating a Next.js app that utilizes Tremor dashboard components. This builds off of another real-time dashboard tutorial we published for streaming data sources.
You can follow along below, or deploy the entire project by cloning the GitHub repo, setting up your individual resources in BigQuery and Tinybird, and deploying it.
Let’s dig in.
Tinybird will serve as the real-time analytics engine that powers our real-time dashboard. To use Tinybird, sign up for a free account here and create a Workspace. I’ve called my Workspace bigquery_dashboard.
To get your BigQuery data into Tinybird, you’ll use the Tinybird BigQuery Connector. Follow the steps in the documentation to authorize Tinybird to view your BigQuery tables, select the table you want to sync and set a sync schedule.
In this tutorial, I’ve created a small sample dataset that contains 20,000 rows of fake baseball stats. If you want to follow along, you can download it from the repo and upload it to your BigQuery project.
Tinybird will copy the contents of your BigQuery table into a Tinybird Data Source and ensure the Data Source stays in sync with your BigQuery table.
In Tinybird, a Pipe is a transformation definition comprised of a series of SQL nodes. You can build metrics through a series of short, composable nodes of SQL. Think of Pipes as a way to build SQL queries without always needing to write common table expressions or subqueries, as these can be split out into independent nodes.
Here, for example, is a simple single-node Pipe that calculates the season batting average for each player:
You can create a Pipe from the BigQuery Data Source you just added by clicking “Create Pipe” in the top right corner of the Tinybird UI.
Every good dashboard is interactive. We can make our Tinybird queries interactive using Tinybird’s templating language to generate query parameters. In Tinybird, you add query parameters using {{<DateType>(<name>,<default_value>}}, defining the data type of the parameter, its name, and an optional default value.
For example, I can dynamically change the number of results I return from the above Pipe with a limit parameter and a default value of 10:
The magic of Tinybird is that you can instantly publish your Pipes as fully-documented, scalable REST APIs in a click. From the Pipe definition in the Tinybird UI, click “Create API Endpoint” in the top right corner, select the node you want to publish, and you’re done. Make sure to name your Pipe something description, as the Pipe name will be used as the URL slug for your API endpoint.
In the project repository, you’ll find 5 .pipe files in the pipes/ directory that define the 5 API endpoints we’ll use to build our real-time dashboard:
Create a Next.js app as follows:
Tinybird APIs are accessible via Auth Tokens. In order to run your dashboard locally, you will need to update your env.local file with the following:
To support the dashboard components you’re about to build, you might find it helpful to create a helper file that contains all your Tinybird API references. In the project repo, that’s called tinybird.js and it looks like this:
For this tutorial, we’re using the Tremor React library, because it provides a clean UI out of the box with very little code. You could easily use ECharts or something similar if you prefer.
Our dashboard contains 3 Bar Charts, 1 Area Chart, and 1 Bar List. We’ll use Tremor Cards to display these components, and each one will have an interactive input. In addition, we’ll show the API response latency underneath the chart (just so we can see how “real-timey” our dashboard is).
Here’s the code Player Batting Averages component (playerBattingPercentages.js):
You’ll find 5 total dashboard components in the src/app/components directory of the repository. Each one renders a dashboard component to display the data received by one of the Tinybird APIs.
Finally, you’ll update your page.js file to render a nicely laid out dashboard with our 5 components. This file gets your Tinybird Auth Token from your local environment variables to be able to access the Tinybird APIs, then renders the 5 components we just built in a Tremor Grid.
To visualize your dashboard, run it locally with npm run dev and open http://localhost:3000. You’ll see your complete real-time dashboard!
Notice the latencies in each dashboard component. This is the Tinybird API request latency. Note this is NOT using any sort of cache or query optimization. Each request is directly querying the 20,000 rows in the table and returning a response. As I interact with the dashboard and change inputs, the APIs respond.
In my case, that’s happening in just a few milliseconds. Now that’s a fast dashboard.
In this tutorial, you learned how to build a real-time JavaScript dashboard over BigQuery using Tinybird and Next.js. Tinybird is the real-time analytics platform that makes this possible. With Tinybird, you can quickly sync BigQuery tables into a platform optimized for real-time analytics, create metrics with nothing but SQL, and publish those metrics as real-time APIs that scale to support thousands or millions of concurrent requests.
If you need to reduce latency with BigQuery, whether to build real-time dashboards or any other user-facing analytics feature, Tinybird can be extremely useful. You can learn more about Tinybird by visiting the website, checking out the documentation, or requesting a demo. If you’re ready to start building, you can sign up for free here.