5 ms·

As an analyst, I often am faced with a choice between - write a giant unmaintainable SQL query, or - pull everything to my PC and use pandas, to take advantag

As an analyst, I often am faced with a choice between - write a giant unmaintainable SQL query, or - pull everything to my PC and use pandas, to take advantage of its ability to build up results piece by piece. I wrote this library to try and enable that piece-by-piece development approach with SQL queries, without resorting to the mental overhead of full on query builders like SQLAlchemy or Linq. Seeking feedback - is this useful for you? is it at the right level of abstraction?

ramraj07 6y ago
Have you tried dbt? Even if you don't want to adapt their "all in SQL" idea, I definitely took inspiration from their Jinja templating idea and have similar libraries as you, except it uses Jinja templating to parameterize the SQL. This allows more logic to be put in the queries than just variable names!
- akdor1154 6y ago
  Huh, no I haven't come across dbt. Looks interesting! Jinja'd sql scares me a bit, but to be fair the string interpolation in my lib here scares me as well :)
  - ramraj07 6y ago
    Practical experience is that while there are kinks, Jinja and SQL marry fairly well together. Combining this idea and snowflake we have been able to create some extremely versatile metrics reporting systems that run with zero intervention I wouldn't have imagined a year or two back!
chaz6 6y ago
As someone in a similar position, my first impression is that this is going to be very useful in saving time coming up with big sql monstrosities. I will be following your project with interest and hope to be able to use it in the daily grind.
acidbaseextract 6y ago
If you're an analyst, I second the recommendation for dbt. Here's a podcast interview with the CEO of the company behind dbt that explains a lot of the philosophy, and I think will help you even if you don't end up using dbt: https://softwareengineeringdaily.com/2020/03/09/dbt-data-build-tool-with-tristan-handy/ https://softwareengineeringdaily.com/2020/03/09/dbt-data-bui...
hawkar20 6y ago
I like what you've done here. I've personally gone down the route of using SQLAlchemy to build queries. The primary reason is that SQLAlchemy lets me build up reuseable SQL elements, to encapsulate business logic, like metrics and custom dimensions in Python. In particular SQLAlchemy's hybrid expressions, that allow you to tie an SQL expression to a ORM model are super useful for doing this sort of thing. There's also first class support for CTEs, window functions and other advanced SQL features. For me there's just something "dirty" about using raw SQL in code, or even templating it, even with security concerns like injection attacks put aside. I second the recommendation for dbt. Especially if you're following an ELT architecture where you load your data into a data warehouse and then want to transform it. However dbt is more useful for transforming existing data, or aggregating data in batches. It's not a tool for generating SQL expressions on-the-fly like your library would allow you to do.
closed 6y ago
Interesting approach! I've been working on it from the other angle: having pandas code generate SQL. If you're interested in checking it out, happy to try and show how it would generate the query in your readme! https://github.com/machow/siuba https://github.com/machow/siuba One thing I was wondering about the gnarly ast stuff you mention, what about operator overloading? E.g. Q("Select a from" + subquery + "where a < 1")
default-kramer 6y ago
Does the unit of composition have to be a complete query/CTE? Theoretically it seems like it could be any fragment of SQL, but the docs seem to imply it must be a complete query. I created something similar recently: https://docs.racket-lang.org/plisqin/index.html https://docs.racket-lang.org/plisqin/index.html. At its core, it is also a library for composing fragments of SQL, but it has some novel (as far as I know) ideas. The most notable is that joins are values (or "expressions", if you prefer) that can be returned from a procedure like any other value. I was hoping that the world would realize "that's obviously how query builders should work" and copy the approach when starting new projects, but that hasn't happened.
prostodata 6y ago
> pull everything to my PC and use pandas, to take advantage of its ability to build up results piece by piece. Most difficulties in data processing arise from the need to process multiple tables. It is highly difficult in SQL but it can be also difficult in pandas especially for complex analytical queries. Indeed, in pandas you still have to use the same join and groupby operations as in SQL. An alternative to SQL, join-groupby, and map-reduce is developed in the Prosto library: - https://github.com/prostodata/prosto https://github.com/prostodata/prosto Functions matter! No join-groupby, No map-reduce. It is a layer over pandas and its main distinguishing feature is that it relies on functions and function operations for data processing as opposed to using only sets and set operations in SQL and other set-oriented approaches

ramraj07 6y ago

Have you tried dbt? Even if you don't want to adapt their "all in SQL" idea, I definitely took inspiration from their Jinja templating idea and have similar libraries as you, except it uses Jinja templating to parameterize the SQL. This allows more logic to be put in the queries than just variable names!

akdor1154 6y ago
Huh, no I haven't come across dbt. Looks interesting! Jinja'd sql scares me a bit, but to be fair the string interpolation in my lib here scares me as well :)
- ramraj07 6y ago
  Practical experience is that while there are kinks, Jinja and SQL marry fairly well together. Combining this idea and snowflake we have been able to create some extremely versatile metrics reporting systems that run with zero intervention I wouldn't have imagined a year or two back!

chaz6 6y ago

As someone in a similar position, my first impression is that this is going to be very useful in saving time coming up with big sql monstrosities. I will be following your project with interest and hope to be able to use it in the daily grind.

acidbaseextract 6y ago

If you're an analyst, I second the recommendation for dbt. Here's a podcast interview with the CEO of the company behind dbt that explains a lot of the philosophy, and I think will help you even if you don't end up using dbt: https://softwareengineeringdaily.com/2020/03/09/dbt-data-build-tool-with-tristan-handy/ https://softwareengineeringdaily.com/2020/03/09/dbt-data-bui...

hawkar20 6y ago

I like what you've done here. I've personally gone down the route of using SQLAlchemy to build queries. The primary reason is that SQLAlchemy lets me build up reuseable SQL elements, to encapsulate business logic, like metrics and custom dimensions in Python. In particular SQLAlchemy's hybrid expressions, that allow you to tie an SQL expression to a ORM model are super useful for doing this sort of thing. There's also first class support for CTEs, window functions and other advanced SQL features. For me there's just something "dirty" about using raw SQL in code, or even templating it, even with security concerns like injection attacks put aside. I second the recommendation for dbt. Especially if you're following an ELT architecture where you load your data into a data warehouse and then want to transform it. However dbt is more useful for transforming existing data, or aggregating data in batches. It's not a tool for generating SQL expressions on-the-fly like your library would allow you to do.

closed 6y ago

Interesting approach! I've been working on it from the other angle: having pandas code generate SQL. If you're interested in checking it out, happy to try and show how it would generate the query in your readme! https://github.com/machow/siuba https://github.com/machow/siuba One thing I was wondering about the gnarly ast stuff you mention, what about operator overloading? E.g. Q("Select a from" + subquery + "where a < 1")

default-kramer 6y ago

Does the unit of composition have to be a complete query/CTE? Theoretically it seems like it could be any fragment of SQL, but the docs seem to imply it must be a complete query. I created something similar recently: https://docs.racket-lang.org/plisqin/index.html https://docs.racket-lang.org/plisqin/index.html. At its core, it is also a library for composing fragments of SQL, but it has some novel (as far as I know) ideas. The most notable is that joins are values (or "expressions", if you prefer) that can be returned from a procedure like any other value. I was hoping that the world would realize "that's obviously how query builders should work" and copy the approach when starting new projects, but that hasn't happened.

prostodata 6y ago

> pull everything to my PC and use pandas, to take advantage of its ability to build up results piece by piece. Most difficulties in data processing arise from the need to process multiple tables. It is highly difficult in SQL but it can be also difficult in pandas especially for complex analytical queries. Indeed, in pandas you still have to use the same join and groupby operations as in SQL. An alternative to SQL, join-groupby, and map-reduce is developed in the Prosto library: - https://github.com/prostodata/prosto https://github.com/prostodata/prosto Functions matter! No join-groupby, No map-reduce. It is a layer over pandas and its main distinguishing feature is that it relies on functions and function operations for data processing as opposed to using only sets and set operations in SQL and other set-oriented approaches