GSP1259

Overview

Imagine you are a data analyst and have been at Data Beans for a year. You have successfully completed many projects on your own and are taking on the responsibility of mentoring new data analysts because the company is growing. The team has already been using Gemini in BigQuery to generate SQL code, data insights, and table explorer. The team has found these helpful, especially with new datasets. However, the team needs a better collaboration tool for visualizing data and creating new, more complex queries that join tables.

You have heard that Data Canvas is a visual interface within Google Cloud BigQuery that simplifies data exploration and analysis. It allows users to interact with their data through point-and-click actions, eliminating the need for complex SQL queries. You can also collaborate with others by sharing canvases with others. Data Canvas appears to be a potential solution for the team's needs, and from what you have read, you want to get started using it, but unsure how to do this.

Objectives

In this lab, you learn how to use Data Canvas to:

• Join the menu, orders, and order item tables.
• Calculate the total revenue for all menu items in 2024.
• Create a bar chart displaying the top 10 items by total revenue.
• Identify two menu items generating the same revenue.
• Collaborate with others.

Finally, you have time to reflect on what you have learned in this lab and consider how you could apply Data Canvas to your data, use cases, and workflows by answering questions in your Lab Journal.

Setup and requirements

Before you click the Start Lab button

Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click Start Lab, shows how long Google Cloud resources are made available to you.

This hands-on lab lets you do the lab activities in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials you use to sign in and access Google Cloud for the duration of the lab.

To complete this lab, you need:

• Access to a standard internet browser (Chrome browser recommended).

Note: Use an Incognito (recommended) or private browser window to run this lab. This prevents conflicts between your personal account and the student account, which may cause extra charges incurred to your personal account.

• Time to complete the lab—remember, once you start, you cannot pause a lab.

Note: Use only the student account for this lab. If you use a different Google Cloud account, you may incur charges to that account.

How to start your lab and sign in to the Google Cloud console

1. Click the Start Lab button. If you need to pay for the lab, a dialog opens for you to select your payment method. On the left is the Lab Details pane with the following:

   • The Open Google Cloud console button
   • Time remaining
   • The temporary credentials that you must use for this lab
   • Other information, if needed, to step through this lab

2. Click Open Google Cloud console (or right-click and select Open Link in Incognito Window if you are running the Chrome browser).

   The lab spins up resources, and then opens another tab that shows the Sign in page.

   Tip: Arrange the tabs in separate windows, side-by-side.

   Note: If you see the Choose an account dialog, click Use Another Account.

3. If necessary, copy the Username below and paste it into the Sign in dialog.

   {{{user_0.username | "Username"}}}

   You can also find the Username in the Lab Details pane.

4. Click Next.

5. Copy the Password below and paste it into the Welcome dialog.

   {{{user_0.password | "Password"}}}

   You can also find the Password in the Lab Details pane.

6. Click Next.

   Important: You must use the credentials the lab provides you. Do not use your Google Cloud account credentials. Note: Using your own Google Cloud account for this lab may incur extra charges.

7. Click through the subsequent pages:

   • Accept the terms and conditions.
   • Do not add recovery options or two-factor authentication (because this is a temporary account).
   • Do not sign up for free trials.

After a few moments, the Google Cloud console opens in this tab.

Note: To access Google Cloud products and services, click the Navigation menu or type the service or product name in the Search field.

Task 1. Join the menu, orders, and order item tables

In this task, you use Data Canvas to find the menu, orders, and order item tables and join them, so that you create insights from them.

Find the menu, orders, and order item tables

1. In the Google Cloud console, on the Navigation menu, click BigQuery.

2. Click DONE on the Welcome dialog.

3. Click , to create a new data canvas for your project.

4. Select for the region.

5. Click SELECT. See the Untitled canvas tab appear. Notice how you can find a table with a natural language prompt, and access recently used tables, queries, and saved queries. You are going to use a natural language prompt to find the menu and order item tables by searching for the coffee_on_wheels dataset.

6. Enter coffee on wheels.

7. Click . The tables for the coffee_on_wheels dataset appear. You should see four tables: location, menu, orders, and order_items.

Note: Click the **Submit search query** button until the number of tables displayed is as expected. If the expected tables are not displayed, open the coffee_on_wheels dataset from the left pane and, for menu, orders, and order_items tables, select Query in > Current data canvas from the three dots. Then, click JOIN from any of the tables and select the remaining two. You can continue with the following subtask from step 3 with the Join these data sources prompt.

Join the menu, orders, and order item tables

Now that you have found the tables, you can join them.

1. Select the menu, orders, and order_item tables. Notice the JOIN button appears.

2. Click JOIN. You see Data Canvas join the three tables visually, and a new branch node is created. However, the join isn't complete yet. Notice a query like the one below is automatically created, and you have options to run or save it.

   SELECT * FROM `{{{project_0.project_id|set at lab start}}}.coffee_on_wheels.menu` AS t1, `{{{project_0.project_id|set at lab start}}}.coffee_on_wheels.orders` AS t2, `{{{project_0.project_id|set at lab start}}}.coffee_on_wheels.order_item` AS t3 LIMIT 10;

   You don't use this query, as it only selects items from the tables and lists 10 of them. You use the prompt included. Which says:

   Join these data sources

3. Click . You see a new query like the one below is generated.

   # prompt: Join these data sources SELECT menu.menu_id, menu.company_id, menu.item_name, menu.item_price, menu.item_description, menu.item_size, order_item.order_item_id, order_item.order_id, order_item.quantity, order_item.item_price, order_item.item_total, orders.location_id, orders.customer_id, orders.order_datetime, orders.order_completion_datetime FROM `{{{project_0.project_id|set at lab start}}}.coffee_on_wheels.menu` AS menu INNER JOIN `{{{project_0.project_id|set at lab start}}}.coffee_on_wheels.order_item` AS order_item ON menu.menu_id = order_item.menu_id INNER JOIN `{{{project_0.project_id|set at lab start}}}.coffee_on_wheels.orders` AS orders ON order_item.order_id = orders.order_id;

4. Click RUN.

5. Review the results and confirm the tables are joined. You see each menu item listed with key fields including item_name, item_price, order_datetime, and item_total. Use these fields within this new table to calculate total revenue for each item in 2024 in the next task.

Click Check my progress to verify the objective. Join the menu, order, and order item tables.

Task 2. Calculate the total revenue for all menu items in 2024

In this task, you use the joined tables to calculate the total revenue for all menu items in 2024 using a gemini prompt resulting in a SQL query.

Beneath the results of the join query, you see options to branch another node in your data canvas, including QUERY THESE RESULTS, VISUALIZE, AND JOIN.

• QUERY THESE RESULTS: can be used to create a query from the resulting joined table.
• VISUALIZE: can be used to create charts from the data in the joined table.
• JOIN: can be used to join this table with another table.

1. Click QUERY THESE RESULTS. A new node is generated in your data canvas. Notice you can enter a prompt, or you can manually write new SQL code.

2. Enter the following prompt.

   From the joined table, only consider orders from 2024. Calculate the total revenue for each menu item. In the results, display the menu_id, the item name, the item size, and the total revenue (rounded as only two decimal places). Order results with total revenue in descending order.

3. Click . You see a new query is generated like the one below.

   # prompt: From the joined table, only consider orders from 2024. Calculate the total revenue for each menu item. In the results, display the menu_id, the item name, the item size, and the total revenue (rounded as only two decimal places). Order results with total revenue in descending order. SELECT t1.menu_id, t1.item_name, t1.item_size, ROUND(SUM(t1.item_total), 2) AS total_revenue FROM `SQL` AS t1 WHERE EXTRACT(YEAR FROM t1.order_datetime) = 2024 GROUP BY 1, 2, 3 ORDER BY total_revenue DESC;

4. Click RUN.

5. Review the results. Notice that the menu_id, item_name, item_size, and total_revenue fields are included.

Time to reflect

1. Which item is ranked number 2 in highest revenue? What is the item_size reported?

Click Check my progress to verify the objective. Calculate the total revenue for all menu items in 2024

Task 3. Create a bar chart displaying the top 10 items by total revenue

In this task, you use the results from the total revenue calculation to create a bar chart of the top 10 items by total revenue.

Identify the top 10 items by total revenue

1. Click QUERY THESE RESULTS. A new node is generated in your data canvas.

2. Enter the following prompt.

   Identify the top 10 items by total revenue and include the menu_id, item_nam, item_size, and total_revenue fields.

3. Click . You see a new query is generated like the one below.

   SELECT t1.menu_id, t1.item_name, t1.item_size, t1.total_revenue FROM `SQL` AS t1 ORDER BY t1.total_revenue DESC LIMIT 10;

4. Click RUN.

5. Review the results. Notice that now only the top 10 items by total revenue are shown.

Create the bar chart

1. Click VISUALIZE.

2. Select Create a bar chart. A new node is generated in your data canvas. Notice how a chart is created automatically from the "Create bar chart" prompt and the chart is displayed with all menu items included with their total revenue.

   This is helpful, but notice how the items aren't in order from highest to lowest total revenue. Nor are the item sizes factored into the chart. You can fix that.

3. Replace the existing prompt by entering the following prompt.

   Create a vertical bar chart displaying total revenue. Include the item name on the x-axis and the total revenue on the y-axis in the results. Start with the location with the highest revenue. Stack the results by item size.

4. Click . Notice how the top ten items are displayed now in order and item size is factored into total revenue with color-coding.

View the chart summary

Now that the chart is created, you also get a summary. To view the chart summary:

1. At the bottom of the chart, click Generate Summary. A chart summary is generated like the one below:

• "Coffee-infused Avocado Toast" emerged as the top revenue generator, reaching $675.48.
• A majority of the menu items (5 out of 10) do not have size variations and fall under the "n/a" size category.
• Items without size options contribute significantly to the overall revenue.
• For items with size options ("Brewhaha Bonanza" and "Java Journey"), the "large" size demonstrates higher popularity.

Time to reflect

1. Compare the bar chart to the raw data in the results for the query you generated in task 2.

   Why is Brewhaha Bonanza displayed as the highest total revenue item in the chart and not Coffee-infused Avocado Toast?

Click Check my progress to verify the objective. Create a bar chart displaying the top 10 items by total revenue

Task 4. Identify two menu items generating the same revenue

In this task, you identify two menu items generating the same revenue from the total revenue calculation you completed in an earlier task.

1. Return to the node for the total revenue calculation.

2. Move your cursor so that it hovers over the bottom center of the node. You see the Branch another node options appear.

3. Click QUERY THESE RESULTS. A new node is generated in your data canvas.

4. Enter the following prompt.

   Find two items with the same total revenue. Within the results, display the item names, item size, and total revenue. Limit your response to only two items.

5. Click . You see a new query like the one below is generated .

   # prompt: Find two items with the same total revenue. Within the results, display the item names, item size, and total revenue. Limit your response to only two items. SELECT t1.item_name, t1.item_size, t1.total_revenue FROM `SQL 1` AS t1 WHERE t1.total_revenue IN( SELECT t2.total_revenue FROM `SQL 1` AS t2 GROUP BY 1 HAVING COUNT(t2.total_revenue) > 1 ) LIMIT 2;

6. Click RUN.

Time to reflect

Review the results. Notice two results are displayed, with the item names, size, and total revenue for each.

1. Which two items are displayed?

2. What are the item sizes?

3. Do the revenues match?

4. Considering your data and use cases, how would you use data canvas to visualize and design queries?

Click Check my progress to verify the objective. Identify two menu items generating the same revenue.

Task 5. Collaborate with others

In this task, you act as two different users, the owner of the data canvas and another user you want to share the data canvas with. First, you review the roles assigned to the owner and the other user within this lab environment. Then, you save and share the data canvas you just created. You also export the data canvas to a notebook. Finally, you access the data canvas as the other user.

Roles required for using and sharing data canvases

1. In the Google Cloud console, on the Navigation menu, click IAM & Admin. You see the list of principals along with the assigned roles.

2. Find the principal.

   Note: This is the owner principal, also this is the user you have been using up until this point in this lab.

   As you can see in IAM, this user has the following roles:

   • BigQuery Admin
   • Gemini for Google Cloud User
   • Owner
   • Service Usage Viewer
   • Viewer

3. Find the principal.

   Note: This is the other principal you use for testing the data canvas sharing feature.

   As you can see in IAM, this user has the following roles:

   • BigQuery Data Editor
   • BigQuery Studio User
   • Gemini for Google Cloud User
   • Code Editor
   • Viewer

See Permissions Required Roles for data canvas for more information.

Save and share a data canvas (as the owner)

1. Return to your data canvas in BigQuery.

2. Find the Save button at the top of the Canvas. Click the down arrow. You see two options, Save and Save As.

3. Click Save as. The Save dialog appears.

4. Enter a name, Two items with same total revenue

5. Keep the default region, as this was assigned to you when you launched the lab.

6. Click SAVE. You notice that the data canvas is now saved and listed in the Explorer panel in the Shared data canvases section.

Export a data canvas to a notebook (as the owner)

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

1. Within the data canvas, click Export as notebook.

2. In the Save Notebook pane, enter the name for the notebook (data_canvas_export) and the region where you want to save it ().

3. Click Save. The notebook is created successfully.

4. To view the created notebook, expand the Notebooks section of the explorer panel.

5. Click on the data_canvas_export notebook.

Note: Given the current permissions for the owner and the other user, only the owner can export. In other words, you need to supply the appropriate permissions to enable the export feature.

Access a Data Canvas (as another user)

Now you access the data canvas as another user.

1. Here, in the lab guide, right-click on Open Google Cloud console.

2. Select Open link in incognito window.

3. Use Username 2 username and password. Access the Google Cloud console the same way you did at the start of this lab.

4. In the Google Cloud console, on the Navigation menu, click BigQuery.

5. Click DONE on the Welcome dialog.

6. In the Explorer panel, expand the project. You see the coffee_on_wheels dataset at the bottom of the list.

7. Expand Data canvases.

8. Expand Shared data canvases. You see the Two items with same total revenue data canvas listed.

9. Click the Two items with same total revenue data canvas. You see the data canvas displayed.

From here, the other can review the data canvas to understand the workflow you designed for this business problem. If needed, they can even modify the canvas to either troubleshoot an issue or augment it as needed, based upon the permissions they have.

Time to reflect

1. Considering your data and use cases, how would you use data canvas to collaborate with your team?

2. How would you manage access to the data canvases your team creates?

Click Check my progress to verify the objective. Export the data canvas to a notebook.

Congratulations!

You have successfully used data canvas to find and join tables from the coffee_on_wheels dataset. You also successfully calculated the total revenue for all menu items in the dataset, and visualized these using a bar chart directly in data canvas. Then, you successfully found two items with the same revenue from this calculation. Finally, you successfully used IAM roles in data canvas to save a data canvas in your project and share it with others.

Consider what you have learned in this lab, and share it with others on your team to identify how you can collaborate with each other using data canvas.

Next steps / learn more

• Analyze with BigQuery data canvas
• Understanding BigQuery data canvas: how to easily transform data into insights with AI
• Gemini Models
• Generative AI

Google Cloud training and certification

...helps you make the most of Google Cloud technologies. Our classes include technical skills and best practices to help you get up to speed quickly and continue your learning journey. We offer fundamental to advanced level training, with on-demand, live, and virtual options to suit your busy schedule. Certifications help you validate and prove your skill and expertise in Google Cloud technologies.

Manual last updated on October 9, 2024

Lab last tested on October 9, 2024

Copyright 2025 Google LLC. All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.