PROC FSQL is Jenner's DataFusion-native SQL engine. One statement joins data that lives in different systems and formats — a database table, avro/parquet/csv files, and objects in cloud storage — and returns a single result set.
This repository is a hands-on tour: runnable notebooks for real data-migration and federation use cases, plus scripts that stand up the sample databases so every example works end to end. Everything is provided in 15 languages, with the SAS-style keywords, comments, and identifiers localized in each.
New to FSQL? Start with the PROC FSQL reference.
| PROC FSQL | |
|---|---|
| Join a database table with a flat file | ✅ one statement |
| Read avro / parquet / csv directly | ✅ |
Read cloud object storage (s3://, gs://, https://) |
✅ |
| Two different databases in one query | ✅ |
| Window functions, CTEs, recursive CTEs | ✅ |
CREATE TABLE to avro / parquet / csv |
✅ |
A deliberately scattered multi-site clinical trial — the kind of setup that makes federation and migration necessary. Each table lives in a different system:
| Table | Lives in | Represents |
|---|---|---|
subjects |
PostgreSQL | the EDC / demographics system |
labs |
MySQL (and a local SQLite copy) | the central-lab system |
sites |
SQLite | a local reference database |
adverse_events |
parquet (and MinIO/S3) | a data-lake extract |
visits |
csv | a site-provided flat file |
exposure |
avro | Jenner's native store |
All tables key on usubjid (subject) and siteid (site), so they join
cleanly wherever they physically live.
Requirements: python3 and the jenner binary on your PATH. The cloud and
server examples also need Docker and a jenner built with the s3 feature.
git clone https://github.com/JennerAnalytics/fsql.git
cd fsql
# Local sources only (no servers needed): builds data/ with
# csv, avro, parquet, and SQLite databases.
./setup/setup.sh
# ...or also start PostgreSQL, MySQL, and MinIO and load them:
./setup/setup.sh --with-servers
# Open the notebooks (launch from the repo root):
jupyter lab # then browse notebooks/en/setup.py is the Python equivalent of setup.sh for the same steps.
Under notebooks/en/ (English) and i18n/<lang>/ (localized):
| # | Notebook | Shows |
|---|---|---|
| 1 | 01_migrate_database_to_avro |
Migrate a database table into Jenner's avro store |
| 2 | 02_convert_file_formats |
Convert between csv, parquet, and avro |
| 3 | 03_federate_file_and_database |
Join a flat file with a database table in one query |
| 4 | 04_reconcile_two_databases |
Reconcile PostgreSQL against MySQL |
| 5 | 05_federate_cloud_and_local |
Join a cloud (S3) object with local sources |
| 6 | 06_analytics_across_sources |
Window functions and CTEs over federated data |
Notebooks that use only local sources (1, 2, 3, 6) run after ./setup/setup.sh.
Notebooks 4 and 5 use the servers — run ./setup/setup.sh --with-servers
first, and for notebook 5 use a jenner built with --features s3 with these
set:
export JENNER_S3_ENDPOINT=http://localhost:9000
export AWS_ACCESS_KEY_ID=minioadmin
export AWS_SECRET_ACCESS_KEY=minioadmin
export JENNER_FSQL_URI_ALLOWLIST=localhost # for inline database URIsEvery notebook is provided in 15 languages under i18n/:
cs da de el es fi fr it ja ko nl pl pt sv zh
Each localized notebook has its SAS-style keywords translated into that
language's tokens (via jenner translate), comments and narrative in that
language, and variable, table, and database names chosen to read
naturally in the language.
setup/ seed.py, build_files.jenner, setup.sh/.py, docker-compose.yml
data/ generated by setup (git-ignored)
notebooks/ en/ the English notebooks
i18n/ <lang>/ localized notebooks
MIT. Sample data is synthetic.