Implementing a Statistics Dashboard with BigQuery and BI Engine

We recently released the Statistics Dashboard feature (beta). If you have published at least one article, you can view your statistics once you log in to Zenn. Please use it to check your writing frequency and as a reference for your view counts.

https://info.zenn.dev/2023-06-01-stats-dashboard-beta

In this post, I will record how we implemented it, along with the challenges we faced.

TL;DR

• Page view events are synced from Google Analytics to BigQuery, creating a challenge in how to JOIN event data (BigQuery) with article data (Cloud SQL).
• We decided to connect BigQuery to Cloud SQL using external connections.
• To avoid per-query costs when reading statistics, we decided to use BigQuery BI Engine.
• We use Scheduled Queries to aggregate data into a minimal format that fits within the BI Engine capacity.
• Charts are styled and rendered using Chart.js.
• Remaining issue: Since slots are shared resources, there is a possibility that the statistics page may take time to load depending on the load status.

Prerequisites

Zenn's high-level architecture is as follows:

I personally wanted to see statistics even when I write articles, and we explored ways to make it happen on Zenn. After discussion, the team decided to implement the following three statistics:

• Viewing page views by period.
• Viewing page views per article.
• Viewing writing frequency.

To keep things simple, this post focuses on viewing page views by period. However, other statistics are implemented using the same mechanism, with only the read queries being different.

I will describe the ins and outs of implementing this.

Page views could be aggregated using the event data collected by Google Analytics. However, the page_view event in Analytics only provides the page URL and timestamp (reference). If we were to prepare custom events and record all necessary information for aggregation, the data volume would explode. Therefore, we needed a place to JOIN event data and article data.

Let's organize the prerequisites and requirements:

• page_view events are recorded in Google Analytics and already forwarded to BigQuery. This was mainly used for our year-end Recap.
• Since statistics are displayed on individual user dashboards, we must authorize logged-in users.
• We need to JOIN event data with article data (to identify ownership).

Considered Implementation Methods

Since we need to trace which user an event recorded in Analytics belongs to, it was certain that we would use our main database, PostgreSQL on Cloud SQL, in some form. We thought about how to integrate with Cloud SQL, but the options multiplied once we got into the "How" phase. First, setting aside the display part, we considered how to aggregate and acquire the desired data:

1. Maintain aggregation data in Cloud SQL and complete everything within Cloud SQL.
2. Use Cube Cloud with BigQuery and Cloud SQL.
3. Somehow JOIN BigQuery data with Cloud SQL data (★ This was adopted).

The method of keeping aggregation data in Cloud SQL

This is a method I debated adopting until the end. Since aggregation per user is required, I thought, why not just put the aggregated data into Cloud SQL? Cloud SQL automatically increases storage capacity, and if we bring the event data in, aggregation can be completed within Cloud SQL. However, from the perspective of what kind of data we want Cloud SQL to hold, I dismissed this idea. Cloud SQL is an RDB, and its primary role is for content where relationships are effectively utilized. Event data such as page_view is on a completely different scale in terms of capacity and record count. If we were to collect other event data, I don't know how much Cloud SQL could withstand. Considering the requirements and system scalability, I wanted to avoid accumulating event data in Cloud SQL.

The method of using Cube Cloud

Cube Cloud is a headless BI tool. It connects to BigQuery and allows aggregation using its own DSL.

https://zenn.dev/waddy/articles/start-cube-cloud

It has a caching mechanism, so I thought it matched the requirements for per-user aggregation. However, the true value of Cube Cloud likely lies in providing an integrated BI environment. It seems to fit scenarios where, for example, a manufacturing company with multiple factories and departments uses it as a BI tool for the marketing team or for anomaly detection at factory units. Conversely, in our case, where the data sources are BigQuery and Cloud SQL and the only display area is the user-facing dashboard (web app) with no plans for expansion, it felt a bit over-engineered.

It also required setting up a dedicated server, and the DSL would need maintenance, so I removed it from the candidates. That said, I would like to use it somewhere if the opportunity arises.

The method of completing it with BigQuery

This is the method we currently use. Since Google Analytics data is accumulated and we can also use powerful queries in BigQuery, we explored whether we could complete everything there. As mentioned earlier, we already had a track record of aggregating and calling BigQuery event data from Rails for our year-end Recap, so it was easy to imagine the implementation. Below, I will describe the challenges of relying on BigQuery and the solutions we found.

Challenges in Aggregating User-Specific Statistics with BigQuery

Unlike the year-end Recap, which runs as a one-off batch process, this time statistics data is requested at the user's convenience. This brings up different challenges.

Challenge 1: Authorization

Naturally, statistics information requested by user A must not be accessible by user B. We solved this by "handling the request itself in Rails, passing authorization via Rails' mechanism, and acquiring the aggregated data from BigQuery via the SDK from Rails." In terms of the architectural diagram, it follows the red line.

Challenge 2: JOINing with Article Data

While event data like page_view is stored and can be aggregated in BigQuery, we need to be able to identify whose article the page belongs to. To identify the article and the author from the recorded URL, coordination with Cloud SQL is necessary.

Using Datastream

We first considered keeping a replica of Cloud SQL (stripped down to the bare minimum information) in BigQuery. Just as we were considering this, Datastream allowed sending data from PostgreSQL to BigQuery.

https://cloud.google.com/blog/ja/products/databases/serverless-replication-of-operational-data-into-bigquery

However, we ultimately did not adopt this. We could not clear the following points:

• We normally use Cloud SQL through Cloud SQL Proxy, so being conscious of Public IP and authorized networks just for this did not justify the operational cost and benefits.
• The replication entity is CDC, but enabling it in PostgreSQL prevents UPDATE/DELETE operations on non-unique indexes.
  • This would be strict because we would be unable to "bulk update articles that meet specific criteria" for operational tasks.

Using External Connections

Next, we considered external connections. We adopted this. BigQuery can integrate with external data sources to load them into tables or execute queries directly (reference). Note that for Cloud SQL, you can execute queries directly from BigQuery using Federated Queries. If Cloud SQL data can be brought into the BigQuery world, the rest can be cooked in any way on the BigQuery side. In this example, since we can retrieve article IDs and URLs from Cloud SQL as shown below, the logic is that we can identify the article ID and user ID where the page_view event occurred by joining with the event data URL.

Example of using Federated Queries in BigQuery

SELECT *
FROM EXTERNAL_QUERY(
  'us.zenn_db_connection',
  '''
    SELECT id, user_id, url
    FROM articles
  ''');

The following article is a good reference for getting started with federated queries.

With this, we were ready to JOIN BigQuery event data with Cloud SQL article data.

Challenge 3: Query Costs

We found that we could JOIN article data using federated queries, so we can aggregate and retrieve the necessary data for each user. There are no functional issues. The remaining issue is cost. Unlike typical BI contexts, unspecified logged-in users will hit BigQuery queries at arbitrary times. Since BigQuery is billed per query, naive usage would result in astronomical query fees.

https://cloud.google.com/bigquery/pricing?hl=en#on_demand_pricing

If we calculate roughly: 10MB (minimum unit) x 100,000 users x 1 time = 1000TB. At $6 per TB, it would cost $6,000 if 100,000 people open the statistics page once. It's not just about the amount; I was concerned about creating a cost bottleneck in a configuration we want to scale.

Using BigQuery BI Engine

While I was worrying about billing, further investigation led me to something called BigQuery BI Engine.

BigQuery BI Engine is a fast, in-memory analysis service that accelerates many SQL queries in BigQuery by intelligently caching frequently used data.

It seems to be a service that speeds up queries by keeping data in memory. When I checked the pricing as well:

Queries that retrieve results from data stored in BI Engine are not billed for data scanning.

They are not billed for data scanning!!! In other words:

• You purchase cache capacity in advance in BI Engine and specify the BigQuery tables you want to cache.
• If all the data used for user statistics fits within the cache, you don't have to pay on-demand fees when displaying the statistics page.

That was the answer. It's a gold mine of possibilities. By the way, the actual implementation only involves setting the cache capacity and preferred tables; from an application perspective, the usage is exactly the same as standard BigQuery.

Setting how much cache capacity to purchase

Setting which tables in the region to prioritize for caching

As the name suggests, BI Engine is likely intended for caching BI data that is analyzed frequently, so this might be a slightly off-label use case. However, since the goal of reducing on-demand costs is the same, we decided to adopt it. To use BI Engine effectively, as mentioned above, we needed to fit the aggregated data within the cache capacity, so the key point for this was using scheduled queries to update a table specialized for BI Engine daily.

Preparing the bare minimum data with scheduled queries

Google Analytics event data has timestamps recorded, but the minimum unit required for aggregation is daily data. The information needed for the statistics page can be surprisingly slimmed down. If we can summarize the data needed for aggregation in one step in advance, it becomes easier to fit it into the BI Engine cache. To implement this, we adopted scheduled queries.

https://cloud.google.com/bigquery/docs/scheduling-queries?hl=en

In scheduled queries, we summarize raw data into a single aggregation step.

• Removing unnecessary data
• Aggregating into daily data

The image of the scheduled query is as follows:

INSERT INTO
  `${project_id}.zenn_stats.content_pv_stats` ( event_date_jst,
    id,
    user_id,
    content_type,
    content_slug,
    pv)
SELECT
  event_date_jst,
  id,
  user_id,
  content_type,
  content_slug,
  pv
FROM
  tmp_article_pv_count_yesterday;

It can be seen that pv is aggregated daily (i.e., event_date_jst), and user_id and content_slug (URL) are assigned to that data. We accumulate this daily. With this data, it becomes possible to aggregate by user and by day when calling for statistics data.

Is it really not billed?

It makes you anxious, but you can check it in the query execution results. When displaying this PV data...

...a query like the following is executed. It is a query against the daily accumulated data we just aggregated.

SELECT
  event_date_jst,
  SUM(pv) AS sum_pv
FROM
  `content_pv_stats`
WHERE
  user_id = 1
  AND event_date_jst BETWEEN '2023-06-26' AND '2023-07-26'
GROUP BY
  user_id,
  event_date_jst

Let's look at the execution results from the Google Cloud Console.

We can see that BI Engine mode: FULL and Bytes billed: 0 B. We can confirm that it fits in the BI Engine capacity and that query fees are not being charged.

Remaining Issue: Potential slowdown due to slot exhaustion

While the billing issue was solved by BI Engine, there is a potential risk that slots could be exhausted. I understand that if slots are exhausted, there may be waiting times for the statistics page to display, so we will continue monitoring to see when it might be exhausted and how long the wait times would be.

This article on slots was very helpful.

https://qiita.com/kccs_hiromi-nishida/items/00ddcfe1873df8f5ca60#2-2定額料金

How about graphing?

I struggled quite a bit with reading the aggregated data, but drawing it was even harder (mainly due to a lack of technical skills). Since it's off-topic, I won't go into details, but we are drawing it using Chart.js.

• It is daily data.
• How to display the X-axis.
• Skipping labels so they don't overlap.

Since these options can be set, we managed to organize them into a readable format by making full use of them. They organized it for us... thanks to my team members.

Conclusion

I introduced an example of how we implemented the statistics page using BI Engine. Looking at it this way, you can see that while we ran into challenges as we proceeded, we managed to crush them one by one.

Things to consider when using BigQuery

BigQuery is a perfect service as a data warehouse, and I think its usage will accelerate in the future. However, since you are billed per query, as the frequency increases, it might be worth reviewing your queries or considering the use of BI Engine.

Things to consider when using BI Engine

Since you purchase capacity in advance, I thought it was crucial to prepare the necessary and sufficient data for the requirements you want to achieve. Scheduled queries are one way to do this.

It's still not perfect, but I would like to continue monitoring billing and load conditions and strive to improve convenience.