GSP1257

Overview

Imagine you're a detective trying to solve a mystery, but instead of clues, you have multiple, massive spreadsheets of data.

You are a new data analyst for Data Beans, a company specializing in data collection, analytics, and insights for mobile coffee truck companies throughout your country. It is your first week at the company, and you have been tasked with exploring the company's data related to the coffee trucks, menu, and orders. Your onboarding buddy recommends that you use BigQuery with the table explorer and data insight features to learn about the data and gain insights from it. These features will help you to get started with exploring the company's data and gain insights from it without having to write SQL queries from scratch.

Objectives

In this lab, you learn how to:

• Generate data insights on the order item table.
• Use the table explorer with the location table and generate basic queries.
• Query the order item table without SQL code.

Once you complete these objectives, you will also review the menu and order tables using either of these features as an open activity.

Finally, you will have time to reflect on what you have learned in this lab and consider how you could apply the table explorer and data insights features to your own use cases 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. Generate data insights on the order item table

In this task, you will enable data insights on the order_item table within the coffee_on_wheels dataset.

Data insights is a tool for anyone who wants to explore their data and gain insights without writing complex SQL queries.

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

2. Click DONE on the welcome pop-up.

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

4. Expand the coffee_on_wheels dataset. You see the order_item table.

5. Click the order_item table. You see the order_item schema displayed. Review the details of the schema.

6. Click the INSIGHTS tab. You see a message stating "Insights have not yet been generated." This is normal, because the insights have never been generated in your lab environment.

7. Click the GENERATE INSIGHTS button.

8. From the Region dropdown select region and click GENERATE. Gemini will now generate insights for the order_item table.

   Note: It will take a few minutes to generate insights for the table, this is normal. Wait until the insights are generated to check completion of this task. You will return to insights for the order_item table later.

Click Check my progress to verify the objective. Generate data insights on the order item table.

Task 2. Use table explorer to review details of the location table

While you are waiting for the insights to be generated, you will use the table explorer feature of BigQuery to review the location table included with the coffee_on_wheels dataset. You will also build a basic query with the table explorer to find all fields associated with Coffee Cart Connection, Empire Espresso, and Street Sips trucks.

Access the table explorer

1. In the Explorer panel, click the location table. You see the location table schema displayed. Review the details of the schema.

2. Click the TABLE EXPLORER tab. Notice how the BigQuery studio page changes where you have the Distinct Values section at the top and the Generated Query section at the bottom, with the query below.

   SELECT * FROM `{{{project_0.project_id|set at lab start}}}.coffee_on_wheels.location`;

   You will work in the Distinct Values section. When you add fields using the SELECT FIELDS button, you will see the query change in the Generated Query section.

Select fields and build a basic query

1. Click SELECT FIELDS. You see all the fields in the table displayed.

2. Select all the fields and click SAVE. Now you see an interactive card for each field. Interactive cards show the most common values for each field in the dataset. The values in each card can be used to modify the query. However, you can only use one card as a filter (Where clause) in your SQL statement.

3. You are going to create a new query using the location_name card. Click:

   • Coffee Cart Connection
   • Street Sips
   • Empire Espresso Explorer
   Note: Once you have selected these values, BigQuery will indicate that "This script will process 5.7 KB when run." This indicates how much data the script will process when you run the query.

   Also notice that the query is:

   SELECT `city_id`, `company_id`, `location_id`, `location_name`, `location_type` FROM `{{{project_0.project_id|set at lab start}}}.coffee_on_wheels.location` WHERE (`location_name` IN ('Coffee Cart Connection', 'Empire Espresso Explorer', 'Street Sips'));

4. Click APPLY. Notice how the other values in the other cards change.

5. Click COPY TO QUERY.

6. A new Untitled query tab opens in BigQuery studio.

7. Click RUN. The result is 3 rows with the city_id, company_id, location_id, location_name, and location_type of each of these trucks.

   Congratulations! You have just written your first query with table explorer without using SQL code.

In summary, table explorer is a tool for getting started with data exploration in BigQuery, especially if you're new to SQL or want a quick way to understand your data.

Here are some key things to remember about table explorer:

• It's a visual tool to explore one table at a time.
• It doesn't support complex operations like joins across multiple tables.
• It generates basic SQL queries. For example, you cannot create complex WHERE clause statements including operands like AND or OR.
• It doesn't provide AI-powered assistance for complex queries.

Time to reflect

1. Considering your data and use cases for BigQuery, how would you use the table explorer feature?

Click Check my progress to verify the objective. Use table explorer to review details of the location table.

Task 3. Query the order item table without code

The insights you generated in an earlier task are now ready. In this task, you will use a prompt generated from these insights to query the order_item table without using code.

Select an insight and run the query associated with it

1. In the Explorer panel, click the order_item table. Review the schema with the associated fields.

2. Click INSIGHTS. Remember: earlier, you generated the insights, and you should see these insights listed at this point.

   Note: Each insight is in the form of a question starting with who, what, when, where, and how, or with an action verb, like calculate, identify, or find.

3. Review the list of insights, and search for an insight similar to the one below:

   What is the total revenue generated from each menu item? Note: This insight may be generated using phrasing like "This query calculates the total revenue generated by each menu item." Note: The insights are generated by Gemini and, because of this, you may not have an insight identical to the one above. This is because Gemini predicts insights based on the data in the dataset and the trained model. So, with this said, search for something similar that has the total revenue for each menu item in the order_item table. Feel free to re-generate insights as needed by using the GENERATE INSIGHTS button if you want to work with this example prompt.

4. Once you find a similar insight, expand the insight to reveal the underlying SQL code for it. You should see a query similar to the one below:

   SELECT menu_id, SUM(item_total) AS total_revenue FROM `coffee_on_wheels.order_item` GROUP BY menu_id;

5. Click COPY TO QUERY. A new tab opens in BigQuery Studio. It is called "Untitled Query" with the question (the prompt that generated the query) and the query included.

6. Click RUN. The query will run, and you will see the result. Notice that the results include two fields: the menu ID and the total revenue generated for each menu item. This is helpful, but we don't quickly know which item has the most revenue, and the total revenue field includes extraneous decimal places. You can fix that again without SQL code.

Transform the query without code to include only two decimal places

1. Select the query.

2. To the immediate left of the SELECT statement, click , and then click Transform. You see a dialog with an empty text field and a GENERATE button. Here, you can transform the query based on natural language.

3. Enter the prompt below:

   Display total_revenue rounded to 2 decimal points.

4. Click GENERATE. You will see the original query with red background text and the modified query with green background text.

5. If you agree with the newly suggested query, click INSERT. The query is inserted back into the Untitled Query tab and should look like this:

   -- What is the total revenue generated by each menu item? -- Display total_revenue as 2 decimal points. SELECT menu_id, ROUND(SUM(item_total), 2) AS total_revenue FROM `{{{project_0.project_id|set at lab start}}}.coffee_on_wheels.order_item` GROUP BY 1;

6. Click RUN. Notice that the total_revenue field is now formatted where results only have two decimal places.

Transform the query to order results in descending order

1. Select the query.

2. To the immediate left of the SELECT statement, click and then click Transform.

3. Enter the prompt below:

   Order the total_revenue field in descending order.

4. Click GENERATE. You will see the original query with red background text, and the modified query with green background text.

5. If you agree with the newly suggested query, click INSERT. The query is inserted back into the Untitled Query tab and should look similar to:

   -- What is the total revenue generated from each menu item? -- Display total_revenue as 2 decimal points. -- Order the total_revenue field in descending order. SELECT menu_id, ROUND(SUM(item_total), 2) AS total_revenue FROM `{{{project_0.project_id|set at lab start}}}.coffee_on_wheels.order_item` GROUP BY menu_id ORDER BY total_revenue DESC;

6. Click RUN. Notice that the total_revenue field is now in descending order, with the menu items with the most total revenue displayed first.

Congratulations! You have successfully used data insights to select an insight and transform it without using SQL code.

In summary, BigQuery data insights is a tool for anyone who wants to explore their data and gain insights without writing complex SQL queries.

Here are some key things to remember about BigQuery insights:

• It's a helpful tool for exploring and understanding your data, especially if you're new to SQL or want to get started with data analysis.
• It uses Gemini to generate queries based on your data's metadata, making it easier to find relevant insights.
• It's a feature that can help you unlock your data's potential.

Time to reflect

1. Which insights did you find most useful with the order_item table?

2. Considering your data and use cases for BigQuery, how would you use the data insights feature?

Click Check my progress to verify the objective. Query the order item table without code.

Task 4. Review the menu and order tables

In this task, you explore the remaining menu and order tables in the coffee_on_wheels dataset and answer the questions below using your Lab Journal. Now that you have learned about the data insights and table explorer tools, we suggest you use them to answer these questions, or even write your own queries if you choose to do so. However, be mindful of the remaining time left for this lab. Once the clock gets to 5 minutes left, we suggest you confirm you have completed all progress checks to get credit for completing the lab.

1. Find the top three items with the highest average prices for each size.

   • Which table or tables would contain this?
   • Which tool would help you answer this question?

2. Find all orders from location_id 37.

   • Which table or tables would contain this information?
   • How many orders are there from this location?
   • Which tool would you use to find all the orders?

3. For your use cases, which tool, table explorer or data insights, would help you the most? Why?

Once you have answered the questions, feel free to review the Lab Journal Solutions.

Congratulations!

In this lab, you learned how to generate data insights, and use these insights to query the coffee_on_wheels dataset without code. You also learned how to use the table explorer to explore the location table and generate basic queries without code. Finally, you considered how to apply these features to your own data and use cases with BigQuery.

Next steps / learn more

Consider taking what you have learned in this lab and your journal responses and share them with your team. You can learn more with the links below:

• Data Insights
• Table Explorer
• 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 February 25, 2025

Lab last tested on February 25, 2025

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.