Skip to content

bigfunctions > generate_sql

generate_sql

Call or Deploy generate_sql ?

✅ You can call this generate_sql bigfunction directly from your Google Cloud Project (no install required).

  • This generate_sql function is deployed in bigfunctions GCP project in 39 datasets for all of the 39 BigQuery regions. You need to use the dataset in the same region as your datasets (otherwise you may have a function not found error).
  • Function is public, so it can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
  • You may prefer to deploy the BigFunction in your own project if you want 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). Discover the framework

Public BigFunctions Datasets:

Region Dataset
eu bigfunctions.eu
us bigfunctions.us
europe-west1 bigfunctions.europe_west1
asia-east1 bigfunctions.asia_east1
... ...

Description

Signature

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

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...        |
+---------------+-------------------+-------------------------+


Need help using generate_sql?

The community can help! Engage the conversation on Slack

For professional suppport, don't hesitate to chat with us.

Found a bug using 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.

For professional suppport, don't hesitate to chat with us.

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.

Spread the word

BigFunctions is fully open-source. Help make it a success by spreading the word!

Share on Add a on