Skip to content

generate_sql

generate_sql(question, fully_qualified_table)

Description

Transform question to a SQL query.

This function sends the following enriched question to ask_ai (the schema of fully_qualified_table is sent so that the generated SQL query is adapted to your data):

Question: {{question}}
Table:    {{fully_qualified_table}}
Columns:  {{columns of fully_qualified_table}}
Answer:   bigquery sql query
Param Description
question A question to ask over your data. It may start with a verb
fully_qualified_table A table whose schema will be given to the generative AI model for context.
- fully_qualified_table format must be like PROJECT.DATASET.TABLE.
- fully_qualified_table must exist.
- You must have read permission on the table schema

Usage

Call or Deploy generate_sql ?
Call generate_sql directly

The easiest way to use bigfunctions

  • generate_sql function is deployed in 39 public datasets for all of the 39 BigQuery regions.
  • It can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
  • (You need to use the dataset in the same region as your datasets otherwise you may have a function not found error)

Public BigFunctions Datasets

Region Dataset
eu bigfunctions.eu
us bigfunctions.us
europe-west1 bigfunctions.europe_west1
asia-east1 bigfunctions.asia_east1
... ...
Deploy generate_sql in your project

Why deploy?

  • You may prefer to deploy generate_sql in your own project to build and manage your own catalog of functions.
  • This is particularly useful if you want to create private functions (for example calling your internal APIs).
  • Get started by reading the framework page

Deployment

generate_sql function can be deployed with:

pip install bigfunctions
bigfun get generate_sql
bigfun deploy generate_sql

Examples

call bigfunctions.eu.generate_sql("get the 3 products which generated the most revenue in 2023", "bigfunctions.eu.sales");
select * from bigfunction_result;

call bigfunctions.us.generate_sql("get the 3 products which generated the most revenue in 2023", "bigfunctions.us.sales");
select * from bigfunction_result;

call bigfunctions.europe_west1.generate_sql("get the 3 products which generated the most revenue in 2023", "bigfunctions.europe_west1.sales");
select * from bigfunction_result;


+---------------+-------------------+-------------------------+
| generated_sql | question          | raw_answer_before_clean |
+---------------+-------------------+-------------------------+
+ SELECT ...    + Question: get ... | sql``` SELECT...        |
+---------------+-------------------+-------------------------+

Use cases

A use case for the generate_sql function is to quickly generate SQL queries from natural language questions for data analysis. Imagine a business analyst who isn't proficient in SQL but needs to explore a BigQuery dataset called my_project.sales_data.transactions. They could use this function like so:

CALL bigfunctions.us.generate_sql(
    'What are the top 5 selling products by revenue in Q1 2024?',
    'my_project.sales_data.transactions'
);
SELECT * FROM bigfunction_result;

This call would send the question and the fully qualified table name to the generate_sql function. The function leverages a generative AI model (likely by internally calling ask_ai) to understand the question and the schema of the provided table (my_project.sales_data.transactions). It then returns a generated SQL query in the bigfunction_result table. The analyst can then execute the generated SQL to get the desired results without having to write the query themselves.

Benefits of this approach:

  • Accessibility: Enables non-technical users to analyze data using natural language.
  • Speed and Efficiency: Quickly generates queries, saving time and effort.
  • Exploration and Prototyping: Facilitates quick data exploration and testing different hypotheses.
  • Learning SQL: Can be used as a learning tool to understand how natural language questions translate to SQL.

Other Examples:

  • Marketing Analyst: "How many customers made their first purchase in the last month?"
  • Sales Manager: "What is the average order value for customers in California?"
  • Product Manager: "Which products have seen the biggest increase in sales this year?"

In each of these cases, the analyst can ask their question in plain English, and the generate_sql function takes care of translating it into a functional SQL query.

Need help or Found a bug?
Get help using generate_sql

The community can help! Engage the conversation on Slack

We also provide professional suppport.

Report a bug about generate_sql

If the function does not work as expected, please

  • report a bug so that it can be improved.
  • or open the discussion with the community on Slack.

We also provide professional suppport.

Show your ❤ by adding a ⭐ on