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 |
| 7 | 07_operate_and_tune_federated_queries | Introspect with DICTIONARY, PROFILE a step, edit locally with DML, write partitioned parquet, and reuse connections |
Notebooks that use only local sources (1, 2, 3, 6, 7) 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.