Chapter 9: Using the SQL Console

Traditionally, data analysis on relational databases has always been performed using SQL, or Structured Query Language. While there are many tools and languages for data analysis, SQL is usually thought of as the starting point or the common denominator.

Over the last several chapters, we've learned that many of the routines you would usually resort to SQL for can be performed easily using Metabase's notebook editor. Indeed, this is one of the selling points of the product: by allowing anyone to carry out what would normally be relegated to SQL programmers, Metabase has democratized data analysis.

At the same time, there is certainly a time and place for using SQL in Metabase. Having proficiency in SQL can make Metabase better for everyone in many ways, and that's what we'll be covering in this chapter. Specifically, we'll cover the following topics:

• Introduction to SQL in Metabase
• Creating Questions-as-Tables...

Technical requirements

You will need a running instance of Metabase with a connection to the pies database. As we'll be writing a lot of SQL in this chapter, it's helpful to have some knowledge of the language. If you don't know SQL, you'll still be able to copy, paste, and run all the queries, but you will get more out of the chapter if you also understand what they do.

Important Note

NoSQL databases, as their name suggests, cannot be queried with SQL. They can still be queried with their own query languages, and these can be run inside Metabase, but they tend to look very different from SQL. In this chapter, we will only cover SQL, as most Metabase users tend to use relational databases.

Introduction to SQL in Metabase

After spending time learning Metabase's notebook data editor, you might be wondering what the pros and cons of using SQL instead are. If you are using a relational database with Metabase, all questions that can be created with the notebook editor can be expressed with SQL instead. The reverse of that statement is not true. Only some questions expressed with SQL can be created with the notebook editor. There are many functions and operations that can be carried out with SQL that don't exist in the notebook editor. This will likely change over time, as the Metabase team is constantly improving the notebook editor, but for the time being, having a solid background in SQL means you can ask questions that would not be possible to express in the notebook editor.

Just because any question created in the notebook can be written in SQL, does not mean that all questions should be written in SQL. There are benefits to using the notebook editor instead...

Creating Questions-as-Tables using SQL

In the last section, we learned that while SQL can often be an easier option, it has a major drawback: SQL questions are hard for non-SQL users to explore. This presents a clear problem for a product such as Metabase, since Metabase aims to democratize data and allow anyone, no matter how technical, to explore it and find answers to their questions. Their solution to this is the notebook editor, but we've already seen that the notebook editor either can't answer a question, such as Items Ordered, which we learned about back in Chapter 6, Creating Questions, or is too unwieldly, as we saw in the last section with the review rate.

Unfortunately, having messy or unwieldy data in your application database is more of a rule than an exception. It's often quoted that 80% of a data scientist's job is cleaning messy data. In my experience, that sounds about right, and a lot of that cleaning is done with SQL. Remember that application...

Using variables in SQL queries

In the last section, we learned how to connect filters to saved questions written in SQL. However, to do so, we needed to use the notebook editor as an intermediary step. It would not have worked had we written the question in pure SQL. Let's see for ourselves, by recreating the Count of Menu Items Ordered question in pure SQL:

1. Open up the Items Ordered question we saved earlier.
2. Click the diagonal arrows to expand the editor.
3. Edit the SQL as follows:

SELECT 
    c.name 
    , SUM(item_count) as items_ordered 
FROM 
(
SELECT 
    * 
    , CAST(single_item_orders->>'id_menu' AS INT) as id_menu
    , CAST(single_item_orders->>'count' AS INT) as item_count
FROM 
    (
        SELECT 
        	*
&...

Creating saved SQL snippets

It may feel like a lot of the SQL we've written has been copied, pasted, and reused over and over again from other queries. This is common in SQL, common in programming in general, and considered an anti-pattern. In fact, there's a principle in computing called the DRY principle, which stands for Don't Repeat Yourself (https://en.wikipedia.org/wiki/Don%27t_repeat_yourself). The principle is about how you should not rewrite the same line or lines of code again and again, as it is time-consuming and introduces more risk of bugs from typos.

Metabase has taken this principle to heart and has a feature called Saved SQL Snippets. A SQL snippet is a block of SQL that you can call upon in a single variable whenever you want to reuse it.

Throughout this chapter and book, we've been working with some iteration of the same query that takes the Orders table and flattens it out, such that every row becomes an item ordered, rather than an entire...

Summary

We covered quite a bit of advanced content in this chapter, all around how to best use SQL in Metabase. By now, you should understand that SQL absolutely has a role to play in a fully functioning Metabase environment. Moreover, you should understand the time and place where SQL is appropriate, and where it's better to stick with the notebook editor.

I've seen a lot of novice SQL programmers become very proficient at SQL after having the chance to play around with it in Metabase. SQL is an incredibly powerful language that has staying power. I believe we'll be using it for decades to come. It tends to be one of the easier languages to learn, too, so I encourage you to make it a part of your Metabase environment.

This chapter concludes our exploration of the core features of Metabase. In the next chapter, we'll learn about some advanced features of Metabase, find out where to go for additional help, and learn how we can contribute to Metabase's...