In short
A collection is a folder of SQL scripts, arranged in a tree and run against a warehouse database. The editor runs a selection, a script or the whole collection, and opens a browser of the database’s schema on demand. Like every Linkr entity, a collection exports, versions and publishes.
Why store your queries
The queries that interrogate a warehouse already exist, in quantity. They are simply scattered: spread across several git repositories, sometimes not versioned at all, and above all rarely pooled. Each team rewrites what another has already written.
A collection gathers them where the data lives. They run against the database concerned, they carry a name, and they follow the same sharing path as everything else — export, git repository, catalog.
This is not a transformation tool
A collection queries. It builds no database and writes nowhere: every database is attached read-only. To feed one database from another, use an ETL pipeline.
The editor
A collection’s page has two tabs, Overview — the readme — and Scripts, plus the More menu. The Scripts tab is the editor: the script tree on the left, the toolbar, then the editor with the results to its right.
| # | Aagender_source_value | #n |
|---|---|---|
gender_source_value... | Min Max | |
| 1 | F | 57 |
| 2 | M | 43 |
Scripts are arranged in folders, like files. .md files are accepted too: a collection can therefore mix queries and notes explaining what they are for — and a markdown file opens rendered, in a Preview tab, rather than as text.
Each run opens its own Result tab, named after the script — and after the selection or the line when only a piece of it ran. Result tabs close one by one, or all at once with a right-click.
Running
Four gestures, from the narrowest to the broadest:
| Action | Shortcut | What runs |
|---|---|---|
| Run selection | Cmd/Ctrl + Enter | The selected text |
| Run current line | Cmd/Ctrl + Enter | The cursor’s line, when nothing is selected |
| Run current script | Run button | The whole file |
| Run all scripts | Cmd/Ctrl + Shift + Enter | The entire collection |
The same shortcut covers the selection and the line: if there is a selection it runs, otherwise the cursor’s line does. This is RStudio’s convention, and it makes working on a long query much more direct — you can run one branch of a WITH without splitting the file.
Cmd/Ctrl + S saves. A toolbar button lists every shortcut.
The schema browser
Browse schema, the table icon next to the database picker, opens a three-pane window titled with the database’s name — the same browser as a database’s Schema tab. On the left, the tables with their row counts and a filter; in the middle, the chosen table’s columns, with their type and completeness; on the right, the chosen column’s statistics.
MIMIC-IV demo (OMOP)
| Column | Type | Completeness | Distinct |
|---|---|---|---|
#person_id | BIGINT | 100% | 100 |
#gender_concept_id | INTEGER | 100% | 2 |
#year_of_birth | INTEGER | 100% | 58 |
birth_datetime | TIMESTAMP | 100% | 100 |
#race_concept_id | INTEGER | 100% | 5 |
Aagender_source_value | VARCHAR | 100% | 2 |
#care_site_id | BIGINT | 12% | 3 |
#location_id | BIGINT | 0% | 0 |
INTEGER
The Stats box in the browser’s bar controls the column statistics: unticked, the browser only lists, which is faster on a large table. Copy SELECT saves retyping a table’s columns.
A neighbouring button, in the editor’s toolbar, Copy schema reference for SQL, copies the prefix that names the database in a query.
That button only appears in browser mode
And rightly so. In browser mode a database is attached under the name ds_<identifier>, which is useful for a query spanning several databases. In server mode that prefix does not exist: scripts use bare table names. The button is hidden rather than offering a reference that would not work. See Databases.
Choosing the database
A collection runs against one database at a time, chosen in the toolbar. The collection remembers a default, which you can change at any point.
This means one collection can serve several databases of the same model: OMOP check scripts written once run against the production database and against a test copy alike, simply by switching the active database.
In server mode a result is capped at 10,000 rows
The query runs entirely on the server, but only the first 10,000 rows come back to the browser. This is a safeguard: a SELECT * on a table of several million rows will not pull the whole warehouse into a tab.
For more than that, aggregate in SQL, or go through a dataset — which is built to carry whole tables.
Sharing a collection
A collection is an entity like any other: ZIP export, git repository, catalog publication, readme and license. Its content is text, so it versions particularly well — a git diff on a query is readable.
It is the natural format for distributing a set of queries: OMOP checks, the computation of a score, a study’s extraction queries. See Entities and sharing.
Going further
- Databases — what the scripts query, and how to name them.
- Data quality — to turn a checking query into a repeatable verification.
- ETL pipelines — for when you need to write, not only read.
- IDE — for an analysis in R or Python rather than SQL.