In this chapter, we’ll be working with the Looker (Google Cloud core), built on Google Cloud infrastructure – therefore, you have nothing to install. You just need internet access, a browser, and a Google (personal or professional) account to access the Google Cloud console.

In this recipe, you will discover how to get access to the Looker environment and start working in it. As mentioned in the introduction, we’ll focus on Looker (Google Cloud core), which is available from the Google Cloud console.

How to do it...

The steps for this recipe are as follows:

1. Let’s start by going to the Google Cloud website: https://cloud.google.com/?hl=en. Once on the website, check whether you’re connected with your Gmail account by checking your Gmail profile photo in the top-right corner. Make sure that you’re connected with the email account you want to use for your Looker tests. If all is good, in the same top-right corner, click on Console (Figure 1.1).

Figure 1.1 – Google Cloud home page

2. After clicking on Console, you will be redirected to the Google Cloud environment where you will need to choose your country, read and accept the Terms of Service, and click Agree and Continue.
3. Note that you might be redirected to a Console page in a different language (your local language, for example). To follow the book’s guidelines easily, it is preferable to switch to the English version – you can do that in your Google account settings (https://myaccount.google.com/) or, on the first Google Cloud page where you clicked on Console, there was an option to choose the language before going to the console (check the top-right corner in Figure 1.1).
4. If it’s your first time working within Google Cloud, you will need to create your first project by clicking on Create Project on the right (on the Google Cloud Welcome window after clicking on Agree and Continue). If you already have projects in your Google Cloud environment, you might still want to create a new one for your Looker experiment.
5. After clicking on Create Project, you will need to choose a project name (or keep the default one) and organization, if you have one created – you might have one if you’re using your professional account. If not, keep the default No Organization option. Click CREATE to finalize the project creation (Figure 1.2). A Google Cloud project is a way to organize your resources and applications in Google Cloud Platform (GCP). It is a billing and access control entity. By creating a project, you can group your GCP resources together and control who has access to them. You can always switch between your projects in the top-left corner.

Figure 1.2 – Project creation

6. When you’re on the Welcome page with your project chosen in the top-left corner, you can continue the activation of your free trial for your Google Cloud (GC) environment by clicking on START FREE in the top-right corner.
7. On the Step 1 of 2 Account Information page (Figure 1.3), choose your country, and your organization or needs (there is an Other option if you don’t know yet), and then read and accept the Terms of Service.

Figure 1.3 – Account information

8. On the Step 2 of 2 page (Figure 1.4), you will need to provide your billing information. There is no autocharge after the free trial ends. Google only asks you for your credit card to make sure you are not a robot. If you use a credit or debit card, you won’t be charged unless you manually upgrade to a paid account.

Figure 1.4 – Payment information

9. Google will verify your billing information; this is usually done through your banking application.
10. Now, you’ll need to fill in a small questionnaire to help GC serve you better.
11. Let’s finally get to Looker! Search for Looker either in the Search bar or by clicking on the hamburger button on the left (Figure 1.5). You can pin the Looker service in the hamburger menu to have it always at the top of your list.

Important note

Currently, to initiate a Looker free trial within your GC console, you’ll need to contact Looker sales directly. You can do this through the following form: https://cloud.google.com/resources/looker-free-trial.

Figure 1.5 – Search bar

12. When you are on the Looker Welcome page, click on CREATE AN INSTANCE (Figure 1.6) to create your Looker instance. Important: To avoid any billing surprises, confirm your free Looker trial is active before creating your instance. You can check the trial status by contacting a Looker sales representative. A Looker instance is a dedicated, isolated environment for running Looker. Looker instances allow users to connect to data sources, model data, explore data, visualize data, share data, and embed analytics.

Figure 1.6 – Looker Welcome page

13. If you see a popup that says Enable required APIs, click on Enable. The Looker (Google Cloud core) API is a RESTful API that allows you to programmatically interact with your Looker instance.

    On the page that will open after you enable the required APIs or after you click on CREATE AN INSTANCE, choose your instance name, then add your OAuth Application Credentials details to access your instance (Figure 1.7). You will need to create your OAuth application credentials in advance. To do this, open a new tab, go to the GC console, click on the menu button in the top - left corner and search for API & Services and click on it ->then click on Credentials -> then click on Create credentials (choose the Web application option) -> then choose OAuth client ID. It might ask you to create an OAuth consent screen where you will need to provide the app name and your email address in the Support and Developer section and keep the default values for the rest.

14. Once your client ID is created, go back to your instance creation form and add your newly created credentials there. Finally, choose a region (if you can’t find your country, choose the one that is closer to you geographically) and click CREATE. The creation can take up to 1 hour.

Important note

After the free trial, your instance may be automatically converted to a paid Looker instance. Please confirm this with your Looker sales representative.

Figure 1.7 – Looker instance creation

15. When the instance is created, you will see your Looker instance link in the Instance URL column (Figure 1.8).

Figure 1.8 – Instance URL

Troubleshooting Instance URL Errors

To avoid getting an error when clicking on the instance URL (such as, for example, Error 400: redirect_uri_mismatch), check the following elements:

• You connected with the right Google account (when you’re connected with multiple Gmail accounts, the one that is used when you open the instance URL in the new tab might not be the one that has access to your Looker and GC environment)
• In APIs & Services, make sure you created your OAuth credentials for the Web application
• In APIs & Services, make sure you added your Looker instance URL plus /oauth2callback in Authorized redirect URIs (Figure 1.9)
• In APIs & Services, make sure you added looker.app as the authorized domain on your OAuth consent screen (click Edit App to add it)

Figure 1.9 – Authorized redirect URIs

There’s more...

It is possible to get access to Looker through labs, as a way to test Looker without creating your own GC environment and without setting up a free trial. You can create an account on the Cloud Skills Boost website (https://www.cloudskillsboost.google/) and find free Looker quests and labs (make sure it is Looker and not Looker Studio). The labs on Cloud Skills Boost give you access to the sandbox environment with the data prepared for you. The labs give you a step-by-step exercise and teach you how to use Looker. The inconvenience is that you have limited time for your exercise. Additionally, you cannot work with your data and with your team on one project in the lab. These labs are only for training purposes and give you a good start in understanding the Looker environment.

For more in-depth exploration, you can combine the lab training and this book.

When you create your first Looker instance, you become its owner and administrator. You can then provide access to the rest of your team. Remember, by default, with the Standard edition, Looker gives you 12 free user allocations with the Standard version – 2 developers and 10 standard users. The Standard edition is tailored for small teams with up to 50 internal platform users.

For your free trial space, it is best to add only the colleagues who will actively participate in testing Looker with you.

How to do it...

To add your colleagues to your Looker free trial instance you will need to do the following:

1. In the GC console, go to the IAM & Admin section.
2. Click on the IAM tab.
3. Click on GRANT ACCESS (Figure 1.10).

Figure 1.10 – Cloud IAM

4. In New principals, add an email (should be a Google account) or multiple emails of your co-testers.
5. In Select a role, type Looker to filter, then assign your colleagues the same Looker role you have (for example, Looker Admin) to ensure they have the same level of access as you during the trial.
6. Send the Looker instance URL to the person that you provided access to (to find your instance URL, see Figure 1.8).
7. When they click on the link or copy and paste it into the browser, they will be asked to authenticate with their Google account.
8. Once authenticated, your colleagues will see the same Looker Welcome page you saw when first connected to Looker.

Important note

When managing user permissions in Looker, it’s important to grant access based on specific needs. While collaboration is key, admin access should be reserved cautiously. For most colleagues, assigning “Looker instance user” status is sufficient.

See also

• Other (more advanced) user roles and connection options will be discussed later in this book: Chapter 9, Administering and Monitoring Looker.

You’ll need to connect Looker to the data source to start working on your data models, visualizations, and so on. Looker supports over 30 dialects – therefore, it can connect to more than 30 types of databases and data warehouses. The full list of dialects is here: https://cloud.google.com/looker/docs/looker-core-dialects#supported-dialects-for. In this book, we will use the connection to BigQuery. BigQuery is Google’s fully managed, serverless data warehouse that enables scalable analysis over petabytes of data. BigQuery is quite known as well for its native connection with Google Analytics.

How to do it...

Let’s explore BigQuery first. The steps for this are as follows:

1. In your GC console, search for BigQuery in the search bar at the top of the console or in the navigation menu (represented by three horizontal lines) on the left side of the console and you should see your project name in the Explorer section (in our case, it’s lookerbook, but you might have a different name).

Figure 1.11 – BigQuery welcome page

2. In the Explorer section, click on the three dots near your project name (lookerbook in the preceding figure) and click on Create dataset (Figure 1.12). A dataset is like a folder that will contain your future data tables.

Figure 1.12 – Dataset creation

3. Name your dataset, choose US in Multi-region where your data will live, and keep everything else as it is, then click on CREATE DATASET (Figure 1.13).

Figure 1.13 – Dataset configurations

4. In this book, to avoid searching for data, we will work with BigQuery public datasets. BigQuery public datasets are datasets that are stored in BigQuery and made available to the general public through the Google Cloud Public Dataset Program. These datasets are provided by a variety of organizations, including government agencies, non-profit organizations, and businesses. You can load your own data into BigQuery (for example, click on three dots near your newly created dataset, click on Create table, and then Create table from). For more information on how to load your data into BigQuery, check this link: https://cloud.google.com/bigquery/docs/loading-data.
5. To work with the public dataset, you first need to add it to your Explorer section (to make it visible). The public dataset is not stored in your project; it is hosted by Google, so you won’t have to pay for storage. But you can create the table out of the public dataset table to store it in your project.
6. To add the public dataset to your BigQuery space, click on ADD in your Explorer section, then click on Star a project by name (Figure 1.14) and add bigquery-public-data.
7. Another option to add public datasets is to go to Additional sources in Figure 1.14, and from there, you can scroll to find public datasets.
8. You can then explore the different datasets available and click View dataset when there is one you find interesting – usually, after that, you will see the bigquery-public-data project pinned in Explorer with all the datasets in it.
   

Figure 1.14 – Adding a public dataset

9. Find the Google Analytics 4 (GA4) dataset in your starred bigquery-public-data project. It is not real GA4 data but it will give you an idea of how your GA4 data will look in BigQuery. Click on ga4_obfuscated_sample_ecommerce and then click on the events_(92) table (using the SCHEMA, DETAILS, and PREVIEW tabs in Figure 1.15).

Figure 1.15 – Google Analytics dataset

10. Click on QUERY button (located above the SCHEMA, DETAILS and other table-related tabs), then choose In new tab, enter the following SQL query, then click RUN (Figure1.16):

    SELECT
    PARSE_DATE("%Y%m%d",event_date) as Session_Date,
    device.category AS Device_category,
    COUNT(*) AS Nb_of_sessions
    FROM
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.  events_202101*`
    WHERE event_name = 'session_start'
    GROUP BY 1,2
    ORDER BY 1,2 ASC;

Figure 1.16 – Running a query

11. In this query, we’re trying to get the number of sessions on the website per device type by day for January 2021. Click on SAVE RESULTS above your Query results table and save it as BigQuery table (Figure 1.17) to your previously created dataset (mydata or another name if you decided to name it differently) in your GC project.
12. Name the table device_category_jan2021 and then click Export.

Figure 1.17 – Save the results

13. If you see that the job (all tasks are called jobs in BigQuery) failed, it might be because your dataset was created in another region. Getting a table from the public dataset that is in US and exporting it to your dataset in EU is not possible, so make sure that you created your mydata dataset in the US multi-region.
14. Go and check whether the table was created. Now, you have your own dataset that contains your own small table (Figure 1.18).

Figure 1.18 – Device category table

Connecting Looker to BigQuery

Let’s go back to our Looker instance to connect Looker to BigQuery. Make sure you have at least two tabs open – one with the GC console and another one with the Looker instance.

Let’s explore the Looker environment. The steps for this are as follows:

1. In your Looker instance, click on Admin on the left, then click on Database and Connections, and then click the Add Connection button (Figure 1.19).

Figure 1.19 – Database connection

2. When you click on Add Connection, give your connection a name (bq_connection1, in our case) and choose a dialect (Google BigQuery Standard SQL in our case).
3. Fill in the form (Figure 1.20) with your billing project ID and dataset, choose the standard UTC time zone, and for the rest, keep the default values, then click on CONNECT to establish the connection. To find your project ID (where the billing is configured), go to the GC console, click on your project name in the top-left corner near the Google Cloud logo, and copy the ID from the pop-up window.

Figure 1.20 – Database connection configuration

How it works...

The connection was relatively simple because Looker (Google Cloud core) has a native connection with GC services. We also configured very few elements, to keep it simple. The goal of this chapter is to quickly go through the Looker basics to give you an overview of how it works. Let’s continue with the LookML project creation.

See also

• Connecting Looker to your database: https://cloud.google.com/looker/docs/looker-core-dialects
• About BigQuery: https://cloud.google.com/bigquery?hl=en