Integrating SQL with Looker for Data Analysis

You may have heard the term “data-driven”. All big companies nowadays have a data-driven approach; it simply means they make strategic decisions based on data analysis and interpretation. To streamline the process and stay competitive, companies rely heavily on data analysis and Business Intelligence (BI) tools. One of the most famous powerful BI platforms is Looker.

Now owned by Google, Looker is well known for its data exploration, visualization, and reporting capabilities. Data exploration with Looker can be done through its visual interface or by writing SQL queries. SQL (Structured Query Language) is the standard language for managing and performing queries on relational databases. Thanks to SQL, data can be efficiently retrieved, filtered, and analyzed.

Integrating SQL with Looker is essential for advanced data analysis: it combines the power of Looker and the strength of SQL. Although data exploration with Looker’s visual interface allows the retrieval of meaningful information, integrating SQL with Looker opens up advanced data operations, such as creating complex queries to retrieve specific data and get data-driven insights.

Want to learn SQL so you can build awesome queries? Check out our SQL Basics course! You’ll quickly understand how to retrieve information from a database and build basic reports from scratch.

Let me clarify something: there is no need to be a developer or an engineer to know SQL! As a data analyst, a BI professional, or anyone involved in data manipulation and database management, mastering SQL operations is a required skill. Plus, if Looker is part of your most used daily tools for work, knowing SQL will give you an incredible benefit. Taking advantage of the synergy between SQL and Looker, you can create robust data models and visualize and explore those models with Looker.

Setting Up Looker with SQL

Integrating Looker with SQL involves several steps.

First there’s the initial configuration. You need to be logged in to Looker before you can do anything else. If you don’t already have a Looker account, sign up for a free trial or start a paid subscription. Then, log in to your Looker account. After logging in, you’ll be guided through the setup process; follow the on-screen instructions to configure basic settings like your time zone, preferred language, etc.

Now you can set up the connection to your database. Click on the gear icon (Admin) in the top-right corner of the Looker interface; this will show the Admin Panel. In the Admin panel, navigate to the Connections section and click “Add Connection”. You will be asked to give a connection name and select your database dialect (MySQL, PostgreSQL, SQL Server, etc.) from the drop-down list. (If you’re unsure which SQL dialect you should learn, don’t worry. SQL is a standardized language and all SQL dialects are mutually intelligible. Switching from one dialect to another is not a big deal.)

You will be asked to enter your database credentials. For the host, enter the hostname or IP address of your database server. For the port, enter the port number your database is using (default ports are typically pre-filled based on your selected dialect). For the database, enter the name of your database; for the username and password, enter your database username and password.

Depending on your database and network setup, you may be asked to provide additional parameters such as SSL certificates, connection timeouts, or specific options for your database dialect.

Once everything is working properly, save your connection by clicking “Add Connection”.

Building and Running SQL Queries in Looker

Once you have configured your database connection, you can run SQL queries in Looker using SQL Runner – a simple interface that allows you to craft custom SQL queries for data retrieval.

From the main navigation bar, click on Develop and select SQL Runner from the dropdown menu. Choose the database connection you previously set up, and then start writing your query in the SQL editor. When you’re done, click “Run” to execute your query; the results will be displayed below the editor.

Example of Looker’s SQL Runner

Imagine that you want to retrieve 2023’s ten biggest orders by order amount and you want the results shown in descending order. You could write the following SQL query:

SELECT 
  order_id,
  order_amount
FROM 
  orders
WHERE 
  order_date BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY 
  order_amount DESC
LIMIT 10;

As you can see, SQL provides clauses and filters that allow users to write queries that match their needs. This flexibility is very important: integrating SQL with Looker for data analysis permits the generation of meaningful reports and dashboards that align with unique business requirements.

For example, SQL can be used to retrieve data based on complex filtering criteria, join multiple tables, and perform complex calculations that may not be directly supported through Looker’s standard interface.

Are you curious about how to write SQL queries that apply to real-world business reporting? Then I highly recommend you enroll in our SQL Reporting track!

Visualizing Data in Looker

Once you have written and run your SQL query, the results will be displayed in a table below the query editor. But you can also choose to visualize your results in other ways. Looker transforms the results of SQL queries into visualizations like charts, graphs, and tables, which you can use to build clear, actionable reports and dashboards.

Example of visualization with Looker

The first and most common type of visualization is the table. Tables are used for detailed data views, summary reports, and comparisons of specific data points. For example, a table might show sales data by product category, including columns for product name, total sales, and quantity sold.

Then, we have the bar chart. The bar chart is used to compare quantities across different categories. For example, a bar chart can compare monthly sales figures across different regions.

The line chart is used to visualize trends over time, e.g. website traffic trends over the past year. The famous pie chart – or “camembert” as they call it in France – is used to show the composition of a whole. You might use a pie chart to illustrate market share distribution among competitors.

Among the less common types of visualization, we have the scatter plot; it’s mainly used to identify correlations between two variables. You could use a scatter plot to show the relationship between advertising spend and sales revenue. To this category also belongs the heatmap, which highlights data density and patterns. You might use a heatmap to understand customer activity on a website by day and time.

Finally, the map is used for geographic data visualization, such as a map that shows store locations and sales performance across a country.

If you are interested in data visualization, I recommend you read Nicole Darney’s excellent article Data Visualization Using SQL: A Complete Guide for Data Analysts.

Advanced Data Analysis Techniques

Looker offers many advanced data analysis techniques that allow you to go deeper into data exploration and visualization.

One of these techniques is known as derived tables. Derived tables in Looker are virtual tables created by writing SQL queries within LookML (Looker Modeling Language). They allow users to define complex transformations and aggregations that are not possible with standard database tables.

Another advanced technique is table calculations. Table calculations make it easy to create real-time metrics. They are similar to the formulas we find in spreadsheets like Excel and Google Sheets. In the following example, the last column uses a table calculation to combine three fields in the data using the concat function:

Example of table calculations with Looker

Using advanced data analysis techniques in Looker, such as creating derived tables and table calculations, data analysts and BI professionals can perform deeper and more complex analyses. Those techniques help to discover new insights, improve data efficiency, and improve the quality of reports and dashboards.

Building a Simple Dashboard

In this section, I will explain how to build a simple dashboard with Lookup.

First, you need to identify the key metrics you want to track for your dashboard. For example, you can use the key metric revenue to build a revenue monitor dashboard. It could include total revenue, revenue by product, revenue by region, monthly revenue trends, and top customers by revenue (among other metrics).

Next, you need to create individual Looks, or a saved query with a visualization. Each metric will be represented by a Look. To do this, you first build your custom SQL query. Then you choose a suitable visualization type (such as a line chart) and save the Look.

Now you can finally build your dashboard. Navigate to the Dashboard section and click on "New Dashboard". Click on "Add Tile", select "Look", and choose the Look you want to insert. Repeat the process for all the Looks you want to have in your dashboard. Then you can drag and drop the tiles and arrange them on the dashboard.

Example of a dashboard in Looker

More Ideas for Using SQL with Looker for Data Analysis

In this article, we have seen the importance of integrating SQL with Looker for effective data analysis – but we have really only scratched the surface! If you want to know more about Looker and data analysis, I recommend you check Looker’s official resources.

If you want to learn SQL, you will find all that you need on LearnSQL.com. You can explore the courses and find the perfect fit for you. If you plan to enroll in multiple courses, let me suggest our All Forever SQL Package. It gives you lifetime access to all current and future courses in all SQL dialects on the LearnSQL.com platform. Give it a try!

Thanks for reading this article on integrating SQL with Looker for data analysis. I hope you liked it and found it useful. See you in the next one!