Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

ggsql: A Grammar of Graphics for SQL

opensource.posit.co

Post reply on HN
Print view View original

41–50 of 91 posts

Re: ggsql: A Grammar of Graphics for SQL

#41

Post by stevedh »

thomasp85 wrote: post #28
data_ders wrote: post #22

ok, this is definitely up my alley. color me nerd-sniped and forgive the onslaught of questions. my questions are less about the syntax, which i'm largely familiar with knowing both SQL and ggplot. i'm more interested in the backend architecture. Looking at the Cargo.toml [1], I was surprised to not see a visualization dependency like D3 or Vega. Is this intentional? I'm certainly going to take this for a spin and I…

Let me try to not miss any of the questions :-) ggsql is modular by design. It consists of various reader modules that takes care of connecting with different data backends (currently we have a DuckDB, an SQLite, and an ODBC reader), a central plot module, and various writer modules that take care of the rendering (currently only Vegalite but I plan to write my own renderer from scratch). As for deployment I can only…

Intermediate Representation

Re: ggsql: A Grammar of Graphics for SQL

#42

Post by thomasp85 »

nicoritschel wrote: post #39

This is neat. I do wish there was a way for this to gracefully degrade in contexts without support for the grammar, though. I devised a similar in spirit (inside SQL, very simplified vs GoG) approach that does degrade (but doesn't read as nice): https://sqlnb.com/spec

I'm not quite sure I understand what you mean by "degrade in context" - care to elaborate?

Re: ggsql: A Grammar of Graphics for SQL

#43

Post by thomasp85 »

jiehong wrote: post #35

The cli only produces vega-lite[0] json graphics, right? It would be nice if it included a rendering engine. [0]: https://github.com/vega/vega-lite

That is certainly in the pipeline. We chose to start with vegalite so we could focus on the internals of the representation

Re: ggsql: A Grammar of Graphics for SQL

#44

Post by anentropic »

thomasp85 wrote: post #13
password4321 wrote: post #11

Earlier quoted context omitted.

Yes this was my question as well, an example showing all the plumbing/dependencies to generate a graph from an external database server would be very helpful.

We certainly plan to create a few videos showing how to set it up and use it. If you use it in Positron with the ggsql extension it can interact directly with the connection pane to connect to the various backends you have there

Please just document the library itself before making a bunch of videos

I eventually found this readme https://github.com/posit-dev/ggsql/tree/main/ggsql-python which tells me far more than anything I found on the website

Re: ggsql: A Grammar of Graphics for SQL

#45

Post by nicoritschel »

thomasp85 wrote: post #42
nicoritschel wrote: post #39

This is neat. I do wish there was a way for this to gracefully degrade in contexts without support for the grammar, though. I devised a similar in spirit (inside SQL, very simplified vs GoG) approach that does degrade (but doesn't read as nice): https://sqlnb.com/spec

I'm not quite sure I understand what you mean by "degrade in context" - care to elaborate?

If you're familiar with the percent format for jupyter notebooks, something like that— so things gracefully degrade in a more "basic" execution context.

# %%

foo = 1

# %%

print(foo)

Above is notebook with two "cells" & also a valid Python script. Perhaps it matters less with SQL vs Python, but it's a nice property.

Re: ggsql: A Grammar of Graphics for SQL

#46

Post by thomasp85 »

stevedh wrote: post #41
thomasp85 wrote: post #28

Earlier quoted context omitted.

Let me try to not miss any of the questions :-) ggsql is modular by design. It consists of various reader modules that takes care of connecting with different data backends (currently we have a DuckDB, an SQLite, and an ODBC reader), a central plot module, and various writer modules that take care of the rendering (currently only Vegalite but I plan to write my own renderer from scratch). As for deployment I can only…

Intermediate Representation

Ah - yes, in theory you could create a "ggplot2 writer" which renders the plot object to an R file you can execute. It is not too far away from the current Vega-Lite writer we use. The other direction (ggplot2->ggsql) is not really feasible

Re: ggsql: A Grammar of Graphics for SQL

#47

Post by thomasp85 »

nicoritschel wrote: post #45
thomasp85 wrote: post #42

Earlier quoted context omitted.

I'm not quite sure I understand what you mean by "degrade in context" - care to elaborate?

If you're familiar with the percent format for jupyter notebooks, something like that— so things gracefully degrade in a more "basic" execution context. # %% foo = 1 # %% print(foo) Above is notebook with two "cells" & also a valid Python script. Perhaps it matters less with SQL vs Python, but it's a nice property.

Ah - I don't think it really matters here, but if you find yourself in need then you can open a GitHub issue and we can discuss

Re: ggsql: A Grammar of Graphics for SQL

#48

Post by johnthescott »

tantalor wrote: post #27
anentropic wrote: post #9

Maybe I skim read it too fast, but I did not find any clear description in the blog post or website docs of how this relates to SQL databases I was kind of guessing that it doesn't run in a database, that it's a SQL-like syntax for a visualisation DSL handled by front end chart library. That appears to be what is described in https://ggsql.org/get_started/anatomy.html But then https://ggsql.org/faq.html has a section…

> SQL databases ... confused "SQL" and "databases" are different things SQL is a declarative language for data manipulation. You can use SQL to query a database, but there's nothing special about databases. You can also write SQL to query other non-database sources like flat files, data streams, or data in a program's memory. Conversely, you can query a database without SQL.

> Conversely, you can query a database without SQL.

fond memories of quel.

Re: ggsql: A Grammar of Graphics for SQL

#49

Post by chatmasta »

georgestagg wrote: post #15
anentropic wrote: post #9

Maybe I skim read it too fast, but I did not find any clear description in the blog post or website docs of how this relates to SQL databases I was kind of guessing that it doesn't run in a database, that it's a SQL-like syntax for a visualisation DSL handled by front end chart library. That appears to be what is described in https://ggsql.org/get_started/anatomy.html But then https://ggsql.org/faq.html has a section…

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?

Re: ggsql: A Grammar of Graphics for SQL

#50

Post by epgui »

getnormality wrote: post #19

I skimmed the article for an explanation of why this is needed, what problem it solves, and didn't find one I could follow. Is the point that we want to be able to ask for visualizations directly against tables in remote SQL databases, instead of having to first pull the data into R data frames so we can run ggplot on it? But why create a new SQL-like language? We already have a package, dbplyr, that translates betwe…

This isn't about ggplot (or any particular library) per se, it's about using a flavour of SQL with a grammar of graphics: https://en.wikipedia.org/wiki/Wilkinson%27s_Grammar_of_Graph...

What makes it interesting is the interface (SQL) coupled with the formalism (GoG). The actual visualization or runtime is an implementation detail (albeit an important one).

Post reply on HN