Use Google BigQuery & Gemini AI For Data Analytics

Key Takeaways

by now you've probably had more than one aha or magical moments thanks to a large language model or llm whether it was chatting with Google Gemini on a browser or mobile app or calling the Gemini API with a prompt What If instead of asking Gemini one question at a time you could prompt it to help you analyze your big query data row by row and at scale in this video you'll see how you can use bigquery's integration with vertex Ai and Google's large language model family Gemini to analyze your unstructured semi-structured and structured data to gain new insights and enable novel analytical applications Gemini can help you analyze your data in several ways including text summarization rephrasing of text feature extraction and sentiment analysis it can also help generate new content for data enhancement such as turning a zip code into a city the Gemini family of models also has options that handle multimodal input meaning a combination of text and image or video this unlocks additional use cases such as object recognition captioning or description of images and videos digital content understanding and for information seeking by combining World Knowledge with information extracted from images or videos however applying these use cases to a large data set say thousands of product images or customer reviews would be a complex task requiring code and infrastructure management luckily bigquery's integration with the Gemini models through vertex AI solves these challenges allowing you to interact with Gemini using SQL and taking advantage of the scale of big query getting started with Gemini follows two highl steps first you'll create a model in big query ml that references the model endpoint once the model is created you can call the model On Demand with a SQL select statement using the ml. generate text function this example shows creating a model that references Gemini Pro which handles text input and generates text as a response if you're planning to use a multimodal model that handles both Visual and text input such as Gemini Pro Vision you'll first make your visual data such as videos or images available as a big query object table object tables are readon tables that reference unstructured data objects residing and cloud storage once created each row of the object table corresponds to an object and the table columns correspond to the object metadata generated by cloud storage including any custom metadata creating the multimodal model is similar to the text model as you only need to update the options to reference the multimodal endpoint calling the model with ml. generate text does have slightly different requirements for multimodal models the text prompt moves out of the select statement and into the struct itself and the same prompt is applied to each row of the referenced object table okay let's go to a demo to see this in action in this demo I'll be analyzing a set of movie poster images to understand more about the movies they represent the multimodal Gemini Pro Vision model will accept the image as an input and will be prompted to identify the movie represented by the poster and its year of release then the Gemini pro model will take that movie title in year as text input and will be prompted to provide a text summary I'm working inside of an embedded notebook directly in bigquery Studio which is an environment where we can combine python SQL and even command line tools like g-cloud and BQ to perform analysis there are a few setup steps for working with vertex AI models in bigquery these include enabling the vertex Ai and bigquery connection apis creating a cloud resource connection and then granting the vertex AI user role to the service account used by the cloud resource connection next I'll add our movie poster images to an object table first first I'm creating a data set to hold the new table and then I'm using the create external table statement to create a table linked to the Google Cloud Storage bucket that houses the images now I'll create the Gemini remote models in bigquery ml firstly I'm creating a remote model pointing to the Gemini pro model endpoint which I'll save in my new data set and name Gemini Pro I'll do the same thing with a second model this time pointing to the Gemini provision model inpoint and I'll name it Gemini provision now if I go to the Explorer pane in the big query console and refresh the contents you can see I now have the new data set with the movie posters object table as well as the Gemini Pro and Gemini Prov vision remote models let's use them to do some analysis the first thing I'll do is use the Gemini provision model to help understand the images I can send requests to the model by using the ml. generate text function referencing the model in the parameters the function syntax when used with the vision model expects an object table in the table parameter I'll provide a text prompt within the function struct in this case I'm asking the model to determine the movie represented in the poster and to provide the movie title in year of release specifying that that response must be returned in Json format once the execution is complete I can take a look at a few sample responses and their structure I'll use sequal to pull out the movie title and release year from these responses into new columns what if I wanted a bit more information about each of these movies say a tech summary of the movie This content generation use case is perfect for the Gemini pro model again I'm using the ml. generate text function this time referencing the Gemini Pro remote model the table parameter will be a select statement that pulls the movie title and year into a prompt and ask the Gemini model to provide a movie summary note that the prompt for Gemini Pro can be unique to each row and incorporate data from The Columns of the table as I'm doing here with the title in columns it also requires the prompt to be provided in its own column named prompt and contained within the table parameter this is slightly different syntax from the previous query to the Gemini provision model where the prompt was provided in the struct in the results I now have the image URI title year and movie summary provided by the Gemini pro model this data could be saved to a table or used in further Downstream analysis say joining it with other data such as movie reviews for sentiment analysis Gemini's ability to turn unstructured data into a structured format enables you to draw novel insights and join that data with your structured data for even more possibilities accessing Gemini via bigquery ml means you can do this at scale without complex code needing ml expertise or ever leaving your data warehouse try it for yourself check out the documentation and more resources Linked In the description below