基于Firebase Analytics与BigQuery实现自动更新图表及React集成

Hey there! Let's break down your two main requirements—automating daily chart updates and integrating BigQuery data into your React frontend—plus optimize that BigQuery query for better performance.

1. 实现每日自动更新的图表 #

To get your charts refreshing automatically every day, combine BigQuery scheduled queries with a visualization tool like Looker Studio (formerly Data Studio):

• Step 1: Schedule your BigQuery query
  • Open the BigQuery console, paste your query (or the optimized version below), then click Schedule query > Create new scheduled query.
  • Set the schedule to run daily (pick a time after Firebase’s event data has fully synced to BigQuery—usually a few hours post-midnight works best).
  • Configure the destination to save results to a dedicated table (e.g., your-project.your-dataset.daily_order_confirmations). This gives you a fresh, pre-aggregated dataset each day.
• Step 2: Connect to Looker Studio for auto-updating charts
  • In Looker Studio, create a new data source linked to your BigQuery destination table.
  • Under data source settings, set the refresh frequency to "Daily" (align it with your scheduled query time).
  • Build your custom charts in Looker Studio—they’ll automatically pull the latest data each day without manual work.

2. Display BigQuery charts in your React frontend #

You’ve got three solid options, depending on your security needs and customization goals:

Option 1: Embed Looker Studio charts (simplest) #

If you already built charts in Looker Studio, embedding them in React is straightforward:

• In Looker Studio, go to Share > Embed report.
• Copy the iframe code, then render it in your React component:

  import React from 'react';
  
  const OrderChartEmbed = () => {
    const embedCode = '<iframe width="600" height="400" src="YOUR_LOOKER_STUDIO_EMBED_URL" frameborder="0"></iframe>';
    return <div dangerouslySetInnerHTML={{ __html: embedCode }} />;
  };
  
  export default OrderChartEmbed;
  

  The embedded chart will automatically reflect the daily updates from your scheduled BigQuery query.

Option 2: Fetch BigQuery data directly with React (full customization) #

For full control over chart rendering, use the BigQuery client library in your app (or call the REST API directly):

• First, handle authentication:
  • For internal tools, use a service account key (store it securely—never expose it in client-side code! Use environment variables or a backend proxy if needed).
  • For public apps, implement Google OAuth so users authenticate with accounts that have BigQuery access.
• Install the BigQuery client library:

  npm install @google-cloud/bigquery
  

• Sample React component to fetch and render data:

  import React, { useState, useEffect } from 'react';
  import { BigQuery } from '@google-cloud/bigquery';
  import { BarChart, Bar, XAxis, YAxis, Tooltip } from 'recharts';
  
  const DailyOrderChart = () => {
    const [chartData, setChartData] = useState([]);
  
    useEffect(() => {
      const fetchOrderData = async () => {
        const bigquery = new BigQuery({
          projectId: 'YOUR_PROJECT_ID',
          credentials: JSON.parse(process.env.REACT_APP_BIGQUERY_KEY)
        });
  
        // Query your pre-populated destination table
        const query = `SELECT date, COUNT(*) AS totalOrders FROM \`your-project.your-dataset.daily_order_confirmations\` GROUP BY date ORDER BY date`;
        const [rows] = await bigquery.query({ query });
  
        // Format data for the chart
        const formattedData = rows.map(row => ({
          date: row.date,
          totalOrders: row.totalOrders
        }));
        setChartData(formattedData);
      };
  
      fetchOrderData();
    }, []);
  
    return (
      <BarChart width={600} height={400} data={chartData}>
        <XAxis dataKey="date" />
        <YAxis />
        <Tooltip />
        <Bar dataKey="totalOrders" fill="#8884d8" />
      </BarChart>
    );
  };
  
  export default DailyOrderChart;
  

  Note: For client-side apps, using a backend proxy (like Cloud Functions) to handle BigQuery requests is safer—this keeps your credentials hidden from end users.

Option 3: Use Cloud Functions as a middle layer (most secure) #

To avoid exposing BigQuery credentials in your React app:

• Write a Google Cloud Function that runs your query and returns formatted JSON data.
• Call this function’s endpoint from React using fetch or Axios.
• Example Cloud Function (Node.js):

  const { BigQuery } = require('@google-cloud/bigquery');
  const bigquery = new BigQuery();
  
  exports.getDailyOrderData = async (req, res) => {
    const query = `SELECT date, COUNT(*) AS totalOrders FROM \`your-project.your-dataset.daily_order_confirmations\` GROUP BY date ORDER BY date`;
    const [rows] = await bigquery.query({ query });
    res.status(200).json(rows);
  };
  

• Then in React:

  useEffect(() => {
    fetch('YOUR_CLOUD_FUNCTION_ENDPOINT')
      .then(res => res.json())
      .then(data => setChartData(data.map(row => ({ date: row.date, totalOrders: row.totalOrders }))));
  }, []);
  

Optimized BigQuery Query #

Your original query works, but using CROSS JOIN UNNEST with MAX(CASE) is more efficient for unnesting repeated fields like user_properties and event_params:

SELECT
  MAX(CASE WHEN up.key = 'restaurantName' THEN up.value.string_value END) AS restaurantName,
  event_name AS event,
  MAX(CASE WHEN up.key = 'restaurantId' THEN up.value.string_value END) AS restaurantId,
  event_date AS date,
  MAX(CASE WHEN ep.key = 'allergens' THEN ep.value.string_value END) AS allergens,
  MAX(CASE WHEN ep.key = 'dishes' THEN ep.value.string_value END) AS dishes,
  MAX(CASE WHEN ep.key = 'vegan' THEN ep.value.string_value END) AS vegan,
  MAX(CASE WHEN ep.key = 'vegetarian' THEN ep.value.string_value END) AS vegetarian,
  MAX(CASE WHEN ep.key = 'orderTotal' THEN ep.value.string_value END) AS orderTotal,
  app_info.version AS version
FROM `reference`
CROSS JOIN UNNEST(user_properties) up
CROSS JOIN UNNEST(event_params) ep
WHERE
  event_name = "ConfirmOrderBtn"
  AND app_info.id = "abc"
GROUP BY
  event_name, event_date, app_info.version
ORDER BY event_date ASC

This approach avoids slow nested subqueries and is easier to maintain if you add more properties later.

内容的提问来源于stack exchange，提问作者Shermeen