Analyze with BigQuery data canvas

This document describes how to use data canvas for data analysis. You can also manage data canvas metadata by using Dataplex.

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

Ensure that Gemini in BigQuery is enabled for your Google Cloud project. An administrator typically performs this step.
Ensure that you have the necessary Identity and Access Management (IAM) permissions to use BigQuery data canvas.
To manage data canvas metadata in Dataplex, ensure that the Dataplex API is enabled in your Google Cloud project.

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)
Gemini for Google Cloud 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.

To manage data canvas metadata in Dataplex, ensure that you have the required Dataplex roles and the dataform.repositories.get permission.

Use BigQuery data canvas

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

Go to the BigQuery page.

Go to BigQuery
In the query editor, next to SQL query, click Create new, and then click Data canvas.
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.
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.

Node types

Each BigQuery data canvas consists of one or more nodes. Nodes can be connected in any order. BigQuery data canvas has the following node types:

Text
Search
Table
SQL
Visualization
Insights

Text node

In BigQuery data canvas, a text node lets you add rich text content to your canvas. It's useful for adding explanations, notes, or instructions to your canvas, making it easier for you and others to understand the context and purpose of your analysis. You can enter any text content you want into the text node editor, including Markdown for formatting. This ability lets you create visually appealing and informative text blocks.

Search node

In BigQuery data canvas, a search node lets you find and incorporate data assets into your canvas. It acts as a bridge between your natural language queries or keyword searches and the actual data you want to work with.

You provide a search query, either with natural language or using keywords. The search node searches through your data assets. It leverages Dataplex metadata for enhanced context awareness. BigQuery data canvas also suggests recently used tables, queries, and saved queries.

The search node returns a list of relevant data assets that match your query. It factors in column names and table descriptions. You can then select the assets you want to add to your data canvas as table nodes, where you can further analyze and visualize the data.

Table node

In BigQuery data canvas, a table node represents a specific table that you've incorporated into your analysis workflow. It represents the data you're working with and lets you interact with it directly.

A table node displays information about the table, such as its name, schema, and a preview of the data. You can interact with the table by viewing details such as the table schema, table details, and a table preview. From the table node, you can click the Query button to open a SQL node, or the Join button to join the table to another table.

SQL node

In BigQuery data canvas, a SQL node lets you execute custom SQL queries directly within your canvas. You can either write SQL code directly in the SQL node editor or use a natural language prompt to generate the SQL.

The SQL node executes the provided SQL query against the specified data sources. The SQL node produces a result table, which can then be connected to other nodes in the data canvas for further analysis or visualization.

After the query has run, you can export it as a scheduled query, export the query results, or share the canvas, similar to running an interactive query.

Within the data canvas, you can do the following:

Query the results in a new SQL node.
Visualize the results in a visualization node.
Generate insights on the results in an insights node.
Join the results to another table.

Visualization node

In BigQuery data canvas, a visualization node lets you display data visually, making it easier to understand trends, patterns, and insights. It provides a variety of chart types to choose from, letting you select and customize the best visualization for your data.

A visualization node takes a table as input, which can be the result of a SQL query or a table node. Based on the selected chart type and the data in the input table, the visualization node generates a chart. You can select Auto-Chart to let BigQuery select the best chart type for your data. The visualization node then displays the generated chart.

The visualization node lets you customize your chart, including changing the colors, labels, and data sources. You can also export the chart as a PNG file.

Insights node

In BigQuery data canvas, an insights node lets you generate insights and summaries from the data within your data canvas. This helps you uncover patterns, assess data quality, and perform statistical analysis on your canvas. It identifies trends, patterns, anomalies, and correlations within your data, as well as generates concise and clear summaries of the data analysis results.

For more information about data insights, see Generate data insights in BigQuery.

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

In the Google Cloud console, go the BigQuery page.

Go to BigQuery
In the query editor, next to SQL query, click Create new, and then click Data canvas.
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.
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

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

In the data canvas, click Query.

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.

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.

To view the results of the query, click Run.

Create a chart

In the data canvas, click Visualize.
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.
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

In the Natural language prompt field, enter the following prompt:
```
Information about trees
```
BigQuery data canvas suggests several tables that have information about trees.
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

On the data canvas, click Join.

BigQuery data canvas suggests tables to join.
To open a new Natural language prompt field, click Search for tables.
In the Natural language prompt field, enter the following prompt:
```
Information about trees
```
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.
On the data canvas, click Join.
In the On this canvas section, select the Table cell checkbox, and then click OK.

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;

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.

In the data canvas, click Export as notebook.
In the Save Notebook pane, enter the name for the notebook and the region where you want to save it.
Click Save. The notebook is created successfully.
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

To find data about US names, enter the following prompt:
```
Find data about USA names
```
BigQuery data canvas generates a list of tables.
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

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`
```
Click Run. The query results are displayed.

Prompt 3: Filter the data

In the data canvas, click Query these results.

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

In the data canvas, click Visualize.

BigQuery data canvas suggests several visualization options, including a bar chart, pie chart, line graph, and custom visualization.
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

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:
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:

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

Go to BigQuery
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:

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

Go to BigQuery
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.
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:

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

Go to BigQuery
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.
Click the Activity tab to see a list of the data canvas versions in descending order by date.
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.
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

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.
Select the Activity pane.
Click View actions next to the version of the data canvas that you want to restore and then click Restore.
Click Confirm to confirm the action.

Comparison pane

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.
Select the Activity pane.
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.
If you want to restore the previous data canvas version after comparison, click Restore.
Click Confirm to confirm the action.

Manage metadata in Dataplex

Dataplex lets you view and manage metadata for data canvases. Data canvases are available in Dataplex by default, without additional configuration.

You can use Dataplex to manage data canvases in all BigQuery locations. Managing data canvases in Dataplex is subject to Dataplex quotas and limits and Dataplex pricing.

Dataplex automatically retrieves the following metadata from data canvases:

Data asset name
Data asset parent
Data asset location
Data asset type
Corresponding Google Cloud project

Dataplex logs data canvases as entries with the following entry values:

System entry group: The system entry group for data canvases is @dataform. To view details of data canvas entries in Dataplex, you need to view the dataform system entry group. For instructions about how to view a list of all entries in an entry group, see View details of an entry group in the Dataplex documentation.
System entry type: The system entry type for data canvases is dataform-code-asset. To view details of data canvases, you need to view the dataform-code-asset system entry type, filter the results with an aspect-based filter, and set the type field inside dataform-code-asset aspect to DATA_CANVAS. Then, select an entry of the selected data canvas. For instructions about how to view details of a selected entry type, see View details of an entry type in the Dataplex documentation. For instructions about how to view details of a selected entry, see View details of an entry in the Dataplex documentation.
System aspect type: The system aspect type for data canvases is dataform-code-asset. To provide additional context to data canvases in Dataplex by annotating data canvas entries with aspects, view the dataform-code-asset aspect type, filter the results with an aspect-based filter, and set the type field inside dataform-code-asset aspect to DATA_CANVAS. For instructions about how to annotate entries with aspects, see Manage aspects and enrich metadata in the Dataplex documentation.
Type: The type for data canvases is DATA_CANVAS. This type lets you filter data canvases in the dataform-code-asset system entry type and the dataform-code-asset aspect type by using the aspect:dataplex-types.global.dataform-code-asset.type=DATA_CANVAS query in an aspect-based filter.

For instructions about how to search for assets in Dataplex, see Search for data assets in Dataplex in the Dataplex documentation.

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:

In the Google Cloud console toolbar, click Submit feedback.
Optional: To copy the DAG JSON information to provide additional context to your feedback, click Copy.
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 datacanvas-feedback@google.com.

What's next

Learn how to write queries with Gemini assistance.
Learn how to create notebooks.