Challenge 8: From insights to action, syncing BigQuery and AlloyDB

Previous Challenge Next Challenge

Target Persona: DBA / Database Developer Estimated Duration: 45 minutes Prerequisites: Challenge 1 and 2 must be completed. Challenge 3 and 5 must be completed (or mock tables used in BigQuery).

Introduction

To serve analytical insights (like wait time forecasts and next-ride recommendations) with sub-millisecond latency and without overloading BigQuery, we will not query BigQuery directly from the agent. Instead, we will use BigQuery Foreign Data Wrapper (FDW) to copy the analytical insights from BigQuery into local tables inside AlloyDB.

This ensures that AlloyDB remains the single, high-performance serving layer for the agent, while BigQuery is used purely for heavy analytical processing.

Description

Task 8.1: Create Local Analytical Tables in AlloyDB

First, you need to create the local tables in AlloyDB that will store the synced analytical data.

  1. Open AlloyDB Studio and connect to your disney database.

  2. Run the following DDL statements to create the target tables:

     CREATE TABLE IF NOT EXISTS public.forecasted_waiting_times (
     attraction_id INT,
     forecasted_timestamp TIMESTAMP,
     predicted_wait_time NUMERIC
 );

 CREATE TABLE IF NOT EXISTS public.graph_recommendations (
     attraction_id INT,
     recommended_next_attraction_id INT,
     recommendation_rank INT
 );

Task 8.2: Map BigQuery Tables using the AlloyDB Studio Wizard

Use the built-in wizard to easily map the BigQuery tables as foreign tables.

  1. In AlloyDB Studio, click on the BigQuery Tables three-dots button in the Data Explorer pane on the left-hand-side.
  2. Follow the wizard to connect to your BigQuery tables.
  3. Map the following tables, naming the foreign tables with a bq_ prefix to distinguish them from your local tables:
    • Map disney.forecasted_waiting_times to a foreign table named bq_forecasted_waiting_times.
    • Map disney.graph_recommendations to a foreign table named bq_graph_recommendations.
  4. Query the foreign tables to verify the connection.

Task 8.3: Sync Data from BigQuery to AlloyDB

Now, copy the data from the foreign tables into your local AlloyDB tables.

  1. In AlloyDB Studio, run the following SQL queries to perform the initial sync:

     -- Sync wait time forecasts
 INSERT INTO public.forecasted_waiting_times (attraction_id, forecasted_timestamp, predicted_wait_time)
 SELECT attraction_id, forecasted_timestamp, predicted_wait_time 
 FROM public.bq_forecasted_waiting_times;

 -- Sync graph recommendations
 INSERT INTO public.graph_recommendations (attraction_id, recommended_next_attraction_id, recommendation_rank)
 SELECT attraction_id, recommended_next_attraction_id, recommendation_rank 
 FROM public.bq_graph_recommendations;

  2. Verify that the local tables public.forecasted_waiting_times and public.graph_recommendations now contain rows.

Success Criteria

To validate this challenge, you must demonstrate the following:

  • Show the DDL used to create the local tables in AlloyDB.
  • Provide a screenshot of AlloyDB Studio showing the local tables populated with synced data from BigQuery.

Previous Challenge Next Challenge