ggsql: A Grammar of Graphics for SQL

Today, we are super excited to announce the alpha-release of ggsql . As the name suggests, ggsql is an implementation of the grammar of graphics based on SQL syntax, bringing rich, structured visualization support to SQL. It is ready for use in Quarto, Jupyter notebooks, Positron and VS Code among others.

In this post we will go over some of the motivations that lead us to develop this tool, as well as give you ample examples of its use; so you can hopefully get as excited about it as we are.

Meet ggsql#

Before we discuss the why, let’s see what ggsql is all about with some examples.

The first plot#

To get our feet wet, lets start with the hello-world of visualizations: A scatterplot, using the built-in penguins dataset:

VISUALIZE bill_len AS x, bill_dep AS y FROM ggsql:penguins
DRAW point

That wasn’t too bad. Sure, it has the verbosity of SQL, but that also means that you can speak your plot code out loud and understand what it does. We can break down what is going on here line-by-line:

• We initiate the visual query with VISUALIZE

and provide a mapping from the built-in penguins dataset, relatingx

to the data in thebill_len

column, andy

in thebill_dep

column. - We draw a point layer that, by default, uses the mapping we defined at the top.

With this in place, we can begin to add to the visualization:

VISUALIZE bill_len AS x, bill_dep AS y, species AS color FROM ggsql:penguins
DRAW point

We see that a single addition to the mappings adds colored categories to the plot. This gradual evolution of plot code is one of the biggest strengths of the grammar of graphics. There are no predefined plot types, only modular parts that can be combined, added, and removed. To further emphasize this, let’s add a smooth regression line to the plot:

VISUALIZE bill_len AS x, bill_dep AS y, species AS color FROM ggsql:penguins
DRAW point
DRAW smooth

We add a new layer on top of the point layer. This layer also borrows the same mapping as the point layer. Since we color by species, the smooth line is split into one for each species.

We can continue doing this, adding more mappings, adding or swapping layers, controlling how scales are applied etc until we arrive at the plot we need, however simple or complicated it may be. In the above example we may well end up deciding we are more interested in looking at the distribution of species across the three islands the data was collected from:

VISUALIZE island AS x, species AS color FROM ggsql:penguins
DRAW bar

While a completely different plot, you can see how much of the code from the previous plot carries over.

A complete example#

With our first couple of plots under the belt, let’s move on to a complete example. It will contains parts we have not seen before, but don’t worry, we will go through it below, even the parts we’ve already seen before. The example is an adaptation of a visualization created by Jack Davison for TidyTuesday.

WITH astronauts AS (
SELECT * FROM 'astronauts.parquet'
QUALIFY ROW_NUMBER() OVER (
PARTITION BY name
ORDER BY mission_number DESC
) = 1
)
SELECT
*,
year_of_selection - year_of_birth AS age,
'Age at selection' AS category
FROM astronauts
UNION ALL
SELECT
*,
year_of_mission - year_of_birth AS age,
'Age at mission' AS category
FROM astronauts
VISUALIZE age AS x, category AS fill
DRAW histogram
SETTING binwidth => 1, position => 'identity'
PLACE rule
SETTING x => (34, 44), linetype => 'dotted'
PLACE text
SETTING
x => (34, 44, 60),
y => (66, 49, 20),
label => (
'Mean age at selection = 34',
'Mean age at mission = 44',
'John Glenn was 77\non his last mission -\nthe oldest person to\ntravel in space!'
),
hjust => 'left',
vjust => 'top',
offset => (10, 0)
SCALE fill TO accent
LABEL
title => 'How old are astronauts on their most recent mission?',
subtitle => 'Age of astronauts when they were selected and when they were sent on their mission',
x => 'Age of astronaut (years)',
fill => null

That was a lot of code, but on the flip-side we have now covered a lot of the most important aspects of the syntax with one example.

At the topmost level there are two parts to this query: The SQL query, and the visualization query. The SQL query is anything from the beginning to the VISUALIZE

clause. It is your standard SQL, and it accepts anything your backend accepts (in this blog post we use a DuckDB backend). The result of the query is funnelled directly into the visualization rather than being returned as a table like you’d normally expect.

Since the point of this post is not to teach you SQL we won’t spend much more time discussing the SQL query part. The main take away is that everything before the VISUALIZE

clause is pure SQL, any resulting table is automatically used by your visualization, and any table or CTE created there is available for referencing in the visualization query.

As we saw in the first examples, the SQL query part is optional. If your data is already in the right shape for plotting you can skip it and instead name the source directly in the VISUALIZE

clause:

VISUALIZE year_of_selection AS x, year_of_mission AS y FROM 'astronauts.parquet'

Now, let’s look at the visual query — everything from VISUALIZE

and onwards. VISUALIZE

marks the end of the SQL query and the beginning of the visualization query (or VISUALISE

for those who prefer UK spelling). It can stand on its own or, as we do here, have one or more mappings which will become defaults for every subsequent layer. Mappings are purely for relating data to abstract visual properties. A mapping is like a SELECT

where you alias columns to a visual properties (called aesthetics in the grammar of graphics). In the visualization above we say that the age column holds the values used for x

(position along the x axis) and the category column holds the values used for fill

(the fill color of the entity). We do not say anything about how to draw it yet.

Following the VISUALIZE

query we have a DRAW

clause. DRAW

is how we add layers to our visualization. There is a large selection of different layer types in ggsql. Some are straightforward: e.g. point

for drawing a scatterplot. Some are more involved: histogram

(which we use here) requires calculation of derived statistics like binned count. A visualization can have any number of layers and layers will be rendered in the sequence they are defined. DRAW

has a sibling clause called PLACE

. It is used for annotation and works like DRAW

except it doesn’t get data from a table but rather as provided literal values. It follows that our visualization above contains three layers: A histogram layer showing data from our table, a rule annotation layer showing precomputed mean values for each category, and a text annotation layer adding context to the visualization. It is worth mentioning that a layer does not correspond to a single graphical entity. Like with the text layer above, each layer can render multiple separate entities of its type so there is no need to have e.g. 3 line layers to render line plots for 3 different categories.

After the DRAW

and PLACE

clauses we have a SCALE

clause. This clause controls how data values are translated into values that are meaningful for the aesthetic. In our case, the category

column holds the strings “Age at mission” and “Age at selection” which doesn’t in itself translate to a color value. The clause SCALE fill TO accent

tells ggsql to use the “accent” color palette when converting the values mapped to fill to actual colors. Scales can be used for much more, like applying transformations to continuous data, defining break points, and setting specific scale types (like ordinal or binned).

The last clause in our visual query is LABEL

which allows us to add or modify various text labels like title, subtitle, and axis and legend titles.

Stepping back#

That was a mouthful. But there are two very