Transcript#
This transcript was generated automatically and may contain errors.
We are joined by our featured lab manager, Thomas Pedersen. Thomas, would you like to introduce yourself?
Hello, everyone. My name is Thomas. I live in Denmark and have been with POSIT for far too long now. 18, I think. I'm working on the Tidyverse team, but also working on stuff that is not R. And I've been taking care of graphics, more or less. I think that's the overarching theme of my work. Everything graphics-related is kind of my table. So I'm maintaining ggplot2 for those that are using R, but also the lower levels that make ggplot2 work underneath. And lately, I am taking all of that to a new place with ggsql, which we'll talk about today.
I'm so excited. And you do artwork.
I do artwork as well. So I do algorithmic art. I started out using ggplot2 for that. A lot of it now is shader-based and JavaScript-based for some more interactive and animated stuff. But still, yeah, that's also part of my gig.
I was going to show everybody one of your pieces, which I have here, I think. Do I? Yeah, I do. Isn't this gorgeous?
That is rendered with ggplot2, actually.
It's just a bunch of polygons. Yeah, so at some point, I started out being a data scientist, actually a bioinformatician, and doing a lot of visualization work. And I have gradually moved over to becoming full-time software engineer, which meant that I didn't really use ggplot2 as a user anymore. So at least for a period of time, it was really nice to be able to maybe not use it, but abuse it a bit for artwork and become a user of my own tools again. I believe deeply that being a user of your own tools builds empathy around what you're doing.
and become a user of my own tools again. I believe deeply that being a user of your own tools builds empathy around what you're doing.
But it's very hard to maintain that kind of balance because once you go full software engineer, it's really hard to get back. So I'm finding small ways to get back there.
It is really hard. Amazing. Well, all right, I say we go ahead and get started. If you would like to share your screen, tell us a little bit about what you are going to be talking about today. And as a reminder to everybody in the chat, if you see a little golf flag next to your question, that is Isabella and I, or other people in the chat, letting us know that there's a question. And so that little golf flag means Isabella or I are aware that there's a question, and we will try to give that to Thomas whenever we can.
And then if you also see anybody lighting candles, that means somebody is probably live coding. You'll see my candles right up here. Don't worry, they're not real. Not a house fire hazard. But just wanted to let you know what those were because sometimes people ask, all right, Thomas, take it away. Tell me. I already have so many questions about ggsql. I'm going to try to hold them in a little bit and see if anybody else asks them. And then maybe I'll unleash them.
Introducing ggsql
Okay. You should all be able to see. This is really just a website. So yeah, we live on ggsql.org, at least for now. And this is the website. So I'm just starting here so you can get acquainted. What is ggsql?
Well, it's a weird thing that we have built over the course of the last half year or so. GG gives it away. It's the grammar of graphics. If you're not familiar with that concept, it's what underpins ggplot2. There's the GG again. And it's a theoretical framework for describing graphics, which means that from a programming point of view, it's a way to be very structured about how you describe your graphics and do it in a composable way because it kind of breaks down the concepts of data visualization into smaller bits that can be composed.
So there's not something like a scatterplot, a boxplot, and these things cannot be like mingled together. Everything is just parts, Lego bricks that you can put together. And if you're coming from the R world, you know ggplot2. It has been widely successful. I think it's partly because of this whole idea. Once you've rocked the idea of ggplot2, you can build more or less anything. You don't have to learn new concepts, and that makes it very powerful. The learning curve is a bit steep compared to something like a function that's called scatterplot, and you know how to make a scatterplot. But it pays dividends, and people really enjoy it.
So ggplot2 is pretty old. We wanted to build something new. We also wanted to explore something completely different. So that's the last three letters of ggsql. That's the SQL part. So ggsql is a grammar of graphics for a SQL. And it's probably just easier to show it.
Live demo on the ggsql website
So this is the front page, and it actually starts with an editable. It can run in the browser. If you're familiar with SQL, hopefully this looks not too unfamiliar, at least the first part up here. That's pure SQL. Everything below that is ggsql.
And what we have tried to do is to express the ideas around the grammar of graphics in a way that feels very native to SQL. So even though we have come up with new concepts and new clauses and so on, the hope is that if you're used to SQL, then these things and the way they work together would feel familiar and at least feel ergonomic to the language.
So here we start by creating some data. We have some built-in data sets. Our lovely penguins data set is there, so we can toy with that. So we just select everything from penguins, where island is this Abisko island. And then usually you would get a table back from this, but here we just funnel it into the next clause, which is this visualize clause. It looks kind of like a select clause if you're familiar with SQL. We visualize build length as X and build depth as Y.
This means that we are mapping these columns in the table into these abstract ideas of what exists in the plot. So X and Y are things in the plot, right? And we say, well, build length is going to be X and build depth is going to be Y, and body mass is going to be the fill color, which is just called fill. And then we begin to compose it. So now we have how the data relates to everything, and now we can begin to compose it. We can say we'll draw points, and we'll just inherit all of these things we have up here.
So if we just, like, let's just delete all of that. So if we just draw points, we have the points, we have the fill. It already gives us a lot for free. It gives us this legend and so on and so forth. We could do things like draw smooth, and it will layer on top of that an additional layer, which is a smoothing curve through all of these points. We can see that there might be some different structures in there. So we could say draw smooth partition by species probably, right? And we'll get to, so we group the data into two things, and we get a line from each of them.
So this concept, like, it's a lot of code if you just want to have a scatterplot, right? But you can see that this concept naturally extends to basically whatever you want to plot. If we're not interested in points at all, we can say let's draw something else. We'll say species as X. This is unrehearsed, true to the style. And we'll draw a bar, and we'll have a bar plot with automatic count.
So you can see that there is a lot of flexibility in some ideas that are universal to plots, right? And this is the thing that underpins ggplot2, and now we're doing it for SQL.
Why ggsql?
Why do we do that? Well, we think we don't want to keep ggplot2 and the fantastic ideas behind that to only our users. So that's one of the things we want to engage with a new audience. In Puzzit, we have not spent too much time catering to SQL users, but SQL is such an integral part of data science that it feels wrong not to. And it's also like we feel like there's a gap there in their workflows that when you're a heavy SQL users, if you want to look at the data, usually you would have to export it, put it into Excel, put it into Python, put it into R, do all of that context switching just for the sake of looking at some interesting part of your data. So why not engage with them in a more direct manner?
This is amazing. I have so many questions, but not as many questions as Dan does. Dan is firing off so many. So let's see if we can answer just a few of them. One is, is ggsql more like ggplot2 or more like Seaborn? Because they both implement the grammar of graphics, but Seaborn is explicitly limited to only statistical plots. ggplot2 allows you to make any arbitrary visualization.
ggplot2. So first and foremost, I am not a Python user. This is not like a value judgment. It just doesn't click with me. I also don't think that like Seaborn doesn't really click with me. So this is not something that I would naturally, this is not growing much in any way, but it's like I have been working with ggplot2 for like maintaining it for nine years. So I'm probably biased as well, but it also means that it's more natural for me to think in those terms.
What I'm going to like, it is not a direct port of ggplot2. ggplot2 is like 19 years old now. Like it's an old thing. And it's carrying around like all of this technical depth and this syntax depth of like bad decisions that Hadley did. I've, of course, never done bad decisions during my time, but carrying around so much from his reign over there, it means that we can just kind of start over from a blank canvas and take the things that we think, well, those we really nailed in ggplot2, but also remove things and rethink things that will be hard to do in ggplot2 because of the backwards compatibility and like the whole infrastructure. But here we're kind of free to just say, what if we don't carry around all of that depth? So we're doing that. And it's very exciting for someone who has maintained such an old package for so long. It is, it's breaking new ground.
When and where to use ggsql
Well, we have a couple of questions from Noor and Nathan that are a little similar. Noor asked, when would I use ggsql versus porting my data into R or Y? And Nathan had asked, do people write slash execute SQL in an IDE? Where would I use this? So when and where would we use ggsql?
Good questions. And perhaps I cannot give like a very satisfying answer right now. First and foremost, ggsql is still in alpha. So where do you use it? Well, we're still figuring that out. It's not that we don't have any ideas, but we don't see ggsql as like a monolithic thing where we build an IDE around it. We want to integrate it in as many offerings as possible. We have a Jupyter kernel for it now. So I think that's so you can have like notebooks running ggsql queries. And we have a Positron extension that means that you can execute ggsql in Positron. I can actually show that to you.
ggsql in Positron
Let's see if I just redo my sharing. So I think this is, yeah. So this is my Positron window. And you can see here, if you're working in R, you're used to having this R console. You can also have a ggsql console back down there. And we can do like visualize. Now I'm just like very uncreative, but doing the same. That's okay. This is live. And you can see it pops up and like acts as an integral. Oh, that's an interesting. I think that must be assumed thing with resizing. Sorry. That's okay. There we go. I can do, yeah, that's better. Staying on the same size. So you can see the integration feels a bit like R. This has a console.
You can have ggsql files, script files that you command enter, send to the console, get your visualization back. So all of these things are kind of integrated.
We also have integration with the connection panes, which can still be improved, of course. But if you have like an ODBC, this is a Postgres SQL. So I have running just for testing. There you go. I can connect to that. I can see my database in here. And have all of these things. And now I can use that database directly from the console and interact with that.
Awesome. That was one of the questions, actually, Diane had asked about database connection. So that kind of, that doesn't answer the other question. Like when, why would I even use that? And that's a fair question.
Who is ggsql for?
I am not here today to sell you on the, like, change your workflow. If you're a happy, like, SQL plus R or SQL plus Python user, that's fantastic. I'm not here to like kind of convince you that you should change your ways. But there is like a huge amount of people that are, I would say, starved for good visualization options because they do not work in R or Python. They work in SQL and they will then have to put it into a GUI or some sort of thing. And in order to see their data. And I think we can do better for them.
I also think that there are like, there are technical good reasons to reach for ggsql, even if you're working in R or Python. One of the things is that ggsql is structured in a way that means that like every plot is, like if you have a billion data points and want to create a, let's say a histogram of that, you would have to like take all of that into R and do segmentation and so on to do it in ggplot2. With ggsql, what it does is actually like it constructs a SQL query to only get back the data required to plot it. So all of the statistical transformation, even for something like density plot or box plots and all of these things, they all happen in your high powered SQL database. And it's very, very fast.
With ggsql, what it does is actually like it constructs a SQL query to only get back the data required to plot it. So all of the statistical transformation, even for something like density plot or box plots and all of these things, they all happen in your high powered SQL database. And it's very, very fast.
So for some situations, especially with large data warehouses, like I think it's a much more economic way to do data exploration. I like the idea of keeping your EDA inside of SQL and adding visualizations to it.
And one of the questions we also had, let me see what was from Hubert. It was, okay, is it usually like here's your SQL to get your data and then we have ggsql after that? Or can they be intermingled somehow as far as like having SQL code interspersed with ggsql code? Yeah, so if you have a ggsql file, then you can like have delimiters that like break them up in chunks. We're still like working on how the best way to do that is. So that's a kind of a weak answer. But yes, you can. I don't think it's perfect right now.
We are missing some infrastructure to make the feeling in Positron even better. We don't have a pure language server that understands ggsql and can say statement ends here. So when you say command enter, it executes down to that line and so on. So some of the affordances that we're used to with Python and R is still like on the building block in the pipeline.
Snowflake and backend support
All right. Well, I have so many more questions. I would love us to keep going, but I will ask one more, which I think could be pretty quick. It's from Nessa, and it says, can I use something like this with a really big data platform like Snowflake? Is that a main use case? Yes, it's a very main use case.
Basically, what we have now is we have a modular setup for ggsql, which consists of a reader module, a plot module, which is kind of the central thing, and a writer module. The reader module is the one that interacts with your database. And it's modular. It has DuckDB bindings. It has ODBC bindings. Through ODBC, it has a dedicated Snowflake binding as well. And we're going to add so many interesting backends to it. Kind of what we said, we want to meet people where their data are. And that's where we're going with it.
On the other side, there's this writer module, which is the one that takes all of these queries that end up in the data that should be plotted and converts it to something that is actually a plot. And currently, we only have one writer, which is a Megalite writer. So it takes all of these things and writes it into a Megalite spec, which is like a JSON spec, and then renders it. But this is not the end of the line for writers. We're working on something much more exciting. But it allowed us to move very fast because we didn't have to think about the actual rendering.
Yeah. Well, let's go ahead and keep going with a little bit more demonstration. And I'm going to hold on to some of these questions. Thank you, everybody, for asking them. They have been amazing questions that are getting at things that I would love to know, too.
One of the questions that I had, you touched on it just a little bit with the visualization part, is like, if I wanted to use this, do I need to be in a platform that has a visualization tool like a viewer? Because a lot of people might be like, oh, can I just pop into SQLite 3 and use this? Is it going to open up like a graphics viewer of some kind?
So we have a DuckDB extension. So if you're working in DuckDB, you can say install ggsql and you can immediately use ggsql from within DuckDB. I think it's always interesting to figure out how to integrate. I think what we want to do with Positron is to create the premier SQL platform, this premier SQL IDE. So it's going to be the obvious way to use this, but we don't want to make it monolithic in the sense that you have to use Positron to get a nice experience with this. So one of this is like a DuckDB extension for it. It obviously doesn't have a viewer pane. So what it does is it's popping up a browser window and displays it there.
You can be a bit constrained when you're dealing with extensions for specific SQL engines, but hopefully we can meet people in a lot of places. Marimo is another interesting place to see this. We don't have anything for it right now, but I think that's an obvious thing to explore. And then there's Quarto documents with the ggsql kernel. I don't even think of Marimo plus SQL. I just think of it as Python. And I know that it can play well with Quarto.
ggplot2 as a SQL backend?
We had a couple more questions that if you have time to answer them would be amazing. So Nathan had asked, I can see a future thing like dbplyr where ggplot2 just has a SQL backend and runs ggsql. If you give it a database. So is that a direction y'all are thinking of going?
Not exactly. I don't think so. I think for all of their similarities, there are also strong dissimilarities between the way ggplot2 works and how it expresses itself and how ggsql works. Part of it is some of these chains of old depth that we want to shed, which means there's not a direct translation. So I think it's more natural to think that we would in some way provide a good high level API for this in R. That's not ggplot2, but just like kind of what dbplyr does constructs a query that is then executed. I think that's a better approach.
There's already like a ggsql R package with bindings for ggsql. So it doesn't have a high level interface like you just give it a SQL string, but you can use that to actually create your own readers. So if you have some data source in R that you cannot interact with with ggsql, then you can use R, the R package as a binding between that and quickly get going. We have the same for Python as well. We have a ggsql Python package.
One interesting thing about the R package is that it's also a knitter engine. So you can use ggsql in knitter and then use that to mix R, Python, and ggsql code in a single Quarto document. And ggsql automatically gets access to the datasets that you have in both your R and Python chunks in front of it.
So that's a very smooth way of saying, I'm using Python or R to do my data wrangling, and then I'm going to use ggsql to describe my plots and just automatically get access to whatever data you create.
Using ggsql inside Positron
That is so super cool. I think that Isabella has a question for you. I'm just curious, would you mind showing us, say, we have a lot of folks who are new to the SQL world and particularly coming from the R, Python world. What would it look like to, you mentioned the Positron extension. After you install the extension, what do you do to use ggsql within Positron?
Sure, let's get back to this view. So let's assume that I'm here, I am an R developer. I was also curious, what is the .rs file? I've never seen that before. Oh, this is a Rust file. So ggsql is written in Rust. It's pure Rust, which means that it compiles to WebAssembly, which is also why we can have it in our website and executing things. It's not talking to a server, it's just running in your browser, doing interesting stuff.
Anyway, you're here. You have installed the ggsql extension. So you have installed this, you have installed ggsql as well. It's kind of like with Quarto, you can install the extension, but you also need to install Quarto so that the executable exists in your system. So if you go to the website and go to get started, there's install instructions there. It's just a regular installer. Anyway, so you have all this set up, and now you want to do something.
What you can do is, if you want to get back to this console that I had before, we can do this by saying start another. And then you can see that it exists there as a console engine, and we're up and running. And then you can just do whatever, as I did before. If you have data sources in your connection pane, you can have them set up and connect to them again. Resume connection, and you can begin to explore.
So this console, while it's a ggsql console, can also execute regular SQL. So you can do both, one and the other. We don't have tab completion yet. This is one of the things, one of the four instances that we're looking into.
So you can do that, and then you can, let's say, what's a meaningful way to describe this? Which visualize is trans.
Super interested in where this dataset lives as well. This is my PostgreSQL dataset of Lego colors.
So this is me using ggsql to run a query on an ODBC-backed PostgreSQL database, and I'm getting a horrible plot out of it, but anyway, the core idea is there.
And you can see that, for instance, Lego sets, that's a larger thing. We also have, so this connection pane is driven by the ggsql extension, but we also integrate into the data viewer as well. So you have all of these things that you're used to looking at when you're in ggsql world and positron as well.
Getting started with ggsql
Yeah, so I think that's how you'd get started, I think. If you want to get started just toying around and seeing if this really is something for you, then we also have a no setup solution on the website, which is, let's see, the playground, which is somewhat of an IDE that exists in your browser. You can upload your own data to play with it, but you can also use some of the built-in datasets, and just like there are examples, example plots that you can start off and then just begin to do fun stuff with it.
This is so fun, because you could still just use basic SQL in here, right? You could just play around with SQL. This is a good little chance to learn if anybody is curious.
In terms of the SQL that you wrote in positron, can it do all the SQL? Yes. Wow.
So the way it works is that it's basically just taking your SQL statement and sending it to your backend. So you're constrained by your backend. You cannot use DuckDB functions. If you have a PostgreSQL database, then you're limited to that and vice versa. So whatever SQL your data, whatever dialect your data lives in, you can use that.
What makes SQL and grammar of graphics a natural fit
We had a question from Notobeko that said, have you discovered anything that this fusion of SQL and ggplot can do that is unique to it past what the individual parts alongside one another can do? And I would further this by saying, have you hit the limits of it where you're like, it's good for this, but it's not good for this?
That's a good question. I think this, where SQL and gg shines together is more on like a theoretical, like foundational level. It feels very right. Like the first time someone told me about this was like, I didn't come up with this idea, but I took it on. The first time it was presented to me, it was like, that sounds weird. But SQL is declarative data manipulation. gg, grammar of graphics, is declarative data visualization. And like these two things just feel so right together in the same sense that dplyr and ggplot2 also feels kind of right together.
gg, grammar of graphics, is declarative data visualization. And like these two things just feel so right together in the same sense that dplyr and ggplot2 also feels kind of right together.
But this is like, this is on a different level. It's like purely the syntax of SQL. It's very verbose, but it also reads like super nice. And what we have found also, if you're using like LLMs to generate your code, like it picks up ggsql syntax, like very easy. And it's very good at expressing itself with it because it reads like text. And it has like, it tries to stay as true to SQL, which it has a lot of, like there's a lot of SQL in LLMs training data. And it tries to stay as true to that, like ether. So it like picks it up really easy.
What alpha means and limits of the syntax
No, no, I was going to say, I think that that covers the first part. And I was going to ask, I was going to say, can I like rephrase my question? Because I said, have you reached the limits of it? I guess what I'm really saying is like, could you talk a little bit about what alpha means? What being an alpha means? Because have you reached the limits is definitely not a challenge. It's more like, hey, let's talk about where the state of things right now, where we hope they will go and how people can help give feedback too.
Yeah, I can start by saying we haven't reached the limit, but we have, I will entertain that idea a bit first, because it does like trying to be true to the SQL syntax and the SQL field also provides us with some limits in what we can do to express ourselves. And one of them is like, in R and Python, you can just like make up functions and pass them into other functions. So like if you wanted to quickly make a summary function, you'll pass it into the stat summary layer and you can do all sorts of things. We're limited here. We don't want people like, it's not even possible in a sensible way to create functions on the fly. So we have to create a syntax that is maximally expressive without resorting to these cheap tricks.
And that is limiting, but it also forces us to think like very clearly around the syntax. There is no escape hatch of just passing the function and like you deal with it. Like we can't do that. We have to think all these things through. So that's also been very interesting.
What does alpha mean? It means that you should probably not build a company around it yet. I think I am like, you could call it better or alpha. I don't put too much into this. It's to me, it just means that it's unfinished and it's subject to change if we find out that something doesn't work as we want it to. In terms of polish, I think we are way beyond the alpha mark there in terms of what it can do and so on. I think we have superseded what we set out to do by many, many leaks.
So alpha is just us being honest that it's still a very, very active development and we're still feeling our way around it. But as you can see, I think from the website as well, I don't want to rewrite all of this documentation. So we're staying pretty, it's fairly solid as it is right now. And I think if you learn it now, you will not wake up in six months and see it being changed drastically and you have to relearn it in any way.
Upcoming spatial support
There are things that we really want to get in. So this is spatial stuff. It's one of these things that will be part of the next release. So Jian, my co-developer, has been working on this for a month or so with very rich spatial support and support for all of these crazy projections and sort of things. That also presents us with some interesting, let's say, challenges, because usually when you have SQL data, you don't have borders of countries, right? You have a country and you have some value for it, and you want to make a chloropleth map or whatever.
So how in R, it's more normal to like, well, you just like, you know, you have a lot of data, well, you just like download some data from natural earth, and then you do left join and you'll have your like all of these kind of things. And working with spatial data is, I think, even more foreign for your regular pure SQL user, because spatial data is usually not part of a database, right? So how do we, without making cheap excuses and polluting the syntax with unpure escape hatches, how do we provide a nice experience for people that are not super into spatial data, but want to make a chloropleth map, for instance, or want to plot location of instances on the globe, like earthquake centers or whatever, like these kind of things. So that's pretty interesting. We're still feeling a way around that.
Community questions and use cases
All right. Well, I think that the questions that we have rolling in can lead us in a cool direction. So let me do a couple of things. One is, I want to read a comment from Tara. Tara or Tara, I don't know how you say your name. I'm so sorry I said it wrong. Tara says, I love this. At work, we query more often than we code in R, and not everyone codes in R. This has been a pain point for me to do some minimal statistical review slash graph creation directly in SQL for those simpler queries before needing to move to the query data in R Python.
And I think that that is a great explanation for anybody who's like, who is this good for? Who would this be good for? Who would be a great use case? There are loads of people that I know in the data world who actually don't code in R or Python at all, who do all of their querying in SQL, and then they might move things over into a Power BI or Tableau or Looker or something else like that. And it would be great to be able to visualize what they're looking at before they take that step of porting it over and use all of that time, effort, or memory to do that. So thank you, Tara, for sharing that.
Kevin had also asked, do you imagine any useful integration between Orbital and ggsql? I know we don't have Emil here to ask that. It's kind of like, it's coming from the same place, right? It is, yeah. How to express something as database queries and get something back. I have not, to be honest, it sounds like an interesting coupling that I have not thought about, so I won't. All right. Watch this space, everybody. We'll ask Emil next time he's on.
Okay. NunnTobacco had also said, it really feels like this is skipping a lot of the backend steps, more so on the Gigi plot side, at least when you're generating a Viz without any styling. So what about styling? Are you purely focusing on creating basic plots just for now?
So right now you cannot style. So that is the honest question. And this is just an omission for now. It is certainly something that will appear. The reason for the omission is that styling is messy. And if you try to express it in, I guess, easy in ggplot2, if you just buy into one of the prebuilt themes, then it's easy. But the theme function is horrible in any way, their number of arguments and so on. So I think the reason why it's not there right now is that I have not yet decided on exactly how it should look. Rather than give a half-assed solution for it for now, I'd rather just say, right now it looks like theme gray and be happy with it.
Well, it does seem like there's one styling you can do, which is changing your labels. And Renato had asked if we could demonstrate the label clause. Is that something you could do with us on screen? Oh, yeah. Yeah, so that's more... It's a little bit of styling. I know that it's technically like, let's just make things correct here. But it does exist. It does exist to take control over labels. So you can have, you can set, like right now, the automatic label for the legend, the fill legend over here is just the name of the column. So that doesn't feel super nice. Instead, we'll call it, I guess it's a graph.
So the label styling is there. Other things you can do that feels sort of there is, for instance, to do like... What's the name? So we are really... Reliable? No. I'm sorry. I've been working on something else that is actually ggsql related, but not really. So it's been a while since I rinsed. That's okay, we're testing. Renaming, yeah, yeah. Look at the candles. Yeah. Yes, exactly. So you can do something like renaming, which allows you to rename the break labels, for instance, for an access or here for this over here. So let's say...
You can do something like that, but you can also, and this is kind of, you cannot pass in functions, but we've created our own little mini language to allow you to do kind of quick glue-ish interpolation of strings.
So the value will be inserted here, and then you can put things around it. And you can also specify like a printf specifier up here. So you could have...
Oh, yeah. So if you want horrible scientific formatting. So there are these kind of affordances right now around text that we have, but if you want to change like the color of grid lines, or if you want to change like the title font, all of these things doesn't exist now, but will certainly.
I think that if the question is around like, will it be like D3? Can you do like really off the rails visualization? Then no, this is for creating more regular plots.
Definitely feels like more EDA to me. You are having a conversation with your data and you want to look at it, much less I'm going to put this in a report, right? That I'm giving to an executive.
But we did have a question. We're going there. We're going there. Oh, okay. Yeah, absolutely. I'm not going to limit your potential.
We had a question, I think that it was from Ken, that said, could the color of the points be sized up? So do you have options available in the draw point clause?
Yes. Well, right now the color of the points are based on this mapping, right? The body mass. So the fill of the points are here. They're very small, so we could say setting size to something bigger. Oh, that's probably a bit too much.
There you go, Ken. So you can see, so this is the default continuous color scale, but we can do scale fill. Oh, we already have the scale fill. Yeah. We do, yeah. We have a scale bin. We can make it not binned. So we can see we have a gradient here instead.
And then we can name a palette if we want to. So we have tons of palettes in there. You can also just say from red to green.
Are they the basic built-ins like plasma or magma or whatever it is? Don't get me started. I've been asking the visualization king of his question. I gotta know. So these are the palettes that are built in.
So we have a bunch, right? So there is like all of the Viridis things. There's Color Brewer. There is the Scientific Color Scales from Krameri. There is a bunch of, there's our own like discrete scale, but we also have the Tableau and the D3 and more Color Brewer. So we have a bunch, right? You should not feel limited by the palettes.
Right now, we don't have. You can customize, which is nice. But Becca had asked a question that I also had as I was watching you play around, which was, are you pressing anything to render as you type? Or is it just executing as you're typing that code and recognizing in the web interface that you have typed good code?
Yeah, the web interface evaluates constantly. And it's this fast. Like it's very, very quick to update. If there is a syntax error, it will not change it. So you don't have this like flickering from like a blank plot or an error. So if I remove this, it'll just say execute error. Horrible errors now. We're going to improve that as well. But it will not render. Like it will just keep the old plot. So we don't have like flickering of going from good syntax to bad syntax, right?
But if I just change this, you can see it getting updated instantly. I'm not doing anything other than just typing things in.
ggsql with Snowflake and R data frames
Wonderful. Okay, and I think, Dave, I saw you ask a question about ggsql and Snowflake. I think we talked about that earlier, but it might've been before you got here that yes, ggsql and Snowflake is a thing. Thomas, if people do want to use ggsql and Snowflake, do they have to do anything specific to get it?
No, if they have Snowflake set up and have ODBC drivers for Snowflake, then things should work. Okay, great. I'm wielding the should with a very strong conviction. Everybody go test it if you have Snowflake and an ODBC driver. There's probably some jankiness still, but it should work. Yes, good reminder that this isn't alpha, right? Yes, exactly. If you hit problems, come back here and talk to us and let us know.
Christoph had also asked if you could demo the visualize from with an R data frame that you constructed in the R console in Positron. The ODBC backend makes sense, he says. I'm not clear on how visualize from accesses a data frame.
Um, yes. So I will do this by not writing things because right now, so for the R package, what it provides is a way to connect to GD or execute ggsql from within an R process. And then this has this knitter engine that allows you to put in like a ggsql block like this, right? And when you then knit your R markdown or your Quarto document, then it will do what expected.
So this website is built with Quarto. So this is actually what we see up here and it creates this thing, right? Um, so in this particular instance, this allows us to have one document where you start by creating, so this is an R, some R code and we create a new data frame that's called gapminder underscore 2002. And then we can fetch that in our ggsql code by just prefixing R colon. Um, and it's just there, right?
So that is how data flows in a Quarto document or an R markdown document. You can do the same with Python. It's just a py colon instead. So you can like within the context of Quarto document, you can intermingle all these things. If you're in Positron, we don't have like a communication channel between the different engines running in Positron in the same way that in Quarto, you can communicate between R and Python chunks, but you can't do that in Positron, right? Those are confined things.
So if you have something in R, like some data that you've prepared in R and want to use ggsql, you can either use the ggsql package and just like, here is ggsql. Let's see, just a moment. Probably have it here. We have three minutes left, but that's okay. Yeah, so you can do something like this where you just like pass in a string, giving your ggsql or just pure SQL code and render it like you would render, like just printing the plot and it will render.
So if you're in R and want to play with it, then the R package is probably the easiest thing. Otherwise you can write to parquet files and just like pick them up automatically from ggsql. Good deal. It looks like the documentation is amazing.
Rapid fire questions
Well, I have a couple of rapid fire questions for you that you can answer very succinctly if you don't want to give any details, but Hubert had asked, can you use ggsql in a Shiny app? Yes, you can. Okay, yes, you can.
And Dan asked, what was the hardest technical part of getting ggsql implemented? Syntax, which is not a, like it's a soft technical part, I would say, but like we have spent so much time nailing the syntax and have so many long discussions around syntax. Everything else I would say kind of clicks into place and it's not like it's a coding problem, but like it's solvable, like getting the syntax right is like steep in my heart. And that has been the toughest thing. Like that's been the cause for most discussions, I would say.
like we have spent so much time nailing the syntax and have so many long discussions around syntax. Everything else I would say kind of clicks into place and it's not like it's a coding problem, but like it's solvable, like getting the syntax right is like steep in my heart.
Well, speaking of syntax, which flavor of SQL is ggsql based on, asks Zach. We have gone very far to not buy into any particular flavor. So this is, if we're trying to do like, if we're trying to mimic a syntax, it's ANSI SQL, which is the boring like ground truth.
The only place where we have kind of needed to come up with something is this fat arrow thingy that doesn't exist in ANSI SQL, but we really needed something for assignment. Ass doesn't really cut it. It's different semantically than setting something. Right, a setting, yeah. Yeah, so that is the only thing that we have kind of bought into.
This thing up here, everything that comes before visualize, it's just like pushed to whatever backend you have. So if you have DuckDB there, you can write DuckDB SQL there, whatever. This is the only thing where we put kind of limits.
I think that Ken's question is what we will end on because we have one minute left and it pertains to that. He says, can you also pass an ODBC connection object directly to the header of a ggsql code block to query your database directly? Or do you have to convert to the data frame first in order, you know, in the context of like a QMD? Uh, I'm not sure I understand it, sorry.
That's okay, it's in the Discord and we can follow up afterwards. I can get a better answer. That one's a big one. We had a couple that were just hanging on there at the end and hopefully we can get an answer and stick those in the Discord. For example, Isabella said, how do we give feedback as it moves to beta? I think that GitHub might be a good place to do that.
GitHub, certainly if you're like, if you can see yourself becoming a user of this or your organization becoming a user, like we would love to just chat with you or just like our database is in this esoteric format and we would love a reader for that. Like we maintain a list of readers that we want to create. Like reach out there, reach out. Yeah, you can also reach out to Isabella and I and we can help connect you with the team. So you know where we are. We're easy to find. We're on Discord and Blue Sky all the time. We're on LinkedIn.
Thomas, this was so much fun. Thank you so much for showing us all of the things. It was a pleasure. Thanks for attending. Thank you.