What feels like a lifetime ago, I made almost all of the R tooling at Uber and actually implemented what was effectively exactly this on top of the R DB tooling. Everyone, I think correctly in retrospect, I showed it to thought it was more or less useless. I had hoped it would be a nice way to pull and instantly visualize data but that was rarely all that valuable like this.
ggsql: A Grammar of Graphics for SQL
51–60 of 91 posts
Re: ggsql: A Grammar of Graphics for SQL
#52This seems really cool, will try it out soon!
What made it click for me was the following snippet from: https://ggsql.org/get_started/grammar.html
> We’ve tried to make the learning curve as easy as possible by keeping the grammar close to the SQL syntax that you’re already familiar with. You’ll start with a classic SELECT statement to get the data that you want. Then you’ll use VISUALIZE (or VISUALISE ) to switch from creating a table of data to creating a plot of that data. Then you’ll DRAW a layer that maps columns in your data to aesthetics (visual properties), like position, colour, and shape. Then you tweak the SCALEs, the mappings between the data and the visual properties, to make the plot easier to read. Then you FACET the plot to show how the relationships differ across subsets of the data. Finally you finish up by adding LABELs to explain your plot to others. This allows you to produce graphics using the same structured thinking that you already use to design a SQL query.
Re: ggsql: A Grammar of Graphics for SQL
#53First, ggsql looks awesome. Can't wait to try it out.
Feedback: A notable omission in the ggsql docs: I cannot find any mention of the possible outputs. Can I output a graphic in PDF? In SVG? PNG? How do I control things like output dimensions (e.g., width=8.5in, height=11in)?
The closest I got was finding these few lines of example code in the docs for the Python library:
# Display or save
chart.display() # In Jupyter
chart.save("chart.html") # Save to fileRe: ggsql: A Grammar of Graphics for SQL
#54Soo can I put this on top of e.g. grafana?
Re: ggsql: A Grammar of Graphics for SQL
#55What feels like a lifetime ago, I made almost all of the R tooling at Uber and actually implemented what was effectively exactly this on top of the R DB tooling. Everyone, I think correctly in retrospect, I showed it to thought it was more or less useless. I had hoped it would be a nice way to pull and instantly visualize data but that was rarely all that valuable like this.
What do you think is missing? I'm a big SQL fan and the idea of direct SQL to X seems appealing at least on the surface.
Re: ggsql: A Grammar of Graphics for SQL
#561) does this alllw to export to Excel? 2) how to make manual adjustments?
My answers will probably disappoint 1) No (unless you count 'render to image and insert that into your excel document') 2) This is not possible - manual adjustments are not reproducible and we live by that ethos
> 2) This is not possible - manual adjustments are not reproducible and we live by that ethos
Just want to give you a high-five on that one. I've dealt with so many hand-adjusted plots in the past where they work until either the dataset changes just a little bit or the plot library itself gets upgraded... in both cases, the plots completely fall apart when you're not expecting it.
Re: ggsql: A Grammar of Graphics for SQL
#57First, ggsql looks awesome. Can't wait to try it out. Feedback: A notable omission in the ggsql docs: I cannot find any mention of the possible outputs. Can I output a graphic in PDF? In SVG? PNG? How do I control things like output dimensions (e.g., width=8.5in, height=11in)? The closest I got was finding these few lines of example code in the docs for the Python library: # Display or save chart.display() # In Jupyt…
Currently our only writer module is for vegalite, the output is a vegalite spec (JSON). Tools already exist to render this kind of output to an interactive chart, SVG, PNG, etc. with their own controls for sizing and the like.
Our ggsql Jupyter kernel can use these vegalite specifications to output charts in a Quarto document, for example.
In the future we plan to create a new high performance writer module from scratch, avoiding this intermediate vegalite step, at which point we’ll have better answers for your questions!
Re: ggsql: A Grammar of Graphics for SQL
#58Earlier quoted context omitted.
ggsql has the concept of a "reader", which can be thought of as the way ggsql interfaces with a SQL database. It handles the connection to the database and generating the correct dialect of SQL for that database. As an alpha, we support just a few readers today: duckdb, sqlite, and an experimental ODBC reader. We have largely been focusing development mainly around driving duckdb with local files, though duckdb has e…
So we could use this with Postgres by putting DuckDB in front with its Postgres extension, pointing to the source data in PG?
In principle, yes, that’s the idea! However I will say we have focused mainly on the grammar and using DuckDB for reading from local files for this alpha release, so I expect there may be some bugs around connecting to remote databases still to iron out!
Re: ggsql: A Grammar of Graphics for SQL
#59I applaud the project, and I completely agree that the concepts maps nicely to SQL. The R equivalent of a WITH data prep block followed by the VISUALIZE is pretty much how all my plotting code is structured.
However, I don't see what the benefits of this are (other than having a simple DSL, but that creates the yet another DSL problme) over ggplot2. What do I gain by using this over ggplot2 in R?
The only problem, and the only reason I ever leave ggplot2 for visualizations, is how difficult it is to do anything "non-standard" that hasn't already had a geom created in the ggplot ecosystem. When you want to do something "different" it's way easier to drop into the primitive drawing operations of whatever you're using than it is to try to write the ggplot-friendly adapter.
Even wrapping common "partial specificiations" as a function (which should "just work" imo) is difficult depending on whether you're trying to wrap something that composes with the rest of the spec via `+` or via pipe (`|>`, the operator formerly known as `%>%`)
Re: ggsql: A Grammar of Graphics for SQL
#60I applaud the project, and I completely agree that the concepts maps nicely to SQL. The R equivalent of a WITH data prep block followed by the VISUALIZE is pretty much how all my plotting code is structured. However, I don't see what the benefits of this are (other than having a simple DSL, but that creates the yet another DSL problme) over ggplot2. What do I gain by using this over ggplot2 in R? The only problem, an…
Side comment: |> and %>% aren't the same btw! The newish base pipe (|>) is faster but doesn't support using the dot (.) placeholder to pipe into something other than the first argument of a function, which can sometimes make things a little cleaner.