Analyze with BigQuery data canvas

BigQuery Studio data canvas, which is a Gemini in BigQuery feature, lets you find, transform, query, and visualize data by using natural language prompts and a graphic interface for analysis workflows.

For analysis workflows, BigQuery data canvas uses a directed acyclic graph (DAG), which provides a graphical view of your workflow. In BigQuery data canvas, you can iterate on query results and work with multiple branches of inquiry in a single place.

BigQuery data canvas is designed to accelerate analytics tasks and help data professionals such as data analysts, data engineers, and others with their data-to-insights journey. It doesn't require that you have technical knowledge of specific tools, only basic familiarity with reading and writing SQL. BigQuery data canvas works with Dataplex metadata to identify appropriate tables based on natural language.

BigQuery data canvas isn't intended for direct use by business users.

BigQuery data canvas uses Gemini in BigQuery to find your data, create SQL, generate charts, and create data summaries.

Learn how and when Gemini for Google Cloud uses your data.

Capabilities

BigQuery data canvas lets you do the following:

• Use natural language queries or keyword search syntax with Dataplex metadata to find assets such as tables, views, or materialized views.

• Use natural language for basic SQL queries such as the following:

  • Queries that contain FROM clauses, math functions, arrays, and structs.
  • JOIN operations for two tables.

• Visualize data by using the following graphic types:

  • Bar chart
  • Heat map
  • Line graph
  • Pie chart
  • Scatter chart

• Create custom visualizations by using natural language to describe what you want.

• Automate data insights.

Limitations

• Natural language commands might not work well with the following:

  • BigQuery ML
  • Apache Spark
  • Object tables
  • BigLake
  • INFORMATION_SCHEMA views
  • JSON
  • Nested and repeated fields
  • Complex functions and data types such as DATETIME and TIMEZONE

• Data visualizations don't work with geomap charts.

Prompting best practices

With the right prompting techniques, you can generate complex SQL queries. The following suggestions help BigQuery data canvas refine your natural language prompts to increase the accuracy of your queries:

• Write with clarity. State your request clearly and avoid being vague.

• Ask direct questions. For the most precise answer, ask one question at a time, and keep your prompts concise. If you need to, separate your prompts into different nodes in BigQuery data canvas.

• Give focused and explicit instructions. Emphasize key terms in your prompts.

• Specify the order of operations. Provide instructions in a clear and ordered manner. Divide tasks into small, focused steps.

• Refine and iterate. Try different phrases and approaches to see what yields the best results.

For more information, see Prompting best practices for BigQuery data canvas.

Before you begin

1. Ensure that Gemini in BigQuery is enabled for your Google Cloud project. An administrator typically performs this step.
2. Ensure that you have the necessary Identity and Access Management (IAM) permissions to use BigQuery data canvas.

Required roles

To get the permissions that you need to use BigQuery data canvas, ask your administrator to grant you the following IAM roles on the project:

• BigQuery Studio User (roles/bigquery.studioUser)
• Cloud AI Companion User (roles/cloudaicompanion.user)

For more information about granting roles, see Manage access to projects, folders, and organizations.

You might also be able to get the required permissions through custom roles or other predefined roles.

For more information about IAM roles and permissions in BigQuery, see Introduction to IAM.

Use BigQuery data canvas

You can use BigQuery data canvas in the Google Cloud console, a query, or a table.

1. Go to the BigQuery page.

   Go to BigQuery

2. In the query editor, next to SQL query, click Create new, and then click Data canvas.

   

3. In the Natural language prompt field, enter a natural language prompt.

   For example, if you enter Find me tables related to trees, BigQuery data canvas returns a list of possible tables, including public datasets like bigquery-public-data.usfs_fia.plot_tree or bigquery-public-data.new_york_trees.tree_species.

4. Select a table.

   A table node for the selected table is added to BigQuery data canvas. To view schema information, view table details, or preview the data, select the various tabs in the table node.

Try example workflows

This section demonstrates different ways to use BigQuery data canvas in analysis workflows.

Example workflow: Find, query, and visualize data

In this example, you use natural language prompts in BigQuery data canvas to find data, generate a query, and edit the query. Then, you create a chart.

Prompt 1: Find data

1. In the Google Cloud console, go the BigQuery page.

   Go to BigQuery

2. In the query editor, next to SQL query, click Create new, and then click Data canvas.

   

3. In the Natural language prompt field, enter the following natural language prompt:

   Chicago taxi trips
   

   BigQuery data canvas generates a list of potential tables based on Dataplex metadata. You can select multiple tables.

4. Select bigquery-public-data.chicago_taxi_trips.taxi_trips table, and then click Add to canvas.

   A table node for taxi_trips is added to BigQuery data canvas. To view schema information, view table details, or preview the data, select the various tabs in the table node.

Prompt 2: Generate a SQL query in the selected table

As an early-stage technology, Gemini for Google Cloud products can generate output that seems plausible but is factually incorrect. We recommend that you validate all output from Gemini for Google Cloud products before you use it. For more information, see Gemini for Google Cloud and responsible AI.

To generate a SQL query for the bigquery-public-data.chicago_taxi_trips.taxi_trips table, do the following:

1. In the data canvas, click Query.

2. In the Natural language prompt field, enter the following:

   Get me the 100 longest trips
   

   BigQuery data canvas generates a SQL query similar to the following:

   SELECT
     taxi_id,
     trip_start_timestamp,
     trip_end_timestamp,
     trip_miles
   FROM
     `bigquery-public-data.chicago_taxi_trips.taxi_trips`
   ORDER BY
     trip_miles DESC
   LIMIT
     100;

Prompt 3: Edit the query

To edit the query that you generated, you can manually edit the query, or you can change the natural language prompt and regenerate the query. In this example, you use a natural language prompt to edit the query to select only trips where the customer paid with cash.

1. In the Natural language prompt field, enter the following:

   Get me the 100 longest trips where the payment type is cash
   

   BigQuery data canvas generates a SQL query similar to the following:

   SELECT
     taxi_id,
     trip_start_timestamp,
     trip_end_timestamp,
     trip_miles
   FROM
     `PROJECT_ID.chicago_taxi_trips_123123.taxi_trips`
   WHERE
     payment_type = 'Cash'
   ORDER BY
     trip_miles DESC
   LIMIT
     100;

   In the preceding example, PROJECT_ID is the ID of your Google Cloud project.

2. To view the results of the query, click Run.

Create a chart

1. In the data canvas, click Visualize.

2. Click Create bar chart.

   BigQuery data canvas creates a bar chart showing the most trip miles by trip ID. Along with providing a chart, BigQuery data canvas summarizes some of the key details of the data backing the visualization.

3. Optional: Do one or more of the following:

   • To modify the chart, click Edit, and then edit the chart in the Edit visualization pane.
   • To share the data canvas, click Share, then click Share Link to copy BigQuery data canvas link.
   • To clean up the data canvas, select More actions, and then select Clear canvas. This step results in a blank canvas.

Example workflow: Join tables

In this example, you use natural language prompts in BigQuery data canvas to find data and join tables. Then, you export a query as a notebook.

Prompt 1: Find data

1. In the Natural language prompt field, enter the following prompt:

   Information about trees
   

   BigQuery data canvas suggests several tables that have information about trees.

2. For this example, select the bigquery-public-data.new_york_trees.tree_census_1995 table, and then click Add to canvas.

   The table is displayed on the canvas.

Prompt 2: Join the tables on their address

1. On the data canvas, click Join.

   BigQuery data canvas suggests tables to join.

2. To open a new Natural language prompt field, click Search for tables.

3. In the Natural language prompt field, enter the following prompt:

   Information about trees
   

4. Select the bigquery-public-data.new_york_trees.tree_census_2005 table, and then click Add to canvas.

   The table is displayed on the canvas.

5. On the data canvas, click Join.

6. In the On this canvas section, select the Table cell checkbox, and then click OK.

7. In the Natural language prompt field, enter the following prompt:

   Join on address
   

   BigQuery data canvas suggests the SQL query to join these two tables on their address:

   SELECT
     *
   FROM
     `bigquery-public-data.new_york_trees.tree_census_2015` AS t2015
   JOIN
     `bigquery-public-data.new_york_trees.tree_census_1995` AS t1995
   ON
     t2015.address = t1995.address;

8. To run the query and view the results, click Run.

Export query as a notebook

BigQuery data canvas lets you export your queries as a notebook.

1. In the data canvas, click Export as notebook.
2. In the Save Notebook pane, enter the name for the notebook and the region where you want to save it.
3. Click Save. The notebook is created successfully.
4. Optional: To view the created notebook, click Open.

Example workflow: Edit a chart by using a prompt

In this example, you use natural language prompts in BigQuery data canvas to find, query, and filter data, and then edit visualization details.

Prompt 1: Find data

1. To find data about US names, enter the following prompt:

   Find data about USA names
   

   BigQuery data canvas generates a list of tables.

2. For this example, select the bigquery-public-data.usa_names.usa_1910_current table, and then click Add to canvas.

Prompt 2: Query the data

1. To query the data, in the data canvas, click Query, and then enter the following prompt:

   Summarize this data
   

   BigQuery data canvas generates a query similar to the following:

   SELECT
     state,
     gender,
     year,
     name,
     number
   FROM
     `bigquery-public-data.usa_names.usa_1910_current`

2. Click Run. The query results are displayed.

Prompt 3: Filter the data

1. In the data canvas, click Query these results.

2. To filter the data, in the SQL prompt field, enter the following prompt:

   Get me the top 10 most popular names in 1980
   

   BigQuery data canvas generates a query similar to the following:

   SELECT
     name,
     SUM(number) AS total_count
   FROM
     `bigquery-public-data`.usa_names.usa_1910_current
   WHERE
     year = 1980
   GROUP BY
     name
   ORDER BY
     total_count DESC
   LIMIT
     10;

   When you run the query, you get a table with the ten most common names of children born in 1980.

Create and edit a chart

1. In the data canvas, click Visualize.

   BigQuery data canvas suggests several visualization options, including a bar chart, pie chart, line graph, and custom visualization.

2. For this example, click Create bar chart.

   BigQuery data canvas creates a bar chart similar to the following:

   

Along with providing a chart, BigQuery data canvas summarizes some of the key details of the data backing the visualization. You can modify the chart by clicking Visualization details and editing your chart in the side panel.

Prompt 4: Edit visualization details

1. In the Visualization prompt field, enter the following:

   Create a bar chart sorted high to low, with a gradient
   

   BigQuery data canvas creates a bar chart similar to the following:

   

2. Optional: To make further changes, click Edit.

   The Edit visualization pane is displayed. You can edit details such as the chart title, x-axis name, and y-axis name. Also, if you click the JSON Editor tab, you can directly edit the chart based on the JSON values.

View all data canvases

To view a list of all data canvases in your project, do the following:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the Explorer pane, click View actions next to Data canvases, and then do one of the following:

• To open the list in the current tab, click Show all.
• To open the list in a new tab, click Show all in > New tab.
• To open the list in a split tab, click Show all in > Split tab.

View data canvas metadata

To view data canvas metadata, do the following:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the Explorer pane, expand your project and the Data canvases folder, and if necessary, the Shared data canvases folder. Click the name of the data canvas you want to view metadata for.

3. Look at the Summary pane to see information about the data canvas such as the region it uses and the date it was last modified.

Work with data canvas versions

You can view, compare, and restore versions of a data canvas.

View and compare data canvas versions

To view different versions of a data canvas and compare them with the current version, do the following:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the Explorer pane, expand your project and the Data canvases folder, and if necessary, the Shared data canvases folder. Click the name of the data canvas you want to view activity for.

3. Click the Activity tab to see a list of the data canvas versions in descending order by date.

4. Click View actions next to a data canvas version and then click Compare. The comparison pane opens, comparing the data canvas version that you selected with the current data canvas version.

5. Optional: To compare the versions inline instead of in separate panes, click Compare and then click Inline.

Restore a data canvas version

Use one of the following options to restore a data canvas version. Restoring from the comparison pane lets you compare the previous version of the data canvas to the current version before choosing whether to restore it.

Activity pane

1. In the Explorer pane, expand your project and the Data canvases folder, and if necessary, the Shared data canvases folder. Click the name of the data canvas that you want to restore a previous version of.
2. Select the Activity pane.
3. Click View actions next to the version of the data canvas that you want to restore and then click Restore.
4. Click Confirm to confirm the action.

Comparison pane

1. In the Explorer pane, expand your project and the Data canvases folder, and if necessary, the Shared data canvases folder. Click the name of the data canvas that you want to restore a previous version of.
2. Select the Activity pane.
3. Click View actions next to a data canvas version and then click Compare. The comparison pane opens, comparing the data canvas version that you selected with the most recent data canvas version.
4. If you want to restore the previous data canvas version after comparison, click Restore.
5. Click Confirm to confirm the action.

Pricing

For details about pricing for this feature, see Gemini in BigQuery pricing overview.

Quotas and limits

For information about quotas and limits for this feature, see Quotas for Gemini in BigQuery.

Provide feedback

You can help improve BigQuery data canvas suggestions by submitting feedback to Google. To provide feedback, do the following:

1. In the Google Cloud console toolbar, click Submit feedback.

2. Optional: To copy the DAG JSON information to provide additional context to your feedback, click Copy.

3. To fill out the form and provide feedback, click form.

Data sharing settings apply to the entire project and can only be set by a project administrator who has the serviceusage.services.enable and serviceusage.services.list IAM permissions. For more information about data use in the Trusted Tester Program, see Gemini in Google Cloud Trusted Tester Program.

To provide direct feedback about this feature, you can also contact [e-mail].

What's next

• Learn how to write queries with Gemini assistance.

• Learn how to create notebooks.