CASE STUDY

Case Study: True Digital Group Helps to Flatten the Curve with MemSQL

Domenic Ravita

True Digital Group is using MemSQL to power a contact tracing app, preventing the spread of COVID-19 in Thailand. The app uses React and Web Workers in the frontend, with MemSQL Pipelines and time series functions processing fast ingestion of events, and geospatial functions used to plot data on the map in real-time. The first functional version of the app was built in two weeks.  

Our Global Lockdown

Preventing the spread of the COVID-19 disease has been our collective priority in this pandemic. By avoiding non-essential, discretionary travel such as shopping trips and social gatherings, we keep ourselves safe and prevent ourselves from unknowingly being asymptomatic carriers to our most vulnerable members of society. The latest data from the hardest hit hotspots of the outbreak show that social distancing is starting to have the intended effect of flattening the curve. Millions of businesses have been forced to close with a disproportionate impact on the restaurant, entertainment, and travel and hospitality industries. Finding ways to safely reopen economies is urgently needed.

How to Start Moving Again

Shelter-in-place orders have been needed because carriers of COVID-19 can be asymptomatic for as much as two weeks and could unknowingly spread the virus. Once a person tests positive, the traditional approach is to then conduct contact tracing, which is done by interviewing each patient for 12 hours to determine with whom he or she has been in contact over the prior days and weeks before the diagnosis. Most people can’t recall all the individuals with whom they’ve been in contact, and they could be unaware of how their proximity to others in public places and stores may have spread the virus to those around them. These interviews require human contact tracers. To conduct interviews in the U.S., given its population, estimates for the number of human contact tracers needed range from 100,000 to 300,000. This makes it clear that to start moving and interacting face-to-face again we must be able to automate some aspects of this to perform “test and trace” at scale and in a timely fashion. The solution to this problem may be your phone.

The True Solution

True Digital Group’s Analytics team is rapidly moving on this idea in Thailand. Operating under True Digital Group, the digital arm of True Corporation, the leading telecommunication provider in the country, the Analytics team is collecting and aggregating anonymous mobile phone locations in real-time so they can assist the Thai Government authorities in providing a tool to help track the spread of COVID-19. 

Tracking novel coronavirus distancing near a Thai university.
The tracepulse app combines live and historical alerting for COVID-19 tracking.

 

To be the most effective in preventing further spread, the data has to be low-latency, real-time data, which shows how these anonymous mobile phones are moving, and to immediately and proactively determine the location hubs of increasing population density. Large gatherings are especially worrisome during a pandemic like this. The key concept is to track how mass population moves from one part of the country to the other, then determine what they can deliver to the Thai Government authorities to help them, firstly, protect themselves and, secondly, obviate the need for testing for the virus.

They would like to have a real-time visualization and alerting system which automatically determines where large gatherings are forming, via the geographic density of the mobile phone locations. There are several technical challenges that must be overcome to anonymously track and trace at scale. First of all, they have to perform event stream processing on 500,000 anonymous location events every second for over 30 million mobile phones. Second, the visualization has to be updated at least every 2 minutes. Third, the solution must support a moving time window of undetermined duration to support queries against real-time and recent data for contact tracing. Fourth, reference data and historical data must be available in some situations for further analysis and investigation – and this dataset is 2 petabytes. Fifth, the real-time visualization must support geoanalytic queries with ultra-low latency, on the order of milliseconds. And finally, the first functional version of this system must be built in only two weeks. 

Prior to this pandemic, True Digital Group’s Analytics team was already leading in the analytics space for telco, with the successful deployment of a platform able to crunch trillions of relevant customer data points such as location, browsing activity, etc. True Digital Group’s Analytics team successfully launched location services based on this, for example to optimize retail branch locations. The computation strategy was to land data in Hadoop, then run map-reduce operations on the data on a scheduled batch basis every 24 hours to create the aggregates and derived data. However, the tool was designed to operate in batch mode, and had no real-time capability. Once the pandemic struck, the need moved from “delayed-data-is-good-enough” to “in-the-moment, analytics with an SLA” being a necessity.

The solution was to stream raw event data from the core telco network into MemSQL via Google Cloud Storage (GCS). Now, with this solution, they can see how many people might be crowding into a specific area. The first usage is to power the wall-sized dashboards in the crisis operations control center for the country. These dashboards allow users to zoom into one province, district, or sub-district of Thailand, and see in real time how many people are located there. This helps to anticipate where large gatherings may be forming. The second usage is for determining the optimal placement of resources, such as medical supplies. This enables dynamic reallocation of human resources and physical resources, which makes the disaster relief efforts more proactive and timely.

Building a Modern App with MemSQL

MemSQL is a distributed operational database that provides a scalable backend, combining the capabilities of transactional and analytical databases, while supporting high-speed parallel ingestion and relational SQL. It also provides specialized data types and functions supporting geospatial, time-series, document, and key-value use cases. The combination of these capabilities and the familiar ANSI SQL of MemSQL suited the requirements of this project, enabling True Digital to integrate and simplify its backend data platform. 

Anonymized location data is continuously written from the core telecom network to Google Cloud Storage (GCS) as a series of Parquet files. As these files appear, they are continuously ingested in parallel by MemSQL Pipelines and then processed by MemSQL stored procedures. While this is happening, the database is serving highly-concurrent queries invoked by an interactive geoanalytic dashboard. This drillable dashboard provides an in-the-moment view of the density of mobile phones in any given area, from the country level down to the city block. Users can visually explore this geographical information and zoom in for more granular, detailed views of clusters of anonymized mobile phones to spot potential emerging problems. Each of these clicks progressively query MemSQL to calculate the new statistics for each geographical region as users drill down. MemSQL’s geospatial functions support the ability to determine the spatial relationships across data, such as distance, containment, and intersection calculations. Geospatial objects are first-class datatypes in MemSQL, which means that you can use spatial relationships to join tables. For example, to find all of the businesses in a neighborhood, you join the businesses table with the neighborhoods table on whether the business location (a point) is contained by the neighborhood’s shape (a polygon). 

The True Digital solution is a progressive web application (PWA) which leverages React and NodeJS to expose APIs to backend microservices, ensuring the best possible interactive user experience. The use of React and Web Workers for the frontend implementation helps to ensure the immediate, interactive responsiveness of the app. For fast-changing data, the response time of the backend database is a crucial component in ensuring this fast user experience. MemSQL is uniquely suited to these types of workloads, which combine the scaling of new data being written while simultaneously handling highly-concurrent lookup queries as well as complex analytic queries. With MemSQL supporting more than a trillion rows per second on raw data, it comfortably handles the ingestion of 500,000 events per second and being the application database for this customer-facing application. 

The app supports queries by location over a recent history of data, allowing users to see trends over time. Data is defined as sequences of events, with each event labeled with a timestamp. Time series data can be stored in MemSQL using rowstore or columnstore tables. Each row has a time-valued attribute to hold the event time. Time series functions, such as time bucketing and smoothing, are available.

Blazingly Fast Delivery

Faced with such a fast-moving public health crisis, every minute that can be saved in getting an accurate, precise prevention system in place literally saves lives. In the frontlines of this global war against a virus, we see our healthcare and essential services workers as heroes. The data architects, data engineers, and frontend developers working 24/7 to get this life-saving system in production are helping prevent the spread, contain the virus, and flatten the curve. In just one week, the first operational version of the application was designed and built with MemSQL, showing that rapid development and quick, agile iterative DevOps cycles can be done with MemSQL. Since then, the functionality has been iteratively developed and extended in order to demonstrate new possibilities for analysis to the Thai Government authorities. 

The Light at the End of the Tunnel

While many are working around the world to develop treatments and a vaccine, this will take quite a long time. In the US, the COVID-19 Healthcare Coalition is one example of such an effort. It is a private-sector led response that brings together healthcare organizations, technology firms, nonprofits, academia, and startups to preserve the healthcare delivery system and help protect U.S. populations. New crisis management systems such as True Digital’s will continue to be rapidly developed and expanded to meet the needs of the new normal. In the workplace, companies must consider how this new normal impacts business continuity planning and human resources. Efforts like True Digital’s to provide a means of identifying emerging breakdowns in social distancing help us to better manage in a crisis. Approaches like this may even provide a way to get the world moving again in a safe fashion and restart economies while we await a vaccine. MemSQL is proud to partner with True Digital for such an important cause.

About True Digital Group

True Digital Group is the digital arm of True Corporation, Thailand’s leading telecommunication company. With the goal of becoming the country’s leader in digital solutions, True Digital Group has built deep competences in cutting-edge technologies such as artificial intelligence (AI), big data, blockchain, cloud, Internet of Things (IoT), and robotics. With this, True Digital Group is able to build a unique ecosystem of digital platforms and solutions, addressing the digital needs of consumers, merchants, and enterprises. True Digital Group has launched regional operations across Southeast Asia, with Indonesia and the Philippines as the first two new markets. Visit the website at www.truedigital.com for more details. 

MemSQL Helios eclipse
Introducing
MemSQL Helios
The World’s Fastest Cloud Database