orcahouse-doc

OrcaHouse Athena

OrcaHouse Athena follows a similar setup from its predecessor Portal Athena setup.

Use the Athena setting as follows.

Workgroup:      orcahouse
Data Source:    orcavault
Database:       mart

Example

See example

Query

See the model guide dbt and erd for schema and column details.

select * from orcavault.mart.lims;

select * from orcavault.mart.fastq;

select
    *
from orcavault.mart.lims as lims
    join orcavault.mart.fastq as fq on lims.sequencing_run_id = fq.sequencing_run_id and lims.library_id = fq.library_id
where
    lims.library_id = 'LPRJ230400' and format = 'ora';

Federated Query

It is best practise to use the full data source pointer when you query or scripting.

select * from <data_source>.<database>.<table>;

Equivalently in native PostgreSQL, this maps as follows.

select * from <database>.<schema>.<table>;

Passthrough query

For some reason, if you’d like to run passthrough native PostgreSQL query statement, you can wrap to table() function like so.

select * from table(
    system.query(
            query => 'select * from orcavault.mart.lims order by sequencing_run_date desc limit 10'
        ));

select * from table(
    system.query(
            query => 'select * from orcavault.mart.lims where sequencing_run_id = ''250328_A01052_0258_AHFGM7DSXF'' order by sequencing_run_date desc limit 10'
        ));