7 msยท
Modularizing SQL? (2007)
- Kinrany 6y agoA tangent: I wonder if a ReactJS-like rerendering could be used as a declarative alternative to schema migrations.
- Kinrany 6y agoHmm, I guess that's "State-based database delivery/migrations". I couldn't find much about them though.
- andreypopp 6y agoCheckout https://github.com/prometheusresearch/baseline-codebase/tree/baseline/master/src/rex.deploy https://github.com/prometheusresearch/baseline-codebase/tree... which does that. Very happy about how it works.
- andreareina 6y agoAlembic works with SQLAlchemy to do some limited diffing between the live schema and what's in the code. In general I prefer to build the migrations by hand based on the code diffs.
- javisantana 6y agoWe found a way to organize large SQL decomposing them in smaller chunks and organize those chunks like in a "jupiter notebook" so you also see results for intermediate results, an example: https://ui.tinybird.co/snapshot/7437e5efa1f944a0b7cc017751696ea3 https://ui.tinybird.co/snapshot/7437e5efa1f944a0b7cc01775169...
- chishaku 6y agoI highly recommend dbt (data build tool) for a modular approach to sql; postgres, snowflake, bigquery or redshift. https://github.com/fishtown-analytics/dbt https://github.com/fishtown-analytics/dbt
- veritas3241 6y agoSeconded! And you can build your own adapters for other databases as well.
- aylr 6y agoThirded! It is hands down the best data tool I've used in years. It's a total delight and composes SQL DAGs out of your SQL files using tiny bits of jinja templating. If you know SQL you can learn it in an hour.
- throwaway_pdp09 6y agoI'm afraid I don't understand it. I assume a data warehouse is in a relational DB anyway, so from https://docs.getdbt.com/docs/introduction/ https://docs.getdbt.com/docs/introduction/ "dbt (data build tool) enables analytics engineers to transform data in their warehouses by simply writing select statements. dbt handles turning these select statements into tables and views" So you are taking some runnable SQL and... running it? Why does SQL need to be turned into tables an views anyway? What problem is this solving?
- chrisjc 6y ago> Why does SQL need to be turned into tables an views anyway? What problem is this solving? So that data is aggregated, summarized, cleansed, transformed, join, normalized, whatever before you query it. It's much cheaper, faster, etc that doing all of that ahead of time instead of query time. > I assume a data warehouse is in a relational DB anyway Not sure what you mean that a data warehouse is in a relations DB? Data in data warehouses can be set up in a relational way, but it certainly doesn't have to be. Perhaps you need to get a better understanding of olap vs oltp? https://stackoverflow.com/questions/21900185/what-are-oltp-and-olap-what-is-the-difference-between-them https://stackoverflow.com/questions/21900185/what-are-oltp-a...
- throwaway_pdp09 6y ago> Not sure what you mean that a data warehouse is in a relations DB? That you're using an RDBMS of some sort. It understands SQL and the data is in the conventional tabular form. I understand olap vs oltp. My question was whether eg. snowflake was an RDBMS (I did check wikipedia and the snowflake website but neither said). If it wasn't then I could see the benefit of this tool). > So that data is aggregated, summarized... That's what SQL is good for, why (if we're already in an RDBMS) do I need another tool on top to do this? > It's much aggregated, summarized, cleansed, transformed, join, normalized ... that doing all of that ahead of time instead of query time. Of course, that's what a DW does as it pulls in new data. But that is what SQL also does rather well, so what does this tool bring to the party?
- kpmah 6y agoI had a go at writing a type-inferred composable language for database queries a few months ago: https://github.com/KMahoney/squee https://github.com/KMahoney/squee
- tlarkworthy 6y agoTerraform templating + SQL plays together pretty well. Certainly helps a fair bit with pattern reuse
- ramraj07 6y agoI solved this problem (to my personal satisfaction) by adapting sqlalchemy for this. Sqlalchemy was not designed for complex data warehouse ELT for sure, but all it took was abstract out a few "quirks" of sqlalchemy and I was able to get a framework that's as expressive as spark, and hence extremely modularizable (not to mention testable), and compiles to regular SQL very easily.
- andreareina 6y agoYou still need to test against a live database instance right?
- ramraj07 6y agoSometimes yes, sometimes no! If the sql you're using is testable syntactically in sqlite itself, then your tests can be very light and done on a in memory sqlite database. The standardization of a dataframe like object allows me to infer the schema going into and out of a method and automatically create and tear down the expected tables! However sometimes you use custom SQL for your specific dialect so the test has to happen on that database. But you can still write some isolation wrappers to run them on a development db. They probably won't run in a second so you might have to keep them in a separate pipeline or something
- chrisjc 6y agoAny chance you could get into a little more detail about this framework? Sounds very interesting. Any reason you developed your framework instead of using a tool like dbt?
- ramraj07 6y agoI will go into brief details, but will clear with my org and post more on HN later for sure. But the gist is I try to extract any sqlalchemy representation as an 'alias' object like: ORMModel.__table__.alias() select([query.c.id]).alias() And wrap it with a class that's called a DataFrame. This class can then have methods implemented such as 'with_column_renamed' and 'join' which can just take simple strings and do all the sqlalchemy heavy boilerplate inside. The goal is to make it almost similar to spark code, where each operation returns the DataFrame with the logic applied (continuously updating the internal alias object). With this DataFrame, we always know the schema of the query, so it makes it easy to write generic transformer functions and pass them to an 'apply' method just like in spark. You can always reach out to the power of sqlalchemy in any step to customize the logic, and write custom compilers for temp tables, etc. This also means you can write unit tests in a very generic fashion if you have standard transformations that process one DataFrame. One reason I didn't go with dbt is that we already have our codebase in python, and going with dbt would have fully siloed the pipelines to Jinja SQL. Also not a huge fan of a pipeline mandatorily split into hundreds of files often having just a few lines each!
- MarkusWinand 6y agoThe most important code management tool SQL has are views and the WITH clause. https://modern-sql.com/use-case/literate-sql https://modern-sql.com/use-case/literate-sql Something like "polymorpihc" views would be extremley useful, but is not there yet (in standard SQL). In other areas there are sublte features like the explicit WINDOW clause to avoid repeating similar OVER clauses. Breifly mentioned at the end of this doc: https://www.postgresql.org/docs/current/tutorial-window.html https://www.postgresql.org/docs/current/tutorial-window.html This was adopted in recent years by many FOSS-DBs, but not yet by the big commercial ones: https://modern-sql.com/static/sm-blog-2019-postgesql-11.over.en/T612.window-clause.zrNWMjbF.svg https://modern-sql.com/static/sm-blog-2019-postgesql-11.over... Similarily the chaining of grouping sets can be helpful in those very rare cases you need them. The Oracle Database intrdouced "SQL Macros" recently. For my taste, they are too 'rude' to make me happy on first sight (much like C #define): https://blog.dbi-services.com/oracle-20c-sql-macros-a-scalar-example-to-join-agility-and-performance/ https://blog.dbi-services.com/oracle-20c-sql-macros-a-scalar...
- switch007 6y agoLittle tip: don't use indents for URLs. URLs are parsed automatically and need no special formatting.
- MarkusWinand 6y agoThx, updated.
- yen223 6y agoBe careful with WITH clauses in Postgres though. Prior to Postgres 12, the engine always materialized queries inside the WITH clauses, even when its unnecessary. This can lead to certain queries being orders of magnitude slower than normal if written with a WITH clause.
- Tostino 6y agoLuckily 12 has been out a while now, and future versions will continue to not have this issue.
- bokwoon 6y agoDon't typical query builders allow for composing SQL from smaller building blocks?
- kolme 6y agoYes, this is the main reason why they exist in the first place.
- throwaway_pdp09 6y agoFrom my perhaps minimal experience with query builders they're there to make sql queries graphical and therefore presumably easier. IME they don't do a good job of that. If you an handle the semantics of SQL you can put a bit more work in and learn the syntax. It'll pay for itself rapidly.
- Kinrany 6y agoYes, but that's basically sugar for generating raw queries
- naranha 6y agoAs said in the comments, some abstractions are possible with stored functions and views, in PostgreSQL you also have generated columns now. I'm sure there are more. Abstractions built on ORMs or query builders have the disadvantage that they are tied to one language and framework and application, making them less reusable.
- eeereerews 6y agoNot really on topic, but what happened to LtU anyway?
- throwaway_pdp09 6y agoIt's evidently still there. It's a slow-post high-info site so not much happens for long periods. I'm not sure what you're asking.
- eeereerews 6y agoIt's slowed down a lot. There's been a total of 8 posts and 50 comments since 2019. There were 32 posts just in 2017.
- throwaway_pdp09 6y agoIt's a user driven site. If you have something relevant to it, post it! It's what will keep it alive. If you're like me who lurk because they've nothing to post bt are concerned about it dying, have a word with the guy running it, Ehud I think.
- bootywizard 6y agoWhether you're trying to do this for a data warehousing use case, or in an environment where you are creating production database queries will change the approach you need to take here. We have been working on SQLX for the data warehousing use case, allowing you to embed JavaScript into your SQL queries and making things like code re-use much easier while fitting in with your existing SQL dialect, might be of interest - https://docs.dataform.co/guides/sqlx https://docs.dataform.co/guides/sqlx. Some of the concepts in there are specific to our framework, some are not. For data warehousing, create more views! They are IMO the right level of abstraction for encapsulating common business logic, data definitions, joins etc, making composing downstream queries much easier. Managing lots of views like this requires some investment in a data modelling tooling tool however (Dataform, DBT etc). For generating queries used against production databases for building user applications, none of this applies.
- chrisjc 6y ago> For data warehousing, create more views! Agreed, but... the problem with views are they aren't parameterizable. In effect they are static templates. Fortunately, modern data warehouses often provide user defined table functions that accomplish pretty much the same thing, but allow you to "create input parameters to your view". Dataform looks interesting (how have I not heard of this before) but I wonder if they support UDTFs?
- nightski 6y agoSure they are, you just slap a where statement on your query when you query a view. Or am I missing something?
- chrisjc 6y agoSure, that's sufficient most of the time. However, there times when your view might span multiple tables and you want to ensure that full table scans aren't triggered before your view predicate is acted upon. This may also come down to how sophisticated your DBs sql optimizer is.
- ksri 6y agoI have been working JinjaSQL[1] for a while to simplify complex SQL Queries. JinjaSQL is a templating language, so you can use interpolation, conditionals, macros, includes and so on. It automatically identifies bind parameters, so you don't have you to keep track of them. 1. https://github.com https://github.com /hashedin/jinjasql
- branko_d 6y agoSQL Server's inline table-valued functions are a good start. They are essentially parametrized views, which are inlined and "disappear" at the point of use, so all the usual transformations are available to the query optimizer. They are also quite limited in a number of ways: - Can't parametrize on the underlying table or view, even when it is "compatible" with the function's body. Some sort of OOP-style polymorphism or even JavaScript-style "duck-typing" would be nice. - Can't parametrize the SELECT list or ORDER BY clause. - No type generics (i.e. ability to "vary" the parameter's type when that doesn't require changing the function's body). - Can't pass another function as an argument (what would be called delegate or function pointer in other languages). - Only one SQL statement in the function body (SQL Server 2019 has lifted this limitation somewhat, but we are not quite there yet). - Can't (explicitly) throw exceptions. - And probably other things that escape me at the moment... Some of these limitations apply to the stand-alone SQL as well, of course.
- throwaway_pdp09 6y agoReason for most of these is the optimiser. What if you have eg. 2 tables t1 and t2 which are structurally identical and have select * from parameterised_t where parameterised_t.x = 99 where parameterised_t is either t1 or t2 passed in as a param, the tables can still have very different statistics. The optimiser needs to know statically which it's getting. Of course, if t1 and t2 are structurally identical, you should not usually be splitting them into 2 tables, they should be one table partitioned logically with some predicate (an extra column) rather than physically (having 2 tables). - Can't parametrize the SELECT list or ORDER BY clause. ditto but worse. > - Can't (explicitly) throw exceptions. and that one's a bitch, and quite unnecessary.
- branko_d 6y ago> Reason for most of these is the optimiser. Well, optimiser as currently implemented, tying only one plan to each uniqe SQL text. There is nothing, in principle, preventing an implementation that produces multiple plans based on the parameter values. This would be useful not just for "advanced" parametrization I mentioned above, but also for simpler cases such as when a parameter being NULL could be handled via a different access path compared to non-NULL. > if t1 and t2 are structurally identical, you should not usually be splitting them into 2 tables Depends on constraints. For example, you might have two structurally same tables that reference (or are referenced by) different tables.
- Tarean 6y agoYou need an SQL optimizer to efficiently abstract over sql. There are a couple implementations, empirically producing good sql at runtime is kinda slow. The pathfinder compiler is meant for C# linq to mix sql and XML queries. It has backends in a couple languages but is kinda slow, and iirc has no support for aggregates. Database Supported Haskell builds on this but has support for basically all sql features and some no-sql backends. Needs some slight work to fix bit rot by now, and the compiler is kinda slow. https://hackage.haskell.org/package/DSH https://hackage.haskell.org/package/DSH The Ur language has great support for SQL, including updateable views. https://en.wikipedia.org/wiki/Ur_(programming_language) https://en.wikipedia.org/wiki/Ur_(programming_language) TL;DR: If you allow general nested results in your abstraction you need an optimizer. Optimizing aggregates in general is REALLY hard and slow.
- throwaway_pdp09 6y agoIf you do mere joins, you need an optimiser.
- default-kramer 6y agoDarn, I was going to do a show HN in a few days, but my side project too relevant here not to link to: https://docs.racket-lang.org/plisqin/index.html https://docs.racket-lang.org/plisqin/index.html As the warning says, it is NOT READY for any kind of use. But the SQL it generates is pretty close to what a human would write, so maybe it's fine to use. There are two main things that Plisqin offers that I haven't seen elsewhere: 1. Joins are values. This is wonderful for composability. When you say (from x SomeTable ....), x is bound as an "instance of SomeTable". Joins are also instances in the same way. 2. Traditional SQL-style aggregates are supported, but "grouped join aggregation" is a new concept in Plisqin. Described here: https://docs.racket-lang.org/plisqin/Aggregates.html https://docs.racket-lang.org/plisqin/Aggregates.html Besides those two, there is also a different approach to comparisons which eliminates 3VL: https://docs.racket-lang.org/plisqin/Nullability.html https://docs.racket-lang.org/plisqin/Nullability.html. This is only in the "strict" variant of Plisqin; the "unsafe" variant gives you 3VL back if you like it. I plan to finish up the documentation, and enhance the "Plisqin as a Research Language" section with more analysis of what works well and what gaps in the language I'd still like to patch.
- cryptonector 6y agoSCHEMAs, TYPEs, FUNCTIONs, VIEWs with and without INSTEAD OF TRIGGERs, TRIGGERs -- these things let you build abstractions in SQL.
- aformella 6y agoMy dream - enabling/disabling columns and tables: ๐๐๐๐๐๐ ๐๐๐๐๐๐๐๐ ๐๐๐๐ ๐ญ๐ฉ๐ฅ(๐ญ1, ๐ง๐๐๐๐๐) ๐๐ ๐๐๐๐๐๐ ๐ญ1.*, ๐ญ2.* ๐๐ ๐ง๐๐๐๐๐ ๐ ๐๐๐ ๐ญ1 ๐๐๐๐ ๐ญ2 ๐๐ (๐ญ๐ซ๐ฎ๐) ๐๐ ๐ง๐๐๐๐๐
- throwaway_pdp09 6y agoDon't get it - why do you think you need this?
- aformella 6y agoAll object-relational mappings would be in database. Why not function returning a query? Because you can't EXPLAIN ANALYZE function body.