orcahouse-doc

OrcaHouse Athena

OrcaHouse Athena follows similar setup from its predecessor Portal Athena setup. Please read high level architecture note to understand the overall data flow and data pipeline within warehouse.

Use Athena setting as follows.

Workgroup:      orcahouse
Data Source:    orcavault
Database:       mart

Query

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';

See model guide dbt and erd for schema details and more…

Federated query

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

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'
        ));