Associate Data Practitioner

Utilize Google LLM Functions in BigQuery

BigQuery lets you call remote functions to run pretrained Google large language models (LLMs) directly in your SQL queries. By using a remote connection to Vertex AI endpoints, you can analyze and interpret text data without moving it outside of BigQuery. This method gives you low-code, scalable access to advanced AI features like text generation, summarization, and embeddings. Keeping your data and computations in one place simplifies your workflow and improves data security.

Before you can use LLM functions, you need to load your text data into BigQuery. Common sources include:

• Conversational Insights and Dialogflow CX exports for chat and customer support logs
• Firebase analytics, messaging, and Crashlytics reports
• Google Analytics 4 and Analytics 360 session information
• Search Console performance data and Recommender snapshots

To set up an LLM remote function, you first create an external connection pointing to your Vertex AI model endpoint. Then you run a CREATE EXTERNAL FUNCTION statement in BigQuery SQL, specifying the connection name, model identifier, and input/output schemas. This setup ensures that when you call the function, BigQuery makes a secure API request to the LLM, keeping your data private and your queries reliable.

Once the remote function is defined, you can mix AI calls with regular SQL operations in the same query. For example, you might:

• Generate concise summaries of long customer feedback
• Compute sentiment scores or topic embeddings on the fly
• Join AI-generated outputs with user profiles for richer analysis

After running your LLM queries, it’s important to evaluate the results by checking response quality, latency, and cost. Use a sample of known texts to validate accuracy, and adjust prompts or parameters as needed. Monitoring query usage helps you optimize both performance and budget, making sure your text analysis remains effective and cost-efficient.

Conclusion

In this section, you learned how to use remote connections in BigQuery to call pretrained Google LLMs via Vertex AI. You saw how to bring text data into BigQuery, set up an external connection, and define remote functions. You also explored mixing AI calls with standard SQL for tasks like summarization and embeddings. Finally, you reviewed ways to evaluate response quality, monitor latency, and manage costs to ensure your text analysis is both accurate and scalable.