Learn How to Create Scalable, Powerful Data Models with Ease
A step-by-step breakdown of Looker and using LookML to model SQL queries
Oct. 27, 2021 | By Sai Kiran Reddy Ravula
Structured query language (SQL) is a very powerful framework for handling structural data. And while it can do almost anything with data, it also comes with some problems. As data files get bigger, the SQL code becomes hard to manage. Not to mention, it’s hard to work as a team on SQL files, which means code reusability becomes a concern. The solution? Introducing: Looker.
What Is Looker?
Looker is a cloud-based reporting platform that leverages SQL to create scalable, powerful data models. Looker breaks down the complexities involved in writing SQL queries into smaller units and handles numerous tables and joins sophistically using LookML. It is version controlled with Git that makes developer collaboration simple and painless.
Benefits of Using Looker
As a powerful BI tool, Looker helps businesses develop insightful visualizations. It offers a user-friendly workflow, is completely browser-based (eliminating the need for desktop software) and facilitates dashboard collaboration. Among other benefits, users can create interactive and dynamic dashboards, schedule and automate the distribution of reports, set custom parameters to receive alerts and utilize embedded analytics. It integrates with Git for code management and collaboration.
What Is LookML?
LookML is the Looker Model Language designed for describing dimensions, aggregates, calculations and data relationships in a SQL database. This is not a replacement for SQL—rather a better way to write SQL. It is the backbone for building the reports on Looker.
A LookML project is a collection of model, view and dashboard files that are typically version controlled together via a Git repository. The model files contain information regarding which tables to use and how they should be joined together. The view files contain information concerning how to calculate information about each table (or across multiple tables if the joins permit them).
LookML separates structure from content, so the query structure (how tables are joined) is independent of the query content.
LookML is a dependency language, as opposed to an imperative language. It provides predefined data types and syntax for data modeling, which is clear and easy to learn. You don’t need prior experience with programming languages; everything you need to know is documented. It is independent of any particular SQL dialects and encapsulates SQL expressions to support any SQL implementation.
The example below shows a minimal LookML project for an e-commerce store, which has a model file and two view files:
An Overview of Fundamental LookML Elements
The following diagram shows fundamental LookML elements and their relationships. For more detail, see LookML terms and concepts.
Looker UI consists of Browse, Explore and Develop tabs. The files related to the project can be found in the Browse tab. The data visualizations and SQL query generation can be found in the Explore tab.
How LookML Generates SQL
Adding a dimension
In the example below, the names of users are stored in a column in the database and can be represented with the following LookML dimension.
When you add a dimension field to “Explore,” Looker constructs a SQL query that includes that field and sends the query to your database.
Adding a field to Explore generates a SQL query
Because the field selected is a dimension, it gets added to the SELECT and GROUP BY clauses. Because you specify which fields should be aggregated using the LookML, Looker will treat them accordingly when generating a SQL query.
Adding a measure
For example, if you want to see the number of users in a given state, you can add a count of users into the Explore query. You don’t have to know that count is an aggregate measure function. Looker will automatically add the measure to your SELECT statement, but it will not appear in the GROUP BY clause.
When we add the aggregate count measure, it is not added to the GROUP BY statement.
Adding filters
To filter by a dimension, apply a filter within Explore on any dimension, and that filter will populate the WHERE clause of the query.
If you filter by a field that is an aggregate, Looker will automatically apply that filter in the HAVING clause.
How to Write a LookML File from a SQL Query
Having just explained how Looker generates a SQL from the LookML file, you can get started on how to write LookML from a SQL Query. LookML generates the SQL queries as per the below format.
If you have a complex SQL query and want to build a LookML out of this, break your query into parts that can fit the below format and reverse engineer to build LookML.
For example, let’s say you have a query that looks like the below:
Select state, sum(population) as total_population from population_stats having sum(population) > 500000
The equivalent LookML code for this view will be as below:
Looker is a great tool for developers and analysts to quickly build reports, and LookML is a great way to leverage SQL and model complex SQL queries with ease. Learn how Looker integrates with Google Cloud Platform.
References:
About the Author
Sai is an experienced Software Professional with more than seven years’ work experience in building data engineering systems and Business Intelligence reporting. As such, he implemented various data migrations to cloud from on-prem. He is skilled in RDBMS, SQL, NoSQL, GraphDB, Python, BigData technologies, GCP, AWS and various data engineering tools and practices.
