BigQuery ML is now compatible with open-source gen AI models

BigQuery Machine Learning allows you to use large language models (LLMs), like Gemini, to perform tasks such as entity extraction, sentiment analysis, translation, text generation, and more on your data using familiar SQL syntax.

Today, we are extending this capability with support for any open-source LLM from the Vertex AI Model Garden — including any models you deploy from Hugging Face and including OSS models you might have tuned. This greatly expands the model choice available to developers.

In this post, we use the Meta Llama 3.3 70B model to illustrate how this integration works. However, you can use any of 170K+ text generation models available on Hugging Face by following the same steps. We've also got a tutorial notebook ready for you, or you can jump right into the steps below.

Using Open-Source Software (OSS) models with BigQuery ML

1. Host the model on a Vertex endpoint
First, choose a text -generation model from Hugging Face. Then, navigate to Vertex AI Model Garden > Deploy from Hugging Face. Enter the model URL and optionally modify the endpoint name, deployment region, and machine spec for the deployment endpoint.

Alternatively, you can search for ‘Llama 3.3’ from the Vertex AI Model Garden UI, accept the terms, and deploy the model endpoint. You can also do this step programmatically (see the tutorial notebook here).

Note: To use LLama models, you need to agree to LLAMA 3.3 COMMUNITY LICENSE AGREEMENT on the LLama 3.3 Model Card in Hugging Face or accept terms in the Vertex Model Garden UI. You need to complete this step before deploying the model.

2. Create a remote model in BigQuery
Model deployment takes several minutes. After the deployment is complete, create a remote model in BigQuery using a SQL statement like following:

To allow BigQuery to connect to a remote endpoint you need to provide a ‘Connection’. If you don’t already have a connection you can create one following the instructions here. Replace the placeholder endpoint in the above code sample with the endpoint URL. You can get information on endpoint_id from the console via Vertex AI > Online Prediction>Endpoints>Sample Request.

3. Perform inference
You are now ready to perform inference against this model from BigQuery ML.For this scenario, take this medical transcripts dataset as an example. It has unstructured and varied raw transcripts capturing history, diagnosis and treatment provided of patients visiting a medical facility. A sample transcript looks like the image below:

Create a table

To analyze this data in BigQuery, first create a table.

Perform inference

You can now use your Llama model to extract structured data from the unstructured transcripts in your table. Say you want to extract the patient's age, gender and list of diseases for each entry. You can do so with a SQL statement like the following and save the derived insights to a table. Include the information you want to extract and its schema in the model prompt.

The output returned from this Llama endpoint includes the input prompt so we also wrote and used a ExtractOutput function to help us parse the output. The output table with the results in the ‘generated_text’ column is as follows:

Perform analytics on results

You can now perform all sorts of analytics on this data. For example, answer ‘What are the most common diseases in females with age 30+ in our sample?’ using a simple SQL query. You can see that ‘Hypertension’, ‘Arthritis’ and ‘Hyperlipidemia’ are most common.

Get started today

Try out BigQuery with your own preferred open model or a tuned/distilled model with the BigQuery and Vertex Model Garden integration today. Learn more in our documentation.