Vibe querying: Write SQL queries faster with Comments to SQL in BigQuery
Gautam Gupta
ML Engineering Manager
Crafting complex SQL queries can be challenging. Often, engineers simply want to express their data needs in plain English directly within their SQL workflow. Recently, we have seen how "vibe coding" — using natural language AI prompts to generate code — makes developing easier for everyone. That’s why we’re introducing Comments to SQL in BigQuery. This feature makes writing queries using natural language – ‘vibe querying’ – a reality.
Go from plain English to SQL code
Comments to SQL is an AI-powered feature that bridges the gap between human language and structured data queries. It helps you embed natural language expressions directly within your SQL statements, which the system then translates into executable SQL code. By automating this translation, you can write complex queries faster and spend less time writing boilerplate code.
For example, let’s say you need to calculate the business days between two dates, including weekends. With this feature, you won’t need to look up the exact functions to calculate business days between two dates. Now AI can generate the SQL date function for the natural language expression: “ How many business days are there between January 1st and March 15th, excluding weekends?” This minimizes the toil of manual SQL construction, which lets you focus on finding answers in your data.
Key functionality:
-
Embed natural language: You can integrate natural language expressions into your SQL queries by enclosing them within comments. For example: /* average trip distance by day of week */.
-
Contextual understanding: BigQuery’s AI analyzes the surrounding SQL context to accurately interpret the comments. This ensures the generated SQL aligns with your intent.
-
Flexible clauses: You can use natural language expressions within various SQL clauses. NL expressions can be used within various SQL clauses, including SELECT, FROM, WHERE, ORDER BY, and GROUP BY.
-
Complex queries: You use multiple expressions within a single SQL statement to build complex queries. For instance, you could use SELECT /* average trip distance, total fare */ FROM /* NYC taxi ride public data of 2020 */ WHERE /* day of week is Saturday */ GROUP BY /* pickup location */.
-
Accessible for everyone: This feature helps you perform data analysis even if you are not an SQL expert.
-
Refine as you go: After the initial SQL is generated, you can refine your natural language expressions and immediately see how the SQL output changes.
Helping all SQL users move faster
We want to help developers be more productive and simplify data exploration. This feature works for a wide range of users, from SQL beginners to seasoned SQL experts. Whether you’re a data analyst, software developer, business analyst, Comments to SQL helps you interact with BigQuery data more effectively. For example, SQL beginners can:
-
Generate summary statistics: SELECT /* average sales per region */ FROM /* sales_table */ GROUP BY /* region */.
-
Filter data based on criteria: SELECT * FROM /* customer_table */ WHERE /* age is greater than 30 and city is New York */.
-
Order results: SELECT * FROM /* product_table */ ORDER BY /* price in descending order */.
For advanced SQL users, here are some more advanced use cases:
1. Time series analysis with conditional aggregations. Handle time-series aggregation, conditional counting, and date extraction within a single query.
NL expression: SELECT /* daily average temperature, and count of days where temperature exceeded 30 degrees Celsius */
FROM /* weather_data */
WHERE /* year is 2023 */
GROUP BY /* day */
ORDER BY /* day */.
Generated SQL:
2. Multi-table joins and complex filtering: How to handle multi-table joins, date range filtering, and string-based filtering, combined with ordering.
NL expression: SELECT /* customer name, order total, and product category */
FROM /* customers */ JOIN /* orders */ ON /* customer ID */ JOIN /* products */ ON /* product ID */
WHERE /* order date is in the last month and customer region is 'Europe'*/
ORDER BY /* order total descending */.
Generated SQL:
3. Window functions for ranking and moving averages: Handle window functions for ranking, which are typically complex to write manually. You can also group data by date parts.
NL expression: SELECT /* product name, monthly sales, and rank of products by sales within each category */
FROM /* sales_data */
WHERE /* year is 2023 */
WINDOW /* partition by category order by monthly sales descending */.Generated SQL:
4. Cohort analysis with date aggregations and user segmentation. Cohort analysis is a common but complex data analysis technique. It involves date truncation, grouping, and pivoting to display user retention over time.
NL expression: SELECT /* cohort month, count of active users in each subsequent month */
FROM /* user_activity */
WHERE /* user signup date is between '2022-01-01' and '2022-12-31' */
GROUP BY /* cohort month, activity month */ PIVOT /* activity month */.
Generated SQL:
Getting started
My team and I are already seeing the potential of BigQuery's NL expressions in comments to generate SQL to streamline our customers’ workflows. We're confident you'll find it a valuable addition to your BigQuery toolkit. To get started:
-
Open BQ Studio.
- Ensure the SQL Generation Widget is enabled.
Examples:
3. Select the SQL with your comments to transform. Click on the Gemini gutter button & click the “Convert comments to SQL” button.
4. Generation widget will appear & provide a diff view of the converted SQL/NL expression.
5. Select Insert or continue to refine using the refine/multiturn feature.
