Using Retool and TiDB Cloud to Build a Real-Time Kanban in 30 Minutes

Author: Jinpeng Zhang (TiDB Cloud Engineer) Editors: Fendy Feng, Tom Dewan

A visualized board, or “Kanban,” can really help us manage our workflows, track the progress of our projects, or general visualized insights. Ideally, this should all be in real time. However, building a real-time Kanban application can be a tedious task and time-consuming.

Now, we can make things much simpler with two remarkable tools: TiDB Cloud and Retool. TiDB Cloud is a fully-managed Database-as-a-Service (DBaaS) that brings TiDB, an open source Hybrid Transactional and Analytical Processing (HTAP) database, to your cloud. Retool is a low-code development platform that helps you build internal tools fast. By leveraging the two tools, you can build a real-time Kanban application in half an hour, and create a Kanban and extract insights from it by just writing simple SQL statements.

In this post, I’ll demonstrate an application I built with TiDB Cloud and Retool, and then walk you through how to build such an application step by step.

A Kanban demo: Hacker News Insight

Before we go into detail on how to build a real-time Kanban application, let me show you a demo called Hacker News Insight I built with TiDB Cloud and Retool.

Using this Kanban, you can quickly get historical and real-time insights about Hacker News, a popular news service platform that focuses on computer science and entrepreneurship. For example, you can identify the most active post submitter and commenters, the number of new posts, and comments submitted in a certain period of time or in real time.

Getting the source data and preparing the database

Before we start building the real-time Kanban, we need to get the source data and the database ready first.

Create your TiDB Cloud cluster

1. Sign up for a free TiDB Cloud account and log in to it. If you already have one, you can log in to your account directly.
2. On the Active Clusters page, click Create Cluster.
3. Create your cluster. On the Create Cluster page, select the free Developer Tier. Set up your cluster name, cloud provider (only AWS is available for the Dev Tier), and region. (We recommend that you use the region where your application is deployed—especially if your application is sensitive to latency.) Then, click Create.
   Note: You can also select the paid TiDB Dedicated Tier to get better performance.
4. After you create a cluster, you will be directed to the Active Clusters page. On the right side of this page, click Connect.
5. On the pop-up window, create a traffic filer and connect with a SQL client.

Prepare the data

After you create a TiDB Cloud cluster, you can start to prepare the data source for your application. If you have applications that can produce real-time data, shadow your application’s traffic to the TiDB Cloud cluster you just created.

In this post, I will use a Hacker News Crawler to grab the real-time data of newly-submitted articles and newly-given comments on Hacker News.

Note: If you want to use your historical data as the data source, you can refer to How to Import Sample Data into TiDB Cloud.

Subscribe to Blog Request a Demo

Build your Kanban application with Retool

Create a Retool account and connect it with TiDB Cloud

1. Sign up for a trial Retool account and sign in to it. If you already have one, you can sign in to your account directly. After you sign in, you can start to create your first application.
2. Create a new application. On the upper right side of the Onboard Page, click Create new.
3. Select your resource type. On the Select a resource type window, select MySQL because TiDB is completely MySQL compatible.
4. Create your MySQL resource. Fill in all the fields such as Name, Folder, Host, Port, Database name, Database username, and Database password. The default port for TiDB Cloud is 4000. When you’re done, click Create resource.

Create your real-time Kanban

After you create your Retool account and connect it with TiDB Cloud, you can use Retool’s components to create your customized and visualized Kanban, and make analytical queries by writing simple SQL statements.

Create a table to show the latest posted 500 Hacker News stories

1. Select a table. On the Retool page, in the Components section of the Kanban, drag a Table component to the center.
2. Open a new query. At the bottom of the Query editing section, click New. Write the following SQL commands to query the real-time data stored and processed in TiDB Cloud.

    _SELECT * FROM items WHERE type='story' and title> '' ORDER BY time DESC LIMIT 500;_

1. Connect the query and the table you just added in the Inspect section.
2. Start the analytical query process. Click Run. Your Kanban board displays the latest 500 stories posted on Hacker News.
   

Create a chart to show the top 20 most active post-submitters

Creating a chart is similar to creating a table. From the Components section of the Kanban on the right side, drag a Chart component to the center. Then, write the following SQL commands to query the top 20 most active post-submitters on Hacker News.

    SELECT who AS author, COUNT(*) as number_of_articles FROM items
        WHERE time > UNIX_TIMESTAMP('2022-07-01') and type="story" and who <> ''
        GROUP BY author ORDER BY number_of_articles DESC LIMIT 20;

Then, your Kanban board displays the top 20 most active post-submitters of Hacker News.

This sample application is only a small part of what you can do with Retool and TiDB Cloud. To learn how to use other Retool components and add them to your application, you can refer to the Retool documentation. If you want to share your Kanban application with others, click Share on the upper right of your Kanban board. Select the access level, and complete the appropriate fields.

Summary

In this tutorial, you’ve learned how to leverage TiDB Cloud and Retool to build a Kanban application that can handle real-time analytics. I hope I’ve inspired you to build your own application.

If you encounter any problems, you can contact us through Twitter, our Slack channel, or you can join TiDB Internals to share your thoughts and feedback with us.

Keep reading:
Build a Better Github Insight Tool in a Week? A True Story
The Beauty of HTAP: TiDB and AlloyDB as Examples
The Long Expedition toward Making a Real-Time HTAP Database

Original Blog Articles

Using Retool and TiDB Cloud to Build a Real-Time Kanban in 30 Minutes.