Power data analytics with generative AI using BigQuery and Gemini

Key Takeaways

[Music] welcome to power data analytics with Gemini in B query session in this session we're talking how to use bigquery and do multimodel generative AI on your Enterprise data by using Gemini models in bwy through b ml h ah all right so first let me introduce myself I'm Thomas stus Al Le engineering team for bigquery joining me shortly is Alicia Williams from our developer advocacy team and later we'll have Sheamus appshare uh joining us who is the chief technical officer for faray uh customer Behavior Predictive Analytics platform all right so quick agenda so we'll cover how we think about generative AI in Google Cloud uh we'll talk about geni in data pipelines and why it makes sense Alicia when will cover the details of bigquery and Gemini integration and we'll do a demo in bigquery studio and then finally Sheamus will join us for his story and tips and tricks on what he's already been building so first let me share how we're thinking about geni in Google at Google um as we build data in AI cloud well in this talk we've been we'll be focusing on bigquery we are adding AI capabilities into every single data service at Google starting from databases such as alloy DB or spanner big query and our business intelligent tool looker now what is unique about Google cloud is the Deep vertical integration we do starting from Hardware uh with tpus which are on their fifth generation now our investment in the foundational models and end to-end Solutions and finally we tie it all together for data and AI governance so as we looked uh how to build AI capabilities in big query uh we realize that existing data architectures don't quite the need meet the needs of AI error so there are three key things first AI is driven a lot by unstructured data and today bringing together structured and unstructured data is hard we tend to live in silos second often we end up moving data to Ai and that creates additional complexity security challenges you need to get additional compliance and security approvals makes it really hard to do those projects and then finally you need to learn new tools uh new Frameworks new systems all that makes it more complicated uh so so the end result only 19% of companies excel at using AI on their data so clearly we can do better and with big query we're taking three key steps so first we added unstructured data support to Big query for object tables so now in a single place you can bring together structured and unstructured data and analyze it second we decided that we want to bring AI to your data so that data never moves from your perspective and we manage it for you and you can use be it classic models or gener gener models all in the same place and third we're enabling your existing tools such as bqu Studio SLE interfaces to be able to access gen models so how are we doing this we use bqu ml bqu ml is in the middle between Beery and vertex AI so thinkware is an extension which brings all the power of vertex AI with all the models and generic capabilities into B query bqu ml offers SQL interface so you can complete complex ml tasks using the worldclass models and you could do both traditional predictive ml forecasting anomaly detection but we also invested heavily in using Jive AI so B offers one of the easiest ways for data professionals to get started using AIML so now you have access to all the tools that traditionally have been reserved to Advanced Data scientists or ml Engineers so back at next 23 we announced big query support for generative AI using Palm models and in this spring we released support for Gemini 10 Pro so now this means that you can access those models directly from Big query really using just two SQL statements create model and then select Gemini 10 pro model is designed for high throughput and better better result quality across wide range of tasks the second model is Gemini provision which is a multimodel multimodel model allows you to add images and Video in addition to text prompts and get back test results and we'll cover some of those um in the upcoming slides so now how do we envision customers using gen well one of the most common use cases in bigquery is using data engineering and data pipelines so now every data engineer who's been working on structured data can be utilizing AI to process unstructured data or use geni as you process structure data as well so how do we make it easier first you don't do any additional data movement it happens all way in theery we do processing at scale using the scale that b query is known for you have broad access access to all the model capabilities and then finally because data does not move the governance is streamlined security is streamlined there is less risk and less management so all in all we believe that this way we can open additional scenarios for every single data engineer so let's look at some of the use cases for tax models such as Gemini 10 Pro uh text use cases so content generation summarizing enhancing rephrasing entity extraction let me show you some examples so first on generating content imagine you can take structure data you already have and generate marketing material for products on your catalog or be it Hotel descriptions imagine you already have some text material you can actually rephrase it to a different customer segment or a demographic and then finally if you have text what of the most common tasks you want to do you want to extract structured data back from it and this is where you can use gen models again right so bridging this unstructured constructure data world as we look at multimodel multiple tasks image classification recognizing objects in images or videos captioning understanding uh the images and relating to well-known World information so for example for object recognition imagine building an application which is understanding damage in Insurance photos or you have large quantities of unstructured data in Google uh cloud storage buckets with object tables and generative AI models now you can catalog it all generate descriptions so we can truly understand the assets that you have and then finally you can take existing images you have and use the information that's encoded in the foundation models about the world and understand them too so with this I'd like to invite Alicia and she'll walk through a details of using Jos in B welcome Alicia thank you thanks all right so Thomas is just at the stage for why generative AI is so important and valuable in your data warehouse and how the integration in bigquery makes a lot of sense and simplifies things for you I'm going to dive into how the integration works and some of the particular implementation details so as Thomas mentioned big quer ml is the interface between big query and models in vertex AI including the Gemini models so the first step is to use the create model statement and bql to point to the endpoint so in this case for the text model Gemini Pro I'm using the endpoint Gemini Pro then you're ready to prompt the model to generate text and this is using the ml. generate text function this function has a particular syntax that expects the model name um you define the model in step one so we're pulling that same llm model in and then basically a table parameter where the table needs to include a column with your prompt and you name it prompt so this could be a table reference that already has a prompt column in it but in this case we're using a select statement and preparing that as you see here and then finally a struct which has some options parameters such as temperature it's not pictured here but if you're familiar with the models you might know what that is and this customizes how Gemini the Gemini model behaves um in response to your prompt so let's see what a response might look like in this example where I'm using the Gemini pro model over a table of some sample news articles so for each row of the table I'm concatenating a prompt that includes the body of the article with a request to extract the key words and I'm copying in just one line here uh I'll run I run the prompt and I can see an example of extracting some of the keywords from this uh article this is just one row but as you'll see in the demo ml. generate text uh Returns the response across the entire row of the table uh the reference table so the model creation part step one is just a onetime process and then you can use that model reference anytime you use ml. generate text so this demonstrated how to run a query ad hoc but why what if you wanted to prompt um Gemini on data as it came into your big query environment so continuous queries is a new big query feature that's launching in public preview this summer and it unlocks continuous analytical processing via SQL the moment that data arrives into big query so this isn't just for llm enrichment this is um any SQL data Transformations you want to do and then you can reverse ETL the results into pubsub big table or a separate big query table so this is going to allow you to use a SQL statement to build an endtoend aid driven event-driven data pipeline to process analyze transform and use AI with your data in real time and if instead of SQL you're using Python and notebooks or any python runtime and you want to utilize this Gemini integration in bqm you can do so with bigquery data frames bigquery data frames is an open source library that allows data developers to use Python within with the big query engine the library does have a few parts uh pandas for data analysis and manipulation as well as an ml library for building ml models and part of that ml Library includes a module where you can prompt Gemini and this is actually built on the bql integration with Gemini and vertex so bigquery data frames did announce its ja launch here uh I think on Monday just before next it's it's integrated really well into big quer big query studio notebooks which I'm going to use in today's demo um and those notebooks are also in G and have scheduling coming soon to preview so in these past few slides we've been focusing on the text only model Gemini Pro but we are announcing here at next the integration of Gemini Pro Vision into big query meaning that you can do the multimodal generative AI analysis in your data warehouse because after all big query isn't just for structured data object t object tables make it possible to access unstructured data in big query like images or video because it provides a record uh structured record interface to data you store in cloud storage so you can perform analysis over these object tables and bigquery using remote functions or perform inference with big query ML and then join the results back with all of the other data in your data warehouse so in case you haven't worked with object tables directly I wanted to show a quick example say you have a data set of images these are of homes and you have them stored in a Google Cloud Storage bucket you can create an external table um an object table using the create external table statement uh and in your options you point to that cloud storage bucket and then in big query you have a new table where each row is uh corresponds to an object in that object table and the columns correspond to metad data that is created by cloud storage again this table is providing just a structured record interface no data copying is happening is happening in the process but once you have that in Cloud store or in an object table we can analyze that visual data in big query with Gemini provision so there's it's very similar to Gemini Pro which is you create a model using the create model statement this time I'll give it a little bit of a different uh name and I'll point to Gemini provision when it's time to prompt the model again you use ml. generate text now referencing your multimodal model but the ml. generate text function syntax is a bit different when you're using it with a multimodal model so the table is going to be the object table uh and then the text portion of the prompt that you want to concatenate with your image is actually going within the function struct so I wanted to show that here on an example that I used with a public data set we have from the Met Museum and ask Gemini Pro to provide a caption for the image so here's just a couple of simple responses with the image and it's not showing the full result set but I'm going to do that in action now on my laptop so let's switch over all right so I'm in my big query Studio notebook if you haven't used a notebook before it's a really awesome place for demos you can use uh g-cloud commands BQ commands Python and SQL all within this notebook really easily in this case I'm going to analyze a set of uh posters that I know to be for movies uh here's just a few sample images from my data set but I have the images in a Google Cloud Storage Bucket over here so um so the first step to access models from Big query are to enable the vertex Ai and bigquery connection apis and then to create a cloud resource connection I'm going to start running a couple of these the cloud resource connection basically gives big query the ability to talk to both um the cloud storage bucket as well as the vertex AI models so then I'll Grant the service account used by that cloud resource connection the rules it needs to do that which are vertex AI user to call the models and then storage object viewer to my uh storage my storage bucket now that I have the setup complet I want to create my object table pointing to my uh cloud storage uh bucket so I'll create a new data set to hold my object table and then I'll use this uh create external table and point to my cloud storage bucket here so I'll run that and then that will be created so now we'll create those two remote models step one for each of the Gemini Pro and Gemini Pro Vision so this is the same code I showed before I'm pointing to Gemini Pro and Gemini Pro Vision as endpoints and giving them um model names so I'll run those oops and then I've just created an object table and two models so I'm going to go over to my Explorer and show you if I refresh the contents that I now have my new data set and you can see I now have the models available and the movie posters object table so now I'm ready to perform analysis on that table using both of these models so the first thing I want to do is use Gemini Pro Vision on the raw images to understand a little bit more about those movie posters so I'm going to ask uh Gemini Pro Vision to pull out I pulled the prompt out here to make it more readable but to decide what the movie title and year of releases for the movie represented by the poster and to provide that back in Json format so I can have it in a structured format so you can see here I provided the prompt within the struct I pointed to my provision model and I have my object table when that it's going out and it's running all of the prompts across all the rows of my table and I'll return the results and you'll see I have some results back um and you can see the that the response is in a format that I can now parse with some SQL and add it to New columns in in a new table so I now have for each of my movie posters I have a title of the movie for that poster and the uh year of release now if I wanted to have a little bit more information maybe I want a movie summary to understand what the movies were about this is a Content generation uh use case that like Thomas talked about earlier and I can do that with Gemini Pro so I created another um I used ml. generate text over my Gemini pro model and I have the request to provide a short summary of the movie titled and then I pull in the title um from from that column and from the year I provide the year column so this is combining the data in my table with a prompt um and I can do that right here in the table parameter and then I'm going to return the results so let me run that and we can see what Gemini Pro comes up with so you can see I have the original data that I've pulled together using Gemini Pro Vision and then the result from Gemini Pro so this is pretty awesome because I've started with a collection of visuals that I knew just a little bit about I knew they were movie posters and I've been able to pull in uh structure data that I can use down the line and also um some additional content generation that gives me even more context about those movies and those posters but I actually happen to have some data already in my data warehouse that would be I think would be very cool to join with this data so I have a data set from IMDb that has all of the all of television and movies that includes data including titles ratings um that were on their platform and also unstructured reviews so I want to join that data back to my movie posters but as you know you don't quite quite have a join key um I have the title of the movie poster and that might not 100% exact match word for word or character for character so what I decided to use was embeddings so embeddings are and you can actually create embeddings directly in big query so I thought it would be a great example for uh another kind of sneak peek at embeddings so I actually my teammate Jeff provided an entire uh session on embeddings earlier today if you were there or if you weren't I highly suggest watching it but a quick intro to these Concepts the way machine learning models arrange data um is within a simple data structure know as known as embeddings so once the um model is trained with spe specific content like text or video um the models create a space called an embedding space and they essentially map each of those pieces of content to a specific Place based on the content's meaning and they put things that are similar closer together then once you have that um embedding space created you can use Vector search to find the distance between all the different um points in the embedding space and find things that are closest together in similarity so what I decided to do was create embeddings for my movie poster um titles and years and do the same thing across the IMDb data set to find the closest matches so creating embeddings within big query um is actually very similar to what we did um the same process with calling the models for Gemini Pro and Gemini Pro Vision we're going to create the um the model but this time we're going to use the text embedding gecko model which is our text embedding model and once I create that model I can then use it to generate text embed ings for my my movie posters what I've decided to do here is create embeddings that are text Based On A concatenated String where I um give the context of a moveie movie title and year and then when I have those results you can see a little bit about what these embeddings look like I'm not going to expand it because there are many many multi-dimensional vectors but I now for each of my movie posters have a a spot on this embedding space I've done the same thing um for my IMDb data set that's pretty large so it took a lot longer so I'm not going to make you uh watch that query but I already have my embeddings for the IMDb uh movies so now what I'm going to do is I'm going to use Vector search to find the closest match the way the vector search works is I have the second table here is my movie poster embeddings this is the table I want to find a match for each row and then the table I'm going to my base table my IMDb is basically what I'm going to look up to find the closest match and here I can return just the closest distance neighbor so I'll run that query and you'll see I'm returning information that I have from my movie posters the URI the title in the year that we got before and then I've matched it with the IMDb title year and movie ID which is the key that data set uses based on the distance in the embedding space so we used coine distance here coine distance is going to return a distance from 0 to two zero being they're on the same exact point in space and two being they're in exact opposite points so as you can see here this worked pretty well in this use case all of my distances are quite small and there's some pretty fun matches like um the French version of a title to an English version for example that might not have worked using other matching uh join functionalities so I'm really excited about the fact that I was able to join this because there's a bunch more bunch more data I can get on these um movies so one of the tables we have is on the the ratings so I can now join that table back to my movie posters using the IMDb ID and I now for my poster ID have a title a year an average rating the number of votes and if I went further I could take the sentiment uh or I could take sentiment analysis over those reviews that were created um or that were provided in our reviews table um and down the line with my data warehouse all right so in this process we took unstructured data with very little knowledge and turned it into structured data joined it with more data in our warehouse and um used it all within SQL so back to the slides I want to go into a few um a few points to talk through when you're implementing this in your data warehouse so as you develop AI pipelines using G Gemini models and bigquery there's a few best practices we'd like to share with you in that process so we recommend using your favorite data transformation tool starting with data form which is deeply integrated with big bigquery Studio you could use cloud composer or DBT from our partners at DBT Labs one thing to call out is that the bigquery llm inference is currently optimized for batch scenarios and it runs below interactive priority so if the ml. generate text job that you're running is competing with other interactive workloads that you have running with Gemini Services then Google Cloud will prioritize those Interactive workloads and then one more thing is that the services have additional protections for Content safety checks um using the responsible AI techniques so not every call the Gemini will re return results and you may also receive failure if those competing interactive workloads have fully consumed your quota so considering these things how can you build a pipeline that's resilient and also make sure that you use your generative AI resources most efficiently well we're making that simpler by developing and sharing a data form package which will handle the additional resil resiliency for you and make sure that capacity is used in an optimal way we alternatively have a reference SQL scripts um that achieve the same end and those are linked here at these at these Google short links so this was all about using the pre-trained foundational model so far but what about customizing the models to use your data beyond what you provide in the context of the prompt well we're also happy to announce that we now support model fine-tuning via the Laura technique directly from bigquery so this is currently supported for the pal to models and is coming soon to the Gemini models whether you're using the foundation models or a model that's been fine tuned with your own data it's key to continually evaluate and measure the quality of the model for your task so big query supports model evaluation over pre-trained or fine-tuned models with a set of standard NLP metrics the way it works is you build an evaluation data set containing inputs and ground truth and then you run the eval over that data set to measure model accuracy building these data sets can be tricky but if your application serves through vertex AI you can easily capture the user prompts and responses and bigquery and then use that to build an eval set um as a representative sample through those logs all right so that's the portion the technical portion Faraday who's a leading customer prediction platform has been using generative AI in bigquery in several ways so far simplifying sentiment analysis customizing content generation and for building out more accurate machine learning models I'd like to invite Sheamus um the CTO and co-founder Faraday to share more about their Journey with the llm integration and big query over to you thank you wow so that was an amazing demo we went through uh a bunch of images in a bucket to something that would have taken just think about how many different vendors it would have taken to Cobble that together even two years ago uh so that was that was fantastic so I'm just going to jump straight to the punchline um and say that we are using both the predictive ml features in bigquery ml with the Gen stuff that we're talking about here to make better lead scoring models so if we just use predictive ML on Faraday's demographic data set we get about a 50% lead scoring lift in this particular case which was selling Financial products but if we pulled in Gemini Pro sentiment analysis which is the same sorts of SQL queries that you just saw uh on screen uh we get 70% Le scoring leftt and that's pretty significant if what you're working with is a lot of data and and you want to find that extra marginal dollar uh marginal lift and so in this case and I'll go through it a little bit in the presentation we asked it to provide structured data about sentiment from unstructured call logs so this was a great result for us to see so quick what is Faraday so we're a unified data and AI platform for customer Behavior predictions and we're all about customer experience because we think that that will be increasingly predictive we also power uh software features that white label us into their products and one of our big advantages is that we bring a lot of uh customer data without needing to reveal it to you and and have you take custody of of this sort of thing so what's our big query story what do we need when we're choosing a software vendor we're a startup so we can't be dealing with a whole uh bunch of different pieces Without Really distracting our teams so having a single vendor in this case Google was very valuable we also needed performance um we've got a large database 1,500 columns 270 million rows um quarterly data going back years and also our clients bring billions more transactions in the form of TR of event streams all that stuff we also need Competitive Edge if we're going to commit to a particular vendor and build our stuff on it we need to know that they're going to stay in The Cutting Edge and so in this case what we're looking at when geni came out which was really an iPhone moment uh where it suddenly the industry changed is we needed the ability to combine predictive and generative so like I was like I was saying about the single vendor we use all of these features you know and we can get it all in one place and the way I like to think about it is that Google cloud is is giving a home to Enterprise AI right if you were to go with another vendor you have to consider how many of these features the vendor gives you or you're going to have to Cobble something together another thing is performance one of the first things that Thomas said is that we have traditionally been bringing data to AI instead of bringing AI to data and running AI wherever the data already lives B query handles this automatically and you have full visibility and all of the tools that you're used to in terms of data governance SQL uh all that stuff I one of my favorite things about big query is that it actually changed the way that our teams work and so previously we were split into developers data Engineers data scientists and by doing our model building and evaluation and even our workflows in SQL we were able to allow our data scientists to write production ready code and so this means that when you want to make a prediction about the entire United States using a model they can just write that in SQL and then that code can go straight into production the final thing I'll mention about Competitive Edge is all of this fancy technology in big query comes down to tables and in the same way that SQL is the linga franka of data science and really data engineering being able to think about all of this stuff in terms of just rows and columns it lowers the barrier to entry a lot so when we mve to Big query thank you Google Cloud we were running everything here we significantly increased our throughput which meant that we could serve more clients and update their scores more often we got less errors we used to have to write a special uh error correcting logic to deal with the fact that sometimes even if you have a 99% reliability in terms of getting your data to the AI system that means you're going to have failures every day all the time it's just not good enough and finally since we are able to take advantage of more training data better algorithms we actually saw the uh conversion lift better models themselves this is the same slide that I showed before um and I I was pointing out these two bonds and annuity sentiment that got mixed into the features that the XG boost model used that we got from Gemini Pro and I'll just give you some quick tips about what we have discovered about building on big query ml with Gen and so my first tip goes back to something that was mentioned towards the end of the demo which is to watch out for partial results because they can be very frustrating and it's simple but it looks like it worked but maybe you only got back results for 50% of the rows and the key point is that a a certain column is actually empty if it worked and so you should check to make sure before you really get too far down the uh the road of of figuring out what you can do with the Gen stuff whether you're actually getting the results that that you're expecting tip number two and this is how you fix that problem is you have to right siiz your quota do this before you uh really do much other development because it can cause uh weeks of confusion um if you don't do it so you start with a default of 100 and um there's different quotas for Generate edding and generate text and just be sure that you pay attention to this in our case it was sufficient to raise it to 6,000 so just a very practical tip my final tip is how to get structured input and Alicia went into this uh in her query she said return turn the results as Jason and if you're using gen in batch mode like this you probably want structured output and this is actually one of the things that I'm in my job most excited about with Gen it's not so much the agents or the chatbots it's the ability to extract data in mass in bulk in batch and so right now your option is to ask Gemini to give you Jason back in the future and this is not official Google uh talk but it was suggested to me by a engineering manager Google who shall go unnamed uh we might be able to use Lang chain form matters something like that so today this is the real query that we used to generate those 20 percentage points of lift off of a standard XG boost model there's a few things that I'm doing here the first thing is I lessen the randomness in the result results right by turning temperature to zero so I think this is a pretty standard practice for batch processing uh with geni second of all I'm explicitly asking for Jason back and in this case it's kind of a tortured way that I'm doing it I found that sometimes just telling it to produce Jason it wouldn't take me seriously which is a strange aspect of developing with Gen that you kind of have to talk to the computer and finally uh even when you turn the temperature down and ask very nicely for Gemini to return Jason sometimes it still won't and so in big query SQL there are a class of functions called safe Dot and they basically will attempt to run a particular function and just return null if it doesn't work um so this is how we literally extracted the sentiment about bonds annuities and CDs from from a list of call notes uh and got those percentage Point lifts I'll point out here and this goes back to that amazing demo we just saw if I had had an S3 bucket or GCS bucket full of call logs like just um the audio from them I think that we will be able to process those in the same way as if they're text which is just amazing and it really helps with integration with some of our software vendors maybe they're a call center CRM and they're just going to give us the recording instead of a transcript or something like that so incredible power being revealed through SQL here and I'll I'll end on uh the potential future for getting structured output hopefully it's not very far in the future there are existing techniques one of them is output parsers and so a SQL or I'm sorry sorry a python example of how to define a data structure is just saying well a joke has a setup and a punchline and maybe in big query you could have instead of generate text generate table where you're providing a structure that you want the big query team to take care of parsing instead of you having to parse it out um which is what you often have to do in in many cases now so if you have any thoughts about how you want to use structured data results from gen please join me in convincing the big query product team to work on that and at this point I'll turn it back over to [Applause] Thomas all right thank you Sheamus exciting to see what you built and what you're going to build in the future together all right so I wanted to cover a few more things um and just to show you the how broad the set of Integrations we have between bigquery and verx to cover the entire spectrum of unstructured data so we have a quite a few capabilities uh using gen models directly but also using targeted apis available in vertex AI right and of course you can combine those capabilities in a creative ways processing images processing text processing speech so let me me go quickly through some of them so we've already covered U generate text be it for text models or multimodel for images and video so ml generate text is the key function for that uh the next set of capabilities is the direct apis for vision uh and image processing so ml. annotate image is something that you might want to take a look at uh we also recently added a preview of speech to text so you could imagine doing transcription get bunch of text out and then doing processing of a text using generative AI document processing directly using our Integrations with vertex document Ai and other capability directly in the query and finally natural language processing sentiment analysis and understanding text translation again you know you can combine those capabilities with generative Ai and then finally the thing that um Alicia mentioned was using embeddings and using Vector search in big query right so the embedding generation capabilities using the models of your choice again another capability in bigquery and vertex AI right so you can imagine processing images video speech generating text processing the text extracting structur data as Sheamus showed using Json uh based prompts and then generating embeding as well and searching and doing similarity and semantic search at scale right all through big so few couple helpful T tips for you to get started so ml. generate text is the key function that we want you to remember and you can also check out uh the demo notebook that Alicia has been demoing um by using the link below all right perfect so we excited to to see what you're going to go build with big query with bigquery ML and generative AI uh couple sessions for you to check out now I think we're at the end of a conference so you can find recordings online but first multimodel embeddings is probably one of the key capabilities that you might might want to explore uh using Python and big frames with Elicia demo so not only SQL but also python based interfaces to access gen and B query and then finally streaming pipelines and processing using gen as data moves so with that thank you for joining us today we'll have out in the back to answer questions on big quy ml or on big quy in general happy to answer thank [Music] you