I'm not aware of a port personally. One thing to note is that the PostgreSQL concepts in this repo are actually generated from the BigQuery ones using regex. To make this simpler, I opted to create a few PostgreSQL functions which replicate the syntax of the BigQuery functions. You may find it easier to move from the BigQuery syntax to SQLite if the functions are a blocker.

You can see the script I use to convert from BigQuery to Postgres here (this is for MIMIC-IV, but the MIMIC-III one is similar): https://github.com/MIT-LCP/mimic-code/blob/main/mimic-iv/concepts/convert_bigquery_to_postgres.sh

Converting to sqlite should be possible since the queries avoided to many BigQuery specific constructs to allow conversion to psql.

Thanks. I noted those! I was hoping to do something similar, either generating things from the BigQuery or from the Postgres ones... I was making progress and got a little hung up on GENERATE_SERIES-based views, and I wasn't finding an sqlite equivalent for table-valued functions... or at least not an easy one, or one that didn't require adding additional dependencies (peewee)... I might come back to that, but have shifted for now to looking at duckdb instead, which has what looks like richer SQL support with a similar embedded design. I noticed MIMIC-IV has a duckdb builder, but MIMIC-III doesn't... if I can get the concepts built in one or the other I think that'll be useful.

On the topic of generating the SQL for one dialect from another... I also discovered sqlglot...which is super-interesting but definitely not a panacea...yet. Trying to make it do my bidding for translating the BigQuery stuff to duckdb right now... we'll see how that goes.

IIRC there's only one query that uses generate series and I ended up hard coding it in psql because the conversion was indeed painful.

Build scripts for MIMIC-III in duckdb should exist in the repo now. One of the PRs from today or yesterday brought it in.

Good luck!

Build scripts for MIMIC-III in duckdb should exist in the repo now. One of the PRs from today or yesterday brought it in.

🎉

Random question on the concepts... I have now gotten as far as the pivot concept views (I think this might actually succeed!) and have an issue with pivoted_oasis.... but then I noticed that the pivot concept views don't seem to be translated to the Postgres version... am I missing them somewhere? Or are they kind of "bonus" and not included in the Postgres version? (Bottom line... would DuckDB version that excluded these be acceptable if I can't figure out how to fix it?)

That's probably an oversight actually. The directory currently is not included in the list of subdirs to convert:

There's nothing intrinsic about the pivot concepts that makes them difficult to convert to PostgreSQL. The only keywords they might use that aren't common elsewhere are LEAD and LAG, but I'd be surprised if duckdb doesn't already support those.

I wouldn't let the lack of completeness stop a contribution! I find the pivot queries extremely useful - which is why most of the MIMIC-IV concepts adopt the style out of the box (almost all the MIMIC-IV queries are id, time, <data specific features).