Alembic 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.
I 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.
Sometimes 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
Any 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?
I 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:
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!
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:
Be 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.
Nice answer, thanks. Have you ever used this syntax? Has anybody here.
I ask because I'm a heavy SQL user when I'm working, and I've never ever found the need (almost used a cube once but not quite) (I guess it's maybe for analysis rather than transactional stuff).
Yeah, we use CUBE to create caches of aggregate data cut by various combinations of criteria, which allows us to keep UIs with lots of filters snappy, instead of having to hit a view.
I abused it in the past for aggregating every bitmask of CIDR, where the underlying data was by IP. So the groups were functions of the IP, one per mask level. We needed aggregations of arbitrary levels and this worked relatively well. We also tried it using it for some reporting that was tied to organizational hierarchy - same intuition - the group sets included org, parent, grandparent, etc ...
The polymorphic view as I would like them would enable us to re-use entiere <query expressions> (i.e. "subqueries") applied to different data sources (i.e. "tables").
E.g (made-up syntax)
CREATE TEMPLATE VIEW numbered_by_ts(t1) AS (
SELECT t1.*
, ROW_NUMBER() OVER(ORDER BY ts) AS rn
FROM t1
)
e.g. use it like this:
SELECT *
FROM numbered_by_ts(table_name);
I think they should behave more like C++ templates rather than C pre-processor makros[1], thus I used the keyword TEMPLATE in CREATE VIEW. If you provide a table that doesn't have a column TS, it would be a syntax error, just like it is with C++ templates.
With common table expressions it would be easily possible to inject entire subqueries into the template view:
WITH cte AS (SELECT ... FROM ...)
SELECT *
FROM numbered_by_ts(cte);
For completness, WITH queries should also be allowed to be TEMPLATEs.
Speaking of the WITH clause, SQLite support ploymorphic views because SQLite CTEs are visible in elements that are "generally contained"[2] in the statement (the SQL standard defines it differently[3]).
Example:
CREATE TABLE t1 (
ts TIMESTAMP
);
INSERT INTO t1 VALUES ('2000-01-01 00:00:00');
CREATE VIEW numbered_by_ts AS
SELECT t1.*
, ROW_NUMBER() OVER(ORDER BY ts) AS rn
FROM t1
;
CREATE TABLE ta (
ts TIMESTAMP
);
INSERT INTO ta VALUES ('2010-01-01 00:00:00');
CREATE TABLE tb (
no_ts TIMESTAMP
);
INSERT INTO tb VALUES ('2020-01-01 00:00:00');
SELECT * FROM numbered_by_ts;
-- accesses t1, resturns year 2000
WITH t1 AS (SELECT * FROM ta)
SELECT * FROM numbered_by_ts;
-- accesses ta, resturns year 2010
WITH t1 AS (SELECT * FROM tb)
SELECT * FROM numbered_by_ts;
-- syntax error: no "ts" column in tb
The polymorpic table functions, as introduced by SQL:2016, might be able to accomplish all of that but for me they feel like using a sledgehamer for cracking a nut.
[1] As far as I understand the Oracle 20c SQL makros behave like C pre-processor makros.
Thank you! Very surprising behavior from SQLite, although it might come in handy if I use it carefully.
Although I ask "why should macros have to return complete query expressions?" To me, something like the following would be much more flexible.
Example 1, %ts_number returns a single numeric expression:
CREATE MACRO %ts_number(x) AS
ROW_NUMBER() OVER(ORDER BY x.ts)
select x.*, %ts_number(x) as rn
from SomeTable x
Example 2, %%foo returns 3 clauses to be combined into a larger query:
CREATE MACRO %%foo(x) AS
WHERE x.ts > 0
ORDER BY ROW_NUMBER() OVER (ORDER BY x.ts)
SELECT ROW_NUMBER() OVER (ORDER BY x.ts) as rn
select x.*
from SomeTable x
%%foo(x) -- this combines the 3 clauses into this query
-- So the result is equivalent to
select x.*, ROW_NUMBER() OVER (ORDER BY x.ts) as rn
from SomeTable x
WHERE x.ts > 0
ORDER BY ROW_NUMBER() OVER (ORDER BY x.ts)
I suppose it could even be up to the macro how its clauses get combined (before or after any existing clauses). In the following example, `...` is a special language element for use in macros. %%foo would append to the end of all the existing WHERE and ORDER BY clauses, but it would prepend to the existing SELECT clauses:
CREATE MACRO %%foo(x) AS
WHERE ... AND x.ts > 0
ORDER BY ..., ROW_NUMBER() OVER (ORDER BY x.ts)
SELECT ROW_NUMBER() OVER (ORDER BY x.ts) as rn, ...
From 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.
As 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.
We 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:
Thirded! 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.
"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?
> 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?
> 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?
DWs like Snowflake certainly share some features you'll find in traditional RDBMSs, but I'm not sure I would classify it as one. https://www.quora.com/Is-Snowflake-a-RDBMS
> 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?
Writing, versioning, testing, deploying, monitoring all that complicated SQL is very challenging. This is what DBT is good for. DBT doesn't replace SQL, it simply augments it by allowing you templatize, modularize, figure out all the inter-dependencies, etc...
> 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?
Sounds like your talking more about ETL, but from what I understand DBT is really useful for what happens after ingestion. Transforming, refining, aggregating, etc from one internal DW source to another. This is where you would want to use SQL, but again, DBT doesn't replace SQL, it augments it.
----
BTW, I've never used DBT and have only really looked into it in the last 24hrs since we're really struggling with the pain of managing all of our ETL (table->table) SQL. I really see where it could make our lives a lot simpler, but it doesn't seem to provide the functionality that we need for our workloads.
We're using Snowflake change streams to build refined, summary and aggregation tables off an ingestion table (Kafka Connect to SF). We use change streams on that ingestion table to track new data that we can refined, summarized and aggregated into final tables in a near real-time fashion that's used for analytics and reporting. Change streams (and tasks and procedures) is not something that DBT seems to be ready for, but it's probably understandable since they're trying to provide functionality to more DWs than just SF.
Whether 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. 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.
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?
Sure, 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.
I 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.
SQL 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.
Reason 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.
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.
> ... nothing, ... preventing an implementation that produces multiple plans based on the parameter values
I'm afraid there is - time and space. Optimising is a very expensive process that can take well over a second to optimise a complex query on MSSQL (and that's not because the best plan is always found; it has a timout after which the best plan so far found is returned. Complex queries will hit the timeout and a suboptimal plan will most likely be returned), and the compiled query plan can take megabytes.
Suppose you want to parameterise by fields selected. That's a combination, which involves factorial. tl;dr that grows fast.
> you might have two structurally same tables that reference (or are referenced by) different tables
Umm. I'd say if their semantics were different enough that they be 2 distinct tables then their identicallity is coincidental. I'd have to see some actual cases before arguing further (edit: though I have come across this, so maybe, but it's not IME common).
Sure, I'm well aware of that. I did say "in principle", after all.
However, we are generating multiple plans already by changing the SQL text (by concatenating-in the table names, SELECT list, ORDER BY and so on).
There is currently no good solution in SQL even for parametrizing different "shapes" of WHERE, so we use dynamic SQL for that, generating even more plans.
There is some low-hanging fruit here.
> Suppose you want to parameterise by fields selected. That's a combination, which involves factorial. tl;dr that grows fast.
You don't have to do it in advance, though.
Today, when you change the text of the SELECT list, you generate a new plan anyway. Parametrized SELECT list would be no different.
> I'd have to see some actual cases before arguing further (edit: though I have come across this, so maybe, but it's not IME common).
Most junction tables have essentially identical structure, but different foreign keys. This is also pretty common when implementing things like "tags" or "attributes" that apply to more than one kind of object...
But it doesn't really matter whether all fields are identical, only those that are actually used by the given SQL statement. In a hypothetical future SQL, you could implement your algorithm generically and then apply it on any table or view that is "compatible enough", even if it has some extra fields that your SQL statement doesn't use.
In today's SQL, unfortunately, you are condemned to repeating the (essentially) same code.
Your point about generating query plans anyway if using dynamic sql is well taken. I can't argue - but see below.
> There is some low-hanging fruit here.
syntactically perhaps. But it is bloody expensive to generate new plans. It's quite reasonable for a query to take one or two or even three orders of magnitude more CPU for plan generation than for actually running it. Neither dynamic sql nor parameterisation will deal with that (that I can see). I think we're stuck with expensive repeated query planning.
> Most junction tables have essentially identical structure
Yes but they are used only to link 2 tables together (I call these join tables because they're just used for joining 2 tables). They aren't used in any other context
> This is also pretty common when implementing things like "tags" or "attributes" that apply to more than one kind of object...
Maybe. I'd need to sit down with you and talk it over (not happening sadly!)
I think parameterised SQL is necessary but also a wide open door to clueless programmers massively increasing the CPU load and I've seen what cluelessness can do to perforemance in an exists-right-now nonparameterised RDBMS. OTOH I've never liked the idea that a tool's power should be reduced to avoid numpties doing damage. You modify the numpty, not the tool.
> I think we're stuck with expensive repeated query planning.
I agree with that (sadly). I'd like to see more expressive SQL, but I'm not saying it will make the situation with planning any better. I'd argue it should not make it much worse either, if done right.
You 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
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.
Besides those two, there is also a different approach to comparisons which eliminates 3VL: 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.
63 comments
[ 3.8 ms ] story [ 144 ms ] threadORMModel.__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!
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
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...
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...
Do you mean 'group by grouping sets'? If not, could you clarify.
E.g.
is equivalant to and (however, I find the last one odd: I like to keep the "tenant" part seperate from the actual grupings you'd like to do).I ask because I'm a heavy SQL user when I'm working, and I've never ever found the need (almost used a cube once but not quite) (I guess it's maybe for analysis rather than transactional stuff).
E.g (made-up syntax)
e.g. use it like this: I think they should behave more like C++ templates rather than C pre-processor makros[1], thus I used the keyword TEMPLATE in CREATE VIEW. If you provide a table that doesn't have a column TS, it would be a syntax error, just like it is with C++ templates.With common table expressions it would be easily possible to inject entire subqueries into the template view:
For completness, WITH queries should also be allowed to be TEMPLATEs.Speaking of the WITH clause, SQLite support ploymorphic views because SQLite CTEs are visible in elements that are "generally contained"[2] in the statement (the SQL standard defines it differently[3]).
Example:
SQLite is the only system I know of that implements WITH like that. See "views bypass with" here: https://modern-sql.com/feature/with#compatibilityThe polymorpic table functions, as introduced by SQL:2016, might be able to accomplish all of that but for me they feel like using a sledgehamer for cracking a nut.
[1] As far as I understand the Oracle 20c SQL makros behave like C pre-processor makros.
[2] "Generally contain" as defined by "Syntactic containment" in ISO/IEC 9075-1. The 2011 version of that can be downloaded for free at ISO: http://standards.iso.org/ittf/PubliclyAvailableStandards/c05...
[3] ISO/IEC 9075-2, "<query expression>": the definition of "query name in scope" says "contained", not "generally contained".
Although I ask "why should macros have to return complete query expressions?" To me, something like the following would be much more flexible.
Example 1, %ts_number returns a single numeric expression:
Example 2, %%foo returns 3 clauses to be combined into a larger query: I suppose it could even be up to the macro how its clauses get combined (before or after any existing clauses). In the following example, `...` is a special language element for use in macros. %%foo would append to the end of all the existing WHERE and ORDER BY clauses, but it would prepend to the existing SELECT clauses: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.
https://ui.tinybird.co/snapshot/7437e5efa1f944a0b7cc01775169...
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.
https://github.com/fishtown-analytics/dbt
"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?
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-a...
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?
> 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?
Writing, versioning, testing, deploying, monitoring all that complicated SQL is very challenging. This is what DBT is good for. DBT doesn't replace SQL, it simply augments it by allowing you templatize, modularize, figure out all the inter-dependencies, etc...
> 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?
Sounds like your talking more about ETL, but from what I understand DBT is really useful for what happens after ingestion. Transforming, refining, aggregating, etc from one internal DW source to another. This is where you would want to use SQL, but again, DBT doesn't replace SQL, it augments it.
----
BTW, I've never used DBT and have only really looked into it in the last 24hrs since we're really struggling with the pain of managing all of our ETL (table->table) SQL. I really see where it could make our lives a lot simpler, but it doesn't seem to provide the functionality that we need for our workloads.
We're using Snowflake change streams to build refined, summary and aggregation tables off an ingestion table (Kafka Connect to SF). We use change streams on that ingestion table to track new data that we can refined, summarized and aggregated into final tables in a near real-time fashion that's used for analytics and reporting. Change streams (and tasks and procedures) is not something that DBT seems to be ready for, but it's probably understandable since they're trying to provide functionality to more DWs than just SF.
On your recommendation I'll have a look at DBT, thanks.
Whatever you're doing at work sounds really complex, possibly worsened by sheer quantity of data. I wish you luck with it!
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. 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.
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?
This may also come down to how sophisticated your DBs sql optimizer is.
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 /hashedin/jinjasql
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.
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.
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.
I'm afraid there is - time and space. Optimising is a very expensive process that can take well over a second to optimise a complex query on MSSQL (and that's not because the best plan is always found; it has a timout after which the best plan so far found is returned. Complex queries will hit the timeout and a suboptimal plan will most likely be returned), and the compiled query plan can take megabytes.
Suppose you want to parameterise by fields selected. That's a combination, which involves factorial. tl;dr that grows fast.
> you might have two structurally same tables that reference (or are referenced by) different tables
Umm. I'd say if their semantics were different enough that they be 2 distinct tables then their identicallity is coincidental. I'd have to see some actual cases before arguing further (edit: though I have come across this, so maybe, but it's not IME common).
Sure, I'm well aware of that. I did say "in principle", after all.
However, we are generating multiple plans already by changing the SQL text (by concatenating-in the table names, SELECT list, ORDER BY and so on).
There is currently no good solution in SQL even for parametrizing different "shapes" of WHERE, so we use dynamic SQL for that, generating even more plans.
There is some low-hanging fruit here.
> Suppose you want to parameterise by fields selected. That's a combination, which involves factorial. tl;dr that grows fast.
You don't have to do it in advance, though.
Today, when you change the text of the SELECT list, you generate a new plan anyway. Parametrized SELECT list would be no different.
> I'd have to see some actual cases before arguing further (edit: though I have come across this, so maybe, but it's not IME common).
Most junction tables have essentially identical structure, but different foreign keys. This is also pretty common when implementing things like "tags" or "attributes" that apply to more than one kind of object...
But it doesn't really matter whether all fields are identical, only those that are actually used by the given SQL statement. In a hypothetical future SQL, you could implement your algorithm generically and then apply it on any table or view that is "compatible enough", even if it has some extra fields that your SQL statement doesn't use.
In today's SQL, unfortunately, you are condemned to repeating the (essentially) same code.
> There is some low-hanging fruit here.
syntactically perhaps. But it is bloody expensive to generate new plans. It's quite reasonable for a query to take one or two or even three orders of magnitude more CPU for plan generation than for actually running it. Neither dynamic sql nor parameterisation will deal with that (that I can see). I think we're stuck with expensive repeated query planning.
> Most junction tables have essentially identical structure
Yes but they are used only to link 2 tables together (I call these join tables because they're just used for joining 2 tables). They aren't used in any other context
> This is also pretty common when implementing things like "tags" or "attributes" that apply to more than one kind of object...
Maybe. I'd need to sit down with you and talk it over (not happening sadly!)
I think parameterised SQL is necessary but also a wide open door to clueless programmers massively increasing the CPU load and I've seen what cluelessness can do to perforemance in an exists-right-now nonparameterised RDBMS. OTOH I've never liked the idea that a tool's power should be reduced to avoid numpties doing damage. You modify the numpty, not the tool.
I agree with that (sadly). I'd like to see more expressive SQL, but I'm not saying it will make the situation with planning any better. I'd argue it should not make it much worse either, if done right.
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
The Ur language has great support for SQL, including updateable views. 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.
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
Besides those two, there is also a different approach to comparisons which eliminates 3VL: 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.