How to allow access to BigQuery for SQL Copilot

In this article, we'll explain the access rights you need to work effectively with the OWOX BI SQL Copilot. Remember, SQL Copilot can generate SQL code based on relationships between your BigQuery tables and can also validate the generated SQL code (via Dry run). It's important to understand that each of the described scenarios requires different access rights to your data in BigQuery. Let’s delve into the details.

Requesting access to BigQuery

Copilot generates functional SQL code based on one or more of your tables in BigQuery, which you specify in your message in the format "project.dataset.table." Following this, Copilot attempts to connect to the BigQuery API to read the metadata of each table and subsequently map the relationships between them.

copilot-03_1.png

By default, the '[OWOX] SQL Builder for BigQuery' application requests permissions to view and manage your tables, datasets, and jobs in Google BigQuery. These permissions should be sufficient not only to visualize connections between tables but also to validate the generated SQL code and even execute it in your GCP project. Now, let’s consider two scenarios based on your expectations.

Important note! When you sign in to BigQuery using OAuth, the specific roles and permissions granted to you depend on the OAuth scopes that are configured by the application during the authentication process. These scopes determine what access the application has to your Google Cloud resources, including BigQuery. The roles and permissions are typically assigned in the Google Cloud IAM (Identity and Access Management) settings, and the OAuth scopes just provide the authenticated application access within those allowed roles. Therefore, it's essential to ensure that your Google Cloud account and the application have the correct IAM roles and OAuth scopes configured to match your needs.

Scenario 1: You want to validate SQL code and run queries

To perform a dry run of an SQL query in BigQuery, which allows you to validate the query without actually executing it and incurring costs, the required permission is bigquery.jobs.create. This permission allows the creation of new jobs, including query jobs in BigQuery, which is necessary for initiating a dry run.

This permission is included in several predefined roles, suitable depending on your level of access and responsibilities:

BigQuery Job User: This role allows the user to run jobs, including query and load jobs.

BigQuery Admin: This role includes permissions for managing and executing all aspects of BigQuery, including running queries.

Using the dry run option is an efficient way to check SQL query syntax and estimate query costs without affecting your data or querying quotas.

Scenario 2: You prefer not to provide data access and only need to generate SQL code

To retrieve the schema of a table in BigQuery, the required permission is bigquery.tables.get. This permission allows Copilot to fetch the metadata about the table, including its schema, without the ability to read the data itself. These permissions are generally included in predefined roles such as:

BigQuery Data Viewer: Grants the ability to view table metadata and data.

BigQuery Metadata Viewer: Grants the ability to view table and dataset metadata, but does not allow viewing data in tables.

So, based on these two scenario examples, you may decide which roles your Google account should have before you sign in to BigQuery using OAuth within Copilot.

 

Step-by-step instructions

Step 1. Click the "Sign in with bigquery.googleapis.com" button to grant access to your tables.

copilot-01.png

Step 2. Select your Google account with sufficient access to BigQuery (based on your expectations from the SQL Copilot’s performance).

copilot-02.png

Step 3. Provide access by hitting the "Allow" button.

copilot-03.png

Step 4. Hit the “Confirm” button.

copilot-04.png

You're all set! Copilot will read the data schema of your tables and continue its work generating valid and accurate SQL code to address your needs.

copilot-05.png

If you have any questions about the OWOX BI SQL Copilot for BigQuery, please contact our support team 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.