<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Patrick Haggerty on Medium]]></title>
        <description><![CDATA[Stories by Patrick Haggerty on Medium]]></description>
        <link>https://medium.com/@pathaggerty?source=rss-cc46f3d1bc14------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*m5FfHbx_m0oXvAT_zQcc1Q.jpeg</url>
            <title>Stories by Patrick Haggerty on Medium</title>
            <link>https://medium.com/@pathaggerty?source=rss-cc46f3d1bc14------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 08:06:49 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@pathaggerty/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Gemini Prompting in BigQuery SQL]]></title>
            <link>https://medium.com/@pathaggerty/gemini-prompting-in-bigquery-sql-46c33ba7cf3e?source=rss-cc46f3d1bc14------2</link>
            <guid isPermaLink="false">https://medium.com/p/46c33ba7cf3e</guid>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[gemini]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[generative-ai-tools]]></category>
            <dc:creator><![CDATA[Patrick Haggerty]]></dc:creator>
            <pubDate>Tue, 18 Jun 2024 20:20:42 GMT</pubDate>
            <atom:updated>2024-06-18T20:20:42.542Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TXX7GHGartyt30j31FWUBw.png" /></figure><p>I spent some time recently writing a lab for an upcoming Google marketing series. Google wanted a workshop exploring Gemini’s use in the developer workflow. Essentially, Gemini Cloud Assist to help do things in Google Cloud, Gemini in BigQuery data canvas to help convert natural language into SQL, and Gemini Code Assist to help create and debug code in the code editor. With a bit of help from my buddy @Kevin Rattan, another instructor here at @ROI Training, we came up with the idea of a cooking site, with an AI cooking assistant to offer advice and an AI recipe search page for the recipes.</p><p>Along the way, the students build two grounded data stores in Vertex AI Agent Builder: one on a bunch of public domain cookbooks I found on the Project Gutenberg site, and the other on a database of recipe data. During the event, the attendees load the dataset as a CSV in a Colab Enterprise Jupyter notebook, and once they finish a little data wrangling, they load the 2.3 million recipes into BigQuery in a table that looks like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Idw_5ERlb_7HxxDN" /></figure><p>When I selected the dataset, I liked that it had so many recipes and that it had links to the sites where the recipes had been scraped from, but I didn’t like its lack of recipe descriptions or categories. It was fine for the lab I created, but the problem got me thinking. If you know a recipe name (title), ingredients list, and cooking directions, could you use Gemini to create the categories and descriptions? And that’s when I realized that I’d never actually called Gemini from within a query in BigQuery.</p><p>Challenge accepted.</p><p>The central command you need to make this all happen is ML.GENERATE_TEXT, which you can find documented, with some examples, <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-generate-text">here</a>. What wasn’t particularly clear to me, though, was the steps you need to run through before going to work on the SQL.</p><p>To use a remote model, one you create or one of Google’s such as Gemini, the general steps go like this:</p><ol><li>Create an External Connection in BigQuery to Vertex AI</li><li>Create a Model and Point It At Gemini</li><li>Access Gemini in SQL Using ML.GENERATE_TEXT</li></ol><p>Let’s take that a step at a time.</p><h4>Create an External Connection in BigQuery to Vertex AI</h4><p>First, you need an external connection to allow BigQuery to reach out and access any models you have stored in Vertex AI, whether you created them yourself, or whether it’s a model from Google such as Gemini. I don’t think there’s a SQL DDL command that can do this, but you can use bq at the command line or use the BigQuery UI.</p><p>In the UI, the steps go like this:</p><ol><li>In the Google Cloud console, use search to navigate to your BigQuery page. At the top of the BigQuery Explorer, click <strong>+ Add</strong>, then select <strong>Connections to external data sources</strong>.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*VHF9tobX-Ceoh2-L" /></figure><p>2. Set the connection type to <strong>Vertex AI remote models…</strong>, give the connection a name, something like vertex-conn, and click <strong>Create Connection</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*aEVdM8n5ahBx6VK-" /></figure><p>3. Once the connection is created, click <strong>Go to connection,</strong> and in the connection info pane, copy the service account ID. We need to grant this service account access to Vertex AI. If you miss this step, you can always find the model in the BigQuery Explorer view and click it to view the details.</p><p>4. In the Google Cloud console, use search to navigate to the <strong>IAM &amp; Admin</strong> page.</p><p>5. Click <strong>Grant Access,</strong> and in the <strong>New principals<em> </em></strong>field, paste the copied service account ID.</p><p>6. In the <strong>Select a role</strong> dropdown menu, scroll down and click the <strong>Vertex AI</strong> group of permissions, then select the<strong> Vertex AI User</strong> role. <strong>Save</strong> your change.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*4j76OOwgrnI8KHqA" /></figure><p>If you’d rather use bq to create the connection, the command would look something like the one shown below. Don’t forget to update the permission on the service account it uses!</p><blockquote>bq mk — connection \<br>— connection_type=CLOUD_RESOURCE \<br> — location=us \<br>vertex-conn</blockquote><p>Excellent! With the connection built, let’s set up the model.</p><h4>Create a Model and Point It At Gemini</h4><p>Through the connection you created, you can access other cloud resources, including models in Vertex AI. However, to keep BigQuery happily querying, you need a model inside one of your datasets, sort of as a stand-in for the remote model, in this case, Gemini. For this example, I’m going to use a DDL command to create the model. For this example, I’m creating a local model named gemini in a dataset named recipe_data that’s sitting in the US, and I’m reaching out to Vertex through our above connection, us.vertex-conn. Make sure you have a dataset in the local project you can use. In the example below, notice how the endpoint option is setting the model. Since I’m not specifying a particular sub-version of Gemini 1.0 Pro, I’ll get whatever is latest (as I’m writing this, that would be gemini-1.0-pro-002).</p><blockquote>CREATE OR REPLACE MODEL `recipe_data.gemini`<br>REMOTE WITH CONNECTION `us.vertex-conn`<br>OPTIONS(endpoint=”gemini-1.0-pro”)</blockquote><p>If you refresh your dataset in the BigQuery Explorer pane after running the above statement, you should see your new model.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/886/0*dzo2woGd-JrfVIvy" /></figure><p>And now, you’re ready to do some Gemini work right in your BigQuery SQL.</p><h4>Access Gemini in SQL Using ML.GENERATE_TEXT</h4><p>And now we circle back to my original problem: how to generate descriptions for recipes using Gemini. With all the pieces in place, let’s look at how the queries work. Let’s start with an example:</p><blockquote>SELECT *<br>FROM ML.GENERATE_TEXT(MODEL `recipe_data.gemini`,<br>(<br>SELECT<br>“How do you use ML.GENERATE_TEXT in a query?” AS prompt<br>),<br>STRUCT(0.5 AS temperature, 1024 AS max_output_tokens));</blockquote><p>You see here that we are selecting everything from the generate text function. ML.GENERATE_TEXT takes in three arguments:</p><ol><li>A reference to the local model being used, in this case, our Gemini placeholder.</li><li>A table of data that contains a prompt column.</li><li>A structure of options to pass to the model, in this case, a temperature, a 0–2 measure of model creativity, and a limit on the max tokens returned by the prompt, with 1 token being a little less than a word. Essentially, don’t get too creative (.5 out of 2) and return no more than about two pages of text in the response.</li></ol><p>When you examine the query results, you see three columns:</p><ul><li><strong>prompt</strong>: since that’s one of the columns we passed to the model, it’s selected back to us. That would be true of any additional columns we selected, they are simply echoed back.</li><li><strong>ml_generate_text_result</strong>: that’s the actual JSON coming back from Gemini.</li><li><strong>ml_generate_text_status</strong>: this will show ERROR if the model runs into issues.</li></ul><p>Pretty cool! Now back to my original problem. If I have a recipe, say:</p><ul><li><strong>id</strong>: 140961</li><li><strong>title</strong>: Dip</li><li><strong>ingredients</strong>: [“8 oz. cream cheese”, “1 pkg. smoked dried beef, chopped fine”, “5 small green onions, chopped fine (with some green tops)”, “2 Tbsp. parsley flakes”, “2 Tbsp. mayonnaise”, “dash of Accent”]</li><li><strong>directions</strong>: [“Mix all ingredients well; serve with crackers.”]</li><li><strong>uri</strong>: <a href="http://www.cookbooks.com/Recipe-Details.aspx?id=34130">www.cookbooks.com/Recipe-Details.aspx?id=34130</a></li></ul><p>What would make a good prompt to generate a description? It took me some fiddling, but I got the best results using something like:</p><p>Create a description for the recipe named [title] with the ingredients [ingredients] and the directions [directions]. Consider the ingredient list and directions while creating the description, but you don’t need to include them in the response.</p><p>It took me a few tries to get to this particular prompt. At first, I figured I wouldn’t include the directions column, but later, I figured that how you prep a set of ingredients has a lot to do with the type of dish you are making.</p><p>The next major issue I ran into was that since I included the directions and ingredients, and instructed Gemini to use them when creating a description, I was getting back a nice introductory description that then went right on and told you how to make the dish. This was not quite what I wanted. That’s when I added the final sentence. That seemed to do the trick.</p><p>I added the id field to my SELECT with the idea that once the descriptions were generated, I’d join them into the recipes table to create an augmented version. That brought me to the query:</p><blockquote>SELECT id, prompt, ml_generate_text_result<br>FROM<br>ML.GENERATE_TEXT(MODEL `recipe_data.gemini`,<br>(<br>SELECT<br>id, <br>CONCAT(“Create a description for the recipe named “, title, <br>“ with the ingredients “, ingredients, “ and the directions “, directions, <br>“. Consider the ingredient list and directions while creating the”,<br>“ description, but you don’t need to include them in the response.”) AS prompt<br>FROM `patrick-haggerty.recipe_data.recipes`<br>limit 10 — Remember! 2.3 million recipes, so don’t forget this<br>), <br>STRUCT(0.5 AS temperature, 1024 AS max_output_tokens));</blockquote><p><strong>Note:</strong> I have the permissions on my table set to allow you to access it if you’d like to experiment, but please remember that it contains 2.3 million recipes. If you plan on playing with my exact dataset, make sure you limit the records you pull. Running this query over all 2.3 million records would take more than the max BigQuery individual allowed query time of 6 hours, because of the limits Gemini has on requests per minute.</p><p>Getting close here, but the format of the JSON-generated response is a little ugly. I’m a coder at heart, so sometimes it helps me to think about what a particular structure would look like if I wanted to access it from Python. To help me think, I pulled the query into a DataFrame in a notebook in Colab Enterprise so I could play with it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*jvq0LY-Vg-6yyivB" /></figure><p>Ah, I see now (or I did eventually). To get the text, it would be:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*9q1g3yAvInBxfCgo" /></figure><p>That’s essentially:</p><ul><li>From ml_generate_text_result</li><li>Extract the candidates array and grab the first element</li><li>Extract the content</li><li>Extract the parts array and grab the first element</li><li>Finally, grab the text</li></ul><p>Turning that into SQL, you get:</p><blockquote>JSON_EXTRACT(JSON_EXTRACT_ARRAY(JSON_EXTRACT(<br>JSON_EXTRACT_ARRAY(ml_generate_text_result, ‘$.candidates’)[OFFSET(0)],<br>‘$.content’),‘$.parts’)[OFFSET(0)],‘$.text’) AS description</blockquote><p>By the way, do you know another great way to come up with that tricky JSON-processing SQL block? Go to the <a href="https://gemini.google.com/">Gemini website</a> and run the prompt:</p><blockquote>I’m in BigQuery and I’m getting back a column named ml_generate_text_result containing a JSON structure like the one you see below. How could I extract the text field?</blockquote><p>{“candidates”:[{“content”:{“parts”:[{“text”:”## A Delicious and Easy Dip with a Kick\n\nThis dip is a perfect appetizer for any party or gathering. It’s quick and easy to make, and it’s sure to be a crowd-pleaser. The combination of pork and beans, wieners, ranch dressing, sour cream, ketchup, and …</p><p>Not only will it return almost the exact query you need, but it will walk you through a nice explanation. Use your tools!</p><p>The final version of my query is ready to roll:</p><blockquote>SELECT id, prompt,<br>JSON_EXTRACT(JSON_EXTRACT_ARRAY(<br>JSON_EXTRACT(JSON_EXTRACT_ARRAY(ml_generate_text_result,<br>‘$.candidates’)[OFFSET(0)],‘$.content’),‘$.parts’)[OFFSET(0)],‘$.text’<br>) AS description<br>FROM<br>ML.GENERATE_TEXT(MODEL `recipe_data.gemini`,<br>(<br>SELECT id, CONCAT(“Create a description for the recipe named “,<br>title, “ with the ingredients “, ingredients, “ and the directions “, directions,<br>“. Consider the ingredient list and directions while creating the”,<br>“ description, but you don’t need to include them in the response.”) AS prompt<br>FROM `patrick-haggerty.recipe_data.recipes`<br>limit 10 — Remember! 2.3 million recipes, so don’t forget this<br>),<br>STRUCT(0.5 AS temperature, 1024 AS max_output_tokens,<br>[Struct(‘HARM_CATEGORY_DANGEROUS_CONTENT’ as category,<br>‘BLOCK_ONLY_HIGH’ as threshold)] as safety_settings));</blockquote><p><strong>Note:</strong> One additional setting I’ve added to this final query version is to lower the dangerous content safety setting. Turns out, there are lots of knives and cutting in various recipes and when I ran this on the full dataset the first time, it blocked a number of responses. This reduced the blocks.</p><p>So, for our example recipe:</p><ul><li>id: 140961</li><li>title: Dip</li><li>ingredients: [“8 oz. cream cheese”, “1 pkg. smoked dried beef, chopped fine”, “5 small green onions, chopped fine (with some green tops)”, “2 Tbsp. parsley flakes”, “2 Tbsp. mayonnaise”, “dash of Accent”]</li><li>directions: [“Mix all ingredients well; serve with crackers.”]</li><li>uri: <a href="http://www.cookbooks.com/Recipe-Details.aspx?id=34130">www.cookbooks.com/Recipe-Details.aspx?id=34130</a></li></ul><p>We end up with quite a nice description:</p><h3>Creamy Smoked Beef Dip</h3><p>This dip is a delightful combination of creamy, smoky, and savory flavors. The smooth texture of the cream cheese pairs perfectly with the chopped smoked dried beef, while the green onions and parsley add a fresh, herbaceous touch. A dash of Accent enhances the overall flavor, making this dip truly irresistible. Serve it with your favorite crackers for a satisfying appetizer or snack.</p><p><strong>Tips:</strong></p><ul><li>For a spicier dip, add a pinch of cayenne pepper.</li><li>If you don’t have smoked dried beef, you can substitute cooked and shredded ham or turkey.</li><li>This dip can be made ahead of time and refrigerated for up to 24 hours.</li></ul><p>Not too shabby. Thanks, Gemini, and thanks, BigQuery!</p><p>By the way, if you are interested in the marketing event that got me thinking about all of this, where you will get a chance to learn a little more about how Gemini can help you in your development process (though there won’t be much SQL), check out the upcoming events, all run by various @Google Cloud trainers from both Google and from @ROI Training:</p><p>In Phoenix on June 4th:<br><a href="https://inthecloud.withgoogle.com/build-apps-ai-era-phoenix/register.html">https://inthecloud.withgoogle.com/build-apps-ai-era-phoenix/register.html</a></p><p>In NYC on June 12th:<br><a href="https://inthecloud.withgoogle.com/gen-ai-building-apps-nyc/register.html">https://inthecloud.withgoogle.com/gen-ai-building-apps-nyc/register.html</a></p><p>In Atlanta on June 13th (featuring yours truly):<br><a href="https://inthecloud.withgoogle.com/build-apps-ai-era-atlanta/register.html">https://inthecloud.withgoogle.com/build-apps-ai-era-atlanta/register.html</a></p><p>In Denver on June 13th:<br><a href="https://inthecloud.withgoogle.com/live-and-labs-denver-noram/register.html">https://inthecloud.withgoogle.com/live-and-labs-denver-noram/register.html</a></p><p>In Salt Lake City on June 18th:<br><a href="https://inthecloud.withgoogle.com/slc-vertex-ai-live-labs/register.html">https://inthecloud.withgoogle.com/slc-vertex-ai-live-labs/register.html</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=46c33ba7cf3e" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>