Generate the SQL code automatically with the SQL Copilot for BigQuery: step-by-step instructions

Step 1. Add context with project tables

To start, provide the SQL Copilot with the project, dataset, and table that you want to be included in the query.

For example, if your project ID is "myproject", your dataset ID is "sales_data", and your table ID is "transactions", provide the following information: "myproject.sales_data.transactions".

This is required to simplify the process of generating the SQL query. As large organizations tend to have dozens of projects with hundreds of datasets and tables, this enables GPT to focus on the data you need right away.

Table details.png

The SQL Copilot plugin will prompt you to allow the action to authorize access to BigQuery. It is used to fetch the details about the data schema, such as tables, columns, and important information like if the data is partitioned, etc.

This enables the GPT with the context to provide you with accurate results. In addition, the integration with BigQuery will help the plugin to validate the query with the “dry run” once the SQL code is generated. In case there will be any issues, it will get the type of error and will fix it automatically.

After the integration is authorized, you may add more tables using a similar format ("myproject.sales_data.transactions"). You can add as many as you need to be included in the query, and you can even do this in bulk, including all of them in one message!

 

Step 2. Visualize the database relations

Data schema.png

Once all the necessary data tables are provided, The SQL Copilot plugin will suggest you generate the database relations table. It will automatically highlight the relations of the objects and their type (one-to-one, one-to-many, etc.).

It enables you to understand if GPT got the relations correctly. This step is key (pun intended!) to generate the correct SQL code that you can use right away.

As a result, The SQL Copilot plugin will provide you with a JPG image right in the chat, and with a link to a high-resolution image that you may open in a separate tab.

A simple fix in case some of the relations are incorrect

Working with custom data may be tricky for the AI, as it may make assumptions about the table keys that may differ from what you expect. You know your data best after all! But if you spot any mistakes, you may correct them by commenting on what is the true relations between objects.

You may use any format you like, but here’s an example for your convenience:

The relation between the tables is project.dataset.table.ProjectId (Many) and project.dataset.anothertable.ProjectName (One).

Feel free to provide all instructions in one message to save time. The plugin will update the preview to help you ensure that the relations are correct.

 

Step 3. Generate SQL code automatically

SQl Query.png

Once the plugin has the details about your data schema, it is ready to generate the SQL code for you. In fact, you may use the conversation multiple times and later on if you need to fetch different insights from the same tables.

Simply ask what you need to learn from the query, and The SQL Copilot plugin will generate the full query and automatically verify that it works. The work here is done!

 

Step 4. Automatically detect & fix the issues

If you logged into BigQuery at the beginning of the conversation, The SQL Copilot plugin will be able to automatically verify if the generated query would work for you. It will perform the dry run in your dataset, and validate the response from BigQuery.

In most cases, it should work right away, but in case there is an issue with the formatting or some objects, it will get the description of the issues and will fix it automatically without you needing to switch tabs to do it yourself.

 

Step 5. Upload the report to Google Sheets

In case the data from your report are required to be shared with your team members, you may find the OWOX BI BigQuery Reports extension for Google Sheets useful. You may share the query with your team, and enable the schedule to automatically update & share dashboards so your team will always have a fresh report before that daily or weekly meeting. It’s also free to install!

 

Feel free to submit your feedback to us at bi@owox.com.

 

 

 

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.