True Digital Group Helps to Flatten the Curve with MemSQL
Updated: Jan 13, 2021
True Digital Group Helps to Flatten the Curve with MemSQL
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. Will the world have to wait for an antiviral treatment or a COVID-19 vaccine before businesses like this can re-open? With a vaccine 12-18 months away, businesses across industries are looking to thrive now. In this new and emerging economy, businesses in the cloud can quickly activate. Not in the cloud yet? We can make the transition easy. Read on to learn how.
How to Start Moving Again
Why is this approach of a lockdown necessary in the first place? This situation exists 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 would 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 “test and trace” 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 be able to help track the spread of COVID-19.
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 more than 30 million 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 2 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 like location, browsing 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 also see how much the specific area increased and decreased by the number of people. 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, 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 combines 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. This combination of capabilities suited the requirements of this project particularly well. 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 geo-analytic 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 to ensure the best possible interactive user experience. Techniques in the frontend with React and Web Workers help to ensure the immediate, interactive responsive 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 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 are available such as time bucketing and smoothing.
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 Governments 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. Efforts like True Digital’s to provide a means of identifying emerging breakdowns in social distancing help to get us through the crisis faster. Efforts like this may even provide a way to get the world moving again in a safe fashion and restart economies while we await the 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 to become the country’s digital enabler, True Digital Group has built deep competences in cutting-edge technologies such as artificial intelligence, 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 also launched its regional operations across Southeast Asia, with Indonesia and the Philippines as its first two markets. Visit our website for more details at www.truedigital.com