PQL — Pipeline Query Language
A dplyr/pandas-style verb pipeline that compiles to safe, read-only ClickHouse SQL over the
Q-omics warehouse. Write methods don't exist in the grammar, so unsafe queries are grammatically
inexpressible. This page is the full surface behind the four pql_* tools.
01What PQL is
PQL describes a query as a chain of verbs: pick a source, then transform it with
.where(), .join(), .group_by(), .agg() and friends. The
server folds the chain into a QueryIR and emits ClickHouse SQL directly — guarded by a locked
engine policy (timeout, row cap, no writes/DDL). You don't write SQL; you compose a pipeline and call
pql_run.
# mental model — a chain of verbs, left to right Q('rna').where(gene='MKI67', lineage='BRCA').group_by('Stage').agg(mu=mean('Value'))
Q('rna') source .where(…) filter .group_by(…) group .agg(…) aggregate → compiles to read-only SQL
02The 4 tools
Same rhythm as the rest of the connector: discover, then execute.
| Tool | Layer | Purpose |
|---|---|---|
| pql_grammar | Discover | Verb signatures, aggregates, sources + cheatsheet. Learn the language. No arguments. |
| pql_schema | Discover | Conventions + source list, or one source's grain/columns/aliases. source optional. |
| pql_examples | Discover | Browse the worked corpus, or pass a NL question → one entity-filled, compilable example. complexity optional. |
| pql_run | Execute | Compile + guarded execute. Returns {columns, rows, row_count, sql, query_id, elapsed_sec} or {error}. pql required. |
Discovery before execution. An assistant should call
pql_schema (and optionally
pql_examples) to ground itself in real sources/columns before composing a query — exactly like
resolve grounds the Guided Analysis tools.03Sources
Every pipeline starts at a source.
| Form | What it is |
|---|---|
| Q('rna') | Sugar sources over omics_data with Data_Type pinned: rna, protein_ms, protein_rppa, drug, sgrna, immune_cell, methylation, go_rna… Column aliases: gene→E_name, value→Value, lineage→Lineage_Name, sample→Sample_ID. |
| Q('q_nt') | Raw tables — curated result tables and metadata: q_nt, q_survival_single, q_cross_asso, q_cross_response, q_sl_cross, q_neoantigen, info_sample, info_mutation… |
| Input(keys=[…], vals=[…]) | A literal input vector (e.g. a gene signature), used as a join target to correlate samples against. |
Call pql_schema('<source>') for a source's grain, exposed columns (name/type/role/domain), and query aliases.
04Verbs
The full chain-method set. The parser and the internal builder both allow only these.
| Verb | Does |
|---|---|
| .where(…) | Filter. col=val, a raw 'sql expr', or a semijoin col__in=Q(…).select('c'). |
| .select(cols) | Project columns. |
| .mutate(x=…) | Add a column — bin(), case(), or a raw expr like r='a/b'. |
| .join(Q(…), on=[(L,R)], how=…) | Join another pipeline. how='any_inner'|'any_left'|'inner'. A join whose right ends in .select() = an enrich (LEFT JOIN attaching those cols). |
| .group_by(cols) | Grouping keys for the following .agg(). |
| .agg(name=fn(…)) | Aggregate. Multiple arms coexist in one call. |
| .having(expr) | Filter groups after aggregation (e.g. coverage 'n >= 100'). |
| .window(partition_by=[…], order_by=…) | Open a window; follow with .mutate(rk='row_number()'). |
| .qualify(expr) | Filter on a window result (e.g. 'rk <= 3'). |
| .arrange('-col') | Order rows (- = descending). |
| .top(n, per=[…]) | Top-N rows per group. |
| .head(n) | Limit rows. |
05Aggregates & expression functions
Statistical tests (return stat + p-value)
welch_tstudent_tmann_whitneyanovacorr
Tests take by='<group expr>', e.g. welch_t('Value', by='Stage >= 3').
Scalar aggregates
countuniqmeansumminmaxquantilestddevvararg_minarg_max
arg_min(arg, val) / arg_max(arg, val) return the value of arg at the row where val is extreme.
Synthesis & window
bincaserow_numberrankdense_ranklaglead
06Special forms
Conditional aggregation (ClickHouse -If)
Any scalar agg takes where='<predicate>' to aggregate only matching rows. Multiple arms
coexist; for a ratio, agg the parts then post-agg with .mutate().
.agg(mut=uniq('Sample_ID', where="E_name='KRAS' AND Variant NOT IN ('Silent','Intron','RNA')"), n=uniq('Sample_ID')).mutate(freq='mut / n')
case() → multiIf
.mutate(lbl=case(('Value > 5', "'hi'"), default="'lo'")) # conditions & values are raw SQL; self-quote labels
bin() → quantile cut
.mutate(tert=bin('Value', method='tertile'))
Semijoin & enrich
Restrict to a cohort with col__in=Q(…).select('c') (semijoin); attach columns by ending a
.join()'s right side in .select(cols) (enrich, put after agg/head).
07Examples · Distribution & ranking
All examples below are DB-verified golden queries from the pql_examples corpus.
A-08 Summarize the KRAS expression distribution per lineage.
per-group mean + median + stddev
Q('rna').where(gene='KRAS', Sample_Type='Tissue').group_by('Lineage_Name') .agg(mu=mean('Value'), med=quantile('Value'), sd=stddev('Value'), n=count()).arrange('-mu').head(30)
B-02 Which genes show the greatest expression heterogeneity in GBM?
per-gene variance ranking with coverage HAVING
Q('rna').where(lineage='GBM', Sample_Type='Tissue').group_by('E_name') .agg(v=var('Value'), n=count()).having('n >= 100').arrange('-v').head(50)
M-10 What are the most stable pan-cancer housekeeping genes (lowest CV)?
stddev + post-agg CV ratio + HAVING
Q('rna').where(Sample_Type='Tissue', Dataset='TCGA').group_by('E_name') .agg(mu=mean('Value'), sd=stddev('Value'), n=count()).having('mu >= 5', 'n >= 5000') .mutate(cv='sd / mu').arrange('cv').head(30)
08Examples · Mutations
I-01 How often is KRAS mutated in pancreatic cancer?
mutation frequency = conditional uniq / cohort (ratio)
Q('info_mutation').where(Dataset='TCGA', Lineage_Name='PAAD').group_by('Lineage_Name') .agg(mut=uniq('Sample_ID', where="E_name='KRAS' AND Variant NOT IN ('Silent','Intron','RNA')"), seq=uniq('Sample_ID')).mutate(freq='mut / seq')
I-03 Which cancers have the most frequent BRAF mutations?
rank LINEAGES by one gene's mutation frequency
Q('info_mutation').where(Dataset='TCGA').group_by('Lineage_Name') .agg(mut=uniq('Sample_ID', where="E_name='BRAF' AND Variant NOT IN ('Silent','Intron','RNA')"), n=uniq('Sample_ID')).mutate(freq='mut / n').arrange('-freq').head(20)
I-14 Which melanoma tumors have the highest mutational burden?
per-sample mutation burden (conditional count)
Q('info_mutation').where(Dataset='TCGA', Lineage_Name='SKCM').group_by('Sample_ID') .agg(burden=count(where="Variant NOT IN ('Silent','Intron','RNA')")).arrange('-burden').head(20)
09Examples · Survival & Normal-vs-Tumor
C-01 Which genes are significant survival biomarkers in BRCA?
curated survival-hit ranking
Q('q_survival_single').where(Data_Type='rna', Lineage_Name='BRCA', OS_DFS=1) .where('Sur_p < 0.05').arrange('Sur_p').head(100)
H-09 Which genes predict survival in glioblastoma, and which direction?
rank survival hits + arg_min direction
Q('q_survival_single').where(Data_Type='rna', Lineage_Name='GBM', Period=5, OS_DFS=1) .where('Sur_p < 0.05').group_by('Entity_Name') .agg(best_p=min('Sur_p'), dir=arg_min('Sur_auc1', 'Sur_p')).arrange('best_p').head(20)
F-03 Which genes are both normal-vs-tumor hits and survival hits in BRCA?
join two curated result tables (NT ∩ survival)
Q('q_nt').where(Data_Type='rna', Lineage_Name='BRCA').where('Pvalue < 0.05') .join(Q('q_survival_single').where(Data_Type='rna', Lineage_Name='BRCA') .where('Sur_p < 0.05').select('Sur_p'), on=[('Entity_id','Entity_id'),('Lineage_Name','Lineage_Name')], how='any_inner') .arrange('-S_Cscore').head(50)
C-02 Does TP53 RNA differ between early and late stage BRCA?
two-group Welch t-test split by a boolean, via join
Q('rna').where(gene='TP53', lineage='BRCA') .join(Q('info_sample').where('Stage IS NOT NULL'), on=[('Sample_ID','Sample_ID'),('Lineage_Name','Lineage_Name'),('Dataset','Dataset')], how='any_inner') .agg(w=welch_t('Value', by='Stage >= 3'))
10Examples · Drug & dependency
D-02 Which cell lines are most sensitive to Olaparib?
rank raw drug-response
Q('drug').where(E_name='Olaparib').arrange('Value').head(20)
D-06 What is MEK-inhibitor sensitivity in KRAS-mutant cell lines?
drug response in a mutant-cell-line arm (semijoin)
Q('drug').where(E_name='Selumetinib') .where(Sample_ID__in=Q('info_mutation').where(E_name='KRAS', Sample_Type='Cell_line').select('Sample_ID')) .agg(mean_resp=mean('Value'), n=count())
L-05 Is PARP1 a dependency in BRCA1-mutant ovarian cells?
CRISPR dependency in a mutant arm (semijoin)
Q('sgrna').where(E_name='PARP1', lineage='OV') .where(Sample_ID__in=Q('info_mutation').where(E_name='BRCA1', Sample_Type='Cell_line').select('Sample_ID')) .agg(mean_dep=mean('Value'), n=count())
11Examples · Curated multi-omics lookups
The q_cross_asso / q_cross_response tables hold pre-computed associations —
cheap point lookups, no on-the-fly stats.
A-04 Are EGFR RNA and protein abundance concordant in LUAD?
curated RNA-vs-protein concordance
Q('q_cross_asso').where(Data_Type1='protein_ms', Data_Type2='rna', X_name='EGFR', Y_name='EGFR', Lineage_Name='LUAD')
C-05 Which genes correlate with CD8 T-cell infiltration in melanoma?
curated immune-cell vs gene association
Q('q_cross_asso').where(Data_Type1='rna', Data_Type2='immune_cell', Y_name='CD8+ T-cells', Lineage_Name='SKCM').where('X_Y_p < 0.05').arrange('-X_Y_fc').head(50)
12Examples · Advanced pipelines
A-03 Top-3 highest-EGFR-expressing tumor samples per lineage.
window: top-N per group via row_number() + qualify
Q('rna').where(gene='EGFR', Sample_Type='Tissue') .window(partition_by=['Lineage_Name'], order_by='-Value') .mutate(rk='row_number()').qualify('rk <= 3')
T-top Top-3 most-mutated genes per lineage (distinct patients).
top(n, per=) — top-N rows per group
Q('info_mutation').where(Dataset='TCGA').where("Variant NOT IN ('Silent','Intron','RNA')") .group_by('Lineage_Name', 'E_name').agg(n=uniq('Sample_ID')).top(3, per=['Lineage_Name'], by='-n')
T-input Find samples whose RNA profile correlates with a gene signature.
Input() literal vector + corr against it + top(per=)
Q('rna').join(Input(keys=['TP53','MYC','EGFR','PTEN','RB1'], vals=[1.2,-0.4,0.8,-1.1,0.3]), on=[('E_name','key')], how='inner') .group_by('Sample_Type','Lineage_Name','Sample_ID') .agg(r=corr('Value','input.val'), n=count()).having('n >= 3').arrange('-r').top(10, per=['Sample_Type'])
T-corr Does RNA match protein for ERBB2 per lineage (raw join)?
two-modality self-join on omics_data + per-group correlation
Q('rna').where(gene='ERBB2') .join(Q('protein_ms').where(gene='ERBB2'), on=[('Sample_ID','Sample_ID'),('Lineage_Name','Lineage_Name'),('Dataset','Dataset')], how='any_inner') .group_by('Lineage_Name').agg(r=corr('rna.Value','protein_ms.Value'))
Stuck composing a query? Call
pql_examples with a natural-language
question — it returns the closest of these templates with your entities filled in, ready to
pql_run.