Transcript#
This transcript was generated automatically and may contain errors.
Hi. Hello, everyone. Welcome to my talk. I'll be presenting to you today ggsql, which is a tool in alpha right now. The idea is basically that we have the grammar of graphics for SQL.
To explain this a little bit, the grammar of graphics is a theory, and we combine it with SQL. You have a grammar and a language, so it can kind of go together.
I want to make a quick example to get everyone's noses pointing the same way. You may all recognize this. You have a SQL query. Anybody coming from the R ecosystem might recognize these penguins. But the problem, if you're working with data like this, is that it's not immediately obvious what's going on here. What is going on with these penguins?
The thing that we are adding is that if you work from WB, you can install the ggsql extension from the community extensions, and then you can add additional syntax to make a plot out of this. This is your query executed. It has only penguins living on the Biscay Island, and we are visualizing the build length of their beaks versus their build depth, and we're drawing these with a point layer, and this has all been done before.
I was explicitly told no demos today. That's why I'm trying to get you to do the demo. There's the ggsql.org website, and you see the text input there. It's an interactive example. You can try it out right now if you want.
Background and motivation
And now I'm going to brag a little bit, because I have been working as a maintainer of ggplot2 and wrote my first SQL query maybe seven months ago, but we have had a long time of maintaining ggplot2. It's the most downloaded R package at all, so even more popular than some hardcore infrastructure packages like rcpp or jsonlite, but the ggplot2 project is mature, but also constrained by backward compatibility, and for us, ggplot2 is a new adventure with a carte blanche for new ideas.
And for those not on the same page yet, the grammar of graphics is a theory that was developed by Leland Wilkinson, which was published in this book. And this theory has several software implementations, of which ggplot2 is one, but there are also ggplot9 for Python, and notably missing from this list is SQL.
So what do we kind of mean? We presume that many of your data analytics workflows are kind of like this. You have your data living somewhere in a database, preferably DuckDB for this audience, and you send your data either to R or Python, use pandas or dplyr to do your data wrangling, and then you make a plot with ggplot9 or ggplot2. The big downside of this is that you need to cross an ecosystem gap, so you go from your SQL, from your DuckDB to R or Python, and this is inconvenient.
So we try to posture ggsql as belonging right here at the SQL side of things, so you can just not interrupt your workflows and get plots straight out of your databases.
Comparison with other approaches
We are not the first to attempt this particular strategy. This path has been trodden by others before. For example, there is a mini-plot extension in the community extensions as well. Mini-plot has a functional approach, so you have this scatter chart function, which you give a list of your variables, and that will also produce the same plot as the first one I showed you. However, you can only make a scatter plot with this function, and it doesn't have any other customisations for it, so this is quite constrained, and the opposite effect is if you want to build everything in a function, you get a giant monolith which is also terrible to use. So that's why we're not doing a functional approach.
What we're also not doing is treading into Microsoft territory and make business intelligence tools, so there's this closed-source Microsoft BI Power BI desktop which allows you to also make plots from your database-backed data. This is point-and-clicking, and this is not open source, and we believe that code is the primary way in which visualisation should be expressed. So we believe code is better than point-and-clicking.
Why SQL and grammar of graphics go together
And actually, when you think about it a little bit, like language, grammar, there are a bunch of commonalities that the two have. Both are declarative. SQL, for example, allows you to speak into information structures, like give me the revenue grouped by month, whereas the grammar of graphics allows you to speak information and codings, like show revenue as bars by month. Both of them are compositional with a small core grammar, and in SQL you have to select clauses, grouped by where, whereas in the grammar of graphics, you have things like layers or things like scales, and you can recombine all these different words to get the task done.
SQL is also sort of portable. Now I'm not so sure any more after hearing about the Spice Park migration talk, but in theory, SQL might be portable. So yeah, you can run SQL on DuckDB, on other database backends, and similarly-ish, you can also execute the grammar of graphics on different backends, like ggplot2, the difference is mostly that the syntax is not conserved across the execution platforms.
How ggsql works
I am going to skim this one, because I just need to tell you that what we receive from the user is this select statement, for example, followed by visualize. What we do is we split the two. We throw the select to the database. We keep the visualize. We build the plots, and out comes a graphic.
The Minard chart example
So to demonstrate this a little bit more fully, it is a darling in the database community to reproduce this famous chart by Charles Minard. This chart is an illustration of Napoleon's 1912 campaign into Russia, and the main things that we can see here, there's a beige line going to the right, and there's a black line returning. The thickness of the line indicates how many troops Napoleon's army has left.
So as you can see it well, but as Napoleon marches to Moscow, he's losing troops and troops and troops. He reaches Moscow and then has to retreat and loses troops and troops and troops. So while technically he did get to Moscow, he didn't come back very well.
So that is what this chart shows, and we can have very similar data. We can have the longitude and latitude coordinates of where the troops are at some point, the number of troops that Napoleon had at that point, whether they're advancing or retreating, and which group of the army it is, and the simplest thing that you can do is just add a visualize, longitude is X, latitude is Y, draw a path. So you have spoken this plot into existence, and then you can from this basis you can start iterating and building more and more stuff up on top the plot.
For example, you can properly split the lines by doing a partition. You can add the direction as a color. You can also add the line width representing the number of survivors. You can add a scale to have the colors match more. You can have a scale to have thicker lines. You can add a text layer to give some context of which cities are at what place.
And lastly, you can use the spatial extension as well to do some map projections. For example, if we project to Mercator, then we will have a properly proportioned plot. We can also add a spatial layer, this was added Monday, where you can just have geospatial data and you can flip through a few projections, zoom in, and as a finishing touch, put the correct labels at the right spot. So this is what you could do.
Distribution and integrations
And our idea is that we want it to work wherever. We distribute it as a lone executable. We don't want to bundle it with R and Python environments, so we don't have a runtime. We want to constrain the execution so that the possibility to execute malicious code is minimal. We have a variety of integrations with, for example, a DuckDB extension, we have a command line interface, a Jupyter kernel. And we also hope that it is kind of nice to work with assistance for this.
Of course, there's a bunch of SQL training data out there that's not out there for ggsql, but we hope that some properties of SQL will transfer well to ggsql. We are also kind of exploring for how do you want to maybe you want to use LLM assistance for interfacing with your dashboards and your data visualizations. There's a query chat package out there that can help you with this, and we wrote a skill to also teach your LLM assistance to work with ggsql.
You can hopefully get this someday, wherever SQL is, but for now, there's interactive examples on the website. There's language support for VS Code and Positron. There's a kernel for Jupyter and Quarto. There's a command line interface, R, Python, and the WebAssembly, which drives the interactive examples. And of course, the DuckDB extension is actually a DuckDB sandwich, really. It's DuckDB in the back, ggsql in the middle, and DuckDB on front.
In closing, we have made a new SQL-based tool for data visualization based on the grammar of graphics. DuckDB is a first-class citizen herein, and with that, I would like to thank you, and in particular Posit, for paying me money, and Thomas, George, and Hadley, who collaborated with me on this.
