How Buzzvil pipes data?

At Buzzvil, we are keeping track of activities of more than 17 million user in more than 30 nations. Such activities include various screen-related activities, AD impressions, AD clicks, AD conversions and beyond. These data comes from multitude of sources and usually get stored in different types of databases ( MySQL, DynamoDB, Redis, and even S3). By having these data in a single storage, we were able to analyze cross-service and cross-platform metrics with ease.

Building and maintaining a data pipeline that directs the data from multiple sources to a single data warehouse was a challenge for our team. and here is how we did it!

Before we discuss the pipeline, Redshift, our main data warehouse is worth mentioning in this blog post. It has surprised us since the beginning and is continuing to do so. Redshift is an AWS-managed, SQL based columnar data warehouse suited for complex and large-scale analytics. It is a solution well adapted by many enterprises customers (Yelp, Coursera, Pinterest, etc.), to drive insight from various types of data generated from their customers. At Buzzvil, we adapted Redshift because of the following reasons:

The primary reason for such preprocessing step is the sheer size of the incoming data. Some data might be too raw for analysts and data scientists to query directly. Thus, we utilized AWS Athena (https://aws.amazon.com/athena/) to leverage the fact that AWS only charges us for the size of the data read by the Athena query. If we are to preprocess the data through EMR or other MapReduce solutions, we might have to spend much more on computing costs in order to process more than

How?

Send data to S3

Aggregate/Process using Athena

Load the processed data into Redshift (COPY command)

Pros?

Serverless (No need to manage EMR clusters or servers)

Cheap ($5 for each 1TB read from S3)

Cons?

During peak hours (12:00 AM UTC), some queries might fail. -> We do not recommend running mission-critical logics on Athena.

Does not (yet) provide the full functionality of PRESTO DB.

2. Via Firehose (Impression, Clicks, Device, Events)

Why?

Kinesis Firehose is excellent as it not only provides a stable pipeline to various data destinations such as Redshift, Elasticsearch and S3, but also seamlessly integrates with Fluentd which provides a steady and stable stream of data from servers to firehose.

With firehose-fluentd integration, rather than having to manage two separate pipelines ( SERVER -> S3, S3 -> Redshift ), we only have to manage and monitor a single data pipeline from the source to the sink.

In order to sync subsets of data from multiple RDS MySQL databases, we had to employ three different techniques to copy the relevant data to our Redshift cluster (This is the least glamorous option of the three)

How?

1.FULL_COPY

Extract a full copy (dump) of a MySQL table and load them as SQL inserts into Redshift)

2.INCREMENTAL_COPY

Start from the last pkey, read any new rows and copy them into Redshift

3.UPDATE_LATEST_COPY

Start from the last updated_at timestamp, find any newly updated/created rows and copy them (after removing duplicates) into Redshift

Pros?

Fine tuned methods to meet the data characteristics

Easier to manage compared to binlog replication methods.

Cons?

Have to manage separate servers or lambdas to handle the MySQL -> Redshift sync task

Redshift ORM is yet mature to support smooth schema altering.

These three methods differ by the type of data that is being delivered to the target sink. For example, transactional, log type data would be delivered by firehose or be aggregated and then loaded to Redshift. On the other hand, fact tables from MySQL might be synced to Redshift via a specific type of CDC (change data capture) sync method. A combination of these 3 methods allows our analysts and BD managers to query cross-service or cross-platform data with ease.