Associate Data Practitioner

Utilize Google LLM Functions in BigQuery

BigQuery allows you to call remote functions that run pretrained Google large language models (LLMs) directly inside your SQL queries. These functions use remote connections to Vertex AI endpoints, so you can analyze text data without moving it out of BigQuery. This integration gives you low-code, scalable access to LLM capabilities such as text generation, summarization, and embeddings, while keeping your data and computation in one place.

Before applying LLM functions, you bring your text data into BigQuery from common sources such as Conversational Insights, Dialogflow CX exports, Firebase analytics, Google Analytics 4, Search Console, and Vertex AI predictions. To set up the function, you create an external connection to your Vertex AI model endpoint and then use CREATE EXTERNAL FUNCTION in BigQuery SQL, specifying the connection, model name, and input/output schemas. This process ensures that your queries call the LLM over a secure API call, maintaining data privacy and consistent performance. Once defined, the function behaves like any other SQL function.

After defining the remote LLM function, you can run queries that mix regular data operations with AI calls—for example, passing conversation texts to generate summaries, computing sentiment or topic embeddings on the fly, or joining LLM outputs with user metadata for richer reports. To evaluate your LLM queries, review response quality, latency, and cost. Use sample sets to validate accuracy and refine prompts or parameters, and monitor query usage to optimize both performance and budget.

Configure Remote Model Definitions

BigQuery ML enables you to create a remote model that interfaces directly with Vertex AI foundation models, such as Gemini, using standard SQL without moving your data or managing complex training infrastructure. The process creates a reference in BigQuery that points to the model hosted in Vertex AI, allowing seamless analysis and content generation. You use Data Definition Language (DDL) statements, primarily a CREATE MODEL statement with the model type set to REMOTE, and you must define the connection options correctly so BigQuery knows which pretrained model to communicate with.

Configuring the correct endpoint is critical for successful connectivity. The location of your BigQuery dataset must align with the region of the Vertex AI model. If your dataset is in a single region, the Vertex AI endpoint must be in the same region. For datasets in multi-regions like US or EU, you can use endpoints within that broader area. A global endpoint offers higher availability for supported models but does not guarantee specific data processing locations.

Once the remote model is configured, you interact with it using standard SQL queries just like any other table or local model. The request is sent to Vertex AI for processing, and the inference results are returned directly to BigQuery for analysis. This integration simplifies the workflow, allowing data practitioners to perform advanced tasks such as text generation or image analysis efficiently.

Establish a Remote Connection to BigQuery

To establish a remote connection to BigQuery for integrating large language models (LLMs), you must configure secure connections and understand authentication methods. Remote connections enable seamless integration with Google Cloud services and facilitate the execution of complex queries within BigQuery. Configuration requires setting up a Google Cloud service account; you can either create a new one or use an existing one. While a service account key in JSON format is typically used for authentication, workload identity emails are recommended over JSON keys for enhanced security.

The integration process requires specific parameters such as account type (default is service_account), project ID (identifying the Google Cloud project linked to BigQuery), and private key ID and private key (provided in the JSON key file). Two main authentication methods are used: the service account key JSON, which provides detailed authentication info including type, project_id, and keys, and workload identity, which avoids the risk of managing JSON key files and is the recommended approach.

Using pretrained Google LLMs with BigQuery allows for advanced data processing and analysis. The remote connection empowers you to leverage these models for substantial computation tasks, enhancing your data analysis capabilities. Efficient integration enables harnessing the potential of LLMs directly in data operations, fostering improved security and performance outcomes. Together, these three areas—remote functions, remote model definitions, and secure connections—form the foundation for using pretrained Google LLMs effectively within BigQuery.