How to build a data agent with BigQuery and CloudSQL

Key Takeaways

Welcome back to the Agentverse. We're a crew of developers, data engineers, platform engineers, and architects. Each completing our own missions to defeat our enemies like procrastination and ambivalence. But ultimately, we must all work together to defeat the final boss, aka build and deploy a powerful, secure, and intelligent application. Check out the first video for developers linked in the description below to start from the beginning of the journey. The mission today is for the data engineers, the scholar. We need to take some chaotic unstructured text and battle scrolls and transform it into a powerful structured knowledge engine. Then we can build and deploy a wise rag agent that can provide nuanced contextaware answers to ultimately help us defeat our enemies. Picture this. Over centuries, battle reports have been recorded to document the ways that adventurers past have been victorious against monsters. These reports are written out in log files that live in a Google Cloud Storage bucket. First, we'll use BQML and Gemini to turn those unstructured reports into structured tables for strategic analysis. After that, we'll build our first rag pipeline by taking that same unstructured text, chunking it, and turning it into vector embeddings. This will unlock a deeper semantic meaning that we can then query in BigQuery. Then we'll switch gears a little bit. BigQuery is awesome, but when we're in battle, we're going to need something a little faster to defeat our enemies. So, we're going to switch to an operational database to create a spell book that we can reference during battle. Then, we'll build out a pipeline so that the latest and greatest information is at our fingertips. Finally, we'll bring all our work together to craft a master rag agent, leveraging all the tools we put together to generate that final grounded answer. I'm not going to show every step, but the lab with all of the directions is linked in the description. Are you ready? Let's go. First up, making some sense of these unstructured battle reports. The easiest way for us to do this is to leave those reports right where they are in Google Cloud Storage. Huh? Fret not. We'll set up a magic lens in the form of an external table that lets us peer from BigQuery into the storage bucket like a giant telescope. This is an in database AI powered form of ELT. Extract, load, and transform. The external table is the extract and load. It'll apply a schema on red allowing BigQuery to directly query raw text files in cloud storage. Now we can see our battle reports in a new raw intel content table. At this point, we'll have made a connection between BigQuery and other services on our project and a data set called BC data where all of our new tables and models will live. Then we'll create a BQML remote model that we can use with the MLG generate text function. This function works by calling our Gemini model directly from a SQL query and transforming our data into a JSON object. Then it's put into a new structured beastiary table with information about monsters, battles, and adventurers. And all of this happens in a single query without writing or managing a separate processing pipeline. There's our JSON. Chef's kiss. Now, JSON is Jbomb, but let's be real. We need to parse, clean, and normalize this puppy. So, this script will read the raw nested JSON from our staging table, cleanse and parse it to get to the core data, and then scribe the relevant pieces into the final table for monsters. We'll do the same for adventurers and battles. And the tables will look a little something like this. Now we have our three tables looking all fine. But if we want to do some real analysis and get strategic insights to use the true power of structured data tables, we're going to need to join them. Exactly. You're smart. Let's go ahead and do that. To create a champion's greatest feat table, now I want to know what's the name of the most powerful monster that each adventurer defeated, and how long did it take them? This is a complex question that covers information across our three tables. I'll run this in the BigQuery editor to get my final table. And there we go. We have the information for the biggest foe that each adventurer defeated and the time it took. Looks like perfectionism is no match for Ara. And now we completed the first part of our mission. turning unstructured battle reports into structured, useful data that you can get insights from. Next up, we want to get some deeper semantic meaning from our data. Structured tables are great, but if I ask a question about a specific monster or its attack, I could get back an entire battle report that might not be exactly what I need. Imagine searching a database for expenses and not getting back a relevant article that mentions expenditure reports. So let's build a rag data preparation pipeline to do some more precise vector search in BigQuery. Rag consists of a few different parts. First, chunking. In order to make it easier to query our data, we need to chunk it into smaller pieces that make sense. If they don't make sense, then it won't be helpful. And this is why there are a few different types of chunking strategies. We'll use the split function to do chunking at every period and then save those sentences into a new chunked Intel table. You can get much fancier with recursive or contentaware chunking, but this is good for what we need. Next, we take each of those chunks and turn them into vector embeddings. Why? Because embeddings are a mathematical representation of our data stored in a vector format. They help us translate the semantic meaning of our data into a format that the computer can understand and compare. Computers are great at math but not so great at human language nuances. So the model does the part of understanding the meaning of the text itself and then encoding it into the computer's language to enable vector search. We'll once again call upon BQML and our embedding model to help us generate these embeddings. This time using the MLG generate embedding function. This function will read every row from our chunked intel table, send the text to the model, and store the resulting vector embeddings in a new embedded intel table. This new table within BigQuery is considered our knowledge base that we can use to do our vector search. And later on, we'll switch to a different vector database, and we'll discuss why. So now, we can start asking questions to do semantic search. something like what are the tactics against a foe that causes paralysis? But before we can get an output, Gemini will turn this prompt into an embedding as it comes in using the same BQML function as before. So now we have our text chunks from our embedded intel table as embeddings and our prompt as an embedding. With these numerical coordinates, BigQuery can perform a vector search and measure the distance between these two embeddings, find the ones that are the closest, giving us our semantic search results as the output. A quick note from our sponsor, this distance that I speak of. There are two main types of distances that are used in vector search, cosine and uklitian. We're using cosine distance, and all you need to know is that it's fantastic for semantic search. It finds texts with a similar meaning, even if the words are different. The smaller the distance or the angle between the vectors, the more aligned their meanings are. Back to our output. As you can see, this is so much more powerful than a basic keyword search. A keyword search would only return scrolls that contain the exact words, tactic, or paralysis. But the top result for our semantic search demonstrates the understanding of the deeper meaning of tactics against paralysis with words that are close like shattered and paralyzing. Moving on, why are we switching databases? BigQuery is an amazing analytics data warehouse. It's designed to answer big complex questions by scanning massive amounts of data like a research library. But when our agent is in the heat of battle, we need a spellbook that it can reference quickly, not an entire library. Something designed to serve live applications by handling many small, fast, and frequent requests. Enter cloud SQL for Postgress and the PG vector extension. Here we have our Arcane Wisdom database and in the query editor of SQL Studio, I can run this query to add the PG vector and Google ML integration extensions to my database. The PG vector extension adds the vector data type to the database and provides the function cosign distance needed to perform similarity search. Without it, Cloud SQL wouldn't know how to store or search the embeddings. The Google ML integration extension provides the embedding function, a powerful feature that we'll use in a little bit that allows us to call the Vertex AI embedding model directly from our SQL query. Once that's done, we can run this command in the editor. It'll create an ancient scrolls table to store both the original battle reports text and their corresponding vector embeddings, making it a perfect spellbook for our rag agent. Quick tip. Before building a complex pipeline, run a manual test. This makes debugging way easier by letting us test our connection and logic in isolation. If everything is working, you'll get data in your database. And it'll look something like this. Our final step before the pipeline is the addition of an index. The a hierarchical navigable small world hnssw index to be exact. This will pre-organize the data and build a multi-layered graph of the vectors starting generally at the top and getting progressively more detailed in its search to pinpoint the exact neighbors at an exceptional speed. Without an index, our search has to do a sequential scan. which is slow. Watch the execution time here. Now we'll create an HNSW index. Let's run the exact same search again. Look at the execution time now. Boom. That's the power of a vector index. All this talk of a pipeline and the time is finally here. We're going to build out our dataf flow pipeline to turn that simple manual process that we just did into a massively scalable, automated, and robust assembly line of scribes. This will process all our existing scrolls and any new ones we add later in a large batch job whenever we run the pipeline. Imagine if we had millions of scrolls. This would be very helpful. We'll build this pipeline using dataf flow, Google cloud's managed service for Apache Beam. This lets us turn our manual script into an automated job. We'll assemble our pipeline right in our code. Now, there are two main components that we'll be using. Embed text batch, which takes a batch of raw text files, calls the Gemini text embedding model in Vert.ex AI, and generates a vector embedding for each one. and write essence to spellillbook, a custom function that connects to our cloud SQL database and inserts each scrolls text and its new vector embedding into the ancient scrolls table. Here's how the pipeline flows. We'll read the files, send them in batches to an embed text batch function to get embeddings and then pass the results to a write essence to spellbook function to save them to our database. Now we can trigger the scheduled jobs for our batch pipeline with a terminal command and check the progress right in dataf flow. Once all the jobs are finished running, we can run a select query in Cloud SQL to see that our scrolls have all been pulled in and have their embeddings. Finally, we have a spellbook that is a true knowledge engine and it's ready to be queried for meaning. How will it be queried, you ask? Ah, this is where our rag agent officially comes in. It's built to perform that full retrieve augment generate loop that we discussed. Let's take a peek at the code. It's a pretty traditional agent where the model, instructions, and tools are all defined. And the first step in the instructions is consult the scrolls with the grimoire lookup tool. The tool is the retrieve part where Cloud SQL is consulted for information about a specific monster. The function takes the monster's name from our questions and converts it into an embedding. It then searches the ancient scrolls table in cloud SQL for the most similar embeddings and returns the top three scrolls which are used to augment the initial question and then generate the final answer. Our agent is ready. Now we just need to fire it up and test it out. If we ask it about the hydra of scope creep, it'll follow this entire process and return us useful contextaware information on how to defeat it. Turns out to avoid scope creep, we need to stop saying yes to more undefined work. I feel personally attacked. Finally, you can deploy your agent to Cloud Run to show that you've completed the mission and then test out your own knowledge of building this rag agent with the boss fight. Remember to clean up your resources to avoid any unwanted costs. As a data engineer, our primary weapon is knowledge. We transform raw chaotic data into actionable intelligence. Much like an alchemist transforms raw chaotic lead into structured, valuable gold. Along the way, we've made crucial architectural decisions to craft our knowledge engine and provide our final rack agent with the power to be victorious. Go to the description to check out the lab link for yourself and find a lot more resources like a detailed blog post and a more detailed video with step-by-step instructions. I encourage you to explore the other paths and see how your skills as a data engineer connect with the developer, the platform engineer, and the architect. Together, we are unstoppable. Scholar out. Wow.