Overview

Looker is a modern data platform in Google Cloud that lets business users analyze and visualize data interactively. LookML developers curate the data used by business users by creating new fields, tables, and views to customize and organize data.

In Looker, LookML developers can use derived tables to create new tables that are not already defined in the underlying database. For example, as a LookML developer, you can create derived tables to summarize details from existing tables, such as order details for each user in an ecommerce dataset.

In this lab, you learn how to create both types of derived tables in LookML: SQL derived tables and native derived tables. You also learn how to define new columns that do not already exist in the underlying data and add them to native derived tables. Last, you learn how to persist derived tables in LookML, so that they can be written back to the underlying database.

For this lab, a project called qwiklabs-ecommerce has been created for you in LookML. This project is based on a mock ecommerce dataset containing information on orders, products, and users.

Learn more about LookML modeling in the Writing LookML section of Looker's documentation.

Objectives

In this lab, you will create and persist derived tables in LookML.

• Create SQL derived and native derived tables in LookML.
• Join new views for derived tables to an Explore.
• Add new columns to native derived tables in LookML.
• Persist derived tables in LookML.
• Use the Explore interface to view changes in the modified LookML project.
• Send LookML changes from a development branch to production.

Setup

For each lab, you get a new project and set of resources for a fixed time at no cost.

1. Make sure you have signed into Qwiklabs using an incognito window.

2. Note the lab's access time (for example, 2:00:00 hrs) and make sure you can finish in that time block.

Note: There is no pause feature. You can restart the lab if needed, but you will start at the beginning of the lab.

3. When ready, click .

A new panel will appear with the temporary credentials that you must use for this lab.

If you need to pay for the lab, a pop-up will open for you to select your payment method.

4. Note your lab credentials. You will use them to sign in to the Looker instance for this lab.

Note: If you use other credentials, you will get errors or incur charges.

5. Click Open Looker.

Tip: Open Looker in a new tab or a separate window, so you can see both the lab instructions and the Looker instance.

6. Enter the provided username and password.

Important: You must use the credentials from the Connection Details panel on this page. Do not use your Qwiklabs credentials. If you have your own Looker account, do not use it for this lab.

7. Click Log In.

After a successful login, you will see the Looker instance for this lab.

Note: Do not click End Lab unless you are finished with the lab or want to restart it. This clears your work and removes the project. Note: For this lab, you can ignore warnings for Can't connect to remote Git service. You can proceed with the lab instructions to complete the tasks locally without pushing your changes to the Git repository.

To complete the tasks locally, be sure to turn on Development Mode as outlined in the next step.

8. On the bottom left of the Looker User Interface, click the toggle button to enter Development mode.

Task 1. Create a new SQL derived table summarizing order details for each user

In LookML, you can define derived tables using either SQL queries to define a SQL derived table, or Explore queries to define a native derived table.

In this task, you write a SQL query to define a new SQL derived table that summarizes order details for each user, including the total number of orders and total revenue for each user. Then, you create a new view file for the SQL derived table in the qwiklabs-ecommerce project.

Define a new derived table using a SQL query

1. If it is not already enabled, enable development mode. On the bottom left of the Looker User Interface, click the toggle button to enter Development mode.

2. On the left side navigation menu of the Looker User Interface, click Develop. Then, click SQL Runner.

3. In the SQL Query window, add the following query:

SELECT order_items.user_id AS user_id ,COUNT(order_items.order_id) AS order_count ,SUM(order_items.sale_price) AS order_revenue FROM cloud-training-demos.looker_ecomm.order_items GROUP BY user_id LIMIT 10

4. Click Run to see the results.

The results indicate that the query successfully summarizes all orders for each user. Specifically, the query is returning the user ID, count of orders, and total revenue from orders for each user.

Notice that the LIMIT clause is used to reduce the amount of data returned during this test; you will remove the LIMIT clause in an upcoming step when you create a new view file for the SQL derived table.

Create a new view file for the SQL derived table

1. Click on the settings gear icon () next to Run (top right of page), and select Add to Project.

2. For Project, select qwiklabs-ecommerce.

3. For View Name, type user_order_details, and click Add.

You are redirected to the Looker Integrated Development Environment (IDE) to review the newly created view file for your SQL derived table.

Notice that the new view file for the user_order_details view has been created outside of the views folder.

4. Click user_order_details.view and drag it under the views folder.

5. Click on the arrow next to views to see the list of views.

6. Click user_order_details.view to see the view file for the SQL derived table.

Notice that Looker auto-generates a dimension for each column in the SELECT clause of the SQL query as well as a new count measure.

In the next steps, you modify the view file to remove the LIMIT clause which is no longer desired, hide the new count measure, and add a primary key for the view.

7. Delete the code line for LIMIT 10 from the sql parameter.

8. In the measure: count definition, add a new line before type: count, and type hidden: yes.

9. In the dimension: user_id definition, add a new line before type: number, and type primary_key: yes.

10. Click Save Changes.

This SQL derived table is now ready to be joined to an Explore, modified with more dimensions and measures, etc. In the next steps, you will send your LookML changes to production, so that other LookML developers can use the view file for your SQL derived table and finalize it as needed.

Send LookML changes from development branch to production

1. Click Validate LookML and then click Commit Changes & Push.

2. In the Commit window, add a message to specify the changes you made, and click Commit.

3. Click Deploy to Production.

In the next task, you will recreate the table as a native derived table using existing LookML dimensions and measures. Then, you will join the native derived table to an Explore, so that you can test the new table in the Explore and make it available to business users.

Click Check my progress to verify the objective. Create SQL derived table

Task 2. Create a new native derived table summarizing order details for each user

In this task, you use an Explore query to recreate the derived table from Task 1 as a native derived table. Then, you create a new view file for the native derived table in the qwiklabs-ecommerce project.

Define a new native derived table from an Explore query

1. On the left side navigation menu of the Looker User Interface, click Explore.

2. Under E-Commerce Training, click Order Items.

3. Click the arrow next to Order Items.

The available dimensions and measures will be listed in the data panel for Order Items.

4. Under Order Items > Dimensions, click User ID.

5. Under Order Items > Measures, click Order Count.

6. Under Order Items > Measures, click Total Revenue.

7. Click Run to see the results.

The Explore query returns the User ID, count of orders, and total revenue from orders for each user.

8. Click on the settings gear icon () next to Run (top right of page), and select Get LookML.

9. Select the tab for Derived Table.

10. Copy the LookML code for the derived table.

In the next section, you paste the LookML code for the derived table into a new view file.

Create a new view file for the native derived table

1. Open a new Looker window in a new tab.

2. On the left side navigation menu of the Looker User Interface, click Develop.

3. Under Projects, click on qwiklabs-ecommerce.

4. Next to File Browser, click the Add file or folder icon ().

5. Select Create View.

6. For file name, type user_order_summary.

7. Click Create.

Notice again that the new view file for the user_order_summary view has been created outside of the views folder.

8. Click user_order_summary.view and drag it under the views folder.

9. Click on the arrow next to views to see the list of views.

10. Click user_order_summary.view to see the view file for the native derived table.

11. Delete all of the auto-generated LookML in the view file.

12. Paste the LookML code you copied for the native derived table.

13. Replace the auto-generated view name (e.g. add_a_unique_name_1620763463) with user_order_summary.

14. Click Save Changes.

In the next task, you will join this new view to an Explore, so that the view is accessible to business users.

Task 3. Join a new view for a derived table to an Explore

For business users to view the data in a derived table, the view containing the derived table definition must either be added as the base view of an Explore or be joined to an existing Explore. The steps to join a view for a derived table to an Explore are the same whether the view is created for a SQL or native derived table.

In this task, you join the view for the native derived table to the order_items explore definition in the model file of the qwiklabs-ecommerce project. Then, you review the data and the generated SQL from the native derived table in the Order Items Explore.

Join the view for the native derived table to the Explore

1. On the left side navigation menu of the Looker User Interface, click Develop.

2. Under Projects, click on qwiklabs-ecommerce.

3. Click the arrow next to models to see the model files.

4. Click training_ecommerce.model to see the model file for the qwiklabs-ecommerce project.

5. In the explore: order_items definition, on a new line before join: users, define a new join using the following code:

join: user_order_summary { type: left_outer sql_on: ${order_items.user_id} = ${user_order_summary.user_id};; relationship: many_to_one }

The sql_on parameter identifies the join field as user_id. The relationship parameter identifies that there are potentially many instances of an user_id in order_items but only one instance of each user_id in user_order_summary, which is organized as one summary row for each user.

6. Click Save Changes.

Review the data and generated SQL from the native derived table in the Explore

1. Open a new Looker window in a new tab.

2. On the left side navigation menu of the Looker User Interface, click Explore.

3. Under E-Commerce Training, click Order Items.

4. Click the arrow next to User Order Summary.

The available dimensions are listed in the data panel for User Order Summary. Notice that there are no measures for User Order Summary, and that the measures for Order Count and Total Revenue selected from Order Items are now dimensions in User Order Summary.

5. Under User Order Summary > Dimensions, click User ID.

6. Under User Order Summary > Dimensions, click Order Count.

7. Under User Order Summary > Dimensions, click Total Revenue.

8. Click Run to see the results.

9. Click on the SQL tab to see the SQL query generated by Looker.

Notice that the SQL contains a common table expression (CTE) identified by a WITH clause. This native derived table is considered ephemeral because it is generated at runtime as a CTE.

Derived tables can also be persisted, which means that they are stored in the underlying database. In a later task, you will persist this derived table, so that it can be written back to the database.

10. Close the browser tab that you opened in step 1 to review the Explore.

Send LookML changes from development branch to production

1. Navigate back to the user_order_summary.view file.

2. Click Validate LookML and then click Commit Changes & Push.

3. In the Commit window, add a message to specify the changes you made, and click Commit.

4. Click Deploy to Production.

Click Check my progress to verify the objective. Create native derived table

Task 4. Add a new column to a native derived table

A key benefit of native derived tables is that you can leverage additional parameters to easily add new columns, including ones that do not already exist in the underlying data. For example, the derived_column parameter can be used to create a new column that does not already exist in the Explore used to define the native derived table.

In this task, you add a derived column to the native derived table that you created in a previous task. The new derived column will store the average order revenue per customer, which can be calculated from existing columns in the native derived table (i.e., total revenue per customer divided by the number of orders per customer).

After you create the derived column, you define a new dimension for the derived column in the user_order_summary view, so that business users can query the data in the Explore.

Add a derived column in the view file for the native derived table

1. On the left side navigation menu of the Looker User Interface, click Develop.

2. Under Projects, click on qwiklabs-ecommerce.

3. Click on the arrow next to views to see the list of views.

4. Click user_order_summary.view to see the view file for the native derived table.

5. In the user_order_summary derived table definition, on a new line after column: total_revenue {}, define a new derived column using the following code:

derived_column: average_order_revenue { sql: total_revenue / order_count ;; }

6. Locate the dimension for total_revenue.

7. On a new line after the dimension for total_revenue, define a new dimension for average_order_revenue using the following code:

dimension: average_order_revenue { value_format: "$#,##0.00" type: number }

8. Click Save Changes.

Review the data and generated SQL from the updated native derived table in the Explore

1. Open a new Looker window in a new tab.

2. On the left side navigation menu of the Looker User Interface, click Explore.

3. Under E-Commerce Training, click Order Items.

4. Click the arrow next to User Order Summary.

The available dimensions are listed in the data panel for User Order Summary.

5. Under User Order Summary > Dimensions, click User ID.

6. Under User Order Summary > Dimensions, click Order Count.

7. Under User Order Summary > Dimensions, click Total Revenue.

8. Under User Order Summary > Dimensions, click Average Order Revenue.

9. Click Run to see the results.

10. Click on the SQL tab to see the SQL query generated by Looker.

Notice that the CTE now includes a calculation for a new column named average_order_revenue, even though the underlying Explore used to create the native derived table (i.e. the Order Items Explore) did not originally contain this column.

For additional information on derived columns, review the Looker documentation about creating derived columns for calculated values.

11. Close the browser tab that you opened in step 1 to review the Explore.

Send LookML changes from development branch to production

1. Navigate back to the user_order_summary.view file. Click Validate LookML and then click Commit Changes & Push.

2. In the Commit window, add a message to specify the changes you made, and click Commit.

3. Click Deploy to Production.

Click Check my progress to verify the objective. Add a derived column in the view file for the native derived table

Task 5. Persist a derived table

In the previous tasks, you created ephemeral derived tables, meaning that the tables are generated at run-time, rather than stored in the underlying database. In this task, you will persist a derived table, so that it is written back to the underlying database. Note that the steps to persist a derived table are the same whether it is a SQL derived or native derived table.

To persist a derived table, you need to add one of the following parameters to the definition of the derived table:

• datagroup_trigger
• sql_trigger_value
• persist_for

To learn more about these parameters, review the section on creating persistent derived tables from the Derived tables in Looker documentation.

In this task, you persist the native derived table using the parameter datagroup_trigger, which rebuilds a persistent derived table based on a pre-defined datagroup (or Looker caching policy) defined in the model file.

For the native derived table named user_order_summary, you add training_ecommerce_default_datagroup as the datagroup_trigger, so that the persistent derived table will rebuild using the datagroup.

The training_ecommerce_default_datagroup is defined within training_ecommerce.model and includes a parameter to rebuild all objects defined in the model every hour.

Persist the native derived table

1. On the left side navigation menu of the Looker User Interface, click Develop.

2. Under Projects, click on qwiklabs-ecommerce.

3. Click on the arrow next to views to see the list of views.

4. Click user_order_summary.view to see the view file for the native derived table.

5. In the derived_table definition, add a new line after the closing bracket (}) for explore_source: order_items, and paste:

datagroup_trigger: training_ecommerce_default_datagroup

6. Click Save Changes.

Review the generated SQL for the persistent derived table in the Explore

1. Open a new Looker window in a new tab.

2. On the left side navigation menu of the Looker User Interface, click Explore.

3. Under E-Commerce Training, click Order Items.

4. Click the arrow next to User Order Summary.

The available dimensions are listed in the data panel for User Order Summary.

5. Under User Order Summary > Dimensions, click User ID.

6. Under User Order Summary > Dimensions, click Order Count.

7. Under User Order Summary > Dimensions, click Total Revenue.

8. Under User Order Summary > Dimensions, click Average Order Revenue.

9. Click Run to see the results.

10. Click on the SQL tab to see the SQL query generated by Looker.

After a few seconds, notice that the WITH clause has been replaced by a regular SQL SELECT statement. There is also a message saying that Looker will use the existing user_order_summary table.

This derived table is no longer generated as a CTE at run-time; rather, the derived table has been persisted using a datagroup and has been written back to the underlying database.

11. Close the browser tab that you opened in step 1 to review the Explore.

Send LookML changes from development branch to production

1. Navigate back to the user_order_summary.view file. Click Validate LookML and then click Commit Changes & Push.

2. In the Commit window, add a message to specify the changes you made, and click Commit.

3. Click Deploy to Production.

End your lab

When you have completed your lab, click End Lab. Google Cloud Skills Boost removes the resources you’ve used and cleans the account for you.

You will be given an opportunity to rate the lab experience. Select the applicable number of stars, type a comment, and then click Submit.

The number of stars indicates the following:

• 1 star = Very dissatisfied
• 2 stars = Dissatisfied
• 3 stars = Neutral
• 4 stars = Satisfied
• 5 stars = Very satisfied

You can close the dialog box if you don't want to provide feedback.

For feedback, suggestions, or corrections, please use the Support tab.

Copyright 2022 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.